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

try {
    // Create the retention_settings table
    $sql = "
    CREATE TABLE IF NOT EXISTS retention_settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        exam_id INT NOT NULL,
        retention_type ENUM('count', 'duration') DEFAULT 'count',
        retention_limit INT DEFAULT 5,
        retention_duration_days INT DEFAULT 30,
        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,
        UNIQUE KEY unique_exam (exam_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ";
    
    $pdo->exec($sql);
    
    echo "Table 'retention_settings' created successfully!";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>