#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
uploaded_files 孤兒清理 — 階段1：比對標記（dry-run / 產 UPDATE SQL）

判斷法（保守）：某 file_id 若在「uploaded_files INSERT 以外」的整份資料找不到任何出現 → 孤兒。
  涵蓋所有引用形式：albums/banner 的 ["file_id"]、內文 data-file-id="file_id"。
  19 位雪花號夠獨特，幾乎不會與其他數值碰撞；且「有出現就保留」是安全方向。

用法：
  python3 hosts/clean-orphan-uploaded-files.py <station>            # dry-run：只報告
  python3 hosts/clean-orphan-uploaded-files.py <station> --mark     # 另產出軟刪 UPDATE SQL

資料來源：站台的 *_migrated.sql（若無則用一般 *.sql）。實際上線可改成直接 dump live DB。
"""
import sys, os, re, time

station = (sys.argv[1] if len(sys.argv) > 1 else 'hosts/stations/jygesg').rstrip('/')
MARK = '--mark' in sys.argv
# 優先用 migrated（代表遷移後/上線的引用狀態）
cands = [f for f in os.listdir(station) if f.endswith('.sql')]
sql_name = next((f for f in cands if f.endswith('_migrated.sql')), None) or next((f for f in cands if not f.endswith('_migrated.sql')), None)
sql = open(os.path.join(station, sql_name), encoding='utf-8', errors='ignore').read()

# 1) 切出 uploaded_files INSERT 區塊（引號感知）
def extract_statement(text, token):
    i = text.find(token)
    if i < 0: return ''
    j, n, inq = i, len(text), False
    while j < n:
        c = text[j]
        if c == '\\': j += 2; continue
        if c == "'": inq = not inq
        elif c == ';' and not inq: return text[i:j + 1]
        j += 1
    return text[i:n]

uf_stmt = extract_statement(sql, "INSERT INTO `uploaded_files`")
if not uf_stmt:
    print("找不到 uploaded_files 資料（INSERT）。"); sys.exit(1)

# 2) 取 uploaded_files 內所有 (file_id, name, path)
rows = []
for t in re.findall(r"\(((?:[^()']|'(?:[^'\\]|\\.)*')*)\)", uf_stmt[uf_stmt.find('VALUES') + 6:]):
    cols = re.findall(r"'(?:[^'\\]|\\.)*'|[^,]+", t)
    if len(cols) >= 4:
        fid = cols[0].strip().strip("'")
        name = cols[1].strip().strip("'")
        path = cols[3].strip().strip("'")
        rows.append((fid, name, path))

# 3) 語料 = 整份 SQL 去掉 uploaded_files INSERT 區塊（避免自我比對）
corpus = sql.replace(uf_stmt, '')

# 4) 逐 file_id 判斷是否還被引用
orphans, used = [], 0
for fid, name, path in rows:
    if fid in corpus:
        used += 1
    else:
        orphans.append((fid, name, path))

# 5) 報告
print("=" * 64)
print(f"uploaded_files 孤兒比對 — {station}  (來源 {sql_name})")
print("=" * 64)
print(f"uploaded_files 總列數：{len(rows)}")
print(f"  ├ 仍被引用：{used}")
print(f"  └ 孤兒(無人引用，建議軟刪)：{len(orphans)}")
if orphans:
    print("\n--- 孤兒清單(前 30) ---")
    for fid, name, path in orphans[:30]:
        print(f"  {fid}  {name}  {path}")
    if len(orphans) > 30:
        print(f"  ... 另有 {len(orphans) - 30} 筆")
else:
    print("\n（目前沒有孤兒——每筆 uploaded_files 都還有引用）")

if MARK and orphans:
    now = int(time.time())
    ids = ",".join("'%s'" % o[0] for o in orphans)
    out = os.path.join(station, 'docs/ai-agents/project-init/orphan-soft-delete.sql')
    os.makedirs(os.path.dirname(out), exist_ok=True)
    open(out, 'w', encoding='utf-8').write(
        f"-- 軟刪 {len(orphans)} 個孤兒 uploaded_files（{time.strftime('%Y-%m-%d')}）\n"
        f"-- 反向救回：若這些 file_id 之後又被引用，clean 腳本會把 deleted_at 清回 0\n"
        f"UPDATE `uploaded_files` SET `deleted_at`={now} WHERE `file_id` IN ({ids}) AND `deleted_at`=0;\n")
    print(f"\n[--mark] 軟刪 UPDATE SQL 已產出：{out}")
    print("（先看過再決定要不要在 DB 執行）")
elif not MARK:
    print("\n(dry-run) 加 --mark 會額外產出軟刪 UPDATE SQL")