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;