-- Add the Private Sector owner field for working group issues.
ALTER TABLE "issues" ADD COLUMN "stakeholder_id" INTEGER;

-- Backfill existing issues from the creator's active Private Sector membership.
-- If a user has more than one Private Sector membership, the smallest
-- stakeholder id is used as the deterministic owner.
UPDATE "issues"
SET "stakeholder_id" = private_sector_owner."stakeholder_id"
FROM (
  SELECT
    "stakeholder_users"."user_id",
    MIN("stakeholder_users"."stakeholder_id") AS "stakeholder_id"
  FROM "stakeholder_users"
  INNER JOIN "stakeholders"
    ON "stakeholders"."id" = "stakeholder_users"."stakeholder_id"
  INNER JOIN "stakeholder_types"
    ON "stakeholder_types"."id" = "stakeholders"."stakeholder_type_id"
  WHERE "stakeholders"."deleted_at" IS NULL
    AND "stakeholders"."active" = true
    AND "stakeholder_types"."deleted_at" IS NULL
    AND LOWER("stakeholder_types"."name") = 'private sector'
  GROUP BY "stakeholder_users"."user_id"
) AS private_sector_owner
WHERE "issues"."user_id" = private_sector_owner."user_id"
  AND "issues"."stakeholder_id" IS NULL;

-- Legacy soft-deleted issues may have been created by admin users before the
-- owner model existed. They are not visible, but the new NOT NULL column still
-- needs a valid value. Assign them to the first active Private Sector so the
-- historical deleted row remains valid without affecting active workflow data.
UPDATE "issues"
SET "stakeholder_id" = fallback_private_sector."id"
FROM (
  SELECT "stakeholders"."id"
  FROM "stakeholders"
  INNER JOIN "stakeholder_types"
    ON "stakeholder_types"."id" = "stakeholders"."stakeholder_type_id"
  WHERE "stakeholders"."deleted_at" IS NULL
    AND "stakeholders"."active" = true
    AND "stakeholder_types"."deleted_at" IS NULL
    AND LOWER("stakeholder_types"."name") = 'private sector'
  ORDER BY "stakeholders"."id" ASC
  LIMIT 1
) AS fallback_private_sector
WHERE "issues"."stakeholder_id" IS NULL
  AND "issues"."deleted_at" IS NOT NULL;

-- Fail with a clear message if old issue rows cannot be assigned an owner.
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM "issues" WHERE "stakeholder_id" IS NULL) THEN
    RAISE EXCEPTION 'Cannot add NOT NULL issues.stakeholder_id because some existing issues were created by users without an active Private Sector stakeholder';
  END IF;
END $$;

ALTER TABLE "issues" ALTER COLUMN "stakeholder_id" SET NOT NULL;

CREATE INDEX "issues_stakeholder_id_idx" ON "issues"("stakeholder_id");

ALTER TABLE "issues"
ADD CONSTRAINT "issues_stakeholder_id_fkey"
FOREIGN KEY ("stakeholder_id")
REFERENCES "stakeholders"("id")
ON DELETE RESTRICT
ON UPDATE CASCADE;
