RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / www / admin / sqls /
Filename : laratrust_tables.sql
back
CREATE TABLE `roles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `display_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permissions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `display_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 CREATE TABLE `teams` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `display_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `teams_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permission_user` (
  `permission_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `user_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `team_id` bigint unsigned DEFAULT NULL,
  UNIQUE KEY `permission_user_user_id_permission_id_user_type_team_id_unique` (`user_id`,`permission_id`,`user_type`,`team_id`),
  KEY `permission_user_permission_id_foreign` (`permission_id`),
  KEY `permission_user_team_id_foreign` (`team_id`),
  CONSTRAINT `permission_user_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `permission_user_team_id_foreign` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permission_role` (
  `permission_id` bigint unsigned NOT NULL,
  `role_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`role_id`),
  KEY `permission_role_role_id_foreign` (`role_id`),
  CONSTRAINT `permission_role_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `permission_role_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_user` (
  `role_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `user_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `team_id` bigint unsigned DEFAULT NULL,
  UNIQUE KEY `role_user_user_id_role_id_user_type_team_id_unique` (`user_id`,`role_id`,`user_type`,`team_id`),
  KEY `role_user_role_id_foreign` (`role_id`),
  KEY `role_user_team_id_foreign` (`team_id`),
  CONSTRAINT `role_user_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `role_user_team_id_foreign` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE coins_for_college_roles CONVERT to character set utf8mb4 collate utf8mb4_unicode_ci;

ALTER TABLE `groups` ADD COLUMN team_id INT AFTER owner_user_id;

-- seed data start

INSERT INTO `roles` VALUES
(1,'ADMIN','Admin','admin role for root access',NOW(),NOW()),
(2,'PARENT','Parent','Legal guardian of a child',NOW(),NOW()),
(3,'FOSTER_CARE_AGENT','Foster Care Agent','Foster Care Case Worker',NOW(),NOW()),
(4,'CHILD','Child','Child -  <18 years old',NOW(),NOW());

INSERT INTO `permissions` VALUES
(1,'GLOBAL_ROOT','Global Root','global root permission',NOW(),NOW()),
(2,'GROUP_OWNER','Group Owner','access to do everything inside a group',NOW(),NOW()),
(3,'GROUP_PARENT','Group Parent','access to create/edit/update rewards, tasks, add products to store, invite children to group',NOW(),NOW()),
(4,'GROUP_CHILD','Group Child','access to all child related actions in a group',NOW(),NOW());

INSERT IGNORE INTO role_user (user_id, role_id, user_type )
SELECT u.user_id, r.id, 'App\\Models\\User' FROM users AS u
JOIN coins_for_college_roles AS cfcr
ON cfcr.coins_for_college_role_id = u.coins_for_college_role_id
JOIN roles As r
ON r.description LIKE  cfcr.role_name ;

INSERT INTO teams (name, display_name, description, created_at, updated_at) SELECT CONCAT(group_name, '-', g.group_id), group_name, group_name, NOW(), NOW() FROM `groups` AS g LEFT JOIN teams AS t on g.team_id = t.id WHERE t.id IS NULL;

UPDATE `groups` AS g JOIN teams AS t on t.name = CAST( CONCAT(g.group_name, '-', g.group_id) AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci  SET g.team_id = t.id WHERE g.team_id IS NULL;

INSERT IGNORE INTO role_user (role_id, user_id, user_type, team_id)
SELECT 4 AS role_id, gm.member_user_id AS user_id, 'App\\Models\\User' AS user_type, g.team_id
FROM `groups` AS g JOIN group_members AS gm ON g.group_id = gm.group_id
WHERE g.team_id IS NOT NULL AND invitation_accepted = true AND member_user_id IS NOT NULL;

INSERT IGNORE INTO role_user (role_id, user_id, user_type, team_id)
SELECT 2 AS role_id, g.owner_user_id, 'App\\Models\\User' AS user_type, g.team_id
FROM `groups` AS g WHERE team_id IS NOT NULL;
-- TODO: populate team members