wulf-pulse/migrations/063_relax_tickets_company_resource_constraints.sql

-- Migration 063: Relax tickets company_id and resource FK constraints
-- Both were causing ticket sync to fail with DATABASE_CONSTRAINT_ERROR.
--
-- Root causes:
--   1. tickets_company_id_fkey — strict, not deferrable, ON DELETE CASCADE.
--      Tickets whose company_id references a company not yet in the local
--      companies table fail on upsert.
--   2. tickets_assigned_resource_id_fkey — was relaxed in migration 008 but
--      got re-created as strict (condeferrable=f) by a subsequent migration.
--
-- Fix: make both constraints deferrable INITIALLY DEFERRED with ON DELETE SET NULL.

-- ── company_id ──────────────────────────────────────────────────────────────
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_company_id_fkey;
ALTER TABLE tickets ALTER COLUMN company_id DROP NOT NULL;
ALTER TABLE tickets
  ADD CONSTRAINT tickets_company_id_fkey
  FOREIGN KEY (company_id) REFERENCES companies(id)
  ON DELETE SET NULL
  DEFERRABLE INITIALLY DEFERRED;

-- ── assigned_resource_id (re-apply in case it was reset) ────────────────────
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_assigned_resource_id_fkey;
ALTER TABLE tickets ALTER COLUMN assigned_resource_id DROP NOT NULL;
ALTER TABLE tickets
  ADD CONSTRAINT tickets_assigned_resource_id_fkey
  FOREIGN KEY (assigned_resource_id) REFERENCES resources(id)
  ON DELETE SET NULL
  DEFERRABLE INITIALLY DEFERRED;

-- ── first_response resource IDs ─────────────────────────────────────────────
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_first_response_assigned_resource_id_fkey;
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_first_response_initiating_resource_id_fkey;

ALTER TABLE tickets
  ADD CONSTRAINT tickets_first_response_assigned_resource_id_fkey
  FOREIGN KEY (first_response_assigned_resource_id) REFERENCES resources(id)
  ON DELETE SET NULL
  DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE tickets
  ADD CONSTRAINT tickets_first_response_initiating_resource_id_fkey
  FOREIGN KEY (first_response_initiating_resource_id) REFERENCES resources(id)
  ON DELETE SET NULL
  DEFERRABLE INITIALLY DEFERRED;