-- Game reviews (single star rating 1~5 + free-text body, one per game per user).
-- PostgreSQL DDL aligned with the existing bibimbap table style (recruit-posts-ddl.sql).
-- W3-2: 댓글/리뷰 분리. game_reviews 신규 + game_comments user_id 컬럼 추가.

CREATE SEQUENCE IF NOT EXISTS "game_reviews_id_seq";

CREATE TABLE IF NOT EXISTS "game_reviews" (
    "id"         bigint DEFAULT nextval('game_reviews_id_seq'::regclass) NOT NULL,
    "game_id"    bigint NOT NULL,
    "user_id"    bigint NOT NULL,
    "rating"     smallint NOT NULL,
    "body"       text,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
    "deleted_at" timestamp with time zone,
    "is_delete"  boolean DEFAULT false NOT NULL,
    PRIMARY KEY ("id")
);

ALTER SEQUENCE "game_reviews_id_seq" OWNED BY "game_reviews"."id";

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'game_reviews_game_id_fkey'
    ) THEN
        ALTER TABLE "game_reviews"
            ADD CONSTRAINT "game_reviews_game_id_fkey"
            FOREIGN KEY ("game_id") REFERENCES "games" ("id");
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'game_reviews_user_id_fkey'
    ) THEN
        ALTER TABLE "game_reviews"
            ADD CONSTRAINT "game_reviews_user_id_fkey"
            FOREIGN KEY ("user_id") REFERENCES "users" ("id");
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'game_reviews_rating_check'
    ) THEN
        ALTER TABLE "game_reviews"
            ADD CONSTRAINT "game_reviews_rating_check"
            CHECK ("rating" BETWEEN 1 AND 5);
    END IF;
END
$$;

-- 게임당 사용자 1회 (active 리뷰 한정 — soft-delete 후 재작성 허용).
CREATE UNIQUE INDEX IF NOT EXISTS "ux_game_reviews_game_user_active"
    ON "game_reviews" ("game_id", "user_id")
    WHERE "is_delete" IS NOT TRUE;

-- 목록 조회 + 후속 집계 SELECT 의 game_id 필터용. 집계 컬럼/뷰는 신설하지 않음 (W2-3 동결 보호).
CREATE INDEX IF NOT EXISTS "idx_game_reviews_game"
    ON "game_reviews" ("game_id")
    WHERE "is_delete" = false;

COMMENT ON TABLE "game_reviews" IS '게임 리뷰. 게임당 사용자 1회, 별점 1~5 + 서술 평가';
COMMENT ON COLUMN "game_reviews"."id" IS '리뷰 고유 ID';
COMMENT ON COLUMN "game_reviews"."game_id" IS '대상 게임 games.id';
COMMENT ON COLUMN "game_reviews"."user_id" IS '리뷰 작성자 users.id (로그인 필수)';
COMMENT ON COLUMN "game_reviews"."rating" IS '별점 5점 단일 (1~5). 향후 다축은 별도 game_review_axes 테이블로 분리';
COMMENT ON COLUMN "game_reviews"."body" IS '서술 평가 (앱레벨 1,000자 제한, DB 무제한)';
COMMENT ON COLUMN "game_reviews"."created_at" IS '리뷰 작성 시각';
COMMENT ON COLUMN "game_reviews"."updated_at" IS '리뷰 마지막 수정 시각. updated_at > created_at 이면 수정됨';
COMMENT ON COLUMN "game_reviews"."deleted_at" IS '리뷰 삭제 시각';
COMMENT ON COLUMN "game_reviews"."is_delete" IS '소프트 삭제 여부';

-- ===========================================================================
-- 기존 DB 적용용 idempotent ALTER (security-hardening-ddl.sql DO $$ 패턴)
-- ---------------------------------------------------------------------------
-- game_comments user_id 컬럼 (W3-2: 작성자 귀속, nullable 비파괴).
-- 위 game_reviews CREATE/제약/인덱스는 전부 IF NOT EXISTS / DO $$ 멱등 → 재실행 안전.
-- ===========================================================================

ALTER TABLE "game_comments" ADD COLUMN IF NOT EXISTS "user_id" bigint;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'game_comments_user_id_fkey'
    ) THEN
        ALTER TABLE "game_comments"
            ADD CONSTRAINT "game_comments_user_id_fkey"
            FOREIGN KEY ("user_id") REFERENCES "users" ("id");
    END IF;
END
$$;

COMMENT ON COLUMN "game_comments"."user_id" IS '덧글 작성자 users.id (nullable — 레거시 닉네임 덧글 보존)';