<?php
// Database Setup Script for School Management System

// Database configuration
$host = 'localhost';
$username = 'root';
$password = 'root';
$dbname = 'school_management';

try {
    // Create database connection
    $pdo = new PDO("mysql:host=$host;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h2>স্কুল ম্যানেজমেন্ট সিস্টেম ডাটাবেস সেটআপ</h2>";
    
    // Create database
    echo "<p>ডাটাবেস তৈরি করা হচ্ছে...</p>";
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "<p style='color: green;'>✓ ডাটাবেস তৈরি সফল</p>";
    
    // Use the database
    $pdo->exec("USE `$dbname`");
    
    // Create tables
    echo "<p>টেবিল তৈরি করা হচ্ছে...</p>";
    
    // Users table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            role ENUM('admin', 'teacher', 'student', 'parent') NOT NULL,
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "<p style='color: green;'>✓ Users টেবিল তৈরি সফল</p>";
    
    // Classes table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS classes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            section VARCHAR(10) NOT NULL,
            class_teacher_id INT,
            capacity INT DEFAULT 40,
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_class_section (name, section)
        )
    ");
    echo "<p style='color: green;'>✓ Classes টেবিল তৈরি সফল</p>";
    
    // Subjects table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS subjects (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            code VARCHAR(20) UNIQUE NOT NULL,
            description TEXT,
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "<p style='color: green;'>✓ Subjects টেবিল তৈরি সফল</p>";
    
    // Teachers table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS teachers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNIQUE,
            employee_id VARCHAR(20) UNIQUE NOT NULL,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            phone VARCHAR(20),
            address TEXT,
            date_of_birth DATE,
            gender ENUM('male', 'female', 'other'),
            qualification VARCHAR(200),
            experience_years INT DEFAULT 0,
            joining_date DATE,
            salary DECIMAL(10,2),
            photo VARCHAR(255),
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    echo "<p style='color: green;'>✓ Teachers টেবিল তৈরি সফল</p>";
    
    // Students table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNIQUE,
            student_id VARCHAR(20) UNIQUE NOT NULL,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            email VARCHAR(100),
            phone VARCHAR(20),
            address TEXT,
            date_of_birth DATE,
            gender ENUM('male', 'female', 'other'),
            class_id INT,
            roll_number INT,
            admission_date DATE,
            guardian_name VARCHAR(100),
            guardian_phone VARCHAR(20),
            guardian_email VARCHAR(100),
            guardian_address TEXT,
            photo VARCHAR(255),
            status ENUM('active', 'inactive', 'graduated', 'transferred') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
            UNIQUE KEY unique_class_roll (class_id, roll_number)
        )
    ");
    echo "<p style='color: green;'>✓ Students টেবিল তৈরি সফল</p>";
    
    // Class subjects mapping
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS class_subjects (
            id INT AUTO_INCREMENT PRIMARY KEY,
            class_id INT NOT NULL,
            subject_id INT NOT NULL,
            teacher_id INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
            UNIQUE KEY unique_class_subject (class_id, subject_id)
        )
    ");
    echo "<p style='color: green;'>✓ Class Subjects টেবিল তৈরি সফল</p>";
    
    // Student subjects mapping (for individual subject selection)
    $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 "<p style='color: green;'>✓ Student Subjects টেবিল তৈরি সফল</p>";
    
    // Exams table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS exams (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            class_id INT NOT NULL,
            exam_date DATE NOT NULL,
            start_time TIME,
            end_time TIME,
            total_marks INT DEFAULT 100,
            pass_marks INT DEFAULT 40,
            description TEXT,
            status ENUM('scheduled', 'ongoing', 'completed', 'cancelled') DEFAULT 'scheduled',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
        )
    ");
    echo "<p style='color: green;'>✓ Exams টেবিল তৈরি সফল</p>";
    
    // Exam subjects
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS exam_subjects (
            id INT AUTO_INCREMENT PRIMARY KEY,
            exam_id INT NOT NULL,
            subject_id INT NOT NULL,
            max_marks INT DEFAULT 100,
            pass_marks INT DEFAULT 40,
            exam_date DATE,
            start_time TIME,
            end_time TIME,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            UNIQUE KEY unique_exam_subject (exam_id, subject_id)
        )
    ");
    echo "<p style='color: green;'>✓ Exam Subjects টেবিল তৈরি সফল</p>";
    
    // Results table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS results (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            exam_id INT NOT NULL,
            subject_id INT NOT NULL,
            marks_obtained DECIMAL(5,2),
            grade VARCHAR(5),
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_exam_subject (student_id, exam_id, subject_id)
        )
    ");
    echo "<p style='color: green;'>✓ Results টেবিল তৈরি সফল</p>";
    
    // Attendance table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS attendance (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            class_id INT NOT NULL,
            date DATE NOT NULL,
            status ENUM('present', 'absent', 'late', 'excused') NOT NULL,
            remarks TEXT,
            marked_by INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            FOREIGN KEY (marked_by) REFERENCES users(id) ON DELETE SET NULL,
            UNIQUE KEY unique_student_date (student_id, date)
        )
    ");
    echo "<p style='color: green;'>✓ Attendance টেবিল তৈরি সফল</p>";
    
    // Fees table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS fees (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            fee_type VARCHAR(50) NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            due_date DATE NOT NULL,
            paid_amount DECIMAL(10,2) DEFAULT 0,
            paid_date DATE,
            status ENUM('pending', 'partial', 'paid', 'overdue') DEFAULT 'pending',
            academic_year VARCHAR(10),
            month VARCHAR(20),
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
        )
    ");
    echo "<p style='color: green;'>✓ Fees টেবিল তৈরি সফল</p>";
    
    // Fee payments table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS fee_payments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            fee_id INT NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            payment_method ENUM('cash', 'bank', 'online', 'cheque') DEFAULT 'cash',
            transaction_id VARCHAR(100),
            payment_date DATE NOT NULL,
            received_by INT,
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (fee_id) REFERENCES fees(id) ON DELETE CASCADE,
            FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    echo "<p style='color: green;'>✓ Fee Payments টেবিল তৈরি সফল</p>";
    
    // Library books table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS library_books (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            author VARCHAR(100),
            isbn VARCHAR(20) UNIQUE,
            category VARCHAR(50),
            publisher VARCHAR(100),
            publication_year YEAR,
            total_copies INT DEFAULT 1,
            available_copies INT DEFAULT 1,
            location VARCHAR(50),
            status ENUM('active', 'inactive', 'damaged', 'lost') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "<p style='color: green;'>✓ Library Books টেবিল তৈরি সফল</p>";
    
    // Library book issues table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS library_issues (
            id INT AUTO_INCREMENT PRIMARY KEY,
            book_id INT NOT NULL,
            student_id INT NOT NULL,
            issue_date DATE NOT NULL,
            due_date DATE NOT NULL,
            return_date DATE,
            fine_amount DECIMAL(8,2) DEFAULT 0,
            status ENUM('issued', 'returned', 'overdue', 'lost') DEFAULT 'issued',
            issued_by INT,
            returned_to INT,
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (book_id) REFERENCES library_books(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY (returned_to) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    echo "<p style='color: green;'>✓ Library Issues টেবিল তৈরি সফল</p>";
    
    // Notices table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS notices (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            content TEXT NOT NULL,
            target_audience ENUM('all', 'students', 'teachers', 'parents', 'specific_class') DEFAULT 'all',
            class_id INT,
            priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
            publish_date DATE NOT NULL,
            expiry_date DATE,
            attachment VARCHAR(255),
            created_by INT,
            status ENUM('draft', 'active', 'expired', 'archived') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    echo "<p style='color: green;'>✓ Notices টেবিল তৈরি সফল</p>";
    
    // Timetable table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS timetable (
            id INT AUTO_INCREMENT PRIMARY KEY,
            class_id INT NOT NULL,
            subject_id INT NOT NULL,
            teacher_id INT NOT NULL,
            day_of_week ENUM('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday') NOT NULL,
            start_time TIME NOT NULL,
            end_time TIME NOT NULL,
            room_number VARCHAR(20),
            academic_year VARCHAR(10),
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
        )
    ");
    echo "<p style='color: green;'>✓ Timetable টেবিল তৈরি সফল</p>";
    
    // Insert default data
    echo "<p>ডিফল্ট ডেটা যোগ করা হচ্ছে...</p>";
    
    // Check if admin user exists
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE role = 'admin'");
    $stmt->execute();
    $admin_count = $stmt->fetchColumn();
    
    if ($admin_count == 0) {
        // Create default admin user
        $admin_password = password_hash('admin123', PASSWORD_DEFAULT);
        $stmt = $pdo->prepare("INSERT INTO users (username, password, email, role) VALUES ('admin', ?, 'admin@school.com', 'admin')");
        $stmt->execute([$admin_password]);
        echo "<p style='color: green;'>✓ ডিফল্ট অ্যাডমিন ইউজার তৈরি (admin/admin123)</p>";
        
        // Insert default classes
        $classes = [
            ['প্রথম শ্রেণী', 'ক'],
            ['দ্বিতীয় শ্রেণী', 'ক'],
            ['তৃতীয় শ্রেণী', 'ক'],
            ['চতুর্থ শ্রেণী', 'ক'],
            ['পঞ্চম শ্রেণী', 'ক'],
            ['ষষ্ঠ শ্রেণী', 'ক'],
            ['সপ্তম শ্রেণী', 'ক'],
            ['অষ্টম শ্রেণী', 'ক'],
            ['নবম শ্রেণী', 'ক'],
            ['দশম শ্রেণী', 'ক']
        ];
        
        foreach ($classes as $class) {
            $stmt = $pdo->prepare("INSERT INTO classes (name, section) VALUES (?, ?)");
            $stmt->execute($class);
        }
        echo "<p style='color: green;'>✓ ডিফল্ট ক্লাস যোগ করা হয়েছে</p>";
        
        // Insert default subjects
        $subjects = [
            ['বাংলা', 'BAN'],
            ['ইংরেজি', 'ENG'],
            ['গণিত', 'MATH'],
            ['বিজ্ঞান', 'SCI'],
            ['সমাজবিজ্ঞান', 'SOC'],
            ['ধর্ম', 'REL'],
            ['শারীরিক শিক্ষা', 'PE'],
            ['কম্পিউটার', 'COMP']
        ];
        
        foreach ($subjects as $subject) {
            $stmt = $pdo->prepare("INSERT INTO subjects (name, code) VALUES (?, ?)");
            $stmt->execute($subject);
        }
        echo "<p style='color: green;'>✓ ডিফল্ট বিষয় যোগ করা হয়েছে</p>";
        
        // Assign subjects to classes (sample data)
        $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]], // পঞ্চম শ্রেণী: বাংলা, ইংরেজি, গণিত, বিজ্ঞান, সমাজবিজ্ঞান, ধর্ম, শারীরিক শিক্ষা
            
            // ষষ্ঠ-অষ্টম শ্রেণী (মাধ্যমিক)
            [6, [1, 2, 3, 4, 5, 6, 7, 8]], // ষষ্ঠ শ্রেণী: সব বিষয়
            [7, [1, 2, 3, 4, 5, 6, 7, 8]], // সপ্তম শ্রেণী: সব বিষয়
            [8, [1, 2, 3, 4, 5, 6, 7, 8]], // অষ্টম শ্রেণী: সব বিষয়
            
            // নবম-দশম শ্রেণী (উচ্চ মাধ্যমিক)
            [9, [1, 2, 3, 4, 5, 6, 7, 8]], // নবম শ্রেণী: সব বিষয়
            [10, [1, 2, 3, 4, 5, 6, 7, 8]], // দশম শ্রেণী: সব বিষয়
        ];
        
        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 "<p style='color: green;'>✓ ক্লাস-বিষয় ম্যাপিং যোগ করা হয়েছে</p>";
        
        // Insert sample notice
        $stmt = $pdo->prepare("
            INSERT INTO notices (title, content, publish_date, created_by) 
            VALUES ('স্বাগতম', 'স্কুল ম্যানেজমেন্ট সিস্টেমে আপনাদের স্বাগতম। এই সিস্টেমের মাধ্যমে আপনি সকল শিক্ষা কার্যক্রম পরিচালনা করতে পারবেন।', CURDATE(), 1)
        ");
        $stmt->execute();
        echo "<p style='color: green;'>✓ নমুনা নোটিশ যোগ করা হয়েছে</p>";
    } else {
        echo "<p style='color: orange;'>⚠ অ্যাডমিন ইউজার ইতিমধ্যে বিদ্যমান</p>";
    }
    
    // Create upload directories
    $upload_dirs = [
        'uploads',
        'uploads/students',
        'uploads/teachers',
        'uploads/notices',
        'uploads/library'
    ];
    
    foreach ($upload_dirs as $dir) {
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
            echo "<p style='color: green;'>✓ $dir ডিরেক্টরি তৈরি</p>";
        }
    }
    
    echo "<h3 style='color: green;'>✅ সেটআপ সম্পন্ন!</h3>";
    echo "<p><strong>লগইন তথ্য:</strong></p>";
    echo "<ul>";
    echo "<li>ইউজারনেম: <strong>admin</strong></li>";
    echo "<li>পাসওয়ার্ড: <strong>admin123</strong></li>";
    echo "</ul>";
    echo "<p><a href='login.php' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>লগইন করুন</a></p>";
    
} catch (PDOException $e) {
    echo "<p style='color: red;'>❌ ত্রুটি: " . $e->getMessage() . "</p>";
}
?>