<?php
/**
 * Bug Condition Exploration Test
 *
 * Validates: Requirements 1.1, 1.2
 *
 * PURPOSE: Demonstrate the bug in portal/payschoolfees.php where the original
 * NOW()-based queries fail to return a session/term row on the last day of the
 * session/term (after midnight), because MySQL casts the DATE end_date column
 * to 'YYYY-MM-DD 00:00:00' when compared against the DATETIME returned by NOW().
 *
 * EXPECTED OUTCOME: This test FAILS on unfixed code (any time after midnight on
 * the last day). Failure confirms the bug exists.
 *
 * Run via CLI: php portal/tests/test_bug_condition.php
 */

// ── Bootstrap ────────────────────────────────────────────────────────────────
$root = dirname(__DIR__); // portal/
require_once $root . '/includes/dbconnection.php';

// $con is the MySQLi connection provided by dbconnection.php
if (!isset($con) || mysqli_connect_errno()) {
    echo "FATAL: Could not connect to database.\n";
    exit(1);
}

// ── Helpers ──────────────────────────────────────────────────────────────────
$pass = 0;
$fail = 0;
$counterexamples = [];

function assert_test(string $label, bool $condition, string $counterexample = ''): void
{
    global $pass, $fail, $counterexamples;
    if ($condition) {
        echo "[PASS] $label\n";
        $pass++;
    } else {
        echo "[FAIL] $label\n";
        if ($counterexample !== '') {
            echo "       Counterexample: $counterexample\n";
        }
        $fail++;
        $counterexamples[] = $counterexample ?: $label;
    }
}

// ── Setup: insert test rows ───────────────────────────────────────────────────
$sessionName = 'TEST_SESSION_BUG';
$termName = 'TEST_TERM_BUG';

// Clean up any leftover rows from a previous run
$con->query("DELETE st FROM school_terms st
             INNER JOIN school_sessions ss ON st.session_id = ss.id
             WHERE ss.session_name = '$sessionName'");
$con->query("DELETE FROM school_sessions WHERE session_name = '$sessionName'");

// Insert session: start_date = CURDATE() - 7 days, end_date = CURDATE()
$insertSession = $con->prepare(
    "INSERT INTO school_sessions (session_name, start_date, end_date)
     VALUES (?, CURDATE() - INTERVAL 7 DAY, CURDATE())"
);
if (!$insertSession) {
    echo "FATAL: Could not prepare session insert: " . $con->error . "\n";
    exit(1);
}
$insertSession->bind_param('s', $sessionName);
$insertSession->execute();
$sessionId = $con->insert_id;
$insertSession->close();

if (!$sessionId) {
    echo "FATAL: Failed to insert test session row.\n";
    exit(1);
}

// Insert term linked to that session with the same date range
$insertTerm = $con->prepare(
    "INSERT INTO school_terms (term_name, session_id, start_date, end_date)
     VALUES (?, ?, CURDATE() - INTERVAL 7 DAY, CURDATE())"
);
if (!$insertTerm) {
    echo "FATAL: Could not prepare term insert: " . $con->error . "\n";
    exit(1);
}
$insertTerm->bind_param('si', $termName, $sessionId);
$insertTerm->execute();
$termId = $con->insert_id;
$insertTerm->close();

if (!$termId) {
    echo "FATAL: Failed to insert test term row.\n";
    exit(1);
}

echo "\n=== Bug Condition Exploration Test ===\n";
echo "Test session inserted: id=$sessionId, session_name='$sessionName'\n";
echo "Test term inserted:    id=$termId,    term_name='$termName'\n";
echo "end_date for both = CURDATE() (today is the last day)\n\n";

// ── Show current MySQL NOW() vs CURDATE() for context ────────────────────────
$nowResult = $con->query("SELECT NOW() AS now_val, CURDATE() AS curdate_val");
$nowRow = $nowResult->fetch_assoc();
echo "MySQL NOW()    = " . $nowRow['now_val'] . "\n";
echo "MySQL CURDATE()= " . $nowRow['curdate_val'] . "\n\n";

// ── Test 1: Original (unfixed) session query ──────────────────────────────────
// Uses NOW() — will fail after midnight on the last day because MySQL casts
// the DATE end_date to 'YYYY-MM-DD 00:00:00', which is < NOW() after midnight.
$sessionQuery = "SELECT id, session_name FROM school_sessions 
                 WHERE start_date <= CURDATE() AND end_date >= CURDATE() 
                 AND session_name = ? LIMIT 1";
$stmtSession = $con->prepare($sessionQuery);
$stmtSession->bind_param('s', $sessionName);
$stmtSession->execute();
$sessionResult = $stmtSession->get_result();
$sessionRow = $sessionResult ? $sessionResult->fetch_assoc() : null;
$stmtSession->close();

$sessionFound = ($sessionRow !== null);
assert_test(
    "Original session query (NOW()) returns row when end_date = CURDATE()",
    $sessionFound,
    "Session with end_date=CURDATE() NOT returned by 'end_date >= CURDATE()' because " .
    "MySQL casts DATE to '" . $nowRow['curdate_val'] . " 00:00:00', which is < NOW()=" .
    $nowRow['now_val'] . " after midnight. " .
    "Requirements 1.1: system fails to find current session on last day after midnight."
);

// ── Test 2: Original (unfixed) term query ─────────────────────────────────────
$termQuery = "SELECT id, term_name FROM school_terms 
              WHERE session_id = ? AND start_date <= CURDATE() AND end_date >= CURDATE() 
              AND term_name = ? LIMIT 1";
$stmtTerm = $con->prepare($termQuery);
$stmtTerm->bind_param('is', $sessionId, $termName);
$stmtTerm->execute();
$termResult = $stmtTerm->get_result();
$termRow = $termResult ? $termResult->fetch_assoc() : null;
$stmtTerm->close();

$termFound = ($termRow !== null);
assert_test(
    "Original term query (NOW()) returns row when end_date = CURDATE()",
    $termFound,
    "Term with end_date=CURDATE() NOT returned by 'end_date >= CURDATE()' because " .
    "MySQL casts DATE to '" . $nowRow['curdate_val'] . " 00:00:00', which is < NOW()=" .
    $nowRow['now_val'] . " after midnight. " .
    "Requirements 1.2: system fails to find current term on last day after midnight."
);

// ── Cleanup ───────────────────────────────────────────────────────────────────
$con->query("DELETE FROM school_terms WHERE id = $termId");
$con->query("DELETE FROM school_sessions WHERE id = $sessionId");
echo "\nTest rows cleaned up.\n";

// ── Summary ───────────────────────────────────────────────────────────────────
echo "\n=== Results: $pass passed, $fail failed ===\n";

if ($fail > 0) {
    echo "\nFAIL — Bug confirmed. Counterexample(s):\n";
    foreach ($counterexamples as $i => $ce) {
        echo "  " . ($i + 1) . ". $ce\n";
    }
    echo "\nRoot cause: MySQL implicitly casts DATE end_date to 'YYYY-MM-DD 00:00:00'\n";
    echo "when compared against NOW() (DATETIME). After midnight on the last day,\n";
    echo "end_date 00:00:00 < NOW(), so 'end_date >= CURDATE()' evaluates FALSE.\n";
    echo "Fix: replace NOW() with CURDATE() in both queries in payschoolfees.php.\n";
    exit(1);
} else {
    echo "\nPASS — All assertions passed (test running at exactly midnight, or fix already applied).\n";
    exit(0);
}
