-- =============================================================================
-- XPERT SAP B1 - Migracion 054
-- Modulo DRP Fase 2: motor de calculo, solo lectura. Una corrida (drp_runs)
-- calcula sugerencias por sucursal+item (drp_run_branch_lines) a partir de
-- drp_branch_sales_history + drp_branch_item_params (Fase 1) y el stock
-- disponible del CD -- sin tocar SAP todavia (eso queda para una Fase 3 de
-- aprobacion y disparo real de STs, con su propia tabla de vinculo).
--
-- branch_current_stock queda siempre en 0 en esta version: no hay forma
-- confiable de conocer el stock de una sucursal externa (managed_by_wms=0)
-- sin consultarlo en vivo a SAP, y eso reintroduciria la dependencia que la
-- Fase 1 evito a proposito usando CSV en vez de sync en tiempo real. Ver
-- DrpCalculationService.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `drp_runs` (
    `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`           INT UNSIGNED NOT NULL,
    `doc_number`           VARCHAR(30) NOT NULL,
    `cd_warehouse_id`      INT UNSIGNED NOT NULL,
    `allocation_strategy`  ENUM('PRO_RATA','ABC_PRIORITY') NOT NULL,
    `sales_window_days`    INT UNSIGNED NOT NULL COMMENT 'Snapshot del parametro usado en esta corrida (drp_company_settings puede cambiar despues)',
    `item_count`           INT UNSIGNED NOT NULL DEFAULT 0,
    `line_count`           INT UNSIGNED NOT NULL DEFAULT 0,
    `triggered_by`         INT UNSIGNED NULL,
    `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_drprun_docnum` (`doc_number`),
    KEY `idx_drprun_company` (`company_id`),
    CONSTRAINT `fk_drprun_company`   FOREIGN KEY (`company_id`)      REFERENCES `companies`(`id`),
    CONSTRAINT `fk_drprun_warehouse` FOREIGN KEY (`cd_warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_drprun_user`      FOREIGN KEY (`triggered_by`)    REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `drp_run_branch_lines` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `run_id`                INT UNSIGNED NOT NULL,
    `company_id`            INT UNSIGNED NOT NULL,
    `warehouse_id`          INT UNSIGNED NOT NULL COMMENT 'Sucursal (managed_by_wms=0)',
    `item_id`                INT UNSIGNED NOT NULL,
    `avg_daily_qty`         DECIMAL(15,4) NOT NULL,
    `coverage_days`         INT UNSIGNED NOT NULL COMMENT 'Efectivo para esta linea: parametro de la sucursal+item o default de la empresa',
    `branch_current_stock`  DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Siempre 0 en esta version -- ver comentario de cabecera de la migracion',
    `suggested_qty`         DECIMAL(15,4) NOT NULL COMMENT 'Demanda bruta antes de prorratear: avg_daily_qty*coverage_days - branch_current_stock',
    `cd_available_qty`      DECIMAL(15,4) NOT NULL COMMENT 'Snapshot del disponible en el CD para este item al momento de la corrida (antes de repartir)',
    `incoming_po_qty`       DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'OC abiertas del CD con fecha estimada dentro del lead time',
    `allocated_qty`         DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Lo que el CD puede cubrir de suggested_qty tras prorratear/priorizar entre sucursales',
    `final_qty`             DECIMAL(15,4) NULL COMMENT 'Editable por el revisor; NULL = usar allocated_qty tal cual',
    `status`                ENUM('PENDING_REVIEW','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING_REVIEW',
    `reviewed_by`           INT UNSIGNED NULL,
    `reviewed_at`           DATETIME NULL,
    `review_notes`          VARCHAR(255) NULL,
    `created_at`            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_drprunline` (`run_id`, `warehouse_id`, `item_id`),
    KEY `idx_drprunline_run` (`run_id`),
    KEY `idx_drprunline_item` (`item_id`),
    CONSTRAINT `fk_drprunline_run`       FOREIGN KEY (`run_id`)       REFERENCES `drp_runs`(`id`),
    CONSTRAINT `fk_drprunline_company`   FOREIGN KEY (`company_id`)  REFERENCES `companies`(`id`),
    CONSTRAINT `fk_drprunline_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_drprunline_item`      FOREIGN KEY (`item_id`)     REFERENCES `items`(`id`),
    CONSTRAINT `fk_drprunline_reviewer`  FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
    ('DRP_RUN', 'DRP-', 0, 6)
ON DUPLICATE KEY UPDATE `prefix` = VALUES(`prefix`);
