"""啟動時資料庫 schema 自動修復。

從 app.py 抽出的 repair_database_schema()。
歷史背景：早期 V9.53 起為了讓部署不必執行 migration，在啟動時動態檢查欄位並
ALTER TABLE 補上。長期應改走 Alembic / Migration script，但這個 fallback 還活著。

呼叫端：app.py 啟動末段呼叫一次。
"""
from utils.logger_manager import SystemLogger

_log = SystemLogger("SchemaRepair").get_logger()


def _ensure_column(engine, text_fn, table, column, ddl, post_sql=None):
    """補上單一欄位 helper：若不存在則 ALTER TABLE。"""
    from sqlalchemy import inspect
    inspector = inspect(engine)
    if table not in inspector.get_table_names():
        return False
    columns = [c['name'] for c in inspector.get_columns(table)]
    if column in columns:
        return False
    _log.warning(f"[Database] [Schema] ⚠️ 偵測到 {table} 表缺少 {column} 欄位 | 正在自動修復...")
    with engine.connect() as conn:
        conn.execute(text_fn(ddl))
        if post_sql:
            conn.execute(text_fn(post_sql))
        conn.commit()
    _log.info(f"[Database] [Schema] ✅ {table}.{column} 欄位修復完成")
    return True


def repair_database_schema():
    """啟動時資料庫結構自動修復（V9.53）。"""
    from database.manager import DatabaseManager
    from sqlalchemy import inspect, text
    from config import DATABASE_TYPE

    db = DatabaseManager()
    engine = db.engine

    try:
        # V9.96: SQLite WAL 模式（解決 database is locked）
        if DATABASE_TYPE == 'sqlite':
            with engine.connect() as conn:
                conn.execute(text("PRAGMA journal_mode=WAL"))
                conn.commit()
            _log.info("[Database] [WAL] ✅ SQLite WAL 模式已啟用 | 提升並發寫入效能")
        else:
            _log.info(f"[Database] ✅ 使用 {DATABASE_TYPE.upper()} 資料庫")

        inspector = inspect(engine)

        # V9.70: products 表
        _ensure_column(engine, text, 'products', 'image_url',
                       "ALTER TABLE products ADD COLUMN image_url TEXT")
        _ensure_column(engine, text, 'products', 'created_at',
                       "ALTER TABLE products ADD COLUMN created_at DATETIME",
                       post_sql="UPDATE products SET created_at = updated_at WHERE created_at IS NULL")

        # promo_products 表
        promo_columns = [
            ('url', "ALTER TABLE promo_products ADD COLUMN url TEXT"),
            ('image_url', "ALTER TABLE promo_products ADD COLUMN image_url TEXT"),
            ('previous_price', "ALTER TABLE promo_products ADD COLUMN previous_price INTEGER"),
            ('session_time_text', "ALTER TABLE promo_products ADD COLUMN session_time_text TEXT"),
            ('remain_qty', "ALTER TABLE promo_products ADD COLUMN remain_qty INTEGER"),
            ('discount_text', "ALTER TABLE promo_products ADD COLUMN discount_text TEXT"),
            ('page_type', "ALTER TABLE promo_products ADD COLUMN page_type TEXT DEFAULT 'edm'"),
        ]
        for col_name, ddl in promo_columns:
            _ensure_column(engine, text, 'promo_products', col_name, ddl)

        # V10.328: PChome/MOMO 比價診斷欄位。正式價差與待審嘗試都需可回溯
        # URL、圖片、庫存與 matcher 結構化原因，避免「待比對」無法被人工處理。
        json_type = 'JSONB' if DATABASE_TYPE == 'postgresql' else 'TEXT'
        competitor_price_columns = [
            ('competitor_product_url', "ALTER TABLE competitor_prices ADD COLUMN competitor_product_url TEXT"),
            ('competitor_image_url', "ALTER TABLE competitor_prices ADD COLUMN competitor_image_url TEXT"),
            ('competitor_stock', "ALTER TABLE competitor_prices ADD COLUMN competitor_stock INTEGER"),
            ('match_diagnostic_json', f"ALTER TABLE competitor_prices ADD COLUMN match_diagnostic_json {json_type}"),
            ('comparison_mode', "ALTER TABLE competitor_prices ADD COLUMN comparison_mode VARCHAR(40)"),
            ('hard_veto', "ALTER TABLE competitor_prices ADD COLUMN hard_veto BOOLEAN"),
            ('diagnostic_codes', f"ALTER TABLE competitor_prices ADD COLUMN diagnostic_codes {json_type}"),
        ]
        competitor_history_columns = [
            ('competitor_product_url', "ALTER TABLE competitor_price_history ADD COLUMN competitor_product_url TEXT"),
            ('competitor_image_url', "ALTER TABLE competitor_price_history ADD COLUMN competitor_image_url TEXT"),
            ('competitor_stock', "ALTER TABLE competitor_price_history ADD COLUMN competitor_stock INTEGER"),
            ('match_diagnostic_json', f"ALTER TABLE competitor_price_history ADD COLUMN match_diagnostic_json {json_type}"),
            ('comparison_mode', "ALTER TABLE competitor_price_history ADD COLUMN comparison_mode VARCHAR(40)"),
            ('hard_veto', "ALTER TABLE competitor_price_history ADD COLUMN hard_veto BOOLEAN"),
            ('diagnostic_codes', f"ALTER TABLE competitor_price_history ADD COLUMN diagnostic_codes {json_type}"),
        ]
        competitor_attempt_columns = [
            ('competitor_product_url', "ALTER TABLE competitor_match_attempts ADD COLUMN competitor_product_url TEXT"),
            ('competitor_image_url', "ALTER TABLE competitor_match_attempts ADD COLUMN competitor_image_url TEXT"),
            ('competitor_stock', "ALTER TABLE competitor_match_attempts ADD COLUMN competitor_stock INTEGER"),
            ('match_diagnostic_json', f"ALTER TABLE competitor_match_attempts ADD COLUMN match_diagnostic_json {json_type}"),
            ('comparison_mode', "ALTER TABLE competitor_match_attempts ADD COLUMN comparison_mode VARCHAR(40)"),
            ('hard_veto', "ALTER TABLE competitor_match_attempts ADD COLUMN hard_veto BOOLEAN"),
            ('diagnostic_codes', f"ALTER TABLE competitor_match_attempts ADD COLUMN diagnostic_codes {json_type}"),
        ]
        for table_columns in (competitor_price_columns, competitor_history_columns, competitor_attempt_columns):
            for col_name, ddl in table_columns:
                table_name = ddl.split()[2]
                _ensure_column(engine, text, table_name, col_name, ddl)

    except Exception as e:
        _log.error(f"[Database] [Schema] ❌ 資料庫修復失敗 | Error: {e}")