#!/usr/bin/env node
// Reads every CSV in data/seed/ and writes a scrubbed copy to data/seed-scrubbed/.
// Goal: preserve structure (column names, row counts, value patterns, relational
// integrity via deterministic mapping) while stripping personal data so the
// scrubbed output is safe to share with an LLM.
//
// What gets scrubbed:
//   - Columns whose header matches name/email/phone/address/iban/etc.
//   - Cells matching email / phone / IBAN regex regardless of column.
//   - Free-text "notes/comments/bemerkung" columns are blanked unless --keep-notes.
// Same input value maps to the same fake (deterministic via hash) so foreign
// keys, repeated organisations, repeated emails etc. still line up.
//
// Usage:
//   node scripts/scrub-csvs.mjs
//   node scripts/scrub-csvs.mjs --keep-notes      # keep notes column verbatim
//   node scripts/scrub-csvs.mjs --dry-run         # report only

import { readdirSync, readFileSync, writeFileSync, mkdirSync } from 'node:fs'
import { createHash } from 'node:crypto'
import { join, basename } from 'node:path'

const SRC = 'data/seed'
const DST = 'data/seed-scrubbed'
const args = new Set(process.argv.slice(2))
const KEEP_NOTES = args.has('--keep-notes')
const DRY = args.has('--dry-run')

mkdirSync(DST, { recursive: true })

const PII_HEADER = /^(name|vorname|nachname|first[_ ]?name|last[_ ]?name|full[_ ]?name|email|e[-_ ]?mail|mail|phone|telefon|tel|mobile|handy|fax|address|adresse|street|strasse|straße|zip|plz|city|ort|stadt|country|land|iban|bic|kontoinhaber|account|signatur|password|passwort)$/i
const NOTES_HEADER = /^(note|notes|notiz|notizen|comment|comments|kommentar|bemerkung|bemerkungen|remark|remarks|message|nachricht|description|beschreibung|free[_ ]?text)$/i
const EMAIL_RE = /[\w.+-]+@[\w-]+\.[\w.-]+/g
const PHONE_RE = /(?:\+\d{1,3}[\s-]?)?(?:\(\d+\)[\s-]?)?\d[\d\s\-/]{6,}\d/g
const IBAN_RE = /\b[A-Z]{2}\d{2}[A-Z0-9]{10,30}\b/g

function fakeFrom(prefix, value) {
  if (value == null || value === '') return ''
  const h = createHash('sha256').update(prefix + '|' + value).digest('hex').slice(0, 8)
  return `${prefix}_${h}`
}
function fakeEmail(v) { return fakeFrom('user', v) + '@example.test' }
function fakeName(v)  { return 'Person_' + createHash('sha256').update(v).digest('hex').slice(0, 6) }
function fakePhone(v) { const h = createHash('sha256').update(v).digest('hex'); return '+49' + parseInt(h.slice(0, 9), 16).toString().padStart(10, '0').slice(0, 10) }
function fakeIban(v)  { return 'DE' + createHash('sha256').update(v).digest('hex').slice(0, 20).toUpperCase() }
function fakeGeneric(prefix, v) { return fakeFrom(prefix, String(v)) }

// Minimal CSV parser — handles quoted fields, embedded commas, doubled quotes, CRLF.
function parseCSV(text) {
  const rows = []
  let row = [], field = '', inQ = false, i = 0
  while (i < text.length) {
    const c = text[i]
    if (inQ) {
      if (c === '"') {
        if (text[i + 1] === '"') { field += '"'; i += 2; continue }
        inQ = false; i++; continue
      }
      field += c; i++; continue
    }
    if (c === '"') { inQ = true; i++; continue }
    if (c === ',') { row.push(field); field = ''; i++; continue }
    if (c === '\r') { i++; continue }
    if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; i++; continue }
    field += c; i++
  }
  if (field !== '' || row.length) { row.push(field); rows.push(row) }
  return rows
}
function emitCSV(rows) {
  return rows.map(r => r.map(v => {
    const s = v == null ? '' : String(v)
    return /[",\n\r]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s
  }).join(',')).join('\n') + '\n'
}

function scrubCell(value, headerName) {
  if (value == null || value === '') return value
  let v = String(value)

  // Header-driven replacement
  const h = headerName.toLowerCase().trim()
  if (PII_HEADER.test(h)) {
    if (/email|mail/.test(h)) return fakeEmail(v)
    if (/phone|telefon|tel|mobile|handy|fax/.test(h)) return fakePhone(v)
    if (/iban|bic|account|kontoinhaber/.test(h)) return fakeIban(v)
    if (/name|vorname|nachname/.test(h)) return fakeName(v)
    if (/address|adresse|street|strasse|straße/.test(h)) return fakeGeneric('addr', v)
    if (/zip|plz/.test(h)) return '00000'
    if (/city|ort|stadt/.test(h)) return fakeGeneric('city', v)
    if (/country|land/.test(h)) return v // country is fine
    return fakeGeneric('val', v)
  }

  if (NOTES_HEADER.test(h) && !KEEP_NOTES) return '[notes-redacted]'

  // Pattern-driven replacement on free-form columns
  v = v.replace(EMAIL_RE, m => fakeEmail(m))
  v = v.replace(IBAN_RE, m => fakeIban(m))
  // phone last — broad regex, may eat dates; only apply if cell is mostly digits/separators
  if (/^[\d\s\-+()/]{8,}$/.test(v)) v = v.replace(PHONE_RE, m => fakePhone(m))

  return v
}

const files = readdirSync(SRC).filter(f => f.toLowerCase().endsWith('.csv'))
if (files.length === 0) {
  console.log(`No .csv files found in ${SRC}/. Drop CSVs there and re-run.`)
  process.exit(0)
}

console.log(`Found ${files.length} file(s). KEEP_NOTES=${KEEP_NOTES} DRY=${DRY}\n`)

for (const f of files) {
  const raw = readFileSync(join(SRC, f), 'utf8')
  const rows = parseCSV(raw)
  if (rows.length === 0) { console.log(`  ${f}: empty`); continue }
  const headers = rows[0]
  const scrubbed = [headers.slice()]
  for (let r = 1; r < rows.length; r++) {
    const row = rows[r]
    if (row.length === 1 && row[0] === '') continue
    const out = []
    for (let c = 0; c < headers.length; c++) {
      out.push(scrubCell(row[c] ?? '', headers[c] ?? `col${c}`))
    }
    scrubbed.push(out)
  }
  const outPath = join(DST, basename(f))
  if (!DRY) writeFileSync(outPath, emitCSV(scrubbed))
  console.log(`  ${f}: ${rows.length - 1} rows, ${headers.length} cols → ${outPath}`)
}

console.log('\nDone. Review data/seed-scrubbed/ before sharing.')