Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /050_gift_dispatch.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

3.21 kB

-- 카페 음성 선물쿠폰 — 단체 발송 + 받는이 식별 (2026-05-28, P1a 전송 2경로)
--
-- 요청서: doc/runbook/req_cafe_gift_mvp.md §5
-- 설계: doc/runbook/cafe_gift_p1a.md (Phase B-2 / 전송 2경로)
--
-- 변경:
--   1) gift_orders 에 order_kind/company_name/company_contact 추가 (단체 분기)
--   2) gift_coupons 의 order_id UNIQUE 제거 (corporate=1 order → N coupons)
--   3) gift_coupons 에 recipient_phone_e164/recipient_name 추가 (받는이 unique 링크 트래킹)
--
-- ⛔ 회귀 가드 (sql/043 데이터 손실 사고 교훈):
--   - 모든 신규 컬럼 nullable + default — 기존 데이터 위반 0
--   - CHECK 는 신규 컬럼에만 (기존 CHECK 무수정)
--   - order_id UNIQUE 제거는 idempotent (DROP CONSTRAINT IF EXISTS)
--   - order_kind 미지정 행은 'personal' 로 해석 — 기존 mock_paid 흐름 회귀 0
--   - 알림톡 호출 코드 0건 (요청서 §5-3 박제)

-- ───────── gift_orders 확장 ─────────

ALTER TABLE public.gift_orders
    ADD COLUMN IF NOT EXISTS order_kind TEXT NOT NULL DEFAULT 'personal'
        CHECK (order_kind IN ('personal','corporate')),
    ADD COLUMN IF NOT EXISTS company_name TEXT,
    ADD COLUMN IF NOT EXISTS company_contact TEXT;

CREATE INDEX IF NOT EXISTS idx_gift_orders_kind
    ON public.gift_orders (order_kind, status, created_at DESC);

COMMENT ON COLUMN public.gift_orders.order_kind IS
    'personal: 1 order → 1 coupon, Kakao Share. corporate: 1 order → N coupons, SMS 일괄.';
COMMENT ON COLUMN public.gift_orders.company_name IS
    'corporate 전용 — 회사명. SMS 본문 [회사명] 머리에 사용.';
COMMENT ON COLUMN public.gift_orders.company_contact IS
    'corporate 전용 — 담당자 이름/연락처(자유 텍스트). 환불·문의 응대용.';


-- ───────── gift_coupons 확장 (1 order → N coupons) ─────────

-- order_id UNIQUE 제거 (corporate 분기 위해). sql/049 에서 인라인 UNIQUE → 제약 이름 = gift_coupons_order_id_key
ALTER TABLE public.gift_coupons
    DROP CONSTRAINT IF EXISTS gift_coupons_order_id_key;

-- 받는이 식별 (corporate 발송 시 채워짐). personal 흐름은 NULL = gift_orders.recipient_name 사용.
ALTER TABLE public.gift_coupons
    ADD COLUMN IF NOT EXISTS recipient_phone_e164 TEXT,
    ADD COLUMN IF NOT EXISTS recipient_name TEXT,
    ADD COLUMN IF NOT EXISTS dispatched_at TIMESTAMPTZ,     -- SMS 발송 시각
    ADD COLUMN IF NOT EXISTS dispatch_status TEXT NOT NULL DEFAULT 'pending'
        CHECK (dispatch_status IN ('pending','sent','failed','skipped')),
    ADD COLUMN IF NOT EXISTS dispatch_error TEXT;

-- order 별 + 받는이별 조회 (대시보드)
CREATE INDEX IF NOT EXISTS idx_gift_coupons_order_dispatch
    ON public.gift_coupons (order_id, dispatch_status);

COMMENT ON COLUMN public.gift_coupons.recipient_phone_e164 IS
    'corporate 발송 받는이 번호. personal=NULL (gift_orders.recipient_name 사용).';
COMMENT ON COLUMN public.gift_coupons.dispatch_status IS
    'pending: 발급만 / sent: SMS 발송 OK / failed: 발송 실패(번호 무효 등) / skipped: 발송 안 함(personal 흐름).';

	-- 카페 음성 선물쿠폰 — 단체 발송 + 받는이 식별 (2026-05-28, P1a 전송 2경로)
	--
	-- 요청서: doc/runbook/req_cafe_gift_mvp.md §5
	-- 설계: doc/runbook/cafe_gift_p1a.md (Phase B-2 / 전송 2경로)
	--
	-- 변경:
	-- 1) gift_orders 에 order_kind/company_name/company_contact 추가 (단체 분기)
	-- 2) gift_coupons 의 order_id UNIQUE 제거 (corporate=1 order → N coupons)
	-- 3) gift_coupons 에 recipient_phone_e164/recipient_name 추가 (받는이 unique 링크 트래킹)
	--
	-- ⛔ 회귀 가드 (sql/043 데이터 손실 사고 교훈):
	-- - 모든 신규 컬럼 nullable + default — 기존 데이터 위반 0
	-- - CHECK 는 신규 컬럼에만 (기존 CHECK 무수정)
	-- - order_id UNIQUE 제거는 idempotent (DROP CONSTRAINT IF EXISTS)
	-- - order_kind 미지정 행은 'personal' 로 해석 — 기존 mock_paid 흐름 회귀 0
	-- - 알림톡 호출 코드 0건 (요청서 §5-3 박제)

	-- ───────── gift_orders 확장 ─────────

	ALTER TABLE public.gift_orders
	ADD COLUMN IF NOT EXISTS order_kind TEXT NOT NULL DEFAULT 'personal'
	CHECK (order_kind IN ('personal','corporate')),
	ADD COLUMN IF NOT EXISTS company_name TEXT,
	ADD COLUMN IF NOT EXISTS company_contact TEXT;

	CREATE INDEX IF NOT EXISTS idx_gift_orders_kind
	ON public.gift_orders (order_kind, status, created_at DESC);

	COMMENT ON COLUMN public.gift_orders.order_kind IS
	'personal: 1 order → 1 coupon, Kakao Share. corporate: 1 order → N coupons, SMS 일괄.';
	COMMENT ON COLUMN public.gift_orders.company_name IS
	'corporate 전용 — 회사명. SMS 본문 [회사명] 머리에 사용.';
	COMMENT ON COLUMN public.gift_orders.company_contact IS
	'corporate 전용 — 담당자 이름/연락처(자유 텍스트). 환불·문의 응대용.';


	-- ───────── gift_coupons 확장 (1 order → N coupons) ─────────

	-- order_id UNIQUE 제거 (corporate 분기 위해). sql/049 에서 인라인 UNIQUE → 제약 이름 = gift_coupons_order_id_key
	ALTER TABLE public.gift_coupons
	DROP CONSTRAINT IF EXISTS gift_coupons_order_id_key;

	-- 받는이 식별 (corporate 발송 시 채워짐). personal 흐름은 NULL = gift_orders.recipient_name 사용.
	ALTER TABLE public.gift_coupons
	ADD COLUMN IF NOT EXISTS recipient_phone_e164 TEXT,
	ADD COLUMN IF NOT EXISTS recipient_name TEXT,
	ADD COLUMN IF NOT EXISTS dispatched_at TIMESTAMPTZ, -- SMS 발송 시각
	ADD COLUMN IF NOT EXISTS dispatch_status TEXT NOT NULL DEFAULT 'pending'
	CHECK (dispatch_status IN ('pending','sent','failed','skipped')),
	ADD COLUMN IF NOT EXISTS dispatch_error TEXT;

	-- order 별 + 받는이별 조회 (대시보드)
	CREATE INDEX IF NOT EXISTS idx_gift_coupons_order_dispatch
	ON public.gift_coupons (order_id, dispatch_status);

	COMMENT ON COLUMN public.gift_coupons.recipient_phone_e164 IS
	'corporate 발송 받는이 번호. personal=NULL (gift_orders.recipient_name 사용).';
	COMMENT ON COLUMN public.gift_coupons.dispatch_status IS
	'pending: 발급만 / sent: SMS 발송 OK / failed: 발송 실패(번호 무효 등) / skipped: 발송 안 함(personal 흐름).';