CREATE TABLE IF NOT EXISTS models (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  make_slug VARCHAR(80) NOT NULL,
  make_name VARCHAR(100) NOT NULL,
  model_slug VARCHAR(80) NOT NULL,
  model_name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_model (make_slug, model_slug),
  KEY idx_make (make_slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS model_years (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  model_id BIGINT UNSIGNED NOT NULL,
  model_year SMALLINT UNSIGNED NOT NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  overview TEXT NULL,
  buyer_checks TEXT NULL,
  limitations TEXT NULL,
  reviewed_by VARCHAR(120) NULL,
  reviewed_at DATETIME NULL,
  data_checked_at DATETIME NULL,
  published_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_year (model_id, model_year),
  KEY idx_public (status, updated_at),
  CONSTRAINT fk_year_model FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS evidence (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  model_year_id BIGINT UNSIGNED NOT NULL,
  kind ENUM('complaint','recall','investigation','service_bulletin') NOT NULL,
  source_name VARCHAR(120) NOT NULL,
  source_record_id VARCHAR(120) NOT NULL,
  source_url VARCHAR(1000) NOT NULL,
  component VARCHAR(150) NULL,
  mileage INT UNSIGNED NULL,
  summary TEXT NULL,
  observed_at DATE NULL,
  imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_evidence (model_year_id, kind, source_name, source_record_id),
  KEY idx_year_kind (model_year_id, kind),
  CONSTRAINT fk_evidence_year FOREIGN KEY (model_year_id) REFERENCES model_years(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;