#!/usr/bin/env python3
"""
Archives D7's Commerce revision tables to CSV (decision 12 / check R-21).

READ-ONLY against D7: one SELECT per table, streamed out of the container.
Nothing is written to the D7 project directory or database.

MySQL batch mode escapes \\t \\n \\0 \\\\ inside values and prints an unquoted
NULL for SQL NULL; both are decoded here, so the CSV holds the real values.
"""
import csv, gzip, json, subprocess, sys, os, hashlib, datetime

D7 = '/Users/apple/neerja/ceonline'
OUT = '/Users/apple/neerja/ceonline-d10/archive/commerce-revisions'
FREEZE = '2026-08-24 13:31:48'
# The archive set: D7's Commerce revision tables. DATABASE_ANALYSIS.md sizes the
# top six of these at ~570 MB of the 1.65 GB database, which is the "~570 MB"
# decision 12 refers to. Enumerated rather than hard-coded so a table added to
# the source is not silently missed.
tables = [t.strip() for t in subprocess.run(['ddev','mysql','--batch','--skip-column-names','-e',
    "SELECT table_name FROM information_schema.tables WHERE table_schema=DATABASE() "
    "AND table_type='BASE TABLE' AND (table_name IN "
    "('commerce_order_revision','commerce_payment_transaction_revision','commerce_product_revision') "
    "OR table_name LIKE 'field_revision_commerce_%' OR table_name LIKE 'field_revision_discount_%' "
    "OR table_name='field_revision_inline_conditions') ORDER BY table_name"],
    cwd=D7, capture_output=True, text=True).stdout.splitlines() if t.strip()]

def unescape(v):
    if v == 'NULL':
        return None
    out=[]; i=0
    while i < len(v):
        c=v[i]
        if c=='\\' and i+1 < len(v):
            n=v[i+1]
            out.append({'t':'\t','n':'\n','0':'\0','\\':'\\'}.get(n,n)); i+=2
        else:
            out.append(c); i+=1
    return ''.join(out)

manifest={'generated':datetime.datetime.now().isoformat(timespec='seconds'),
          'source':'ceonline (Drupal 7.105), read-only','freeze_point':FREEZE,
          'encoding':{'null':'empty field, recorded as SQL NULL in the source',
                      'escapes':'MySQL batch escapes decoded before writing CSV'},
          'tables':[]}

for t in tables:
    n = int(subprocess.run(['ddev','mysql','--batch','--skip-column-names','-e',
        f'SELECT COUNT(*) FROM `{t}`'], cwd=D7, capture_output=True, text=True).stdout.strip())
    path = os.path.join(OUT, f'{t}.csv.gz')
    p = subprocess.Popen(['ddev','mysql','--batch','-e',f'SELECT * FROM `{t}`'],
                         cwd=D7, stdout=subprocess.PIPE, text=True, bufsize=1<<20)
    written=0; header=None; h=hashlib.sha256()
    with gzip.open(path,'wt',newline='',encoding='utf-8') as fh:
        w=csv.writer(fh)
        for line in p.stdout:
            line=line.rstrip('\n')
            if header is None:
                header=line.split('\t'); w.writerow(header); continue
            row=[unescape(f) for f in line.split('\t')]
            w.writerow(['' if f is None else f for f in row]); written+=1
            h.update(('\x1f'.join('' if f is None else f for f in row)).encode('utf-8','replace'))
    p.wait()
    ok = (written==n)
    manifest['tables'].append({'table':t,'source_rows':n,'archived_rows':written,
        'match':ok,'columns':header,'file':os.path.basename(path),
        'bytes':os.path.getsize(path),'sha256_of_values':h.hexdigest()})
    print(f"  {t:48} {n:>9,} -> {written:>9,}  {'OK' if ok else 'MISMATCH'}  {os.path.getsize(path)/1048576:.1f} MB", flush=True)
    if not ok:
        print("  ABORT: row count mismatch", file=sys.stderr); sys.exit(1)

manifest['total_source_rows']=sum(x['source_rows'] for x in manifest['tables'])
manifest['total_archived_rows']=sum(x['archived_rows'] for x in manifest['tables'])
manifest['total_bytes']=sum(x['bytes'] for x in manifest['tables'])
json.dump(manifest, open(os.path.join(OUT,'MANIFEST.json'),'w'), indent=2)
print(f"\nTOTAL {manifest['total_source_rows']:,} source rows -> {manifest['total_archived_rows']:,} archived, "
      f"{manifest['total_bytes']/1048576:.1f} MB across {len(tables)} files")