-- =============================================================================
-- XPERT SAP B1 - Migracion 016
-- Completa dos tablas que el motor operativo ya usa (Empaque y asignacion de
-- zonas por empresa) pero que no tenian su propio CREATE TABLE en el
-- historial de migraciones original -- portadas desde el esquema real en
-- produccion (mismo gap existia en el origen, XPERT3PL).
-- =============================================================================

CREATE TABLE IF NOT EXISTS `packing_orders` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `document_id`   INT UNSIGNED NOT NULL COMMENT '1:1 con outbound_documents',
    `pack_number`   VARCHAR(30)  NOT NULL,
    `warehouse_id`  INT UNSIGNED NOT NULL COMMENT 'Denormalizado desde outbound_documents.warehouse_id',
    `status`        ENUM('OPEN','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
    `notes`         TEXT NULL,
    `completed_at`  DATETIME NULL,
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by`    INT UNSIGNED NULL,
    UNIQUE KEY `uq_packing_docnum` (`pack_number`),
    UNIQUE KEY `uq_packing_document` (`document_id`),
    KEY `fk_packing_warehouse` (`warehouse_id`),
    CONSTRAINT `fk_packing_document` FOREIGN KEY (`document_id`) REFERENCES `outbound_documents`(`id`),
    CONSTRAINT `fk_packing_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='1 orden de packing por documento de egreso (no hay consolidacion multi-cliente en este proyecto)';

CREATE TABLE IF NOT EXISTS `packing_lines` (
    `id`                         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `packing_order_id`           INT UNSIGNED NOT NULL,
    `line_num`                   INT NOT NULL,
    `outbound_document_line_id`  INT UNSIGNED NOT NULL COMMENT 'De donde viene (copia solo lineas ya PICKED/SHORT con picked_qty>0)',
    `item_id`                    INT UNSIGNED NOT NULL,
    `batch_id`                   INT UNSIGNED NULL,
    `quantity`                   DECIMAL(15,4) NOT NULL COMMENT '= picked_qty de la linea de origen al momento de crear el packing',
    `packed_qty`                 DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom`                        VARCHAR(20)   NOT NULL DEFAULT 'UN',
    `status`                     ENUM('PENDING','PACKED') NOT NULL DEFAULT 'PENDING',
    `created_at`                 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`                 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_packingline` (`packing_order_id`, `line_num`),
    KEY `idx_packingline_item` (`item_id`),
    KEY `fk_packingline_outboundline` (`outbound_document_line_id`),
    KEY `fk_packingline_batch` (`batch_id`),
    CONSTRAINT `fk_packingline_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_packingline_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_packingline_order` FOREIGN KEY (`packing_order_id`) REFERENCES `packing_orders`(`id`),
    CONSTRAINT `fk_packingline_outboundline` FOREIGN KEY (`outbound_document_line_id`) REFERENCES `outbound_document_lines`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `zone_companies` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `zone_id`     INT UNSIGNED NOT NULL,
    `company_id`  INT UNSIGNED NOT NULL,
    `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_zone_company` (`zone_id`, `company_id`),
    KEY `idx_zonecompanies_company` (`company_id`),
    CONSTRAINT `fk_zonecompanies_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_zonecompanies_zone` FOREIGN KEY (`zone_id`) REFERENCES `warehouse_zones`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Asignacion de que empresas pueden usar cada zona (zonas compartidas entre companies)';
