#!/usr/bin/env python3
"""
Database migration script to create tables and seed initial data
"""
import sys
import os
from datetime import datetime

# Add the current directory to the path
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from database import init_database, engine, Base
from services import UserService, OrderService, ProductService
from sqlalchemy import text
import logging

# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

def create_tables():
    """Create all database tables"""
    try:
        logger.info("Creating database tables...")
        Base.metadata.create_all(bind=engine)
        logger.info("✅ All tables created successfully")
        return True
    except Exception as e:
        logger.error(f"❌ Failed to create tables: {e}")
        return False

def seed_users():
    """Seed initial user data"""
    try:
        logger.info("Seeding initial users...")

        # Create admin user if doesn't exist
        if not UserService.get_user_by_username("admin"):
            UserService.create_user("admin", "admin@lemis.com", "admin123", "admin")
            logger.info("✅ Created admin user")

        # Create regular user if doesn't exist
        if not UserService.get_user_by_username("user"):
            UserService.create_user("user", "user@lemis.com", "user123", "user")
            logger.info("✅ Created regular user")

        return True
    except Exception as e:
        logger.error(f"❌ Failed to seed users: {e}")
        return False

def seed_sample_data():
    """Seed sample data for testing"""
    try:
        logger.info("Seeding sample data...")

        # Create sample products if they don't exist
        products_data = [
            {"sku": "PROD001", "name": "Produto Premium A", "cost_price": 45.00, "sale_price": 120.00, "stock_quantity": 50, "category": "Cosméticos"},
            {"sku": "PROD002", "name": "Produto Premium B", "cost_price": 35.50, "sale_price": 95.00, "stock_quantity": 75, "category": "Cuidados Pessoais"},
            {"sku": "PROD003", "name": "Produto Básico C", "cost_price": 8.50, "sale_price": 15.00, "stock_quantity": 200, "category": "Acessórios"}
        ]

        for product_data in products_data:
            if not ProductService.get_product_by_sku(product_data["sku"]):
                ProductService.create_product(**product_data)
                logger.info(f"✅ Created product: {product_data['sku']}")

        # Create sample orders if they don't exist
        orders_data = [
            {"order_number": "ML001", "customer_name": "João Silva", "customer_email": "joao@email.com", "total_amount": 299.90, "freight_amount": 15.00, "platform": "MercadoLivre"},
            {"order_number": "SP001", "customer_name": "Maria Santos", "customer_email": "maria@email.com", "total_amount": 199.90, "freight_amount": 12.00, "platform": "Shopee"},
            {"order_number": "MG001", "customer_name": "Pedro Costa", "customer_email": "pedro@email.com", "total_amount": 399.90, "freight_amount": 20.00, "platform": "Magalu"}
        ]

        for order_data in orders_data:
            if not OrderService.get_order_by_id(order_data["order_number"]):
                OrderService.create_order(**order_data)
                logger.info(f"✅ Created order: {order_data['order_number']}")

        return True
    except Exception as e:
        logger.error(f"❌ Failed to seed sample data: {e}")
        return False

def run_migration():
    """Run the complete migration process"""
    logger.info("🚀 Starting database migration...")

    # Step 1: Create tables
    if not create_tables():
        return False

    # Step 2: Seed users
    if not seed_users():
        return False

    # Step 3: Seed sample data
    if not seed_sample_data():
        return False

    logger.info("✅ Database migration completed successfully!")
    return True

def rollback_migration():
    """Rollback database migration (drop all tables)"""
    try:
        logger.warning("🔄 Rolling back database migration...")
        Base.metadata.drop_all(bind=engine)
        logger.info("✅ Database rollback completed")
        return True
    except Exception as e:
        logger.error(f"❌ Failed to rollback database: {e}")
        return False

if __name__ == "__main__":
    if len(sys.argv) > 1 and sys.argv[1] == "--rollback":
        rollback_migration()
    else:
        run_migration()