`enrich.py`

131 lines
#!/usr/bin/env python3
"""
enrich.py — RENTV CRE-sheet enrichment engine.

LAYER 1 (this file, $0, deterministic): derive each company's Website from its
existing corporate email domain, and add a Website/LinkedIn/Marketing-VP column set.
Only EMPTY target cells are filled; existing data is never overwritten. Every filled
cell is stamped light-green by lib.batch_fill.

Modes:
  --dry-run   read local data/tab_<gid>.csv, report projected fills, write NOTHING
  --apply     read the LIVE tab via the Sheets API and write fills (needs connect.py)

LinkedIn + Marketing/VP contact enrichment is LAYER 2 (research) — added next,
after Layer 1 is verified on the live sheet.
"""
import sys, os, csv, re

sys.path.insert(0, os.path.dirname(__file__))
import lib

FREEMAIL = {"gmail.com","hotmail.com","yahoo.com","outlook.com","aol.com","icloud.com",
    "me.com","comcast.net","att.net","sbcglobal.net","live.com","msn.com","ymail.com",
    "protonmail.com","mac.com","verizon.net","cox.net"}

# Per-tab config keyed by gid (=sheetId). Column indices are 0-based.
#   header_row : row index holding column titles (0-based)
#   data_start : first data row (0-based)
#   company/email : source column indices
#   new_website/new_linkedin/new_mvp : destination column indices for the added set
# base = first column of the 4-col added block, chosen to sit AFTER each tab's true
# max-used column (verified against the LIVE read, NOT the misleading CSV export).
TABS = {
    1536880271: dict(name="Sponsor & Speaker Companies",
        header_row=0, data_start=2, company=2, email=6, base=10),   # K-N
    2087021739: dict(name="Strong Prospects",
        header_row=1, data_start=2, company=3, email=7, base=11),   # L-O (skips K status col)
    1929460437: dict(name="Event Sponsors",
        header_row=1, data_start=2, company=2, email=6, base=9),    # J-M (after I status col)
    3823360: dict(name="Unique Contacts (all tabs)",                 # the deduped MASTER
        header_row=0, data_start=1, company=2, email=1, base=15),   # cols P.. (append after O)
}

NEW_LABELS = ["Website", "LinkedIn (Company)", "LinkedIn (Contact)",
              "Marketing/VP Contact", "Updated Email (found)"]

def cols(cfg):
    b = cfg["base"]
    return dict(new_website=b, new_li_company=b+1, new_li_contact=b+2, new_mvp=b+3)

def cell(row, i):
    return row[i].strip() if i < len(row) else ""

def domain_from_emails(emailcell):
    """Pick the first corporate (non-freemail) domain from a possibly multi-email cell."""
    for m in re.findall(r'[\w.+-]+@([\w-]+\.[\w.-]+)', emailcell or ""):
        d = m.lower().strip(".")
        if d not in FREEMAIL:
            return d
    return ""

def plan_tab(gid, rows):
    """Return (fills, stats). fills = list of {row0,col0,value}."""
    cfg = TABS[gid]; nc = cols(cfg)
    fills, stats = [], dict(rows=0, has_email=0, website_fill=0, freemail_only=0, no_email=0)
    # header cells for the new columns (only if currently blank)
    hr = cfg["header_row"]
    hdr = rows[hr] if hr < len(rows) else []
    for i, label in enumerate(NEW_LABELS):
        col = cfg["base"] + i
        if cell(hdr, col) == "":
            fills.append({"row0": hr, "col0": col, "value": label})
    for r in range(cfg["data_start"], len(rows)):
        row = rows[r]
        comp = cell(row, cfg["company"])
        if not comp:
            continue
        stats["rows"] += 1
        emailc = cell(row, cfg["email"])
        # only fill Website if that new cell is currently empty
        if cell(row, nc["new_website"]) != "":
            continue
        dom = domain_from_emails(emailc)
        if dom:
            stats["has_email"] += 1; stats["website_fill"] += 1
            fills.append({"row0": r, "col0": nc["new_website"], "value": dom})
        elif emailc:
            stats["freemail_only"] += 1   # has email but only free-mail -> needs Layer 2
        else:
            stats["no_email"] += 1        # no email -> needs Layer 2
    return fills, stats

def load_csv(gid):
    path = os.path.join(os.path.dirname(__file__), "data", f"tab_{gid}.csv")
    with open(path, newline="", encoding="utf-8", errors="ignore") as f:
        return list(csv.reader(f))

def main():
    mode = "--dry-run" if "--apply" not in sys.argv else "--apply"
    tok = lib.access_token() if mode == "--apply" else None
    meta = lib.get_meta(tok) if tok else None
    title_by_gid = {}
    if meta:
        for s in meta["sheets"]:
            title_by_gid[s["properties"]["sheetId"]] = s["properties"]["title"]

    only = None
    if "--only" in sys.argv:
        only = int(sys.argv[sys.argv.index("--only")+1])
    for gid, cfg in TABS.items():
        if only is not None and gid != only:
            continue
        if mode == "--apply":
            rows = lib.read_tab(tok, title_by_gid[gid])
        else:
            rows = load_csv(gid)
        fills, stats = plan_tab(gid, rows)
        data_fills = [f for f in fills if f["row0"] != cfg["header_row"]]
        print(f"\n=== {cfg['name']} (gid={gid}) ===")
        print(f"  companies: {stats['rows']} | website derivable now: {stats['website_fill']}"
              f" | freemail-only (Layer2): {stats['freemail_only']} | no-email (Layer2): {stats['no_email']}")
        print(f"  cells to fill this run: {len(fills)} ({len(data_fills)} websites + headers)")
        for f in data_fills[:5]:
            print(f"    -> R{f['row0']+1}C{lib.col_letter(f['col0'])}: {f['value']}")
        if mode == "--apply":
            res = lib.batch_fill(tok, gid, fills)
            print(f"  APPLIED: {res.get('totalUpdatedCells','?')} cells written (light-green).")

if __name__ == "__main__":
    main()