import asyncio
import sqlite3
import os
import sys

# Thêm đường dẫn project vào sys.path để import
sys.path.append(os.path.abspath(os.path.dirname(__file__)))

from config import MEMO_DB_PATH
from common.sqlite_client import init_sqlite, sqlite_client

async def migrate_missing_columns():
    """
    Tự động đọc cấu trúc mong đợi trong init_sqlite, so khớp với DB thật,
    nếu thiếu cột thì tự động chạy ALTER TABLE ADD COLUMN.
    """
    print(f"[*] Checking database at {MEMO_DB_PATH}...")
    
    # Đảm bảo các bảng cơ bản tồn tại
    await init_sqlite()
    db = sqlite_client.db

    # Đọc raw text từ sqlite_client.py để trích xuất schema
    client_path = os.path.join(os.path.dirname(__file__), "common", "sqlite_client.py")
    
    with open(client_path, 'r', encoding='utf-8') as f:
        code = f.read()

    # Regex cùi bắp để bắt đoạn tables = [ ... ]
    import ast
    
    # Parse mảng tables thủ công vì eval nguy hiểm, nhưng ta biết code nằm trong init_sqlite
    # Nhanh nhất là dùng sqlite3 để lấy pragma, so cột
    
    tables_definition = [
        ("cuccu_users", "id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT UNIQUE, password_hash TEXT, nickname TEXT, username TEXT UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_memos", "id INTEGER PRIMARY KEY AUTOINCREMENT, uid TEXT UNIQUE, creator_id TEXT, content TEXT, visibility TEXT DEFAULT 'PRIVATE', pinned BOOLEAN DEFAULT 0, row_status TEXT DEFAULT 'NORMAL', parent TEXT, payload TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, workspace_id TEXT DEFAULT 'PERSONAL', is_read BOOLEAN DEFAULT 0"),
        ("cuccu_teams", "id INTEGER PRIMARY KEY AUTOINCREMENT, owner_id TEXT, name TEXT, description TEXT, invite_code TEXT UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_team_members", "id INTEGER PRIMARY KEY AUTOINCREMENT, team_id INTEGER, user_id TEXT, role TEXT DEFAULT 'MEMBER', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(team_id, user_id)"),
        ("cuccu_team_memos", "id INTEGER PRIMARY KEY AUTOINCREMENT, team_id INTEGER, creator_id TEXT, space TEXT, content TEXT, visibility TEXT DEFAULT 'PRIVATE', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_team_comments", "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, team_id INTEGER, creator_id TEXT, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_team_reactions", "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, user_id TEXT, emoji TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(memo_id, user_id, emoji)"),
        ("cuccu_user_profiles", "user_id TEXT PRIMARY KEY, username TEXT, first_name TEXT, last_name TEXT, avatar_url TEXT, cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_activities", "id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT, creator_id TEXT, memo_id TEXT, related_memo_id TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_chat_history", "id INTEGER PRIMARY KEY, identity_key TEXT, message TEXT, is_human BOOLEAN, timestamp TIMESTAMP"),
        ("cuccu_attachments", "id INTEGER PRIMARY KEY AUTOINCREMENT, uid TEXT UNIQUE, memo_id TEXT, file_name TEXT, file_type TEXT, file_size INTEGER, blob_id TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_memo_relations", "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, related_memo_id TEXT, type TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_reactions", "id INTEGER PRIMARY KEY AUTOINCREMENT, content_id TEXT, creator_id TEXT, reaction_type TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_memo_embeddings", "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, content TEXT, tags TEXT, date_key TEXT, embedding TEXT, dim INTEGER, model TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_inbox", "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, message TEXT, is_read BOOLEAN DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_user_settings", "user_id TEXT PRIMARY KEY, settings_json TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_shortcuts", "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, title TEXT, payload TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_notifications", "id INTEGER PRIMARY KEY AUTOINCREMENT, recipient_id TEXT, sender_id TEXT, activity_id INTEGER, notification_type TEXT, status TEXT DEFAULT 'UNREAD', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_memo_versions", "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, version INTEGER, content TEXT, created_by TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_personal_access_tokens", "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, description TEXT, token_hash TEXT, expires_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_webhooks", "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, name TEXT, url TEXT, events TEXT, is_active BOOLEAN DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_documents", "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, title TEXT, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        ("cuccu_refresh_tokens", "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, token TEXT UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP")
    ]

    for table_name, schema in tables_definition:
        expected_cols = []
        for col_def in schema.split(","):
            col_def = col_def.strip()
            if col_def.upper().startswith("UNIQUE"):
                continue
            col_name = col_def.split(" ")[0]
            expected_cols.append((col_name, col_def))
            
        cursor = await db.execute(f"PRAGMA table_info({table_name})")
        rows = await cursor.fetchall()
        existing_cols = [r[1] for r in rows]
        
        for col_name, full_def in expected_cols:
            if col_name not in existing_cols:
                # Add missing column
                print(f"[+] Missing column '{col_name}' in table '{table_name}'. Migrating...")
                try:
                    await db.execute(f"ALTER TABLE {table_name} ADD COLUMN {full_def}")
                    print(f"    -> Added {col_name} to {table_name}")
                except Exception as e:
                    print(f"    -> Error adding column: {e}")

    await db.commit()
    print("[*] Migration completed!")
    await sqlite_client.close()

if __name__ == "__main__":
    asyncio.run(migrate_missing_columns())
