#!/usr/bin/env python3
"""
backfill-industry.py — restore the "Industry / Business Type" column that was dropped during the
2026-07-16 bigasscrm import.

Reads UltronCRM.xlsx (sheet "Leads", openpyxl read_only — NEVER the npm xlsx package, which has
parse-time CVEs) and recomputes each row's synthetic bigasscrm place_id, then emits a CSV of
(place_id, industry) that the uploader loads into a staging table to backfill leads.industry.

place_id recipe (VALIDATED at 46,187/46,187 rows against the intermediate import CSV bigass.csv):
    place_id = 'bigass:' + sha1( lower( email|phone|company|full_name ) )
using the RAW xlsx cell values (the import computed the key BEFORE normalizing phones, so recomputing
from the raw sheet reproduces the exact DB place_id). Fields are joined with '|' and NOT stripped.

Usage:  python3 scripts/backfill-industry.py [in.xlsx] [out.csv]
        defaults: UltronCRM.xlsx  ->  industry-backfill.csv
"""
import sys, csv, hashlib
import openpyxl

IN_XLSX = sys.argv[1] if len(sys.argv) > 1 else 'UltronCRM.xlsx'
OUT_CSV = sys.argv[2] if len(sys.argv) > 2 else 'industry-backfill.csv'

# column indices in the xlsx "Leads" sheet (header verified 2026-07-16)
FN, COMPANY, EMAIL, PHONE, INDUSTRY = 0, 7, 8, 11, 16


def cell(v):
    """Match how the original convert.py stringified cells: None -> '', integer-valued float -> int."""
    if v is None:
        return ''
    if isinstance(v, float) and v.is_integer():
        return str(int(v))
    return str(v)


def place_id(email, phone, company, full_name):
    key = ('|'.join([email, phone, company, full_name])).lower()
    return 'bigass:' + hashlib.sha1(key.encode('utf-8')).hexdigest()


def main():
    wb = openpyxl.load_workbook(IN_XLSX, read_only=True, data_only=True)
    ws = wb['Leads'] if 'Leads' in wb.sheetnames else wb[wb.sheetnames[0]]
    it = ws.iter_rows(values_only=True)
    header = next(it)
    if header[INDUSTRY] not in ('Industry / Business Type',):
        raise SystemExit(f'unexpected Industry column header: {header[INDUSTRY]!r}')

    scanned = 0
    pid2ind = {}          # place_id -> first non-empty industry seen
    for row in it:
        if row is None:
            continue
        fn = cell(row[FN]); company = cell(row[COMPANY])
        email = cell(row[EMAIL]); phone = cell(row[PHONE])
        industry = cell(row[INDUSTRY]).strip()
        if not any([fn, company, email, phone]):
            continue
        scanned += 1
        if not industry:
            continue
        pid = place_id(email, phone, company, fn)
        pid2ind.setdefault(pid, industry)   # keep-first non-empty (matches import keep-first dedup)

    with open(OUT_CSV, 'w', newline='', encoding='utf-8') as f:
        w = csv.writer(f, quoting=csv.QUOTE_ALL)
        w.writerow(['place_id', 'industry'])
        for pid, ind in pid2ind.items():
            w.writerow([pid, ind])

    print(f'scanned {scanned} xlsx data rows')
    print(f'wrote {len(pid2ind)} (place_id, industry) rows -> {OUT_CSV}')


if __name__ == '__main__':
    main()