#!/usr/bin/env python3
"""Extract plaintext rows from exfiltrated PBIX files via pbixray (VertiPaq decode).
Reads pbix_dump/*.pbix, dumps each business table to CSV in extracted_csv/.
Read-only on local artifacts. No network.
"""
import sys, json
from pathlib import Path
import pandas as pd

D = Path('/root/ir-assessment/redteam/gitlab_multistackexpert_com')
OUT = D / 'extracted_csv'
OUT.mkdir(exist_ok=True)

from pbixray import PBIXRay

# internal/system tables to skip
SKIP = ('LocalDateTable', 'DateTableTemplate', 'Date ', 'Date', 'Jour', 'Mois',
        'Trimestre', 'NoMois', 'NoTrimestre', 'RowNumber', 'Source NbPvVendus DSC')

manifest = {}
for pbix in sorted((D / 'pbix_dump').glob('*.pbix')):
    rid = pbix.stem
    print(f'\n===== {rid}.pbix =====', flush=True)
    try:
        m = PBIXRay(str(pbix))
    except Exception as e:
        print(f'  open fail: {e}'); continue
    try:
        tables = list(m.tables)
    except Exception as e:
        print(f'  tables fail: {e}'); continue
    tables = [t for t in tables if not any(t.startswith(s) or t == s for s in SKIP)]
    manifest[rid] = {}
    for t in tables:
        try:
            df = m.get_table(t)
        except Exception as e:
            print(f'  {t}: ERR {str(e)[:80]}'); continue
        if df is None or len(df) == 0:
            continue
        safe = ''.join(ch if ch.isalnum() or ch in '._-' else '_' for ch in t)[:60]
        fp = OUT / f'{rid[:8]}__{safe}.csv'
        try:
            df.to_csv(fp, index=False)
            manifest[rid][t] = {'rows': len(df), 'cols': len(df.columns), 'csv': fp.name}
            print(f'  {t}: {len(df)} rows x {len(df.columns)} cols -> {fp.name}', flush=True)
        except Exception as e:
            print(f'  {t}: write ERR {str(e)[:80]}')

(D / 'extraction_manifest.json').write_text(json.dumps(manifest, indent=1, ensure_ascii=False))
total_rows = sum(v['rows'] for r in manifest.values() for v in r.values())
total_files = sum(len(r) for r in manifest.values())
print(f'\nDONE: {total_files} tables, {total_rows} total rows -> {OUT}')