<?php
/**
 * Migration: Remove Exam System
 * Removes all exam-related tables, stored procedures, triggers, and views
 * Run this migration to remove exam functionality from the LMS
 */

require_once __DIR__ . '/../config/database.php';

$db = getDB();

try {
    $db->beginTransaction();

    // Drop triggers first (they depend on tables)
    echo "Dropping triggers...\n";
    $db->exec("DROP TRIGGER IF EXISTS trg_after_exam_attempt_submit");
    $db->exec("DROP TRIGGER IF EXISTS trg_before_exam_attempt_insert");
    $db->exec("DROP TRIGGER IF EXISTS trg_student_answer_after_insert");
    $db->exec("DROP TRIGGER IF EXISTS trg_before_answer_delete");
    $db->exec("DROP TRIGGER IF EXISTS trg_before_answer_update");

    // Drop views
    echo "Dropping views...\n";
    $db->exec("DROP VIEW IF EXISTS v_student_answers_overview");
    $db->exec("DROP VIEW IF EXISTS v_pending_grades");

    // Drop stored procedures
    echo "Dropping stored procedures...\n";
    $db->exec("DROP PROCEDURE IF EXISTS capture_student_exam_submission");
    $db->exec("DROP PROCEDURE IF EXISTS sp_bulk_grade_answers");
    $db->exec("DROP PROCEDURE IF EXISTS sp_grade_student_answer");
    $db->exec("DROP PROCEDURE IF EXISTS sp_upsert_student_answer");
    $db->exec("DROP PROCEDURE IF EXISTS test_debug_procedure");

    // Drop tables (in correct order due to foreign keys)
    echo "Dropping exam-related tables...\n";
    $db->exec("DROP TABLE IF EXISTS student_answer_sync_log");
    $db->exec("DROP TABLE IF EXISTS student_answers");
    $db->exec("DROP TABLE IF EXISTS student_answer");
    $db->exec("DROP TABLE IF EXISTS exam_question_grades");
    $db->exec("DROP TABLE IF EXISTS exam_questions");
    $db->exec("DROP TABLE IF EXISTS exam_attempts");
    $db->exec("DROP TABLE IF EXISTS exam_reviews");
    $db->exec("DROP TABLE IF EXISTS exam_access");
    $db->exec("DROP TABLE IF EXISTS exams");
    
    // Drop question-related tables
    echo "Dropping question-related tables...\n";
    $db->exec("DROP TABLE IF EXISTS question_tag_assignments");
    $db->exec("DROP TABLE IF EXISTS question_tags");
    $db->exec("DROP TABLE IF EXISTS question_reviews");
    $db->exec("DROP TABLE IF EXISTS question_options");
    $db->exec("DROP TABLE IF EXISTS question_categories");
    $db->exec("DROP TABLE IF EXISTS question_analytics");
    $db->exec("DROP TABLE IF EXISTS questions");

    $db->commit();
    echo "Exam system removed successfully!\n";

} catch (PDOException $e) {
    $db->rollBack();
    echo "Error removing exam system: " . $e->getMessage() . "\n";
    exit(1);
}