init.sql

-- ============================================
-- 음성 꼬맨틀 데이터베이스 스키마
-- ============================================

-- ============================================
-- 1. 퍼즐 정보 테이블 (마스터 데이터)
-- ============================================
CREATE TABLE puzzles (
    puzzle_number INT PRIMARY KEY,

    -- 정답 정보
    answer_word VARCHAR(100) NOT NULL,

    -- 퍼즐 메타데이터
    difficulty VARCHAR(20),                     -- 'easy', 'medium', 'hard'
    category VARCHAR(50),                       -- 카테고리 (예: 'meme', 'movie', 'song')

    -- 날짜 정보
    puzzle_date DATE NOT NULL UNIQUE,           -- 퍼즐 날짜 (YYYY-MM-DD)

    -- 타임스탬프
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 제약 조건
    CONSTRAINT positive_puzzle_number CHECK (puzzle_number > 0)
);

CREATE INDEX idx_puzzle_date ON puzzles(puzzle_date);

COMMENT ON TABLE puzzles IS '일일 퍼즐 정보 - 정답 단어';


-- ============================================
-- 2. 추측 기록 테이블 (음성 파일 없이 메타데이터만)
-- ============================================
CREATE TABLE guess_records (
    -- 기본 키
    id BIGSERIAL PRIMARY KEY,

    -- 게임 컨텍스트
    session_id VARCHAR(100) NOT NULL,
    puzzle_number INT NOT NULL,

    -- AI 분석 결과
    pitch DECIMAL(5, 2),                     -- AI 음높이 분석 (0-100)
    rhythm DECIMAL(5, 2),                    -- AI 리듬감 분석 (0-100)
    energy DECIMAL(5, 2),                    -- AI 에너지 분석 (0-100)
    pronunciation DECIMAL(5, 2),             -- AI 발음 분석 (0-100)
    transcript TEXT,                         -- AI 분석 대사 (score, kept for compatibility)
    user_text TEXT,                          -- STT transcription from MCP
    overall DECIMAL(5, 2),                   -- AI 총점 (0-100)
    advice TEXT,                             -- AI 조언

    -- 정답 여부
    is_correct BOOLEAN DEFAULT FALSE,

    -- 타임스탬프
    guess_timestamp BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 제약 조건
    CONSTRAINT pitch_range CHECK (pitch IS NULL OR (pitch >= 0 AND pitch <= 100)),
    CONSTRAINT rhythm_range CHECK (rhythm IS NULL OR (rhythm >= 0 AND rhythm <= 100)),
    CONSTRAINT energy_range CHECK (energy IS NULL OR (energy >= 0 AND energy <= 100)),
    CONSTRAINT pronunciation_range CHECK (pronunciation IS NULL OR (pronunciation >= 0 AND pronunciation <= 100)),
    CONSTRAINT overall_range CHECK (overall IS NULL OR (overall >= 0 AND overall <= 100))
);

-- ============================================
-- 인덱스
-- ============================================
CREATE INDEX idx_guess_session ON guess_records(session_id);
CREATE INDEX idx_guess_puzzle ON guess_records(puzzle_number);
CREATE INDEX idx_guess_created ON guess_records(created_at);
CREATE INDEX idx_guess_correct ON guess_records(is_correct);
CREATE INDEX idx_guess_overall ON guess_records(overall);

COMMENT ON TABLE guess_records IS '사용자 추측 기록 - AI 분석 결과만 저장 (음성 파일 없음)';


-- ============================================
-- 3. 날짜별 전체 통계 VIEW (대시보드용)
-- ============================================
CREATE OR REPLACE VIEW daily_statistics AS
SELECT
    p.puzzle_date,

    -- 참여자 수 (해당 날짜에 시도한 고유 세션 수)
    COUNT(DISTINCT gr.session_id) as participants,

    -- 정답자 수 (해당 날짜에 정답 맞춘 고유 세션 수)
    COUNT(DISTINCT CASE WHEN gr.is_correct = true THEN gr.session_id END) as winners,

    -- 정답률
    ROUND(
        CASE
            WHEN COUNT(DISTINCT gr.session_id) > 0
            THEN (COUNT(DISTINCT CASE WHEN gr.is_correct = true THEN gr.session_id END)::NUMERIC
                  / COUNT(DISTINCT gr.session_id) * 100)
            ELSE 0
        END,
        1
    ) as success_rate,

    -- 시도 횟수 (해당 날짜 총 음성 시도 수)
    COUNT(*) as total_attempts,

    -- 평균 시도 횟수 (참여자당)
    ROUND(
        CASE
            WHEN COUNT(DISTINCT gr.session_id) > 0
            THEN COUNT(*)::NUMERIC / COUNT(DISTINCT gr.session_id)
            ELSE 0
        END,
        1
    ) as avg_attempts_per_user

FROM puzzles p
LEFT JOIN guess_records gr ON p.puzzle_number = gr.puzzle_number
GROUP BY p.puzzle_date
ORDER BY p.puzzle_date DESC;

COMMENT ON VIEW daily_statistics IS '날짜별 전체 게임 통계 - 대시보드용';


-- ============================================
-- 4. 전체 누적 통계 VIEW (대시보드용)
-- ============================================
CREATE OR REPLACE VIEW overall_statistics AS
SELECT
    -- 전체 참여자 수 (모든 시간 동안 참여한 고유 세션 수)
    COUNT(DISTINCT session_id) as total_participants,

    -- 전체 정답자 수 (한 번이라도 정답 맞춘 고유 세션 수)
    COUNT(DISTINCT CASE WHEN is_correct = true THEN session_id END) as total_winners,

    -- 전체 정답률 (정답자 수 / 참여자 수)
    ROUND(
        CASE
            WHEN COUNT(DISTINCT session_id) > 0
            THEN (COUNT(DISTINCT CASE WHEN is_correct = true THEN session_id END)::NUMERIC
                  / COUNT(DISTINCT session_id) * 100)
            ELSE 0
        END,
        1
    ) as overall_success_rate,

    -- 전체 시도 횟수
    COUNT(*) as total_attempts,

    -- 전체 퍼즐 수
    (SELECT COUNT(*) FROM puzzles) as total_puzzles,

    -- 전체 평균 시도 횟수
    ROUND(
        CASE
            WHEN COUNT(DISTINCT session_id) > 0
            THEN COUNT(*)::NUMERIC / COUNT(DISTINCT session_id)
            ELSE 0
        END,
        1
    ) as avg_attempts_per_user

FROM guess_records;

COMMENT ON VIEW overall_statistics IS '전체 누적 게임 통계 - 대시보드용';


-- ============================================
-- 5. 개별 사용자 통계 VIEW
-- ============================================
CREATE OR REPLACE VIEW user_statistics AS
WITH
daily_activity AS (
    SELECT
        gr.session_id,
        p.puzzle_date,
        COUNT(*) as attempts,
        MAX(CASE WHEN gr.is_correct = true THEN 1 ELSE 0 END) as won
    FROM guess_records gr
    JOIN puzzles p ON gr.puzzle_number = p.puzzle_number
    GROUP BY gr.session_id, p.puzzle_date
),
streaks AS (
    SELECT
        session_id,
        puzzle_date,
        won,
        puzzle_date - (ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY puzzle_date))::int AS play_streak_group
    FROM daily_activity
),
current_play_streak AS (
    SELECT
        session_id,
        COUNT(*) as play_streak
    FROM streaks
    WHERE play_streak_group = (
        SELECT play_streak_group
        FROM streaks s2
        WHERE s2.session_id = streaks.session_id
        ORDER BY puzzle_date DESC
        LIMIT 1
    )
    GROUP BY session_id
),
win_streaks AS (
    SELECT
        session_id,
        COUNT(*) as win_streak
    FROM (
        SELECT
            session_id,
            puzzle_date,
            won,
            SUM(CASE WHEN won = 0 THEN 1 ELSE 0 END)
                OVER (PARTITION BY session_id ORDER BY puzzle_date DESC) as loss_count
        FROM daily_activity
    ) ws
    WHERE loss_count = 0 AND won = 1
    GROUP BY session_id
)
SELECT
    da.session_id,
    COUNT(DISTINCT da.puzzle_date) as total_plays,
    SUM(da.attempts) as total_attempts,
    SUM(da.won) as wins,
    ROUND(
        CASE
            WHEN COUNT(DISTINCT da.puzzle_date) > 0
            THEN (SUM(da.won)::NUMERIC / COUNT(DISTINCT da.puzzle_date) * 100)
            ELSE 0
        END,
        1
    ) as win_rate,
    COALESCE(cps.play_streak, 0) as play_streak,
    COALESCE(ws.win_streak, 0) as win_streak
FROM daily_activity da
LEFT JOIN current_play_streak cps ON da.session_id = cps.session_id
LEFT JOIN win_streaks ws ON da.session_id = ws.session_id
GROUP BY da.session_id, cps.play_streak, ws.win_streak;

COMMENT ON VIEW user_statistics IS '개별 사용자 게임 통계';


-- ============================================
-- 샘플 데이터 (테스트용)
-- ============================================
INSERT INTO puzzles (puzzle_number, answer_word, puzzle_date, difficulty, category)
VALUES
(1, 'Tralalero Tralala', '2024-11-24', 'easy', 'meme'),
(2, 'Wingardium Leviosa', '2024-11-25', 'medium', 'movie'),
(3, 'Baby shark, doo doo doo doo doo doo', '2024-11-26', 'hard', 'song');