"""
添加聊天室数字员工关联表的数据库迁移脚本
"""
import sqlite3

# 连接数据库
conn = sqlite3.connect('qa_system.db')
cursor = conn.cursor()

print("="*60)
print("数据库迁移：添加聊天室数字员工关联表")
print("="*60)

try:
    # 检查表是否已存在
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='room_employees'")
    table_exists = cursor.fetchone()
    
    if not table_exists:
        print("创建 room_employees 表...")
        cursor.execute("""
            CREATE TABLE room_employees (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                room_id INTEGER NOT NULL,
                employee_id INTEGER NOT NULL,
                FOREIGN KEY (room_id) REFERENCES chat_rooms (id),
                FOREIGN KEY (employee_id) REFERENCES digital_employees (id),
                UNIQUE(room_id, employee_id)
            )
        """)
        print("[OK] room_employees 表创建成功")
    else:
        print("[OK] room_employees 表已存在")
    
    conn.commit()
    print("\n" + "="*60)
    print("迁移完成！")
    print("="*60)
    
except Exception as e:
    print(f"[ERROR] 迁移失败: {e}")
    conn.rollback()

finally:
    conn.close()
