-- Create scheduled_tasks table for delayed status updates
CREATE TABLE IF NOT EXISTS `scheduled_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_type` varchar(50) NOT NULL,
  `reference_id` int(11) NOT NULL,
  `scheduled_at` datetime NOT NULL,
  `status` enum('pending','completed','failed') DEFAULT 'pending',
  `task_data` text,
  `error_message` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_task_type` (`task_type`),
  KEY `idx_status` (`status`),
  KEY `idx_scheduled_at` (`scheduled_at`),
  KEY `idx_reference_id` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add payment_completed_at column to bookings table if not exists
ALTER TABLE `bookings` 
ADD COLUMN IF NOT EXISTS `payment_completed_at` datetime DEFAULT NULL AFTER `status`,
ADD COLUMN IF NOT EXISTS `activated_at` datetime DEFAULT NULL AFTER `payment_completed_at`;