-- Add sequential customer category reference and enforce uniqueness.
ALTER TABLE customer_categories
  ADD COLUMN IF NOT EXISTS category_number TEXT;

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM customer_categories
  WHERE NULLIF(TRIM(COALESCE(category_number, '')), '') IS NULL
)
UPDATE customer_categories cc
SET category_number = 'CAT-' || LPAD(ranked.rn::text, 4, '0')
FROM ranked
WHERE cc.id = ranked.id;

CREATE UNIQUE INDEX IF NOT EXISTS ux_customer_categories_category_number
  ON customer_categories ((NULLIF(TRIM(category_number), '')))
  WHERE NULLIF(TRIM(category_number), '') IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS ux_customer_categories_name_ci
  ON customer_categories (LOWER(TRIM(name)));