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

try {
    // Create student_subjects table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS student_subjects (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            subject_id INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_subject (student_id, subject_id)
        )
    ");
    echo "✓ Student Subjects টেবিল তৈরি সফল<br>";
    
    // Insert sample subjects if they don't exist
    $subjects = [
        ['বাংলা', 'BAN'],
        ['ইংরেজি', 'ENG'],
        ['গণিত', 'MATH'],
        ['বিজ্ঞান', 'SCI'],
        ['সমাজবিজ্ঞান', 'SOC'],
        ['ধর্ম', 'REL'],
        ['শারীরিক শিক্ষা', 'PE'],
        ['কম্পিউটার', 'COMP']
    ];
    
    foreach ($subjects as $subject) {
        $stmt = $pdo->prepare("INSERT IGNORE INTO subjects (name, code) VALUES (?, ?)");
        $stmt->execute($subject);
    }
    echo "✓ নমুনা বিষয় যোগ করা হয়েছে<br>";
    
    // Insert sample class-subject mappings
    $class_subject_assignments = [
        [1, [1, 2, 3, 7]], // প্রথম শ্রেণী
        [2, [1, 2, 3, 7]], // দ্বিতীয় শ্রেণী
        [3, [1, 2, 3, 4, 7]], // তৃতীয় শ্রেণী
        [4, [1, 2, 3, 4, 5, 7]], // চতুর্থ শ্রেণী
        [5, [1, 2, 3, 4, 5, 6, 7]], // পঞ্চম শ্রেণী
    ];
    
    foreach ($class_subject_assignments as $assignment) {
        $class_id = $assignment[0];
        $subject_ids = $assignment[1];
        
        foreach ($subject_ids as $subject_id) {
            $stmt = $pdo->prepare("INSERT IGNORE INTO class_subjects (class_id, subject_id) VALUES (?, ?)");
            $stmt->execute([$class_id, $subject_id]);
        }
    }
    echo "✓ ক্লাস-বিষয় ম্যাপিং যোগ করা হয়েছে<br>";
    
    echo "<br><strong>সেটআপ সম্পন্ন!</strong><br>";
    echo "<a href='students/add.php'>ছাত্র যোগ করুন</a> | ";
    echo "<a href='subjects/manage.php'>বিষয় ব্যবস্থাপনা</a>";
    
} catch (Exception $e) {
    echo "ত্রুটি: " . $e->getMessage();
}
?>