<?php
session_start();
if(empty($_SESSION['branch']) || $_SESSION['branch'] == ''){
    header("Location: index.php");
    die();
}
require_once 'system/conn.php';

// Enable Exception Handling
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// =============================================================
// 1. HELPER: DETECT DATA & COUNTS (UI ONLY)
// =============================================================
function getArchiveStats($conn) {
    // 1. Detect Latest "Active" Month (The Upper Bound for ALL tables)
    $stmtMax = $conn->query("SELECT MAX(CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED)) FROM emp_salary");
    $maxVal = $stmtMax->fetchColumn();

    $stats = [
        'maxVal' => $maxVal,
        'label' => "No Data Found",
        'counts' => ['salary' => 0, 'ot' => 0, 'att' => 0, 'sal_att' => 0],
        'total' => 0,
        'canArchive' => false
    ];

    if ($maxVal) {
        $keepYear = substr($maxVal, 0, 4);
        $keepMonth = substr($maxVal, 4, 2);
        $stats['label'] = date('F Y', mktime(0, 0, 0, $keepMonth, 10, $keepYear));
        
        // Count Eligible Rows (Strictly older than Max)
        $where = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
        
        $t1 = $conn->prepare("SELECT COUNT(*) FROM emp_salary $where"); $t1->execute([$maxVal]);
        $stats['counts']['salary'] = (int)$t1->fetchColumn();
        
        $t2 = $conn->prepare("SELECT COUNT(*) FROM ot_details $where"); $t2->execute([$maxVal]);
        $stats['counts']['ot'] = (int)$t2->fetchColumn();
        
        $t3 = $conn->prepare("SELECT COUNT(*) FROM attendance $where"); $t3->execute([$maxVal]);
        $stats['counts']['att'] = (int)$t3->fetchColumn();
        
        $t4 = $conn->prepare("SELECT COUNT(*) FROM salary_attendance $where"); $t4->execute([$maxVal]);
        $stats['counts']['sal_att'] = (int)$t4->fetchColumn();
        
        $stats['total'] = array_sum($stats['counts']);
        $stats['canArchive'] = ($stats['total'] > 0);
    }

    return $stats;
}

// Initial Load Stats
$stats = getArchiveStats($conn);
$message = "";
$msgType = "";

// =============================================================
// 2. PROCESS ARCHIVE REQUEST (FULL 7-STEP SAFETY)
// =============================================================
if (isset($_POST['archive_btn']) && $stats['canArchive']) {
    try {
        // --- A. CONCURRENCY LOCK ---
        $stmtLock = $conn->query("SELECT GET_LOCK('utc_salary_archive_lock', 10)");
        if ($stmtLock->fetchColumn() != 1) {
            throw new Exception("Another archive process is currently running. Please try again.");
        }

        $conn->beginTransaction();
        
        $maxVal = $stats['maxVal'];
        // The Global Rule: Move everything strictly older than Max
        $whereSource = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
        $paramsSource = [$maxVal];

        // ------------------------------------------------------------------
        // TABLE 1: EMP_SALARY
        // ------------------------------------------------------------------
        // 1. Get Local Min (Oldest Record) for SCOPED verification
        $stmtMin = $conn->prepare("SELECT MIN(CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED)) FROM emp_salary $whereSource");
        $stmtMin->execute($paramsSource);
        $minValSal = $stmtMin->fetchColumn();

        if ($minValSal) {
            // Define Scoped Verification Range: [LocalMin, GlobalMax)
            $whereDest = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) >= ? 
                            AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
            $paramsDest = [$minValSal, $maxVal];

            // 2. Count Expected
            $stmt = $conn->prepare("SELECT COUNT(*) FROM emp_salary $whereSource");
            $stmt->execute($paramsSource);
            $expected = (int)$stmt->fetchColumn();

            // 3. Pre-Count Archive
            $stmtPre = $conn->prepare("SELECT COUNT(*) FROM prv_emp_salary $whereDest");
            $stmtPre->execute($paramsDest);
            $countPre = (int)$stmtPre->fetchColumn();

            // 4. Insert
            $sqlIns = "INSERT INTO prv_emp_salary (
                name, designation, ac_no, emp_id, month, year, attendance, t_at, basic_pay, hra, convyance, medical, gross_pay, pf, esic, advance, tax, spc_allow, deduction, net_pay, block, cheque, branch, payment_status
            ) SELECT name, designation, ac_no, emp_id, month, year, attendance, t_at, basic_pay, hra, convyance, medical, gross_pay, pf, esic, advance, tax, spc_allow, deduction, net_pay, block, cheque, branch, payment_status FROM emp_salary $whereSource";
            $conn->prepare($sqlIns)->execute($paramsSource);

            // 5. Post-Count & Verify Insert
            $stmtPost = $conn->prepare("SELECT COUNT(*) FROM prv_emp_salary $whereDest");
            $stmtPost->execute($paramsDest);
            $countPost = (int)$stmtPost->fetchColumn();

            if (($countPost - $countPre) !== $expected) throw new Exception("Salary Archive Mismatch: Expected $expected, Inserted " . ($countPost - $countPre));

            // 6. Delete
            $conn->prepare("DELETE FROM emp_salary $whereSource")->execute($paramsSource);
            
            // 7. Verify Delete (Ensure Source Empty)
            $stmtCheck = $conn->prepare("SELECT COUNT(*) FROM emp_salary $whereSource");
            $stmtCheck->execute($paramsSource);
            if ($stmtCheck->fetchColumn() > 0) throw new Exception("Salary Delete Failed: Source table not empty.");
        }

        // ------------------------------------------------------------------
        // TABLE 2: OT_DETAILS (Localized Min + Full Verify)
        // ------------------------------------------------------------------
        $stmtMin = $conn->prepare("SELECT MIN(CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED)) FROM ot_details $whereSource");
        $stmtMin->execute($paramsSource);
        $minValOT = $stmtMin->fetchColumn();

        if ($minValOT) {
            $whereDest = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) >= ? 
                            AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
            $paramsDest = [$minValOT, $maxVal];

            $stmt = $conn->prepare("SELECT COUNT(*) FROM ot_details $whereSource");
            $stmt->execute($paramsSource);
            $expected = (int)$stmt->fetchColumn();

            $stmtPre = $conn->prepare("SELECT COUNT(*) FROM prv_ot_details $whereDest");
            $stmtPre->execute($paramsDest);
            $countPre = (int)$stmtPre->fetchColumn();

            $sqlIns = "INSERT INTO prv_ot_details (
                emp_name, Designation, ac_no, emp_id, ot_days, amount, month, year, branch, Hold, cheque, payment_status
            ) SELECT emp_name, Designation, ac_no, emp_id, ot_days, amount, month, year, branch, Hold, cheque, payment_status FROM ot_details $whereSource";
            $conn->prepare($sqlIns)->execute($paramsSource);

            $stmtPost = $conn->prepare("SELECT COUNT(*) FROM prv_ot_details $whereDest");
            $stmtPost->execute($paramsDest);
            $countPost = (int)$stmtPost->fetchColumn();

            if (($countPost - $countPre) !== $expected) throw new Exception("OT Archive Mismatch: Expected $expected, Inserted " . ($countPost - $countPre));

            $conn->prepare("DELETE FROM ot_details $whereSource")->execute($paramsSource);
            
            // VERIFY DELETE (ADDED)
            $stmtCheck = $conn->prepare("SELECT COUNT(*) FROM ot_details $whereSource");
            $stmtCheck->execute($paramsSource);
            if ($stmtCheck->fetchColumn() > 0) throw new Exception("OT Delete Failed: Source rows still exist.");
        }

        // ------------------------------------------------------------------
        // TABLE 3: ATTENDANCE (Localized Min + Full Verify)
        // ------------------------------------------------------------------
        $stmtMin = $conn->prepare("SELECT MIN(CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED)) FROM attendance $whereSource");
        $stmtMin->execute($paramsSource);
        $minValAtt = $stmtMin->fetchColumn();

        if ($minValAtt) {
            $whereDest = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) >= ? 
                            AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
            $paramsDest = [$minValAtt, $maxVal];

            $stmt = $conn->prepare("SELECT COUNT(*) FROM attendance $whereSource");
            $stmt->execute($paramsSource);
            $expected = (int)$stmt->fetchColumn();

            $stmtPre = $conn->prepare("SELECT COUNT(*) FROM prv_attendance $whereDest");
            $stmtPre->execute($paramsDest);
            $countPre = (int)$stmtPre->fetchColumn();

            $sqlIns = "INSERT INTO prv_attendance (
                emp_id, month, year, site, attendance, only_ot, block, `check`, advance_deduction, spc_allow, branch
            ) SELECT emp_id, month, year, site, attendance, only_ot, block, `check`, advance_deduction, spc_allow, branch FROM attendance $whereSource";
            $conn->prepare($sqlIns)->execute($paramsSource);

            $stmtPost = $conn->prepare("SELECT COUNT(*) FROM prv_attendance $whereDest");
            $stmtPost->execute($paramsDest);
            $countPost = (int)$stmtPost->fetchColumn();

            if (($countPost - $countPre) !== $expected) throw new Exception("Attendance Archive Mismatch: Expected $expected, Inserted " . ($countPost - $countPre));

            $conn->prepare("DELETE FROM attendance $whereSource")->execute($paramsSource);
            
            // VERIFY DELETE (ADDED)
            $stmtCheck = $conn->prepare("SELECT COUNT(*) FROM attendance $whereSource");
            $stmtCheck->execute($paramsSource);
            if ($stmtCheck->fetchColumn() > 0) throw new Exception("Attendance Delete Failed: Source rows still exist.");
        }

        // ------------------------------------------------------------------
        // TABLE 4: SALARY_ATTENDANCE (Localized Min + Full Verify)
        // ------------------------------------------------------------------
        $stmtMin = $conn->prepare("SELECT MIN(CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED)) FROM salary_attendance $whereSource");
        $stmtMin->execute($paramsSource);
        $minValSalAtt = $stmtMin->fetchColumn();

        if ($minValSalAtt) {
            $whereDest = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) >= ? 
                            AND CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
            $paramsDest = [$minValSalAtt, $maxVal];

            $stmt = $conn->prepare("SELECT COUNT(*) FROM salary_attendance $whereSource");
            $stmt->execute($paramsSource);
            $expected = (int)$stmt->fetchColumn();

            $stmtPre = $conn->prepare("SELECT COUNT(*) FROM prv_salary_attendance $whereDest");
            $stmtPre->execute($paramsDest);
            $countPre = (int)$stmtPre->fetchColumn();

            $sqlIns = "INSERT INTO prv_salary_attendance (
                emp_id, month, year, t_a, b_a, o_a, block, cheque, branch, maual_advance, spc_allow, details
            ) SELECT emp_id, month, year, t_a, b_a, o_a, block, cheque, branch, maual_advance, spc_allow, details FROM salary_attendance $whereSource";
            $conn->prepare($sqlIns)->execute($paramsSource);

            $stmtPost = $conn->prepare("SELECT COUNT(*) FROM prv_salary_attendance $whereDest");
            $stmtPost->execute($paramsDest);
            $countPost = (int)$stmtPost->fetchColumn();

            if (($countPost - $countPre) !== $expected) throw new Exception("Salary Attendance Archive Mismatch: Expected $expected, Inserted " . ($countPost - $countPre));

            $conn->prepare("DELETE FROM salary_attendance $whereSource")->execute($paramsSource);
            
            // VERIFY DELETE (ADDED)
            $stmtCheck = $conn->prepare("SELECT COUNT(*) FROM salary_attendance $whereSource");
            $stmtCheck->execute($paramsSource);
            if ($stmtCheck->fetchColumn() > 0) throw new Exception("Salary Attendance Delete Failed: Source rows still exist.");
        }

        // ------------------------------------------------------------------
        // COMMIT & CLEANUP
        // ------------------------------------------------------------------
        $conn->commit();
        $conn->query("SELECT RELEASE_LOCK('utc_salary_archive_lock')");

        $message = "<b>SUCCESS:</b> Data Integrity Verified. All records older than <b>{$stats['label']}</b> moved to history.";
        $msgType = "success";
        
        $stats = getArchiveStats($conn); // Refresh UI

    } catch (Exception $e) {
        $conn->rollBack();
        $conn->query("SELECT RELEASE_LOCK('utc_salary_archive_lock')");
        $message = "<b>ARCHIVE FAILED (Rolled Back):</b> " . $e->getMessage();
        $msgType = "danger";
    }
}
?>

<!doctype html>
<html lang="en" class="semi-dark">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Safe Archive Utility</title>
    <link href="assets/css/pace.min.css" rel="stylesheet" />
    <script src="assets/js/pace.min.js"></script>
    <link href="assets/plugins/perfect-scrollbar/css/perfect-scrollbar.css" rel="stylesheet" />
    <link href="assets/plugins/metismenu/css/metisMenu.min.css" rel="stylesheet" />
    <link href="assets/css/bootstrap.min.css" rel="stylesheet">
    <link href="assets/css/bootstrap-extended.css" rel="stylesheet">
    <link href="assets/css/style.css" rel="stylesheet">
    <link href="assets/css/icons.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.7.2/font/bootstrap-icons.css">
    <link href="assets/css/dark-theme.css" rel="stylesheet" />
    <link href="assets/css/semi-dark.css" rel="stylesheet" />
    <link href="assets/css/header-colors.css" rel="stylesheet" />
</head>
<body>
<div class="wrapper">
    <?php include 'system/header.php'; ?>
    <div class="page-content-wrapper">
        <div class="page-content">
            
            <div class="page-breadcrumb d-none d-sm-flex align-items-center mb-3">
                <div class="breadcrumb-title pe-3">System Maintenance</div>
                <div class="ps-3">
                    <nav aria-label="breadcrumb">
                        <ol class="breadcrumb mb-0 p-0">
                            <li class="breadcrumb-item"><a href="dashboard.php"><i class="bx bx-home-alt"></i></a></li>
                            <li class="breadcrumb-item active" aria-current="page">Safe Data Archival</li>
                        </ol>
                    </nav>
                </div>
            </div>
            <hr/>

            <?php if($message): ?>
            <div class="alert alert-<?= $msgType ?> border-0 bg-<?= $msgType ?> alert-dismissible fade show py-2">
                <div class="d-flex align-items-center">
                    <div class="font-35 text-white"><i class='bx <?= ($msgType=='success')?'bx-check-circle':'bx-error' ?>'></i></div>
                    <div class="ms-3">
                        <h6 class="mb-0 text-white"><?= ($msgType=='success')?'Archive Successful':'Verification Failed' ?></h6>
                        <div class="text-white"><?= $message ?></div>
                    </div>
                </div>
            </div>
            <?php endif; ?>

            <div class="row">
                <div class="col-xl-9 mx-auto">
                    <div class="card border-top border-0 border-4 border-primary shadow-sm">
                        <div class="card-body p-5 text-center">
                            
                            <div class="mb-4">
                                <i class="bx bx-shield-quarter text-primary" style="font-size: 5rem;"></i>
                            </div>
                            
                            <h3 class="fw-bold mb-2">Safe Data Archival Tool</h3>
                            <p class="text-muted mb-4">
                                Moves finalized records to history with <strong>Scoped Zero-Data-Loss Verification</strong>.
                            </p>

                            <?php if ($stats['canArchive']): ?>
                                <div class="p-3 bg-light border rounded mb-4 d-inline-block px-5">
                                    <span class="text-secondary d-block small text-uppercase">Current Active Month</span>
                                    <h2 class="text-primary fw-bold my-2"><?= $stats['label'] ?></h2>
                                    <span class="badge bg-success">Active</span>
                                </div>
                                <div class="d-block"></div>
                                <div class="mb-4 text-muted small">
                                    Found: 
                                    <b><?= $stats['counts']['salary'] ?></b> Salary, 
                                    <b><?= $stats['counts']['ot'] ?></b> OT, 
                                    <b><?= $stats['counts']['att'] ?></b> Attendance
                                </div>
                                <form method="post" onsubmit="return confirm('CONFIRM ARCHIVE:\n\n1. This will move ALL records older than <?= $stats['label'] ?>.\n2. Integrity checks will run per table.\n3. If any check fails, the process will auto-rollback.\n\nProceed?');">
                                    <button type="submit" name="archive_btn" class="btn btn-primary btn-lg px-5 shadow">
                                        <i class="bx bx-archive-in me-2"></i> Run Safe Archive
                                    </button>
                                </form>
                            <?php else: ?>
                                <div class="alert alert-success d-inline-block px-5">
                                    <i class="bx bx-check-circle me-1"></i> System is Clean & Up to Date
                                </div>
                                <div class="mt-2 text-muted">Active Month: <b><?= $stats['label'] ?></b></div>
                            <?php endif; ?>

                        </div>
                    </div>
                </div>
            </div>

        </div>
    </div>
    <?php include 'system/footer.php'; ?>
</div>
</body>
</html>