<?php
declare(strict_types=1);

if (!function_exists('adv_table_cols')) {
    function adv_table_cols(PDO $pdo, string $table): array
    {
        try {
            $st = $pdo->query("SHOW COLUMNS FROM `$table`");
            return $st->fetchAll(PDO::FETCH_COLUMN, 0) ?: [];
        } catch (Throwable $e) {
            return [];
        }
    }
}

if (!function_exists('adv_first_existing')) {
    function adv_first_existing(array $cols, array $candidates): ?string
    {
        foreach ($candidates as $c) {
            if (in_array($c, $cols, true)) {
                return $c;
            }
        }
        return null;
    }
}

if (!function_exists('apply_monthly_advance_deductions')) {
    function apply_monthly_advance_deductions(PDO $pdo, string $payrollMonth, int $branchId, int $userId): array
    {
        $result = [
            'created' => 0,
            'skipped' => 0,
            'errors'  => [],
            'notes'   => [],
        ];

        $advCols = adv_table_cols($pdo, 'employee_advances');
        $dedCols = adv_table_cols($pdo, 'employee_deductions');
        $empCols = adv_table_cols($pdo, 'employees');

        if (!$advCols || !$dedCols) {
            $result['errors'][] = 'جدول السلف أو الخصومات غير موجود.';
            return $result;
        }

        $empBranchCol          = adv_first_existing($empCols, ['branch_id', 'branch', 'branchId']);
        $hasDeductionDate      = in_array('deduction_date', $dedCols, true);
        $hasPayrollMonth       = in_array('payroll_month', $dedCols, true);
        $hasReason             = in_array('reason', $dedCols, true);
        $hasCreatedBy          = in_array('created_by', $dedCols, true);
        $hasSourceType         = in_array('source_type', $dedCols, true);
        $hasSourceId           = in_array('source_id', $dedCols, true);

        $hasMonthsCount        = in_array('months_count', $advCols, true);
        $hasDeductedTotal      = in_array('deducted_total', $advCols, true);
        $hasDeductedMonths     = in_array('deducted_months', $advCols, true);
        $hasNextDeductionMonth = in_array('next_deduction_month', $advCols, true);
        $hasAutoDeductEnabled  = in_array('auto_deduct_enabled', $advCols, true);
        $hasStatus             = in_array('status', $advCols, true);

        $sql = "
            SELECT
                a.id,
                a.employee_id,
                a.amount,
                COALESCE(a.monthly_deduction, 0) AS monthly_deduction,
                " . ($hasMonthsCount ? "COALESCE(a.months_count,0)" : "0") . " AS months_count,
                " . ($hasDeductedTotal ? "COALESCE(a.deducted_total,0)" : "0") . " AS deducted_total,
                " . ($hasDeductedMonths ? "COALESCE(a.deducted_months,0)" : "0") . " AS deducted_months,
                " . ($hasNextDeductionMonth ? "a.next_deduction_month" : "NULL") . " AS next_deduction_month,
                " . ($hasAutoDeductEnabled ? "COALESCE(a.auto_deduct_enabled,1)" : "1") . " AS auto_deduct_enabled,
                " . ($hasStatus ? "COALESCE(a.status,'UNPAID')" : "'UNPAID'") . " AS status
            FROM employee_advances a
            JOIN employees e ON e.id = a.employee_id
            WHERE COALESCE(a.status, 'UNPAID') <> 'PAID'
              AND COALESCE(a.monthly_deduction, 0) > 0
        ";

        $params = [];

        if ($hasAutoDeductEnabled) {
            $sql .= " AND COALESCE(a.auto_deduct_enabled,1) = 1";
        }

        if ($hasNextDeductionMonth) {
            $sql .= " AND (a.next_deduction_month IS NULL OR a.next_deduction_month = '' OR a.next_deduction_month <= ?)";
            $params[] = $payrollMonth;
        }

        if ($empBranchCol) {
            $sql .= " AND e.`$empBranchCol` = ?";
            $params[] = $branchId;
        }

        $sql .= " ORDER BY a.id ASC";

        $st = $pdo->prepare($sql);
        $st->execute($params);
        $advances = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

        foreach ($advances as $adv) {
            try {
                $advanceId      = (int)$adv['id'];
                $employeeId     = (int)$adv['employee_id'];
                $advanceAmount  = (float)$adv['amount'];
                $monthlyDeduct  = (float)$adv['monthly_deduction'];
                $monthsCount    = (int)$adv['months_count'];
                $deductedTotal  = (float)$adv['deducted_total'];
                $deductedMonths = (int)$adv['deducted_months'];

                if ($employeeId <= 0 || $advanceAmount <= 0 || $monthlyDeduct <= 0) {
                    $result['skipped']++;
                    continue;
                }

                if ($monthsCount > 0 && $deductedMonths >= $monthsCount) {
                    if ($hasStatus) {
                        $up = $pdo->prepare("UPDATE employee_advances SET status='PAID' WHERE id=?");
                        $up->execute([$advanceId]);
                    }
                    $result['skipped']++;
                    continue;
                }

                if ($deductedTotal >= $advanceAmount) {
                    if ($hasStatus) {
                        $up = $pdo->prepare("UPDATE employee_advances SET status='PAID' WHERE id=?");
                        $up->execute([$advanceId]);
                    }
                    $result['skipped']++;
                    continue;
                }

                $reasonText = "خصم سلفة شهر {$payrollMonth} - سلفة #{$advanceId}";

                if ($hasPayrollMonth && $hasReason) {
                    $check = $pdo->prepare("
                        SELECT id
                        FROM employee_deductions
                        WHERE employee_id = ?
                          AND payroll_month = ?
                          AND reason = ?
                        LIMIT 1
                    ");
                    $check->execute([$employeeId, $payrollMonth, $reasonText]);

                    if ($check->fetchColumn()) {
                        $result['skipped']++;
                        continue;
                    }
                }

                $remaining       = max(0, $advanceAmount - $deductedTotal);
                $deductionAmount = min($monthlyDeduct, $remaining);

                if ($deductionAmount <= 0) {
                    $result['skipped']++;
                    continue;
                }

                $deductionDate = $payrollMonth . '-01';

                $fields = ['employee_id', 'amount'];
                $values = [$employeeId, $deductionAmount];

                if ($hasReason) {
                    $fields[] = 'reason';
                    $values[] = $reasonText;
                }

                if ($hasDeductionDate) {
                    $fields[] = 'deduction_date';
                    $values[] = $deductionDate;
                }

                if ($hasPayrollMonth) {
                    $fields[] = 'payroll_month';
                    $values[] = $payrollMonth;
                }

                if ($hasCreatedBy) {
                    $fields[] = 'created_by';
                    $values[] = $userId;
                }

                if ($hasSourceType) {
                    $fields[] = 'source_type';
                    $values[] = 'advance_installment';
                }

                if ($hasSourceId) {
                    $fields[] = 'source_id';
                    $values[] = $advanceId;
                }

                $fieldSql = '`' . implode('`,`', $fields) . '`';
                $placeholders = implode(',', array_fill(0, count($fields), '?'));

                $ins = $pdo->prepare("
                    INSERT INTO employee_deductions ($fieldSql)
                    VALUES ($placeholders)
                ");
                $ins->execute($values);

                $newDeductedTotal  = $deductedTotal + $deductionAmount;
                $newDeductedMonths = $deductedMonths + 1;

                $updateSets = [];
                $updateVals = [];

                if ($hasDeductedTotal) {
                    $updateSets[] = "deducted_total = ?";
                    $updateVals[] = $newDeductedTotal;
                }

                if ($hasDeductedMonths) {
                    $updateSets[] = "deducted_months = ?";
                    $updateVals[] = $newDeductedMonths;
                }

                if ($hasNextDeductionMonth) {
                    $d = DateTime::createFromFormat('Y-m-d', $deductionDate);
                    $d->modify('+1 month');
                    $updateSets[] = "next_deduction_month = ?";
                    $updateVals[] = $d->format('Y-m');
                }

                if ($hasStatus) {
                    $newStatus = ($newDeductedTotal >= $advanceAmount) ? 'PAID' : 'UNPAID';
                    $updateSets[] = "status = ?";
                    $updateVals[] = $newStatus;
                }

                if ($updateSets) {
                    $updateVals[] = $advanceId;
                    $up = $pdo->prepare("
                        UPDATE employee_advances
                        SET " . implode(', ', $updateSets) . "
                        WHERE id = ?
                    ");
                    $up->execute($updateVals);
                }

                $result['created']++;
            } catch (Throwable $e) {
                $result['errors'][] = 'Advance #' . (int)$adv['id'] . ': ' . $e->getMessage();
            }
        }

        return $result;
    }
}