#!/usr/bin/env python3
"""
Database migration runner for content pipeline.
Executes SQL migrations from database/migrations/ directory.
"""

import logging
import sys
from pathlib import Path

# Add parent directory to path for imports
sys.path.insert(0, str(Path(__file__).parent.parent))

from database.connection import get_cursor, execute_query, test_connection

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

MIGRATIONS_DIR = Path(__file__).parent.parent / "database" / "migrations"


def get_executed_migrations() -> set[str]:
    """Get list of already executed migrations."""
    try:
        rows = execute_query(
            "SELECT migration_name FROM parketry_migrations"
        )
        return {row["migration_name"] for row in rows} if rows else set()
    except Exception:
        # Table doesn't exist yet
        return set()


def run_migration(migration_file: Path) -> bool:
    """Execute a single migration file."""
    migration_name = migration_file.stem
    logger.info(f"Running migration: {migration_name}")

    sql_content = migration_file.read_text()

    # Split by semicolons, but handle edge cases
    statements = []
    current_statement = []

    for line in sql_content.split("\n"):
        stripped = line.strip()
        if stripped.startswith("--") or not stripped:
            continue
        current_statement.append(line)
        if stripped.endswith(";"):
            statements.append("\n".join(current_statement))
            current_statement = []

    with get_cursor() as cursor:
        for statement in statements:
            statement = statement.strip()
            if statement:
                try:
                    cursor.execute(statement)
                    logger.debug(f"Executed: {statement[:80]}...")
                except Exception as e:
                    # Skip duplicate key errors for migration tracking
                    if "Duplicate entry" in str(e) and "parketry_migrations" in statement:
                        logger.debug(f"Migration already recorded: {migration_name}")
                    else:
                        raise

    logger.info(f"Migration completed: {migration_name}")
    return True


def run_all_migrations() -> int:
    """Run all pending migrations in order."""
    if not test_connection():
        logger.error("Database connection failed")
        return 1

    logger.info("Database connection successful")

    executed = get_executed_migrations()
    logger.info(f"Already executed: {len(executed)} migrations")

    migration_files = sorted(MIGRATIONS_DIR.glob("*.sql"))
    pending = [f for f in migration_files if f.stem not in executed]

    if not pending:
        logger.info("No pending migrations")
        return 0

    logger.info(f"Found {len(pending)} pending migrations")

    for migration_file in pending:
        try:
            run_migration(migration_file)
        except Exception as e:
            logger.error(f"Migration failed: {migration_file.name} - {e}")
            return 1

    logger.info("All migrations completed successfully")
    return 0


def main():
    """Main entry point."""
    logger.info("Starting migration runner")
    sys.exit(run_all_migrations())


if __name__ == "__main__":
    main()