-- Migration 008: Create order_items table
-- Requirements: 6.2, 6.3, 6.6, 16.2
-- Design: Data Models -> 8. ORDER ITEMS

CREATE TABLE IF NOT EXISTS order_items (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    marketplace_order_id    BIGINT UNSIGNED NOT NULL,
    line_number             INT          NOT NULL,
    sku_raw                 VARCHAR(128) NULL,
    product_name_raw        VARCHAR(255) NULL,
    variant_name_raw        VARCHAR(255) NULL,
    token_match             VARCHAR(512) NULL,
    variant_id              BIGINT UNSIGNED NULL,
    quantity                INT          NOT NULL,
    unit_price              DECIMAL(15,2) NOT NULL DEFAULT 0,
    hpp_snapshot            DECIMAL(15,2) NOT NULL DEFAULT 0,
    mapping_method          ENUM('EXACT_SKU','TOKEN_EXACT','FALLBACK','AMBIGUOUS','UNRESOLVED') NOT NULL,
    created_at              DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at              DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_items_order_line (marketplace_order_id, line_number),
    KEY idx_items_variant (variant_id),
    KEY idx_items_mapping (mapping_method),
    KEY idx_items_token (token_match),
    CONSTRAINT fk_items_order FOREIGN KEY (marketplace_order_id)
        REFERENCES marketplace_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_items_variant FOREIGN KEY (variant_id)
        REFERENCES variants(id) ON DELETE SET NULL,
    CONSTRAINT chk_items_qty CHECK (quantity >= 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;