Blame for backend/sql/001_credits.sql

-- 크레딧 시스템 테이블 (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);