<?php
declare(strict_types=1);

error_reporting(E_ALL);
ini_set('display_errors', '1');

require_once __DIR__ . '/../inc/db.php';
require_once __DIR__ . '/../vendor/autoload.php';
require_once __DIR__ . '/../inc/mail_helper.php';

/*
|--------------------------------------------------------------------------
| Resolve PDO connection
|--------------------------------------------------------------------------
*/
$pdoConn = null;

if (isset($pdo) && $pdo instanceof PDO) {
    $pdoConn = $pdo;
} elseif (isset($conn) && $conn instanceof PDO) {
    $pdoConn = $conn;
} elseif (function_exists('db')) {
    $tmp = db();
    if ($tmp instanceof PDO) {
        $pdoConn = $tmp;
    }
} elseif (function_exists('getPDO')) {
    $tmp = getPDO();
    if ($tmp instanceof PDO) {
        $pdoConn = $tmp;
    }
}

if (!$pdoConn instanceof PDO) {
    http_response_code(500);
    header('Content-Type: text/plain; charset=utf-8');
    exit(
        "خطأ: لم أستطع الحصول على اتصال قاعدة البيانات بصيغة PDO.\n" .
        "تأكد من ملف /inc/db.php وهل يعرّف \$pdo أو \$conn أو دالة db() أو getPDO()."
    );
}

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 [];
    }
}

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

function send_deduction_email_to_employee(
    string $toEmail,
    string $employeeName,
    float $deductionAmount,
    string $payrollMonth,
    string $reasonText
): bool {
    if ($toEmail === '') {
        return false;
    }

    try {
        $mail = new PHPMailer(true);

        $mail->isSMTP();
        $mail->Host = 'localhost';
        $mail->SMTPAuth = false;
        $mail->Port = 25;
        $mail->CharSet = 'UTF-8';

        $mail->setFrom('no-reply@hamdanlegalgroup.ae', 'Hamdan Legal Group');
        $mail->addAddress($toEmail, $employeeName);
        $mail->addCC('contact@hamdanlegalgroup.ae');

        $mail->isHTML(true);
        $mail->Subject = 'إشعار خصم من الراتب – Hamdan Legal Group';

        $amountFormatted = number_format($deductionAmount, 2);

       $mail->Body = "
<div dir='rtl' style='font-family:Tajawal,Arial,sans-serif;line-height:1.9;color:#111'>
    <p>السيد/ة {$employeeName}،</p>
    <p>نحيطكم علمًا بأنه تم اعتماد راتب شهر {$payrollMonth} وفق التفاصيل التالية:</p>

    <table width='100%' style='border-collapse:collapse;margin:20px 0'>
        <tr>
            <td style='padding:10px;border:1px solid #ddd;background:#f8fafc'>إجمالي الراتب</td>
            <td style='padding:10px;border:1px solid #ddd'>{$grossFormatted} درهم</td>
        </tr>
        <tr>
            <td style='padding:10px;border:1px solid #ddd;background:#f8fafc'>قسط السلفة الشهري</td>
            <td style='padding:10px;border:1px solid #ddd'>{$advanceInstallmentFormatted} درهم</td>
        </tr>
        <tr>
            <td style='padding:10px;border:1px solid #ddd;background:#f8fafc'>إجمالي الخصومات لهذا الشهر</td>
            <td style='padding:10px;border:1px solid #ddd'>{$totalDeductionsFormatted} درهم</td>
        </tr>
        <tr>
            <td style='padding:10px;border:1px solid #ddd;background:#f8fafc'><strong>صافي الراتب</strong></td>
            <td style='padding:10px;border:1px solid #ddd'><strong>{$netFormatted} درهم</strong></td>
        </tr>
    </table>

    <p>في حال وجود أي استفسار يرجى التواصل مع الإدارة المالية.</p>
    <p>مع التحية<br>Hamdan Legal Group</p>
</div>

                            <!-- Footer -->
                            <tr>
                                <td style='background:#f8fafc;text-align:center;padding:15px;font-size:12px;color:#64748b'>
                                    هذا إشعار تلقائي – يرجى عدم الرد على هذا البريد
                                </td>
                            </tr>

                        </table>

                    </td>
                </tr>
            </table>
        </div>
        ";

        $mail->AltBody =
            "السيد/ة {$employeeName}\n\n" .
            "تم تسجيل خصم من راتبكم.\n" .
            "الشهر: {$payrollMonth}\n" .
            "المبلغ: {$amountFormatted} درهم\n" .
            "السبب: {$reasonText}\n\n" .
            "Hamdan Legal Group";

        return $mail->send();

    } catch (Exception $e) {
        error_log('send_deduction_email_to_employee error: ' . $e->getMessage());
        return false;
    }
}

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) {
        $result['errors'][] = 'جدول employee_advances غير موجود أو غير قابل للقراءة.';
        return $result;
    }

    if (!$dedCols) {
        $result['errors'][] = 'جدول employee_deductions غير موجود أو غير قابل للقراءة.';
        return $result;
    }

    $requiredAdvanceCols = [
        'id',
        'employee_id',
        'amount',
        'monthly_deduction',
        'status'
    ];

    foreach ($requiredAdvanceCols as $col) {
        if (!in_array($col, $advCols, true)) {
            $result['errors'][] = "العمود {$col} غير موجود في جدول employee_advances.";
        }
    }

    $requiredDeductionCols = ['employee_id', 'amount'];
    foreach ($requiredDeductionCols as $col) {
        if (!in_array($col, $dedCols, true)) {
            $result['errors'][] = "العمود {$col} غير موجود في جدول employee_deductions.";
        }
    }

    if ($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);

    $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);

    $empNameCol  = adv_first_existing($empCols, ['full_name', 'name', 'emp_name', 'employee', 'arabic_name']);
    $empEmailCol = adv_first_existing($empCols, ['email', 'work_email', 'employee_email', 'personal_email']);

    $empNameSql = $empNameCol
        ? "COALESCE(NULLIF(TRIM(e.`$empNameCol`), ''), CONCAT('EMP#', e.id)) AS employee_name"
        : "CONCAT('EMP#', e.id) AS employee_name";

    $empEmailSql = $empEmailCol
        ? "COALESCE(NULLIF(TRIM(e.`$empEmailCol`), ''), '') AS employee_email"
        : "'' AS employee_email";

    $monthsCountSql = $hasMonthsCount ? "a.months_count" : "NULL AS months_count";
    $deductedTotalSql = $hasDeductedTotal ? "COALESCE(a.deducted_total, 0) AS deducted_total" : "0 AS deducted_total";
    $deductedMonthsSql = $hasDeductedMonths ? "COALESCE(a.deducted_months, 0) AS deducted_months" : "0 AS deducted_months";
    $nextMonthSql = $hasNextDeductionMonth ? "a.next_deduction_month" : "NULL AS next_deduction_month";
    $autoDeductSql = $hasAutoDeductEnabled ? "COALESCE(a.auto_deduct_enabled, 1) AS auto_deduct_enabled" : "1 AS auto_deduct_enabled";

    $sql = "
        SELECT
            a.id,
            a.employee_id,
            a.amount,
            a.monthly_deduction,
            $monthsCountSql,
            $deductedTotalSql,
            $deductedMonthsSql,
            $nextMonthSql,
            $autoDeductSql,
            COALESCE(a.status, 'UNPAID') AS status,
            $empNameSql,
            $empEmailSql
        FROM employee_advances a
        JOIN employees e ON e.id = a.employee_id
        WHERE COALESCE(a.status, 'UNPAID') <> 'PAID'
          AND a.monthly_deduction IS NOT NULL
          AND a.monthly_deduction > 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    = isset($adv['months_count']) && $adv['months_count'] !== null ? (int)$adv['months_count'] : 0;
            $deductedTotal  = (float)($adv['deducted_total'] ?? 0);
            $deductedMonths = (int)($adv['deducted_months'] ?? 0);
            $employeeName   = trim((string)($adv['employee_name'] ?? ('EMP#' . $employeeId)));
            $employeeEmail  = trim((string)($adv['employee_email'] ?? ''));

            if ($employeeId <= 0 || $advanceAmount <= 0 || $monthlyDeduct <= 0) {
                $result['skipped']++;
                $result['notes'][] = "تم تجاوز السلفة #{$advanceId} لأن البيانات غير مكتملة.";
                continue;
            }

            if ($monthsCount > 0 && $deductedMonths >= $monthsCount) {
                if ($hasStatusCol = in_array('status', $advCols, true)) {
                    $up = $pdo->prepare("UPDATE employee_advances SET status='PAID' WHERE id=?");
                    $up->execute([$advanceId]);
                }

                $result['skipped']++;
                $result['notes'][] = "تم تجاوز السلفة #{$advanceId} لأنها وصلت لعدد الأشهر المحدد.";
                continue;
            }

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

                $result['skipped']++;
                $result['notes'][] = "تم تجاوز السلفة #{$advanceId} لأنها مسددة بالكامل.";
                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']++;
                    $result['notes'][] = "تم تجاوز السلفة #{$advanceId} لأن خصم هذا الشهر موجود بالفعل.";
                    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;
            }

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

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

            $emailSent = false;
            if ($employeeEmail !== '') {
                $emailSent = send_deduction_email_to_employee(
                    $employeeEmail,
                    $employeeName,
                    $deductionAmount,
                    $payrollMonth,
                    $reasonText
                );
            }

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

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

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

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

            if ($hasNextDeductionMonth) {
                $nextMonthDate = DateTime::createFromFormat('Y-m-d', $deductionDate);
                if (!$nextMonthDate) {
                    throw new RuntimeException("تعذر تكوين تاريخ الشهر التالي للسلفة #{$advanceId}");
                }
                $nextMonthDate->modify('+1 month');
                $nextDeductionMonth = $nextMonthDate->format('Y-m');

                $updateSets[] = "next_deduction_month = ?";
                $updateVals[] = $nextDeductionMonth;
            }

            $newStatus = 'UNPAID';
            if ($newDeductedTotal >= $advanceAmount) {
                $newStatus = 'PAID';
            } elseif ($monthsCount > 0 && $newDeductedMonths >= $monthsCount) {
                $newStatus = 'PAID';
            }

            $updateSets[] = "status = ?";
            $updateVals[] = $newStatus;

            $updateVals[] = $advanceId;

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

            if (function_exists('audit')) {
                audit($pdo, $userId, 'AUTO_DEDUCT', 'advance', $advanceId, [
                    'payroll_month'    => $payrollMonth,
                    'deduction_amount' => $deductionAmount,
                    'deducted_total'   => $newDeductedTotal,
                    'deducted_months'  => $newDeductedMonths,
                    'employee_email'   => $employeeEmail,
                    'email_sent'       => $emailSent ? 1 : 0,
                ]);
            }

            $result['created']++;
            $result['notes'][] = "تم إنشاء خصم للسلفة #{$advanceId} بمبلغ {$deductionAmount} للشهر {$payrollMonth}.";

            if ($employeeEmail === '') {
                $result['notes'][] = "لم يتم إرسال البريد للموظف {$employeeName} لعدم وجود بريد إلكتروني.";
            } elseif ($emailSent) {
                $result['notes'][] = "تم إرسال بريد إشعار إلى {$employeeName} ({$employeeEmail}).";
            } else {
                $result['notes'][] = "تعذر إرسال البريد إلى {$employeeName} ({$employeeEmail}).";
            }

        } catch (Throwable $e) {
            $result['errors'][] = "السلفة #" . ((int)($adv['id'] ?? 0)) . ": " . $e->getMessage();
        }
    }

    return $result;
}

header('Content-Type: text/plain; charset=utf-8');

try {
    $branchId = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : 1;
    $userId   = isset($_GET['user_id']) ? (int)$_GET['user_id'] : 1;
    $payrollMonth = trim((string)($_GET['month'] ?? date('Y-m')));

    if (!preg_match('/^\d{4}-\d{2}$/', $payrollMonth)) {
        throw new RuntimeException('صيغة month يجب أن تكون مثل 2026-03');
    }

    echo "تشغيل خصومات السلف للشهر: {$payrollMonth}\n";
    echo "الفرع: {$branchId}\n";
    echo "المستخدم: {$userId}\n";
    echo "-----------------------------\n";

    $result = apply_monthly_advance_deductions($pdoConn, $payrollMonth, $branchId, $userId);

    echo "تم الإنشاء: " . (int)$result['created'] . "\n";
    echo "تم التجاوز: " . (int)$result['skipped'] . "\n";

    if (!empty($result['notes'])) {
        echo "\nملاحظات:\n";
        foreach ($result['notes'] as $note) {
            echo "- {$note}\n";
        }
    }

    if (!empty($result['errors'])) {
        echo "\nأخطاء:\n";
        foreach ($result['errors'] as $error) {
            echo "- {$error}\n";
        }
    } else {
        echo "\nتم التنفيذ بنجاح.\n";
    }

} catch (Throwable $e) {
    http_response_code(500);
    echo "خطأ: " . $e->getMessage();
}