#!/usr/bin/env python3
"""Deep secret hunt across ALL dumped DBs (engagement one-off).

Scans the actual DATA rows (not just headers) of every gz dump for live
credentials: plaintext passwords, API keys, tokens, private keys, connection
strings with embedded creds. Reads locally — zero network.
Output: L7_deep_secrets.json + L7_deep_secrets.tsv (no-masking).
"""
import gzip, glob, re, os, json
from collections import defaultdict

ROOT = '/root/ir-assessment/redteam/gitlab_npontutechnologies_com'
PATTERNS = {
 'glpat': re.compile(r'glpat-[\w-]{20,}'),
 'tgbot': re.compile(r'\d{8,10}:AA[\w-]{33}'),
 'jwt': re.compile(r'eyJ[\w-]{10,}\.[\w-]{10,}\.[\w-]{5,}'),
 'privkey': re.compile(r'-----BEGIN [A-Z0-9 ]*PRIVATE KEY-----'),
 'akia': re.compile(r'AKIA[0-9A-Z]{16}'),
 'google_api': re.compile(r'AIza[0-9A-Za-z_-]{35}'),
 'slack': re.compile(r'xox[baprs]-[0-9A-Za-z-]{10,}'),
 'mailgun': re.compile(r'key-[0-9a-f]{32}'),
 'kowri_key': re.compile(r'kgw_[\w-]{8,}'),
 'snwolley_key': re.compile(r'key_[0-9a-f]{12,}'),
 'bearer': re.compile(r'(?i)bearer\s+[a-z0-9._-]{16,}'),
 'assign': re.compile(r'(?i)(password|passwd|pwd|secret|api[_-]?key|apikey|client_secret|app_secret|access_token|auth_token|smtp_password|mail_password|db_password)\s*[:=]\s*["\']?([^\s"\'#,;\\]{6,120})'),
 'conn_str': re.compile(r'(?i)(mysql|postgres|mongodb|redis|amqp|smtp)://[^\s:]+:[^\s@]+@[^\s/]+'),
}
PLACEHOLDER = re.compile(r'(?i)^(change[-_ ]?me|placeholder|example|your[-_]|\$\{|<.*>|test|null|none|xxx|\*+|redacted|insert|todo|default|secret$|password$|token$)')

def scan_text(db, tbl, text, out):
    for name, rx in PATTERNS.items():
        for m in rx.finditer(text):
            if name == 'assign':
                val = m.group(2)
                if PLACEHOLDER.match(val) or len(val) < 6: continue
                out.append({'db': db, 'table': tbl, 'kind': 'assign', 'key': m.group(1)[:40], 'value': val})
            elif name == 'conn_str':
                out.append({'db': db, 'table': tbl, 'kind': 'conn_str', 'value': m.group(0)[:200]})
            else:
                out.append({'db': db, 'table': tbl, 'kind': name, 'value': m.group(0)[:200]})

def main():
    out = []
    files = glob.glob(ROOT + '/exfil_all/pg_*/*.csv.gz') + glob.glob(ROOT + '/exfil_hellio/*.csv.gz') + glob.glob(ROOT + '/exfil/*.txt.gz')
    print(f'scanning {len(files)} files...')
    for i, f in enumerate(files):
        rel = f.split(ROOT + '/')[1]
        parts = rel.split('/')
        db = parts[1].replace('pg_', '') if parts[0] == 'exfil_all' else ('hellio' if parts[0] == 'exfil_hellio' else 'exfil')
        tbl = os.path.basename(f).replace('.csv.gz', '').replace('.txt.gz', '')
        try:
            with gzip.open(f, 'rt', errors='replace') as fh:
                # cap per-file read to 40MB to keep it fast; secrets live in config/users tables anyway
                text = fh.read(40 * 1024 * 1024)
        except Exception as e:
            continue
        scan_text(db, tbl, text, out)
        if (i + 1) % 500 == 0:
            print(f'  ...{i+1}/{len(files)} files, {len(out)} hits', flush=True)
    # dedup by value
    seen = {}
    for r in out:
        v = r['value']
        if v not in seen:
            seen[v] = r
        else:
            seen[v].setdefault('also_in', []).append(f"{r['db']}.{r['table']}")
    uniq = list(seen.values())
    json.dump(uniq, open(ROOT + '/L7_deep_secrets.json', 'w'), ensure_ascii=False, indent=1)
    def _t(v): return ('' if v is None else str(v)).replace('\r','\\r').replace('\n','\\n').replace('\t','\\t')
    with open(ROOT + '/L7_deep_secrets.tsv', 'w') as fh:
        fh.write('db\ttable\tkind\tkey\tvalue\n')
        for r in uniq:
            fh.write('\t'.join([_t(r['db']), _t(r['table']), _t(r['kind']), _t(r.get('key','')), _t(r['value'])]) + '\n')
    print(f'[+] {len(out)} raw hits -> {len(uniq)} distinct -> L7_deep_secrets.json/.tsv')

if __name__ == '__main__':
    main()