-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.1.26-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win32
-- HeidiSQL Version:             9.4.0.5173
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping structure for table dash_audit_v4.tbl_client
CREATE TABLE IF NOT EXISTS `tbl_client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_name` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `date_reg` datetime NOT NULL,
  `status` enum('available','deleted') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_client: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_client` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_client` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_download_file_log
CREATE TABLE IF NOT EXISTS `tbl_download_file_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `engagement_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `user_id_downloader` int(11) NOT NULL,
  `date_download` datetime NOT NULL,
  `file_type` enum('template','attachment') NOT NULL,
  `downloaded_file_name` varchar(255) NOT NULL,
  `action_status` enum('open','downloaded') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_download_file_log: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_download_file_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_download_file_log` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_engagement
CREATE TABLE IF NOT EXISTS `tbl_engagement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `date_target` datetime NOT NULL,
  `description` text NOT NULL,
  `status` enum('available','trash') NOT NULL,
  `folder_type` enum('0','1') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `tbl_engagement_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `tbl_client` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_engagement: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_engagement` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_engagement` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_engagement_members
CREATE TABLE IF NOT EXISTS `tbl_engagement_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `engagement_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role` enum('reviewer 1','reviewer 2','preparer','team leader','partner') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `engagement_id` (`engagement_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_engagement_members_ibfk_1` FOREIGN KEY (`engagement_id`) REFERENCES `tbl_engagement` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_engagement_members_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_engagement_members: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_engagement_members` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_engagement_members` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_file
CREATE TABLE IF NOT EXISTS `tbl_file` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `engagement_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `date_upload` datetime NOT NULL,
  `file_name` varchar(320) NOT NULL,
  `file_type` varchar(32) NOT NULL,
  `file_size` varchar(32) NOT NULL,
  `file_content` longblob NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('signed off','archive','open') NOT NULL,
  `file_version` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `engagement_id` (`engagement_id`,`template_id`,`user_id`),
  KEY `template_id` (`template_id`),
  CONSTRAINT `tbl_file_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `tbl_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_file_ibfk_2` FOREIGN KEY (`engagement_id`) REFERENCES `tbl_engagement` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_file: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_file` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_file` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_file_approval
CREATE TABLE IF NOT EXISTS `tbl_file_approval` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `engagement_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `level` enum('rev 1','rev 2','rev 3','team leader','partner') NOT NULL,
  `user_id` int(11) NOT NULL,
  `date_approval` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `engagement_id` (`engagement_id`,`template_id`,`user_id`),
  KEY `template_id` (`template_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_file_approval_ibfk_1` FOREIGN KEY (`engagement_id`) REFERENCES `tbl_engagement` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_file_approval_ibfk_2` FOREIGN KEY (`template_id`) REFERENCES `tbl_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_file_approval_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_file_approval: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_file_approval` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_file_approval` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_file_attachment
CREATE TABLE IF NOT EXISTS `tbl_file_attachment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `engagement_id` int(11) NOT NULL,
  `attachment_id` int(11) NOT NULL,
  `date_upload` datetime NOT NULL,
  `file_name` varchar(150) NOT NULL,
  `file_type` varchar(150) NOT NULL,
  `file_size` varchar(32) NOT NULL,
  `file_content` longblob NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('open','archived','signed off') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `engagement_id` (`engagement_id`,`attachment_id`,`user_id`),
  KEY `attachment_id` (`attachment_id`),
  CONSTRAINT `tbl_file_attachment_ibfk_1` FOREIGN KEY (`attachment_id`) REFERENCES `tbl_templates_attachment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_file_attachment_ibfk_2` FOREIGN KEY (`engagement_id`) REFERENCES `tbl_engagement` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_file_attachment: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_file_attachment` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_file_attachment` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_folders
CREATE TABLE IF NOT EXISTS `tbl_folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folder_name` varchar(100) NOT NULL,
  `parent_folder_id` int(11) NOT NULL,
  `folder_type` enum('0','1') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_folder_id` (`parent_folder_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_folders: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_folders` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_folders` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_notification
CREATE TABLE IF NOT EXISTS `tbl_notification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender_id` int(11) NOT NULL,
  `receiver_id` int(11) NOT NULL,
  `engagement_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `action` text NOT NULL,
  `date_sent` datetime NOT NULL,
  `status` enum('new','viewed','read') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sender_id` (`sender_id`),
  KEY `receiver_id` (`receiver_id`),
  KEY `engagement_id` (`engagement_id`),
  KEY `template_id` (`template_id`),
  CONSTRAINT `tbl_notification_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbl_notification_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_notification: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_notification` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_notification` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_review
CREATE TABLE IF NOT EXISTS `tbl_review` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `engagement_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `comments` text NOT NULL,
  `date_comment` datetime NOT NULL,
  `user_rev_id` int(11) NOT NULL,
  `resolution` text NOT NULL,
  `date_resolution` datetime NOT NULL,
  `user_prep_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `template_id` (`template_id`),
  CONSTRAINT `tbl_review_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `tbl_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_review: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_review` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_review` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_signed_off
CREATE TABLE IF NOT EXISTS `tbl_signed_off` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `engagement_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `role` varchar(32) NOT NULL,
  `date_signoff` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_signed_off: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_signed_off` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_signed_off` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_templates
CREATE TABLE IF NOT EXISTS `tbl_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(130) NOT NULL,
  `folder_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `folder_id` (`folder_id`),
  CONSTRAINT `tbl_templates_ibfk_1` FOREIGN KEY (`folder_id`) REFERENCES `tbl_folders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_templates: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_templates` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_templates` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_templates_attachment
CREATE TABLE IF NOT EXISTS `tbl_templates_attachment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `folder_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `folder_id` (`folder_id`),
  CONSTRAINT `tbl_templates_attachment_ibfk_1` FOREIGN KEY (`folder_id`) REFERENCES `tbl_folders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_templates_attachment: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_templates_attachment` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_templates_attachment` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_template_default_file
CREATE TABLE IF NOT EXISTS `tbl_template_default_file` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_id` int(11) NOT NULL,
  `file_name` varchar(100) NOT NULL,
  `file_type` varchar(100) NOT NULL,
  `file_size` varchar(100) NOT NULL,
  `file_content` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_template_default_file: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_template_default_file` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_template_default_file` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_user
CREATE TABLE IF NOT EXISTS `tbl_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(255) NOT NULL,
  `type` enum('admin','partner','user','team leader') NOT NULL DEFAULT 'user',
  `fname` varchar(32) NOT NULL,
  `lname` varchar(32) NOT NULL,
  `mname` varchar(32) NOT NULL,
  `date_reg` datetime NOT NULL,
  `last_activity` datetime NOT NULL,
  `status` enum('offline','online') NOT NULL,
  `main_status` enum('available','trashed') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_user: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_user` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_user` ENABLE KEYS */;

-- Dumping structure for table dash_audit_v4.tbl_user_availability
CREATE TABLE IF NOT EXISTS `tbl_user_availability` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `engagement_id` int(11) NOT NULL,
  `status` enum('online','offline') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table dash_audit_v4.tbl_user_availability: ~0 rows (approximately)
/*!40000 ALTER TABLE `tbl_user_availability` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl_user_availability` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
