--
-- Create table `module_helpdesk_quiz_cat`
--
DROP TABLE IF EXISTS `module_helpdesk_tickets_cat`;
CREATE TABLE `module_helpdesk_tickets_cat` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `default` tinyint(1) DEFAULT '0',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `title` VARCHAR(100),
    `description` VARCHAR(200) ,
    `icon` VARCHAR(100), 
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


DROP TABLE IF EXISTS `module_helpdesk_tickets_priority`;
CREATE TABLE `module_helpdesk_tickets_priority` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `default` tinyint(1) DEFAULT '0',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `name` VARCHAR(100),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


ALTER TABLE `module_helpdesk_tickets_priority` 
ADD COLUMN `sorder` INT NULL AFTER `name`;



DROP TABLE IF EXISTS `module_helpdesk_tickets_treatment_type`;
CREATE TABLE `module_helpdesk_tickets_treatment_type` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `default` tinyint(1) DEFAULT '0',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `name` VARCHAR(100),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


ALTER TABLE `module_helpdesk_tickets_treatment_type` 
ADD COLUMN `sorder` INT NULL AFTER `name`;




DROP TABLE IF EXISTS `module_helpdesk_tickets`;
CREATE TABLE `module_helpdesk_tickets` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `default` tinyint(1) DEFAULT '0',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `requester_id` int(11) ,
    `client_id` int(11) ,
    `product_cat_id` int(11) ,
    `product_id` int(11) ,
    `responsible_id` int(11) ,
    `module_helpdesk_tickets_treatment_type_id` int(11) ,
    `status` ENUM('Open','Closed') ,
    `module_helpdesk_tickets_priority_id` int(11) ,
    `description` mediumtext, 
     module_helpdesk_tickets_cat_id int,
   PRIMARY KEY (`id`)
)  ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 



CREATE TABLE `module_helpdesk_tickets_attach` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `src` varchar(100) DEFAULT NULL,
  `name` text DEFAULT NULL,
  `module_helpdesk_tickets_id` int(11) DEFAULT NULL,
  user_id  int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

ALTER TABLE `module_helpdesk_tickets` 
CHANGE COLUMN `status` `status` ENUM('Open', 'Closed', 'Atendence') NULL DEFAULT NULL ;


ALTER TABLE `module_helpdesk_tickets` 
CHANGE COLUMN `default` `deleted_at` TIMESTAMP NULL DEFAULT NULL ;



DELETE FROM `module_helpdesk_tickets_priority` WHERE `id`>='1';
INSERT INTO `module_helpdesk_tickets_priority` (`id`, `default`, `name`) VALUES ('1', '1','module_helpdesk priority Functional');
INSERT INTO `module_helpdesk_tickets_priority` (`id`, `default`, `name`) VALUES ('2', '1', 'module_helpdesk priority Partially stopped');
INSERT INTO `module_helpdesk_tickets_priority` (`id`, `default`, `name`) VALUES ('3', '1', 'module_helpdesk priority Stopped');


CREATE TABLE `module_helpdesk_tickets_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_cat_id` int(11) ,
  `product_id` int(11) ,
  `module_helpdesk_tickets_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


ALTER TABLE `module_helpdesk_tickets` 
DROP COLUMN `product_id`,
DROP COLUMN `product_cat_id`;


DELETE FROM `module_helpdesk_tickets_treatment_type` WHERE `id`>='1';
INSERT INTO `module_helpdesk_tickets_treatment_type` (`id`, `default`, `name`) VALUES ('1', '1', 'module_helpdesk type remote');
INSERT INTO `module_helpdesk_tickets_treatment_type` (`id`, `default`, `name`) VALUES ('2', '1', 'module_helpdesk type presential');
INSERT INTO `module_helpdesk_tickets_treatment_type` (`id`, `default`, `name`) VALUES ('3', '1', 'module_helpdesk send equipament');

ALTER TABLE `module_helpdesk_tickets` 
ADD COLUMN `treatment_type_local` VARCHAR(45) NULL;

CREATE TABLE `module_helpdesk_tickets_type_attachment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `src` varchar(100) DEFAULT NULL,
  `name` text DEFAULT NULL,
  `module_helpdesk_tickets_id` int(11) DEFAULT NULL,
  user_id  int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


-- LEONARDO LOPES
-- CRUD PARA SLA
DROP TABLE IF EXISTS `module_helpdesk_sla`;
CREATE TABLE `module_helpdesk_sla` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_by` int(11) NULL DEFAULT NULL,
    `updated_by` int(11) NULL DEFAULT NULL,
    `deleted_by` int(11) NULL DEFAULT NULL,
    `client_id` int(11),
    `sla1` decimal(10,2) NOT NULL DEFAULT '0.00',
    `sla2` decimal(10,2) NOT NULL DEFAULT '0.00',
    `sla3` decimal(10,2) NOT NULL DEFAULT '0.00',
    `sla4` decimal(10,2) NOT NULL DEFAULT '0.00',
    `sla5` decimal(10,2) NOT NULL DEFAULT '0.00',
    `module_helpdesk_tickets_treatment_type_id` int(11) ,
    `module_helpdesk_tickets_cat_id`int(11),
   PRIMARY KEY (`id`)
)  ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


DROP TABLE IF EXISTS `module_helpdesk_sla_products`;
CREATE TABLE `module_helpdesk_sla_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_cat_id` int(11) ,
  `product_id` int(11) ,
  `module_helpdesk_sla_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


ALTER TABLE `module_helpdesk_tickets` 
ADD COLUMN `begin_at` TIMESTAMP NULL DEFAULT NULL;

ALTER TABLE `module_helpdesk_tickets` 
ADD COLUMN `closed_at` TIMESTAMP NULL DEFAULT NULL;

ALTER TABLE `module_helpdesk_tickets` 
ADD COLUMN `form_id` INT NULL DEFAULT NULL;


--
-- Create table 'module_forms_data'
--
DROP TABLE IF EXISTS `module_helpdesk_form_data`;
CREATE TABLE `module_helpdesk_form_data` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `ticket_id` int(11) NOT NULL,  
    `form_id` int(11) NOT NULL,
    `question_id` int(11) NOT NULL,
    `question_type` int(11) NOT NULL,
    `field` varchar(555) NOT NULL,
    `value` text NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `module_helpdesk_tickets_form_attachment`;
CREATE TABLE `module_helpdesk_tickets_form_attachment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `src` varchar(100) DEFAULT NULL,
  `name` text DEFAULT NULL,
  `ticket_id` int(11) DEFAULT NULL,
  `user_id` int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


-- colocar question type varchar

ALTER TABLE `module_helpdesk_form_data` 
CHANGE COLUMN `question_type` `question_type` VARCHAR(555) NOT NULL ;


-- erp-564
ALTER TABLE `module_helpdesk_tickets` 
ADD COLUMN `id_offline` INT NULL AFTER `form_id`;


ALTER TABLE `module_helpdesk_tickets` 
CHANGE COLUMN `id_offline` `id_offline` VARCHAR(255) NULL DEFAULT NULL ;


-- histoty serialnumber_client
CREATE TABLE `crm_customfieldsvalues_history` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `fieldid` int(10) NOT NULL,
  `relid` int(10) NOT NULL,
  `fvalue` text NOT NULL,
   date_start date,
   date_end date default null,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

ALTER TABLE `crm_customfieldsvalues_history` 
ADD COLUMN `count_fvalue` INT NULL AFTER `date_end`;


ALTER TABLE `crm_customfieldsvalues_history` 
ADD INDEX `INDICE` (`relid` ASC, `fieldid` ASC),
ADD INDEX `INDICEDATA` (`date_start` ASC, `date_end` ASC);

-- André 07-01-22
ALTER TABLE `module_helpdesk_tickets` 
ADD COLUMN `requester_name_client` VARCHAR(255) NULL AFTER `id_offline`,
ADD COLUMN `requester_email_client` VARCHAR(255) NULL AFTER `requester_name_client`;


-- 
-- LEONARDO LOPES RAMOS
-- 19/06/2023
--
ALTER TABLE `module_helpdesk_tickets` 
ADD INDEX `INDEX_HEX` (
          `deleted_at` ASC, 
          `created_at` ASC, 
          `responsible_id` ASC, 
          `module_helpdesk_tickets_treatment_type_id` ASC, 
          `status` ASC, `module_helpdesk_tickets_cat_id` ASC, 
          `client_id` ASC);


ALTER TABLE `module_helpdesk_tickets_products` 
ADD INDEX `INDEX HEX` (`module_helpdesk_tickets_id` ASC, `product_id` ASC, `product_cat_id` ASC);

ALTER TABLE `crm_accounts` 
ADD INDEX `INDEX HEX` (`account` ASC, `company` ASC, `cpf_cnpj` ASC, `foreign_doc_identification` ASC);

UPDATE `crm_groups` SET `default` = '1' WHERE (`id` = '6');

ALTER TABLE `module_helpdesk_tickets` 
ADD COLUMN `provider_id` INT(11) NULL AFTER `requester_email_client`;


-- 
-- LEONARDO LOPES RAMOS
-- 09/09/2024
--
ALTER TABLE `module_helpdesk_tickets` 
ADD INDEX `INDEX_CSV` (`requester_id` ASC, `form_id` ASC, `module_helpdesk_tickets_priority_id` ASC, `provider_id` ASC) VISIBLE;

ALTER TABLE `module_forms_questions` 
ADD INDEX `INDEX_CSV` (`parent_id` ASC) VISIBLE;

ALTER TABLE `module_helpdesk_form_data` 
ADD INDEX `INDEX CSV` (`ticket_id` ASC, `form_id` ASC, `question_id` ASC) VISIBLE;