-- =====================================================================
-- TAXITUR.NO PRODUCTION DATABASE UPDATE SCRIPT
-- RUN THIS SCRIPT IN PHPMYADMIN / MYSQL WORKBENCH ON YOUR PRODUCTION SERVER
-- DATE: 2026-09-29
-- =====================================================================

-- 1. Update vbs_users table for Driver Compliance Docs & Customer Bank Refund Info
ALTER TABLE `vbs_users` 
  ADD COLUMN IF NOT EXISTS `license_back` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `driver_card_front` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `driver_card_back` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `driver_ticket_front` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `driver_ticket_back` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `vehicle_registration` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `insurance_certificate` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `bank_document` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `isps_course` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `avinor_pass` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `taxi_license` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `police_certificate` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `verification_status` VARCHAR(50) DEFAULT 'pending',
  ADD COLUMN IF NOT EXISTS `verification_note` TEXT NULL,
  ADD COLUMN IF NOT EXISTS `bank_name` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `account_number` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `iban` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `swift` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `card_last_4` VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS `refund_notes` TEXT NULL;

-- 2. Update vbs_corporate_customers table for Discount %, Customer # & Contact Person
ALTER TABLE `vbs_corporate_customers`
  ADD COLUMN IF NOT EXISTS `contact_person` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `customer_number` VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS `discount_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00;

-- 3. Update vbs_bookings table for Driver Assignment, GPS Lat/Lng & Payout Status
ALTER TABLE `vbs_bookings`
  ADD COLUMN IF NOT EXISTS `driver_id` INT(11) NULL DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `car_owner_id` INT(11) NULL DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `corporate_id` INT(11) NULL DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `pickup_latitude` VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS `pickup_longitude` VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS `driver_lat` VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS `driver_lng` VARCHAR(50) NULL,
  ADD COLUMN IF NOT EXISTS `payout_status` VARCHAR(50) DEFAULT 'pending_payout',
  ADD COLUMN IF NOT EXISTS `payout_date` DATETIME NULL;

-- 4. Update vbs_vehicle table for Plate Photo & Reg Number
ALTER TABLE `vbs_vehicle`
  ADD COLUMN IF NOT EXISTS `plate_photo` VARCHAR(255) NULL,
  ADD COLUMN IF NOT EXISTS `reg_number` VARCHAR(100) NULL;

-- 5. Create vbs_corporate_branches table for Corporate Branch Location Management
CREATE TABLE IF NOT EXISTS `vbs_corporate_branches` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `corporate_id` INT(11) NOT NULL,
  `branch_name` VARCHAR(255) NOT NULL,
  `branch_address` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `corporate_id` (`corporate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- End of migration script
