--
-- Table structure for table `module_pcp_equipaments`
--
CREATE TABLE IF NOT EXISTS `module_pcp_equipaments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `description` varchar(45) DEFAULT NULL,
  `type` enum('0','1','2') NOT NULL,
  `production_capacity` float NOT NULL,
  `production_cost` decimal(10,2) NOT NULL,
  `situation` enum('0','1','2','3','4') NOT NULL DEFAULT '1',
  `brand` varchar(45) DEFAULT NULL,
  `model` varchar(45) DEFAULT NULL,
  `id_unit` int(11) DEFAULT NULL,
  `current_price` decimal(10,2) DEFAULT '0.00',
  `purchase_price` decimal(10,2) DEFAULT '0.00',
  `depreciation` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_module_pcp_equipaments_1_idx` (`id_unit`),
  CONSTRAINT `fk_module_pcp_equipaments_unit` FOREIGN KEY (`id_unit`) REFERENCES `sys_items_unit` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `module_pcp_equipament_maintenance`
--
  CREATE TABLE IF NOT EXISTS `module_pcp_equipament_maintenance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_equipament` int(11) NOT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `title` varchar(45) DEFAULT NULL,
  `description` varchar(45) DEFAULT NULL,
  `status` enum('0','1','2') DEFAULT NULL COMMENT 'Maintance Equipament Table',
  `value` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `fk_module_pcp_equipament_equipament_idx` (`id_equipament`),
  CONSTRAINT `fk_module_pcp_equipament_equipament` FOREIGN KEY (`id_equipament`) REFERENCES `module_pcp_equipaments` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `module_pcp_equipaments_employees`
--
CREATE TABLE IF NOT EXISTS `module_pcp_equipaments_employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_equipament` int(11) NOT NULL,
  `id_account` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_module_pcp_equipaments_employees_1_idx` (`id_equipament`),
  KEY `fk_module_pcp_accounts_idx` (`id_account`),
  CONSTRAINT `fk_module_pcp_accounts` FOREIGN KEY (`id_account`) REFERENCES `crm_accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_module_pcp_equipaments` FOREIGN KEY (`id_equipament`) REFERENCES `module_pcp_equipaments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `module_pcp_equipaments_maintenance_backlogs`
--
CREATE TABLE IF NOT EXISTS `module_pcp_equipaments_maintenance_backlogs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_maintenance` int(11) NOT NULL,
  `user` varchar(200) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `message` text,
  `close_message` text,
  `closed_by` varchar(200) DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `active` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_module_pcp_equipaments_maintanence_idx` (`id_maintenance`),
  CONSTRAINT `fk_module_pcp_equipaments_maintanence` FOREIGN KEY (`id_maintenance`) REFERENCES `module_pcp_equipament_maintenance` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

  CREATE TABLE IF NOT EXISTS `module_pcp_production_steps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `equipament_steps_id` int(11) DEFAULT NULL,
  `duration_time` int(11) DEFAULT NULL,
  `create_at` timestamp NULL DEFAULT NULL,
  `update_ar` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `order` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `module_pcp_production_steps_equipaments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_production_steps` int(11) DEFAULT NULL,
  `id_equipament` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `module_pcp_production_plan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `production_qtd` decimal(10,2) DEFAULT NULL,
  `obs` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `forecast_duration` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;



ALTER TABLE `module_pcp_equipament_maintenance`
ADD COLUMN `end_message` VARCHAR(45) NULL;

ALTER TABLE `module_pcp_equipaments`
ADD COLUMN `id_manufacturer` INT(11) NULL;


ALTER TABLE `module_pcp_equipaments`
ADD INDEX `fk_module_pcp_equipaments_manufacturer_idx` (`id_manufacturer` ASC);

ALTER TABLE `module_pcp_equipaments`
ADD CONSTRAINT `fk_module_pcp_equipaments_manufacturer`
  FOREIGN KEY (`id_manufacturer`)
  REFERENCES `sys_items_manufacturer` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `module_pcp_equipament_maintenance`
ADD COLUMN `operators` VARCHAR(255) NULL COMMENT 'This value is a crm.accounts ID refereces.';

ALTER TABLE `module_pcp_equipament_maintenance`
CHANGE COLUMN `description` `details` VARCHAR(45) NULL DEFAULT NULL ;

ALTER TABLE `module_pcp_equipaments`
DROP FOREIGN KEY `fk_module_pcp_equipaments_manufacturer`;
ALTER TABLE `module_pcp_equipaments`
ADD CONSTRAINT `fk_module_pcp_equipaments_manufacturer`
  FOREIGN KEY (`id_manufacturer`)
  REFERENCES `sys_items_manufacturer` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `module_pcp_equipaments_maintenance_backlogs`
DROP FOREIGN KEY `fk_module_pcp_equipaments_maintanence`;
ALTER TABLE `module_pcp_equipaments_maintenance_backlogs`
ADD CONSTRAINT `fk_module_pcp_equipaments_maintanence`
  FOREIGN KEY (`id_maintenance`)
  REFERENCES `module_pcp_equipament_maintenance` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

-- Leonardo Lopes
--  Estava faltando essa coluna no banco o que impedia a criação de uma etapa de produção.
ALTER TABLE `module_pcp_production_steps`
ADD COLUMN `ids_equipaments` VARCHAR(255) NULL;






-- INSERT INTO `module_pcp_equipaments` VALUES (1,'Impressora Fiscal','Impressora de uso fiscal.','0',5,0.50,'2','Bematech','CP-123',5,750.00,750.00,5.50,NULL,'2017-03-15 07:02:44'),(2,'Maquina de Embalar','Uma maquina que embala as coisas.','0',1000,5.50,'1','Rators','G213',1,3000.00,3760.00,15.00,NULL,'2017-03-14 20:13:57'),(3,'Esteira Rolante','Esteira de pátio','1',10,12.00,'1','','',5,1222.22,1233.33,12.09,NULL,'2017-03-15 06:14:12'),(4,'Seladora Automática','Para Picole, Sorvetes Com Data','0',120,1.00,'3','Flith','FG213\'',6,1997.00,1997.00,10.00,NULL,'2017-03-15 06:18:18'),(5,'Forno Mufla Digital','Microprocessado 2000c 20x15x15cm','0',1000,2.50,'1','Mufla','M55',6,700.00,700.00,0.00,NULL,'2017-03-15 06:18:14'),(6,'Dobradeira De Acrílico','Dobradeira Termoplástica 70cm','1',100,5.00,'4','Goldan','G988',4,2760.00,2760.00,70.00,NULL,'2017-03-15 06:14:21'),(7,'Secador De Material Plástico','Capacidade 50 Kilos/hora','1',50,500.00,'4','Trello','T542',3,250000.00,150000.00,2344.00,NULL,'2017-03-15 06:18:03');
-- INSERT INTO `module_pcp_equipaments_employees` VALUES (10,7,1001),(11,6,1002),(12,5,1001),(13,4,1002),(14,2,1001);
-- INSERT INTO `module_pcp_equipament_maintenance` VALUES (1,7,'2017-03-15 09:30:00',NULL,'Limpeza','','0',0.00),(2,6,'2017-03-16 09:30:00',NULL,'Limpeza','','0',0.00);