app/db/init_db.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from ..core.config import settings
from ..core.security import get_password_hash
from datetime import datetime
from .models import Base, User, Product
import asyncio

def init_db():
    # Create synchronous engine for initialization
    engine = create_engine(
        settings.DATABASE_URL.replace("+asyncpg", ""),
        echo=True
    )
    
    # Create all tables
    Base.metadata.create_all(bind=engine)
    
    # Create session
    SessionLocal = sessionmaker(bind=engine)
    session = SessionLocal()
    
    try:
        # Create default admin user if not exists
        admin_user = session.query(User).filter_by(email="admin@example.com").first()
        if not admin_user:
            admin_user = User(
                email="admin@example.com",
                username="admin",
                full_name="System Administrator",
                hashed_password=get_password_hash("admin123"),  # Change in production
                is_active=True,
                is_superuser=True,
                roles=["admin"],
                created_at=datetime.utcnow()
            )
            session.add(admin_user)
            print("Created default admin user.")
        
        # Create default product categories as products
        categories = [
            "Soups & Stews", 
            "Rice Dishes", 
            "Swallow & Fufu", 
            "Snacks & Small Chops", 
            "Protein & Meat", 
            "Drinks"
        ]
        
        for category in categories:
            exists = session.query(Product).filter_by(name=category).first()
            if not exists:
                product = Product(
                    name=category,
                    description=f"Category: {category}",
                    price=0.0,  # Category products have zero price
                    category=category,
                    inventory_count=0,  # Categories don't have inventory
                    seller_id=admin_user.id if admin_user else 1,  # Link to admin user
                    created_at=datetime.utcnow()
                )
                session.add(product)
        
        print("Initialized product categories.")
        
        # Commit changes
        session.commit()
        
    except Exception as e:
        print(f"Error during initialization: {e}")
        session.rollback()
        raise
    finally:
        session.close()

if __name__ == "__main__":
    init_db()