#!/usr/bin/env python3
"""Inspect data in the remote cPanel MySQL database over SSH.

Uses credentials from the local ``.env``.

Usage::

    python3 scripts/remote_db.py tables
    python3 scripts/remote_db.py config
    python3 scripts/remote_db.py ctrader
    python3 scripts/remote_db.py tg
    python3 scripts/remote_db.py pairs
    python3 scripts/remote_db.py signal-log --limit 20
    python3 scripts/remote_db.py query "SELECT * FROM config_vars WHERE var_key='web_pin'"
"""

from __future__ import annotations

import argparse
import sys
from pathlib import Path

from dotenv import load_dotenv

from common import load_env, RemoteDB


def _get_db(args: argparse.Namespace) -> RemoteDB:
    env_path = Path(args.env)
    load_dotenv(env_path)
    env = load_env(env_path)
    return RemoteDB(args.ssh_host, env)


# ── Subcommands ────────────────────────────────────────────────────────────


def cmd_tables(args: argparse.Namespace) -> int:
    db = _get_db(args)
    print(db.query("SHOW TABLES;"))
    db.cleanup()
    return 0


def cmd_config(args: argparse.Namespace) -> int:
    db = _get_db(args)
    # Do not print raw secret values
    sql = (
        "SELECT var_key, "
        "CASE "
        "  WHEN var_value LIKE 'enc:%' THEN '***encrypted***' "
        "  WHEN LENGTH(var_value) > 32 THEN CONCAT(LEFT(var_value, 6), '***') "
        "  ELSE LEFT(var_value, 120) "
        "END AS var_value "
        "FROM config_vars ORDER BY var_key;"
    )
    print(db.query(sql))
    db.cleanup()
    return 0


def cmd_config_raw(args: argparse.Namespace) -> int:
    db = _get_db(args)
    print(db.query("SELECT * FROM config_vars ORDER BY var_key;"))
    db.cleanup()
    return 0


def cmd_ctrader(args: argparse.Namespace) -> int:
    db = _get_db(args)
    sql = (
        "SELECT account_id, broker_name, account_number, is_live, "
        " encrypted_payload IS NOT NULL AS has_token, created_at "
        "FROM ctrader_accounts ORDER BY created_at DESC;"
    )
    print(db.query(sql))
    db.cleanup()
    return 0


def cmd_tg(args: argparse.Namespace) -> int:
    db = _get_db(args)
    sql = (
        "SELECT id, name, phone, is_primary, is_authorized, status, created_at "
        "FROM tg_accounts ORDER BY id;"
    )
    print(db.query(sql))
    db.cleanup()
    return 0


def cmd_pairs(args: argparse.Namespace) -> int:
    db = _get_db(args)
    sql = (
        "SELECT id, enabled, source_title, dest_title, filter_type, "
        " price_augment, forward_via_bot, llm_enabled "
        "FROM channel_pairs ORDER BY id;"
    )
    print(db.query(sql))
    db.cleanup()
    return 0


def cmd_signal_log(args: argparse.Namespace) -> int:
    db = _get_db(args)
    sql = f"SELECT * FROM signal_log ORDER BY created_at DESC LIMIT {int(args.limit)};"
    print(db.query(sql))
    db.cleanup()
    return 0


def cmd_query(args: argparse.Namespace) -> int:
    db = _get_db(args)
    print(db.query(args.sql))
    db.cleanup()
    return 0


# ── CLI ─────────────────────────────────────────────────────────────────────


def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Inspect remote cPanel MySQL data")
    parser.add_argument("--env", default=".env", help="Path to local .env")
    parser.add_argument("--ssh-host", default="jnc", help="SSH host alias")

    sub = parser.add_subparsers(dest="command", required=True)

    sub.add_parser("tables", help="List database tables").set_defaults(func=cmd_tables)
    sub.add_parser("config", help="Show config_vars (secrets masked)").set_defaults(func=cmd_config)
    sub.add_parser("config-raw", help="Show raw config_vars").set_defaults(func=cmd_config_raw)
    sub.add_parser("ctrader", help="List linked cTrader accounts").set_defaults(func=cmd_ctrader)
    sub.add_parser("tg", help="List Telegram accounts").set_defaults(func=cmd_tg)
    sub.add_parser("pairs", help="List channel pairs").set_defaults(func=cmd_pairs)

    p_log = sub.add_parser("signal-log", help="Show recent signal_log rows")
    p_log.add_argument("--limit", type=int, default=20)
    p_log.set_defaults(func=cmd_signal_log)

    p_query = sub.add_parser("query", help="Run an arbitrary SQL query")
    p_query.add_argument("sql", help="SQL query")
    p_query.set_defaults(func=cmd_query)

    args = parser.parse_args(argv)
    return args.func(args)


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