#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
修復 dt_translation_cache 資料表結構

問題：資料表欄位名稱與模型定義不一致
- content_hash → source_text_hash
- source_lang → source_language
- target_lang → target_language
"""

import sys
from pathlib import Path

project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

from app import create_app, db
from sqlalchemy import text


def run_migration():
    app = create_app()

    with app.app_context():
        print("=" * 60)
        print("修復 dt_translation_cache 資料表結構")
        print("=" * 60)

        try:
            # 1. 檢查當前結構
            print("\n[1/6] 檢查當前資料表結構...")
            result = db.session.execute(text('SHOW COLUMNS FROM dt_translation_cache'))
            current_columns = {row[0]: row for row in result}
            print(f"   ✓ 當前欄位: {', '.join(current_columns.keys())}")

            # 2. 備份資料
            print("\n[2/6] 備份現有資料...")
            db.session.execute(text('''
                CREATE TABLE IF NOT EXISTS dt_translation_cache_backup_20251001
                AS SELECT * FROM dt_translation_cache
            '''))
            db.session.commit()

            backup_count = db.session.execute(
                text('SELECT COUNT(*) FROM dt_translation_cache_backup_20251001')
            ).scalar()
            print(f"   ✓ 已備份 {backup_count} 筆記錄")

            # 3. 重新命名欄位：content_hash → source_text_hash
            if 'content_hash' in current_columns and 'source_text_hash' not in current_columns:
                print("\n[3/6] 重新命名 content_hash → source_text_hash...")
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    CHANGE COLUMN content_hash source_text_hash VARCHAR(64) NOT NULL COMMENT '來源文字hash'
                '''))
                db.session.commit()
                print("   ✓ 已重新命名")
            else:
                print("\n[3/6] 跳過（已經是 source_text_hash）")

            # 4. 重新命名欄位：source_lang → source_language
            if 'source_lang' in current_columns and 'source_language' not in current_columns:
                print("\n[4/6] 重新命名 source_lang → source_language...")
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    CHANGE COLUMN source_lang source_language VARCHAR(50) NOT NULL COMMENT '來源語言'
                '''))
                db.session.commit()
                print("   ✓ 已重新命名")
            else:
                print("\n[4/6] 跳過（已經是 source_language）")

            # 5. 重新命名欄位：target_lang → target_language
            if 'target_lang' in current_columns and 'target_language' not in current_columns:
                print("\n[5/6] 重新命名 target_lang → target_language...")
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    CHANGE COLUMN target_lang target_language VARCHAR(50) NOT NULL COMMENT '目標語言'
                '''))
                db.session.commit()
                print("   ✓ 已重新命名")
            else:
                print("\n[5/6] 跳過（已經是 target_language）")

            # 6. 刪除不需要的欄位
            print("\n[6/6] 清理多餘欄位...")

            # 檢查並刪除 quality_score
            if 'quality_score' in current_columns:
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    DROP COLUMN quality_score
                '''))
                print("   ✓ 已刪除 quality_score")

            # 檢查並刪除 hit_count
            if 'hit_count' in current_columns:
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    DROP COLUMN hit_count
                '''))
                print("   ✓ 已刪除 hit_count")

            # 檢查並刪除 last_used_at
            if 'last_used_at' in current_columns:
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    DROP COLUMN last_used_at
                '''))
                print("   ✓ 已刪除 last_used_at")

            db.session.commit()

            # 7. 重建索引和約束
            print("\n[7/7] 重建索引和約束...")

            # 先刪除舊的唯一約束（如果存在）
            try:
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    DROP INDEX idx_content_hash
                '''))
                print("   ✓ 已刪除舊索引 idx_content_hash")
            except:
                pass

            try:
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    DROP INDEX idx_source_lang_target_lang
                '''))
                print("   ✓ 已刪除舊索引 idx_source_lang_target_lang")
            except:
                pass

            # 建立新的唯一約束
            try:
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    ADD UNIQUE KEY uk_cache (source_text_hash, source_language, target_language)
                '''))
                print("   ✓ 已建立唯一約束 uk_cache")
            except Exception as e:
                if 'Duplicate' not in str(e):
                    print(f"   ⚠ 約束建立警告: {str(e)}")

            # 建立語言索引
            try:
                db.session.execute(text('''
                    ALTER TABLE dt_translation_cache
                    ADD INDEX idx_languages (source_language, target_language)
                '''))
                print("   ✓ 已建立索引 idx_languages")
            except Exception as e:
                if 'Duplicate' not in str(e):
                    print(f"   ⚠ 索引建立警告: {str(e)}")

            db.session.commit()

            # 驗證最終結構
            print("\n" + "=" * 60)
            print("驗證最終資料表結構")
            print("=" * 60)

            result = db.session.execute(text('SHOW COLUMNS FROM dt_translation_cache'))
            final_columns = list(result)

            print(f"\n最終欄位列表 (共 {len(final_columns)} 個):")
            for col in final_columns:
                print(f"  - {col[0]:30} {col[1]:30} NULL={col[2]}")

            # 顯示索引
            print("\n索引:")
            result = db.session.execute(text('SHOW INDEX FROM dt_translation_cache'))
            for idx in result:
                print(f"  - {idx[2]:30} -> {idx[4]}")

            print("\n" + "=" * 60)
            print("✅ Migration 執行完成!")
            print("=" * 60)

        except Exception as e:
            db.session.rollback()
            print(f"\n❌ Migration 失敗: {str(e)}")
            print("\n可以使用備份表還原資料:")
            print("   DROP TABLE dt_translation_cache;")
            print("   CREATE TABLE dt_translation_cache AS SELECT * FROM dt_translation_cache_backup_20251001;")
            raise


if __name__ == '__main__':
    run_migration()