<?php
/**
 * Bug Condition Fix Verification Test
 *
 * Validates: Requirements 2.1, 2.2
 *
 * PURPOSE: Verify that the fixed CURDATE()-based queries (as now used in
 * portal/payschoolfees.php) correctly return a session/term row on the last
 * day of the session/term, at any time of day.
 *
 * EXPECTED OUTCOME: Both assertions PASS — confirms the fix works.
 *
 * Run via CLI: php portal/tests/test_bug_condition_fixed.php
 */

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

if (!isset($con) || mysqli_connect_errno()) {
    echo "FATAL: Could not connect to database.\n";
    exit(1);
}

$pass = 0;
$fail = 0;

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

$sessionName = 'TEST_SESSION_FIXED';
$termName = 'TEST_TERM_FIXED';

// Cleanup any leftover rows
$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 with end_date = CURDATE() (last day scenario)
$ins = $con->prepare("INSERT INTO school_sessions (session_name, start_date, end_date)
                      VALUES (?, CURDATE() - INTERVAL 7 DAY, CURDATE())");
$ins->bind_param('s', $sessionName);
$ins->execute();
$sessionId = $con->insert_id;
$ins->close();

// Insert term linked to that session
$ins = $con->prepare("INSERT INTO school_terms (term_name, session_id, start_date, end_date)
                      VALUES (?, ?, CURDATE() - INTERVAL 7 DAY, CURDATE())");
$ins->bind_param('si', $termName, $sessionId);
$ins->execute();
$termId = $con->insert_id;
$ins->close();

$nowRow = $con->query("SELECT NOW() AS now_val, CURDATE() AS curdate_val")->fetch_assoc();

echo "\n=== Bug Condition Fix Verification Test ===\n";
echo "MySQL NOW()    = {$nowRow['now_val']}\n";
echo "MySQL CURDATE()= {$nowRow['curdate_val']}\n";
echo "end_date = CURDATE() (last day of session/term)\n\n";

// Test 1: Fixed session query (CURDATE())
$stmt = $con->prepare("SELECT id, session_name FROM school_sessions
                       WHERE start_date <= CURDATE() AND end_date >= CURDATE()
                       AND session_name = ? LIMIT 1");
$stmt->bind_param('s', $sessionName);
$stmt->execute();
$row = $stmt->get_result()->fetch_assoc();
$stmt->close();

assert_test(
    "Fixed session query (CURDATE()) returns row when end_date = CURDATE()",
    $row !== null,
    "Session not returned — fix may not have been applied correctly."
);

// Test 2: Fixed term query (CURDATE())
$stmt = $con->prepare("SELECT id, term_name FROM school_terms
                       WHERE session_id = ? AND start_date <= CURDATE() AND end_date >= CURDATE()
                       AND id = ? LIMIT 1");
$stmt->bind_param('ii', $sessionId, $termId);
$stmt->execute();
$row = $stmt->get_result()->fetch_assoc();
$stmt->close();

assert_test(
    "Fixed term query (CURDATE()) returns row when end_date = CURDATE()",
    $row !== null,
    "Term not returned — fix may not have been applied correctly."
);

// 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";

echo "\n=== Results: $pass passed, $fail failed ===\n";
if ($fail > 0) {
    echo "\nFAIL — Fixed queries did not return the expected rows on the last day.\n";
    exit(1);
} else {
    echo "\nPASS — Fixed CURDATE() queries correctly include the full last day.\n";
    exit(0);
}
