#!/usr/bin/env python3
"""Mine 1.5GB sendgrid log_send_mail dump for secrets/creds/reset-links (operator GO 2026-08-14).

Local-only (file already on disk). Streams the SQL dump, extracts per-row:
subject, from/to, purpose custom_arg, and scans JSONReq for:
 - credential patterns (password/token/api_key/secret assignments)
 - reset/activation/verification links with tokens
 - internal endpoints (pharmalink.id hosts, IPs)
 - jenkins/gitlab/vault mentions
Outputs TSV (no-masking) + summary counts. No egress.
"""
import json
import re
import sys
from pathlib import Path

P = Path('/root/ir-assessment/redteam/gitlab_pharmalink_id/downloads/big/colosseum__April_2025__reporting_sendgrid.sql')
OUT = Path('/root/ir-assessment/redteam/gitlab_pharmalink_id/sendgrid_mining_aug14.tsv')
SUM = Path('/root/ir-assessment/redteam/gitlab_pharmalink_id/sendgrid_mining_summary_aug14.json')

CRED = re.compile(r'(?i)(password|passwd|secret|api[_-]?key|token|credential|username|user[_-]?name|login)\s*["\\:= ]+\s*["\']?([^\s"\',\\}]{4,120})')
LINK = re.compile(r'https?://[^\s"\'\\)]+')
HOST = re.compile(r'(?i)\b([a-z0-9.-]*pharmalink\.id|jenkins|gitlab|vault|grafana|argocd|sonarqube?)\b')
IP = re.compile(r'\b(?:\d{1,3}\.){3}\d{1,3}\b')

def unescape(s):
    return s.replace('\\\\n', '\n').replace('\\n', ' ').replace('\\"', '"').replace("\\'", "'").replace('\\\\', '\\')

counts = {'rows': 0, 'subjects': {}, 'purposes': {}, 'froms': {}, 'tos': {}}
findings = []
seen = set()

with open(P, 'rb') as f:
    for raw in f:
        line = raw.decode('utf-8', 'ignore')
        if not line.startswith('INSERT INTO'):
            continue
        counts['rows'] += 1
        try:
            subject = (re.search(r'\\\\"subject\\\\":\\\\"(.*?)\\\\"', line) or [None, ''])[1]
            purpose = (re.search(r'email-purpose\\\\":\\\\"(.*?)\\\\"', line) or [None, ''])[1]
            frm = (re.search(r"'([^']+@[^']+)'\s*,\s*'([^']+@[^']+)'\s*,", line) or [None, '', ''])
            to = frm[2] if isinstance(frm, tuple) else ''
            frm = frm[1] if isinstance(frm, tuple) else ''
            subject_u, purpose_u = unescape(subject), unescape(purpose)
            for k, v in (('subjects', subject_u), ('purposes', purpose_u), ('froms', frm), ('tos', to)):
                counts[k][v] = counts[k].get(v, 0) + 1

            # credential scan in full line
            for m in CRED.finditer(line):
                key, val = m.group(1), unescape(m.group(2))
                if len(val) < 4 or val.lower() in ('null', 'true', 'false', 'name', 'email'):
                    continue
                sig = ('cred', key.lower(), val)
                if sig in seen:
                    continue
                seen.add(sig)
                findings.append(('cred', key, val, subject_u[:80], frm))
            # links with tokens / resets
            for m in LINK.finditer(line):
                url = unescape(m.group(0))[:300]
                if re.search(r'(?i)(reset|verify|activ|token|confirm|password|invite|signup)', url):
                    sig = ('link', url)
                    if sig in seen:
                        continue
                    seen.add(sig)
                    findings.append(('link', url, '', subject_u[:80], frm))
            # internal hosts
            for m in HOST.finditer(line):
                h = m.group(1).lower()
                sig = ('host', h)
                if sig in seen:
                    continue
                seen.add(sig)
                findings.append(('host', h, '', subject_u[:80], frm))
            # IPs (skip private 10./192.168. only-report anyway)
            for m in IP.finditer(line):
                ip = m.group(0)
                if ip.startswith(('0.', '255.')):
                    continue
                sig = ('ip', ip)
                if sig in seen:
                    continue
                seen.add(sig)
                findings.append(('ip', ip, '', subject_u[:80], frm))
        except Exception:
            continue

with open(OUT, 'w') as f:
    f.write('type\tkey_or_url\tvalue\tsubject\tfrom\n')
    for row in findings:
        f.write('\t'.join(str(x).replace('\t', ' ') for x in row) + '\n')

summary = {
    'rows_scanned': counts['rows'],
    'findings': len(findings),
    'by_type': {},
    'top_subjects': sorted(counts['subjects'].items(), key=lambda x: -x[1])[:20],
    'top_purposes': sorted(counts['purposes'].items(), key=lambda x: -x[1])[:20],
    'top_froms': sorted(counts['froms'].items(), key=lambda x: -x[1])[:15],
    'top_tos': sorted(counts['tos'].items(), key=lambda x: -x[1])[:15],
}
for t, *_ in findings:
    summary['by_type'][t] = summary['by_type'].get(t, 0) + 1
SUM.write_text(json.dumps(summary, indent=1, ensure_ascii=False))
print(f"rows={counts['rows']} findings={len(findings)} by_type={summary['by_type']}", file=sys.stderr)
print(f"[+] -> {OUT}", file=sys.stderr)