CREATE TABLE IF NOT EXISTS vehicle_years (
id INT AUTO_INCREMENT PRIMARY KEY,
year INT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_year (year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_makes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_make (name)
) 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,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_make_model (make_id, name),
KEY idx_make (make_id),
CONSTRAINT fk_models_make FOREIGN KEY (make_id)
REFERENCES vehicle_makes(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS engine_sizes (
id INT AUTO_INCREMENT PRIMARY KEY,
size VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_size (size)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

7641de9c897eddcd8c12709df240366c

c55eb46b4f1af21eef377ff311621cf7