dodil/erp-brick-product

src/brick_product/migrations/00005_audit_fixes/up.sql

-- 00005_audit_fixes: additive only. v4 audit MUST-1: one main-GTIN claim row per unit (the key a racing add / make-main / retire meets on).
CREATE TABLE IF NOT EXISTS prod_gtin_main (
    owner      VARCHAR NOT NULL,
    unit_code  VARCHAR NOT NULL,
    gtin       VARCHAR,
    updated_by VARCHAR,
    updated_at TIMESTAMP,
    PRIMARY KEY (owner, unit_code)
);

-- the units that already have an active main GTIN get their claim row
INSERT INTO prod_gtin_main (owner, unit_code, gtin, updated_by, updated_at)
SELECT g.owner, g.unit_code, MIN(g.gtin), 'migration 00005', NOW()
FROM prod_gtin g
WHERE COALESCE(g.kind, 'main') = 'main' AND COALESCE(g.status, 'active') = 'active'
  AND NOT EXISTS (SELECT 1 FROM prod_gtin_main m WHERE m.owner = g.owner AND m.unit_code = g.unit_code)
GROUP BY g.owner, g.unit_code;