CREATE TABLE IF NOT EXISTS online_payments (

  id INT AUTO_INCREMENT PRIMARY KEY,

  student_id INT NOT NULL,

  parent_name VARCHAR(150) DEFAULT NULL,

  parent_phone VARCHAR(20) DEFAULT NULL,

  parent_email VARCHAR(150) DEFAULT NULL,

  fee_structure_ids JSON DEFAULT NULL,

  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 JSON DEFAULT NULL,

  status ENUM('pending','success','failed','refunded')

    NOT NULL DEFAULT 'pending',

  receipt_number VARCHAR(50) DEFAULT NULL,

  initiated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  completed_at DATETIME DEFAULT NULL,

  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



ALTER TABLE users

  ADD COLUMN IF NOT EXISTS photo VARCHAR(255) DEFAULT NULL;



ALTER TABLE sms_logs

  ADD COLUMN IF NOT EXISTS type VARCHAR(50) DEFAULT NULL;



INSERT IGNORE INTO settings (setting_key, setting_value) VALUES

('paystack_secret_key',  ''),

('paystack_public_key',  ''),

('hubtel_client_id',     ''),

('hubtel_client_secret', ''),

('hubtel_sender_id',     'EduTrack'),

('payment_currency',     'GHS'),

('payment_enabled',      '1'),

('current_term',         'First Term'),

('smtp_host',            ''),

('smtp_port',            '587'),

('smtp_user',            ''),

('smtp_pass',            ''),

('from_email',           ''),

('from_name',            '');



CREATE TABLE IF NOT EXISTS timetable_email_reminders (

  id INT AUTO_INCREMENT PRIMARY KEY,

  teacher_id INT NOT NULL,

  slot_id INT 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 DEFAULT CURRENT_TIMESTAMP,

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

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

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

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS exam_questions (

  id INT AUTO_INCREMENT PRIMARY KEY,

  subject_id INT NOT NULL,

  class_name VARCHAR(100) NOT NULL,

  term VARCHAR(50) DEFAULT NULL,

  academic_year VARCHAR(20) DEFAULT NULL,

  question_type ENUM('mcq','theory','fill_blank') NOT NULL DEFAULT 'mcq',

  question TEXT NOT 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,

  correct_answer ENUM('A','B','C','D') DEFAULT NULL,

  marks INT NOT NULL DEFAULT 1,

  difficulty ENUM('easy','medium','hard') NOT NULL DEFAULT 'medium',

  created_by INT DEFAULT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



ALTER TABLE exam_questions ENGINE=InnoDB;

ALTER TABLE exam_questions

  ADD COLUMN IF NOT EXISTS subject_id INT DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS class_name VARCHAR(100) DEFAULT '',

  ADD COLUMN IF NOT EXISTS term VARCHAR(50) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS academic_year VARCHAR(20) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS question_type ENUM('mcq','theory','fill_blank') NOT NULL DEFAULT 'mcq',

  ADD COLUMN IF NOT EXISTS question TEXT DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS option_a VARCHAR(500) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS option_b VARCHAR(500) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS option_c VARCHAR(500) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS option_d VARCHAR(500) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS marks INT NOT NULL DEFAULT 1,

  ADD COLUMN IF NOT EXISTS difficulty ENUM('easy','medium','hard') NOT NULL DEFAULT 'medium',

  ADD COLUMN IF NOT EXISTS created_by INT DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE exam_questions

  MODIFY COLUMN subject_id INT DEFAULT NULL,

  MODIFY COLUMN class_name VARCHAR(100) DEFAULT '',

  MODIFY COLUMN question TEXT DEFAULT NULL;

ALTER TABLE exam_questions

  ADD INDEX IF NOT EXISTS idx_exam_questions_subject_id (subject_id);



CREATE TABLE IF NOT EXISTS exam_papers (

  id INT AUTO_INCREMENT PRIMARY KEY,

  title VARCHAR(255) NOT NULL,

  subject_id INT DEFAULT NULL,

  class_name VARCHAR(100) NOT NULL,

  term VARCHAR(50) NOT NULL,

  academic_year VARCHAR(20) NOT NULL,

  duration_minutes INT NOT NULL DEFAULT 60,

  total_marks INT NOT NULL DEFAULT 100,

  instructions TEXT DEFAULT NULL,

  status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',

  created_by INT DEFAULT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS exam_paper_questions (

  id INT AUTO_INCREMENT PRIMARY KEY,

  paper_id INT NOT NULL,

  question_id INT NOT NULL,

  marks INT NOT NULL DEFAULT 1,

  order_no INT NOT NULL DEFAULT 0,

  UNIQUE KEY unique_paper_question (paper_id, question_id),

  FOREIGN KEY (paper_id) REFERENCES exam_papers(id) ON DELETE CASCADE,

  FOREIGN KEY (question_id) REFERENCES exam_questions(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS exam_schedules (

  id INT AUTO_INCREMENT PRIMARY KEY,

  paper_id INT 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 DEFAULT NULL,

  status ENUM('scheduled','ongoing','completed','cancelled') NOT NULL DEFAULT 'scheduled',

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (paper_id) REFERENCES exam_papers(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS exam_results (

  id INT AUTO_INCREMENT PRIMARY KEY,

  paper_id INT NOT NULL,

  student_id INT NOT NULL,

  score DECIMAL(6,2) DEFAULT NULL,

  grade VARCHAR(5) DEFAULT NULL,

  remarks TEXT DEFAULT NULL,

  marked_by INT DEFAULT NULL,

  submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY unique_result (paper_id, student_id),

  FOREIGN KEY (paper_id) REFERENCES exam_papers(id) ON DELETE CASCADE,

  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Timetable: period definitions

CREATE TABLE IF NOT EXISTS timetable_periods (

  id INT AUTO_INCREMENT PRIMARY KEY,

  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 NOT NULL DEFAULT 0,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Timetable: slot assignments

CREATE TABLE IF NOT EXISTS timetable_slots (

  id INT AUTO_INCREMENT PRIMARY KEY,

  class_name VARCHAR(100) NOT NULL,

  day_of_week ENUM('Mon','Tue','Wed','Thu','Fri','Sat') NOT NULL,

  period_id INT NOT NULL,

  subject_id INT DEFAULT NULL,

  teacher_id INT DEFAULT NULL,

  room VARCHAR(100) DEFAULT NULL,

  academic_year VARCHAR(20) NOT NULL,

  term VARCHAR(50) NOT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

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

  FOREIGN KEY (period_id) REFERENCES timetable_periods(id) ON DELETE CASCADE,

  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Timetable: substitution log

CREATE TABLE IF NOT EXISTS timetable_substitutions (

  id INT AUTO_INCREMENT PRIMARY KEY,

  slot_id INT NOT NULL,

  original_teacher_id INT DEFAULT NULL,

  substitute_teacher_id INT NOT NULL,

  substitution_date DATE NOT NULL,

  reason TEXT DEFAULT NULL,

  recorded_by INT DEFAULT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (slot_id) REFERENCES timetable_slots(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Seed default periods (Ghana school day)

INSERT IGNORE INTO timetable_periods

  (period_name, start_time, end_time, is_break, order_no) VALUES

('Period 1',   '07:30:00', '08:10:00', 0, 1),

('Period 2',   '08:10:00', '08:50:00', 0, 2),

('Period 3',   '08:50:00', '09:30:00', 0, 3),

('Break',      '09:30:00', '09:50:00', 1, 4),

('Period 4',   '09:50:00', '10:30:00', 0, 5),

('Period 5',   '10:30:00', '11:10:00', 0, 6),

('Period 6',   '11:10:00', '11:50:00', 0, 7),

('Lunch',      '11:50:00', '12:30:00', 1, 8),

('Period 7',   '12:30:00', '13:10:00', 0, 9),

('Period 8',   '13:10:00', '13:50:00', 0, 10);



CREATE TABLE IF NOT EXISTS parent_accounts (

  id INT AUTO_INCREMENT PRIMARY KEY,

  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 DEFAULT CURRENT_TIMESTAMP,

  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY unique_phone (phone),

  UNIQUE KEY unique_email (email)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS parent_student_link (

  id INT AUTO_INCREMENT PRIMARY KEY,

  parent_id INT NOT NULL,

  student_id INT NOT NULL,

  relationship VARCHAR(50) DEFAULT 'Parent',

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY unique_link (parent_id, student_id),

  FOREIGN KEY (parent_id) REFERENCES parent_accounts(id) ON DELETE CASCADE,

  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS parent_notifications (

  id INT AUTO_INCREMENT PRIMARY KEY,

  parent_id INT 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 DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (parent_id) REFERENCES parent_accounts(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS parent_messages (

  id INT AUTO_INCREMENT PRIMARY KEY,

  parent_id INT NOT NULL,

  admin_id INT 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 DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (parent_id) REFERENCES parent_accounts(id) ON DELETE CASCADE,

  FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS ai_study_materials (

  id INT AUTO_INCREMENT PRIMARY KEY,

  subject_id INT 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 DEFAULT NULL,

  extracted_content LONGTEXT DEFAULT NULL,

  extraction_status ENUM('pending','processing','done','failed')

    NOT NULL DEFAULT 'pending',

  uploaded_by INT DEFAULT NULL,

  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS ai_generation_jobs (

  id INT AUTO_INCREMENT PRIMARY KEY,

  material_id INT DEFAULT NULL,

  subject_id INT 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 NOT NULL DEFAULT 0,

  theory_count INT NOT NULL DEFAULT 0,

  fill_blank_count INT NOT NULL DEFAULT 0,

  diagram_count INT 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 DEFAULT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  completed_at TIMESTAMP NULL DEFAULT NULL,

  FOREIGN KEY (material_id) REFERENCES ai_study_materials(id)

    ON DELETE SET NULL,

  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



ALTER TABLE exam_questions

  ADD COLUMN IF NOT EXISTS diagram_required TINYINT(1) NOT NULL DEFAULT 0,

  ADD COLUMN IF NOT EXISTS diagram_type VARCHAR(50) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS diagram_spec JSON DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS ai_generated TINYINT(1) NOT NULL DEFAULT 0,

  ADD COLUMN IF NOT EXISTS generation_job_id INT DEFAULT NULL;



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;



ALTER TABLE exam_papers

  ADD COLUMN IF NOT EXISTS paper_file_path VARCHAR(255) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS answer_key_file_path VARCHAR(255) DEFAULT NULL;



-- ── AI Fallback System additions ─────────────────────────────────



-- Track which AI engine generated each job

ALTER TABLE ai_generation_jobs

  ADD COLUMN IF NOT EXISTS engine_used VARCHAR(20) DEFAULT NULL;



-- AI engine configuration table (priority + future settings)

CREATE TABLE IF NOT EXISTS ai_engine_config (

  id            INT AUTO_INCREMENT PRIMARY KEY,

  setting_key   VARCHAR(100) UNIQUE NOT NULL,

  setting_value TEXT,

  updated_by    INT DEFAULT NULL,

  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP

                ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Seed default engine priority

INSERT IGNORE INTO ai_engine_config (setting_key, setting_value)

VALUES ('engine_priority', 'claude,groq,gemini');



-- ═══════════════════════════════════════════════════════════════

-- PHASE 7: RBC AI Agent Tables

-- ═══════════════════════════════════════════════════════════════



CREATE TABLE IF NOT EXISTS ai_usage_log (

  id               INT AUTO_INCREMENT PRIMARY KEY,

  provider         VARCHAR(50) NOT NULL,

  prompt_preview   VARCHAR(200) DEFAULT NULL,

  response_preview VARCHAR(200) DEFAULT NULL,

  user_id          INT DEFAULT NULL,

  task_type        VARCHAR(100) DEFAULT NULL,

  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS ai_tasks (

  id            INT AUTO_INCREMENT PRIMARY KEY,

  task_type     VARCHAR(100) NOT NULL,

  payload       JSON DEFAULT NULL,

  status        ENUM('pending','running','completed','failed') NOT NULL DEFAULT 'pending',

  result        JSON DEFAULT NULL,

  triggered_by  ENUM('manual','scheduled','chat') DEFAULT 'manual',

  provider_used VARCHAR(50) DEFAULT NULL,

  created_by    INT DEFAULT NULL,

  scheduled_at  DATETIME DEFAULT NULL,

  started_at    DATETIME DEFAULT NULL,

  completed_at  DATETIME DEFAULT NULL,

  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS ai_chat_logs (

  id            INT AUTO_INCREMENT PRIMARY KEY,

  session_id    VARCHAR(100) NOT NULL,

  user_id       INT 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 DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS ai_schedules (

  id                   INT AUTO_INCREMENT PRIMARY KEY,

  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              JSON DEFAULT NULL,

  is_active            TINYINT(1) NOT NULL DEFAULT 1,

  last_run             DATETIME DEFAULT NULL,

  next_run             DATETIME DEFAULT NULL,

  created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Add parent_accounts columns needed by bulk action

ALTER TABLE parent_accounts

  ADD COLUMN IF NOT EXISTS parent_name VARCHAR(150) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS username VARCHAR(100) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS password_hash VARCHAR(255) DEFAULT NULL;



-- Add notification columns used by bulk_notify action

ALTER TABLE parent_notifications

  ADD COLUMN IF NOT EXISTS notification_type VARCHAR(50) DEFAULT 'general',

  ADD COLUMN IF NOT EXISTS is_sent TINYINT(1) DEFAULT 0,

  ADD COLUMN IF NOT EXISTS sent_by INT DEFAULT NULL;



-- Seed AI provider API key settings (empty — admin fills via Settings page)

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES

('claude_api_key',  ''),

('groq_api_key',    ''),

('gemini_api_key',  ''),

('openai_api_key',  '');



-- Update engine priority to include openai

UPDATE ai_engine_config SET setting_value = 'claude,groq,gemini,openai'

WHERE setting_key = 'engine_priority';



-- Add order_num column to exam_paper_questions if not present

ALTER TABLE exam_paper_questions

  ADD COLUMN IF NOT EXISTS order_num INT DEFAULT 0;



-- Add is_active to exam_questions for bank filtering

ALTER TABLE exam_questions

  ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1;



-- ── Ensure OTP columns exist for parent password reset ─────────────────

ALTER TABLE parent_accounts

  ADD COLUMN IF NOT EXISTS otp_code VARCHAR(10) DEFAULT NULL,

  ADD COLUMN IF NOT EXISTS otp_expires DATETIME DEFAULT NULL;



-- ── Cron token setting ─────────────────────────────────────────────────

INSERT IGNORE INTO settings (setting_key, setting_value)

VALUES ('cron_token', '');



-- ── Classes table ──────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS classes (

  id INT AUTO_INCREMENT PRIMARY KEY,

  class_name VARCHAR(100) NOT NULL,

  class_level VARCHAR(50) DEFAULT NULL,

  class_teacher_id INT DEFAULT NULL,

  capacity INT DEFAULT NULL,

  academic_year VARCHAR(20) DEFAULT NULL,

  is_active TINYINT(1) NOT NULL DEFAULT 1,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY unique_class_year (class_name, academic_year),

  FOREIGN KEY (class_teacher_id) REFERENCES staff(id) ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Seed classes from existing student records

INSERT IGNORE INTO classes (class_name, academic_year)

SELECT DISTINCT class, academic_year FROM students

WHERE class IS NOT NULL AND class != '';



-- ── Attendance table ───────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS attendance (

  id INT AUTO_INCREMENT PRIMARY KEY,

  student_id INT 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 DEFAULT NULL,

  notes VARCHAR(255) DEFAULT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY unique_attendance (student_id, attendance_date),

  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,

  INDEX idx_att_date (attendance_date),

  INDEX idx_att_class (class_name),

  INDEX idx_att_student (student_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- ── Paystack Subaccount / Settlement (Phase: Online Payments) ─────────

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES

('paystack_subaccount_code',   ''),

('paystack_subaccount_id',     ''),

('paystack_bearer',            'account'),

('paystack_split_enabled',     '0'),

('school_bank_name',           ''),

('school_bank_account_number', ''),

('school_bank_account_name',   ''),

('school_bank_code',           '');



-- ── Communication tables ───────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS announcements (

  id INT AUTO_INCREMENT PRIMARY KEY,

  title VARCHAR(200) NOT NULL,

  body TEXT NOT NULL,

  author_id INT NOT NULL,

  target_roles JSON DEFAULT NULL,

  is_pinned TINYINT(1) DEFAULT 0,

  expires_at DATE DEFAULT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE IF NOT EXISTS internal_messages (

  id INT AUTO_INCREMENT PRIMARY KEY,

  sender_id INT NOT NULL,

  recipient_id INT NOT NULL,

  subject VARCHAR(200) DEFAULT NULL,

  body TEXT NOT NULL,

  is_read TINYINT(1) DEFAULT 0,

  read_at DATETIME DEFAULT NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,

  FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE,

  INDEX idx_msg_recipient (recipient_id),

  INDEX idx_msg_sender (sender_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;





-- ============================================================

-- Teacher Portal — Database Migration

-- Run once. Safe to re-run (IF NOT EXISTS throughout).

-- ============================================================



-- ── Lesson Plans ──────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `teacher_lesson_plans` (

  `id`               INT AUTO_INCREMENT PRIMARY KEY,

  `teacher_id`       INT NOT NULL,

  `class_name`       VARCHAR(100) NOT NULL,

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

  `content`          TEXT,

  `teaching_methods` TEXT,

  `materials`        TEXT,

  `assessment`       TEXT,

  `notes`            TEXT,

  `status`           ENUM('draft','submitted','approved') NOT NULL DEFAULT 'draft',

  `approved_by`      INT DEFAULT NULL,

  `approved_at`      DATETIME DEFAULT NULL,

  `created_at`       DATETIME DEFAULT CURRENT_TIMESTAMP,

  `updated_at`       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX `idx_teacher`  (`teacher_id`),

  INDEX `idx_week`     (`week_start`),

  INDEX `idx_class`    (`class_name`),

  INDEX `idx_status`   (`status`)

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



-- ── Lesson Notes ──────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `teacher_lesson_notes` (

  `id`                 INT AUTO_INCREMENT PRIMARY KEY,

  `teacher_id`         INT NOT NULL,

  `class_name`         VARCHAR(100) NOT NULL,

  `subject_id`         INT DEFAULT NULL,

  `subject_name`       VARCHAR(100) NOT NULL DEFAULT '',

  `lesson_date`        DATE NOT NULL,

  `period_id`          INT DEFAULT NULL,

  `period_name`        VARCHAR(50) DEFAULT '',

  `what_was_taught`    TEXT,

  `objectives_met`     TEXT,

  `student_engagement` ENUM('excellent','good','fair','poor') NOT NULL DEFAULT 'good',

  `challenges`         TEXT,

  `next_lesson_plan`   TEXT,

  `created_at`         DATETIME DEFAULT CURRENT_TIMESTAMP,

  `updated_at`         DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX `idx_teacher`  (`teacher_id`),

  INDEX `idx_date`     (`lesson_date`),

  INDEX `idx_class`    (`class_name`)

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



-- ── Homework Assignments ───────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `homework` (

  `id`                    INT AUTO_INCREMENT PRIMARY KEY,

  `teacher_id`            INT NOT NULL,

  `class_name`            VARCHAR(100) NOT NULL,

  `subject_id`            INT DEFAULT NULL,

  `subject_name`          VARCHAR(100) NOT NULL DEFAULT '',

  `title`                 VARCHAR(255) NOT NULL,

  `description`           TEXT,

  `due_date`              DATE NOT NULL,

  `assigned_date`         DATE NOT NULL,

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

  INDEX `idx_teacher`     (`teacher_id`),

  INDEX `idx_class`       (`class_name`),

  INDEX `idx_due`         (`due_date`),

  INDEX `idx_visible`     (`is_visible_to_parents`, `due_date`)

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



-- ── Homework Submissions (per student) ────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `homework_submissions` (

  `id`              INT AUTO_INCREMENT PRIMARY KEY,

  `homework_id`     INT NOT NULL,

  `student_id`      INT NOT NULL,

  `status`          ENUM('pending','submitted','missing','excused') NOT NULL DEFAULT 'pending',

  `score`           DECIMAL(5,2) DEFAULT NULL,

  `teacher_comment` TEXT,

  `submitted_at`    DATETIME DEFAULT NULL,

  `marked_at`       DATETIME DEFAULT NULL,

  `marked_by`       INT DEFAULT NULL,

  `created_at`      DATETIME DEFAULT CURRENT_TIMESTAMP,

  `updated_at`      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY `unique_hw_student` (`homework_id`, `student_id`),

  INDEX `idx_homework` (`homework_id`),

  INDEX `idx_student`  (`student_id`)

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



-- ── Ensure announcements.target_role can hold 'tutor' ─────────────────────────

-- (The column is already VARCHAR(50) so no change needed — just confirm)

-- ALTER TABLE announcements MODIFY target_role VARCHAR(50) DEFAULT 'all';



-- ── Collation fix: align teacher portal tables with the rest of the database ──

-- Run these if the tables already exist with utf8mb4_unicode_ci (live server fix)

ALTER TABLE `teacher_lesson_plans`  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE `teacher_lesson_notes`  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE `homework`              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE `homework_submissions`  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;



-- Migration: Add class_name to timetable_periods

-- Run once. Idempotent on MySQL 8.0+ (IF NOT EXISTS on ALTER).



ALTER TABLE timetable_periods

  ADD COLUMN IF NOT EXISTS class_name VARCHAR(100) NOT NULL DEFAULT '' AFTER order_no;



ALTER TABLE timetable_periods

  ADD INDEX IF NOT EXISTS idx_class_name (class_name);





-- ============================================================

-- Student Interventions — teacher support notes per student

-- Run once. Safe to re-run (IF NOT EXISTS throughout).

-- ============================================================



CREATE TABLE IF NOT EXISTS `student_interventions` (

  `id`                INT AUTO_INCREMENT PRIMARY KEY,

  `student_id`        INT NOT NULL,

  `teacher_id`        INT NOT NULL,

  `class_name`        VARCHAR(100) NOT NULL,

  `subject_id`        INT DEFAULT NULL,

  `type`              ENUM('academic','attendance','behaviour','general') NOT NULL DEFAULT 'academic',

  `note`              TEXT NOT NULL,

  `action_taken`      TEXT,

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

  INDEX `idx_student`  (`student_id`),

  INDEX `idx_teacher`  (`teacher_id`),

  INDEX `idx_class`    (`class_name`),

  INDEX `idx_status`   (`status`),

  INDEX `idx_visible`  (`student_id`, `visible_to_parent`)

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



-- School Calendar: term dates + public holidays

-- Safe to run multiple times (idempotent).



CREATE TABLE IF NOT EXISTS school_terms (

    id           INT AUTO_INCREMENT PRIMARY KEY,

    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,

    UNIQUE KEY unique_term (academic_year, term)

);



CREATE TABLE IF NOT EXISTS school_holidays (

    id           INT AUTO_INCREMENT PRIMARY KEY,

    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) NULL,

    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,

    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_holiday_date (holiday_date, holiday_name(50))

);



-- Ghana 2025 Statutory Public Holidays (Ministry of Interior)

-- Fixed-date holidays

INSERT IGNORE INTO school_holidays (holiday_name, holiday_date, is_observed, is_movable, note) VALUES

('New Year''s Day',         '2026-01-01', 1, 0, NULL),

('Constitution Day',        '2026-01-07', 1, 0, NULL),

('Independence Day',        '2026-03-06', 1, 0, NULL),

('Good Friday',             '2026-04-18', 1, 0, NULL),

('Easter Saturday',         '2026-04-19', 1, 0, NULL),

('Easter Monday',           '2026-04-21', 1, 0, NULL),

('Labour Day',              '2026-05-01', 1, 0, NULL),

('Africa Unity Day',        '2026-05-25', 1, 0, NULL),

('Republic Day',            '2026-07-01', 1, 0, NULL),

('Founder''s Day',          '2026-09-21', 1, 0, NULL),

('Farmer''s Day',           '2026-12-05', 1, 0, NULL),

('Christmas Day',           '2026-12-25', 1, 0, NULL),

('Boxing Day',              '2026-12-26', 1, 0, NULL),

-- Movable Islamic holidays (approximate 2025 dates — admin should verify/adjust)

('Eid-Ul-Fitr (Eid)',       '2026-03-31', 1, 1, 'Date set by Chief Imam — verify before term starts'),

('Shaqq Day',               '2026-04-01', 1, 1, 'Date set by Chief Imam — verify before term starts'),

('Eid-Ul-Adha',             '2026-06-07', 1, 1, 'Date set by Chief Imam — verify before term starts');



