#!/usr/bin/env python3
"""Apply sql/web_setup.sql on production VPS only (no full deploy)."""
import sys
from pathlib import Path

import paramiko

VPS_HOST = "65.75.210.95"
VPS_USER = "root"
VPS_PASS = "%8qd6oJx%PBB"
REMOTE_DIR = "/var/www/servidor/web"
LOCAL_SQL = Path(__file__).resolve().parent.parent / "sql" / "web_setup.sql"


def parse_env(text: str) -> dict[str, str]:
    values: dict[str, str] = {}
    for line in text.splitlines():
        line = line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        key, _, value = line.partition("=")
        values[key.strip()] = value.strip()
    return values


def main() -> int:
    if not LOCAL_SQL.is_file():
        print(f"Missing {LOCAL_SQL}")
        return 1

    client = paramiko.SSHClient()
    client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    print(f"Connecting to {VPS_HOST}...")
    client.connect(VPS_HOST, username=VPS_USER, password=VPS_PASS, timeout=20)
    print("Connected.")

    remote_sql = f"{REMOTE_DIR}/sql/web_setup.sql"
    sftp = client.open_sftp()
    try:
        try:
            sftp.mkdir(f"{REMOTE_DIR}/sql")
        except OSError:
            pass
        sftp.put(str(LOCAL_SQL), remote_sql)
        print(f"Uploaded {remote_sql}")
    finally:
        sftp.close()

    _, env_out, _ = client.exec_command(
        f"cat {REMOTE_DIR}/.env.production 2>/dev/null || true",
        timeout=15,
    )
    env = parse_env(env_out.read().decode("utf-8", errors="replace"))
    host = env.get("DATABASE_HOST", "127.0.0.1")
    user = env.get("DATABASE_USER", "")
    password = env.get("DATABASE_PASSWORD", "")
    database = env.get("DATABASE_NAME", "s4_Urban")
    if not user:
        print("DATABASE_USER not found in .env.production")
        client.close()
        return 1

    password_escaped = password.replace("'", "'\\''")
    migrate_cmd = (
        f"mysql -h {host} -u {user} -p'{password_escaped}' {database} "
        f"< {remote_sql} 2>&1"
    )
    print("Applying web_setup.sql...")
    _, migrate_out, migrate_err = client.exec_command(migrate_cmd, timeout=60)
    out = migrate_out.read().decode("utf-8", errors="replace")
    err = migrate_err.read().decode("utf-8", errors="replace")
    if out.strip():
        print(out.strip())
    if err.strip():
        print(err.strip())

    verify_cmd = (
        f"mysql -h {host} -u {user} -p'{password_escaped}' {database} "
        f"-e \"SHOW TABLES LIKE 'pending_registrations';\" 2>&1"
    )
    _, verify_out, _ = client.exec_command(verify_cmd, timeout=15)
    verify = verify_out.read().decode("utf-8", errors="replace")
    print(verify.strip())

    client.close()
    if "pending_registrations" in verify:
        print("OK: pending_registrations exists.")
        return 0
    print("ERROR: pending_registrations still missing.")
    return 1


if __name__ == "__main__":
    sys.exit(main())