<?php
/**
 * Migration Script: Whole Exam to Class-Level Publishing
 * This script helps migrate from whole-exam publishing to class-by-class publishing
 */

session_start();
require_once 'config.php';

echo "<!DOCTYPE html>\n";
echo "<html lang='en'>\n";
echo "<head>\n";
echo "    <meta charset='UTF-8'>\n";
echo "    <meta name='viewport' content='width=device-width, initial-scale=1.0'>\n";
echo "    <title>🔄 Exam Publishing System Migration</title>\n";
echo "    <style>\n";
echo "        body { font-family: Arial, sans-serif; max-width: 1000px; margin: 0 auto; padding: 20px; background: #f5f5f5; }\n";
echo "        .container { background: white; padding: 30px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }\n";
echo "        .section { margin: 20px 0; padding: 20px; border: 1px solid #ddd; border-radius: 6px; }\n";
echo "        .success { background: #d4edda; border-color: #c3e6cb; color: #155724; }\n";
echo "        .warning { background: #fff3cd; border-color: #ffeaa7; color: #856404; }\n";
echo "        .info { background: #d1ecf1; border-color: #bee5eb; color: #0c5460; }\n";
echo "        .btn { background: #007bff; color: white; padding: 10px 16px; text-decoration: none; border-radius: 4px; display: inline-block; margin: 5px; border: none; cursor: pointer; }\n";
echo "        .btn:hover { background: #0056b3; }\n";
echo "        .btn-success { background: #28a745; }\n";
echo "        .btn-danger { background: #dc3545; }\n";
echo "        pre { background: #f8f9fa; padding: 15px; border-radius: 4px; overflow-x: auto; }\n";
echo "        code { background: #f0f0f0; padding: 2px 4px; border-radius: 3px; }\n";
echo "    </style>\n";
echo "</head>\n";
echo "<body>\n";

echo "<div class='container'>\n";
echo "<h1>🔄 Exam Publishing System Migration</h1>\n";
echo "<p>This page helps you migrate from whole-exam publishing to class-by-class publishing.</p>\n";

// Check if user is admin
if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true) {
    echo "<div class='section warning'>❌ Please login as administrator to run migration.</div>\n";
    echo "<a href='login.php' class='btn'>Go to Login</a>\n";
    echo "</div></body></html>\n";
    exit;
}

if ($_SESSION["role"] !== 1 && $_SESSION["role"] !== 0) {
    echo "<div class='section warning'>❌ Insufficient permissions. Admin access required.</div>\n";
    echo "</div></body></html>\n";
    exit;
}

$school_id = $_SESSION["school_id"];
$user_id = $_SESSION["id"];

// Check 1: Does exam_class_status table exist?
echo "<div class='section info'>\n";
echo "<h3>1. Database Table Check</h3>\n";

$table_exists = $conn->query("SHOW TABLES LIKE 'exam_class_status'")->num_rows > 0;

if (!$table_exists) {
    echo "<div class='section warning'>⚠️ <strong>exam_class_status table does not exist.</strong></div>\n";
    echo "<p>You need to create this table first. Choose one option:</p>\n";
    
    echo "<h4>Option A: Manual SQL Setup</h4>\n";
    echo "<p>Run this SQL in your database:</p>\n";
    echo "<pre><code>";
    include 'create_exam_class_status.sql';
    echo "</code></pre>\n";
    
    echo "<h4>Option B: Auto-Create Table</h4>\n";
    echo "<button class='btn btn-success' onclick='createTable()'>Create Table Automatically</button>\n";
    echo "<div id='create-table-result'></div>\n";
    
} else {
    echo "<div class='section success'>✅ <strong>exam_class_status table exists.</strong></div>\n";
}
echo "</div>\n";

// Check 2: Current exam status analysis
echo "<div class='section info'>\n";
echo "<h3>2. Current Exam Status Analysis</h3>\n";

if ($table_exists) {
    // Get exam statistics
    $sql = "
        SELECT 
            e.exam_id,
            e.exam_name,
            e.status as exam_status,
            COUNT(DISTINCT c.class_id) as total_classes,
            COUNT(DISTINCT ecs.exam_class_status_id) as tracked_classes,
            COUNT(DISTINCT CASE WHEN ecs.status = 'Published' THEN ecs.exam_class_status_id END) as published_classes
        FROM exams e
        LEFT JOIN classes c ON c.school_id = e.school_id
        LEFT JOIN exam_class_status ecs ON ecs.exam_id = e.exam_id AND ecs.class_id = c.class_id
        WHERE e.school_id = ?
        GROUP BY e.exam_id, e.exam_name, e.status
        HAVING total_classes > 0
        ORDER BY e.exam_name
    ";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $school_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    echo "<table style='width: 100%; border-collapse: collapse;'>\n";
    echo "<tr><th>Exam Name</th><th>Current Status</th><th>Total Classes</th><th>Tracked</th><th>Published</th><th>Action</th></tr>\n";
    
    while ($exam = $result->fetch_assoc()) {
        $tracked_pct = $exam['total_classes'] > 0 ? round(($exam['tracked_classes'] / $exam['total_classes']) * 100) : 0;
        
        echo "<tr>\n";
        echo "<td>{$exam['exam_name']}</td>\n";
        echo "<td><span style='background: " . ($exam['exam_status'] === 'Published' ? '#28a745' : '#ffc107') . "; color: white; padding: 4px 8px; border-radius: 12px;'>" . ($exam['exam_status'] === 'Published' ? 'Published' : 'Draft') . "</span></td>\n";
        echo "<td>{$exam['total_classes']}</td>\n";
        echo "<td>{$exam['tracked_classes']} ({$tracked_pct}%)</td>\n";
        echo "<td>{$exam['published_classes']}</td>\n";
        
        if ($exam['tracked_classes'] == 0 && $exam['exam_status'] === 'Published') {
            echo "<td><button class='btn btn-success' onclick='migrateExam({$exam['exam_id']})'>Migrate to Class-Level</button></td>\n";
        } else {
            echo "<td>-</td>\n";
        }
        
        echo "</tr>\n";
    }
    echo "</table>\n";
    
} else {
    echo "<p>Cannot analyze exams until exam_class_status table is created.</p>\n";
}
echo "</div>\n";

// Check 3: Migration recommendations
echo "<div class='section info'>\n";
echo "<h3>3. Migration Recommendations</h3>\n";

if ($table_exists) {
    echo "<div class='section success'>\n";
    echo "<h4>✅ Migration Status</h4>\n";
    echo "<ul>\n";
    echo "<li>exam_class_status table is created</li>\n";
    echo "<li>System supports class-by-class publishing</li>\n";
    echo "<li>Ready for production use</li>\n";
    echo "</ul>\n";
    echo "</div>\n";
    
    echo "<div class='section info'>\n";
    echo "<h4>📋 Next Steps</h4>\n";
    echo "<ol>\n";
    echo "<li>Test class-by-class publishing with a new exam</li>\n";
    echo "<li>Migrate existing published exams if needed</li>\n";
    echo "<li>Update admin interface to use new class-level features</li>\n";
    echo "<li>Train administrators on new workflow</li>\n";
    echo "</ol>\n";
    echo "</div>\n";
    
} else {
    echo "<div class='section warning'>\n";
    echo "<h4>⚠️ Migration Required</h4>\n";
    echo "<p>To enable class-by-class publishing:</p>\n";
    echo "<ol>\n";
    echo "<li>Create the exam_class_status table (see Step 1 above)</li>\n";
    echo "<li>Upload the new PHP files</li>\n";
    echo "<li>Update admin interface</li>\n";
    echo "<li>Test the new system</li>\n";
    echo "</ol>\n";
    echo "</div>\n";
}
echo "</div>\n";

echo "</div>\n";

echo "<script>\n";
echo "function createTable() {\n";
echo "    const resultDiv = document.getElementById('create-table-result');\n";
echo "    resultDiv.innerHTML = '<div>Creating table...</div>';\n";
echo "    \n";
echo "    fetch('ajax_manage_exam_class_status.php', {\n";
echo "        method: 'POST',\n";
echo "        headers: {\n";
echo "            'Content-Type': 'application/x-www-form-urlencoded',\n";
echo "        },\n";
echo "        body: 'action=create_table'\n";
echo "    })\n";
echo "    .then(response => response.json())\n";
echo "    .then(data => {\n";
echo "        if (data.success) {\n";
echo "            resultDiv.innerHTML = '<div class=\"success\">Table created successfully!</div>';\n";
echo "            setTimeout(() => location.reload(), 2000);\n";
echo "        } else {\n";
echo "            resultDiv.innerHTML = '<div class=\"error\">Error: ' + data.message + '</div>';\n";
echo "        }\n";
echo "    })\n";
echo "    .catch(error => {\n";
echo "        resultDiv.innerHTML = '<div class=\"error\">Error: ' + error.message + '</div>';\n";
echo "    });\n";
echo "}\n";

echo "function migrateExam(examId) {\n";
echo "    if (!confirm('Migrate this exam to class-level publishing? This will create class-level records based on the current exam status.')) {\n";
echo "        return;\n";
echo "    }\n";
echo "    \n";
echo "    fetch('ajax_manage_exam_class_status.php', {\n";
echo "        method: 'POST',\n";
echo "        headers: {\n";
echo "            'Content-Type': 'application/x-www-form-urlencoded',\n";
echo "        },\n";
echo "        body: 'action=migrate_exam&exam_id=' + examId\n";
echo "    })\n";
echo "    .then(response => response.json())\n";
echo "    .then(data => {\n";
echo "        if (data.success) {\n";
echo "            alert('Exam migrated successfully!');\n";
echo "            location.reload();\n";
echo "        } else {\n";
echo "            alert('Error: ' + data.message);\n";
echo "        }\n";
echo "    })\n";
echo "    .catch(error => {\n";
echo "        alert('Error: ' + error.message);\n";
echo "    });\n";
echo "}\n";
echo "</script>\n";

echo "</body>\n";
echo "</html>\n";

$conn->close();
?>