<?php
// Include your database connection
require_once 'system/conn.php';

try {
    // ---------------------------------------------------
    // 1. DYNAMICALLY CALCULATE LAST MONTH & YEAR
    // ---------------------------------------------------
    // This targets the salary month that just passed (e.g., if today is Jan 30, this targets Dec 2025)
    $prev_date_ts = strtotime("first day of last month");
    $target_month = date('m', $prev_date_ts); 
    $target_year  = date('Y', $prev_date_ts);
    
    // Current date for the 'd_o_l' and 'entry_date' fields
    $current_date = date('Y-m-d'); 

    echo "<h3>System Check</h3>";
    echo "Current Date: " . date('d-m-Y') . "<br>";
    echo "Targeting Salary Data: <strong>" . $target_month . " / " . $target_year . "</strong><br><hr>";

    // Start Transaction to ensure both things happen or neither happens
    $conn->beginTransaction();

    // ---------------------------------------------------
    // 2. STEP 1: INSERT INTO left_employee
    // ---------------------------------------------------
    // We select the missing people and insert them directly into the history table
    $sql_insert = "
        INSERT INTO left_employee (emp_id, d_o_l, entry_date, branch, status)
        SELECT e.e_id, :dol, :entry, e.branch, 1
        FROM emp_details e
        LEFT JOIN salary_attendance s 
            ON e.e_id = s.emp_id 
            AND e.branch = s.branch 
            AND s.month = :month 
            AND s.year = :year
        WHERE s.id IS NULL 
        AND e.status = 1
    ";

    $stmt_insert = $conn->prepare($sql_insert);
    $stmt_insert->execute([
        ':dol'   => $current_date,
        ':entry' => $current_date,
        ':month' => $target_month,
        ':year'  => $target_year
    ]);
    
    $inserted_count = $stmt_insert->rowCount();

    // ---------------------------------------------------
    // 3. STEP 2: UPDATE emp_details (Mark as Left)
    // ---------------------------------------------------
    // Now that they are archived, we mark them as inactive in the main table
    $sql_update = "
        UPDATE emp_details e
        LEFT JOIN salary_attendance s 
            ON e.e_id = s.emp_id 
            AND e.branch = s.branch 
            AND s.month = :month 
            AND s.year = :year
        SET e.status = 0
        WHERE s.id IS NULL 
        AND e.status = 1
    ";

    $stmt_update = $conn->prepare($sql_update);
    $stmt_update->execute([
        ':month' => $target_month,
        ':year'  => $target_year
    ]);

    $updated_count = $stmt_update->rowCount();

    // Commit changes
    $conn->commit();

    // ---------------------------------------------------
    // 4. REPORTING
    // ---------------------------------------------------
    if($updated_count > 0) {
        echo "<h2 style='color:green;'>Operation Successful!</h2>";
        echo "<ul>";
        echo "<li><strong>$inserted_count</strong> records added to <code>left_employee</code> table.</li>";
        echo "<li><strong>$updated_count</strong> employees marked as Inactive (Status 0) in <code>emp_details</code>.</li>";
        echo "</ul>";
        echo "<p>These employees were active but missing from the Salary Sheet for <strong>$target_month/$target_year</strong>.</p>";
    } else {
        echo "<h2 style='color:blue;'>No Changes Needed</h2>";
        echo "All currently active employees have a valid salary record for $target_month/$target_year.";
    }

} catch(PDOException $e) {
    // If error, roll back changes so we don't have half-finished data
    $conn->rollBack();
    echo "<h3 style='color:red;'>Error</h3>";
    echo "Database Error: " . $e->getMessage();
}
?>