ALTER TABLE `companies_allocations`
ADD COLUMN `crush_balance` DECIMAL(15,3) NULL DEFAULT 0 AFTER `amount`;


DROP VIEW IF EXISTS `companies_allocations_view`;
CREATE VIEW `companies_allocations_view` AS
select `ca`.`id`            AS `id`,
       `ca`.`company_id`    AS `company_id`,
       `ca`.`allocation_id` AS `allocation_id`,
       `ca`.`amount`        AS `amount`,
       `ca`.`crush_balance` AS `crush_balance`,
       `ca`.`date_created`  AS `date_created`,
       `ca`.`created_by`    AS `created_by`,
       `ca`.`date_modified` AS `date_modified`,
       `ca`.`modified_by`   AS `modified_by`,
       sum(`s`.`weight`)    AS `total_weight_shipped`
from (`companies_allocations` `ca`
         left join `shipments` `s` on ((`ca`.`id` = `s`.`company_allocation_id`)))
group by `ca`.`id`;


-- -----------------------------------------------------
-- Table `shipment_pending_amounts`
-- -----------------------------------------------------
CREATE TABLE `shipment_pending_amounts` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`company_allocation_id` INT(10) UNSIGNED NOT NULL,
	`product` ENUM('soybean','soybean_meal','soybean_oil','soybean_hull','soy_isolate','soy_concentrate','soy_flour','lecithin','soy_fiber') NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`weight` DECIMAL(15,3) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `company_allocation_id_product` (`company_allocation_id`, `product`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

-- -----------------------------------------------------
-- Table `customer_crush_balance`
-- -----------------------------------------------------
CREATE TABLE `customer_crush_balance` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`shipment_id` INT(10) UNSIGNED NOT NULL,
	`crush_balance` DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT '0.000',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

-- -----------------------------------------------------
-- Table `customer_pending_amounts`
-- -----------------------------------------------------
CREATE TABLE `customer_pending_amounts` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`shipment_id` INT(10) UNSIGNED NOT NULL,
	`product` ENUM('soybean','soybean_meal','soybean_oil','soybean_hull','soy_isolate','soy_concentrate','soy_flour','lecithin','soy_fiber') NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`weight` DECIMAL(15,3) UNSIGNED NOT NULL COMMENT 'Product weight (not Soybeans weight)',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `shipment_id_product` (`shipment_id`, `product`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;