-- EXEMPLO E ORIENTAÇÕES BASE
-- CREATE TABLE IF NOT EXISTS `module_default` ( --  criar tabela assim
--   `id` int(11) NOT NULL AUTO_INCREMENT,      -- PRIMARY KEY deve ser sempre ID
--  PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;        -- necessário definir engine e charset

 CREATE TABLE IF NOT EXISTS  `module_fleets_vehicle_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `sorder` int(11) DEFAULT NULL,
  `default` enum('1','0') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

-- INSERTS DEDAULT PARA A TABELA
INSERT INTO `module_fleets_vehicle_type` (`id`, `name`, `default`) VALUES ('1', 'Fleets Motorbike', '1');
INSERT INTO `module_fleets_vehicle_type` (`id`, `name`, `default`) VALUES ('2', 'Fleets Car', '1');
INSERT INTO `module_fleets_vehicle_type` (`id`, `name`, `default`) VALUES ('3', 'Fleets Utility', '1');
INSERT INTO `module_fleets_vehicle_type` (`id`, `name`, `default`) VALUES ('4', 'Fleets Truck', '1');

-- TABELA DE REGISTRO DE VEÍCULOS
 CREATE TABLE IF NOT EXISTS `module_fleets_vehicle` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `module_fleets_vehicle_type_id` int(11) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `board` varchar(45) DEFAULT NULL,
  `renavam` varchar(45) DEFAULT NULL,
  `fuel` enum('Gasoline','Alcool','Diesel','Gas','Flex') DEFAULT NULL,
  `acquisition_value` decimal(15,2) DEFAULT NULL,
  `market_value` decimal(15,2) DEFAULT NULL,
  `current_km` int(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_module_fleets_vehicle_module_fleets_vehicle_type_idx` (`module_fleets_vehicle_type_id`),
  CONSTRAINT `fk_module_fleets_vehicle_module_fleets_vehicle_type` FOREIGN KEY (`module_fleets_vehicle_type_id`) REFERENCES `module_fleets_vehicle_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- alocação de veículos
 CREATE TABLE IF NOT EXISTS `module_fleets_alocation_vehicle` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) DEFAULT NULL,
  `km_out` bigint(20) DEFAULT NULL,
  `date_out` timestamp NULL DEFAULT NULL,
  `tank_out` int(11) DEFAULT NULL,
  `tank_in` int(11) DEFAULT NULL,
  `module_fleets_vehicle_id` int(11) DEFAULT NULL,
  `km_in` bigint(20) DEFAULT NULL,
  `supply_liters` int(11) DEFAULT NULL,
  `supply_value` decimal(15,2) DEFAULT NULL,
  `out` enum('1','0') DEFAULT '1',
  `date_in` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_module_fleets_alocation_vehicle_vehicle_idx` (`module_fleets_vehicle_id`),
  KEY `fk_module_fleets_alocation_vehicle_contact_idx` (`employee_id`),
  CONSTRAINT `fk_module_fleets_alocation_vehicle_contact` FOREIGN KEY (`employee_id`) REFERENCES `crm_accounts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_module_fleets_alocation_vehicle_vehicle` FOREIGN KEY (`module_fleets_vehicle_id`) REFERENCES `module_fleets_vehicle` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE IF NOT EXISTS `module_fleets_alocation_vehicle_attach` (
  `id` int(11) NOT NULL,
  `src` varchar(100) DEFAULT NULL,
  `module_fleets_alocation_vehicle_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_module_fleets_alocation_vehicle_attach_alocation_idx` (`module_fleets_alocation_vehicle_id`),
  CONSTRAINT `fk_module_fleets_alocation_vehicle_attach_alocation` FOREIGN KEY (`module_fleets_alocation_vehicle_id`) REFERENCES `module_fleets_alocation_vehicle` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `module_fleets_alocation_vehicle_attach` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;

-- update acrécimo de ano erp-233
ALTER TABLE `module_fleets_vehicle` 
ADD COLUMN `year` INT(4) NULL AFTER `current_km`;

 CREATE TABLE IF NOT EXISTS  `module_fleets_vehicle_brand` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `sorder` int(11) DEFAULT NULL,
  `default` enum('1','0') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;


ALTER TABLE `module_fleets_vehicle` 
ADD COLUMN `module_fleets_vehicle_brand_id` INT NULL AFTER `year`,
ADD INDEX `fk_module_fleets_vehicle_brand_idx` (`module_fleets_vehicle_brand_id` ASC);
ALTER TABLE `module_fleets_vehicle` 
ADD CONSTRAINT `fk_module_fleets_vehicle_brand`
  FOREIGN KEY (`module_fleets_vehicle_brand_id`)
  REFERENCES `module_fleets_vehicle_brand` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;