CREATE TABLE "tenancy_permissions" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  "key" text NOT NULL UNIQUE
);
--> statement-breakpoint
CREATE TABLE "tenancy_roles" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  "tenant_id" uuid NOT NULL REFERENCES "tenants"("id") ON DELETE CASCADE,
  "name" text NOT NULL,
  "is_system_role" boolean NOT NULL DEFAULT false,
  CONSTRAINT "tenancy_roles_tenant_name_unique" UNIQUE ("tenant_id", "name")
);
--> statement-breakpoint
CREATE TABLE "tenancy_role_permissions" (
  "role_id" uuid NOT NULL REFERENCES "tenancy_roles"("id") ON DELETE CASCADE,
  "permission_id" uuid NOT NULL REFERENCES "tenancy_permissions"("id") ON DELETE CASCADE,
  PRIMARY KEY ("role_id", "permission_id")
);
--> statement-breakpoint
CREATE INDEX "idx_tenancy_roles_tenant_name" ON "tenancy_roles" ("tenant_id", "name");
--> statement-breakpoint
INSERT INTO "tenancy_permissions" ("key")
SELECT DISTINCT p."key"
FROM "permissions" p
ON CONFLICT ("key") DO NOTHING;
--> statement-breakpoint
INSERT INTO "tenancy_roles" ("tenant_id", "name", "is_system_role")
SELECT DISTINCT r."tenant_id", r."name", r."is_system_role"
FROM "roles" r
ON CONFLICT ("tenant_id", "name") DO UPDATE
SET "is_system_role" = EXCLUDED."is_system_role";
--> statement-breakpoint
INSERT INTO "tenancy_role_permissions" ("role_id", "permission_id")
SELECT DISTINCT tr."id", tp."id"
FROM "roles" r
INNER JOIN "role_permissions" rp ON rp."role_id" = r."id"
INNER JOIN "permissions" p ON p."id" = rp."permission_id"
INNER JOIN "tenancy_roles" tr ON tr."tenant_id" = r."tenant_id" AND tr."name" = r."name"
INNER JOIN "tenancy_permissions" tp ON tp."key" = p."key"
ON CONFLICT DO NOTHING;
