-- 1. Rename existing user_id columns to preserve historical data
ALTER TABLE "es_site" RENAME COLUMN "user_id" TO "created_by_user_id";
ALTER TABLE "es_site_test" RENAME COLUMN "user_id" TO "triggered_by_user_id";

-- 2. Drop the old NOT NULL constraint on es_site.created_by_user_id (was user_id NOT NULL)
ALTER TABLE "es_site" ALTER COLUMN "created_by_user_id" DROP NOT NULL;

-- 3. Add FK constraints with ON DELETE SET NULL
ALTER TABLE "es_site" ADD CONSTRAINT "es_site_created_by_user_id_user_id_fk"
  FOREIGN KEY ("created_by_user_id") REFERENCES "user"("id") ON DELETE SET NULL ON UPDATE NO ACTION;
ALTER TABLE "es_site_test" ADD CONSTRAINT "es_site_test_triggered_by_user_id_user_id_fk"
  FOREIGN KEY ("triggered_by_user_id") REFERENCES "user"("id") ON DELETE SET NULL ON UPDATE NO ACTION;

-- 4. Add organization_id columns (NULLABLE for now so we can backfill)
ALTER TABLE "es_site" ADD COLUMN "organization_id" text;
ALTER TABLE "es_site_test" ADD COLUMN "organization_id" text;

-- 5. Backfill organization_id from the user's first membership.
UPDATE "es_site" s
SET "organization_id" = (
  SELECT m."organization_id"
  FROM "member" m
  WHERE m."user_id" = s."created_by_user_id"
  ORDER BY m."created_at" ASC
  LIMIT 1
)
WHERE s."created_by_user_id" IS NOT NULL;

UPDATE "es_site_test" t
SET "organization_id" = (
  SELECT m."organization_id"
  FROM "member" m
  WHERE m."user_id" = t."triggered_by_user_id"
  ORDER BY m."created_at" ASC
  LIMIT 1
)
WHERE t."triggered_by_user_id" IS NOT NULL;

-- 6. Verify no orphan sites
DO $$
DECLARE orphan_count int;
BEGIN
  SELECT COUNT(*) INTO orphan_count FROM "es_site" WHERE "organization_id" IS NULL;
  IF orphan_count > 0 THEN
    RAISE EXCEPTION 'Backfill incomplete: % es_site row(s) still have NULL organization_id', orphan_count;
  END IF;
END $$;

-- 7. Set NOT NULL on es_site.organization_id + add FK
ALTER TABLE "es_site" ALTER COLUMN "organization_id" SET NOT NULL;
ALTER TABLE "es_site" ADD CONSTRAINT "es_site_organization_id_organization_id_fk"
  FOREIGN KEY ("organization_id") REFERENCES "organization"("id") ON DELETE CASCADE ON UPDATE NO ACTION;

-- 8. es_site_test.organization_id stays NULLABLE (anonymous audits) — just add the FK
ALTER TABLE "es_site_test" ADD CONSTRAINT "es_site_test_organization_id_organization_id_fk"
  FOREIGN KEY ("organization_id") REFERENCES "organization"("id") ON DELETE CASCADE ON UPDATE NO ACTION;

-- 9. Drop old indexes, add new ones
DROP INDEX IF EXISTS "idx_site_user";
DROP INDEX IF EXISTS "idx_site_test_user";
CREATE INDEX "idx_site_org" ON "es_site" USING btree ("organization_id");
CREATE INDEX "idx_site_test_org" ON "es_site_test" USING btree ("organization_id");