-- migrate:up
CREATE TABLE stock_history (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Unique ID for each adjustment
    part_number VARCHAR(100) NOT NULL, -- Product identifier
    warehouse VARCHAR(100) NOT NULL, -- Stock location
    status ENUM('IN', 'OUT') NOT NULL, -- Status of stock movement
    qty_change INT NOT NULL, -- Amount changed (always positive)
    prev_qty INT NOT NULL, -- Quantity before adjustment
    new_qty INT NOT NULL, -- Quantity after adjustment
    transfer_id INT DEFAULT NULL, -- Related transfer ID (if any)
    from_warehouse_id INT DEFAULT NULL, -- ID of the sending warehouse
    to_warehouse_id INT DEFAULT NULL, -- ID of the receiving warehouse
    reason VARCHAR(255) DEFAULT NULL, -- Reason for adjustment
    category ENUM('MOVE', 'SALE', 'COUNT', 'OTHER') DEFAULT 'OTHER' NOT NULL, -- Adjustment category
    created_by VARCHAR(100) DEFAULT 'SYSTEM' NOT NULL, -- User who made the adjustment
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Date and time of adjustment
);

-- migrate:down
DROP TABLE IF EXISTS stock_history;