-- DWH TABLES START
CREATE TABLE IF NOT EXISTS `dim_shipping_method` (
  `pk_dim_shipping_method` INT NOT NULL,
  `shipping_method` VARCHAR(255) NOT NULL,
  `carrier_code` VARCHAR(255) NOT NULL,
  `method_code` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_shipping_method`),
  UNIQUE (`method_code`, `description`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_shipping_method` (
  `pk_dim_shipping_method` INT NOT NULL,
  `fk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_shipping_method`, `dataset_id`),
  INDEX (`fk_fact_order`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_salesrule` (
  `pk_dim_salesrule` INT NOT NULL,
  `rule_id` INT NOT NULL,
  `rule_name` VARCHAR(255) NOT NULL,
  `from_date` VARCHAR(10) NOT NULL,
  `to_date` VARCHAR(10) NOT NULL,
  `promotion_type` VARCHAR(255) NOT NULL,
  `action` VARCHAR(255) NOT NULL,
  `free_shipping` VARCHAR(255) NOT NULL,
  `is_shipping_discount` VARCHAR(18) NOT NULL,
  `discount_fixed` decimal(20,4) NOT NULL,
  `discount_percent` DECIMAL(12,6) NOT NULL,
  `discount_qty` INT NOT NULL,
  `discount_step` INT NOT NULL,
  `final_rule` VARCHAR(10) NOT NULL,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_salesrule`),
  UNIQUE (`rule_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_salesrule` (
  `pk_dim_salesrule` INT NOT NULL,
  `fk_fact_order_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_salesrule`, `dataset_id`),
  INDEX (`fk_fact_order_item`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `map_salesrule` (
  `pk_map_salesrule` INT NOT NULL AUTO_INCREMENT,
  `fk_dim_salesrule` INT NOT NULL,
  `order_item_id` INT NOT NULL,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_map_salesrule`),
  INDEX USING BTREE (`fk_dim_salesrule`, `order_item_id`),
  INDEX (`order_item_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_map_salesrule` (
  `pk_map_salesrule` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_map_salesrule`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_product` (
  `pk_dim_product` INT NOT NULL,
  `product_id` INT NOT NULL DEFAULT 0,
  `product_name` VARCHAR(255) NOT NULL DEFAULT '',
  `attribute_set_id` INT NOT NULL DEFAULT 0,
  `attribute_set_name` VARCHAR(255) NOT NULL DEFAULT '',
  `product_type` VARCHAR(255) NOT NULL DEFAULT '',
  `sku` VARCHAR(255) NOT NULL DEFAULT '',
  `product_date` VARCHAR(10) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_product`),
  UNIQUE (`product_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_product` (
  `pk_dim_product` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_product`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_order_item` (
  `pk_fact_order_item` INT NOT NULL AUTO_INCREMENT,
  `has_parent` VARCHAR(1) NOT NULL DEFAULT '',
  `order_id` INT NOT NULL DEFAULT 0,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `fk_dim_product` INT NOT NULL DEFAULT 0,
  `parent_item_id` INT NOT NULL DEFAULT -1,
  `ordered_product_type` varchar(255) NOT NULL DEFAULT '' COMMENT 'Product Type',
  `row_total` decimal(20,4) NOT NULL DEFAULT 0,
  `discount_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `weight` decimal(20,4) NOT NULL DEFAULT 0,
  `price` decimal(20,4) NOT NULL DEFAULT 0,
  `row_total_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `tax_before_discount` decimal(20,4) NOT NULL DEFAULT 0,
  `row_weight` decimal(20,4) NOT NULL DEFAULT 0,
  `price_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `hidden_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `original_price` decimal(20,4) NOT NULL DEFAULT 0,
  `discount_percent` decimal(12,6) NOT NULL DEFAULT 0,
  `cost` decimal(20,4) NOT NULL DEFAULT 0,
  `qty_ordered` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_order_item`),
  UNIQUE (`order_item_id`),
  INDEX USING BTREE (`order_id`),
  INDEX USING BTREE (`fk_dim_product`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_order_item` (
  `pk_fact_order_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_order_item`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_order_item_parent` (
  `pk_fact_order_item_parent` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL DEFAULT 0,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `fk_dim_complex_product` INT NOT NULL DEFAULT 0,
  `weight` decimal(20,4) NOT NULL DEFAULT 0,
  `price` decimal(20,4) NOT NULL DEFAULT 0,
  `price_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `row_weight` decimal(20,4) NOT NULL DEFAULT 0,
  `qty_ordered` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_order_item_parent`),
  INDEX USING BTREE (`order_id`),
  UNIQUE (`order_item_id`),
  INDEX USING BTREE (`fk_dim_complex_product`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_order_item_parent` (
  `pk_fact_order_item_parent` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_order_item_parent`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_shipment_item` (
  `pk_fact_shipment_item` INT NOT NULL AUTO_INCREMENT,
  `shipment_item_id` INT NOT NULL DEFAULT 0,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `shipping_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `quantity` decimal(20,4) NOT NULL DEFAULT 0,
  `weight` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_shipment_item`),
  UNIQUE (`shipment_item_id`),
  INDEX USING BTREE (`order_item_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_shipment_item` (
  `pk_fact_shipment_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_shipment_item`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_shipment_item_parent` (
  `pk_fact_shipment_item_parent` INT NOT NULL AUTO_INCREMENT,
  `shipment_item_id` INT NOT NULL DEFAULT 0,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `quantity` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_shipment_item_parent`),
  UNIQUE (`shipment_item_id`),
  INDEX USING BTREE (`order_item_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_shipment_item_parent` (
  `pk_fact_shipment_item_parent` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_shipment_item_parent`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_invoice_item` (
  `pk_fact_invoice_item` INT NOT NULL AUTO_INCREMENT,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `invoice_item_id` INT NOT NULL DEFAULT 0,
  `invoice_id` INT NOT NULL DEFAULT 0,
  `price` decimal(20,4) NOT NULL DEFAULT 0,
  `row_total` decimal(20,4) NOT NULL DEFAULT 0,
  `discount_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `price_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `cost` decimal(20,4) NOT NULL DEFAULT 0,
  `row_total_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `hidden_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `quantity` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_invoice_item`),
  UNIQUE (`invoice_item_id`),
  INDEX USING BTREE (`order_item_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_invoice_item` (
  `pk_fact_invoice_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_invoice_item`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_invoice_item_parent` (
  `pk_fact_invoice_item_parent` INT NOT NULL AUTO_INCREMENT,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `invoice_item_id` INT NOT NULL DEFAULT 0,
  `invoice_id` INT NOT NULL DEFAULT 0,
  `quantity` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_invoice_item_parent`),
  UNIQUE (`invoice_item_id`),
  INDEX USING BTREE (`order_item_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_invoice_item_parent` (
  `pk_fact_invoice_item_parent` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_invoice_item_parent`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_order_cancel_item` (
  `pk_fact_order_cancel_item` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL DEFAULT 0,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `qty_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `updated_at_datetime` BIGINT(8) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_order_cancel_item`),
  INDEX USING BTREE (`order_item_id`),
  INDEX USING BTREE (`order_id`),
  INDEX USING HASH (`order_item_id`, `updated_at_datetime`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_order_cancel_item` (
  `pk_fact_order_cancel_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_order_cancel_item`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_order_cancel_item_parent` (
  `pk_fact_order_cancel_item_parent` INT NOT NULL AUTO_INCREMENT,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `qty_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `updated_at_datetime` BIGINT(8) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_order_cancel_item_parent`),
  INDEX USING BTREE (`order_item_id`),
  INDEX USING HASH (`order_item_id`, `updated_at_datetime`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_order_cancel_item_parent` (
  `pk_fact_order_cancel_item_parent` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_order_cancel_item_parent`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_customer_mini` (
  `pk_dim_customer_mini` INT NOT NULL,
  `group_id` INT NOT NULL DEFAULT 0,
  `group_code` VARCHAR(255) NOT NULL DEFAULT '',
  `gender_code` TINYINT NOT NULL DEFAULT 0,
  `gender_name` VARCHAR(255) NOT NULL DEFAULT '',
  `age` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_customer_mini`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_customer_mini` (
  `pk_dim_customer_mini` INT NOT NULL,
  `fk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_customer_mini`, `dataset_id`),
  INDEX (`fk_fact_order`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_customer` (
  `pk_dim_customer` INT NOT NULL,
  `customer_id` INT NOT NULL DEFAULT 0,
  `website_id` int signed NOT NULL DEFAULT 0,
  `store_id` int signed NOT NULL DEFAULT 0,
  `email` varchar(500) NOT NULL DEFAULT '',
  `email_hash` VARCHAR(40) NOT NULL DEFAULT '',
  `is_active` smallint NOT NULL DEFAULT 0,
  `created_in_store` varchar(255) NOT NULL DEFAULT '',
  `prefix` varchar(255) NOT NULL DEFAULT '',
  `first_name` varchar(255) NOT NULL DEFAULT '',
  `middle_name` varchar(255) NOT NULL DEFAULT '',
  `last_name` varchar(255) NOT NULL DEFAULT '',
  `suffix` varchar(255) NOT NULL DEFAULT '',
  `vat_number` varchar(255) NOT NULL DEFAULT '',
  `registration_time` VARCHAR(8) NOT NULL DEFAULT '',
  `registration_date` VARCHAR(10) NOT NULL DEFAULT '',
  `group_code` varchar(255) DEFAULT '' COMMENT 'Customer Group Code',
  `fk_dim_default_billing_address` INT NOT NULL,
  `fk_dim_default_shipping_address` INT NOT NULL,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_customer`),
  INDEX (`stg_inserted_at` ASC),
  INDEX (`fk_dim_default_billing_address`),
  INDEX (`fk_dim_default_shipping_address`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_customer` (
  `pk_dim_customer` INT NOT NULL,
  `fk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_customer`, `dataset_id`),
  INDEX (`fk_fact_order`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dim_default_billing_address` (
 `pk_dim_default_billing_address` INT NOT NULL AUTO_INCREMENT,
 `address_id` INT NOT NULL,
 `firstname` varchar(255) NOT NULL DEFAULT '',
 `lastname` varchar(255) NOT NULL DEFAULT '',
 `middlename` varchar(255) NOT NULL DEFAULT '',
 `prefix` varchar(255) NOT NULL DEFAULT '',
 `suffix` varchar(255) NOT NULL DEFAULT '',
 `company_name` varchar(255) NOT NULL DEFAULT '',
 `street` varchar(255) NOT NULL DEFAULT '',
 `email` varchar(255) NOT NULL DEFAULT '',
 `telephone` varchar(255) NOT NULL DEFAULT '',
 `vat_id` varchar(255) NOT NULL DEFAULT '',
 `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
 `inserted_at` TIMESTAMP NULL DEFAULT NULL,
 PRIMARY KEY (`pk_dim_default_billing_address`),
 INDEX (`address_id`),
 INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sn_dim_default_billing_address` (
  `pk_dim_default_billing_address` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_default_billing_address`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dim_default_shipping_address` (
  `pk_dim_default_shipping_address` INT NOT NULL AUTO_INCREMENT,
  `address_id` INT NOT NULL,
  `firstname` varchar(255) NOT NULL DEFAULT '',
  `lastname` varchar(255) NOT NULL DEFAULT '',
  `middlename` varchar(255) NOT NULL DEFAULT '',
  `prefix` varchar(255) NOT NULL DEFAULT '',
  `suffix` varchar(255) NOT NULL DEFAULT '',
  `company_name` varchar(255) NOT NULL DEFAULT '',
  `street` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `telephone` varchar(255) NOT NULL DEFAULT '',
  `vat_id` varchar(255) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_default_shipping_address`),
  INDEX (`address_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sn_dim_default_shipping_address` (
 `pk_dim_default_shipping_address` INT NOT NULL,
 `dataset_id` INT NOT NULL,
 `sequence_number` INT NOT NULL DEFAULT 0,
 `inserted_at` TIMESTAMP NULL DEFAULT NULL,
 PRIMARY KEY(`pk_dim_default_shipping_address`, `dataset_id`),
 INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dim_store` (
  `pk_dim_store` INT NOT NULL,
  `website_id` INT NOT NULL DEFAULT 0,
  `store_group_id` INT NOT NULL DEFAULT 0,
  `store_id` INT NOT NULL DEFAULT 0,
  `website_name` VARCHAR(255) NOT NULL DEFAULT '',
  `store_group_name` VARCHAR(255) NOT NULL DEFAULT '',
  `store_name` VARCHAR(255) NOT NULL DEFAULT '',
  `store_code` VARCHAR(255) NOT NULL DEFAULT '',
  `website_code` VARCHAR(255) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_store`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_store` (
  `pk_dim_store` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_store`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_order` (
  `pk_fact_order` INT NOT NULL AUTO_INCREMENT,
  `order_time` VARCHAR(8) NOT NULL DEFAULT '',
  `order_date` VARCHAR(10) NOT NULL DEFAULT '',
  `fk_order_date` INT NOT NULL DEFAULT 0,
  `fk_order_time` INT NOT NULL DEFAULT 0,
  `order_id` INT NOT NULL DEFAULT 0,
  `order_number` varchar(255) NOT NULL DEFAULT 'n/a',
  `fk_dim_customer_mini` INT NOT NULL DEFAULT 0,
  `customer_id` INT NOT NULL DEFAULT 0,
  `fk_customer_address` VARCHAR(40) NOT NULL DEFAULT '',
  `fk_dim_shipping_method` INT NOT NULL,
  `email` varchar(500) NOT NULL DEFAULT '',
  `email_hash` VARCHAR(40) NOT NULL DEFAULT '',
  `email_domain` varchar(255) NOT NULL DEFAULT '',
  `discount_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `hidden_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_hidden_tax_amnt` decimal(20,4) NOT NULL DEFAULT 0,
  `subtotal` decimal(20,4) NOT NULL DEFAULT 0,
  `total_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `base_to_global_rate` decimal(20,4) NOT NULL DEFAULT 0,
  `fk_dim_store` INT NOT NULL DEFAULT 0,
  `fk_billing_address` VARCHAR(40) DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_order`),
  UNIQUE (`order_id`),
  INDEX USING BTREE (`fk_order_date`),
  INDEX USING BTREE (`fk_order_time`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_order` (
  `pk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_order`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_credit_memo_item` (
  `pk_fact_credit_memo_item` INT NOT NULL AUTO_INCREMENT,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `creditmemo_id` INT NOT NULL DEFAULT 0,
  `creditmemo_item_id` INT NOT NULL DEFAULT 0,
  `row_total` decimal(20,4) NOT NULL DEFAULT 0,
  `price` decimal(20,4) NOT NULL DEFAULT 0,
  `price_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `discount_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `hidden_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `cost` decimal(20,4) NOT NULL DEFAULT 0,
  `quantity` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_credit_memo_item`),
  UNIQUE (`creditmemo_item_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_credit_memo_item` (
  `pk_fact_credit_memo_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_credit_memo_item`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_credit_memo_item_parent` (
  `pk_fact_credit_memo_item_parent` INT NOT NULL AUTO_INCREMENT,
  `order_item_id` INT NOT NULL DEFAULT 0,
  `creditmemo_id` INT NOT NULL DEFAULT 0,
  `creditmemo_item_id` INT NOT NULL DEFAULT 0,
  `quantity` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_credit_memo_item_parent`),
  UNIQUE (`creditmemo_item_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_credit_memo_item_parent` (
  `pk_fact_credit_memo_item_parent` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_credit_memo_item_parent`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_shipping_address` (
  `pk_dim_shipping_address` VARCHAR(40) NOT NULL,
  `country_id` VARCHAR(2) NOT NULL DEFAULT '',
  `country_iso2_code` VARCHAR(2) NOT NULL DEFAULT '',
  `country_iso3_code` VARCHAR(3) NOT NULL DEFAULT '',
  `region_name` VARCHAR(255) NOT NULL DEFAULT '',
  `region_code` VARCHAR(255) NOT NULL DEFAULT '',
  `region_default_name` VARCHAR(255) NOT NULL DEFAULT '',
  `city` VARCHAR(255) NOT NULL DEFAULT '',
  `postcode` VARCHAR(255) NOT NULL DEFAULT '',
  `region_country` VARCHAR(255) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_shipping_address`),
  INDEX USING HASH(`country_id`, `country_iso2_code`,  `country_iso3_code`,
          `region_name`(10), `region_code`(10),
                  `region_default_name`(10), `region_country`(10), `city`(10), `postcode`(10)),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_shipping_address` (
  `pk_dim_shipping_address` VARCHAR(40) NOT NULL,
  `fk_fact_shipment` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_shipping_address`, `dataset_id`),
  INDEX (`fk_fact_shipment`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dim_address_details` (
  `pk_dim_address_details` INT NOT NULL AUTO_INCREMENT,
  `address_id` INT NOT NULL,
  -- new fields
  `firstname` varchar(255) NOT NULL DEFAULT '',
  `lastname` varchar(255) NOT NULL DEFAULT '',
  `middlename` varchar(255) NOT NULL DEFAULT '',
  `prefix` varchar(255) NOT NULL DEFAULT '',
  `suffix` varchar(255) NOT NULL DEFAULT '',
  `company_name` varchar(255) NOT NULL DEFAULT '',
  `street` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `telephone` varchar(255) NOT NULL DEFAULT '',
  `vat_id` varchar(255) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_address_details`),
  INDEX (`address_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sn_dim_address_details` (
  `pk_dim_address_details` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_address_details`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `fact_shipment` (
  `pk_fact_shipment` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL DEFAULT 0,
  `shipment_id` INT NOT NULL DEFAULT 0,
  `shipment_number` varchar(255) NOT NULL DEFAULT 'n/a',
  `fk_shipping_address` VARCHAR(40) NOT NULL DEFAULT '',
  `shipment_time` VARCHAR(8) NOT NULL DEFAULT '',
  `shipment_date` VARCHAR(10) NOT NULL DEFAULT '',
  `total_weight` decimal(20,4) NOT NULL DEFAULT 0,
  `total_qty` decimal(20,4) NOT NULL DEFAULT 0,
  `fk_shipment_date` INT NOT NULL DEFAULT 0,
  `fk_dim_address_details` INT NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_shipment`),
  UNIQUE (`shipment_id`),
  INDEX USING BTREE (`fk_dim_address_details`),
  INDEX USING BTREE (`order_id`),
  INDEX (`stg_inserted_at` ASC)
  )
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_shipment` (
  `pk_fact_shipment` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_shipment`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_billing_address` (
  `pk_dim_billing_address` VARCHAR(40) NOT NULL,
  `country_id` VARCHAR(2) NOT NULL DEFAULT '',
  `country_iso2_code` VARCHAR(2) NOT NULL DEFAULT '',
  `country_iso3_code` VARCHAR(3) NOT NULL DEFAULT '',
  `region_name` VARCHAR(255) NOT NULL DEFAULT '',
  `region_code` VARCHAR(255) NOT NULL DEFAULT '',
  `region_default_name` VARCHAR(255) NOT NULL DEFAULT '',
  `city` VARCHAR(255) NOT NULL DEFAULT '',
  `postcode` VARCHAR(255) NOT NULL DEFAULT '',
  `region_country` VARCHAR(255) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_billing_address`),
  INDEX USING HASH(`country_id`, `country_iso2_code`,  `country_iso3_code`,
          `region_name`(10), `region_code`(10),
                  `region_default_name`(10), `region_country`(10), `city`(10), `postcode`(10)),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_billing_address` (
  `pk_dim_billing_address` VARCHAR(40) NOT NULL,
  `fk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_billing_address`, `dataset_id`),
  INDEX (`fk_fact_order`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_invoice` (
  `pk_fact_invoice` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL DEFAULT 0,
  `invoice_id` INT NOT NULL DEFAULT 0,
  `invoice_number` varchar(255) NOT NULL DEFAULT 'n/a',
  `grand_total` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `discount_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `subtotal_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `subtotal` decimal(20,4) NOT NULL DEFAULT 0,
  `fk_invoice_date` INT NOT NULL DEFAULT 0,
  `invoice_time` VARCHAR(8) NOT NULL DEFAULT '',
  `invoice_date` VARCHAR(10) NOT NULL DEFAULT '',
  `hidden_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_hidden_tax_amnt` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `fk_billing_address` VARCHAR(40) NOT NULL DEFAULT '',
  `tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `base_to_global_rate` decimal(20,4) NOT NULL DEFAULT 0,
  `fk_dim_address_details` INT NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_invoice`),
  UNIQUE (`invoice_id`),
  INDEX USING BTREE (`fk_dim_address_details`),
  INDEX USING BTREE (`order_id`),
  INDEX (`stg_inserted_at` ASC)
  )
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_invoice` (
  `pk_fact_invoice` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_invoice`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `fact_payment` (
  `pk_fact_payment` int NOT NULL AUTO_INCREMENT,
  `payment_id` int signed NOT NULL DEFAULT 0,
  `order_id` int signed NOT NULL,
  `fk_payment_method` varchar(100) NOT NULL DEFAULT '',
  `shipping_captured` decimal(20,4) NOT NULL DEFAULT 0,
  `amount_paid` decimal(20,4) NOT NULL DEFAULT 0,
  `amount_authorized` decimal(20,4) NOT NULL DEFAULT 0,
  `amount_paid_online` decimal(20,4) NOT NULL DEFAULT 0,
  `amount_refunded_online` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `amount_ordered` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_refunded` decimal(20,4) NOT NULL DEFAULT 0,
  `amount_refunded` decimal(20,4) NOT NULL DEFAULT 0,
  `amount_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_payment`),
  UNIQUE (`payment_id`),
  INDEX USING BTREE (`order_id`),
  INDEX (`stg_inserted_at` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_payment` (
  `pk_fact_payment` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_payment`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dim_payment_method` (
  `pk_dim_payment_method` varchar(100) NOT NULL DEFAULT '',
  `method_code` VARCHAR(255) NOT NULL DEFAULT '',
  `method_name` varchar(255) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_payment_method`),
  INDEX (`stg_inserted_at` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_payment_method` (
  `pk_dim_payment_method` VARCHAR(100) NOT NULL,
  `fk_fact_payment` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_payment_method`, `dataset_id`),
  INDEX (`fk_fact_payment`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_credit_memo` (
  `pk_fact_credit_memo` INT NOT NULL AUTO_INCREMENT,
  `creditmemo_id` INT NOT NULL DEFAULT 0,
  `creditmemo_number` varchar(255) NOT NULL DEFAULT 'n/a',
  `order_id` INT NOT NULL DEFAULT 0,
  `grand_total` decimal(20,4) NOT NULL DEFAULT 0,
  `subtotal` decimal(20,4) NOT NULL DEFAULT 0,
  `subtotal_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `discount_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `adjustment` decimal(20,4) NOT NULL DEFAULT 0,
  `adjustment_positive` decimal(20,4) NOT NULL DEFAULT 0,
  `adjustment_negative` decimal(20,4) NOT NULL DEFAULT 0,
  `hidden_tax_amount` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_hidden_tax_amnt` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_incl_tax` decimal(20,4) NOT NULL DEFAULT 0,
  `base_to_global_rate` decimal(20,4) NOT NULL DEFAULT 0,
  `credit_memo_date` VARCHAR(10) NOT NULL DEFAULT '',
  `credit_memo_time` VARCHAR(8) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_credit_memo`),
  UNIQUE (`creditmemo_id`),
  INDEX USING BTREE (`order_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_credit_memo` (
  `pk_fact_credit_memo` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_credit_memo`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_order_cancel` (
  `pk_fact_order_cancel` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL DEFAULT 0,
  `discount_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `shipping_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `tax_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `subtotal_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `total_canceled` decimal(20,4) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_order_cancel`),
  UNIQUE (`order_id`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_order_cancel` (
  `pk_fact_order_cancel` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_order_cancel`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_category` (
  `pk_dim_category` INT NOT NULL,
  `category_id` INT NOT NULL DEFAULT 0,
  `category_name` VARCHAR(255) NOT NULL DEFAULT '',
  `root_category` VARCHAR(255) NOT NULL DEFAULT '',
  `sub_category_1` VARCHAR(255) NOT NULL DEFAULT '',
  `sub_category_2` VARCHAR(255) NOT NULL DEFAULT '',
  `sub_category_3` VARCHAR(255) NOT NULL DEFAULT '',
  `sub_category_4` VARCHAR(255) NOT NULL DEFAULT '',
  `root_category_id` INT NOT NULL DEFAULT 0,
  `sub_category_1_id` INT NOT NULL DEFAULT 0,
  `sub_category_2_id` INT NOT NULL DEFAULT 0,
  `sub_category_3_id` INT NOT NULL DEFAULT 0,
  `sub_category_4_id` INT NOT NULL DEFAULT 0,
  `parent_id` INT NOT NULL DEFAULT 0,
  `path` VARCHAR(255) NOT NULL DEFAULT '',
  `position` INT NOT NULL DEFAULT 0,
  `level` SMALLINT NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_category`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_category` (
  `pk_dim_category` INT NOT NULL,
  `fk_fact_order_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_category`, `dataset_id`),
  INDEX (`fk_fact_order_item`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `map_product_category` (
  `pk_map_product_category` INT NOT NULL AUTO_INCREMENT,
  `fk_dim_product` INT NOT NULL DEFAULT 0,
  `fk_dim_category` INT NOT NULL DEFAULT 0,
  `depth_from_parent` VARCHAR(255) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_map_product_category`),
  UNIQUE (`fk_dim_product`, `fk_dim_category`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_map_product_category` (
  `pk_map_product_category` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_map_product_category`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_attribute_option` (
  `pk_dim_attribute_option` VARCHAR(30) NOT NULL,
  `attribute_id` INT NOT NULL DEFAULT 0,
  `attribute_code` VARCHAR(255) NOT NULL DEFAULT '',
  `attribute_name` VARCHAR(255) NOT NULL DEFAULT '',
  `attribute_group_id` INT NOT NULL DEFAULT 0,
  `attribute_group_name` VARCHAR(255) NOT NULL DEFAULT '',
  `attribute_option_label` VARCHAR(255) NOT NULL DEFAULT '',
  `attribute_option_value` BIGINT NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_attribute_option`),
  INDEX USING HASH (`attribute_id`, `attribute_group_id`, `attribute_option_value`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_attribute_option` (
  `pk_dim_attribute_option` VARCHAR(30) NOT NULL,
  `fk_fact_order_item` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_attribute_option`, `dataset_id`),
  INDEX (`fk_fact_order_item`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `map_product_attribute` (
  `pk_map_product_attribute` INT NOT NULL AUTO_INCREMENT,
  `fk_dim_attribute_option` VARCHAR(30) NOT NULL DEFAULT 0,
  `fk_dim_product` INT NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_map_product_attribute`),
  INDEX USING HASH (`fk_dim_product`, `fk_dim_attribute_option`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_map_product_attribute` (
  `pk_map_product_attribute` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_map_product_attribute`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_order_date` (
  `pk_dim_order_date` INT NOT NULL,
  `full_date` varchar(10) NOT NULL,
  `year` SMALLINT NOT NULL,
  `quarter` TINYINT NOT NULL,
  `month` TINYINT NOT NULL,
  `month_name` VARCHAR(45) NOT NULL,
  `week` TINYINT NOT NULL,
  `day_of_year` SMALLINT NOT NULL,
  `day_of_week_sunday_first` TINYINT NOT NULL,
  `day_of_week_monday_first` TINYINT NOT NULL,
  `day_of_week` TINYINT NOT NULL,
  `day_of_month` TINYINT NOT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_order_date`))
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_order_date` (
  `pk_dim_order_date` INT NOT NULL,
  `fk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_order_date`, `dataset_id`),
  INDEX (`fk_fact_order`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_order_time` (
  `pk_dim_order_time` INT NOT NULL,
  `hour` TINYINT NOT NULL,
  `minute` SMALLINT NOT NULL,
  `second` TINYINT NOT NULL,
  `second_of_day` BIGINT NOT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_order_time`))
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_order_time` (
  `pk_dim_order_time` INT NOT NULL,
  `fk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_order_time`, `dataset_id`),
  INDEX (`fk_fact_order`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dim_invoice_date` (
    `pk_dim_invoice_date` INT NOT NULL,
    `full_date` char(10) NOT NULL,
    `year` SMALLINT NOT NULL,
    `quarter` TINYINT NOT NULL,
    `month` TINYINT NOT NULL,
    `month_name` VARCHAR(45) NOT NULL,
    `week` TINYINT NOT NULL,
    `day_of_year` SMALLINT NOT NULL,
    `day_of_week_sunday_first` TINYINT NOT NULL,
    `day_of_week_monday_first` TINYINT NOT NULL,
    `day_of_month` TINYINT NOT NULL,
    `inserted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`pk_dim_invoice_date`))
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sn_dim_invoice_date` (
    `pk_dim_invoice_date` INT NOT NULL,
    `fk_fact_invoice` INT NOT NULL,
    `dataset_id` INT NOT NULL,
    `fk_invoices_in_dataset` INT NOT NULL,
    `sequence_number` INT NOT NULL DEFAULT 0,
    `inserted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY(`pk_dim_invoice_date`, `dataset_id`),
    INDEX (`fk_fact_invoice`),
    INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_age` (
  `pk_dim_age` INT NOT NULL DEFAULT 0,
  `age` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_age`))
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_age` (
  `pk_dim_age` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_age`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `tmp_dim_category_by_column` (
  `pk_tmp_dim_category_by_column` INT NOT NULL AUTO_INCREMENT,
  `product_id` INT NOT NULL DEFAULT 0,
  `category_id` INT NOT NULL DEFAULT 0,
  `parent_category1` INT NOT NULL DEFAULT 0,
  `parent_category2` INT NOT NULL DEFAULT 0,
  `parent_category3` INT NOT NULL DEFAULT 0,
  `parent_category4` INT NOT NULL DEFAULT 0,
  `parent_category5` INT NOT NULL DEFAULT 0,
  `parent_category6` INT NOT NULL DEFAULT 0,
  `parent_category7` INT NOT NULL DEFAULT 0,
  `parent_category8` INT NOT NULL DEFAULT 0,
  `parent_category9` INT NOT NULL DEFAULT 0,
  `parent_category10` INT NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_tmp_dim_category_by_column`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fact_metadata` (
  `pk_fact_metadata` INT NOT NULL AUTO_INCREMENT,
  `update_time` VARCHAR(19) NOT NULL, -- format dd/mm/yyyy hh:mm:ss
  `export_time` VARCHAR(19) NOT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_metadata`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_fact_metadata` (
  `pk_fact_metadata` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_metadata`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `map_order_state` (
  `pk_map_order_state` INT NOT NULL AUTO_INCREMENT,
  `state_number` INT NOT NULL,
  `order_id` INT NOT NULL,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_map_order_state`),
  INDEX `order_id` USING BTREE (`order_id` ASC),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_map_order_state` (
  `pk_map_order_state` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_map_order_state`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `dim_order_state` (
  `pk_dim_order_state` INT NOT NULL AUTO_INCREMENT,
  `state_name` VARCHAR(32) NOT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_order_state`),
  INDEX `state_name` USING BTREE (`state_name` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_order_state` (
  `pk_dim_order_state` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_order_state`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dim_tax_rate` (
  `pk_dim_tax_rate` INT NOT NULL,
  `tax_rate_name` VARCHAR(255) NOT NULL,
  `tax_rate_code` VARCHAR(255) NOT NULL,
  `percent` decimal(12,6) NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_dim_tax_rate`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_dim_tax_rate` (
  `pk_dim_tax_rate` INT NOT NULL,
  `fk_fact_order` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_dim_tax_rate`, `dataset_id`),
  INDEX (`fk_fact_order`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `map_order_tax_rate` (
  `pk_map_order_tax_rate` INT NOT NULL,
  `fk_dim_tax_rate` INT NOT NULL,
  `order_id` INT NOT NULL,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_map_order_tax_rate`),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_map_order_tax_rate` (
  `pk_map_order_tax_rate` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_map_order_tax_rate`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `map_order_item_tax_rate` (
  `pk_map_order_item_tax_rate` INT NOT NULL,
  `fk_dim_tax_rate` INT NOT NULL,
  `order_item_id` INT NOT NULL,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_map_order_item_tax_rate`),
  INDEX `order_item_id` USING BTREE (`order_item_id` ASC),
  INDEX (`stg_inserted_at` ASC)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `sn_map_order_item_tax_rate` (
  `pk_map_order_item_tax_rate` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_map_order_item_tax_rate`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

-- TABLE FOR DATASETS
CREATE TABLE IF NOT EXISTS `bim_dwh_orders_in_dataset` (
  `pk_orders_in_dataset` int signed NOT NULL AUTO_INCREMENT,
  `order_id` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_orders_in_dataset`),
  UNIQUE (`order_id`, `dataset_id`)
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `fact_order_status_updates` (
  `pk_fact_order_status_updates` int signed NOT NULL AUTO_INCREMENT,
  `order_status_change_id` INT signed NOT NULL,
  `order_id` int signed NOT NULL,
  `order_status` varchar(32),
  `rank` INT NOT NULL,
  `prev_status` varchar(32),
  `next_status` varchar(32),
  `processing_time` INT unsigned,
  `event_type` varchar (6) NOT NULL,
  `entity_type` varchar(32) NOT NULL,
  `status_update_time` VARCHAR(8) NOT NULL DEFAULT '',
  `status_update_date` VARCHAR(10) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_order_status_updates`),
  UNIQUE (`order_status_change_id`, `event_type`),
  INDEX (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `bim_tmp_order_status_history_rank` (
  `change_id` INT signed NOT NULL,
  `rank` INT NOT NULL,
  `order_id` INT NOT NULL,
  `status` varchar(32),
  `entity_type` varchar(32) NOT NULL,
  `created_at_datetime` BIGINT NOT NULL DEFAULT 0,
  `stg_inserted_at` TIMESTAMP NULL DEFAULT NULL,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`change_id`),
  INDEX USING BTREE (`order_id`, `rank`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `fact_comment` (
  `pk_fact_comment` int signed NOT NULL AUTO_INCREMENT,
  `comment_id` INT signed NOT NULL DEFAULT '0',
  `order_id` INT signed NOT NULL DEFAULT '0',
  `is_customer_notified` TINYINT NOT NULL DEFAULT 0,
  `order_comment` varchar(255) NOT NULL,
  `comment_time` VARCHAR(8) NOT NULL DEFAULT '',
  `comment_date` VARCHAR(10) NOT NULL DEFAULT '',
  `stg_inserted_at` TIMESTAMP,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`pk_fact_comment`),
  UNIQUE (`comment_id`),
  INDEX (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sn_fact_order_status_updates` (
  `pk_fact_order_status_updates` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_order_status_updates`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sn_fact_comment` (
  `pk_fact_comment` INT NOT NULL,
  `dataset_id` INT NOT NULL,
  `fk_orders_in_dataset` INT NOT NULL,
  `sequence_number` INT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY(`pk_fact_comment`, `dataset_id`),
  INDEX USING BTREE (`dataset_id`, `sequence_number`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `bim_tmp_product_attribute_values_latest` (
  `product_id` INT NOT NULL DEFAULT 0,
  `attribute_id` INT NOT NULL DEFAULT 0,
  `attribute_value` BIGINT NOT NULL DEFAULT 0,
  `updated_at_datetime` BIGINT NOT NULL DEFAULT 0,
  `inserted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`, `attribute_id`),
  INDEX (`inserted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;