-- SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
create table `tenancy_roles` (
  `id` char(36) primary key,
  `version` integer not null,
  `created_at` datetime(6) not null,
  `updated_at` datetime(6) not null,
  `org_id` char(36) not null,
  `key` text not null,
  `name` text not null,
  `level` text not null,
  `permissions` json not null,
  `known_permissions` json not null,
  `created_by` char(36),
  `revision` integer not null
) engine=InnoDB default charset=utf8mb4;

alter table `tenancy_roles` add constraint `tenancy_roles_org_id_key_key` unique (`org_id`, `key`);

create table `project_memberships` (
  `id` char(36) primary key,
  `version` integer not null,
  `created_at` datetime(6) not null,
  `updated_at` datetime(6) not null,
  `org_id` char(36) not null,
  `project_id` char(36) not null,
  `user_id` char(36) not null,
  `role_key` text not null
) engine=InnoDB default charset=utf8mb4;

alter table `project_memberships` add constraint `project_memberships_org_id_project_id_user_id_key` unique (`org_id`, `project_id`, `user_id`);

create index `project_memberships_org_id_user_id_idx` on `project_memberships` (`org_id`, `user_id`);

create index `project_memberships_org_id_role_key_idx` on `project_memberships` (`org_id`, `role_key`(191));

alter table `org_memberships` add column `role_key` text;

alter table `workspace_memberships` add column `role_key` text;

alter table `invitations` add column `role_key` text;

alter table `projects` add column `visibility` text;

create index `org_memberships_org_id_role_key_idx` on `org_memberships` (`org_id`, `role_key`(191));

create index `workspace_memberships_org_id_role_key_idx` on `workspace_memberships` (`org_id`, `role_key`(191));

create index `invitations_org_id_role_key_idx` on `invitations` (`org_id`, `role_key`(191));