-- ============================================
-- FIX BOOKINGS TABLE - APPLY THIS MIGRATION
-- ============================================
-- This migration adds all required columns for the booking system to work
-- Run this in phpMyAdmin or MySQL command line
-- ============================================

-- Add new columns to bookings table
ALTER TABLE `bookings` 
ADD COLUMN IF NOT EXISTS `total_rent` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Total rent for the duration',
ADD COLUMN IF NOT EXISTS `security_deposit` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Security deposit amount (50% of total rent)',
ADD COLUMN IF NOT EXISTS `booking_fee` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Booking/processing fee',
ADD COLUMN IF NOT EXISTS `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Total amount to be paid',
ADD COLUMN IF NOT EXISTS `amount_paid` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Amount paid so far',
ADD COLUMN IF NOT EXISTS `balance` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Remaining balance',
ADD COLUMN IF NOT EXISTS `currency` VARCHAR(3) NOT NULL DEFAULT 'GHS' COMMENT 'Currency code',
ADD COLUMN IF NOT EXISTS `inspection_status` ENUM('pending','scheduled','completed','cancelled') DEFAULT 'pending' COMMENT 'Inspection status',
ADD COLUMN IF NOT EXISTS `inspection_type` ENUM('virtual','onsite') DEFAULT NULL COMMENT 'Type of inspection',
ADD COLUMN IF NOT EXISTS `inspection_feedback` TEXT DEFAULT NULL COMMENT 'Tenant feedback after inspection',
ADD COLUMN IF NOT EXISTS `inspection_satisfied` TINYINT(1) DEFAULT NULL COMMENT '1=satisfied, 0=not satisfied',
ADD COLUMN IF NOT EXISTS `refund_requested` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether refund was requested',
ADD COLUMN IF NOT EXISTS `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT 'Refund amount (deposit - 5% fee)',
ADD COLUMN IF NOT EXISTS `refund_status` ENUM('pending','processing','completed','failed') DEFAULT NULL COMMENT 'Refund status',
ADD COLUMN IF NOT EXISTS `refund_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'When refund was processed',
ADD COLUMN IF NOT EXISTS `full_payment_deadline` TIMESTAMP NULL DEFAULT NULL COMMENT '24 hours after inspection satisfaction',
ADD COLUMN IF NOT EXISTS `auto_refund_triggered` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether auto-refund was triggered',
ADD COLUMN IF NOT EXISTS `payment_gateway_id` INT(11) DEFAULT NULL COMMENT 'Payment gateway used',
ADD COLUMN IF NOT EXISTS `hostel_option_id` INT(11) DEFAULT NULL COMMENT 'Selected hostel option (for hostel/homestel rooms)',
ADD COLUMN IF NOT EXISTS `selected_occupancy` INT(11) DEFAULT NULL COMMENT 'Selected occupancy (for hostel/homestel)',
ADD COLUMN IF NOT EXISTS `notes` TEXT DEFAULT NULL COMMENT 'Additional notes';

-- Add indexes for better performance
ALTER TABLE `bookings` ADD INDEX IF NOT EXISTS `idx_payment_gateway` (`payment_gateway_id`);
ALTER TABLE `bookings` ADD INDEX IF NOT EXISTS `idx_inspection_status` (`inspection_status`);
ALTER TABLE `bookings` ADD INDEX IF NOT EXISTS `idx_refund_status` (`refund_status`);

-- Update status enum to include more states
ALTER TABLE `bookings` MODIFY COLUMN `status` ENUM(
    'pending',
    'pending_payment',
    'deposit_paid',
    'inspection_scheduled',
    'inspection_completed',
    'awaiting_full_payment',
    'payment_completed',
    'refund_requested',
    'refunded',
    'cancelled',
    'expired',
    'active',
    'completed',
    'confirmed'
) NOT NULL DEFAULT 'pending_payment';

-- Create booking status history table
CREATE TABLE IF NOT EXISTS `booking_status_history` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `booking_id` INT(11) NOT NULL,
  `old_status` VARCHAR(50) DEFAULT NULL,
  `new_status` VARCHAR(50) NOT NULL,
  `changed_by` INT(11) DEFAULT NULL COMMENT 'User ID who made the change',
  `reason` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `booking_id` (`booking_id`),
  KEY `changed_by` (`changed_by`),
  FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create inspection schedules table
CREATE TABLE IF NOT EXISTS `inspection_schedules` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `booking_id` INT(11) NOT NULL,
  `inspection_type` ENUM('virtual','onsite') NOT NULL,
  `scheduled_date` DATE NOT NULL,
  `scheduled_time` TIME NOT NULL,
  `meeting_link` VARCHAR(255) DEFAULT NULL COMMENT 'For virtual inspections',
  `meeting_location` TEXT DEFAULT NULL COMMENT 'For onsite inspections',
  `status` ENUM('scheduled','completed','cancelled','rescheduled') NOT NULL DEFAULT 'scheduled',
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `tenant_notes` TEXT DEFAULT NULL,
  `landlord_notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `booking_id` (`booking_id`),
  FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create refunds table
CREATE TABLE IF NOT EXISTS `refunds` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `booking_id` INT(11) NOT NULL,
  `transaction_id` INT(11) DEFAULT NULL COMMENT 'Original payment transaction',
  `refund_reference` VARCHAR(100) NOT NULL,
  `original_amount` DECIMAL(10,2) NOT NULL,
  `refund_fee` DECIMAL(10,2) NOT NULL COMMENT '5% processing fee',
  `refund_amount` DECIMAL(10,2) NOT NULL COMMENT 'Amount after fee deduction',
  `reason` ENUM('inspection_unsatisfied','payment_deadline_missed','booking_cancelled','other') NOT NULL,
  `reason_details` TEXT DEFAULT NULL,
  `status` ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  `gateway_refund_id` VARCHAR(255) DEFAULT NULL COMMENT 'Refund ID from payment gateway',
  `gateway_response` TEXT DEFAULT NULL COMMENT 'JSON response from gateway',
  `processed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `refund_reference` (`refund_reference`),
  KEY `booking_id` (`booking_id`),
  KEY `transaction_id` (`transaction_id`),
  FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create notifications table for booking updates
CREATE TABLE IF NOT EXISTS `booking_notifications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `booking_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `notification_type` VARCHAR(50) NOT NULL COMMENT 'e.g., deposit_received, inspection_reminder, payment_deadline',
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `sent_via` SET('email','sms','push','in_app') DEFAULT 'in_app',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `booking_id` (`booking_id`),
  KEY `user_id` (`user_id`),
  KEY `is_read` (`is_read`),
  FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SUCCESS MESSAGE
SELECT 'Migration completed successfully! Bookings table has been updated with all required columns.' AS Status;