<?php
session_start();

if (empty($_SESSION['branch']) || $_SESSION['branch'] == '') {
    header("Location: index.php");
    die();
}

require_once 'system/conn.php';
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $dateObj = new DateTime('first day of last month');
    $month   = $dateObj->format('m');
    $year    = $dateObj->format('Y');
    $branch  = $_SESSION['branch'];
    $displayDate = $dateObj->format('F Y');

    // CONCURRENCY LOCK - Prevents collision with generation
    $stmtLock = $conn->query("SELECT GET_LOCK('utc_salary_generate', 10)"); 
    if ($stmtLock->fetchColumn() != 1) {
        throw new Exception("System Busy: Another salary process is running.");
    }

    $conn->beginTransaction();

    $params = [':month' => $month, ':year' => $year, ':branch' => $branch];

    // 1. LEDGER CORRECTION: Find and reverse applied deductions
    // ✅ FIX: We fetch 'manual_balance' because that is the actual deduction amount
    $findAdv = $conn->prepare("SELECT e_id, manual_balance FROM `advance` WHERE `month` = :month AND `year` = :year AND `branch` = :branch AND `manual_balance` > 0");
    $findAdv->execute($params);
    $advancesToDelete = $findAdv->fetchAll(PDO::FETCH_ASSOC);

    foreach ($advancesToDelete as $adv) {
        $emp_id = $adv['e_id'];
        $amountToRestore = $adv['manual_balance']; // Restoring the specific deduction applied

        // Update the current balance for the employee by adding back the deduction
        $updateLedger = $conn->prepare("
            UPDATE `advance` 
            SET `remaining_amount` = `remaining_amount` + :amt 
            WHERE `e_id` = :eid AND `branch` = :br 
            ORDER BY `date` DESC, `adv_id` DESC 
            LIMIT 1
        ");
        $updateLedger->execute([':amt' => $amountToRestore, ':eid' => $emp_id, ':br' => $branch]);
    }

    // 2. PERFORM ATOMIC DELETIONS
    $totalDeleted = 0;

    // Delete Salary Attendance
    $stmt1 = $conn->prepare("DELETE FROM `salary_attendance` WHERE `month` = :month AND `year` = :year AND `branch` = :branch");
    $stmt1->execute($params);
    $totalDeleted += $stmt1->rowCount();

    // Delete Employee Salary
    $stmt2 = $conn->prepare("DELETE FROM `emp_salary` WHERE `month` = :month AND `year` = :year AND `branch` = :branch");
    $stmt2->execute($params);
    $totalDeleted += $stmt2->rowCount();

    // Delete OT Details (Integrated generation pass)
    $stmt3 = $conn->prepare("DELETE FROM `ot_details` WHERE `month` = :month AND `year` = :year AND `branch` = :branch");
    $stmt3->execute($params);
    $totalDeleted += $stmt3->rowCount();

    // Delete the Advance deduction records
    $stmt4 = $conn->prepare("DELETE FROM `advance` WHERE `month` = :month AND `year` = :year AND `branch` = :branch AND `manual_balance` > 0");
    $stmt4->execute($params);
    $totalDeleted += $stmt4->rowCount();

    $conn->commit();
    $conn->query("SELECT RELEASE_LOCK('utc_salary_generate')");

    if ($totalDeleted > 0) {
        $_SESSION['delete'] = "Success: Deleted $totalDeleted records and restored ledger balances for $displayDate.";
    } else {
        $_SESSION['s_sa'] = "Notice: No data found for $displayDate.";
    }

    header("Location: gross-sheet.php");
    exit;

} catch (Exception $e) {
    if (isset($conn) && $conn->inTransaction()) $conn->rollBack();
    if (isset($conn)) {
        try { $conn->query("SELECT RELEASE_LOCK('utc_salary_generate')"); } catch (Throwable $t) {}
    }
    $_SESSION['s_sa'] = "Error: " . $e->getMessage();
    header("Location: gross-sheet.php");
    exit;
}