-- migration_inventory.sql
-- Inventory reservation system

CREATE TABLE IF NOT EXISTS inventory_cache (
    variant_id      BIGINT PRIMARY KEY,
    product_id      BIGINT NOT NULL,
    product_title   VARCHAR(255),
    variant_title   VARCHAR(255),
    shopify_qty     INT NOT NULL DEFAULT 0,  -- actual Shopify inventory
    held_qty        INT NOT NULL DEFAULT 0,  -- held by pending orders
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory_holds (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    order_id        INT NOT NULL,
    variant_id      BIGINT NOT NULL,
    quantity        INT NOT NULL,
    status          ENUM('held','released','committed') DEFAULT 'held',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order (order_id),
    INDEX idx_variant (variant_id)
);