#!/usr/bin/env python3
"""
Jetour BI — Database Migration Runner

Usage:
  python migrate.py [DB_URL]

Defaults to DATABASE_URL env var, or:
  postgresql://postgres:password@host:5432/jetour-bi

Runs all .sql files in migrations/ in order, tracking applied
migrations in a _migrations table. Safe to run repeatedly —
only unapplied migrations are executed.
"""

import os, sys, re, psycopg
from pathlib import Path

MIGRATIONS_DIR = Path(__file__).parent / "migrations"
DB_URL = sys.argv[1] if len(sys.argv) > 1 else os.environ.get(
    "DATABASE_URL", "postgresql://postgres:postgres@localhost:5432/jetour-bi"
)


def main():
    conn = psycopg.connect(DB_URL)
    conn.autocommit = True
    cur = conn.cursor()

    # Ensure tracking table exists
    cur.execute("""
        CREATE TABLE IF NOT EXISTS _migrations (
            id          SERIAL PRIMARY KEY,
            filename    VARCHAR(200) NOT NULL UNIQUE,
            applied_at  TIMESTAMPTZ DEFAULT NOW()
        )
    """)

    # Get list of applied migrations
    cur.execute("SELECT filename FROM _migrations ORDER BY id")
    applied = {r[0] for r in cur.fetchall()}

    # Find and run pending .sql files
    sql_files = sorted(MIGRATIONS_DIR.glob("*.sql"))
    if not sql_files:
        print("No migration files found in", MIGRATIONS_DIR)
        return

    pending = 0
    for f in sql_files:
        if f.name in applied:
            continue
        print(f"  Applying: {f.name} ...", end=" ", flush=True)
        try:
            sql = f.read_text()
            cur.execute(sql)
            cur.execute("INSERT INTO _migrations (filename) VALUES (%s)", (f.name,))
            print("✓")
            pending += 1
        except Exception as e:
            print(f"✗ {e}")
            conn.rollback()
            sys.exit(1)

    if pending:
        print(f"\n✅ {pending} migration(s) applied")
    else:
        print("\n✓ All migrations already applied")

    # Create a default admin user if none exists
    cur.execute("SELECT count(*) FROM dashboard_users")
    if cur.fetchone()[0] == 0:
        import bcrypt
        pw = bcrypt.hashpw(b"admin", bcrypt.gensalt()).decode()
        cur.execute(
            "INSERT INTO dashboard_users (username, email, password_hash, role) VALUES (%s, %s, %s, %s)",
            ("admin", "admin@jetour.mu", pw, "admin"),
        )
        print("✓ Default admin user created (username: admin, password: admin)")

    conn.close()


if __name__ == "__main__":
    main()