#!/usr/bin/env python3
"""
Database Initialization Script
Creates all tables and optionally seeds initial data
"""

import sys
from pathlib import Path

# Add current directory to Python path
current_dir = Path(__file__).parent
sys.path.insert(0, str(current_dir))

import logging
from sqlalchemy.exc import OperationalError
from database import init_db, engine, SessionLocal
from database.models import User, UserLanguagePreference
from utils.auth import get_password_hash
from config.settings import settings

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


def create_tables():
    """Create all database tables"""
    try:
        logger.info("Creating database tables...")
        init_db()
        logger.info("✓ Database tables created successfully")
        return True
    except OperationalError as e:
        logger.error(f"✗ Database connection failed: {e}")
        logger.error(f"  Please ensure PostgreSQL is running and database '{settings.db_name}' exists")
        logger.error(f"  Connection string: postgresql://{settings.db_user}@{settings.db_host}:{settings.db_port}/{settings.db_name}")
        return False
    except Exception as e:
        logger.error(f"✗ Failed to create tables: {e}")
        return False


def create_admin_user(db):
    """Create default admin user if it doesn't exist"""
    try:
        # Check if admin user exists
        admin = db.query(User).filter(User.username == "admin").first()
        
        if admin:
            logger.info("  Admin user already exists")
            return
        
        # Create admin user
        admin = User(
            username="admin",
            email="admin@vibeon.app",
            hashed_password=get_password_hash("admin123"),  # Change this in production!
            is_active=True,
            is_superuser=True
        )
        db.add(admin)
        db.commit()
        db.refresh(admin)
        
        # Create language preferences for admin
        prefs = UserLanguagePreference(
            user_id=admin.id,
            primary_language="en",
            secondary_languages=["es", "fr", "de"]
        )
        db.add(prefs)
        db.commit()
        
        logger.info("✓ Admin user created successfully")
        logger.info(f"  Username: admin")
        logger.info(f"  Password: admin123 (CHANGE THIS IN PRODUCTION!)")
        logger.info(f"  Email: admin@vibeon.app")
        
    except Exception as e:
        logger.error(f"✗ Failed to create admin user: {e}")
        db.rollback()


def seed_test_data(db):
    """Seed database with test data"""
    try:
        logger.info("Seeding test data...")
        
        # Check if test user exists
        test_user = db.query(User).filter(User.username == "testuser").first()
        
        if test_user:
            logger.info("  Test user already exists")
            return
        
        # Create test user
        test_user = User(
            username="testuser",
            email="test@vibeon.app",
            hashed_password=get_password_hash("test123"),
            is_active=True,
            is_superuser=False
        )
        db.add(test_user)
        db.commit()
        db.refresh(test_user)
        
        # Create language preferences
        prefs = UserLanguagePreference(
            user_id=test_user.id,
            primary_language="en",
            secondary_languages=["es", "fr"]
        )
        db.add(prefs)
        db.commit()
        
        logger.info("✓ Test data seeded successfully")
        logger.info(f"  Test user: testuser / test123")
        
    except Exception as e:
        logger.error(f"✗ Failed to seed test data: {e}")
        db.rollback()


def verify_database():
    """Verify database connection and tables"""
    try:
        from sqlalchemy import inspect
        
        inspector = inspect(engine)
        tables = inspector.get_table_names()
        
        logger.info(f"\nDatabase verification:")
        logger.info(f"  Database: {settings.db_name}")
        logger.info(f"  Host: {settings.db_host}:{settings.db_port}")
        logger.info(f"  Tables created: {len(tables)}")
        
        for table in tables:
            logger.info(f"    - {table}")
        
        return True
        
    except Exception as e:
        logger.error(f"✗ Database verification failed: {e}")
        return False


def main():
    """Main initialization function"""
    logger.info("="*60)
    logger.info("Vibeon Translator - Database Initialization")
    logger.info("="*60)
    logger.info(f"\nDatabase Configuration:")
    logger.info(f"  Name: {settings.db_name}")
    logger.info(f"  Host: {settings.db_host}")
    logger.info(f"  Port: {settings.db_port}")
    logger.info(f"  User: {settings.db_user}")
    logger.info("")
    
    # Step 1: Create tables
    if not create_tables():
        logger.error("\n✗ Database initialization failed!")
        sys.exit(1)
    
    # Step 2: Verify database
    if not verify_database():
        logger.error("\n✗ Database verification failed!")
        sys.exit(1)
    
    # Step 3: Seed initial data
    db = SessionLocal()
    try:
        logger.info("\nSeeding initial data...")
        create_admin_user(db)
        seed_test_data(db)
    finally:
        db.close()
    
    logger.info("\n" + "="*60)
    logger.info("✓ Database initialization completed successfully!")
    logger.info("="*60)
    logger.info("\nNext steps:")
    logger.info("  1. Change the admin password")
    logger.info("  2. Start the API: python main.py")
    logger.info("  3. Access docs at: http://localhost:8000/docs")
    logger.info("")


if __name__ == "__main__":
    main()