#!/usr/bin/env python3
"""Rename test enjoy -> Otantik_Hub_Nkoabang and insert matching plans. Do not touch MikroTik profiles."""
import re
import subprocess
import sys

t = open("/var/www/wifi-otantik/config.php").read()


def g(k):
    m = re.search(r"\$" + k + r"\s*=\s*['\"]([^'\"]+)", t)
    return m.group(1) if m else ""


host = g("db_host") or "localhost"
user = g("db_user")
pw = g("db_pass")
name = g("db_name")


def mysql(sql):
    r = subprocess.run(
        ["mysql", "-h", host, "-u", user, f"-p{pw}", name, "-N", "-e", sql],
        capture_output=True,
        text=True,
    )
    if r.returncode:
        sys.stderr.write(r.stderr.replace(pw, "***"))
        raise SystemExit("mysql failed: " + sql[:120])
    return r.stdout.strip()


OLD = "test enjoy"
NEW = "Otantik_Hub_Nkoabang"
SLUG = "otantik-hub-nkoabang"

rid = mysql("SELECT id FROM tbl_routers WHERE name='%s' OR slug='test-enjoy' LIMIT 1" % OLD.replace("'", "\\'"))
if not rid:
    rid = mysql("SELECT id FROM tbl_routers WHERE slug='%s' LIMIT 1" % SLUG)
    if rid:
        print("already renamed router_id", rid)
    else:
        raise SystemExit("router not found")
else:
    mysql(
        "UPDATE tbl_routers SET name='%s', slug='%s', ssid='Otantik Hub Nkoabang', "
        "ros_version='7', pppoe_interface='hs-bridge', status='Offline', "
        "portal_title='Otantik Hub Nkoabang' WHERE id=%s"
        % (NEW, SLUG, rid)
    )
    mysql("UPDATE tbl_plans SET routers='%s' WHERE routers='%s'" % (NEW, OLD))
    mysql("UPDATE tbl_pool SET routers='%s' WHERE routers='%s'" % (NEW, OLD))
    cols = mysql("SHOW COLUMNS FROM tbl_user_recharges LIKE 'routers'")
    if cols:
        mysql("UPDATE tbl_user_recharges SET routers='%s' WHERE routers='%s'" % (NEW, OLD))
    print("renamed router_id", rid)

# API password already set on live box; keep existing tbl_routers.password (do not print)
mysql("UPDATE tbl_routers SET username='admin' WHERE id=%s" % rid)
# password: only set if empty so cloud API can log in
empty = mysql("SELECT IF(password='' OR password IS NULL,1,0) FROM tbl_routers WHERE id=%s" % rid)
if empty == "1":
    mysql("UPDATE tbl_routers SET password='demopass' WHERE id=%s" % rid)
    print("api password filled")
else:
    print("api password kept")

pool_name = "wo-pppoe-%s" % rid
if not mysql("SELECT id FROM tbl_pool WHERE pool_name='%s'" % pool_name):
    mysql(
        "INSERT INTO tbl_pool (pool_name, local_ip, range_ip, routers) "
        "VALUES ('%s','10.20.4.1','10.20.4.10-10.20.4.250','%s')"
        % (pool_name, NEW)
    )
    print("pool", pool_name)
else:
    mysql("UPDATE tbl_pool SET routers='%s' WHERE pool_name='%s'" % (NEW, pool_name))
    print("pool exists")

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),
]
# MK rate-limit A/B treated as up/down for display; do not push to router
ppp = [
    ("pppoe-profile", "5000", 30, "Days", 1, 4),
    ("Business", "10000", 30, "Days", 2, 10),
    ("BAMi", "5000", 30, "Days", 2, 6),
    ("Home", "7500", 30, "Days", 2, 8),
    ("Premium", "15000", 30, "Days", 3, 16),
    ("NoLimit", "25000", 30, "Days", 100, 300),
    ("expire", "500", 1, "Days", 2, 1),
]


def ensure_bw(label, down, up):
    nm = "%s-%s" % (label, NEW)
    bid = mysql("SELECT id FROM tbl_bandwidth WHERE name_bw='%s' LIMIT 1" % nm.replace("'", "\\'"))
    if bid:
        mysql(
            "UPDATE tbl_bandwidth SET rate_down=%s, rate_down_unit='Mbps', rate_up=%s, rate_up_unit='Mbps' WHERE id=%s"
            % (down, up, bid)
        )
        return bid
    mysql(
        "INSERT INTO tbl_bandwidth (name_bw, rate_down, rate_down_unit, rate_up, rate_up_unit, burst) "
        "VALUES ('%s', %s, 'Mbps', %s, 'Mbps', '')" % (nm.replace("'", "\\'"), down, up)
    )
    return mysql("SELECT id FROM tbl_bandwidth WHERE name_bw='%s' ORDER BY id DESC LIMIT 1" % nm.replace("'", "\\'"))


def time_limit(val, unit):
    if unit == "Days":
        return val * 24, "Hrs"
    return val, unit


for name_plan, price, val, unit, down, up, shared in hs:
    exists = mysql(
        "SELECT id FROM tbl_plans WHERE name_plan='%s' AND routers='%s' AND type='Hotspot' LIMIT 1"
        % (name_plan.replace("'", "\\'"), NEW)
    )
    bwid = ensure_bw(name_plan, down, up)
    tl, tu = time_limit(val, unit)
    if exists:
        mysql(
            "UPDATE tbl_plans SET id_bw=%s, price='%s', typebp='Unlimited', limit_type='Time_Limit', "
            "time_limit=%s, time_unit='%s', data_limit=0, data_unit='MB', validity=%s, validity_unit='%s', "
            "shared_users=%s, enabled=1, prepaid='yes', device='MikrotikHotspot' WHERE id=%s"
            % (bwid, price, tl, tu, val, unit, shared, exists)
        )
        print("upd hs", name_plan)
    else:
        mysql(
            "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, plan_expired, expired_date, "
            "enabled, prepaid, device) VALUES ('%s', %s, '%s', '', 'Hotspot', 'Unlimited', 'Time_Limit', %s, '%s', 0, 'MB', "
            "%s, '%s', %s, '%s', 0, 0, 20, 1, 'yes', 'MikrotikHotspot')"
            % (name_plan.replace("'", "\\'"), bwid, price, tl, tu, val, unit, shared, NEW)
        )
        print("add hs", name_plan)

for name_plan, price, val, unit, up, down in ppp:
    exists = mysql(
        "SELECT id FROM tbl_plans WHERE name_plan='%s' AND routers='%s' AND type='PPPOE' LIMIT 1"
        % (name_plan.replace("'", "\\'"), NEW)
    )
    bwid = ensure_bw(name_plan, down, up)
    tl, tu = time_limit(val, unit)
    if exists:
        mysql(
            "UPDATE tbl_plans SET id_bw=%s, price='%s', typebp='Unlimited', limit_type='Time_Limit', "
            "time_limit=%s, time_unit='%s', data_limit=0, data_unit='MB', validity=%s, validity_unit='%s', "
            "shared_users=1, pool='%s', enabled=1, prepaid='yes', device='MikrotikPppoe' WHERE id=%s"
            % (bwid, price, tl, tu, val, unit, pool_name, exists)
        )
        print("upd ppp", name_plan)
    else:
        mysql(
            "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, plan_expired, "
            "expired_date, enabled, prepaid, device) VALUES ('%s', %s, '%s', '', 'PPPOE', 'Unlimited', 'Time_Limit', %s, '%s', "
            "0, 'MB', %s, '%s', 1, '%s', 0, '%s', 0, 20, 1, 'yes', 'MikrotikPppoe')"
            % (name_plan.replace("'", "\\'"), bwid, price, tl, tu, val, unit, NEW, pool_name)
        )
        print("add ppp", name_plan)

mysql("UPDATE tbl_otantik_peers SET status='pending' WHERE router_id=%s" % rid)
print("DONE", mysql("SELECT id,name,slug,ip_address FROM tbl_routers WHERE id=%s" % rid))
print("plans", mysql("SELECT COUNT(*) FROM tbl_plans WHERE routers='%s'" % NEW))