schema_postgres.sql

-- sql/schema_postgres.sql
-- 조교 이미숫 (Leemisut Bot) - 클라우드 PostgreSQL 테이블 생성 스크립트
--
-- 언제 쓰나요?
--   봇을 처음 켜면 database.py 가 이 내용을 똑같이 실행해 테이블을 자동으로 만듭니다.
--   Supabase 의 SQL Editor 나 psql 에서 미리 만들어 두고 싶을 때 이 파일을 실행하세요.
--   (이 파일은 database.py 의 POSTGRES_SCHEMA 에서 그대로 뽑아낸 것이라 내용이 항상 같습니다)
--
-- 설계 메모
--   · user_id 는 디스코드 ID(최대 19자리)라서 INTEGER 로는 넘칩니다. 반드시 BIGINT 를 씁니다.
--   · 시각(solved_at, created_at)은 TEXT 에 UTC ISO 8601 로 넣습니다.
--     로컬 SQLite 와 형식을 똑같이 맞춰, 두 DB 사이에 데이터를 옮겨도 그대로 읽히게 하려는 선택입니다.
--   · 외래 키(FOREIGN KEY)는 일부러 걸지 않았습니다. /수어삭제 로 단어를 지워도
--     퀴즈 기록(quiz_logs)은 학습 이력으로 남겨야 하기 때문입니다.
--     (단어장 항목은 봇이 지울 때 함께 정리하고, 조회할 때는 sign_words 와 JOIN 해서 걸러냅니다)
--   · Supabase 를 쓰신다면: 봇은 DB 주소로 직접 붙으므로 RLS(행 수준 보안)와 무관하게 동작합니다.
--     대신 anon/public API 키로 이 테이블이 바깥에 노출되지 않게 해 주세요. (RLS 활성화 권장)

CREATE TABLE IF NOT EXISTS users (
    user_id         BIGINT  PRIMARY KEY,
    points          INTEGER NOT NULL DEFAULT 0,
    exp             INTEGER NOT NULL DEFAULT 0,
    level           INTEGER NOT NULL DEFAULT 1,
    streak          INTEGER NOT NULL DEFAULT 0,
    last_quiz_date  TEXT,
    last_daily_date TEXT
);

CREATE TABLE IF NOT EXISTS sign_words (
    word_id    BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    word_name  TEXT NOT NULL,
    meaning    TEXT NOT NULL,
    video_url  TEXT NOT NULL,
    image_url  TEXT NOT NULL DEFAULT '',
    category   TEXT NOT NULL DEFAULT '일반',
    detail_url TEXT NOT NULL DEFAULT '',
    image_urls TEXT NOT NULL DEFAULT '',   -- 수형 이미지 전체 (API signImages · 줄바꿈 구분)
    aliases    TEXT NOT NULL DEFAULT '',   -- 표제어에 함께 적힌 다른 이름 (고맙다,감사 → 감사 · 줄바꿈 구분)
    CONSTRAINT sign_words_name_video_key UNIQUE (word_name, video_url)
);

CREATE INDEX IF NOT EXISTS idx_sign_words_name ON sign_words(word_name);

CREATE TABLE IF NOT EXISTS quiz_logs (
    log_id     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id    BIGINT   NOT NULL,
    word_id    BIGINT   NOT NULL,
    is_correct SMALLINT NOT NULL,
    solved_at  TEXT     NOT NULL,
    quiz_type  TEXT     NOT NULL DEFAULT 'normal'  -- 'normal' /수어퀴즈 · 'review' /복습퀴즈
);

CREATE INDEX IF NOT EXISTS idx_quiz_logs_user_word ON quiz_logs(user_id, word_id);

CREATE INDEX IF NOT EXISTS idx_quiz_logs_user_solved ON quiz_logs(user_id, solved_at);

CREATE TABLE IF NOT EXISTS user_bookmarks (
    user_id    BIGINT NOT NULL,
    word_id    BIGINT NOT NULL,
    created_at TEXT   NOT NULL,
    CONSTRAINT user_bookmarks_user_word_key UNIQUE (user_id, word_id)
);

-- 오답노트. 틀린 단어가 자동으로 쌓이고, /복습퀴즈 로 맞히면 해결(is_mastered=1)됩니다.
-- ※ 이 파일보다 먼저 만든 DB라면 봇이 시작할 때 quiz_logs.quiz_type 컬럼을 자동으로 추가합니다.
CREATE TABLE IF NOT EXISTS user_quiz_notes (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id       BIGINT   NOT NULL,
    word_id       BIGINT   NOT NULL,
    wrong_count   INTEGER  NOT NULL DEFAULT 1,
    is_mastered   SMALLINT NOT NULL DEFAULT 0,
    last_wrong_at TEXT     NOT NULL,
    mastered_at   TEXT,
    CONSTRAINT user_quiz_notes_user_word_key UNIQUE (user_id, word_id)
);

CREATE INDEX IF NOT EXISTS idx_quiz_notes_open
    ON user_quiz_notes(user_id, is_mastered, wrong_count DESC, last_wrong_at DESC);

-- 문장 수어 (/문장수어). id 는 utils/sentence_seed.py 의 문장 번호를 그대로 씁니다. (자동 증가 아님)
-- 봇이 켜질 때 시드 파일 내용으로 맞추므로, 문장은 시드 파일에서 고쳐 주세요.
-- DB 에 직접 문장을 넣을 때는 id 를 1000 이상으로 적어 시드 번호와 겹치지 않게 해 주세요.
CREATE TABLE IF NOT EXISTS sign_sentences (
    id               BIGINT   PRIMARY KEY,
    category         TEXT     NOT NULL,              -- 속담 · 명언 · 일상회화 · VRChat
    korean_text      TEXT     NOT NULL,              -- 한국어 원문
    ksl_gloss        TEXT     NOT NULL,              -- 참고 예시 수어문 (글로스 · 절은 ' / ' 로 구분)
    translation_tip  TEXT     NOT NULL DEFAULT '',   -- 표현 꿀팁 · 비수지 신호
    difficulty       SMALLINT NOT NULL DEFAULT 1,    -- 1 입문 · 2 초급 · 3 중급
    source           TEXT     NOT NULL DEFAULT '',   -- 출처
    related_word_ids TEXT     NOT NULL DEFAULT '[]', -- sign_words.word_id 목록 (JSON 배열)
    created_at       TEXT     NOT NULL
);

-- 유저가 제출한 수어문. 같은 날(KST) 첫 제출에만 보상합니다.
CREATE TABLE IF NOT EXISTS sentence_submissions (
    id              BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sentence_id     BIGINT NOT NULL,
    user_id         BIGINT NOT NULL,
    submission_text TEXT   NOT NULL,
    created_at      TEXT   NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_sentence_submissions_user ON sentence_submissions(user_id, created_at);

CREATE INDEX IF NOT EXISTS idx_sentence_submissions_sentence ON sentence_submissions(sentence_id);