Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /046_gift_products.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

2.98 kB

-- 카페 음성 선물쿠폰 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).';

	-- 카페 음성 선물쿠폰 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).';