<?php
// finance/advances.php
declare(strict_types=1);

if (!function_exists('h')) {
    function h($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

require_once __DIR__ . '/_layout.php';
require_once __DIR__ . '/../inc/permissions.php';

$canPermissionManage = has_any_permission(['finance_access', 'advances.php_page']);
$canFinanceManage    = function_exists('finance_can_manage') ? finance_can_manage() : false;
$currentEmployeeId   = function_exists('finance_current_employee_id') ? finance_current_employee_id($pdo) : 0;

$role = strtoupper((string)($_SESSION['role'] ?? ''));

$canManage = ($role === 'OWNER') || $canFinanceManage || $canPermissionManage;
$canSharePayroll = ($role === 'OWNER') || $canPermissionManage || $canFinanceManage;

if (!$canManage && $currentEmployeeId <= 0) {
    if (!headers_sent()) {
        http_response_code(403);
    }
    exit('غير مصرح');
}

finance_shell_start('advances');
page_header('السلف');

$branchId = (int)($_SESSION['branch_id'] ?? 1);
$userId   = (int)($_SESSION['user_id'] ?? 0);

$msg = '';
$err = '';
$editAdvance = null;

/* ===== Helpers ===== */
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 adv_emp_name_sql(PDO $pdo, string $alias = 'e'): string
{
    $cols = adv_table_cols($pdo, 'employees');

    $nameCol = adv_first_existing($cols, ['full_name', 'name', 'emp_name', 'employee', 'arabic_name', 'first_name']);
    $lastCol = adv_first_existing($cols, ['last_name', 'family_name']);

    if ($nameCol && $nameCol !== 'first_name') {
        return "NULLIF(TRIM($alias.`$nameCol`),'')";
    }

    if ($nameCol === 'first_name' && $lastCol) {
        return "NULLIF(TRIM(CONCAT(COALESCE($alias.`first_name`,''),' ',COALESCE($alias.`$lastCol`,''))),'')";
    }

    return "CONCAT('EMP#', $alias.id)";
}

function sync_advance_to_deduction(
    PDO $pdo,
    int $advanceId,
    int $employeeId,
    float $amount,
    string $reason,
    string $advanceDate,
    int $userId
): void {
    $dedCols = adv_table_cols($pdo, 'employee_deductions');
    if (!$dedCols) {
        return;
    }

    $hasReason       = in_array('reason', $dedCols, true);
    $hasDedDate      = in_array('deduction_date', $dedCols, true);
    $hasPayrollMonth = in_array('payroll_month', $dedCols, true);
    $hasCreatedBy    = in_array('created_by', $dedCols, true);
    $hasSourceType   = in_array('source_type', $dedCols, true);
    $hasSourceId     = in_array('source_id', $dedCols, true);

    $payrollMonth = substr($advanceDate !== '' ? $advanceDate : date('Y-m-d'), 0, 7);
    $reasonText   = 'سلفة' . ($reason !== '' ? ' - ' . $reason : '');

    $existingId = 0;
    if ($hasSourceType && $hasSourceId) {
        $st = $pdo->prepare("
            SELECT id
            FROM employee_deductions
            WHERE source_type = 'advance' AND source_id = ?
            LIMIT 1
        ");
        $st->execute([$advanceId]);
        $existingId = (int)($st->fetchColumn() ?: 0);
    }

    if ($existingId > 0) {
        $sets = ['employee_id = ?', 'amount = ?'];
        $vals = [$employeeId, $amount];

        if ($hasReason) {
            $sets[] = 'reason = ?';
            $vals[] = $reasonText;
        }

        if ($hasDedDate) {
            $sets[] = 'deduction_date = ?';
            $vals[] = $advanceDate !== '' ? $advanceDate : date('Y-m-d');
        }

        if ($hasPayrollMonth) {
            $sets[] = 'payroll_month = ?';
            $vals[] = $payrollMonth;
        }

        if ($hasCreatedBy) {
            $sets[] = 'created_by = ?';
            $vals[] = $userId;
        }

        $vals[] = $existingId;

        $sql = "UPDATE employee_deductions SET " . implode(', ', $sets) . " WHERE id = ?";
        $st = $pdo->prepare($sql);
        $st->execute($vals);
        return;
    }

    $fields = ['employee_id', 'amount'];
    $vals   = [$employeeId, $amount];

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

    if ($hasDedDate) {
        $fields[] = 'deduction_date';
        $vals[]   = $advanceDate !== '' ? $advanceDate : date('Y-m-d');
    }

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

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

    if ($hasSourceType) {
        $fields[] = 'source_type';
        $vals[]   = 'advance';
    }

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

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

    $st = $pdo->prepare("
        INSERT INTO employee_deductions ($fieldList)
        VALUES ($placeholders)
    ");
    $st->execute($vals);
}

function delete_advance_deduction_link(PDO $pdo, int $advanceId): void
{
    $dedCols = adv_table_cols($pdo, 'employee_deductions');
    if (!$dedCols) {
        return;
    }

    if (in_array('source_type', $dedCols, true) && in_array('source_id', $dedCols, true)) {
        $st = $pdo->prepare("
            DELETE FROM employee_deductions
            WHERE source_type = 'advance' AND source_id = ?
        ");
        $st->execute([$advanceId]);
    }
}

/* ===== Schema ===== */
$advCols = adv_table_cols($pdo, 'employee_advances');
if (!$advCols) {
    echo "<div class='card' style='margin-bottom:12px'>";
    echo "<h3 style='margin:0 0 10px 0;color:#fff'>جدول السلف غير موجود</h3>";
    echo "<div style='color:#64748b;font-size:14px;margin-bottom:10px'>نفّذ هذا SQL مرة واحدة في phpMyAdmin:</div>";
    echo "<pre style='direction:ltr;text-align:left;background:#0f172a;color:#fff;padding:14px;border-radius:12px;white-space:pre-wrap'>CREATE TABLE employee_advances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  reason VARCHAR(255) NULL,
  advance_date DATE NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'UNPAID',
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</pre>";
    echo "</div>";
    finance_shell_end();
    exit;
}

$empCols         = adv_table_cols($pdo, 'employees');
$empBranchCol    = adv_first_existing($empCols, ['branch_id', 'branch', 'branchId']);
$empNameExpr     = adv_emp_name_sql($pdo, 'e');

$hasReason             = in_array('reason', $advCols, true);
$hasAdvanceDate        = in_array('advance_date', $advCols, true);
$hasStatus             = in_array('status', $advCols, true);
$hasCreatedBy          = in_array('created_by', $advCols, true);
$hasCreatedAt          = in_array('created_at', $advCols, true);

$hasBasicSalary        = in_array('basic_salary', $advCols, true);
$hasDeductionPercent   = in_array('deduction_percentage', $advCols, true);
$hasMonthlyDeduction   = in_array('monthly_deduction', $advCols, 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);

$statusUnpaid  = 'UNPAID';
$statusPaid    = 'PAID';
$statusPending = 'PENDING';

/* ===== POST: Create / Edit / Delete / Mark paid ===== */
if ($canManage && $_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = (string)($_POST['action'] ?? 'create');

    if ($action === 'create') {
        $employeeId      = (int)($_POST['employee_id'] ?? 0);
        $amount          = (float)($_POST['amount'] ?? 0);
        $reason          = trim((string)($_POST['reason'] ?? ''));
        $advDate         = trim((string)($_POST['advance_date'] ?? ''));
        $basicSalary     = (float)($_POST['basic_salary'] ?? 0);
        $percentageInput = (float)($_POST['deduction_percentage'] ?? 0);
        $percentage      = ($percentageInput > 0) ? ($percentageInput / 100) : 0;
        $monthly         = (float)($_POST['monthly_deduction'] ?? 0);

        if ($advDate !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $advDate)) {
            $advDate = date('Y-m-d');
        }
        if ($advDate === '') {
            $advDate = date('Y-m-d');
        }

        if ($monthly <= 0 && $basicSalary > 0 && $percentage > 0) {
            $monthly = round($basicSalary * $percentage, 2);
        }

        $months = ($monthly > 0) ? (int)ceil($amount / $monthly) : 0;

        if ($employeeId <= 0 || $amount <= 0) {
            $err = 'اختر موظف وأدخل مبلغ صحيح';
        } else {
            try {
                $pdo->beginTransaction();

                if ($empBranchCol) {
                    $st = $pdo->prepare("SELECT id FROM employees WHERE id = ? AND `$empBranchCol` = ? LIMIT 1");
                    $st->execute([$employeeId, $branchId]);
                } else {
                    $st = $pdo->prepare("SELECT id FROM employees WHERE id = ? LIMIT 1");
                    $st->execute([$employeeId]);
                }

                if (!$st->fetchColumn()) {
                    throw new RuntimeException('الموظف غير موجود');
                }

                $fields = ['employee_id', 'amount'];
                $vals   = [$employeeId, $amount];

                if ($hasReason) {
                    $fields[] = 'reason';
                    $vals[]   = ($reason !== '' ? $reason : null);
                }

                if ($hasAdvanceDate) {
                    $fields[] = 'advance_date';
                    $vals[]   = $advDate;
                }

                if ($hasStatus) {
                    $fields[] = 'status';
                    $vals[]   = $statusUnpaid;
                }

                if ($hasBasicSalary) {
                    $fields[] = 'basic_salary';
                    $vals[]   = ($basicSalary > 0 ? $basicSalary : null);
                }

                if ($hasDeductionPercent) {
                    $fields[] = 'deduction_percentage';
                    $vals[]   = ($percentage > 0 ? $percentage : null);
                }

                if ($hasMonthlyDeduction) {
                    $fields[] = 'monthly_deduction';
                    $vals[]   = ($monthly > 0 ? $monthly : null);
                }

                if ($hasMonthsCount) {
                    $fields[] = 'months_count';
                    $vals[]   = ($months > 0 ? $months : null);
                }

                if ($hasDeductedTotal) {
                    $fields[] = 'deducted_total';
                    $vals[]   = 0;
                }

                if ($hasDeductedMonths) {
                    $fields[] = 'deducted_months';
                    $vals[]   = 0;
                }

                if ($hasNextDeductionMonth) {
                    $fields[] = 'next_deduction_month';
                    $vals[]   = date('Y-m');
                }

                if ($hasAutoDeductEnabled) {
                    $fields[] = 'auto_deduct_enabled';
                    $vals[]   = 1;
                }

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

                $fieldList = '`' . implode('`,`', $fields) . '`';
                $ph = implode(',', array_fill(0, count($fields), '?'));

                $st = $pdo->prepare("INSERT INTO employee_advances ($fieldList) VALUES ($ph)");
                $st->execute($vals);

                $advanceId = (int)$pdo->lastInsertId();

                sync_advance_to_deduction($pdo, $advanceId, $employeeId, $amount, $reason, $advDate, $userId);

                if (function_exists('audit')) {
                    audit($pdo, $userId, 'CREATE', 'advance', $advanceId, [
                        'employee_id'          => $employeeId,
                        'amount'               => $amount,
                        'reason'               => $reason,
                        'advance_date'         => $advDate,
                        'basic_salary'         => $basicSalary,
                        'deduction_percentage' => $percentage,
                        'monthly_deduction'    => $monthly,
                        'months_count'         => $months,
                    ]);
                }

                $pdo->commit();
                $msg = 'تم إضافة السلفة وإظهارها تلقائيًا في الخصومات';
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                $err = 'تعذر الإضافة: ' . $e->getMessage();
            }
        }
    }

    if ($action === 'update') {
        $advanceId       = (int)($_POST['advance_id'] ?? 0);
        $employeeId      = (int)($_POST['employee_id'] ?? 0);
        $amount          = (float)($_POST['amount'] ?? 0);
        $reason          = trim((string)($_POST['reason'] ?? ''));
        $advDate         = trim((string)($_POST['advance_date'] ?? ''));
        $status          = strtoupper(trim((string)($_POST['status'] ?? $statusUnpaid)));
        $basicSalary     = (float)($_POST['basic_salary'] ?? 0);
        $percentageInput = (float)($_POST['deduction_percentage'] ?? 0);
        $percentage      = ($percentageInput > 0) ? ($percentageInput / 100) : 0;
        $monthly         = (float)($_POST['monthly_deduction'] ?? 0);

        if ($advDate !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $advDate)) {
            $advDate = date('Y-m-d');
        }
        if ($advDate === '') {
            $advDate = date('Y-m-d');
        }

        if (!in_array($status, [$statusUnpaid, $statusPaid, $statusPending], true)) {
            $status = $statusUnpaid;
        }

        if ($monthly <= 0 && $basicSalary > 0 && $percentage > 0) {
            $monthly = round($basicSalary * $percentage, 2);
        }

        $months = ($monthly > 0) ? (int)ceil($amount / $monthly) : 0;

        if ($advanceId <= 0 || $employeeId <= 0 || $amount <= 0) {
            $err = 'بيانات التعديل غير صحيحة';
        } else {
            try {
                $pdo->beginTransaction();

                $checkSql = "
                    SELECT a.id
                    FROM employee_advances a
                    JOIN employees e ON e.id = a.employee_id
                    WHERE a.id = ?
                ";
                $paramsCheck = [$advanceId];

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

                $st = $pdo->prepare($checkSql . " LIMIT 1");
                $st->execute($paramsCheck);

                if (!$st->fetchColumn()) {
                    throw new RuntimeException('السلفة غير موجودة أو غير مصرح لك بتعديلها');
                }

                $sets = ["employee_id = ?", "amount = ?"];
                $vals = [$employeeId, $amount];

                if ($hasReason) {
                    $sets[] = "reason = ?";
                    $vals[] = ($reason !== '' ? $reason : null);
                }

                if ($hasAdvanceDate) {
                    $sets[] = "advance_date = ?";
                    $vals[] = $advDate;
                }

                if ($hasStatus) {
                    $sets[] = "status = ?";
                    $vals[] = $status;
                }

                if ($hasBasicSalary) {
                    $sets[] = "basic_salary = ?";
                    $vals[] = ($basicSalary > 0 ? $basicSalary : null);
                }

                if ($hasDeductionPercent) {
                    $sets[] = "deduction_percentage = ?";
                    $vals[] = ($percentage > 0 ? $percentage : null);
                }

                if ($hasMonthlyDeduction) {
                    $sets[] = "monthly_deduction = ?";
                    $vals[] = ($monthly > 0 ? $monthly : null);
                }

                if ($hasMonthsCount) {
                    $sets[] = "months_count = ?";
                    $vals[] = ($months > 0 ? $months : null);
                }

                $vals[] = $advanceId;

                $sql = "UPDATE employee_advances SET " . implode(', ', $sets) . " WHERE id = ?";
                $st = $pdo->prepare($sql);
                $st->execute($vals);

                sync_advance_to_deduction($pdo, $advanceId, $employeeId, $amount, $reason, $advDate, $userId);

                if (function_exists('audit')) {
                    audit($pdo, $userId, 'UPDATE', 'advance', $advanceId, [
                        'employee_id'          => $employeeId,
                        'amount'               => $amount,
                        'reason'               => $reason,
                        'advance_date'         => $advDate,
                        'status'               => $status,
                        'basic_salary'         => $basicSalary,
                        'deduction_percentage' => $percentage,
                        'monthly_deduction'    => $monthly,
                        'months_count'         => $months,
                    ]);
                }

                $pdo->commit();
                $msg = 'تم تعديل السلفة';
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                $err = 'تعذر التعديل: ' . $e->getMessage();
            }
        }
    }

    if ($action === 'delete') {
        $advanceId = (int)($_POST['advance_id'] ?? 0);

        if ($advanceId > 0) {
            try {
                $pdo->beginTransaction();

                $checkSql = "
                    SELECT a.id
                    FROM employee_advances a
                    JOIN employees e ON e.id = a.employee_id
                    WHERE a.id = ?
                ";
                $paramsCheck = [$advanceId];

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

                $st = $pdo->prepare($checkSql . " LIMIT 1");
                $st->execute($paramsCheck);

                if (!$st->fetchColumn()) {
                    throw new RuntimeException('السلفة غير موجودة أو غير مصرح لك بحذفها');
                }

                delete_advance_deduction_link($pdo, $advanceId);

                $st = $pdo->prepare("DELETE FROM employee_advances WHERE id = ?");
                $st->execute([$advanceId]);

                if (function_exists('audit')) {
                    audit($pdo, $userId, 'DELETE', 'advance', $advanceId, []);
                }

                $pdo->commit();
                $msg = 'تم حذف السلفة';
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
                $err = 'تعذر الحذف: ' . $e->getMessage();
            }
        }
    }

    if ($action === 'mark_paid' && $hasStatus) {
        $advanceId = (int)($_POST['advance_id'] ?? 0);

        if ($advanceId > 0) {
            try {
                $checkSql = "
                    SELECT a.id
                    FROM employee_advances a
                    JOIN employees e ON e.id = a.employee_id
                    WHERE a.id = ?
                ";
                $paramsCheck = [$advanceId];

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

                $st = $pdo->prepare($checkSql . " LIMIT 1");
                $st->execute($paramsCheck);

                if (!$st->fetchColumn()) {
                    throw new RuntimeException('السلفة غير موجودة أو غير مصرح لك بتحديثها');
                }

                $st = $pdo->prepare("UPDATE employee_advances SET status = ? WHERE id = ?");
                $st->execute([$statusPaid, $advanceId]);

                if (function_exists('audit')) {
                    audit($pdo, $userId, 'UPDATE', 'advance', $advanceId, ['status' => $statusPaid]);
                }

                $msg = 'تم تعليم السلفة بأنها مسددة';
            } catch (Throwable $e) {
                $err = 'تعذر التحديث: ' . $e->getMessage();
            }
        }
    }
}

/* ===== Edit mode ===== */
if ($canManage && isset($_GET['edit'])) {
    $editId = (int)($_GET['edit'] ?? 0);

    if ($editId > 0) {
        try {
            $editSql = "
                SELECT a.*
                FROM employee_advances a
                JOIN employees e ON e.id = a.employee_id
                WHERE a.id = ?
            ";
            $editParams = [$editId];

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

            $st = $pdo->prepare($editSql . " LIMIT 1");
            $st->execute($editParams);
            $editAdvance = $st->fetch(PDO::FETCH_ASSOC) ?: null;
        } catch (Throwable $e) {
            $err = 'تعذر تحميل بيانات السلفة للتعديل';
        }
    }
}

/* ===== Employees dropdown ===== */
$employees = [];
if ($canManage) {
    $whereEmp = [];
    $paramsEmp = [];

    if ($empBranchCol) {
        $whereEmp[] = "e.`$empBranchCol` = ?";
        $paramsEmp[] = $branchId;
    }

    $whereSqlEmp = $whereEmp ? ('WHERE ' . implode(' AND ', $whereEmp)) : '';

    $salCols = adv_table_cols($pdo, 'employee_salaries');
    $hasSalaryIsActive = in_array('is_active', $salCols, true);

    $salaryJoinCondition = $hasSalaryIsActive
        ? "s.employee_id = e.id AND s.is_active = 1"
        : "s.employee_id = e.id";

    $st = $pdo->prepare("
        SELECT
            e.id,
            COALESCE($empNameExpr, CONCAT('EMP#', e.id)) AS emp_name,
            COALESCE(s.basic_salary, 0) AS basic_salary,
            COALESCE(s.housing_allowance, 0) AS housing_allowance,
            COALESCE(s.transport_allowance, 0) AS transport_allowance,
            COALESCE(s.other_allowance, 0) AS other_allowance
        FROM employees e
        LEFT JOIN employee_salaries s
            ON $salaryJoinCondition
        $whereSqlEmp
        GROUP BY e.id
        ORDER BY emp_name ASC
    ");
    $st->execute($paramsEmp);
    $employees = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
/* ===== List ===== */
$params = [];
$whereSql = '';

if ($canManage) {
    $where = [];
    if ($empBranchCol) {
        $where[] = "e.`$empBranchCol` = ?";
        $params[] = $branchId;
    }
    $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
} else {
    $whereSql = "WHERE a.employee_id = ?";
    $params[] = $currentEmployeeId;
}

$dateSelect            = $hasAdvanceDate ? "a.advance_date" : ($hasCreatedAt ? "DATE(a.created_at) AS advance_date" : "NULL AS advance_date");
$statusSelect          = $hasStatus ? "a.status" : "'UNPAID' AS status";
$reasonSelect          = $hasReason ? "a.reason" : "NULL AS reason";
$basicSalarySelect     = $hasBasicSalary ? "a.basic_salary" : "NULL AS basic_salary";
$percentSelect         = $hasDeductionPercent ? "a.deduction_percentage" : "NULL AS deduction_percentage";
$monthlySelect         = $hasMonthlyDeduction ? "a.monthly_deduction" : "NULL AS monthly_deduction";
$monthsSelect          = $hasMonthsCount ? "a.months_count" : "NULL AS months_count";
$deductedTotalSelect   = $hasDeductedTotal ? "a.deducted_total" : "NULL AS deducted_total";
$deductedMonthsSelect  = $hasDeductedMonths ? "a.deducted_months" : "NULL AS deducted_months";
$nextMonthSelect       = $hasNextDeductionMonth ? "a.next_deduction_month" : "NULL AS next_deduction_month";

$sqlRows = "
SELECT
    a.id,
    a.employee_id,
    $dateSelect,
    $statusSelect,
    $reasonSelect,
    $basicSalarySelect,
    $percentSelect,
    $monthlySelect,
    $monthsSelect,
    $deductedTotalSelect,
    $deductedMonthsSelect,
    $nextMonthSelect,
    a.amount,
    COALESCE($empNameExpr, CONCAT('EMP#', e.id)) AS emp_name
FROM employee_advances a
JOIN employees e ON e.id = a.employee_id
$whereSql
ORDER BY a.id DESC
LIMIT 100
";

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

/* ===== Outstanding total ===== */
$sumParams = [];
$sumWhere = [];

if ($canManage) {
    if ($empBranchCol) {
        $sumWhere[] = "e.`$empBranchCol` = ?";
        $sumParams[] = $branchId;
    }
} else {
    $sumWhere[] = "a.employee_id = ?";
    $sumParams[] = $currentEmployeeId;
}

if ($hasStatus) {
    $sumWhere[] = "a.status <> ?";
    $sumParams[] = $statusPaid;
}

$sumSql = "
SELECT COALESCE(SUM(a.amount), 0)
FROM employee_advances a
JOIN employees e ON e.id = a.employee_id
" . ($sumWhere ? ('WHERE ' . implode(' AND ', $sumWhere)) : '');

$st = $pdo->prepare($sumSql);
$st->execute($sumParams);
$outstanding = (float)$st->fetchColumn();

$showSmartFields = $hasBasicSalary || $hasDeductionPercent || $hasMonthlyDeduction || $hasMonthsCount;
?>

<style>
.msg{padding:10px 12px;border-radius:12px;margin:10px 0;border:1px solid #e5e7eb;background:#fff}
.msg.ok{border-color:#bbf7d0;background:#ecfdf5;color:#065f46}
.msg.err{border-color:#fecaca;background:#fff1f2;color:#991b1b}
.table{width:100%;border-collapse:separate;border-spacing:0;overflow:hidden;border-radius:16px}
.table th,.table td{padding:10px 12px;border-bottom:1px solid #eef2f7;text-align:right;font-size:13px;vertical-align:middle}
.table thead th{background:#f8fafc;color:#0f172a;font-weight:700}
.table tr:last-child td{border-bottom:none}
.note{font-size:12px;color:#64748b;margin-top:6px}
.badge{display:inline-block;padding:6px 10px;border-radius:999px;font-size:12px;border:1px solid #e5e7eb;background:#fff}
.badge.ok{background:#ecfdf5;border-color:#bbf7d0;color:#065f46}
.badge.warn{background:#fff7ed;border-color:#fed7aa;color:#9a3412}
.kpi-mini{font-size:28px;font-weight:700;color:#fff}
.adv-smart-grid{display:flex;gap:10px;flex-wrap:wrap}
.adv-smart-grid > *{flex:1 1 180px}
.progress-wrap{min-width:110px}
.progress-bar{background:#e5e7eb;border-radius:999px;height:8px;overflow:hidden}
.progress-bar > span{display:block;height:8px;background:#16a34a;border-radius:999px}
</style>

<script>
document.addEventListener('DOMContentLoaded', function () {
    const basicEl   = document.querySelector('[name="basic_salary"]');
    const percentEl = document.querySelector('[name="deduction_percentage"]');
    const monthlyEl = document.querySelector('[name="monthly_deduction"]');
    const amountEl  = document.querySelector('[name="amount"]');
    const monthsEl  = document.querySelector('[name="months_count_preview"]');

    function calcAdvancePlan() {
        const basic   = basicEl ? parseFloat(basicEl.value || '0') : 0;
        const percent = percentEl ? parseFloat(percentEl.value || '0') : 0;
        const amount  = amountEl ? parseFloat(amountEl.value || '0') : 0;
        let monthly   = monthlyEl ? parseFloat(monthlyEl.value || '0') : 0;

        if (monthly <= 0 && basic > 0 && percent > 0 && monthlyEl) {
            monthly = basic * (percent / 100);
            monthlyEl.value = monthly.toFixed(2);
        }

        if (monthsEl) {
            if (amount > 0 && monthly > 0) {
                monthsEl.value = Math.ceil(amount / monthly) + ' شهر';
            } else {
                monthsEl.value = '';
            }
        }
    }

    if (basicEl) basicEl.addEventListener('input', calcAdvancePlan);
    if (percentEl) percentEl.addEventListener('input', calcAdvancePlan);
    if (monthlyEl) monthlyEl.addEventListener('input', calcAdvancePlan);
    if (amountEl) amountEl.addEventListener('input', calcAdvancePlan);

    calcAdvancePlan();
});
</script>

<?php if ($msg): ?><div class="msg ok"><?= h($msg) ?></div><?php endif; ?>
<?php if ($err): ?><div class="msg err"><?= h($err) ?></div><?php endif; ?>

<div class="card" style="margin-bottom:12px">
    <h3 style="margin:0 0 10px 0;color:#fff">
        <?= $canManage ? 'إجمالي السلف غير المسددة' : 'سلفي غير المسددة' ?>
    </h3>
    <div class="kpi-mini"><?= number_format($outstanding, 2) ?> AED</div>
</div>

<?php if ($canManage): ?>
<div class="card" style="margin-bottom:12px">
    <h3 style="margin:0 0 10px 0;color:#fff">
        <?= !empty($editAdvance) ? 'تعديل السلفة' : 'إضافة سلفة' ?>
    </h3>

    <form method="post" class="row">
        <input type="hidden" name="action" value="<?= !empty($editAdvance) ? 'update' : 'create' ?>">

        <?php if (!empty($editAdvance)): ?>
            <input type="hidden" name="advance_id" value="<?= (int)$editAdvance['id'] ?>">
        <?php endif; ?>

       <select class="select" name="employee_id" id="employee_id" required>
    <option value="">اختر موظف</option>
    <?php foreach ($employees as $e): ?>
        <?php
            $basic = (float)($e['basic_salary'] ?? 0);
            $housing = (float)($e['housing_allowance'] ?? 0);
            $transport = (float)($e['transport_allowance'] ?? 0);
            $other = (float)($e['other_allowance'] ?? 0);
            $allow = $housing + $transport + $other;
            $gross = $basic + $allow;
        ?>
        <option
            value="<?= (int)$e['id'] ?>"
            data-basic="<?= h((string)$basic) ?>"
            data-housing="<?= h((string)$housing) ?>"
            data-transport="<?= h((string)$transport) ?>"
            data-other="<?= h((string)$other) ?>"
            data-allow="<?= h((string)$allow) ?>"
            data-gross="<?= h((string)$gross) ?>"
            <?= (!empty($editAdvance) && (int)$editAdvance['employee_id'] === (int)$e['id']) ? 'selected' : '' ?>
        >
            <?= h((string)$e['emp_name']) ?>
        </option>
    <?php endforeach; ?>
</select>

        <input
            class="input"
            type="number"
            step="0.01"
            name="amount"
            placeholder="مبلغ السلفة"
            value="<?= !empty($editAdvance) ? h((string)$editAdvance['amount']) : '' ?>"
            required
        >

        <?php if ($hasAdvanceDate): ?>
            <input
                class="input"
                type="date"
                name="advance_date"
                value="<?= !empty($editAdvance)
                    ? h((string)($editAdvance['advance_date'] ?? date('Y-m-d')))
                    : h(date('Y-m-d')) ?>"
            >
        <?php endif; ?>

        <?php if ($hasReason): ?>
            <input
                class="input"
                name="reason"
                placeholder="السبب (اختياري)"
                style="min-width:260px"
                value="<?= !empty($editAdvance) ? h((string)($editAdvance['reason'] ?? '')) : '' ?>"
            >
        <?php endif; ?>

        <?php if ($hasBasicSalary || $hasDeductionPercent || $hasMonthlyDeduction || $hasMonthsCount): ?>
        <div class="adv-smart-grid" style="width:100%">
            <?php if ($hasBasicSalary): ?>
                <input
                    class="input"
                    type="number"
                    step="0.01"
                    name="basic_salary"
                    placeholder="الراتب الأساسي"
                    value="<?= !empty($editAdvance) ? h((string)($editAdvance['basic_salary'] ?? '')) : '' ?>"
                >
            <?php endif; ?>

            <?php if ($hasDeductionPercent): ?>
                <input
                    class="input"
                    type="number"
                    step="0.01"
                    min="0"
                    max="100"
                    name="deduction_percentage"
                    placeholder="نسبة الخصم % (اختياري)"
                    value="<?=
                        !empty($editAdvance) && isset($editAdvance['deduction_percentage']) && $editAdvance['deduction_percentage'] !== null
                        ? h((string)(((float)$editAdvance['deduction_percentage']) * 100))
                        : ''
                    ?>"
                >
            <?php endif; ?>

            <?php if ($hasMonthlyDeduction): ?>
                <input
                    class="input"
                    type="number"
                    step="0.01"
                    name="monthly_deduction"
                    placeholder="الخصم الشهري"
                    value="<?= !empty($editAdvance) ? h((string)($editAdvance['monthly_deduction'] ?? '')) : '' ?>"
                >
            <?php endif; ?>

            <?php if ($hasMonthsCount): ?>
                <input
                    class="input"
                    type="text"
                    name="months_count_preview"
                    placeholder="تنتهي خلال كم شهر"
                    value="<?=
                        !empty($editAdvance) && !empty($editAdvance['months_count'])
                        ? h((string)$editAdvance['months_count']) . ' شهر'
                        : ''
                    ?>"
                    readonly
                >
            <?php endif; ?>
        </div>
        <?php endif; ?>

        <?php if (!empty($editAdvance) && $hasStatus): ?>
            <select class="select" name="status">
                <option value="UNPAID" <?= (($editAdvance['status'] ?? '') === 'UNPAID') ? 'selected' : '' ?>>غير مسددة</option>
                <option value="PAID" <?= (($editAdvance['status'] ?? '') === 'PAID') ? 'selected' : '' ?>>مسددة</option>
                <option value="PENDING" <?= (($editAdvance['status'] ?? '') === 'PENDING') ? 'selected' : '' ?>>قيد الانتظار</option>
            </select>
        <?php endif; ?>

        <button class="btn" type="submit">
            <?= !empty($editAdvance) ? 'حفظ التعديل' : 'إضافة' ?>
        </button>

        <?php if (!empty($editAdvance)): ?>
            <a class="btn secondary" href="advances.php">إلغاء</a>
        <?php endif; ?>
    </form>
</div>
<?php endif; ?>

<div class="card">
    <h3 style="margin:0 0 10px 0;color:#fff">
        <?= $canManage ? 'سجل السلف' : 'سلفي' ?>
    </h3>

    <?php if (!$canManage): ?>
        <div class="note">هذه الصفحة تعرض سلفك فقط حفاظًا على الخصوصية.</div>
    <?php endif; ?>

    <table class="table">
        <thead>
            <tr>
                <th>التاريخ</th>
                <th>الموظف</th>
                <th>مبلغ السلفة</th>
                <?php if ($hasBasicSalary): ?><th>الراتب الأساسي</th><?php endif; ?>
                <?php if ($hasDeductionPercent): ?><th>نسبة الخصم</th><?php endif; ?>
                <?php if ($hasMonthlyDeduction): ?><th>الخصم الشهري</th><?php endif; ?>
                <?php if ($hasMonthsCount): ?><th>عدد الأشهر</th><?php endif; ?>
                <?php if ($hasDeductedTotal): ?><th>المخصوم</th><?php endif; ?>
                <?php if ($hasDeductedTotal): ?><th>المتبقي</th><?php endif; ?>
                <?php if ($hasDeductedTotal): ?><th>نسبة السداد</th><?php endif; ?>
                <?php if ($hasNextDeductionMonth): ?><th>الشهر القادم</th><?php endif; ?>
                <th>الحالة</th>
                <th>السبب</th>
                <?php if ($canManage): ?><th>تعديل</th><?php endif; ?>
                <?php if ($canManage): ?><th>حذف</th><?php endif; ?>
                <?php if ($canManage && $hasStatus): ?><th>إجراء</th><?php endif; ?>
            </tr>
        </thead>
        <tbody>
            <?php
            $baseCols = 5;
            $smartCols = 0;
            if ($hasBasicSalary) $smartCols++;
            if ($hasDeductionPercent) $smartCols++;
            if ($hasMonthlyDeduction) $smartCols++;
            if ($hasMonthsCount) $smartCols++;
            if ($hasDeductedTotal) $smartCols += 3;
            if ($hasNextDeductionMonth) $smartCols++;
            $manageCols = 0;
            if ($canManage) $manageCols += 2;
            if ($canManage && $hasStatus) $manageCols += 1;
            $colspan = $baseCols + $smartCols + $manageCols;
            ?>

            <?php if (!$rows): ?>
                <tr>
                    <td colspan="<?= (int)$colspan ?>">لا توجد بيانات</td>
                </tr>
            <?php else: ?>
                <?php foreach ($rows as $r): ?>
                    <?php
                    $status = strtoupper((string)($r['status'] ?? $statusUnpaid));
                    $isPaid = ($status === $statusPaid);

                    $statusLabel = match ($status) {
                        'PAID'    => 'مسددة',
                        'PENDING' => 'قيد الانتظار',
                        default   => 'غير مسددة',
                    };

                    $amountVal    = (float)($r['amount'] ?? 0);
                    $deductedVal  = (float)($r['deducted_total'] ?? 0);
                    $remainingVal = max(0, $amountVal - $deductedVal);
                    $percentPaid  = ($amountVal > 0) ? min(100, round(($deductedVal / $amountVal) * 100)) : 0;
                    ?>
                    <tr>
                        <td><?= h((string)($r['advance_date'] ?? '')) ?></td>
                        <td><?= h((string)$r['emp_name']) ?></td>
                        <td><?= number_format($amountVal, 2) ?> AED</td>

                        <?php if ($hasBasicSalary): ?>
                            <td><?= ($r['basic_salary'] !== null) ? number_format((float)$r['basic_salary'], 2) . ' AED' : '—' ?></td>
                        <?php endif; ?>

                        <?php if ($hasDeductionPercent): ?>
                            <td><?= ($r['deduction_percentage'] !== null) ? rtrim(rtrim(number_format(((float)$r['deduction_percentage']) * 100, 2), '0'), '.') . '%' : '—' ?></td>
                        <?php endif; ?>

                        <?php if ($hasMonthlyDeduction): ?>
                            <td><?= ($r['monthly_deduction'] !== null) ? number_format((float)$r['monthly_deduction'], 2) . ' AED' : '—' ?></td>
                        <?php endif; ?>

                        <?php if ($hasMonthsCount): ?>
                            <td><?= ($r['months_count'] !== null) ? ((int)$r['months_count'] . ' شهر') : '—' ?></td>
                        <?php endif; ?>

                        <?php if ($hasDeductedTotal): ?>
                            <td><?= number_format($deductedVal, 2) ?> AED</td>
                            <td style="color:<?= $remainingVal > 0 ? '#dc2626' : '#16a34a' ?>">
                                <?= number_format($remainingVal, 2) ?> AED
                            </td>
                            <td>
                                <div class="progress-wrap">
                                    <div class="progress-bar"><span style="width:<?= (int)$percentPaid ?>%"></span></div>
                                    <small><?= (int)$percentPaid ?>%</small>
                                </div>
                            </td>
                        <?php endif; ?>

                        <?php if ($hasNextDeductionMonth): ?>
                            <td><?= h((string)($r['next_deduction_month'] ?? '—')) ?></td>
                        <?php endif; ?>

                        <td>
                            <span class="badge <?= $isPaid ? 'ok' : 'warn' ?>">
                                <?= h($statusLabel) ?>
                            </span>
                        </td>

                        <td><?= h((string)($r['reason'] ?? '')) ?></td>

                        <?php if ($canManage): ?>
                            <td>
                                <a class="btn secondary" href="advances.php?edit=<?= (int)$r['id'] ?>">تعديل</a>
                            </td>
                        <?php endif; ?>

                        <?php if ($canManage): ?>
                            <td>
                                <form method="post" style="margin:0" onsubmit="return confirm('هل أنت متأكد من حذف هذه السلفة؟');">
                                    <input type="hidden" name="action" value="delete">
                                    <input type="hidden" name="advance_id" value="<?= (int)$r['id'] ?>">
                                    <button class="btn secondary" type="submit">حذف</button>
                                </form>
                            </td>
                        <?php endif; ?>

                        <?php if ($canManage && $hasStatus): ?>
                            <td>
                                <?php if (!$isPaid): ?>
                                    <form method="post" style="margin:0">
                                        <input type="hidden" name="action" value="mark_paid">
                                        <input type="hidden" name="advance_id" value="<?= (int)$r['id'] ?>">
                                        <button class="btn secondary" type="submit">تعليم كمسددة</button>
                                    </form>
                                <?php else: ?>
                                    —
                                <?php endif; ?>
                            </td>
                        <?php endif; ?>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
        </tbody>
    </table>
</div>
<script>
document.addEventListener('DOMContentLoaded', function () {
    const employeeEl = document.getElementById('employee_id');
    const amountEl   = document.querySelector('[name="amount"]');
    const basicEl    = document.querySelector('[name="basic_salary"]');

    const previewWrap      = document.getElementById('salaryPreview');
    const previewEmployee  = document.getElementById('pv_employee');
    const previewBasic     = document.getElementById('pv_basic');
    const previewHousing   = document.getElementById('pv_housing');
    const previewTransport = document.getElementById('pv_transport');
    const previewOther     = document.getElementById('pv_other');
    const previewGross     = document.getElementById('pv_gross');
    const previewNet       = document.getElementById('pv_net_after');

    function money(val) {
        return parseFloat(val || 0).toFixed(2) + ' AED';
    }

    function updateEmployeeSalaryPreview() {
        if (!employeeEl) return;

        const option = employeeEl.options[employeeEl.selectedIndex];
        if (!option || !option.value) {
            if (previewWrap) previewWrap.classList.remove('active');
            return;
        }

        const empName   = option.textContent.trim();
        const basic     = parseFloat(option.dataset.basic || '0');
        const housing   = parseFloat(option.dataset.housing || '0');
        const transport = parseFloat(option.dataset.transport || '0');
        const other     = parseFloat(option.dataset.other || '0');
        const gross     = parseFloat(option.dataset.gross || '0');
        const amount    = amountEl ? parseFloat(amountEl.value || '0') : 0;
        const netAfter  = gross - amount;

        if (basicEl && (!basicEl.value || parseFloat(basicEl.value || '0') <= 0)) {
            basicEl.value = basic.toFixed(2);
        }

        if (previewWrap) previewWrap.classList.add('active');
        if (previewEmployee)  previewEmployee.textContent  = empName;
        if (previewBasic)     previewBasic.textContent     = money(basic);
        if (previewHousing)   previewHousing.textContent   = money(housing);
        if (previewTransport) previewTransport.textContent = money(transport);
        if (previewOther)     previewOther.textContent     = money(other);
        if (previewGross)     previewGross.textContent     = money(gross);
        if (previewNet)       previewNet.textContent       = money(netAfter);
    }

    if (employeeEl) employeeEl.addEventListener('change', updateEmployeeSalaryPreview);
    if (amountEl) amountEl.addEventListener('input', updateEmployeeSalaryPreview);

    updateEmployeeSalaryPreview();
});
</script>
<?php finance_shell_end(); ?>