-- Add trip_expenses to track driver-added expenses per trip.
CREATE TABLE IF NOT EXISTS trip_expenses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transport_owner_id INT UNSIGNED NOT NULL,
  trip_id INT UNSIGNED NOT NULL,
  driver_id INT UNSIGNED NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_te_owner FOREIGN KEY (transport_owner_id) REFERENCES transport_owners (id) ON DELETE CASCADE,
  CONSTRAINT fk_te_trip FOREIGN KEY (trip_id) REFERENCES fleet_trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_te_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers (id) ON DELETE SET NULL,
  KEY idx_te_trip (trip_id),
  KEY idx_te_owner (transport_owner_id)
);