USE BeezifiIOM;

CREATE TABLE IF NOT EXISTS order_item_allocations (
    id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT UNSIGNED NOT NULL,
    order_id             INT UNSIGNED NOT NULL,
    order_item_id        INT UNSIGNED NOT NULL,
    product_id           INT UNSIGNED NOT NULL,
    warehouse_id         INT UNSIGNED NOT NULL,
    inventory_product_id INT UNSIGNED NOT NULL,
    batch_id             INT UNSIGNED NULL,
    serial_id            INT UNSIGNED NULL,
    step                 ENUM('receive', 'pick', 'pack', 'ship') NOT NULL,
    allocated_quantity   DECIMAL(10,3) NOT NULL DEFAULT 0,
    exception_type       ENUM('short_pick', 'damaged', 'substitute') NULL,
    substitute_product_id INT UNSIGNED NULL,
    scanned_code         VARCHAR(255) NULL,
    audit_note           TEXT NULL,
    created_by           INT UNSIGNED NULL,
    created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_order_step (tenant_id, order_id, step),
    INDEX idx_order_item (order_item_id),
    INDEX idx_batch (batch_id),
    INDEX idx_serial (serial_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (inventory_product_id) REFERENCES products(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE SET NULL,
    FOREIGN KEY (serial_id) REFERENCES serial_numbers(id) ON DELETE SET NULL,
    FOREIGN KEY (substitute_product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB;
