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

try {
    // Create class_teachers table for multiple teachers per class
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS class_teachers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            class_id INT NOT NULL,
            teacher_id INT NOT NULL,
            assignment_type ENUM('primary', 'assistant', 'subject') DEFAULT 'assistant',
            assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            assigned_by INT,
            status ENUM('active', 'inactive') DEFAULT 'active',
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
            FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
            UNIQUE KEY unique_class_teacher (class_id, teacher_id)
        )
    ");
    
    echo "✓ class_teachers table created successfully\n";
    
    // Add indexes for better performance
    $pdo->exec("CREATE INDEX idx_class_teachers_class_id ON class_teachers (class_id)");
    $pdo->exec("CREATE INDEX idx_class_teachers_teacher_id ON class_teachers (teacher_id)");
    $pdo->exec("CREATE INDEX idx_class_teachers_assignment_type ON class_teachers (assignment_type)");
    
    echo "✓ Indexes created successfully\n";
    
    // Migrate existing class_teacher_id data to the new table
    $stmt = $pdo->query("SELECT id, class_teacher_id FROM classes WHERE class_teacher_id IS NOT NULL");
    $classes = $stmt->fetchAll();
    
    $migrated_count = 0;
    foreach ($classes as $class) {
        try {
            $insert_stmt = $pdo->prepare("
                INSERT INTO class_teachers (class_id, teacher_id, assignment_type, assigned_at, status) 
                VALUES (?, ?, 'primary', NOW(), 'active')
            ");
            $insert_stmt->execute([$class['id'], $class['class_teacher_id']]);
            $migrated_count++;
        } catch (Exception $e) {
            echo "Warning: Could not migrate teacher for class ID {$class['id']}: " . $e->getMessage() . "\n";
        }
    }
    
    echo "✓ Migrated $migrated_count existing class teachers to new table\n";
    echo "✓ Migration completed successfully\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>