-- Add sequential reference for currencies and enforce uniqueness for code/reference.

-- Normalize currency code casing before applying uniqueness.
UPDATE currencies
SET raw = jsonb_set(raw, '{code}', to_jsonb(UPPER(TRIM(COALESCE(raw->>'code', '')))), true),
    updated_at = NOW()
WHERE NULLIF(TRIM(COALESCE(raw->>'code', '')), '') IS NOT NULL
  AND raw->>'code' <> UPPER(TRIM(COALESCE(raw->>'code', '')));

-- Backfill missing currencyNumber values in deterministic order.
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM currencies
  WHERE NULLIF(TRIM(COALESCE(raw->>'currencyNumber', '')), '') IS NULL
)
UPDATE currencies c
SET raw = jsonb_set(
      c.raw,
      '{currencyNumber}',
      to_jsonb('CUR-' || LPAD(ranked.rn::text, 4, '0')),
      true
    ),
    updated_at = NOW()
FROM ranked
WHERE c.id = ranked.id;

CREATE UNIQUE INDEX IF NOT EXISTS ux_currencies_code_ci
  ON currencies (LOWER(TRIM(COALESCE(raw->>'code', ''))))
  WHERE NULLIF(TRIM(COALESCE(raw->>'code', '')), '') IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS ux_currencies_reference
  ON currencies (TRIM(COALESCE(raw->>'currencyNumber', '')))
  WHERE NULLIF(TRIM(COALESCE(raw->>'currencyNumber', '')), '') IS NOT NULL;