#!/usr/bin/env python3
"""Create nexus database (if missing) and all tables via SQLAlchemy init_db + migrations.

Run from project root:
    python3 scripts/bootstrap_database.py
"""
import asyncio
import os
import re
import sys
from pathlib import Path
from urllib.parse import unquote

ROOT = Path(__file__).resolve().parent.parent
sys.path.insert(0, str(ROOT))

# Load only NEXUS_* from .env (MYSQL_* is for MCP only)
env_path = ROOT / ".env"
if env_path.exists():
    for line in env_path.read_text(encoding="utf-8").splitlines():
        line = line.strip()
        if line and not line.startswith("#") and "=" in line:
            k, v = line.split("=", 1)
            k = k.strip()
            if k.startswith("NEXUS_"):
                os.environ.setdefault(k, v.strip())

URL_RE = re.compile(
    r"^mysql\+(?:aiomysql|pymysql)://([^:]+):([^@]+)@([^:/]+)(?::(\d+))?/([^?\s#]+)",
    re.IGNORECASE,
)

EXPECTED_TABLES = [
    "admins", "audit_logs", "command_logs", "db_credentials", "login_attempts",
    "nodes", "password_presets", "platforms", "push_retry_jobs", "push_schedules",
    "script_executions", "scripts", "servers", "settings", "ssh_sessions", "sync_logs",
]


_DB_NAME_RE = re.compile(r"^[A-Za-z0-9_]{1,64}$")


def parse_url() -> dict:
    url = os.environ.get("NEXUS_DATABASE_URL", "")
    m = URL_RE.match(url.strip())
    if not m:
        raise SystemExit(f"Invalid NEXUS_DATABASE_URL: {url!r}")
    user, password, host, port, database = m.groups()
    return {
        "host": host,
        "port": int(port or 3306),
        "user": unquote(user),
        "password": unquote(password),
        "database": unquote(database),
    }


async def ensure_database(cfg: dict) -> None:
    db_name = cfg["database"]
    if not _DB_NAME_RE.match(db_name):
        raise SystemExit(f"Invalid database name (allowed: [A-Za-z0-9_], max 64): {db_name!r}")

    import aiomysql

    conn = await aiomysql.connect(
        host=cfg["host"],
        port=cfg["port"],
        user=cfg["user"],
        password=cfg["password"],
        connect_timeout=15,
    )
    async with conn.cursor() as cur:
        await cur.execute(
            f"CREATE DATABASE IF NOT EXISTS `{cfg['database']}` "
            "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        )
    conn.close()
    print(f"OK: database `{cfg['database']}` exists")


async def init_schema() -> list[str]:
    from server.infrastructure.database.session import init_db
    from server.infrastructure.database.migrations import run_migrations
    from server.infrastructure.database.session import AsyncSessionLocal
    from sqlalchemy import text

    await init_db()
    print("OK: init_db() — tables created via SQLAlchemy metadata")

    await run_migrations()
    print("OK: run_migrations() — data + schema migrations")

    async with AsyncSessionLocal() as session:
        result = await session.execute(text("SHOW TABLES"))
        return sorted(row[0] for row in result.all())


async def main() -> int:
    cfg = parse_url()
    print(f"Bootstrap {cfg['user']}@{cfg['host']}:{cfg['port']}/{cfg['database']}")

    await ensure_database(cfg)
    tables = await init_schema()

    missing = [t for t in EXPECTED_TABLES if t not in tables]
    extra = [t for t in tables if t not in EXPECTED_TABLES]
    print(f"\nTables ({len(tables)}): {tables}")
    if missing:
        print("WARNING missing expected:", missing)
        return 1
    if extra:
        print("Extra tables:", extra)
    print("\nBootstrap complete.")
    return 0


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