-- 카페 음성 선물쿠폰 MVP — gift_products (2026-05-28, P1a Phase A)
--
-- 카페별 쿠폰 상품 (아메리카노 1잔, 케이크 1조각 등).
-- 요청서: doc/runbook/req_cafe_gift_mvp.md §1
-- 설계: doc/runbook/cafe_gift_p1a.md §2 sql/046
--
-- 핵심:
--   - cafe_id FK + CASCADE (카페 삭제 시 상품도 정리)
--   - image_path → Supabase Storage 'cafe-products' 버킷 (12h signed URL)
--   - active=false 면 비로그인 구매 페이지에 안 보임 (RLS + 백엔드 이중)
--   - sort_order 사장이 직접 순서 변경
--
-- ⛔ 회귀 가드: 신규 테이블, 기존 CHECK 영향 0.

CREATE TABLE IF NOT EXISTS public.gift_products (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cafe_id         UUID NOT NULL REFERENCES public.cafe_merchants(id) ON DELETE CASCADE,

    name            TEXT NOT NULL,                          -- '아메리카노 1잔'
    description     TEXT,                                   -- 부가 설명 (옵션)
    price_krw       INT NOT NULL CHECK (price_krw > 0),     -- 원 단위 (KRW 정수)
    image_path      TEXT,                                   -- Storage 'cafe-products' 버킷 path
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    sort_order      INT NOT NULL DEFAULT 0,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_gift_products_cafe_active
    ON public.gift_products (cafe_id, active, sort_order);

ALTER TABLE public.gift_products ENABLE ROW LEVEL SECURITY;

-- 사장: 본인 카페 상품 ALL (cafe_merchants owner 검증)
DROP POLICY IF EXISTS gift_products_owner ON public.gift_products;
CREATE POLICY gift_products_owner ON public.gift_products
    FOR ALL
    USING (cafe_id IN (
        SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
    ))
    WITH CHECK (cafe_id IN (
        SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
    ));

-- 공개: active=true 만 SELECT (anon 가능, 구매 페이지용)
DROP POLICY IF EXISTS gift_products_public_read ON public.gift_products;
CREATE POLICY gift_products_public_read ON public.gift_products
    FOR SELECT
    USING (active = TRUE);

CREATE OR REPLACE FUNCTION public._gift_products_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS gift_products_touch_updated_at ON public.gift_products;
CREATE TRIGGER gift_products_touch_updated_at
    BEFORE UPDATE ON public.gift_products
    FOR EACH ROW EXECUTE FUNCTION public._gift_products_touch_updated_at();

COMMENT ON TABLE public.gift_products IS
    '카페 선물 상품 카탈로그. active=false 비공개. 이미지는 Storage cafe-products 버킷.';
COMMENT ON COLUMN public.gift_products.price_krw IS
    'KRW 정수. 백엔드에서 gift_orders.amount_krw 로 복사 (가격 변경 후속 주문 영향 0).';