Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /048_gift_orders.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

4.54 kB

-- 카페 음성 선물쿠폰 MVP — gift_orders (2026-05-28, P1a Phase B)
--
-- 주문 = 카페·상품 + 음성편지 + 받는이 이름 + 구매자 식별.
-- 요청서: doc/runbook/req_cafe_gift_mvp.md §1
-- 설계: doc/runbook/cafe_gift_p1a.md §2 sql/048
--
-- Stateless 구매자 (사용자 결정 #1):
--   - buyer_user_id 는 NULL (Supabase anon 미사용)
--   - 영수증/재접근 = reveal_token + buyer_email
--
-- 결제 mock (P1a):
--   - status 'mock_paid' 만 coupon 발급. P1b 에서 'paid' enum 추가.
--   - mock-pay endpoint 가 실 PG 콜백을 가장 (워터마크로 UI 노출)
--
-- 유효기간 5년 (공정위 표준약관):
--   - 백엔드가 명시 set (DB default X) → 정책 변경 추적 가능
--   - gift_coupons.expires_at 도 이 값을 복사
--
-- ⛔ 회귀 가드: 신규 테이블, CHECK 모두 신규 컬럼만. 결제 mock 명시 분리 ('mock_paid' vs 'paid').

CREATE TABLE IF NOT EXISTS public.gift_orders (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    cafe_id             UUID NOT NULL REFERENCES public.cafe_merchants(id) ON DELETE RESTRICT,
    product_id          UUID NOT NULL REFERENCES public.gift_products(id) ON DELETE RESTRICT,
    voice_letter_id     UUID REFERENCES public.voice_letters(id) ON DELETE SET NULL,

    -- Stateless 구매자 식별 (사용자 결정 #1)
    buyer_user_id       UUID REFERENCES auth.users(id) ON DELETE SET NULL,  -- P1a 는 항상 NULL
    buyer_name          TEXT NOT NULL DEFAULT '',
    buyer_phone_e164    TEXT,                        -- 영수증/환불용 (선택)
    buyer_email         TEXT,                        -- 영수증 발송 (선택)
    recipient_name      TEXT NOT NULL DEFAULT '',

    amount_krw          INT NOT NULL CHECK (amount_krw > 0),

    -- P1a 결제 mock — 'mock_paid' 만 coupon 발급. P1b 에서 'paid' enum 정상화.
    status              TEXT NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending','letter_pending','payable','mock_paid','paid','refunded','canceled')),
    pg_tx_id            TEXT,                        -- P1b 에서 실 PG 거래 ID

    -- 유효기간 5년 (백엔드가 now + 5y 명시 세팅, DB default X)
    expires_at          TIMESTAMPTZ,

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

CREATE INDEX IF NOT EXISTS idx_gift_orders_cafe_status
    ON public.gift_orders (cafe_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_gift_orders_buyer_user
    ON public.gift_orders (buyer_user_id)
    WHERE buyer_user_id IS NOT NULL;

-- voice_letters → orders FK 양방향 보강 (sql/047 에서는 column 만 만들고 FK 는 여기)
ALTER TABLE public.voice_letters
    DROP CONSTRAINT IF EXISTS voice_letters_order_id_fk;
ALTER TABLE public.voice_letters
    ADD CONSTRAINT voice_letters_order_id_fk
    FOREIGN KEY (order_id) REFERENCES public.gift_orders(id) ON DELETE SET NULL;

ALTER TABLE public.gift_orders ENABLE ROW LEVEL SECURITY;

-- 사장: 본인 카페 주문 SELECT (대시보드용, Phase E)
DROP POLICY IF EXISTS gift_orders_cafe_read ON public.gift_orders;
CREATE POLICY gift_orders_cafe_read ON public.gift_orders FOR SELECT
    USING (cafe_id IN (
        SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
    ));

-- 구매자 본인 (로그인 케이스 — P1a 는 NULL 이라 비활성, P1b 호환)
DROP POLICY IF EXISTS gift_orders_buyer_read ON public.gift_orders;
CREATE POLICY gift_orders_buyer_read ON public.gift_orders FOR SELECT
    USING (buyer_user_id = auth.uid());

-- INSERT/UPDATE 는 백엔드 service-role 만 (비로그인 손님 흐름)

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

DROP TRIGGER IF EXISTS gift_orders_touch_updated_at ON public.gift_orders;
CREATE TRIGGER gift_orders_touch_updated_at
    BEFORE UPDATE ON public.gift_orders
    FOR EACH ROW EXECUTE FUNCTION public._gift_orders_touch_updated_at();

COMMENT ON TABLE public.gift_orders IS
    '카페 선물 주문. P1a stateless (buyer_user_id NULL), mock_paid 만 coupon 발급. 5년 만료.';
COMMENT ON COLUMN public.gift_orders.status IS
    'pending → letter_pending → payable → mock_paid (P1a) | paid (P1b) → refunded/canceled.';
COMMENT ON COLUMN public.gift_orders.expires_at IS
    '구매일 + 5년 (공정위 표준약관). 백엔드가 명시 세팅 (DB default X).';

	-- 카페 음성 선물쿠폰 MVP — gift_orders (2026-05-28, P1a Phase B)
	--
	-- 주문 = 카페·상품 + 음성편지 + 받는이 이름 + 구매자 식별.
	-- 요청서: doc/runbook/req_cafe_gift_mvp.md §1
	-- 설계: doc/runbook/cafe_gift_p1a.md §2 sql/048
	--
	-- Stateless 구매자 (사용자 결정 #1):
	-- - buyer_user_id 는 NULL (Supabase anon 미사용)
	-- - 영수증/재접근 = reveal_token + buyer_email
	--
	-- 결제 mock (P1a):
	-- - status 'mock_paid' 만 coupon 발급. P1b 에서 'paid' enum 추가.
	-- - mock-pay endpoint 가 실 PG 콜백을 가장 (워터마크로 UI 노출)
	--
	-- 유효기간 5년 (공정위 표준약관):
	-- - 백엔드가 명시 set (DB default X) → 정책 변경 추적 가능
	-- - gift_coupons.expires_at 도 이 값을 복사
	--
	-- ⛔ 회귀 가드: 신규 테이블, CHECK 모두 신규 컬럼만. 결제 mock 명시 분리 ('mock_paid' vs 'paid').

	CREATE TABLE IF NOT EXISTS public.gift_orders (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

	cafe_id UUID NOT NULL REFERENCES public.cafe_merchants(id) ON DELETE RESTRICT,
	product_id UUID NOT NULL REFERENCES public.gift_products(id) ON DELETE RESTRICT,
	voice_letter_id UUID REFERENCES public.voice_letters(id) ON DELETE SET NULL,

	-- Stateless 구매자 식별 (사용자 결정 #1)
	buyer_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL, -- P1a 는 항상 NULL
	buyer_name TEXT NOT NULL DEFAULT '',
	buyer_phone_e164 TEXT, -- 영수증/환불용 (선택)
	buyer_email TEXT, -- 영수증 발송 (선택)
	recipient_name TEXT NOT NULL DEFAULT '',

	amount_krw INT NOT NULL CHECK (amount_krw > 0),

	-- P1a 결제 mock — 'mock_paid' 만 coupon 발급. P1b 에서 'paid' enum 정상화.
	status TEXT NOT NULL DEFAULT 'pending'
	CHECK (status IN ('pending','letter_pending','payable','mock_paid','paid','refunded','canceled')),
	pg_tx_id TEXT, -- P1b 에서 실 PG 거래 ID

	-- 유효기간 5년 (백엔드가 now + 5y 명시 세팅, DB default X)
	expires_at TIMESTAMPTZ,

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

	CREATE INDEX IF NOT EXISTS idx_gift_orders_cafe_status
	ON public.gift_orders (cafe_id, status, created_at DESC);
	CREATE INDEX IF NOT EXISTS idx_gift_orders_buyer_user
	ON public.gift_orders (buyer_user_id)
	WHERE buyer_user_id IS NOT NULL;

	-- voice_letters → orders FK 양방향 보강 (sql/047 에서는 column 만 만들고 FK 는 여기)
	ALTER TABLE public.voice_letters
	DROP CONSTRAINT IF EXISTS voice_letters_order_id_fk;
	ALTER TABLE public.voice_letters
	ADD CONSTRAINT voice_letters_order_id_fk
	FOREIGN KEY (order_id) REFERENCES public.gift_orders(id) ON DELETE SET NULL;

	ALTER TABLE public.gift_orders ENABLE ROW LEVEL SECURITY;

	-- 사장: 본인 카페 주문 SELECT (대시보드용, Phase E)
	DROP POLICY IF EXISTS gift_orders_cafe_read ON public.gift_orders;
	CREATE POLICY gift_orders_cafe_read ON public.gift_orders FOR SELECT
	USING (cafe_id IN (
	SELECT id FROM public.cafe_merchants WHERE owner_user_id = auth.uid()
	));

	-- 구매자 본인 (로그인 케이스 — P1a 는 NULL 이라 비활성, P1b 호환)
	DROP POLICY IF EXISTS gift_orders_buyer_read ON public.gift_orders;
	CREATE POLICY gift_orders_buyer_read ON public.gift_orders FOR SELECT
	USING (buyer_user_id = auth.uid());

	-- INSERT/UPDATE 는 백엔드 service-role 만 (비로그인 손님 흐름)

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

	DROP TRIGGER IF EXISTS gift_orders_touch_updated_at ON public.gift_orders;
	CREATE TRIGGER gift_orders_touch_updated_at
	BEFORE UPDATE ON public.gift_orders
	FOR EACH ROW EXECUTE FUNCTION public._gift_orders_touch_updated_at();

	COMMENT ON TABLE public.gift_orders IS
	'카페 선물 주문. P1a stateless (buyer_user_id NULL), mock_paid 만 coupon 발급. 5년 만료.';
	COMMENT ON COLUMN public.gift_orders.status IS
	'pending → letter_pending → payable → mock_paid (P1a) \| paid (P1b) → refunded/canceled.';
	COMMENT ON COLUMN public.gift_orders.expires_at IS
	'구매일 + 5년 (공정위 표준약관). 백엔드가 명시 세팅 (DB default X).';