CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  email_verified_at DATETIME NULL,
  email_verify_token CHAR(64) NULL,
  password_hash VARCHAR(255) NOT NULL,
  password_reset_token CHAR(64) NULL,
  password_reset_expires_at DATETIME NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  account_status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  plan ENUM('free','plus') NOT NULL DEFAULT 'free',
  subscription_status VARCHAR(40) NOT NULL DEFAULT 'none',
  stripe_customer_id VARCHAR(255) NULL,
  stripe_subscription_id VARCHAR(255) NULL,
  marketing_consent TINYINT(1) NOT NULL DEFAULT 0,
  marketing_consent_at DATETIME NULL,
  alert_launch TINYINT(1) NOT NULL DEFAULT 1,
  alert_recall TINYINT(1) NOT NULL DEFAULT 1,
  alert_maintenance TINYINT(1) NOT NULL DEFAULT 1,
  alert_warranty TINYINT(1) NOT NULL DEFAULT 1,
  unsubscribe_token CHAR(64) NOT NULL,
  last_login_at DATETIME NULL,
  admin_notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_plan (plan),
  INDEX idx_account_status (account_status),
  INDEX idx_stripe_subscription (stripe_subscription_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_user_actions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_user_id BIGINT UNSIGNED NULL,
  target_user_id BIGINT UNSIGNED NULL,
  target_email VARCHAR(255) NOT NULL,
  action VARCHAR(80) NOT NULL,
  details VARCHAR(1000) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_admin_action_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_admin_action_target FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_admin_action_target (target_user_id, created_at),
  INDEX idx_admin_action_actor (actor_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS brands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  aliases TEXT NULL,
  country VARCHAR(80) NOT NULL DEFAULT 'China',
  website VARCHAR(500) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  model_year VARCHAR(20) NULL,
  body_style VARCHAR(80) NULL,
  status ENUM('watching','announced','importer_seen','canada_rated','priced','dealers','available') NOT NULL DEFAULT 'watching',
  status_note VARCHAR(500) NULL,
  canada_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  msrp_cad DECIMAL(12,2) NULL,
  range_km INT NULL,
  efficiency_kwh_100km DECIMAL(7,2) NULL,
  recharge_hours DECIMAL(6,2) NULL,
  source_url VARCHAR(800) NULL,
  source_label VARCHAR(255) NULL,
  source_date DATE NULL,
  featured TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_brand_name_year (brand_id, name, model_year),
  CONSTRAINT fk_vehicle_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  INDEX idx_status (status),
  INDEX idx_featured (featured)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS evidence (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id BIGINT UNSIGNED NULL,
  brand_id BIGINT UNSIGNED NULL,
  evidence_type VARCHAR(80) NOT NULL,
  title VARCHAR(255) NOT NULL,
  detail TEXT NULL,
  source_name VARCHAR(180) NOT NULL,
  source_url VARCHAR(800) NOT NULL,
  source_date DATE NULL,
  fingerprint CHAR(64) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_evidence_fingerprint (fingerprint),
  CONSTRAINT fk_evidence_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_evidence_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  INDEX idx_evidence_vehicle (vehicle_id),
  INDEX idx_evidence_brand (brand_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recalls (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recall_number VARCHAR(80) NOT NULL,
  model_year VARCHAR(20) NULL,
  make_name VARCHAR(120) NOT NULL,
  model_name VARCHAR(180) NOT NULL,
  category VARCHAR(120) NULL,
  system_type VARCHAR(160) NULL,
  comment_text MEDIUMTEXT NULL,
  recall_date DATE NULL,
  last_update_date DATE NULL,
  manufacturer_name VARCHAR(180) NULL,
  source_url VARCHAR(800) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_recall_model (recall_number, model_year, make_name, model_name),
  INDEX idx_recall_make (make_name),
  INDEX idx_recall_date (recall_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS importer_matches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id BIGINT UNSIGNED NULL,
  matched_term VARCHAR(180) NOT NULL,
  row_summary TEXT NULL,
  raw_json MEDIUMTEXT NULL,
  source_url VARCHAR(800) NOT NULL,
  fingerprint CHAR(64) NOT NULL,
  first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_importer_fingerprint (fingerprint),
  CONSTRAINT fk_importer_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sources (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_key VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  url VARCHAR(800) NOT NULL,
  interval_minutes INT NOT NULL DEFAULT 1440,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_run_at DATETIME NULL,
  last_success_at DATETIME NULL,
  last_status VARCHAR(30) NULL,
  last_message TEXT NULL,
  last_hash CHAR(64) NULL,
  next_run_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS source_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_key VARCHAR(80) NOT NULL,
  status VARCHAR(30) NOT NULL,
  records_seen INT NOT NULL DEFAULT 0,
  records_changed INT NOT NULL DEFAULT 0,
  message TEXT NULL,
  run_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_run_source (source_key, run_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS watchlist (
  user_id BIGINT UNSIGNED NOT NULL,
  vehicle_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, vehicle_id),
  CONSTRAINT fk_watch_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_watch_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS alerts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  vehicle_id BIGINT UNSIGNED NULL,
  subject VARCHAR(255) NOT NULL,
  body_text TEXT NOT NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  attempts INT NOT NULL DEFAULT 0,
  last_error TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  CONSTRAINT fk_alert_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_alert_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
  INDEX idx_alert_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  email VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  ip_hash CHAR(64) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS garage_vehicles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  nickname VARCHAR(120) NULL,
  make_name VARCHAR(120) NOT NULL,
  model_name VARCHAR(180) NOT NULL,
  model_year VARCHAR(20) NULL,
  current_odometer_km INT UNSIGNED NOT NULL DEFAULT 0,
  start_odometer_km INT UNSIGNED NOT NULL DEFAULT 0,
  purchase_date DATE NULL,
  purchase_price_cad DECIMAL(12,2) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_garage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_garage_user (user_id),
  INDEX idx_garage_make_model (make_name,model_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  garage_vehicle_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  interval_km INT UNSIGNED NULL,
  interval_months INT UNSIGNED NULL,
  last_service_km INT UNSIGNED NULL,
  last_service_date DATE NULL,
  next_due_km INT UNSIGNED NULL,
  next_due_date DATE NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_alerted_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_maintenance_garage FOREIGN KEY (garage_vehicle_id) REFERENCES garage_vehicles(id) ON DELETE CASCADE,
  INDEX idx_maintenance_due_date (next_due_date),
  INDEX idx_maintenance_due_km (next_due_km)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  garage_vehicle_id BIGINT UNSIGNED NOT NULL,
  service_date DATE NOT NULL,
  odometer_km INT UNSIGNED NULL,
  service_type VARCHAR(160) NOT NULL,
  description TEXT NULL,
  shop_name VARCHAR(180) NULL,
  cost_cad DECIMAL(12,2) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_service_garage FOREIGN KEY (garage_vehicle_id) REFERENCES garage_vehicles(id) ON DELETE CASCADE,
  INDEX idx_service_vehicle_date (garage_vehicle_id,service_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS warranties (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  garage_vehicle_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  end_date DATE NULL,
  end_odometer_km INT UNSIGNED NULL,
  notes TEXT NULL,
  last_alerted_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_warranty_garage FOREIGN KEY (garage_vehicle_id) REFERENCES garage_vehicles(id) ON DELETE CASCADE,
  INDEX idx_warranty_end_date (end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS garage_recall_notifications (
  garage_vehicle_id BIGINT UNSIGNED NOT NULL,
  recall_id BIGINT UNSIGNED NOT NULL,
  notified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (garage_vehicle_id,recall_id),
  CONSTRAINT fk_grn_garage FOREIGN KEY (garage_vehicle_id) REFERENCES garage_vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_grn_recall FOREIGN KEY (recall_id) REFERENCES recalls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS charging_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  garage_vehicle_id BIGINT UNSIGNED NOT NULL,
  charge_date DATE NOT NULL,
  odometer_km INT UNSIGNED NULL,
  kwh DECIMAL(10,3) NULL,
  cost_cad DECIMAL(12,2) NULL,
  station VARCHAR(180) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_charge_garage FOREIGN KEY (garage_vehicle_id) REFERENCES garage_vehicles(id) ON DELETE CASCADE,
  INDEX idx_charge_vehicle_date (garage_vehicle_id,charge_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_comparison_settings (
  garage_vehicle_id BIGINT UNSIGNED PRIMARY KEY,
  gas_l_per_100km DECIMAL(8,3) NULL,
  gas_price_per_litre DECIMAL(8,3) NULL,
  electricity_price_per_kwh DECIMAL(8,4) NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_compare_garage FOREIGN KEY (garage_vehicle_id) REFERENCES garage_vehicles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tire_sets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  garage_vehicle_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  tire_type ENUM('summer','winter','all-season','all-weather','other') NOT NULL DEFAULT 'other',
  installed_date DATE NULL,
  installed_odometer_km INT UNSIGNED NULL,
  cost_cad DECIMAL(12,2) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tire_garage FOREIGN KEY (garage_vehicle_id) REFERENCES garage_vehicles(id) ON DELETE CASCADE,
  INDEX idx_tire_vehicle (garage_vehicle_id,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  status ENUM('sent','failed') NOT NULL,
  error_text TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email_status (status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS source_health_alerts (
  source_key VARCHAR(80) PRIMARY KEY,
  last_alerted_at DATETIME NULL,
  last_status VARCHAR(30) NULL,
  last_message TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stripe_webhook_events (
  event_id VARCHAR(120) PRIMARY KEY,
  event_type VARCHAR(120) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'received',
  message TEXT NULL,
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  INDEX idx_webhook_received (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;