<?php
require_once __DIR__ . '/../includes/bootstrap.php';

$id = (int)($_GET['id'] ?? 0);

$stmt = db()->prepare("SELECT * FROM payroll_periods WHERE id = ? LIMIT 1");
$stmt->execute([$id]);
$period = $stmt->fetch();

if (!$period) {
    redirect('payroll/index.php');
}

$startDate = $period['start_date'];
$endDate   = $period['end_date'];

/*
 |------------------------------------------------------------
 | IMPORTANTE:
 | Tu tabla employees no tiene first_name / last_name.
 | Se usa full_name, como ya aparece en otros módulos.
 |------------------------------------------------------------
*/
$employees = db()->query("
    SELECT *
    FROM employees
    WHERE status = 'active'
    ORDER BY full_name
")->fetchAll();

/*
 |------------------------------------------------------------
 | Borra la nómina previa del período y la recalcula completa
 |------------------------------------------------------------
*/
db()->prepare("DELETE FROM payroll_items WHERE period_id = ?")->execute([$id]);

foreach ($employees as $emp) {
    $employeeId = (int)$emp['id'];
    $baseSalary = isset($emp['salary']) ? (float)$emp['salary'] : 0;

    /*
     |--------------------------------------------------------
     | Extras del período
     |--------------------------------------------------------
     | Si no tenés aún la tabla payroll_extras, dejá 0.
     */
    $extras = 0;
    try {
        $stmtExtras = db()->prepare("
            SELECT COALESCE(SUM(amount),0)
            FROM payroll_extras
            WHERE employee_id = ?
              AND extra_date BETWEEN ? AND ?
              AND status = 'Activo'
        ");
        $stmtExtras->execute([$employeeId, $startDate, $endDate]);
        $extras = (float)$stmtExtras->fetchColumn();
    } catch (Exception $e) {
        $extras = 0;
    }

    /*
     |--------------------------------------------------------
     | Descuentos del período
     |--------------------------------------------------------
     | Si no tenés aún la tabla payroll_discounts, dejá 0.
     */
    $discounts = 0;
    try {
        $stmtDiscounts = db()->prepare("
            SELECT COALESCE(SUM(amount),0)
            FROM payroll_discounts
            WHERE employee_id = ?
              AND discount_date BETWEEN ? AND ?
              AND status = 'Activo'
        ");
        $stmtDiscounts->execute([$employeeId, $startDate, $endDate]);
        $discounts = (float)$stmtDiscounts->fetchColumn();
    } catch (Exception $e) {
        $discounts = 0;
    }

    /*
     |--------------------------------------------------------
     | Adelantos del período
     |--------------------------------------------------------
     */
    $stmtAdv = db()->prepare("
        SELECT COALESCE(SUM(amount),0)
        FROM salary_advances
        WHERE employee_id = ?
          AND advance_date BETWEEN ? AND ?
          AND status = 'Activo'
    ");
    $stmtAdv->execute([$employeeId, $startDate, $endDate]);
    $advances = (float)$stmtAdv->fetchColumn();

    /*
     |--------------------------------------------------------
     | Neto = base + extras - descuentos - adelantos
     |--------------------------------------------------------
     */
    $netPay = $baseSalary + $extras - $discounts - $advances;

    if ($netPay < 0) {
        $netPay = 0;
    }

    $stmtInsert = db()->prepare("
        INSERT INTO payroll_items
        (period_id, employee_id, base_salary, extras, discounts, advances, net_pay, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
    ");
    $stmtInsert->execute([
        $id,
        $employeeId,
        $baseSalary,
        $extras,
        $discounts,
        $advances,
        $netPay
    ]);
}

db()->prepare("UPDATE payroll_periods SET status = 'Generado' WHERE id = ?")->execute([$id]);

redirect('payroll/view.php?id=' . $id);