-- Car rental schema rename migration.
-- Source structure: C:\Users\nwa\Downloads\car_rental.sql
-- Review carefully before running. The current PHP code must be updated to use these names.
--
-- Suggested backup first:
-- C:\xampp\mysql\bin\mysqldump.exe -h 127.0.0.1 -u root car_rental > C:\xampp\htdocs\car_rental\backup_car_rental_before_schema_rename.sql

USE `naingwinaung93_car_rental`;

RENAME TABLE
  `room` TO `vehicle`,
  `room_type` TO `vehicle_category`,
  `guest` TO `customer`,
  `checkin` TO `rental_pickup`,
  `checkin_detail` TO `rental_pickup_detail`,
  `checkin_guest` TO `rental_customer`,
  `check_out` TO `rental_return`,
  `check_out_detail` TO `rental_return_detail`;

ALTER TABLE `vehicle`
  CHANGE `room_number` `vehicle_no` varchar(50) DEFAULT NULL,
  CHANGE `room_type` `vehicle_category` varchar(100) DEFAULT NULL,
  CHANGE `bed_type` `vehicle_note` varchar(50) DEFAULT NULL,
  CHANGE `max_occupancy` `seats` int(11) DEFAULT NULL,
  CHANGE `room_rate` `rental_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_bed` `extra_driver` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_person` `extra_passenger` decimal(10,2) DEFAULT NULL,
  CHANGE `room_status` `vehicle_status` varchar(50) DEFAULT 'vacant';

ALTER TABLE `vehicle_category`
  CHANGE `room_type` `vehicle_category` varchar(100) DEFAULT NULL,
  CHANGE `room_rate` `rental_rate` int(11) DEFAULT 0,
  CHANGE `max_occupancy` `seats` int(11) DEFAULT 0,
  CHANGE `amenities` `features` text DEFAULT NULL,
  CHANGE `extra_bed_rate` `extra_driver_rate` int(11) DEFAULT 0;

ALTER TABLE `customer`
  CHANGE `guest_name` `customer_name` varchar(50) DEFAULT NULL,
  CHANGE `ppnrc` `document_no` varchar(50) DEFAULT NULL;

ALTER TABLE `reservation`
  CHANGE `reservation_no` `booking_no` varchar(20) NOT NULL,
  CHANGE `guest_id` `customer_id` int(11) DEFAULT NULL,
  CHANGE `guest_name` `customer_name` varchar(100) DEFAULT NULL,
  CHANGE `room_type` `vehicle_category` varchar(100) NOT NULL,
  CHANGE `bed_type` `vehicle_note` varchar(100) NOT NULL,
  CHANGE `room_no` `vehicle_no` varchar(50) NOT NULL,
  CHANGE `adult` `drivers` int(11) NOT NULL DEFAULT 0,
  CHANGE `child` `passengers` int(11) NOT NULL DEFAULT 0,
  CHANGE `checkin` `picked_up` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `checkin_created_at` `pickup_created_at` varchar(50) NOT NULL DEFAULT '',
  CHANGE `checkout` `returned` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `checkout_created_at` `return_created_at` varchar(50) NOT NULL DEFAULT '';

ALTER TABLE `reservation_delete`
  CHANGE `reservation_no` `booking_no` varchar(20) NOT NULL,
  CHANGE `guest_id` `customer_id` int(11) DEFAULT NULL,
  CHANGE `guest_name` `customer_name` varchar(100) DEFAULT NULL,
  CHANGE `room_type` `vehicle_category` varchar(100) NOT NULL,
  CHANGE `bed_type` `vehicle_note` varchar(100) NOT NULL,
  CHANGE `room_no` `vehicle_no` varchar(50) NOT NULL,
  CHANGE `adult` `drivers` int(11) NOT NULL DEFAULT 0,
  CHANGE `child` `passengers` int(11) NOT NULL DEFAULT 0,
  CHANGE `checkin` `picked_up` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `checkin_created_at` `pickup_created_at` varchar(50) NOT NULL DEFAULT '',
  CHANGE `checkout` `returned` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `checkout_created_at` `return_created_at` varchar(50) NOT NULL DEFAULT '';

ALTER TABLE `reservation_detail`
  CHANGE `reservation_no` `booking_no` varchar(50) NOT NULL DEFAULT '',
  CHANGE `room_rate` `rental_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_bed` `extra_driver` int(11) DEFAULT 0,
  CHANGE `extra_bed_rate` `extra_driver_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_person` `extra_passenger` int(11) DEFAULT 0,
  CHANGE `extra_person_rate` `extra_passenger_rate` decimal(10,2) DEFAULT NULL;

ALTER TABLE `reservation_detail_delete`
  CHANGE `reservation_no` `booking_no` varchar(50) NOT NULL DEFAULT '',
  CHANGE `room_rate` `rental_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_bed` `extra_driver` int(11) DEFAULT 0,
  CHANGE `extra_bed_rate` `extra_driver_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_person` `extra_passenger` int(11) DEFAULT 0,
  CHANGE `extra_person_rate` `extra_passenger_rate` decimal(10,2) DEFAULT NULL;

ALTER TABLE `rental_pickup`
  CHANGE `reservation_no` `booking_no` varchar(50) DEFAULT NULL,
  CHANGE `guest_id` `customer_id` int(11) DEFAULT NULL,
  CHANGE `guest_name` `customer_name` varchar(200) DEFAULT NULL,
  CHANGE `room_type` `vehicle_category` varchar(200) DEFAULT NULL,
  CHANGE `bed_type` `vehicle_note` varchar(200) DEFAULT NULL,
  CHANGE `room_no` `vehicle_no` varchar(100) DEFAULT NULL,
  CHANGE `adult` `drivers` int(11) DEFAULT NULL,
  CHANGE `child` `passengers` int(11) DEFAULT NULL;

ALTER TABLE `rental_pickup_detail`
  CHANGE `checkin_id` `pickup_id` int(11) NOT NULL DEFAULT 0,
  CHANGE `room_rate` `rental_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_bed` `extra_driver` int(11) DEFAULT NULL,
  CHANGE `extra_bed_rate` `extra_driver_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `extra_person` `extra_passenger` int(11) DEFAULT NULL,
  CHANGE `extra_person_rate` `extra_passenger_rate` decimal(10,2) DEFAULT NULL,
  CHANGE `checkout_date` `return_date` varchar(50) DEFAULT NULL;

ALTER TABLE `rental_customer`
  CHANGE `checkin_id` `pickup_id` int(11) DEFAULT NULL,
  CHANGE `guest_id` `customer_id` int(11) DEFAULT NULL;

ALTER TABLE `rental_return`
  CHANGE `checkin_id` `pickup_id` int(11) DEFAULT 0,
  CHANGE `reservation_no` `booking_no` varchar(50) DEFAULT NULL,
  CHANGE `room_no` `vehicle_no` varchar(50) DEFAULT NULL,
  CHANGE `guest_id` `customer_id` int(11) DEFAULT 0,
  CHANGE `guest_name` `customer_name` varchar(50) DEFAULT NULL,
  CHANGE `checkin_date` `pickup_date` varchar(50) DEFAULT NULL,
  CHANGE `checkout_date` `return_date` varchar(50) DEFAULT NULL;

ALTER TABLE `rental_return_detail`
  CHANGE `checkin_id` `pickup_id` int(11) DEFAULT 0,
  CHANGE `checkout_id` `return_id` int(11) DEFAULT 0;

ALTER TABLE `deposit`
  CHANGE `checkin_id` `pickup_id` int(11) DEFAULT 0,
  CHANGE `reservation_no` `booking_no` varchar(50) DEFAULT NULL,
  CHANGE `room_no` `vehicle_no` varchar(50) DEFAULT NULL,
  CHANGE `checkout_paid` `return_paid` int(11) NOT NULL DEFAULT 0;

ALTER TABLE `deposit_copy`
  CHANGE `checkin_id` `pickup_id` int(11) DEFAULT 0,
  CHANGE `reservation_no` `booking_no` varchar(50) DEFAULT NULL,
  CHANGE `room_no` `vehicle_no` varchar(50) DEFAULT NULL,
  CHANGE `checkout_paid` `return_paid` int(11) NOT NULL DEFAULT 0;

ALTER TABLE `other_service_charges`
  CHANGE `checkin_id` `pickup_id` int(11) DEFAULT NULL,
  CHANGE `room_no` `vehicle_no` varchar(50) DEFAULT NULL,
  CHANGE `check_out` `returned` int(11) DEFAULT 0;

ALTER TABLE `validate`
  CHANGE `checkin_id` `pickup_id` int(11) NOT NULL DEFAULT 0,
  CHANGE `reservation_no` `booking_no` varchar(50) NOT NULL DEFAULT '',
  CHANGE `room_type` `vehicle_category` varchar(100) DEFAULT NULL,
  CHANGE `bed_type` `vehicle_note` varchar(100) DEFAULT NULL,
  CHANGE `room_no` `vehicle_no` varchar(100) DEFAULT NULL,
  CHANGE `room_rate` `rental_rate` decimal(20,2) DEFAULT NULL,
  CHANGE `extra_bed` `extra_driver` int(11) DEFAULT 0,
  CHANGE `extra_bed_rate` `extra_driver_rate` decimal(20,2) DEFAULT NULL,
  CHANGE `extra_person` `extra_passenger` int(11) DEFAULT 0,
  CHANGE `extra_person_rate` `extra_passenger_rate` decimal(20,2) DEFAULT NULL,
  CHANGE `status` `rental_status` varchar(50) DEFAULT NULL,
  CHANGE `check_out_date` `return_date` varchar(50) DEFAULT NULL;

ALTER TABLE `userrole`
  CHANGE `room` `vehicle` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `reservation` `booking` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `upcoming_arrivals` `upcoming_pickups` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `arrival` `pickup` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `occupied_list` `active_rentals` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `deleted_reservation_list` `deleted_booking_list` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `guest_report` `customer_report` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `arrival_report` `pickup_report` tinyint(4) NOT NULL DEFAULT 0,
  CHANGE `departure_report` `return_report` tinyint(4) NOT NULL DEFAULT 0;

RENAME TABLE
  `reservation` TO `booking`,
  `reservation_detail` TO `booking_detail`,
  `reservation_delete` TO `booking_delete`,
  `reservation_detail_delete` TO `booking_detail_delete`;

ALTER TABLE `booking`
  ADD COLUMN IF NOT EXISTS `rental_mode` varchar(20) NOT NULL DEFAULT 'daily' AFTER `remark`,
  ADD COLUMN IF NOT EXISTS `discount_amount` decimal(20,2) NOT NULL DEFAULT 0 AFTER `folio_no`,
  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 `discount_amount` decimal(20,2) NOT NULL DEFAULT 0 AFTER `folio_no`,
  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`;
