CREATE TABLE `iq_client` (
  	`client_id`  	 		INT NOT NULL AUTO_INCREMENT,
  	`first_name` 	 		VARCHAR(55) NOT NULL DEFAULT 'null',
  	`last_name`  	 		VARCHAR(55) NOT NULL DEFAULT 'null',
  	`email`      	 		VARCHAR(55) NOT NULL DEFAULT 'null',
  	`phone_number` 			INT NOT NULL,
  	`address` 	 			VARCHAR(55) NOT NULL DEFAULT 'null',
  	`linkedin`       		VARCHAR(55) NOT NULL DEFAULT 'null',
  	`office_number`   		INT,
  	`website` 				VARCHAR(55) NOT NULL DEFAULT 'null',
  	`gst_number` 			VARCHAR(55) NOT NULL DEFAULT 'null',
  	`created_at` 			DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	`updated_at` 			DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  	`created_by` 			VARCHAR(55) DEFAULT NULL,
  	`updated_by` 			VARCHAR(55) DEFAULT NULL,
  	PRIMARY KEY (`client_id`)
);

CREATE TABLE iq_project_type (
	`project_type_id` 		INT NOT NULL AUTO_INCREMENT,
	`name` 					VARCHAR(255),
	`created_at` 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 			VARCHAR(55) DEFAULT NULL,
  	`updated_by` 			VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`project_type_id`)
);

CREATE TABLE iq_project_status (
	`project_status_id` 		INT NOT NULL AUTO_INCREMENT,
	`name` 						VARCHAR(55) NOT NULL DEFAULT 'null',
	`description` 				TEXT,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`project_status_id`)
);

CREATE TABLE iq_project (
	`project_id` 			INT NOT NULL AUTO_INCREMENT,
	`project_status_id` 	INT,
	`name` 					VARCHAR(255),
	`description` 			TEXT,
	`project_type_id` 		INT,
	`start_date` 			DATE,
	`end_date` 				DATE,
	`total_quote` 			DECIMAL(10,2),
	`total_quoted_efforts` 	DECIMAL(10,2),
	`jira_project_id_ext` 	INT,
	`created_at` 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 			VARCHAR(55) DEFAULT NULL,
  	`updated_by` 			VARCHAR(55) DEFAULT NULL,
	`lead_name` 			VARCHAR(255) DEFAULT NULL,
  	`lead_account_id` 		VARCHAR(255) DEFAULT NULL,
  	`is_deleted` 			TINYINT unsigned DEFAULT '1',
	PRIMARY KEY (`project_id`),
	FOREIGN KEY (`project_status_id`) REFERENCES `iq_project_status`(`project_status_id`) ON DELETE CASCADE,
	FOREIGN KEY (`project_type_id`) REFERENCES `iq_project_type`(`project_type_id`) ON DELETE CASCADE
);

CREATE TABLE iq_assignee (
	`assignee_id` 			INT NOT NULL AUTO_INCREMENT,
	`first_name` 			VARCHAR(255),
	`last_name` 			VARCHAR(255),
	`email`      	 		VARCHAR(55) NOT NULL DEFAULT 'null',
	`phone_number` 			INT DEFAULT '0',
	`designation_id`		INT DEFAULT '0',
	`created_at` 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 			VARCHAR(55) DEFAULT NULL,
  	`updated_by` 			VARCHAR(55) DEFAULT NULL,
	`jira_account_id_ext` 	VARCHAR(45) DEFAULT NULL,
	PRIMARY KEY (`assignee_id`)
);

CREATE TABLE iq_issue_type (
	`issue_type_id` 			INT NOT NULL AUTO_INCREMENT,
	`name` 						VARCHAR(255),
	`description` 				TEXT,
	`sla`      	 				VARCHAR(55) NOT NULL DEFAULT 'null',
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`issue_type_id`)
);

CREATE TABLE iq_issue (
	`issue_id` 					INT NOT NULL AUTO_INCREMENT,
	`project_id` 				INT,
	`issue_type_id` 			INT,
	`name` 						VARCHAR(255),
	`description` 				TEXT,
	`acceptance_criteria` 		TEXT,
	`start_date` 				DATE,
	`end_date` 					DATE,
	`actual_start_date` 		DATE,
	`actual_end_date`			DATE,
	`Priority` 					INT,
	`Estimated_hours` 			DECIMAL(10,2),
	`label`						VARCHAR(255),
	`parent_issue_id`			INT,
	`jira_sprint_number_ext`	VARCHAR(255),
	`total_quoted_efforts` 		DECIMAL(10,2),
	`jira_issue_id_ext` 		INT,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	`jira_reporter_id_ext` 		VARCHAR(255) DEFAULT NULL,
	`comments` 					TEXT,
  	`jira_assignee_id_ext` 		VARCHAR(255) DEFAULT NULL,
 	`assignee_id` 				INT DEFAULT NULL,
	PRIMARY KEY (`issue_id`),
	FOREIGN KEY (`project_id`) REFERENCES `iq_project`(`project_id`),
	FOREIGN KEY (`issue_type_id`) REFERENCES `iq_issue_type`(`issue_type_id`)
);

CREATE TABLE iq_client_project_map (
	`client_project_id` 		INT NOT NULL AUTO_INCREMENT,
	`client_id` 				INT NOT NULL,
	`project_id` 				INT NOT NULL,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`client_project_id`),
	FOREIGN KEY (`client_id`) REFERENCES `iq_client` (`client_id`),
	FOREIGN KEY (`project_id`) REFERENCES `iq_project` (`project_id`)
);

CREATE TABLE iq_project_assignee_map (
	`project_user_id` 		INT NOT NULL AUTO_INCREMENT,
	`project_id` 				INT NOT NULL,
	`assignee_id` 				INT NOT NULL,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`project_user_id`),
	FOREIGN KEY (`project_id`) REFERENCES `iq_project` (`project_id`),
	FOREIGN KEY (`assignee_id`) REFERENCES `iq_assignee` (`assignee_id`)
);

CREATE TABLE iq_issue_assignee_map (
	`issue_assignee_id` 		INT NOT NULL AUTO_INCREMENT,
	`assignee_id` 				INT NOT NULL,
	`issue_id` 					INT NOT NULL,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`issue_assignee_id`),
	FOREIGN KEY (`assignee_id`) REFERENCES `iq_assignee` (`assignee_id`),
	FOREIGN KEY (`issue_id`) REFERENCES `iq_issue` (`issue_id`)
);

CREATE TABLE iq_issue_status (
	`issue_status_id` 			INT NOT NULL AUTO_INCREMENT,
	`name` 						VARCHAR(55) NOT NULL DEFAULT 'null',
	`description` 				TEXT,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`issue_status_id`)
);

CREATE TABLE iq_issue_status_history (
	`issue_status_history_id` 	INT NOT NULL AUTO_INCREMENT,
	`issue_id` 					INT NOT NULL,
	`issue_status_id` 			INT NOT NULL,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`issue_status_history_id`),
	FOREIGN KEY (`issue_id`) REFERENCES `iq_issue` (`issue_id`),
	FOREIGN KEY (`issue_status_id`) REFERENCES `iq_issue_status` (`issue_status_id`)
);

CREATE TABLE iq_assignee_designation (
	`assignee_designation_id` 	INT NOT NULL AUTO_INCREMENT,
	`name` 						VARCHAR(55) NOT NULL DEFAULT 'null',
	`description` 				TEXT,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`assignee_designation_id`)
);

CREATE TABLE iq_issue_time_log (
	`issue_time_log_id` 		INT NOT NULL AUTO_INCREMENT,
	`issue_id` 					INT NOT NULL,
	`logged_hours` 				TEXT,
	`comments` 					TEXT,
	`jira_time_log_id_ext`		INT NOT NULL,
	`created_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	`updated_at` 				TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_by` 				VARCHAR(55) DEFAULT NULL,
  	`updated_by` 				VARCHAR(55) DEFAULT NULL,
	`jira_time_logged_at` 		TIMESTAMP NULL DEFAULT NULL,
  	`jira_time_logged_by_id_ext` VARCHAR(55) DEFAULT NULL,
  	`jira_time_logged_by_name` 	VARCHAR(255) DEFAULT NULL,
  	`jira_time_logged_by_email` VARCHAR(55) DEFAULT NULL,
	PRIMARY KEY (`issue_time_log_id`),
	FOREIGN KEY (`issue_id`) REFERENCES `iq_issue` (`issue_id`)
);
