-- =============================================================================
-- MIGRATION: Variant image and price-validity window
-- A variant is a thing a shopper picks between, so it needs its own picture;
-- and a price that applies for a season needs the season recorded with it
-- rather than in someone's calendar.
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

ALTER TABLE products.product_variants
  ADD COLUMN IF NOT EXISTS "imageUrl"           TEXT,
  ADD COLUMN IF NOT EXISTS "priceEffectiveFrom" TIMESTAMP(3),
  ADD COLUMN IF NOT EXISTS "priceEffectiveTo"   TIMESTAMP(3);

-- A window that ends before it starts would silently price nothing.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'product_variants_price_window_check') THEN
    ALTER TABLE products.product_variants
      ADD CONSTRAINT "product_variants_price_window_check"
      CHECK (
        "priceEffectiveFrom" IS NULL
        OR "priceEffectiveTo" IS NULL
        OR "priceEffectiveTo" >= "priceEffectiveFrom"
      );
  END IF;
END $$;

COMMIT;