-- Migration 006: Create hpp_history table
-- Requirements: 2.1
-- Design: Data Models -> 6. HPP HISTORY
--
-- Riwayat perubahan HPP per varian (append-only). Dipakai ETL untuk
-- snapshot HPP historis (P7): getHppAt(variant, order_date) mengambil HPP
-- yang berlaku pada waktu order, bukan HPP terkini.

CREATE TABLE IF NOT EXISTS hpp_history (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    variant_id      BIGINT UNSIGNED NOT NULL,
    hpp_price       DECIMAL(15,2) NOT NULL,
    effective_from  DATETIME(3)  NOT NULL,
    changed_by      BIGINT UNSIGNED NOT NULL,
    reason          VARCHAR(512) NULL,
    created_at      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    KEY idx_hpp_variant_eff (variant_id, effective_from DESC),
    CONSTRAINT fk_hpp_variant FOREIGN KEY (variant_id)
        REFERENCES variants(id) ON DELETE CASCADE,
    CONSTRAINT fk_hpp_user FOREIGN KEY (changed_by)
        REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;