-- EduFlow 360 — multi-school schema.
-- Import this once via cPanel → phpMyAdmin → your database → Import.
-- Safe to re-run: uses CREATE TABLE IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS schools (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  slug          VARCHAR(40) NOT NULL UNIQUE,
  name          VARCHAR(200) NOT NULL,
  tier_id       VARCHAR(20) NOT NULL DEFAULT 't3',
  cycle         ENUM('monthly','quarterly','yearly') NOT NULL DEFAULT 'quarterly',
  status        ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  ai_credits    INT NOT NULL DEFAULT 0,
  sms_credits   INT NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  school_id     INT NOT NULL,
  name          VARCHAR(150) NOT NULL,
  email         VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','director','principal','bursar','teacher','parent') NOT NULL DEFAULT 'admin',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  UNIQUE KEY school_email (school_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Platform onboarding decisions are separate from school-level operational
-- approval permissions. Every status change is recorded with actor and request metadata.
CREATE TABLE IF NOT EXISTS school_onboarding_audit (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  school_id       INT NOT NULL,
  actor_user_id   INT NOT NULL,
  action          ENUM('approve','reject','suspend') NOT NULL,
  previous_status ENUM('pending','approved','rejected','suspended') NOT NULL,
  new_status      ENUM('pending','approved','rejected','suspended') NOT NULL,
  reason          VARCHAR(500) NOT NULL DEFAULT '',
  metadata        JSON NULL,
  ip_address      VARCHAR(45) NOT NULL DEFAULT '',
  user_agent      VARCHAR(512) NOT NULL DEFAULT '',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX onboarding_audit_school_created (school_id, created_at),
  INDEX onboarding_audit_actor_created (actor_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- school-scoped permission below. Directors are implicit and are not stored
-- as a grant, so a missing row fails closed for every other eligible role.
CREATE TABLE IF NOT EXISTS approval_permissions (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  school_id     INT NOT NULL,
  role          ENUM('admin','principal','bursar') NOT NULL,
  can_finalize  TINYINT(1) NOT NULL DEFAULT 0,
  granted_by    INT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY school_approval_role (school_id, role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS students (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  school_id     INT NOT NULL,
  name          VARCHAR(150) NOT NULL,
  class_name    VARCHAR(60) NOT NULL,
  parent_name   VARCHAR(150) NOT NULL DEFAULT '',
  parent_phone  VARCHAR(30)  NOT NULL DEFAULT '',
  parent_email  VARCHAR(190) NOT NULL DEFAULT '',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teachers (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  school_id     INT NOT NULL,
  name          VARCHAR(150) NOT NULL,
  subject       VARCHAR(100) NOT NULL DEFAULT '',
  phone         VARCHAR(30)  NOT NULL DEFAULT '',
  email         VARCHAR(190) NOT NULL DEFAULT '',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Everything else the app currently keeps in browser localStorage
-- (fee consents/instalments, communications log, comment-automation
-- settings, result cards, social handles, subscriptions history...)
-- still needs an equivalent table here, following the same
-- "school_id FOREIGN KEY, scoped by session" pattern as the tables
-- above. See backend-php/README.md for the prioritized list.