"""
Database migration script for PostgreSQL.

Usage:
    python -m scripts.migrate
"""

import asyncio
import os
import sys
from pathlib import Path

# Add project root to path
project_root = Path(__file__).resolve().parent.parent
sys.path.insert(0, str(project_root))

import asyncpg
from dotenv import load_dotenv

load_dotenv()


def get_database_url() -> str:
    """Get database URL from environment (required)."""
    url = os.getenv("DATABASE_URL")
    if not url:
        raise EnvironmentError(
            "DATABASE_URL environment variable is required. "
            "Set it in .env or docker-compose.yml. "
            "Example: postgresql://user:password@localhost:5432/dbname"
        )
    return url


async def run_migrations():
    """Run all SQL migrations in order."""
    database_url = get_database_url()

    print(f"Connecting to database...")

    try:
        conn = await asyncpg.connect(database_url)
    except Exception as e:
        print(f"Failed to connect to database: {e}")
        print(
            "Make sure PostgreSQL is running (use 'make up' or 'docker compose up -d db')"
        )
        sys.exit(1)

    try:
        # Create migrations tracking table
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS _migrations (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) NOT NULL UNIQUE,
                applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            )
        """)

        # Get list of applied migrations
        applied = await conn.fetch("SELECT name FROM _migrations ORDER BY id")
        applied_names = {row["name"] for row in applied}

        # Find migration files
        migrations_dir = project_root / "migrations"
        if not migrations_dir.exists():
            print("No migrations directory found")
            return

        migration_files = sorted(migrations_dir.glob("*.sql"))

        if not migration_files:
            print("No migration files found")
            return

        # Run pending migrations
        for migration_file in migration_files:
            migration_name = migration_file.name

            if migration_name in applied_names:
                print(f"  ✓ {migration_name} (already applied)")
                continue

            print(f"  → Applying {migration_name}...")

            # Read and execute migration
            sql = migration_file.read_text()

            try:
                async with conn.transaction():
                    await conn.execute(sql)

                    # Record migration
                    await conn.execute(
                        "INSERT INTO _migrations (name) VALUES ($1)", migration_name
                    )

                print(f"  ✓ {migration_name} applied successfully")

            except Exception as e:
                print(f"  ✗ {migration_name} failed: {e}")
                raise

        print("\nAll migrations complete!")

    finally:
        await conn.close()


def main():
    """Entry point."""
    print("Running database migrations...")
    asyncio.run(run_migrations())


if __name__ == "__main__":
    main()