-- ═══════════════════════════════════════════════════════════════════════════
-- SchoollyApp — Multi-tenant schema (PostgreSQL)
--
-- Tenancy model: every tenant-owned table carries school_id. Row-Level
-- Security (RLS) policies are applied as a hard backstop, so even a bug in
-- application code cannot leak one school's rows into another school's
-- response — the database itself refuses the query.
--
-- To use RLS: the app sets `app.current_school_id` at the start of each
-- request (see src/config/db.js -> withTenant()), and every policy below
-- filters on it automatically.
-- ═══════════════════════════════════════════════════════════════════════════

-- ─── PLATFORM LEVEL (no school_id — these exist above/across all tenants) ───

CREATE TABLE IF NOT EXISTS schools (
  id                SERIAL PRIMARY KEY,
  name              TEXT NOT NULL,
  slug              TEXT NOT NULL UNIQUE,          -- used in the URL: schoollyapp.com/<slug>
  monthly_fee       NUMERIC(10,2) NOT NULL DEFAULT 0,  -- custom, set/edited by the SchoollyApp Team per school; only starts applying after free_until
  free_until        DATE,                          -- registration_date + 1 year; no fee owed before this date
  payment_status    TEXT NOT NULL DEFAULT 'trial',  -- 'trial' | 'paid' | 'overdue'
  next_due_date     DATE,
  active            BOOLEAN NOT NULL DEFAULT TRUE,
  sms_enabled       BOOLEAN NOT NULL DEFAULT FALSE, -- per-school SMS toggle, set by the SchoollyApp Team
  library_enabled   BOOLEAN NOT NULL DEFAULT FALSE, -- per-school Team-uploaded Library/Textbooks toggle
  library_rate      NUMERIC(10,2) NOT NULL DEFAULT 0, -- shared library is free; column kept in case a paid tier returns later
  term_start_date   DATE,  -- basis for feeding/transport debt calculations
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Payment records — manual entries today, populated by the Paystack/MoMo
-- webhook once that's wired up (see src/routes/payments.js).
CREATE TABLE IF NOT EXISTS platform_payments (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  amount          NUMERIC(12,2) NOT NULL,
  currency        TEXT NOT NULL DEFAULT 'GHS',
  provider        TEXT NOT NULL DEFAULT 'manual', -- 'manual' | 'paystack' | 'momo'
  provider_ref    TEXT,                            -- external transaction id, for webhook idempotency
  status          TEXT NOT NULL DEFAULT 'success', -- 'success' | 'failed' | 'pending'
  paid_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  recorded_by     TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_platform_payments_provider_ref
  ON platform_payments(provider, provider_ref) WHERE provider_ref IS NOT NULL;

-- Platform super-admins (the old "hellosuper" role) — operate across ALL
-- schools, so they intentionally have no school_id.
CREATE TABLE IF NOT EXISTS platform_admins (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  username      TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  team_role     TEXT NOT NULL DEFAULT 'lead_engineer'
                CHECK (team_role IN ('lead_engineer', 'associate_engineer', 'finance', 'support')),
  email         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── TENANT-SCOPED TABLES (every one carries school_id) ─────────────────────

-- Staff accounts: school admin + teachers. (Old "helloadmin" role becomes
-- the first admin user created at signup for that school.)
CREATE TABLE IF NOT EXISTS users (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  role          TEXT NOT NULL CHECK (role IN ('admin', 'teacher')),
  name          TEXT NOT NULL,
  username      TEXT NOT NULL,
  email         TEXT,
  password_hash TEXT NOT NULL,
  class_name    TEXT,                          -- teachers may be tied to a class
  active        BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, username)
);

CREATE TABLE IF NOT EXISTS students (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_code  TEXT NOT NULL,                 -- e.g. old "ID" column
  name          TEXT NOT NULL,
  class_name    TEXT,
  dob           DATE,
  gender        TEXT,
  pin_hash      TEXT,                          -- student portal login PIN
  photo_url     TEXT,
  failed_pin_attempts  INTEGER NOT NULL DEFAULT 0,
  locked_until         TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, student_code)
);

CREATE TABLE IF NOT EXISTS parents (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  phone         TEXT NOT NULL,
  address       TEXT,
  pin_hash      TEXT NOT NULL,
  failed_pin_attempts  INTEGER NOT NULL DEFAULT 0,
  locked_until         TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, phone)
);

CREATE TABLE IF NOT EXISTS parent_students (
  parent_id     INTEGER NOT NULL REFERENCES parents(id) ON DELETE CASCADE,
  student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  PRIMARY KEY (parent_id, student_id)
);

CREATE TABLE IF NOT EXISTS fee_bills (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id        INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  term              TEXT NOT NULL,
  school_fees       NUMERIC(12,2) DEFAULT 0,
  feeding_fees      NUMERIC(12,2) DEFAULT 0,
  transport_fees    NUMERIC(12,2) DEFAULT 0,
  exam_fees         NUMERIC(12,2) DEFAULT 0,
  uniform_fees      NUMERIC(12,2) DEFAULT 0,
  toiletries        TEXT,
  other_fees        NUMERIC(12,2) DEFAULT 0,
  other_label       TEXT,
  previous_balance  NUMERIC(12,2) DEFAULT 0,
  total_due         NUMERIC(12,2) DEFAULT 0,
  payment_deadline  DATE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS fee_payments (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id      INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  term            TEXT,
  amount          NUMERIC(12,2) NOT NULL,
  method          TEXT,
  receipt_number  TEXT,  -- auto-generated at recording time, e.g. "RCP-000123"
  recorded_by     TEXT,
  paid_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS feeding_payments (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  pay_date      DATE NOT NULL,
  amount        NUMERIC(12,2) NOT NULL,
  recorded_by   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS transport_payments (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  pay_date      DATE NOT NULL,
  term          TEXT,  -- real term tagging, matching fee_payments/expenses, so income can be scoped per term
  amount        NUMERIC(12,2) NOT NULL,
  recorded_by   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS attendance_logs (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id    INTEGER REFERENCES students(id) ON DELETE CASCADE,
  staff_id      INTEGER REFERENCES users(id) ON DELETE CASCADE,
  staff_name    TEXT,      -- denormalized so staff-attendance works even for people not in `users`
  log_date      DATE NOT NULL,
  status        TEXT NOT NULL DEFAULT 'present',
  time_in       TIME,
  time_out      TIME,
  recorded_by   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (student_id IS NOT NULL OR staff_id IS NOT NULL OR staff_name IS NOT NULL)
);

CREATE TABLE IF NOT EXISTS expenses (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  expense_date  DATE NOT NULL DEFAULT CURRENT_DATE,
  item          TEXT NOT NULL,
  category      TEXT,
  amount        NUMERIC(12,2) NOT NULL,
  notes         TEXT,
  authorized_by TEXT,
  term          TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS announcements (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  title         TEXT NOT NULL,
  body          TEXT NOT NULL,
  audience      TEXT DEFAULT 'all',
  created_by    TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_log (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER REFERENCES schools(id) ON DELETE CASCADE,
  role          TEXT,
  actor         TEXT,
  function_name TEXT,
  summary       TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── ADDITIONAL MODULES ──────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS timetable (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_name  TEXT,
  day_of_week   TEXT,
  time_slot     TEXT,
  subject       TEXT,
  class_name    TEXT,
  room          TEXT,
  notes         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS lesson_notes (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_name  TEXT NOT NULL,
  class_name    TEXT,
  week_start    DATE,
  week_end      DATE,
  subjects      TEXT,
  topics        TEXT,
  methods       TEXT,
  assignments   TEXT,
  challenges    TEXT,
  next_week     TEXT,
  status        TEXT NOT NULL DEFAULT 'Pending',
  admin_remark  TEXT,
  marked_at     TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS course_outlines (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_name  TEXT NOT NULL,
  class_name    TEXT,
  outline_date  DATE,
  subject       TEXT,
  objectives    TEXT,
  activities    TEXT,
  assessment    TEXT,
  resources     TEXT,
  status        TEXT NOT NULL DEFAULT 'Pending',
  admin_remark  TEXT,
  reviewed_by   TEXT,
  reviewed_at   TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS school_events (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  event_date    DATE NOT NULL,
  event_name    TEXT NOT NULL,
  description   TEXT,
  category      TEXT,
  event_time    TEXT
);

CREATE TABLE IF NOT EXISTS teacher_roster (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  day_of_week     TEXT,
  specific_date   DATE,
  teacher_name    TEXT NOT NULL,
  duty_type       TEXT,
  notes           TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS staff_management (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  staff_code      TEXT NOT NULL,
  full_name       TEXT NOT NULL,
  role            TEXT,
  contact         TEXT,
  monthly_salary  NUMERIC(12,2),
  join_date       DATE,
  notes           TEXT,
  class_name      TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, staff_code)
);

CREATE TABLE IF NOT EXISTS welfare_contributions (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_name  TEXT NOT NULL,
  amount        NUMERIC(12,2) NOT NULL,
  contrib_date  DATE NOT NULL,
  purpose       TEXT,
  recorded_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS ges_assessments (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  term          TEXT NOT NULL,
  student_name  TEXT NOT NULL,
  subject       TEXT NOT NULL,
  class_score   NUMERIC(6,2),
  exam_score    NUMERIC(6,2),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS parent_messages (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  parent_name     TEXT NOT NULL,
  parent_phone    TEXT,
  child_name      TEXT,
  teacher_name    TEXT,
  subject         TEXT,
  message         TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'Pending',
  admin_response  TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS meeting_requests (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  parent_name       TEXT NOT NULL,
  parent_phone      TEXT,
  child_name        TEXT,
  teacher_name      TEXT,
  preferred_date    TEXT,
  preferred_time    TEXT,
  reason            TEXT,
  status            TEXT NOT NULL DEFAULT 'Pending',
  notes             TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── BATCH 2: remaining modules ──────────────────────────────────────────

CREATE TABLE IF NOT EXISTS feeding_rates (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  daily_rate    NUMERIC(10,2) DEFAULT 0,
  monthly_rate  NUMERIC(10,2) DEFAULT 0,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id)
);

CREATE TABLE IF NOT EXISTS feeding_monthly_config (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  month         INTEGER NOT NULL,
  year          INTEGER NOT NULL,
  school_days   INTEGER NOT NULL,
  monthly_total NUMERIC(10,2) DEFAULT 0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, month, year)
);

CREATE TABLE IF NOT EXISTS feeding_holidays (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  holiday_date  DATE NOT NULL,
  reason        TEXT
);

CREATE TABLE IF NOT EXISTS transport_rates (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  route         TEXT NOT NULL,
  amount        NUMERIC(10,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS library_books (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  title         TEXT NOT NULL,
  author        TEXT,
  category      TEXT,
  description   TEXT,
  doc_link      TEXT,
  icon          TEXT,
  has_quiz      BOOLEAN NOT NULL DEFAULT FALSE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS library_quiz_questions (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  book_id         INTEGER NOT NULL REFERENCES library_books(id) ON DELETE CASCADE,
  question        TEXT NOT NULL,
  option_a        TEXT, option_b TEXT, option_c TEXT, option_d TEXT,
  correct_answer  TEXT,
  points          INTEGER NOT NULL DEFAULT 5
);

CREATE TABLE IF NOT EXISTS quiz_results (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id    INTEGER REFERENCES students(id) ON DELETE CASCADE,
  book_id       INTEGER REFERENCES library_books(id) ON DELETE SET NULL,
  score         INTEGER NOT NULL DEFAULT 0,
  total         INTEGER NOT NULL DEFAULT 0,
  points        INTEGER NOT NULL DEFAULT 0,
  quiz_type     TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS inventory_items (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,
  category        TEXT,
  stock           INTEGER NOT NULL DEFAULT 0,
  reorder_level   INTEGER NOT NULL DEFAULT 5,  -- stock at or below this triggers a low-stock flag
  price           NUMERIC(10,2) DEFAULT 0,
  notes           TEXT
);

CREATE TABLE IF NOT EXISTS class_registry (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  class_name        TEXT NOT NULL,
  level             TEXT,
  assigned_teacher  TEXT,
  capacity          INTEGER,
  notes             TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS student_membership (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  full_name     TEXT NOT NULL,
  nickname      TEXT,
  gender        TEXT,
  dob           DATE,
  age           TEXT,
  siblings      TEXT,
  allergies     TEXT,
  parent_name   TEXT,
  parent_phone  TEXT,
  address       TEXT,
  pin           TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS teacher_queries (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_name    TEXT NOT NULL,
  query_date      DATE,
  reason          TEXT,
  action_taken    TEXT,
  status          TEXT NOT NULL DEFAULT 'Open',
  admin_remarks   TEXT,
  recorded_by     TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Adapted from the old "hellosuper approval" workflow: reviewed by the
-- school's own admin rather than a cross-school platform role, since each
-- school is now its own isolated tenant.
CREATE TABLE IF NOT EXISTS pending_approvals (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  approval_type   TEXT NOT NULL,
  submitted_by    TEXT,
  data_json       JSONB NOT NULL DEFAULT '{}',
  status          TEXT NOT NULL DEFAULT 'Pending',
  override_reason TEXT,
  reviewed_by     TEXT,
  reviewed_at     TIMESTAMPTZ,
  notes           TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS financial_projections (
  id                  SERIAL PRIMARY KEY,
  school_id           INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  term                TEXT NOT NULL,
  academic_year       TEXT,
  expected_fees       NUMERIC(12,2) DEFAULT 0,
  expected_salary     NUMERIC(12,2) DEFAULT 0,
  expected_feeding    NUMERIC(12,2) DEFAULT 0,
  expected_transport  NUMERIC(12,2) DEFAULT 0,
  expected_other      NUMERIC(12,2) DEFAULT 0,
  notes               TEXT,
  set_by              TEXT,
  set_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS bill_templates (
  id                  SERIAL PRIMARY KEY,
  school_id           INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  class_name          TEXT NOT NULL,
  term                TEXT NOT NULL,
  school_fees         NUMERIC(12,2) DEFAULT 0,
  feeding_fees        NUMERIC(12,2) DEFAULT 0,
  transport_fees      NUMERIC(12,2) DEFAULT 0,
  exam_fees           NUMERIC(12,2) DEFAULT 0,
  uniform_fees        NUMERIC(12,2) DEFAULT 0,
  toiletries_list     TEXT,
  other_fees          NUMERIC(12,2) DEFAULT 0,
  other_label         TEXT,
  payment_deadline    DATE,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS report_archive (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_name      TEXT NOT NULL,
  class_name        TEXT,
  term              TEXT NOT NULL,
  level             TEXT,
  html_content      TEXT,
  archived_by       TEXT,
  status            TEXT NOT NULL DEFAULT 'Pending Review',
  head_remarks      TEXT,
  rejection_reason  TEXT,
  reviewed_by       TEXT,
  reviewed_at       TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, student_name, term)
);

CREATE TABLE IF NOT EXISTS terms (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, name)
);

-- SECURITY: server-side logout. JWTs are normally stateless (valid until
-- they naturally expire), so this table lets a specific token be forcibly
-- invalidated early — checked on every authenticated request. Rows older
-- than the JWT expiry window are safe to prune periodically (a cron job
-- or the backup script can do `DELETE FROM revoked_tokens WHERE revoked_at < now() - interval '7 days'`).
CREATE TABLE IF NOT EXISTS revoked_tokens (
  jti           TEXT PRIMARY KEY,
  revoked_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Self-service password reset — a random token, valid for 30 minutes,
-- single-use (deleted once consumed).
CREATE TABLE IF NOT EXISTS password_reset_tokens (
  token         TEXT PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  user_id       INTEGER NOT NULL,
  expires_at    TIMESTAMPTZ NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Structured score drafts, built up before being rendered to HTML and
-- submitted to report_archive. Uses JSONB for per-subject scores rather
-- than rigid per-subject columns, since the subject list differs across
-- PRIMARY/JHS/NURSERY/KG levels.
CREATE TABLE IF NOT EXISTS student_report_drafts (
  id                  SERIAL PRIMARY KEY,
  school_id           INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id          INTEGER REFERENCES students(id) ON DELETE CASCADE,
  term                TEXT NOT NULL,
  academic_year       TEXT,
  class_name          TEXT,
  scores              JSONB NOT NULL DEFAULT '{}',      -- { "English": { "classScore": 30, "examScore": 55 }, ... }
  conduct             TEXT,
  attendance          TEXT,
  total_attendance    TEXT,
  competence          JSONB NOT NULL DEFAULT '{}',
  domains             JSONB NOT NULL DEFAULT '{}',
  kg_grades           JSONB NOT NULL DEFAULT '{}',
  teacher_remarks     TEXT,
  manager_remarks     TEXT,
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, student_id, term)
);

-- Configurable dropdown options for the report-card builder (teacher
-- remarks bank + co-curricular activity list). One row per school,
-- seeded with sensible defaults on first save.
CREATE TABLE IF NOT EXISTS report_options (
  id                      SERIAL PRIMARY KEY,
  school_id               INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_remarks_options JSONB NOT NULL DEFAULT '[]',
  cocurricular_options    JSONB NOT NULL DEFAULT '[]',
  UNIQUE (school_id)
);

-- One flexible, school-editable-list store, covering: staff roles, conduct
-- descriptors, and the Creche developmental checklist. All three are
-- school-specific phrasing/operational choices, not tied to any GES/NaCCA
-- standard — unlike subjects and grade bands, which stay Team-managed
-- (see school_extra_subjects below for the one thing schools CAN add
-- there). One row per (school, list_type); items is a flat JSON array,
-- except creche_checklist, where each entry is { category, items: [...] }.
CREATE TABLE IF NOT EXISTS school_config_lists (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  list_type     TEXT NOT NULL CHECK (list_type IN ('staff_roles', 'conduct_descriptors', 'creche_checklist')),
  items         JSONB NOT NULL DEFAULT '[]',
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, list_type)
);

-- Extra subjects a school adds ON TOP of the standard NaCCA/GES list —
-- deliberately additive-only (no way to remove/rename a standard
-- subject), so the official list stays intact while schools still get
-- real flexibility (e.g. a specific local language).
CREATE TABLE IF NOT EXISTS school_extra_subjects (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  level         TEXT NOT NULL,  -- 'Primary' | 'JHS'
  subject       TEXT NOT NULL,
  UNIQUE (school_id, level, subject)
);

-- Teacher observation/evaluation records, done by admin or head teacher —
-- distinct from teacher_queries (disciplinary/incident log).
CREATE TABLE IF NOT EXISTS teacher_observations (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_name      TEXT NOT NULL,
  observed_by       TEXT NOT NULL,
  observation_date  DATE NOT NULL DEFAULT CURRENT_DATE,
  class_name        TEXT,
  subject           TEXT,
  lesson_delivery_rating   TEXT,  -- 'Excellent' | 'Good' | 'Satisfactory' | 'Needs Improvement'
  classroom_mgmt_rating    TEXT,
  punctuality_rating       TEXT,
  strengths         TEXT,
  areas_to_improve  TEXT,
  overall_comments  TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Standing authorized pickup persons per student.
CREATE TABLE IF NOT EXISTS pickup_authorized_persons (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  person_name   TEXT NOT NULL,
  relationship  TEXT,
  is_primary    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One-off pickup notices submitted by a parent for a specific date
-- ("today only, my sister is picking up instead of me").
CREATE TABLE IF NOT EXISTS pickup_notices (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  pickup_date   DATE NOT NULL,
  person_name   TEXT NOT NULL,
  relationship  TEXT,
  submitted_by  TEXT,       -- parent's name
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- The actual pickup log — recorded by the school admin at pickup time.
CREATE TABLE IF NOT EXISTS pickup_log (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id        INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  picked_up_by      TEXT NOT NULL,
  relationship      TEXT,
  was_authorized    BOOLEAN NOT NULL DEFAULT FALSE, -- matched the standing list or a one-off notice
  matched_notice_id INTEGER REFERENCES pickup_notices(id) ON DELETE SET NULL,
  recorded_by       TEXT,   -- admin who logged it
  picked_up_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Which students a school has opted in to (billed) library access — the
-- school picks specific students, not a blanket whole-school switch, so
-- billing (library_rate x opted-in count) only ever covers students the
-- school actually signed up.
CREATE TABLE IF NOT EXISTS library_student_optins (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  opted_in_by   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (school_id, student_id)
);

-- Records each login attempt (success or failure) for staff/admin/platform
-- accounts, so an admin can see their own login history. Not built for
-- students/parents, matching the same "admins and teachers only" scope
-- used elsewhere for security features in this app.
CREATE TABLE IF NOT EXISTS login_history (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER REFERENCES schools(id) ON DELETE CASCADE, -- NULL for platform_admin logins
  user_id       INTEGER,     -- references users.id or platform_admins.id, not FK'd (different tables)
  username      TEXT NOT NULL,
  role          TEXT NOT NULL,
  success       BOOLEAN NOT NULL,
  ip_address    TEXT,
  attempted_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Real salary disbursement records — replaces the earlier "monthly_salary
-- figure as a proxy" approach with actual recorded payments, so financial
-- actuals and pay slips are both built on genuine transaction records.
CREATE TABLE IF NOT EXISTS salary_payments (
  id              SERIAL PRIMARY KEY,
  school_id       INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  staff_id        INTEGER REFERENCES staff_management(id) ON DELETE SET NULL,
  staff_name      TEXT NOT NULL,  -- denormalized so a payslip/history stays intact even if the staff record changes later
  role            TEXT,
  term            TEXT,
  pay_period      TEXT,           -- e.g. "August 2026"
  basic_salary    NUMERIC(12,2) NOT NULL DEFAULT 0,
  allowances      NUMERIC(12,2) NOT NULL DEFAULT 0,
  deductions      NUMERIC(12,2) NOT NULL DEFAULT 0,
  net_pay         NUMERIC(12,2) NOT NULL DEFAULT 0,
  method          TEXT,
  recorded_by     TEXT,
  paid_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Additive support for a teacher teaching multiple classes/subjects — the
-- original single users.class_name field stays as their PRIMARY class
-- (backward compatible with everything already built), this table adds
-- any FURTHER classes/subjects on top.
CREATE TABLE IF NOT EXISTS teacher_classes (
  id            SERIAL PRIMARY KEY,
  school_id     INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  class_name    TEXT NOT NULL,
  subject       TEXT,
  UNIQUE (school_id, user_id, class_name, subject)
);

-- Records BOTH successful and failed platform subscription payment
-- attempts (platform_payments already has a status column for this —
-- previously only ever written with status='success'; failures now get
-- logged too instead of just vanishing on error).

-- ─── Team-uploaded platform-level content: Textbooks (PDFs) and Creche learning
-- videos (YouTube links). Owned by no single school — visible to every
-- school's students, filtered by class level. Uploaded via the Team Portal.
-- Deliberately NOT in the tenant RLS list below: this is intentionally
-- shared, not school-isolated.
CREATE TABLE IF NOT EXISTS platform_textbooks (
  id            SERIAL PRIMARY KEY,
  title         TEXT NOT NULL,
  subject       TEXT,
  level         TEXT NOT NULL, -- standardized: Creche, Nursery 1/2, KG 1/2, Primary 1-6, JHS 1-3
  term          TEXT,
  file_path     TEXT NOT NULL, -- server-side storage path, served only to logged-in students
  file_size_kb  INTEGER,
  thumbnail_path TEXT,
  uploaded_by   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Team-uploaded story/reading books (distinct from Textbooks — fiction/
-- leisure reading rather than curriculum). Same shared, class-level-filtered,
-- streamed-PDF pattern as platform_textbooks. This is separate from the
-- older per-school `library_books` table, which schools still manage
-- themselves for their own quiz/points/leaderboard features.
CREATE TABLE IF NOT EXISTS platform_library_books (
  id            SERIAL PRIMARY KEY,
  title         TEXT NOT NULL,
  author        TEXT,
  category      TEXT,       -- Animals, Adventure, STEM, Arts, Nature, Funny, etc.
  description   TEXT,
  level         TEXT NOT NULL,
  file_path     TEXT NOT NULL,
  file_size_kb  INTEGER,
  icon          TEXT,       -- emoji/icon shown in the catalog grid
  uploaded_by   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS platform_creche_videos (
  id            SERIAL PRIMARY KEY,
  title         TEXT NOT NULL,
  category      TEXT,
  youtube_id    TEXT NOT NULL,  -- just the video ID, e.g. "dQw4w9WgXcQ"
  level         TEXT NOT NULL DEFAULT 'Creche',
  uploaded_by   TEXT,
  approved      BOOLEAN NOT NULL DEFAULT FALSE, -- Team must approve before it's visible — curation safeguard
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─── INDEXES ─────────────────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_users_school            ON users(school_id);
CREATE INDEX IF NOT EXISTS idx_students_school          ON students(school_id);
CREATE INDEX IF NOT EXISTS idx_parents_school           ON parents(school_id);
CREATE INDEX IF NOT EXISTS idx_fee_bills_school_student ON fee_bills(school_id, student_id);
CREATE INDEX IF NOT EXISTS idx_fee_payments_school_stu  ON fee_payments(school_id, student_id);
CREATE INDEX IF NOT EXISTS idx_attendance_school_date   ON attendance_logs(school_id, log_date);
CREATE INDEX IF NOT EXISTS idx_expenses_school          ON expenses(school_id);
CREATE INDEX IF NOT EXISTS idx_announcements_school     ON announcements(school_id);
CREATE INDEX IF NOT EXISTS idx_audit_school             ON audit_log(school_id);
CREATE INDEX IF NOT EXISTS idx_timetable_school         ON timetable(school_id);
CREATE INDEX IF NOT EXISTS idx_lesson_notes_school       ON lesson_notes(school_id);
CREATE INDEX IF NOT EXISTS idx_course_outlines_school    ON course_outlines(school_id);
CREATE INDEX IF NOT EXISTS idx_school_events_school       ON school_events(school_id);
CREATE INDEX IF NOT EXISTS idx_teacher_roster_school      ON teacher_roster(school_id);
CREATE INDEX IF NOT EXISTS idx_staff_mgmt_school          ON staff_management(school_id);
CREATE INDEX IF NOT EXISTS idx_welfare_school             ON welfare_contributions(school_id);
CREATE INDEX IF NOT EXISTS idx_ges_school                 ON ges_assessments(school_id);
CREATE INDEX IF NOT EXISTS idx_parent_msgs_school          ON parent_messages(school_id);
CREATE INDEX IF NOT EXISTS idx_meeting_reqs_school         ON meeting_requests(school_id);
CREATE INDEX IF NOT EXISTS idx_feeding_payments_school      ON feeding_payments(school_id);
CREATE INDEX IF NOT EXISTS idx_transport_payments_school    ON transport_payments(school_id);
CREATE INDEX IF NOT EXISTS idx_library_books_school         ON library_books(school_id);
CREATE INDEX IF NOT EXISTS idx_library_quiz_school          ON library_quiz_questions(school_id);
CREATE INDEX IF NOT EXISTS idx_quiz_results_school          ON quiz_results(school_id);
CREATE INDEX IF NOT EXISTS idx_inventory_school             ON inventory_items(school_id);
CREATE INDEX IF NOT EXISTS idx_class_registry_school        ON class_registry(school_id);
CREATE INDEX IF NOT EXISTS idx_membership_school            ON student_membership(school_id);
CREATE INDEX IF NOT EXISTS idx_teacher_queries_school       ON teacher_queries(school_id);
CREATE INDEX IF NOT EXISTS idx_pending_approvals_school     ON pending_approvals(school_id);
CREATE INDEX IF NOT EXISTS idx_financial_proj_school        ON financial_projections(school_id);
CREATE INDEX IF NOT EXISTS idx_bill_templates_school        ON bill_templates(school_id);
CREATE INDEX IF NOT EXISTS idx_report_archive_school        ON report_archive(school_id);
CREATE INDEX IF NOT EXISTS idx_terms_school                 ON terms(school_id);
CREATE INDEX IF NOT EXISTS idx_report_drafts_school          ON student_report_drafts(school_id);
CREATE INDEX IF NOT EXISTS idx_report_options_school         ON report_options(school_id);
CREATE INDEX IF NOT EXISTS idx_teacher_obs_school             ON teacher_observations(school_id);
CREATE INDEX IF NOT EXISTS idx_pickup_persons_school          ON pickup_authorized_persons(school_id, student_id);
CREATE INDEX IF NOT EXISTS idx_pickup_notices_school          ON pickup_notices(school_id, student_id, pickup_date);
CREATE INDEX IF NOT EXISTS idx_pickup_log_school              ON pickup_log(school_id, student_id);
CREATE INDEX IF NOT EXISTS idx_library_optins_school          ON library_student_optins(school_id);
CREATE INDEX IF NOT EXISTS idx_login_history_school           ON login_history(school_id, user_id, attempted_at);
CREATE INDEX IF NOT EXISTS idx_login_history_username         ON login_history(username, attempted_at);
CREATE INDEX IF NOT EXISTS idx_salary_payments_school         ON salary_payments(school_id, staff_id, term);
CREATE INDEX IF NOT EXISTS idx_teacher_classes_school          ON teacher_classes(school_id, user_id);
CREATE INDEX IF NOT EXISTS idx_school_config_lists_school      ON school_config_lists(school_id, list_type);
CREATE INDEX IF NOT EXISTS idx_school_extra_subjects_school    ON school_extra_subjects(school_id, level);
CREATE INDEX IF NOT EXISTS idx_textbooks_level                ON platform_textbooks(level);
CREATE INDEX IF NOT EXISTS idx_platform_library_level          ON platform_library_books(level);
CREATE INDEX IF NOT EXISTS idx_creche_videos_level             ON platform_creche_videos(level, approved);

-- ─── ROW-LEVEL SECURITY (backstop — filters every query by tenant) ─────────
DO $$
DECLARE
  t TEXT;
BEGIN
  FOR t IN SELECT unnest(ARRAY[
    'users','students','parents','parent_students','fee_bills','fee_payments',
    'feeding_payments','transport_payments','attendance_logs','expenses',
    'announcements','timetable','school_events',
    'lesson_notes','course_outlines','teacher_roster','staff_management',
    'welfare_contributions','ges_assessments','parent_messages','meeting_requests',
    'feeding_rates','feeding_monthly_config','feeding_holidays','transport_rates',
    'library_books','library_quiz_questions','quiz_results','inventory_items',
    'class_registry','student_membership','teacher_queries','pending_approvals',
    'financial_projections','bill_templates','report_archive','terms',
    'student_report_drafts','report_options',
    'teacher_observations','pickup_authorized_persons','pickup_notices','pickup_log',
    'library_student_optins', 'salary_payments', 'teacher_classes',
    'school_config_lists', 'school_extra_subjects'
  ])
  LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY;', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY;', t); -- applies even to the table owner
    EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I;', t);
    EXECUTE format(
      'CREATE POLICY tenant_isolation ON %I
         USING (school_id = NULLIF(current_setting(''app.current_school_id'', true), '''')::int);',
      t
    );
  END LOOP;
END $$;

-- audit_log intentionally has NO per-school RLS: the only reader is the
-- platform-level cross-school audit view (Team Portal), which needs to see
-- every school's activity. There is no per-school "view your own audit log"
-- endpoint that RLS would need to protect. If a previous schema run enabled
-- RLS here, this reverts it.
ALTER TABLE audit_log DISABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON audit_log;