
CREATE TABLE `tbl_department_roles` (
  `department_role_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `role_speciality` varchar(100) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbl_department_roles`
--

INSERT INTO `tbl_department_roles` (`department_role_id`, `department_id`, `role_id`, `role_speciality`, `is_active`) VALUES
(16, 2, 8, 'Infrastructure Strategist', 1),
(17, 2, 11, 'Health Planner', 1),
(18, 2, 10, 'Stakeholder Engagement Lead', 1),
(19, 2, 12, 'Project Manager', 1),
(20, 2, 17, 'Environmental Risk Assessor', 1),
(21, 2, 11, 'Workforce Planner', 1),
(22, 2, 7, 'Civil, Structural, etc Engineer', 1),
(23, 2, 7, 'Sustainability Engineer', 1),
(24, 2, 15, 'Health IT Specialist', 1),
(25, 2, 18, 'Procurement Officer', 1),
(26, 2, 14, 'Training Coordinator', 1),
(27, 2, 15, 'Resilience Specialist', 1),
(28, 2, 15, 'Monitoring Specialist', 1),
(29, 2, 8, 'HR Strategist', 1),
(30, 2, 17, 'Environmental Assessor', 1),
(74, 11, 8, 'Social Infrastructure Strategist', 1),
(75, 11, 11, 'Social Services Planner', 1),
(76, 11, 10, 'Stakeholder Engagement Lead', 1),
(77, 11, 12, 'Project Manager', 1),
(78, 11, 17, 'Environmental & Social Risk Assessor', 1),
(79, 11, 11, 'Workforce Planner', 1),
(80, 11, 23, 'Architect', 1),
(81, 11, 15, 'Sustainability Specialist', 1),
(82, 11, 11, 'Digital Infrastructure Planner', 1),
(83, 11, 18, 'Procurement Officer', 1),
(84, 11, 14, 'Development Coordinator', 1),
(85, 11, 7, 'Engineer', 1),
(86, 11, 14, 'Skills Development Coordinator', 1),
(87, 11, 15, 'Resilience Specialist', 1),
(88, 11, 15, 'Monitoring Specialist', 1),
(89, 11, 11, 'Digital Planner', 1),
(90, 11, 8, 'HR Strategist', 1),
(91, 7, 8, 'Mobility Strategist', 1),
(92, 7, 11, 'Transport Planner', 1),
(93, 7, 10, 'Stakeholder Engagement Lead', 1),
(94, 7, 12, 'Project Manager', 1),
(95, 7, 17, 'Environmental Assessor', 1),
(96, 7, 16, 'Transport Economist', 1),
(97, 7, 7, 'Transport Engineer', 1),
(98, 7, 21, 'Traffic Modeller', 1),
(99, 7, 7, 'Smart Mobility Engineer', 1),
(100, 7, 18, 'Procurement Officer', 1),
(101, 7, 19, 'Legal Advisor', 1),
(102, 7, 14, 'Skills Development Coordinator', 1),
(103, 7, 12, 'Construction Manager', 1),
(104, 7, 10, 'Smart Mobility Lead', 1),
(105, 7, 24, 'Community Liaison', 1),
(106, 7, 14, 'Skills Coordinator', 1),
(107, 7, 18, 'Quality Officer', 1),
(108, 7, 11, 'Operations Planner', 1),
(109, 7, 15, 'Monitoring Specialist', 1),
(110, 7, 12, 'Operations Manager', 1),
(111, 7, 13, 'Data Analyst', 1),
(112, 7, 10, 'Innovation Lead', 1),
(113, 15, 8, 'Cultural Strategist', 1),
(114, 15, 11, 'Community Planner', 1),
(115, 15, 10, 'Stakeholder Engagement Lead', 1),
(116, 15, 12, 'Project Manager', 1),
(117, 15, 17, 'Social Impact Assessor', 1),
(118, 15, 19, 'Heritage Advisor', 1),
(119, 15, 23, 'Architect', 1),
(120, 15, 15, 'Inclusion Specialist', 1),
(121, 15, 11, 'Digital Planner', 1),
(122, 15, 18, 'Procurement Officer', 1),
(123, 15, 19, 'Legal Advisor', 1),
(124, 15, 14, 'Skills Development Coordinator', 1),
(125, 15, 12, 'Construction Manager', 1),
(126, 15, 24, 'Community Liaison', 1),
(127, 15, 18, 'Quality Officer', 1),
(128, 15, 11, 'Operations Planner', 1),
(129, 15, 15, 'Monitoring Specialist', 1),
(130, 15, 12, 'Facility Manager', 1),
(131, 15, 13, 'Data Analyst', 1),
(132, 15, 10, 'Innovation Lead', 1),
(133, 1, 8, 'Agricultural Strategist', 1),
(134, 1, 22, 'Agri-Economist', 1),
(135, 1, 10, 'Stakeholder Engagement Lead', 1),
(136, 1, 12, 'Project Manager', 1),
(137, 1, 17, 'Environmental Assessor', 1),
(138, 1, 15, 'Irrigation Specialist', 1),
(139, 1, 7, 'Agricultural Engineer', 1),
(140, 1, 9, 'Agri-Tech Designer', 1),
(141, 1, 15, 'Sustainability Specialist', 1),
(142, 1, 18, 'Procurement Officer', 1),
(143, 1, 19, 'Legal Advisor', 1),
(144, 1, 14, 'Skills Development Coordinator', 1),
(145, 1, 12, 'Construction Manager', 1),
(146, 1, 10, 'Agri-Tech Lead', 1),
(147, 1, 24, 'Community Liaison', 1),
(148, 1, 14, 'Skills Coordinator', 1),
(149, 1, 18, 'Quality Officer', 1),
(150, 1, 11, 'Operations Planner', 1),
(151, 1, 15, 'Monitoring Specialist', 1),
(152, 1, 12, 'Facility Manager', 1),
(153, 1, 18, 'Extension Officer', 1),
(154, 1, 13, 'Data Analyst', 1),
(155, 1, 10, 'Innovation Lead', 1),
(156, 12, 8, 'Housing Strategist', 1),
(157, 12, 11, 'Urban Planner', 1),
(158, 12, 10, 'Stakeholder Engagement Lead', 1),
(159, 12, 12, 'Project Manager', 1),
(160, 12, 17, 'Environmental Assessor', 1),
(161, 12, 16, 'Housing Economist', 1),
(162, 12, 23, 'Architect', 1),
(163, 12, 15, 'Sustainability Specialist', 1),
(164, 12, 13, 'GIS Analyst', 1),
(165, 12, 18, 'Procurement Officer', 1),
(166, 12, 19, 'Legal Advisor', 1),
(167, 12, 14, 'Skills Development Coordinator', 1),
(168, 12, 12, 'Construction Manager', 1),
(169, 12, 24, 'Community Liaison', 1),
(170, 12, 18, 'Quality Officer', 1),
(171, 12, 11, 'Operations Planner', 1),
(172, 12, 15, 'Monitoring Specialist', 1),
(173, 12, 12, 'Facility Manager', 1),
(174, 12, 18, 'Social Officer', 1),
(175, 12, 13, 'Data Analyst', 1),
(176, 12, 10, 'Innovation Lead', 1),
(177, 14, 8, 'Safety Strategist', 1),
(178, 14, 11, 'Safety Planner', 1),
(179, 14, 10, 'Stakeholder Engagement Lead', 1),
(180, 14, 12, 'Project Manager', 1),
(181, 14, 13, 'Crime Analyst', 1),
(182, 14, 17, 'Social Impact Assessor', 1),
(183, 14, 23, 'Architect', 1),
(184, 14, 7, 'Security Engineer', 1),
(185, 14, 11, 'ICT Planner', 1),
(186, 14, 18, 'Procurement Officer', 1),
(187, 14, 19, 'Legal Advisor', 1),
(188, 14, 14, 'Skills Development Coordinator', 1),
(189, 14, 12, 'Construction Manager', 1),
(190, 14, 20, 'Security Installer', 1),
(191, 14, 24, 'Community Liaison', 1),
(192, 14, 14, 'Skills Coordinator', 1),
(193, 14, 18, 'Quality Officer', 1),
(194, 14, 11, 'Operations Planner', 1),
(195, 14, 15, 'Monitoring Specialist', 1),
(196, 14, 12, 'Infrastructure Manager', 1),
(197, 14, 13, 'Safety Analyst', 1),
(198, 14, 10, 'Innovation Lead', 1),
(199, 3, 8, 'Education Strategist', 1),
(200, 3, 11, 'Education Planner', 1),
(201, 3, 10, 'Stakeholder Engagement Lead', 1),
(202, 3, 12, 'Project Manager', 1),
(203, 3, 17, 'Social Impact Assessor', 1),
(204, 3, 16, 'Education Economist', 1),
(205, 3, 23, 'Architect', 1),
(206, 3, 15, 'ECD Specialist', 1),
(207, 3, 11, 'ICT Planner', 1),
(208, 3, 18, 'Procurement Officer', 1),
(209, 3, 19, 'Legal Advisor', 1),
(210, 3, 14, 'Skills Development Coordinator', 1),
(211, 3, 12, 'Construction Manager', 1),
(212, 3, 20, 'ICT Installer', 1),
(213, 3, 24, 'Community Liaison', 1),
(214, 3, 14, 'Skills Coordinator', 1),
(215, 3, 18, 'Quality Officer', 1),
(216, 3, 11, 'Operations Planner', 1),
(217, 3, 15, 'Monitoring Specialist', 1),
(218, 3, 12, 'Facility Manager', 1),
(219, 3, 13, 'Data Analyst', 1),
(220, 3, 10, 'Innovation Lead', 1),
(221, 4, 12, 'Project Manager', 1),
(222, 4, 15, 'Monitoring Specialist', 1),
(223, 4, 17, 'Environmental Risk Assessor', 1),
(224, 4, 18, 'Procurement Officer', 1),
(225, 4, 18, 'Quality Officer', 1),


-- --------------------------------------------------------

--
-- Table structure for table `tbl_role_master`
--

CREATE TABLE `tbl_role_master` (
  `role_id` int(11) NOT NULL,
  `app_id` int(11) NOT NULL COMMENT 'FK tbl_apps',
  `role_type` enum('global','project') NOT NULL DEFAULT 'project',
  `role_name` varchar(255) NOT NULL,
  `role_short_name` varchar(200) NOT NULL,
  `isAdmin` int(11) NOT NULL DEFAULT 0,
  `isActive` int(11) NOT NULL DEFAULT 1,
  `creation_date` datetime DEFAULT current_timestamp(),
  `created_by` varchar(50) DEFAULT NULL,
  `updation_date` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `updated_by` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `tbl_role_master`
--

INSERT INTO `tbl_role_master` (`role_id`, `app_id`, `role_type`, `role_name`, `role_short_name`, `isAdmin`, `isActive`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES
(1, 0, 'global', 'User', 'User', 0, 1, '2025-07-07 18:39:06', NULL, '2025-11-24 11:54:14', NULL),
(2, 2, 'global', 'Admin', 'Admin                  ', 1, 1, '2025-07-07 18:34:28', NULL, '2025-11-24 11:54:14', ''),
(3, 2, 'project', 'Evaluator', 'Evaluator', 0, 1, '2025-07-07 13:45:08', '1', '2025-07-09 17:29:07', NULL),
(4, 1, 'project', 'Approver-Level 1', 'Approver-Level 1', 0, 1, '2025-07-16 17:57:17', '1', NULL, NULL),
(5, 1, 'project', 'Approver-Level 2', 'Approver-Level 2', 0, 1, '2025-07-16 17:57:51', '1', NULL, NULL),
(6, 1, 'project', 'Approver-Level 3', 'Approver-Level 3', 0, 1, '2025-07-16 17:58:27', '1', '2025-07-16 18:00:08', NULL),
(7, 1, 'project', 'Engineer', 'Engineer', 0, 1, '2025-07-16 17:58:27', '1', '2025-07-16 18:00:08', NULL),
(8, 1, 'project', 'Strategist', 'Strategist', 0, 1, '2025-12-03 17:18:54', '1', '2025-12-03 17:18:54', NULL),
(9, 1, 'project', 'Designer', 'Designer', 0, 1, '2025-12-03 17:18:54', '1', '2025-12-03 17:18:54', NULL),
(10, 1, 'project', 'Lead', 'Lead', 0, 1, '2025-12-03 17:18:54', '1', '2025-12-03 17:18:54', NULL),
(11, 1, 'project', 'Planner', 'Planner', 0, 1, '2025-12-03 17:18:54', '1', '2025-12-03 17:18:54', NULL),
(12, 1, 'project', 'Manager', 'Manager', 0, 1, '2025-12-03 17:18:54', '1', '2025-12-03 17:18:54', NULL),
(13, 1, 'project', 'Analyst', 'Analyst', 0, 1, '2025-12-03 17:18:54', '1', '2025-12-03 17:18:54', NULL),
(14, 1, 'project', 'Coordinator', 'Coordinator', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(15, 1, 'project', 'Specialist', 'Specialist', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(16, 1, 'project', 'Economist', 'Economist', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(17, 1, 'project', 'Assessor', 'Assessor', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(18, 1, 'project', 'Officer', 'Officer', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(19, 1, 'project', 'Advisor', 'Advisor', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(20, 1, 'project', 'Installer', 'Installer', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(21, 1, 'project', 'Modeller', 'Modeller', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(22, 1, 'project', 'Agri-Economist', 'Agri-Economist', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(23, 1, 'project', 'Architect', 'Architect', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL),
(24, 1, 'project', 'Liaison', 'Liaison', 0, 1, '2025-12-03 17:21:48', '1', '2025-12-03 17:21:48', NULL);

--
-- Triggers `tbl_role_master`
--
DROP TRIGGER IF EXISTS `tr_role_right_association_delete`;
DELIMITER $$
CREATE TRIGGER `tr_role_right_association_delete` AFTER DELETE ON `tbl_role_master` FOR EACH ROW BEGIN

  delete from tbl_role_right_association where role_id=old.role_id;

END
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `tr_role_right_association_insert`;
DELIMITER $$
CREATE TRIGGER `tr_role_right_association_insert` AFTER INSERT ON `tbl_role_master` FOR EACH ROW BEGIN



  INSERT INTO tbl_role_right_association(role_id, right_id, value)  

   SELECT new.role_id, b.right_id,-1 FROM tbl_right_master as b;



END
$$
DELIMITER ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_department_roles`
--
ALTER TABLE `tbl_department_roles`
  ADD PRIMARY KEY (`department_role_id`),
  ADD UNIQUE KEY `uniq_dept_role` (`department_id`,`role_id`,`role_speciality`) USING BTREE;

--
-- Indexes for table `tbl_role_master`
--
ALTER TABLE `tbl_role_master`
  ADD PRIMARY KEY (`role_id`),
  ADD UNIQUE KEY `app_id_role_name` (`app_id`,`role_name`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_department_roles`
--
ALTER TABLE `tbl_department_roles`
  MODIFY `department_role_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=221;
COMMIT;