USE bulo;

CREATE TABLE IF NOT EXISTS veiculo_marcas_catalogo (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(80) NOT NULL,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_veiculo_marcas_nome (nome),
  INDEX idx_veiculo_marcas_ativo (ativo)
);

CREATE TABLE IF NOT EXISTS veiculo_modelos_catalogo (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_marca INT NOT NULL,
  nome VARCHAR(120) NOT NULL,
  categoria_economica TINYINT(1) NOT NULL DEFAULT 1,
  categoria_plus TINYINT(1) NOT NULL DEFAULT 0,
  categoria_pro TINYINT(1) NOT NULL DEFAULT 0,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_modelos_catalogo_marca FOREIGN KEY (id_marca) REFERENCES veiculo_marcas_catalogo(id),
  UNIQUE KEY uk_modelos_catalogo_marca_nome (id_marca, nome),
  INDEX idx_modelos_catalogo_marca (id_marca),
  INDEX idx_modelos_catalogo_ativo (ativo),
  INDEX idx_modelos_catalogo_categorias (categoria_economica, categoria_plus, categoria_pro)
);

CREATE TABLE IF NOT EXISTS veiculo_cores_catalogo (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(60) NOT NULL,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_veiculo_cores_nome (nome),
  INDEX idx_veiculo_cores_ativo (ativo)
);

ALTER TABLE veiculos
  ADD COLUMN IF NOT EXISTS id_marca_catalogo INT NULL AFTER id_motorista,
  ADD COLUMN IF NOT EXISTS id_modelo_catalogo INT NULL AFTER id_marca_catalogo,
  ADD COLUMN IF NOT EXISTS id_cor_catalogo INT NULL AFTER id_modelo_catalogo,
  ADD INDEX IF NOT EXISTS idx_veiculos_marca_catalogo (id_marca_catalogo),
  ADD INDEX IF NOT EXISTS idx_veiculos_modelo_catalogo (id_modelo_catalogo),
  ADD INDEX IF NOT EXISTS idx_veiculos_cor_catalogo (id_cor_catalogo);

INSERT INTO veiculo_marcas_catalogo (nome) VALUES
  ('Audi'), ('BMW'), ('BYD'), ('Caoa Chery'), ('Chevrolet'), ('Citroen'),
  ('Fiat'), ('Ford'), ('GWM'), ('Honda'), ('Hyundai'), ('Jeep'), ('Kia'),
  ('Land Rover'), ('Mercedes-Benz'), ('Mitsubishi'), ('Nissan'), ('Peugeot'),
  ('RAM'), ('Renault'), ('Toyota'), ('Volkswagen'), ('Volvo')
ON DUPLICATE KEY UPDATE ativo = VALUES(ativo);

INSERT INTO veiculo_modelos_catalogo (id_marca, nome, categoria_economica, categoria_plus, categoria_pro)
SELECT m.id, x.modelo, 1, 0, 0
FROM veiculo_marcas_catalogo m
INNER JOIN (
  SELECT 'Chevrolet' marca, 'Onix' modelo UNION ALL
  SELECT 'Chevrolet', 'Onix Plus' UNION ALL
  SELECT 'Chevrolet', 'Tracker' UNION ALL
  SELECT 'Chevrolet', 'Spin' UNION ALL
  SELECT 'Chevrolet', 'Montana' UNION ALL
  SELECT 'Chevrolet', 'S10' UNION ALL
  SELECT 'Chevrolet', 'Cruze' UNION ALL
  SELECT 'Fiat', 'Argo' UNION ALL
  SELECT 'Fiat', 'Cronos' UNION ALL
  SELECT 'Fiat', 'Mobi' UNION ALL
  SELECT 'Fiat', 'Pulse' UNION ALL
  SELECT 'Fiat', 'Fastback' UNION ALL
  SELECT 'Fiat', 'Strada' UNION ALL
  SELECT 'Fiat', 'Toro' UNION ALL
  SELECT 'Fiat', 'Fiorino' UNION ALL
  SELECT 'Fiat', 'Palio' UNION ALL
  SELECT 'Fiat', 'Uno' UNION ALL
  SELECT 'Fiat', 'Grand Siena' UNION ALL
  SELECT 'Volkswagen', 'Polo' UNION ALL
  SELECT 'Volkswagen', 'Virtus' UNION ALL
  SELECT 'Volkswagen', 'Nivus' UNION ALL
  SELECT 'Volkswagen', 'T-Cross' UNION ALL
  SELECT 'Volkswagen', 'Taos' UNION ALL
  SELECT 'Volkswagen', 'Saveiro' UNION ALL
  SELECT 'Volkswagen', 'Jetta' UNION ALL
  SELECT 'Volkswagen', 'Gol' UNION ALL
  SELECT 'Volkswagen', 'Voyage' UNION ALL
  SELECT 'Volkswagen', 'Fox' UNION ALL
  SELECT 'Volkswagen', 'Up' UNION ALL
  SELECT 'Hyundai', 'HB20' UNION ALL
  SELECT 'Hyundai', 'HB20S' UNION ALL
  SELECT 'Hyundai', 'Creta' UNION ALL
  SELECT 'Hyundai', 'Tucson' UNION ALL
  SELECT 'Hyundai', 'Santa Fe' UNION ALL
  SELECT 'Toyota', 'Corolla' UNION ALL
  SELECT 'Toyota', 'Corolla Cross' UNION ALL
  SELECT 'Toyota', 'Yaris Hatch' UNION ALL
  SELECT 'Toyota', 'Yaris Sedan' UNION ALL
  SELECT 'Toyota', 'Hilux' UNION ALL
  SELECT 'Toyota', 'SW4' UNION ALL
  SELECT 'Toyota', 'RAV4' UNION ALL
  SELECT 'Toyota', 'Etios Hatch' UNION ALL
  SELECT 'Toyota', 'Etios Sedan' UNION ALL
  SELECT 'Honda', 'City Hatchback' UNION ALL
  SELECT 'Honda', 'City Sedan' UNION ALL
  SELECT 'Honda', 'Civic' UNION ALL
  SELECT 'Honda', 'HR-V' UNION ALL
  SELECT 'Honda', 'ZR-V' UNION ALL
  SELECT 'Honda', 'Fit' UNION ALL
  SELECT 'Honda', 'WR-V' UNION ALL
  SELECT 'Renault', 'Kwid' UNION ALL
  SELECT 'Renault', 'Stepway' UNION ALL
  SELECT 'Renault', 'Duster' UNION ALL
  SELECT 'Renault', 'Oroch' UNION ALL
  SELECT 'Renault', 'Kardian' UNION ALL
  SELECT 'Renault', 'Sandero' UNION ALL
  SELECT 'Renault', 'Logan' UNION ALL
  SELECT 'Nissan', 'Kicks' UNION ALL
  SELECT 'Nissan', 'Versa' UNION ALL
  SELECT 'Nissan', 'Sentra' UNION ALL
  SELECT 'Nissan', 'Frontier' UNION ALL
  SELECT 'Nissan', 'March' UNION ALL
  SELECT 'Jeep', 'Renegade' UNION ALL
  SELECT 'Jeep', 'Compass' UNION ALL
  SELECT 'Jeep', 'Commander' UNION ALL
  SELECT 'Peugeot', '208' UNION ALL
  SELECT 'Peugeot', '2008' UNION ALL
  SELECT 'Peugeot', '3008' UNION ALL
  SELECT 'Citroen', 'C3' UNION ALL
  SELECT 'Citroen', 'C3 Aircross' UNION ALL
  SELECT 'Citroen', 'Basalt' UNION ALL
  SELECT 'Ford', 'Territory' UNION ALL
  SELECT 'Ford', 'Ranger' UNION ALL
  SELECT 'Ford', 'Maverick' UNION ALL
  SELECT 'Ford', 'Ka' UNION ALL
  SELECT 'Ford', 'Fiesta' UNION ALL
  SELECT 'Ford', 'EcoSport' UNION ALL
  SELECT 'Ford', 'Focus' UNION ALL
  SELECT 'Mitsubishi', 'Outlander' UNION ALL
  SELECT 'Mitsubishi', 'Eclipse Cross' UNION ALL
  SELECT 'Mitsubishi', 'L200 Triton' UNION ALL
  SELECT 'Caoa Chery', 'Tiggo 5X' UNION ALL
  SELECT 'Caoa Chery', 'Tiggo 7' UNION ALL
  SELECT 'Caoa Chery', 'Tiggo 8' UNION ALL
  SELECT 'Caoa Chery', 'Arrizo 6' UNION ALL
  SELECT 'BYD', 'Dolphin' UNION ALL
  SELECT 'BYD', 'Dolphin Mini' UNION ALL
  SELECT 'BYD', 'Song Plus' UNION ALL
  SELECT 'BYD', 'Song Pro' UNION ALL
  SELECT 'BYD', 'Yuan Plus' UNION ALL
  SELECT 'BYD', 'Seal' UNION ALL
  SELECT 'GWM', 'Haval H6' UNION ALL
  SELECT 'GWM', 'Ora 03' UNION ALL
  SELECT 'Kia', 'Sportage' UNION ALL
  SELECT 'Kia', 'Stonic' UNION ALL
  SELECT 'Kia', 'Cerato' UNION ALL
  SELECT 'Mercedes-Benz', 'Classe A' UNION ALL
  SELECT 'Mercedes-Benz', 'Classe C' UNION ALL
  SELECT 'Mercedes-Benz', 'GLA' UNION ALL
  SELECT 'Mercedes-Benz', 'GLB' UNION ALL
  SELECT 'BMW', 'Serie 1' UNION ALL
  SELECT 'BMW', 'Serie 3' UNION ALL
  SELECT 'BMW', 'X1' UNION ALL
  SELECT 'BMW', 'X3' UNION ALL
  SELECT 'Audi', 'A3' UNION ALL
  SELECT 'Audi', 'A4' UNION ALL
  SELECT 'Audi', 'Q3' UNION ALL
  SELECT 'Audi', 'Q5' UNION ALL
  SELECT 'Volvo', 'XC40' UNION ALL
  SELECT 'Volvo', 'XC60' UNION ALL
  SELECT 'Volvo', 'EX30' UNION ALL
  SELECT 'Land Rover', 'Discovery Sport' UNION ALL
  SELECT 'Land Rover', 'Range Rover Evoque' UNION ALL
  SELECT 'RAM', 'Rampage' UNION ALL
  SELECT 'RAM', '1500'
) x ON x.marca = m.nome
ON DUPLICATE KEY UPDATE categoria_economica = VALUES(categoria_economica);

INSERT INTO veiculo_cores_catalogo (nome) VALUES
  ('Branco'), ('Preto'), ('Prata'), ('Cinza'), ('Grafite'), ('Vermelho'),
  ('Azul'), ('Verde'), ('Amarelo'), ('Bege'), ('Marrom'), ('Laranja'),
  ('Dourado'), ('Vinho')
ON DUPLICATE KEY UPDATE ativo = VALUES(ativo);

UPDATE veiculos v
INNER JOIN veiculo_marcas_catalogo m ON LOWER(m.nome) = LOWER(v.marca)
SET v.id_marca_catalogo = m.id
WHERE v.id_marca_catalogo IS NULL;

UPDATE veiculos v
INNER JOIN veiculo_modelos_catalogo mo
  ON LOWER(mo.nome) = LOWER(v.modelo)
 AND mo.id_marca = v.id_marca_catalogo
SET v.id_modelo_catalogo = mo.id
WHERE v.id_modelo_catalogo IS NULL;

UPDATE veiculos v
INNER JOIN veiculo_cores_catalogo c ON LOWER(c.nome) = LOWER(v.cor)
SET v.id_cor_catalogo = c.id
WHERE v.id_cor_catalogo IS NULL;
