<?php
/**
 * Quick Verification Script: Check Department Field Status
 * 
 * This script verifies that the department field was added successfully.
 * Run this to confirm your migration worked.
 */

require_once 'db_config.php';

echo '<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Verify Department Field</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 800px; margin: 50px auto; padding: 20px; background: #f5f5f5; }
        .container { background: white; padding: 30px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        h1 { color: #2c3e50; border-bottom: 3px solid #3498db; padding-bottom: 10px; }
        .success { background: #d4edda; border: 1px solid #28a745; padding: 15px; margin: 15px 0; border-radius: 4px; color: #155724; }
        .error { background: #f8d7da; border: 1px solid #dc3545; padding: 15px; margin: 15px 0; border-radius: 4px; color: #721c24; }
        .info { background: #d1ecf1; border: 1px solid #17a2b8; padding: 15px; margin: 15px 0; border-radius: 4px; color: #0c5460; }
        table { width: 100%; border-collapse: collapse; margin: 20px 0; }
        th { background: #34495e; color: white; padding: 12px; text-align: left; }
        td { padding: 12px; border-bottom: 1px solid #ddd; }
        tr:hover { background: #f5f5f5; }
        .highlight { background: #ffffcc; font-weight: bold; }
        .badge { display: inline-block; padding: 4px 8px; border-radius: 3px; font-size: 0.9em; }
        .badge-success { background: #28a745; color: white; }
        .badge-error { background: #dc3545; color: white; }
        .stat-box { display: inline-block; background: #ecf0f1; padding: 20px; margin: 10px; border-radius: 6px; text-align: center; min-width: 150px; }
        .stat-number { font-size: 2em; font-weight: bold; color: #3498db; }
        .stat-label { color: #7f8c8d; margin-top: 5px; }
    </style>
</head>
<body>
    <div class="container">';

try {
    $pdo = getDBConnection();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h1>✓ Department Field Verification</h1>";
    
    // Check if column exists
    $columnCheck = $pdo->query("
        SELECT 
            COLUMN_NAME,
            COLUMN_TYPE,
            IS_NULLABLE,
            COLUMN_DEFAULT,
            COLUMN_KEY,
            EXTRA,
            COLUMN_COMMENT
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'delegates' 
        AND COLUMN_NAME = 'department'
    ")->fetch(PDO::FETCH_ASSOC);
    
    if ($columnCheck) {
        echo "<div class='success'>";
        echo "<h2>✅ SUCCESS! Department field exists</h2>";
        echo "<p>The migration was successful. The department column is present in your delegates table.</p>";
        echo "</div>";
        
        echo "<h3>Column Details:</h3>";
        echo "<table>";
        echo "<tr><th>Property</th><th>Value</th></tr>";
        foreach ($columnCheck as $key => $value) {
            echo "<tr><td><strong>$key</strong></td><td>" . ($value ?: '—') . "</td></tr>";
        }
        echo "</table>";
    } else {
        echo "<div class='error'>";
        echo "<h2>❌ Column Not Found</h2>";
        echo "<p>The department column does not exist. The migration may not have completed.</p>";
        echo "</div>";
    }
    
    // Check if index exists
    echo "<h3>Index Verification:</h3>";
    $indexCheck = $pdo->query("
        SELECT 
            INDEX_NAME,
            COLUMN_NAME,
            NON_UNIQUE,
            SEQ_IN_INDEX
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'delegates'
        AND INDEX_NAME = 'idx_delegate_department'
    ")->fetch(PDO::FETCH_ASSOC);
    
    if ($indexCheck) {
        echo "<div class='success'>";
        echo "✅ Index 'idx_delegate_department' exists";
        echo "</div>";
    } else {
        echo "<div class='error'>";
        echo "❌ Index 'idx_delegate_department' not found";
        echo "</div>";
    }
    
    // Show full table structure
    echo "<h3>Complete Delegates Table Structure:</h3>";
    $columns = $pdo->query("DESCRIBE delegates")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<table>";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
    foreach ($columns as $col) {
        $highlight = $col['Field'] === 'department' ? "class='highlight'" : "";
        echo "<tr $highlight>";
        echo "<td>{$col['Field']}</td>";
        echo "<td>{$col['Type']}</td>";
        echo "<td>{$col['Null']}</td>";
        echo "<td>{$col['Key']}</td>";
        echo "<td>" . ($col['Default'] ?? 'NULL') . "</td>";
        echo "<td>" . ($col['Extra'] ?? '—') . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    // Check current data
    echo "<h3>Current Data Statistics:</h3>";
    $stats = $pdo->query("
        SELECT 
            COUNT(*) as total_delegates,
            COUNT(CASE WHEN department IS NOT NULL AND department != '' THEN 1 END) as with_department,
            COUNT(CASE WHEN department IS NULL OR department = '' THEN 1 END) as without_department
        FROM delegates
    ")->fetch(PDO::FETCH_ASSOC);
    
    echo "<div style='text-align: center;'>";
    echo "<div class='stat-box'>";
    echo "<div class='stat-number'>{$stats['total_delegates']}</div>";
    echo "<div class='stat-label'>Total Delegates</div>";
    echo "</div>";
    
    echo "<div class='stat-box'>";
    echo "<div class='stat-number'>{$stats['with_department']}</div>";
    echo "<div class='stat-label'>With Department</div>";
    echo "</div>";
    
    echo "<div class='stat-box'>";
    echo "<div class='stat-number'>{$stats['without_department']}</div>";
    echo "<div class='stat-label'>Without Department</div>";
    echo "</div>";
    echo "</div>";
    
    // Show sample data
    echo "<h3>Sample Records (first 5):</h3>";
    $samples = $pdo->query("
        SELECT id, full_name, designation, department, email
        FROM delegates 
        ORDER BY id DESC
        LIMIT 5
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($samples)) {
        echo "<table>";
        echo "<tr><th>ID</th><th>Full Name</th><th>Designation</th><th>Department</th><th>Email</th></tr>";
        foreach ($samples as $sample) {
            echo "<tr>";
            echo "<td>{$sample['id']}</td>";
            echo "<td>{$sample['full_name']}</td>";
            echo "<td>{$sample['designation']}</td>";
            echo "<td>" . ($sample['department'] ?: '—') . "</td>";
            echo "<td>" . ($sample['email'] ?: '—') . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    echo "<div class='info'>";
    echo "<h3>✅ Verification Complete!</h3>";
    echo "<p><strong>Summary:</strong></p>";
    echo "<ul>";
    echo "<li>✅ Department column exists and is configured correctly</li>";
    echo "<li>✅ Index is in place for performance</li>";
    echo "<li>✅ All existing records are intact (department = NULL for existing records)</li>";
    echo "<li>✅ Ready to use in your registration forms!</li>";
    echo "</ul>";
    echo "<p><strong>Next Steps:</strong></p>";
    echo "<ol>";
    echo "<li>Test the registration form - you should see the Department field</li>";
    echo "<li>Check the admin panel delegates page</li>";
    echo "<li>Try exporting delegates CSV (should include department column)</li>";
    echo "<li>Delete this verification file (verify_department_field.php) for security</li>";
    echo "</ol>";
    echo "</div>";
    
} catch (Exception $e) {
    echo "<div class='error'>";
    echo "<h2>Error</h2>";
    echo "<p>" . htmlspecialchars($e->getMessage()) . "</p>";
    echo "</div>";
}

echo '</div></body></html>';
?>

