import asyncio
import os
import sys

# Ensure backend dir is in path
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "..")))

from common.sqlite_client import sqlite_client, init_sqlite, TABLE_ACTIVITIES

async def run_migration():
    print("Running migration for missing tables & columns...")
    
    # Run the core init_sqlite which will create missing tables based on the updated SQLiteClient list
    await init_sqlite()
    
    # Need to manually add the 'related_memo_id' column to cuccu_activities if it doesn't exist
    db = sqlite_client.db
    cursor = await db.execute(f"PRAGMA table_info({TABLE_ACTIVITIES})")
    rows = await cursor.fetchall()
    cols = [r[1] for r in rows]
    
    if "related_memo_id" not in cols:
        print(f"Adding 'related_memo_id' column to {TABLE_ACTIVITIES}...")
        await db.execute(f"ALTER TABLE {TABLE_ACTIVITIES} ADD COLUMN related_memo_id TEXT")
        await db.commit()
        print("Column added successfully.")
    else:
        print(f"Column 'related_memo_id' already exists in {TABLE_ACTIVITIES}.")
        
    await sqlite_client.close()
    print("Migration completed.")

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