-- Update contractor payments to reference Projects table instead of contractor_projects
-- Migration: Fix contractor payments foreign key references
-- Date: 2026-03-14

-- Drop existing foreign key constraint if it exists
ALTER TABLE contractor_payments 
DROP FOREIGN KEY IF EXISTS contractor_payments_ibfk_2;

-- Add new foreign key constraint to Projects table
ALTER TABLE contractor_payments 
ADD CONSTRAINT fk_contractor_payments_projects 
FOREIGN KEY (projectId) REFERENCES Projects(id) 
ON DELETE SET NULL ON UPDATE CASCADE;

-- Update existing payments to ensure data integrity
UPDATE contractor_payments cp
SET cp.projectId = NULL
WHERE cp.projectId IS NOT NULL 
AND NOT EXISTS (
  SELECT 1 FROM Projects p 
  WHERE p.id = cp.projectId
);

-- Add index for better performance
CREATE INDEX idx_contractor_payments_project_contractor 
ON contractor_payments (projectId, contractorId);