# ************************************************************
# Sequel Ace SQL dump
# Version 20046
#
# https://sequel-ace.com/
# https://github.com/Sequel-Ace/Sequel-Ace
#
# Host: 127.0.0.1 (MySQL 5.7.37)
# Database: verified
# Generation Time: 2023-09-21 22:47:57 +0000
# ************************************************************



# Dump of table sessions
# ------------------------------------------------------------


CREATE TABLE `sessions` (
                            `id` char(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
                            `created` datetime DEFAULT CURRENT_TIMESTAMP,
                            `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                            `data` blob,
                            `expires` int(10) unsigned DEFAULT NULL,
                            PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Dump of table actions
# ------------------------------------------------------------


CREATE TABLE `actions` (
                           `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                           `object_type` int(11) DEFAULT NULL,
                           `action_user_id` bigint(20) DEFAULT NULL,
                           `block_id` bigint(20) DEFAULT NULL,
                           `block_issue_id` bigint(20) DEFAULT NULL,
                           `collaborator_id` bigint(20) DEFAULT NULL,
                           `company_id` bigint(20) DEFAULT NULL,
                           `company_person_id` bigint(20) DEFAULT NULL,
                           `email_state_id` bigint(20) DEFAULT NULL,
                           `user_id` bigint(20) DEFAULT NULL,
                           `user_company_job_id` bigint(20) DEFAULT NULL,
                           `log_message` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `log_level` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `description` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                           `data` blob,
                           `created` timestamp NULL DEFAULT NULL,
                           `modified` timestamp NULL DEFAULT NULL,
                           PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=521 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table blocks
# ------------------------------------------------------------


CREATE TABLE `blocks` (
                          `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                          `user_id` bigint(11) DEFAULT NULL,
                          `company_id` bigint(11) DEFAULT NULL,
                          `user_company_job_id` bigint(11) DEFAULT NULL,
                          `company_person_id` bigint(11) DEFAULT NULL,
                          `contract` int(11) DEFAULT NULL COMMENT '5 options',
                          `approved` timestamp NULL DEFAULT NULL,
                          `project` varchar(500) CHARACTER SET utf8mb4 DEFAULT NULL,
                          `project_link` varchar(150) CHARACTER SET utf8mb4 DEFAULT NULL,
                          `project_thumbnail` varchar(150) CHARACTER SET utf8mb4 DEFAULT NULL,
                          `project_thumbnail_alt_text` varchar(150) CHARACTER SET utf8mb4 DEFAULT NULL,
                          `personal_note` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL,
                          `denied` timestamp NULL DEFAULT NULL,
                          `uuid` binary(16) DEFAULT NULL,
                          `created` timestamp NULL DEFAULT NULL,
                          `modified` timestamp NULL DEFAULT NULL,
                          PRIMARY KEY (`id`),
                          UNIQUE KEY `blocks_u_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table blocks_issue
# ------------------------------------------------------------


CREATE TABLE `blocks_issue` (
                                `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                `block_id` bigint(11) DEFAULT NULL,
                                `issue_id` int(11) DEFAULT NULL,
                                `notes` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                `active` tinyint(1) DEFAULT NULL,
                                `uuid` binary(16) DEFAULT NULL,
                                `created` timestamp NULL DEFAULT NULL,
                                `modified` timestamp NULL DEFAULT NULL,
                                PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table blocks_states
# ------------------------------------------------------------


CREATE TABLE `blocks_states` (
                                 `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                 `block_id` bigint(11) DEFAULT NULL,
                                 `state_id` int(11) DEFAULT NULL,
                                 `active` tinyint(1) DEFAULT NULL,
                                 `created` timestamp NULL DEFAULT NULL,
                                 `modified` timestamp NULL DEFAULT NULL,
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=176 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table collaborators
# ------------------------------------------------------------


CREATE TABLE `collaborators` (
                                 `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                 `block_id` bigint(11) DEFAULT NULL,
                                 `user_id` bigint(11) DEFAULT NULL,
                                 `state_id` int(11) DEFAULT NULL COMMENT 'Entity without backing data',
                                 `uuid` binary(16) DEFAULT NULL,
                                 `email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                 `title` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                 `contract` int(11) DEFAULT NULL,
                                 `invited` timestamp NULL DEFAULT NULL,
                                 `linked` timestamp NULL DEFAULT NULL,
                                 `accepted` timestamp NULL DEFAULT NULL,
                                 `expired` timestamp NULL DEFAULT NULL,
                                 `deleted` timestamp NULL DEFAULT NULL,
                                 `created` timestamp NULL DEFAULT NULL,
                                 `modified` timestamp NULL DEFAULT NULL,
                                 PRIMARY KEY (`id`),
                                 KEY `collaborators_u_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table companies
# ------------------------------------------------------------


CREATE TABLE `companies` (
                             `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                             `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
                             `domain` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
                             `verification_installed` tinyint(1) DEFAULT NULL,
                             `verification_location` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
                             `uuid` binary(16) DEFAULT NULL,
                             `admin_verified` timestamp NULL DEFAULT NULL,
                             `created` timestamp NULL DEFAULT NULL,
                             `modified` timestamp NULL DEFAULT NULL,
                             PRIMARY KEY (`id`),
                             UNIQUE KEY `u_companies_uuid` (`uuid`),
                             UNIQUE KEY `u_domain` (`domain`),
                             KEY `companies_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1215 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table company_person
# ------------------------------------------------------------


CREATE TABLE `company_person` (
                                  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                  `company_id` bigint(11) DEFAULT NULL,
                                  `name` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `companyReferenceNumber` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                  `uuid` binary(16) DEFAULT NULL,
                                  `created` timestamp NULL DEFAULT NULL,
                                  `modified` timestamp NULL DEFAULT NULL,
                                  PRIMARY KEY (`id`),
                                  UNIQUE KEY `u_company_ref_num` (`companyReferenceNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table email_states
# ------------------------------------------------------------


CREATE TABLE `email_states` (
                                `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                `type` smallint(4) DEFAULT NULL COMMENT 'enum confirm | reset',
                                `user_id` bigint(11) DEFAULT NULL,
                                `uuid` binary(16) DEFAULT NULL,
                                `expiry` timestamp NULL DEFAULT NULL,
                                `action_taken` tinyint(1) DEFAULT NULL,
                                `action_date` timestamp NULL DEFAULT NULL,
                                `ip_address` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL,
                                `created` timestamp NULL DEFAULT NULL,
                                `modified` timestamp NULL DEFAULT NULL,
                                PRIMARY KEY (`id`),
                                UNIQUE KEY `u_es_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=533 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table roles
# ------------------------------------------------------------


CREATE TABLE `roles` (
                         `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                         `name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,
                         `description` varchar(150) CHARACTER SET utf8mb4 DEFAULT NULL,
                         `alias` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,
                         `created` timestamp NULL DEFAULT NULL,
                         `modified` timestamp NULL DEFAULT NULL,
                         PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table roles_users
# ------------------------------------------------------------


CREATE TABLE `roles_users` (
                               `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                               `user_id` int(11) NOT NULL,
                               `role_id` int(11) NOT NULL,
                               `created` timestamp NULL DEFAULT NULL,
                               `modified` timestamp NULL DEFAULT NULL,
                               PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table users_company_job
# ------------------------------------------------------------


CREATE TABLE `users_company_job` (
                                     `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                     `user_id` bigint(11) DEFAULT NULL,
                                     `company_id` bigint(20) DEFAULT NULL,
                                     `role_description` varchar(40) CHARACTER SET utf8mb4 DEFAULT NULL,
                                     `active` tinyint(1) DEFAULT NULL,
                                     `uuid` binary(16) DEFAULT NULL,
                                     `created` timestamp NULL DEFAULT NULL,
                                     `modified` timestamp NULL DEFAULT NULL,
                                     PRIMARY KEY (`id`),
                                     KEY `u_ucj_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=187 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table users_email_change
# ------------------------------------------------------------


CREATE TABLE `users_email_change` (
                                      `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                      `user_id` bigint(11) DEFAULT NULL,
                                      `new_email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                      `old_email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
                                      `uuid` binary(16) DEFAULT NULL,
                                      `active` tinyint(1) DEFAULT NULL,
                                      `created` timestamp NULL DEFAULT NULL,
                                      `modified` timestamp NULL DEFAULT NULL,
                                      PRIMARY KEY (`id`),
                                      UNIQUE KEY `u_uec_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



# Dump of table users_states
# ------------------------------------------------------------


CREATE TABLE `users_states` (
                                `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
                                `user_id` bigint(11) NOT NULL,
                                `state_id` int(11) NOT NULL,
                                `active` tinyint(1) DEFAULT '1',
                                `created` timestamp NULL DEFAULT NULL,
                                `modified` timestamp NULL DEFAULT NULL,
                                PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50338 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;




--
-- Dumping routines (FUNCTION) for database 'verified'
--
DELIMITER ;;

# Dump of FUNCTION b2s_uuid
# ------------------------------------------------------------

/*!50003 DROP FUNCTION IF EXISTS `b2s_uuid` */;;
/*!50003 SET SESSION SQL_MODE="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `b2s_uuid`(b BINARY(16)) RETURNS char(36) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    DETERMINISTIC
RETURN insert(
		insert(
         insert(
           insert(hex(b),9,0,'-'),
           14,0,'-'),
         19,0,'-'),
        24,0,'-') */;;

/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;
# Dump of FUNCTION s2b_uuid
# ------------------------------------------------------------

/*!50003 DROP FUNCTION IF EXISTS `s2b_uuid` */;;
/*!50003 SET SESSION SQL_MODE="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `s2b_uuid`(s CHAR(36)) RETURNS binary(16)
    DETERMINISTIC
RETURN unhex(replace(s,'-','')) */;;

/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;
DELIMITER ;

