-- Post-rename car rental database updates.
-- Run after the main rename migration when tables are already named booking/vehicle/customer.

USE `naingwinaung93_car_rental`;

-- Keep deleted booking history compatible with hourly bookings.
ALTER TABLE `booking`
  ADD COLUMN IF NOT EXISTS `rental_mode` varchar(20) NOT NULL DEFAULT 'daily' AFTER `remark`,
  ADD COLUMN IF NOT EXISTS `pickup_datetime` varchar(50) DEFAULT NULL AFTER `rental_mode`,
  ADD COLUMN IF NOT EXISTS `return_datetime` varchar(50) DEFAULT NULL AFTER `pickup_datetime`,
  ADD COLUMN IF NOT EXISTS `rental_hours` decimal(10,2) NOT NULL DEFAULT 0 AFTER `return_datetime`;

ALTER TABLE `booking_detail`
  ADD COLUMN IF NOT EXISTS `description` text DEFAULT NULL AFTER `date`,
  ADD COLUMN IF NOT EXISTS `rate_mode` varchar(20) NOT NULL DEFAULT 'daily' AFTER `description`,
  ADD COLUMN IF NOT EXISTS `billing_units` decimal(10,2) NOT NULL DEFAULT 1 AFTER `rate_mode`,
  ADD COLUMN IF NOT EXISTS `unit_rate` decimal(10,2) DEFAULT NULL AFTER `billing_units`;

ALTER TABLE `booking_delete`
  ADD COLUMN IF NOT EXISTS `rental_mode` varchar(20) NOT NULL DEFAULT 'daily' AFTER `remark`,
  ADD COLUMN IF NOT EXISTS `pickup_datetime` varchar(50) DEFAULT NULL AFTER `rental_mode`,
  ADD COLUMN IF NOT EXISTS `return_datetime` varchar(50) DEFAULT NULL AFTER `pickup_datetime`,
  ADD COLUMN IF NOT EXISTS `rental_hours` decimal(10,2) NOT NULL DEFAULT 0 AFTER `return_datetime`;

ALTER TABLE `booking_detail_delete`
  ADD COLUMN IF NOT EXISTS `description` text DEFAULT NULL AFTER `date`,
  ADD COLUMN IF NOT EXISTS `rate_mode` varchar(20) NOT NULL DEFAULT 'daily' AFTER `description`,
  ADD COLUMN IF NOT EXISTS `billing_units` decimal(10,2) NOT NULL DEFAULT 1 AFTER `rate_mode`,
  ADD COLUMN IF NOT EXISTS `unit_rate` decimal(10,2) DEFAULT NULL AFTER `billing_units`;

-- Helpful indexes for availability, booking lists, and deposits.
ALTER TABLE `booking`
  ADD INDEX IF NOT EXISTS `idx_booking_vehicle_status` (`vehicle_no`, `picked_up`, `returned`),
  ADD INDEX IF NOT EXISTS `idx_booking_no` (`booking_no`);

ALTER TABLE `booking_detail`
  ADD INDEX IF NOT EXISTS `idx_booking_detail_booking_date` (`booking_no`, `date`);

ALTER TABLE `booking_delete`
  ADD INDEX IF NOT EXISTS `idx_booking_delete_no` (`booking_no`);

ALTER TABLE `booking_detail_delete`
  ADD INDEX IF NOT EXISTS `idx_booking_detail_delete_booking_date` (`booking_no`, `date`);

ALTER TABLE `deposit`
  ADD INDEX IF NOT EXISTS `idx_deposit_booking_vehicle` (`booking_no`, `vehicle_no`),
  ADD INDEX IF NOT EXISTS `idx_deposit_pickup` (`pickup_id`);

ALTER TABLE `other_service_charges`
  ADD INDEX IF NOT EXISTS `idx_osc_pickup` (`pickup_id`),
  ADD INDEX IF NOT EXISTS `idx_osc_vehicle` (`vehicle_no`);
