#!/usr/bin/env python3
"""Apply schema.sql (and migrate_existing.sql) to the remote cPanel MySQL DB.

Uses credentials from the local ``.env`` and runs ``mysql`` over SSH.

Usage::

    python3 scripts/apply_schema.py --env .env --ssh-host jnc

The script is safe to re-run: ``schema.sql`` uses ``CREATE IF NOT EXISTS``, and
``migrate_existing.sql`` is idempotent.
"""

from __future__ import annotations

import argparse
import sys
from pathlib import Path

from dotenv import load_dotenv

from common import load_env, ssh_run, ssh_upload


def _needs_env(env: dict[str, str], keys: list[str]) -> None:
    missing = [k for k in keys if not env.get(k)]
    if missing:
        print(f"Missing {', '.join(missing)} in .env", file=sys.stderr)
        sys.exit(1)


def _apply_file(host: str, sql_path: Path) -> None:
    """Upload and apply a single SQL file to the remote database."""
    if not sql_path.exists():
        print(f"Skipping {sql_path} (not found)")
        return

    print(f"Applying {sql_path} ...")
    sql = sql_path.read_bytes()

    r_upload = ssh_upload(host, "/tmp/apply_schema.sql", sql, mode="600")
    if r_upload.returncode != 0:
        print(r_upload.stderr.decode("utf-8", errors="ignore"), file=sys.stderr)
        sys.exit(1)

    r = ssh_run(host, "mysql --defaults-file=/tmp/apply_schema.cnf < /tmp/apply_schema.sql")
    stdout = r.stdout.decode("utf-8", errors="ignore").strip()
    stderr = r.stderr.decode("utf-8", errors="ignore").strip()
    if stdout:
        print(stdout)
    if stderr:
        print(stderr, file=sys.stderr)
    if r.returncode != 0:
        sys.exit(1)
    print(f"  {sql_path.name} applied")


def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Apply schema/migrations to remote cPanel MySQL")
    parser.add_argument("--env", default=".env", help="Path to local .env")
    parser.add_argument("--ssh-host", default="jnc", help="SSH host alias")
    parser.add_argument(
        "--skip-migrate", action="store_true",
        help="Skip migrate_existing.sql"
    )
    args = parser.parse_args(argv)

    env_path = Path(args.env)
    load_dotenv(env_path)
    env = load_env(env_path)
    _needs_env(env, ["MYSQL_HOST", "MYSQL_USER", "MYSQL_PASSWORD", "MYSQL_DB"])

    # Upload .my.cnf with database credentials.
    cnf = (
        "[client]\n"
        f"host={env['MYSQL_HOST']}\n"
        f"user={env['MYSQL_USER']}\n"
        f"password={env['MYSQL_PASSWORD']}\n"
        f"database={env['MYSQL_DB']}\n"
    ).encode()
    r = ssh_upload(args.ssh_host, "/tmp/apply_schema.cnf", cnf)
    if r.returncode != 0:
        print(r.stderr.decode("utf-8", errors="ignore"), file=sys.stderr)
        sys.exit(1)

    root = Path(__file__).resolve().parent.parent
    _apply_file(args.ssh_host, root / "schema.sql")
    if not args.skip_migrate:
        _apply_file(args.ssh_host, root / "migrate_existing.sql")

    ssh_run(args.ssh_host, "rm -f /tmp/apply_schema.cnf /tmp/apply_schema.sql")
    print("Schema up to date")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())