#!/usr/bin/env python3
"""Create Wifi-Otantik zone Otantik_Hub_Nkoabang and matching plans (DB + WG)."""
from __future__ import annotations

import re
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent))
import vps_ssh

SQL_FILE = "/tmp/wo_nkoabang.sql"


def mysql(sql: str) -> str:
    # password via defaults file on the VPS, already used by the app
    cmd = (
        "mysql --defaults-extra-file=/var/www/wifi-otantik/.my.cnf wifi_otantik "
        "-N -e " + repr(sql)
    )
    code, out = vps_ssh.run(cmd)
    if code != 0:
        cmd2 = (
            "php -r "
            + repr(
                "include '/var/www/wifi-otantik/config.php'; "
                "$c=new mysqli($db_host,$db_user,$db_pass,$db_name); "
                "if($c->connect_error){fwrite(STDERR,$c->connect_error);exit(1);} "
                "$r=$c->query(" + repr(sql) + "); "
                "if($r===true){echo 'OK';exit;} "
                "if(!$r){fwrite(STDERR,$c->error);exit(1);} "
                "while($row=$r->fetch_row()){echo implode('\\t',$row).\"\\n\";}"
            )
        )
        # fallback parsed from config.php
        code, out = vps_ssh.run(
            "python3 - <<'PY'\n"
            "import re,subprocess\n"
            "t=open('/var/www/wifi-otantik/config.php').read()\n"
            "def g(k):\n"
            "    m=re.search(r\"\\$\"+k+r\"\\s*=\\s*['\\\"]([^'\\\"]+)\", t)\n"
            "    return m.group(1) if m else ''\n"
            "host=g('db_host') or 'localhost'\n"
            "user=g('db_user')\n"
            "pw=g('db_pass')\n"
            "name=g('db_name')\n"
            "sql="+repr(sql)+"\n"
            "r=subprocess.run(['mysql','-h',host,'-u',user,f'-p{pw}',name,'-N','-e',sql],capture_output=True,text=True)\n"
            "print(r.stdout,end='')\n"
            "print(r.stderr,end='',file=__import__('sys').stderr)\n"
            "raise SystemExit(r.returncode)\n"
            "PY"
        )
    if code != 0:
        raise SystemExit("mysql failed: " + out)
    return out


def main() -> None:
    out = mysql("SELECT id,name,slug,owner_id,ip_address FROM tbl_routers")
    print("ROUTERS:\n" + out)
    if "Otantik_Hub_Nkoabang" in out:
        print("zone already exists")
        return
    m = re.search(r"(\d+)\ttest enjoy\t", out, re.I)
    owner = "1"
    if m:
        # get owner of test enjoy
        own = mysql("SELECT owner_id FROM tbl_routers WHERE name='test enjoy' LIMIT 1")
        owner = (own.strip() or "1").split()[0]
    print("owner_id", owner)

    code, keys = vps_ssh.run("sudo -n /usr/local/sbin/otantik-wg genkey", sudo=False)
    if code != 0 or "usage:" in keys:
        code, keys = vps_ssh.run("sudo /usr/local/sbin/otantik-wg genkey", sudo=True)
    lines = [ln.strip() for ln in keys.splitlines() if ln.strip() and "usage" not in ln.lower()]
    # last two non-empty after sudo prompt noise
    priv = pub = ""
    for ln in reversed(lines):
        if len(ln) > 20 and " " not in ln:
            if not pub:
                pub = ln
            elif not priv:
                priv = ln
                break
    # genkey prints priv then pub
    clean = [ln for ln in lines if re.fullmatch(r"[A-Za-z0-9+/=]{40,}", ln)]
    if len(clean) >= 2:
        priv, pub = clean[0], clean[1]
    if not priv or not pub:
        raise SystemExit("wg genkey failed: " + keys[:500])

    used = mysql("SELECT wg_ip FROM tbl_otantik_peers")
    used_set = set(used.split())
    wg_ip = ""
    for i in range(2, 200):
        a, b = divmod(i, 254)
        b = (i % 254) + 1
        ip = f"10.88.{a}.{b}"
        if ip not in used_set:
            wg_ip = ip
            break
    print("wg_ip", wg_ip)
    code, addout = vps_ssh.run(
        f"sudo -n /usr/local/sbin/otantik-wg add {pub} {wg_ip}", sudo=False
    )
    if code != 0:
        code, addout = vps_ssh.run(
            f"sudo /usr/local/sbin/otantik-wg add {pub} {wg_ip}", sudo=True
        )
    print("wg add", addout.strip()[-80:])

    sqls = [
        f"""INSERT INTO tbl_routers (name, ip_address, username, password, description, enabled, status, owner_id, slug, ssid, ros_version, pppoe_interface)
VALUES ('Otantik_Hub_Nkoabang', '{wg_ip}', 'admin', 'demopass', 'wifi-otantik', 1, 'Offline', {int(owner)}, 'otantik-hub-nkoabang', 'Otantik Hub Nkoabang', '7', 'hs-bridge')""",
    ]
    mysql(sqls[0].replace("\n", " "))
    rid = mysql("SELECT id FROM tbl_routers WHERE slug='otantik-hub-nkoabang'").strip()
    print("router_id", rid)
    mysql(
        f"""INSERT INTO tbl_otantik_peers (router_id, owner_id, wg_ip, public_key, private_key, status, created_at)
VALUES ({int(rid)}, {int(owner)}, '{wg_ip}', '{pub}', '{priv}', 'pending', NOW())"""
    )
    pid = mysql("SELECT id FROM tbl_otantik_peers WHERE router_id=" + rid).strip()
    print("peer_id", pid)

    hs = [
        ("01 H", 200, 1, "Hrs", 10, 10, 2),
        ("1 JOUR", 500, 1, "Days", 10, 10, 2),
        ("3 JOURS", 1000, 3, "Days", 10, 10, 2),
        ("7 JOURS", 2000, 7, "Days", 10, 10, 2),
        ("30 JOURS", 5000, 30, "Days", 10, 10, 2),
        ("trial", 0, 10, "Mins", 10, 10, 1),
    ]
    ppp = [
        ("pppoe-profile", 5000, 30, "Days", 4, 1),
        ("Business", 10000, 30, "Days", 10, 2),
        ("BAMi", 5000, 30, "Days", 6, 2),
        ("Home", 7500, 30, "Days", 8, 2),
        ("Premium", 15000, 30, "Days", 16, 3),
        ("NoLimit", 25000, 30, "Days", 300, 100),
        ("expire", 500, 1, "Days", 1, 2),
    ]
    for name, price, val, unit, down, up, shared in hs:
        mysql(
            f"""INSERT INTO tbl_bandwidth (name_bw, rate_down, rate_down_unit, rate_up, rate_up_unit, burst)
VALUES ('{name}-Otantik_Hub_Nkoabang', {down}, 'Mbps', {up}, 'Mbps', '')"""
        )
        bwid = mysql(
            f"SELECT id FROM tbl_bandwidth WHERE name_bw='{name}-Otantik_Hub_Nkoabang' ORDER BY id DESC LIMIT 1"
        ).strip()
        tl, tu = val, unit
        if unit == "Days":
            tl, tu = val * 24, "Hrs"
        mysql(
            f"""INSERT INTO tbl_plans (name_plan, id_bw, price, price_old, type, typebp, limit_type, time_limit, time_unit, data_limit, data_unit, validity, validity_unit, shared_users, routers, is_radius, enabled, prepaid, device)
VALUES ('{name}', {int(bwid)}, '{price}', '', 'Hotspot', 'Unlimited', 'Time_Limit', {tl}, '{tu}', 0, 'MB', {val}, '{unit}', {shared}, 'Otantik_Hub_Nkoabang', 0, 1, 'yes', 'MikrotikHotspot')"""
        )
        print("plan hs", name)
    for name, price, val, unit, down, up in ppp:
        mysql(
            f"""INSERT INTO tbl_bandwidth (name_bw, rate_down, rate_down_unit, rate_up, rate_up_unit, burst)
VALUES ('{name}-Otantik_Hub_Nkoabang', {down}, 'Mbps', {up}, 'Mbps', '')"""
        )
        bwid = mysql(
            f"SELECT id FROM tbl_bandwidth WHERE name_bw='{name}-Otantik_Hub_Nkoabang' ORDER BY id DESC LIMIT 1"
        ).strip()
        tl = val * 24 if unit == "Days" else val
        tu = "Hrs" if unit == "Days" else unit
        mysql(
            f"""INSERT INTO tbl_plans (name_plan, id_bw, price, price_old, type, typebp, limit_type, time_limit, time_unit, data_limit, data_unit, validity, validity_unit, shared_users, routers, is_radius, pool, enabled, prepaid, device)
VALUES ('{name}', {int(bwid)}, '{price}', '', 'PPPOE', 'Unlimited', 'Time_Limit', {tl}, '{tu}', 0, 'MB', {val}, '{unit}', 1, 'Otantik_Hub_Nkoabang', 0, 'wo-pppoe-3', 1, 'yes', 'MikrotikPppoe')"""
        )
        print("plan ppp", name)
    print("DONE zone otantik-hub-nkoabang")


if __name__ == "__main__":
    main()