# ANU 독립 재측정 — task-3063. 전부 하나의 트랜잭션, 마지막에 무조건 ROLLBACK.
import os, re, sys, uuid, pathlib
import psycopg2

ROOT = pathlib.Path("/home/jay/projects/InsuRo")
WT   = ROOT / ".worktrees/task-3063-dev5"

env = {}
for line in (ROOT/".env").read_text().splitlines():
    m = re.match(r'^([A-Z_]+)=(.*)$', line.strip())
    if m: env[m.group(1)] = m.group(2).strip().strip('"').strip("'")

pw  = env["SUPABASE_DB_PASSWORD"]
ref = env["VITE_SUPABASE_PROJECT_ID"]

conn = psycopg2.connect(
    host="aws-1-ap-northeast-2.pooler.supabase.com", port=5432,
    user=f"postgres.{ref}", password=pw, dbname="postgres",
    connect_timeout=20, sslmode="require",
)
conn.autocommit = False
cur = conn.cursor()
def q(sql, args=None):
    cur.execute(sql, args); 
    try: return cur.fetchall()
    except Exception: return None
def show(tag, v): print(f"  {tag:<46} {v}")

OUT = []
try:
    print("=== [PRE] 적용 전 상태 (ANU 직접 조회) ===")
    show("benchmark_sources 존재", q("SELECT to_regclass('public.benchmark_sources')")[0][0])
    show("cron job 총 개수", q("SELECT count(*) FROM cron.job")[0][0])
    show("pg_cron 버전", q("SELECT extversion FROM pg_extension WHERE extname='pg_cron'")[0][0])
    show("current_database", q("SELECT current_database()")[0][0])

    print("\n=== [DDL] 마이그레이션 2개 실행 (트랜잭션 내부) ===")
    for f in ["20260830T000001_task3063_benchmark_sources.sql",
              "20260830T000003_task3063_benchmark_sources_purge.sql"]:
        sql = (WT/"supabase/migrations"/f).read_text()
        sql = sql.replace("BEGIN;", "").replace("COMMIT;", "")   # 바깥 트랜잭션 유지
        cur.execute(sql)
        show(f"{f[:34]} 실행", "OK")
    show("cron job 등록 수", q("SELECT count(*) FROM cron.job WHERE jobname='task3063-purge-benchmark-sources'")[0][0])

    users = q("SELECT id FROM auth.users ORDER BY created_at LIMIT 2")
    u1, u2 = str(users[0][0]), str(users[1][0])
    show("테스트 사용자 2명 확보", f"{u1[:8]}… / {u2[:8]}…")

    # ---------- P1: purge 가 실제로 지우는가 (ANU 자체 데이터) ----------
    print("\n=== [P1] 자동삭제 로직 — ANU 자체 데이터로 재측정 ===")
    for i in range(4):   # 만료 4건 (봇은 3건)
        q("""INSERT INTO public.benchmark_sources
             (user_id,keyword,channel,rank,url,created_at,expires_at)
             VALUES (%s,%s,'blog',1,%s, now()-interval '9 days', now()-interval '2 days')""",
          (u1, f"ANU만료{i}", f"https://blog.naver.com/anu/{i}"))
    for i in range(3):   # 미만료 3건
        q("""INSERT INTO public.benchmark_sources
             (user_id,keyword,channel,rank,url) VALUES (%s,%s,'blog',1,%s)""",
          (u1, f"ANU생존{i}", f"https://blog.naver.com/anu/live{i}"))
    show("삭제 전 총행 / 만료경과", f"{q('SELECT count(*) FROM public.benchmark_sources')[0][0]} / "
                                    f"{q('SELECT count(*) FROM public.benchmark_sources WHERE expires_at<=now()')[0][0]}")
    show("health(삭제 전)", q("SELECT * FROM public.benchmark_sources_purge_health")[0])
    deleted = q("SELECT public.purge_expired_benchmark_sources()")[0][0]
    show("★ 함수 반환 삭제건수 (기대 4)", deleted)
    show("★ 삭제 후 총행 / 만료경과 (기대 3/0)",
         f"{q('SELECT count(*) FROM public.benchmark_sources')[0][0]} / "
         f"{q('SELECT count(*) FROM public.benchmark_sources WHERE expires_at<=now()')[0][0]}")
    show("생존 키워드", [r[0] for r in q("SELECT keyword FROM public.benchmark_sources ORDER BY keyword")])
    show("health(삭제 후)", q("SELECT * FROM public.benchmark_sources_purge_health")[0])
    OUT.append(("P1 purge 실삭제", deleted == 4))

    # ---------- P2: RLS 양방향 ----------
    print("\n=== [P2] RLS 양방향 (ANU 직접) ===")
    q("SAVEPOINT sp_rls")
    cur.execute("SET LOCAL ROLE authenticated")
    cur.execute("SELECT set_config('request.jwt.claims', %s, true)", ('{"sub":"%s","role":"authenticated"}' % u1,))
    own = q("SELECT count(*) FROM public.benchmark_sources")[0][0]
    show("소유자 본인 조회 (기대 3)", own)
    cur.execute("SELECT set_config('request.jwt.claims', %s, true)", ('{"sub":"%s","role":"authenticated"}' % u2,))
    other = q("SELECT count(*) FROM public.benchmark_sources")[0][0]
    show("★ 타 사용자 조회 (기대 0)", other)
    try:
        q("""INSERT INTO public.benchmark_sources (user_id,keyword,channel,rank,url)
             VALUES (%s,'타인명의','blog',1,'https://x')""", (u1,))
        forged = "★★ 통과됨 = 취약"
    except Exception as e:
        forged = f"거부됨 ({type(e).__name__})"
    show("★ 타인 명의 INSERT", forged)
    q("ROLLBACK TO SAVEPOINT sp_rls")
    cur.execute("RESET ROLE")
    OUT.append(("P2 RLS 격리", own == 3 and other == 0 and "거부" in forged))
    print("=== PROBE PART 1 END ===")
finally:
    pass

try:
    # ---------- P3: ★ ANU 신규 — 봉인 ① 우회 시도 (봇 미시험) ----------
    print("\n=== [P3] ★ ANU 신규 우회 시험 — 만료 봉인을 뚫을 수 있는가 ===")
    q("SAVEPOINT sp_byp")
    cur.execute("SET LOCAL ROLE authenticated")
    cur.execute("SELECT set_config('request.jwt.claims', %s, true)", ('{"sub":"%s","role":"authenticated"}' % u1,))

    # B1) expires_at 만 미래로 (created_at 은 기본값) → CHECK 가 막아야 함
    try:
        q("""INSERT INTO public.benchmark_sources (user_id,keyword,channel,rank,url,expires_at)
             VALUES (%s,'B1','blog',1,'https://x', now()+interval '3650 days')""", (u1,))
        b1 = "★★ 통과 = 취약"
    except Exception as e:
        b1 = f"거부됨 ({getattr(e,'diag',None) and e.diag.constraint_name})"
    show("B1 expires_at 만 3650일", b1)
    q("ROLLBACK TO SAVEPOINT sp_byp")
    cur.execute("SET LOCAL ROLE authenticated")
    cur.execute("SELECT set_config('request.jwt.claims', %s, true)", ('{"sub":"%s","role":"authenticated"}' % u1,))

    # B2) ★★ created_at 도 함께 미래로 밀기 → CHECK 는 상대비교라 통과할 수 있다
    try:
        q("""INSERT INTO public.benchmark_sources (user_id,keyword,channel,rank,url,created_at,expires_at)
             VALUES (%s,'B2','blog',1,'https://x',
                     now()+interval '3650 days', now()+interval '3655 days')""", (u1,))
        row = q("""SELECT expires_at > now()+interval '3000 days'
                     FROM public.benchmark_sources WHERE keyword='B2'""")
        b2 = f"★★ 통과 = 봉인 우회됨 (만료 10년 후: {row[0][0] if row else '?'})"
        b2_vuln = True
    except Exception as e:
        b2 = f"거부됨 ({getattr(e,'diag',None) and e.diag.constraint_name})"; b2_vuln = False
    show("B2 ★ created_at+expires_at 동시 미래", b2)

    # B2-2) 그 행이 purge 를 실제로 회피하는가
    if b2_vuln:
        cur.execute("RESET ROLE")
        d = q("SELECT public.purge_expired_benchmark_sources()")[0][0]
        surv = q("SELECT count(*) FROM public.benchmark_sources WHERE keyword='B2'")[0][0]
        show("★ purge 실행 후 B2 행 생존 (1=영구잔존)", surv)
        OUT.append(("B2 만료봉인 우회", not (surv == 1)))
    q("ROLLBACK TO SAVEPOINT sp_byp")
    cur.execute("RESET ROLE")

    # ---------- P4: ★ ANU 신규 — 뷰/로그 노출 범위 (봇 주장 재검증) ----------
    print("\n=== [P4] ★ ANU 신규 — purge_log / purge_health 실제 노출 범위 ===")
    show("health 는 뷰인가", q("SELECT relkind FROM pg_class WHERE relname='benchmark_sources_purge_health'")[0][0])
    show("health security_invoker",
         q("""SELECT coalesce((SELECT option_value FROM pg_options_to_table(c.reloptions)
                               WHERE option_name='security_invoker'),'미설정(=owner권한)')
                FROM pg_class c WHERE c.relname='benchmark_sources_purge_health'""")[0][0])
    show("purge_log RLS 켜짐", q("SELECT relrowsecurity FROM pg_class WHERE relname='benchmark_sources_purge_log'")[0][0])
    show("purge_log 정책 수", q("SELECT count(*) FROM pg_policies WHERE tablename='benchmark_sources_purge_log'")[0][0])
    for role in ("anon","authenticated"):
        show(f"{role} → health SELECT 권한",
             q("SELECT has_table_privilege(%s,'public.benchmark_sources_purge_health','SELECT')",(role,))[0][0])
        show(f"{role} → purge_log SELECT 권한",
             q("SELECT has_table_privilege(%s,'public.benchmark_sources_purge_log','SELECT')",(role,))[0][0])
    # 실제로 읽히는지 (권한이 있다면 RLS 가 막아주는지까지)
    for role in ("anon","authenticated"):
        q("SAVEPOINT sp_v")
        try:
            cur.execute(f"SET LOCAL ROLE {role}")
            cur.execute("SELECT set_config('request.jwt.claims', %s, true)", ('{"sub":"%s","role":"%s"}' % (u2, role),))
            r = q("SELECT * FROM public.benchmark_sources_purge_health")
            show(f"★ {role} 실제 health 조회", f"성공 → {r[0]}")
        except Exception as e:
            show(f"{role} 실제 health 조회", f"거부 ({type(e).__name__})")
        q("ROLLBACK TO SAVEPOINT sp_v"); cur.execute("RESET ROLE")
        q("SAVEPOINT sp_l")
        try:
            cur.execute(f"SET LOCAL ROLE {role}")
            r = q("SELECT count(*) FROM public.benchmark_sources_purge_log")
            show(f"★ {role} 실제 purge_log 조회", f"성공 → {r[0][0]}행")
        except Exception as e:
            show(f"{role} 실제 purge_log 조회", f"거부 ({type(e).__name__})")
        q("ROLLBACK TO SAVEPOINT sp_l"); cur.execute("RESET ROLE")

    # ---------- P5: ★ ANU 신규 — 1인당 행수 상한이 있는가 ----------
    print("\n=== [P5] ★ ANU 신규 — 1인당 저장 건수 상한 ===")
    q("SAVEPOINT sp_cap")
    cur.execute("SET LOCAL ROLE authenticated")
    cur.execute("SELECT set_config('request.jwt.claims', %s, true)", ('{"sub":"%s","role":"authenticated"}' % u1,))
    n_ok = 0
    for i in range(60):
        try:
            q("""INSERT INTO public.benchmark_sources (user_id,keyword,channel,rank,url)
                 VALUES (%s,'CAP','blog',1,'https://blog.naver.com/same/1')""", (u1,))
            n_ok += 1
        except Exception: break
    show("★ 동일 URL 60회 반복 저장 성공 수", f"{n_ok}  (상한 있으면 <60)")
    show("중복 방지(UNIQUE) 존재?", q("""SELECT count(*) FROM pg_indexes
          WHERE tablename='benchmark_sources' AND indexdef ILIKE '%UNIQUE%'""")[0][0])
    OUT.append(("P5 1인당 상한 존재", n_ok < 60))
    q("ROLLBACK TO SAVEPOINT sp_cap"); cur.execute("RESET ROLE")

    # ---------- P6: UPDATE 로 만료 연장 ----------
    print("\n=== [P6] UPDATE 정책 부재 재확인 ===")
    q("SAVEPOINT sp_up")
    cur.execute("SET LOCAL ROLE authenticated")
    cur.execute("SELECT set_config('request.jwt.claims', %s, true)", ('{"sub":"%s","role":"authenticated"}' % u1,))
    cur.execute("UPDATE public.benchmark_sources SET expires_at = now()+interval '6 days'")
    show("★ 소유자 UPDATE 영향행 (기대 0)", cur.rowcount)
    OUT.append(("P6 UPDATE 차단", cur.rowcount == 0))
    q("ROLLBACK TO SAVEPOINT sp_up"); cur.execute("RESET ROLE")

    print("\n=== [요약] ===")
    for name, ok in OUT: print(f"  {'PASS' if ok else '★FAIL/발견'}  {name}")
finally:
    conn.rollback()
    cur.execute("SELECT to_regclass('public.benchmark_sources'), (SELECT count(*) FROM cron.job)")
    print("\n[POST-ROLLBACK 흔적 확인]", cur.fetchone())
    cur.close(); conn.close()