-- Fix data-integrity bug: composite FKs with bare ON DELETE SET NULL null ALL
-- referencing columns on delete, including the NOT NULL project_id column,
-- which raises 23502 and makes the referenced-row delete impossible.
-- PostgreSQL 15+ supports a column-subset form: ON DELETE SET NULL (col, ...)
-- so only the nullable column is nulled.

-- ============ issues -> workspaces ============
-- Original (0003): FOREIGN KEY (project_id, workspace_id)
--   REFERENCES workspaces (project_id, id) MATCH SIMPLE ON DELETE SET NULL
-- Deleting a workspace still referenced by an issue tried to null issues.project_id
-- (NOT NULL) -> blocked workspace deletion entirely.
ALTER TABLE issues DROP CONSTRAINT issues_project_workspace_fk;
ALTER TABLE issues ADD CONSTRAINT issues_project_workspace_fk
  FOREIGN KEY (project_id, workspace_id)
  REFERENCES workspaces (project_id, id) MATCH SIMPLE
  ON DELETE SET NULL (workspace_id);

-- ============ acp_sessions -> issues ============
-- Original (0006): FOREIGN KEY (project_id, issue_id)
--   REFERENCES issues (project_id, id) MATCH SIMPLE ON DELETE SET NULL
ALTER TABLE acp_sessions DROP CONSTRAINT acp_sessions_issue_project_fk;
ALTER TABLE acp_sessions ADD CONSTRAINT acp_sessions_issue_project_fk
  FOREIGN KEY (project_id, issue_id)
  REFERENCES issues (project_id, id) MATCH SIMPLE
  ON DELETE SET NULL (issue_id);

-- ============ acp_sessions -> requirements ============
-- Original (0006): FOREIGN KEY (project_id, requirement_id)
--   REFERENCES requirements (project_id, id) MATCH SIMPLE ON DELETE SET NULL
ALTER TABLE acp_sessions DROP CONSTRAINT acp_sessions_req_project_fk;
ALTER TABLE acp_sessions ADD CONSTRAINT acp_sessions_req_project_fk
  FOREIGN KEY (project_id, requirement_id)
  REFERENCES requirements (project_id, id) MATCH SIMPLE
  ON DELETE SET NULL (requirement_id);