"""
Init DB on Railway first boot.

Si DATABASE_PATH no existe (caso volumen Railway recien creado),
inicializa schema desde init_db.sql + aplica migrations + crea
usuario admin minimo (admin@ergonpy.com con password definida en
env ERGON_ADMIN_PASSWORD, fallback a 'change-me-on-first-login').

Idempotente: si la DB existe con tablas, retorna early sin tocar nada.

Uso:
    from init_railway_db import ensure_db_initialized
    ensure_db_initialized()  # llamar al inicio del servidor
"""

import os
import sqlite3
import sys
from pathlib import Path


def _table_exists(conn: sqlite3.Connection, name: str) -> bool:
    cur = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name = ?",
        (name,)
    )
    return cur.fetchone() is not None


def ensure_db_initialized() -> dict:
    """
    Asegura que la DB en DATABASE_PATH este inicializada.

    Retorna dict con info de la operacion:
        {ok: bool, action: 'created'|'existed'|'error', path: str, msg: str}
    """
    db_path = Path(os.environ.get("DATABASE_PATH", "./db/ergon.db"))
    db_dir = db_path.parent

    try:
        # Asegurar que el directorio existe (Railway volume /data ya existe,
        # pero por las dudas)
        db_dir.mkdir(parents=True, exist_ok=True)

        # Conectar (sqlite crea archivo vacio si no existe)
        conn = sqlite3.connect(str(db_path))
        conn.execute("PRAGMA foreign_keys = ON")

        had_obras = _table_exists(conn, "obras")

        # Cargar schema desde init_db.sql (idempotente: CREATE TABLE IF NOT EXISTS)
        sql_path = Path(__file__).parent / "init_db.sql"
        if not sql_path.exists():
            conn.close()
            return {
                "ok": False,
                "action": "error",
                "path": str(db_path),
                "msg": f"init_db.sql no encontrado en {sql_path}"
            }

        with open(sql_path, "r", encoding="utf-8") as f:
            schema_sql = f.read()
        conn.executescript(schema_sql)
        conn.commit()

        # Aplicar migrations en orden (cada una es idempotente)
        from importlib import import_module
        migrations_aplicadas = []
        for mig_name in [
            "migrate_f8",
            "migrate_f9",
            "migrate_f13",
            "migrate_f_marca",
            "migrate_parser_bitacora",
            "migrate_supabase_bridge",
            "migrate_trial",
        ]:
            try:
                mod = import_module(mig_name)
                # Cada migration tiene su propia signature; intentar patrones comunes
                if hasattr(mod, "migrate"):
                    mod.migrate(str(db_path))
                    migrations_aplicadas.append(mig_name)
                elif hasattr(mod, "main"):
                    # Algunas migrations son scripts main()
                    mod.main()
                    migrations_aplicadas.append(mig_name)
            except Exception as e:
                print(f"[init_railway_db] WARN migration {mig_name}: {e}", file=sys.stderr)

        # Crear usuario admin minimo via api_obras (que tiene bcrypt)
        try:
            from api_obras import create_usuario, get_usuario_by_email
            admin_email = "admin@ergonpy.com"
            admin_pwd = os.environ.get("ERGON_ADMIN_PASSWORD", "change-me-on-first-login")

            existing = get_usuario_by_email(conn, admin_email)
            if existing is None:
                create_usuario(conn, {
                    "email": admin_email,
                    "password": admin_pwd,
                    "nombre": "ERGON Admin",
                    "rol": "admin",
                })
                admin_created = True
            else:
                admin_created = False
        except Exception as e:
            print(f"[init_railway_db] WARN admin user: {e}", file=sys.stderr)
            admin_created = False

        conn.close()
        return {
            "ok": True,
            "action": "created" if not had_obras else "synced",
            "path": str(db_path),
            "msg": f"DB sincronizada. Migrations: {migrations_aplicadas}. Admin creado: {admin_created}"
        }

    except sqlite3.Error as e:
        return {
            "ok": False,
            "action": "error",
            "path": str(db_path),
            "msg": f"SQL error: {e}"
        }
    except Exception as e:
        return {
            "ok": False,
            "action": "error",
            "path": str(db_path),
            "msg": f"Error: {e}"
        }


if __name__ == "__main__":
    result = ensure_db_initialized()
    print(f"init_railway_db: {result}")
    sys.exit(0 if result["ok"] else 1)