`diagnose.py`

72 lines
#!/usr/bin/env python3
"""
diagnose.py — TK-11029 READ-ONLY diagnosis of maya_catalog.width pollution.
Emits exact affected counts (original vs current), per-slug breakdown, and the
residual still-polluted rows. Zero writes. dw_unified via local socket.
"""
import subprocess, json, datetime, os, sys

HERE = os.path.dirname(os.path.abspath(__file__))
PSQL = ["psql", "-h", "/tmp", "-d", "dw_unified", "-At", "-F", "\t", "-c"]


def q(sql):
    r = subprocess.run(PSQL + [sql], capture_output=True, text=True)
    if r.returncode != 0:
        print("SQL ERR:", r.stderr, file=sys.stderr); sys.exit(1)
    return [ln.split("\t") for ln in r.stdout.strip().split("\n") if ln]


def main():
    ts = datetime.datetime.now(datetime.timezone.utc).isoformat()
    counts = q("""SELECT
        count(*) FILTER (WHERE dw_sku LIKE 'DWMR-8%'),
        count(*) FILTER (WHERE dw_sku LIKE 'DWMR-8%' AND width LIKE '%device-width%'),
        count(*) FILTER (WHERE dw_sku LIKE 'DWMR-8%' AND width IS NOT NULL AND width NOT LIKE '%device-width%'),
        count(*) FILTER (WHERE dw_sku LIKE 'DWMR-8%' AND width IS NULL),
        count(*) FILTER (WHERE dw_sku LIKE 'DWMR-8%' AND width_inches IS NULL)
      FROM maya_catalog""")[0]
    total, polluted, clean, nullw, null_wi = map(int, counts)

    residual = [{"dw_sku": r[0], "mfr_sku": r[1], "width": r[2],
                 "width_inches": r[3], "product_url": r[4], "slug": r[5]}
                for r in q("""SELECT dw_sku, mfr_sku, width, coalesce(width_inches::text,''),
                                     product_url, split_part(product_url,'/collections/',2)
                              FROM maya_catalog
                              WHERE dw_sku LIKE 'DWMR-8%' AND width LIKE '%device-width%'
                              ORDER BY dw_sku""")]

    per_slug = [{"slug": r[0], "rows": int(r[1]), "polluted": int(r[2]),
                 "distinct_width_inches": r[3]}
                for r in q("""SELECT split_part(product_url,'/collections/',2), count(*),
                                     count(*) FILTER (WHERE width LIKE '%device-width%'),
                                     string_agg(DISTINCT coalesce(width_inches::text,'NULL'),'|')
                              FROM maya_catalog WHERE dw_sku LIKE 'DWMR-8%'
                              GROUP BY 1 ORDER BY 2 DESC""")]

    out = {
        "ticket": "TK-11029", "generated_at": ts, "mode": "READ-ONLY diagnosis",
        "table": "maya_catalog", "row_scope": "dw_sku LIKE 'DWMR-8%'",
        "polluted_string": "=device-width, initial-scale=1\">",
        "counts": {
            "total_dwmr8": total,
            "original_polluted_all_224": total,   # historical: 224/224 were polluted at ticket open
            "current_still_polluted": polluted,
            "current_clean_width": clean,
            "current_null_width": nullw,
            "current_null_width_inches": null_wi,   # 17 multi-width (ambiguous) + 2 residual keep NULL/garbage
        },
        "residual_rows": residual,
        "per_slug": per_slug,
    }
    path = os.path.join(HERE, f"diagnosis-{ts.replace(':', '-')}.json")
    json.dump(out, open(path, "w"), indent=2)
    print(f"total={total} still_polluted={polluted} clean={clean} null_width={nullw} null_width_inches={null_wi}")
    print(f"residual (needs Maya rep): {[r['dw_sku'] for r in residual]}")
    print(f"DIAGNOSIS -> {path}")
    return 0


if __name__ == "__main__":
    sys.exit(main())