-- Change files and tasks to cascade delete when contract is deleted
-- Previously these were ON DELETE SET NULL, which orphaned records

-- Drop existing foreign key constraints
ALTER TABLE files DROP CONSTRAINT IF EXISTS files_contract_id_fkey;
ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_contract_id_fkey;

-- Re-add with CASCADE
ALTER TABLE files
    ADD CONSTRAINT files_contract_id_fkey
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE;

ALTER TABLE tasks
    ADD CONSTRAINT tasks_contract_id_fkey
    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE;