

CREATE TABLE IF NOT EXISTS vehicles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plate VARCHAR(20) NOT NULL UNIQUE,
  model VARCHAR(120) NOT NULL,
  color VARCHAR(40),
  year INT,
  status ENUM('active','maintenance','inactive') NOT NULL DEFAULT 'active',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS reservations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  vehicle_id INT NOT NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  reason VARCHAR(255),
  status ENUM('pending','approved','rejected','cancelled','finished') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_r_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_r_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
  INDEX idx_vehicle_time (vehicle_id, starts_at, ends_at)
);

-- veículos exemplo
INSERT IGNORE INTO vehicles (plate, model, color, year) VALUES
('ABC-1A23','Fiat Fiorino','Branco',2021),
('XYZ-9Z99','Chevrolet S10','Prata',2020);
