<?php
/**
 * Migration: Create student_answer Table
 * 
 * This migration creates the student_answer table for storing and managing
 * submitted answers for exam questions.
 * 
 * Table Structure:
 * - answer_id: Primary key auto-increment
 * - student_id: Foreign key to users table (role=student)
 * - exam_id: Foreign key to exams table
 * - question_id: Foreign key to questions table
 * - answer_text: Student's written response
 * - answer_choice: Multiple choice selections
 * - is_correct: Boolean flag for correctness
 * - score: Points earned for the answer
 * - submitted_at: Timestamp when answer was submitted
 * - graded_at: Timestamp when answer was graded
 * - graded_by: Foreign key to users table (teacher who graded)
 * - feedback: Instructor comments
 * - attempt_number: Track which attempt this is
 * - time_spent_seconds: Time taken to answer
 * - ip_address: Submission device IP
 * - status: Grading status (pending, graded, needs_review)
 * 
 * Indexes:
 * - Primary key on answer_id
 * - Unique composite index on (student_id, exam_id, question_id) to prevent duplicates
 * - Index on is_correct for analytics queries
 * - Index on submitted_at for chronological queries
 * - Composite indexes for common query patterns
 * 
 * Foreign Keys:
 * - student_id -> users(id) with CASCADE delete
 * - exam_id -> exams(id) with CASCADE delete
 * - question_id -> questions(id) with CASCADE delete
 * - graded_by -> users(id) with SET NULL (instructor can be removed)
 */

require_once 'config/database.php';

function createStudentAnswerTable() {
    $pdo = getDB();
    
    try {
        echo "=== Creating student_answer Table ===\n\n";
        
        // Drop table if exists (for clean re-creation)
        $pdo->exec("DROP TABLE IF EXISTS student_answer");
        echo "✓ Dropped existing student_answer table (if exists)\n";
        
        // Create the student_answer table with comprehensive structure
        // Note: Using 'users' table as students and instructors are stored there with roles
        $pdo->exec("
            CREATE TABLE student_answer (
                -- ========================================================================
                -- PRIMARY KEY
                -- ========================================================================
                answer_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for each answer',
                
                -- ========================================================================
                -- FOREIGN KEY REFERENCES
                -- ========================================================================
                student_id INT NOT NULL COMMENT 'Foreign key to users table (students)',
                exam_id INT NOT NULL COMMENT 'Foreign key to exams table',
                question_id INT NOT NULL COMMENT 'Foreign key to questions table',
                graded_by INT NULL COMMENT 'Foreign key to users table (instructor who graded)',
                
                -- ========================================================================
                -- ANSWER CONTENT FIELDS
                -- ========================================================================
                answer_text TEXT NULL COMMENT 'Student written response for essay/short answer questions',
                answer_choice VARCHAR(255) NULL COMMENT 'Selected choice for multiple choice questions',
                
                -- ========================================================================
                -- GRADING FIELDS
                -- ========================================================================
                is_correct BOOLEAN NULL DEFAULT NULL COMMENT 'Flag indicating if answer is correct',
                score DECIMAL(10, 2) NULL DEFAULT NULL COMMENT 'Points earned for this answer',
                feedback TEXT NULL COMMENT 'Instructor comments and feedback',
                
                -- ========================================================================
                -- TIMESTAMP FIELDS
                -- ========================================================================
                submitted_at TIMESTAMP NULL DEFAULT NULL COMMENT 'When the answer was submitted',
                graded_at TIMESTAMP NULL DEFAULT NULL COMMENT 'When the answer was graded',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
                
                -- ========================================================================
                -- ATTEMPT AND TRACKING FIELDS
                -- ========================================================================
                attempt_number INT NOT NULL DEFAULT 1 COMMENT 'Which attempt this is for the student on this question',
                time_spent_seconds INT NULL DEFAULT NULL COMMENT 'How long the student took to answer (in seconds)',
                ip_address VARCHAR(45) NULL COMMENT 'IP address of the submission device',
                
                -- ========================================================================
                -- STATUS FIELD
                -- ========================================================================
                status ENUM('pending', 'in_progress', 'submitted', 'graded', 'needs_review', 'auto_graded', 'manually_graded', 'partially_graded', 'regraded', 'archived') 
                    NOT NULL DEFAULT 'pending' 
                    COMMENT 'Grading status: pending, in_progress, submitted, graded, needs_review',
                
                -- ========================================================================
                -- CONSTRAINTS AND INDEXES
                -- ========================================================================
                
                -- Unique constraint to prevent duplicate submissions
                -- A student can only have one answer per question per exam attempt
                UNIQUE KEY uk_student_exam_question (student_id, exam_id, question_id),
                
                -- Index on is_correct for analytics queries
                INDEX idx_is_correct (is_correct),
                
                -- Index on submitted_at for chronological queries
                INDEX idx_submitted_at (submitted_at),
                
                -- Index on status for filtering by grading status
                INDEX idx_status (status),
                
                -- Composite index for common query: get all answers for an exam
                INDEX idx_exam_answers (exam_id, question_id),
                
                -- Composite index for common query: get student's answers for an exam
                INDEX idx_student_exam (student_id, exam_id),
                
                -- Composite index for grading queue queries
                INDEX idx_grading_queue (status, graded_at),
                
                -- Index for graded_by foreign key lookups
                INDEX idx_graded_by (graded_by),
                
                -- ========================================================================
                -- FOREIGN KEY CONSTRAINTS
                -- ========================================================================
                
                -- Foreign key to users table (students) with cascade delete
                CONSTRAINT fk_student_answer_student
                    FOREIGN KEY (student_id) 
                    REFERENCES users(id) 
                    ON DELETE CASCADE 
                    ON UPDATE CASCADE,
                
                -- Foreign key to exams table with cascade delete
                CONSTRAINT fk_student_answer_exam
                    FOREIGN KEY (exam_id) 
                    REFERENCES exams(id) 
                    ON DELETE CASCADE 
                    ON UPDATE CASCADE,
                
                -- Foreign key to questions table with cascade delete
                CONSTRAINT fk_student_answer_question
                    FOREIGN KEY (question_id) 
                    REFERENCES questions(id) 
                    ON DELETE CASCADE 
                    ON UPDATE CASCADE,
                
                -- Foreign key to users table (instructors) - SET NULL when instructor is deleted
                CONSTRAINT fk_student_answer_instructor
                    FOREIGN KEY (graded_by) 
                    REFERENCES users(id) 
                    ON DELETE SET NULL 
                    ON UPDATE CASCADE
                    
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            COMMENT='Stores student submitted answers for exam questions with grading tracking'
        ");
        
        echo "✓ Created student_answer table with all fields\n";
        
        // Verify the table was created
        $stmt = $pdo->query("SHOW CREATE TABLE student_answer");
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "\nTable created successfully!\n";
        echo "Engine: InnoDB\n";
        echo "Charset: utf8mb4\n\n";
        
        // Display column information
        echo "Columns created:\n";
        $columns = $pdo->query("SHOW COLUMNS FROM student_answer");
        while ($column = $columns->fetch(PDO::FETCH_ASSOC)) {
            echo "  - {$column['Field']}: {$column['Type']}";
            if ($column['Default']) echo " DEFAULT {$column['Default']}";
            if ($column['Null'] === 'NO') echo " NOT NULL";
            echo "\n";
        }
        
        // Display indexes
        echo "\nIndexes created:\n";
        $indexes = $pdo->query("SHOW INDEX FROM student_answer");
        while ($index = $indexes->fetch(PDO::FETCH_ASSOC)) {
            $unique = ($index['Non_unique'] == 0) ? 'UNIQUE' : '';
            echo "  - {$index['Key_name']}: {$index['Column_name']} ($unique)\n";
        }
        
        // Display foreign keys
        echo "\nForeign keys created:\n";
        $fks = $pdo->query("
            SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, 
                   REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME,
                   ON_DELETE, ON_UPDATE
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'student_answer'
            AND REFERENCED_TABLE_NAME IS NOT NULL
        ");
        while ($fk = $fks->fetch(PDO::FETCH_ASSOC)) {
            echo "  - {$fk['CONSTRAINT_NAME']}: {$fk['COLUMN_NAME']} -> {$fk['REFERENCED_TABLE_NAME']}({$fk['REFERENCED_COLUMN_NAME']}) ";
            echo "ON DELETE {$fk['ON_DELETE']} ON UPDATE {$fk['ON_UPDATE']}\n";
        }
        
        echo "\n=== student_answer table creation completed! ===\n";
        
        return true;
        
    } catch (PDOException $e) {
        echo "Error creating student_answer table: " . $e->getMessage() . "\n";
        return false;
    }
}

// Run the migration if executed directly
if (php_sapi_name() === 'cli' && basename(__FILE__) === basename($_SERVER['SCRIPT_NAME'])) {
    createStudentAnswerTable();
}
