<?php
// Emergency Attendance Fix Tool
require_once 'config/database.php';

// Target student: MST. SONIA KHATUN
$target_student_name = 'MST. SONIA KHATUN';
$search_date = '2026-01-16';
$target_class_id = 14;

echo "<!DOCTYPE html>
<html>
<head>
    <title>Emergency Attendance Fix</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .success { color: green; font-weight: bold; }
        .error { color: red; font-weight: bold; }
        .warning { color: orange; font-weight: bold; }
        .info { color: blue; }
        pre { background: #f5f5f5; padding: 10px; border-radius: 5px; }
        .fix-button { background: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 5px; cursor: pointer; }
        .fix-button:hover { background: #0056b3; }
    </style>
</head>
<body>
<h1>🔧 Emergency Attendance Fix Tool</h1>
<h2>Target: $target_student_name on $search_date</h2>";

try {
    // Step 1: Find the exact student
    echo "<h3>Step 1: Locating Student</h3>";
    $student_stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name, roll_number, class_id FROM students WHERE CONCAT(first_name, ' ', last_name) = ? AND status = 'active'");
    $student_stmt->execute([$target_student_name]);
    $student = $student_stmt->fetch();
    
    if (!$student) {
        echo "<p class='error'>❌ Exact student match not found!</p>";
        
        // Try partial match
        $partial_stmt = $pdo->prepare("SELECT id, student_id, first_name, last_name, roll_number, class_id FROM students WHERE first_name LIKE ? OR last_name LIKE ? AND status = 'active'");
        $partial_stmt->execute(['%SONIA%', '%KHATUN%']);
        $partial_students = $partial_stmt->fetchAll();
        
        if (!empty($partial_students)) {
            echo "<p class='warning'>⚠️ Found similar students:</p>";
            foreach ($partial_students as $ps) {
                echo "<div class='info'>- {$ps['first_name']} {$ps['last_name']} (ID: {$ps['id']})</div>";
            }
            $student = $partial_students[0]; // Use first match
            echo "<p class='info'>Using: {$student['first_name']} {$student['last_name']}</p>";
        } else {
            echo "<p class='error'>❌ No matching students found at all!</p>";
            exit;
        }
    } else {
        echo "<p class='success'>✅ Found student: {$student['first_name']} {$student['last_name']}</p>";
        echo "<p class='info'>Student ID: {$student['id']}, Roll: {$student['roll_number']}</p>";
    }
    
    // Step 2: Check existing attendance for this date
    echo "<h3>Step 2: Checking Existing Attendance</h3>";
    $check_stmt = $pdo->prepare("SELECT id, status, created_at FROM attendance WHERE student_id = ? AND date = ?");
    $check_stmt->execute([$student['id'], $search_date]);
    $existing = $check_stmt->fetch();
    
    if ($existing) {
        echo "<p class='warning'>⚠️ Attendance record already exists!</p>";
        echo "<div class='info'>";
        echo "<strong>ID:</strong> {$existing['id']}<br>";
        echo "<strong>Status:</strong> {$existing['status']}<br>";
        echo "<strong>Created:</strong> {$existing['created_at']}";
        echo "</div>";
        
        if ($existing['status'] !== 'present') {
            echo "<p class='warning'>Status is not 'present' - would you like to update it?</p>";
        } else {
            echo "<p class='success'>✅ Attendance is already correctly marked as present!</p>";
        }
    } else {
        echo "<p class='error'>❌ No attendance record found for this date</p>";
        echo "<p class='info'>This confirms the visual display is not backed by actual database records</p>";
    }
    
    // Step 3: FORCE SAVE the attendance record
    echo "<h3>Step 3: Force Attendance Save</h3>";
    
    if (isset($_POST['force_save'])) {
        echo "<p class='info'>Executing forced save...</p>";
        
        try {
            // Begin transaction
            $pdo->beginTransaction();
            
            // Insert the attendance record
            $insert_stmt = $pdo->prepare("INSERT INTO attendance (student_id, class_id, date, status, remarks, marked_by) VALUES (?, ?, ?, ?, ?, ?)");
            $result = $insert_stmt->execute([
                $student['id'],
                $target_class_id,
                $search_date,
                'present',
                'FORCE SAVED - Emergency fix',
                1 // System/admin user
            ]);
            
            if ($result) {
                $new_id = $pdo->lastInsertId();
                echo "<p class='success'>✅ Attendance record created successfully!</p>";
                echo "<p><strong>New Record ID:</strong> $new_id</p>";
                
                // Verify the insertion
                $verify_stmt = $pdo->prepare("SELECT * FROM attendance WHERE id = ?");
                $verify_stmt->execute([$new_id]);
                $verified = $verify_stmt->fetch();
                
                if ($verified) {
                    echo "<p class='success'>✅ Verification successful!</p>";
                    echo "<div class='info'>";
                    echo "<strong>ID:</strong> {$verified['id']}<br>";
                    echo "<strong>Student ID:</strong> {$verified['student_id']}<br>";
                    echo "<strong>Date:</strong> {$verified['date']}<br>";
                    echo "<strong>Status:</strong> {$verified['status']}<br>";
                    echo "<strong>Remarks:</strong> {$verified['remarks']}<br>";
                    echo "<strong>Created:</strong> {$verified['created_at']}";
                    echo "</div>";
                    
                    // Commit transaction
                    $pdo->commit();
                    echo "<p class='success'>✅ Transaction committed successfully!</p>";
                    
                } else {
                    $pdo->rollback();
                    echo "<p class='error'>❌ Verification failed - rolling back</p>";
                }
            } else {
                $pdo->rollback();
                echo "<p class='error'>❌ Failed to insert record</p>";
                echo "<pre>" . print_r($insert_stmt->errorInfo(), true) . "</pre>";
            }
            
        } catch (Exception $e) {
            $pdo->rollback();
            echo "<p class='error'>❌ Error during save: " . $e->getMessage() . "</p>";
        }
        
    } else {
        // Show the force save button
        echo "<form method='POST'>";
        echo "<input type='hidden' name='force_save' value='1'>";
        echo "<button type='submit' class='fix-button'>_FORCE SAVE ATTENDANCE NOW_</button>";
        echo "</form>";
        echo "<p class='warning'>⚠️ This will create a definitive attendance record in the database</p>";
    }
    
    // Step 4: Show current display vs reality
    echo "<h3>Step 4: Status Comparison</h3>";
    echo "<table border='1' cellpadding='10'>";
    echo "<tr><th>Aspect</th><th>Current Display</th><th>Database Reality</th></tr>";
    
    // Check what the system would show
    $display_check = $pdo->prepare("SELECT status FROM attendance WHERE student_id = ? AND date = ?");
    $display_check->execute([$student['id'], $search_date]);
    $display_result = $display_check->fetch();
    
    echo "<tr>";
    echo "<td><strong>Attendance Status</strong></td>";
    echo "<td>উপস্থিত (ID: 565)</td>"; // From your example
    echo "<td>" . ($display_result ? $display_result['status'] : 'ABSENT') . "</td>";
    echo "</tr>";
    
    echo "<tr>";
    echo "<td><strong>Record ID</strong></td>";
    echo "<td>565 (visual only)</td>";
    echo "<td>" . ($display_result ? $display_result['id'] : 'NONE') . "</td>";
    echo "</tr>";
    
    echo "</table>";
    
} catch (Exception $e) {
    echo "<p class='error'>❌ Critical Error: " . $e->getMessage() . "</p>";
}

echo "<hr>";
echo "<h3>Next Steps:</h3>";
echo "<ol>";
echo "<li>Click the FORCE SAVE button above to create the actual database record</li>";
echo "<li>Refresh the auto-attendance page to verify the fix worked</li>";
echo "<li>If the problem persists, we need to check the AJAX saving mechanism</li>";
echo "</ol>";

echo "<p><a href='attendance_dashboard/auto_attendance.php?class_id=14&session=2024-25&date=2026-01-16'>← Back to Auto Attendance Page</a></p>";
echo "</body></html>";
?>