-- Rich, GES-style lesson plan format, matching the real official lesson
-- plan structure (Week/Subject/Class/Class size/Week ending, Strand,
-- Sub-strand, Indicator code, Content standard code, Performance
-- indicator, Core competencies, Key words, TLRs, Ref), with a per-day
-- child table for the Phase 1/2/3 (Starter/Main/Plenary) breakdown —
-- since one plan can cover several different days (e.g. Monday AND
-- Thursday), each with its own date/period/lesson-number/phases.
--
-- Kept SEPARATE from the existing simpler `lesson_notes` table rather
-- than replacing it — that one still works for a quick weekly summary;
-- this is the fuller, official-format option alongside it.
CREATE TABLE IF NOT EXISTS lesson_plans (
  id                    SERIAL PRIMARY KEY,
  school_id             INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  teacher_name          TEXT NOT NULL,
  week_label            TEXT,       -- e.g. "Week 5"
  subject               TEXT,
  class_name            TEXT,
  class_size            INTEGER,
  week_ending           DATE,
  strand                TEXT,
  sub_strand            TEXT,
  indicator_code        TEXT,
  content_standard_code TEXT,
  performance_indicator TEXT,
  core_competencies     TEXT,
  key_words             TEXT,
  tlrs                  TEXT,       -- Teaching & Learning Resources
  reference             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 lesson_plan_days (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
  lesson_plan_id     INTEGER NOT NULL REFERENCES lesson_plans(id) ON DELETE CASCADE,
  day_name          TEXT,       -- e.g. "Monday"
  day_date          DATE,
  period            TEXT,       -- e.g. "Fourth"
  lesson_number     TEXT,       -- e.g. "7 of 10"
  starter_phase     TEXT,       -- Phase 1
  main_phase        TEXT,       -- Phase 2
  plenary_phase     TEXT        -- Phase 3
);

CREATE INDEX IF NOT EXISTS idx_lesson_plans_school ON lesson_plans(school_id, teacher_name);
CREATE INDEX IF NOT EXISTS idx_lesson_plan_days_plan ON lesson_plan_days(lesson_plan_id);

-- RLS for these two new tenant tables, matching the same pattern used
-- everywhere else in this app (fail-closed if the tenant context isn't
-- set, NULLIF-guarded against the empty-string-after-transaction-commit
-- quirk we found and fixed earlier).
ALTER TABLE lesson_plans FORCE ROW LEVEL SECURITY;
ALTER TABLE lesson_plans ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON lesson_plans;
CREATE POLICY tenant_isolation ON lesson_plans
  USING (school_id = NULLIF(current_setting('app.current_school_id', true), '')::int);

ALTER TABLE lesson_plan_days FORCE ROW LEVEL SECURITY;
ALTER TABLE lesson_plan_days ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON lesson_plan_days;
CREATE POLICY tenant_isolation ON lesson_plan_days
  USING (school_id = NULLIF(current_setting('app.current_school_id', true), '')::int);