<?php
// إخفاء الأخطاء في واجهة المستخدم
error_reporting(0);
ini_set('display_errors', 0);

require_once 'db_connect.php';

$step = 1;
$message = '';
$error = false;

// عندما يضغط العميل على "بناء النظام"
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['start_install'])) {
    
    // =========================================================================
    // 🚀 الهيكل البرمجي الكامل لقاعدة البيانات (45 جدول) 🚀
    // =========================================================================
    $sql_schema = <<<SQL
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `academic_grades` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `level_id` int(11) NOT NULL,
  `grade_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `academic_levels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) NOT NULL,
  `level_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `academic_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(150) DEFAULT NULL,
  `teacher_name` varchar(150) DEFAULT NULL,
  `req_type` varchar(50) DEFAULT NULL,
  `subject_class` varchar(255) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `academic_sections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade_id` int(11) NOT NULL,
  `section_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `academic_subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade_id` int(11) NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `academy_media_library` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) NOT NULL DEFAULT 0,
  `subject` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `media_type` varchar(20) NOT NULL,
  `source_url` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `academy_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_name` varchar(255) NOT NULL,
  `full_html` longtext NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_name` (`page_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `acc_employee_contracts` (
  `contract_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_type` enum('teacher','admin','accountant','staff') NOT NULL,
  `reference_id` int(11) NOT NULL,
  `base_salary` decimal(10,2) NOT NULL,
  `currency` varchar(10) DEFAULT 'USD',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('active','terminated') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`contract_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_expenses` (
  `expense_id` int(11) NOT NULL AUTO_INCREMENT,
  `voucher_type` enum('expense','income') DEFAULT 'expense',
  `school_id` int(11) DEFAULT NULL,
  `category_name` varchar(100) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `expense_date` date NOT NULL,
  `description` text DEFAULT NULL,
  `invoice_attachment` varchar(255) DEFAULT NULL,
  `recorded_by` varchar(100) DEFAULT NULL,
  `student_id` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`expense_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_fees_plans` (
  `plan_id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) DEFAULT NULL,
  `stage_name` varchar(100) NOT NULL,
  `department_name` varchar(100) DEFAULT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_financial_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(150) DEFAULT NULL,
  `category_name` varchar(100) NOT NULL,
  `category_type` enum('income','expense','both') DEFAULT 'both',
  `is_system_default` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique_category` (`school_name`,`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_installments` (
  `installment_id` int(11) NOT NULL AUTO_INCREMENT,
  `financial_id` int(11) NOT NULL,
  `due_date` date NOT NULL,
  `amount_due` decimal(10,2) NOT NULL,
  `is_paid` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`installment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_payments` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `financial_id` int(11) NOT NULL,
  `installment_id` int(11) DEFAULT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `payment_date` datetime DEFAULT current_timestamp(),
  `payment_method` varchar(50) DEFAULT 'cash',
  `receipt_number` varchar(50) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `received_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`payment_id`),
  UNIQUE KEY `receipt_number` (`receipt_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_pricing_matrix` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(150) DEFAULT NULL,
  `stage_name` varchar(100) NOT NULL,
  `base_price` decimal(15,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_stage_school` (`school_name`,`stage_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_salary_payments` (
  `sp_id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `payment_month` varchar(7) NOT NULL,
  `base_amount` decimal(10,2) NOT NULL,
  `bonuses` decimal(10,2) DEFAULT 0.00,
  `deductions` decimal(10,2) DEFAULT 0.00,
  `net_paid` decimal(10,2) NOT NULL,
  `payment_date` datetime DEFAULT current_timestamp(),
  `payment_method` varchar(50) DEFAULT 'cash',
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`sp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_student_financials` (
  `financial_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(100) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `discount_percentage` int(11) DEFAULT 0,
  `discount_amount` decimal(10,2) DEFAULT 0.00,
  `net_total` decimal(10,2) NOT NULL,
  `total_paid` decimal(10,2) DEFAULT 0.00,
  `total_refunded` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','completed','defaulted') DEFAULT 'active',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`financial_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `acc_treasury_setup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(150) DEFAULT NULL,
  `opening_balance` decimal(15,2) DEFAULT 0.00,
  `currency_name` varchar(50) DEFAULT 'د.ع',
  `last_updated` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activation_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) NOT NULL,
  `token_code` varchar(50) NOT NULL,
  `is_used` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token_code` (`token_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `archived_lessons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `lesson_content` longtext DEFAULT NULL,
  `pdf_file` varchar(255) DEFAULT NULL,
  `video_file` varchar(255) DEFAULT NULL,
  `video_url` text NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT current_timestamp(),
  `class_name` varchar(100) NOT NULL DEFAULT 'عام',
  `department` varchar(100) DEFAULT 'عام',
  `branch` varchar(100) DEFAULT 'الكل',
  `status` varchar(50) NOT NULL DEFAULT 'published',
  `teacher_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `school_name` varchar(255) DEFAULT 'المدرسة العامة',
  PRIMARY KEY (`id`),
  KEY `teacher_name` (`teacher_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(50) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  `lesson_id` varchar(255) DEFAULT NULL,
  `arrival_time` datetime DEFAULT NULL,
  `teacher_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendance_date` date DEFAULT NULL,
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'absent',
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `lesson_id` (`lesson_id`),
  KEY `student_id_2` (`student_id`),
  KEY `school_name` (`school_name`),
  KEY `attendance_date` (`attendance_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(50) DEFAULT NULL,
  `section` varchar(10) DEFAULT NULL,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `class_sections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stage_name` varchar(100) NOT NULL,
  `section_name` varchar(50) NOT NULL,
  `status` varchar(20) DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `control_grades` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(100) NOT NULL,
  `school_name` varchar(255) NOT NULL,
  `teacher_name` varchar(255) NOT NULL,
  `subject_name` varchar(150) NOT NULL,
  `stage` varchar(100) NOT NULL,
  `department` varchar(100) NOT NULL,
  `section` varchar(100) NOT NULL,
  `month_1` decimal(5,2) DEFAULT NULL,
  `month_2` decimal(5,2) DEFAULT NULL,
  `month_3` decimal(5,2) DEFAULT NULL,
  `term_1` decimal(5,2) DEFAULT NULL,
  `orig_term_1` decimal(5,2) DEFAULT NULL,
  `mid_year` decimal(5,2) DEFAULT NULL,
  `orig_mid_year` decimal(5,2) DEFAULT NULL,
  `month_4` decimal(5,2) DEFAULT NULL,
  `month_5` decimal(5,2) DEFAULT NULL,
  `month_6` decimal(5,2) DEFAULT NULL,
  `term_2` decimal(5,2) DEFAULT NULL,
  `orig_term_2` decimal(5,2) DEFAULT NULL,
  `annual_effort` decimal(5,2) DEFAULT NULL,
  `final_exam` decimal(5,2) DEFAULT NULL,
  `final_grade` decimal(5,2) DEFAULT NULL,
  `pass_score` decimal(5,2) DEFAULT 50.00,
  `admin_grace` decimal(5,2) DEFAULT 0.00,
  `makeup_exam` decimal(5,2) DEFAULT NULL,
  `final_after_makeup` decimal(5,2) DEFAULT NULL,
  `oral_data` text DEFAULT NULL,
  `is_submitted` tinyint(1) DEFAULT 0,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `final_oral` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `std_sub_term` (`student_id`,`subject_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `course_activations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `access_code` varchar(50) NOT NULL,
  `course_name` varchar(150) NOT NULL,
  `device_info` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `activated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `teacher_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `access_code` (`access_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `generated_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stage_name` varchar(100) NOT NULL,
  `section_name` varchar(50) NOT NULL,
  `day_of_week` varchar(20) NOT NULL,
  `period_number` int(11) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  `teacher_name` varchar(100) NOT NULL,
  `is_published` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `school_name` varchar(100) DEFAULT NULL,
  `department_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `join_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_slug` varchar(100) DEFAULT NULL,
  `student_name` varchar(150) DEFAULT NULL,
  `student_phone` varchar(50) DEFAULT NULL,
  `stage` varchar(100) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `request_date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `lessons_archive` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `video_title` varchar(255) NOT NULL,
  `video_path` text NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `class_name` varchar(100) NOT NULL DEFAULT 'عام',
  `dept` varchar(100) DEFAULT NULL,
  `section` varchar(100) DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'published',
  `teacher_id` varchar(50) DEFAULT NULL,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `teacher_name` (`teacher_name`),
  KEY `teacher_id` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `licenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `license_key` varchar(100) NOT NULL,
  `school_name` varchar(200) NOT NULL,
  `domain` varchar(150) NOT NULL,
  `status` enum('active','expired','suspended') NOT NULL DEFAULT 'active',
  `expire_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `license_key` (`license_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `live_alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` varchar(100) DEFAULT NULL,
  `stage` varchar(100) DEFAULT NULL,
  `dept` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `live_alerts_v2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` varchar(100) DEFAULT NULL,
  `target_stage` varchar(100) DEFAULT NULL,
  `target_dept` varchar(100) DEFAULT NULL,
  `specific_students` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `live_chat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` varchar(100) DEFAULT NULL,
  `sender_name` varchar(255) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `live_waiting_room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` varchar(100) DEFAULT NULL,
  `student_id` varchar(100) DEFAULT NULL,
  `student_name` varchar(255) DEFAULT NULL,
  `status` enum('pending','accepted','rejected') DEFAULT 'pending',
  `request_time` datetime DEFAULT current_timestamp(),
  `can_mic` int(11) DEFAULT 1,
  `can_cam` int(11) DEFAULT 1,
  `can_draw` int(11) DEFAULT 0,
  `is_kicked` int(11) DEFAULT 0,
  `hand_raised` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `live_whiteboard_sync` (
  `teacher_id` varchar(100) NOT NULL,
  `payload_json` longtext DEFAULT NULL,
  `student_payload` longtext DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `menus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `url` varchar(255) NOT NULL,
  `parent_id` int(11) DEFAULT 0,
  `order_no` int(11) DEFAULT 0,
  `location` enum('header','footer') DEFAULT 'header',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `newsletter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) DEFAULT NULL,
  `subscribed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `sender_name` varchar(255) DEFAULT NULL,
  `student_id` varchar(50) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_title` varchar(255) NOT NULL,
  `page_slug` varchar(255) NOT NULL,
  `content` text DEFAULT NULL,
  `placement` enum('header','footer','both','none') DEFAULT 'header',
  `status` enum('active','draft') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `parents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `parent_name` varchar(150) DEFAULT NULL,
  `phone` varchar(50) NOT NULL COMMENT 'يستخدم كاسم مستخدم للدخول',
  `password` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `username` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_school_phone` (`school_name`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `parent_student_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `student_name` varchar(255) NOT NULL,
  `linked_at` timestamp NULL DEFAULT current_timestamp(),
  `student_id` varchar(100) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  `school_prefix` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `profile_update_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(50) NOT NULL,
  `old_name` varchar(255) NOT NULL,
  `new_name` varchar(255) NOT NULL,
  `old_stage` varchar(255) NOT NULL,
  `new_stage` varchar(255) NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `request_date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `schedules_archive` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) NOT NULL,
  `school_name` varchar(255) DEFAULT 'المدرسة العامة',
  `schedule_title` varchar(255) NOT NULL,
  `table_html` longtext NOT NULL,
  `target_stage` varchar(100) NOT NULL,
  `target_dept` varchar(100) NOT NULL,
  `target_branch` varchar(100) NOT NULL,
  `target_student` varchar(100) DEFAULT 'all',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `schedule_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stage_name` varchar(100) NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  `weekly_sessions` int(11) NOT NULL,
  `primary_teacher_id` varchar(100) NOT NULL,
  `duration_minutes` int(11) DEFAULT 45,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `school_name` varchar(100) DEFAULT NULL,
  `department_name` varchar(100) DEFAULT NULL,
  `section_name` varchar(100) DEFAULT NULL,
  `teacher_name` varchar(100) DEFAULT NULL,
  `allowed_days` varchar(255) DEFAULT '',
  `allowed_periods` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` varchar(50) DEFAULT NULL,
  `school_name` varchar(150) DEFAULT NULL,
  `custom_domain` varchar(255) DEFAULT NULL,
  `school_logo` varchar(255) DEFAULT 'images/icon.png',
  `manager_name` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `whatsapp_phone` varchar(50) DEFAULT NULL,
  `whatsapp_provider` varchar(50) DEFAULT NULL,
  `whatsapp_api_url` varchar(255) DEFAULT NULL,
  `whatsapp_api_token` varchar(255) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `account_status` enum('pending','active') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_active` datetime DEFAULT NULL,
  `working_days` varchar(255) DEFAULT 'Sunday,Monday,Tuesday,Wednesday,Thursday',
  `assembly_end_time` time DEFAULT '08:30:00',
  `whatsapp_number` varchar(20) DEFAULT NULL,
  `timezone` varchar(100) DEFAULT 'Asia/Baghdad',
  `admin_email` varchar(150) DEFAULT NULL,
  `accounting_email` varchar(150) DEFAULT NULL,
  `primary_color` varchar(20) DEFAULT '#ff4d79',
  `secondary_color` varchar(20) DEFAULT '#6b1024',
  `ui_language` varchar(20) DEFAULT 'ar',
  `ui_font` varchar(100) NOT NULL DEFAULT 'Tajawal',
  `bg_type` varchar(20) DEFAULT 'solid',
  `bg_color_1` varchar(20) DEFAULT '#090204',
  `bg_color_2` varchar(20) DEFAULT '#1e293b',
  `btn_bg_color` varchar(20) DEFAULT '#ff4d79',
  `btn_text_color` varchar(20) DEFAULT '#ffffff',
  `text_color` varchar(20) DEFAULT '#ffffff',
  `border_thickness` varchar(10) DEFAULT '1',
  `enable_shadow` varchar(10) DEFAULT '1',
  `shadow_color` varchar(20) DEFAULT '#000000',
  `modal_title_color` varchar(20) DEFAULT '#ff4d79',
  `modal_radius` varchar(10) DEFAULT '15',
  `btn_radius` varchar(10) DEFAULT '8',
  PRIMARY KEY (`id`),
  UNIQUE KEY `school_id` (`school_id`),
  UNIQUE KEY `custom_domain` (`custom_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `school_buses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `bus_number` varchar(50) NOT NULL,
  `plate_number` varchar(50) NOT NULL,
  `driver_name` varchar(100) NOT NULL,
  `driver_phone` varchar(20) NOT NULL,
  `traccar_device_id` varchar(100) DEFAULT NULL,
  `camera_stream_url` varchar(255) DEFAULT NULL,
  `capacity` int(11) DEFAULT 30,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `supervisor_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `school_holidays` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `holiday_date` date DEFAULT NULL,
  `holiday_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `school_news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `news_content` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `school_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(150) NOT NULL,
  `stage_name` varchar(100) NOT NULL COMMENT 'المرحلة أو الكروب',
  `department_name` varchar(100) DEFAULT NULL COMMENT 'القسم أو المسار',
  `section_name` varchar(50) DEFAULT NULL COMMENT 'الشعبة',
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `site_config` (
  `id` int(11) NOT NULL DEFAULT 1,
  `site_title` varchar(255) DEFAULT 'أكاديمية روزماري',
  `main_color` varchar(20) DEFAULT '#0d6efd',
  `secondary_color` varchar(20) DEFAULT '#198754',
  `logo_path` varchar(255) DEFAULT 'images/logo.png',
  `contact_email` varchar(100) DEFAULT 'info@roozmari.com',
  `footer_copy_right` text DEFAULT NULL,
  `hero_title` text DEFAULT NULL,
  `hero_subtitle` text DEFAULT NULL,
  `is_maintenance_mode` tinyint(1) DEFAULT 0,
  `text_color` varchar(20) DEFAULT '#333333',
  `btn_color` varchar(20) DEFAULT '#0d6efd',
  `icon_color` varchar(20) DEFAULT '#0d6efd',
  `business_emails` text DEFAULT NULL,
  `slider_title_size` varchar(10) DEFAULT '50px',
  `slider_desc_size` varchar(10) DEFAULT '20px',
  `hero_image` varchar(255) DEFAULT 'images/hero-bg.jpg',
  `news_title` varchar(255) DEFAULT 'أحدث الوسائل التقنية',
  `news_content` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `site_visitors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'غير معروف',
  `last_visit` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_address` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `students` (
  `student_id` varchar(50) NOT NULL,
  `student_name` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `parent_phone` varchar(50) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `barcode_uid` varchar(100) DEFAULT NULL,
  `country` varchar(100) NOT NULL,
  `stage` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT 'عام',
  `branch` varchar(100) DEFAULT 'الكل',
  `password` varchar(255) DEFAULT '123456',
  `role` varchar(20) DEFAULT 'student',
  `class_id` int(11) DEFAULT NULL,
  `is_suspended` tinyint(1) DEFAULT 0,
  `is_promoted` tinyint(1) NOT NULL DEFAULT 0,
  `teacher_name` varchar(255) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT 'المدرسة العامة',
  `month_grade` int(11) DEFAULT 0,
  `success_rate` int(11) DEFAULT 0,
  `absence_days` int(11) DEFAULT 0,
  `teacher_note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '',
  `last_active` datetime DEFAULT NULL,
  `personal_photo` varchar(255) DEFAULT NULL,
  `id_document` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `stage_name` varchar(100) DEFAULT NULL,
  `department_name` varchar(100) DEFAULT NULL,
  `section_name` varchar(100) DEFAULT NULL,
  `financial_block` tinyint(1) DEFAULT 1,
  `enrollment_status` enum('active','temporary','withdrawn') DEFAULT 'active',
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `student_permissions` (
  `student_id` varchar(50) NOT NULL,
  `can_live` tinyint(1) DEFAULT 1,
  `can_exams` tinyint(1) DEFAULT 1,
  `can_archive` tinyint(1) DEFAULT 1,
  `can_videos` int(1) DEFAULT 1,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `student_progress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(100) NOT NULL,
  `lesson_id` int(11) NOT NULL,
  `completed_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_completion` (`student_id`,`lesson_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `student_transport` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `bus_id` int(11) NOT NULL,
  `student_name` varchar(255) DEFAULT NULL,
  `class_info` varchar(255) DEFAULT NULL,
  `pickup_address` varchar(255) DEFAULT NULL,
  `pickup_time` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `subscription_packages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `package_type` varchar(50) NOT NULL,
  `package_name` varchar(100) NOT NULL,
  `monthly_price` decimal(10,2) NOT NULL,
  `discount_percentage` int(11) DEFAULT 15,
  `max_students` int(11) DEFAULT 0,
  `max_videos` int(11) DEFAULT 0,
  `max_live` int(11) DEFAULT 0,
  `features` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `package_type` (`package_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `system_admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','viewer') DEFAULT 'viewer',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` varchar(10) NOT NULL,
  `whatsapp_number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS `teachers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(100) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT 'المدرسة العامة',
  `display_name` varchar(150) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `subject` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `title` varchar(50) DEFAULT 'أستاذ',
  `specialization` varchar(100) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `whatsapp_api_key` varchar(255) DEFAULT NULL,
  `whatsapp_instance_id` varchar(255) DEFAULT NULL,
  `role` varchar(20) DEFAULT 'teacher',
  `primary_color` varchar(10) DEFAULT '#6b1437' COMMENT 'الهوية البصرية',
  `ui_language` varchar(10) DEFAULT 'ar' COMMENT 'لغة الواجهة',
  `e_signature` varchar(255) DEFAULT NULL COMMENT 'التوقيع الإلكتروني',
  `require_approval` tinyint(1) DEFAULT 0 COMMENT 'غرفة الانتظار',
  `enable_watermark` tinyint(1) DEFAULT 0 COMMENT 'العلامة المائية',
  `enable_gamification` tinyint(1) DEFAULT 0 COMMENT 'نظام النقاط',
  `country` varchar(100) NOT NULL,
  `bio` text DEFAULT NULL,
  `experience` text DEFAULT NULL,
  `achievements` text DEFAULT NULL,
  `gallery` text DEFAULT NULL,
  `personal_photo` varchar(255) DEFAULT NULL,
  `id_document` varchar(255) DEFAULT NULL,
  `account_status` enum('pending','active','suspended') DEFAULT 'pending',
  `subscription_end` datetime DEFAULT NULL,
  `max_videos` int(11) DEFAULT 0,
  `max_lessons` int(11) DEFAULT 0,
  `max_archive_lessons` int(11) DEFAULT 0,
  `max_archive_videos` int(11) DEFAULT 0,
  `last_active` datetime DEFAULT NULL,
  `max_students` int(11) DEFAULT 0,
  `max_live` int(11) DEFAULT 0,
  `base_salary` decimal(10,2) DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `teacher_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(150) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `teacher_name` varchar(150) NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  `stage` varchar(100) NOT NULL,
  `department` varchar(100) NOT NULL,
  `section` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `teacher_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) NOT NULL,
  `can_live` tinyint(1) DEFAULT 0,
  `can_upload_videos` tinyint(1) DEFAULT 0,
  `can_create_exams` tinyint(1) DEFAULT 0,
  `can_manage_schedules` tinyint(1) DEFAULT 0,
  `is_trial_mode` tinyint(1) DEFAULT 0,
  `can_branding` tinyint(1) DEFAULT 0,
  `can_certificates` tinyint(1) DEFAULT 0,
  `can_security_waiting_room` tinyint(1) DEFAULT 0,
  `can_points_system` tinyint(1) DEFAULT 0,
  `can_tags_classes` tinyint(1) DEFAULT 0,
  `can_smart_cleaner` tinyint(1) DEFAULT 0,
  `can_teaching_plan` tinyint(1) DEFAULT 0,
  `can_lessons_archive` tinyint(1) DEFAULT 0,
  `can_videos_archive` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `teacher_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(100) NOT NULL,
  `tag_name` varchar(100) NOT NULL COMMENT 'اسم التصنيف (مثال: السادس العلمي)',
  `tag_slug` varchar(100) NOT NULL COMMENT 'الاسم المختصر (الرابط)',
  `parent_id` int(11) DEFAULT 0 COMMENT '0 يعني فئة رئيسية، أي رقم آخر يعني فئة فرعية',
  `tag_type` varchar(50) DEFAULT 'general' COMMENT 'نوع المجموعة للتنظيم',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `user_devices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_type` enum('student','teacher') NOT NULL,
  `user_id` varchar(100) NOT NULL,
  `device_hash` varchar(100) NOT NULL,
  `device_name` varchar(255) NOT NULL,
  `added_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_device` (`device_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `webrtc_signals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` varchar(50) DEFAULT NULL,
  `sid` varchar(50) DEFAULT NULL,
  `offer` text DEFAULT NULL,
  `answer` text DEFAULT NULL,
  `teacher_ice` text DEFAULT NULL,
  `student_ice` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `screen_offer` text DEFAULT NULL,
  `screen_answer` text DEFAULT NULL,
  `screen_ice` text DEFAULT NULL,
  `media_cmd` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_conn` (`tid`,`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `zoom_active_meetings` (
  `teacher_id` varchar(100) NOT NULL,
  `meeting_id` varchar(100) DEFAULT NULL,
  `meeting_pass` varchar(100) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

ALTER TABLE `attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`);

SQL;

    // تنفيذ الاستعلامات المتعددة لبناء قاعدة البيانات
    if ($conn->multi_query($sql_schema)) {
        do {
            if ($result = $conn->store_result()) { $result->free(); }
        } while ($conn->more_results() && $conn->next_result());
        
        $step = 2; // الانتقال لخطوة النجاح
        
        // أمان: يفضل حذف ملف التنصيب تلقائياً بعد تشغيله في الاستضافة الحقيقية
        // @unlink(__FILE__); 
    } else {
        $error = true;
        $message = "حدث خطأ أثناء بناء الجداول: " . $conn->error;
    }
}
?>
<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>تثبيت نظام روزماري الأكاديمي</title>
    <!-- Tailwind CSS -->
    <script src="https://cdn.tailwindcss.com"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Tajawal:wght@400;700;900&display=swap" rel="stylesheet">
    
    <style>
        body { font-family: 'Tajawal', sans-serif; background-color: #0b0f19; background-image: radial-gradient(at 50% 50%, #1e293b 0px, transparent 50%); background-attachment: fixed; color: #f8fafc; }
        .glass-card { background: rgba(15, 23, 42, 0.7); backdrop-filter: blur(16px); -webkit-backdrop-filter: blur(16px); border: 1px solid rgba(255, 255, 255, 0.05); box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.5); }
    </style>
</head>
<body class="min-h-screen flex items-center justify-center p-4">

    <!-- دائرة مضيئة في الخلفية -->
    <div class="absolute w-[600px] h-[600px] bg-blue-600/20 rounded-full blur-[100px] pointer-events-none z-0"></div>

    <div class="glass-card w-full max-w-lg rounded-3xl p-8 relative z-10 text-center border-t border-blue-500/30">
        
        <div class="mb-8">
            <div class="w-20 h-20 bg-gradient-to-br from-blue-500 to-indigo-600 rounded-2xl mx-auto flex items-center justify-center text-4xl text-white shadow-[0_0_20px_rgba(59,130,246,0.4)] mb-4 transform rotate-12">
                <i class="fas fa-rocket -rotate-12"></i>
            </div>
            <h1 class="text-3xl font-black text-white">تثبيت منصة الإدارة</h1>
            <p class="text-gray-400 mt-2 text-sm">خطوة واحدة لبناء 45 جدول والبدء بالعمل.</p>
        </div>

        <?php if ($error): ?>
            <div class="bg-red-900/30 border border-red-500 text-red-400 p-4 rounded-xl mb-6 font-bold text-sm">
                <i class="fas fa-exclamation-triangle me-1"></i> <?= $message ?>
            </div>
            <button onclick="window.location.reload()" class="w-full bg-gray-800 hover:bg-gray-700 text-white font-bold py-3.5 rounded-xl transition">المحاولة مرة أخرى</button>
        <?php elseif ($step === 1): ?>
            <div class="bg-blue-900/20 border border-blue-900/50 p-4 rounded-xl mb-8 text-right">
                <ul class="text-sm text-gray-300 space-y-2 font-bold">
                    <li><i class="fas fa-check text-green-500 me-2"></i> تأسيس جداول الطلاب والمالية</li>
                    <li><i class="fas fa-check text-green-500 me-2"></i> تأسيس سجلات الكادر والرواتب</li>
                    <li><i class="fas fa-check text-green-500 me-2"></i> تأسيس جداول البث المباشر والإشعارات</li>
                </ul>
            </div>

            <form method="POST">
                <button type="submit" name="start_install" class="w-full bg-blue-600 hover:bg-blue-500 text-white font-black text-lg py-4 rounded-xl transition shadow-[0_0_20px_rgba(59,130,246,0.4)] group">
                    <i class="fas fa-cogs me-2 group-hover:rotate-180 transition-transform duration-500"></i> بناء قاعدة البيانات الآن
                </button>
            </form>
        <?php elseif ($step === 2): ?>
            <div class="bg-green-900/30 border border-green-500 p-6 rounded-xl mb-8">
                <div class="text-5xl text-green-400 mb-4 animate-bounce"><i class="fas fa-check-circle"></i></div>
                <h2 class="text-xl font-black text-green-400 mb-2">ممتاز! تم البناء بنجاح</h2>
                <p class="text-sm text-green-200/70 font-bold">قاعدة بياناتك أصبحت جاهزة وتعمل بالكامل.</p>
            </div>
            
            <a href="index.php" class="block w-full bg-gray-800 hover:bg-gray-700 border border-gray-600 text-white font-bold text-lg py-4 rounded-xl transition">
                الذهاب للصفحة الرئيسية للمؤسسة <i class="fas fa-arrow-left ms-2"></i>
            </a>
        <?php endif; ?>

        <div class="mt-8 pt-4 border-t border-gray-800 text-xs text-gray-500 font-bold">
            مُشغل بواسطة نظام Roozmari &copy; <?= date('Y') ?>
        </div>
    </div>

</body>
</html>