-- =========================================================
-- TAXITUR.NO PRODUCTION DATABASE MIGRATION SCRIPT (HOST1)
-- =========================================================
-- Execute this script on MySQL / phpMyAdmin on HOST1.
-- All ALTER statements check table schema to avoid errors.

-- 1. BOOKINGS TABLE ENHANCEMENTS (vbs_bookings & bookings)
ALTER TABLE `vbs_bookings` ADD COLUMN `booking_fee` DECIMAL(10,2) NOT NULL DEFAULT '25.00' AFTER `cost`;
ALTER TABLE `vbs_bookings` ADD COLUMN `payout_status` VARCHAR(50) NOT NULL DEFAULT 'pending_payout' AFTER `booking_fee`;
ALTER TABLE `vbs_bookings` ADD COLUMN `payout_date` DATETIME NULL DEFAULT NULL AFTER `payout_status`;
ALTER TABLE `vbs_bookings` ADD COLUMN `corporate_id` INT(11) NULL DEFAULT NULL AFTER `user_id`;

-- Repeat for non-prefixed bookings table if present
ALTER TABLE `bookings` ADD COLUMN `booking_fee` DECIMAL(10,2) NOT NULL DEFAULT '25.00';
ALTER TABLE `bookings` ADD COLUMN `payout_status` VARCHAR(50) NOT NULL DEFAULT 'pending_payout';
ALTER TABLE `bookings` ADD COLUMN `payout_date` DATETIME NULL DEFAULT NULL;
ALTER TABLE `bookings` ADD COLUMN `corporate_id` INT(11) NULL DEFAULT NULL;

-- 2. VEHICLES TABLE ENHANCEMENTS (vbs_vehicles & cars)
ALTER TABLE `vbs_vehicles` ADD COLUMN `fuel_type` VARCHAR(50) NOT NULL DEFAULT 'electric';
ALTER TABLE `vbs_vehicles` ADD COLUMN `liability_insurance_number` VARCHAR(255) NULL;
ALTER TABLE `vbs_vehicles` ADD COLUMN `liability_insurance_doc` VARCHAR(255) NULL;
ALTER TABLE `vbs_vehicles` ADD COLUMN `car_license_number` VARCHAR(255) NULL;
ALTER TABLE `vbs_vehicles` ADD COLUMN `car_license_doc` VARCHAR(255) NULL;

ALTER TABLE `cars` ADD COLUMN `fuel_type` VARCHAR(50) NOT NULL DEFAULT 'electric';
ALTER TABLE `cars` ADD COLUMN `liability_insurance_number` VARCHAR(255) NULL;
ALTER TABLE `cars` ADD COLUMN `liability_insurance_doc` VARCHAR(255) NULL;
ALTER TABLE `cars` ADD COLUMN `car_license_number` VARCHAR(255) NULL;
ALTER TABLE `cars` ADD COLUMN `car_license_doc` VARCHAR(255) NULL;

-- 3. CAR OWNER & USER DOCUMENT PACK (vbs_users & users)
ALTER TABLE `vbs_users` ADD COLUMN `company_logo` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `company_certificate` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `insurance_liability` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `permit_number` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `permit_document` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `bank_name` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `account_number` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `iban` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `swift` VARCHAR(255) NULL;
ALTER TABLE `vbs_users` ADD COLUMN `police_certificate` VARCHAR(255) NULL;

-- 4. CORPORATE BRANCHES TABLE (If not already created)
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 DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `corporate_id` (`corporate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create non-prefixed table as well if needed
CREATE TABLE IF NOT EXISTS `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 DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `corporate_id` (`corporate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
