-- =============================================================================
-- MIGRATION: Unit of measure on products and variants
-- Quick-commerce catalogues sell the same item in several sizes (500 ml, 1 L,
-- a pack of 6). The size is part of what the customer buys, so it is stored
-- structurally — an integer quantity in a base unit — rather than as a label
-- typed into the product name.
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

ALTER TABLE products.products
  ADD COLUMN IF NOT EXISTS "unitOfMeasure" TEXT,
  ADD COLUMN IF NOT EXISTS "unitQuantity"  INTEGER;

ALTER TABLE products.product_variants
  ADD COLUMN IF NOT EXISTS "unitOfMeasure" TEXT,
  ADD COLUMN IF NOT EXISTS "unitQuantity"  INTEGER;

-- Base units only. kg/L/dozen are display conversions applied in the UI, not
-- storage: keeping one row of truth per measure means 1 kg and 1000 g compare,
-- sort and filter as the same amount instead of as two unrelated strings.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'products_unitOfMeasure_check') THEN
    ALTER TABLE products.products
      ADD CONSTRAINT "products_unitOfMeasure_check"
      CHECK ("unitOfMeasure" IS NULL OR "unitOfMeasure" IN ('piece', 'g', 'ml', 'cm'));
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'product_variants_unitOfMeasure_check') THEN
    ALTER TABLE products.product_variants
      ADD CONSTRAINT "product_variants_unitOfMeasure_check"
      CHECK ("unitOfMeasure" IS NULL OR "unitOfMeasure" IN ('piece', 'g', 'ml', 'cm'));
  END IF;

  -- A quantity of zero or less is not a size; it is a data-entry accident that
  -- would render as "0 g" on a storefront.
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'products_unitQuantity_check') THEN
    ALTER TABLE products.products
      ADD CONSTRAINT "products_unitQuantity_check"
      CHECK ("unitQuantity" IS NULL OR "unitQuantity" > 0);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'product_variants_unitQuantity_check') THEN
    ALTER TABLE products.product_variants
      ADD CONSTRAINT "product_variants_unitQuantity_check"
      CHECK ("unitQuantity" IS NULL OR "unitQuantity" > 0);
  END IF;
END $$;

COMMIT;

-- =============================================================================
-- Foreign key for the Product -> ProductVariant relation.
-- The column has always held a product id; there was simply no constraint, so
-- Prisma could not express the relation and no query could include variants.
-- Any orphan rows are deleted first — a variant whose product no longer exists
-- is unreachable from every screen and would block the constraint.
-- =============================================================================

BEGIN;

DELETE FROM products.product_variants v
WHERE NOT EXISTS (SELECT 1 FROM products.products p WHERE p."id" = v."productId");

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'product_variants_productId_fkey') THEN
    ALTER TABLE products.product_variants
      ADD CONSTRAINT "product_variants_productId_fkey"
      FOREIGN KEY ("productId") REFERENCES products.products("id") ON DELETE CASCADE;
  END IF;
END $$;

COMMIT;