-- =============================================================================
-- XPERT SAP B1 - Migracion 052
-- Modulo DRP (continua migracion 051): parametros de reposicion.
-- drp_company_settings es 1:1 con la empresa (no ALTER a company_settings
-- existente -- tabla nueva y separada a proposito) y trae is_enabled, el
-- flag que decide si el modulo esta prendido para este cliente. Solo lo
-- setea XPEAS via scripts/drp_toggle.php (SSH), nunca una pantalla del
-- cliente -- ver enabled_at/enabled_by_note.
-- drp_branch_item_params es el molde de item_picking_levels pero por
-- sucursal+item en vez de por item solo (una sucursal puede necesitar
-- distinta cobertura que otra para el mismo item).
-- =============================================================================

CREATE TABLE IF NOT EXISTS `drp_company_settings` (
    `company_id`            INT UNSIGNED NOT NULL PRIMARY KEY,
    `cd_warehouse_id`        INT UNSIGNED NOT NULL COMMENT 'Almacen central (managed_by_wms=1) origen de los traslados',
    `default_coverage_days`  INT UNSIGNED NOT NULL DEFAULT 7,
    `default_lead_time_days` INT UNSIGNED NOT NULL DEFAULT 3,
    `sales_window_days`      INT UNSIGNED NOT NULL DEFAULT 90,
    `allocation_strategy`    ENUM('PRO_RATA','ABC_PRIORITY') NOT NULL DEFAULT 'PRO_RATA',
    `is_enabled`             TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Solo lo setea XPEAS via scripts/drp_toggle.php -- no hay pantalla de cliente para esto',
    `enabled_at`             DATETIME NULL,
    `enabled_by_note`        VARCHAR(255) NULL COMMENT 'Texto libre: quien de XPEAS lo activo y por que (no es un user_id del cliente)',
    `updated_at`             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_drpsettings_company`   FOREIGN KEY (`company_id`)      REFERENCES `companies`(`id`),
    CONSTRAINT `fk_drpsettings_warehouse` FOREIGN KEY (`cd_warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `drp_branch_item_params` (
    `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)',
    `item_id`        INT UNSIGNED NOT NULL,
    `coverage_days`  INT UNSIGNED NULL COMMENT 'Override opcional de drp_company_settings.default_coverage_days',
    `lead_time_days` INT UNSIGNED NULL COMMENT 'Override opcional de drp_company_settings.default_lead_time_days',
    `min_qty`        DECIMAL(15,4) NULL COMMENT 'Piso opcional independiente del calculo por cobertura',
    `is_active`      TINYINT(1) NOT NULL DEFAULT 1,
    `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_drpparams_wh_item` (`warehouse_id`, `item_id`),
    KEY `idx_drpparams_company` (`company_id`),
    CONSTRAINT `fk_drpparams_company`   FOREIGN KEY (`company_id`)   REFERENCES `companies`(`id`),
    CONSTRAINT `fk_drpparams_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_drpparams_item`      FOREIGN KEY (`item_id`)      REFERENCES `items`(`id`),
    CONSTRAINT `fk_drpparams_user`      FOREIGN KEY (`created_by`)   REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
