-- Migration 010: Create mapping_fallbacks table
-- Requirements: 8.2, 16.5
-- Design: Data Models -> 10. MAPPING FALLBACKS (permanen)
--
-- Tabel persistensi fallback mapping ketika token-match gagal:
--   - status=UNRESOLVED: token belum punya kandidat varian (variant_id NULL).
--   - status=AMBIGUOUS : >1 kandidat varian (candidate_variant_ids JSON).
--   - status=RESOLVED  : sudah dipetakan ke variant_id oleh user.
-- UNIQUE (marketplace_source, token_match) menjamin satu entri per token
-- per marketplace; ETL worker melakukan upsert + increment occurrence_count.
-- Index (status, updated_at) digunakan untuk antrian fallback queue di UI.

CREATE TABLE IF NOT EXISTS mapping_fallbacks (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    marketplace_source      ENUM('SHOPEE','TOKOPEDIA','TIKTOK_SHOP') NOT NULL,
    token_match             VARCHAR(512) NOT NULL,
    variant_id              BIGINT UNSIGNED NULL,
    status                  ENUM('UNRESOLVED','AMBIGUOUS','RESOLVED')
                              NOT NULL DEFAULT 'UNRESOLVED',
    candidate_variant_ids   JSON         NULL,
    sample_product_name     VARCHAR(255) NULL,
    sample_variant_name     VARCHAR(255) NULL,
    occurrence_count        INT          NOT NULL DEFAULT 1,
    resolved_by             BIGINT UNSIGNED NULL,
    resolved_at             DATETIME(3)  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_mf_source_token (marketplace_source, token_match),
    KEY idx_mf_status (status, updated_at),
    CONSTRAINT fk_mf_variant FOREIGN KEY (variant_id)
        REFERENCES variants(id) ON DELETE SET NULL,
    CONSTRAINT fk_mf_resolver FOREIGN KEY (resolved_by)
        REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;