#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
#2 遷移：pages_i18n.banner_desc → uploaded_files.content

把「橫幅描述」(banner_desc) 依 index 對應到「橫幅圖」(banner = file_id)，
搬進該圖在 uploaded_files 的 content 欄位。之後 banner_desc 退役（可清空）。

  banner[i] (file_id) ↔ banner_desc[i] (描述) → UPDATE uploaded_files SET content WHERE file_id

用法：
  python3 hosts/migrate-banner-desc.py <station>            # dry-run：列對照
  python3 hosts/migrate-banner-desc.py <station> --apply    # 產出 UPDATE SQL 檔

來源：<station> 的 *_migrated.sql（banner 已是 file_id）。
"""
import sys, os, re, json

station = (sys.argv[1] if len(sys.argv) > 1 else 'hosts/stations/jygesg').rstrip('/')
APPLY = '--apply' in sys.argv
sql_name = next((f for f in os.listdir(station) if f.endswith('_migrated.sql')), None)
if not sql_name:
    print("找不到 *_migrated.sql（請先跑主遷移）"); sys.exit(1)
sql = open(os.path.join(station, sql_name), encoding='utf-8', errors='ignore').read()

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]

def split_tuples(body):
    out, depth, cur, inq, i = [], 0, '', False, 0
    while i < len(body):
        c = body[i]
        if c == '\\' and inq: cur += body[i:i+2]; i += 2; continue
        if c == "'": inq = not inq
        if not inq and c == '(':
            depth += 1
            if depth == 1: cur = ''; i += 1; continue
        if not inq and c == ')':
            depth -= 1
            if depth == 0: out.append(cur); i += 1; continue
        if depth >= 1: cur += c
        i += 1
    return out

def split_cols(t):
    cols, cur, inq, i = [], '', False, 0
    while i < len(t):
        c = t[i]
        if c == '\\' and inq: cur += t[i:i+2]; i += 2; continue
        if c == "'": inq = not inq; cur += c; i += 1; continue
        if c == ',' and not inq: cols.append(cur.strip()); cur = ''; i += 1; continue
        cur += c; i += 1
    cols.append(cur.strip())
    return cols

def unesc(s):  # 去外層單引號 + 還原 \" 等
    if s.startswith("'") and s.endswith("'"): s = s[1:-1]
    return re.sub(r'\\(.)', r'\1', s)

def sqlq(s):
    return s.replace('\\', '\\\\').replace("'", "\\'")

# 解析 pages_i18n：欄位序 pageID,lngID,banner,banner_desc,...
stmt = extract_statement(sql, "INSERT INTO `pages_i18n`")
body = stmt[stmt.find('VALUES') + 6:]

pairs = []      # (file_id, desc_obj, pageID, lng, idx)
clear_descs = set()
for tup in split_tuples(body):
    cols = split_cols(tup)
    if len(cols) < 4: continue
    pid, lng = cols[0].strip(), cols[1].strip().strip("'")
    try: banner = json.loads(unesc(cols[2]))
    except Exception: banner = None
    try: descs = json.loads(unesc(cols[3]))
    except Exception: descs = None
    if not isinstance(banner, list) or not isinstance(descs, list): continue
    # zip by index：file_id ↔ desc
    matched = 0
    for i, fid in enumerate(banner):
        if not (isinstance(fid, str) and fid.isdigit()): continue   # 只處理已 file_id 化的
        if i < len(descs) and descs[i]:
            pairs.append((fid, descs[i], pid, lng, i)); matched += 1
    if matched > 0:
        clear_descs.add(cols[3].strip())   # 這列的 banner_desc 原始值（待清空）

# 報告
print("=" * 64)
print(f"banner_desc → uploaded_files.content  ({station})")
print("=" * 64)
print(f"可搬移的 (file_id ↔ 描述) 配對：{len(pairs)}")
print(f"待清空 banner_desc 的列：{len(clear_descs)}")
print("\n--- 範例(前5) ---")
for fid, desc, pid, lng, idx in pairs[:5]:
    title = ''
    if isinstance(desc, dict):
        t = desc.get('title'); title = (t[0] if isinstance(t, list) and t else t) or ''
    print(f"  file_id={fid}  (page {pid}/{lng} #{idx})  title={str(title)[:40]}")

if not APPLY:
    print("\n(dry-run) 加 --apply 產出 UPDATE SQL")
    sys.exit(0)

# 產出 UPDATE SQL
lines = ["-- #2 遷移：banner_desc → uploaded_files.content\n"]
for fid, desc, pid, lng, idx in pairs:
    cj = json.dumps(desc, ensure_ascii=False, separators=(',', ':'))
    lines.append(f"UPDATE `uploaded_files` SET `content`='{sqlq(cj)}' WHERE `file_id`='{fid}';")
lines.append("\n-- banner_desc 退役（描述已移到 content）：清空已搬移列的 banner_desc")
lines.append("UPDATE `pages_i18n` SET `banner_desc`='[]' WHERE `banner_desc`!='' AND `banner_desc`!='[]';")
out = os.path.join(station, 'docs/ai-agents/project-init/migrate-banner-desc.sql')
os.makedirs(os.path.dirname(out), exist_ok=True)
open(out, 'w', encoding='utf-8').write("\n".join(lines) + "\n")
print(f"\n[--apply] UPDATE SQL 產出：{out}")
print(f"  uploaded_files content UPDATE：{len(pairs)} 筆")
print("  （看過後可附到 migrated.sql 末端或單獨在新 DB 執行）")