-- =============================================================================
-- XPERT SAP B1 - Migracion 051
-- Modulo DRP (Distribution Requirements Planning) - reposicion automatica de
-- sucursales desde el CD. Punto de partida: cada sucursal exporta su propio
-- historial de ventas y lo sube como CSV (no hay sync en vivo de ventas desde
-- SAP en esta version). drp_sales_uploads es la cabecera de cada carga;
-- drp_branch_sales_history es el detalle, una fila por item+fecha vendida.
-- item_id nullable a proposito -- mismo criterio que inbound_document_lines:
-- una fila sin matchear no bloquea el resto de la carga, se puede re-vincular
-- despues (ver DrpSalesUploadController::relinkItem).
-- Modulo 100% aditivo: sin ALTER a tablas existentes, sin tocar ningun otro
-- flujo. Inerte hasta que drp_company_settings.is_enabled este prendido
-- (ver migracion 052).
-- =============================================================================

CREATE TABLE IF NOT EXISTS `drp_sales_uploads` (
    `id`                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`         INT UNSIGNED NOT NULL,
    `warehouse_id`       INT UNSIGNED NOT NULL COMMENT 'Sucursal (managed_by_wms=0) duena del historial subido',
    `original_filename`  VARCHAR(255) NOT NULL,
    `period_from`        DATE NULL,
    `period_to`          DATE NULL,
    `row_count`          INT UNSIGNED NOT NULL DEFAULT 0,
    `matched_row_count`  INT UNSIGNED NOT NULL DEFAULT 0,
    `unmatched_row_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `status`             ENUM('PROCESSED','PROCESSED_WITH_WARNINGS') NOT NULL DEFAULT 'PROCESSED',
    `uploaded_by`        INT UNSIGNED NULL,
    `created_at`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_drpuploads_company` (`company_id`),
    KEY `idx_drpuploads_warehouse` (`warehouse_id`),
    CONSTRAINT `fk_drpuploads_company`   FOREIGN KEY (`company_id`)   REFERENCES `companies`(`id`),
    CONSTRAINT `fk_drpuploads_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_drpuploads_user`      FOREIGN KEY (`uploaded_by`)  REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `drp_branch_sales_history` (
    `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`          INT UNSIGNED NOT NULL,
    `warehouse_id`        INT UNSIGNED NOT NULL,
    `item_id`             INT UNSIGNED NULL COMMENT 'NULL = fila sin matchear todavia, ver sap_item_code_text',
    `sap_item_code_text`  VARCHAR(50) NOT NULL COMMENT 'Codigo tal cual vino en la columna item_code del CSV',
    `sale_date`           DATE NOT NULL,
    `qty_sold`            DECIMAL(15,4) NOT NULL,
    `uom`                 VARCHAR(20) NOT NULL DEFAULT 'UN',
    `upload_batch_id`     INT UNSIGNED NOT NULL,
    `created_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_drpsales_wh_item_date` (`warehouse_id`, `item_id`, `sale_date`),
    KEY `idx_drpsales_code` (`sap_item_code_text`),
    KEY `idx_drpsales_batch` (`upload_batch_id`),
    CONSTRAINT `fk_drpsales_company`  FOREIGN KEY (`company_id`)      REFERENCES `companies`(`id`),
    CONSTRAINT `fk_drpsales_warehouse` FOREIGN KEY (`warehouse_id`)   REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_drpsales_item`     FOREIGN KEY (`item_id`)         REFERENCES `items`(`id`),
    CONSTRAINT `fk_drpsales_batch`    FOREIGN KEY (`upload_batch_id`) REFERENCES `drp_sales_uploads`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
