-- =========================================================================
-- EduTrack — schema only, idempotent. NO DATA.
--
-- Generated 2026-07-23 14:40 by database/generate_schema_sql.php from `rvfcyvys_xcpo`.
-- 79 tables. WordPress `oric_*` tables are excluded.
--
-- Safe to run repeatedly on a live database:
--   * missing TABLES  are created (CREATE TABLE IF NOT EXISTS)
--   * missing COLUMNS are added   (ADD COLUMN IF NOT EXISTS)
--   * missing INDEXES are added   (ADD INDEX  IF NOT EXISTS)
--
-- It never DROPs anything and never MODIFYs an existing column, so a
-- column whose type has drifted is left alone rather than rewritten —
-- altering a populated column can truncate data, so that stays manual.
--
-- Foreign keys are intentionally omitted: adding one to a live table
-- fails outright if any existing row violates it.
--
-- Requires MariaDB 10.0.2+ (uses ADD COLUMN/INDEX IF NOT EXISTS).
-- BACK UP FIRST:  mysqldump -u USER -p DBNAME > backup.sql
-- =========================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- -------------------------------------------------------------------------
-- ai_agent_memory
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_agent_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `memory_key` varchar(200) NOT NULL,
  `memory_value` text NOT NULL,
  `memory_type` enum('school_fact','user_preference','learned_pattern','correction') DEFAULT 'school_fact',
  `confidence` decimal(3,2) DEFAULT 1.00,
  `source_message_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `memory_key` (`memory_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_agent_memory` ADD COLUMN IF NOT EXISTS `memory_key` varchar(200) NOT NULL AFTER `id`;
ALTER TABLE `ai_agent_memory` ADD COLUMN IF NOT EXISTS `memory_value` text NOT NULL AFTER `memory_key`;
ALTER TABLE `ai_agent_memory` ADD COLUMN IF NOT EXISTS `memory_type` enum('school_fact','user_preference','learned_pattern','correction') DEFAULT 'school_fact' AFTER `memory_value`;
ALTER TABLE `ai_agent_memory` ADD COLUMN IF NOT EXISTS `confidence` decimal(3,2) DEFAULT 1.00 AFTER `memory_type`;
ALTER TABLE `ai_agent_memory` ADD COLUMN IF NOT EXISTS `source_message_id` int(11) DEFAULT NULL AFTER `confidence`;
ALTER TABLE `ai_agent_memory` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `source_message_id`;
ALTER TABLE `ai_agent_memory` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `ai_agent_memory` ADD UNIQUE INDEX IF NOT EXISTS `memory_key` (`memory_key`);

-- -------------------------------------------------------------------------
-- ai_chat_logs
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_chat_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(100) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `role` enum('user','assistant') NOT NULL,
  `message` text NOT NULL,
  `provider_used` varchar(50) DEFAULT NULL,
  `action_taken` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `thread_id` varchar(100) DEFAULT NULL,
  `message_type` enum('text','action_result','clarification','error','system') DEFAULT 'text',
  `actions_taken` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`actions_taken`)),
  `tokens_used` int(11) DEFAULT NULL,
  `response_ms` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `session_id` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `user_id` int(11) DEFAULT NULL AFTER `session_id`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `role` enum('user','assistant') NOT NULL AFTER `user_id`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `message` text NOT NULL AFTER `role`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `provider_used` varchar(50) DEFAULT NULL AFTER `message`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `action_taken` varchar(100) DEFAULT NULL AFTER `provider_used`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `action_taken`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `thread_id` varchar(100) DEFAULT NULL AFTER `created_at`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `message_type` enum('text','action_result','clarification','error','system') DEFAULT 'text' AFTER `thread_id`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `actions_taken` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`actions_taken`)) AFTER `message_type`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `tokens_used` int(11) DEFAULT NULL AFTER `actions_taken`;
ALTER TABLE `ai_chat_logs` ADD COLUMN IF NOT EXISTS `response_ms` int(11) DEFAULT NULL AFTER `tokens_used`;

-- -------------------------------------------------------------------------
-- ai_engine_config
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_engine_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_engine_config` ADD COLUMN IF NOT EXISTS `setting_key` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `ai_engine_config` ADD COLUMN IF NOT EXISTS `setting_value` text DEFAULT NULL AFTER `setting_key`;
ALTER TABLE `ai_engine_config` ADD COLUMN IF NOT EXISTS `updated_by` int(11) DEFAULT NULL AFTER `setting_value`;
ALTER TABLE `ai_engine_config` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `updated_by`;
ALTER TABLE `ai_engine_config` ADD UNIQUE INDEX IF NOT EXISTS `setting_key` (`setting_key`);

-- -------------------------------------------------------------------------
-- ai_generation_chunks
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_generation_chunks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `chunk_index` int(11) NOT NULL,
  `topic` varchar(255) DEFAULT NULL,
  `word_count` int(11) NOT NULL DEFAULT 0,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `questions_saved` int(11) NOT NULL DEFAULT 0,
  `error_message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `job_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `chunk_index` int(11) NOT NULL AFTER `job_id`;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `topic` varchar(255) DEFAULT NULL AFTER `chunk_index`;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `word_count` int(11) NOT NULL DEFAULT 0 AFTER `topic`;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `status` varchar(20) NOT NULL DEFAULT 'pending' AFTER `word_count`;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `questions_saved` int(11) NOT NULL DEFAULT 0 AFTER `status`;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `error_message` text DEFAULT NULL AFTER `questions_saved`;
ALTER TABLE `ai_generation_chunks` ADD COLUMN IF NOT EXISTS `created_at` timestamp NULL DEFAULT current_timestamp() AFTER `error_message`;

-- -------------------------------------------------------------------------
-- ai_generation_jobs
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_generation_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `material_id` int(11) DEFAULT NULL,
  `subject_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `term` varchar(50) DEFAULT NULL,
  `academic_year` varchar(20) DEFAULT NULL,
  `difficulty` enum('easy','medium','hard') NOT NULL DEFAULT 'medium',
  `mcq_count` int(11) NOT NULL DEFAULT 0,
  `theory_count` int(11) NOT NULL DEFAULT 0,
  `fill_blank_count` int(11) NOT NULL DEFAULT 0,
  `diagram_count` int(11) NOT NULL DEFAULT 0,
  `chunk_count` int(11) DEFAULT 0,
  `status` enum('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
  `result_summary` varchar(255) DEFAULT NULL,
  `refined` tinyint(1) NOT NULL DEFAULT 0,
  `refiner_engine` varchar(50) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL,
  `engine_used` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `material_id` (`material_id`),
  KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `material_id` int(11) DEFAULT NULL AFTER `id`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `material_id`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `subject_id`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `term` varchar(50) DEFAULT NULL AFTER `class_name`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) DEFAULT NULL AFTER `term`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `difficulty` enum('easy','medium','hard') NOT NULL DEFAULT 'medium' AFTER `academic_year`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `mcq_count` int(11) NOT NULL DEFAULT 0 AFTER `difficulty`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `theory_count` int(11) NOT NULL DEFAULT 0 AFTER `mcq_count`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `fill_blank_count` int(11) NOT NULL DEFAULT 0 AFTER `theory_count`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `diagram_count` int(11) NOT NULL DEFAULT 0 AFTER `fill_blank_count`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `chunk_count` int(11) DEFAULT 0 AFTER `diagram_count`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `status` enum('pending','processing','done','failed') NOT NULL DEFAULT 'pending' AFTER `chunk_count`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `result_summary` varchar(255) DEFAULT NULL AFTER `status`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `refined` tinyint(1) NOT NULL DEFAULT 0 AFTER `result_summary`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `refiner_engine` varchar(50) DEFAULT NULL AFTER `refined`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `error_message` text DEFAULT NULL AFTER `refiner_engine`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `error_message`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `completed_at` timestamp NULL DEFAULT NULL AFTER `created_at`;
ALTER TABLE `ai_generation_jobs` ADD COLUMN IF NOT EXISTS `engine_used` varchar(20) DEFAULT NULL AFTER `completed_at`;
ALTER TABLE `ai_generation_jobs` ADD INDEX IF NOT EXISTS `material_id` (`material_id`);
ALTER TABLE `ai_generation_jobs` ADD INDEX IF NOT EXISTS `subject_id` (`subject_id`);

-- -------------------------------------------------------------------------
-- ai_generation_quota
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_generation_quota` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_code` varchar(50) NOT NULL,
  `month_year` char(7) NOT NULL,
  `jobs_used` int(11) NOT NULL DEFAULT 0,
  `word_count` int(11) NOT NULL DEFAULT 0,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_school_month` (`school_code`,`month_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_generation_quota` ADD COLUMN IF NOT EXISTS `school_code` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `ai_generation_quota` ADD COLUMN IF NOT EXISTS `month_year` char(7) NOT NULL AFTER `school_code`;
ALTER TABLE `ai_generation_quota` ADD COLUMN IF NOT EXISTS `jobs_used` int(11) NOT NULL DEFAULT 0 AFTER `month_year`;
ALTER TABLE `ai_generation_quota` ADD COLUMN IF NOT EXISTS `word_count` int(11) NOT NULL DEFAULT 0 AFTER `jobs_used`;
ALTER TABLE `ai_generation_quota` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `word_count`;
ALTER TABLE `ai_generation_quota` ADD UNIQUE INDEX IF NOT EXISTS `uq_school_month` (`school_code`,`month_year`);

-- -------------------------------------------------------------------------
-- ai_models
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_models` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model_name` varchar(100) NOT NULL,
  `model_version` varchar(20) NOT NULL,
  `model_data` longtext NOT NULL,
  `accuracy` decimal(5,4) DEFAULT NULL,
  `training_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_model` (`model_name`,`model_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_models` ADD COLUMN IF NOT EXISTS `model_name` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `ai_models` ADD COLUMN IF NOT EXISTS `model_version` varchar(20) NOT NULL AFTER `model_name`;
ALTER TABLE `ai_models` ADD COLUMN IF NOT EXISTS `model_data` longtext NOT NULL AFTER `model_version`;
ALTER TABLE `ai_models` ADD COLUMN IF NOT EXISTS `accuracy` decimal(5,4) DEFAULT NULL AFTER `model_data`;
ALTER TABLE `ai_models` ADD COLUMN IF NOT EXISTS `training_date` datetime DEFAULT NULL AFTER `accuracy`;
ALTER TABLE `ai_models` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `training_date`;
ALTER TABLE `ai_models` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `ai_models` ADD UNIQUE INDEX IF NOT EXISTS `unique_model` (`model_name`,`model_version`);

-- -------------------------------------------------------------------------
-- ai_pending_tasks
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_pending_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` varchar(100) NOT NULL,
  `task_plan` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`task_plan`)),
  `current_step` int(11) DEFAULT 0,
  `total_steps` int(11) DEFAULT 1,
  `status` enum('planning','running','paused','completed','failed') DEFAULT 'planning',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_pending_tasks` ADD COLUMN IF NOT EXISTS `thread_id` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `ai_pending_tasks` ADD COLUMN IF NOT EXISTS `task_plan` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`task_plan`)) AFTER `thread_id`;
ALTER TABLE `ai_pending_tasks` ADD COLUMN IF NOT EXISTS `current_step` int(11) DEFAULT 0 AFTER `task_plan`;
ALTER TABLE `ai_pending_tasks` ADD COLUMN IF NOT EXISTS `total_steps` int(11) DEFAULT 1 AFTER `current_step`;
ALTER TABLE `ai_pending_tasks` ADD COLUMN IF NOT EXISTS `status` enum('planning','running','paused','completed','failed') DEFAULT 'planning' AFTER `total_steps`;
ALTER TABLE `ai_pending_tasks` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `status`;
ALTER TABLE `ai_pending_tasks` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- ai_predictions_cache
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_predictions_cache` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cache_key` varchar(255) NOT NULL,
  `prediction_data` longtext NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `cache_key` (`cache_key`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_predictions_cache` ADD COLUMN IF NOT EXISTS `cache_key` varchar(255) NOT NULL AFTER `id`;
ALTER TABLE `ai_predictions_cache` ADD COLUMN IF NOT EXISTS `prediction_data` longtext NOT NULL AFTER `cache_key`;
ALTER TABLE `ai_predictions_cache` ADD COLUMN IF NOT EXISTS `expires_at` datetime NOT NULL AFTER `prediction_data`;
ALTER TABLE `ai_predictions_cache` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `expires_at`;
ALTER TABLE `ai_predictions_cache` ADD UNIQUE INDEX IF NOT EXISTS `cache_key` (`cache_key`);
ALTER TABLE `ai_predictions_cache` ADD INDEX IF NOT EXISTS `idx_expires` (`expires_at`);

-- -------------------------------------------------------------------------
-- ai_schedules
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_type` varchar(100) NOT NULL,
  `schedule_description` varchar(200) NOT NULL,
  `cron_expression` varchar(50) DEFAULT NULL,
  `day_of_week` varchar(20) DEFAULT NULL,
  `run_time` time DEFAULT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_run` datetime DEFAULT NULL,
  `next_run` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `task_type` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `schedule_description` varchar(200) NOT NULL AFTER `task_type`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `cron_expression` varchar(50) DEFAULT NULL AFTER `schedule_description`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `day_of_week` varchar(20) DEFAULT NULL AFTER `cron_expression`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `run_time` time DEFAULT NULL AFTER `day_of_week`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)) AFTER `run_time`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `payload`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `last_run` datetime DEFAULT NULL AFTER `is_active`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `next_run` datetime DEFAULT NULL AFTER `last_run`;
ALTER TABLE `ai_schedules` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `next_run`;

-- -------------------------------------------------------------------------
-- ai_study_materials
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_study_materials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `original_filename` varchar(255) NOT NULL,
  `stored_filename` varchar(255) NOT NULL,
  `file_type` varchar(20) NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `page_count` int(11) NOT NULL DEFAULT 0,
  `extracted_content` longtext DEFAULT NULL,
  `cleaned_content` longtext DEFAULT NULL,
  `cleaned_word_count` int(11) NOT NULL DEFAULT 0,
  `chunk_count` int(11) NOT NULL DEFAULT 0,
  `extraction_status` enum('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
  `uploaded_by` int(11) DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `word_count` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `subject_id`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `original_filename` varchar(255) NOT NULL AFTER `class_name`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `stored_filename` varchar(255) NOT NULL AFTER `original_filename`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `file_type` varchar(20) NOT NULL AFTER `stored_filename`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `file_size` int(11) DEFAULT NULL AFTER `file_type`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `page_count` int(11) NOT NULL DEFAULT 0 AFTER `file_size`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `extracted_content` longtext DEFAULT NULL AFTER `page_count`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `cleaned_content` longtext DEFAULT NULL AFTER `extracted_content`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `cleaned_word_count` int(11) NOT NULL DEFAULT 0 AFTER `cleaned_content`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `chunk_count` int(11) NOT NULL DEFAULT 0 AFTER `cleaned_word_count`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `extraction_status` enum('pending','processing','done','failed') NOT NULL DEFAULT 'pending' AFTER `chunk_count`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `uploaded_by` int(11) DEFAULT NULL AFTER `extraction_status`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `uploaded_by`;
ALTER TABLE `ai_study_materials` ADD COLUMN IF NOT EXISTS `word_count` int(11) NOT NULL DEFAULT 0 AFTER `uploaded_at`;
ALTER TABLE `ai_study_materials` ADD INDEX IF NOT EXISTS `subject_id` (`subject_id`);

-- -------------------------------------------------------------------------
-- ai_tasks
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_type` varchar(100) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `status` enum('pending','running','completed','failed') NOT NULL DEFAULT 'pending',
  `result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`result`)),
  `triggered_by` enum('manual','scheduled','chat') DEFAULT 'manual',
  `provider_used` varchar(50) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `scheduled_at` datetime DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `task_type` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)) AFTER `task_type`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `status` enum('pending','running','completed','failed') NOT NULL DEFAULT 'pending' AFTER `payload`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`result`)) AFTER `status`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `triggered_by` enum('manual','scheduled','chat') DEFAULT 'manual' AFTER `result`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `provider_used` varchar(50) DEFAULT NULL AFTER `triggered_by`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `provider_used`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `scheduled_at` datetime DEFAULT NULL AFTER `created_by`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `started_at` datetime DEFAULT NULL AFTER `scheduled_at`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `completed_at` datetime DEFAULT NULL AFTER `started_at`;
ALTER TABLE `ai_tasks` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `completed_at`;

-- -------------------------------------------------------------------------
-- ai_training_logs
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_training_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model_name` varchar(100) NOT NULL,
  `accuracy` decimal(5,4) DEFAULT NULL,
  `samples_used` int(11) DEFAULT NULL,
  `training_date` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_model` (`model_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_training_logs` ADD COLUMN IF NOT EXISTS `model_name` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `ai_training_logs` ADD COLUMN IF NOT EXISTS `accuracy` decimal(5,4) DEFAULT NULL AFTER `model_name`;
ALTER TABLE `ai_training_logs` ADD COLUMN IF NOT EXISTS `samples_used` int(11) DEFAULT NULL AFTER `accuracy`;
ALTER TABLE `ai_training_logs` ADD COLUMN IF NOT EXISTS `training_date` datetime NOT NULL AFTER `samples_used`;
ALTER TABLE `ai_training_logs` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `training_date`;
ALTER TABLE `ai_training_logs` ADD INDEX IF NOT EXISTS `idx_model` (`model_name`);

-- -------------------------------------------------------------------------
-- ai_usage_log
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_usage_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider` varchar(50) NOT NULL,
  `prompt_preview` varchar(200) DEFAULT NULL,
  `response_preview` varchar(200) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `task_type` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `ai_usage_log` ADD COLUMN IF NOT EXISTS `provider` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `ai_usage_log` ADD COLUMN IF NOT EXISTS `prompt_preview` varchar(200) DEFAULT NULL AFTER `provider`;
ALTER TABLE `ai_usage_log` ADD COLUMN IF NOT EXISTS `response_preview` varchar(200) DEFAULT NULL AFTER `prompt_preview`;
ALTER TABLE `ai_usage_log` ADD COLUMN IF NOT EXISTS `user_id` int(11) DEFAULT NULL AFTER `response_preview`;
ALTER TABLE `ai_usage_log` ADD COLUMN IF NOT EXISTS `task_type` varchar(100) DEFAULT NULL AFTER `user_id`;
ALTER TABLE `ai_usage_log` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `task_type`;

-- -------------------------------------------------------------------------
-- announcements
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `target_role` varchar(50) DEFAULT 'all',
  `is_pinned` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_target` (`target_role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `announcements` ADD COLUMN IF NOT EXISTS `title` varchar(255) NOT NULL AFTER `id`;
ALTER TABLE `announcements` ADD COLUMN IF NOT EXISTS `body` text NOT NULL AFTER `title`;
ALTER TABLE `announcements` ADD COLUMN IF NOT EXISTS `target_role` varchar(50) DEFAULT 'all' AFTER `body`;
ALTER TABLE `announcements` ADD COLUMN IF NOT EXISTS `is_pinned` tinyint(1) NOT NULL DEFAULT 0 AFTER `target_role`;
ALTER TABLE `announcements` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `is_pinned`;
ALTER TABLE `announcements` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `announcements` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `announcements` ADD INDEX IF NOT EXISTS `idx_created_at` (`created_at`);
ALTER TABLE `announcements` ADD INDEX IF NOT EXISTS `idx_target` (`target_role`);

-- -------------------------------------------------------------------------
-- attendance
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `attendance_date` date NOT NULL,
  `status` enum('present','absent','late','excused') NOT NULL DEFAULT 'present',
  `recorded_by` int(11) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_attendance` (`student_id`,`attendance_date`),
  KEY `idx_class_date` (`class_name`,`attendance_date`),
  KEY `idx_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `attendance` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `attendance` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `student_id`;
ALTER TABLE `attendance` ADD COLUMN IF NOT EXISTS `attendance_date` date NOT NULL AFTER `class_name`;
ALTER TABLE `attendance` ADD COLUMN IF NOT EXISTS `status` enum('present','absent','late','excused') NOT NULL DEFAULT 'present' AFTER `attendance_date`;
ALTER TABLE `attendance` ADD COLUMN IF NOT EXISTS `recorded_by` int(11) DEFAULT NULL AFTER `status`;
ALTER TABLE `attendance` ADD COLUMN IF NOT EXISTS `notes` varchar(255) DEFAULT NULL AFTER `recorded_by`;
ALTER TABLE `attendance` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `notes`;
ALTER TABLE `attendance` ADD UNIQUE INDEX IF NOT EXISTS `unique_attendance` (`student_id`,`attendance_date`);
ALTER TABLE `attendance` ADD INDEX IF NOT EXISTS `idx_class_date` (`class_name`,`attendance_date`);
ALTER TABLE `attendance` ADD INDEX IF NOT EXISTS `idx_student` (`student_id`);

-- -------------------------------------------------------------------------
-- blog_posts
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `blog_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `tags` varchar(500) DEFAULT NULL,
  `excerpt` text DEFAULT NULL,
  `html_content` longtext NOT NULL,
  `reading_time_minutes` int(11) DEFAULT 5,
  `status` enum('published','draft','archived') DEFAULT 'draft',
  `topic_source` text DEFAULT NULL,
  `view_count` int(11) DEFAULT 0,
  `published_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `idx_status` (`status`),
  KEY `idx_category` (`category`),
  KEY `idx_published` (`published_at`),
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `topic_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `title` varchar(255) NOT NULL AFTER `topic_id`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `slug` varchar(255) NOT NULL AFTER `title`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `meta_description` varchar(255) DEFAULT NULL AFTER `slug`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `category` varchar(100) DEFAULT NULL AFTER `meta_description`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `tags` varchar(500) DEFAULT NULL AFTER `category`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `excerpt` text DEFAULT NULL AFTER `tags`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `html_content` longtext NOT NULL AFTER `excerpt`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `reading_time_minutes` int(11) DEFAULT 5 AFTER `html_content`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `status` enum('published','draft','archived') DEFAULT 'draft' AFTER `reading_time_minutes`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `topic_source` text DEFAULT NULL AFTER `status`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `view_count` int(11) DEFAULT 0 AFTER `topic_source`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `published_at` datetime DEFAULT NULL AFTER `view_count`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `published_at`;
ALTER TABLE `blog_posts` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `blog_posts` ADD UNIQUE INDEX IF NOT EXISTS `slug` (`slug`);
ALTER TABLE `blog_posts` ADD INDEX IF NOT EXISTS `idx_status` (`status`);
ALTER TABLE `blog_posts` ADD INDEX IF NOT EXISTS `idx_category` (`category`);
ALTER TABLE `blog_posts` ADD INDEX IF NOT EXISTS `idx_published` (`published_at`);
ALTER TABLE `blog_posts` ADD INDEX IF NOT EXISTS `idx_slug` (`slug`);

-- -------------------------------------------------------------------------
-- blog_topics
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `blog_topics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_text` text NOT NULL,
  `use_count` int(11) DEFAULT 0,
  `max_uses` int(11) DEFAULT 2,
  `is_active` tinyint(1) DEFAULT 1,
  `last_used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `blog_topics` ADD COLUMN IF NOT EXISTS `topic_text` text NOT NULL AFTER `id`;
ALTER TABLE `blog_topics` ADD COLUMN IF NOT EXISTS `use_count` int(11) DEFAULT 0 AFTER `topic_text`;
ALTER TABLE `blog_topics` ADD COLUMN IF NOT EXISTS `max_uses` int(11) DEFAULT 2 AFTER `use_count`;
ALTER TABLE `blog_topics` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) DEFAULT 1 AFTER `max_uses`;
ALTER TABLE `blog_topics` ADD COLUMN IF NOT EXISTS `last_used_at` datetime DEFAULT NULL AFTER `is_active`;
ALTER TABLE `blog_topics` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `last_used_at`;

-- -------------------------------------------------------------------------
-- canteen_biometric_access_log
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `canteen_biometric_access_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `accessed_by` int(11) NOT NULL,
  `action` enum('enroll','verify','delete','export') NOT NULL,
  `method` enum('fingerprint','facial') DEFAULT NULL,
  `result` enum('success','failure','fallback') DEFAULT NULL,
  `accessed_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_student` (`student_id`),
  KEY `idx_accessed` (`accessed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `canteen_biometric_access_log` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `canteen_biometric_access_log` ADD COLUMN IF NOT EXISTS `accessed_by` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `canteen_biometric_access_log` ADD COLUMN IF NOT EXISTS `action` enum('enroll','verify','delete','export') NOT NULL AFTER `accessed_by`;
ALTER TABLE `canteen_biometric_access_log` ADD COLUMN IF NOT EXISTS `method` enum('fingerprint','facial') DEFAULT NULL AFTER `action`;
ALTER TABLE `canteen_biometric_access_log` ADD COLUMN IF NOT EXISTS `result` enum('success','failure','fallback') DEFAULT NULL AFTER `method`;
ALTER TABLE `canteen_biometric_access_log` ADD COLUMN IF NOT EXISTS `accessed_at` datetime DEFAULT current_timestamp() AFTER `result`;
ALTER TABLE `canteen_biometric_access_log` ADD INDEX IF NOT EXISTS `idx_student` (`student_id`);
ALTER TABLE `canteen_biometric_access_log` ADD INDEX IF NOT EXISTS `idx_accessed` (`accessed_at`);

-- -------------------------------------------------------------------------
-- canteen_biometric_credentials
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `canteen_biometric_credentials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `credential_id` varchar(512) DEFAULT NULL,
  `public_key` text DEFAULT NULL,
  `facial_descriptor` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`facial_descriptor`)),
  `facial_enrolled_at` datetime DEFAULT NULL,
  `fingerprint_enrolled_at` datetime DEFAULT NULL,
  `enrolled_by` int(11) DEFAULT NULL,
  `last_updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `student_id` (`student_id`),
  KEY `idx_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `credential_id` varchar(512) DEFAULT NULL AFTER `student_id`;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `public_key` text DEFAULT NULL AFTER `credential_id`;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `facial_descriptor` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`facial_descriptor`)) AFTER `public_key`;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `facial_enrolled_at` datetime DEFAULT NULL AFTER `facial_descriptor`;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `fingerprint_enrolled_at` datetime DEFAULT NULL AFTER `facial_enrolled_at`;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `enrolled_by` int(11) DEFAULT NULL AFTER `fingerprint_enrolled_at`;
ALTER TABLE `canteen_biometric_credentials` ADD COLUMN IF NOT EXISTS `last_updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `enrolled_by`;
ALTER TABLE `canteen_biometric_credentials` ADD UNIQUE INDEX IF NOT EXISTS `student_id` (`student_id`);
ALTER TABLE `canteen_biometric_credentials` ADD INDEX IF NOT EXISTS `idx_student` (`student_id`);

-- -------------------------------------------------------------------------
-- canteen_entitlements
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `canteen_entitlements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `fee_payment_id` int(11) DEFAULT NULL,
  `qr_token` varchar(128) NOT NULL,
  `qr_token_expires_at` datetime NOT NULL,
  `status` enum('pending','confirmed','absent','cancelled') DEFAULT 'pending',
  `verification_method` enum('qr','fingerprint','facial','manual_id') DEFAULT NULL,
  `confirmed_at` datetime DEFAULT NULL,
  `confirmed_by` int(11) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL,
  `offline_queued` tinyint(1) DEFAULT 0,
  `offline_synced_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `qr_token` (`qr_token`),
  UNIQUE KEY `unique_student_session` (`student_id`,`session_id`),
  KEY `idx_token` (`qr_token`),
  KEY `idx_session` (`session_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `session_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `fee_payment_id` int(11) DEFAULT NULL AFTER `session_id`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `qr_token` varchar(128) NOT NULL AFTER `fee_payment_id`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `qr_token_expires_at` datetime NOT NULL AFTER `qr_token`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `status` enum('pending','confirmed','absent','cancelled') DEFAULT 'pending' AFTER `qr_token_expires_at`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `verification_method` enum('qr','fingerprint','facial','manual_id') DEFAULT NULL AFTER `status`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `confirmed_at` datetime DEFAULT NULL AFTER `verification_method`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `confirmed_by` int(11) DEFAULT NULL AFTER `confirmed_at`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `device_info` varchar(255) DEFAULT NULL AFTER `confirmed_by`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `offline_queued` tinyint(1) DEFAULT 0 AFTER `device_info`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `offline_synced_at` datetime DEFAULT NULL AFTER `offline_queued`;
ALTER TABLE `canteen_entitlements` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `offline_synced_at`;
ALTER TABLE `canteen_entitlements` ADD UNIQUE INDEX IF NOT EXISTS `qr_token` (`qr_token`);
ALTER TABLE `canteen_entitlements` ADD UNIQUE INDEX IF NOT EXISTS `unique_student_session` (`student_id`,`session_id`);
ALTER TABLE `canteen_entitlements` ADD INDEX IF NOT EXISTS `idx_token` (`qr_token`);
ALTER TABLE `canteen_entitlements` ADD INDEX IF NOT EXISTS `idx_session` (`session_id`);
ALTER TABLE `canteen_entitlements` ADD INDEX IF NOT EXISTS `idx_status` (`status`);

-- -------------------------------------------------------------------------
-- canteen_offline_queue
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `canteen_offline_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_token` varchar(128) DEFAULT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`payload`)),
  `synced` tinyint(1) DEFAULT 0,
  `synced_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `canteen_offline_queue` ADD COLUMN IF NOT EXISTS `device_token` varchar(128) DEFAULT NULL AFTER `id`;
ALTER TABLE `canteen_offline_queue` ADD COLUMN IF NOT EXISTS `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`payload`)) AFTER `device_token`;
ALTER TABLE `canteen_offline_queue` ADD COLUMN IF NOT EXISTS `synced` tinyint(1) DEFAULT 0 AFTER `payload`;
ALTER TABLE `canteen_offline_queue` ADD COLUMN IF NOT EXISTS `synced_at` datetime DEFAULT NULL AFTER `synced`;
ALTER TABLE `canteen_offline_queue` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `synced_at`;

-- -------------------------------------------------------------------------
-- canteen_scans
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `canteen_scans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entitlement_id` int(11) DEFAULT NULL,
  `student_id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `scan_result` enum('success','already_confirmed','invalid_token','expired_token','not_enrolled','manual_override') NOT NULL,
  `override_reason` varchar(50) DEFAULT NULL,
  `verification_method` enum('qr','fingerprint','facial','manual_id') DEFAULT NULL,
  `confidence_score` decimal(4,3) DEFAULT NULL,
  `scanned_by` int(11) NOT NULL,
  `scanned_at` datetime DEFAULT current_timestamp(),
  `device_info` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_session_date` (`session_id`,`scanned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `entitlement_id` int(11) DEFAULT NULL AFTER `id`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `entitlement_id`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `session_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `scan_result` enum('success','already_confirmed','invalid_token','expired_token','not_enrolled','manual_override') NOT NULL AFTER `session_id`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `override_reason` varchar(50) DEFAULT NULL AFTER `scan_result`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `verification_method` enum('qr','fingerprint','facial','manual_id') DEFAULT NULL AFTER `override_reason`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `confidence_score` decimal(4,3) DEFAULT NULL AFTER `verification_method`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `scanned_by` int(11) NOT NULL AFTER `confidence_score`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `scanned_at` datetime DEFAULT current_timestamp() AFTER `scanned_by`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `device_info` varchar(255) DEFAULT NULL AFTER `scanned_at`;
ALTER TABLE `canteen_scans` ADD COLUMN IF NOT EXISTS `ip_address` varchar(45) DEFAULT NULL AFTER `device_info`;
ALTER TABLE `canteen_scans` ADD INDEX IF NOT EXISTS `idx_session_date` (`session_id`,`scanned_at`);

-- -------------------------------------------------------------------------
-- canteen_sessions
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `canteen_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) DEFAULT 1,
  `session_name` varchar(50) NOT NULL,
  `session_date` date NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `price_per_meal` decimal(10,2) DEFAULT NULL,
  `classes_enrolled` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`classes_enrolled`)),
  `total_enrolled` int(11) DEFAULT 0,
  `status` enum('scheduled','active','closed','cancelled') DEFAULT 'scheduled',
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_date` (`session_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `school_id` int(11) DEFAULT 1 AFTER `id`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `session_name` varchar(50) NOT NULL AFTER `school_id`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `session_date` date NOT NULL AFTER `session_name`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `start_time` time DEFAULT NULL AFTER `session_date`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `end_time` time DEFAULT NULL AFTER `start_time`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `price_per_meal` decimal(10,2) DEFAULT NULL AFTER `end_time`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `classes_enrolled` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`classes_enrolled`)) AFTER `price_per_meal`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `total_enrolled` int(11) DEFAULT 0 AFTER `classes_enrolled`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `status` enum('scheduled','active','closed','cancelled') DEFAULT 'scheduled' AFTER `total_enrolled`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `status`;
ALTER TABLE `canteen_sessions` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `canteen_sessions` ADD INDEX IF NOT EXISTS `idx_date` (`session_date`);
ALTER TABLE `canteen_sessions` ADD INDEX IF NOT EXISTS `idx_status` (`status`);

-- -------------------------------------------------------------------------
-- canteen_settings
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `canteen_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) NOT NULL DEFAULT 1,
  `qr_enabled` tinyint(1) DEFAULT 1,
  `fingerprint_enabled` tinyint(1) DEFAULT 0,
  `facial_recognition_enabled` tinyint(1) DEFAULT 0,
  `manual_id_fallback` tinyint(1) DEFAULT 1,
  `qr_validity_hours` int(11) DEFAULT 24,
  `cook_pin_hash` varchar(255) DEFAULT NULL,
  `scanner_pin_hash` varchar(255) DEFAULT NULL,
  `auto_ai_report` tinyint(1) DEFAULT 1,
  `cook_dashboard_refresh_seconds` int(11) DEFAULT 30,
  `hardware_fingerprint_model` varchar(100) DEFAULT NULL,
  `hardware_webcam_model` varchar(100) DEFAULT NULL,
  `facial_min_confidence` decimal(3,2) DEFAULT 0.85,
  `setup_completed` tinyint(1) DEFAULT 0,
  `setup_completed_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_school` (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `school_id` int(11) NOT NULL DEFAULT 1 AFTER `id`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `qr_enabled` tinyint(1) DEFAULT 1 AFTER `school_id`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `fingerprint_enabled` tinyint(1) DEFAULT 0 AFTER `qr_enabled`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `facial_recognition_enabled` tinyint(1) DEFAULT 0 AFTER `fingerprint_enabled`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `manual_id_fallback` tinyint(1) DEFAULT 1 AFTER `facial_recognition_enabled`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `qr_validity_hours` int(11) DEFAULT 24 AFTER `manual_id_fallback`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `cook_pin_hash` varchar(255) DEFAULT NULL AFTER `qr_validity_hours`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `scanner_pin_hash` varchar(255) DEFAULT NULL AFTER `cook_pin_hash`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `auto_ai_report` tinyint(1) DEFAULT 1 AFTER `scanner_pin_hash`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `cook_dashboard_refresh_seconds` int(11) DEFAULT 30 AFTER `auto_ai_report`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `hardware_fingerprint_model` varchar(100) DEFAULT NULL AFTER `cook_dashboard_refresh_seconds`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `hardware_webcam_model` varchar(100) DEFAULT NULL AFTER `hardware_fingerprint_model`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `facial_min_confidence` decimal(3,2) DEFAULT 0.85 AFTER `hardware_webcam_model`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `setup_completed` tinyint(1) DEFAULT 0 AFTER `facial_min_confidence`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `setup_completed_at` datetime DEFAULT NULL AFTER `setup_completed`;
ALTER TABLE `canteen_settings` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `setup_completed_at`;
ALTER TABLE `canteen_settings` ADD UNIQUE INDEX IF NOT EXISTS `unique_school` (`school_id`);

-- -------------------------------------------------------------------------
-- classes
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) NOT NULL,
  `class_level` varchar(50) DEFAULT NULL,
  `class_teacher_id` int(11) DEFAULT NULL,
  `capacity` int(11) DEFAULT NULL,
  `academic_year` varchar(20) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_class_year` (`class_name`,`academic_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `classes` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `classes` ADD COLUMN IF NOT EXISTS `class_level` varchar(50) DEFAULT NULL AFTER `class_name`;
ALTER TABLE `classes` ADD COLUMN IF NOT EXISTS `class_teacher_id` int(11) DEFAULT NULL AFTER `class_level`;
ALTER TABLE `classes` ADD COLUMN IF NOT EXISTS `capacity` int(11) DEFAULT NULL AFTER `class_teacher_id`;
ALTER TABLE `classes` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) DEFAULT NULL AFTER `capacity`;
ALTER TABLE `classes` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `academic_year`;
ALTER TABLE `classes` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `is_active`;
ALTER TABLE `classes` ADD UNIQUE INDEX IF NOT EXISTS `unique_class_year` (`class_name`,`academic_year`);

-- -------------------------------------------------------------------------
-- class_subjects
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `class_subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(20) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `class_score_percent` int(11) DEFAULT 50,
  `exam_score_percent` int(11) DEFAULT 50,
  `max_class_score` decimal(5,2) DEFAULT 50.00,
  `max_exam_score` decimal(5,2) DEFAULT 50.00,
  `is_compulsory` enum('yes','no') DEFAULT 'yes',
  `display_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `academic_year` varchar(20) NOT NULL DEFAULT '',
  `term` varchar(20) NOT NULL DEFAULT '',
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_class_subject` (`class_name`,`subject_id`),
  KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `class_name` varchar(20) NOT NULL AFTER `id`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `class_name`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `class_score_percent` int(11) DEFAULT 50 AFTER `subject_id`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `exam_score_percent` int(11) DEFAULT 50 AFTER `class_score_percent`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `max_class_score` decimal(5,2) DEFAULT 50.00 AFTER `exam_score_percent`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `max_exam_score` decimal(5,2) DEFAULT 50.00 AFTER `max_class_score`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `is_compulsory` enum('yes','no') DEFAULT 'yes' AFTER `max_exam_score`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `display_order` int(11) DEFAULT 0 AFTER `is_compulsory`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `display_order`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL DEFAULT '' AFTER `created_at`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL DEFAULT '' AFTER `academic_year`;
ALTER TABLE `class_subjects` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) DEFAULT NULL AFTER `term`;
ALTER TABLE `class_subjects` ADD UNIQUE INDEX IF NOT EXISTS `unique_class_subject` (`class_name`,`subject_id`);
ALTER TABLE `class_subjects` ADD INDEX IF NOT EXISTS `subject_id` (`subject_id`);

-- -------------------------------------------------------------------------
-- coupons
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `coupons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_code` varchar(50) NOT NULL,
  `coupon_name` varchar(150) NOT NULL,
  `discount_type` enum('percentage','fixed') DEFAULT 'percentage',
  `discount_value` decimal(10,2) NOT NULL,
  `max_uses` int(11) DEFAULT NULL,
  `times_used` int(11) DEFAULT 0,
  `applicable_to` enum('all','specific_class','specific_fee') DEFAULT 'all',
  `applicable_class` varchar(100) DEFAULT NULL,
  `applicable_fee_id` int(11) DEFAULT NULL,
  `valid_from` date DEFAULT NULL,
  `valid_until` date NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `coupon_code` (`coupon_code`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `coupon_code` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `coupon_name` varchar(150) NOT NULL AFTER `coupon_code`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `discount_type` enum('percentage','fixed') DEFAULT 'percentage' AFTER `coupon_name`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `discount_value` decimal(10,2) NOT NULL AFTER `discount_type`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `max_uses` int(11) DEFAULT NULL AFTER `discount_value`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `times_used` int(11) DEFAULT 0 AFTER `max_uses`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `applicable_to` enum('all','specific_class','specific_fee') DEFAULT 'all' AFTER `times_used`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `applicable_class` varchar(100) DEFAULT NULL AFTER `applicable_to`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `applicable_fee_id` int(11) DEFAULT NULL AFTER `applicable_class`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `valid_from` date DEFAULT NULL AFTER `applicable_fee_id`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `valid_until` date NOT NULL AFTER `valid_from`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) DEFAULT 1 AFTER `valid_until`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `is_active`;
ALTER TABLE `coupons` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `coupons` ADD UNIQUE INDEX IF NOT EXISTS `coupon_code` (`coupon_code`);
ALTER TABLE `coupons` ADD INDEX IF NOT EXISTS `created_by` (`created_by`);

-- -------------------------------------------------------------------------
-- coupon_usages
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `coupon_usages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `payment_id` int(11) DEFAULT NULL,
  `original_amount` decimal(10,2) NOT NULL,
  `discount_amount` decimal(10,2) NOT NULL,
  `final_amount` decimal(10,2) NOT NULL,
  `used_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `coupon_id` (`coupon_id`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `coupon_usages` ADD COLUMN IF NOT EXISTS `coupon_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `coupon_usages` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `coupon_id`;
ALTER TABLE `coupon_usages` ADD COLUMN IF NOT EXISTS `payment_id` int(11) DEFAULT NULL AFTER `student_id`;
ALTER TABLE `coupon_usages` ADD COLUMN IF NOT EXISTS `original_amount` decimal(10,2) NOT NULL AFTER `payment_id`;
ALTER TABLE `coupon_usages` ADD COLUMN IF NOT EXISTS `discount_amount` decimal(10,2) NOT NULL AFTER `original_amount`;
ALTER TABLE `coupon_usages` ADD COLUMN IF NOT EXISTS `final_amount` decimal(10,2) NOT NULL AFTER `discount_amount`;
ALTER TABLE `coupon_usages` ADD COLUMN IF NOT EXISTS `used_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `final_amount`;
ALTER TABLE `coupon_usages` ADD INDEX IF NOT EXISTS `coupon_id` (`coupon_id`);
ALTER TABLE `coupon_usages` ADD INDEX IF NOT EXISTS `student_id` (`student_id`);

-- -------------------------------------------------------------------------
-- demo_requests
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `demo_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(200) NOT NULL,
  `contact_name` varchar(150) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `whatsapp` varchar(40) NOT NULL,
  `email` varchar(190) DEFAULT NULL,
  `region` varchar(120) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `access_code` varchar(20) DEFAULT NULL,
  `code_expires_at` datetime DEFAULT NULL,
  `uses_allowed` int(11) DEFAULT 50,
  `uses_count` int(11) DEFAULT 0,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `approved_at` datetime DEFAULT NULL,
  `last_access_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_code` (`access_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `school_name` varchar(200) NOT NULL AFTER `id`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `contact_name` varchar(150) NOT NULL AFTER `school_name`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `role` varchar(100) DEFAULT NULL AFTER `contact_name`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `whatsapp` varchar(40) NOT NULL AFTER `role`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `email` varchar(190) DEFAULT NULL AFTER `whatsapp`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `region` varchar(120) DEFAULT NULL AFTER `email`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `message` text DEFAULT NULL AFTER `region`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending' AFTER `message`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `access_code` varchar(20) DEFAULT NULL AFTER `status`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `code_expires_at` datetime DEFAULT NULL AFTER `access_code`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `uses_allowed` int(11) DEFAULT 50 AFTER `code_expires_at`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `uses_count` int(11) DEFAULT 0 AFTER `uses_allowed`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `ip_address` varchar(45) DEFAULT NULL AFTER `uses_count`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `created_at` datetime NOT NULL DEFAULT current_timestamp() AFTER `ip_address`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `approved_at` datetime DEFAULT NULL AFTER `created_at`;
ALTER TABLE `demo_requests` ADD COLUMN IF NOT EXISTS `last_access_at` datetime DEFAULT NULL AFTER `approved_at`;
ALTER TABLE `demo_requests` ADD INDEX IF NOT EXISTS `idx_status` (`status`);
ALTER TABLE `demo_requests` ADD INDEX IF NOT EXISTS `idx_code` (`access_code`);

-- -------------------------------------------------------------------------
-- exam_generation_jobs
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_generation_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) NOT NULL,
  `status` enum('queued','processing','retrying','completed','failed') NOT NULL DEFAULT 'queued',
  `request_payload` longtext NOT NULL,
  `attempts` int(11) NOT NULL DEFAULT 0,
  `max_attempts` int(11) NOT NULL DEFAULT 3,
  `available_at` datetime NOT NULL DEFAULT current_timestamp(),
  `started_at` datetime DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `exam_id` int(11) DEFAULT NULL,
  `generator_used` varchar(100) DEFAULT NULL,
  `job_warnings` longtext DEFAULT NULL,
  `last_error` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_exam_generation_status` (`status`,`available_at`),
  KEY `idx_exam_generation_creator` (`created_by`),
  KEY `idx_exam_generation_exam` (`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `created_by` int(11) NOT NULL AFTER `id`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `status` enum('queued','processing','retrying','completed','failed') NOT NULL DEFAULT 'queued' AFTER `created_by`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `request_payload` longtext NOT NULL AFTER `status`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `attempts` int(11) NOT NULL DEFAULT 0 AFTER `request_payload`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `max_attempts` int(11) NOT NULL DEFAULT 3 AFTER `attempts`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `available_at` datetime NOT NULL DEFAULT current_timestamp() AFTER `max_attempts`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `started_at` datetime DEFAULT NULL AFTER `available_at`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `locked_at` datetime DEFAULT NULL AFTER `started_at`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `completed_at` datetime DEFAULT NULL AFTER `locked_at`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `exam_id` int(11) DEFAULT NULL AFTER `completed_at`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `generator_used` varchar(100) DEFAULT NULL AFTER `exam_id`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `job_warnings` longtext DEFAULT NULL AFTER `generator_used`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `last_error` text DEFAULT NULL AFTER `job_warnings`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `last_error`;
ALTER TABLE `exam_generation_jobs` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `exam_generation_jobs` ADD INDEX IF NOT EXISTS `idx_exam_generation_status` (`status`,`available_at`);
ALTER TABLE `exam_generation_jobs` ADD INDEX IF NOT EXISTS `idx_exam_generation_creator` (`created_by`);
ALTER TABLE `exam_generation_jobs` ADD INDEX IF NOT EXISTS `idx_exam_generation_exam` (`exam_id`);

-- -------------------------------------------------------------------------
-- exam_generation_logs
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_generation_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `log_level` varchar(20) NOT NULL DEFAULT 'info',
  `message` varchar(255) NOT NULL,
  `context_json` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_exam_generation_logs_job` (`job_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_generation_logs` ADD COLUMN IF NOT EXISTS `job_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `exam_generation_logs` ADD COLUMN IF NOT EXISTS `log_level` varchar(20) NOT NULL DEFAULT 'info' AFTER `job_id`;
ALTER TABLE `exam_generation_logs` ADD COLUMN IF NOT EXISTS `message` varchar(255) NOT NULL AFTER `log_level`;
ALTER TABLE `exam_generation_logs` ADD COLUMN IF NOT EXISTS `context_json` longtext DEFAULT NULL AFTER `message`;
ALTER TABLE `exam_generation_logs` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `context_json`;
ALTER TABLE `exam_generation_logs` ADD INDEX IF NOT EXISTS `idx_exam_generation_logs_job` (`job_id`,`id`);

-- -------------------------------------------------------------------------
-- exam_papers
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_papers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `class_name` varchar(100) NOT NULL,
  `term` varchar(50) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `duration_minutes` int(11) NOT NULL DEFAULT 60,
  `total_marks` int(11) NOT NULL DEFAULT 100,
  `instructions` text DEFAULT NULL,
  `status` enum('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `paper_file_path` varchar(255) DEFAULT NULL,
  `answer_key_file_path` varchar(255) DEFAULT NULL,
  `section_config` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `title` varchar(255) NOT NULL AFTER `id`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `subject_id` int(11) DEFAULT NULL AFTER `title`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `subject_id`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `term` varchar(50) NOT NULL AFTER `class_name`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `term`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `duration_minutes` int(11) NOT NULL DEFAULT 60 AFTER `academic_year`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `total_marks` int(11) NOT NULL DEFAULT 100 AFTER `duration_minutes`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `instructions` text DEFAULT NULL AFTER `total_marks`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `status` enum('draft','published','archived') NOT NULL DEFAULT 'draft' AFTER `instructions`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `status`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `paper_file_path` varchar(255) DEFAULT NULL AFTER `updated_at`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `answer_key_file_path` varchar(255) DEFAULT NULL AFTER `paper_file_path`;
ALTER TABLE `exam_papers` ADD COLUMN IF NOT EXISTS `section_config` text DEFAULT NULL AFTER `answer_key_file_path`;
ALTER TABLE `exam_papers` ADD INDEX IF NOT EXISTS `subject_id` (`subject_id`);

-- -------------------------------------------------------------------------
-- exam_paper_questions
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_paper_questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `paper_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `marks` int(11) NOT NULL DEFAULT 1,
  `order_no` int(11) NOT NULL DEFAULT 0,
  `order_num` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_paper_question` (`paper_id`,`question_id`),
  KEY `question_id` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_paper_questions` ADD COLUMN IF NOT EXISTS `paper_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `exam_paper_questions` ADD COLUMN IF NOT EXISTS `question_id` int(11) NOT NULL AFTER `paper_id`;
ALTER TABLE `exam_paper_questions` ADD COLUMN IF NOT EXISTS `marks` int(11) NOT NULL DEFAULT 1 AFTER `question_id`;
ALTER TABLE `exam_paper_questions` ADD COLUMN IF NOT EXISTS `order_no` int(11) NOT NULL DEFAULT 0 AFTER `marks`;
ALTER TABLE `exam_paper_questions` ADD COLUMN IF NOT EXISTS `order_num` int(11) DEFAULT 0 AFTER `order_no`;
ALTER TABLE `exam_paper_questions` ADD UNIQUE INDEX IF NOT EXISTS `unique_paper_question` (`paper_id`,`question_id`);
ALTER TABLE `exam_paper_questions` ADD INDEX IF NOT EXISTS `question_id` (`question_id`);

-- -------------------------------------------------------------------------
-- exam_paper_templates
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_paper_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `description` varchar(255) NOT NULL DEFAULT '',
  `level` varchar(30) NOT NULL DEFAULT '',
  `structure` longtext NOT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_tmpl_active` (`is_active`,`is_system`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `name` varchar(120) NOT NULL AFTER `id`;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `description` varchar(255) NOT NULL DEFAULT '' AFTER `name`;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `level` varchar(30) NOT NULL DEFAULT '' AFTER `description`;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `structure` longtext NOT NULL AFTER `level`;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `is_system` tinyint(1) NOT NULL DEFAULT 0 AFTER `structure`;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `is_system`;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `is_active`;
ALTER TABLE `exam_paper_templates` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `exam_paper_templates` ADD INDEX IF NOT EXISTS `idx_tmpl_active` (`is_active`,`is_system`);

-- -------------------------------------------------------------------------
-- exam_questions
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_id` int(11) NOT NULL,
  `section` enum('objective','subjective','comprehension') NOT NULL,
  `question_number` int(11) NOT NULL,
  `question_text` text NOT NULL,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)),
  `correct_answer` varchar(255) DEFAULT NULL,
  `marks` int(11) NOT NULL,
  `model_answer` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `subject_id` int(11) DEFAULT NULL,
  `class_name` varchar(100) DEFAULT '',
  `term` varchar(50) DEFAULT NULL,
  `academic_year` varchar(20) DEFAULT NULL,
  `question_type` enum('mcq','theory','fill_blank','true_false','matching','comprehension') NOT NULL DEFAULT 'mcq',
  `question` text DEFAULT NULL,
  `option_a` varchar(500) DEFAULT NULL,
  `option_b` varchar(500) DEFAULT NULL,
  `option_c` varchar(500) DEFAULT NULL,
  `option_d` varchar(500) DEFAULT NULL,
  `difficulty` enum('easy','medium','hard') NOT NULL DEFAULT 'medium',
  `created_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `diagram_required` tinyint(1) NOT NULL DEFAULT 0,
  `diagram_type` varchar(50) DEFAULT NULL,
  `diagram_spec` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`diagram_spec`)),
  `ai_generated` tinyint(1) NOT NULL DEFAULT 0,
  `generation_job_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `chunk_topic` varchar(255) DEFAULT NULL,
  `moderation_note` text DEFAULT NULL,
  `refined` tinyint(1) NOT NULL DEFAULT 0,
  `extra_data` longtext DEFAULT NULL,
  `marking_guide` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `exam_id` (`exam_id`),
  KEY `idx_exam_questions_subject_id` (`subject_id`),
  KEY `fk_exam_questions_generation_job` (`generation_job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `exam_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `section` enum('objective','subjective','comprehension') NOT NULL AFTER `exam_id`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `question_number` int(11) NOT NULL AFTER `section`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `question_text` text NOT NULL AFTER `question_number`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)) AFTER `question_text`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `correct_answer` varchar(255) DEFAULT NULL AFTER `options`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `marks` int(11) NOT NULL AFTER `correct_answer`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `model_answer` text DEFAULT NULL AFTER `marks`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `created_at` timestamp NULL DEFAULT current_timestamp() AFTER `model_answer`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `subject_id` int(11) DEFAULT NULL AFTER `created_at`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) DEFAULT '' AFTER `subject_id`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `term` varchar(50) DEFAULT NULL AFTER `class_name`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) DEFAULT NULL AFTER `term`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `question_type` enum('mcq','theory','fill_blank','true_false','matching','comprehension') NOT NULL DEFAULT 'mcq' AFTER `academic_year`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `question` text DEFAULT NULL AFTER `question_type`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `option_a` varchar(500) DEFAULT NULL AFTER `question`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `option_b` varchar(500) DEFAULT NULL AFTER `option_a`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `option_c` varchar(500) DEFAULT NULL AFTER `option_b`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `option_d` varchar(500) DEFAULT NULL AFTER `option_c`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `difficulty` enum('easy','medium','hard') NOT NULL DEFAULT 'medium' AFTER `option_d`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `difficulty`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_by`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `diagram_required` tinyint(1) NOT NULL DEFAULT 0 AFTER `updated_at`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `diagram_type` varchar(50) DEFAULT NULL AFTER `diagram_required`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `diagram_spec` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`diagram_spec`)) AFTER `diagram_type`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `ai_generated` tinyint(1) NOT NULL DEFAULT 0 AFTER `diagram_spec`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `generation_job_id` int(11) DEFAULT NULL AFTER `ai_generated`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `generation_job_id`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `chunk_topic` varchar(255) DEFAULT NULL AFTER `is_active`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `moderation_note` text DEFAULT NULL AFTER `chunk_topic`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `refined` tinyint(1) NOT NULL DEFAULT 0 AFTER `moderation_note`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `extra_data` longtext DEFAULT NULL AFTER `refined`;
ALTER TABLE `exam_questions` ADD COLUMN IF NOT EXISTS `marking_guide` text DEFAULT NULL AFTER `extra_data`;
ALTER TABLE `exam_questions` ADD INDEX IF NOT EXISTS `exam_id` (`exam_id`);
ALTER TABLE `exam_questions` ADD INDEX IF NOT EXISTS `idx_exam_questions_subject_id` (`subject_id`);
ALTER TABLE `exam_questions` ADD INDEX IF NOT EXISTS `fk_exam_questions_generation_job` (`generation_job_id`);

-- -------------------------------------------------------------------------
-- exam_results
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `paper_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `score` decimal(6,2) DEFAULT NULL,
  `grade` varchar(5) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `marked_by` int(11) DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_result` (`paper_id`,`student_id`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_results` ADD COLUMN IF NOT EXISTS `paper_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `exam_results` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `paper_id`;
ALTER TABLE `exam_results` ADD COLUMN IF NOT EXISTS `score` decimal(6,2) DEFAULT NULL AFTER `student_id`;
ALTER TABLE `exam_results` ADD COLUMN IF NOT EXISTS `grade` varchar(5) DEFAULT NULL AFTER `score`;
ALTER TABLE `exam_results` ADD COLUMN IF NOT EXISTS `remarks` text DEFAULT NULL AFTER `grade`;
ALTER TABLE `exam_results` ADD COLUMN IF NOT EXISTS `marked_by` int(11) DEFAULT NULL AFTER `remarks`;
ALTER TABLE `exam_results` ADD COLUMN IF NOT EXISTS `submitted_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `marked_by`;
ALTER TABLE `exam_results` ADD UNIQUE INDEX IF NOT EXISTS `unique_result` (`paper_id`,`student_id`);
ALTER TABLE `exam_results` ADD INDEX IF NOT EXISTS `student_id` (`student_id`);

-- -------------------------------------------------------------------------
-- exam_schedules
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `paper_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `exam_date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `venue` varchar(150) DEFAULT NULL,
  `invigilator_id` int(11) DEFAULT NULL,
  `status` enum('scheduled','ongoing','completed','cancelled') NOT NULL DEFAULT 'scheduled',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `paper_id` (`paper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `paper_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `paper_id`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `exam_date` date NOT NULL AFTER `class_name`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `start_time` time NOT NULL AFTER `exam_date`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `end_time` time NOT NULL AFTER `start_time`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `venue` varchar(150) DEFAULT NULL AFTER `end_time`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `invigilator_id` int(11) DEFAULT NULL AFTER `venue`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `status` enum('scheduled','ongoing','completed','cancelled') NOT NULL DEFAULT 'scheduled' AFTER `invigilator_id`;
ALTER TABLE `exam_schedules` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `status`;
ALTER TABLE `exam_schedules` ADD INDEX IF NOT EXISTS `paper_id` (`paper_id`);

-- -------------------------------------------------------------------------
-- exam_templates
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_name` varchar(100) NOT NULL,
  `header_html` text DEFAULT NULL,
  `footer_html` text DEFAULT NULL,
  `css_styles` text DEFAULT NULL,
  `is_default` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `exam_templates` ADD COLUMN IF NOT EXISTS `template_name` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `exam_templates` ADD COLUMN IF NOT EXISTS `header_html` text DEFAULT NULL AFTER `template_name`;
ALTER TABLE `exam_templates` ADD COLUMN IF NOT EXISTS `footer_html` text DEFAULT NULL AFTER `header_html`;
ALTER TABLE `exam_templates` ADD COLUMN IF NOT EXISTS `css_styles` text DEFAULT NULL AFTER `footer_html`;
ALTER TABLE `exam_templates` ADD COLUMN IF NOT EXISTS `is_default` tinyint(1) DEFAULT 0 AFTER `css_styles`;
ALTER TABLE `exam_templates` ADD COLUMN IF NOT EXISTS `created_at` timestamp NULL DEFAULT current_timestamp() AFTER `is_default`;

-- -------------------------------------------------------------------------
-- exam_workflow_logs
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `exam_workflow_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_id` int(11) NOT NULL,
  `action` varchar(40) NOT NULL,
  `from_status` varchar(30) DEFAULT NULL,
  `to_status` varchar(30) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `acted_by` int(11) DEFAULT NULL,
  `acted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_exam` (`exam_id`),
  KEY `idx_acted_at` (`acted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `exam_workflow_logs` ADD COLUMN IF NOT EXISTS `exam_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `exam_workflow_logs` ADD COLUMN IF NOT EXISTS `action` varchar(40) NOT NULL AFTER `exam_id`;
ALTER TABLE `exam_workflow_logs` ADD COLUMN IF NOT EXISTS `from_status` varchar(30) DEFAULT NULL AFTER `action`;
ALTER TABLE `exam_workflow_logs` ADD COLUMN IF NOT EXISTS `to_status` varchar(30) DEFAULT NULL AFTER `from_status`;
ALTER TABLE `exam_workflow_logs` ADD COLUMN IF NOT EXISTS `note` text DEFAULT NULL AFTER `to_status`;
ALTER TABLE `exam_workflow_logs` ADD COLUMN IF NOT EXISTS `acted_by` int(11) DEFAULT NULL AFTER `note`;
ALTER TABLE `exam_workflow_logs` ADD COLUMN IF NOT EXISTS `acted_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `acted_by`;
ALTER TABLE `exam_workflow_logs` ADD INDEX IF NOT EXISTS `idx_exam` (`exam_id`);
ALTER TABLE `exam_workflow_logs` ADD INDEX IF NOT EXISTS `idx_acted_at` (`acted_at`);

-- -------------------------------------------------------------------------
-- expenses
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_date` date NOT NULL,
  `category` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` enum('cash','card','bank_transfer') DEFAULT 'cash',
  `vendor` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `expenses` ADD COLUMN IF NOT EXISTS `expense_date` date NOT NULL AFTER `id`;
ALTER TABLE `expenses` ADD COLUMN IF NOT EXISTS `category` varchar(50) NOT NULL AFTER `expense_date`;
ALTER TABLE `expenses` ADD COLUMN IF NOT EXISTS `description` text NOT NULL AFTER `category`;
ALTER TABLE `expenses` ADD COLUMN IF NOT EXISTS `amount` decimal(10,2) NOT NULL AFTER `description`;
ALTER TABLE `expenses` ADD COLUMN IF NOT EXISTS `payment_method` enum('cash','card','bank_transfer') DEFAULT 'cash' AFTER `amount`;
ALTER TABLE `expenses` ADD COLUMN IF NOT EXISTS `vendor` varchar(100) DEFAULT NULL AFTER `payment_method`;
ALTER TABLE `expenses` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `vendor`;

-- -------------------------------------------------------------------------
-- fee_structures
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `fee_structures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(20) NOT NULL,
  `fee_type` varchar(50) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `due_date` date DEFAULT NULL,
  `academic_year` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `fee_structures` ADD COLUMN IF NOT EXISTS `class_name` varchar(20) NOT NULL AFTER `id`;
ALTER TABLE `fee_structures` ADD COLUMN IF NOT EXISTS `fee_type` varchar(50) NOT NULL AFTER `class_name`;
ALTER TABLE `fee_structures` ADD COLUMN IF NOT EXISTS `amount` decimal(10,2) NOT NULL AFTER `fee_type`;
ALTER TABLE `fee_structures` ADD COLUMN IF NOT EXISTS `due_date` date DEFAULT NULL AFTER `amount`;
ALTER TABLE `fee_structures` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) DEFAULT NULL AFTER `due_date`;
ALTER TABLE `fee_structures` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `academic_year`;

-- -------------------------------------------------------------------------
-- generated_exams
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `generated_exams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `class_name` varchar(50) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  `exam_type` enum('mid_term','end_term') NOT NULL,
  `term` varchar(20) NOT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'draft',
  `academic_year` varchar(20) NOT NULL,
  `instructions` text DEFAULT NULL,
  `total_marks` int(11) NOT NULL DEFAULT 100,
  `duration_minutes` int(11) NOT NULL DEFAULT 60,
  `exam_data` longtext NOT NULL,
  `generator_used` varchar(50) DEFAULT NULL,
  `generation_warnings` text DEFAULT NULL,
  `review_notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_edited_by` int(11) DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `published_by` int(11) DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `download_count` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_class` (`class_name`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `title` varchar(255) NOT NULL AFTER `id`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `class_name` varchar(50) NOT NULL AFTER `title`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `class_name`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `subject_name` varchar(100) NOT NULL AFTER `subject_id`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `exam_type` enum('mid_term','end_term') NOT NULL AFTER `subject_name`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL AFTER `exam_type`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `status` varchar(30) NOT NULL DEFAULT 'draft' AFTER `term`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `status`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `instructions` text DEFAULT NULL AFTER `academic_year`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `total_marks` int(11) NOT NULL DEFAULT 100 AFTER `instructions`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `duration_minutes` int(11) NOT NULL DEFAULT 60 AFTER `total_marks`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `exam_data` longtext NOT NULL AFTER `duration_minutes`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `generator_used` varchar(50) DEFAULT NULL AFTER `exam_data`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `generation_warnings` text DEFAULT NULL AFTER `generator_used`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `review_notes` text DEFAULT NULL AFTER `generation_warnings`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `review_notes`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `last_edited_by` int(11) DEFAULT NULL AFTER `updated_at`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `reviewed_by` int(11) DEFAULT NULL AFTER `last_edited_by`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `reviewed_at` datetime DEFAULT NULL AFTER `reviewed_by`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `approved_by` int(11) DEFAULT NULL AFTER `reviewed_at`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `approved_at` datetime DEFAULT NULL AFTER `approved_by`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `published_by` int(11) DEFAULT NULL AFTER `approved_at`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `published_at` datetime DEFAULT NULL AFTER `published_by`;
ALTER TABLE `generated_exams` ADD COLUMN IF NOT EXISTS `download_count` int(11) DEFAULT 0 AFTER `published_at`;
ALTER TABLE `generated_exams` ADD INDEX IF NOT EXISTS `idx_class` (`class_name`);
ALTER TABLE `generated_exams` ADD INDEX IF NOT EXISTS `idx_created` (`created_at`);

-- -------------------------------------------------------------------------
-- grade_boundaries
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `grade_boundaries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(20) NOT NULL,
  `grade` varchar(2) NOT NULL,
  `min_score` decimal(5,2) NOT NULL,
  `max_score` decimal(5,2) NOT NULL,
  `remark` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `academic_year` varchar(20) DEFAULT NULL,
  `term` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_class_grade` (`class_name`,`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `class_name` varchar(20) NOT NULL AFTER `id`;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `grade` varchar(2) NOT NULL AFTER `class_name`;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `min_score` decimal(5,2) NOT NULL AFTER `grade`;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `max_score` decimal(5,2) NOT NULL AFTER `min_score`;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `remark` varchar(50) NOT NULL AFTER `max_score`;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `remark`;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) DEFAULT NULL AFTER `created_at`;
ALTER TABLE `grade_boundaries` ADD COLUMN IF NOT EXISTS `term` varchar(20) DEFAULT NULL AFTER `academic_year`;
ALTER TABLE `grade_boundaries` ADD UNIQUE INDEX IF NOT EXISTS `unique_class_grade` (`class_name`,`grade`);

-- -------------------------------------------------------------------------
-- homework
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `homework` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `subject_name` varchar(100) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `due_date` date NOT NULL,
  `assigned_date` date NOT NULL,
  `max_score` int(11) NOT NULL DEFAULT 10,
  `is_visible_to_parents` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('active','closed','archived') NOT NULL DEFAULT 'active',
  `academic_year` varchar(20) NOT NULL DEFAULT '',
  `term` varchar(50) NOT NULL DEFAULT '',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_teacher` (`teacher_id`),
  KEY `idx_class` (`class_name`),
  KEY `idx_due` (`due_date`),
  KEY `idx_visible` (`is_visible_to_parents`,`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `teacher_id`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `subject_id` int(11) DEFAULT NULL AFTER `class_name`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `subject_name` varchar(100) NOT NULL DEFAULT '' AFTER `subject_id`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `title` varchar(255) NOT NULL AFTER `subject_name`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `description` text DEFAULT NULL AFTER `title`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `due_date` date NOT NULL AFTER `description`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `assigned_date` date NOT NULL AFTER `due_date`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `max_score` int(11) NOT NULL DEFAULT 10 AFTER `assigned_date`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `is_visible_to_parents` tinyint(1) NOT NULL DEFAULT 1 AFTER `max_score`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `status` enum('active','closed','archived') NOT NULL DEFAULT 'active' AFTER `is_visible_to_parents`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL DEFAULT '' AFTER `status`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `term` varchar(50) NOT NULL DEFAULT '' AFTER `academic_year`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `term`;
ALTER TABLE `homework` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `homework` ADD INDEX IF NOT EXISTS `idx_teacher` (`teacher_id`);
ALTER TABLE `homework` ADD INDEX IF NOT EXISTS `idx_class` (`class_name`);
ALTER TABLE `homework` ADD INDEX IF NOT EXISTS `idx_due` (`due_date`);
ALTER TABLE `homework` ADD INDEX IF NOT EXISTS `idx_visible` (`is_visible_to_parents`,`due_date`);

-- -------------------------------------------------------------------------
-- homework_submissions
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `homework_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `homework_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `status` enum('pending','submitted','missing','excused') NOT NULL DEFAULT 'pending',
  `score` decimal(5,2) DEFAULT NULL,
  `teacher_comment` text DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `marked_at` datetime DEFAULT NULL,
  `marked_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_hw_student` (`homework_id`,`student_id`),
  KEY `idx_homework` (`homework_id`),
  KEY `idx_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `homework_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `homework_id`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `status` enum('pending','submitted','missing','excused') NOT NULL DEFAULT 'pending' AFTER `student_id`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `score` decimal(5,2) DEFAULT NULL AFTER `status`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `teacher_comment` text DEFAULT NULL AFTER `score`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `submitted_at` datetime DEFAULT NULL AFTER `teacher_comment`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `marked_at` datetime DEFAULT NULL AFTER `submitted_at`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `marked_by` int(11) DEFAULT NULL AFTER `marked_at`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `marked_by`;
ALTER TABLE `homework_submissions` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `homework_submissions` ADD UNIQUE INDEX IF NOT EXISTS `unique_hw_student` (`homework_id`,`student_id`);
ALTER TABLE `homework_submissions` ADD INDEX IF NOT EXISTS `idx_homework` (`homework_id`);
ALTER TABLE `homework_submissions` ADD INDEX IF NOT EXISTS `idx_student` (`student_id`);

-- -------------------------------------------------------------------------
-- internal_messages
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `internal_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `subject` varchar(255) DEFAULT '',
  `body` text NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_recipient` (`recipient_id`,`is_read`),
  KEY `idx_sender` (`sender_id`),
  KEY `idx_thread` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `internal_messages` ADD COLUMN IF NOT EXISTS `sender_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `internal_messages` ADD COLUMN IF NOT EXISTS `recipient_id` int(11) NOT NULL AFTER `sender_id`;
ALTER TABLE `internal_messages` ADD COLUMN IF NOT EXISTS `subject` varchar(255) DEFAULT '' AFTER `recipient_id`;
ALTER TABLE `internal_messages` ADD COLUMN IF NOT EXISTS `body` text NOT NULL AFTER `subject`;
ALTER TABLE `internal_messages` ADD COLUMN IF NOT EXISTS `parent_id` int(11) DEFAULT NULL AFTER `body`;
ALTER TABLE `internal_messages` ADD COLUMN IF NOT EXISTS `is_read` tinyint(1) NOT NULL DEFAULT 0 AFTER `parent_id`;
ALTER TABLE `internal_messages` ADD COLUMN IF NOT EXISTS `sent_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `is_read`;
ALTER TABLE `internal_messages` ADD INDEX IF NOT EXISTS `idx_recipient` (`recipient_id`,`is_read`);
ALTER TABLE `internal_messages` ADD INDEX IF NOT EXISTS `idx_sender` (`sender_id`);
ALTER TABLE `internal_messages` ADD INDEX IF NOT EXISTS `idx_thread` (`parent_id`);

-- -------------------------------------------------------------------------
-- learning_materials
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `learning_materials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(50) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `material_type` enum('textbook','lesson_note','syllabus','past_question','reference') NOT NULL DEFAULT 'lesson_note',
  `title` varchar(255) NOT NULL,
  `content` longtext DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `file_size` int(11) DEFAULT NULL,
  `extracted_text` longtext DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_class_subject` (`class_name`,`subject_id`),
  KEY `idx_material_type` (`material_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `class_name` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `class_name`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `material_type` enum('textbook','lesson_note','syllabus','past_question','reference') NOT NULL DEFAULT 'lesson_note' AFTER `subject_id`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `title` varchar(255) NOT NULL AFTER `material_type`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `content` longtext DEFAULT NULL AFTER `title`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `file_path` varchar(255) DEFAULT NULL AFTER `content`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `file_size` int(11) DEFAULT NULL AFTER `file_path`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `extracted_text` longtext DEFAULT NULL AFTER `file_size`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `uploaded_by` int(11) DEFAULT NULL AFTER `extracted_text`;
ALTER TABLE `learning_materials` ADD COLUMN IF NOT EXISTS `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `uploaded_by`;
ALTER TABLE `learning_materials` ADD INDEX IF NOT EXISTS `idx_class_subject` (`class_name`,`subject_id`);
ALTER TABLE `learning_materials` ADD INDEX IF NOT EXISTS `idx_material_type` (`material_type`);

-- -------------------------------------------------------------------------
-- login_attempts
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(45) NOT NULL,
  `username` varchar(190) DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `attempted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ip_time` (`ip`,`attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `login_attempts` ADD COLUMN IF NOT EXISTS `ip` varchar(45) NOT NULL AFTER `id`;
ALTER TABLE `login_attempts` ADD COLUMN IF NOT EXISTS `username` varchar(190) DEFAULT NULL AFTER `ip`;
ALTER TABLE `login_attempts` ADD COLUMN IF NOT EXISTS `success` tinyint(1) NOT NULL DEFAULT 0 AFTER `username`;
ALTER TABLE `login_attempts` ADD COLUMN IF NOT EXISTS `attempted_at` datetime NOT NULL AFTER `success`;
ALTER TABLE `login_attempts` ADD INDEX IF NOT EXISTS `idx_ip_time` (`ip`,`attempted_at`);

-- -------------------------------------------------------------------------
-- migration_log
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `migration_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `migration` varchar(100) NOT NULL,
  `ran_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `migration` (`migration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `migration_log` ADD COLUMN IF NOT EXISTS `migration` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `migration_log` ADD COLUMN IF NOT EXISTS `ran_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `migration`;
ALTER TABLE `migration_log` ADD UNIQUE INDEX IF NOT EXISTS `migration` (`migration`);

-- -------------------------------------------------------------------------
-- notifications
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `type` enum('sms','email') NOT NULL,
  `recipient` varchar(100) NOT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `message` text NOT NULL,
  `status` enum('sent','failed','pending') DEFAULT 'pending',
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `notifications` ADD COLUMN IF NOT EXISTS `student_id` int(11) DEFAULT NULL AFTER `id`;
ALTER TABLE `notifications` ADD COLUMN IF NOT EXISTS `type` enum('sms','email') NOT NULL AFTER `student_id`;
ALTER TABLE `notifications` ADD COLUMN IF NOT EXISTS `recipient` varchar(100) NOT NULL AFTER `type`;
ALTER TABLE `notifications` ADD COLUMN IF NOT EXISTS `subject` varchar(200) DEFAULT NULL AFTER `recipient`;
ALTER TABLE `notifications` ADD COLUMN IF NOT EXISTS `message` text NOT NULL AFTER `subject`;
ALTER TABLE `notifications` ADD COLUMN IF NOT EXISTS `status` enum('sent','failed','pending') DEFAULT 'pending' AFTER `message`;
ALTER TABLE `notifications` ADD COLUMN IF NOT EXISTS `sent_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `status`;
ALTER TABLE `notifications` ADD INDEX IF NOT EXISTS `student_id` (`student_id`);

-- -------------------------------------------------------------------------
-- online_payments
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `online_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `parent_name` varchar(150) DEFAULT NULL,
  `parent_phone` varchar(20) DEFAULT NULL,
  `parent_email` varchar(150) DEFAULT NULL,
  `fee_structure_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`fee_structure_ids`)),
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(5) NOT NULL DEFAULT 'GHS',
  `payment_method` enum('card','momo_mtn','momo_vodafone','momo_airteltigo') NOT NULL,
  `gateway` enum('paystack','hubtel') NOT NULL,
  `gateway_reference` varchar(255) DEFAULT NULL,
  `gateway_status` varchar(100) DEFAULT NULL,
  `gateway_response` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`gateway_response`)),
  `status` enum('pending','success','failed','refunded') NOT NULL DEFAULT 'pending',
  `receipt_number` varchar(50) DEFAULT NULL,
  `initiated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `completed_at` datetime DEFAULT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `original_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `parent_name` varchar(150) DEFAULT NULL AFTER `student_id`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `parent_phone` varchar(20) DEFAULT NULL AFTER `parent_name`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `parent_email` varchar(150) DEFAULT NULL AFTER `parent_phone`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `fee_structure_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`fee_structure_ids`)) AFTER `parent_email`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `amount` decimal(10,2) NOT NULL AFTER `fee_structure_ids`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `currency` varchar(5) NOT NULL DEFAULT 'GHS' AFTER `amount`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `payment_method` enum('card','momo_mtn','momo_vodafone','momo_airteltigo') NOT NULL AFTER `currency`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `gateway` enum('paystack','hubtel') NOT NULL AFTER `payment_method`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `gateway_reference` varchar(255) DEFAULT NULL AFTER `gateway`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `gateway_status` varchar(100) DEFAULT NULL AFTER `gateway_reference`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `gateway_response` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`gateway_response`)) AFTER `gateway_status`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `status` enum('pending','success','failed','refunded') NOT NULL DEFAULT 'pending' AFTER `gateway_response`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `receipt_number` varchar(50) DEFAULT NULL AFTER `status`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `initiated_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `receipt_number`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `completed_at` datetime DEFAULT NULL AFTER `initiated_at`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `coupon_id` int(11) DEFAULT NULL AFTER `completed_at`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00 AFTER `coupon_id`;
ALTER TABLE `online_payments` ADD COLUMN IF NOT EXISTS `original_amount` decimal(10,2) NOT NULL DEFAULT 0.00 AFTER `discount_amount`;
ALTER TABLE `online_payments` ADD INDEX IF NOT EXISTS `student_id` (`student_id`);

-- -------------------------------------------------------------------------
-- parent_accounts
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `parent_accounts` (
  `id` int(11) NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `otp_code` varchar(10) DEFAULT NULL,
  `otp_expires` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `parent_name` varchar(150) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `full_name` varchar(150) NOT NULL AFTER `id`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `email` varchar(150) DEFAULT NULL AFTER `full_name`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `phone` varchar(20) NOT NULL AFTER `email`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `password_hash` varchar(255) NOT NULL AFTER `phone`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `password_hash`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `last_login` datetime DEFAULT NULL AFTER `is_active`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `otp_code` varchar(10) DEFAULT NULL AFTER `last_login`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `otp_expires` datetime DEFAULT NULL AFTER `otp_code`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `otp_expires`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `parent_name` varchar(150) DEFAULT NULL AFTER `updated_at`;
ALTER TABLE `parent_accounts` ADD COLUMN IF NOT EXISTS `username` varchar(100) DEFAULT NULL AFTER `parent_name`;

-- -------------------------------------------------------------------------
-- parent_messages
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `parent_messages` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `sender` enum('parent','admin') NOT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `parent_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `admin_id` int(11) DEFAULT NULL AFTER `parent_id`;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `sender` enum('parent','admin') NOT NULL AFTER `admin_id`;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `subject` varchar(200) DEFAULT NULL AFTER `sender`;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `message` text NOT NULL AFTER `subject`;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `is_read` tinyint(1) NOT NULL DEFAULT 0 AFTER `message`;
ALTER TABLE `parent_messages` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `is_read`;

-- -------------------------------------------------------------------------
-- parent_notifications
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `parent_notifications` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `type` enum('fee','academic','general','alert') NOT NULL DEFAULT 'general',
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `notification_type` varchar(50) DEFAULT 'general',
  `is_sent` tinyint(1) DEFAULT 0,
  `sent_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `parent_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `title` varchar(200) NOT NULL AFTER `parent_id`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `message` text NOT NULL AFTER `title`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `type` enum('fee','academic','general','alert') NOT NULL DEFAULT 'general' AFTER `message`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `is_read` tinyint(1) NOT NULL DEFAULT 0 AFTER `type`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `is_read`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `notification_type` varchar(50) DEFAULT 'general' AFTER `created_at`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `is_sent` tinyint(1) DEFAULT 0 AFTER `notification_type`;
ALTER TABLE `parent_notifications` ADD COLUMN IF NOT EXISTS `sent_by` int(11) DEFAULT NULL AFTER `is_sent`;

-- -------------------------------------------------------------------------
-- parent_student_link
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `parent_student_link` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `relationship` varchar(50) DEFAULT 'Parent',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `parent_student_link` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `parent_student_link` ADD COLUMN IF NOT EXISTS `parent_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `parent_student_link` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `parent_id`;
ALTER TABLE `parent_student_link` ADD COLUMN IF NOT EXISTS `relationship` varchar(50) DEFAULT 'Parent' AFTER `student_id`;
ALTER TABLE `parent_student_link` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `relationship`;

-- -------------------------------------------------------------------------
-- payments
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `fee_structure_id` int(11) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` enum('cash','card','bank_transfer','online') DEFAULT 'cash',
  `transaction_id` varchar(50) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `coupon_id` int(11) DEFAULT NULL,
  `discount_amount` decimal(10,2) DEFAULT 0.00,
  `original_amount` decimal(10,2) DEFAULT NULL,
  `recorded_via` enum('standard','express_scanner','ai_agent','import','canteen_bulk') DEFAULT 'standard'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `fee_structure_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `amount_paid` decimal(10,2) NOT NULL AFTER `fee_structure_id`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `payment_date` date NOT NULL AFTER `amount_paid`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `payment_method` enum('cash','card','bank_transfer','online') DEFAULT 'cash' AFTER `payment_date`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `transaction_id` varchar(50) DEFAULT NULL AFTER `payment_method`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `transaction_id`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `notes`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `coupon_id` int(11) DEFAULT NULL AFTER `created_at`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `discount_amount` decimal(10,2) DEFAULT 0.00 AFTER `coupon_id`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `original_amount` decimal(10,2) DEFAULT NULL AFTER `discount_amount`;
ALTER TABLE `payments` ADD COLUMN IF NOT EXISTS `recorded_via` enum('standard','express_scanner','ai_agent','import','canteen_bulk') DEFAULT 'standard' AFTER `original_amount`;

-- -------------------------------------------------------------------------
-- question_bank
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `question_bank` (
  `id` int(11) NOT NULL,
  `class_name` varchar(50) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  `question_type` enum('objective','subjective','comprehension') NOT NULL,
  `question_text` text NOT NULL,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)),
  `correct_answer` varchar(255) DEFAULT NULL,
  `marks` int(11) NOT NULL DEFAULT 2,
  `difficulty` enum('easy','medium','hard') NOT NULL DEFAULT 'medium',
  `topic` varchar(255) DEFAULT NULL,
  `bloom_level` varchar(50) DEFAULT NULL,
  `section_title` varchar(255) DEFAULT NULL,
  `source_exam_id` int(11) DEFAULT NULL,
  `source_material_ids` text DEFAULT NULL,
  `question_hash` varchar(64) DEFAULT NULL,
  `times_used` int(11) NOT NULL DEFAULT 0,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_used` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `class_name` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `class_name`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `subject_name` varchar(100) NOT NULL AFTER `subject_id`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `question_type` enum('objective','subjective','comprehension') NOT NULL AFTER `subject_name`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `question_text` text NOT NULL AFTER `question_type`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)) AFTER `question_text`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `correct_answer` varchar(255) DEFAULT NULL AFTER `options`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `marks` int(11) NOT NULL DEFAULT 2 AFTER `correct_answer`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `difficulty` enum('easy','medium','hard') NOT NULL DEFAULT 'medium' AFTER `marks`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `topic` varchar(255) DEFAULT NULL AFTER `difficulty`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `bloom_level` varchar(50) DEFAULT NULL AFTER `topic`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `section_title` varchar(255) DEFAULT NULL AFTER `bloom_level`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `source_exam_id` int(11) DEFAULT NULL AFTER `section_title`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `source_material_ids` text DEFAULT NULL AFTER `source_exam_id`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `question_hash` varchar(64) DEFAULT NULL AFTER `source_material_ids`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `times_used` int(11) NOT NULL DEFAULT 0 AFTER `question_hash`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `times_used`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `created_by`;
ALTER TABLE `question_bank` ADD COLUMN IF NOT EXISTS `last_used` timestamp NULL DEFAULT NULL AFTER `created_at`;

-- -------------------------------------------------------------------------
-- report_details
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `report_details` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `term` varchar(20) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `attendance_out_of` int(11) DEFAULT 0,
  `promoted_to` varchar(50) DEFAULT NULL,
  `conduct` text DEFAULT NULL,
  `interest` text DEFAULT NULL,
  `teacher_remark` text DEFAULT NULL,
  `director_signature` varchar(255) DEFAULT NULL,
  `headteacher_signature` varchar(255) DEFAULT NULL,
  `class_teacher_signature` varchar(255) DEFAULT NULL,
  `school_stamp` varchar(255) DEFAULT NULL,
  `reopening_date` varchar(100) DEFAULT NULL,
  `closing_date` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `is_published` tinyint(1) NOT NULL DEFAULT 0,
  `published_at` datetime DEFAULT NULL,
  `attendance_total_days` int(11) NOT NULL DEFAULT 0,
  `headteacher_remark` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL AFTER `student_id`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `term`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `attendance_out_of` int(11) DEFAULT 0 AFTER `academic_year`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `promoted_to` varchar(50) DEFAULT NULL AFTER `attendance_out_of`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `conduct` text DEFAULT NULL AFTER `promoted_to`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `interest` text DEFAULT NULL AFTER `conduct`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `teacher_remark` text DEFAULT NULL AFTER `interest`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `director_signature` varchar(255) DEFAULT NULL AFTER `teacher_remark`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `headteacher_signature` varchar(255) DEFAULT NULL AFTER `director_signature`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `class_teacher_signature` varchar(255) DEFAULT NULL AFTER `headteacher_signature`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `school_stamp` varchar(255) DEFAULT NULL AFTER `class_teacher_signature`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `reopening_date` varchar(100) DEFAULT NULL AFTER `school_stamp`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `closing_date` varchar(100) DEFAULT NULL AFTER `reopening_date`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `closing_date`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `is_published` tinyint(1) NOT NULL DEFAULT 0 AFTER `updated_at`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `published_at` datetime DEFAULT NULL AFTER `is_published`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `attendance_total_days` int(11) NOT NULL DEFAULT 0 AFTER `published_at`;
ALTER TABLE `report_details` ADD COLUMN IF NOT EXISTS `headteacher_remark` text DEFAULT NULL AFTER `attendance_total_days`;

-- -------------------------------------------------------------------------
-- sba_component_types
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sba_component_types` (
  `id` int(11) NOT NULL,
  `class_name` varchar(20) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  `component_name` varchar(80) NOT NULL,
  `max_score` decimal(6,2) NOT NULL DEFAULT 100.00,
  `weight` decimal(6,2) NOT NULL DEFAULT 1.00,
  `display_order` int(11) NOT NULL DEFAULT 0,
  `term` varchar(20) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `class_name` varchar(20) NOT NULL AFTER `id`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `class_name`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) DEFAULT NULL AFTER `subject_id`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `component_name` varchar(80) NOT NULL AFTER `teacher_id`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `max_score` decimal(6,2) NOT NULL DEFAULT 100.00 AFTER `component_name`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `weight` decimal(6,2) NOT NULL DEFAULT 1.00 AFTER `max_score`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `display_order` int(11) NOT NULL DEFAULT 0 AFTER `weight`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL AFTER `display_order`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `term`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `academic_year`;
ALTER TABLE `sba_component_types` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `is_active`;

-- -------------------------------------------------------------------------
-- sba_scores
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sba_scores` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `component_type_id` int(11) NOT NULL,
  `score` decimal(6,2) NOT NULL DEFAULT 0.00,
  `term` varchar(20) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `component_type_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `score` decimal(6,2) NOT NULL DEFAULT 0.00 AFTER `component_type_id`;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL AFTER `score`;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `term`;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `academic_year`;
ALTER TABLE `sba_scores` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- school_holidays
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `school_holidays` (
  `id` int(11) NOT NULL,
  `holiday_name` varchar(100) NOT NULL,
  `holiday_date` date NOT NULL,
  `is_observed` tinyint(1) NOT NULL DEFAULT 1,
  `is_movable` tinyint(1) NOT NULL DEFAULT 0,
  `note` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `holiday_name` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `holiday_date` date NOT NULL AFTER `holiday_name`;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `is_observed` tinyint(1) NOT NULL DEFAULT 1 AFTER `holiday_date`;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `is_movable` tinyint(1) NOT NULL DEFAULT 0 AFTER `is_observed`;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `note` varchar(255) DEFAULT NULL AFTER `is_movable`;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `note`;
ALTER TABLE `school_holidays` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- school_terms
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `school_terms` (
  `id` int(11) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `term` varchar(20) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
ALTER TABLE `school_terms` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `school_terms` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `id`;
ALTER TABLE `school_terms` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL AFTER `academic_year`;
ALTER TABLE `school_terms` ADD COLUMN IF NOT EXISTS `start_date` date NOT NULL AFTER `term`;
ALTER TABLE `school_terms` ADD COLUMN IF NOT EXISTS `end_date` date NOT NULL AFTER `start_date`;
ALTER TABLE `school_terms` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `end_date`;
ALTER TABLE `school_terms` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- scores
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `scores` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `class_score` decimal(5,2) DEFAULT 0.00,
  `exam_score` decimal(5,2) DEFAULT 0.00,
  `raw_class_score` decimal(5,2) DEFAULT 0.00,
  `raw_exam_score` decimal(5,2) DEFAULT 0.00,
  `weighted_class_score` decimal(5,2) DEFAULT 0.00,
  `weighted_exam_score` decimal(5,2) DEFAULT 0.00,
  `total_score` decimal(5,2) DEFAULT 0.00,
  `grade` varchar(2) DEFAULT 'W',
  `remark` varchar(50) DEFAULT 'Weak',
  `term` varchar(20) NOT NULL,
  `academic_year` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `subject_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `class_score` decimal(5,2) DEFAULT 0.00 AFTER `subject_id`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `exam_score` decimal(5,2) DEFAULT 0.00 AFTER `class_score`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `raw_class_score` decimal(5,2) DEFAULT 0.00 AFTER `exam_score`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `raw_exam_score` decimal(5,2) DEFAULT 0.00 AFTER `raw_class_score`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `weighted_class_score` decimal(5,2) DEFAULT 0.00 AFTER `raw_exam_score`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `weighted_exam_score` decimal(5,2) DEFAULT 0.00 AFTER `weighted_class_score`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `total_score` decimal(5,2) DEFAULT 0.00 AFTER `weighted_exam_score`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `grade` varchar(2) DEFAULT 'W' AFTER `total_score`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `remark` varchar(50) DEFAULT 'Weak' AFTER `grade`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `term` varchar(20) NOT NULL AFTER `remark`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `term`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `academic_year`;
ALTER TABLE `scores` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- settings
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `setting_key` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `setting_value` text DEFAULT NULL AFTER `setting_key`;
ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `setting_value`;

-- -------------------------------------------------------------------------
-- sms_logs
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sms_logs` (
  `id` int(11) NOT NULL,
  `phone` text NOT NULL,
  `message` longtext NOT NULL,
  `status` varchar(200) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `recipient` varchar(20) NOT NULL DEFAULT '',
  `gateway_response` text DEFAULT NULL,
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `type` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `phone` text NOT NULL AFTER `id`;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `message` longtext NOT NULL AFTER `phone`;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `status` varchar(200) NOT NULL AFTER `message`;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `status`;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `recipient` varchar(20) NOT NULL DEFAULT '' AFTER `created_at`;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `gateway_response` text DEFAULT NULL AFTER `recipient`;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `sent_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `gateway_response`;
ALTER TABLE `sms_logs` ADD COLUMN IF NOT EXISTS `type` varchar(50) DEFAULT NULL AFTER `sent_at`;

-- -------------------------------------------------------------------------
-- staff
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `staff` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(20) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `reg_no` varchar(50) DEFAULT NULL,
  `academic_qualification` text DEFAULT NULL,
  `professional_qualification` text DEFAULT NULL,
  `class_taught` varchar(200) DEFAULT NULL,
  `present_rank` varchar(100) DEFAULT NULL,
  `date_promoted_to_present_rank` date DEFAULT NULL,
  `date_of_first_appointment` date DEFAULT NULL,
  `year_of_certification` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('Male','Female','Other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `place_of_birth` varchar(100) DEFAULT NULL,
  `nationality` varchar(100) DEFAULT NULL,
  `residential_address` text DEFAULT NULL,
  `institutions_attended` text DEFAULT NULL,
  `teaching_experience` text DEFAULT NULL,
  `previous_schools` text DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_account` varchar(50) DEFAULT NULL,
  `date_posted_to_present_station` date DEFAULT NULL,
  `expected_retirement_date` date DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `tel_no` varchar(20) DEFAULT NULL,
  `religious_denomination` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `marital_status` enum('Single','Married','Divorced','Widowed') DEFAULT NULL,
  `languages_spoken` text DEFAULT NULL,
  `next_of_kin` varchar(200) DEFAULT NULL,
  `next_of_kin_phone` varchar(20) DEFAULT NULL,
  `digital_address` varchar(100) DEFAULT NULL,
  `ssnit_number` varchar(50) DEFAULT NULL,
  `district` varchar(100) DEFAULT NULL,
  `tin_number` varchar(50) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  `hometown` varchar(100) DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `date_of_employment` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `class_assigned` varchar(100) DEFAULT NULL,
  `subject_to_teach` varchar(200) DEFAULT NULL,
  `approved_by` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user_id` int(11) DEFAULT NULL,
  `classes_assigned` varchar(500) DEFAULT NULL,
  `subjects_taught` varchar(500) DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `staff_id` varchar(20) NOT NULL AFTER `id`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `full_name` varchar(100) NOT NULL AFTER `staff_id`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `reg_no` varchar(50) DEFAULT NULL AFTER `full_name`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `academic_qualification` text DEFAULT NULL AFTER `reg_no`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `professional_qualification` text DEFAULT NULL AFTER `academic_qualification`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `class_taught` varchar(200) DEFAULT NULL AFTER `professional_qualification`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `present_rank` varchar(100) DEFAULT NULL AFTER `class_taught`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `date_promoted_to_present_rank` date DEFAULT NULL AFTER `present_rank`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `date_of_first_appointment` date DEFAULT NULL AFTER `date_promoted_to_present_rank`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `year_of_certification` varchar(10) DEFAULT NULL AFTER `date_of_first_appointment`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `age` int(11) DEFAULT NULL AFTER `year_of_certification`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `sex` enum('Male','Female','Other') DEFAULT NULL AFTER `age`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `date_of_birth` date DEFAULT NULL AFTER `sex`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `place_of_birth` varchar(100) DEFAULT NULL AFTER `date_of_birth`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `nationality` varchar(100) DEFAULT NULL AFTER `place_of_birth`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `residential_address` text DEFAULT NULL AFTER `nationality`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `institutions_attended` text DEFAULT NULL AFTER `residential_address`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `teaching_experience` text DEFAULT NULL AFTER `institutions_attended`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `previous_schools` text DEFAULT NULL AFTER `teaching_experience`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `bank_name` varchar(100) DEFAULT NULL AFTER `previous_schools`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `bank_account` varchar(50) DEFAULT NULL AFTER `bank_name`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `date_posted_to_present_station` date DEFAULT NULL AFTER `bank_account`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `expected_retirement_date` date DEFAULT NULL AFTER `date_posted_to_present_station`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `phone` varchar(20) DEFAULT NULL AFTER `expected_retirement_date`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `tel_no` varchar(20) DEFAULT NULL AFTER `phone`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `religious_denomination` varchar(100) DEFAULT NULL AFTER `tel_no`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `email` varchar(100) DEFAULT NULL AFTER `religious_denomination`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `marital_status` enum('Single','Married','Divorced','Widowed') DEFAULT NULL AFTER `email`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `languages_spoken` text DEFAULT NULL AFTER `marital_status`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `next_of_kin` varchar(200) DEFAULT NULL AFTER `languages_spoken`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `next_of_kin_phone` varchar(20) DEFAULT NULL AFTER `next_of_kin`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `digital_address` varchar(100) DEFAULT NULL AFTER `next_of_kin_phone`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `ssnit_number` varchar(50) DEFAULT NULL AFTER `digital_address`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `district` varchar(100) DEFAULT NULL AFTER `ssnit_number`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `tin_number` varchar(50) DEFAULT NULL AFTER `district`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `region` varchar(100) DEFAULT NULL AFTER `tin_number`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `hometown` varchar(100) DEFAULT NULL AFTER `region`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `photo_path` varchar(255) DEFAULT NULL AFTER `hometown`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `date_of_employment` date DEFAULT NULL AFTER `photo_path`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `salary` decimal(10,2) DEFAULT NULL AFTER `date_of_employment`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `class_assigned` varchar(100) DEFAULT NULL AFTER `salary`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `subject_to_teach` varchar(200) DEFAULT NULL AFTER `class_assigned`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `approved_by` varchar(100) DEFAULT NULL AFTER `subject_to_teach`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `status` enum('active','inactive') DEFAULT 'active' AFTER `approved_by`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `status`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `user_id` int(11) DEFAULT NULL AFTER `updated_at`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `classes_assigned` varchar(500) DEFAULT NULL AFTER `user_id`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `subjects_taught` varchar(500) DEFAULT NULL AFTER `classes_assigned`;
ALTER TABLE `staff` ADD COLUMN IF NOT EXISTS `signature_path` varchar(255) DEFAULT NULL AFTER `subjects_taught`;

-- -------------------------------------------------------------------------
-- students
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL,
  `student_id` varchar(20) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `sex` enum('Male','Female','Other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `place_of_birth` varchar(100) DEFAULT NULL,
  `nationality` varchar(50) DEFAULT NULL,
  `house_no` varchar(50) DEFAULT NULL,
  `father_name` varchar(100) DEFAULT NULL,
  `father_occupation` varchar(100) DEFAULT NULL,
  `father_address` text DEFAULT NULL,
  `father_phone` varchar(20) DEFAULT NULL,
  `mother_name` varchar(100) DEFAULT NULL,
  `mother_occupation` varchar(100) DEFAULT NULL,
  `mother_address` text DEFAULT NULL,
  `mother_phone` varchar(20) DEFAULT NULL,
  `religion` varchar(50) DEFAULT NULL,
  `parents_live_together` enum('Yes','No') DEFAULT NULL,
  `guardian_relationship` varchar(100) DEFAULT NULL,
  `has_handicap` enum('Yes','No') DEFAULT NULL,
  `handicap_description` text DEFAULT NULL,
  `languages_spoken` text DEFAULT NULL,
  `last_school` varchar(100) DEFAULT NULL,
  `last_class` varchar(20) DEFAULT NULL,
  `emergency_contact_name` varchar(100) DEFAULT NULL,
  `emergency_contact_address` text DEFAULT NULL,
  `emergency_contact_phone` varchar(20) DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL,
  `class` varchar(20) NOT NULL,
  `section` varchar(10) DEFAULT NULL,
  `parent_name` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `father_email` varchar(100) DEFAULT NULL,
  `identity_qr_token` varchar(128) DEFAULT NULL,
  `identity_qr_generated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `student_id` varchar(20) NOT NULL AFTER `id`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `first_name` varchar(50) NOT NULL AFTER `student_id`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `last_name` varchar(50) NOT NULL AFTER `first_name`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `sex` enum('Male','Female','Other') DEFAULT NULL AFTER `last_name`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `date_of_birth` date DEFAULT NULL AFTER `sex`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `place_of_birth` varchar(100) DEFAULT NULL AFTER `date_of_birth`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `nationality` varchar(50) DEFAULT NULL AFTER `place_of_birth`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `house_no` varchar(50) DEFAULT NULL AFTER `nationality`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `father_name` varchar(100) DEFAULT NULL AFTER `house_no`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `father_occupation` varchar(100) DEFAULT NULL AFTER `father_name`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `father_address` text DEFAULT NULL AFTER `father_occupation`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `father_phone` varchar(20) DEFAULT NULL AFTER `father_address`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `mother_name` varchar(100) DEFAULT NULL AFTER `father_phone`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `mother_occupation` varchar(100) DEFAULT NULL AFTER `mother_name`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `mother_address` text DEFAULT NULL AFTER `mother_occupation`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `mother_phone` varchar(20) DEFAULT NULL AFTER `mother_address`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `religion` varchar(50) DEFAULT NULL AFTER `mother_phone`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `parents_live_together` enum('Yes','No') DEFAULT NULL AFTER `religion`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `guardian_relationship` varchar(100) DEFAULT NULL AFTER `parents_live_together`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `has_handicap` enum('Yes','No') DEFAULT NULL AFTER `guardian_relationship`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `handicap_description` text DEFAULT NULL AFTER `has_handicap`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `languages_spoken` text DEFAULT NULL AFTER `handicap_description`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `last_school` varchar(100) DEFAULT NULL AFTER `languages_spoken`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `last_class` varchar(20) DEFAULT NULL AFTER `last_school`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `emergency_contact_name` varchar(100) DEFAULT NULL AFTER `last_class`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `emergency_contact_address` text DEFAULT NULL AFTER `emergency_contact_name`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `emergency_contact_phone` varchar(20) DEFAULT NULL AFTER `emergency_contact_address`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `photo_path` varchar(255) DEFAULT NULL AFTER `emergency_contact_phone`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `signature_path` varchar(255) DEFAULT NULL AFTER `photo_path`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `class` varchar(20) NOT NULL AFTER `signature_path`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `section` varchar(10) DEFAULT NULL AFTER `class`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `parent_name` varchar(100) DEFAULT NULL AFTER `section`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `phone` varchar(20) DEFAULT NULL AFTER `parent_name`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `email` varchar(100) DEFAULT NULL AFTER `phone`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `address` text DEFAULT NULL AFTER `email`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `status` enum('active','inactive') DEFAULT 'active' AFTER `address`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `status`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `father_email` varchar(100) DEFAULT NULL AFTER `created_at`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `identity_qr_token` varchar(128) DEFAULT NULL AFTER `father_email`;
ALTER TABLE `students` ADD COLUMN IF NOT EXISTS `identity_qr_generated_at` datetime DEFAULT NULL AFTER `identity_qr_token`;

-- -------------------------------------------------------------------------
-- student_interventions
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `student_interventions` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `type` enum('academic','attendance','behaviour','general') NOT NULL DEFAULT 'academic',
  `note` text NOT NULL,
  `action_taken` text DEFAULT NULL,
  `follow_up_date` date DEFAULT NULL,
  `visible_to_parent` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('open','monitoring','resolved') NOT NULL DEFAULT 'open',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `teacher_id`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `subject_id` int(11) DEFAULT NULL AFTER `class_name`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `type` enum('academic','attendance','behaviour','general') NOT NULL DEFAULT 'academic' AFTER `subject_id`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `note` text NOT NULL AFTER `type`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `action_taken` text DEFAULT NULL AFTER `note`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `follow_up_date` date DEFAULT NULL AFTER `action_taken`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `visible_to_parent` tinyint(1) NOT NULL DEFAULT 1 AFTER `follow_up_date`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `status` enum('open','monitoring','resolved') NOT NULL DEFAULT 'open' AFTER `visible_to_parent`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `status`;
ALTER TABLE `student_interventions` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- subjects
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `subjects` (
  `id` int(11) NOT NULL,
  `subject_name` varchar(100) NOT NULL,
  `subject_code` varchar(20) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `subjects` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `subjects` ADD COLUMN IF NOT EXISTS `subject_name` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `subjects` ADD COLUMN IF NOT EXISTS `subject_code` varchar(20) DEFAULT NULL AFTER `subject_name`;
ALTER TABLE `subjects` ADD COLUMN IF NOT EXISTS `active` tinyint(1) NOT NULL DEFAULT 1 AFTER `subject_code`;
ALTER TABLE `subjects` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `active`;

-- -------------------------------------------------------------------------
-- teacher_lesson_notes
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `teacher_lesson_notes` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `subject_name` varchar(100) NOT NULL DEFAULT '',
  `lesson_date` date NOT NULL,
  `period_id` int(11) DEFAULT NULL,
  `period_name` varchar(50) DEFAULT '',
  `what_was_taught` text DEFAULT NULL,
  `objectives_met` text DEFAULT NULL,
  `student_engagement` enum('excellent','good','fair','poor') NOT NULL DEFAULT 'good',
  `challenges` text DEFAULT NULL,
  `next_lesson_plan` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `teacher_id`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `subject_id` int(11) DEFAULT NULL AFTER `class_name`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `subject_name` varchar(100) NOT NULL DEFAULT '' AFTER `subject_id`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `lesson_date` date NOT NULL AFTER `subject_name`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `period_id` int(11) DEFAULT NULL AFTER `lesson_date`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `period_name` varchar(50) DEFAULT '' AFTER `period_id`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `what_was_taught` text DEFAULT NULL AFTER `period_name`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `objectives_met` text DEFAULT NULL AFTER `what_was_taught`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `student_engagement` enum('excellent','good','fair','poor') NOT NULL DEFAULT 'good' AFTER `objectives_met`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `challenges` text DEFAULT NULL AFTER `student_engagement`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `next_lesson_plan` text DEFAULT NULL AFTER `challenges`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `next_lesson_plan`;
ALTER TABLE `teacher_lesson_notes` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- teacher_lesson_plans
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `teacher_lesson_plans` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `subject_name` varchar(100) NOT NULL DEFAULT '',
  `week_start` date NOT NULL,
  `term` varchar(50) NOT NULL DEFAULT '',
  `academic_year` varchar(20) NOT NULL DEFAULT '',
  `objectives` text DEFAULT NULL,
  `content` text DEFAULT NULL,
  `teaching_methods` text DEFAULT NULL,
  `materials` text DEFAULT NULL,
  `assessment` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('draft','submitted','approved') NOT NULL DEFAULT 'draft',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `teacher_id`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `subject_id` int(11) DEFAULT NULL AFTER `class_name`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `subject_name` varchar(100) NOT NULL DEFAULT '' AFTER `subject_id`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `week_start` date NOT NULL AFTER `subject_name`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `term` varchar(50) NOT NULL DEFAULT '' AFTER `week_start`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL DEFAULT '' AFTER `term`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `objectives` text DEFAULT NULL AFTER `academic_year`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `content` text DEFAULT NULL AFTER `objectives`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `teaching_methods` text DEFAULT NULL AFTER `content`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `materials` text DEFAULT NULL AFTER `teaching_methods`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `assessment` text DEFAULT NULL AFTER `materials`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `assessment`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `status` enum('draft','submitted','approved') NOT NULL DEFAULT 'draft' AFTER `notes`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `approved_by` int(11) DEFAULT NULL AFTER `status`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `approved_at` datetime DEFAULT NULL AFTER `approved_by`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `approved_at`;
ALTER TABLE `teacher_lesson_plans` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- timetable_email_reminders
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `timetable_email_reminders` (
  `id` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  `slot_id` int(11) NOT NULL,
  `schedule_date` date NOT NULL,
  `reminder_type` enum('30_min','15_min','10_min') NOT NULL DEFAULT '15_min',
  `recipient_email` varchar(150) NOT NULL,
  `status` enum('sent','failed') NOT NULL DEFAULT 'sent',
  `error_message` text DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `slot_id` int(11) NOT NULL AFTER `teacher_id`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `schedule_date` date NOT NULL AFTER `slot_id`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `reminder_type` enum('30_min','15_min','10_min') NOT NULL DEFAULT '15_min' AFTER `schedule_date`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `recipient_email` varchar(150) NOT NULL AFTER `reminder_type`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `status` enum('sent','failed') NOT NULL DEFAULT 'sent' AFTER `recipient_email`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `error_message` text DEFAULT NULL AFTER `status`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `sent_at` datetime DEFAULT NULL AFTER `error_message`;
ALTER TABLE `timetable_email_reminders` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `sent_at`;

-- -------------------------------------------------------------------------
-- timetable_periods
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `timetable_periods` (
  `id` int(11) NOT NULL,
  `period_name` varchar(50) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `is_break` tinyint(1) NOT NULL DEFAULT 0,
  `period_type` varchar(30) NOT NULL DEFAULT 'teaching',
  `order_no` int(11) NOT NULL DEFAULT 0,
  `class_name` varchar(100) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `period_name` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `start_time` time NOT NULL AFTER `period_name`;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `end_time` time NOT NULL AFTER `start_time`;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `is_break` tinyint(1) NOT NULL DEFAULT 0 AFTER `end_time`;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `period_type` varchar(30) NOT NULL DEFAULT 'teaching' AFTER `is_break`;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `order_no` int(11) NOT NULL DEFAULT 0 AFTER `period_type`;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL DEFAULT '' AFTER `order_no`;
ALTER TABLE `timetable_periods` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `class_name`;

-- -------------------------------------------------------------------------
-- timetable_slots
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `timetable_slots` (
  `id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `day_of_week` enum('Mon','Tue','Wed','Thu','Fri','Sat') NOT NULL,
  `period_id` int(11) NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  `room` varchar(100) DEFAULT NULL,
  `academic_year` varchar(20) NOT NULL,
  `term` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `class_name` varchar(100) NOT NULL AFTER `id`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `day_of_week` enum('Mon','Tue','Wed','Thu','Fri','Sat') NOT NULL AFTER `class_name`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `period_id` int(11) NOT NULL AFTER `day_of_week`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `subject_id` int(11) DEFAULT NULL AFTER `period_id`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `teacher_id` int(11) DEFAULT NULL AFTER `subject_id`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `room` varchar(100) DEFAULT NULL AFTER `teacher_id`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `academic_year` varchar(20) NOT NULL AFTER `room`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `term` varchar(50) NOT NULL AFTER `academic_year`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `term`;
ALTER TABLE `timetable_slots` ADD COLUMN IF NOT EXISTS `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() AFTER `created_at`;

-- -------------------------------------------------------------------------
-- timetable_substitutions
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `timetable_substitutions` (
  `id` int(11) NOT NULL,
  `slot_id` int(11) NOT NULL,
  `original_teacher_id` int(11) DEFAULT NULL,
  `substitute_teacher_id` int(11) NOT NULL,
  `substitution_date` date NOT NULL,
  `reason` text DEFAULT NULL,
  `recorded_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `slot_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `original_teacher_id` int(11) DEFAULT NULL AFTER `slot_id`;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `substitute_teacher_id` int(11) NOT NULL AFTER `original_teacher_id`;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `substitution_date` date NOT NULL AFTER `substitute_teacher_id`;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `reason` text DEFAULT NULL AFTER `substitution_date`;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `recorded_by` int(11) DEFAULT NULL AFTER `reason`;
ALTER TABLE `timetable_substitutions` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `recorded_by`;

-- -------------------------------------------------------------------------
-- users
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `last_login` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `role` enum('admin','headteacher','accountant','cashier','registrar','supervisor','director','tutor','cook','assist. cook','principal') NOT NULL DEFAULT 'tutor',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `photo` varchar(255) DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `username` varchar(50) NOT NULL AFTER `id`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `password` varchar(255) NOT NULL AFTER `username`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `full_name` varchar(100) NOT NULL AFTER `password`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `email` varchar(100) NOT NULL AFTER `full_name`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `phone` varchar(20) DEFAULT NULL AFTER `email`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `profile_image` varchar(255) DEFAULT NULL AFTER `phone`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `last_login` timestamp NULL DEFAULT NULL AFTER `profile_image`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) DEFAULT 1 AFTER `last_login`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `role` enum('admin','headteacher','accountant','cashier','registrar','supervisor','director','tutor','cook','assist. cook','principal') NOT NULL DEFAULT 'tutor' AFTER `is_active`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `role`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `photo` varchar(255) DEFAULT NULL AFTER `created_at`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `signature_path` varchar(255) DEFAULT NULL AFTER `photo`;

-- -------------------------------------------------------------------------
-- user_activities
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_activities` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `action` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `details` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `user_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `action` varchar(100) NOT NULL AFTER `user_id`;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `description` text DEFAULT NULL AFTER `action`;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `ip_address` varchar(45) DEFAULT NULL AFTER `description`;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `user_agent` text NOT NULL AFTER `ip_address`;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `user_agent`;
ALTER TABLE `user_activities` ADD COLUMN IF NOT EXISTS `details` text DEFAULT NULL AFTER `created_at`;

-- -------------------------------------------------------------------------
-- weekly_feeding_payments
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `weekly_feeding_payments` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `payment_id` int(11) NOT NULL,
  `week_start_date` date NOT NULL,
  `week_end_date` date NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `sms_sent` tinyint(1) DEFAULT 0,
  `sms_sent_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `week_start` date DEFAULT NULL,
  `week_end` date DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT current_timestamp(),
  `recorded_by` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `weeks_covered` int(11) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `id` int(11) NOT NULL FIRST;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `student_id` int(11) NOT NULL AFTER `id`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `payment_id` int(11) NOT NULL AFTER `student_id`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `week_start_date` date NOT NULL AFTER `payment_id`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `week_end_date` date NOT NULL AFTER `week_start_date`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `amount_paid` decimal(10,2) NOT NULL AFTER `week_end_date`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `sms_sent` tinyint(1) DEFAULT 0 AFTER `amount_paid`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `sms_sent_date` datetime DEFAULT NULL AFTER `sms_sent`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `created_at` timestamp NOT NULL DEFAULT current_timestamp() AFTER `sms_sent_date`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `week_start` date DEFAULT NULL AFTER `created_at`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `week_end` date DEFAULT NULL AFTER `week_start`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `amount` decimal(10,2) DEFAULT NULL AFTER `week_end`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `paid_at` timestamp NULL DEFAULT current_timestamp() AFTER `amount`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `recorded_by` int(11) DEFAULT NULL AFTER `paid_at`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `recorded_by`;
ALTER TABLE `weekly_feeding_payments` ADD COLUMN IF NOT EXISTS `weeks_covered` int(11) DEFAULT 1 AFTER `notes`;

SET FOREIGN_KEY_CHECKS = 1;

-- Summary: 79 tables, 886 column guards, 77 index guards.
