-- INNENT SCHOOL360 | Migration 001: Foundation
-- MySQL 8+ / MariaDB 10.6+ | InnoDB | utf8mb4
-- Covers: plans, tenancy, school setup, academic calendar, RBAC, users, audit,
--         students, parents, class history. Spec sections 7-12, 14, 16, 20, 79, 91, 93, 104.
-- Tenant integrity rule: child tables carry school_id AND use composite FKs
-- (school_id, parent_id) -> parent(school_id, id) so cross-school links are impossible.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ========== PLATFORM ==========
CREATE TABLE plans (
  id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,            -- starter | professional | enterprise
  name VARCHAR(80) NOT NULL,
  features JSON NOT NULL,                      -- feature flags e.g. {"transport":true,"ai":false}
  max_students INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE school_groups (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE schools (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  group_id INT UNSIGNED NULL,
  plan_id TINYINT UNSIGNED NULL,
  code VARCHAR(20) NOT NULL UNIQUE,            -- subdomain / prefix e.g. INNACAD
  name VARCHAR(200) NOT NULL,
  motto VARCHAR(255) NULL,
  logo_path VARCHAR(255) NULL,
  school_type ENUM('pre_primary','primary','junior','senior','secondary','mixed','international') NOT NULL DEFAULT 'primary',
  residence_type ENUM('day','boarding','day_boarding') NOT NULL DEFAULT 'day',
  nemis_code VARCHAR(30) NULL,                 -- identifier only; SCHOOL360 is not NEMIS
  knec_code VARCHAR(30) NULL,
  registration_no VARCHAR(60) NULL,
  county VARCHAR(60) NULL, sub_county VARCHAR(60) NULL,
  address VARCHAR(255) NULL, phone VARCHAR(30) NULL, email VARCHAR(150) NULL, website VARCHAR(150) NULL,
  timezone VARCHAR(40) NOT NULL DEFAULT 'Africa/Nairobi',
  currency CHAR(3) NOT NULL DEFAULT 'KES',
  status ENUM('onboarding','active','suspended','closed') NOT NULL DEFAULT 'onboarding',
  subscription_expires_at DATE NULL,           -- expiry restricts features, NEVER deletes data
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  KEY idx_schools_group (group_id),
  CONSTRAINT fk_schools_group FOREIGN KEY (group_id) REFERENCES school_groups(id),
  CONSTRAINT fk_schools_plan  FOREIGN KEY (plan_id)  REFERENCES plans(id)
) ENGINE=InnoDB;

-- Per-school key/value configuration (grading scheme, attendance threshold, prefixes, SMS sender...)
CREATE TABLE school_settings (
  school_id INT UNSIGNED NOT NULL,
  setting_key VARCHAR(80) NOT NULL,
  setting_value TEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,     -- secrets stored encrypted by the app
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (school_id, setting_key),
  CONSTRAINT fk_set_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

-- ========== SCHOOL STRUCTURE ==========
CREATE TABLE campuses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  address VARCHAR(255) NULL,
  is_main TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_campus (school_id, name),
  UNIQUE KEY uq_campus_tenant (school_id, id),
  CONSTRAINT fk_campus_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

CREATE TABLE departments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  UNIQUE KEY uq_dept (school_id, name),
  UNIQUE KEY uq_dept_tenant (school_id, id),
  CONSTRAINT fk_dept_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

CREATE TABLE academic_years (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  name VARCHAR(20) NOT NULL,                   -- e.g. 2026
  start_date DATE NOT NULL, end_date DATE NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  active_flag TINYINT GENERATED ALWAYS AS (IF(is_active=1,1,NULL)) STORED,
  UNIQUE KEY uq_year (school_id, name),
  UNIQUE KEY uq_year_tenant (school_id, id),
  UNIQUE KEY uq_one_active_year (school_id, active_flag),   -- only one active year per school
  CONSTRAINT fk_year_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

CREATE TABLE terms (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  academic_year_id INT UNSIGNED NOT NULL,
  name VARCHAR(30) NOT NULL,                   -- Term 1
  start_date DATE NOT NULL, end_date DATE NOT NULL,
  next_term_opens DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  active_flag TINYINT GENERATED ALWAYS AS (IF(is_active=1,1,NULL)) STORED,
  UNIQUE KEY uq_term (school_id, academic_year_id, name),
  UNIQUE KEY uq_term_tenant (school_id, id),
  UNIQUE KEY uq_one_active_term (school_id, active_flag),
  CONSTRAINT fk_term_year FOREIGN KEY (school_id, academic_year_id) REFERENCES academic_years(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE classes (                         -- Grade 4, Form 2, PP1 ... (levels are configurable, not hard-coded)
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  campus_id INT UNSIGNED NULL,
  name VARCHAR(60) NOT NULL,
  level_order SMALLINT NOT NULL DEFAULT 0,     -- drives promotion order
  curriculum ENUM('cbc','cbe','legacy','other') NOT NULL DEFAULT 'cbc',
  UNIQUE KEY uq_class (school_id, campus_id, name),
  UNIQUE KEY uq_class_tenant (school_id, id),
  CONSTRAINT fk_class_campus FOREIGN KEY (school_id, campus_id) REFERENCES campuses(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE streams (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  name VARCHAR(60) NOT NULL,
  capacity SMALLINT UNSIGNED NULL,
  UNIQUE KEY uq_stream (school_id, class_id, name),
  UNIQUE KEY uq_stream_tenant (school_id, id),
  CONSTRAINT fk_stream_class FOREIGN KEY (school_id, class_id) REFERENCES classes(school_id, id)
) ENGINE=InnoDB;

-- ========== USERS & RBAC ==========
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  school_id INT UNSIGNED NULL,                 -- NULL = platform-level user
  user_type ENUM('platform','staff','parent','student','applicant') NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,         -- password_hash(PASSWORD_ARGON2ID)
  full_name VARCHAR(150) NOT NULL,
  status ENUM('pending','active','locked','disabled') NOT NULL DEFAULT 'pending',
  twofa_method ENUM('none','email','sms','totp') NOT NULL DEFAULT 'none',
  twofa_secret VARBINARY(255) NULL,            -- encrypted at app level
  twofa_recovery JSON NULL,                    -- hashed recovery codes
  failed_logins TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  last_login_at DATETIME NULL, last_login_ip VARCHAR(45) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  UNIQUE KEY uq_user_email (school_id, email),
  UNIQUE KEY uq_user_tenant (school_id, id),
  KEY idx_user_phone (school_id, phone),
  CONSTRAINT fk_user_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NULL,                 -- NULL = system template role
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  scope ENUM('platform','school','external') NOT NULL DEFAULT 'school',
  requires_2fa TINYINT(1) NOT NULL DEFAULT 0,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_role (school_id, code),
  UNIQUE KEY uq_role_tenant (school_id, id),
  CONSTRAINT fk_role_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

CREATE TABLE permissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(80) NOT NULL UNIQUE,            -- students.view, fees.reverse, exams.publish_results
  module VARCHAR(40) NOT NULL,
  description VARCHAR(200) NULL,
  is_sensitive TINYINT(1) NOT NULL DEFAULT 0,  -- sensitive views get audit-logged
  KEY idx_perm_module (module)
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_roles (                      -- users may hold multiple roles
  user_id BIGINT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  campus_id INT UNSIGNED NULL,                 -- optional campus-limited role
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ========== SECURITY ==========
CREATE TABLE login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  identifier VARCHAR(190) NOT NULL,
  ip VARCHAR(45) NOT NULL,
  success TINYINT(1) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_la_ident (identifier, created_at),
  KEY idx_la_ip (ip, created_at)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (                      -- append-only: app DB user should have INSERT/SELECT only
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(40) NOT NULL,                 -- login, create, update, delete, view_sensitive, export, ai_action
  module VARCHAR(40) NOT NULL,
  record_table VARCHAR(60) NULL,
  record_id VARCHAR(40) NULL,
  old_values JSON NULL, new_values JSON NULL,
  ip VARCHAR(45) NULL, user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY idx_audit_school_time (school_id, created_at),
  KEY idx_audit_record (record_table, record_id),
  KEY idx_audit_user (user_id, created_at)
) ENGINE=InnoDB;

-- ========== LEARNERS & PARENTS ==========
CREATE TABLE students (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  school_id INT UNSIGNED NOT NULL,
  campus_id INT UNSIGNED NULL,
  admission_no VARCHAR(30) NOT NULL,
  nemis_upi VARCHAR(30) NULL,
  first_name VARCHAR(80) NOT NULL, middle_name VARCHAR(80) NULL, last_name VARCHAR(80) NOT NULL,
  preferred_name VARCHAR(80) NULL,
  gender ENUM('male','female','other') NOT NULL,
  date_of_birth DATE NOT NULL,
  birth_cert_no VARCHAR(40) NULL,
  nationality VARCHAR(60) NOT NULL DEFAULT 'Kenyan',
  county VARCHAR(60) NULL,
  photo_path VARCHAR(255) NULL,                -- private storage path, streamed via authorised endpoint
  previous_school VARCHAR(200) NULL,
  residence ENUM('day','boarding') NOT NULL DEFAULT 'day',
  admission_date DATE NULL,
  status ENUM('applicant','active','suspended','transferred','graduated','expelled','withdrawn','deceased','archived') NOT NULL DEFAULT 'active',
  user_id BIGINT UNSIGNED NULL,                -- student portal login
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  UNIQUE KEY uq_adm (school_id, admission_no),
  UNIQUE KEY uq_upi (school_id, nemis_upi),
  UNIQUE KEY uq_student_tenant (school_id, id),
  KEY idx_stu_name (school_id, last_name, first_name),
  KEY idx_stu_dup (school_id, date_of_birth, last_name),   -- duplicate detection (sec 86)
  KEY idx_stu_cert (school_id, birth_cert_no),
  KEY idx_stu_status (school_id, status),
  CONSTRAINT fk_stu_school FOREIGN KEY (school_id) REFERENCES schools(id),
  CONSTRAINT fk_stu_campus FOREIGN KEY (school_id, campus_id) REFERENCES campuses(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE parents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE,
  school_id INT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,                -- parent portal login
  full_name VARCHAR(150) NOT NULL,
  national_id VARCHAR(30) NULL,
  phone VARCHAR(30) NOT NULL,                  -- normalised +2547XXXXXXXX
  alt_phone VARCHAR(30) NULL,
  email VARCHAR(150) NULL,
  address VARCHAR(255) NULL, occupation VARCHAR(100) NULL,
  comm_pref ENUM('sms','email','whatsapp','portal') NOT NULL DEFAULT 'sms',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  UNIQUE KEY uq_parent_tenant (school_id, id),
  KEY idx_parent_phone (school_id, phone),
  CONSTRAINT fk_parent_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

CREATE TABLE student_guardians (               -- many-to-many
  school_id INT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NOT NULL,
  relationship VARCHAR(40) NOT NULL,           -- mother, father, guardian...
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  is_financially_responsible TINYINT(1) NOT NULL DEFAULT 0,
  can_pickup TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (student_id, parent_id),
  KEY idx_sg_parent (school_id, parent_id),
  CONSTRAINT fk_sg_student FOREIGN KEY (school_id, student_id) REFERENCES students(school_id, id),
  CONSTRAINT fk_sg_parent  FOREIGN KEY (school_id, parent_id)  REFERENCES parents(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE student_class_history (           -- history is never overwritten (sec 20, 29)
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  academic_year_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NULL,
  class_id INT UNSIGNED NOT NULL,
  stream_id INT UNSIGNED NULL,
  outcome ENUM('in_progress','promoted','repeated','transferred','graduated','withdrawn') NOT NULL DEFAULT 'in_progress',
  started_on DATE NOT NULL, ended_on DATE NULL,
  UNIQUE KEY uq_history (student_id, academic_year_id),
  KEY idx_sch_class (school_id, academic_year_id, class_id, stream_id),
  CONSTRAINT fk_sch_student FOREIGN KEY (school_id, student_id) REFERENCES students(school_id, id),
  CONSTRAINT fk_sch_year    FOREIGN KEY (school_id, academic_year_id) REFERENCES academic_years(school_id, id),
  CONSTRAINT fk_sch_term    FOREIGN KEY (school_id, term_id) REFERENCES terms(school_id, id),
  CONSTRAINT fk_sch_class   FOREIGN KEY (school_id, class_id) REFERENCES classes(school_id, id),
  CONSTRAINT fk_sch_stream  FOREIGN KEY (school_id, stream_id) REFERENCES streams(school_id, id)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ========== SEEDS ==========
INSERT INTO plans (code, name, features, max_students) VALUES
('starter','Starter','{"students":true,"attendance":true,"fees":true,"exams":true,"transport":false,"boarding":false,"ai":false}',500),
('professional','Professional','{"students":true,"attendance":true,"fees":true,"exams":true,"transport":true,"boarding":true,"library":true,"ai":false}',2000),
('enterprise','Enterprise','{"all":true,"multi_campus":true,"ai":true}',NULL);

INSERT INTO permissions (code, module, is_sensitive) VALUES
('students.view','students',0),('students.create','students',0),('students.edit','students',0),('students.delete','students',1),
('students.export','students',1),('parents.view','parents',0),('parents.manage','parents',0),
('academics.manage','academics',0),
('attendance.view','attendance',0),('attendance.mark','attendance',0),
('fees.view','fees',0),('fees.create','fees',0),('fees.reverse','fees',1),('fees.refund','fees',1),
('exams.create','exams',0),('exams.enter_marks','exams',0),('exams.approve_marks','exams',1),('exams.publish_results','exams',1),
('health.view','health',1),('discipline.view','discipline',1),
('transport.view','transport',0),('transport.manage','transport',0),
('settings.manage','settings',0),('users.manage','users',1),('roles.manage','roles',1),('audit.view','audit',1);

INSERT INTO roles (school_id, code, name, scope, is_system, requires_2fa) VALUES
(NULL,'super_admin','Super Administrator','platform',1,1),
(NULL,'school_admin','School Administrator','school',1,1),
(NULL,'principal','Principal','school',1,1),
(NULL,'bursar','Bursar','school',1,1),
(NULL,'class_teacher','Class Teacher','school',1,0),
(NULL,'subject_teacher','Subject Teacher','school',1,0),
(NULL,'nurse','Nurse','school',1,0),
(NULL,'parent','Parent/Guardian','external',1,0),
(NULL,'student','Student/Learner','external',1,0);

-- Template permission grants (copied into each school's own roles at onboarding)
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.school_id IS NULL AND (
  r.code IN ('super_admin','school_admin')
  OR (r.code='principal'       AND p.code NOT IN ('users.manage','roles.manage','fees.refund'))
  OR (r.code='bursar'          AND p.module IN ('fees') OR (r.code='bursar' AND p.code IN ('students.view','parents.view')))
  OR (r.code='class_teacher'   AND p.code IN ('students.view','parents.view','attendance.view','attendance.mark','exams.enter_marks'))
  OR (r.code='subject_teacher' AND p.code IN ('students.view','attendance.view','exams.enter_marks'))
  OR (r.code='nurse'           AND p.code IN ('students.view','health.view'))
);