#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
上傳檔集中化 — 遷移工具

  python3 hosts/migrate-uploaded-files.py <station>            # dry-run：只出報告
  python3 hosts/migrate-uploaded-files.py <station> --build    # 產出新 .sql + 搬檔腳本（不動原始檔）

產出（--build）：
  <station>/<name>_migrated.sql     新 DB 匯入用：albums/banner→file_id、內文→data-file-id、補 uploaded_files
  <station>/migrate_move_files.sh   把實體檔複製到 upload/<日期>/<file_id>.ext（與 .sql 的 path 對齊）
  <station>/docs/ai-agents/project-init/migrate-uploaded-files-dryrun.txt

媒體欄位兩種策略：
  結構化(albums/banner)：JSON 解析→重建。路徑字串 與 物件{filename,src} 都換成 file_id。
  內文(content/description)：<img src=絕對網址> → <img data-file-id>。
唯讀原則：原始 .sql 與既有實體檔都不動。
"""
import sys, os, re, json, urllib.parse, time

station = (sys.argv[1] if len(sys.argv) > 1 else 'hosts/stations/jygesg').rstrip('/')
BUILD = '--build' in sys.argv
sql_name = next((f for f in os.listdir(station) if f.endswith('.sql') and not f.endswith('_migrated.sql')), None)
sql_path = os.path.join(station, sql_name)
assets = os.path.join(station, 'apps/resources/assets')
sql = open(sql_path, encoding='utf-8', errors='ignore').read()

IMG_EXT = ('.jpg', '.jpeg', '.png', '.gif', '.webp', '.jfif', '.jp2', '.svg', '.bmp')
MIME = {'.jpg': 'image/jpeg', '.jpeg': 'image/jpeg', '.png': 'image/png', '.gif': 'image/gif',
        '.webp': 'image/webp', '.jfif': 'image/jpeg', '.jp2': 'image/jp2', '.svg': 'image/svg+xml', '.bmp': 'image/bmp'}
IMG_RE = re.compile(r'\.(?:jpe?g|png|gif|webp|jfif|jp2|svg|bmp)$', re.I)

TWEPOC = 1288834974657
_seq = 0; _last = 0
def snowflake():
    global _seq, _last
    ts = int(time.time() * 1000)
    if ts == _last:
        _seq = (_seq + 1) & 4095
        if _seq == 0:
            while ts <= _last: ts = int(time.time() * 1000)
    else:
        _seq = 0
    _last = ts
    return str(((ts - TWEPOC) << 22) | _seq)

def to_relpath(ref):
    r = re.sub(r'^https?://[^/]+', '', ref.strip())
    r = urllib.parse.unquote(r)
    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

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

def date_bucket(phys):
    try: return time.strftime('%Y%m%d', time.localtime(os.path.getmtime(phys)))
    except OSError: return 'unknown'

def extract_statement(text, token):
    """引號感知地切出某 INSERT 整段（從 token 到 quote 外的 ';'）"""
    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]

def unescape(s):
    return re.sub(r'\\(.)', r'\1', s)

def is_img_ref(s):
    return isinstance(s, str) and ('upload/' in s or '/upload' in s) and bool(IMG_RE.search(s))

def ref_of(item):
    """list 中一個 item → 它的圖片引用字串（路徑 or 物件的 src）；非圖回 None"""
    if is_img_ref(item): return item
    if isinstance(item, dict):
        for k in ('src', 'url', 'path', 'file'):
            if k in item and is_img_ref(item[k]): return item[k]
    return None

# ---------- 檔案登錄 ----------
files = {}  # relpath -> {name,exists,size,ext,date,refs,sources,file_id}
def reg(rel, source):
    e = files.get(rel)
    if not e:
        phys = os.path.join(assets, rel); exists = os.path.isfile(phys)
        e = files[rel] = {"name": os.path.basename(rel), "exists": exists,
                          "size": (os.path.getsize(phys) if exists else 0),
                          "ext": os.path.splitext(rel)[1].lower(), "date": date_bucket(phys),
                          "refs": 0, "sources": set(), "file_id": None}
    e["refs"] += 1; e["sources"].add(source)
    return e

# ---------- 收集媒體 ----------
media_entries = {}   # old_raw -> (kind, parsed_data)
content_strings = {} # 原始 img src -> relpath

def collect_struct(v, source):
    if isinstance(v, list):
        for item in v:
            r = ref_of(item)
            if r is not None: reg(to_relpath(r), source)
            else: collect_struct(item, source)
    elif isinstance(v, dict):
        for val in v.values(): collect_struct(val, source)

# A. albums：全 dump 的 '{...cover...}'（albums 一律是 {cover:[...]}）
for m in re.finditer(r"'(\{(?:[^'\\]|\\.)*\})'", sql):
    inner = m.group(1)
    if 'cover' not in inner: continue
    try: data = json.loads(unescape(inner))
    except Exception: continue
    if not isinstance(data, dict) or 'cover' not in data: continue
    collect_struct(data, 'albums')
    media_entries[m.group(0)] = ('albums', data)

# B. banner：僅 pages_i18n（有掛 resolver 的讀取路徑）內的裸陣列 '[...]'
stmt = extract_statement(sql, "INSERT INTO `pages_i18n`")
for m in re.finditer(r"'(\[(?:[^'\\]|\\.)*\])'", stmt):
    inner = m.group(1)
    if 'upload' not in inner: continue
    try: data = json.loads(unescape(inner))
    except Exception: continue
    if not isinstance(data, list): continue
    collect_struct(data, 'banner')
    media_entries[m.group(0)] = ('banner', data)

# C. 內文 img src
for orig in re.findall(r'src=\\?["\']([^"\'\\]+)', sql):
    if ('/upload/' not in orig and not orig.startswith('upload/')) or not orig.lower().endswith(IMG_EXT):
        continue
    reg(to_relpath(orig), 'content'); content_strings[orig] = to_relpath(orig)

# ---------- 分配 file_id ----------
for rel in sorted(files):
    files[rel]["file_id"] = os.path.splitext(files[rel]["name"])[0] if is_snowflake_name(rel) else snowflake()

def item_to_fid(item):
    r = ref_of(item)
    if r is None: return item
    rel = to_relpath(r); e = files.get(rel)
    return e['file_id'] if (e and e['exists']) else rel

def rebuild(v):
    if isinstance(v, list):
        return [item_to_fid(x) if ref_of(x) is not None else rebuild(x) for x in v]
    if isinstance(v, dict):
        return {k: rebuild(val) for k, val in v.items()}
    return v

# ---------- 報告 ----------
plan = [dict(rel=r, **files[r]) for r in sorted(files)]
total = len(plan); reuse = sum(1 for p in plan if is_snowflake_name(p['rel']))
missing = sum(1 for p in plan if not p['exists'])
out = []
def w(s=""): out.append(s)
w("=" * 70); w(f"遷移報告 — {station}  ({'BUILD' if BUILD else 'DRY-RUN'})"); w("=" * 70)
w(f"唯一實體圖檔：{total}（引用 {sum(p['refs'] for p in plan)} 次）")
w(f"  ├ 沿用既有 file_id：{reuse}    ├ 發新雪花號：{total - reuse}    └ 實體不存在(跳過)：{missing}")
for k in ('albums', 'banner', 'content'):
    w(f"  {k}: {sum(1 for p in plan if k in p['sources'])} 檔")
w("")
for p in plan[:10]:
    w(f"[{p['file_id']}] ({','.join(sorted(p['sources']))} ×{p['refs']}){'' if p['exists'] else ' ⚠不存在'}  {p['rel']}")
w("\n--- 破圖(不存在、不遷移) ---")
for p in plan:
    if not p['exists']: w(f"  ✗ {p['rel']} ({','.join(sorted(p['sources']))})")
rep = "\n".join(out)
print(rep)
dst = os.path.join(station, 'docs/ai-agents/project-init/migrate-uploaded-files-dryrun.txt')
os.makedirs(os.path.dirname(dst), exist_ok=True)
open(dst, 'w', encoding='utf-8').write(rep + "\n\n--- 完整清單 ---\n" +
    "\n".join(f"{p['file_id']}\t{','.join(sorted(p['sources']))}\trefs={p['refs']}\texists={p['exists']}\t{p['rel']}" for p in plan))

if not BUILD:
    print(f"\n(dry-run) 報告：{dst}\n加 --build 產出新 .sql + 搬檔腳本")
    sys.exit(0)

# ================= BUILD =================
def sqlq(s): return s.replace('\\', '\\\\').replace("'", "\\'")
def to_raw(data):
    js = json.dumps(data, ensure_ascii=False, separators=(',', ':'))
    return "'" + js.replace('\\', '\\\\').replace("'", "\\'").replace('"', '\\"') + "'"

migrated = sql
n_struct = n_img = 0
# 1) 結構化（albums/banner）：整值重建
for old_raw, (kind, data) in media_entries.items():
    new_raw = to_raw(rebuild(data))
    if old_raw != new_raw and old_raw in migrated:
        migrated = migrated.replace(old_raw, new_raw); n_struct += 1
# 2) 內文 img src → data-file-id
for s, rel in content_strings.items():
    e = files.get(rel)
    if not e or not e['exists']: continue
    fid = e['file_id']
    for pat in ('src=\\"' + s + '\\"', 'src="' + s + '"', "src='" + s + "'"):
        c = migrated.count(pat)
        if c:
            migrated = migrated.replace(pat, 'data-file-id=\\"' + fid + '\\"'); n_img += c

# 3) uploaded_files INSERT
now = int(time.time()); rows = []
for p in plan:
    if not p['exists']: continue
    fid = p['file_id']; np = f"/assets/upload/{p['date']}/{fid}{p['ext']}"
    info = json.dumps({"basename": f"{fid}{p['ext']}", "type": MIME.get(p['ext'], 'application/octet-stream'),
                       "size": p['size'], "local": np}, ensure_ascii=False)
    rows.append("(" + ",".join([f"'{fid}'", f"'{sqlq(p['name'])}'", "''", f"'{np}'", f"'{sqlq(info)}'",
        "''", "''", "0", "''", "0", "1", str(now), "0"]) + ")")
insert = ("\n-- 集中化遷移：uploaded_files 資料\nINSERT INTO `uploaded_files` (`file_id`,`name`,`host`,`path`,`info`,"
          "`content`,`seo`,`is_locked`,`password`,`is_tmp`,`is_enable`,`created_at`,`deleted_at`) VALUES\n"
          + ",\n".join(rows) + ";\n")
migrated = migrated.rstrip() + "\n" + insert
out_sql = os.path.join(station, sql_name.replace('.sql', '_migrated.sql'))
open(out_sql, 'w', encoding='utf-8').write(migrated)

# 4) 搬檔腳本
moves = []
for p in plan:
    if not p['exists']: continue
    src = os.path.join('apps/resources/assets', p['rel'])
    moves.append(f'mkdir -p "apps/resources/assets/upload/{p["date"]}"; cp -n "{src}" "apps/resources/assets/upload/{p["date"]}/{p["file_id"]}{p["ext"]}"')
mv_path = os.path.join(station, 'migrate_move_files.sh')
open(mv_path, 'w', encoding='utf-8').write("#!/usr/bin/env bash\n# 在站台根目錄執行\nset -e\n" + "\n".join(moves) + "\n")
os.chmod(mv_path, 0o755)

print(f"\n[BUILD 完成]")
print(f"  結構化(albums/banner) 改寫：{n_struct} 處")
print(f"  內文改寫：{n_img} 處")
print(f"  uploaded_files INSERT：{len(rows)} 列")
print(f"  → {out_sql}\n  → {mv_path}")