-- Global patients: mobile is cloud-wide unique identity; org scope removed.
-- Test/dev data only — clear patient-linked rows before reshape.

DELETE FROM "treatment_case_sends";
DELETE FROM "treatment_case_attachments";
DELETE FROM "treatment_cases";
DELETE FROM "treatments";
DELETE FROM "appointments";
DELETE FROM "patients";

ALTER TABLE "patients" DROP CONSTRAINT IF EXISTS "patients_organizationId_fkey";

DROP INDEX IF EXISTS "patients_organizationId_createdAt_idx";
DROP INDEX IF EXISTS "patients_organizationId_lastName_firstName_idx";

ALTER TABLE "patients" DROP COLUMN "organizationId";
ALTER TABLE "patients" DROP COLUMN "phone";

ALTER TABLE "patients" ADD COLUMN "mobile" TEXT NOT NULL;
ALTER TABLE "patients" ADD COLUMN "createdByOrganizationId" TEXT;

CREATE UNIQUE INDEX "patients_mobile_key" ON "patients"("mobile");
CREATE INDEX "patients_lastName_firstName_idx" ON "patients"("lastName", "firstName");

ALTER TABLE "patients"
  ADD CONSTRAINT "patients_createdByOrganizationId_fkey"
  FOREIGN KEY ("createdByOrganizationId") REFERENCES "organizations"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;