-- Migration 008: Relax tickets resource foreign key constraints
-- Allows tickets to sync even if assigned resources don't exist in our database
-- This is necessary because:
-- 1. Some tickets may reference deleted/inactive resources
-- 2. Resource sync may be incomplete
-- 3. Autotask may have data inconsistencies

-- Drop the strict foreign key constraint on assigned_resource_id
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_assigned_resource_id_fkey;

-- Drop other resource-related constraints that might cause issues
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;

-- Make assigned_resource_id nullable (it should already be, but ensure it)
ALTER TABLE tickets ALTER COLUMN assigned_resource_id DROP NOT NULL;
ALTER TABLE tickets ALTER COLUMN first_response_assigned_resource_id DROP NOT NULL;
ALTER TABLE tickets ALTER COLUMN first_response_initiating_resource_id DROP NOT NULL;

-- Re-add as lenient foreign keys with ON DELETE SET NULL
-- This allows tickets to exist even if the resource doesn't exist
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;

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;

-- Also relax tasks resource constraint
ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_assigned_resource_id_fkey;

ALTER TABLE tasks ALTER COLUMN assigned_resource_id DROP NOT NULL;

ALTER TABLE tasks 
  ADD CONSTRAINT tasks_assigned_resource_id_fkey 
  FOREIGN KEY (assigned_resource_id) REFERENCES resources(id) 
  ON DELETE SET NULL
  DEFERRABLE INITIALLY DEFERRED;