-- ZTE Model Registry — vendor part number → Google ZTE canonical model name.
--
-- Replaces the regex-based extractModel() in core/lib/zte-compatible-devices.js
-- with a DB lookup so:
--   • new SKUs auto-flag at PO line-item insert (before devices arrive)
--   • mappings are admin-editable without code deploys
--   • per-mapping success/failure counts surface bad rows
--
-- See .claude/docs/zte-device-claiming.md for the OEM-specific quirks this
-- table normalizes (Lenovo MTM regional suffixes, ASUS ADID requirement,
-- HubX placeholder serials, etc.).

CREATE TABLE IF NOT EXISTS zte_model_registry (
    id INT AUTO_INCREMENT PRIMARY KEY,

    -- Identity (join key from purchase_order_items.part_number)
    vendor_part_number VARCHAR(100) NOT NULL,
    -- Normalized form with regional/keyboard suffix stripped.
    -- HP: '3V2U9UT#ABA' → '3V2U9UT'
    -- Lenovo: '82W00001US' → '82W00001'
    -- ASUS: 'CR1100CKA-YZ182' → 'CR1100CKA'
    manufacturer_part VARCHAR(100) NOT NULL,
    -- Optional vendor scope. NULL = matches any vendor for this part number.
    -- Use when D&H and TD Synnex ship the same MTM under different SKUs and
    -- only one of them needs special handling.
    vendor VARCHAR(50) DEFAULT NULL,

    -- ZTE claim payload (must match Google's ZTE portal exactly)
    manufacturer VARCHAR(50) NOT NULL,
    google_model_name VARCHAR(255) NOT NULL,
    -- Where buildZteDeviceIdentifier() should pull the chromeOsAttestedDeviceId from:
    --   serial         — HP/Acer formula: attestedId == serial
    --   barcode_8char  — Lenovo: strip 20-char label-scan barcode to last 8 chars
    --   asus_billy     — ASUS: requires stored ADID from device_attested_ids (Billy Abbott CSV)
    --   none           — non-ZTE-compatible row, kept for completeness
    attested_id_source ENUM('serial','barcode_8char','asus_billy','none') NOT NULL DEFAULT 'serial',

    is_zte_compatible TINYINT(1) NOT NULL DEFAULT 1,
    -- Free-text grouping for the admin UI (e.g. "HP Fortis G1m", "Lenovo 100e Gen 5")
    product_family VARCHAR(100) DEFAULT NULL,
    notes TEXT DEFAULT NULL,

    -- Provenance — how the row got here
    source ENUM(
        'seed_extract_regex',   -- auto-seeded from existing extractModel() regex match
        'seed_google_list',     -- pulled from Google's published canonical model list
        'live_claim_success',   -- inferred from a successful live claim
        'manual'                -- admin-edited via UI
    ) NOT NULL DEFAULT 'manual',

    -- Verification / self-healing counters
    verified_at TIMESTAMP NULL DEFAULT NULL,
    verified_by INT DEFAULT NULL,
    claim_success_count INT NOT NULL DEFAULT 0,
    claim_failure_count INT NOT NULL DEFAULT 0,
    last_failure_reason TEXT DEFAULT NULL,
    last_claim_at TIMESTAMP NULL DEFAULT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uniq_part_vendor (vendor_part_number, vendor),
    INDEX idx_zmr_mfg_part (manufacturer_part),
    INDEX idx_zmr_manufacturer (manufacturer),
    INDEX idx_zmr_compatible (is_zte_compatible),
    INDEX idx_zmr_failures (claim_failure_count),
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Unmapped queue — distinct (part_number, vendor) seen on POs that have no
-- registry row yet. Populated by the auto-flag hook on PO line-item insert
-- and surfaced in the admin UI's "Unmapped" tab.
CREATE TABLE IF NOT EXISTS zte_unmapped_parts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_part_number VARCHAR(100) NOT NULL,
    vendor VARCHAR(50) DEFAULT NULL,
    sample_product_name VARCHAR(500) DEFAULT NULL,
    -- Most recent PO line that surfaced this part — for context in the admin UI
    last_po_id INT DEFAULT NULL,
    last_po_item_id INT DEFAULT NULL,
    occurrence_count INT NOT NULL DEFAULT 1,
    -- Status moves: open → resolved (admin created a registry row) | dismissed (admin marked non-ZTE)
    status ENUM('open','resolved','dismissed') NOT NULL DEFAULT 'open',
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    resolved_by INT DEFAULT NULL,
    first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uniq_unmapped_part_vendor (vendor_part_number, vendor),
    INDEX idx_zup_status (status),
    FOREIGN KEY (last_po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Parallel-run telemetry — Phase A logs every claim's regex result vs DB
-- result so we can tune the registry before flipping the source of truth.
-- Drops once Phase C lands (extractModel removed).
CREATE TABLE IF NOT EXISTS zte_lookup_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    -- Inputs
    part_number VARCHAR(100) DEFAULT NULL,
    vendor VARCHAR(50) DEFAULT NULL,
    product_name VARCHAR(500) DEFAULT NULL,
    -- What each path returned
    db_manufacturer VARCHAR(50) DEFAULT NULL,
    db_model VARCHAR(255) DEFAULT NULL,
    db_attested_id_source VARCHAR(50) DEFAULT NULL,
    regex_manufacturer VARCHAR(50) DEFAULT NULL,
    regex_model VARCHAR(255) DEFAULT NULL,
    -- One of: agree | db_only | regex_only | disagree | both_null
    verdict VARCHAR(20) NOT NULL,
    -- Used by the actual claim path (Phase A = regex, Phase B = db)
    used_source ENUM('regex','db') NOT NULL,
    -- Optional link back to the PO/claim that triggered this lookup
    po_id INT DEFAULT NULL,
    serial_number VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_zla_verdict (verdict),
    INDEX idx_zla_part (part_number),
    INDEX idx_zla_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;