-- Phase 4 — Fitment Resolution Layer (Authoritative)
-- NOTE: Phase 4 is the first phase where confidence decisions are allowed.

CREATE TABLE fitment_resolved (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

  -- Identity
  product_id BIGINT UNSIGNED NOT NULL,
  year SMALLINT NOT NULL,
  make VARCHAR(100) NOT NULL,
  model VARCHAR(100) NOT NULL,
  submodel VARCHAR(100) DEFAULT NULL,

  -- Resolution output
  final_confidence DECIMAL(4,3) NOT NULL,
  confidence_band ENUM('CERTAIN','HIGH','MEDIUM','REVIEW') NOT NULL,

  -- Evidence accounting
  evidence_count INT NOT NULL,
  sources_json JSON NOT NULL,
  evidence_ids_json JSON NOT NULL,

  -- Governance
  resolution_reason VARCHAR(255) NOT NULL,
  override_flag TINYINT(1) NOT NULL DEFAULT 0,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uniq_product_vehicle (
    product_id, year, make, model, submodel
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
