<?php
/**
 * New Features Database Migration
 * Adds tables for Transport, Hostel, Parent Portal, LMS, Backup, and Audit systems
 */

require_once __DIR__ . '/../config/database.php';

try {
    // ==================== TRANSPORT/BUS MANAGEMENT ====================
    
    // Buses table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS buses (
            id INT AUTO_INCREMENT PRIMARY KEY,
            bus_number VARCHAR(20) UNIQUE NOT NULL,
            bus_name VARCHAR(100),
            model VARCHAR(50),
            capacity INT DEFAULT 50,
            driver_name VARCHAR(100),
            driver_phone VARCHAR(20),
            conductor_name VARCHAR(100),
            conductor_phone VARCHAR(20),
            registration_number VARCHAR(50),
            insurance_expiry DATE,
            fitness_certificate_expiry DATE,
            status ENUM('active', 'maintenance', 'retired') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    
    // Routes table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS bus_routes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            route_name VARCHAR(100) NOT NULL,
            route_code VARCHAR(20) UNIQUE NOT NULL,
            start_point VARCHAR(200) NOT NULL,
            end_point VARCHAR(200) NOT NULL,
            distance_km DECIMAL(8,2),
            estimated_time TIME,
            fare_amount DECIMAL(10,2) DEFAULT 0.00,
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    
    // Route stops table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS bus_route_stops (
            id INT AUTO_INCREMENT PRIMARY KEY,
            route_id INT NOT NULL,
            stop_name VARCHAR(200) NOT NULL,
            stop_order INT NOT NULL,
            arrival_time TIME,
            departure_time TIME,
            fare_from_start DECIMAL(10,2) DEFAULT 0.00,
            landmark VARCHAR(200),
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            INDEX idx_route_order (route_id, stop_order)
        )
    ");
    
    // Bus route assignments table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS bus_assignments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            bus_id INT NOT NULL,
            route_id INT NOT NULL,
            shift ENUM('morning', 'evening', 'both') DEFAULT 'both',
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (bus_id) REFERENCES buses(id) ON DELETE CASCADE,
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            UNIQUE KEY unique_bus_route_shift (bus_id, route_id, shift)
        )
    ");
    
    // Student bus subscriptions table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS student_bus_subscriptions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            bus_id INT NOT NULL,
            route_id INT NOT NULL,
            pickup_stop_id INT,
            drop_stop_id INT,
            shift ENUM('morning', 'evening', 'both') DEFAULT 'both',
            monthly_fare DECIMAL(10,2) DEFAULT 0.00,
            subscription_date DATE,
            valid_until DATE,
            status ENUM('active', 'suspended', 'cancelled') DEFAULT 'active',
            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 (bus_id) REFERENCES buses(id) ON DELETE CASCADE,
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            FOREIGN KEY (pickup_stop_id) REFERENCES bus_route_stops(id) ON DELETE SET NULL,
            FOREIGN KEY (drop_stop_id) REFERENCES bus_route_stops(id) ON DELETE SET NULL
        )
    ");
    
    // Bus attendance table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS bus_attendance (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            bus_id INT NOT NULL,
            route_id INT NOT NULL,
            date DATE NOT NULL,
            morning_pickup BOOLEAN DEFAULT FALSE,
            morning_drop BOOLEAN DEFAULT FALSE,
            evening_pickup BOOLEAN DEFAULT FALSE,
            evening_drop BOOLEAN DEFAULT FALSE,
            marked_by INT,
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (bus_id) REFERENCES buses(id) ON DELETE CASCADE,
            FOREIGN KEY (route_id) REFERENCES bus_routes(id) ON DELETE CASCADE,
            FOREIGN KEY (marked_by) REFERENCES users(id) ON DELETE SET NULL,
            UNIQUE KEY unique_student_date (student_id, date)
        )
    ");
    
    // ==================== HOSTEL MANAGEMENT ====================
    
    // Hostel buildings table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS hostel_buildings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            building_name VARCHAR(100) NOT NULL,
            building_code VARCHAR(20) UNIQUE NOT NULL,
            address TEXT,
            total_floors INT DEFAULT 1,
            warden_name VARCHAR(100),
            warden_phone VARCHAR(20),
            warden_email VARCHAR(100),
            status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    
    // Hostel rooms table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS hostel_rooms (
            id INT AUTO_INCREMENT PRIMARY KEY,
            building_id INT NOT NULL,
            room_number VARCHAR(20) NOT NULL,
            floor_number INT DEFAULT 1,
            room_type ENUM('single', 'double', 'triple', 'dormitory') DEFAULT 'double',
            capacity INT DEFAULT 2,
            current_occupancy INT DEFAULT 0,
            amenities TEXT,
            monthly_rent DECIMAL(10,2) DEFAULT 0.00,
            status ENUM('available', 'occupied', 'maintenance', 'reserved') DEFAULT 'available',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (building_id) REFERENCES hostel_buildings(id) ON DELETE CASCADE,
            UNIQUE KEY unique_building_room (building_id, room_number)
        )
    ");
    
    // Hostel allocations table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS hostel_allocations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            room_id INT NOT NULL,
            allocation_date DATE NOT NULL,
            valid_until DATE,
            monthly_rent DECIMAL(10,2) DEFAULT 0.00,
            security_deposit DECIMAL(10,2) DEFAULT 0.00,
            status ENUM('active', 'vacated', 'suspended') DEFAULT 'active',
            vacated_date DATE,
            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 (room_id) REFERENCES hostel_rooms(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_allocation (student_id, status)
        )
    ");
    
    // Hostel fees table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS hostel_fees (
            id INT AUTO_INCREMENT PRIMARY KEY,
            allocation_id INT NOT NULL,
            student_id INT NOT NULL,
            month_year VARCHAR(10) NOT NULL,
            rent_amount DECIMAL(10,2) NOT NULL,
            electricity_charge DECIMAL(10,2) DEFAULT 0.00,
            water_charge DECIMAL(10,2) DEFAULT 0.00,
            other_charges DECIMAL(10,2) DEFAULT 0.00,
            total_amount DECIMAL(10,2) NOT NULL,
            paid_amount DECIMAL(10,2) DEFAULT 0.00,
            due_date DATE,
            paid_date DATE,
            status ENUM('pending', 'partial', 'paid', 'overdue') DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (allocation_id) REFERENCES hostel_allocations(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_month (student_id, month_year)
        )
    ");
    
    // ==================== PARENT PORTAL ====================
    
    // Parents table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS parents (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNIQUE,
            father_name VARCHAR(100) NOT NULL,
            father_phone VARCHAR(20),
            father_email VARCHAR(100),
            father_occupation VARCHAR(100),
            mother_name VARCHAR(100),
            mother_phone VARCHAR(20),
            mother_email VARCHAR(100),
            mother_occupation VARCHAR(100),
            guardian_name VARCHAR(100),
            guardian_phone VARCHAR(20),
            guardian_email VARCHAR(100),
            guardian_relation VARCHAR(50),
            address TEXT,
            emergency_contact VARCHAR(20),
            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
        )
    ");
    
    // Parent-Student relationship table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS parent_students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            parent_id INT NOT NULL,
            student_id INT NOT NULL,
            relation ENUM('father', 'mother', 'guardian', 'other') NOT NULL,
            is_primary BOOLEAN DEFAULT FALSE,
            can_pickup BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            UNIQUE KEY unique_parent_student (parent_id, student_id)
        )
    ");
    
    // Parent notifications table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS parent_notifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            parent_id INT NOT NULL,
            student_id INT,
            title VARCHAR(200) NOT NULL,
            message TEXT NOT NULL,
            type ENUM('attendance', 'fee', 'exam', 'general', 'homework', 'emergency') DEFAULT 'general',
            is_read BOOLEAN DEFAULT FALSE,
            read_at TIMESTAMP NULL,
            sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            INDEX idx_parent_read (parent_id, is_read)
        )
    ");
    
    // Parent complaints/suggestions table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS parent_complaints (
            id INT AUTO_INCREMENT PRIMARY KEY,
            parent_id INT NOT NULL,
            student_id INT,
            subject VARCHAR(200) NOT NULL,
            message TEXT NOT NULL,
            category ENUM('academic', 'behavior', 'facilities', 'transport', 'fee', 'other') DEFAULT 'other',
            status ENUM('pending', 'in_progress', 'resolved', 'rejected') DEFAULT 'pending',
            submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            resolved_at TIMESTAMP NULL,
            resolved_by INT,
            resolution_notes TEXT,
            FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL,
            FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    
    // ==================== ONLINE CLASS/LMS ====================
    
    // Online courses table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS courses (
            id INT AUTO_INCREMENT PRIMARY KEY,
            course_code VARCHAR(20) UNIQUE NOT NULL,
            course_name VARCHAR(200) NOT NULL,
            description TEXT,
            class_id INT,
            subject_id INT,
            teacher_id INT,
            thumbnail VARCHAR(255),
            start_date DATE,
            end_date DATE,
            status ENUM('draft', 'active', 'completed', 'archived') DEFAULT 'draft',
            created_by INT,
            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 (subject_id) REFERENCES subjects(id) ON DELETE SET NULL,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    
    // Course modules table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS course_modules (
            id INT AUTO_INCREMENT PRIMARY KEY,
            course_id INT NOT NULL,
            module_name VARCHAR(200) NOT NULL,
            module_order INT DEFAULT 1,
            description TEXT,
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
        )
    ");
    
    // Course lessons table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS course_lessons (
            id INT AUTO_INCREMENT PRIMARY KEY,
            module_id INT NOT NULL,
            lesson_title VARCHAR(200) NOT NULL,
            lesson_order INT DEFAULT 1,
            content_type ENUM('video', 'pdf', 'text', 'quiz', 'assignment', 'live_class') NOT NULL,
            content_url VARCHAR(500),
            content_text TEXT,
            duration_minutes INT,
            is_free BOOLEAN DEFAULT FALSE,
            status ENUM('active', 'inactive', 'draft') DEFAULT 'draft',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (module_id) REFERENCES course_modules(id) ON DELETE CASCADE
        )
    ");
    
    // Live classes table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS live_classes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            course_id INT,
            teacher_id INT NOT NULL,
            class_id INT,
            subject_id INT,
            topic VARCHAR(200) NOT NULL,
            description TEXT,
            meeting_link VARCHAR(500),
            meeting_id VARCHAR(100),
            passcode VARCHAR(50),
            scheduled_at DATETIME NOT NULL,
            duration_minutes INT DEFAULT 60,
            status ENUM('scheduled', 'ongoing', 'completed', 'cancelled') DEFAULT 'scheduled',
            recording_url VARCHAR(500),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL
        )
    ");
    
    // Student enrollments table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS student_enrollments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            course_id INT NOT NULL,
            enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at TIMESTAMP NULL,
            progress_percent INT DEFAULT 0,
            status ENUM('enrolled', 'in_progress', 'completed', 'dropped') DEFAULT 'enrolled',
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_course (student_id, course_id)
        )
    ");
    
    // Student lesson progress table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS student_lesson_progress (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            lesson_id INT NOT NULL,
            is_completed BOOLEAN DEFAULT FALSE,
            completed_at TIMESTAMP NULL,
            time_spent_seconds INT DEFAULT 0,
            last_accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_lesson (student_id, lesson_id)
        )
    ");
    
    // Assignments table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS lms_assignments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            course_id INT NOT NULL,
            lesson_id INT,
            title VARCHAR(200) NOT NULL,
            description TEXT,
            instructions TEXT,
            max_marks INT DEFAULT 100,
            pass_marks INT DEFAULT 40,
            due_date DATETIME,
            allow_late_submission BOOLEAN DEFAULT FALSE,
            late_submission_penalty_percent INT DEFAULT 0,
            status ENUM('draft', 'published', 'closed') DEFAULT 'draft',
            created_by INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
            FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE SET NULL,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    
    // Assignment submissions table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS assignment_submissions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            assignment_id INT NOT NULL,
            student_id INT NOT NULL,
            submission_text TEXT,
            attachment_url VARCHAR(500),
            submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            is_late BOOLEAN DEFAULT FALSE,
            marks_obtained DECIMAL(5,2),
            grade VARCHAR(5),
            feedback TEXT,
            graded_by INT,
            graded_at TIMESTAMP NULL,
            status ENUM('submitted', 'graded', 'returned') DEFAULT 'submitted',
            FOREIGN KEY (assignment_id) REFERENCES lms_assignments(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (graded_by) REFERENCES users(id) ON DELETE SET NULL,
            UNIQUE KEY unique_assignment_student (assignment_id, student_id)
        )
    ");
    
    // ==================== AUDIT TRAIL ====================
    
    // Audit log table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS audit_log (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            user_id INT,
            user_role VARCHAR(20),
            action VARCHAR(100) NOT NULL,
            table_name VARCHAR(50),
            record_id INT,
            old_values JSON,
            new_values JSON,
            ip_address VARCHAR(45),
            user_agent VARCHAR(255),
            session_id VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
            INDEX idx_user_time (user_id, created_at),
            INDEX idx_action (action),
            INDEX idx_table_record (table_name, record_id)
        )
    ");
    
    // Audit log archive table (for old records)
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS audit_log_archive (
            id BIGINT PRIMARY KEY,
            user_id INT,
            user_role VARCHAR(20),
            action VARCHAR(100) NOT NULL,
            table_name VARCHAR(50),
            record_id INT,
            old_values JSON,
            new_values JSON,
            ip_address VARCHAR(45),
            user_agent VARCHAR(255),
            session_id VARCHAR(255),
            created_at TIMESTAMP,
            archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_created_at (created_at)
        )
    ");
    
    // Login attempts log
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS login_attempts (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50),
            ip_address VARCHAR(45) NOT NULL,
            user_agent VARCHAR(255),
            attempt_status ENUM('success', 'failed') NOT NULL,
            failure_reason VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ip_time (ip_address, created_at),
            INDEX idx_username (username)
        )
    ");
    
    // Login attempts archive table (for old records)
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS login_attempts_archive (
            id BIGINT PRIMARY KEY,
            username VARCHAR(50),
            ip_address VARCHAR(45) NOT NULL,
            user_agent VARCHAR(255),
            attempt_status ENUM('success', 'failed') NOT NULL,
            failure_reason VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_created_at (created_at),
            INDEX idx_archived_at (archived_at)
        )
    ");
    
    // ==================== AUTOMATED BACKUP ====================
    
    // Backup configuration table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS backup_config (
            id INT AUTO_INCREMENT PRIMARY KEY,
            backup_type ENUM('database', 'files', 'full') DEFAULT 'database',
            frequency ENUM('hourly', 'daily', 'weekly', 'monthly') DEFAULT 'daily',
            backup_time TIME DEFAULT '02:00:00',
            retention_days INT DEFAULT 30,
            storage_location VARCHAR(500),
            storage_type ENUM('local', 'ftp', 's3', 'gdrive') DEFAULT 'local',
            credentials_encrypted TEXT,
            is_active BOOLEAN DEFAULT TRUE,
            last_backup_at TIMESTAMP NULL,
            next_backup_at TIMESTAMP NULL,
            created_by INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        )
    ");
    
    // Backup history table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS backup_history (
            id INT AUTO_INCREMENT PRIMARY KEY,
            config_id INT,
            backup_type ENUM('database', 'files', 'full') NOT NULL,
            file_name VARCHAR(255),
            file_path VARCHAR(500),
            file_size BIGINT,
            status ENUM('running', 'completed', 'failed', 'partial') NOT NULL,
            start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            end_time TIMESTAMP NULL,
            error_message TEXT,
            tables_backed JSON,
            retention_until DATE,
            is_deleted BOOLEAN DEFAULT FALSE,
            FOREIGN KEY (config_id) REFERENCES backup_config(id) ON DELETE SET NULL,
            INDEX idx_status_time (status, start_time)
        )
    ");
    
    // System health check table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS system_health (
            id INT AUTO_INCREMENT PRIMARY KEY,
            check_name VARCHAR(100) NOT NULL,
            check_type ENUM('disk_space', 'memory', 'cpu', 'database', 'backup', 'security') NOT NULL,
            status ENUM('healthy', 'warning', 'critical') DEFAULT 'healthy',
            current_value VARCHAR(100),
            threshold_value VARCHAR(100),
            details TEXT,
            checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_check_time (check_type, checked_at)
        )
    ");
    
    echo "✅ সব নতুন features এর টেবিল সফলভাবে তৈরি হয়েছে!\n\n";
    echo "তৈরি হওয়া টেবিলগুলো:\n";
    echo "📦 Transport/Bus Management:\n";
    echo "   - buses, bus_routes, bus_route_stops, bus_assignments, student_bus_subscriptions, bus_attendance\n\n";
    echo "🏠 Hostel Management:\n";
    echo "   - hostel_buildings, hostel_rooms, hostel_allocations, hostel_fees\n\n";
    echo "👨‍👩‍👧 Parent Portal:\n";
    echo "   - parents, parent_students, parent_notifications, parent_complaints\n\n";
    echo "📚 Online Class/LMS:\n";
    echo "   - courses, course_modules, course_lessons, live_classes, student_enrollments, student_lesson_progress, lms_assignments, assignment_submissions\n\n";
    echo "🔍 Audit Trail:\n";
    echo "   - audit_log, audit_log_archive, login_attempts\n\n";
    echo "💾 Automated Backup:\n";
    echo "   - backup_config, backup_history, system_health\n\n";
    
} catch(PDOException $e) {
    die("❌ Database error: " . $e->getMessage());
}
?>
