Day 8~14: Supabase + DB + pg_cron + 검수 SLA

-- 줍줍 4 테이블 + 인덱스

-- 1. users
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  kakao_id TEXT UNIQUE NOT NULL,
  nickname TEXT NOT NULL,
  fcm_token TEXT,
  target_business_type TEXT,
  target_stage TEXT,
  target_region TEXT,
  target_age_min INT,
  target_age_max INT,
  target_household_type TEXT,
  income_criteria TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. benefits — SSOT for two tabs (E5 [1])
CREATE TABLE benefits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source TEXT NOT NULL,
  source_id TEXT NOT NULL,
  name TEXT NOT NULL,
  support_type TEXT,
  deadline DATE,
  support_amount BIGINT,
  target_business_type TEXT,
  target_stage TEXT,
  target_age_min INT,
  target_age_max INT,
  target_household_type TEXT,
  income_criteria TEXT,
  confidence FLOAT NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  total_jupjups INT DEFAULT 0,
  total_success INT DEFAULT 0,
  UNIQUE (source, source_id)         -- 중복 INSERT 차단
);

-- 3. jupjups — 사용자 행위 데이터
CREATE TABLE jupjups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  benefit_id UUID NOT NULL REFERENCES benefits(id) ON DELETE CASCADE,
  result TEXT NOT NULL DEFAULT 'PENDING',
  ip_address INET,                   -- IP rate limit 추적 (G-6)
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (user_id, benefit_id)       -- ⚠️ 통계 조작 차단 1차
);

-- 4. reports — 후기 신고
CREATE TABLE reports (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  reporter_user_id UUID NOT NULL REFERENCES users(id),
  reported_jupjup_id UUID NOT NULL REFERENCES jupjups(id),
  reason TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'PENDING',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

// G-6 본격 — IP rate limit (1h 5+ reject)
const REDIS_URL = Deno.env.get('UPSTASH_REDIS_REST_URL')!;
const REDIS_TOKEN = Deno.env.get('UPSTASH_REDIS_REST_TOKEN')!;

export async function checkIpRate(ip: string): Promise<{ allowed: boolean; remaining: number }> {
  const key = `jupjup:ip:${ip}`;

  // INCR + EXPIRE (atomic via Redis pipeline)
  const incrResp = await fetch(`${REDIS_URL}/incr/${key}`, {
    headers: { Authorization: `Bearer ${REDIS_TOKEN}` },
  });
  const { result: count } = await incrResp.json();

  // 첫 호출이면 TTL 1h 설정
  if (count === 1) {
    await fetch(`${REDIS_URL}/expire/${key}/3600`, {
      headers: { Authorization: `Bearer ${REDIS_TOKEN}` },
    });
  }

  return { allowed: count <= 5, remaining: Math.max(0, 5 - count) };
}

-- 1. jupjups 집계 트리거 (INSERT/UPDATE/DELETE)
CREATE OR REPLACE FUNCTION update_benefits_stats()
RETURNS TRIGGER AS $$
DECLARE
  target_benefit_id UUID;
BEGIN
  IF TG_OP = 'DELETE' THEN
    target_benefit_id := OLD.benefit_id;
  ELSE
    target_benefit_id := NEW.benefit_id;
  END IF;

  UPDATE benefits SET
    total_jupjups = (SELECT COUNT(*) FROM jupjups WHERE benefit_id = target_benefit_id),
    total_success = (
      SELECT COUNT(*) FROM jupjups
      WHERE benefit_id = target_benefit_id AND result = 'SUCCESS'
    ),
    avg_days_taken = (
      SELECT AVG(days_taken) FROM jupjups
      WHERE benefit_id = target_benefit_id AND days_taken IS NOT NULL
        AND result = 'SUCCESS'
    ),
    updated_at = NOW()
  WHERE id = target_benefit_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_jupjups_stats
AFTER INSERT OR UPDATE OR DELETE ON jupjups
FOR EACH ROW EXECUTE FUNCTION update_benefits_stats();

-- 2. is_active 자동 비활성화 (G-3) — pg_cron에서 호출
CREATE OR REPLACE FUNCTION deactivate_expired_benefits()
RETURNS void AS $$
BEGIN
  UPDATE benefits
  SET is_active = false, updated_at = NOW()
  WHERE deadline < NOW() AND is_active = true;
END;
$$ LANGUAGE plpgsql;

-- 복합 인덱스 (P1 — REVIEW.md 1차 W-C → PASS 전환)
CREATE INDEX idx_benefits_target_business
  ON benefits (target_business_type, target_stage, deadline)
  WHERE is_active = true;

CREATE INDEX idx_benefits_target_individual
  ON benefits (target_age_min, target_age_max, target_household_type, deadline)
  WHERE is_active = true;

CREATE INDEX idx_jupjups_user_benefit
  ON jupjups (user_id, benefit_id);  -- UNIQUE 보강

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 1. 매일 03:00 (KST) → 4 API 호출 Edge Function 트리거
SELECT cron.schedule(
  'daily-fetch-public-data',
  '0 18 * * *',  -- UTC 18:00 = KST 03:00
  $$ SELECT net.http_post(
    'https://[PROJECT_REF].supabase.co/functions/v1/fetch-public-data',
    '{}'::jsonb,
    '{}'::jsonb,
    '{"Content-Type":"application/json","Authorization":"Bearer [SERVICE_ROLE]"}'::jsonb
  ); $$
);

-- 2. 매일 04:00 → is_active 자동 비활성화 (G-3)
SELECT cron.schedule(
  'daily-deactivate-expired',
  '0 19 * * *',  -- UTC 19:00 = KST 04:00
  $$ SELECT deactivate_expired_benefits(); $$
);

"어드민 검수 큐 페이지 prototype. Day 5의 lib/admin/review-queue.ts
인터페이스 활용.

요구사항:
1. src/app/admin/queue/page.tsx (Next.js App Router 또는 Expo Router)
2. 7건 admin 큐 (5 admin + 2 parse_failed) 노출
3. 각 행: source · raw_response · classify_result · 재분류 버튼
4. 재분류 → Sonnet으로 호출 → 결과 갱신
5. 승인 → benefits INSERT → 큐에서 제거
6. 반려 → 영구 제거"

"검수 SLA 7일 cron + Slack 알림. C-3 발견 구현.

요구사항:
1. supabase/functions/admin-sla-check/ Edge Function
2. 매일 09:00 KST 호출 (pg_cron)
3. lib/admin/review-queue.ts 의 getOverdueItems() 사용
4. 7일+ 미처리 행 발견 시 Slack webhook 호출
5. SLACK_WEBHOOK_URL 환경변수
6. 메시지: 어드민 큐 N건 SLA 위반 (7일+)"