<?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
// =============================================================
function getArchiveStats($conn) {
    // -------------------------------------------------------------
    // FIX: Calculate Active Month based on REAL DATE, not DB Data
    // -------------------------------------------------------------
    // If today is Jan 2026, "Last Month" is Dec 2025.
    // We treat Dec 2025 as the "Active/Working" month.
    // Anything strictly older than Dec 2025 will be archived.
    
    $dateObj = new DateTime('first day of last month'); 
    $keepYear = $dateObj->format('Y');
    $keepMonth = $dateObj->format('m');
    
    // Format: 202512 (Integer for comparison)
    $maxVal = $keepYear . $keepMonth; 
    
    $stats = [
        'maxVal' => $maxVal,
        'label' => $dateObj->format('F Y'), // e.g., "December 2025"
        'counts' => ['payment' => 0, 'rev' => 0],
        'total' => 0,
        'canArchive' => false
    ];

    // Define Archive Scope (Strictly Older than Dec 2025)
    $where = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
    
    // Check Payment Table
    $t1 = $conn->prepare("SELECT COUNT(*) FROM payment_tbl $where"); 
    $t1->execute([$maxVal]);
    $stats['counts']['payment'] = (int)$t1->fetchColumn();
    
    // Check Reverse Payment Table
    $t2 = $conn->prepare("SELECT COUNT(*) FROM rev_payment_tbl $where"); 
    $t2->execute([$maxVal]);
    $stats['counts']['rev'] = (int)$t2->fetchColumn();
    
    $stats['total'] = array_sum($stats['counts']);
    $stats['canArchive'] = ($stats['total'] > 0);

    return $stats;
}

$stats = getArchiveStats($conn);
$message = "";
$msgType = "";

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

        $conn->beginTransaction();
        
        $maxVal = $stats['maxVal'];
        $whereSource = "WHERE CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED) < ?";
        $paramsSource = [$maxVal];

        // --- TABLE 1: PAYMENT_TBL ---
        // 1. Local Min for Scoped Verification
        $stmtMin = $conn->prepare("SELECT MIN(CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED)) FROM payment_tbl $whereSource");
        $stmtMin->execute($paramsSource);
        $minValPay = $stmtMin->fetchColumn();

        if ($minValPay) {
            $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 = [$minValPay, $maxVal];

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

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

            // 4. Insert (Explicit ID and Columns)
            $cols = "id, payment_for, payment_type, payment_id, narration, branch, payment_date, month, year, updated_date";
            $conn->prepare("INSERT INTO prv_payment_tbl ($cols) SELECT $cols FROM payment_tbl $whereSource")->execute($paramsSource);

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

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

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

        // --- TABLE 2: REV_PAYMENT_TBL ---
        $stmtMin = $conn->prepare("SELECT MIN(CAST(CONCAT(TRIM(year), LPAD(TRIM(month), 2, '0')) AS UNSIGNED)) FROM rev_payment_tbl $whereSource");
        $stmtMin->execute($paramsSource);
        $minValRev = $stmtMin->fetchColumn();

        if ($minValRev) {
            $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 = [$minValRev, $maxVal];

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

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

            // 4. Insert (Explicit ID and Columns)
            $cols = "id, rev_from, emp_id, return_date, reason, branch, month, year, updated_date";
            $conn->prepare("INSERT INTO prv_rev_payment ($cols) SELECT $cols FROM rev_payment_tbl $whereSource")->execute($paramsSource);

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

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

            // 6. Delete & Verify
            $conn->prepare("DELETE FROM rev_payment_tbl $whereSource")->execute($paramsSource);

            $stmtCheck = $conn->prepare("SELECT COUNT(*) FROM rev_payment_tbl $whereSource");
            $stmtCheck->execute($paramsSource);
            if ($stmtCheck->fetchColumn() > 0) throw new Exception("Rev Payment Delete Failed: Source table not empty.");
        }

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

        $message = "<b>SUCCESS:</b> Payment Data Archived. 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_payment_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 Payment Archival</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 Payment 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-info shadow-sm">
                        <div class="card-body p-5 text-center">
                            
                            <div class="mb-4">
                                <i class="bx bx-wallet text-info" style="font-size: 5rem;"></i>
                            </div>
                            
                            <h3 class="fw-bold mb-2">Safe Payment Archival Tool</h3>
                            <p class="text-muted mb-4">
                                Moves finalized payment 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 (System Date)</span>
                                    <h2 class="text-primary fw-bold my-2"><?= $stats['label'] ?></h2>
                                    <span class="badge bg-success">Active - Kept</span>
                                </div>
                                <div class="d-block"></div>
                                <div class="mb-4 text-muted small">
                                    Records strictly older than <b><?= $stats['label'] ?></b>:
                                    <br>
                                    Found <b><?= $stats['counts']['payment'] ?></b> Payment Records, 
                                    <b><?= $stats['counts']['rev'] ?></b> Reversal Records
                                </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-info btn-lg px-5 shadow text-white">
                                        <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> Payment Records are 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>