#!/usr/bin/env python3
"""
Step 1: Rename 现有 version 标签到新命名方案。

- capability.version: 'howard_dedup' → 'dev_abstract'  (332)
- resource.version:   'howard_dedup' → 'dev_abstract'  (2539)
- strategy.version:   'howard_strategy_instance' → 'dev_abstract'  (26)
- knowledge.version:  'howard_strategy_instance' → 'dev_abstract'  (193); v0 保持不变

requirement 不动（跨版本共享）。
"""
import sys, time
from pathlib import Path
sys.path.insert(0, str(Path(__file__).parent.parent.parent))
from knowhub.knowhub_db.pg_capability_store import PostgreSQLCapabilityStore


RENAMES = [
    ('capability', 'howard_dedup', 'dev_abstract'),
    ('resource',   'howard_dedup', 'dev_abstract'),
    ('strategy',   'howard_strategy_instance', 'dev_abstract'),
    ('knowledge',  'howard_strategy_instance', 'dev_abstract'),
]


def main():
    s = PostgreSQLCapabilityStore()
    cur = s._get_cursor()
    try:
        cur.execute("SET statement_timeout = '60s'")
        # kill idle-in-tx（防卡锁）
        cur.execute("""SELECT pid FROM pg_stat_activity WHERE state='idle in transaction'
                       AND pid!=pg_backend_pid() AND datname=current_database()""")
        for r in cur.fetchall():
            pid = r['pid']
            print(f'kill idle-in-tx pid={pid}', flush=True)
            cur.execute("SELECT pg_terminate_backend(%s)", (pid,))

        for tbl, old, new in RENAMES:
            t0 = time.time()
            cur.execute(f"SELECT COUNT(*) c FROM {tbl} WHERE version=%s", (old,))
            before = cur.fetchone()['c']
            cur.execute(f"UPDATE {tbl} SET version=%s WHERE version=%s", (new, old))
            cur.execute(f"SELECT COUNT(*) c FROM {tbl} WHERE version=%s", (new,))
            after = cur.fetchone()['c']
            print(f'[{time.time()-t0:.1f}s] {tbl}: {old}→{new}  before={before}  after(new)={after}', flush=True)

        print('\n=== 全表 version 分布（验证）===', flush=True)
        for tbl in ['capability', 'resource', 'strategy', 'knowledge', 'requirement']:
            cur.execute(f"SELECT version, COUNT(*) n FROM {tbl} GROUP BY version ORDER BY n DESC")
            parts = [f'{r["version"]}={r["n"]}' for r in cur.fetchall()]
            print(f'  {tbl}: {", ".join(parts)}', flush=True)
    finally:
        cur.close(); s.close()


if __name__ == '__main__':
    main()