-- =============================================================================
-- XPERT 3PL - Migracion 022
-- Recall de productos: construido enteramente sobre la trazabilidad ya
-- armada en la migracion 021 (batch_receipts, stock_movements con serie).
-- Al abrir un recall se calculan automaticamente los resultados separados
-- en dos categorias: lo que todavia esta en deposito (para bloquear) y lo
-- que ya se despacho (para notificar al cliente).
-- =============================================================================

CREATE TABLE IF NOT EXISTS `recalls` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`    INT UNSIGNED NOT NULL,
    `item_id`       INT UNSIGNED NOT NULL,
    `batch_id`      INT UNSIGNED NULL COMMENT 'NULL = recall de todo el item, sin importar el lote',
    `reason`        TEXT NOT NULL,
    `status`        ENUM('OPEN','IN_PROGRESS','CLOSED') NOT NULL DEFAULT 'OPEN',
    `initiated_by`  INT UNSIGNED NULL,
    `initiated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `closed_at`     DATETIME NULL,
    KEY `idx_recalls_company` (`company_id`),
    KEY `idx_recalls_item` (`item_id`),
    KEY `idx_recalls_status` (`status`),
    CONSTRAINT `fk_recalls_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`),
    CONSTRAINT `fk_recalls_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`),
    CONSTRAINT `fk_recalls_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `recall_results` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `recall_id`             INT UNSIGNED NOT NULL,
    `source`                ENUM('STOCK_ON_HAND','DISPATCHED') NOT NULL,
    `batch_id`              INT UNSIGNED NULL COMMENT 'Que lote puntual cubre esta fila (util cuando el recall es de todo el item)',
    `bin_id`                INT UNSIGNED NULL COMMENT 'Solo para source=STOCK_ON_HAND',
    `outbound_document_id`  INT UNSIGNED NULL COMMENT 'Solo para source=DISPATCHED',
    `customer_id`           INT UNSIGNED NULL COMMENT 'Solo para source=DISPATCHED',
    `quantity`              DECIMAL(15,4) NOT NULL,
    `generated_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_recallresults_recall` (`recall_id`),
    KEY `idx_recallresults_source` (`source`),
    CONSTRAINT `fk_recallresults_recall` FOREIGN KEY (`recall_id`) REFERENCES `recalls`(`id`),
    CONSTRAINT `fk_recallresults_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches`(`id`),
    CONSTRAINT `fk_recallresults_bin` FOREIGN KEY (`bin_id`) REFERENCES `bins`(`id`),
    CONSTRAINT `fk_recallresults_outbound` FOREIGN KEY (`outbound_document_id`) REFERENCES `outbound_documents`(`id`),
    CONSTRAINT `fk_recallresults_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Calculado automaticamente al abrir el recall, no editable a mano';
