-- ============================================================
-- 강상식의 국어세상 — PostgreSQL 스키마
-- 학생 활동 백로그를 단일 DB로 통합 관리
-- ============================================================

CREATE TYPE student_status AS ENUM ('재원', '휴원', '퇴원');
CREATE TYPE teacher_role   AS ENUM ('강사', '조교');
CREATE TYPE word_category  AS ENUM ('기본어휘', '사자성어', '고전어휘');
CREATE TYPE recitation_kind AS ENUM ('낭송', '낭송후암송');
CREATE TYPE image_pos      AS ENUM ('상단', '중간', '하단');
CREATE TYPE question_area  AS ENUM ('언어와 매체', '화법과 작문', '문학', '독서');
CREATE TYPE actor_type     AS ENUM ('학생', '학부모', '강사', '시스템');
CREATE TYPE notify_type    AS ENUM ('제출완료', '72h', '48h', '24h', '결과안내');
CREATE TYPE notify_channel AS ENUM ('sms', 'kakao');
CREATE TYPE notify_status  AS ENUM ('예약', '발송', '실패');

-- ── ① 회원 · 학급 ──────────────────────────────────────────

CREATE TABLE academies (
  id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(60) NOT NULL
);

CREATE TABLE classes (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  academy_id uuid NOT NULL REFERENCES academies(id),
  name       varchar(60) NOT NULL,
  days       varchar(20) NOT NULL,          -- 예: '월,목'
  start_time time NOT NULL,
  end_time   time NOT NULL
);

CREATE TABLE students (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name         varchar(30) NOT NULL,
  nickname     varchar(30) UNIQUE,           -- 회차 랭킹 노출용
  phone        varchar(20) UNIQUE NOT NULL,
  parent_phone varchar(20) NOT NULL,
  student_sms_opt_in boolean NOT NULL DEFAULT false, -- 학생 본인 문자·카카오톡 수신 동의
  parent_sms_opt_in  boolean NOT NULL DEFAULT false, -- 학부모 문자·카카오톡 수신 동의
  consent_at   timestamptz,                    -- 수신 동의 일시 (광고성 정보 규정 대비 기록)
  avatar_url   text,
  status       student_status NOT NULL DEFAULT '재원',
  created_at   timestamptz NOT NULL DEFAULT now()
);
-- 로그인: (name, phone, right(parent_phone, 4)) 일치 시 세션 발급
CREATE INDEX idx_students_login ON students (name, phone);

CREATE TABLE teachers (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name              varchar(30) NOT NULL,
  role              teacher_role NOT NULL DEFAULT '강사',
  phone             varchar(20),
  can_view_contacts boolean NOT NULL DEFAULT true -- 조교는 false: 연락처 마스킹
);

CREATE TABLE enrollments (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id  uuid NOT NULL REFERENCES students(id),
  class_id    uuid NOT NULL REFERENCES classes(id),
  status      student_status NOT NULL DEFAULT '재원',
  enrolled_at date NOT NULL DEFAULT current_date,
  UNIQUE (student_id, class_id)
);

-- ── ② 어휘집 · 어휘 퀴즈 ────────────────────────────────────

CREATE TABLE wordbooks (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title       varchar(100) NOT NULL,
  file_url    text,
  uploaded_by uuid REFERENCES teachers(id),
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE words (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  wordbook_id uuid NOT NULL REFERENCES wordbooks(id),
  category    word_category NOT NULL,
  headword    text NOT NULL,                -- 옛한글(첫가끝 자모) 그대로 저장
  hanja       text,                          -- 사자성어 한자 병기
  meaning     text NOT NULL,
  example     text
);
CREATE INDEX idx_words_category ON words (wordbook_id, category);

CREATE TABLE quiz_configs (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  daily_count smallint NOT NULL DEFAULT 10,
  ratios      jsonb NOT NULL,               -- {"기본어휘":50,"사자성어":30,"고전어휘":20}
  updated_by  uuid REFERENCES teachers(id),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE quiz_config_targets (
  config_id uuid NOT NULL REFERENCES quiz_configs(id) ON DELETE CASCADE,
  class_id  uuid NOT NULL REFERENCES classes(id),
  PRIMARY KEY (config_id, class_id)
);

CREATE TABLE quizzes (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  round_no  int NOT NULL,
  quiz_date date NOT NULL,
  config_id uuid REFERENCES quiz_configs(id),
  UNIQUE (round_no)
);

CREATE TABLE quiz_questions (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quiz_id    uuid NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
  word_id    uuid NOT NULL REFERENCES words(id),
  position   smallint NOT NULL,
  choices    jsonb NOT NULL,                -- 5개 보기 단어
  answer_pos smallint NOT NULL CHECK (answer_pos BETWEEN 0 AND 4)
);

CREATE TABLE quiz_attempts (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quiz_id      uuid NOT NULL REFERENCES quizzes(id),
  student_id   uuid NOT NULL REFERENCES students(id),
  score        smallint NOT NULL DEFAULT 0,
  submitted_at timestamptz NOT NULL DEFAULT now(),
  submit_order smallint,                     -- 제출 순서 기준 등수 계산용
  UNIQUE (quiz_id, student_id)
);

CREATE TABLE quiz_answers (
  attempt_id  uuid NOT NULL REFERENCES quiz_attempts(id) ON DELETE CASCADE,
  question_id uuid NOT NULL REFERENCES quiz_questions(id),
  picked      smallint NOT NULL,
  is_correct  boolean NOT NULL,
  PRIMARY KEY (attempt_id, question_id)
);

-- ── ③ 암송 과제 ────────────────────────────────────────────

CREATE TABLE recitation_tasks (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title      varchar(100) NOT NULL,
  kind       recitation_kind NOT NULL,
  passage    text NOT NULL,
  commentary text,                           -- 제시문 해설 (학생 화면에 함께 표시)
  image_url  text,
  image_pos  image_pos,
  read_count smallint NOT NULL DEFAULT 1,    -- 낭송 횟수
  step_count smallint,                        -- 낭송후암송의 암송 단계 수
  due_at     timestamptz NOT NULL,
  created_by uuid REFERENCES teachers(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE recitation_steps (
  task_id      uuid NOT NULL REFERENCES recitation_tasks(id) ON DELETE CASCADE,
  step_no      smallint NOT NULL,
  word_indices jsonb NOT NULL,               -- 이 단계에서 가릴 단어 위치 [1,8]
  points       smallint NOT NULL,            -- 단계 상승 시 배점 상승 (합계 100)
  PRIMARY KEY (task_id, step_no)
);

CREATE TABLE recitation_task_targets (
  task_id  uuid NOT NULL REFERENCES recitation_tasks(id) ON DELETE CASCADE,
  class_id uuid NOT NULL REFERENCES classes(id),
  PRIMARY KEY (task_id, class_id)
);

CREATE TABLE recitation_attempts (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id       uuid NOT NULL REFERENCES recitation_tasks(id),
  student_id    uuid NOT NULL REFERENCES students(id),
  stage_results jsonb NOT NULL,              -- [{stage:1, accuracy:0.96, earned:16}, …]
  total_score   smallint NOT NULL,           -- Σ(배점 × 정확도)
  submitted_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (task_id, student_id)
);

-- ── ④ 주간 과제 ────────────────────────────────────────────

CREATE TABLE weekly_tasks (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title             varchar(100) NOT NULL,
  round_no          int NOT NULL UNIQUE,
  given_at          timestamptz NOT NULL,    -- 이때 학생 페이지에 노출
  due_at            timestamptz NOT NULL,
  question_file_url text,
  solution_file_url text,                    -- 제출 후에만 다운로드 허용
  created_by        uuid REFERENCES teachers(id)
);

CREATE TABLE weekly_task_targets (
  task_id  uuid NOT NULL REFERENCES weekly_tasks(id) ON DELETE CASCADE,
  class_id uuid NOT NULL REFERENCES classes(id),
  PRIMARY KEY (task_id, class_id)
);

CREATE TABLE weekly_questions (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id      uuid NOT NULL REFERENCES weekly_tasks(id) ON DELETE CASCADE,
  no           smallint NOT NULL,
  points       smallint NOT NULL DEFAULT 2,
  area         question_area NOT NULL,
  subject      varchar(20) NOT NULL,         -- 문법/화작/현대 문학/고전 문학/갈래 복합/인문/사회/과학기술/예술
  genre        varchar(20) NOT NULL,         -- 현대시가/현대소설/극·수필/고전시가/고전소설/갈래 복합/…
  answers      jsonb NOT NULL,               -- 복수 정답 가능: [2] 또는 [2,4]
  correct_rate numeric(5,2),                 -- 제출 누적 시 갱신 (정답률)
  UNIQUE (task_id, no)
);

CREATE TABLE weekly_submissions (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id       uuid NOT NULL REFERENCES weekly_tasks(id),
  student_id    uuid NOT NULL REFERENCES students(id),
  score         smallint NOT NULL DEFAULT 0, -- 자동 채점
  submitted_at  timestamptz NOT NULL DEFAULT now(),
  rank_by_score smallint,                    -- 점수 기준 등수
  rank_by_order smallint,                    -- 제출 순서 기준 등수
  UNIQUE (task_id, student_id)
);

CREATE TABLE weekly_answers (
  submission_id uuid NOT NULL REFERENCES weekly_submissions(id) ON DELETE CASCADE,
  question_no   smallint NOT NULL,
  picked        jsonb NOT NULL,              -- 학생이 고른 번호(들)
  is_correct    boolean NOT NULL,
  PRIMARY KEY (submission_id, question_no)
);

-- ── ⑤ 소통 · 알림 ──────────────────────────────────────────

CREATE TABLE message_threads (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id uuid NOT NULL REFERENCES students(id),
  opened_by  actor_type NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE messages (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  thread_id   uuid NOT NULL REFERENCES message_threads(id) ON DELETE CASCADE,
  sender_type actor_type NOT NULL,
  sender_id   uuid,
  body        text,
  file_url    text,                          -- 파일 첨부
  created_at  timestamptz NOT NULL DEFAULT now(),
  read_at     timestamptz
);

CREATE TABLE notification_rules (
  id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  type    notify_type NOT NULL,
  channel notify_channel NOT NULL DEFAULT 'kakao',
  enabled boolean NOT NULL DEFAULT true
);

CREATE TABLE notifications (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rule_id      uuid REFERENCES notification_rules(id),
  student_id   uuid REFERENCES students(id),
  to_phone     varchar(20) NOT NULL,
  payload      jsonb NOT NULL,               -- 메시지 본문·과제 정보
  scheduled_at timestamptz NOT NULL,
  sent_at      timestamptz,
  status       notify_status NOT NULL DEFAULT '예약'
);
CREATE INDEX idx_notifications_queue ON notifications (status, scheduled_at);

-- ── ⑥ 보충 영상 ────────────────────────────────────────────

CREATE TABLE makeup_videos (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  class_id   uuid REFERENCES classes(id),      -- 강의명 (개설 강의 중 선택)
  lesson_at  timestamptz NOT NULL,             -- 강의 일시 (연월일시)
  video_url  text NOT NULL,                    -- 영상 주소 (유튜브·비메오 등)
  created_by uuid REFERENCES teachers(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE makeup_video_targets (
  video_id   uuid NOT NULL REFERENCES makeup_videos(id) ON DELETE CASCADE,
  student_id uuid NOT NULL REFERENCES students(id),   -- 해당 학생 (복수 선택)
  watched_at timestamptz,                             -- 학생이 시청한 시각
  PRIMARY KEY (video_id, student_id)
);

-- ── ⑦ 음악 듣기 (문학 작품 노래) ───────────────────────────

CREATE TABLE music_tracks (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  artist     varchar(30) NOT NULL,          -- 작가명
  title      varchar(100) NOT NULL,         -- 작품명
  file_name  varchar(200) NOT NULL,         -- 음악 파일명
  file_url   text,                          -- 업로드된 파일 경로
  duration   smallint,                      -- 재생 시간 (초)
  created_by uuid REFERENCES teachers(id),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_music_search ON music_tracks (artist, title);

CREATE TABLE music_plays (                  -- 재생 횟수 집계용 로그
  id         bigserial PRIMARY KEY,
  track_id   uuid NOT NULL REFERENCES music_tracks(id) ON DELETE CASCADE,
  student_id uuid NOT NULL REFERENCES students(id),
  played_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE music_favorites (              -- 즐겨찾기 (학생당 곡 1회)
  track_id   uuid NOT NULL REFERENCES music_tracks(id) ON DELETE CASCADE,
  student_id uuid NOT NULL REFERENCES students(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (track_id, student_id)
);

CREATE TABLE music_playlist_items (         -- 나의 플레이 리스트
  student_id uuid NOT NULL REFERENCES students(id),
  track_id   uuid NOT NULL REFERENCES music_tracks(id) ON DELETE CASCADE,
  position   smallint NOT NULL,             -- 리스트 내 순서
  added_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (student_id, track_id)
);

-- 재생 횟수·즐겨찾기 횟수는 뷰로 집계
-- CREATE VIEW v_music_stats AS
--   SELECT t.id, count(DISTINCT p.id) AS play_count, count(DISTINCT f.student_id) AS fav_count
--   FROM music_tracks t
--   LEFT JOIN music_plays p ON p.track_id = t.id
--   LEFT JOIN music_favorites f ON f.track_id = t.id
--   GROUP BY t.id;

-- ── ⑧ 통합 백로그 ──────────────────────────────────────────

CREATE TABLE activity_log (
  id         bigserial PRIMARY KEY,
  actor_type actor_type NOT NULL,
  actor_id   uuid,
  event_type varchar(40) NOT NULL,           -- signup, login, quiz_attempt, submit, message, notify …
  table_name varchar(40) NOT NULL,
  record_id  uuid,
  meta       jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_activity_log_time ON activity_log (created_at DESC);
CREATE INDEX idx_activity_log_actor ON activity_log (actor_type, actor_id);

-- 예시 트리거: 주간 과제 제출 시 백로그 적재
CREATE OR REPLACE FUNCTION log_weekly_submission() RETURNS trigger AS $$
BEGIN
  INSERT INTO activity_log (actor_type, actor_id, event_type, table_name, record_id, meta)
  VALUES ('학생', NEW.student_id, 'weekly_submit', 'weekly_submissions', NEW.id,
          jsonb_build_object('task_id', NEW.task_id, 'score', NEW.score));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_weekly_submission
AFTER INSERT ON weekly_submissions
FOR EACH ROW EXECUTE FUNCTION log_weekly_submission();

-- ── 파생 뷰 ────────────────────────────────────────────────

-- 회차별 랭킹 (닉네임 노출)
CREATE VIEW v_quiz_rankings AS
SELECT q.round_no,
       s.id AS student_id,
       s.nickname,
       a.score,
       RANK() OVER (PARTITION BY q.round_no ORDER BY a.score DESC)        AS rank_by_score,
       RANK() OVER (PARTITION BY q.round_no ORDER BY a.submitted_at ASC)  AS rank_by_order
FROM quiz_attempts a
JOIN quizzes q  ON q.id = a.quiz_id
JOIN students s ON s.id = a.student_id;

-- 영역·제재·갈래별 성적 분석 (학생 페이지 방사형 그래프의 원천)
CREATE VIEW v_score_analysis AS
SELECT ws.student_id,
       wt.round_no,
       wq.area,
       wq.subject,
       wq.genre,
       SUM(wq.points)                                                    AS total_points,
       SUM(CASE WHEN wa.is_correct THEN wq.points ELSE 0 END)            AS my_points,
       ROUND(AVG(wq.correct_rate), 1)                                    AS avg_rate
FROM weekly_answers wa
JOIN weekly_submissions ws ON ws.id = wa.submission_id
JOIN weekly_tasks wt       ON wt.id = ws.task_id
JOIN weekly_questions wq   ON wq.task_id = wt.id AND wq.no = wa.question_no
GROUP BY ws.student_id, wt.round_no, wq.area, wq.subject, wq.genre;
