-- eduKateSG.IC.DataSchema.SQL.v1.0
CREATE TABLE learner (
  learner_id TEXT PRIMARY KEY,
  name_alias TEXT NOT NULL,
  dob DATE,
  age INTEGER NOT NULL,
  stage TEXT CHECK(stage IN ('A1','A2','A3','A4','A5','A6','A7')),
  cohort_id TEXT,
  start_date DATE,
  active INTEGER NOT NULL DEFAULT 1  -- 1=true, 0=false
);
CREATE TABLE weekly_state (
  learner_id TEXT NOT NULL,
  week_start DATE NOT NULL,
  depth_d REAL CHECK(depth_d BETWEEN 0 AND 1),
  stability_s REAL CHECK(stability_s BETWEEN 0 AND 1),
  transfer_t REAL CHECK(transfer_t BETWEEN 0 AND 1),
  verification_v REAL CHECK(verification_v BETWEEN 0 AND 1),
  coordination_c REAL CHECK(coordination_c BETWEEN 0 AND 1),
  stress_signal TEXT CHECK(stress_signal IN ('Low','Med','High')),
  timed_stability_index REAL CHECK(timed_stability_index BETWEEN 0 AND 1),
  transfer_index REAL CHECK(transfer_index BETWEEN 0 AND 1),
  verification_log_count INTEGER DEFAULT 0,
  phase TEXT CHECK(phase IN ('P0','P1','P2','P3')),
  notes_1sentence TEXT,
  PRIMARY KEY (learner_id, week_start),
  FOREIGN KEY (learner_id) REFERENCES learner(learner_id)
);
CREATE TABLE drift_event (
  drift_id TEXT PRIMARY KEY,
  learner_id TEXT NOT NULL,
  event_date DATE NOT NULL,
  drift_type TEXT CHECK(drift_type IN (
    'FoundationBreak','StabilityDrop','TransferFail','VerificationDrop','StressRise'
  )),
  severity TEXT CHECK(severity IN ('Low','Med','High')),
  error_cluster TEXT,
  classified_by TEXT, -- staff_id
  action_triggered TEXT CHECK(action_triggered IN (
    'None','RepairLoop','StopLoss','ParentAction','OracleReview'
  )) DEFAULT 'None',
  FOREIGN KEY (learner_id) REFERENCES learner(learner_id)
);
CREATE TABLE intervention (
  intervention_id TEXT PRIMARY KEY,
  learner_id TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  intervention_type TEXT CHECK(intervention_type IN (
    'RepairLoop','TransferProgram','VerificationProgram','StressStabilityProgram'
  )),
  u_r REAL CHECK(u_r BETWEEN 0 AND 1),
  u_l REAL CHECK(u_l BETWEEN 0 AND 1),
  u_x REAL CHECK(u_x BETWEEN 0 AND 1),
  u_v REAL CHECK(u_v BETWEEN 0 AND 1),
  u_a REAL CHECK(u_a BETWEEN 0 AND 1),
  owner_staff_id TEXT,
  outcome TEXT CHECK(outcome IN ('Improved','NoChange','Worse','Pending')) DEFAULT 'Pending',
  failure_trace TEXT,
  FOREIGN KEY (learner_id) REFERENCES learner(learner_id)
);
CREATE TABLE family_buffer (
  learner_id TEXT NOT NULL,
  week_start DATE NOT NULL,
  routine_stability_b1 REAL CHECK(routine_stability_b1 BETWEEN 0 AND 1),
  sleep_signal TEXT CHECK(sleep_signal IN ('Good','OK','Poor')),
  device_load TEXT CHECK(device_load IN ('Low','Med','High')),
  conflict_signal TEXT CHECK(conflict_signal IN ('Low','Med','High')),
  PRIMARY KEY (learner_id, week_start),
  FOREIGN KEY (learner_id) REFERENCES learner(learner_id)
);
CREATE TABLE staff (
  staff_id TEXT PRIMARY KEY,
  name_alias TEXT NOT NULL,
  role TEXT CHECK(role IN ('Operator','Oracle','Visionary','Architect')),
  certification_level TEXT CHECK(certification_level IN ('None','Operator','Oracle','Visionary','Architect')) DEFAULT 'None',
  active INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE audit (
  audit_id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  audit_date DATE NOT NULL,
  audited_by TEXT NOT NULL, -- staff_id
  record_completeness REAL CHECK(record_completeness BETWEEN 0 AND 1),
  verification_compliance REAL CHECK(verification_compliance BETWEEN 0 AND 1),
  transfer_density REAL CHECK(transfer_density BETWEEN 0 AND 1),
  collapse_rate REAL CHECK(collapse_rate BETWEEN 0 AND 1),
  p3_fraction REAL CHECK(p3_fraction BETWEEN 0 AND 1),
  flags TEXT,
  actions_next TEXT,
  FOREIGN KEY (audited_by) REFERENCES staff(staff_id)
);
-- Helpful indexes
CREATE INDEX idx_weekly_state_week ON weekly_state(week_start);
CREATE INDEX idx_drift_event_date ON drift_event(event_date);
CREATE INDEX idx_intervention_dates ON intervention(start_date, end_date);

OperatorView:
  TodayList:
    - learners with StressSignal=High
    - learners with Phase=P0/P1
    - learners with VerificationLogCount=0
  ThisWeekTargets:
    - 2 timed sessions scheduled
    - 1 transfer task scheduled
    - verification log created
  PerLearnerCard:
    - D,S,T,V,C
    - Phase
    - Top error_cluster
    - Current intervention + next action

OracleView:
  CompliancePanel:
    - VerificationCompliance (avg V)
    - RecordCompleteness
    - DriftListBacklog (count unprocessed)
  RiskPanel:
    - cohorts with CollapseRate rising
    - sites with VerificationCompliance dropping
  CapstoneValidationQueue:
    - items missing edge-case tests
    - items missing failure trace

VisionaryView:
  GrowthPanel:
    - P3Fraction trend
    - TransferDensity trend
    - CoordinationStability trend
  ModulePerformance:
    - which transfer tasks improve T fastest
    - which projects increase C without raising CollapseRate
  InnovationQueue:
    - proposals awaiting Oracle validation

ArchitectView:
  SystemEnvelope:
    - StabilityInequality status (Ġ vs Ḋ)
    - SymmetryBudget Δ_allowed vs Δ_actual
  PilotLab:
    - router changes under test
    - rollback readiness
  City/National Layer:
    - cross-site differences
    - failure archive patterns