CREATE EXTENSION IF NOT EXISTS "pgcrypto";

UPDATE "users"
SET "id" = gen_random_uuid()::text
WHERE "id" !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$';

UPDATE "organizations"
SET "id" = gen_random_uuid()::text
WHERE "id" !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$';

UPDATE "refresh_tokens"
SET "id" = gen_random_uuid()::text
WHERE "id" !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$';

UPDATE "handoff_tokens"
SET "id" = gen_random_uuid()::text
WHERE "id" !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$';

UPDATE "user_affiliations"
SET "id" = gen_random_uuid()::text
WHERE "id" !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$';

UPDATE "login_notices"
SET "id" = gen_random_uuid()::text
WHERE "id" !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$';

ALTER TABLE "refresh_tokens" DROP CONSTRAINT "refresh_tokens_user_id_fkey";
ALTER TABLE "handoff_tokens" DROP CONSTRAINT "handoff_tokens_user_id_fkey";
ALTER TABLE "user_affiliations" DROP CONSTRAINT "user_affiliations_user_id_fkey";
ALTER TABLE "user_affiliations" DROP CONSTRAINT "user_affiliations_organization_id_fkey";

ALTER TABLE "users"
  ALTER COLUMN "id" TYPE uuid USING "id"::uuid;

ALTER TABLE "organizations"
  ALTER COLUMN "id" TYPE uuid USING "id"::uuid;

ALTER TABLE "refresh_tokens"
  ALTER COLUMN "id" TYPE uuid USING "id"::uuid,
  ALTER COLUMN "user_id" TYPE uuid USING "user_id"::uuid;

ALTER TABLE "handoff_tokens"
  ALTER COLUMN "id" TYPE uuid USING "id"::uuid,
  ALTER COLUMN "user_id" TYPE uuid USING "user_id"::uuid;

ALTER TABLE "user_affiliations"
  ALTER COLUMN "id" TYPE uuid USING "id"::uuid,
  ALTER COLUMN "user_id" TYPE uuid USING "user_id"::uuid,
  ALTER COLUMN "organization_id" TYPE uuid USING "organization_id"::uuid;

ALTER TABLE "login_notices"
  ALTER COLUMN "id" TYPE uuid USING "id"::uuid;

ALTER TABLE "refresh_tokens" ADD CONSTRAINT "refresh_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "handoff_tokens" ADD CONSTRAINT "handoff_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "user_affiliations" ADD CONSTRAINT "user_affiliations_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "user_affiliations" ADD CONSTRAINT "user_affiliations_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;