-- My Daneshyar canonical database schema
-- Stack: Laravel + MySQL/MariaDB
-- Policy: this is the single baseline schema during early development.
-- Dates shown to users are Jalali/Persian; timestamps are stored as SQL timestamps.



CREATE TABLE schools (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name_fa VARCHAR(190) NOT NULL,
  name_en VARCHAR(190) NULL,
  school_code VARCHAR(80) NOT NULL UNIQUE,
  province VARCHAR(120) NULL,
  city VARCHAR(120) NULL,
  address TEXT NULL,
  phone VARCHAR(30) NULL,
  logo_path VARCHAR(255) NULL,
  primary_color VARCHAR(20) DEFAULT '#0EA678',
  status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  school_type ENUM('ebtedaee','motevasete_aval','motevasete_dovom') NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  deleted_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  username VARCHAR(120) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  mobile VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  national_code VARCHAR(20) NULL,
  role_key VARCHAR(80) NOT NULL,
  status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
  avatar_path VARCHAR(255) NULL,
  must_change_password BOOLEAN NOT NULL DEFAULT FALSE,
  must_complete_profile BOOLEAN NOT NULL DEFAULT TRUE,
  profile_completed_at TIMESTAMP NULL,
  last_profile_update_at TIMESTAMP NULL,
  last_login_at TIMESTAMP NULL,
  last_seen_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  deleted_at TIMESTAMP NULL,
  INDEX idx_users_school_role (school_id, role_key),
  CONSTRAINT fk_users_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(80) NOT NULL UNIQUE,
  name_fa VARCHAR(190) NOT NULL,
  description TEXT NULL,
  price DECIMAL(14,2) DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE licenses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NULL,
  starts_at DATE NOT NULL,
  expires_at DATE NOT NULL,
  status ENUM('active','expired','suspended','cancelled') NOT NULL DEFAULT 'active',
  max_students INT UNSIGNED NULL,
  max_teachers INT UNSIGNED NULL,
  max_storage_mb INT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_licenses_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_licenses_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL,
  CONSTRAINT fk_licenses_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE modules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(120) NOT NULL UNIQUE,
  name_fa VARCHAR(190) NOT NULL,
  name_en VARCHAR(190) NULL,
  description TEXT NULL,
  is_core BOOLEAN NOT NULL DEFAULT FALSE,
  is_sellable BOOLEAN NOT NULL DEFAULT TRUE,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE module_dependencies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module_id BIGINT UNSIGNED NOT NULL,
  required_module_id BIGINT UNSIGNED NOT NULL,
  is_required BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_module_dependency (module_id, required_module_id),
  CONSTRAINT fk_module_dep_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  CONSTRAINT fk_module_dep_required FOREIGN KEY (required_module_id) REFERENCES modules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE school_modules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  module_id BIGINT UNSIGNED NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  starts_at DATE NULL,
  expires_at DATE NULL,
  enabled_by BIGINT UNSIGNED NULL,
  disabled_by BIGINT UNSIGNED NULL,
  price DECIMAL(14,2) DEFAULT 0,
  notes TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_school_module (school_id, module_id),
  CONSTRAINT fk_school_modules_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_school_modules_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE school_module_limits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  module_id BIGINT UNSIGNED NOT NULL,
  limit_key VARCHAR(120) NOT NULL,
  limit_value VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_school_module_limit (school_id, module_id, limit_key),
  CONSTRAINT fk_module_limits_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_module_limits_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE school_module_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  module_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(120) NOT NULL,
  old_value JSON NULL,
  new_value JSON NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_module_logs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_module_logs_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  CONSTRAINT fk_module_logs_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE grade_levels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  number TINYINT UNSIGNED NOT NULL UNIQUE,
  name_fa VARCHAR(80) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE school_grade_levels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  grade_level_id BIGINT UNSIGNED NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  enabled_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_school_grade (school_id, grade_level_id),
  CONSTRAINT fk_school_grade_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_school_grade_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  key_name VARCHAR(100) NOT NULL,
  name_fa VARCHAR(120) NOT NULL,
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_role_school_key (school_id, key_name),
  CONSTRAINT fk_roles_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(160) NOT NULL UNIQUE,
  name_fa VARCHAR(190) NOT NULL,
  module_id BIGINT UNSIGNED NULL,
  description TEXT NULL,
  is_sellable BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_permissions_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permission_dependencies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  permission_id BIGINT UNSIGNED NOT NULL,
  required_permission_id BIGINT UNSIGNED NOT NULL,
  is_required BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_permission_dependency (permission_id, required_permission_id),
  CONSTRAINT fk_perm_dep_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
  CONSTRAINT fk_perm_dep_required FOREIGN KEY (required_permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE school_permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  starts_at DATE NULL,
  expires_at DATE NULL,
  enabled_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_school_permission (school_id, permission_id),
  CONSTRAINT fk_school_permissions_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_school_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_role_permission (role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  effect ENUM('allow','deny') NOT NULL DEFAULT 'allow',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_user_permission (user_id, permission_id),
  CONSTRAINT fk_user_permissions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permission_packages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(120) NOT NULL UNIQUE,
  name_fa VARCHAR(190) NOT NULL,
  description TEXT NULL,
  price DECIMAL(14,2) DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permission_package_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  package_id BIGINT UNSIGNED NOT NULL,
  module_id BIGINT UNSIGNED NULL,
  permission_id BIGINT UNSIGNED NULL,
  grade_level_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_pkg_items_package FOREIGN KEY (package_id) REFERENCES permission_packages(id) ON DELETE CASCADE,
  CONSTRAINT fk_pkg_items_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  CONSTRAINT fk_pkg_items_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
  CONSTRAINT fk_pkg_items_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE school_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL UNIQUE,
  required_student_fields JSON NULL,
  -- کدام شماره ولی هنگام تکمیل پروفایل دانش‌آموز الزامی است
  parent_mobile_requirement ENUM('none','father','mother','either','both') NOT NULL DEFAULT 'either',
  absence_sms_mode ENUM('disabled','instant','approval_required') NOT NULL DEFAULT 'approval_required',
  sms_low_balance_threshold INT UNSIGNED NOT NULL DEFAULT 20,
  qualitative_thresholds JSON NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_school_settings_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE students (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  student_code VARCHAR(80) NOT NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  national_code VARCHAR(20) NULL,
  mobile VARCHAR(30) NULL,
  father_name VARCHAR(120) NULL,
  mother_name VARCHAR(120) NULL,
  father_mobile VARCHAR(30) NULL,
  mother_mobile VARCHAR(30) NULL,
  home_phone VARCHAR(30) NULL,
  emergency_phone VARCHAR(30) NULL,
  birth_date DATE NULL,
  birth_date_jalali VARCHAR(20) NULL,
  gender ENUM('male','female','other') NULL,
  grade_level_id BIGINT UNSIGNED NULL,
  address TEXT NULL,
  photo_path VARCHAR(255) NULL,
  status ENUM('active','inactive','graduated','transferred') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  deleted_at TIMESTAMP NULL,
  UNIQUE KEY uniq_student_school_code (school_id, student_code),
  CONSTRAINT fk_students_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_students_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_students_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE teachers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  teacher_code VARCHAR(80) NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  national_code VARCHAR(20) NULL,
  mobile VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  specialty VARCHAR(190) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_teacher_school_code (school_id, teacher_code),
  CONSTRAINT fk_teachers_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_teachers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE classrooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  grade_level_id BIGINT UNSIGNED NOT NULL,
  field_of_study VARCHAR(120) NULL,
  name VARCHAR(120) NOT NULL,
  capacity INT UNSIGNED NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_class_school_name (school_id, name),
  CONSTRAINT fk_classrooms_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_classrooms_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subjects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  grade_level_id BIGINT UNSIGNED NULL,
  name VARCHAR(160) NOT NULL,
  code VARCHAR(40) NULL,
  coefficient DECIMAL(5,2) DEFAULT 1,
  weekly_hours TINYINT UNSIGNED NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_subjects_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_subjects_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE class_students (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  starts_at DATE NULL,
  ends_at DATE NULL,
  status ENUM('active','inactive','transferred') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_class_student_active (classroom_id, student_id, status),
  CONSTRAINT fk_class_students_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_class_students_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_class_students_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_class_transfers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  from_classroom_id BIGINT UNSIGNED NOT NULL,
  to_classroom_id BIGINT UNSIGNED NOT NULL,
  transferred_by_user_id BIGINT UNSIGNED NULL,
  note TEXT NULL,
  transferred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_transfers_school_student (school_id, student_id),
  CONSTRAINT fk_transfers_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_transfers_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_transfers_from_class FOREIGN KEY (from_classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_transfers_to_class FOREIGN KEY (to_classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_transfers_user FOREIGN KEY (transferred_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE class_teachers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  teacher_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_class_teacher_subject (classroom_id, teacher_id, subject_id),
  CONSTRAINT fk_class_teachers_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_class_teachers_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_class_teachers_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
  CONSTRAINT fk_class_teachers_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE class_subjects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_class_subject (classroom_id, subject_id),
  CONSTRAINT fk_class_subjects_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_class_subjects_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_class_subjects_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE schedules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NOT NULL,
  teacher_id BIGINT UNSIGNED NOT NULL,
  day_of_week TINYINT UNSIGNED NOT NULL COMMENT '0=شنبه, 1=یکشنبه, 2=دوشنبه, 3=سه‌شنبه, 4=چهارشنبه, 5=پنج‌شنبه, 6=جمعه — محاسبه: ((Carbon::dayOfWeek + 1) % 7)',
  starts_at TIME NOT NULL,
  ends_at TIME NOT NULL,
  room VARCHAR(120) NULL,
  online_class_url VARCHAR(500) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_schedules_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_schedules_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_schedules_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  CONSTRAINT fk_schedules_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exams (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NOT NULL,
  teacher_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  type ENUM('test','descriptive','mixed','file','offline') NOT NULL DEFAULT 'mixed',
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  duration_minutes INT UNSIGNED NULL,
  max_score DECIMAL(6,2) NOT NULL DEFAULT 20,
  status ENUM('draft','published','closed','archived') NOT NULL DEFAULT 'draft',
  settings JSON NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_exams_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_exams_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_exams_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  CONSTRAINT fk_exams_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exam_questions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  exam_id BIGINT UNSIGNED NOT NULL,
  question_type ENUM('test','descriptive','file') NOT NULL,
  body TEXT NOT NULL,
  score DECIMAL(6,2) NOT NULL DEFAULT 1,
  image_path VARCHAR(255) NULL,
  allow_file_upload TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'برای سوالات تشریحی — اجازه آپلود فایل در پاسخ',
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_exam_questions_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_exam_questions_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exam_options (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_id BIGINT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  is_correct BOOLEAN NOT NULL DEFAULT FALSE,
  image_path VARCHAR(255) NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_exam_options_question FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exam_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  exam_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  started_at DATETIME NULL,
  submitted_at DATETIME NULL,
  score DECIMAL(6,2) NULL,
  status ENUM('started','submitted','graded','expired') NOT NULL DEFAULT 'started',
  question_order JSON NULL COMMENT 'ترتیب تصادفی سوالات (آرایه question_id) — فقط اگر randomize_questions=true',
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_exam_student_session (exam_id, student_id),
  CONSTRAINT fk_exam_sessions_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_exam_sessions_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
  CONSTRAINT fk_exam_sessions_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exam_answers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  exam_session_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  option_id BIGINT UNSIGNED NULL,
  answer_text TEXT NULL,
  file_path VARCHAR(255) NULL,
  score DECIMAL(6,2) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_exam_answers_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_exam_answers_session FOREIGN KEY (exam_session_id) REFERENCES exam_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_exam_answers_question FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON DELETE CASCADE,
  CONSTRAINT fk_exam_answers_option FOREIGN KEY (option_id) REFERENCES exam_options(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE grades (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NOT NULL,
  teacher_id BIGINT UNSIGNED NULL,
  exam_id BIGINT UNSIGNED NULL,
  score DECIMAL(6,2) NOT NULL,
  max_score DECIMAL(6,2) NOT NULL DEFAULT 20,
  status ENUM('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft',
  description TEXT NULL,
  qualitative_label VARCHAR(60) NULL,
  created_by BIGINT UNSIGNED NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_grades_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_grades_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_grades_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_grades_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  CONSTRAINT fk_grades_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
  CONSTRAINT fk_grades_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_cards (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NULL,
  period_key VARCHAR(80) NOT NULL,
  average_score DECIMAL(6,2) NULL,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  data JSON NULL,
  published_by BIGINT UNSIGNED NULL,
  published_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_report_cards_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_report_cards_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_report_cards_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE SET NULL,
  CONSTRAINT fk_report_cards_user FOREIGN KEY (published_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_card_comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_year_id BIGINT UNSIGNED NULL,
  term TINYINT UNSIGNED NULL,
  comment TEXT NOT NULL,
  author_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_report_card_comments_lookup (school_id, student_id, school_year_id, term),
  CONSTRAINT fk_report_card_comments_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_report_card_comments_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_report_card_comments_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_card_share_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_year_id BIGINT UNSIGNED NULL,
  term TINYINT UNSIGNED NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  token_plain VARCHAR(32) NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  revoked TINYINT(1) NOT NULL DEFAULT 0,
  expires_at TIMESTAMP NULL,
  last_viewed_at TIMESTAMP NULL,
  view_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_report_card_share_tokens_school_student (school_id, student_id),
  CONSTRAINT fk_report_card_share_tokens_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_report_card_share_tokens_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_report_card_share_tokens_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE grade_change_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  grade_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  changed_by BIGINT UNSIGNED NULL,
  old_score DECIMAL(6,2) NULL,
  new_score DECIMAL(6,2) NULL,
  reason TEXT NULL,
  ip_address VARCHAR(80) NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_grade_logs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_grade_logs_grade FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
  CONSTRAINT fk_grade_logs_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_grade_logs_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE homeworks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NULL,
  teacher_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  description TEXT NULL,
  due_at DATETIME NULL,
  allow_file_upload BOOLEAN NOT NULL DEFAULT TRUE,
  status ENUM('draft','published','closed','archived') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_homeworks_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_homeworks_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_homeworks_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL,
  CONSTRAINT fk_homeworks_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE homework_submissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  homework_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  body TEXT NULL,
  file_path VARCHAR(255) NULL,
  status ENUM('submitted','late','reviewed','returned') NOT NULL DEFAULT 'submitted',
  submitted_at TIMESTAMP NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_homework_student (homework_id, student_id),
  CONSTRAINT fk_hw_submissions_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_hw_submissions_homework FOREIGN KEY (homework_id) REFERENCES homeworks(id) ON DELETE CASCADE,
  CONSTRAINT fk_hw_submissions_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_hw_submissions_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NULL,
  teacher_id BIGINT UNSIGNED NULL,
  schedule_id BIGINT UNSIGNED NULL,
  online_class_id BIGINT UNSIGNED NULL,
  type ENUM('in_person','online') NOT NULL DEFAULT 'in_person',
  date DATE NOT NULL,
  date_jalali VARCHAR(20) NULL,
  starts_at TIME NULL,
  ends_at TIME NULL,
  session_number INT UNSIGNED NULL,
  status ENUM('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft',
  submitted_by BIGINT UNSIGNED NULL,
  submitted_at TIMESTAMP NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_attsess_school_date (school_id, `date`),
  CONSTRAINT fk_att_sessions_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_sessions_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_sessions_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL,
  CONSTRAINT fk_att_sessions_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
  CONSTRAINT fk_att_sessions_schedule FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE SET NULL,
  CONSTRAINT fk_att_sessions_online_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  attendance_session_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  status ENUM('present','absent','late','excused') NOT NULL DEFAULT 'present',
  delay_minutes INT UNSIGNED NULL,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_att_record_student (attendance_session_id, student_id),
  CONSTRAINT fk_att_records_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_records_session FOREIGN KEY (attendance_session_id) REFERENCES attendance_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_records_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_approval_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  attendance_session_id BIGINT UNSIGNED NOT NULL,
  requested_by BIGINT UNSIGNED NULL,
  assigned_to BIGINT UNSIGNED NULL,
  type ENUM('absence_sms','attendance_approval') NOT NULL DEFAULT 'absence_sms',
  status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  absent_count INT UNSIGNED NOT NULL DEFAULT 0,
  sms_cost INT UNSIGNED NOT NULL DEFAULT 0,
  reason TEXT NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at TIMESTAMP NULL,
  rejected_by BIGINT UNSIGNED NULL,
  rejected_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_att_requests_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_requests_session FOREIGN KEY (attendance_session_id) REFERENCES attendance_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL UNIQUE,
  balance INT UNSIGNED NOT NULL DEFAULT 0,
  reserved_balance INT UNSIGNED NOT NULL DEFAULT 0,
  total_charged INT UNSIGNED NOT NULL DEFAULT 0,
  total_used INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_sms_wallets_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_packages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  sms_count INT UNSIGNED NOT NULL,
  price DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_charge_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  package_id BIGINT UNSIGNED NULL,
  requested_by BIGINT UNSIGNED NULL,
  sms_count INT UNSIGNED NOT NULL,
  price DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  note TEXT NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_sms_charge_req_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_sms_charge_req_user FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_sms_charge_req_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  invoice_number VARCHAR(120) NOT NULL UNIQUE,
  type ENUM('license','sms_charge','module_purchase','manual') NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('draft','issued','paid','cancelled','failed') NOT NULL DEFAULT 'issued',
  description TEXT NULL,
  issued_by BIGINT UNSIGNED NULL,
  paid_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_invoices_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_invoices_user FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  invoice_id BIGINT UNSIGNED NULL,
  provider_name VARCHAR(120) NULL,
  provider_transaction_id VARCHAR(190) NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('pending','paid','failed','cancelled','refunded') NOT NULL DEFAULT 'pending',
  request_payload JSON NULL,
  response_payload JSON NULL,
  paid_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_payments_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_wallet_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  type ENUM('charge','manual_adjustment','send_success','send_refund') NOT NULL,
  amount INT NOT NULL,
  balance_before INT NOT NULL,
  balance_after INT NOT NULL,
  payment_id VARCHAR(190) NULL,
  created_by BIGINT UNSIGNED NULL,
  description TEXT NULL,
  created_at TIMESTAMP NULL,
  INDEX idx_smstx_school_created (school_id, created_at),
  CONSTRAINT fk_sms_transactions_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_sms_transactions_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  sender_user_id BIGINT UNSIGNED NULL,
  target_type VARCHAR(80) NOT NULL,
  message_body TEXT NOT NULL,
  recipient_count INT UNSIGNED NOT NULL DEFAULT 0,
  cost INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('draft','queued','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
  provider_message_id VARCHAR(190) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_smsmsg_school_created (school_id, created_at),
  CONSTRAINT fk_sms_messages_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_sms_messages_sender FOREIGN KEY (sender_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_recipients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sms_message_id BIGINT UNSIGNED NOT NULL,
  recipient_type ENUM('student','student_contact','teacher','user') NOT NULL,
  recipient_id BIGINT UNSIGNED NULL,
  mobile VARCHAR(30) NOT NULL,
  status ENUM('queued','sent','failed','charged') NOT NULL DEFAULT 'queued',
  provider_status VARCHAR(120) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_sms_recipients_message FOREIGN KEY (sms_message_id) REFERENCES sms_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  key_name VARCHAR(120) NOT NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_sms_template_school_key (school_id, key_name),
  CONSTRAINT fk_sms_templates_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_sms_templates_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_provider_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  sms_message_id BIGINT UNSIGNED NULL,
  provider_name VARCHAR(120) NULL,
  request_payload JSON NULL,
  response_payload JSON NULL,
  status VARCHAR(120) NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_sms_provider_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL,
  CONSTRAINT fk_sms_provider_message FOREIGN KEY (sms_message_id) REFERENCES sms_messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_sms_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  attendance_session_id BIGINT UNSIGNED NOT NULL,
  sms_message_id BIGINT UNSIGNED NULL,
  approval_request_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_att_sms_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_sms_session FOREIGN KEY (attendance_session_id) REFERENCES attendance_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_sms_message FOREIGN KEY (sms_message_id) REFERENCES sms_messages(id) ON DELETE SET NULL,
  CONSTRAINT fk_att_sms_request FOREIGN KEY (approval_request_id) REFERENCES attendance_approval_requests(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE votings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  description TEXT NULL,
  type ENUM('student_council','class_representative','survey','custom') NOT NULL DEFAULT 'custom',
  is_secret BOOLEAN NOT NULL DEFAULT TRUE,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  status ENUM('draft','published','paused','closed','archived') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  published_by BIGINT UNSIGNED NULL,
  published_at TIMESTAMP NULL,
  closed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_votings_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_votings_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE voting_candidates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  voting_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NULL,
  display_name VARCHAR(190) NOT NULL,
  classroom_id BIGINT UNSIGNED NULL,
  slogan VARCHAR(255) NULL,
  description TEXT NULL,
  image_path VARCHAR(255) NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_voting_candidates_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_candidates_voting FOREIGN KEY (voting_id) REFERENCES votings(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_candidates_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL,
  CONSTRAINT fk_voting_candidates_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE voting_eligible_voters (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  voting_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NULL,
  grade_level_id BIGINT UNSIGNED NULL,
  classroom_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_voting_eligible_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_eligible_voting FOREIGN KEY (voting_id) REFERENCES votings(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_eligible_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_eligible_grade FOREIGN KEY (grade_level_id) REFERENCES grade_levels(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_eligible_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE voting_votes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  voting_id BIGINT UNSIGNED NOT NULL,
  candidate_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  vote_hash VARCHAR(255) NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  created_at TIMESTAMP NULL,
  UNIQUE KEY uniq_voting_student_vote (voting_id, student_id),
  CONSTRAINT fk_voting_votes_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_votes_voting FOREIGN KEY (voting_id) REFERENCES votings(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_votes_candidate FOREIGN KEY (candidate_id) REFERENCES voting_candidates(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_votes_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE voting_results_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  voting_id BIGINT UNSIGNED NOT NULL,
  snapshot JSON NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_voting_snapshots_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_snapshots_voting FOREIGN KEY (voting_id) REFERENCES votings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE voting_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  voting_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  old_value JSON NULL,
  new_value JSON NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_voting_logs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_logs_voting FOREIGN KEY (voting_id) REFERENCES votings(id) ON DELETE CASCADE,
  CONSTRAINT fk_voting_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  sender_user_id BIGINT UNSIGNED NULL,
  subject VARCHAR(190) NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_messages_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_messages_sender FOREIGN KEY (sender_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE message_recipients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  message_id BIGINT UNSIGNED NOT NULL,
  recipient_user_id BIGINT UNSIGNED NOT NULL,
  read_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_message_recipients_message FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
  CONSTRAINT fk_message_recipients_user FOREIGN KEY (recipient_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE announcements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  audience VARCHAR(120) NOT NULL DEFAULT 'all',
  is_important BOOLEAN NOT NULL DEFAULT FALSE,
  color VARCHAR(20) NOT NULL DEFAULT '#d97706',
  has_pulse TINYINT(1) NOT NULL DEFAULT 1,
  expires_at TIMESTAMP NULL,
  published_at TIMESTAMP NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_announcements_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_announcements_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE announcement_dismissals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  announcement_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  dismissed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_announcement_user (announcement_id, user_id),
  INDEX idx_announcement_dismissal_user (user_id),
  CONSTRAINT fk_ann_dismissal_announcement FOREIGN KEY (announcement_id) REFERENCES announcements(id) ON DELETE CASCADE,
  CONSTRAINT fk_ann_dismissal_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- کلاس آنلاین: meeting_url خالی = اتاق کلاس داخلی سامانه (چت/تخته/فایل)؛ پر = لینک خارجی
-- سرورهای رسانه (TURN/SFU) — کلاس جدید روی کم‌بارترین سرور فعال باز می‌شود
CREATE TABLE media_servers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  -- turn = coturn relay (mesh) · sfu = selective forwarding unit
  kind ENUM('turn','sfu') NOT NULL DEFAULT 'turn',
  host VARCHAR(190) NOT NULL,
  port INT UNSIGNED NOT NULL DEFAULT 3478,
  scheme VARCHAR(10) NOT NULL DEFAULT 'turn',
  realm VARCHAR(190) NULL,
  -- HMAC secret for time-limited TURN credentials; NULL for public STUN
  secret VARCHAR(255) NULL,
  region VARCHAR(60) NULL,
  public_ip VARCHAR(45) NULL,
  -- how many concurrent participants this box is trusted to carry
  capacity INT UNSIGNED NOT NULL DEFAULT 50,
  -- refreshed by the health check; drives least-loaded selection
  current_load INT UNSIGNED NOT NULL DEFAULT 0,
  -- lower number wins when loads tie
  priority INT UNSIGNED NOT NULL DEFAULT 100,
  status ENUM('active','draining','offline') NOT NULL DEFAULT 'active',
  manually_disabled BOOLEAN NOT NULL DEFAULT FALSE,
  last_health_at TIMESTAMP NULL,
  last_health_ok BOOLEAN NOT NULL DEFAULT FALSE,
  last_health_note VARCHAR(255) NULL,
  -- Remote management credentials are encrypted by Laravel using APP_KEY.
  ssh_host VARCHAR(190) NULL,
  ssh_port SMALLINT UNSIGNED NOT NULL DEFAULT 22,
  ssh_user VARCHAR(64) NOT NULL DEFAULT 'root',
  ssh_auth_type VARCHAR(16) NOT NULL DEFAULT 'password',
  ssh_password TEXT NULL,
  ssh_private_key LONGTEXT NULL,
  ssh_key_passphrase TEXT NULL,
  ssh_host_fingerprint VARCHAR(100) NULL,
  last_ssh_at TIMESTAMP NULL,
  last_ssh_ok BOOLEAN NOT NULL DEFAULT FALSE,
  last_ssh_note VARCHAR(500) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_media_server_host_port (host, port, kind),
  INDEX idx_media_server_pick (status, kind, current_load, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE media_server_actions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  media_server_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(40) NOT NULL,
  status ENUM('queued','running','succeeded','failed') NOT NULL DEFAULT 'queued',
  output MEDIUMTEXT NULL,
  started_at TIMESTAMP NULL,
  finished_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_media_action_server_status (media_server_id, status),
  INDEX idx_media_action_queue_status (status, created_at),
  CONSTRAINT fk_media_action_server FOREIGN KEY (media_server_id) REFERENCES media_servers(id) ON DELETE CASCADE,
  CONSTRAINT fk_media_action_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE online_classes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  -- Permanent opaque URL key. It is generated once and never changes.
  room_key CHAR(36) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  classroom_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NULL,
  teacher_id BIGINT UNSIGNED NULL COMMENT 'teachers.id — برای فیلتر مدرسه',
  teacher_user_id BIGINT UNSIGNED NULL COMMENT 'users.id — کاربر ایجادکننده کلاس (هر دو ستون باید همزمان پر شوند)',
  schedule_id BIGINT UNSIGNED NULL,
  -- Prevents two requests from creating a second room for the same timetable/date.
  session_key VARCHAR(100) NULL,
  session_date DATE NULL,
  -- سروری که این کلاس رویش باز شده؛ هنگام شروع کلاس انتخاب و ثابت می‌ماند
  media_server_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  meeting_url VARCHAR(500) NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  status ENUM('scheduled','live','ended') NOT NULL DEFAULT 'scheduled',
  -- صحنه اصلی که معلم برای همه انتخاب می‌کند: NULL/'auto' (گوینده فعال)، 'board' (تخته)، یا 'u:<userId>' (سنجاق روی یک نفر)
  stage VARCHAR(40) NULL DEFAULT NULL,
  allow_student_chat TINYINT(1) NOT NULL DEFAULT 1,
  allow_student_files TINYINT(1) NOT NULL DEFAULT 0,
  require_token TINYINT(1) NOT NULL DEFAULT 1,
  started_at TIMESTAMP NULL,
  finished_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_online_classes_room_key (room_key),
  UNIQUE KEY uniq_online_classes_session_key (session_key),
  INDEX idx_oc_schedule (schedule_id),
  CONSTRAINT fk_online_classes_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_online_classes_class FOREIGN KEY (classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_online_classes_media_server FOREIGN KEY (media_server_id) REFERENCES media_servers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- حضور زنده در اتاق کلاس داخلی
CREATE TABLE online_class_participants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  online_class_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  display_name VARCHAR(190) NOT NULL,
  role ENUM('host','student') NOT NULL DEFAULT 'student',
  token_id BIGINT UNSIGNED NULL,
  hand_raised TINYINT(1) NOT NULL DEFAULT 0,
  hand_raised_at TIMESTAMP NULL,
  chat_muted TINYINT(1) NOT NULL DEFAULT 0,
  audio_allowed TINYINT(1) NOT NULL DEFAULT 0,
  video_allowed TINYINT(1) NOT NULL DEFAULT 0,
  screen_allowed TINYINT(1) NOT NULL DEFAULT 0,
  audio_active TINYINT(1) NOT NULL DEFAULT 0,
  video_active TINYINT(1) NOT NULL DEFAULT 0,
  screen_active TINYINT(1) NOT NULL DEFAULT 0,
  kicked TINYINT(1) NOT NULL DEFAULT 0,
  joined_at TIMESTAMP NULL,
  last_seen_at TIMESTAMP NULL,
  total_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_class_participant (online_class_id, user_id),
  INDEX idx_ocp_presence (online_class_id, last_seen_at),
  CONSTRAINT fk_ocp_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocp_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Short-lived WebRTC signaling inbox. Reverb delivers these immediately;
-- polling this table keeps calls functional when the socket service is down.
CREATE TABLE online_class_signals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  online_class_id BIGINT UNSIGNED NOT NULL,
  from_user_id BIGINT UNSIGNED NOT NULL,
  to_user_id BIGINT UNSIGNED NOT NULL,
  type ENUM('offer','answer','ice','renegotiate') NOT NULL,
  payload JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ocs_recipient_cursor (online_class_id, to_user_id, id),
  INDEX idx_ocs_created (created_at),
  CONSTRAINT fk_ocs_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocs_from FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocs_to FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- چت کلاس: to_user_id خالی = عمومی؛ پر = خصوصی (معلم همه را می‌بیند)
CREATE TABLE online_class_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_key CHAR(36) NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  online_class_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  to_user_id BIGINT UNSIGNED NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP NULL,
  UNIQUE KEY uniq_ocm_event_key (event_key),
  INDEX idx_ocm_class_id (online_class_id, id),
  INDEX idx_ocm_private (online_class_id, to_user_id, id),
  CONSTRAINT fk_ocm_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocm_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE online_class_files (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  online_class_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  path VARCHAR(500) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NULL,
  size_bytes BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  INDEX idx_ocf_class (online_class_id, id),
  CONSTRAINT fk_ocf_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocf_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocf_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- توکن ورود شخصی هر دانش‌آموز به کلاس آنلاین — استفاده از توکن دیگری رد و ثبت می‌شود؛
-- ورود از IP/دستگاه متفاوت با اولین استفاده پرچم‌گذاری می‌شود (flagged + flag_reason)
CREATE TABLE online_class_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  online_class_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  token VARCHAR(20) NOT NULL UNIQUE,
  status ENUM('active','revoked') NOT NULL DEFAULT 'active',
  expires_at TIMESTAMP NULL,
  first_used_at TIMESTAMP NULL,
  last_used_at TIMESTAMP NULL,
  use_count INT UNSIGNED NOT NULL DEFAULT 0,
  bound_ip VARCHAR(80) NULL,
  bound_device_hash VARCHAR(64) NULL,
  flagged TINYINT(1) NOT NULL DEFAULT 0,
  flag_reason VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_class_token_student (online_class_id, student_id),
  INDEX idx_oct_class (online_class_id, status),
  CONSTRAINT fk_oct_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_oct_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_oct_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- رویدادهای تخته (stroke / clear) — کلاینت‌ها به ترتیب id بازپخش می‌کنند
CREATE TABLE online_class_board_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_key CHAR(36) NULL,
  online_class_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  event JSON NOT NULL,
  created_at TIMESTAMP NULL,
  UNIQUE KEY uniq_ocb_event_key (event_key),
  INDEX idx_ocb_class_id (online_class_id, id),
  CONSTRAINT fk_ocb_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE online_class_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  online_class_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  token_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  joined_at TIMESTAMP NOT NULL,
  left_at TIMESTAMP NULL,
  duration_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  INDEX idx_ocl_class_joined (online_class_id, joined_at),
  INDEX idx_ocl_open_session (online_class_id, user_id, left_at),
  CONSTRAINT fk_oc_logs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_oc_logs_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_oc_logs_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL,
  CONSTRAINT fk_oc_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE online_class_recordings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  online_class_id BIGINT UNSIGNED NOT NULL,
  recorded_by BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  disk VARCHAR(40) NOT NULL DEFAULT 'local',
  path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(120) NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  duration_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  started_at TIMESTAMP NOT NULL,
  ended_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_ocr_class_started (online_class_id, started_at),
  INDEX idx_ocr_school (school_id),
  INDEX idx_ocr_recorder (recorded_by),
  CONSTRAINT fk_ocr_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocr_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocr_recorder FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE files (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  disk VARCHAR(80) NOT NULL DEFAULT 'local',
  path VARCHAR(500) NOT NULL,
  original_name VARCHAR(255) NULL,
  mime_type VARCHAR(120) NULL,
  size_bytes BIGINT UNSIGNED NULL,
  visibility ENUM('private','public') NOT NULL DEFAULT 'private',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_files_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_files_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  user_role VARCHAR(80) NULL,
  action VARCHAR(160) NOT NULL,
  entity_type VARCHAR(160) NULL,
  entity_id BIGINT UNSIGNED NULL,
  old_value JSON NULL,
  new_value JSON NULL,
  description TEXT NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  created_at TIMESTAMP NULL,
  INDEX idx_audit_school_action (school_id, action),
  INDEX idx_audit_school_created (school_id, created_at),
  CONSTRAINT fk_audit_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  username VARCHAR(120) NULL,
  status ENUM('success','failed') NOT NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  created_at TIMESTAMP NULL,
  INDEX idx_login_school_created (school_id, created_at),
  INDEX idx_login_user_created (user_id, created_at),
  CONSTRAINT fk_login_logs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL,
  CONSTRAINT fk_login_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_resets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  email VARCHAR(190) NULL,
  mobile VARCHAR(30) NULL,
  token VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  used_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_password_resets_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_password_resets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_heartbeats (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  last_seen_at TIMESTAMP NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  page VARCHAR(255) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_user_heartbeat (user_id),
  CONSTRAINT fk_heartbeats_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_heartbeats_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_usage_daily (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  date DATE NOT NULL,
  login_count INT UNSIGNED NOT NULL DEFAULT 0,
  active_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  important_actions_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY uniq_user_usage_day (user_id, date),
  INDEX idx_usage_school_date (school_id, `date`),
  CONSTRAINT fk_usage_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(160) NOT NULL,
  description TEXT NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  created_at TIMESTAMP NULL,
  INDEX idx_activity_school_created (school_id, created_at),
  CONSTRAINT fk_activity_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL,
  CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE import_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(80) NOT NULL,
  file_path VARCHAR(500) NULL,
  status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  total_rows INT UNSIGNED NOT NULL DEFAULT 0,
  valid_rows INT UNSIGNED NOT NULL DEFAULT 0,
  invalid_rows INT UNSIGNED NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  started_at TIMESTAMP NULL,
  finished_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_import_jobs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_import_jobs_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE import_job_rows (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  import_job_id BIGINT UNSIGNED NOT NULL,
  row_index INT UNSIGNED NOT NULL,
  raw_data JSON NULL,
  status ENUM('valid','invalid','imported','skipped') NOT NULL DEFAULT 'valid',
  errors JSON NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_import_rows_job FOREIGN KEY (import_job_id) REFERENCES import_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE export_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(80) NOT NULL,
  format ENUM('csv','xlsx','pdf','zip') NOT NULL DEFAULT 'csv',
  status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  file_path VARCHAR(500) NULL,
  requested_by BIGINT UNSIGNED NULL,
  expires_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  CONSTRAINT fk_export_jobs_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_export_jobs_user FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE export_files (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  export_job_id BIGINT UNSIGNED NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  size_bytes BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT fk_export_files_job FOREIGN KEY (export_job_id) REFERENCES export_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  -- message | grade | homework | attendance | system …
  type VARCHAR(40) NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NULL,
  -- مقصد کلیک روی نوتیفیکیشن
  link VARCHAR(255) NULL,
  read_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_notif_user_read (user_id, read_at),
  CONSTRAINT fk_notifications_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contact_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_name VARCHAR(200) NOT NULL,
  manager_name VARCHAR(200) NOT NULL,
  mobile VARCHAR(30) NOT NULL,
  city VARCHAR(120) NULL,
  message TEXT NULL,
  ip_address VARCHAR(45) NULL,
  status ENUM('new','contacted','closed') NOT NULL DEFAULT 'new',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_contact_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE partner_schools (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  address VARCHAR(300) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- تنظیمات غیرحساس سرویس‌های پیامک (کلیدهای API فقط در متغیرهای محیطی نگهداری می‌شوند)
CREATE TABLE sms_provider_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_key VARCHAR(40) NOT NULL UNIQUE,
  name_fa VARCHAR(120) NOT NULL,
  color VARCHAR(20) NOT NULL DEFAULT 'green',
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  priority TINYINT UNSIGNED NOT NULL DEFAULT 1,
  line_number VARCHAR(40) NULL,
  otp_template_id VARCHAR(80) NULL,
  otp_param_name VARCHAR(40) NOT NULL DEFAULT 'Code',
  absent_template_id VARCHAR(80) NULL,
  last_test_status ENUM('never','success','failed') NOT NULL DEFAULT 'never',
  last_test_at TIMESTAMP NULL,
  last_error TEXT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- کدهای یکبارمصرف (بازیابی رمز عبور با پیامک)
CREATE TABLE otp_codes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(20) NOT NULL,
  purpose VARCHAR(40) NOT NULL DEFAULT 'password_reset',
  code_hash VARCHAR(255) NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  INDEX idx_otp_mobile_purpose (mobile, purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- تنظیمات عمومی سامانه (کلید/مقدار) — مثل sms_price
CREATE TABLE system_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(120) NOT NULL UNIQUE,
  value TEXT NULL,
  updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جداول فریم‌ورک لاراول (SESSION_DRIVER=database / CACHE_STORE=database / QUEUE_CONNECTION=database)
CREATE TABLE migrations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  migration VARCHAR(255) NOT NULL,
  batch INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sessions (
  id VARCHAR(255) NOT NULL PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  user_agent TEXT NULL,
  payload LONGTEXT NOT NULL,
  last_activity INT NOT NULL,
  INDEX sessions_user_id_index (user_id),
  INDEX sessions_last_activity_index (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cache (
  `key` VARCHAR(255) NOT NULL PRIMARY KEY,
  value MEDIUMTEXT NOT NULL,
  expiration INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cache_locks (
  `key` VARCHAR(255) NOT NULL PRIMARY KEY,
  owner VARCHAR(255) NOT NULL,
  expiration INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  queue VARCHAR(255) NOT NULL,
  payload LONGTEXT NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL,
  reserved_at INT UNSIGNED NULL,
  available_at INT UNSIGNED NOT NULL,
  created_at INT UNSIGNED NOT NULL,
  INDEX jobs_queue_index (queue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════
-- QR ورود — توکن دائمی تا تغییر رمز عبور
-- ═══════════════════════════════════════════════════════════════
CREATE TABLE qr_login_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(64) NOT NULL,
  token_plain VARCHAR(32) NOT NULL,
  revoke_reason VARCHAR(80) NULL,
  revoked TINYINT(1) NOT NULL DEFAULT 0,
  expires_at TIMESTAMP NULL,
  last_used_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  INDEX idx_qr_user (user_id),
  INDEX idx_qr_hash (token_hash),
  CONSTRAINT fk_qr_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════
-- اخبار داشبورد
-- ═══════════════════════════════════════════════════════════════
CREATE TABLE dashboard_news (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  sender_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  is_important TINYINT(1) NOT NULL DEFAULT 0,
  audience_type ENUM('all','role','classroom','user') NOT NULL DEFAULT 'all',
  audience_value VARCHAR(80) NULL,
  expires_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_news_school (school_id),
  CONSTRAINT fk_news_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_news_sender FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dashboard_news_reads (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  news_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  read_at TIMESTAMP NULL,
  UNIQUE KEY uq_news_read (news_id, user_id),
  CONSTRAINT fk_news_reads_news FOREIGN KEY (news_id) REFERENCES dashboard_news(id) ON DELETE CASCADE,
  CONSTRAINT fk_news_reads_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════
-- نظرسنجی زنده داخل کلاس آنلاین
-- ═══════════════════════════════════════════════════════════════
CREATE TABLE online_class_polls (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  online_class_id BIGINT UNSIGNED NOT NULL,
  question VARCHAR(500) NOT NULL,
  options JSON NOT NULL,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  closed_at TIMESTAMP NULL,
  INDEX idx_polls_class (online_class_id),
  CONSTRAINT fk_polls_class FOREIGN KEY (online_class_id) REFERENCES online_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_polls_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE online_class_poll_votes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  poll_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  option_index TINYINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL,
  UNIQUE KEY uq_poll_vote (poll_id, user_id),
  CONSTRAINT fk_poll_votes_poll FOREIGN KEY (poll_id) REFERENCES online_class_polls(id) ON DELETE CASCADE,
  CONSTRAINT fk_poll_votes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════
-- سال تحصیلی
-- ═══════════════════════════════════════════════════════════════
CREATE TABLE school_years (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(120) NOT NULL,
  starts_on DATE NULL,
  ends_on DATE NULL,
  first_term_ends_on DATE NULL,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  promoted_students INT UNSIGNED NOT NULL DEFAULT 0,
  graduated_students INT UNSIGNED NOT NULL DEFAULT 0,
  closed_at TIMESTAMP NULL,
  closed_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_years_school (school_id),
  CONSTRAINT fk_years_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_years_closed_by FOREIGN KEY (closed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════
CREATE TABLE teacher_subjects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  teacher_id BIGINT UNSIGNED NOT NULL,
  subject_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL,
  UNIQUE KEY uniq_ts (teacher_id, subject_id),
  CONSTRAINT fk_ts_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_ts_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
  CONSTRAINT fk_ts_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════
CREATE TABLE temp_credentials (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  person_type ENUM('teacher','student') NOT NULL DEFAULT 'teacher',
  plain_password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL,
  UNIQUE KEY uniq_tc_user (user_id),
  CONSTRAINT fk_tc_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_tc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

