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

echo "<!DOCTYPE html>";
echo "<html lang='bn'>";
echo "<head>";
echo "<meta charset='UTF-8'>";
echo "<title>ডাটাবেস আপডেট - সেশন, গ্রুপ ও মার্কস সিস্টেম</title>";
echo "<link href='https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css' rel='stylesheet'>";
echo "<link href='https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css' rel='stylesheet'>";
echo "<link href='assets/css/style.css' rel='stylesheet'>";
echo "</head>";
echo "<body class='bengali-text bg-light'>";

echo "<div class='container mt-4'>";
echo "<div class='row justify-content-center'>";
echo "<div class='col-md-10'>";

echo "<div class='card'>";
echo "<div class='card-header bg-primary text-white text-center'>";
echo "<h3><i class='fas fa-database'></i> ডাটাবেস আপডেট - সেশন, গ্রুপ ও মার্কস সিস্টেম</h3>";
echo "</div>";
echo "<div class='card-body'>";

try {
    // 1. Add session and group fields to students table
    echo "<h5><i class='fas fa-user-graduate'></i> শিক্ষার্থী টেবিল আপডেট:</h5>";
    
    try {
        $pdo->exec("ALTER TABLE students ADD COLUMN session VARCHAR(20) AFTER class_id");
        echo "<p class='text-success'><i class='fas fa-check'></i> Session ফিল্ড যোগ করা হয়েছে</p>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "<p class='text-info'><i class='fas fa-info'></i> Session ফিল্ড ইতিমধ্যে বিদ্যমান</p>";
        } else {
            throw $e;
        }
    }
    
    try {
        $pdo->exec("ALTER TABLE students ADD COLUMN group_name VARCHAR(50) AFTER session");
        echo "<p class='text-success'><i class='fas fa-check'></i> Group ফিল্ড যোগ করা হয়েছে</p>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "<p class='text-info'><i class='fas fa-info'></i> Group ফিল্ড ইতিমধ্যে বিদ্যমান</p>";
        } else {
            throw $e;
        }
    }
    
    // 2. Create academic_sessions table
    echo "<hr><h5><i class='fas fa-calendar-alt'></i> একাডেমিক সেশন টেবিল:</h5>";
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS academic_sessions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            session_name VARCHAR(20) NOT NULL UNIQUE,
            start_date DATE NOT NULL,
            end_date DATE NOT NULL,
            is_current BOOLEAN DEFAULT FALSE,
            status ENUM('active', 'inactive', 'completed') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "<p class='text-success'><i class='fas fa-check'></i> Academic Sessions টেবিল তৈরি</p>";
    
    // 3. Create groups table
    echo "<hr><h5><i class='fas fa-users'></i> গ্রুপ টেবিল:</h5>";
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS student_groups (
            id INT AUTO_INCREMENT PRIMARY KEY,
            group_name VARCHAR(50) NOT NULL,
            group_code VARCHAR(10) NOT NULL UNIQUE,
            description TEXT,
            applicable_classes TEXT COMMENT 'JSON array of class IDs',
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "<p class='text-success'><i class='fas fa-check'></i> Student Groups টেবিল তৈরি</p>";
    
    // 4. Create subject_mark_distribution table
    echo "<hr><h5><i class='fas fa-chart-pie'></i> বিষয়ভিত্তিক মার্কস বিভাজন টেবিল:</h5>";
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS subject_mark_distribution (
            id INT AUTO_INCREMENT PRIMARY KEY,
            subject_id INT NOT NULL,
            class_id INT NOT NULL,
            session VARCHAR(20) NOT NULL,
            group_name VARCHAR(50),
            exam_type ENUM('midterm', 'final', 'test', 'assignment', 'practical') NOT NULL,
            total_marks INT NOT NULL DEFAULT 100,
            cq_marks INT DEFAULT 0 COMMENT 'Creative Question marks',
            mcq_marks INT DEFAULT 0 COMMENT 'Multiple Choice Question marks',
            practical_marks INT DEFAULT 0 COMMENT 'Practical marks',
            assignment_marks INT DEFAULT 0 COMMENT 'Assignment marks',
            attendance_marks INT DEFAULT 0 COMMENT 'Attendance marks',
            pass_marks INT DEFAULT 40,
            grade_system ENUM('letter', 'gpa', 'percentage') DEFAULT 'gpa',
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            UNIQUE KEY unique_subject_class_session_group_exam (subject_id, class_id, session, group_name, exam_type)
        )
    ");
    echo "<p class='text-success'><i class='fas fa-check'></i> Subject Mark Distribution টেবিল তৈরি</p>";
    
    // 5. Update results table for detailed marks
    echo "<hr><h5><i class='fas fa-chart-bar'></i> রেজাল্ট টেবিল আপডেট:</h5>";
    
    try {
        $pdo->exec("ALTER TABLE results ADD COLUMN session VARCHAR(20) AFTER subject_id");
        echo "<p class='text-success'><i class='fas fa-check'></i> Results টেবিলে Session ফিল্ড যোগ</p>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "<p class='text-info'><i class='fas fa-info'></i> Results টেবিলে Session ফিল্ড ইতিমধ্যে বিদ্যমান</p>";
        }
    }
    
    try {
        $pdo->exec("ALTER TABLE results ADD COLUMN group_name VARCHAR(50) AFTER session");
        echo "<p class='text-success'><i class='fas fa-check'></i> Results টেবিলে Group ফিল্ড যোগ</p>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "<p class='text-info'><i class='fas fa-info'></i> Results টেবিলে Group ফিল্ড ইতিমধ্যে বিদ্যমান</p>";
        }
    }
    
    try {
        $pdo->exec("ALTER TABLE results ADD COLUMN exam_type ENUM('midterm', 'final', 'test', 'assignment', 'practical') DEFAULT 'final' AFTER group_name");
        echo "<p class='text-success'><i class='fas fa-check'></i> Results টেবিলে Exam Type ফিল্ড যোগ</p>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "<p class='text-info'><i class='fas fa-info'></i> Results টেবিলে Exam Type ফিল্ড ইতিমধ্যে বিদ্যমান</p>";
        }
    }
    
    try {
        $pdo->exec("ALTER TABLE results ADD COLUMN cq_marks DECIMAL(5,2) DEFAULT 0 AFTER marks_obtained");
        $pdo->exec("ALTER TABLE results ADD COLUMN mcq_marks DECIMAL(5,2) DEFAULT 0 AFTER cq_marks");
        $pdo->exec("ALTER TABLE results ADD COLUMN practical_marks DECIMAL(5,2) DEFAULT 0 AFTER mcq_marks");
        $pdo->exec("ALTER TABLE results ADD COLUMN assignment_marks DECIMAL(5,2) DEFAULT 0 AFTER practical_marks");
        $pdo->exec("ALTER TABLE results ADD COLUMN attendance_marks DECIMAL(5,2) DEFAULT 0 AFTER assignment_marks");
        echo "<p class='text-success'><i class='fas fa-check'></i> Results টেবিলে বিস্তারিত মার্কস ফিল্ড যোগ</p>";
    } catch (Exception $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "<p class='text-info'><i class='fas fa-info'></i> Results টেবিলে বিস্তারিত মার্কস ফিল্ড ইতিমধ্যে বিদ্যমান</p>";
        }
    }
    
    // 6. Insert sample academic sessions
    echo "<hr><h5><i class='fas fa-plus'></i> নমুনা একাডেমিক সেশন যোগ:</h5>";
    
    $sessions = [
        ['2024', '2024-01-01', '2024-12-31', true],
        ['2023', '2023-01-01', '2023-12-31', false],
        ['2025', '2025-01-01', '2025-12-31', false]
    ];
    
    foreach ($sessions as $session) {
        try {
            $stmt = $pdo->prepare("INSERT IGNORE INTO academic_sessions (session_name, start_date, end_date, is_current) VALUES (?, ?, ?, ?)");
            $stmt->execute($session);
            echo "<p class='text-success'><i class='fas fa-check'></i> সেশন {$session[0]} যোগ করা হয়েছে</p>";
        } catch (Exception $e) {
            echo "<p class='text-warning'><i class='fas fa-exclamation-triangle'></i> সেশন {$session[0]} যোগ করতে সমস্যা</p>";
        }
    }
    
    // 7. Insert sample groups
    echo "<hr><h5><i class='fas fa-plus'></i> নমুনা গ্রুপ যোগ:</h5>";
    
    $groups = [
        ['Science', 'SCI', 'বিজ্ঞান বিভাগ', '["9", "10", "11", "12"]'],
        ['Commerce', 'COM', 'ব্যবসায় শিক্ষা বিভাগ', '["9", "10", "11", "12"]'],
        ['Arts', 'ARTS', 'মানবিক বিভাগ', '["9", "10", "11", "12"]'],
        ['General', 'GEN', 'সাধারণ (১-৮ শ্রেণি)', '["1", "2", "3", "4", "5", "6", "7", "8"]']
    ];
    
    foreach ($groups as $group) {
        try {
            $stmt = $pdo->prepare("INSERT IGNORE INTO student_groups (group_name, group_code, description, applicable_classes) VALUES (?, ?, ?, ?)");
            $stmt->execute($group);
            echo "<p class='text-success'><i class='fas fa-check'></i> গ্রুপ {$group[0]} যোগ করা হয়েছে</p>";
        } catch (Exception $e) {
            echo "<p class='text-warning'><i class='fas fa-exclamation-triangle'></i> গ্রুপ {$group[0]} যোগ করতে সমস্যা</p>";
        }
    }
    
    // 8. Insert sample mark distributions
    echo "<hr><h5><i class='fas fa-plus'></i> নমুনা মার্কস বিভাজন যোগ:</h5>";
    
    // Get some subjects and classes for sample data
    $subjects = $pdo->query("SELECT id, name FROM subjects LIMIT 5")->fetchAll();
    $classes = $pdo->query("SELECT id, name FROM classes LIMIT 3")->fetchAll();
    
    if (!empty($subjects) && !empty($classes)) {
        $markDistributions = [
            // Science group - different distributions
            ['midterm', 100, 60, 30, 10, 0, 0, 40],  // CQ heavy
            ['final', 100, 70, 20, 10, 0, 0, 40],    // CQ heavy
            ['practical', 50, 0, 0, 50, 0, 0, 20],   // All practical
            
            // Commerce group
            ['midterm', 100, 50, 40, 0, 10, 0, 40],  // CQ + MCQ + Assignment
            ['final', 100, 60, 30, 0, 10, 0, 40],    // CQ + MCQ + Assignment
            
            // Arts group
            ['midterm', 100, 80, 20, 0, 0, 0, 40],   // Mostly CQ
            ['final', 100, 90, 10, 0, 0, 0, 40],     // Mostly CQ
        ];
        
        $groups = ['Science', 'Commerce', 'Arts'];
        $distributionIndex = 0;
        
        foreach ($subjects as $subject) {
            foreach ($classes as $class) {
                foreach ($groups as $group) {
                    if ($distributionIndex < count($markDistributions)) {
                        $dist = $markDistributions[$distributionIndex % count($markDistributions)];
                        
                        try {
                            $stmt = $pdo->prepare("
                                INSERT IGNORE INTO subject_mark_distribution 
                                (subject_id, class_id, session, group_name, exam_type, total_marks, cq_marks, mcq_marks, practical_marks, assignment_marks, attendance_marks, pass_marks) 
                                VALUES (?, ?, '2024', ?, ?, ?, ?, ?, ?, ?, ?, ?)
                            ");
                            $stmt->execute([
                                $subject['id'], $class['id'], $group,
                                $dist[0], $dist[1], $dist[2], $dist[3], $dist[4], $dist[5], $dist[6], $dist[7]
                            ]);
                            
                            $distributionIndex++;
                        } catch (Exception $e) {
                            // Skip if already exists
                        }
                    }
                }
            }
        }
        echo "<p class='text-success'><i class='fas fa-check'></i> নমুনা মার্কস বিভাজন যোগ করা হয়েছে</p>";
    } else {
        echo "<p class='text-warning'><i class='fas fa-exclamation-triangle'></i> মার্কস বিভাজনের জন্য প্রথমে বিষয় ও ক্লাস যোগ করুন</p>";
    }
    
    // 9. Create grade_system table
    echo "<hr><h5><i class='fas fa-award'></i> গ্রেড সিস্টেম টেবিল:</h5>";
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS grade_system (
            id INT AUTO_INCREMENT PRIMARY KEY,
            grade_name VARCHAR(5) NOT NULL,
            min_percentage DECIMAL(5,2) NOT NULL,
            max_percentage DECIMAL(5,2) NOT NULL,
            gpa_point DECIMAL(3,2) NOT NULL,
            description VARCHAR(100),
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");
    echo "<p class='text-success'><i class='fas fa-check'></i> Grade System টেবিল তৈরি</p>";
    
    // Insert standard grade system
    $grades = [
        ['A+', 80.00, 100.00, 5.00, 'Outstanding'],
        ['A', 70.00, 79.99, 4.00, 'Excellent'],
        ['A-', 60.00, 69.99, 3.50, 'Very Good'],
        ['B', 50.00, 59.99, 3.00, 'Good'],
        ['C', 40.00, 49.99, 2.00, 'Satisfactory'],
        ['D', 33.00, 39.99, 1.00, 'Acceptable'],
        ['F', 0.00, 32.99, 0.00, 'Fail']
    ];
    
    foreach ($grades as $grade) {
        try {
            $stmt = $pdo->prepare("INSERT IGNORE INTO grade_system (grade_name, min_percentage, max_percentage, gpa_point, description) VALUES (?, ?, ?, ?, ?)");
            $stmt->execute($grade);
        } catch (Exception $e) {
            // Skip if exists
        }
    }
    echo "<p class='text-success'><i class='fas fa-check'></i> স্ট্যান্ডার্ড গ্রেড সিস্টেম যোগ করা হয়েছে</p>";
    
    echo "<hr>";
    echo "<div class='alert alert-success text-center'>";
    echo "<h4><i class='fas fa-check-circle'></i> ডাটাবেস আপডেট সম্পন্ন!</h4>";
    echo "<p>সেশন, গ্রুপ ও মার্কস বিভাজন সিস্টেম সফলভাবে যোগ করা হয়েছে।</p>";
    echo "</div>";
    
    // Show summary
    echo "<div class='row'>";
    
    // Sessions count
    $sessionCount = $pdo->query("SELECT COUNT(*) FROM academic_sessions")->fetchColumn();
    echo "<div class='col-md-3'>";
    echo "<div class='card bg-primary text-white text-center'>";
    echo "<div class='card-body'>";
    echo "<h3>$sessionCount</h3>";
    echo "<p>একাডেমিক সেশন</p>";
    echo "</div></div></div>";
    
    // Groups count
    $groupCount = $pdo->query("SELECT COUNT(*) FROM student_groups")->fetchColumn();
    echo "<div class='col-md-3'>";
    echo "<div class='card bg-success text-white text-center'>";
    echo "<div class='card-body'>";
    echo "<h3>$groupCount</h3>";
    echo "<p>শিক্ষার্থী গ্রুপ</p>";
    echo "</div></div></div>";
    
    // Mark distributions count
    $markDistCount = $pdo->query("SELECT COUNT(*) FROM subject_mark_distribution")->fetchColumn();
    echo "<div class='col-md-3'>";
    echo "<div class='card bg-info text-white text-center'>";
    echo "<div class='card-body'>";
    echo "<h3>$markDistCount</h3>";
    echo "<p>মার্কস বিভাজন</p>";
    echo "</div></div></div>";
    
    // Grades count
    $gradeCount = $pdo->query("SELECT COUNT(*) FROM grade_system")->fetchColumn();
    echo "<div class='col-md-3'>";
    echo "<div class='card bg-warning text-white text-center'>";
    echo "<div class='card-body'>";
    echo "<h3>$gradeCount</h3>";
    echo "<p>গ্রেড সিস্টেম</p>";
    echo "</div></div></div>";
    
    echo "</div>";
    
    echo "<div class='text-center mt-4'>";
    echo "<h5>এখন আপনি ব্যবহার করতে পারেন:</h5>";
    echo "<div class='d-flex justify-content-center flex-wrap gap-2 mt-3'>";
    echo "<a href='students/add.php' class='btn btn-success'><i class='fas fa-user-plus'></i> নতুন শিক্ষার্থী (সেশন ও গ্রুপ সহ)</a>";
    echo "<a href='marks/setup.php' class='btn btn-info'><i class='fas fa-cog'></i> মার্কস সেটআপ</a>";
    echo "<a href='marks/entry.php' class='btn btn-warning'><i class='fas fa-edit'></i> মার্কস এন্ট্রি</a>";
    echo "<a href='index.php' class='btn btn-primary'><i class='fas fa-home'></i> হোম</a>";
    echo "</div>";
    echo "</div>";
    
} catch (Exception $e) {
    echo "<div class='alert alert-danger'>";
    echo "<h5><i class='fas fa-exclamation-triangle'></i> ত্রুটি!</h5>";
    echo "<p>" . $e->getMessage() . "</p>";
    echo "</div>";
}

echo "</div>"; // card-body
echo "</div>"; // card
echo "</div>"; // col
echo "</div>"; // row
echo "</div>"; // container

echo "</body>";
echo "</html>";
?>