-- Booking enhancements: client requirements (name plate, trip types, multi-stop, km+minute pricing)
-- Run once on local/production DB

ALTER TABLE `vbs_bookings`
  ADD COLUMN IF NOT EXISTS `trip_type` ENUM('transfer','day_trip','sightseeing') NOT NULL DEFAULT 'transfer' AFTER `package_type`,
  ADD COLUMN IF NOT EXISTS `pickup_name_plate` VARCHAR(255) NULL DEFAULT NULL AFTER `pick_point`,
  ADD COLUMN IF NOT EXISTS `special_requests` TEXT NULL AFTER `info_to_drivers`,
  ADD COLUMN IF NOT EXISTS `duration_hours` DECIMAL(5,1) NULL DEFAULT NULL AFTER `total_time`,
  ADD COLUMN IF NOT EXISTS `journey_minutes` INT NULL DEFAULT NULL AFTER `duration_hours`,
  ADD COLUMN IF NOT EXISTS `waypoints_json` TEXT NULL AFTER `journey_minutes`,
  ADD COLUMN IF NOT EXISTS `stop_waiting_cost` DECIMAL(10,2) NULL DEFAULT 0 AFTER `waiting_cost`;

ALTER TABLE `vbs_vehicle_categories`
  ADD COLUMN IF NOT EXISTS `price_per_minute` DECIMAL(10,2) NULL DEFAULT NULL AFTER `price`,
  ADD COLUMN IF NOT EXISTS `price_per_hour` DECIMAL(10,2) NULL DEFAULT NULL AFTER `price_per_minute`;

-- Default minute/hour rates from existing km price where missing
UPDATE `vbs_vehicle_categories`
SET `price_per_minute` = ROUND(GREATEST(COALESCE(`price`, 1) / 4, 1), 2)
WHERE `price_per_minute` IS NULL;

UPDATE `vbs_vehicle_categories`
SET `price_per_hour` = ROUND(GREATEST(COALESCE(`day_rate`, 0) / 8, COALESCE(`price_per_minute`, 1) * 60), 2)
WHERE `price_per_hour` IS NULL OR `price_per_hour` = 0;
