Blame for backend/sql/009_age_verification.sql

-- 18+ 연령 인증 (2026-05-03)
--
-- 성인 대화 모드(spice_level=hot) 및 자유대화 채팅·음성 진입 시 필요한
-- 자가 인증(self-attestation) + 동의 로그 보관.
--
-- 향후 정식 본인인증(다날·PASS 등) 통합 자리 마련. method 컬럼이 인증 수단을 구분.
--
-- 한국 정보통신망법 / 일본 특상법(JP 확장 시) 모두 만족하는 최소 요건:
--   - 명시적 동의 시점 기록 (verified_at)
--   - 동의 시 IP·user_agent 기록 (분쟁 시 입증)
--   - 동의 본문의 버전 추적 (consent_text_version) — 약관 변경 시 재동의 트리거
--   - 철회(revoke) 가능성 — 사용자 요청 시 즉시 무효화

CREATE TABLE IF NOT EXISTS public.user_age_verifications (
    user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    verified BOOLEAN NOT NULL DEFAULT false,
    verified_at TIMESTAMPTZ,
    method VARCHAR(32) NOT NULL DEFAULT 'self_attestation',
        -- 'self_attestation' | 'danal_phone' | 'pass' | 'ipin' (확장 자리)
    ip_address INET,
    user_agent TEXT,
    consent_text_version VARCHAR(16) NOT NULL DEFAULT 'v1',
    revoked_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- 동의 이력 로그 (감사용 — 동의·철회·재동의 모두 추적)
CREATE TABLE IF NOT EXISTS public.age_verification_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    event VARCHAR(16) NOT NULL,  -- 'verified' | 'revoked' | 're_verified'
    method VARCHAR(32) NOT NULL DEFAULT 'self_attestation',
    ip_address INET,
    user_agent TEXT,
    consent_text_version VARCHAR(16) NOT NULL DEFAULT 'v1',
    detail JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_age_verification_logs_user
    ON public.age_verification_logs(user_id, created_at DESC);

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

-- updated_at 자동 갱신 trigger
CREATE OR REPLACE FUNCTION public.tg_age_verifications_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_age_verifications_updated_at ON public.user_age_verifications;
CREATE TRIGGER trg_age_verifications_updated_at
  BEFORE UPDATE ON public.user_age_verifications
  FOR EACH ROW EXECUTE FUNCTION public.tg_age_verifications_updated_at();