#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
上傳檔集中化 — 遷移 dry-run（唯讀，不寫 DB、不搬檔）

掃描站台 SQL 裡的媒體引用，攤開「每張圖 → 實體檔 → file_id → uploaded_files 列 → 改寫後值」。
  A. albums：各 *_bind / *_category_bind 的 albums 欄（JSON，內含 cover 路徑）
  B. content：內文 description/content 的 <img src>（CKEditor 絕對網址）

用法：python3 hosts/migrate-uploaded-files-dryrun.py hosts/stations/jyg
"""
import sys, os, re, json, urllib.parse, time
from collections import defaultdict

def date_bucket(phys):
    """以檔案 mtime 決定 YYYYMMDD 分桶；檔案不存在則回 'unknown'"""
    try:
        return time.strftime('%Y%m%d', time.localtime(os.path.getmtime(phys)))
    except OSError:
        return 'unknown'

station = sys.argv[1].rstrip('/') if len(sys.argv) > 1 else 'hosts/stations/jygesg'
sql_path = next((os.path.join(station, f) for f in os.listdir(station) if f.endswith('.sql')), None)
assets = os.path.join(station, 'apps/resources/assets')
if not sql_path:
    print("找不到站台 SQL"); sys.exit(1)
sql = open(sql_path, encoding='utf-8', errors='ignore').read()

IMG_EXT = ('.jpg', '.jpeg', '.png', '.gif', '.webp', '.jfif', '.jp2', '.svg', '.bmp')

def to_physical(ref):
    """把引用字串正規化成 assets 底下的相對路徑（並回傳實體絕對路徑）"""
    r = ref.strip()
    r = re.sub(r'^https?://[^/]+', '', r)          # 去網域
    r = urllib.parse.unquote(r)                    # URL 解碼
    i = r.find('/assets/')
    if i >= 0: r = r[i + len('/assets/'):]
    elif r.startswith('assets/'): r = r[len('assets/'):]
    r = r.lstrip('/')
    if not r.startswith('upload/'):
        j = r.find('upload/')
        if j >= 0: r = r[j:]
    return r, os.path.join(assets, r)

def is_snowflake(name):
    base = os.path.splitext(os.path.basename(name))[0]
    return base.isdigit() and len(base) >= 15

# ---- 收集所有引用：{relpath: {"refs":N, "sources":set, "snowflake":bool}} ----
files = {}
def add(relpath, source):
    e = files.setdefault(relpath, {"refs": 0, "sources": set(), "snowflake": is_snowflake(relpath)})
    e["refs"] += 1; e["sources"].add(source)

# A. albums 欄（抓所有 *_bind / *_category_bind 的 INSERT，albums 是其中一欄的 JSON）
album_tables = re.findall(r'`(\w+?_(?:category_)?bind)`', sql)
album_vals = re.findall(r"'(\{\\\"cover\\\".*?\})'", sql)  # 粗抓 {"cover":...} 形式
for raw in album_vals:
    try:
        decoded = json.loads(raw.replace('\\"', '"').replace("\\'", "'"))
    except Exception:
        continue
    def walk(v):
        if isinstance(v, dict): [walk(x) for x in v.values()]
        elif isinstance(v, list): [walk(x) for x in v]
        elif isinstance(v, str) and 'upload/' in v and v.lower().endswith(IMG_EXT):
            rel, _ = to_physical(v); add(rel, 'albums')
    walk(decoded)

# B. 內文 <img src>（CKEditor）
for src in re.findall(r'<img[^>]+src=\\?["\']([^"\'\\]+)', sql):
    if '/upload/' not in src and not src.startswith('upload/'): continue
    if not src.lower().endswith(IMG_EXT): continue
    rel, _ = to_physical(src); add(rel, 'content')

# ---- 分配 file_id：snowflake 沿用檔名；其餘發 NEW 序號 ----
plan = []
newseq = 0
for rel in sorted(files):
    info = files[rel]
    phys = os.path.join(assets, rel)
    exists = os.path.isfile(phys)
    if info["snowflake"]:
        fid = os.path.splitext(os.path.basename(rel))[0]
    else:
        newseq += 1
        fid = f"NEW#{newseq:04d}"  # dry-run 佔位；實際執行時改發雪花號
    ext = os.path.splitext(rel)[1]
    newpath = f"/assets/upload/{date_bucket(phys)}/{fid}{ext}"
    plan.append({"rel": rel, "file_id": fid, "name": os.path.basename(rel),
                 "exists": exists, "refs": info["refs"], "newpath": newpath,
                 "sources": ",".join(sorted(info["sources"])), "snowflake": info["snowflake"]})

# ---- 報告 ----
total = len(plan)
reuse = sum(1 for p in plan if p["snowflake"])
need_new = total - reuse
missing = sum(1 for p in plan if not p["exists"])
total_refs = sum(p["refs"] for p in plan)
out = []
def w(s=""): out.append(s)

w("=" * 72)
w(f"遷移 dry-run（唯讀） — {station}")
w("=" * 72)
w(f"唯一實體圖檔：{total}（被引用 {total_refs} 次 → 去重後集中成 {total} 筆 uploaded_files）")
w(f"  ├ 已是 file_id 命名(沿用)：{reuse}")
w(f"  ├ 需發新 file_id        ：{need_new}")
w(f"  └ 實體檔不存在(需注意)  ：{missing}")
w(f"來源分布：albums={sum(1 for p in plan if 'albums' in p['sources'])}  content={sum(1 for p in plan if 'content' in p['sources'])}")
w("")
w("--- 範例：每張圖 → file_id → uploaded_files 列 → 改寫後值（前 15 筆）---")
for p in plan[:15]:
    flag = "" if p["exists"] else "  ⚠檔案不存在"
    w(f"\n[{p['file_id']}]  ({p['sources']}, 被引用×{p['refs']}){flag}")
    w(f"  實體檔(現)  : assets/{p['rel']}")
    w(f"  搬移到      : {p['newpath']}")
    w(f"  uploaded_files列: name={p['name']}  path={p['newpath']}")
    if 'albums' in p['sources']:
        w(f"  albums改寫 : [\"...{os.path.basename(p['rel'])}\"]  →  [\"{p['file_id']}\"]")
    if 'content' in p['sources']:
        w(f"  內文改寫   : <img src=\"...{os.path.basename(p['rel'])}\">  →  <img data-file-id=\"{p['file_id']}\">")

w("")
w("--- 不存在的實體檔（遷移前需處理） ---")
miss = [p for p in plan if not p["exists"]]
for p in miss[:20]:
    w(f"  ✗ assets/{p['rel']}  ({p['sources']})")
if len(miss) > 20: w(f"  ... 另有 {len(miss)-20} 筆")
if not miss: w("  （無，全部實體檔都在）")

report = "\n".join(out)
print(report)
dst = os.path.join(station, 'docs/ai-agents/project-init/migrate-uploaded-files-dryrun.txt')
os.makedirs(os.path.dirname(dst), exist_ok=True)
# 完整清單另存
full = report + "\n\n--- 完整清單 ---\n" + "\n".join(
    f"{p['file_id']}\t{p['sources']}\trefs={p['refs']}\texists={p['exists']}\tassets/{p['rel']}" for p in plan)
open(dst, 'w', encoding='utf-8').write(full)
print(f"\n完整報告已存：{dst}")