--
-- Host: localhost    Database: db_leaderslinked_dev
-- ------------------------------------------------------


--
-- Table structure for table `tbl_abuse_reports`
--

CREATE TABLE `tbl_abuse_reports` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `network_id` int unsigned NOT NULL,
  `user_reporting_id` int unsigned NOT NULL,
  `user_reported_id` int unsigned NOT NULL,
  `related_id` int unsigned DEFAULT NULL,
  `reason` enum('s','o','d','a','t','ot') NOT NULL DEFAULT 's' COMMENT 's = sexuality, o = ofensive, d = discrimination, a = addictions, t = terrorism,  ot = other  ',
  `status` enum('p','r','c') NOT NULL DEFAULT 'p' COMMENT 'p = pending, r = in review, c = completed ',
  `type` enum('f','p','m','c','cu','cg') NOT NULL COMMENT 'f = feed, p = post, m = inmail message, c = comment, cu = chat user, cg = chat group ',
  `comment` varchar(500) DEFAULT NULL,
  `review` varchar(500) DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_reporting_id` (`user_reporting_id`,`related_id`,`type`),
  KEY `network_id` (`network_id`),
  KEY `user_reported_id` (`user_reported_id`),
  CONSTRAINT `tbl_abuse_reports_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_abuse_reports_ibfk_2` FOREIGN KEY (`user_reporting_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_abuse_reports_ibfk_3` FOREIGN KEY (`user_reported_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`test`@`127.0.0.1`*/ /*!50003 TRIGGER `tr_abuse_report_before_insert` BEFORE INSERT ON `tbl_abuse_reports` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_aptitudes`
--

CREATE TABLE `tbl_aptitudes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_aptitude_before_insert` BEFORE INSERT ON `tbl_aptitudes` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_behaviors`
--

CREATE TABLE `tbl_behaviors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `behavior_id_default` int unsigned DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `company_id` int unsigned DEFAULT NULL,
  `external_code` varchar(15) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `behavior_id_default` (`behavior_id_default`),
  KEY `external_code` (`external_code`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_behaviors_before_insert` BEFORE INSERT ON `tbl_behaviors` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_calendar_events`
--

CREATE TABLE `tbl_calendar_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  `type` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `relational_id` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `type` (`type`,`relational_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_calendar_events_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=166 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_chat_group_messages`
--

CREATE TABLE `tbl_chat_group_messages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `group_id` int unsigned NOT NULL,
  `sender_id` int unsigned NOT NULL,
  `content` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `type` enum('text','image','video','document') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'text',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `group_id` (`group_id`),
  KEY `sender_id` (`sender_id`),
  CONSTRAINT `tbl_chat_group_messages_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `tbl_chat_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_chat_group_messages_ibfk_2` FOREIGN KEY (`sender_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_chat_group_messages_before_insert` BEFORE INSERT ON `tbl_chat_group_messages` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_chat_group_user_messages`
--

CREATE TABLE `tbl_chat_group_user_messages` (
  `group_id` int unsigned NOT NULL,
  `message_id` int unsigned NOT NULL,
  `receiver_id` int unsigned NOT NULL,
  `recd` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `seen` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('p','d','b') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = publish, d = deleted, r = reported',
  UNIQUE KEY `message_id` (`message_id`,`receiver_id`),
  KEY `group_id` (`group_id`),
  KEY `receiver_id` (`receiver_id`),
  CONSTRAINT `tbl_chat_group_user_messages_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `tbl_chat_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_chat_group_user_messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_chat_group_user_messages_ibfk_3` FOREIGN KEY (`message_id`) REFERENCES `tbl_chat_group_messages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_chat_group_users`
--

CREATE TABLE `tbl_chat_group_users` (
  `group_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `owner` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `open` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  UNIQUE KEY `group_id` (`group_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_chat_group_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_chat_group_users_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `tbl_chat_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_chat_groups`
--

CREATE TABLE `tbl_chat_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `high_performance_team_group_id` int unsigned DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `high_performance_team_groups_id` (`high_performance_team_group_id`),
  KEY `high_performance_team_groups_i_2` (`high_performance_team_group_id`),
  CONSTRAINT `tbl_chat_groups_ibfk_1` FOREIGN KEY (`high_performance_team_group_id`) REFERENCES `tbl_high_performance_team_groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_chat_groups_before_insert` BEFORE INSERT ON `tbl_chat_groups` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_chat_messages`
--

CREATE TABLE `tbl_chat_messages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `chat_id` int unsigned NOT NULL,
  `from_id` int unsigned NOT NULL,
  `to_id` int unsigned NOT NULL,
  `content` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `recd` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `seen` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `type` enum('text','image','video','document') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'text',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('p','d','r') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = publish, d = deleted, r = reported',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `chat_id` (`chat_id`),
  KEY `from_id` (`from_id`),
  KEY `to_id` (`to_id`),
  CONSTRAINT `tbl_chat_messages_ibfk_1` FOREIGN KEY (`chat_id`) REFERENCES `tbl_chat_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_chat_messages_ibfk_2` FOREIGN KEY (`from_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_chat_messages_ibfk_3` FOREIGN KEY (`to_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=543 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_chat_messages_before_insert` BEFORE INSERT ON `tbl_chat_messages` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_chat_users`
--

CREATE TABLE `tbl_chat_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id1` int unsigned NOT NULL,
  `user_id2` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `user_open1` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `user_open2` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id1` (`user_id1`),
  KEY `user_id2` (`user_id2`),
  CONSTRAINT `tbl_chat_users_ibfk_1` FOREIGN KEY (`user_id1`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_chat_users_ibfk_2` FOREIGN KEY (`user_id2`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_chat_users_before_insert` BEFORE INSERT ON `tbl_chat_users` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_comments`
--

CREATE TABLE `tbl_comments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `feed_id` int unsigned DEFAULT NULL,
  `comment` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('p','s','d','b') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = publish, s = saved, d = deleted, r = reported',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `parent_id` int unsigned DEFAULT NULL,
  `network_id` int unsigned NOT NULL,
  `post_id` int unsigned DEFAULT NULL,
  `relational` enum('f','p','ka','mc') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'f' COMMENT 'f = feed, p = post, ka = knowledge area, mc = my coach',
  `knowledge_area_id` int unsigned DEFAULT NULL,
  `my_coach_answer_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `feed_id` (`feed_id`),
  KEY `parent_id` (`parent_id`),
  KEY `network_id` (`network_id`),
  KEY `post_id` (`post_id`),
  KEY `knowledge_area_id` (`knowledge_area_id`),
  CONSTRAINT `tbl_comments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_comments_ibfk_2` FOREIGN KEY (`feed_id`) REFERENCES `tbl_feeds` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_comments_ibfk_3` FOREIGN KEY (`parent_id`) REFERENCES `tbl_comments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_comments_ibfk_4` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`),
  CONSTRAINT `tbl_comments_ibfk_5` FOREIGN KEY (`post_id`) REFERENCES `tbl_posts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_comments_ibfk_6` FOREIGN KEY (`knowledge_area_id`) REFERENCES `tbl_knowledge_area_contents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=927 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_comment_before_insert` BEFORE INSERT ON `tbl_comments` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_companies`
--

CREATE TABLE `tbl_companies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `industry_id` int unsigned NOT NULL,
  `company_size_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `image` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `cover` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `header` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `footer` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `description` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `website` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `foundation_year` int unsigned NOT NULL,
  `status` enum('a','i','p','r','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a = active,  i = inactive, p = pending, r = rejected, d = deleted ',
  `facebook` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `twitter` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `instagram` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `internal` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `default_for_network` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `network_id` int unsigned NOT NULL,
  `default_for_followers` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`),
  KEY `industry_id` (`industry_id`),
  KEY `company_size_id` (`company_size_id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_companies_ibfk_1` FOREIGN KEY (`industry_id`) REFERENCES `tbl_industries` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_companies_ibfk_2` FOREIGN KEY (`company_size_id`) REFERENCES `tbl_company_sizes` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_companies_ibfk_3` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_before_insert` BEFORE INSERT ON `tbl_companies` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_company_followers`
--

CREATE TABLE `tbl_company_followers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `follower_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`,`follower_id`),
  KEY `follower_id` (`follower_id`),
  CONSTRAINT `tbl_company_followers_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_company_followers_ibfk_2` FOREIGN KEY (`follower_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_company_locations`
--

CREATE TABLE `tbl_company_locations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `location_id` int unsigned NOT NULL,
  `is_main` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'y - Yes, n - No',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `tbl_company_locations_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_company_locations_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_location_before_insert` BEFORE INSERT ON `tbl_company_locations` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_company_roles`
--

CREATE TABLE `tbl_company_roles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `role_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_id` (`company_id`,`role_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `tbl_company_roles_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_company_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `tbl_roles` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_company_services`
--

CREATE TABLE `tbl_company_services` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `service_id` int unsigned NOT NULL,
  `status` enum('a','c','s','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'i' COMMENT 'a = active, c = cancelled, s = suspended,  i = inactive',
  `paid_from` datetime DEFAULT NULL,
  `paid_to` datetime DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `service_id` (`service_id`),
  CONSTRAINT `tbl_company_services_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_company_services_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `tbl_services` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_company_sizes`
--

CREATE TABLE `tbl_company_sizes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `minimum_no_of_employee` int unsigned NOT NULL,
  `maximum_no_of_employee` int unsigned NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_comany_size_before_insert` BEFORE INSERT ON `tbl_company_sizes` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_company_user_roles`
--

CREATE TABLE `tbl_company_user_roles` (
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `role_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `company_id` (`company_id`,`user_id`,`role_id`),
  KEY `company_id_2` (`company_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `tbl_company_user_roles_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_company_user_roles_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_company_user_roles_ibfk_3` FOREIGN KEY (`role_id`) REFERENCES `tbl_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_company_users`
--

CREATE TABLE `tbl_company_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `status` enum('p','s','a','r','c','aa') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'p = pending, s = sent, a - accepted, r - rejected, c - cancelled, aa - admin will add ',
  `owner` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n' COMMENT 'y = Yes, n = No',
  `creator` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n' COMMENT 'y = Yes, n = No',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `backend` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n' COMMENT 'y = Yes, n = No',
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_company_users_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_company_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=964 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_competencies`
--

CREATE TABLE `tbl_competencies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `competency_type_id` int unsigned NOT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `behaviors` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `competency_id_default` int unsigned DEFAULT NULL,
  `external_code` varchar(15) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `competency_type_id` (`competency_type_id`),
  KEY `company_id` (`company_id`),
  KEY `competency_id_default` (`competency_id_default`),
  KEY `external_code` (`external_code`),
  CONSTRAINT `tbl_competencies_ibfk_1` FOREIGN KEY (`competency_type_id`) REFERENCES `tbl_competency_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_competencies_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_competencies_ibfk_3` FOREIGN KEY (`competency_id_default`) REFERENCES `tbl_competencies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_competency_before_insert` BEFORE INSERT ON `tbl_competencies` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_competency_behaviors`
--

CREATE TABLE `tbl_competency_behaviors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `behavior_id` int unsigned NOT NULL,
  `competency_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_description_id` (`behavior_id`),
  KEY `competency_id` (`competency_id`),
  CONSTRAINT `tbl_competency_behaviors_ibfk_1` FOREIGN KEY (`competency_id`) REFERENCES `tbl_competencies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_competency_behaviors_ibfk_2` FOREIGN KEY (`behavior_id`) REFERENCES `tbl_behaviors` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_competency_types`
--

CREATE TABLE `tbl_competency_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `competency_type_id_default` int unsigned DEFAULT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `external_code` varchar(15) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `competency_type_id_default` (`competency_type_id_default`),
  KEY `external_code` (`external_code`),
  CONSTRAINT `tbl_competency_types_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_competency_types_ibfk_2` FOREIGN KEY (`competency_type_id_default`) REFERENCES `tbl_competency_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_competency_type_before_insert` BEFORE INSERT ON `tbl_competency_types` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_connections`
--

CREATE TABLE `tbl_connections` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `request_from` int unsigned NOT NULL,
  `request_to` int unsigned NOT NULL,
  `status` enum('s','a','r','c') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 's - Sent, a - Accepted, r - Rejected, c - Cancelled ',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_from` (`request_from`,`request_to`),
  KEY `request_to` (`request_to`),
  CONSTRAINT `tbl_connections_ibfk_1` FOREIGN KEY (`request_from`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_connections_ibfk_2` FOREIGN KEY (`request_to`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_contact_us`
--

CREATE TABLE `tbl_contact_us` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('c','f') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'c' COMMENT 'c - Contact Us Query, f - Feedback',
  `is_registered_user` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `first_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `last_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `email` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `subject` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `message` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_contact_us_replies`
--

CREATE TABLE `tbl_contact_us_replies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `contact_us_id` int unsigned NOT NULL,
  `message` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `replied_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `contact_us_id` (`contact_us_id`),
  CONSTRAINT `tbl_contact_us_replies_ibfk_1` FOREIGN KEY (`contact_us_id`) REFERENCES `tbl_contact_us` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_content_reactions`
--

CREATE TABLE `tbl_content_reactions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `feed_id` int unsigned DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `post_id` int unsigned DEFAULT NULL,
  `relational` enum('f','p','ka','mc') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'f' COMMENT 'f = feed, p = post, ka = knowledge area, mc = my coach',
  `reaction` enum('r','s','l','i','f') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'r' COMMENT 'r = recommended, s = support, l = love, i = interest, f = fun',
  `knowledge_area_id` int unsigned DEFAULT NULL,
  `my_coach_answer_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `feed_id` (`feed_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `post_id` (`post_id`),
  KEY `knowledge_area_id` (`knowledge_area_id`),
  CONSTRAINT `tbl_content_reactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_content_reactions_ibfk_2` FOREIGN KEY (`feed_id`) REFERENCES `tbl_feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_content_reactions_ibfk_3` FOREIGN KEY (`post_id`) REFERENCES `tbl_posts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_content_reactions_ibfk_4` FOREIGN KEY (`knowledge_area_id`) REFERENCES `tbl_knowledge_area_contents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=335 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_conversations`
--

CREATE TABLE `tbl_conversations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sender_id` int unsigned NOT NULL,
  `receiver_id` int unsigned DEFAULT NULL,
  `sender_status` enum('p','n','t','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = pending, n - Normal, t - Trashed, d - Deleted',
  `receiver_status` enum('p','n','t','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = pending, n - Normal, t - Trashed, d - Deleted',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `receiver_id` (`receiver_id`),
  KEY `sender_id_and_receiver_id` (`sender_id`,`receiver_id`) USING BTREE,
  CONSTRAINT `tbl_conversations_ibfk_6` FOREIGN KEY (`receiver_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_conversations_ibfk_7` FOREIGN KEY (`sender_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_conversations_ibfk_8` FOREIGN KEY (`receiver_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_conversations_ibfk_9` FOREIGN KEY (`receiver_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=476 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_countries`
--

CREATE TABLE `tbl_countries` (
  `code` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `country` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `city` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `code` (`code`),
  KEY `country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_currencies`
--

CREATE TABLE `tbl_currencies` (
  `currency` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_daily_pulse`
--

CREATE TABLE `tbl_daily_pulse` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `uuid` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `privacy` enum('p','c') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'c' COMMENT 'c = company, p = public',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_daily_pulse_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_daily_pulse_before_insert` BEFORE INSERT ON `tbl_daily_pulse` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_daily_pulse_emojis`
--

CREATE TABLE `tbl_daily_pulse_emojis` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `image` varchar(256) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `points` int unsigned NOT NULL,
  `order` int unsigned NOT NULL,
  `type` enum('how-are-you-feel','climate-on-your-organization') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'i' COMMENT 'i = inactive, a = active',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_daily_pulse_emojis_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_daily_pulse_emojis_before_insert` BEFORE INSERT ON `tbl_daily_pulse_emojis` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_daily_pulse_records`
--

CREATE TABLE `tbl_daily_pulse_records` (
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `emoji_id` int unsigned NOT NULL,
  `date` date NOT NULL,
  `type` enum('how-are-you-feel','climate-on-your-organization') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `engagement` int unsigned NOT NULL DEFAULT '0',
  `points` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`,`date`,`type`),
  KEY `company_id` (`company_id`),
  KEY `emoji_id` (`emoji_id`),
  CONSTRAINT `tbl_daily_pulse_records_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_daily_pulse_records_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_daily_pulse_records_ibfk_3` FOREIGN KEY (`emoji_id`) REFERENCES `tbl_daily_pulse_emojis` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_degrees`
--

CREATE TABLE `tbl_degrees` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_degree_before_insert` BEFORE INSERT ON `tbl_degrees` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_device_history`
--

CREATE TABLE `tbl_device_history` (
  `device_id` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `device_id` (`device_id`,`user_id`,`ip`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_device_history_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `tbl_devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_device_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_discovery_contact_blacklist`
--

CREATE TABLE `tbl_discovery_contact_blacklist` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `blacklist_reason_id` int unsigned NOT NULL,
  `contact_id` int unsigned NOT NULL,
  `notes` text COLLATE utf8mb3_unicode_ci,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `blacklist_reason_id` (`blacklist_reason_id`),
  KEY `user_id` (`user_id`),
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `tbl_discovery_contact_blacklist_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_discovery_contact_blacklist_ibfk_2` FOREIGN KEY (`blacklist_reason_id`) REFERENCES `tbl_discovery_contact_blacklist_reasons` (`id`),
  CONSTRAINT `tbl_discovery_contact_blacklist_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_discovery_contact_blacklist_ibfk_4` FOREIGN KEY (`contact_id`) REFERENCES `tbl_discovery_contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_discovery_contact_blacklist_before_insert` BEFORE INSERT ON `tbl_discovery_contact_blacklist` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_discovery_contact_blacklist_reasons`
--

CREATE TABLE `tbl_discovery_contact_blacklist_reasons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_discovery_contact_blacklist_reasons_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_discovery_contact_blacklist_reasons_before_insert` BEFORE INSERT ON `tbl_discovery_contact_blacklist_reasons` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_discovery_contact_interaction_types`
--

CREATE TABLE `tbl_discovery_contact_interaction_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `default` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_discovery_contact_interaction_types_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_discovery_contact_interaction_type_before_insert` BEFORE INSERT ON `tbl_discovery_contact_interaction_types` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_discovery_contact_interactions`
--

CREATE TABLE `tbl_discovery_contact_interactions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `interaction_type_id` int unsigned NOT NULL,
  `contact_id` int unsigned NOT NULL,
  `notes` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `interaction_type_id` (`interaction_type_id`),
  KEY `user_id` (`user_id`),
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `tbl_discovery_contact_interactions_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_discovery_contact_interactions_ibfk_2` FOREIGN KEY (`interaction_type_id`) REFERENCES `tbl_discovery_contact_interaction_types` (`id`),
  CONSTRAINT `tbl_discovery_contact_interactions_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_discovery_contact_interactions_ibfk_4` FOREIGN KEY (`contact_id`) REFERENCES `tbl_discovery_contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21096 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_discovery_contact_interactions_before_insert` BEFORE INSERT ON `tbl_discovery_contact_interactions` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_discovery_contact_logs`
--

CREATE TABLE `tbl_discovery_contact_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `contact_id` int unsigned NOT NULL,
  `activity` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `details` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `tbl_discovery_contact_logs_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_discovery_contact_logs_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_discovery_contact_logs_ibfk_3` FOREIGN KEY (`contact_id`) REFERENCES `tbl_discovery_contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20901 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_discovery_contact_logs_before_insert` BEFORE INSERT ON `tbl_discovery_contact_logs` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_discovery_contacts`
--

CREATE TABLE `tbl_discovery_contacts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `first_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `last_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `corporate_email` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `position` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `country` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `state` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `city` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `personal_email` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `phone` varchar(35) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `phone_extension` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `celular` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `whatsapp` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `linkedin` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sector` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `scholarship` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_discovery_contacts_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21095 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_discovery_contacts_before_insert` BEFORE INSERT ON `tbl_discovery_contacts` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_email_templates`
--

CREATE TABLE `tbl_email_templates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `content` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `network_id` int unsigned NOT NULL,
  `code` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `email_template_default_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`,`network_id`),
  KEY `network_id` (`network_id`),
  KEY `email_template_default_id` (`email_template_default_id`),
  CONSTRAINT `tbl_email_templates_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`),
  CONSTRAINT `tbl_email_templates_ibfk_2` FOREIGN KEY (`email_template_default_id`) REFERENCES `tbl_email_templates` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_emails`
--

CREATE TABLE `tbl_emails` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `status` enum('p','c','e') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = pending, c = completed, e = error',
  `response` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `tried` smallint unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `added_on` (`added_on`),
  KEY `added_on_2` (`added_on`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_engagement`
--

CREATE TABLE `tbl_engagement` (
  `company_id` int unsigned NOT NULL,
  `daily_pulse_how_are_you_feel` int unsigned NOT NULL DEFAULT '0',
  `daily_pulse_climate_on_your_organization` int unsigned NOT NULL DEFAULT '0',
  `internal_survey` int unsigned NOT NULL DEFAULT '0',
  `feed_survey` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_engagement_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_engagement_records`
--

CREATE TABLE `tbl_engagement_records` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `points` int unsigned NOT NULL,
  `date` date NOT NULL,
  `type` enum('daily-pulse-how-are-you-feel','daily-pulse-climate-on-your-organization','internal-survey','gift') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `reward_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `reward_id` (`reward_id`),
  CONSTRAINT `tbl_engagement_records_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_engagement_records_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_engagement_records_ibfk_3` FOREIGN KEY (`reward_id`) REFERENCES `tbl_engagement_rewards` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_engagement_rewards`
--

CREATE TABLE `tbl_engagement_rewards` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `points` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `image` varchar(256) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'i' COMMENT 'i = inactive, a = active',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_engagement_rewards_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_engagement_rewards_before_insert` BEFORE INSERT ON `tbl_engagement_rewards` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_engagement_users`
--

CREATE TABLE `tbl_engagement_users` (
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `points` int unsigned NOT NULL,
  `date` date NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `company_id` (`company_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_engagement_users_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_engagement_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_fast_survey_votes`
--

CREATE TABLE `tbl_fast_survey_votes` (
  `fast_survey_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `vote` smallint unsigned NOT NULL DEFAULT '1',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `fast_survey_id` (`fast_survey_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_fast_survey_votes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_fast_survey_votes_ibfk_2` FOREIGN KEY (`fast_survey_id`) REFERENCES `tbl_fast_surveys` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_fast_surveys_after_insert` AFTER INSERT ON `tbl_fast_survey_votes` FOR EACH ROW BEGIN
	
	IF NEW.vote = 1 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes1` = `votes1` + 1
	   WHERE `id` = NEW.fast_survey_id;
	END IF;

	IF NEW.vote = 2 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes2` = `votes2` + 1
	   WHERE `id` = NEW.fast_survey_id;
	END IF;

	IF NEW.vote = 3 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes3` = `votes3` + 1
	   WHERE `id` = NEW.fast_survey_id;
	END IF;

	IF NEW.vote = 4 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes4` = `votes4` + 1
	   WHERE `id` = NEW.fast_survey_id;
	END IF;

	IF NEW.vote = 5 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes5` = `votes5` + 1
	   WHERE `id` = NEW.fast_survey_id;
	END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_fast_surveys_after_delete` AFTER DELETE ON `tbl_fast_survey_votes` FOR EACH ROW BEGIN
	
	IF OLD.vote = 1 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes1` = `votes1` - 1
	   WHERE `id` = OLD.fast_survey_id;
	END IF;

	IF OLD.vote = 2 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes2` = `votes2` - 1
	   WHERE `id` = OLD.fast_survey_id;
	END IF;

	IF OLD.vote = 3 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes3` = `votes3` - 1
	   WHERE `id` = OLD.fast_survey_id;
	END IF;

	IF OLD.vote = 4 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes4` = `votes4` - 1
	   WHERE `id` = OLD.fast_survey_id;
	END IF;

	IF OLD.vote = 5 THEN 
	   UPDATE `tbl_fast_surveys` SET `votes5` = `votes5` - 1
	   WHERE `id` = OLD.fast_survey_id;
	END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_fast_surveys`
--

CREATE TABLE `tbl_fast_surveys` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `network_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `question` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `number_of_answers` smallint unsigned NOT NULL DEFAULT '2',
  `answer1` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `answer2` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `answer3` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `answer4` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `answer5` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `votes1` int unsigned NOT NULL DEFAULT '0',
  `votes2` int unsigned NOT NULL DEFAULT '0',
  `votes3` int unsigned NOT NULL DEFAULT '0',
  `votes4` int unsigned NOT NULL DEFAULT '0',
  `votes5` int unsigned NOT NULL DEFAULT '0',
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a = active, i = inactive',
  `privacy` enum('p','c') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'c' COMMENT 'c = company, p = public',
  `result_type` enum('pu','pr') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'pr' COMMENT 'pr = private, pu = public',
  `duration_days` int unsigned NOT NULL DEFAULT '0',
  `duration_hours` int unsigned NOT NULL DEFAULT '0',
  `duration_minutes` int unsigned NOT NULL DEFAULT '0',
  `expire_on` datetime NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `network_id` (`network_id`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_fast_surveys_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`),
  CONSTRAINT `tbl_fast_surveys_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_fast_surveys_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_fast_surveys_before_insert` BEFORE INSERT ON `tbl_fast_surveys` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_feeds`
--

CREATE TABLE `tbl_feeds` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `group_id` int unsigned DEFAULT NULL,
  `topic_id` int unsigned DEFAULT NULL,
  `shared_with` enum('p','c') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'p - Public, c - Connections',
  `title` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT '',
  `description` text COLLATE utf8mb3_unicode_ci,
  `type` enum('u','a','j','c','g','hptg','mytq','myta','dc') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'u - Update, a - Article, j - Job, c - Company, g - Group, hptg = High Performance Team Group, mytq = My Trainer Question, myta = My Trainer Answer, dc = Development and Coment',
  `posted_or_shared` enum('p','s') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'p - Posted, s - Shared',
  `shared_feed_id` int unsigned DEFAULT NULL,
  `shared_job_id` int unsigned DEFAULT NULL,
  `shared_group_id` int unsigned DEFAULT NULL,
  `shared_company_id` int unsigned DEFAULT NULL,
  `status` enum('p','s','d','b') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = publish, s = saved, d = deleted, r = reported',
  `file_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `file_type` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `file_image_preview` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `total_shared` int unsigned NOT NULL DEFAULT '0',
  `total_comments` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `high_performance_group_id` int unsigned DEFAULT NULL,
  `myt_id` int DEFAULT NULL,
  `related_feed` int unsigned DEFAULT NULL,
  `link_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `link_type` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `link_media` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `priority` enum('u','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT 'n' COMMENT 'u = urgent, n= normal',
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `network_id` int unsigned NOT NULL,
  `total_external_shared` int unsigned NOT NULL DEFAULT '0',
  `fast_survey_id` int unsigned DEFAULT NULL,
  `publish_on` date NOT NULL DEFAULT (curdate()),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `shared_feed_id` (`shared_feed_id`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  KEY `shared_job_id` (`shared_job_id`),
  KEY `group_id` (`group_id`),
  KEY `shared_company_id` (`shared_company_id`),
  KEY `shared_group_id` (`shared_group_id`),
  KEY `high_performance_group_id` (`high_performance_group_id`),
  KEY `topic_id` (`topic_id`),
  KEY `network_id` (`network_id`),
  KEY `fast_survey_id` (`fast_survey_id`),
  CONSTRAINT `tbl_feeds_ibfk_1` FOREIGN KEY (`shared_feed_id`) REFERENCES `tbl_feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_10` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`),
  CONSTRAINT `tbl_feeds_ibfk_11` FOREIGN KEY (`fast_survey_id`) REFERENCES `tbl_fast_surveys` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_4` FOREIGN KEY (`shared_job_id`) REFERENCES `tbl_jobs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_5` FOREIGN KEY (`group_id`) REFERENCES `tbl_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_6` FOREIGN KEY (`shared_company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_7` FOREIGN KEY (`shared_group_id`) REFERENCES `tbl_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_8` FOREIGN KEY (`high_performance_group_id`) REFERENCES `tbl_high_performance_team_groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_feeds_ibfk_9` FOREIGN KEY (`topic_id`) REFERENCES `tbl_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1288 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_feed_before_insert` BEFORE INSERT ON `tbl_feeds` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_group_members`
--

CREATE TABLE `tbl_group_members` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `joining_request_on` datetime NOT NULL,
  `status` enum('aj','jr','a','r','c','aa') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'aj - Auto joined, jr- Joining Requested,  a - Accepted, c - Cancelled r - Rejected, aa - Added by Admin',
  `joined_on` datetime DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_id` (`group_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `tbl_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_group_members_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=182 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_group_topics`
--

CREATE TABLE `tbl_group_topics` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int unsigned NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `tbl_group_topics_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `tbl_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_group_types`
--

CREATE TABLE `tbl_group_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_group_type_before_insert` BEFORE INSERT ON `tbl_group_types` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_groups`
--

CREATE TABLE `tbl_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `image` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `cover` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `description` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `type_id` int unsigned NOT NULL,
  `industry_id` int unsigned NOT NULL,
  `is_high` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `website` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `privacy` enum('pr','pu') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'pu' COMMENT 'pr - Private, pu - Public',
  `status` enum('a','i','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a - Active, i - inactive, d = deleted',
  `accessibility` enum('aj','rj','aa') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'rj' COMMENT 'aj - Auto join, rj - Request to join, aa - Admin will add',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `network_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  KEY `type_id` (`type_id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_groups_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_groups_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_groups_ibfk_3` FOREIGN KEY (`type_id`) REFERENCES `tbl_group_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_groups_ibfk_4` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_group_before_insert` BEFORE INSERT ON `tbl_groups` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_categories`
--

CREATE TABLE `tbl_habits_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(128) NOT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'i' COMMENT 'i = inactive, a = active',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_habits_categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_categories_insert` BEFORE INSERT ON `tbl_habits_categories` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_content`
--

CREATE TABLE `tbl_habits_content` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  `description` text,
  `order` int unsigned NOT NULL DEFAULT '0',
  `type` enum('image','video','audio','document') DEFAULT NULL,
  `file` varchar(500) DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('a','i') NOT NULL DEFAULT 'i' COMMENT 'i = inactive, a = active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_habits_content_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_content_insert` BEFORE INSERT ON `tbl_habits_content` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_emojis`
--

CREATE TABLE `tbl_habits_emojis` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(128) NOT NULL,
  `image` varchar(256) DEFAULT NULL,
  `code` varchar(10) NOT NULL,
  `order` int unsigned NOT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'i' COMMENT 'i = inactive, a = active',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `code` (`code`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_habits_emojis_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_emojis_insert` BEFORE INSERT ON `tbl_habits_emojis` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_goal_skills`
--

CREATE TABLE `tbl_habits_goal_skills` (
  `network_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `goal_id` int unsigned NOT NULL,
  `skill_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`,`goal_id`,`skill_id`),
  KEY `network_id` (`network_id`),
  KEY `goal_id` (`goal_id`),
  KEY `skill_id` (`skill_id`),
  CONSTRAINT `tbl_habits_goal_skills_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_goal_skills_ibfk_2` FOREIGN KEY (`goal_id`) REFERENCES `tbl_habits_goals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_goal_skills_ibfk_3` FOREIGN KEY (`skill_id`) REFERENCES `tbl_habits_skills` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_goal_skills_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_habits_goals`
--

CREATE TABLE `tbl_habits_goals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text,
  `value` decimal(10,2) NOT NULL DEFAULT '0.00',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `network_id` (`network_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_habits_goals_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_goals_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_goals_insert` BEFORE INSERT ON `tbl_habits_goals` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_paradigms`
--

CREATE TABLE `tbl_habits_paradigms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(64) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `network_id` (`network_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_habits_paradigms_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_paradigms_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_paradigms_insert` BEFORE INSERT ON `tbl_habits_paradigms` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_purposes`
--

CREATE TABLE `tbl_habits_purposes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(64) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `network_id` (`network_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_habits_purposes_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_purposes_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_purposes_insert` BEFORE INSERT ON `tbl_habits_purposes` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_skills`
--

CREATE TABLE `tbl_habits_skills` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int unsigned NOT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text,
  `monday_active` smallint unsigned NOT NULL DEFAULT '1',
  `tuesday_active` smallint unsigned NOT NULL DEFAULT '1',
  `wednesday_active` smallint unsigned NOT NULL DEFAULT '1',
  `thursday_active` smallint unsigned NOT NULL DEFAULT '1',
  `friday_active` smallint unsigned NOT NULL DEFAULT '1',
  `saturday_active` smallint unsigned NOT NULL DEFAULT '1',
  `sunday_active` smallint unsigned NOT NULL DEFAULT '1',
  `monday_time` time NOT NULL DEFAULT '08:00:00',
  `tuesday_time` time NOT NULL DEFAULT '08:00:00',
  `wednesday_time` time NOT NULL DEFAULT '08:00:00',
  `thursday_time` time NOT NULL DEFAULT '08:00:00',
  `friday_time` time NOT NULL DEFAULT '08:00:00',
  `saturday_time` time NOT NULL DEFAULT '08:00:00',
  `sunday_time` time NOT NULL DEFAULT '08:00:00',
  `quantitative_value` decimal(10,2) NOT NULL DEFAULT '0.00',
  `qualitative_description` varchar(50) DEFAULT NULL,
  `notification_10min_before` smallint unsigned NOT NULL DEFAULT '1',
  `notification_30min_before` smallint unsigned NOT NULL DEFAULT '1',
  `intelligence` enum('emotional','physical','intellectual','consciousness') NOT NULL,
  `template` enum('y','n') NOT NULL DEFAULT 'n',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `network_id` (`network_id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_habits_skills_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_skills_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_skills_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_skills_insert` BEFORE INSERT ON `tbl_habits_skills` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_skills_registers`
--

CREATE TABLE `tbl_habits_skills_registers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `network_id` int unsigned NOT NULL,
  `skill_id` int unsigned NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `date` datetime NOT NULL,
  `quantitative_value` decimal(10,2) NOT NULL DEFAULT '0.00',
  `qualitative_description` text,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `skill_date` (`skill_id`,`date`),
  KEY `network_id` (`network_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_habits_skills_registers_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_skills_registers_ibfk_2` FOREIGN KEY (`skill_id`) REFERENCES `tbl_habits_skills` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_skills_registers_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_skills_registers_insert` BEFORE INSERT ON `tbl_habits_skills_registers` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_habits_user_logs_categories`
--

CREATE TABLE `tbl_habits_user_logs_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `category_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `code` varchar(10) NOT NULL,
  `date` varchar(10) NOT NULL,
  `time` varchar(8) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`,`category_id`,`date`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `tbl_habits_user_logs_categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_habits_user_logs_categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `tbl_habits_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_habits_user_logs_categories_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_habits_user_logs_content`
--

CREATE TABLE `tbl_habits_user_logs_content` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `content_id` int unsigned NOT NULL,
  `date` varchar(10) NOT NULL,
  `time` varchar(8) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`,`content_id`,`date`),
  KEY `user_id_2` (`user_id`,`date`),
  KEY `content_id` (`content_id`),
  CONSTRAINT `tbl_habits_user_logs_content_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_habits_user_logs_content_ibfk_2` FOREIGN KEY (`content_id`) REFERENCES `tbl_habits_content` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_habits_user_logs_content_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_habits_users`
--

CREATE TABLE `tbl_habits_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int unsigned NOT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned NOT NULL,
  `access` enum('u','p','r','c','s') NOT NULL DEFAULT 'u' COMMENT 'u = unlimited, p = pay period, r = revoke, c=  cancelled, s = supended',
  `paid_from` datetime DEFAULT NULL,
  `paid_to` datetime DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`company_id`,`network_id`),
  KEY `network_id` (`network_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_habits_users_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_users_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_users_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_habits_values`
--

CREATE TABLE `tbl_habits_values` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(64) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `network_id` (`network_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_habits_values_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_habits_values_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_habits_values_insert` BEFORE INSERT ON `tbl_habits_values` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_high_performance_team_group_members`
--

CREATE TABLE `tbl_high_performance_team_group_members` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `type` enum('c','a','m','u') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'c - Creator, a = administrator, m = moderator, u = user, ',
  `status` enum('a','aa','c','i','r') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Accepted, aa - Added by Admin, c - Cancelled, i = invited, r - Rejected, ',
  `joined_on` datetime NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_id` (`group_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_high_performance_team_group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `tbl_high_performance_team_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_high_performance_team_group_members_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_high_performance_team_groups`
--

CREATE TABLE `tbl_high_performance_team_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `title` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','d','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, d - Delete, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_high_performance_team_groups_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_high_performance_team_group_insert` BEFORE INSERT ON `tbl_high_performance_team_groups` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_hobbies_and_interests`
--

CREATE TABLE `tbl_hobbies_and_interests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_hobby_and_interest_before_insert` BEFORE INSERT ON `tbl_hobbies_and_interests` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_industries`
--

CREATE TABLE `tbl_industries` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_industry_before_insert` BEFORE INSERT ON `tbl_industries` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_inmail_virtual_aliases`
--

CREATE TABLE `tbl_inmail_virtual_aliases` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int unsigned NOT NULL,
  `source` varchar(100) NOT NULL,
  `destination` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `tbl_inmail_virtual_aliases_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_invoice_transactions`
--

CREATE TABLE `tbl_invoice_transactions` (
  `invoice_id` bigint unsigned NOT NULL,
  `transaction_id` bigint unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `invoice_id` (`invoice_id`,`transaction_id`),
  KEY `transaction_id` (`transaction_id`),
  CONSTRAINT `tbl_invoice_transactions_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `tbl_invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_invoice_transactions_ibfk_2` FOREIGN KEY (`transaction_id`) REFERENCES `tbl_transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `tbl_invoices`
--

CREATE TABLE `tbl_invoices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `number` varchar(15) NOT NULL,
  `user_id` int unsigned NOT NULL,
  `internal_id` varchar(128) DEFAULT NULL,
  `location_id` int unsigned NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `email` varchar(250) NOT NULL,
  `phone` varchar(25) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `tbl_invoices_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_invoices_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `tbl_job_applications`
--

CREATE TABLE `tbl_job_applications` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `user_profile_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  KEY `user_id` (`user_id`),
  KEY `user_profile_id` (`user_profile_id`),
  CONSTRAINT `tbl_job_applications_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tbl_jobs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_job_applications_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_job_applications_ibfk_3` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_job_categories`
--

CREATE TABLE `tbl_job_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_job_category_before_insert` BEFORE INSERT ON `tbl_job_categories` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_job_degrees`
--

CREATE TABLE `tbl_job_degrees` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int unsigned NOT NULL,
  `degree_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  KEY `degree_id` (`degree_id`),
  CONSTRAINT `tbl_job_degrees_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tbl_jobs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_job_degrees_ibfk_2` FOREIGN KEY (`degree_id`) REFERENCES `tbl_degrees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_job_description_competencies`
--

CREATE TABLE `tbl_job_description_competencies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_description_id` int unsigned NOT NULL,
  `competency_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_description_id` (`job_description_id`),
  KEY `competency_id` (`competency_id`),
  CONSTRAINT `tbl_job_description_competencies_ibfk_1` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_job_description_competencies_ibfk_2` FOREIGN KEY (`competency_id`) REFERENCES `tbl_competencies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_job_description_competency_behaviors`
--

CREATE TABLE `tbl_job_description_competency_behaviors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_description_id` int unsigned NOT NULL,
  `competency_id` int unsigned NOT NULL,
  `behavior_id` int unsigned NOT NULL,
  `level` int NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_description_id` (`job_description_id`),
  KEY `competency_id` (`competency_id`),
  KEY `behavior_id` (`behavior_id`),
  CONSTRAINT `tbl_job_description_competency_behaviors_ibfk_2` FOREIGN KEY (`competency_id`) REFERENCES `tbl_competencies` (`id`),
  CONSTRAINT `tbl_job_description_competency_behaviors_ibfk_3` FOREIGN KEY (`behavior_id`) REFERENCES `tbl_behaviors` (`id`),
  CONSTRAINT `tbl_job_description_competency_behaviors_ibfk_4` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_job_description_subordinates`
--

CREATE TABLE `tbl_job_description_subordinates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_description_id_top_level` int unsigned NOT NULL,
  `job_description_id_low_level` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `job_description_id_top_level` (`job_description_id_top_level`,`job_description_id_low_level`),
  KEY `job_description_id_low_level` (`job_description_id_low_level`),
  CONSTRAINT `tbl_job_description_subordinates_ibfk_1` FOREIGN KEY (`job_description_id_top_level`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_job_description_subordinates_ibfk_2` FOREIGN KEY (`job_description_id_low_level`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_job_languages`
--

CREATE TABLE `tbl_job_languages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int unsigned NOT NULL,
  `language_id` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `tbl_job_languages_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tbl_jobs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_job_languages_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `tbl_languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_job_responsibilities`
--

CREATE TABLE `tbl_job_responsibilities` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int unsigned NOT NULL,
  `responsibility` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  CONSTRAINT `tbl_job_responsibilities_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tbl_jobs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_job_skills`
--

CREATE TABLE `tbl_job_skills` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int unsigned NOT NULL,
  `skill_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  KEY `skill_id` (`skill_id`),
  CONSTRAINT `tbl_job_skills_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tbl_jobs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_job_skills_ibfk_2` FOREIGN KEY (`skill_id`) REFERENCES `tbl_skills` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_jobs`
--

CREATE TABLE `tbl_jobs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `job_category_id` int unsigned NOT NULL,
  `title` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `salary_visible` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `salary_min` decimal(10,2) DEFAULT NULL,
  `salary_max` decimal(10,2) DEFAULT NULL,
  `salary_currency` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'USD',
  `experience_visible` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `experience_min` int unsigned DEFAULT NULL,
  `experience_max` int unsigned DEFAULT NULL,
  `employment_type` enum('f','p','c','t','r') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'f - Full time, p - Part time, c - Contract, t -Temporary, r - Remote',
  `location_id` int unsigned NOT NULL,
  `last_date_of_application` date NOT NULL,
  `apply_email` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `apply_url` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` enum('a','i','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `is_featured` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `visits` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `network_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `job_category_id` (`job_category_id`),
  KEY `location_id` (`location_id`),
  KEY `salary_currency` (`salary_currency`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_jobs_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_jobs_ibfk_2` FOREIGN KEY (`job_category_id`) REFERENCES `tbl_job_categories` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_jobs_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_jobs_ibfk_4` FOREIGN KEY (`salary_currency`) REFERENCES `tbl_currencies` (`currency`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_jobs_ibfk_5` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_job_before_insert` BEFORE INSERT ON `tbl_jobs` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_jobs_description`
--

CREATE TABLE `tbl_jobs_description` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `job_description_id_boss` int unsigned DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `functions` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `objectives` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `job_description_id_default` int unsigned DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `position` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `id` (`job_description_id_default`),
  KEY `job_description_id_boss` (`job_description_id_boss`,`id`) USING BTREE,
  CONSTRAINT `tbl_jobs_description_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_jobs_description_ibfk_2` FOREIGN KEY (`job_description_id_boss`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `tbl_jobs_description_ibfk_3` FOREIGN KEY (`job_description_id_default`) REFERENCES `tbl_jobs_description` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_job_description_before_insert` BEFORE INSERT ON `tbl_jobs_description` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_jobs_saved`
--

CREATE TABLE `tbl_jobs_saved` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_jobs_saved_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `tbl_jobs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_jobs_saved_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_jwt_tokens`
--

CREATE TABLE `tbl_jwt_tokens` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `aes` varchar(50) DEFAULT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `csrf` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=32218 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_jwt_tokens_before_insert` BEFORE INSERT ON `tbl_jwt_tokens` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_knowledge_area_categories`
--

CREATE TABLE `tbl_knowledge_area_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a - active, i - inactive',
  `privacy` enum('p','c') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'c' COMMENT 'c - company, p = public',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `name` (`name`),
  KEY `company_id` (`company_id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_knowledge_area_categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_knowledge_area_categories_ibfk_2` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_knowledge_area_categories_before_insert` BEFORE INSERT ON `tbl_knowledge_area_categories` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_knowledge_area_category_jobs_description`
--

CREATE TABLE `tbl_knowledge_area_category_jobs_description` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int unsigned DEFAULT NULL,
  `job_description_id` int unsigned DEFAULT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `company_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_id` (`category_id`,`job_description_id`),
  KEY `job_description_id` (`job_description_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_knowledge_area_category_jobs_description_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tbl_knowledge_area_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_knowledge_area_category_jobs_description_ibfk_2` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_knowledge_area_category_jobs_description_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_knowledge_area_category_users`
--

CREATE TABLE `tbl_knowledge_area_category_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `role` enum('a','e','u') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'u' COMMENT 'u = user, a = administrator, e = editor',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_id` (`category_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_knowledge_area_category_users_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tbl_knowledge_area_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_knowledge_area_category_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_knowledge_area_contents`
--

CREATE TABLE `tbl_knowledge_area_contents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `network_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `category_id` int unsigned NOT NULL,
  `title` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `image` varchar(250) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `attachment` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `link` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `total_comments` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `category_id` (`category_id`),
  KEY `company_id` (`company_id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_knowledge_area_contents_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_knowledge_area_contents_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `tbl_knowledge_area_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_knowledge_area_contents_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_knowledge_area_contents_ibfk_4` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_knowledge_area_contents_before_insert` BEFORE INSERT ON `tbl_knowledge_area_contents` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_languages`
--

CREATE TABLE `tbl_languages` (
  `id` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a = active, i = inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_likes`
--

CREATE TABLE `tbl_likes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `feed_id` int unsigned DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `post_id` int unsigned DEFAULT NULL,
  `relational` enum('f','p') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'f' COMMENT 'f = feed, p = post',
  PRIMARY KEY (`id`),
  UNIQUE KEY `feed_id` (`feed_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `post_id` (`post_id`),
  CONSTRAINT `tbl_likes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_likes_ibfk_2` FOREIGN KEY (`feed_id`) REFERENCES `tbl_feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_likes_ibfk_3` FOREIGN KEY (`post_id`) REFERENCES `tbl_posts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=244 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_locations`
--

CREATE TABLE `tbl_locations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `formatted_address` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `address1` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `address2` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `country` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `state` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `city1` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `city2` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `postal_code` varchar(6) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(10,8) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_location_before_insert` BEFORE INSERT ON `tbl_locations` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_logs`
--

CREATE TABLE `tbl_logs` (
  `log_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned DEFAULT NULL,
  `priority_level` int unsigned NOT NULL,
  `priority_name` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `message` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `ip` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `user_id` (`user_id`),
  KEY `added_on` (`added_on`),
  KEY `ip` (`ip`),
  CONSTRAINT `tbl_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=188297 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_media_categories`
--

CREATE TABLE `tbl_media_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `added_on` datetime NOT NULL,
  `updated_on` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_meeting_files`
--

CREATE TABLE `tbl_meeting_files` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `meeting_id` int unsigned NOT NULL,
  `filename` varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` enum('a','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a = active, d = deleted',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `meeting_id` (`meeting_id`),
  CONSTRAINT `tbl_meeting_files_ibfk_1` FOREIGN KEY (`meeting_id`) REFERENCES `tbl_meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_meeting_members`
--

CREATE TABLE `tbl_meeting_members` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `meeting_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `joining_request_on` datetime NOT NULL,
  `action` enum('i','a','d','aa') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'i' COMMENT 'i - invited,  a - Accepted, d - Declined, aa - Added by Admin',
  `joined_on` datetime NOT NULL,
  `member_role` enum('o','m') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'o' COMMENT 'o = observer, m = moderator',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `meeting_id` (`meeting_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_meeting_members_ibfk_1` FOREIGN KEY (`meeting_id`) REFERENCES `tbl_meetings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_meeting_members_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_messages`
--

CREATE TABLE `tbl_messages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `conversation_id` int unsigned NOT NULL,
  `sender_id` int unsigned NOT NULL,
  `receiver_id` int unsigned NOT NULL,
  `message` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `type` enum('text','image','video','document') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'text',
  `filename` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `image_preview` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `sender_status` enum('n','t','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n' COMMENT 'n - Normal, t - Trashed, d - Deleted',
  `receiver_status` enum('n','t','d','r') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n' COMMENT 'n = normal, t = trashed, d = delete, r = reported',
  `read` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n' COMMENT 'y-read, n-unread',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `conversation_id` (`conversation_id`),
  KEY `receiver_id` (`receiver_id`),
  KEY `sender_id_and_receiver_id` (`sender_id`,`receiver_id`) USING BTREE,
  CONSTRAINT `tbl_messages_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `tbl_conversations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_messages_ibfk_3` FOREIGN KEY (`sender_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1906 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_messages_insert` BEFORE INSERT ON `tbl_messages` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_capsule_comments`
--

CREATE TABLE `tbl_microlearning_capsule_comments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `capsule_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `rating` tinyint unsigned NOT NULL DEFAULT '0',
  `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `capsule_id` (`capsule_id`),
  CONSTRAINT `tbl_microlearning_capsule_comments_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_capsule_comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_capsule_comments_ibfk_4` FOREIGN KEY (`capsule_id`) REFERENCES `tbl_microlearning_capsules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_capsule_comment_before_insert` BEFORE INSERT ON `tbl_microlearning_capsule_comments` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_capsules`
--

CREATE TABLE `tbl_microlearning_capsules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `image` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` enum('a','i','d') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a - Active, i - inactive, d = deleted',
  `type` enum('p','f','s') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = private, f = free, s = selling',
  `privacy` enum('pr','pu') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'pu' COMMENT 'pr - Private, pu - Public',
  `cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `marketplace` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_capsules_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_capsule_before_insert` BEFORE INSERT ON `tbl_microlearning_capsules` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user`
--

CREATE TABLE `tbl_microlearning_extend_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `extend_function_id` int unsigned DEFAULT NULL,
  `extend_student_type_id` int unsigned DEFAULT NULL,
  `extend_company_id` int unsigned DEFAULT NULL,
  `extend_partner_id` int unsigned DEFAULT NULL,
  `extend_institution_id` int unsigned DEFAULT NULL,
  `extend_group_id` int unsigned DEFAULT NULL,
  `extend_sector_id` int unsigned DEFAULT NULL,
  `extend_program_id` int unsigned DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `extend_country_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `extend_function_id` (`extend_function_id`),
  KEY `extend_student_type_id` (`extend_student_type_id`),
  KEY `extend_company_id` (`extend_company_id`),
  KEY `extend_partner_id` (`extend_partner_id`),
  KEY `extend_institution_id` (`extend_institution_id`),
  KEY `extend_group_id` (`extend_group_id`),
  KEY `extend_sector_id` (`extend_sector_id`),
  KEY `extend_program_id` (`extend_program_id`),
  KEY `extend_country_id` (`extend_country_id`),
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_10` FOREIGN KEY (`extend_program_id`) REFERENCES `tbl_microlearning_extend_user_programs` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_11` FOREIGN KEY (`extend_country_id`) REFERENCES `tbl_microlearning_extend_user_countries` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_3` FOREIGN KEY (`extend_function_id`) REFERENCES `tbl_microlearning_extend_user_functions` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_4` FOREIGN KEY (`extend_student_type_id`) REFERENCES `tbl_microlearning_extend_user_student_types` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_5` FOREIGN KEY (`extend_company_id`) REFERENCES `tbl_microlearning_extend_user_companies` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_6` FOREIGN KEY (`extend_partner_id`) REFERENCES `tbl_microlearning_extend_user_partners` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_7` FOREIGN KEY (`extend_institution_id`) REFERENCES `tbl_microlearning_extend_user_institutions` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_8` FOREIGN KEY (`extend_group_id`) REFERENCES `tbl_microlearning_extend_user_groups` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `tbl_microlearning_extend_user_ibfk_9` FOREIGN KEY (`extend_sector_id`) REFERENCES `tbl_microlearning_extend_user_sectors` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=498 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_microlearning_extend_user_companies`
--

CREATE TABLE `tbl_microlearning_extend_user_companies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_companies_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_companies_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_companies` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_countries`
--

CREATE TABLE `tbl_microlearning_extend_user_countries` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_countries_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_countries_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_countries` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_functions`
--

CREATE TABLE `tbl_microlearning_extend_user_functions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_functions_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_functions_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_functions` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_groups`
--

CREATE TABLE `tbl_microlearning_extend_user_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_groups_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_groups_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_groups` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_institutions`
--

CREATE TABLE `tbl_microlearning_extend_user_institutions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_institutions_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_institutions_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_institutions` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_partners`
--

CREATE TABLE `tbl_microlearning_extend_user_partners` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_partners_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_partners_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_partners` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_programs`
--

CREATE TABLE `tbl_microlearning_extend_user_programs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_programs_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_programs_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_programs` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_sectors`
--

CREATE TABLE `tbl_microlearning_extend_user_sectors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_sectors_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_sectors_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_sectors` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_extend_user_student_types`
--

CREATE TABLE `tbl_microlearning_extend_user_student_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_extend_user_student_types_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_extend_user_student_types_before_insert` BEFORE INSERT ON `tbl_microlearning_extend_user_student_types` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_quiz_answers`
--

CREATE TABLE `tbl_microlearning_quiz_answers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `quiz_id` int unsigned NOT NULL,
  `question_id` int unsigned NOT NULL,
  `text` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `points` smallint unsigned NOT NULL DEFAULT '0',
  `correct` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `quiz_id` (`quiz_id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `tbl_microlearning_quiz_answers_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_quiz_answers_ibfk_2` FOREIGN KEY (`quiz_id`) REFERENCES `tbl_microlearning_quizzes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_quiz_answers_ibfk_3` FOREIGN KEY (`question_id`) REFERENCES `tbl_microlearning_quiz_questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_quiz_answer_before_insert` BEFORE INSERT ON `tbl_microlearning_quiz_answers` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_quiz_questions`
--

CREATE TABLE `tbl_microlearning_quiz_questions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `quiz_id` int unsigned NOT NULL,
  `text` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `type` enum('sl','ml','s','m','r5','r6','r10','ro') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'sl' COMMENT 'sl = singleline, ml = multiline  , s = radio selection single, m = checkbox selection multiple r5 = range 1-5, r6 = range 1-6, r10 = range 1-10,  ro  = range open',
  `maxlength` int unsigned NOT NULL,
  `points` smallint unsigned NOT NULL DEFAULT '0',
  `check` enum('ok','pending','wrong') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'pending',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `tbl_microlearning_quiz_questions_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_quiz_questions_ibfk_2` FOREIGN KEY (`quiz_id`) REFERENCES `tbl_microlearning_quizzes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_quiz_question_before_insert` BEFORE INSERT ON `tbl_microlearning_quiz_questions` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_quizzes`
--

CREATE TABLE `tbl_microlearning_quizzes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `text` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `failed` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'A - ACTIVE, i - inactive',
  `points` smallint unsigned NOT NULL DEFAULT '0',
  `minimum_points_required` smallint unsigned NOT NULL DEFAULT '0',
  `check` enum('ok','pending','wrong') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'pending',
  `max_time` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_quizzes_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_quiz_before_insert` BEFORE INSERT ON `tbl_microlearning_quizzes` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_slides`
--

CREATE TABLE `tbl_microlearning_slides` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `capsule_id` int unsigned NOT NULL,
  `quiz_id` int unsigned DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `order` smallint unsigned NOT NULL DEFAULT '0',
  `type` enum('image','video','audio','quiz','document','text') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'image',
  `file` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `background` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `capsule_id` (`capsule_id`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `tbl_microlearning_slides_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_slides_ibfk_3` FOREIGN KEY (`capsule_id`) REFERENCES `tbl_microlearning_capsules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_slides_ibfk_4` FOREIGN KEY (`quiz_id`) REFERENCES `tbl_microlearning_quizzes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=269 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_slide_before_insert` BEFORE INSERT ON `tbl_microlearning_slides` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_topic_capsules`
--

CREATE TABLE `tbl_microlearning_topic_capsules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int unsigned NOT NULL,
  `capsule_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `status` enum('a','i') NOT NULL COMMENT 'a = active, i = inactive',
  `send_notification` enum('y','n') NOT NULL DEFAULT 'y' COMMENT 'y = yes, n = no',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `publish_on` date NOT NULL DEFAULT (curdate()),
  `notification_on` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `topic_id` (`topic_id`,`capsule_id`),
  KEY `company_id` (`company_id`),
  KEY `capsule_id` (`capsule_id`),
  CONSTRAINT `tbl_microlearning_topic_capsules_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_microlearning_topic_capsules_ibfk_2` FOREIGN KEY (`topic_id`) REFERENCES `tbl_microlearning_topics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_microlearning_topic_capsules_ibfk_3` FOREIGN KEY (`capsule_id`) REFERENCES `tbl_microlearning_capsules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_microlearning_topic_users`
--

CREATE TABLE `tbl_microlearning_topic_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `access` enum('u','p','r','c','s') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'u' COMMENT 'u = unlimited, p = pay period, r = revoke, c=  cancelled, s = supended',
  `paid_from` datetime DEFAULT NULL,
  `paid_to` datetime DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`),
  CONSTRAINT `tbl_microlearning_topic_users_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_topic_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_topic_users_ibfk_3` FOREIGN KEY (`topic_id`) REFERENCES `tbl_microlearning_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1781 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_microlearning_topics`
--

CREATE TABLE `tbl_microlearning_topics` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `image` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'i' COMMENT 'a = active, i = inactive',
  `order` smallint unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `privacy` enum('pr','pu') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'pu' COMMENT 'pr - Private, pu - Public',
  `marketplace` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `type` enum('p','f','s') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'f' COMMENT 'p - Private, f - FREE, s - SELLING',
  `cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_microlearning_topics_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_microlearning_topic_before_insert` BEFORE INSERT ON `tbl_microlearning_topics` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_microlearning_user_log`
--

CREATE TABLE `tbl_microlearning_user_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned DEFAULT NULL,
  `topic_id` int unsigned DEFAULT NULL,
  `capsule_id` int unsigned DEFAULT NULL,
  `slide_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `activity` enum('signin','signout','start-topic','start-capsule','view-slide','take-a-test','retake-a-test','approved-test','completed-capsule','completed-topic') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`),
  KEY `capsule_id` (`capsule_id`),
  KEY `slide_id` (`slide_id`),
  CONSTRAINT `tbl_microlearning_user_log_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_log_ibfk_3` FOREIGN KEY (`topic_id`) REFERENCES `tbl_microlearning_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_log_ibfk_4` FOREIGN KEY (`capsule_id`) REFERENCES `tbl_microlearning_capsules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_log_ibfk_5` FOREIGN KEY (`slide_id`) REFERENCES `tbl_microlearning_slides` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10564 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_microlearning_user_progress`
--

CREATE TABLE `tbl_microlearning_user_progress` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `topic_id` int unsigned DEFAULT NULL,
  `capsule_id` int unsigned DEFAULT NULL,
  `slide_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned NOT NULL,
  `progress` decimal(6,2) NOT NULL DEFAULT '0.00',
  `type` enum('t','c','s') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 't = topic, c = capsule, s = slide',
  `total_slides` int unsigned NOT NULL DEFAULT '0',
  `view_slides` int unsigned NOT NULL DEFAULT '0',
  `returning` int unsigned NOT NULL DEFAULT '0',
  `returning_after_completed` int unsigned NOT NULL DEFAULT '0',
  `completed` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `returning_on` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`),
  KEY `capsule_id` (`capsule_id`),
  KEY `slide_id` (`slide_id`),
  CONSTRAINT `tbl_microlearning_user_progress_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_progress_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_progress_ibfk_3` FOREIGN KEY (`topic_id`) REFERENCES `tbl_microlearning_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_progress_ibfk_4` FOREIGN KEY (`capsule_id`) REFERENCES `tbl_microlearning_capsules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_progress_ibfk_5` FOREIGN KEY (`slide_id`) REFERENCES `tbl_microlearning_slides` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4506 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_microlearning_user_quizzes`
--

CREATE TABLE `tbl_microlearning_user_quizzes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `topic_id` int unsigned NOT NULL,
  `capsule_id` int unsigned NOT NULL,
  `slide_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `quiz_id` int unsigned NOT NULL,
  `response` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `points` int unsigned NOT NULL DEFAULT '0',
  `status` enum('p','f') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'p = pass, f = fail',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`),
  KEY `capsule_id` (`capsule_id`),
  KEY `slide_id` (`slide_id`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `tbl_microlearning_user_quizzes_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_quizzes_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_quizzes_ibfk_3` FOREIGN KEY (`topic_id`) REFERENCES `tbl_microlearning_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_quizzes_ibfk_4` FOREIGN KEY (`capsule_id`) REFERENCES `tbl_microlearning_capsules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_quizzes_ibfk_5` FOREIGN KEY (`slide_id`) REFERENCES `tbl_microlearning_slides` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_user_quizzes_ibfk_6` FOREIGN KEY (`quiz_id`) REFERENCES `tbl_microlearning_quizzes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_microlearning_users`
--

CREATE TABLE `tbl_microlearning_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_id` (`company_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_microlearning_users_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_microlearning_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_my_coach_answers`
--

CREATE TABLE `tbl_my_coach_answers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `question_id` int unsigned NOT NULL,
  `text` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `tbl_my_coach_answers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_my_coach_answers_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `tbl_my_coach_questions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_my_coach_answers_before_insert` BEFORE INSERT ON `tbl_my_coach_answers` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_my_coach_answers_before_delete` BEFORE DELETE ON `tbl_my_coach_answers` FOR EACH ROW BEGIN
	DELETE FROM `tbl_content_reactions` WHERE `my_coach_answer_id`  = OLD.id;
	DELETE FROM `tbl_comments` WHERE `my_coach_answer_id`  = OLD.id;
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_my_coach_categories`
--

CREATE TABLE `tbl_my_coach_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `network_id` int unsigned NOT NULL,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a - active, i - inactive',
  `privacy` enum('p','c') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'c' COMMENT 'c - company, p = public',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `name` (`name`),
  KEY `company_id` (`company_id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_my_coach_categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_my_coach_categories_ibfk_2` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_my_coach_categories_before_insert` BEFORE INSERT ON `tbl_my_coach_categories` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_my_coach_category_jobs_description`
--

CREATE TABLE `tbl_my_coach_category_jobs_description` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int unsigned DEFAULT NULL,
  `job_description_id` int unsigned DEFAULT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `company_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_id` (`category_id`,`job_description_id`),
  KEY `job_description_id` (`job_description_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_my_coach_category_jobs_description_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tbl_my_coach_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_my_coach_category_jobs_description_ibfk_2` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_my_coach_category_jobs_description_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_my_coach_category_users`
--

CREATE TABLE `tbl_my_coach_category_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `role` enum('a','e','u') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'u' COMMENT 'u = user, a = administrator, e = editor',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_id` (`category_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_my_coach_category_users_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tbl_my_coach_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_my_coach_category_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_my_coach_question_categories`
--

CREATE TABLE `tbl_my_coach_question_categories` (
  `category_id` int unsigned NOT NULL,
  `question_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `category_id` (`category_id`,`question_id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `tbl_my_coach_question_categories_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tbl_my_coach_categories` (`id`),
  CONSTRAINT `tbl_my_coach_question_categories_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `tbl_my_coach_questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_my_coach_question_views`
--

CREATE TABLE `tbl_my_coach_question_views` (
  `user_id` int unsigned NOT NULL,
  `question_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`,`question_id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `tbl_my_coach_question_views_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_my_coach_question_views_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `tbl_my_coach_questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_my_coach_questions`
--

CREATE TABLE `tbl_my_coach_questions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `network_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `title` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_my_coach_questions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_my_coach_questions_ibfk_2` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_my_coach_questions_before_insert` BEFORE INSERT ON `tbl_my_coach_questions` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_my_trainer_categories`
--

CREATE TABLE `tbl_my_trainer_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `text` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `title` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_my_trainer_categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_my_trainer_categories_before_insert` BEFORE INSERT ON `tbl_my_trainer_categories` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_my_trainer_questions`
--

CREATE TABLE `tbl_my_trainer_questions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `first_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `last_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `category_id` int unsigned NOT NULL,
  `text` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `title` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `tbl_my_trainer_questions_ibfk_1` (`category_id`),
  CONSTRAINT `tbl_my_trainer_questions_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tbl_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_my_trainer_questions_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_my_trainer_questions_before_insert` BEFORE INSERT ON `tbl_my_trainer_questions` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_networks` BEFORE INSERT ON `tbl_networks` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
	SET NEW.one_time_password = fn_one_time_password();
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_newsletters`
--

CREATE TABLE `tbl_newsletters` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `subject` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `content` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_notifications`
--

CREATE TABLE `tbl_notifications` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `read` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `message` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `url` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `type` enum('receive-connection-request','accept-my-request-connection','receive-invitation-group','accept-my-request-join-group','receive-request-join-my-group','receive-invitation-company','like-my-feed','comment-my-feed','share-my-feed','new-microlearning-capsule') COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `feed_id` int unsigned DEFAULT NULL,
  `group_id` int unsigned DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `capsule_id` int unsigned DEFAULT NULL,
  `topic_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  KEY `feed_id` (`feed_id`),
  KEY `group_id` (`group_id`),
  KEY `capsule_id` (`capsule_id`),
  KEY `topic_id` (`topic_id`),
  CONSTRAINT `tbl_notifications_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_notifications_ibfk_2` FOREIGN KEY (`feed_id`) REFERENCES `tbl_feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_notifications_ibfk_3` FOREIGN KEY (`group_id`) REFERENCES `tbl_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_notifications_ibfk_4` FOREIGN KEY (`capsule_id`) REFERENCES `tbl_microlearning_capsules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_notifications_ibfk_5` FOREIGN KEY (`topic_id`) REFERENCES `tbl_microlearning_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1016 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_notifications_before_insert` BEFORE INSERT ON `tbl_notifications` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_organization_positions`
--

CREATE TABLE `tbl_organization_positions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `job_description_id` int unsigned NOT NULL,
  `employee_id` int unsigned NOT NULL,
  `boss_id` int unsigned DEFAULT NULL,
  `status` enum('a','i') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `boss_id` (`boss_id`,`job_description_id`),
  KEY `company_id` (`company_id`),
  KEY `job_description_id` (`job_description_id`),
  KEY `employee_id` (`employee_id`),
  KEY `boss_id_2` (`boss_id`),
  CONSTRAINT `tbl_organization_positions_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_organization_positions_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_organization_positions_ibfk_3` FOREIGN KEY (`boss_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_organization_positions_ibfk_4` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_organization_positions_before_insert` BEFORE INSERT ON `tbl_organization_positions` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_pages`
--

CREATE TABLE `tbl_pages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `content` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `type` enum('p','u') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p - Page, u - URL',
  `url` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fixed` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `network_id` int unsigned NOT NULL,
  `code` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `page_default_id` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`,`network_id`),
  KEY `network_id` (`network_id`),
  KEY `page_default_id` (`page_default_id`),
  CONSTRAINT `tbl_pages_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`),
  CONSTRAINT `tbl_pages_ibfk_2` FOREIGN KEY (`page_default_id`) REFERENCES `tbl_pages` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_performance_evaluation_forms`
--

CREATE TABLE `tbl_performance_evaluation_forms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `job_description_id` int unsigned NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `content` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'A - ACTIVE, I - INACTIVE',
  `tests` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `job_description_id` (`job_description_id`),
  CONSTRAINT `tbl_performance_evaluation_forms_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_performance_evaluation_forms_ibfk_2` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_performance_evaluation_forms_before_insert` BEFORE INSERT ON `tbl_performance_evaluation_forms` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_performance_evaluation_tests`
--

CREATE TABLE `tbl_performance_evaluation_tests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `parent_id` int unsigned DEFAULT NULL,
  `form_id` int unsigned NOT NULL,
  `last_date` date DEFAULT NULL,
  `company_id` int unsigned NOT NULL,
  `supervisor_id` int unsigned DEFAULT NULL,
  `employee_id` int unsigned DEFAULT NULL,
  `type` enum('e','s','b') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'b' COMMENT 'e = employee, s = supervisor, b = both',
  `status` enum('p','c') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p - pending, c = completed ',
  `content` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `level` int unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `last_date` (`last_date`),
  KEY `supervisor_id` (`supervisor_id`),
  KEY `employee_id` (`employee_id`),
  KEY `form_id` (`form_id`),
  KEY `parent_id` (`parent_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_performance_evaluation_tests_ibfk_1` FOREIGN KEY (`supervisor_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_performance_evaluation_tests_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_performance_evaluation_tests_ibfk_3` FOREIGN KEY (`form_id`) REFERENCES `tbl_performance_evaluation_forms` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_performance_evaluation_tests_ibfk_4` FOREIGN KEY (`parent_id`) REFERENCES `tbl_performance_evaluation_tests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_performance_evaluation_tests_ibfk_5` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_performance_evaluation_tests_before_insert` BEFORE INSERT ON `tbl_performance_evaluation_tests` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_performance_evaluation_tests_after_insert` AFTER INSERT ON `tbl_performance_evaluation_tests` FOR EACH ROW BEGIN
	IF NEW.type = 'e' THEN 
		INSERT INTO `tbl_calendar_events` (`user_id`, `start_time` , `relational_id`, `type`)
		VALUES (NEW.employee_id, NEW.last_date, NEW.id, 'performance-evaluation');
	END IF;

	IF NEW.type = 's' THEN 
		INSERT INTO `tbl_calendar_events` (`user_id`, `start_time` , `relational_id`, `type`)
		VALUES (NEW.supervisor_id, NEW.last_date, NEW.id, 'performance-evaluation');
	END IF;

	IF NEW.type = 'b' THEN 
		UPDATE tbl_performance_evaluation_forms SET `tests` = `tests`+ 1 WHERE `id` = NEW.form_id;
	
		INSERT INTO `tbl_calendar_events` (`user_id`, `start_time` , `relational_id`, `type`)
		VALUES (NEW.employee_id, NEW.last_date, NEW.id, 'performance-evaluation');
	
		INSERT INTO `tbl_calendar_events` (`user_id`, `start_time` , `relational_id`, `type`)
		VALUES (NEW.supervisor_id, NEW.last_date, NEW.id, 'performance-evaluation');
	END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_performance_evaluation_tests_after_update` AFTER UPDATE ON `tbl_performance_evaluation_tests` FOR EACH ROW BEGIN
	IF OLD.status = 'p' AND NEW.status = 'c' THEN 
		DELETE FROM `tbl_calendar_events`  WHERE `type` = 'performance-evaluation' AND `relational_id` = OLD.id;
	END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_performance_evaluation_tests_after_delete` AFTER DELETE ON `tbl_performance_evaluation_tests` FOR EACH ROW BEGIN
	IF OLD.type = 'b' THEN 
		UPDATE tbl_performance_evaluation_forms SET `tests` = `tests` - 1 WHERE `id` = OLD.form_id;
	END IF;

	DELETE FROM `tbl_calendar_events` WHERE `relational_id` = OLD.id AND  `type` = 'performance-evaluation';
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_planning_goals`
--

CREATE TABLE `tbl_planning_goals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `period_id` int unsigned NOT NULL,
  `objective_id` int unsigned NOT NULL,
  `title` varchar(128) COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - active, i - inactive',
  `tasks` int unsigned NOT NULL DEFAULT '0',
  `progress` int unsigned NOT NULL DEFAULT '0',
  `budget` decimal(16,2) NOT NULL DEFAULT '0.00',
  `cost` decimal(16,2) NOT NULL DEFAULT '0.00',
  `start` date NOT NULL,
  `end` date NOT NULL,
  `hours` decimal(10,2) NOT NULL DEFAULT '0.00',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `objective_id` (`objective_id`),
  KEY `company_id` (`company_id`),
  KEY `period_id` (`period_id`),
  CONSTRAINT `tbl_planning_goals_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_goals_ibfk_2` FOREIGN KEY (`period_id`) REFERENCES `tbl_planning_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_goals_ibfk_3` FOREIGN KEY (`objective_id`) REFERENCES `tbl_planning_objectives` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_goals_before_insert` BEFORE INSERT ON `tbl_planning_goals` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_goals_after_insert` AFTER INSERT ON `tbl_planning_goals` FOR EACH ROW BEGIN
	UPDATE `tbl_planning_periods` SET `goals` = `goals`  + 1
	WHERE `id` = NEW.period_id;
	

	UPDATE `tbl_planning_objectives` SET `goals` = `goals`  + 1
	WHERE `id` = NEW.objective_id;

END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_goals_after_delete` AFTER DELETE ON `tbl_planning_goals` FOR EACH ROW BEGIN
	UPDATE `tbl_planning_periods` SET `goals` = `goals`  - 1
	WHERE `id` = OLD.period_id;
	

	UPDATE `tbl_planning_objectives` SET `goals` = `goals`  - 1
	WHERE `id` = OLD.objective_id;
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_planning_objectives`
--

CREATE TABLE `tbl_planning_objectives` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `period_id` int unsigned NOT NULL,
  `title` varchar(128) COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - active, i - inactive',
  `goals` int unsigned NOT NULL DEFAULT '0',
  `tasks` int unsigned NOT NULL DEFAULT '0',
  `budget` decimal(20,2) NOT NULL DEFAULT '0.00',
  `cost` decimal(20,2) NOT NULL DEFAULT '0.00',
  `start` date NOT NULL,
  `end` date NOT NULL,
  `progress` int unsigned NOT NULL,
  `hours` decimal(10,2) NOT NULL DEFAULT '0.00',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `period_id` (`period_id`),
  CONSTRAINT `tbl_planning_objectives_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_objectives_ibfk_2` FOREIGN KEY (`period_id`) REFERENCES `tbl_planning_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_objectives_before_insert` BEFORE INSERT ON `tbl_planning_objectives` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_objectives_after_insert` AFTER INSERT ON `tbl_planning_objectives` FOR EACH ROW BEGIN
	UPDATE `tbl_planning_periods` SET `objectives` = `objectives` + 1
	WHERE `id` = NEW.period_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_objectives_after_delete` AFTER DELETE ON `tbl_planning_objectives` FOR EACH ROW BEGIN
	UPDATE `tbl_planning_periods` SET `objectives` = `objectives` - 1
	WHERE `id` = OLD.period_id;
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_planning_periods`
--

CREATE TABLE `tbl_planning_periods` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `title` varchar(128) COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - active, d - delete',
  `objectives` int unsigned NOT NULL DEFAULT '0',
  `goals` int unsigned NOT NULL DEFAULT '0',
  `tasks` int unsigned NOT NULL DEFAULT '0',
  `progress` int unsigned NOT NULL DEFAULT '0',
  `start` date NOT NULL,
  `end` date NOT NULL,
  `budget` decimal(24,2) NOT NULL DEFAULT '0.00',
  `cost` decimal(24,2) NOT NULL DEFAULT '0.00',
  `hours` decimal(10,2) NOT NULL DEFAULT '0.00',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_planning_periods_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_periods_before_insert` BEFORE INSERT ON `tbl_planning_periods` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_planning_task_users`
--

CREATE TABLE `tbl_planning_task_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `period_id` int unsigned NOT NULL,
  `objective_id` int unsigned NOT NULL,
  `goal_id` int unsigned NOT NULL,
  `task_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_id` (`task_id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  KEY `period_id` (`period_id`),
  KEY `objective_id` (`objective_id`),
  KEY `goal_id` (`goal_id`),
  CONSTRAINT `tbl_planning_task_users_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_task_users_ibfk_2` FOREIGN KEY (`period_id`) REFERENCES `tbl_planning_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_task_users_ibfk_3` FOREIGN KEY (`objective_id`) REFERENCES `tbl_planning_objectives` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_task_users_ibfk_4` FOREIGN KEY (`goal_id`) REFERENCES `tbl_planning_goals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_task_users_ibfk_5` FOREIGN KEY (`task_id`) REFERENCES `tbl_planning_tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_task_users_ibfk_6` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_planning_tasks`
--

CREATE TABLE `tbl_planning_tasks` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned NOT NULL,
  `period_id` int unsigned NOT NULL,
  `objective_id` int unsigned NOT NULL,
  `goal_id` int unsigned NOT NULL,
  `title` varchar(128) COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `place` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
  `how` varchar(1024) COLLATE utf8mb3_unicode_ci NOT NULL,
  `start` date NOT NULL,
  `end` date NOT NULL,
  `progress` int unsigned NOT NULL DEFAULT '0',
  `cost` decimal(12,2) unsigned NOT NULL DEFAULT '0.00',
  `detour` varchar(1024) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `evaluation` varchar(1024) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `priority` enum('y','n') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'y = yes, n = no',
  `urgent` enum('y','n') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'y = yes, n = no',
  `status` enum('a','i') COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a = active, i = inactive',
  `hours` decimal(10,2) NOT NULL DEFAULT '0.00',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `period_id` (`period_id`),
  KEY `objective_id` (`objective_id`),
  KEY `goal_id` (`goal_id`),
  CONSTRAINT `tbl_planning_tasks_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_tasks_ibfk_2` FOREIGN KEY (`period_id`) REFERENCES `tbl_planning_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_tasks_ibfk_3` FOREIGN KEY (`objective_id`) REFERENCES `tbl_planning_objectives` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_planning_tasks_ibfk_4` FOREIGN KEY (`goal_id`) REFERENCES `tbl_planning_goals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_tasks_before_insert` BEFORE INSERT ON `tbl_planning_tasks` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_tasks_after_insert` AFTER INSERT ON `tbl_planning_tasks` FOR EACH ROW BEGIN
	UPDATE `tbl_planning_periods` SET `cost` = `cost` + NEW.cost, 
	`hours` = `hours` + NEW.hours, `tasks` = `tasks` + 1
	WHERE `id` = NEW.period_id;
	

	UPDATE `tbl_planning_objectives` SET `cost` = `cost` + NEW.cost, 
	`hours` = `hours` + NEW.hours, `tasks` = `tasks` + 1
	WHERE `id` = NEW.objective_id;
	
	UPDATE `tbl_planning_goals` SET `cost` = `cost` + NEW.cost, 
	`hours` = `hours` + NEW.hours, `tasks` = `tasks` + 1
	WHERE `id` = NEW.goal_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_tasks_after_update` AFTER UPDATE ON `tbl_planning_tasks` FOR EACH ROW BEGIN
	IF OLD.cost <> NEW.cost THEN	

		IF(OLD.cost > NEW.cost) THEN
			UPDATE `tbl_planning_periods` SET  `cost` = `cost` - (OLD.cost - NEW.cost) WHERE `id` = OLD.period_id;
		 ELSE
			UPDATE `tbl_planning_periods` SET  `cost` = `cost` + (NEW.cost - OLD.cost) WHERE `id` = OLD.period_id;
		END IF;
	
		IF(OLD.cost > NEW.cost) THEN
			UPDATE `tbl_planning_objectives` SET  `cost` = `cost` - (OLD.cost - NEW.cost) WHERE `id` = OLD.objective_id;
		 ELSE
			UPDATE `tbl_planning_objectives` SET  `cost` = `cost` + (NEW.cost - OLD.cost) WHERE `id` = OLD.objective_id;
		END IF;
	
		IF(OLD.cost > NEW.cost) THEN
			UPDATE `tbl_planning_goals` SET  `cost` = `cost` - (OLD.cost - NEW.cost) WHERE `id` = OLD.goal_id;
		 ELSE
			UPDATE `tbl_planning_goals` SET  `cost` = `cost` + (NEW.cost - OLD.cost) WHERE `id` = OLD.goal_id;
		END IF;
	END IF;
		
	IF OLD.hours <> NEW.hours THEN	

		IF(OLD.hours > NEW.hours) THEN
			UPDATE `tbl_planning_periods` SET  `hours` = `hours` - (OLD.hours - NEW.hours) WHERE `id` = OLD.period_id;
		 ELSE
			UPDATE `tbl_planning_periods` SET  `hours` = `hours` + (NEW.hours - OLD.hours) WHERE `id` = OLD.period_id;
		END IF;
	
		IF(OLD.hours > NEW.hours) THEN
			UPDATE `tbl_planning_objectives` SET  `hours` = `hours` - (OLD.hours - NEW.hours) WHERE `id` = OLD.objective_id;
		 ELSE
			UPDATE `tbl_planning_objectives` SET  `hours` = `hours` + (NEW.hours - OLD.hours) WHERE `id` = OLD.objective_id;
		END IF;
	
		IF(OLD.hours > NEW.hours) THEN
			UPDATE `tbl_planning_goals` SET  `hours` = `hours` - (OLD.hours - NEW.hours) WHERE `id` = OLD.goal_id;
		 ELSE
			UPDATE `tbl_planning_goals` SET  `hours` = `hours` + (NEW.hours - OLD.hours) WHERE `id` = OLD.goal_id;
		END IF;
	END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_planning_tasks_after_delete` AFTER DELETE ON `tbl_planning_tasks` FOR EACH ROW BEGIN
	UPDATE `tbl_planning_periods` SET `cost` = `cost` - OLD.cost, 
	`hours` = `hours` - OLD.hours, `tasks` = `tasks` - 1
	WHERE `id` = OLD.period_id;
	

	UPDATE `tbl_planning_objectives` SET `cost` = `cost` - OLD.cost, 
	`hours` = `hours` - OLD.hours,  `tasks` = `tasks` - 1
	WHERE `id` = OLD.objective_id;
	
	UPDATE `tbl_planning_goals` SET `cost` = `cost` - OLD.cost, 
	`hours` = `hours` - OLD.hours, `tasks` = `tasks` - 1
	WHERE `id` = OLD.goal_id;
	
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_posts`
--

CREATE TABLE `tbl_posts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `title` varchar(150) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `url` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` enum('a','i','d','r') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'i' COMMENT 'a = active ,i = inactive, d = deleted, r = repored ',
  `image` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `file` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `date` date NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `network_id` int unsigned NOT NULL,
  `total_external_shared` int unsigned NOT NULL DEFAULT '0',
  `user_id` int unsigned NOT NULL,
  `publish_on` date NOT NULL DEFAULT (curdate()),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `network_id` (`network_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_posts_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`),
  CONSTRAINT `tbl_posts_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_post_before_insert` BEFORE INSERT ON `tbl_posts` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_profile_visits`
--

CREATE TABLE `tbl_profile_visits` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_profile_id` int unsigned NOT NULL,
  `visitor_id` int unsigned NOT NULL,
  `visited_id` int unsigned NOT NULL,
  `visited_on` date NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `visitor_id` (`visitor_id`,`visited_id`,`visited_on`,`user_profile_id`),
  KEY `visited_id` (`visited_id`),
  KEY `user_profile_id` (`user_profile_id`),
  CONSTRAINT `tbl_profile_visits_ibfk_1` FOREIGN KEY (`visitor_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_profile_visits_ibfk_2` FOREIGN KEY (`visited_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_profile_visits_ibfk_3` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=419 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_push_templates`
--

CREATE TABLE `tbl_push_templates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `code` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `company_id` int unsigned DEFAULT NULL,
  `push_template_default_id` int unsigned DEFAULT NULL,
  `network_id` int unsigned DEFAULT NULL,
  `title` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `body` varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a',
  `type` enum('system','microlearning') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `code` (`code`,`company_id`),
  KEY `company_id` (`company_id`),
  KEY `push_template_default_id` (`push_template_default_id`),
  KEY `network_id` (`network_id`),
  CONSTRAINT `tbl_push_templates_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_push_templates_ibfk_2` FOREIGN KEY (`push_template_default_id`) REFERENCES `tbl_push_templates` (`id`),
  CONSTRAINT `tbl_push_templates_ibfk_3` FOREIGN KEY (`network_id`) REFERENCES `tbl_networks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_push_template_before_insert` BEFORE INSERT ON `tbl_push_templates` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_pushes`
--

CREATE TABLE `tbl_pushes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `status` enum('p','c','e') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'p = pending, c = completed, e = error',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6445 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_recruitment_selection_applications`
--

CREATE TABLE `tbl_recruitment_selection_applications` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `vacancy_id` int unsigned NOT NULL,
  `candidate_id` int unsigned NOT NULL,
  `status` enum('a','i','s','r') NOT NULL DEFAULT 'a' COMMENT 'a = active, i = inactive, s = selected, r = reject',
  `level` enum('c','p','h','b','f') NOT NULL DEFAULT 'c' COMMENT 'c = capture, p = pre selected, h = human resource interview, b = boss interview, f = finished',
  `points` int unsigned DEFAULT NULL COMMENT '0-100',
  `comment` text,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `vacancy_id` (`vacancy_id`),
  CONSTRAINT `tbl_recruitment_selection_applications_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_applications_ibfk_2` FOREIGN KEY (`vacancy_id`) REFERENCES `tbl_recruitment_selection_vacancies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_applications_before_insert` BEFORE INSERT ON `tbl_recruitment_selection_applications` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_recruitment_selection_candidates`
--

CREATE TABLE `tbl_recruitment_selection_candidates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `company_id` int unsigned NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `email` varchar(250) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_recruitment_selection_candidates_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_candidates_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_candidates_before_insert` BEFORE INSERT ON `tbl_recruitment_selection_candidates` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_recruitment_selection_files`
--

CREATE TABLE `tbl_recruitment_selection_files` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `application_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `vacancy_id` int unsigned NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `name` varchar(128) NOT NULL,
  `file` varchar(500) NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `vacancy_id` (`vacancy_id`),
  KEY `application_id` (`application_id`),
  CONSTRAINT `tbl_recruitment_selection_files_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_files_ibfk_2` FOREIGN KEY (`vacancy_id`) REFERENCES `tbl_recruitment_selection_vacancies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_files_ibfk_3` FOREIGN KEY (`application_id`) REFERENCES `tbl_recruitment_selection_applications` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_files_before_insert` BEFORE INSERT ON `tbl_recruitment_selection_files` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_recruitment_selection_interviews`
--

CREATE TABLE `tbl_recruitment_selection_interviews` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `last_date` date DEFAULT NULL,
  `actual_date` date DEFAULT NULL,
  `interviewer_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `vacancy_id` int unsigned NOT NULL,
  `application_id` int unsigned DEFAULT NULL,
  `candidate_id` int unsigned DEFAULT NULL,
  `status` enum('p','a','r') NOT NULL DEFAULT 'p' COMMENT 'p = pending, a = accepted, r = rejected',
  `type` enum('hr','b') DEFAULT NULL COMMENT 'hr = human resources, b = Boss',
  `points` int unsigned DEFAULT NULL COMMENT '0-100',
  `content` text,
  `comment` text,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `last_date` (`last_date`),
  KEY `interviewer_id` (`interviewer_id`),
  KEY `company_id` (`company_id`),
  KEY `vacancy_id` (`vacancy_id`),
  KEY `application_id` (`application_id`),
  KEY `candidate_id` (`candidate_id`),
  CONSTRAINT `tbl_recruitment_selection_interviews_ibfk_1` FOREIGN KEY (`interviewer_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_interviews_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`),
  CONSTRAINT `tbl_recruitment_selection_interviews_ibfk_3` FOREIGN KEY (`vacancy_id`) REFERENCES `tbl_recruitment_selection_vacancies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_interviews_ibfk_4` FOREIGN KEY (`application_id`) REFERENCES `tbl_recruitment_selection_applications` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_interviews_ibfk_5` FOREIGN KEY (`candidate_id`) REFERENCES `tbl_recruitment_selection_candidates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_interviews_before_insert` BEFORE INSERT ON `tbl_recruitment_selection_interviews` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_interviews_after_insert` AFTER INSERT ON `tbl_recruitment_selection_interviews` FOR EACH ROW BEGIN
	INSERT INTO `tbl_calendar_events` (`user_id`, `start_time` , `relational_id`, `type`)
	VALUES (NEW.interviewer_id, NEW.last_date, NEW.id, 'recruitment-selection-interview');
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_interviews_after_update` AFTER UPDATE ON `tbl_recruitment_selection_interviews` FOR EACH ROW BEGIN
	DELETE FROM `tbl_calendar_events`  WHERE `type` = 'recruitment-selection-interview' AND `relational_id` = OLD.id;


	INSERT INTO `tbl_calendar_events` (`user_id`, `start_time` , `relational_id`, `type`)
	VALUES (NEW.interviewer_id, NEW.last_date, NEW.id, 'recruitment-selection-interview');
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_interviews_after_delete` AFTER DELETE ON `tbl_recruitment_selection_interviews` FOR EACH ROW BEGIN
	DELETE FROM `tbl_calendar_events`  WHERE `type` = 'recruitment-selection-interview' AND `relational_id` = OLD.id;
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_recruitment_selection_pre_aplications`
--

CREATE TABLE `tbl_recruitment_selection_pre_aplications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `location` text,
  `previous_positions` text,
  `skills` text,
  `specialization` text,
  `vacancy_compatibility_score` decimal(5,2) DEFAULT NULL,
  `vacancy_id` int unsigned NOT NULL,
  `years_experience` text,
  `updated_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `added_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `vacancy_id` (`vacancy_id`),
  CONSTRAINT `tbl_recruitment_selection_pre_aplications_ibfk_1` FOREIGN KEY (`vacancy_id`) REFERENCES `tbl_recruitment_selection_vacancies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_recruitment_selection_vacancies`
--

CREATE TABLE `tbl_recruitment_selection_vacancies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  `job_description_id` int unsigned DEFAULT NULL,
  `job_category_id` int unsigned NOT NULL,
  `description` text NOT NULL,
  `location_id` int unsigned NOT NULL,
  `industry_id` int unsigned DEFAULT NULL,
  `last_date` date NOT NULL,
  `content` text,
  `status` enum('a','i','e','c') NOT NULL COMMENT 'a = active, i = inactive, c = complete, e = expire',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `job_description_id` (`job_description_id`),
  KEY `job_category_id` (`job_category_id`),
  KEY `location_id` (`location_id`),
  KEY `industry_id` (`industry_id`),
  CONSTRAINT `tbl_recruitment_selection_vacancies_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_vacancies_ibfk_2` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_vacancies_ibfk_3` FOREIGN KEY (`job_category_id`) REFERENCES `tbl_job_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_vacancies_ibfk_4` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_recruitment_selection_vacancies_ibfk_5` FOREIGN KEY (`industry_id`) REFERENCES `tbl_industries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_recruitment_selection_vacancies_before_insert` BEFORE INSERT ON `tbl_recruitment_selection_vacancies` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_role_routes`
--

CREATE TABLE `tbl_role_routes` (
  `role_id` int unsigned NOT NULL,
  `route` varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `role_route` (`role_id`,`route`),
  KEY `route` (`route`),
  CONSTRAINT `tbl_role_routes_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `tbl_roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tbl_role_routes_ibfk_2` FOREIGN KEY (`route`) REFERENCES `tbl_routes` (`route`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_roles`
--

CREATE TABLE `tbl_roles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `service_id` int unsigned DEFAULT NULL,
  `code` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creator` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `service_id` (`service_id`),
  CONSTRAINT `tbl_roles_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `tbl_services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1701 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_routes`
--

CREATE TABLE `tbl_routes` (
  `route` varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `public` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `required` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `route` (`route`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_route_after_insert` AFTER INSERT ON `tbl_routes` FOR EACH ROW BEGIN
	IF (SUBSTRING(NEW.route,1,5) = 'admin') THEN
		SET @role_id = fn_fetch_role_id_by_role_code('admin');
	
		INSERT INTO `tbl_role_routes` (`role_id`, `route`) VALUES (@role_id, NEW.route);
	END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_self_evaluation_form_users`
--

CREATE TABLE `tbl_self_evaluation_form_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `form_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `form_id` (`form_id`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_self_evaluation_form_users_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `tbl_self_evaluation_forms` (`id`),
  CONSTRAINT `tbl_self_evaluation_form_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_self_evaluation_form_users_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_self_evaluation_forms`
--

CREATE TABLE `tbl_self_evaluation_forms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  `description` text,
  `text` text,
  `language` enum('en','es') NOT NULL COMMENT 'en - ENGLISH,es - SPANISH',
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `status` enum('a','i') NOT NULL COMMENT 'A - ACTIVE, I - INACTIVE',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_self_evaluation_forms_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb3;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_self_evaluation_form_before_insert` BEFORE INSERT ON `tbl_self_evaluation_forms` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_self_evaluation_tests`
--

CREATE TABLE `tbl_self_evaluation_tests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL,
  `form_id` int unsigned NOT NULL,
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `comments` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('d','p','r','c') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'd- DRAFT p - PENDING, r - REVIEW,  c - COMPLETE',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `form_id` (`form_id`),
  KEY `user_id` (`user_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_self_evaluation_tests_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `tbl_self_evaluation_forms` (`id`),
  CONSTRAINT `tbl_self_evaluation_tests_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`),
  CONSTRAINT `tbl_self_evaluation_tests_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_company_self_evaluation_test_before_insert` BEFORE INSERT ON `tbl_self_evaluation_tests` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_sent_newsletters`
--

CREATE TABLE `tbl_sent_newsletters` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `newsletter_id` int unsigned NOT NULL,
  `subscriber_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `newsletter_id` (`newsletter_id`),
  KEY `subscriber_id` (`subscriber_id`),
  CONSTRAINT `tbl_sent_newsletters_ibfk_1` FOREIGN KEY (`newsletter_id`) REFERENCES `tbl_newsletters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_sent_newsletters_ibfk_2` FOREIGN KEY (`subscriber_id`) REFERENCES `tbl_subscribers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_services`
--

CREATE TABLE `tbl_services` (
  `id` int unsigned NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `default_company_is_required` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_sessions`
--

CREATE TABLE `tbl_sessions` (
  `id` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `modified` int unsigned DEFAULT NULL,
  `lifetime` int unsigned DEFAULT NULL,
  `data` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  UNIQUE KEY `id` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_shorters`
--

CREATE TABLE `tbl_shorters` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `target` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `url` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'a' COMMENT 'a = active, i = inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `target` (`target`)
) ENGINE=InnoDB AUTO_INCREMENT=226 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_shorters_before_insert` BEFORE INSERT ON `tbl_shorters` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_skills`
--

CREATE TABLE `tbl_skills` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_skill_before_insert` BEFORE INSERT ON `tbl_skills` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_survey_campaign_industries`
--

CREATE TABLE `tbl_survey_campaign_industries` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `campaign_id` int unsigned NOT NULL,
  `industry_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `industry_id` (`industry_id`),
  KEY `campaign_id` (`campaign_id`),
  CONSTRAINT `tbl_survey_campaign_industries_ibfk_1` FOREIGN KEY (`industry_id`) REFERENCES `tbl_industries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_campaign_industries_ibfk_2` FOREIGN KEY (`campaign_id`) REFERENCES `tbl_survey_campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_survey_campaign_jobs_description`
--

CREATE TABLE `tbl_survey_campaign_jobs_description` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `campaign_id` int unsigned NOT NULL,
  `job_description_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `job_description_id` (`job_description_id`),
  KEY `campaign_id` (`campaign_id`),
  CONSTRAINT `tbl_survey_campaign_jobs_description_ibfk_1` FOREIGN KEY (`job_description_id`) REFERENCES `tbl_jobs_description` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_campaign_jobs_description_ibfk_2` FOREIGN KEY (`campaign_id`) REFERENCES `tbl_survey_campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_survey_campaign_languages`
--

CREATE TABLE `tbl_survey_campaign_languages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `campaign_id` int unsigned NOT NULL,
  `language_id` varchar(5) COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `language_id` (`language_id`),
  KEY `campaign_id` (`campaign_id`),
  CONSTRAINT `tbl_survey_campaign_languages_ibfk_1` FOREIGN KEY (`language_id`) REFERENCES `tbl_languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_campaign_languages_ibfk_2` FOREIGN KEY (`campaign_id`) REFERENCES `tbl_survey_campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_survey_campaign_skills`
--

CREATE TABLE `tbl_survey_campaign_skills` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `campaign_id` int unsigned NOT NULL,
  `skill_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `skill_id` (`skill_id`),
  KEY `campaign_id` (`campaign_id`),
  CONSTRAINT `tbl_survey_campaign_skills_ibfk_1` FOREIGN KEY (`skill_id`) REFERENCES `tbl_skills` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_campaign_skills_ibfk_2` FOREIGN KEY (`campaign_id`) REFERENCES `tbl_survey_campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tbl_survey_campaigns`
--

CREATE TABLE `tbl_survey_campaigns` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `form_id` int unsigned NOT NULL,
  `location_id` int unsigned DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `content` text,
  `type` enum('n','c') NOT NULL COMMENT 'n = normal, c = organizational climate',
  `identity` enum('y','n') NOT NULL COMMENT 'y = yes, n = no',
  `total_test` int unsigned NOT NULL DEFAULT '0',
  `completed_test` int unsigned NOT NULL DEFAULT '0',
  `status` enum('a','i') NOT NULL COMMENT 'a = active, i = inactive',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `form_id` (`form_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `tbl_survey_campaigns_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_campaigns_ibfk_2` FOREIGN KEY (`form_id`) REFERENCES `tbl_survey_forms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_campaigns_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_survey_campaigns_before_insert` BEFORE INSERT ON `tbl_survey_campaigns` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_survey_forms`
--

CREATE TABLE `tbl_survey_forms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  `text` text,
  `content` text NOT NULL,
  `status` enum('a','i') NOT NULL COMMENT 'a - ACTIVE, i - INACTIVE	',
  `type` enum('n','c') NOT NULL COMMENT 'n = normal, c = organizational climate',
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tbl_survey_forms_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_survey_forms_before_insert` BEFORE INSERT ON `tbl_survey_forms` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_survey_tests`
--

CREATE TABLE `tbl_survey_tests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) NOT NULL,
  `company_id` int unsigned NOT NULL,
  `campaign_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `email` varchar(250) DEFAULT NULL,
  `status` enum('p','c') NOT NULL DEFAULT 'p' COMMENT 'p = pending, c = completed',
  `content` text NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `company_id` (`company_id`),
  KEY `campaign_id` (`campaign_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_survey_tests_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_tests_ibfk_2` FOREIGN KEY (`campaign_id`) REFERENCES `tbl_survey_campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_survey_tests_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_survey_tests_before_insert` BEFORE INSERT ON `tbl_survey_tests` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_survey_tests_after_insert` AFTER INSERT ON `tbl_survey_tests` FOR EACH ROW BEGIN
	UPDATE `tbl_survey_campaigns` SET `total_test` = `total_test` + 1 WHERE `id` = NEW.campaign_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_survey_tests_after_update` AFTER UPDATE ON `tbl_survey_tests` FOR EACH ROW BEGIN
	IF OLD.status = 'p' AND NEW.status = 'c' THEN 
		UPDATE `tbl_survey_campaigns` SET `completed_test` = `completed_test` + 1 WHERE `id` = NEW.campaign_id;
	END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`phpmyadmin`@`localhost`*/ /*!50003 TRIGGER `tr_survey_tests_after_delete` AFTER DELETE ON `tbl_survey_tests` FOR EACH ROW BEGIN
	DELETE FROM `tbl_calendar_events`  WHERE `type` IN  ('survey-normal', 'survey-organizational-climate')
	AND `relational_id` = OLD.id;
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_sync_logs`
--

CREATE TABLE `tbl_sync_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `device_uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `type` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `data` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `device_uuid` (`device_uuid`,`added_on`),
  KEY `device_uuid_2` (`device_uuid`,`type`,`added_on`),
  KEY `ip` (`ip`)
) ENGINE=InnoDB AUTO_INCREMENT=27764 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_themes`
--

CREATE TABLE `tbl_themes` (
  `id` int unsigned NOT NULL,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('a','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'a - Active, i - inactive',
  `default` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'y = Yes, n = No',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `imagesize_logo` varchar(11) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `imagesize_navbar` varchar(11) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `imagesize_favico` varchar(11) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `imagesize_moodle` varchar(11) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_theme_before_insert` BEFORE INSERT ON `tbl_themes` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_topics`
--

CREATE TABLE `tbl_topics` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `high_performance_group_id` int unsigned DEFAULT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `type` enum('hptg','myt','hptgf','dc') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'hptg =''high performance teams group '', "myt"='' my trainer'', hptgf="high performance teams group foro ",  dc = ''development content''',
  `status` enum('a','d','i') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT '	a - Active, d - Delete, i - inactive	',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `high_performance_group_id` (`high_performance_group_id`),
  CONSTRAINT `tbl_topics_ibfk_1` FOREIGN KEY (`high_performance_group_id`) REFERENCES `tbl_high_performance_team_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_topics_before_insert` BEFORE INSERT ON `tbl_topics` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_topics_feed`
--

CREATE TABLE `tbl_topics_feed` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `feed_id` int unsigned NOT NULL,
  `topic_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `feed_id` (`feed_id`),
  KEY `topic_id` (`topic_id`),
  CONSTRAINT `tbl_topics_feed_ibfk_1` FOREIGN KEY (`feed_id`) REFERENCES `tbl_feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_topics_feed_ibfk_2` FOREIGN KEY (`topic_id`) REFERENCES `tbl_topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_topics_feed_before_insert` BEFORE INSERT ON `tbl_topics_feed` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_transactions`
--

CREATE TABLE `tbl_transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `provider` varchar(25) DEFAULT NULL,
  `external_id` varchar(128) DEFAULT NULL,
  `external_ref` varchar(128) DEFAULT NULL,
  `internal_id` varchar(128) DEFAULT NULL,
  `internal_ref` varchar(128) DEFAULT NULL,
  `type` enum('payment','coupon','reverse','transfer','invoice') NOT NULL,
  `status` enum('pending','processing','rejected','completed','cancelled') NOT NULL,
  `previous` decimal(18,2) DEFAULT '0.00',
  `amount` decimal(18,2) DEFAULT '0.00',
  `current` decimal(18,2) DEFAULT '0.00',
  `description` varchar(50) NOT NULL,
  `request` text,
  `response` text,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `provider` (`provider`,`external_id`),
  KEY `provider_2` (`provider`,`external_ref`),
  KEY `internal_id` (`internal_id`),
  KEY `internal_ref` (`internal_ref`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `tbl_user_aptitudes`
--

CREATE TABLE `tbl_user_aptitudes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `user_profile_id` int unsigned NOT NULL,
  `aptitude_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `user_profile_id` (`user_profile_id`),
  KEY `aptitude_id` (`aptitude_id`),
  CONSTRAINT `tbl_user_aptitudes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_aptitudes_ibfk_2` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_aptitudes_ibfk_3` FOREIGN KEY (`aptitude_id`) REFERENCES `tbl_user_aptitudes` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_billing_info`
--

CREATE TABLE `tbl_user_billing_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `location_id` int unsigned NOT NULL,
  `first_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `last_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `email` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `phone` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `tbl_user_billing_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_billing_info_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_browsers`
--

CREATE TABLE `tbl_user_browsers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `platform` varchar(50) DEFAULT NULL,
  `browser` varchar(50) DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `version` varchar(25) DEFAULT NULL,
  `is_mobile_device` smallint unsigned NOT NULL DEFAULT '0',
  `is_tablet` smallint unsigned NOT NULL DEFAULT '0',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`device_type`),
  CONSTRAINT `tbl_user_browsers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `tbl_user_educations`
--

CREATE TABLE `tbl_user_educations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `user_profile_id` int unsigned NOT NULL,
  `degree_id` int unsigned NOT NULL,
  `location_id` int unsigned NOT NULL,
  `university` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `field_of_study` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `from_year` varchar(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `to_year` varchar(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `grade_or_percentage` varchar(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `user_profile_id` (`user_profile_id`),
  KEY `degree_id` (`degree_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `tbl_user_educations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_educations_ibfk_2` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_educations_ibfk_3` FOREIGN KEY (`degree_id`) REFERENCES `tbl_degrees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_educations_ibfk_4` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_user_educations_insert` BEFORE INSERT ON `tbl_user_educations` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_user_experiences`
--

CREATE TABLE `tbl_user_experiences` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `user_profile_id` int unsigned NOT NULL,
  `industry_id` int unsigned NOT NULL,
  `company_size_id` int unsigned NOT NULL,
  `company` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `title` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `location_id` int unsigned NOT NULL,
  `from_month` int unsigned NOT NULL,
  `from_year` int unsigned NOT NULL,
  `to_month` int unsigned DEFAULT NULL,
  `to_year` int unsigned DEFAULT NULL,
  `is_current` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `user_profile_id` (`user_profile_id`),
  KEY `location_id` (`location_id`),
  KEY `industry_id` (`industry_id`),
  KEY `company_size_id` (`company_size_id`),
  CONSTRAINT `tbl_user_experiences_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_experiences_ibfk_2` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_experiences_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_experiences_ibfk_4` FOREIGN KEY (`industry_id`) REFERENCES `tbl_industries` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_experiences_ibfk_5` FOREIGN KEY (`company_size_id`) REFERENCES `tbl_company_sizes` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_user_experiences_insert` BEFORE INSERT ON `tbl_user_experiences` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_user_hobbies_and_interests`
--

CREATE TABLE `tbl_user_hobbies_and_interests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `user_profile_id` int unsigned NOT NULL,
  `hobby_and_interest_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `user_profile_id` (`user_profile_id`),
  KEY `hobby_and_interest_id` (`hobby_and_interest_id`),
  CONSTRAINT `tbl_user_hobbies_and_interests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_hobbies_and_interests_ibfk_2` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_hobbies_and_interests_ibfk_3` FOREIGN KEY (`hobby_and_interest_id`) REFERENCES `tbl_hobbies_and_interests` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_ips`
--

CREATE TABLE `tbl_user_ips` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `country_code` varchar(3) DEFAULT NULL,
  `country_name` varchar(50) DEFAULT NULL,
  `state_code` varchar(3) DEFAULT NULL,
  `state_name` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `ip` varchar(25) NOT NULL,
  `latitude` decimal(20,7) NOT NULL DEFAULT '0.0000000',
  `longitude` decimal(20,7) NOT NULL DEFAULT '0.0000000',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_user_ips_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=955 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `tbl_user_languages`
--

CREATE TABLE `tbl_user_languages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `user_profile_id` int unsigned NOT NULL,
  `language_id` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `user_profile_id` (`user_profile_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `tbl_user_languages_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_languages_ibfk_2` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_languages_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `tbl_languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=205 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_notification_settings`
--

CREATE TABLE `tbl_user_notification_settings` (
  `user_id` int unsigned NOT NULL,
  `receive_connection_request` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `accept_my_request_connection` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `receive_invitation_group` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `accept_my_request_join_group` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `receive_request_join_my_group` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `receive_invitation_company` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `like_my_feed` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `comment_my_feed` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `share_my_feed` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `receive_inmail` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `receive_invitation_meeting` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `receive_reminder_meeting` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `receive_records_available_meeting` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_user_notification_settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_profiles`
--

CREATE TABLE `tbl_user_profiles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `location_id` int unsigned DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `image` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `cover` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `facebook` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `twitter` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `instagram` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `public` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `visible` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'n',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `user_id` (`user_id`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `tbl_user_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_profiles_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `tbl_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1203 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_user_profile_insert` BEFORE INSERT ON `tbl_user_profiles` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
END */;;
DELIMITER ;

--
-- Table structure for table `tbl_user_providers`
--

CREATE TABLE `tbl_user_providers` (
  `id` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `provider` enum('facebook','twitter','google') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `email` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `image` varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`provider`),
  UNIQUE KEY `email` (`email`,`provider`),
  CONSTRAINT `tbl_user_providers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_roles`
--

CREATE TABLE `tbl_user_roles` (
  `user_id` int unsigned NOT NULL,
  `role_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`,`role_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `tbl_user_roles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `tbl_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_skills`
--

CREATE TABLE `tbl_user_skills` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `user_profile_id` int unsigned NOT NULL,
  `skill_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `user_profile_id` (`user_profile_id`),
  KEY `skill_id` (`skill_id`),
  CONSTRAINT `tbl_user_skills_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_skills_ibfk_2` FOREIGN KEY (`user_profile_id`) REFERENCES `tbl_user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_user_skills_ibfk_3` FOREIGN KEY (`skill_id`) REFERENCES `tbl_skills` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=165 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_user_two_factor`
--

CREATE TABLE `tbl_user_two_factor` (
  `code` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `status` enum('p','a','e') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'p = pending a = active e = expired',
  `activated_on` datetime DEFAULT NULL,
  `expired_on` datetime DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_user_two_factor_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_user_before_insert` BEFORE INSERT ON `tbl_users` FOR EACH ROW BEGIN
	SET NEW.uuid = fn_uuid_v4(); 
	SET NEW.share_key = fn_share_key();
END */;;
DELIMITER ;
DELIMITER ;;;
DELIMITER ;
DELIMITER ;;;
DELIMITER ;

--
-- Table structure for table `tbl_users_blocked`
--

CREATE TABLE `tbl_users_blocked` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `blocked_id` int unsigned NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`blocked_id`),
  KEY `blocked_id` (`blocked_id`),
  CONSTRAINT `tbl_users_blocked_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_users_blocked_ibfk_2` FOREIGN KEY (`blocked_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_users_deleted`
--

CREATE TABLE `tbl_users_deleted` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `first_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `last_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `email` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `image` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `gender` enum('m','f') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `phone` varchar(18) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `pending` enum('y','n') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'y',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `email` (`email`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_users_deleted_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_usertypes`
--

CREATE TABLE `tbl_usertypes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_video_converter`
--

CREATE TABLE `tbl_video_converter` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('feed','microlearning') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `filename` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Table structure for table `tbl_zoom_meeting_users`
--

CREATE TABLE `tbl_zoom_meeting_users` (
  `zoom_meeting_id` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `type` enum('c','p') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'p' COMMENT 'c = creator, p = participant',
  `added_on` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `zoom_meeting_id` (`zoom_meeting_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_zoom_meeting_users_ibfk_1` FOREIGN KEY (`zoom_meeting_id`) REFERENCES `tbl_zoom_meetings` (`id`),
  CONSTRAINT `tbl_zoom_meeting_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELIMITER ;;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_zoom_meeting_users_after_delete` AFTER DELETE ON `tbl_zoom_meeting_users` FOR EACH ROW BEGIN
	DELETE FROM `tbl_calendar_events` WHERE `type` = 'zoom' AND `relational_id` = OLD.zoom_meeting_id
	AND `user_id` = OLD.user_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `tr_zoom_meetings_after_delete` AFTER DELETE ON `tbl_zoom_meetings` FOR EACH ROW BEGIN
	DELETE FROM `tbl_calendar_events` WHERE `type` = 'zoom' AND `relational_id` = OLD.id;
END */;;
DELIMITER ;

--
-- Temporary view structure for view `vw_inmail_virtual_domains`
--

/*!50001 DROP VIEW IF EXISTS `vw_inmail_virtual_domains`*/;
/*!50001 CREATE VIEW `vw_inmail_virtual_domains` AS SELECT 
 1 AS `id`,
 1 AS `name`*/;

--
-- Temporary view structure for view `vw_inmail_virtual_users`
--

/*!50001 DROP VIEW IF EXISTS `vw_inmail_virtual_users`*/;
/*!50001 CREATE VIEW `vw_inmail_virtual_users` AS SELECT 
 1 AS `id`,
 1 AS `domain_id`,
 1 AS `password`,
 1 AS `email`*/;

--
-- Temporary view structure for view `vw_xmpp_users`
--

/*!50001 DROP VIEW IF EXISTS `vw_xmpp_users`*/;
/*!50001 CREATE VIEW `vw_xmpp_users` AS SELECT 
 1 AS `id`,
 1 AS `username`,
 1 AS `password`,
 1 AS `name`,
 1 AS `email`,
 1 AS `is_super_user`*/;

--
-- Final view structure for view `vw_inmail_virtual_domains`
--

/*!50001 DROP VIEW IF EXISTS `vw_inmail_virtual_domains`*/;

--
-- Final view structure for view `vw_inmail_virtual_users`
--

/*!50001 DROP VIEW IF EXISTS `vw_inmail_virtual_users`*/;

--
-- Final view structure for view `vw_xmpp_users`
--

/*!50001 DROP VIEW IF EXISTS `vw_xmpp_users`*/;


