-- ================================================================
-- JNM Home Education Program — Phase 2 Migration
-- Kindergarten skill tracking + time logs + lesson media columns
-- ================================================================
-- Run:  psql -U homeschool_user -d jnm_homeschool_db -f migration-phase2.sql
--
-- SAFE / NON-DESTRUCTIVE:
--   - Creates two NEW tables (skill_progress, time_logs)
--   - Adds NEW columns to lessons (IF NOT EXISTS)
--   - Does NOT alter or delete any existing rows
--   - Re-runnable (guards on every statement)
--
-- Existing data impact: NONE. Grades 1–12 and all current tables untouched.
-- ================================================================

-- ----------------------------------------------------------------
-- 1. LESSON MEDIA / INTERACTIVITY COLUMNS
-- ----------------------------------------------------------------
-- Used mainly by Kindergarten (and available to all grades later).
-- image_url / audio_url / video_url already may exist for video_url;
-- we add the rest safely.
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS image_url TEXT;
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS audio_url TEXT;
-- video_url already exists from base schema; ensure it's present anyway:
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS video_url TEXT;
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS lesson_type TEXT;        -- e.g. 'read_along','letter','counting','shapes','activity'
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS interactive_type TEXT;   -- e.g. 'flip_card','tap_select','match','read_along'
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS activity_data JSONB;     -- structured interactive content (cards, choices, story words)
ALTER TABLE lessons ADD COLUMN IF NOT EXISTS skill_name TEXT;         -- the K skill this lesson develops

-- ----------------------------------------------------------------
-- 2. SKILL PROGRESS TABLE (Kindergarten skill-based assessment)
-- ----------------------------------------------------------------
-- Kindergarten is NOT graded by percentage. Each lesson/activity maps
-- to a skill whose status is tracked here.
--
-- status values: 'not_started','introduced','practicing','recognized',
--                'needs_more_practice','mastered','completed'
CREATE TABLE IF NOT EXISTS skill_progress (
  id SERIAL PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id INTEGER REFERENCES courses(id) ON DELETE SET NULL,
  lesson_id INTEGER REFERENCES lessons(id) ON DELETE SET NULL,
  skill_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'introduced',
  notes TEXT,
  activity_completed BOOLEAN DEFAULT false,
  time_spent_seconds INTEGER DEFAULT 0,
  completed_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- One progress row per (student, lesson, skill)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'skill_progress_student_lesson_skill_key'
  ) THEN
    ALTER TABLE skill_progress
      ADD CONSTRAINT skill_progress_student_lesson_skill_key
      UNIQUE (student_id, lesson_id, skill_name);
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_skill_progress_student ON skill_progress(student_id);
CREATE INDEX IF NOT EXISTS idx_skill_progress_course ON skill_progress(course_id);

-- ----------------------------------------------------------------
-- 3. TIME LOGS TABLE (assignment-level time tracking foundation)
-- ----------------------------------------------------------------
-- Tracks time per student/course/lesson/assignment. Daily Hours can be
-- aggregated from here later. Nullable foreign keys so it can log time
-- at whatever granularity is available.
CREATE TABLE IF NOT EXISTS time_logs (
  id SERIAL PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id INTEGER REFERENCES courses(id) ON DELETE SET NULL,
  lesson_id INTEGER REFERENCES lessons(id) ON DELETE SET NULL,
  assignment_id INTEGER REFERENCES assignments(id) ON DELETE SET NULL,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  active_seconds INTEGER DEFAULT 0,
  log_date DATE DEFAULT CURRENT_DATE,
  status TEXT DEFAULT 'completed',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_time_logs_student ON time_logs(student_id);
CREATE INDEX IF NOT EXISTS idx_time_logs_student_date ON time_logs(student_id, log_date);
CREATE INDEX IF NOT EXISTS idx_time_logs_assignment ON time_logs(assignment_id);

-- ----------------------------------------------------------------
-- 4. SUBMISSIONS: time tracking + status support (safe additions)
-- ----------------------------------------------------------------
-- Support "In Progress" resumable classwork and time-spent display.
-- 'status' already exists on submissions; we just ensure helper columns.
ALTER TABLE submissions ADD COLUMN IF NOT EXISTS time_spent_seconds INTEGER DEFAULT 0;
ALTER TABLE submissions ADD COLUMN IF NOT EXISTS started_at TIMESTAMP;
ALTER TABLE submissions ADD COLUMN IF NOT EXISTS is_retake BOOLEAN DEFAULT FALSE;
ALTER TABLE submissions ADD COLUMN IF NOT EXISTS retake_number INTEGER DEFAULT 0;

-- ----------------------------------------------------------------
-- 5. SCHOOL CALENDAR EVENTS (2026–2027) — seed if missing
-- ----------------------------------------------------------------
-- The frontend previously used hardcoded demo events. Seed real rows so
-- the live calendar route returns data. Guarded so it only inserts once.
INSERT INTO calendar_events (event_name, event_date, event_type, is_school_day, school_year)
SELECT v.event_name, v.event_date::date, v.event_type, v.is_school_day, '2026-2027'
FROM (VALUES
  ('First Day of School', '2026-08-24', 'school_event', true),
  ('Labor Day', '2026-09-07', 'holiday', false),
  ('Fall Break', '2026-10-12', 'break', false),
  ('Veterans Day', '2026-11-11', 'holiday', false),
  ('Thanksgiving Break', '2026-11-26', 'break', false),
  ('Thanksgiving Break Day 2', '2026-11-27', 'break', false),
  ('Winter Break Begins', '2026-12-21', 'break', false),
  ('Winter Break Ends', '2027-01-01', 'break', false),
  ('Classes Resume', '2027-01-05', 'school_event', true),
  ('MLK Jr. Day', '2027-01-18', 'holiday', false),
  ('Presidents Day', '2027-02-15', 'holiday', false),
  ('Spring Break', '2027-03-29', 'break', false),
  ('Memorial Day', '2027-05-31', 'holiday', false),
  ('Last Day of School', '2027-06-11', 'school_event', true)
) AS v(event_name, event_date, event_type, is_school_day)
WHERE NOT EXISTS (
  SELECT 1 FROM calendar_events ce WHERE ce.school_year = '2026-2027'
);

-- ----------------------------------------------------------------
-- DONE — verification queries
-- ----------------------------------------------------------------
-- SELECT table_name FROM information_schema.tables WHERE table_name IN ('skill_progress','time_logs');
-- SELECT column_name FROM information_schema.columns WHERE table_name='lessons' AND column_name IN ('image_url','audio_url','lesson_type','interactive_type','activity_data','skill_name');
-- SELECT event_name, event_date FROM calendar_events WHERE school_year='2026-2027' ORDER BY event_date;