-- 035_recipient_public_ref.sql
-- Customer-visible download reference for recipient JPG filenames.
--
-- Security rule:
--   This value is random and public-safe. It must not be derived from
--   customer_id, order_id, recipient_uni_key, decrypt keys, or QR payloads.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

ALTER TABLE customers
  ADD COLUMN IF NOT EXISTS recipient_public_ref TEXT;

UPDATE customers
SET recipient_public_ref = 'R-' || UPPER(SUBSTRING(ENCODE(gen_random_bytes(4), 'hex') FROM 1 FOR 6))
WHERE recipient_public_ref IS NULL;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'customers_recipient_public_ref_format_check'
  ) THEN
    ALTER TABLE customers
      ADD CONSTRAINT customers_recipient_public_ref_format_check
      CHECK (
        recipient_public_ref IS NULL
        OR recipient_public_ref ~ '^R-[A-Z0-9]{6}$'
      );
  END IF;
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS customers_recipient_public_ref_uidx
  ON customers(recipient_public_ref)
  WHERE recipient_public_ref IS NOT NULL;

