"""
数据库迁移脚本：创建工具调用记录表
用于记录AI工具的调用历史
"""

from sqlalchemy import create_engine, text
from database import SQLALCHEMY_DATABASE_URL

def migrate():
    """执行数据库迁移"""
    engine = create_engine(SQLALCHEMY_DATABASE_URL)
    
    with engine.connect() as conn:
        # 检查表是否已存在
        result = conn.execute(text("""
            SELECT COUNT(*) as count 
            FROM sqlite_master 
            WHERE type='table' AND name='tool_call_logs'
        """))
        
        count = result.fetchone()[0]
        
        if count == 0:
            print("正在创建 tool_call_logs 表...")
            # 创建工具调用记录表
            conn.execute(text("""
                CREATE TABLE tool_call_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    tool_id INTEGER NOT NULL,
                    employee_id INTEGER NOT NULL,
                    user_id INTEGER,
                    room_id INTEGER,
                    input_params TEXT,
                    output_result TEXT,
                    status VARCHAR NOT NULL,
                    error_message TEXT,
                    execution_time INTEGER,
                    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (tool_id) REFERENCES ai_tools (id),
                    FOREIGN KEY (employee_id) REFERENCES digital_employees (id),
                    FOREIGN KEY (user_id) REFERENCES users (id),
                    FOREIGN KEY (room_id) REFERENCES chat_rooms (id)
                )
            """))
            
            # 创建索引以提高查询性能
            conn.execute(text("""
                CREATE INDEX idx_tool_call_logs_tool_id ON tool_call_logs(tool_id)
            """))
            
            conn.execute(text("""
                CREATE INDEX idx_tool_call_logs_employee_id ON tool_call_logs(employee_id)
            """))
            
            conn.execute(text("""
                CREATE INDEX idx_tool_call_logs_timestamp ON tool_call_logs(timestamp)
            """))
            
            conn.commit()
            print("✓ tool_call_logs 表创建成功")
            print("✓ 索引创建成功")
        else:
            print("✓ tool_call_logs 表已存在，无需迁移")

if __name__ == "__main__":
    print("开始数据库迁移...")
    try:
        migrate()
        print("数据库迁移完成！")
    except Exception as e:
        print(f"迁移失败: {e}")
        import traceback
        traceback.print_exc()
