CREATE TABLE IF NOT EXISTS stock_job_items (
    sji_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sji_stock_item_id INT UNSIGNED NOT NULL,
    sji_job_type VARCHAR(20) NOT NULL,
    sji_job_id INT UNSIGNED NOT NULL,
    sji_quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    sji_unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    sji_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sji_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (sji_id),
    KEY idx_sji_job (sji_job_type, sji_job_id),
    KEY idx_sji_stock_item (sji_stock_item_id),
    CONSTRAINT fk_sji_stock_item FOREIGN KEY (sji_stock_item_id) REFERENCES stock_items(si_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
