CREATE TABLE IF NOT EXISTS vehicle_makes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_models (
id INT AUTO_INCREMENT PRIMARY KEY,
make_id INT NOT NULL,
name VARCHAR(120) NOT NULL,
UNIQUE KEY uniq_make_model (make_id, name),
CONSTRAINT fk_models_make
FOREIGN KEY (make_id) REFERENCES vehicle_makes(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed common makes
INSERT IGNORE INTO vehicle_makes (name) VALUES
('Ford'),('Jeep'),('Honda'),('Toyota'),('Chevrolet'),('Nissan'),('Hyundai'),('Kia'),
('Dodge'),('RAM'),('GMC'),('BMW'),('Mercedes-Benz'),('Audi'),('Subaru'),('Volkswagen');

-- Seed a few models (add more anytime)
INSERT IGNORE INTO vehicle_models (make_id, name)
SELECT m.id, x.model
FROM vehicle_makes m
JOIN (
SELECT 'Ford' make, 'F-150' model UNION ALL
SELECT 'Ford', 'Mustang' UNION ALL
SELECT 'Ford', 'Explorer' UNION ALL
SELECT 'Jeep', 'Wrangler' UNION ALL
SELECT 'Jeep', 'Grand Cherokee' UNION ALL
SELECT 'Honda', 'Civic' UNION ALL
SELECT 'Honda', 'Accord' UNION ALL
SELECT 'Honda', 'CR-V' UNION ALL
SELECT 'Toyota', 'Camry' UNION ALL
SELECT 'Toyota', 'Corolla' UNION ALL
SELECT 'Chevrolet', 'Silverado' UNION ALL
SELECT 'Nissan', 'Altima'
) x ON x.make = m.name;