# -*- coding: utf-8 -*-
"""
資料庫遷移腳本：新增 notification_emails 欄位到 ts_temp_spec 表
執行方式：python migrate_add_email_column.py
"""

import os
import sys
from sqlalchemy import create_engine, text
from config import Config

def migrate_database():
    """執行資料庫遷移，新增 notification_emails 欄位"""
    
    # 使用 config.py 中的資料庫配置
    engine = create_engine(Config.SQLALCHEMY_DATABASE_URI)
    
    try:
        with engine.connect() as conn:
            # 檢查欄位是否已存在
            result = conn.execute(text("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = 'ts_temp_spec' 
                AND COLUMN_NAME = 'notification_emails'
                AND TABLE_SCHEMA = DATABASE()
            """))
            
            if result.fetchone():
                print("✓ notification_emails 欄位已存在，無需遷移")
                return True
            
            # 新增欄位
            print("正在新增 notification_emails 欄位到 ts_temp_spec 表...")
            conn.execute(text("""
                ALTER TABLE ts_temp_spec 
                ADD COLUMN notification_emails TEXT DEFAULT NULL
                COMMENT '通知郵件清單，以分號分隔'
            """))
            conn.commit()
            
            print("✓ 成功新增 notification_emails 欄位")
            
            # 驗證欄位是否成功新增
            result = conn.execute(text("""
                SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = 'ts_temp_spec' 
                AND COLUMN_NAME = 'notification_emails'
                AND TABLE_SCHEMA = DATABASE()
            """))
            
            column_info = result.fetchone()
            if column_info:
                print(f"✓ 欄位資訊：")
                print(f"  - 欄位名稱：{column_info[0]}")
                print(f"  - 資料類型：{column_info[1]}")
                print(f"  - 可否為空：{column_info[2]}")
                return True
            else:
                print("✗ 欄位新增後無法驗證")
                return False
                
    except Exception as e:
        print(f"✗ 遷移失敗：{str(e)}")
        return False

if __name__ == "__main__":
    print("=" * 50)
    print("資料庫遷移腳本")
    print("=" * 50)
    
    success = migrate_database()
    
    if success:
        print("\n✓ 資料庫遷移完成！")
        print("  現在可以使用新的郵件通知功能了。")
    else:
        print("\n✗ 資料庫遷移失敗，請檢查錯誤訊息。")
        sys.exit(1)