



CREATE TABLE IF NOT EXISTS  `ai_agent_memory` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `ai_chat_logs` (

  `id` int(11) NOT NULL,

  `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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `ai_engine_config` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `ai_generation_jobs` (

  `id` int(11) NOT NULL,

  `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,

  `status` enum('pending','processing','done','failed') NOT NULL DEFAULT 'pending',

  `result_summary` varchar(255) 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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `ai_generation_quota` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `ai_models` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `ai_pending_tasks` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `ai_predictions_cache` (

  `id` int(11) NOT NULL,

  `cache_key` varchar(255) NOT NULL,

  `prediction_data` longtext NOT NULL,

  `expires_at` datetime NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `ai_schedules` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;







CREATE TABLE IF NOT EXISTS  `ai_study_materials` (

  `id` int(11) NOT NULL,

  `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,

  `extracted_content` longtext DEFAULT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `ai_tasks` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `ai_training_logs` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `ai_usage_log` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `announcements` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `attendance` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `classes` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `class_subjects` (

  `id` int(11) NOT NULL,

  `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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `coupons` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;







CREATE TABLE IF NOT EXISTS  `coupon_usages` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `exam_generation_jobs` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `exam_generation_logs` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `exam_papers` (

  `id` int(11) NOT NULL,

  `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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `exam_paper_questions` (

  `id` int(11) NOT NULL,

  `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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `exam_questions` (

  `id` int(11) NOT NULL,

  `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') 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

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;





CREATE TABLE IF NOT EXISTS  `exam_results` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `exam_schedules` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `exam_templates` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE IF NOT EXISTS  `exam_workflow_logs` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `expenses` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





CREATE TABLE IF NOT EXISTS  `fee_structures` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `generated_exams` (

  `id` int(11) NOT NULL,

  `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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `grade_boundaries` (

  `id` int(11) NOT NULL,

  `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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `internal_messages` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `learning_materials` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE IF NOT EXISTS  `notifications` (

  `id` int(11) NOT NULL,

  `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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;







CREATE TABLE IF NOT EXISTS  `online_payments` (

  `id` int(11) NOT NULL,

  `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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;







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;



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;



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;



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;





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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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;



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()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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;



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;



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;



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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;







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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;





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;





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','10_min') NOT NULL,

  `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;



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,

  `order_no` int(11) NOT NULL DEFAULT 0,

  `created_at` timestamp NOT NULL DEFAULT current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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;





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;



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') NOT NULL DEFAULT 'tutor',

  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),

  `photo` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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;





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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



--

-- Indexes for table `ai_agent_memory`

--

ALTER TABLE `ai_agent_memory`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `memory_key` (`memory_key`);



--

-- Indexes for table `ai_chat_logs`

--

ALTER TABLE `ai_chat_logs`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `ai_engine_config`

--

ALTER TABLE `ai_engine_config`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `setting_key` (`setting_key`);



--

-- Indexes for table `ai_generation_jobs`

--

ALTER TABLE `ai_generation_jobs`

  ADD PRIMARY KEY (`id`),

  ADD KEY `material_id` (`material_id`),

  ADD KEY `subject_id` (`subject_id`);



--

-- Indexes for table `ai_generation_quota`

--

ALTER TABLE `ai_generation_quota`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `uq_school_month` (`school_code`,`month_year`);



--

-- Indexes for table `ai_models`

--

ALTER TABLE `ai_models`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_model` (`model_name`,`model_version`);



--

-- Indexes for table `ai_pending_tasks`

--

ALTER TABLE `ai_pending_tasks`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `ai_predictions_cache`

--

ALTER TABLE `ai_predictions_cache`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `cache_key` (`cache_key`),

  ADD KEY `idx_expires` (`expires_at`);



--

-- Indexes for table `ai_schedules`

--

ALTER TABLE `ai_schedules`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `ai_study_materials`

--

ALTER TABLE `ai_study_materials`

  ADD PRIMARY KEY (`id`),

  ADD KEY `subject_id` (`subject_id`);



--

-- Indexes for table `ai_tasks`

--

ALTER TABLE `ai_tasks`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `ai_training_logs`

--

ALTER TABLE `ai_training_logs`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_model` (`model_name`);



--

-- Indexes for table `ai_usage_log`

--

ALTER TABLE `ai_usage_log`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `announcements`

--

ALTER TABLE `announcements`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_created_at` (`created_at`),

  ADD KEY `idx_target` (`target_role`);



--

-- Indexes for table `attendance`

--

ALTER TABLE `attendance`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_attendance` (`student_id`,`attendance_date`),

  ADD KEY `idx_class_date` (`class_name`,`attendance_date`),

  ADD KEY `idx_student` (`student_id`);



--

-- Indexes for table `classes`

--

ALTER TABLE `classes`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_class_year` (`class_name`,`academic_year`);



--

-- Indexes for table `class_subjects`

--

ALTER TABLE `class_subjects`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_class_subject` (`class_name`,`subject_id`),

  ADD KEY `subject_id` (`subject_id`);



--

-- Indexes for table `coupons`

--

ALTER TABLE `coupons`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `coupon_code` (`coupon_code`),

  ADD KEY `created_by` (`created_by`);



--

-- Indexes for table `coupon_usages`

--

ALTER TABLE `coupon_usages`

  ADD PRIMARY KEY (`id`),

  ADD KEY `coupon_id` (`coupon_id`),

  ADD KEY `student_id` (`student_id`);



--

-- Indexes for table `exam_generation_jobs`

--

ALTER TABLE `exam_generation_jobs`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_exam_generation_status` (`status`,`available_at`),

  ADD KEY `idx_exam_generation_creator` (`created_by`),

  ADD KEY `idx_exam_generation_exam` (`exam_id`);



--

-- Indexes for table `exam_generation_logs`

--

ALTER TABLE `exam_generation_logs`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_exam_generation_logs_job` (`job_id`,`id`);



--

-- Indexes for table `exam_papers`

--

ALTER TABLE `exam_papers`

  ADD PRIMARY KEY (`id`),

  ADD KEY `subject_id` (`subject_id`);



--

-- Indexes for table `exam_paper_questions`

--

ALTER TABLE `exam_paper_questions`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_paper_question` (`paper_id`,`question_id`),

  ADD KEY `question_id` (`question_id`);



--

-- Indexes for table `exam_questions`

--

ALTER TABLE `exam_questions`

  ADD PRIMARY KEY (`id`),

  ADD KEY `exam_id` (`exam_id`),

  ADD KEY `idx_exam_questions_subject_id` (`subject_id`),

  ADD KEY `fk_exam_questions_generation_job` (`generation_job_id`);



--

-- Indexes for table `exam_results`

--

ALTER TABLE `exam_results`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_result` (`paper_id`,`student_id`),

  ADD KEY `student_id` (`student_id`);



--

-- Indexes for table `exam_schedules`

--

ALTER TABLE `exam_schedules`

  ADD PRIMARY KEY (`id`),

  ADD KEY `paper_id` (`paper_id`);



--

-- Indexes for table `exam_templates`

--

ALTER TABLE `exam_templates`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `exam_workflow_logs`

--

ALTER TABLE `exam_workflow_logs`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_exam` (`exam_id`),

  ADD KEY `idx_acted_at` (`acted_at`);



--

-- Indexes for table `expenses`

--

ALTER TABLE `expenses`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `fee_structures`

--

ALTER TABLE `fee_structures`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `generated_exams`

--

ALTER TABLE `generated_exams`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_class` (`class_name`),

  ADD KEY `idx_created` (`created_at`);



--

-- Indexes for table `grade_boundaries`

--

ALTER TABLE `grade_boundaries`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_class_grade` (`class_name`,`grade`);



--

-- Indexes for table `internal_messages`

--

ALTER TABLE `internal_messages`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_recipient` (`recipient_id`,`is_read`),

  ADD KEY `idx_sender` (`sender_id`),

  ADD KEY `idx_thread` (`parent_id`);



--

-- Indexes for table `learning_materials`

--

ALTER TABLE `learning_materials`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_class_subject` (`class_name`,`subject_id`),

  ADD KEY `idx_material_type` (`material_type`);



--

-- Indexes for table `notifications`

--

ALTER TABLE `notifications`

  ADD PRIMARY KEY (`id`),

  ADD KEY `student_id` (`student_id`);



--

-- Indexes for table `online_payments`

--

ALTER TABLE `online_payments`

  ADD PRIMARY KEY (`id`),

  ADD KEY `student_id` (`student_id`);



--

-- Indexes for table `parent_accounts`

--

ALTER TABLE `parent_accounts`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_phone` (`phone`),

  ADD UNIQUE KEY `unique_email` (`email`);



--

-- Indexes for table `parent_messages`

--

ALTER TABLE `parent_messages`

  ADD PRIMARY KEY (`id`),

  ADD KEY `parent_id` (`parent_id`),

  ADD KEY `admin_id` (`admin_id`);



--

-- Indexes for table `parent_notifications`

--

ALTER TABLE `parent_notifications`

  ADD PRIMARY KEY (`id`),

  ADD KEY `parent_id` (`parent_id`);



--

-- Indexes for table `parent_student_link`

--

ALTER TABLE `parent_student_link`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_link` (`parent_id`,`student_id`),

  ADD KEY `student_id` (`student_id`);



--

-- Indexes for table `payments`

--

ALTER TABLE `payments`

  ADD PRIMARY KEY (`id`),

  ADD KEY `student_id` (`student_id`),

  ADD KEY `fee_structure_id` (`fee_structure_id`);



--

-- Indexes for table `question_bank`

--

ALTER TABLE `question_bank`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_class_subject` (`class_name`,`subject_id`),

  ADD KEY `idx_type` (`question_type`),

  ADD KEY `idx_question_hash` (`question_hash`);



--

-- Indexes for table `report_details`

--

ALTER TABLE `report_details`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `scores`

--

ALTER TABLE `scores`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_score` (`student_id`,`subject_id`,`term`,`academic_year`),

  ADD KEY `subject_id` (`subject_id`);



--

-- Indexes for table `settings`

--

ALTER TABLE `settings`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `setting_key` (`setting_key`);



--

-- Indexes for table `sms_logs`

--

ALTER TABLE `sms_logs`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `staff`

--

ALTER TABLE `staff`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `staff_id` (`staff_id`);



--

-- Indexes for table `students`

--

ALTER TABLE `students`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `student_id` (`student_id`);



--

-- Indexes for table `subjects`

--

ALTER TABLE `subjects`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `timetable_email_reminders`

--

ALTER TABLE `timetable_email_reminders`

  ADD PRIMARY KEY (`id`),

  ADD KEY `idx_timetable_email_lookup` (`teacher_id`,`slot_id`,`schedule_date`,`reminder_type`,`status`),

  ADD KEY `fk_timetable_email_slot` (`slot_id`);



--

-- Indexes for table `timetable_periods`

--

ALTER TABLE `timetable_periods`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `timetable_slots`

--

ALTER TABLE `timetable_slots`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_slot` (`class_name`,`day_of_week`,`period_id`,`academic_year`,`term`),

  ADD KEY `period_id` (`period_id`),

  ADD KEY `subject_id` (`subject_id`);



--

-- Indexes for table `timetable_substitutions`

--

ALTER TABLE `timetable_substitutions`

  ADD PRIMARY KEY (`id`),

  ADD KEY `slot_id` (`slot_id`);



--

-- Indexes for table `users`

--

ALTER TABLE `users`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `username` (`username`);



--

-- Indexes for table `user_activities`

--

ALTER TABLE `user_activities`

  ADD PRIMARY KEY (`id`),

  ADD KEY `user_id` (`user_id`);



--

-- Indexes for table `weekly_feeding_payments`

--

ALTER TABLE `weekly_feeding_payments`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `unique_student_week` (`student_id`,`week_start_date`),

  ADD KEY `payment_id` (`payment_id`);



--

-- AUTO_INCREMENT for dumped tables

--



--

-- AUTO_INCREMENT for table `ai_agent_memory`

--

ALTER TABLE `ai_agent_memory`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;



--

-- AUTO_INCREMENT for table `ai_chat_logs`

--

ALTER TABLE `ai_chat_logs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=141;



--

-- AUTO_INCREMENT for table `ai_engine_config`

--

ALTER TABLE `ai_engine_config`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;



--

-- AUTO_INCREMENT for table `ai_generation_jobs`

--

ALTER TABLE `ai_generation_jobs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `ai_generation_quota`

--

ALTER TABLE `ai_generation_quota`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `ai_models`

--

ALTER TABLE `ai_models`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;



--

-- AUTO_INCREMENT for table `ai_pending_tasks`

--

ALTER TABLE `ai_pending_tasks`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `ai_predictions_cache`

--

ALTER TABLE `ai_predictions_cache`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `ai_schedules`

--

ALTER TABLE `ai_schedules`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `ai_study_materials`

--

ALTER TABLE `ai_study_materials`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `ai_tasks`

--

ALTER TABLE `ai_tasks`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=57;



--

-- AUTO_INCREMENT for table `ai_training_logs`

--

ALTER TABLE `ai_training_logs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;



--

-- AUTO_INCREMENT for table `ai_usage_log`

--

ALTER TABLE `ai_usage_log`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=131;



--

-- AUTO_INCREMENT for table `announcements`

--

ALTER TABLE `announcements`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;



--

-- AUTO_INCREMENT for table `attendance`

--

ALTER TABLE `attendance`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;



--

-- AUTO_INCREMENT for table `classes`

--

ALTER TABLE `classes`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;



--

-- AUTO_INCREMENT for table `class_subjects`

--

ALTER TABLE `class_subjects`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;



--

-- AUTO_INCREMENT for table `coupons`

--

ALTER TABLE `coupons`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;



--

-- AUTO_INCREMENT for table `coupon_usages`

--

ALTER TABLE `coupon_usages`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `exam_generation_jobs`

--

ALTER TABLE `exam_generation_jobs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;



--

-- AUTO_INCREMENT for table `exam_generation_logs`

--

ALTER TABLE `exam_generation_logs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;



--

-- AUTO_INCREMENT for table `exam_papers`

--

ALTER TABLE `exam_papers`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;



--

-- AUTO_INCREMENT for table `exam_paper_questions`

--

ALTER TABLE `exam_paper_questions`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=538;



--

-- AUTO_INCREMENT for table `exam_questions`

--

ALTER TABLE `exam_questions`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=492;



--

-- AUTO_INCREMENT for table `exam_results`

--

ALTER TABLE `exam_results`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `exam_schedules`

--

ALTER TABLE `exam_schedules`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;



--

-- AUTO_INCREMENT for table `exam_templates`

--

ALTER TABLE `exam_templates`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;



--

-- AUTO_INCREMENT for table `exam_workflow_logs`

--

ALTER TABLE `exam_workflow_logs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `expenses`

--

ALTER TABLE `expenses`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;



--

-- AUTO_INCREMENT for table `fee_structures`

--

ALTER TABLE `fee_structures`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=76;



--

-- AUTO_INCREMENT for table `generated_exams`

--

ALTER TABLE `generated_exams`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;



--

-- AUTO_INCREMENT for table `grade_boundaries`

--

ALTER TABLE `grade_boundaries`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=87;



--

-- AUTO_INCREMENT for table `internal_messages`

--

ALTER TABLE `internal_messages`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;



--

-- AUTO_INCREMENT for table `learning_materials`

--

ALTER TABLE `learning_materials`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;



--

-- AUTO_INCREMENT for table `notifications`

--

ALTER TABLE `notifications`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;



--

-- AUTO_INCREMENT for table `online_payments`

--

ALTER TABLE `online_payments`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;



--

-- AUTO_INCREMENT for table `parent_accounts`

--

ALTER TABLE `parent_accounts`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=53;



--

-- AUTO_INCREMENT for table `parent_messages`

--

ALTER TABLE `parent_messages`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;



--

-- AUTO_INCREMENT for table `parent_notifications`

--

ALTER TABLE `parent_notifications`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;



--

-- AUTO_INCREMENT for table `parent_student_link`

--

ALTER TABLE `parent_student_link`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=84;



--

-- AUTO_INCREMENT for table `payments`

--

ALTER TABLE `payments`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=68;



--

-- AUTO_INCREMENT for table `question_bank`

--

ALTER TABLE `question_bank`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `report_details`

--

ALTER TABLE `report_details`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;



--

-- AUTO_INCREMENT for table `scores`

--

ALTER TABLE `scores`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;



--

-- AUTO_INCREMENT for table `settings`

--

ALTER TABLE `settings`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=97;



--

-- AUTO_INCREMENT for table `sms_logs`

--

ALTER TABLE `sms_logs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=111;



--

-- AUTO_INCREMENT for table `staff`

--

ALTER TABLE `staff`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;



--

-- AUTO_INCREMENT for table `students`

--

ALTER TABLE `students`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=82;



--

-- AUTO_INCREMENT for table `subjects`

--

ALTER TABLE `subjects`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;



--

-- AUTO_INCREMENT for table `timetable_email_reminders`

--

ALTER TABLE `timetable_email_reminders`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;



--

-- AUTO_INCREMENT for table `timetable_periods`

--

ALTER TABLE `timetable_periods`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=78;



--

-- AUTO_INCREMENT for table `timetable_slots`

--

ALTER TABLE `timetable_slots`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;



--

-- AUTO_INCREMENT for table `timetable_substitutions`

--

ALTER TABLE `timetable_substitutions`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `users`

--

ALTER TABLE `users`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;



--

-- AUTO_INCREMENT for table `user_activities`

--

ALTER TABLE `user_activities`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=60;



--

-- AUTO_INCREMENT for table `weekly_feeding_payments`

--

ALTER TABLE `weekly_feeding_payments`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;



--

-- Constraints for dumped tables

--



--

-- Constraints for table `ai_generation_jobs`

--

ALTER TABLE `ai_generation_jobs`

  ADD CONSTRAINT `ai_generation_jobs_ibfk_1` FOREIGN KEY (`material_id`) REFERENCES `ai_study_materials` (`id`) ON DELETE SET NULL,

  ADD CONSTRAINT `ai_generation_jobs_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `ai_study_materials`

--

ALTER TABLE `ai_study_materials`

  ADD CONSTRAINT `ai_study_materials_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `class_subjects`

--

ALTER TABLE `class_subjects`

  ADD CONSTRAINT `class_subjects_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `coupons`

--

ALTER TABLE `coupons`

  ADD CONSTRAINT `coupons_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;



--

-- Constraints for table `coupon_usages`

--

ALTER TABLE `coupon_usages`

  ADD CONSTRAINT `coupon_usages_ibfk_1` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`),

  ADD CONSTRAINT `coupon_usages_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`);



--

-- Constraints for table `exam_papers`

--

ALTER TABLE `exam_papers`

  ADD CONSTRAINT `exam_papers_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE SET NULL;



--

-- Constraints for table `exam_paper_questions`

--

ALTER TABLE `exam_paper_questions`

  ADD CONSTRAINT `exam_paper_questions_ibfk_1` FOREIGN KEY (`paper_id`) REFERENCES `exam_papers` (`id`) ON DELETE CASCADE,

  ADD CONSTRAINT `exam_paper_questions_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `exam_questions` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `exam_questions`

--

ALTER TABLE `exam_questions`

  ADD CONSTRAINT `fk_exam_questions_generation_job` FOREIGN KEY (`generation_job_id`) REFERENCES `ai_generation_jobs` (`id`) ON DELETE SET NULL;



--

-- Constraints for table `exam_results`

--

ALTER TABLE `exam_results`

  ADD CONSTRAINT `exam_results_ibfk_1` FOREIGN KEY (`paper_id`) REFERENCES `exam_papers` (`id`) ON DELETE CASCADE,

  ADD CONSTRAINT `exam_results_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `exam_schedules`

--

ALTER TABLE `exam_schedules`

  ADD CONSTRAINT `exam_schedules_ibfk_1` FOREIGN KEY (`paper_id`) REFERENCES `exam_papers` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `notifications`

--

ALTER TABLE `notifications`

  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`);



--

-- Constraints for table `online_payments`

--

ALTER TABLE `online_payments`

  ADD CONSTRAINT `online_payments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `parent_messages`

--

ALTER TABLE `parent_messages`

  ADD CONSTRAINT `parent_messages_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent_accounts` (`id`) ON DELETE CASCADE,

  ADD CONSTRAINT `parent_messages_ibfk_2` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;



--

-- Constraints for table `parent_notifications`

--

ALTER TABLE `parent_notifications`

  ADD CONSTRAINT `parent_notifications_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent_accounts` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `parent_student_link`

--

ALTER TABLE `parent_student_link`

  ADD CONSTRAINT `parent_student_link_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent_accounts` (`id`) ON DELETE CASCADE,

  ADD CONSTRAINT `parent_student_link_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `payments`

--

ALTER TABLE `payments`

  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`),

  ADD CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`fee_structure_id`) REFERENCES `fee_structures` (`id`);



--

-- Constraints for table `scores`

--

ALTER TABLE `scores`

  ADD CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,

  ADD CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `timetable_email_reminders`

--

ALTER TABLE `timetable_email_reminders`

  ADD CONSTRAINT `fk_timetable_email_slot` FOREIGN KEY (`slot_id`) REFERENCES `timetable_slots` (`id`) ON DELETE CASCADE,

  ADD CONSTRAINT `fk_timetable_email_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `timetable_slots`

--

ALTER TABLE `timetable_slots`

  ADD CONSTRAINT `timetable_slots_ibfk_1` FOREIGN KEY (`period_id`) REFERENCES `timetable_periods` (`id`) ON DELETE CASCADE,

  ADD CONSTRAINT `timetable_slots_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE SET NULL;



--

-- Constraints for table `timetable_substitutions`

--

ALTER TABLE `timetable_substitutions`

  ADD CONSTRAINT `timetable_substitutions_ibfk_1` FOREIGN KEY (`slot_id`) REFERENCES `timetable_slots` (`id`) ON DELETE CASCADE;



--

-- Constraints for table `user_activities`

--

ALTER TABLE `user_activities`

  ADD CONSTRAINT `user_activities_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);



--

-- Constraints for table `weekly_feeding_payments`

--

ALTER TABLE `weekly_feeding_payments`

  ADD CONSTRAINT `weekly_feeding_payments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`),

  ADD CONSTRAINT `weekly_feeding_payments_ibfk_2` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`);

COMMIT;



/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;



ALTER TABLE students

  ADD COLUMN IF NOT EXISTS identity_qr_token VARCHAR(128) UNIQUE,

  ADD COLUMN IF NOT EXISTS identity_qr_generated_at DATETIME;



ALTER TABLE payments

  ADD COLUMN IF NOT EXISTS recorded_via

    ENUM('standard','express_scanner','ai_agent','import','canteen_bulk')

    DEFAULT 'standard';



-- ── canteen_settings ─────────────────────────────────────────



CREATE TABLE IF NOT EXISTS canteen_settings (

  id                              INT AUTO_INCREMENT PRIMARY KEY,

  school_id                       INT 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 DEFAULT 24,

  cook_pin_hash                   VARCHAR(255),

  scanner_pin_hash                VARCHAR(255),

  auto_ai_report                  TINYINT(1) DEFAULT 1,

  cook_dashboard_refresh_seconds  INT DEFAULT 30,

  hardware_fingerprint_model      VARCHAR(100),

  hardware_webcam_model           VARCHAR(100),

  facial_min_confidence           DECIMAL(3,2) DEFAULT 0.85,

  setup_completed                 TINYINT(1) DEFAULT 0,

  setup_completed_at              DATETIME,

  updated_at                      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY unique_school (school_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── canteen_sessions ─────────────────────────────────────────



CREATE TABLE IF NOT EXISTS canteen_sessions (

  id               INT AUTO_INCREMENT PRIMARY KEY,

  school_id        INT DEFAULT 1,

  session_name     VARCHAR(50) NOT NULL,

  session_date     DATE NOT NULL,

  start_time       TIME,

  end_time         TIME,

  price_per_meal   DECIMAL(10,2),

  classes_enrolled JSON,

  total_enrolled   INT DEFAULT 0,

  status           ENUM('scheduled','active','closed','cancelled') DEFAULT 'scheduled',

  created_by       INT,

  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_date   (session_date),

  INDEX idx_status (status)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── canteen_entitlements ──────────────────────────────────────



CREATE TABLE IF NOT EXISTS canteen_entitlements (

  id                    INT AUTO_INCREMENT PRIMARY KEY,

  student_id            INT NOT NULL,

  session_id            INT NOT NULL,

  fee_payment_id        INT,

  qr_token              VARCHAR(128) UNIQUE 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'),

  confirmed_at          DATETIME,

  confirmed_by          INT,

  device_info           VARCHAR(255),

  offline_queued        TINYINT(1) DEFAULT 0,

  offline_synced_at     DATETIME,

  created_at            DATETIME DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY unique_student_session (student_id, session_id),

  INDEX idx_token   (qr_token),

  INDEX idx_session (session_id),

  INDEX idx_status  (status)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── canteen_scans (immutable audit log) ──────────────────────



CREATE TABLE IF NOT EXISTS canteen_scans (

  id                  INT AUTO_INCREMENT PRIMARY KEY,

  entitlement_id      INT,

  student_id          INT NOT NULL,

  session_id          INT NOT NULL,

  scan_result         ENUM('success','already_confirmed','invalid_token',

                           'expired_token','not_enrolled','manual_override') NOT NULL,

  override_reason     VARCHAR(50),

  verification_method ENUM('qr','fingerprint','facial','manual_id'),

  confidence_score    DECIMAL(4,3),

  scanned_by          INT NOT NULL,

  scanned_at          DATETIME DEFAULT CURRENT_TIMESTAMP,

  device_info         VARCHAR(255),

  ip_address          VARCHAR(45),

  INDEX idx_session_date (session_id, scanned_at)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── canteen_biometric_credentials ────────────────────────────



CREATE TABLE IF NOT EXISTS canteen_biometric_credentials (

  id                        INT AUTO_INCREMENT PRIMARY KEY,

  student_id                INT NOT NULL UNIQUE,

  credential_id             VARCHAR(512),

  public_key                TEXT,

  facial_descriptor         JSON,

  facial_enrolled_at        DATETIME,

  fingerprint_enrolled_at   DATETIME,

  enrolled_by               INT,

  last_updated              DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_student (student_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── canteen_biometric_access_log (GDPR/DPA compliance) ───────



CREATE TABLE IF NOT EXISTS canteen_biometric_access_log (

  id          INT AUTO_INCREMENT PRIMARY KEY,

  student_id  INT NOT NULL,

  accessed_by INT NOT NULL,

  action      ENUM('enroll','verify','delete','export') NOT NULL,

  method      ENUM('fingerprint','facial'),

  result      ENUM('success','failure','fallback'),

  accessed_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_student  (student_id),

  INDEX idx_accessed (accessed_at)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── canteen_offline_queue (IndexedDB sync target) ────────────



CREATE TABLE IF NOT EXISTS canteen_offline_queue (

  id           INT AUTO_INCREMENT PRIMARY KEY,

  device_token VARCHAR(128),

  payload      JSON NOT NULL,

  synced       TINYINT(1) DEFAULT 0,

  synced_at    DATETIME,

  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── Seed initial settings row ─────────────────────────────────



INSERT IGNORE INTO canteen_settings (school_id) VALUES (1);

