"""
Database initialization script for FleetMind
Run this script to create the PostgreSQL database and tables
"""

import sys
from pathlib import Path

# Add parent directory to path to import database module
sys.path.insert(0, str(Path(__file__).parent.parent))

from database.connection import init_database, get_db_connection, test_connection
from database.schema import get_full_schema
import logging

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


def main():
    """Initialize the FleetMind PostgreSQL database"""
    try:
        logger.info("=" * 60)
        logger.info("FleetMind Database Initialization")
        logger.info("=" * 60)

        # Test connection first
        logger.info("\nStep 1: Testing PostgreSQL connection...")
        if not test_connection():
            logger.error("Failed to connect to PostgreSQL database")
            logger.error("Please check your .env file and ensure PostgreSQL is running")
            sys.exit(1)

        logger.info("✓ PostgreSQL connection successful")

        # Initialize database with full schema
        logger.info("\nStep 2: Creating database schema...")
        init_database()

        # Verify tables were created
        logger.info("\nStep 3: Verifying table creation...")
        conn = get_db_connection()
        cursor = conn.cursor()

        # Get list of all tables
        cursor.execute("""
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = 'public'
            ORDER BY table_name
        """)
        tables = cursor.fetchall()

        if tables:
            logger.info(f"✓ Successfully created {len(tables)} tables:")
            for table in tables:
                logger.info(f"  - {table['table_name']}")

                # Get column count for each table
                cursor.execute(f"""
                    SELECT COUNT(*) as column_count
                    FROM information_schema.columns
                    WHERE table_name = %s
                """, (table['table_name'],))
                col_count = cursor.fetchone()
                logger.info(f"    ({col_count['column_count']} columns)")
        else:
            logger.warning("No tables were created")

        # Specifically check orders table
        logger.info("\nStep 4: Verifying orders table...")
        cursor.execute("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns
            WHERE table_name = 'orders'
            ORDER BY ordinal_position
        """)
        columns = cursor.fetchall()

        if columns:
            logger.info(f"✓ Orders table created with {len(columns)} columns:")
            for col in columns[:10]:  # Show first 10 columns
                nullable = "NULL" if col['is_nullable'] == 'YES' else "NOT NULL"
                logger.info(f"  - {col['column_name']}: {col['data_type']} ({nullable})")

            if len(columns) > 10:
                logger.info(f"  ... and {len(columns) - 10} more columns")
        else:
            logger.error("✗ Orders table was not created")

        conn.close()

        logger.info("\n" + "=" * 60)
        logger.info("Database initialization completed successfully!")
        logger.info("=" * 60)
        logger.info("\nYou can now:")
        logger.info("  1. Test the database: python scripts/test_db.py")
        logger.info("  2. Start the application: python ui/app.py")

    except Exception as e:
        logger.error(f"\n✗ Database initialization failed: {e}")
        logger.error("\nTroubleshooting:")
        logger.error("  1. Ensure PostgreSQL is installed and running")
        logger.error("  2. Create a database named 'fleetmind' in PostgreSQL")
        logger.error("     Command: createdb fleetmind")
        logger.error("  3. Update .env file with correct database credentials")
        logger.error("  4. Verify PostgreSQL user has necessary permissions")
        sys.exit(1)


if __name__ == "__main__":
    main()