-- LEGACY_READ
-- LEGACY TABLE WARNING:
-- product_fitment is deprecated and must not be used for
-- fitment evaluation, confidence scoring, or reconciliation.
-- Use fitment_resolved instead.
-- Phase 4 only — non-runtime augmentation.
-- WARNING: Do not import Phase 3 runtime or bootstrap code.

CREATE TABLE IF NOT EXISTS product_fitment_exclusions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    make VARCHAR(64) NOT NULL DEFAULT '',
    model VARCHAR(128) NOT NULL DEFAULT '',
    year_from INT NOT NULL DEFAULT 0,
    year_to INT NOT NULL DEFAULT 0,
    reason VARCHAR(255) NOT NULL DEFAULT '',
    created_by VARCHAR(80) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
    INDEX idx_exclusions_product (product_id),
    INDEX idx_exclusions_vehicle (make, model, year_from, year_to)
);

CREATE TABLE IF NOT EXISTS product_fitment_confidence (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    make VARCHAR(64) NOT NULL DEFAULT '',
    model VARCHAR(128) NOT NULL DEFAULT '',
    model_year INT NOT NULL DEFAULT 0,
    confidence VARCHAR(32) NOT NULL DEFAULT '',
    source VARCHAR(64) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
    INDEX idx_confidence_product (product_id),
    INDEX idx_confidence_vehicle (make, model, model_year)
);

CREATE TABLE IF NOT EXISTS product_fitment_annotations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    note TEXT NOT NULL,
    tags VARCHAR(255) NOT NULL DEFAULT '',
    created_by VARCHAR(80) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
    INDEX idx_annotations_product (product_id)
);

CREATE TABLE IF NOT EXISTS product_fitment_overrides_audit (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    proposed_action VARCHAR(64) NOT NULL DEFAULT '',
    rationale TEXT NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'proposed',
    created_by VARCHAR(80) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
    INDEX idx_overrides_product (product_id),
    INDEX idx_overrides_status (status)
);

CREATE TABLE IF NOT EXISTS fitment_gpt_scout (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    internal_product_id BIGINT UNSIGNED NOT NULL,
    fitment_resolved_id BIGINT UNSIGNED NOT NULL,

    scout_flags JSON NOT NULL,
    confidence_delta DECIMAL(4,3) NOT NULL,
    reasoning TEXT NOT NULL,

    model_version VARCHAR(50) NOT NULL,
    prompt_hash CHAR(64) NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uniq_scout_run (
        internal_product_id,
        fitment_resolved_id,
        prompt_hash
    )
);
