<?php
/**
 * Migration: Enhanced Student Answers Table Configuration
 * 
 * This migration enhances the student_answers table with:
 * - Additional columns for complete answer tracking
 * - Proper constraints for data integrity
 * - Performance indexes for common queries
 * - Triggers for automatic synchronization with exam_attempts
 * - Audit log table for tracking changes
 * - Views for answer overview and pending grades
 */

require_once 'config/database.php';

function enhanceStudentAnswersTable() {
    $pdo = getDB();
    
    try {
        echo "Starting enhanced student_answers configuration...\n\n";
        
        // Check if student_answers table exists
        $stmt = $pdo->prepare("SHOW TABLES LIKE 'student_answers'");
        $stmt->execute();
        $tableExists = $stmt->rowCount() > 0;
        
        if (!$tableExists) {
            echo "Creating student_answers table first...\n";
            // Create the base table if it doesn't exist
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS student_answers (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    exam_id INT NOT NULL,
                    attempt_id INT NULL,
                    student_id INT NOT NULL,
                    question_id INT NOT NULL,
                    answer_content LONGTEXT NOT NULL,
                    answer_type ENUM('text', 'json', 'numeric', 'boolean') DEFAULT 'text',
                    is_correct BOOLEAN NULL,
                    score DECIMAL(10, 2) NULL,
                    max_score DECIMAL(10, 2) DEFAULT 0.00,
                    feedback TEXT NULL,
                    is_graded BOOLEAN DEFAULT FALSE,
                    graded_by INT NULL,
                    graded_at TIMESTAMP NULL,
                    grading_method ENUM('auto', 'manual', 'ai') DEFAULT 'auto',
                    version_number INT DEFAULT 1,
                    previous_answer_id INT NULL,
                    time_spent_seconds INT DEFAULT 0,
                    ip_address VARCHAR(45),
                    browser_info JSON,
                    started_at TIMESTAMP NULL,
                    submitted_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    
                    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
                    FOREIGN KEY (attempt_id) REFERENCES exam_attempts(id) ON DELETE SET NULL,
                    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
                    FOREIGN KEY (graded_by) REFERENCES users(id) ON DELETE SET NULL,
                    FOREIGN KEY (previous_answer_id) REFERENCES student_answers(id) ON DELETE SET NULL,
                    
                    UNIQUE KEY unique_student_question_exam (student_id, question_id, exam_id, attempt_id),
                    INDEX idx_exam (exam_id),
                    INDEX idx_student (student_id),
                    INDEX idx_question (question_id),
                    INDEX idx_attempt (attempt_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
        }
        
        echo "✓ Base table exists or created\n";
        
        // Add additional columns if they don't exist
        $columnsToAdd = [
            ['name' => 'status', 'definition' => "ENUM('in_progress', 'saved', 'submitted', 'graded', 'reviewed') DEFAULT 'in_progress'"],
            ['name' => 'is_draft', 'definition' => 'BOOLEAN DEFAULT TRUE'],
            ['name' => 'is_final', 'definition' => 'BOOLEAN DEFAULT FALSE'],
            ['name' => 'answer_hash', 'definition' => 'VARCHAR(64)'],
            ['name' => 'answer_length', 'definition' => 'INT DEFAULT 0'],
            ['name' => 'word_count', 'definition' => 'INT DEFAULT 0'],
            ['name' => 'char_count', 'definition' => 'INT DEFAULT 0'],
            ['name' => 'first_save_at', 'definition' => 'TIMESTAMP NULL'],
            ['name' => 'last_save_at', 'definition' => 'TIMESTAMP NULL'],
            ['name' => 'save_count', 'definition' => 'INT DEFAULT 0'],
            ['name' => 'is_flagged', 'definition' => 'BOOLEAN DEFAULT FALSE'],
            ['name' => 'flagged_at', 'definition' => 'TIMESTAMP NULL'],
            ['name' => 'needs_review', 'definition' => 'BOOLEAN DEFAULT FALSE'],
            ['name' => 'source', 'definition' => "ENUM('web', 'mobile', 'api', 'import') DEFAULT 'web'"],
            ['name' => 'session_id', 'definition' => 'VARCHAR(100)'],
            ['name' => 'device_info', 'definition' => 'JSON'],
            ['name' => 'client_timezone', 'definition' => 'VARCHAR(50)']
        ];
        
        foreach ($columnsToAdd as $column) {
            $stmt = $pdo->prepare("SHOW COLUMNS FROM student_answers LIKE ?");
            $stmt->execute([$column['name']]);
            
            if ($stmt->rowCount() === 0) {
                $pdo->exec("ALTER TABLE student_answers ADD COLUMN {$column['name']} {$column['definition']}");
                echo "  ✓ Added column: {$column['name']}\n";
            }
        }
        
        echo "✓ Additional columns added/verified\n";
        
        // Add performance indexes
        $indexesToCreate = [
            ['name' => 'idx_status', 'definition' => 'status'],
            ['name' => 'idx_is_graded', 'definition' => 'is_graded'],
            ['name' => 'idx_is_flagged', 'definition' => 'is_flagged'],
            ['name' => 'idx_exam_status', 'definition' => 'exam_id, status'],
            ['name' => 'idx_student_exam_status', 'definition' => 'student_id, exam_id, status'],
            ['name' => 'idx_question_graded', 'definition' => 'question_id, is_graded'],
            ['name' => 'idx_attempt_question', 'definition' => 'attempt_id, question_id'],
            ['name' => 'idx_student_graded', 'definition' => 'student_id, is_graded'],
            ['name' => 'idx_exam_graded', 'definition' => 'exam_id, is_graded'],
            ['name' => 'idx_submitted_at', 'definition' => 'submitted_at'],
            ['name' => 'idx_answer_hash', 'definition' => 'answer_hash'],
            ['name' => 'idx_graded_by_date', 'definition' => 'graded_by, graded_at']
        ];
        
        foreach ($indexesToCreate as $index) {
            $stmt = $pdo->prepare("SHOW INDEX FROM student_answers WHERE Key_name = ?");
            $stmt->execute([$index['name']]);
            
            if ($stmt->rowCount() === 0) {
                $pdo->exec("CREATE INDEX {$index['name']} ON student_answers ({$index['definition']})");
                echo "  ✓ Created index: {$index['name']}\n";
            }
        }
        
        echo "✓ Performance indexes added/verified\n";
        
        // Create student_answer_sync_log table for tracking sync operations
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS student_answer_sync_log (
                id INT AUTO_INCREMENT PRIMARY KEY,
                answer_id INT NOT NULL,
                attempt_id INT NOT NULL,
                action ENUM('create', 'update', 'delete', 'sync') NOT NULL,
                old_values JSON,
                new_values JSON,
                sync_status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
                error_message TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                synced_at TIMESTAMP NULL,
                
                INDEX idx_answer (answer_id),
                INDEX idx_attempt (attempt_id),
                INDEX idx_sync_status (sync_status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        
        echo "✓ Created student_answer_sync_log table\n";
        
        // Create trigger for answer content changes logging
        $pdo->exec("DROP TRIGGER IF EXISTS trg_before_answer_update");
        
        $pdo->exec("
            CREATE TRIGGER trg_before_answer_update
            BEFORE UPDATE ON student_answers
            FOR EACH ROW
            BEGIN
                IF OLD.answer_content != NEW.answer_content OR OLD.status != NEW.status THEN
                    INSERT INTO student_answer_sync_log (answer_id, attempt_id, action, old_values, new_values, sync_status, synced_at)
                    VALUES (
                        NEW.id, 
                        NEW.attempt_id, 
                        'update',
                        JSON_OBJECT('answer_content', LEFT(OLD.answer_content, 1000), 'status', OLD.status),
                        JSON_OBJECT('answer_content', LEFT(NEW.answer_content, 1000), 'status', NEW.status),
                        'completed',
                        NOW()
                    );
                END IF;
                
                IF OLD.answer_content != NEW.answer_content THEN
                    SET NEW.answer_hash = MD5(NEW.answer_content);
                    SET NEW.answer_length = CHAR_LENGTH(NEW.answer_content);
                END IF;
            END
        ");
        
        echo "✓ Created trigger: trg_before_answer_update\n";
        
        // Create trigger for answer deletion logging
        $pdo->exec("DROP TRIGGER IF EXISTS trg_before_answer_delete");
        
        $pdo->exec("
            CREATE TRIGGER trg_before_answer_delete
            BEFORE DELETE ON student_answers
            FOR EACH ROW
            BEGIN
                INSERT INTO student_answer_sync_log (answer_id, attempt_id, action, old_values, sync_status, synced_at)
                VALUES (
                    OLD.id, 
                    OLD.attempt_id, 
                    'delete',
                    JSON_OBJECT('exam_id', OLD.exam_id, 'question_id', OLD.question_id, 'student_id', OLD.student_id),
                    'completed',
                    NOW()
                );
            END
        ");
        
        echo "✓ Created trigger: trg_before_answer_delete\n";
        
        // Create view for answer overview
        $pdo->exec("
            CREATE OR REPLACE VIEW v_student_answers_overview AS
            SELECT 
                sa.id,
                sa.exam_id,
                e.title AS exam_title,
                sa.attempt_id,
                sa.student_id,
                u.username,
                u.email,
                sa.question_id,
                q.content AS question_content,
                q.question_type,
                sa.answer_content,
                sa.answer_type,
                sa.is_correct,
                sa.score,
                sa.max_score,
                sa.feedback,
                sa.is_graded,
                sa.graded_by,
                u2.username AS graded_by_username,
                sa.graded_at,
                sa.grading_method,
                sa.status,
                sa.is_flagged,
                sa.time_spent_seconds,
                sa.submitted_at,
                sa.created_at
            FROM student_answers sa
            LEFT JOIN exams e ON sa.exam_id = e.id
            LEFT JOIN users u ON sa.student_id = u.id
            LEFT JOIN questions q ON sa.question_id = q.id
            LEFT JOIN users u2 ON sa.graded_by = u2.id
        ");
        
        echo "✓ Created view: v_student_answers_overview\n";
        
        // Create view for pending grades
        $pdo->exec("
            CREATE OR REPLACE VIEW v_pending_grades AS
            SELECT 
                sa.id AS answer_id,
                sa.exam_id,
                e.title AS exam_title,
                sa.attempt_id,
                ea.attempt_number,
                sa.student_id,
                u.username,
                u.email,
                sa.question_id,
                q.question_type,
                q.content AS question_content,
                sa.answer_content,
                sa.max_score,
                sa.status,
                sa.is_flagged,
                sa.submitted_at,
                TIMESTAMPDIFF(HOUR, sa.submitted_at, NOW()) AS hours_pending
            FROM student_answers sa
            LEFT JOIN exams e ON sa.exam_id = e.id
            LEFT JOIN exam_attempts ea ON sa.attempt_id = ea.id
            LEFT JOIN users u ON sa.student_id = u.id
            LEFT JOIN questions q ON sa.question_id = q.id
            WHERE sa.is_graded = FALSE 
                AND sa.status IN ('submitted', 'reviewed')
                AND sa.answer_content IS NOT NULL 
                AND sa.answer_content != ''
            ORDER BY sa.submitted_at ASC
        ");
        
        echo "✓ Created view: v_pending_grades\n";
        
        echo "\n=== Enhanced student_answers configuration completed! ===\n\n";
        echo "Summary of changes:\n";
        echo "- Added 16 new columns for comprehensive answer tracking\n";
        echo "- Created 12 performance indexes for optimized queries\n";
        echo "- Created student_answer_sync_log table for audit trail\n";
        echo "- Created 2 database triggers for automatic operations\n";
        echo "- Created 2 views for answer overview and pending grades\n";
        
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage() . "\n";
        echo "SQLSTATE: " . $e->getCode() . "\n";
        exit(1);
    }
}

// Run the migration
enhanceStudentAnswersTable();
?>
