BEGIN;

ALTER TABLE customers
  ADD COLUMN IF NOT EXISTS design_version TEXT;

COMMENT ON COLUMN customers.design_version IS
  'Immutable postcard design bundle version used to render this order. Example: legacy-v1, 20260719-op2-template-v2.';

CREATE INDEX IF NOT EXISTS idx_customers_design_version
  ON customers(design_version);

-- Old orders whose stored UI manifest points at the old postcard templates.
UPDATE customers
SET design_version = 'legacy-v1'
WHERE design_version IS NULL
  AND (
    asset_manifest #>> '{postcardBack,physical}' = 'postcard_back_template.webp'
    OR asset_manifest #>> '{postcardBack,digital}' = 'postcard_back_digital.webp'
  );

-- Everything else currently active should use the new immutable bundle.
UPDATE customers
SET design_version = '20260719-op2-template-v2'
WHERE design_version IS NULL;

ALTER TABLE customers
  ALTER COLUMN design_version SET NOT NULL;

COMMIT;
