#!/usr/bin/env python3
"""
Complete database initialization script for the infoscreen application.

This script:
1. Runs all Alembic migrations to create/update database schema
2. Creates default user groups and admin user
3. Initializes academic periods for Austrian schools

Usage:
    python initialize_database.py
"""

import os
import sys
import subprocess
from pathlib import Path

# Add workspace to Python path
sys.path.insert(0, '/workspace')

def run_command(cmd, description):
    """Run a command and handle errors."""
    print(f"\n🔄 {description}...")
    try:
        result = subprocess.run(cmd, shell=True, check=True, capture_output=True, text=True)
        if result.stdout:
            print(result.stdout)
        print(f"✅ {description} completed successfully")
        return True
    except subprocess.CalledProcessError as e:
        print(f"❌ {description} failed:")
        print(f"Error: {e}")
        if e.stdout:
            print(f"Stdout: {e.stdout}")
        if e.stderr:
            print(f"Stderr: {e.stderr}")
        return False

def check_database_connection():
    """Check if database is accessible."""
    print("\n🔍 Checking database connection...")
    try:
        from dotenv import load_dotenv
        from sqlalchemy import create_engine, text
        
        load_dotenv('/workspace/.env')
        
        DB_USER = os.getenv('DB_USER')
        DB_PASSWORD = os.getenv('DB_PASSWORD')
        DB_HOST = os.getenv('DB_HOST', 'db')
        DB_NAME = os.getenv('DB_NAME')
        DB_URL = f'mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}'
        
        engine = create_engine(DB_URL)
        with engine.connect() as conn:
            result = conn.execute(text('SELECT VERSION()'))
            version = result.scalar()
            print(f"✅ Connected to database: {version}")
            return True
    except Exception as e:
        print(f"❌ Database connection failed: {e}")
        return False

def check_current_migration():
    """Check current Alembic migration status."""
    print("\n🔍 Checking current migration status...")
    try:
        result = subprocess.run(
            "cd /workspace/server && alembic current", 
            shell=True, 
            capture_output=True, 
            text=True
        )
        if "head" in result.stdout:
            print("✅ Database is up to date")
            return True
        elif result.stdout.strip() == "":
            print("⚠️  No migrations applied yet")
            return False
        else:
            print(f"⚠️  Current migration: {result.stdout.strip()}")
            return False
    except Exception as e:
        print(f"❌ Migration check failed: {e}")
        return False

def main():
    """Main initialization function."""
    print("🚀 Starting database initialization for infoscreen application")
    print("=" * 60)
    
    # Check if we're in the right directory
    if not os.path.exists('/workspace/server/alembic.ini'):
        print("❌ Error: alembic.ini not found. Are you in the correct directory?")
        return False
    
    # Check database connection
    if not check_database_connection():
        print("\n❌ Cannot connect to database. Please ensure:")
        print("  - Database container is running")
        print("  - Environment variables are set correctly")
        print("  - Network connectivity is available")
        return False
    
    # Check current migration status
    needs_migration = not check_current_migration()
    
    # Run migrations if needed
    if needs_migration:
        if not run_command(
            "cd /workspace/server && alembic upgrade head",
            "Running Alembic migrations"
        ):
            return False
    else:
        print("⏭️  Skipping migrations (already up to date)")
    
    # Initialize default data
    if not run_command(
        "cd /workspace/server && python init_defaults.py",
        "Creating default groups and admin user"
    ):
        return False
    
    # Initialize academic periods
    if not run_command(
        "cd /workspace/server && python init_academic_periods.py",
        "Setting up academic periods"
    ):
        return False
    
    print("\n" + "=" * 60)
    print("🎉 Database initialization completed successfully!")
    print("\nNext steps:")
    print("  1. Start the application services")
    print("  2. Access the dashboard to verify everything works")
    print("  3. Login with admin credentials if needed")
    
    return True

if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)