-- MySQL Workbench Forward Engineering

        'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema agungmotor
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `acl_roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `acl_roles`
(
    `id`          BIGINT UNSIGNED NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `acl_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `acl_groups`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(255)    NOT NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `acl_group_roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `acl_group_roles`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `acl_group_id` BIGINT UNSIGNED NOT NULL,
    `acl_role_id`  BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_acl_group_roles_acl_roles_idx` (`acl_role_id` ASC),
    INDEX `fk_acl_group_roles_acl_groups1_idx` (`acl_group_id` ASC),
    CONSTRAINT `fk_acl_group_roles_acl_roles`
        FOREIGN KEY (`acl_role_id`)
            REFERENCES `acl_roles` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_acl_group_roles_acl_groups1`
        FOREIGN KEY (`acl_group_id`)
            REFERENCES `acl_groups` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `employees`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `title`       VARCHAR(255)    NOT NULL,
    `email`       VARCHAR(255)    NOT NULL,
    `password`    VARCHAR(255)    NOT NULL,
    `session`     JSON            NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC),
    UNIQUE INDEX `email_UNIQUE` (`email` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `employee_acl_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `employee_acl_groups`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id`  BIGINT UNSIGNED NOT NULL,
    `acl_group_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_employee_acl_groups_employees1_idx` (`employee_id` ASC),
    INDEX `fk_employee_acl_groups_acl_groups1_idx` (`acl_group_id` ASC),
    CONSTRAINT `fk_employee_acl_groups_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_employee_acl_groups_acl_groups1`
        FOREIGN KEY (`acl_group_id`)
            REFERENCES `acl_groups` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `warehouses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouses`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `address`     VARCHAR(255)    NOT NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `warehouse_employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouse_employees`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id` BIGINT UNSIGNED NOT NULL,
    `employee_id`  BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_warehouse_employees_warehouses1_idx` (`warehouse_id` ASC),
    INDEX `fk_warehouse_employees_employees1_idx` (`employee_id` ASC),
    CONSTRAINT `fk_warehouse_employees_warehouses1`
        FOREIGN KEY (`warehouse_id`)
            REFERENCES `warehouses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_warehouse_employees_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `customers`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id` BIGINT UNSIGNED NOT NULL,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `address1`    VARCHAR(255)    NOT NULL,
    `address2`    VARCHAR(255)    NULL,
    `city`        VARCHAR(255)    NOT NULL,
    `state`       VARCHAR(255)    NULL,
    `country`     VARCHAR(255)    NULL,
    `phone1`      VARCHAR(255)    NULL,
    `phone2`      VARCHAR(255)    NULL,
    `discount`    FLOAT UNSIGNED  NULL DEFAULT 0,
    `payment`     INT UNSIGNED    NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC),
    INDEX `fk_customers_employees1_idx` (`employee_id` ASC),
    CONSTRAINT `fk_customers_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `audits`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `audits`
(
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id`   BIGINT UNSIGNED NOT NULL,
    `timestamp`     TIMESTAMP       NOT NULL DEFAULT NOW(),
    `action`        VARCHAR(255)    NOT NULL,
    `object`        VARCHAR(255)    NOT NULL,
    `object_id`     BIGINT UNSIGNED NOT NULL,
    `object_before` JSON            NULL,
    `object_after`  JSON            NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_audits_employees1_idx` (`employee_id` ASC),
    CONSTRAINT `fk_audits_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `items`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `part`        VARCHAR(255)    NULL,
    `unit`        VARCHAR(255)    NOT NULL,
    `package`     INT UNSIGNED    NOT NULL,
    `price`       FLOAT UNSIGNED  NOT NULL,
    `weight`      FLOAT           NOT NULL,
    `barcode`     VARCHAR(255)    NULL,
    `photo`       LONGBLOB        NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `warehouse_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouse_items`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id` BIGINT UNSIGNED NOT NULL,
    `item_id`      BIGINT UNSIGNED NOT NULL,
    `location`     VARCHAR(255)    NULL,
    `stock`        INT UNSIGNED    NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_warehouse_items_warehouses1_idx` (`warehouse_id` ASC),
    INDEX `fk_warehouse_items_items1_idx` (`item_id` ASC),
    CONSTRAINT `fk_warehouse_items_warehouses1`
        FOREIGN KEY (`warehouse_id`)
            REFERENCES `warehouses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_warehouse_items_items1`
        FOREIGN KEY (`item_id`)
            REFERENCES `items` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mappings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappings`
(
    `id`     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `object` VARCHAR(255)    NOT NULL,
    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mapping_fields`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mapping_fields`
(
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `mapping_id` BIGINT UNSIGNED NOT NULL,
    `database`   VARCHAR(255)    NOT NULL,
    `excel`      VARCHAR(255)    NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_import_mapping_fields_import_mappings1_idx` (`mapping_id` ASC),
    CONSTRAINT `fk_import_mapping_fields_import_mappings1`
        FOREIGN KEY (`mapping_id`)
            REFERENCES `mappings` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `orders`
(
    `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id`        BIGINT UNSIGNED NOT NULL,
    `customer_id`         BIGINT UNSIGNED NOT NULL,
    `sales_id`            BIGINT UNSIGNED NOT NULL,
    `admin_id`            BIGINT UNSIGNED NULL,
    `carrier_id`          BIGINT UNSIGNED NULL,
    `checker_id`          BIGINT UNSIGNED NULL,
    `code`                VARCHAR(255)    NOT NULL,
    `new_customer`        TINYINT         NOT NULL DEFAULT 0,
    `taxable`             TINYINT         NOT NULL DEFAULT 0,
    `status`              VARCHAR(255)    NOT NULL,
    `date`                TIMESTAMP       NOT NULL,
    `total`               FLOAT           NOT NULL DEFAULT 0,
    `discount`            FLOAT UNSIGNED  NOT NULL DEFAULT 0,
    `final_total`         FLOAT UNSIGNED  NOT NULL,
    `sales_update_date`   TIMESTAMP       NULL,
    `admin_update_date`   TIMESTAMP       NULL,
    `carrier_update_date` TIMESTAMP       NULL,
    `checker_update_date` TIMESTAMP       NULL,
    `sales_memo`          TEXT            NULL,
    `admin_memo`          TEXT            NULL,
    `carrier_memo`        TEXT            NULL,
    `checker_memo`        TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC),
    INDEX `fk_orders_employees1_idx` (`sales_id` ASC),
    INDEX `fk_orders_employees2_idx` (`admin_id` ASC),
    INDEX `fk_orders_employees3_idx` (`checker_id` ASC),
    INDEX `fk_orders_employees4_idx` (`carrier_id` ASC),
    INDEX `fk_orders_warehouses1_idx` (`warehouse_id` ASC),
    INDEX `fk_orders_customers1_idx` (`customer_id` ASC),
    CONSTRAINT `fk_orders_employees1`
        FOREIGN KEY (`sales_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_employees2`
        FOREIGN KEY (`admin_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_employees3`
        FOREIGN KEY (`checker_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_employees4`
        FOREIGN KEY (`carrier_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_warehouses1`
        FOREIGN KEY (`warehouse_id`)
            REFERENCES `warehouses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_customers1`
        FOREIGN KEY (`customer_id`)
            REFERENCES `customers` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `order_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `order_details`
(
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id`          BIGINT UNSIGNED NOT NULL,
    `warehouse_item_id` BIGINT UNSIGNED NOT NULL,
    `price`             FLOAT UNSIGNED  NOT NULL,
    `quantity`          INT UNSIGNED    NOT NULL,
    `shipping_quantity` INT             NULL,
    `discount`          FLOAT UNSIGNED  NOT NULL,
    `final_price`       FLOAT UNSIGNED  NOT NULL,
    `subtotal`          FLOAT UNSIGNED  NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_order_detail_warehouse_items1_idx` (`warehouse_item_id` ASC),
    INDEX `fk_order_detail_orders1_idx` (`order_id` ASC),
    CONSTRAINT `fk_order_detail_warehouse_items1`
        FOREIGN KEY (`warehouse_item_id`)
            REFERENCES `warehouse_items` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_order_detail_orders1`
        FOREIGN KEY (`order_id`)
            REFERENCES `orders` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kolis`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kolis`
(
    `id`       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `number`   INT UNSIGNED    NOT NULL,
    `weight`   FLOAT UNSIGNED  NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_kolis_orders1_idx` (`order_id` ASC),
    CONSTRAINT `fk_kolis_orders1`
        FOREIGN KEY (`order_id`)
            REFERENCES `orders` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `koli_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `koli_items`
(
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `koli_id`         BIGINT UNSIGNED NOT NULL,
    `order_detail_id` BIGINT UNSIGNED NOT NULL,
    `quantity`        INT UNSIGNED    NOT NULL DEFAULT 0,
    `weight`          FLOAT UNSIGNED  NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_koli_details_kolis1_idx` (`koli_id` ASC),
    INDEX `fk_koli_details_order_details1_idx` (`order_detail_id` ASC),
    CONSTRAINT `fk_koli_details_kolis1`
        FOREIGN KEY (`koli_id`)
            REFERENCES `kolis` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_koli_details_order_details1`
        FOREIGN KEY (`order_detail_id`)
            REFERENCES `order_details` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- View `acl_group_roles_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `acl_group_roles_view` AS
SELECT agr.id,
       ag.id          AS `group_id`,
       ag.name        AS `group_name`,
       ar.id          AS `role_id`,
       ar.name        AS `role_name`,
       ar.description AS `role_description`
FROM acl_group_roles agr
         INNER JOIN acl_groups ag ON ag.id = agr.acl_group_id
         INNER JOIN acl_roles ar ON ar.id = agr.acl_role_id;

-- -----------------------------------------------------
-- View `employee_acl_groups_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `employee_acl_groups_view` AS
SELECT eag.*, e.code AS `employee_code`, e.name AS `employee_name`, ag.name AS `acl_group_name`
FROM employee_acl_groups eag
         INNER JOIN employees e ON e.id = eag.employee_id
         INNER JOIN acl_groups ag ON ag.id = eag.acl_group_id;

-- -----------------------------------------------------
-- View `audits_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `audits_view` AS
SELECT a.*, e.code AS `employee_code`, e.name AS `employee_name`
FROM audits a
         INNER JOIN employees e ON e.id = a.employee_id;

-- -----------------------------------------------------
-- View `warehouse_employees_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `warehouse_employees_view` AS
SELECT we.*,
       w.code AS `warehouse_code`,
       w.name AS `warehouse_name`,
       e.code AS `employee_code`,
       e.name as `employee_name`
FROM warehouse_employees we
         INNER JOIN warehouses w ON w.id = we.warehouse_id
         INNER JOIN employees e ON e.id = we.employee_id;

-- -----------------------------------------------------
-- View `warehouse_items_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `warehouse_items_view` AS
SELECT wi.*,
       w.code    AS `warehouse_code`,
       w.name    AS `warehouse_name`,
       i.code    AS `item_code`,
       i.name    AS `item_name`,
       i.part    AS `item_part`,
       i.unit    AS `item_unit`,
       i.package AS `item_package`,
       i.price   AS `item_price`,
       i.weight  AS `item_weight`,
       i.barcode AS `item_barcode`
FROM warehouse_items wi
         INNER JOIN warehouses w ON w.id = wi.warehouse_id
         INNER JOIN items i ON i.id = wi.item_id;

-- -----------------------------------------------------
-- View `customers_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `customers_view` AS
SELECT c.*, e.code AS `employee_code`, e.name AS `employee_name`
FROM customers c
         INNER JOIN employees e ON e.id = c.employee_id;

-- -----------------------------------------------------
-- View `orders_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `orders_view` AS
SELECT o.*,
       w.code        AS 'warehouse_code',
       w.name        AS 'warehouse_name',
       c.code        AS 'customer_code',
       c.name        AS 'customer_name',
       c.address1    AS 'customer_address1',
       c.address2    AS 'customer_address2',
       c.city        AS 'customer_city',
       c.state       AS 'customer_state',
       c.country     AS 'customer_country',
       c.phone1      AS 'customer_phone1',
       c.phone2      AS 'customer_phone2',
       c.discount    AS 'customer_discount',
       c.payment     AS 'customer_payment',
       c.description AS 'customer_description',
       es.code       AS 'sales_code',
       es.name       AS 'sales_name',
       ea.code       AS 'admin_code',
       ea.name       AS 'admin_name',
       eca.code      AS 'carrier_code',
       eca.name      AS 'carrier_name',
       ech.code      AS 'checker_code',
       ech.name      AS 'checker_name'
FROM orders o
         INNER JOIN warehouses w ON w.id = o.warehouse_id
         INNER JOIN customers c ON c.id = o.customer_id
         INNER JOIN employees es ON es.id = o.sales_id
         LEFT OUTER JOIN employees ea ON ea.id = o.admin_id
         LEFT OUTER JOIN employees eca ON eca.id = o.carrier_id
         LEFT OUTER JOIN employees ech ON ech.id = o.checker_id;

-- -----------------------------------------------------
-- View `order_details_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `order_details_view` AS
SELECT od.*,
       wi.location AS 'item_location',
       wi.stock    AS 'item_stock',
       i.code      AS 'item_code',
       i.name      AS 'item_name',
       i.part      AS 'item_part',
       i.unit      AS 'item_unit',
       i.package   AS 'item_package',
       i.price     AS 'item_price',
       i.weight    AS 'item_weight'
FROM order_details od
         INNER JOIN warehouse_items wi ON wi.id = od.warehouse_item_id
         INNER JOIN items i ON i.id = wi.item_id;

-- -----------------------------------------------------
-- View `koli_items_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `koli_items_view` AS
SELECT k.*, o.item_code, o.item_name
FROM koli_items k
         INNER JOIN order_details_view o ON o.id = k.order_detail_id;

-- MySQL Workbench Forward Engineering

        'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema agungmotor
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `acl_roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `acl_roles`
(
    `id`          BIGINT UNSIGNED NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `acl_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `acl_groups`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(255)    NOT NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `acl_group_roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `acl_group_roles`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `acl_group_id` BIGINT UNSIGNED NOT NULL,
    `acl_role_id`  BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_acl_group_roles_acl_roles_idx` (`acl_role_id` ASC),
    INDEX `fk_acl_group_roles_acl_groups1_idx` (`acl_group_id` ASC),
    CONSTRAINT `fk_acl_group_roles_acl_roles`
        FOREIGN KEY (`acl_role_id`)
            REFERENCES `acl_roles` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_acl_group_roles_acl_groups1`
        FOREIGN KEY (`acl_group_id`)
            REFERENCES `acl_groups` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `employees`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `title`       VARCHAR(255)    NOT NULL,
    `email`       VARCHAR(255)    NOT NULL,
    `password`    VARCHAR(255)    NOT NULL,
    `session`     JSON            NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC),
    UNIQUE INDEX `email_UNIQUE` (`email` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `employee_acl_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `employee_acl_groups`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id`  BIGINT UNSIGNED NOT NULL,
    `acl_group_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_employee_acl_groups_employees1_idx` (`employee_id` ASC),
    INDEX `fk_employee_acl_groups_acl_groups1_idx` (`acl_group_id` ASC),
    CONSTRAINT `fk_employee_acl_groups_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_employee_acl_groups_acl_groups1`
        FOREIGN KEY (`acl_group_id`)
            REFERENCES `acl_groups` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `warehouses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouses`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `address`     VARCHAR(255)    NOT NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `warehouse_employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouse_employees`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id` BIGINT UNSIGNED NOT NULL,
    `employee_id`  BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_warehouse_employees_warehouses1_idx` (`warehouse_id` ASC),
    INDEX `fk_warehouse_employees_employees1_idx` (`employee_id` ASC),
    CONSTRAINT `fk_warehouse_employees_warehouses1`
        FOREIGN KEY (`warehouse_id`)
            REFERENCES `warehouses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_warehouse_employees_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `customers`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id` BIGINT UNSIGNED NOT NULL,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `address1`    VARCHAR(255)    NOT NULL,
    `address2`    VARCHAR(255)    NULL,
    `city`        VARCHAR(255)    NOT NULL,
    `state`       VARCHAR(255)    NULL,
    `country`     VARCHAR(255)    NULL,
    `phone1`      VARCHAR(255)    NULL,
    `phone2`      VARCHAR(255)    NULL,
    `discount`    FLOAT UNSIGNED  NULL DEFAULT 0,
    `payment`     INT UNSIGNED    NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC),
    INDEX `fk_customers_employees1_idx` (`employee_id` ASC),
    CONSTRAINT `fk_customers_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `audits`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `audits`
(
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id`   BIGINT UNSIGNED NOT NULL,
    `timestamp`     TIMESTAMP       NOT NULL DEFAULT NOW(),
    `action`        VARCHAR(255)    NOT NULL,
    `object`        VARCHAR(255)    NOT NULL,
    `object_id`     BIGINT UNSIGNED NOT NULL,
    `object_before` JSON            NULL,
    `object_after`  JSON            NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_audits_employees1_idx` (`employee_id` ASC),
    CONSTRAINT `fk_audits_employees1`
        FOREIGN KEY (`employee_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `items`
(
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category`    VARCHAR(255)    NOT NULL,
    `code`        VARCHAR(255)    NOT NULL,
    `name`        VARCHAR(255)    NOT NULL,
    `part`        VARCHAR(255)    NULL,
    `unit`        VARCHAR(255)    NOT NULL,
    `package`     INT UNSIGNED    NOT NULL,
    `price`       FLOAT UNSIGNED  NOT NULL,
    `weight`      FLOAT           NOT NULL,
    `barcode`     VARCHAR(255)    NULL,
    `photo`       LONGBLOB        NULL,
    `description` TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `warehouse_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouse_items`
(
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id` BIGINT UNSIGNED NOT NULL,
    `item_id`      BIGINT UNSIGNED NOT NULL,
    `location`     VARCHAR(255)    NULL,
    `stock`        INT UNSIGNED    NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_warehouse_items_warehouses1_idx` (`warehouse_id` ASC),
    INDEX `fk_warehouse_items_items1_idx` (`item_id` ASC),
    CONSTRAINT `fk_warehouse_items_warehouses1`
        FOREIGN KEY (`warehouse_id`)
            REFERENCES `warehouses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_warehouse_items_items1`
        FOREIGN KEY (`item_id`)
            REFERENCES `items` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mappings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mappings`
(
    `id`     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `object` VARCHAR(255)    NOT NULL,
    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mapping_fields`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mapping_fields`
(
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `mapping_id` BIGINT UNSIGNED NOT NULL,
    `database`   VARCHAR(255)    NOT NULL,
    `excel`      VARCHAR(255)    NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_import_mapping_fields_import_mappings1_idx` (`mapping_id` ASC),
    CONSTRAINT `fk_import_mapping_fields_import_mappings1`
        FOREIGN KEY (`mapping_id`)
            REFERENCES `mappings` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `orders`
(
    `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id`        BIGINT UNSIGNED NOT NULL,
    `customer_id`         BIGINT UNSIGNED NOT NULL,
    `sales_id`            BIGINT UNSIGNED NOT NULL,
    `admin_id`            BIGINT UNSIGNED NULL,
    `carrier_id`          BIGINT UNSIGNED NULL,
    `checker_id`          BIGINT UNSIGNED NULL,
    `code`                VARCHAR(255)    NOT NULL,
    `new_customer`        TINYINT         NOT NULL DEFAULT 0,
    `taxable`             TINYINT         NOT NULL DEFAULT 0,
    `status`              VARCHAR(255)    NOT NULL,
    `date`                TIMESTAMP       NOT NULL,
    `total`               FLOAT           NOT NULL DEFAULT 0,
    `discount`            FLOAT UNSIGNED  NOT NULL DEFAULT 0,
    `final_total`         FLOAT UNSIGNED  NOT NULL,
    `sales_update_date`   TIMESTAMP       NULL,
    `admin_update_date`   TIMESTAMP       NULL,
    `carrier_update_date` TIMESTAMP       NULL,
    `checker_update_date` TIMESTAMP       NULL,
    `sales_memo`          TEXT            NULL,
    `admin_memo`          TEXT            NULL,
    `carrier_memo`        TEXT            NULL,
    `checker_memo`        TEXT            NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code` ASC),
    INDEX `fk_orders_employees1_idx` (`sales_id` ASC),
    INDEX `fk_orders_employees2_idx` (`admin_id` ASC),
    INDEX `fk_orders_employees3_idx` (`checker_id` ASC),
    INDEX `fk_orders_employees4_idx` (`carrier_id` ASC),
    INDEX `fk_orders_warehouses1_idx` (`warehouse_id` ASC),
    INDEX `fk_orders_customers1_idx` (`customer_id` ASC),
    CONSTRAINT `fk_orders_employees1`
        FOREIGN KEY (`sales_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_employees2`
        FOREIGN KEY (`admin_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_employees3`
        FOREIGN KEY (`checker_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_employees4`
        FOREIGN KEY (`carrier_id`)
            REFERENCES `employees` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_warehouses1`
        FOREIGN KEY (`warehouse_id`)
            REFERENCES `warehouses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_orders_customers1`
        FOREIGN KEY (`customer_id`)
            REFERENCES `customers` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `order_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `order_details`
(
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id`          BIGINT UNSIGNED NOT NULL,
    `warehouse_item_id` BIGINT UNSIGNED NOT NULL,
    `quantity`          INT UNSIGNED    NOT NULL,
    `shipping_quantity` INT             NULL,
    `discount`          FLOAT UNSIGNED  NOT NULL,
    `final_price`       FLOAT UNSIGNED  NOT NULL,
    `subtotal`          FLOAT UNSIGNED  NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_order_detail_warehouse_items1_idx` (`warehouse_item_id` ASC),
    INDEX `fk_order_detail_orders1_idx` (`order_id` ASC),
    CONSTRAINT `fk_order_detail_warehouse_items1`
        FOREIGN KEY (`warehouse_item_id`)
            REFERENCES `warehouse_items` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_order_detail_orders1`
        FOREIGN KEY (`order_id`)
            REFERENCES `orders` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kolis`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kolis`
(
    `id`       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `number`   INT UNSIGNED    NOT NULL,
    `weight`   FLOAT UNSIGNED  NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_kolis_orders1_idx` (`order_id` ASC),
    CONSTRAINT `fk_kolis_orders1`
        FOREIGN KEY (`order_id`)
            REFERENCES `orders` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `koli_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `koli_items`
(
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `koli_id`         BIGINT UNSIGNED NOT NULL,
    `order_detail_id` BIGINT UNSIGNED NOT NULL,
    `quantity`        INT UNSIGNED    NOT NULL DEFAULT 0,
    `weight`          FLOAT UNSIGNED  NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_koli_details_kolis1_idx` (`koli_id` ASC),
    INDEX `fk_koli_details_order_details1_idx` (`order_detail_id` ASC),
    CONSTRAINT `fk_koli_details_kolis1`
        FOREIGN KEY (`koli_id`)
            REFERENCES `kolis` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `fk_koli_details_order_details1`
        FOREIGN KEY (`order_detail_id`)
            REFERENCES `order_details` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `announcements`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `announcements`
(
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `content`         TEXT NOT NULL,
    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- View `acl_group_roles_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `acl_group_roles_view` AS
SELECT agr.id,
       ag.id          AS `group_id`,
       ag.name        AS `group_name`,
       ar.id          AS `role_id`,
       ar.name        AS `role_name`,
       ar.description AS `role_description`
FROM acl_group_roles agr
         INNER JOIN acl_groups ag ON ag.id = agr.acl_group_id
         INNER JOIN acl_roles ar ON ar.id = agr.acl_role_id;

-- -----------------------------------------------------
-- View `employee_acl_groups_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `employee_acl_groups_view` AS
SELECT eag.*, e.code AS `employee_code`, e.name AS `employee_name`, ag.name AS `acl_group_name`
FROM employee_acl_groups eag
         INNER JOIN employees e ON e.id = eag.employee_id
         INNER JOIN acl_groups ag ON ag.id = eag.acl_group_id;

-- -----------------------------------------------------
-- View `audits_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `audits_view` AS
SELECT a.*, e.code AS `employee_code`, e.name AS `employee_name`
FROM audits a
         INNER JOIN employees e ON e.id = a.employee_id;

-- -----------------------------------------------------
-- View `warehouse_employees_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `warehouse_employees_view` AS
SELECT we.*,
       w.code AS `warehouse_code`,
       w.name AS `warehouse_name`,
       e.code AS `employee_code`,
       e.name as `employee_name`
FROM warehouse_employees we
         INNER JOIN warehouses w ON w.id = we.warehouse_id
         INNER JOIN employees e ON e.id = we.employee_id;

-- -----------------------------------------------------
-- View `warehouse_items_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `warehouse_items_view` AS
SELECT wi.*,
       w.code    AS `warehouse_code`,
       w.name    AS `warehouse_name`,
       i.code    AS `item_code`,
       i.name    AS `item_name`,
       i.part    AS `item_part`,
       i.unit    AS `item_unit`,
       i.package AS `item_package`,
       i.price   AS `item_price`,
       i.weight  AS `item_weight`,
       i.barcode AS `item_barcode`
FROM warehouse_items wi
         INNER JOIN warehouses w ON w.id = wi.warehouse_id
         INNER JOIN items i ON i.id = wi.item_id;

-- -----------------------------------------------------
-- View `customers_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `customers_view` AS
SELECT c.*, e.code AS `employee_code`, e.name AS `employee_name`
FROM customers c
         INNER JOIN employees e ON e.id = c.employee_id;

-- -----------------------------------------------------
-- View `orders_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `orders_view` AS
SELECT o.*,
       w.code        AS 'warehouse_code',
       w.name        AS 'warehouse_name',
       c.code        AS 'customer_code',
       c.name        AS 'customer_name',
       c.address1    AS 'customer_address1',
       c.address2    AS 'customer_address2',
       c.city        AS 'customer_city',
       c.state       AS 'customer_state',
       c.country     AS 'customer_country',
       c.phone1      AS 'customer_phone1',
       c.phone2      AS 'customer_phone2',
       c.discount    AS 'customer_discount',
       c.payment     AS 'customer_payment',
       c.description AS 'customer_description',
       es.code       AS 'sales_code',
       es.name       AS 'sales_name',
       ea.code       AS 'admin_code',
       ea.name       AS 'admin_name',
       eca.code      AS 'carrier_code',
       eca.name      AS 'carrier_name',
       ech.code      AS 'checker_code',
       ech.name      AS 'checker_name'
FROM orders o
         INNER JOIN warehouses w ON w.id = o.warehouse_id
         INNER JOIN customers c ON c.id = o.customer_id
         INNER JOIN employees es ON es.id = o.sales_id
         LEFT OUTER JOIN employees ea ON ea.id = o.admin_id
         LEFT OUTER JOIN employees eca ON eca.id = o.carrier_id
         LEFT OUTER JOIN employees ech ON ech.id = o.checker_id;

-- -----------------------------------------------------
-- View `order_details_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `order_details_view` AS
SELECT od.*,
       wi.location AS 'item_location',
       wi.stock    AS 'item_stock',
       i.code      AS 'item_code',
       i.name      AS 'item_name',
       i.part      AS 'item_part',
       i.unit      AS 'item_unit',
       i.package   AS 'item_package',
       i.price     AS 'item_price',
       i.weight    AS 'item_weight'
FROM order_details od
         INNER JOIN warehouse_items wi ON wi.id = od.warehouse_item_id
         INNER JOIN items i ON i.id = wi.item_id;

-- -----------------------------------------------------
-- View `koli_items_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `koli_items_view` AS
SELECT k.*, o.item_code, o.item_name
FROM koli_items k
         INNER JOIN order_details_view o ON o.id = k.order_detail_id;

-- -----------------------------------------------------
-- View `empry_orders_view`
-- -----------------------------------------------------
CREATE OR REPLACE VIEW `empty_orders_view` AS
SELECT ov.code,
       ov.new_customer,
       ov.taxable,
       ov.status,
       ov.date,
       ov.total,
       ov.discount  AS 'order_discount',
       ov.final_total,
       ov.sales_update_date,
       ov.admin_update_date,
       ov.carrier_update_date,
       ov.checker_update_date,
       ov.sales_memo,
       ov.admin_memo,
       ov.carrier_memo,
       ov.checker_memo,
       ov.warehouse_code,
       ov.warehouse_name,
       ov.customer_code,
       ov.customer_name,
       ov.customer_address1,
       ov.customer_address2,
       ov.customer_city,
       ov.customer_state,
       ov.customer_country,
       ov.customer_phone1,
       ov.customer_phone2,
       ov.customer_discount,
       ov.customer_payment,
       ov.customer_description,
       ov.sales_code,
       ov.sales_name,
       ov.admin_code,
       ov.admin_name,
       ov.carrier_code,
       ov.carrier_name,
       ov.checker_code,
       ov.checker_name,
       odv.quantity,
       odv.shipping_quantity,
       odv.discount AS 'order_detail_discount',
       odv.final_price,
       odv.subtotal,
       odv.item_location,
       odv.item_stock,
       odv.item_code,
       odv.item_name,
       odv.item_part,
       odv.item_unit,
       odv.item_package,
       odv.item_price,
       odv.item_weight
FROM orders_view ov
         INNER JOIN order_details_view odv ON odv.order_id = ov.id
WHERE odv.quantity != odv.shipping_quantity;

