Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /001_credits.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

2.57 kB

-- 크레딧 시스템 테이블 (Supabase SQL Editor에서 실행)

-- 1. 사용자 크레딧 잔액
CREATE TABLE IF NOT EXISTS public.user_credits (
    user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    balance INTEGER NOT NULL DEFAULT 100,
    total_earned INTEGER NOT NULL DEFAULT 100,
    total_spent INTEGER NOT NULL DEFAULT 0,
    daily_reset_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- RLS: 본인만 조회, service_role만 수정
ALTER TABLE public.user_credits ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can read own credits" ON public.user_credits
    FOR SELECT USING (auth.uid() = user_id);

-- 2. 크레딧 변동 로그
CREATE TABLE IF NOT EXISTS public.credit_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    amount INTEGER NOT NULL,  -- 양수=충전, 음수=차감
    reason TEXT NOT NULL DEFAULT '',
    detail JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE public.credit_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can read own logs" ON public.credit_logs
    FOR SELECT USING (auth.uid() = user_id);

CREATE INDEX IF NOT EXISTS idx_credit_logs_user ON public.credit_logs(user_id, created_at DESC);

-- 3. 쿠폰 테이블
CREATE TABLE IF NOT EXISTS public.coupons (
    id BIGSERIAL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    credit_amount INTEGER NOT NULL DEFAULT 10000,
    max_uses INTEGER NOT NULL DEFAULT 1,
    used_count INTEGER NOT NULL DEFAULT 0,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE public.coupons ENABLE ROW LEVEL SECURITY;
-- 쿠폰은 service_role로만 관리

-- 4. 쿠폰 사용 이력
CREATE TABLE IF NOT EXISTS public.coupon_redemptions (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    coupon_id BIGINT NOT NULL REFERENCES public.coupons(id),
    credit_amount INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, coupon_id)  -- 1인 1회 사용
);

ALTER TABLE public.coupon_redemptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can read own redemptions" ON public.coupon_redemptions
    FOR SELECT USING (auth.uid() = user_id);

-- 5. 테스트 쿠폰 삽입 (필요시)
-- INSERT INTO public.coupons (code, credit_amount, max_uses) VALUES ('WELCOME2026', 10000, 100);

	-- 크레딧 시스템 테이블 (Supabase SQL Editor에서 실행)

	-- 1. 사용자 크레딧 잔액
	CREATE TABLE IF NOT EXISTS public.user_credits (
	user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
	balance INTEGER NOT NULL DEFAULT 100,
	total_earned INTEGER NOT NULL DEFAULT 100,
	total_spent INTEGER NOT NULL DEFAULT 0,
	daily_reset_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	-- RLS: 본인만 조회, service_role만 수정
	ALTER TABLE public.user_credits ENABLE ROW LEVEL SECURITY;
	CREATE POLICY "Users can read own credits" ON public.user_credits
	FOR SELECT USING (auth.uid() = user_id);

	-- 2. 크레딧 변동 로그
	CREATE TABLE IF NOT EXISTS public.credit_logs (
	id BIGSERIAL PRIMARY KEY,
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	amount INTEGER NOT NULL, -- 양수=충전, 음수=차감
	reason TEXT NOT NULL DEFAULT '',
	detail JSONB NOT NULL DEFAULT '{}',
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	ALTER TABLE public.credit_logs ENABLE ROW LEVEL SECURITY;
	CREATE POLICY "Users can read own logs" ON public.credit_logs
	FOR SELECT USING (auth.uid() = user_id);

	CREATE INDEX IF NOT EXISTS idx_credit_logs_user ON public.credit_logs(user_id, created_at DESC);

	-- 3. 쿠폰 테이블
	CREATE TABLE IF NOT EXISTS public.coupons (
	id BIGSERIAL PRIMARY KEY,
	code TEXT NOT NULL UNIQUE,
	credit_amount INTEGER NOT NULL DEFAULT 10000,
	max_uses INTEGER NOT NULL DEFAULT 1,
	used_count INTEGER NOT NULL DEFAULT 0,
	active BOOLEAN NOT NULL DEFAULT TRUE,
	expires_at TIMESTAMPTZ,
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	ALTER TABLE public.coupons ENABLE ROW LEVEL SECURITY;
	-- 쿠폰은 service_role로만 관리

	-- 4. 쿠폰 사용 이력
	CREATE TABLE IF NOT EXISTS public.coupon_redemptions (
	id BIGSERIAL PRIMARY KEY,
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	coupon_id BIGINT NOT NULL REFERENCES public.coupons(id),
	credit_amount INTEGER NOT NULL,
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	UNIQUE(user_id, coupon_id) -- 1인 1회 사용
	);

	ALTER TABLE public.coupon_redemptions ENABLE ROW LEVEL SECURITY;
	CREATE POLICY "Users can read own redemptions" ON public.coupon_redemptions
	FOR SELECT USING (auth.uid() = user_id);

	-- 5. 테스트 쿠폰 삽입 (필요시)
	-- INSERT INTO public.coupons (code, credit_amount, max_uses) VALUES ('WELCOME2026', 10000, 100);