`scripts/pg-writeback.py`

54 lines
#!/usr/bin/env python3
"""PostgreSQL-first writeback for the Fentucci Naturals (Tokiwa) draft import.

create-drafts.py records the Shopify product ids to data/created.jsonl only.
Per Steve's standing rule the staging table must also carry the Shopify id +
an on_shopify flag so dw_unified knows which staged rows are live on the store.

This is idempotent: adds the two columns if missing, then upserts from
created.jsonl. Local dw_unified mirror only — no Shopify writes. $0 (local).

Usage:  PGHOST=/tmp python3 scripts/pg-writeback.py
"""
import json, os, subprocess, sys

HERE = os.path.dirname(os.path.abspath(__file__))
ROOT = os.path.dirname(HERE)
CREATED = os.path.join(ROOT, "data", "created.jsonl")
os.environ.setdefault("PGHOST", "/tmp")

rows = [json.loads(l) for l in open(CREATED) if l.strip()]
if not rows:
    sys.exit("created.jsonl empty — nothing to write back")

# de-dup on sku, last write wins
by_sku = {r["sku"]: r["product_id"] for r in rows}
values = ",\n".join(f"('{sku}',{pid})" for sku, pid in sorted(by_sku.items()))

sql = f"""
BEGIN;
ALTER TABLE tokiwa_catalog ADD COLUMN IF NOT EXISTS shopify_product_id bigint;
ALTER TABLE tokiwa_catalog ADD COLUMN IF NOT EXISTS on_shopify boolean NOT NULL DEFAULT false;

UPDATE tokiwa_catalog t SET
  shopify_product_id = v.pid,
  on_shopify = true
FROM (VALUES
{values}
) AS v(sku, pid)
WHERE t.dw_sku = v.sku;

-- report
\\echo rows now on_shopify:
SELECT count(*) FROM tokiwa_catalog WHERE on_shopify;
\\echo staged rows NOT yet on_shopify (should be 0 if import complete):
SELECT count(*) FROM tokiwa_catalog WHERE NOT on_shopify;
COMMIT;
"""

p = subprocess.run(["psql", "-d", "dw_unified", "-v", "ON_ERROR_STOP=1"],
                   input=sql, text=True, capture_output=True)
sys.stdout.write(p.stdout)
sys.stderr.write(p.stderr)
sys.exit(p.returncode)