"""
Database Migration Script for Categories Table
"""

from app import db, create_app
from sqlalchemy import text

def create_categories_table():
    """Create categories table"""
    
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        category_id VARCHAR(50) UNIQUE NOT NULL,
        name VARCHAR(100) NOT NULL,
        image VARCHAR(255),
        brand_id VARCHAR(50) NOT NULL,
        brand_name VARCHAR(100) NOT NULL,
        branch_id VARCHAR(50) NOT NULL,
        branch_name VARCHAR(100) NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
        FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
    );
    """
    
    create_index_sqls = [
        "CREATE INDEX IF NOT EXISTS idx_category_id ON categories(category_id);",
        "CREATE INDEX IF NOT EXISTS idx_category_brand_id ON categories(brand_id);",
        "CREATE INDEX IF NOT EXISTS idx_category_branch_id ON categories(branch_id);",
        "CREATE INDEX IF NOT EXISTS idx_category_name ON categories(name);"
    ]
    
    try:
        app = create_app()
        with app.app_context():
            with db.engine.connect() as connection:
                # Create the categories table
                connection.execute(text(create_table_sql))
                
                # Create each index separately (SQLite limitation)
                for sql in create_index_sqls:
                    connection.execute(text(sql))
                
                connection.commit()

            print("✓ Categories table created successfully!")
            print("✓ Indexes created successfully!")
            
            # Optional: add sample categories
            add_sample = input("\nAdd sample categories? (y/n): ")
            if add_sample.lower() == 'y':
                add_sample_categories()
            
    except Exception as e:
        print(f"✗ Error creating table: {e}")
        import traceback
        traceback.print_exc()


def add_sample_categories():
    """Add sample category data"""
    from app.models.models import Category, Brand, Branch
    
    brands = Brand.query.all()
    branches = Branch.query.all()
    
    if not brands or not branches:
        print("✗ Please create brands and branches first!")
        return
    
    sample_categories = [
        {'name': 'Beverages', 'brand_id': brands[0].brand_id if len(brands) > 0 else None},
        {'name': 'Snacks', 'brand_id': brands[1].brand_id if len(brands) > 1 else brands[0].brand_id},
        {'name': 'Chocolates', 'brand_id': brands[1].brand_id if len(brands) > 1 else brands[0].brand_id}
    ]
    
    try:
        for cat_data in sample_categories:
            if not cat_data['brand_id']:
                continue
                
            brand = Brand.query.filter_by(brand_id=cat_data['brand_id']).first()
            if not brand:
                continue
            
            branch = Branch.query.filter_by(branch_id=brand.branch_id).first()
            if not branch:
                continue
            
            category_id = Category.generate_category_id(cat_data['name'])
            
            category = Category(
                category_id=category_id,
                name=cat_data['name'],
                brand_id=brand.brand_id,
                brand_name=brand.name,
                branch_id=branch.branch_id,
                branch_name=branch.name,
                image=None
            )
            
            db.session.add(category)
        
        db.session.commit()
        print(f"✓ Added sample categories successfully!")
        
        # Display all created categories
        categories = Category.query.all()
        print("\nCreated categories:")
        for cat in categories:
            print(f"  - {cat.category_id}: {cat.name} @ {cat.brand_name} ({cat.branch_name})")
        
    except Exception as e:
        db.session.rollback()
        print(f"✗ Error adding sample data: {e}")
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    create_categories_table()