-- MySQL Script generated by MySQL Workbench
-- Fri Feb 12 15:01:40 2021
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema USSES WORDPRESS UPDATE
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `allocations`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `allocations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `amount` FLOAT NOT NULL,
  `notes` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `date_created` DATETIME NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `date_modified` DATETIME NOT NULL,
  `modified_by` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 9
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `companies`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `companies` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `address` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `address2` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `city` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `state` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `zip` VARCHAR(16) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `phone` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `active` TINYINT UNSIGNED NOT NULL,
  `status` ENUM('pending', 'active', 'inactive', 'rejected') CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `ussec_note` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `date_created` DATETIME NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `date_modified` DATETIME NOT NULL,
  `modified_by` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name` (`name` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 10129
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `companies_allocations`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `companies_allocations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `allocation_id` INT UNSIGNED NOT NULL,
  `amount` FLOAT NOT NULL,
  `crush_balance` DECIMAL(15,3) NULL DEFAULT 0,
  `company_note` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci',
  `ussec_note` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci',
  `date_created` DATETIME NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `date_modified` DATETIME NOT NULL,
  `modified_by` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `unique_company_allocation` (`company_id` ASC, `allocation_id` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 346
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `companies_allocations_pending`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `companies_allocations_pending` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `allocation_id` INT UNSIGNED NOT NULL,
  `amount` FLOAT NOT NULL,
  `company_note` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci',
  `ussec_note` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci',
  `date_created` DATETIME NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `date_modified` DATETIME NOT NULL,
  `modified_by` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `unique_company_allocation` (`company_id` ASC, `allocation_id` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `companies_allocations_rejected`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `companies_allocations_rejected` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `allocation_id` INT UNSIGNED NOT NULL,
  `amount` FLOAT NOT NULL,
  `company_note` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci',
  `ussec_note` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci',
  `date_created` DATETIME NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `date_modified` DATETIME NOT NULL,
  `modified_by` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 7
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `departments`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `departments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `address` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `address2` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `city` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `state` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `zip` VARCHAR(16) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `phone` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `email` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `date_created` DATETIME NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `date_modified` DATETIME NOT NULL,
  `modified_by` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 116
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `shipments`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `shipments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `department_id` INT UNSIGNED NOT NULL,
  `company_allocation_id` INT UNSIGNED NOT NULL,
  `allocation_id` INT UNSIGNED NOT NULL,
  `company_name` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL DEFAULT '',
  `product` ENUM('soybean', 'soybean_meal', 'soybean_oil', 'soybean_hull', 'soy_isolate', 'soy_concentrate', 'soy_flour', 'lecithin', 'soy_fiber') CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `input_weight` DECIMAL(15,3) UNSIGNED NOT NULL,
  `weight` DECIMAL(15,3) UNSIGNED NOT NULL,
  `country` INT UNSIGNED NOT NULL,
  `vessel` VARCHAR(30) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `contract_ref` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `port_location` VARCHAR(60) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `port_location_include` TINYINT UNSIGNED NOT NULL,
  `destination` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `ship_date` DATE NOT NULL,
  `notes` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `customer_emails` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `approved_signatory` INT UNSIGNED NOT NULL,
  `cert_code` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `cert_pin` VARCHAR(4) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `date_created` DATETIME NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `date_modified` DATETIME NOT NULL,
  `modified_by` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 75435711
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
--
-- Table structure for table `wp_usermeta`
--

CREATE TABLE `wp_usermeta` (
                               `umeta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
                               `user_id` bigint unsigned NOT NULL DEFAULT '0',
                               `meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
                               `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
                               PRIMARY KEY (`umeta_id`),
                               KEY `user_id` (`user_id`),
                               KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

--
-- Table structure for table `wp_users`
--

CREATE TABLE `wp_users` (
    `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
    `user_login` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `user_pass` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `user_nicename` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `user_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `user_url` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `user_registered` datetime NOT NULL DEFAULT NOW(),
    `user_activation_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    `user_status` int NOT NULL DEFAULT '0',
    `display_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    PRIMARY KEY (`ID`),
    KEY `user_login_key` (`user_login`),
    KEY `user_nicename` (`user_nicename`),
    KEY `user_email` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

--
-- Dumping data for table `wp_users`
--

INSERT INTO `wp_users` VALUES (1,'usses_admn','$P$Bvpw7L86.aMd1DDHqlTrtHseE/jr290','usses_admn','mike@beanstalkwebsolutions.com','https://usses.beanstalkweb.com','2020-09-23 19:52:11','',0,'usses_admn');

--
-- Dumping data for table `wp_usermeta`
--

INSERT INTO `wp_usermeta` VALUES (1,1,'nickname','usses_admn'),(2,1,'first_name','Michael'),(3,1,'last_name','Stokes'),(4,1,'description',''),(5,1,'rich_editing','true'),(6,1,'syntax_highlighting','true'),(7,1,'comment_shortcuts','false'),(8,1,'admin_color','fresh'),(9,1,'use_ssl','0'),(10,1,'show_admin_bar_front','true'),(11,1,'locale',''),(12,1,'wp_capabilities','a:1:{s:13:\"administrator\";b:1;}'),(13,1,'wp_user_level','10'),(14,1,'dismissed_wp_pointers','vc_pointers_backend_editor,theme_editor_notice'),(15,1,'show_welcome_panel','1'),(18,1,'wfls-last-login','1615389769'),(19,1,'wp_dashboard_quick_press_last_post_id','66'),(17,1,'session_tokens','a:5:{s:64:\"aad9e95ac366cc650abe618330a484ba7eddfaf5a32b8bb9b8132b399701db8e\";a:4:{s:10:\"expiration\";i:1615392958;s:2:\"ip\";s:14:\"141.101.77.181\";s:2:\"ua\";s:78:\"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0\";s:5:\"login\";i:1615220158;}s:64:\"abf972e4e14fd8c9d113e931164a6920a458624e3eac73de948e8c8df093d55c\";a:4:{s:10:\"expiration\";i:1615417356;s:2:\"ip\";s:15:\"108.162.216.197\";s:2:\"ua\";s:114:\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.72 Safari/537.36\";s:5:\"login\";i:1615244556;}s:64:\"1bae3a8fa9707e8b5d1a9caf43d1c2962b99ad6a59acda68b34d26e67bd8a46b\";a:4:{s:10:\"expiration\";i:1615438075;s:2:\"ip\";s:14:\"162.158.74.196\";s:2:\"ua\";s:121:\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36\";s:5:\"login\";i:1615265275;}s:64:\"8c570fb3f949d713e1b3c90f1d63f9fb3d6428d4ef5176bd2dd2f89b0ef54b5f\";a:4:{s:10:\"expiration\";i:1615520939;s:2:\"ip\";s:9:\"127.0.0.1\";s:2:\"ua\";s:121:\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36\";s:5:\"login\";i:1615348139;}s:64:\"f32f2fcf2a92636fabaae1d9fa1cf064a1a0c86312fede25a31c3f3d06c38303\";a:4:{s:10:\"expiration\";i:1615562569;s:2:\"ip\";s:12:\"172.68.37.49\";s:2:\"ua\";s:78:\"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0\";s:5:\"login\";i:1615389769;}}'),(47,1,'closedpostboxes_page','a:0:{}'),(48,1,'metaboxhidden_page','a:3:{i:0;s:19:\"wpb_visual_composer\";i:1;s:7:\"slugdiv\";i:2;s:9:\"authordiv\";}'),(21,1,'community-events-location','a:1:{s:2:\"ip\";s:12:\"24.171.116.0\";}'),(37,1,'wp_yoast_notifications','a:1:{i:0;a:2:{s:7:\"message\";s:369:\"<strong>New in Yoast SEO 15.2: </strong>You’re now able to optimize URLs for more visibility when shared on Slack! <a href=\"https://yoa.st/yoast15-2?php_version=7.4&#038;platform=wordpress&#038;platform_version=5.5.3&#038;software=free&#038;software_version=15.2.1&#038;days_active=30plus&#038;user_language=en_US\" target=\"_blank\">Read all about version 15.2 here</a>\";s:7:\"options\";a:10:{s:4:\"type\";s:7:\"updated\";s:2:\"id\";s:20:\"wpseo-plugin-updated\";s:4:\"user\";O:7:\"WP_User\":8:{s:4:\"data\";O:8:\"stdClass\":10:{s:2:\"ID\";s:1:\"1\";s:10:\"user_login\";s:10:\"usses_admn\";s:9:\"user_pass\";s:34:\"$P$Bvpw7L86.aMd1DDHqlTrtHseE/jr290\";s:13:\"user_nicename\";s:10:\"usses_admn\";s:10:\"user_email\";s:30:\"mike@beanstalkwebsolutions.com\";s:8:\"user_url\";s:30:\"https://usses.beanstalkweb.com\";s:15:\"user_registered\";s:19:\"2020-09-23 19:52:11\";s:19:\"user_activation_key\";s:0:\"\";s:11:\"user_status\";s:1:\"0\";s:12:\"display_name\";s:10:\"usses_admn\";}s:2:\"ID\";i:1;s:4:\"caps\";a:1:{s:13:\"administrator\";b:1;}s:7:\"cap_key\";s:15:\"wp_capabilities\";s:5:\"roles\";a:1:{i:0;s:13:\"administrator\";}s:7:\"allcaps\";a:63:{s:13:\"switch_themes\";b:1;s:11:\"edit_themes\";b:1;s:16:\"activate_plugins\";b:1;s:12:\"edit_plugins\";b:1;s:10:\"edit_users\";b:1;s:10:\"edit_files\";b:1;s:14:\"manage_options\";b:1;s:17:\"moderate_comments\";b:1;s:17:\"manage_categories\";b:1;s:12:\"manage_links\";b:1;s:12:\"upload_files\";b:1;s:6:\"import\";b:1;s:15:\"unfiltered_html\";b:1;s:10:\"edit_posts\";b:1;s:17:\"edit_others_posts\";b:1;s:20:\"edit_published_posts\";b:1;s:13:\"publish_posts\";b:1;s:10:\"edit_pages\";b:1;s:4:\"read\";b:1;s:8:\"level_10\";b:1;s:7:\"level_9\";b:1;s:7:\"level_8\";b:1;s:7:\"level_7\";b:1;s:7:\"level_6\";b:1;s:7:\"level_5\";b:1;s:7:\"level_4\";b:1;s:7:\"level_3\";b:1;s:7:\"level_2\";b:1;s:7:\"level_1\";b:1;s:7:\"level_0\";b:1;s:17:\"edit_others_pages\";b:1;s:20:\"edit_published_pages\";b:1;s:13:\"publish_pages\";b:1;s:12:\"delete_pages\";b:1;s:19:\"delete_others_pages\";b:1;s:22:\"delete_published_pages\";b:1;s:12:\"delete_posts\";b:1;s:19:\"delete_others_posts\";b:1;s:22:\"delete_published_posts\";b:1;s:20:\"delete_private_posts\";b:1;s:18:\"edit_private_posts\";b:1;s:18:\"read_private_posts\";b:1;s:20:\"delete_private_pages\";b:1;s:18:\"edit_private_pages\";b:1;s:18:\"read_private_pages\";b:1;s:12:\"delete_users\";b:1;s:12:\"create_users\";b:1;s:17:\"unfiltered_upload\";b:1;s:14:\"edit_dashboard\";b:1;s:14:\"update_plugins\";b:1;s:14:\"delete_plugins\";b:1;s:15:\"install_plugins\";b:1;s:13:\"update_themes\";b:1;s:14:\"install_themes\";b:1;s:11:\"update_core\";b:1;s:10:\"list_users\";b:1;s:12:\"remove_users\";b:1;s:13:\"promote_users\";b:1;s:18:\"edit_theme_options\";b:1;s:13:\"delete_themes\";b:1;s:6:\"export\";b:1;s:20:\"wpseo_manage_options\";b:1;s:13:\"administrator\";b:1;}s:6:\"filter\";N;s:16:\"\0WP_User\0site_id\";i:1;}s:5:\"nonce\";N;s:8:\"priority\";d:0.5;s:9:\"data_json\";a:1:{s:13:\"dismiss_value\";s:6:\"15.2.1\";}s:13:\"dismissal_key\";s:20:\"wpseo-plugin-updated\";s:12:\"capabilities\";a:1:{i:0;s:20:\"wpseo_manage_options\";}s:16:\"capability_check\";s:3:\"all\";s:14:\"yoast_branding\";b:0;}}}'),(25,1,'managenav-menuscolumnshidden','a:5:{i:0;s:11:\"link-target\";i:1;s:11:\"css-classes\";i:2;s:3:\"xfn\";i:3;s:11:\"description\";i:4;s:15:\"title-attribute\";}'),(26,1,'metaboxhidden_nav-menus','a:12:{i:0;s:18:\"add-post-type-team\";i:1;s:17:\"add-post-type-faq\";i:2;s:23:\"add-post-type-portfolio\";i:3;s:21:\"add-post-type-gallery\";i:4;s:12:\"add-post_tag\";i:5;s:15:\"add-post_format\";i:6;s:18:\"add-faq_categories\";i:7;s:26:\"add-testimonial_categories\";i:8;s:21:\"add-client_categories\";i:9;s:19:\"add-team_categories\";i:10;s:24:\"add-portfolio_categories\";i:11;s:22:\"add-gallery_categories\";}'),(29,1,'_yoast_wpseo_profile_updated','1605627944'),(32,1,'wp_user-settings','libraryContent=browse'),(31,1,'nav_menu_recently_edited','7'),(33,1,'wp_user-settings-time','1605900596'),(34,1,'closedpostboxes_toplevel_page_theme-general-settings','a:0:{}'),(35,1,'metaboxhidden_toplevel_page_theme-general-settings','a:0:{}');

--
-- View structure for view `allocations_view`
--

DROP VIEW IF EXISTS `allocations_view`;
CREATE VIEW `allocations_view` AS
select `a`.`id`            AS `id`,
       `a`.`start_date`    AS `start_date`,
       `a`.`end_date`      AS `end_date`,
       `a`.`amount`        AS `amount`,
       `a`.`notes`         AS `notes`,
       `a`.`date_created`  AS `date_created`,
       `a`.`created_by`    AS `created_by`,
       `a`.`date_modified` AS `date_modified`,
       `a`.`modified_by`   AS `modified_by`,
       sum(`ca`.`amount`)  AS `amount_allocated`
from (`allocations` `a`
         left join `companies_allocations` `ca` on ((`a`.`id` = `ca`.`allocation_id`)))
group by `a`.`id`;

--
-- View structure for view `companies_allocations_view`
--

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`,
       sum((case when (`s`.`product` = 'soybean') then `s`.`weight` else 0 end)) AS `soybeans_weight_shipped`
from (`companies_allocations` `ca`
         left join `shipments` `s` on ((`ca`.`id` = `s`.`company_allocation_id`)))
group by `ca`.`id`;

-- -----------------------------------------------------
-- Table `customer_profile`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `customer_profile` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(256) NULL,
  `country` INT NULL,
  `emails` VARCHAR(1024) NULL,
  `company_id` INT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `customer_certificates`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `customer_certificates` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `shipment_id` INT NULL,
   `company_id` INT NULL,
   `department_id` INT NULL,
   `company_name` VARCHAR(100) NULL,
   `product` ENUM('soybean', 'soybean_meal', 'soybean_oil', 'soybean_hull', 'soy_isolate', 'soy_concentrate', 'soy_flour', 'lecithin', 'soy_fiber') NULL,
   `input_weight` DECIMAL(15,3) NULL,
   `weight` DECIMAL(15,3) NULL,
   `vessel` VARCHAR(30) NULL,
   `customer_profile` INT NULL,
   `contract_ref` VARCHAR(100) NULL,
   `ship_date` DATE NULL,
   `notes` VARCHAR(1024) NULL,
   `ussec_note` VARCHAR(1024) NULL,
   `approved_signatory` INT NULL,
   `cert_code` VARCHAR(20) NULL,
   `cert_pin` VARCHAR(4) NULL,
   `status` ENUM('pending', 'confirmed', 'rejected') NULL,
   `date_created` DATETIME NULL,
   `created_by` INT NULL,
   `date_modified` DATETIME NULL,
   `modified_by` INT NULL,
   PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `customer_regions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `customer_regions` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `region` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `customer_country`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `customer_country` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `country` VARCHAR(45) NULL,
    `region_id` INT NULL,
    PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `shipment_pending_amounts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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;