-- FIAC Vehicle Models (run after vehicle_brands_schema.sql)

USE fiac_db;

CREATE TABLE IF NOT EXISTS vehicle_models (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  brand_id TINYINT UNSIGNED NOT NULL,
  name VARCHAR(80) NOT NULL,
  slug VARCHAR(80) NOT NULL,
  sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_vehicle_model_slug (brand_id, slug),
  KEY idx_vehicle_models_brand (brand_id),
  CONSTRAINT fk_vehicle_models_brand FOREIGN KEY (brand_id) REFERENCES vehicle_brands (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO vehicle_models (brand_id, name, slug, sort_order)
SELECT b.id, v.name, v.slug, v.sort_order
FROM vehicle_brands b
JOIN (
  SELECT 'audi' AS brand, 'A3' AS name, 'a3' AS slug, 1 AS sort_order UNION ALL
  SELECT 'audi', 'A4', 'a4', 2 UNION ALL
  SELECT 'audi', 'A6', 'a6', 3 UNION ALL
  SELECT 'audi', 'A8', 'a8', 4 UNION ALL
  SELECT 'audi', 'Q3', 'q3', 5 UNION ALL
  SELECT 'audi', 'Q5', 'q5', 6 UNION ALL
  SELECT 'audi', 'Q7', 'q7', 7 UNION ALL
  SELECT 'audi', 'Q8', 'q8', 8 UNION ALL
  SELECT 'audi', 'e-tron', 'e_tron', 9 UNION ALL
  SELECT 'audi', 'RS5', 'rs5', 10 UNION ALL

  SELECT 'bentley', 'Continental GT', 'continental_gt', 1 UNION ALL
  SELECT 'bentley', 'Flying Spur', 'flying_spur', 2 UNION ALL
  SELECT 'bentley', 'Bentayga', 'bentayga', 3 UNION ALL
  SELECT 'bentley', 'Mulsanne', 'mulsanne', 4 UNION ALL

  SELECT 'ferrari', '488 GTB', '488_gtb', 1 UNION ALL
  SELECT 'ferrari', 'F8 Tributo', 'f8_tributo', 2 UNION ALL
  SELECT 'ferrari', 'Roma', 'roma', 3 UNION ALL
  SELECT 'ferrari', 'Portofino', 'portofino', 4 UNION ALL
  SELECT 'ferrari', 'SF90 Stradale', 'sf90_stradale', 5 UNION ALL
  SELECT 'ferrari', '812 Superfast', '812_superfast', 6 UNION ALL
  SELECT 'ferrari', 'Purosangue', 'purosangue', 7 UNION ALL

  SELECT 'bmw', '3 Series', '3_series', 1 UNION ALL
  SELECT 'bmw', '5 Series', '5_series', 2 UNION ALL
  SELECT 'bmw', '7 Series', '7_series', 3 UNION ALL
  SELECT 'bmw', 'X1', 'x1', 4 UNION ALL
  SELECT 'bmw', 'X3', 'x3', 5 UNION ALL
  SELECT 'bmw', 'X5', 'x5', 6 UNION ALL
  SELECT 'bmw', 'X7', 'x7', 7 UNION ALL
  SELECT 'bmw', 'M3', 'm3', 8 UNION ALL
  SELECT 'bmw', 'M5', 'm5', 9 UNION ALL
  SELECT 'bmw', 'i4', 'i4', 10 UNION ALL
  SELECT 'bmw', 'iX', 'ix', 11 UNION ALL

  SELECT 'jaguar', 'XF', 'xf', 1 UNION ALL
  SELECT 'jaguar', 'XJ', 'xj', 2 UNION ALL
  SELECT 'jaguar', 'F-Pace', 'f_pace', 3 UNION ALL
  SELECT 'jaguar', 'E-Pace', 'e_pace', 4 UNION ALL
  SELECT 'jaguar', 'I-Pace', 'i_pace', 5 UNION ALL
  SELECT 'jaguar', 'F-Type', 'f_type', 6 UNION ALL

  SELECT 'lamborghini', 'Huracan', 'huracan', 1 UNION ALL
  SELECT 'lamborghini', 'Aventador', 'aventador', 2 UNION ALL
  SELECT 'lamborghini', 'Urus', 'urus', 3 UNION ALL
  SELECT 'lamborghini', 'Revuelto', 'revuelto', 4 UNION ALL

  SELECT 'land_rover', 'Defender', 'defender', 1 UNION ALL
  SELECT 'land_rover', 'Discovery', 'discovery', 2 UNION ALL
  SELECT 'land_rover', 'Range Rover', 'range_rover', 3 UNION ALL
  SELECT 'land_rover', 'Range Rover Sport', 'range_rover_sport', 4 UNION ALL
  SELECT 'land_rover', 'Range Rover Velar', 'range_rover_velar', 5 UNION ALL
  SELECT 'land_rover', 'Range Rover Evoque', 'range_rover_evoque', 6 UNION ALL

  SELECT 'maybach', 'S-Class', 's_class', 1 UNION ALL
  SELECT 'maybach', 'GLS', 'gls', 2 UNION ALL
  SELECT 'maybach', 'EQS', 'eqs', 3 UNION ALL

  SELECT 'mercedes', 'A-Class', 'a_class', 1 UNION ALL
  SELECT 'mercedes', 'C-Class', 'c_class', 2 UNION ALL
  SELECT 'mercedes', 'E-Class', 'e_class', 3 UNION ALL
  SELECT 'mercedes', 'S-Class', 's_class', 4 UNION ALL
  SELECT 'mercedes', 'GLA', 'gla', 5 UNION ALL
  SELECT 'mercedes', 'GLC', 'glc', 6 UNION ALL
  SELECT 'mercedes', 'GLE', 'gle', 7 UNION ALL
  SELECT 'mercedes', 'GLS', 'gls', 8 UNION ALL
  SELECT 'mercedes', 'AMG GT', 'amg_gt', 9 UNION ALL
  SELECT 'mercedes', 'EQS', 'eqs', 10 UNION ALL

  SELECT 'mg', 'Hector', 'hector', 1 UNION ALL
  SELECT 'mg', 'Astor', 'astor', 2 UNION ALL
  SELECT 'mg', 'Gloster', 'gloster', 3 UNION ALL
  SELECT 'mg', 'ZS EV', 'zs_ev', 4 UNION ALL
  SELECT 'mg', 'Comet EV', 'comet_ev', 5 UNION ALL

  SELECT 'mini_cooper', 'Cooper', 'cooper', 1 UNION ALL
  SELECT 'mini_cooper', 'Cooper S', 'cooper_s', 2 UNION ALL
  SELECT 'mini_cooper', 'Countryman', 'countryman', 3 UNION ALL
  SELECT 'mini_cooper', 'Clubman', 'clubman', 4 UNION ALL

  SELECT 'jeep', 'Wrangler', 'wrangler', 1 UNION ALL
  SELECT 'jeep', 'Grand Cherokee', 'grand_cherokee', 2 UNION ALL
  SELECT 'jeep', 'Compass', 'compass', 3 UNION ALL
  SELECT 'jeep', 'Meridian', 'meridian', 4 UNION ALL

  SELECT 'volkswagen', 'Polo', 'polo', 1 UNION ALL
  SELECT 'volkswagen', 'Virtus', 'virtus', 2 UNION ALL
  SELECT 'volkswagen', 'Taigun', 'taigun', 3 UNION ALL
  SELECT 'volkswagen', 'Tiguan', 'tiguan', 4 UNION ALL
  SELECT 'volkswagen', 'Passat', 'passat', 5 UNION ALL

  SELECT 'porsche', '911', '911', 1 UNION ALL
  SELECT 'porsche', 'Cayenne', 'cayenne', 2 UNION ALL
  SELECT 'porsche', 'Macan', 'macan', 3 UNION ALL
  SELECT 'porsche', 'Panamera', 'panamera', 4 UNION ALL
  SELECT 'porsche', 'Taycan', 'taycan', 5 UNION ALL
  SELECT 'porsche', '718 Boxster', '718_boxster', 6 UNION ALL

  SELECT 'volvo', 'XC40', 'xc40', 1 UNION ALL
  SELECT 'volvo', 'XC60', 'xc60', 2 UNION ALL
  SELECT 'volvo', 'XC90', 'xc90', 3 UNION ALL
  SELECT 'volvo', 'S60', 's60', 4 UNION ALL
  SELECT 'volvo', 'S90', 's90', 5
) v ON v.brand = b.slug
ON DUPLICATE KEY UPDATE name = VALUES(name), sort_order = VALUES(sort_order);