-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Jul 09, 2022 at 06:17 PM
-- Server version: 5.7.31
-- PHP Version: 7.4.9

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `lankssqo_indexpo_dev`
--

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

--
-- Table structure for table `audit_cycles`
--

DROP TABLE IF EXISTS `audit_cycles`;
CREATE TABLE IF NOT EXISTS `audit_cycles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cutomer_certificates_id` int(11) NOT NULL,
  `cycles_id` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cutomer_certificates_id` (`cutomer_certificates_id`),
  KEY `cycles_id` (`cycles_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `audit_documents`
--

DROP TABLE IF EXISTS `audit_documents`;
CREATE TABLE IF NOT EXISTS `audit_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_phases_id` int(11) NOT NULL,
  `phases_id` int(11) NOT NULL,
  `customers_id` int(11) NOT NULL,
  `customers_name` varchar(200) NOT NULL,
  `certificates_id` int(11) NOT NULL,
  `document_types_id` int(11) NOT NULL,
  `document_types_name` varchar(100) NOT NULL,
  `file` varchar(200) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_phases_id` (`audit_phases_id`),
  KEY `phases_id` (`phases_id`),
  KEY `customers_id` (`customers_id`),
  KEY `certificates_id` (`certificates_id`),
  KEY `document_types_id` (`document_types_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `audit_documents_history`
--

DROP TABLE IF EXISTS `audit_documents_history`;
CREATE TABLE IF NOT EXISTS `audit_documents_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_documents_id` int(11) NOT NULL,
  `audit_documents_file` varchar(200) NOT NULL,
  `document_type_revisions_id` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_documents_id` (`audit_documents_id`),
  KEY `document_type_revisions_id` (`document_type_revisions_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `audit_phases`
--

DROP TABLE IF EXISTS `audit_phases`;
CREATE TABLE IF NOT EXISTS `audit_phases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `audit_cycles_id` int(11) NOT NULL,
  `phases_id` int(11) NOT NULL,
  `phases_name` varchar(100) NOT NULL,
  `customer_certificates_id` int(11) NOT NULL,
  `customers_id` int(11) NOT NULL,
  `customers_name` varchar(200) NOT NULL,
  `certificates_id` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_cycles_id` (`audit_cycles_id`),
  KEY `phases_id` (`phases_id`),
  KEY `customer_certificates_id` (`customer_certificates_id`),
  KEY `customers_id` (`customers_id`),
  KEY `certificates_id` (`certificates_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `certificates`
--

DROP TABLE IF EXISTS `certificates`;
CREATE TABLE IF NOT EXISTS `certificates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `certification_category_name` varchar(50) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `certificate_types_id` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `certificate_types_id` (`certificate_types_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `certificate_types`
--

DROP TABLE IF EXISTS `certificate_types`;
CREATE TABLE IF NOT EXISTS `certificate_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(50) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `customers`
--

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `address` varchar(500) NOT NULL,
  `first_contact` varchar(200) NOT NULL,
  `first_contact_phone_1` varchar(50) NOT NULL,
  `joined_date` datetime NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `customer_certificates`
--

DROP TABLE IF EXISTS `customer_certificates`;
CREATE TABLE IF NOT EXISTS `customer_certificates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customers_id` int(11) NOT NULL,
  `certificates_id` int(11) NOT NULL,
  `customers_name` varchar(200) NOT NULL,
  `accreditation` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customers_id` (`customers_id`),
  KEY `certificates_id` (`certificates_id`),
  KEY `customers_id_2` (`customers_id`),
  KEY `certificates_id_2` (`certificates_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `customer_name_changes`
--

DROP TABLE IF EXISTS `customer_name_changes`;
CREATE TABLE IF NOT EXISTS `customer_name_changes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customers_id` int(11) NOT NULL,
  `customers_name` varchar(200) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customers_id` (`customers_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `cycles`
--

DROP TABLE IF EXISTS `cycles`;
CREATE TABLE IF NOT EXISTS `cycles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cycle_name` varchar(50) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edietd_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `document_actions`
--

DROP TABLE IF EXISTS `document_actions`;
CREATE TABLE IF NOT EXISTS `document_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `audit_documents_id` int(11) NOT NULL,
  `audit_document_history_id` int(11) NOT NULL,
  `action_date` datetime NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `audit_documents_id` (`audit_documents_id`),
  KEY `audit_document_history_id` (`audit_document_history_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `document_types`
--

DROP TABLE IF EXISTS `document_types`;
CREATE TABLE IF NOT EXISTS `document_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `document_type_revisions`
--

DROP TABLE IF EXISTS `document_type_revisions`;
CREATE TABLE IF NOT EXISTS `document_type_revisions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_types_id` int(11) NOT NULL,
  `file_name` varchar(200) NOT NULL,
  `issue_no` varchar(50) NOT NULL,
  `issue_date` datetime NOT NULL,
  `revision_no` varchar(50) NOT NULL,
  `revision_date` datetime NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `document_types_id` (`document_types_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `migration`
--

DROP TABLE IF EXISTS `migration`;
CREATE TABLE IF NOT EXISTS `migration` (
  `version` varchar(180) NOT NULL,
  `apply_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`version`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `migration`
--

INSERT INTO `migration` (`version`, `apply_time`) VALUES
('m000000_000000_base', 1656924185),
('m130524_201442_init', 1656924204),
('m190124_110200_add_verification_token_column_to_user_table', 1656924204);

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

--
-- Table structure for table `phases`
--

DROP TABLE IF EXISTS `phases`;
CREATE TABLE IF NOT EXISTS `phases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_role_id` int(11) DEFAULT NULL,
  `auth_key` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password_reset_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` smallint(6) NOT NULL DEFAULT '10',
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL,
  `verification_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `password_reset_token` (`password_reset_token`),
  KEY `user_role_id` (`user_role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `user_customers`
--

DROP TABLE IF EXISTS `user_customers`;
CREATE TABLE IF NOT EXISTS `user_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `customers_id` int(11) NOT NULL,
  `has_access` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `customers_id` (`customers_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `user_roles`
--

DROP TABLE IF EXISTS `user_roles`;
CREATE TABLE IF NOT EXISTS `user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `allow_view` int(11) NOT NULL,
  `allow_download` int(11) NOT NULL,
  `allow_print` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_on` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `audit_cycles`
--
ALTER TABLE `audit_cycles`
  ADD CONSTRAINT `audit_cycles_ibfk_1` FOREIGN KEY (`cutomer_certificates_id`) REFERENCES `customer_certificates` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_cycles_ibfk_2` FOREIGN KEY (`cycles_id`) REFERENCES `cycles` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `audit_documents`
--
ALTER TABLE `audit_documents`
  ADD CONSTRAINT `audit_documents_ibfk_1` FOREIGN KEY (`audit_phases_id`) REFERENCES `audit_phases` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_documents_ibfk_2` FOREIGN KEY (`certificates_id`) REFERENCES `certificates` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_documents_ibfk_3` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_documents_ibfk_4` FOREIGN KEY (`document_types_id`) REFERENCES `document_types` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_documents_ibfk_5` FOREIGN KEY (`phases_id`) REFERENCES `phases` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `audit_documents_history`
--
ALTER TABLE `audit_documents_history`
  ADD CONSTRAINT `audit_documents_history_ibfk_1` FOREIGN KEY (`audit_documents_id`) REFERENCES `audit_documents` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_documents_history_ibfk_2` FOREIGN KEY (`document_type_revisions_id`) REFERENCES `document_type_revisions` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `audit_phases`
--
ALTER TABLE `audit_phases`
  ADD CONSTRAINT `audit_phases_ibfk_1` FOREIGN KEY (`audit_cycles_id`) REFERENCES `audit_cycles` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_phases_ibfk_2` FOREIGN KEY (`certificates_id`) REFERENCES `certificates` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_phases_ibfk_3` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_phases_ibfk_4` FOREIGN KEY (`customer_certificates_id`) REFERENCES `customer_certificates` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `audit_phases_ibfk_5` FOREIGN KEY (`phases_id`) REFERENCES `phases` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `certificates`
--
ALTER TABLE `certificates`
  ADD CONSTRAINT `certificates_ibfk_1` FOREIGN KEY (`certificate_types_id`) REFERENCES `certificate_types` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `customer_certificates`
--
ALTER TABLE `customer_certificates`
  ADD CONSTRAINT `customer_certificates_ibfk_1` FOREIGN KEY (`certificates_id`) REFERENCES `certificates` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `customer_certificates_ibfk_2` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `customer_name_changes`
--
ALTER TABLE `customer_name_changes`
  ADD CONSTRAINT `customer_name_changes_ibfk_1` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `document_actions`
--
ALTER TABLE `document_actions`
  ADD CONSTRAINT `document_actions_ibfk_1` FOREIGN KEY (`audit_documents_id`) REFERENCES `audit_documents` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `document_actions_ibfk_2` FOREIGN KEY (`audit_document_history_id`) REFERENCES `audit_documents_history` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `document_actions_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `document_type_revisions`
--
ALTER TABLE `document_type_revisions`
  ADD CONSTRAINT `document_type_revisions_ibfk_1` FOREIGN KEY (`document_types_id`) REFERENCES `document_types` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `user`
--
ALTER TABLE `user`
  ADD CONSTRAINT `user_ibfk_1` FOREIGN KEY (`user_role_id`) REFERENCES `user_roles` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `user_customers`
--
ALTER TABLE `user_customers`
  ADD CONSTRAINT `user_customers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `user_customers_ibfk_2` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`) ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
