#!/usr/bin/env python3
"""Import MikroTik hotspot/PPPoE data into PHPNuxBill. SQL-only on the billing DB.

MikroTik writes are limited to:
- walled-garden allow rules (add if missing)
- login.html upload (captive portal buy buttons -> PHPNuxBill)
No user/profile/WAN/PPP/RADIUS changes.
"""
from __future__ import annotations

import ftplib
import io
import json
import os
import re
import sys
from datetime import date, timedelta
from pathlib import Path

import paramiko

ROOT = Path(r"c:\laragon\www\light-otantik-nux")
sys.path.insert(0, str(ROOT / "scripts"))
from mk_client import connect as mk_connect, ok, replies_to_rows, talk, trap_msg  # noqa: E402

DUMP = ROOT / "deploy" / "prod-import" / "mikrotik-dump-20260815-142342.json"
LOGIN_HTML = ROOT / "deploy" / "nux-custom" / "hotspot" / "login.html"
SQL_OUT = ROOT / "deploy" / "prod-import" / "import.sql"

SSH_HOST = "10.15.15.89"
SSH_USER = "ubuntu"
SSH_PASS = "ubuntu"
MK_HOST = os.environ.get("MK_HOST", "10.15.15.1")
MK_USER = os.environ.get("MK_USER", "admin")
MK_PASS = os.environ.get("MK_PASS", "")
ROUTER_NAME = "WOYLA MKT"
PORTAL = "http://10.15.15.89/?_route=portal"
LOGIN_URL = "http://10.15.15.1/login"

HS_VOUCHER_PRICES = {
    "3H": ("300", 3, "Hrs", 2),
    "6H": ("400", 6, "Hrs", 2),
    "1J": ("500", 1, "Days", 2),
    "2J": ("800", 2, "Days", 2),
    "7J": ("2500", 7, "Days", 3),
    "1M": ("8000", 1, "Months", 3),
}

WALLED = [
    {"dst-host": "10.15.15.89", "comment": "phpnuxbill"},
    {"dst-host": "billing.woyla.net", "comment": "phpnuxbill"},
    {"dst-host": "*.woyla.net", "comment": "phpnuxbill"},
    {"dst-host": "mesomb.hachther.com", "comment": "phpnuxbill-mesomb"},
    {"dst-host": "*.mesomb.hachther.com", "comment": "phpnuxbill-mesomb"},
    {"dst-host": "*.mesomb.com", "comment": "phpnuxbill-mesomb"},
    {"dst-host": "business.mesomb.com", "comment": "phpnuxbill-mesomb"},
    {"dst-host": "wa.me", "comment": "phpnuxbill-support"},
    {"dst-host": "*.whatsapp.com", "comment": "phpnuxbill-support"},
    {"dst-host": "*.whatsapp.net", "comment": "phpnuxbill-support"},
]


def esc(s: object) -> str:
    return str(s if s is not None else "").replace("\\", "\\\\").replace("'", "\\'")


def parse_rate(rate: str | None) -> tuple[int, str, int, str, str]:
    if not rate:
        return 0, "Mbps", 0, "Mbps", ""
    parts = rate.split()
    first = parts[0]
    burst = " ".join(parts[1:])[:128]
    if "/" not in first:
        return 0, "Mbps", 0, "Mbps", burst

    def one(tok: str) -> tuple[int, str]:
        tok = tok.strip()
        m = re.match(r"^([0-9.]+)([KkMm])?$", tok)
        if not m:
            return 0, "Mbps"
        n = int(float(m.group(1)))
        u = (m.group(2) or "M").lower()
        return n, ("Kbps" if u == "k" else "Mbps")

    up_s, down_s = first.split("/", 1)
    up, uu = one(up_s)
    down, du = one(down_s)
    return up, uu, down, du, burst


def ssh() -> paramiko.SSHClient:
    client = paramiko.SSHClient()
    client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    client.connect(
        SSH_HOST,
        username=SSH_USER,
        password=SSH_PASS,
        timeout=20,
        allow_agent=False,
        look_for_keys=False,
    )
    return client


def sudo(client: paramiko.SSHClient, cmd: str, timeout: int = 120) -> tuple[int, str]:
    wrapped = f"sudo -S -p '' bash -lc {repr(cmd)}"
    stdin, stdout, stderr = client.exec_command(wrapped, timeout=timeout, get_pty=True)
    stdin.write(SSH_PASS + "\n")
    stdin.flush()
    out = stdout.read().decode("utf-8", "replace")
    code = stdout.channel.recv_exit_status()
    lines = [ln for ln in out.splitlines() if ln.strip() != SSH_PASS]
    return code, "\n".join(lines)


def build_sql(data: dict) -> str:
    yesterday = (date.today() - timedelta(days=1)).isoformat()
    lines = [
        "SET NAMES utf8mb4;",
        "START TRANSACTION;",
        f"UPDATE tbl_routers SET name='{esc(ROUTER_NAME)}', ip_address='{esc(MK_HOST)}', "
        f"username='{esc(MK_USER)}', password='{esc(MK_PASS)}', "
        f"description='CCR1009 production - API only, do not recreate profiles', "
        f"enabled=1, status='Online' WHERE id=1;",
        f"INSERT INTO tbl_pool (pool_name, local_ip, range_ip, routers) "
        f"SELECT 'hs-pool-10', '10.15.15.1', '10.15.15.2-10.15.15.240', '{esc(ROUTER_NAME)}' FROM DUAL "
        f"WHERE NOT EXISTS (SELECT 1 FROM tbl_pool WHERE pool_name='hs-pool-10');",
        "UPDATE tbl_plans SET enabled=0 WHERE name_plan LIKE 'TEST-%';",
        f"UPDATE tbl_appconfig SET value='{esc(LOGIN_URL)}' WHERE setting='mesomb_hotspot_login_url';",
    ]

    def bw_sql(bw_name: str, rate: str | None) -> None:
        up, uu, down, du, burst = parse_rate(rate)
        lines.append(
            "INSERT INTO tbl_bandwidth (name_bw, rate_down, rate_down_unit, rate_up, rate_up_unit, burst) "
            f"SELECT '{esc(bw_name)}', {down}, '{du}', {up}, '{uu}', '{esc(burst)}' FROM DUAL "
            f"WHERE NOT EXISTS (SELECT 1 FROM tbl_bandwidth WHERE name_bw='{esc(bw_name)}');"
        )

    def plan_sql(
        name: str,
        bw_name: str,
        ptype: str,
        device: str,
        price: str,
        validity: int,
        vunit: str,
        shared: int,
        prepaid: str,
        typebp: str = "Unlimited",
        time_limit: int = 0,
        time_unit: str = "Hrs",
        enabled: int = 1,
    ) -> None:
        pool = "hs-pool-10" if ptype == "PPPOE" else ""
        lines.append(
            "INSERT INTO tbl_plans (name_plan, id_bw, price, type, typebp, limit_type, time_limit, time_unit, "
            "data_limit, data_unit, validity, validity_unit, shared_users, routers, is_radius, pool, "
            "plan_expired, enabled, prepaid, plan_type, device) "
            f"SELECT '{esc(name)}', id, '{esc(price)}', '{ptype}', '{typebp}', 'Time_Limit', "
            f"{time_limit}, '{time_unit}', 0, 'MB', {validity}, '{vunit}', {shared}, "
            f"'{esc(ROUTER_NAME)}', 0, '{esc(pool)}', 0, {enabled}, '{prepaid}', 'Personal', '{device}' "
            f"FROM tbl_bandwidth WHERE name_bw='{esc(bw_name)}' "
            f"AND NOT EXISTS (SELECT 1 FROM tbl_plans WHERE name_plan='{esc(name)}' AND type='{ptype}');"
        )

    for p in data["hotspot_user_profile"]:
        name = p.get("name") or ""
        if not name:
            continue
        bw_name = f"HS {name}"[:255]
        bw_sql(bw_name, p.get("rate-limit"))
        shared = int(p.get("shared-users") or 1)
        if name in HS_VOUCHER_PRICES:
            price, val, unit, sh = HS_VOUCHER_PRICES[name]
            tlimit = val if unit == "Hrs" else (val * 24 if unit == "Days" else 0)
            tunit = "Hrs" if unit in ("Hrs", "Days") else "Hrs"
            if unit == "Days":
                tlimit = val * 24
            if unit == "Months":
                tlimit = 0
            plan_sql(
                name, bw_name, "Hotspot", "MikrotikHotspot", price, val, unit, sh or shared,
                "yes", "Limited" if unit in ("Hrs", "Days") else "Unlimited",
                tlimit, tunit, 1,
            )
        elif name == "Trial":
            plan_sql(name, bw_name, "Hotspot", "MikrotikHotspot", "0", 5, "Mins", shared, "no", "Limited", 5, "Mins", 1)
        else:
            plan_sql(name, bw_name, "Hotspot", "MikrotikHotspot", "0", 30, "Days", shared, "no")

    for p in data["ppp_profile"]:
        name = p.get("name") or ""
        if name in ("", "default", "default-encryption"):
            continue
        bw_name = f"PPP {name}"[:255]
        bw_sql(bw_name, p.get("rate-limit"))
        plan_sql(name, bw_name, "PPPOE", "MikrotikPppoe", "0", 30, "Days", 1, "no")

    hs_names = {u.get("name", "") for u in data["hotspot_users"]}
    hs_lower = {n.lower() for n in hs_names if n}

    def customer_sql(
        username: str,
        password: str,
        fullname: str,
        service: str,
        disabled: bool,
        pppoe_user: str = "",
        pppoe_pass: str = "",
        phone: str = "0",
    ) -> None:
        status = "Disabled" if disabled else "Active"
        email = f"{username.replace(' ', '_')}@imported.woyla.local"[:128]
        lines.append(
            "INSERT INTO tbl_customers (username, password, pppoe_username, pppoe_password, fullname, "
            "phonenumber, email, account_type, balance, service_type, auto_renewal, status, created_by) "
            f"SELECT '{esc(username)}', '{esc(password)}', '{esc(pppoe_user)}', '{esc(pppoe_pass)}', "
            f"'{esc(fullname)[:45]}', '{esc(phone)[:20]}', '{esc(email)}', 'Personal', 0.00, '{service}', "
            f"0, '{status}', 1 FROM DUAL "
            f"WHERE NOT EXISTS (SELECT 1 FROM tbl_customers WHERE username='{esc(username)}');"
        )

    def recharge_sql(cust_user: str, plan_name: str, ptype: str, disabled: bool) -> None:
        status = "off" if disabled else "on"
        exp = yesterday if disabled else "2099-12-31"
        tm = "00:00:00" if disabled else "23:59:59"
        lines.append(
            "INSERT INTO tbl_user_recharges (customer_id, username, plan_id, namebp, recharged_on, "
            "recharged_time, expiration, time, status, method, routers, type, admin_id) "
            f"SELECT c.id, c.username, p.id, p.name_plan, CURDATE(), CURTIME(), '{exp}', '{tm}', "
            f"'{status}', 'import-mikrotik', '{esc(ROUTER_NAME)}', '{ptype}', 1 "
            f"FROM tbl_customers c JOIN tbl_plans p ON p.name_plan='{esc(plan_name)}' AND p.type='{ptype}' "
            f"WHERE c.username='{esc(cust_user)}' "
            f"AND NOT EXISTS (SELECT 1 FROM tbl_user_recharges WHERE username='{esc(cust_user)}' AND plan_id=p.id);"
        )

    for u in data["hotspot_users"]:
        name = u.get("name") or ""
        if not name or u.get("default") == "true" or name == "default-trial":
            continue
        profile = u.get("profile") or "default"
        disabled = u.get("disabled") == "true"
        fullname = (u.get("comment") or name).split("|")[0].strip() or name
        phone = name if name.isdigit() else "0"
        customer_sql(name, u.get("password") or name, fullname, "Hotspot", disabled, phone=phone)
        recharge_sql(name, profile, "Hotspot", disabled)

    for u in data["ppp_secret"]:
        name = u.get("name") or ""
        if not name:
            continue
        profile = u.get("profile") or "default"
        disabled = u.get("disabled") == "true"
        fullname = (u.get("comment") or name).split("|")[0].strip() or name
        cust_user = f"{name}_pppoe" if name.lower() in hs_lower else name
        customer_sql(
            cust_user,
            u.get("password") or name,
            fullname if cust_user == name else f"{fullname} (PPPoE)",
            "PPPoE",
            disabled,
            pppoe_user=name,
            pppoe_pass=u.get("password") or name,
        )
        recharge_sql(cust_user, profile, "PPPOE", disabled)

    lines.append("COMMIT;")
    lines.append("SELECT 'routers' AS t, COUNT(*) c FROM tbl_routers;")
    lines.append("SELECT 'plans' AS t, type, COUNT(*) c FROM tbl_plans GROUP BY type;")
    lines.append("SELECT 'customers' AS t, service_type, COUNT(*) c FROM tbl_customers GROUP BY service_type;")
    lines.append("SELECT 'recharges' AS t, type, status, COUNT(*) c FROM tbl_user_recharges GROUP BY type, status;")
    return "\n".join(lines) + "\n"


def add_walled_garden() -> None:
    sock = mk_connect()
    existing = replies_to_rows(talk(sock, ["/ip/hotspot/walled-garden/print"]))
    have = {(e.get("dst-host", ""), e.get("dst-port", "")) for e in existing}
    for w in WALLED:
        key = (w["dst-host"], "")
        if any(h == w["dst-host"] for h, _p in have):
            print("SKIP walled-garden", w["dst-host"])
            continue
        args = ["/ip/hotspot/walled-garden/add", f"=dst-host={w['dst-host']}", "=action=allow", f"=comment={w['comment']}"]
        r = talk(sock, args)
        print("walled-garden", w["dst-host"], "OK" if ok(r) else trap_msg(r))
        if not ok(r):
            raise SystemExit(f"walled-garden failed: {w}")
    ip_exist = replies_to_rows(talk(sock, ["/ip/hotspot/walled-garden/ip/print"]))
    if not any(e.get("dst-address") == "10.15.15.89" for e in ip_exist):
        r = talk(
            sock,
            [
                "/ip/hotspot/walled-garden/ip/add",
                "=dst-address=10.15.15.89",
                "=action=accept",
                "=comment=phpnuxbill",
            ],
        )
        print("walled-garden-ip 10.15.15.89", "OK" if ok(r) else trap_msg(r))
        if not ok(r):
            raise SystemExit("walled-garden-ip failed")
    else:
        print("SKIP walled-garden-ip 10.15.15.89")
    sock.close()


def upload_login_html() -> None:
    html = LOGIN_HTML.read_bytes().replace(b"\r\n", b"\n")
    ftp = ftplib.FTP()
    ftp.encoding = "latin-1"
    ftp.connect(MK_HOST, 21, timeout=20)
    ftp.login(MK_USER, MK_PASS)
    ftp.set_pasv(True)
    ftp.cwd("woylaHotspot")
    ftp.storbinary("STOR login.html", io.BytesIO(html))
    ftp.quit()
    print("ftp login.html", len(html))


def main() -> int:
    data = json.loads(DUMP.read_text(encoding="utf-8"))
    sql = build_sql(data)
    SQL_OUT.write_text(sql, encoding="utf-8")
    print("sql bytes", SQL_OUT.stat().st_size, "lines", sql.count("\n"))

    client = ssh()
    try:
        sftp = client.open_sftp()
        sftp.put(str(SQL_OUT), "/home/ubuntu/woyla-import.sql")
        sftp.put(str(ROOT / "deploy" / "prod-import" / "phpnuxbill-before-prod.sql"), "/home/ubuntu/phpnuxbill-before-prod.sql")
        for i, (local, remote) in enumerate(
            [
                (ROOT / "deploy/nux-custom/system/controllers/portal.php", "/var/www/phpnuxbill/system/controllers/portal.php"),
                (ROOT / "deploy/nux-custom/system/paymentgateway/mesomb.php", "/var/www/phpnuxbill/system/paymentgateway/mesomb.php"),
            ]
        ):
            tmp = f"/home/ubuntu/woyla_upd_{i}_{local.name}"
            with sftp.file(tmp, "wb") as f:
                f.write(local.read_bytes().replace(b"\r\n", b"\n"))
        sftp.close()

        apply_sh = r"""#!/bin/bash
set -euo pipefail
install -m 600 /home/ubuntu/phpnuxbill-before-prod.sql /root/phpnuxbill-before-prod.sql
set -a
. /root/woyla.env
set +a
mysql -u nuxbill -p"$DB_PASS" phpnuxbill < /home/ubuntu/woyla-import.sql
install -m 644 /home/ubuntu/woyla_upd_0_portal.php /var/www/phpnuxbill/system/controllers/portal.php
install -m 644 /home/ubuntu/woyla_upd_1_mesomb.php /var/www/phpnuxbill/system/paymentgateway/mesomb.php
chown www-data:www-data /var/www/phpnuxbill/system/controllers/portal.php /var/www/phpnuxbill/system/paymentgateway/mesomb.php
find /var/www/phpnuxbill/ui/compiled -type f -delete || true
python3 - <<'PY'
from pathlib import Path
p = Path("/etc/apache2/sites-available/phpnuxbill.conf")
t = p.read_text()
t = t.replace("ServerAlias 192.168.88.21 localhost", "ServerAlias 10.15.15.89 billing.woyla.net localhost")
if "10.15.15.89" not in t:
    t = t.replace("ServerAlias", "ServerAlias 10.15.15.89", 1)
p.write_text(t)
print(t)
PY
apache2ctl configtest
systemctl reload apache2
echo === VERIFY ===
mysql -N -u nuxbill -p"$DB_PASS" phpnuxbill -e "SELECT id,name,ip_address,username FROM tbl_routers;"
mysql -N -u nuxbill -p"$DB_PASS" phpnuxbill -e "SELECT type,COUNT(*) FROM tbl_plans WHERE enabled=1 GROUP BY type;"
mysql -N -u nuxbill -p"$DB_PASS" phpnuxbill -e "SELECT service_type,COUNT(*) FROM tbl_customers GROUP BY service_type;"
mysql -N -u nuxbill -p"$DB_PASS" phpnuxbill -e "SELECT setting,value FROM tbl_appconfig WHERE setting='mesomb_hotspot_login_url';"
php /home/ubuntu/woyla-api-test.php
"""
        api_php = r"""<?php
$_SERVER["HTTP_HOST"] = "10.15.15.89";
$_SERVER["SERVER_NAME"] = "10.15.15.89";
$_SERVER["SCRIPT_NAME"] = "/index.php";
$_SERVER["REQUEST_URI"] = "/";
chdir("/var/www/phpnuxbill");
require "init.php";
$r = ORM::for_table("tbl_routers")->find_one(1);
echo "router ".$r["name"]." ".$r["ip_address"]."\n";
try {
    $iport = explode(":", $r["ip_address"]);
    $c = new PEAR2\Net\RouterOS\Client($iport[0], $r["username"], $r["password"]);
    echo "API_OK\n";
} catch (Exception $e) {
    echo "API_FAIL ".$e->getMessage()."\n";
    exit(1);
}
"""
        sftp = client.open_sftp()
        with sftp.file("/home/ubuntu/woyla-apply.sh", "w") as f:
            f.write(apply_sh.replace("\r\n", "\n"))
        with sftp.file("/home/ubuntu/woyla-api-test.php", "w") as f:
            f.write(api_php.replace("\r\n", "\n"))
        sftp.close()
        code, out = sudo(client, "chmod +x /home/ubuntu/woyla-apply.sh && bash /home/ubuntu/woyla-apply.sh", timeout=180)
        print(out)
        if code != 0:
            print("SERVER APPLY FAILED", code)
            return code
    finally:
        client.close()

    add_walled_garden()
    upload_login_html()
    print("DONE")
    return 0


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