-- Allow ARC applications in the Buildium import staging table
ALTER TABLE public.buildium_import_staging
  DROP CONSTRAINT IF EXISTS buildium_import_staging_kind_check;

ALTER TABLE public.buildium_import_staging
  ADD CONSTRAINT buildium_import_staging_kind_check
  CHECK (kind = ANY (ARRAY['unit'::text, 'owner'::text, 'gl_account'::text, 'ledger_entry'::text, 'arc_application'::text]));

-- Track the originating Buildium request id on ARC applications so re-imports update in place
ALTER TABLE public.arc_applications
  ADD COLUMN IF NOT EXISTS buildium_arc_request_id text;

CREATE UNIQUE INDEX IF NOT EXISTS uniq_arc_apps_assoc_buildium_id
  ON public.arc_applications(association_id, buildium_arc_request_id)
  WHERE buildium_arc_request_id IS NOT NULL;

-- Allow staff service role / system to insert seeded comments where user_id is null (Buildium-imported decision notes)
ALTER TABLE public.arc_application_comments
  ALTER COLUMN user_id DROP NOT NULL;