-- Migration 002: Relax foreign key constraints to allow syncing without strict dependencies
-- This allows entities to sync even if their referenced entities haven't synced yet

-- Drop existing foreign key constraints that are too strict
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_contact_id_fkey;
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_project_id_fkey;

ALTER TABLE contacts DROP CONSTRAINT IF EXISTS contacts_company_id_fkey;

ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_company_id_fkey;
ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_project_lead_resource_id_fkey;
ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_owner_resource_id_fkey;

ALTER TABLE configuration_items DROP CONSTRAINT IF EXISTS configuration_items_company_id_fkey;

ALTER TABLE contracts DROP CONSTRAINT IF EXISTS contracts_company_id_fkey;

ALTER TABLE billing_items DROP CONSTRAINT IF EXISTS billing_items_company_id_fkey;

-- Make company_id nullable for entities that might not have it
ALTER TABLE tickets ALTER COLUMN company_id DROP NOT NULL;
ALTER TABLE projects ALTER COLUMN company_id DROP NOT NULL;

-- Re-add foreign keys with ON DELETE SET NULL (more lenient)
-- This allows orphaned records to exist temporarily

-- Tickets
ALTER TABLE tickets 
  ADD CONSTRAINT tickets_contact_id_fkey 
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL;

ALTER TABLE tickets 
  ADD CONSTRAINT tickets_project_id_fkey 
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL;

-- Contacts (keep company_id but make it lenient)
ALTER TABLE contacts 
  ADD CONSTRAINT contacts_company_id_fkey 
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

-- Projects (keep company_id but make it lenient)
ALTER TABLE projects 
  ADD CONSTRAINT projects_company_id_fkey 
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

ALTER TABLE projects 
  ADD CONSTRAINT projects_project_lead_resource_id_fkey 
  FOREIGN KEY (project_lead_resource_id) REFERENCES resources(id) ON DELETE SET NULL;

ALTER TABLE projects 
  ADD CONSTRAINT projects_owner_resource_id_fkey 
  FOREIGN KEY (owner_resource_id) REFERENCES resources(id) ON DELETE SET NULL;

-- Configuration Items
ALTER TABLE configuration_items 
  ADD CONSTRAINT configuration_items_company_id_fkey 
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

-- Contracts
ALTER TABLE contracts 
  ADD CONSTRAINT contracts_company_id_fkey 
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

-- Billing Items
ALTER TABLE billing_items 
  ADD CONSTRAINT billing_items_company_id_fkey 
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;