-- Add fields for inspection confirmation and cancellation tracking
-- Run this migration to support the new inspection workflow

ALTER TABLE `bookings` 
ADD COLUMN IF NOT EXISTS `tenant_inspection_confirmed` TINYINT(1) DEFAULT 0 COMMENT 'Tenant confirms inspection happened',
ADD COLUMN IF NOT EXISTS `landlord_inspection_confirmed` TINYINT(1) DEFAULT 0 COMMENT 'Landlord confirms inspection happened',
ADD COLUMN IF NOT EXISTS `inspection_confirmed_at` DATETIME DEFAULT NULL COMMENT 'When both parties confirmed',
ADD COLUMN IF NOT EXISTS `payment_deadline` DATETIME DEFAULT NULL COMMENT '24-hour deadline for final payment after inspection',
ADD COLUMN IF NOT EXISTS `cancellation_reason` TEXT DEFAULT NULL COMMENT 'Reason for cancellation',
ADD COLUMN IF NOT EXISTS `cancelled_by` INT DEFAULT NULL COMMENT 'User ID who cancelled',
ADD COLUMN IF NOT EXISTS `cancelled_at` DATETIME DEFAULT NULL COMMENT 'When booking was cancelled',
ADD COLUMN IF NOT EXISTS `refund_amount` DECIMAL(10,2) DEFAULT NULL COMMENT 'Amount refunded to tenant',
ADD COLUMN IF NOT EXISTS `refund_fee` DECIMAL(10,2) DEFAULT NULL COMMENT 'Fee deducted from refund',
ADD COLUMN IF NOT EXISTS `refund_processed` TINYINT(1) DEFAULT 0 COMMENT 'Whether refund has been processed',
ADD COLUMN IF NOT EXISTS `refund_processed_at` DATETIME DEFAULT NULL COMMENT 'When refund was processed';

-- Add index for payment deadline queries (for cron job)
CREATE INDEX IF NOT EXISTS `idx_payment_deadline` ON `bookings` (`payment_deadline`, `status`);

-- Add index for cancelled bookings
CREATE INDEX IF NOT EXISTS `idx_cancelled` ON `bookings` (`cancelled_at`, `status`);