-- Migration 005: Create variants table
-- Requirements: 1.2, 1.3, 1.5
-- Design: Data Models -> 5. VARIANTS
--
-- Catatan: `sku_active` adalah generated column STORED yang bernilai `sku`
-- saat varian aktif (deleted_at IS NULL) dan NULL saat soft-deleted. Pola
-- generated-column + UNIQUE meniadakan benturan SKU pada varian aktif saja
-- (MySQL/MariaDB tidak punya partial unique index).

CREATE TABLE IF NOT EXISTS variants (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    master_product_id   BIGINT UNSIGNED NOT NULL,
    sku                 VARCHAR(128) NOT NULL,
    variant_name        VARCHAR(255) NOT NULL,
    hpp_price           DECIMAL(15,2) NOT NULL,
    stock_quantity      INT          NOT NULL DEFAULT 0,
    -- Token_Match: gabungan product_name + variant_name dinormalisasi
    -- (NFKD -> lowercase -> strip non-[a-z0-9]). Dihitung di aplikasi,
    -- disimpan terindeks supaya lookup O(log n) dari mapping engine.
    token_match         VARCHAR(512) 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),
    deleted_at          DATETIME(3)  NULL,
    -- SKU unik HANYA untuk varian aktif (soft-delete safe).
    sku_active          VARCHAR(128) GENERATED ALWAYS AS
                            (CASE WHEN deleted_at IS NULL THEN sku ELSE NULL END) STORED,
    PRIMARY KEY (id),
    UNIQUE KEY uk_variants_sku_active (sku_active),
    KEY idx_variants_token  (token_match),
    KEY idx_variants_master (master_product_id, deleted_at),
    CONSTRAINT fk_variants_master FOREIGN KEY (master_product_id)
        REFERENCES master_products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;