BEGIN;

-- Cross-browser menu settings and login/attendance sessions.
CREATE TABLE IF NOT EXISTS student_menu_controls (
  student_id INTEGER PRIMARY KEY REFERENCES students(id) ON DELETE CASCADE,
  controls JSONB NOT NULL DEFAULT '{}'::jsonb,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_student_menu_controls_updated_at
  ON student_menu_controls(updated_at DESC);

CREATE TABLE IF NOT EXISTS student_activity_sessions (
  id BIGSERIAL PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  session_key TEXT NOT NULL,
  activity_type TEXT NOT NULL DEFAULT 'login',
  started_at TIMESTAMPTZ NOT NULL,
  last_activity_at TIMESTAMPTZ NOT NULL,
  ended_at TIMESTAMPTZ,
  subject TEXT NOT NULL DEFAULT 'General Learning',
  note TEXT NOT NULL DEFAULT 'Student login session',
  assignment_id TEXT,
  assignment_title TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (student_id, session_key)
);

CREATE INDEX IF NOT EXISTS idx_student_activity_sessions_student_time
  ON student_activity_sessions(student_id, started_at DESC);

-- Current Grade 6 curriculum is generated by the frontend. This table is the
-- permanent source shared by the signed-in student and parent browsers.
CREATE TABLE IF NOT EXISTS curriculum_submissions (
  id BIGSERIAL PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  client_submission_key TEXT NOT NULL,
  assignment_key TEXT NOT NULL,
  assignment_title TEXT NOT NULL,
  course_name TEXT NOT NULL,
  subject TEXT NOT NULL,
  unit_title TEXT NOT NULL DEFAULT '',
  lesson_title TEXT NOT NULL DEFAULT '',
  assignment_type TEXT NOT NULL DEFAULT 'classwork',
  weight_category TEXT NOT NULL DEFAULT 'classwork',
  status TEXT NOT NULL DEFAULT 'completed',
  percentage NUMERIC(6,2),
  letter_grade TEXT NOT NULL DEFAULT '',
  points_earned NUMERIC(10,2),
  points_possible NUMERIC(10,2),
  point_value NUMERIC(10,2),
  counts_toward_grade BOOLEAN NOT NULL DEFAULT TRUE,
  needs_review BOOLEAN NOT NULL DEFAULT FALSE,
  time_spent_seconds INTEGER NOT NULL DEFAULT 0,
  language_key TEXT,
  elective_kind TEXT,
  details JSONB NOT NULL DEFAULT '{}'::jsonb,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  graded_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (student_id, client_submission_key)
);

CREATE INDEX IF NOT EXISTS idx_curriculum_submissions_student_time
  ON curriculum_submissions(student_id, submitted_at DESC);

CREATE INDEX IF NOT EXISTS idx_curriculum_submissions_student_assignment
  ON curriculum_submissions(student_id, assignment_key, submitted_at DESC);

CREATE TABLE IF NOT EXISTS student_curriculum_settings (
  student_id INTEGER PRIMARY KEY REFERENCES students(id) ON DELETE CASCADE,
  foreign_language_key TEXT NOT NULL DEFAULT 'spanish'
    CHECK (foreign_language_key IN ('spanish', 'french', 'portuguese')),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Program announcements must be shared between the parent browser and every
-- signed-in student browser. They are not student submissions.
CREATE TABLE IF NOT EXISTS program_announcements (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  audience TEXT NOT NULL DEFAULT 'All Students',
  status TEXT NOT NULL DEFAULT 'Draft' CHECK (status IN ('Draft', 'Published')),
  display_date TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO program_announcements (id, title, audience, status, display_date, body)
VALUES
  ('welcome', 'Welcome to JNM Home Education Program', 'All Students', 'Published', 'Today', 'Keep checking your dashboard for assignments, grades, calendar updates, and teacher notes.'),
  ('hours', 'Remember to complete your daily learning time', 'All Students', 'Draft', 'This Week', 'Daily hours are recorded from logins, learning sessions, and submitted assignments.'),
  ('calendar', 'School calendar is ready', 'All Students', 'Published', 'School Year', 'Holidays, breaks, and school planning dates appear inside the calendar.')
ON CONFLICT (id) DO NOTHING;

COMMIT;