--
-- Create table `module_teammanagement_quiz_cat`
--
DROP TABLE IF EXISTS `module_teammanagement_quiz_cat`;
CREATE TABLE `module_teammanagement_quiz_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), 
    `src` VARCHAR(100), 
    `name_src` VARCHAR(100), 

   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 

DROP TABLE IF EXISTS `module_teammanagement_quiz_content`;
CREATE TABLE `module_teammanagement_quiz_content` (
    `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),
    `type` VARCHAR(100) ,
    `module_teammanagement_quiz_cat_id` INTEGER, 
    `link` VARCHAR(100), 
	`text` mediumtext, 
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 

DROP TABLE IF EXISTS `module_teammanagement_quiz_questions`;
CREATE TABLE `module_teammanagement_quiz_questions` (
    `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,
    `level` int(11),
    `module_teammanagement_quiz_cat_id` INTEGER, 
    `question` mediumtext,
    `option_1` mediumtext,
    `option_2` mediumtext,
    `option_3` mediumtext,
    `option_4` mediumtext, 
     answer int(11),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 

ALTER TABLE `module_teammanagement_quiz_cat` 
ADD COLUMN `active` INT NULL AFTER `name_src`;

ALTER TABLE `module_teammanagement_quiz_cat` 
CHANGE COLUMN `active` `active` INT(11) NULL DEFAULT 0 ;



DROP TABLE IF EXISTS `module_teammanagement_quiz_register`;
CREATE TABLE `module_teammanagement_quiz_register` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_user` int(11),
    `date` date,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `level` int(11),
    `module_teammanagement_quiz_question_id` INTEGER, 
    answered int(11),
    jump int(11),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


DROP TABLE IF EXISTS `module_teammanagement_statement`;
CREATE TABLE `module_teammanagement_statement` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_user` int(11),
    `date` date,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `type` varchar(45),
     description text,
    `module_teammanagement_quiz_question_id` INTEGER, 
     points int(11),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


DROP TABLE IF EXISTS `module_teammanagement_selfdiagnosis_question`;
CREATE TABLE `module_teammanagement_selfdiagnosis_question` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_user` int(11),
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `question` mediumtext,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 

DROP TABLE IF EXISTS `module_teammanagement_selfdiagnosis_question_option`;
CREATE TABLE `module_teammanagement_selfdiagnosis_question_option` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `module_teammanagement_selfdiagnosis_question_id` int(11),
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `option` mediumtext,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 



DROP TABLE IF EXISTS `module_teammanagement_config`;
CREATE TABLE `module_teammanagement_config` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `key` varchar(100),
    `value` text,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


INSERT INTO `module_teammanagement_config` (`id`, `key`, `value`) VALUES ('1', 'sales_volume_qty', '');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('2', 'volume_of_parts_qty');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('3', 'number_of_ustomers_served');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('4', 'average_ticket');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('5', 'relationship_x_footwear_clothing ');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('6', 'points_of_engagement');
INSERT INTO `module_teammanagement_config` (`id`, `key`, `value`) VALUES ('7', 'alias_points', 'Pontos');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('8', 'mascot_ok_quiz');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('9', 'mascot_default_pluggin');
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('10', 'mascot_error_quiz');

UPDATE `module_teammanagement_config` SET `key`='relationship_x_footwear_clothing' WHERE `id`='5';
ALTER TABLE `module_teammanagement_statement` 
DROP COLUMN `module_teammanagement_quiz_question_id`; -- coluna nao solicitada registro na task



UPDATE `module_teammanagement_config` SET `key`='number_of_customers_served' WHERE `id`='3';

UPDATE `module_teammanagement_config` SET `key`='volume_of_clotinhg' WHERE `id`='2';
INSERT INTO `module_teammanagement_config` (`id`, `key`) VALUES ('11', 'volume_of_footwear');


DROP TABLE IF EXISTS `module_teammanagement_register`;
CREATE TABLE `module_teammanagement_register` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_user` int(11),
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `parameter` varchar(100),
    `value` varchar(100),
    `date` date,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 

ALTER TABLE `module_teammanagement_config` 
ADD COLUMN `primary` INT NULL DEFAULT 0 AFTER `value`;


UPDATE `module_teammanagement_config` SET `primary`='1' WHERE `id`='11';
UPDATE `module_teammanagement_config` SET `primary`='1' WHERE `id`='1';
UPDATE `module_teammanagement_config` SET `primary`='1' WHERE `id`='2';
UPDATE `module_teammanagement_config` SET `primary`='1' WHERE `id`='3';
UPDATE `module_teammanagement_config` SET `primary`='1' WHERE `id`='6';


ALTER TABLE `module_teammanagement_register` 
ADD COLUMN `crm_accounts_id` INT NULL COMMENT '  incluir na tabela module_teammanagement_register uma coluna \"crm_account_id\" que vai indicar a filial (contato PJ cadastrado na tabela de contatos)' AFTER `date`;

ALTER TABLE `module_teammanagement_config` 
ADD COLUMN `icon` VARCHAR(45) NULL COMMENT 'icon para dashboard client' AFTER `primary`;


UPDATE `module_teammanagement_config` SET `icon`='fa-users' WHERE `id`='3';
UPDATE `module_teammanagement_config` SET `icon`='fa-shopping-cart' WHERE `id`='1';
UPDATE `module_teammanagement_config` SET `icon`='fa-tags' WHERE `id`='11';
UPDATE `module_teammanagement_config` SET `icon`='fa-id-badge' WHERE `id`='2';
UPDATE `module_teammanagement_config` SET `icon`='fa-thumbs-o-up' WHERE `id`='6';

ALTER TABLE `module_teammanagement_config` 
ADD COLUMN `color` VARCHAR(45) NULL AFTER `icon`;

ALTER TABLE `module_teammanagement_config` 
CHANGE COLUMN `color` `color` VARCHAR(45) NULL DEFAULT NULL COMMENT '.navy-bg \n.blue-bg \n.lazur-bg \n.yellow-bg\n.red-bg \n.black-bg \nexamples' ;

UPDATE `module_teammanagement_config` SET `color`='red-bg' WHERE `id`='1';
UPDATE `module_teammanagement_config` SET `color`='navy-bg' WHERE `id`='2';
UPDATE `module_teammanagement_config` SET `color`='blue-bg' WHERE `id`='3';
UPDATE `module_teammanagement_config` SET `color`='lazur-bg' WHERE `id`='6';
UPDATE `module_teammanagement_config` SET `color`='yellow-bg' WHERE `id`='11';
UPDATE `module_teammanagement_config` SET `icon`='fa-calculator', `color`='gray-bg' WHERE `id`='4';
UPDATE `module_teammanagement_config` SET `icon`='fa-balance-scale', `color`='black-bg' WHERE `id`='5';


DROP TABLE IF EXISTS `module_teammanagement_action_plan`;
CREATE TABLE `module_teammanagement_action_plan` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `title` VARCHAR(100),
    `description` text,
    `tags` text, 
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 


DROP TABLE IF EXISTS `module_teammanagement_configuration_action_plan`;
CREATE TABLE `module_teammanagement_configuration_action_plan` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `date_start` date,
     plan_ids text,
    `crm_accounts_id` int(11),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 

ALTER TABLE `module_teammanagement_configuration_action_plan` 
CHANGE COLUMN `crm_accounts_id` `crm_accounts_id` INT(11) NULL DEFAULT NULL COMMENT 'FILIAL' ;


DROP TABLE IF EXISTS `module_teammanagement_execution_action_plan`;
CREATE TABLE `module_teammanagement_execution_action_plan` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `date` datetime,
    `crm_accounts_id` int(11),
    `id_user` int(11),
    `module_teammanagement_configuration_action_plan_id` int(11),
    params mediumtext,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT CHARSET=utf8; 

ALTER TABLE `module_teammanagement_execution_action_plan` 
ADD COLUMN `obs` MEDIUMTEXT NULL AFTER `params`;

-- remove o primary da configuração de pontos de engajamento
UPDATE `module_teammanagement_config` SET `primary`='0' WHERE `id`='6';