<?php
// Script to create learning_materials table
require_once '../config/database.php';

try {
    // Create learning_materials table
    $sql = "
    CREATE TABLE IF NOT EXISTS learning_materials (
        id INT(11) NOT NULL AUTO_INCREMENT,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        class_id INT(11) NOT NULL,
        subject_id INT(11) NOT NULL,
        category ENUM('lecture_notes', 'presentation', 'video_lecture', 'audio_lecture', 'assignment', 'practice_questions') NOT NULL,
        file_upload_id INT(11) NOT NULL,
        allow_download TINYINT(1) DEFAULT 1,
        allow_share TINYINT(1) DEFAULT 1,
        allow_print TINYINT(1) DEFAULT 1,
        access_control ENUM('all', 'class_only', 'specific_students') DEFAULT 'all',
        created_by INT(11) NOT NULL,
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
        FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
        FOREIGN KEY (file_upload_id) REFERENCES file_uploads(id) ON DELETE CASCADE,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $pdo->exec($sql);
    
    // Create learning_materials_views table to track views/downloads
    $sql2 = "
    CREATE TABLE IF NOT EXISTS learning_materials_views (
        id INT(11) NOT NULL AUTO_INCREMENT,
        material_id INT(11) NOT NULL,
        user_id INT(11) NOT NULL,
        action ENUM('view', 'download') NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        FOREIGN KEY (material_id) REFERENCES learning_materials(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_view (material_id, user_id, action)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $pdo->exec($sql2);
    
    echo "Learning materials tables created successfully!\n";
    
} catch (Exception $e) {
    echo "Error creating tables: " . $e->getMessage() . "\n";
}
?>