#!/usr/bin/env python3
"""Local mining of downloaded data (operator GO 2026-08-17). No egress.

(a) sendgrid: decode base64 attachments (JVBER... -> PDF) from log_send_mail,
    save unique PDFs, count; also extract plain-text PO content.
(b) great-wall 2021: dump schema/rowcounts per 6 non-auth DBs + extract any
    credential-looking rows (password/token/key patterns in INSERTs).
(c) blackhole dailies: extract LATEST (2025-05-20) fully + inventory tar
    contents of 3 newest (looking beyond farmacare: configs/keys/db).
Out: mined/ + mined_report_aug17.json + OPLOG.
"""
import base64
import json
import re
import subprocess
import sys
import tarfile
from datetime import datetime, timezone
from pathlib import Path

ROOT = Path('/root/ir-assessment')
DOSSIER = ROOT / 'redteam/gitlab_pharmalink_id'
DL = DOSSIER / 'downloads'
MORE = DL / 'more'
OUT = DOSSIER / 'mined'

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 | (no egress) | python3 mining | sendgrid attachments + great-wall + blackhole inventory | {output} | {result} | none | local mine GO\n")

def main():
    OUT.mkdir(exist_ok=True)
    report = {}

    # ---- (a) sendgrid attachments ----
    print('[*] (a) sendgrid attachments', file=sys.stderr)
    p = DL / 'big/colosseum__April_2025__reporting_sendgrid.sql'
    pdfs = {}
    n_rows = 0
    with open(p, errors='ignore') as f:
        for line in f:
            if not line.startswith('INSERT'):
                continue
            n_rows += 1
            for m in re.finditer(r'\\"content\\":\\"(JVBER[A-Za-z0-9+/=]+)\\"', line):
                b64 = m.group(1)
                if b64 in pdfs:
                    continue
                try:
                    raw = base64.b64decode(b64 + '=' * (-len(b64) % 4))
                    if raw[:5] == b'%PDF-':
                        pdfs[b64] = raw
                except Exception:
                    pass
    pdf_dir = OUT / 'sendgrid_pdfs'
    pdf_dir.mkdir(exist_ok=True)
    total_size = 0
    for i, (k, raw) in enumerate(pdfs.items()):
        fn = pdf_dir / f'po_{i:04d}.pdf'
        fn.write_bytes(raw)
        total_size += len(raw)
    report['sendgrid'] = {'rows': n_rows, 'unique_pdfs': len(pdfs), 'bytes': total_size}
    print(f'  {len(pdfs)} unique PDF attachments, {total_size/1e6:.1f}MB', file=sys.stderr)

    # ---- (b) great-wall DBs ----
    print('[*] (b) great-wall content', file=sys.stderr)
    gw = MORE / 'cfu-main-db-archives__great-wall_Cloud_SQL_Export_2021-02-18 (16:17:11).sql'
    txt = gw.read_text(errors='ignore')
    dbs = {}
    for m in re.finditer(r'Current Database: `([^`]+)`', txt):
        dbs[m.group(1)] = m.start()
    gw_info = {}
    cred_pat = re.compile(r'(?i)(password|passwd|secret|api_key|apikey|token|access_key)')
    for db, start in dbs.items():
        end = min([v for v in dbs.values() if v > start] + [len(txt)])
        seg = txt[start:end]
        tables = re.findall(r'CREATE TABLE `([^`]+)`', seg)
        inserts = len(re.findall(r'INSERT INTO', seg))
        cred_hits = []
        for tm in re.finditer(r'INSERT INTO `([^`]+)` VALUES \((.*?)\);', seg, re.S):
            row = tm.group(2)
            if cred_pat.search(row):
                # extract likely k=v pairs
                for kv in re.finditer(r"'([A-Za-z0-9_.@-]{3,60})','([^']{4,120})'", row):
                    cred_hits.append({'table': tm.group(1), 'pair': kv.groups()})
                if len(cred_hits) > 30:
                    break
        gw_info[db] = {'tables': len(tables), 'inserts': inserts, 'table_names': tables[:15],
                       'cred_hits': cred_hits[:30]}
    report['greatwall'] = gw_info
    (OUT / 'greatwall_content_aug17.json').write_text(json.dumps(gw_info, indent=1, ensure_ascii=False))
    for db, info in gw_info.items():
        print(f"  {db}: {info['tables']} tables, {info['inserts']} inserts, cred_hits={len(info['cred_hits'])}", file=sys.stderr)

    # ---- (c) blackhole inventory ----
    print('[*] (c) blackhole inventory', file=sys.stderr)
    bh_files = sorted(MORE.glob('cfu-main-blackhole-backup__*.tgz.bz2'))
    inv = {}
    for f in bh_files[-4:]:  # 4 newest
        try:
            names = subprocess.run(['tar', 'tjf', str(f)], capture_output=True, text=True, timeout=120).stdout.splitlines()
            top = {}
            for n in names:
                key = '/'.join(n.split('/')[:3])
                top[key] = top.get(key, 0) + 1
            interesting = [n for n in names if re.search(r'(?i)\.env|\.pem|\.key|id_rsa|shadow|passwd|config|secret|\.sql|\.db|credential', n)]
            inv[f.name[:60]] = {'files': len(names), 'top': dict(sorted(top.items(), key=lambda x: -x[1])[:10]),
                                'interesting': interesting[:40]}
            print(f'  {f.name[:50]}: {len(names)} files, interesting={len(interesting)}', file=sys.stderr)
        except Exception as e:
            inv[f.name[:60]] = {'error': str(e)[:100]}
    report['blackhole'] = inv
    (OUT / 'blackhole_inventory_aug17.json').write_text(json.dumps(inv, indent=1, ensure_ascii=False))

    (DOSSIER / 'mined_report_aug17.json').write_text(json.dumps(report, indent=1, ensure_ascii=False))
    oplog(f'sendgrid_pdfs={len(pdfs)} greatwall_dbs={len(gw_info)} blackhole_inventoried={len(inv)}',
          'SUCCESS')

if __name__ == '__main__':
    main()