import asyncio
from sqlalchemy import text
from app.core.database import engine

async def migrate():
    async with engine.begin() as conn:
        print("Starting database migration...")
        
        # 1. Create material_categories table if it doesn't exist
        await conn.execute(text("""
            CREATE TABLE IF NOT EXISTS material_categories (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL UNIQUE,
                description VARCHAR(255),
                is_active BOOLEAN DEFAULT TRUE,
                created_by INT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                last_updated_by INT,
                last_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX (name)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """))
        print("Table 'material_categories' checked/created.")

        # 2. Update material_tb table
        # Check if columns exist before adding
        result = await conn.execute(text("SHOW COLUMNS FROM material_tb"))
        columns = [row[0] for row in result.fetchall()]

        if 'category_id' not in columns:
            print("Adding 'category_id' to 'material_tb'...")
            await conn.execute(text("ALTER TABLE material_tb ADD COLUMN category_id INT AFTER name"))
            await conn.execute(text("ALTER TABLE material_tb ADD CONSTRAINT fk_material_category FOREIGN KEY (category_id) REFERENCES material_categories(id)"))
        
        if 'supplier_location' not in columns:
            print("Adding 'supplier_location' to 'material_tb'...")
            await conn.execute(text("ALTER TABLE material_tb ADD COLUMN supplier_location VARCHAR(300) AFTER supplier"))

        if 'last_updated_by' not in columns:
            print("Adding audit fields to 'material_tb'...")
            await conn.execute(text("ALTER TABLE material_tb ADD COLUMN last_updated_by INT"))
            await conn.execute(text("ALTER TABLE material_tb ADD COLUMN last_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

        # 3. Optional: Move data from 'category' string to 'material_categories' table?
        # For now, let's just make sure the table structure is ready.
        
        print("Migration completed successfully.")

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