#!/usr/bin/env python3
"""Migration script to add RoomTypes fields to Reservation table.

This migration adds three optional fields to the reservations table:
- room_type_code: String (max 8 chars)
- room_classification_code: String (numeric pattern)
- room_type: String (enum: 1-5)

This script can be run manually before starting the server, or the changes
will be applied automatically when the server starts via Base.metadata.create_all.
"""

import asyncio
import sys
from pathlib import Path

# Add parent directory to path so we can import alpine_bits_python
sys.path.insert(0, str(Path(__file__).parent.parent.parent))

from sqlalchemy import inspect, text
from sqlalchemy.ext.asyncio import create_async_engine

from alpine_bits_python.config_loader import load_config
from alpine_bits_python.db import get_database_url
from alpine_bits_python.logging_config import get_logger, setup_logging

_LOGGER = get_logger(__name__)


async def check_columns_exist(engine, table_name: str, columns: list[str]) -> dict[str, bool]:
    """Check which columns exist in the table.

    Returns a dict mapping column name to whether it exists.
    """
    async with engine.connect() as conn:
        def _check(connection):
            inspector = inspect(connection)
            existing_cols = [col['name'] for col in inspector.get_columns(table_name)]
            return {col: col in existing_cols for col in columns}

        result = await conn.run_sync(_check)
        return result


async def add_room_types_columns(engine):
    """Add RoomTypes columns to reservations table if they don't exist."""
    from alpine_bits_python.db import Base

    table_name = "reservations"
    columns_to_add = ["room_type_code", "room_classification_code", "room_type"]

    # First, ensure the table exists by creating all tables if needed
    _LOGGER.info("Ensuring database tables exist...")
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    _LOGGER.info("Database tables checked/created.")

    _LOGGER.info("Checking which columns need to be added to %s table...", table_name)

    # Check which columns already exist
    columns_exist = await check_columns_exist(engine, table_name, columns_to_add)

    columns_to_create = [col for col, exists in columns_exist.items() if not exists]

    if not columns_to_create:
        _LOGGER.info("All RoomTypes columns already exist in %s table. No migration needed.", table_name)
        return

    _LOGGER.info("Adding columns to %s table: %s", table_name, ", ".join(columns_to_create))

    # Build ALTER TABLE statements for missing columns
    # Note: SQLite supports ALTER TABLE ADD COLUMN but not ADD MULTIPLE COLUMNS
    async with engine.begin() as conn:
        for column in columns_to_create:
            sql = f"ALTER TABLE {table_name} ADD COLUMN {column} VARCHAR"
            _LOGGER.info("Executing: %s", sql)
            await conn.execute(text(sql))

    _LOGGER.info("Successfully added %d columns to %s table", len(columns_to_create), table_name)


async def main():
    """Run the migration."""
    try:
        # Load config
        config = load_config()
        setup_logging(config)
    except Exception as e:
        _LOGGER.warning("Failed to load config: %s. Using defaults.", e)
        config = {}

    _LOGGER.info("=" * 60)
    _LOGGER.info("Starting RoomTypes Migration")
    _LOGGER.info("=" * 60)

    # Get database URL
    database_url = get_database_url(config)
    _LOGGER.info("Database URL: %s", database_url.replace("://", "://***:***@").split("@")[-1])

    # Create engine
    engine = create_async_engine(database_url, echo=False)

    try:
        # Run migration
        await add_room_types_columns(engine)

        _LOGGER.info("=" * 60)
        _LOGGER.info("Migration completed successfully!")
        _LOGGER.info("=" * 60)

    except Exception as e:
        _LOGGER.exception("Migration failed: %s", e)
        sys.exit(1)
    finally:
        await engine.dispose()


if __name__ == "__main__":
    asyncio.run(main())
