--
-- Create table `module_fish_tank`
--
DROP TABLE IF EXISTS `module_fish_tank`;
CREATE TABLE `module_fish_tank` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `crm_accounts_id` int(11) DEFAULT NULL,
  `title_tank` VARCHAR(64) DEFAULT NULL,
  `form_tank` ENUM('quadrado','circular','retangular') NULL DEFAULT NULL,
  `area_tank` VARCHAR(64) DEFAULT NULL,
  `depth_tank` VARCHAR(64) DEFAULT NULL,
  `type_tank` ENUM('tanque','viveiro') NULL DEFAULT NULL,
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),

  INDEX `fk_module_fish_tank_idx` (`crm_accounts_id` ASC),

  CONSTRAINT `fk_module_fish_tank_id`
  FOREIGN KEY (`crm_accounts_id`)
  REFERENCES `crm_accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

--
-- Create table `module_fish_species`
--
DROP TABLE IF EXISTS `module_fish_species`;
CREATE TABLE `module_fish_species` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `popular_name` VARCHAR(128) DEFAULT NULL,
  `scientific_name` VARCHAR(128) DEFAULT NULL,
  `food_habit` TEXT,
  `description_specie` TEXT DEFAULT NULL,
  `weight_expenditure` FLOAT NULL DEFAULT NULL,
  `order_specie` int(4) DEFAULT '0',
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_fish_food_program_periods`;
DROP TABLE IF EXISTS `module_fish_food_programs`;
CREATE TABLE `module_fish_food_programs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) DEFAULT NULL,
   initial_amount_fish  int,
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

--
-- Create table `module_fish_simplified_program`
--
CREATE TABLE `module_fish_food_program_periods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `module_fish_food_programs_id` INT(11) DEFAULT NULL,
  `period` INT(11) DEFAULT NULL,
  `period_total` INT(11) DEFAULT NULL,
  `sys_item_id` int(11) DEFAULT NULL,
  `number_deals` int(11) DEFAULT NULL,
  `weight_of` int(4) DEFAULT NULL,
  `weight_up` int(4) DEFAULT NULL,
  `mortality_rate` int(11) DEFAULT NULL,
  `biomass` DECIMAL(20,3) DEFAULT NULL,
  `pv` DECIMAL(20,2) DEFAULT NULL,
  `gpd` INT DEFAULT NULL,
  `consumption_day` DECIMAL(20,3) DEFAULT NULL,
  `tca` DECIMAL(20,2) DEFAULT NULL,
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_module_fish_food_programs_idx` (`module_fish_food_programs_id` ASC),
  CONSTRAINT `fk_module_fish_food_programs_id`
  FOREIGN KEY (`module_fish_food_programs_id`)
  REFERENCES `module_fish_food_programs` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


ALTER TABLE `module_fish_food_program_periods` 
CHANGE COLUMN `weight_of` `weight_of` DECIMAL(20,3) NULL DEFAULT NULL COMMENT 'KG' ,
CHANGE COLUMN `weight_up` `weight_up` DECIMAL(20,3) NULL DEFAULT NULL COMMENT 'KG' ,
CHANGE COLUMN `gpd` `gpd` DECIMAL(20,3) NULL DEFAULT NULL COMMENT 'KG' ;



--
-- INSERT table sys_items_category
--
INSERT INTO `sys_items_category` (`id`, `name`, `sorder`, `parent`) VALUES (NULL, 'Ração', NULL, NULL);


INSERT INTO `crm_groups` (`gname`, `alias`, `color`, `discount`, `parent`, `pid`, `exempt`, `description`, `separateinvoices`, `sorder`, `c1`, `c2`, `c3`, `c4`, `c5`, `default`) VALUES ('Associated', 'associated', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1');


DROP TABLE IF EXISTS `module_fish_lots`;
CREATE TABLE `module_fish_lots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `crm_accounts_id` int(11),
  `title_lote` VARCHAR(128) DEFAULT NULL,
   module_fish_species_id int(11),
   date_settlement date,
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module_fish_lots_distribution`;
CREATE TABLE `module_fish_lots_distribution` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `module_fish_lots_id` int(11),
   module_fish_tank_id int(11),
   qty int(11),
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `module_fish_lots_distribution_biometry`;
CREATE TABLE `module_fish_lots_distribution_biometry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `module_fish_lots_distribution_id` int(11),
  `date` date,
   average_weight  decimal(20,3),
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `module_fish_lots_distribution_program`;
CREATE TABLE `module_fish_lots_distribution_program` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `module_fish_lots_distribution_id` int(11),
  `date` date,
   module_fish_program_id  int(11),
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `module_fish_feeding`;
CREATE TABLE `module_fish_feeding` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `module_fish_lots_id` int(11),
  `date` date,
  `qty` DECIMAL(20,3) DEFAULT NULL,
   sys_item_id  int(11),
   user_id  int(11),
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


ALTER TABLE `module_fish_lots_distribution_biometry` 
ADD COLUMN `user_id` INT NULL AFTER `deleted_at`;



DROP TABLE IF EXISTS `module_fish_movement`;
CREATE TABLE `module_fish_movement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `module_fish_lots_distribution_id` int(11),
  `date` timestamp,
  `user_id`  int(11),
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `module_fish_movement_itens`;
CREATE TABLE `module_fish_movement_itens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `qty` INT,
  `type`  enum('fusion','division','death','sale'),
   module_fish_movement_id INT,
  `module_fish_tank_id_origin`  int(11),
  `module_fish_tank_id_destiny`  int(11),
  `default` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


ALTER TABLE `module_fish_lots_distribution` 
ADD COLUMN `closed` TINYINT(1) NULL DEFAULT 0 AFTER `deleted_at`,
ADD COLUMN `module_fish_movement_id` INT NULL AFTER `closed`;

ALTER TABLE `module_fish_lots_distribution_biometry` 
ADD COLUMN `module_fish_movement_id` INT NULL;

--  necessário armazenar essa informação para montar a view correta depois,
--  da movimentaçaõ de morte e de venda e de fusao, que o cadastro da biometria vai ser diferente para a fusion
ALTER TABLE `module_fish_movement_itens` 
ADD COLUMN `average_weight` DECIMAL(20,3) NULL AFTER `deleted_at`;

ALTER TABLE `module_fish_tank` 
ADD COLUMN `function_tank` ENUM('engorda','berçario') NULL DEFAULT NULL AFTER `deleted_at`;

ALTER TABLE `module_fish_movement` 
ADD COLUMN `sales_price` DECIMAL(20,2) NULL AFTER `deleted_at`,
ADD COLUMN `client_id` INT NULL COMMENT 'CRM_ACCOUNTS_ID' AFTER `sales_price`;

--  ERP-478
ALTER TABLE `module_fish_movement_itens` 
CHANGE COLUMN `type` `type` ENUM('fusion', 'division', 'death', 'sale', 'recont') NULL DEFAULT NULL ;

-- ERP - 479
ALTER TABLE `sys_stock` 
ADD COLUMN `crm_accounts_id` INT NULL COMMENT 'ARMAZENA O ID do associado';

-- Correção do nome da coluna, para indicar a tabela correta
ALTER TABLE `module_fish_feeding` 
CHANGE COLUMN `module_fish_lots_id` `module_fish_lots_distribution_id` INT(11) NULL DEFAULT NULL ;


ALTER TABLE `module_fish_food_programs` 
ADD COLUMN `crm_accounts_id` INT NULL ;