-- Migration: Add expires_at column to pending_orders table
-- This migration adds the expires_at column required for the 10-minute reservation mechanism
-- as described in the IPN Callback Integration Guide

-- Step 1: Add the column (nullable first for existing records)
ALTER TABLE `pending_orders`
  ADD COLUMN `expires_at` datetime NULL
  AFTER `created_at`;

-- Step 2: Update existing records to set expires_at based on created_at + 10 minutes
UPDATE `pending_orders`
SET `expires_at` = DATE_ADD(`created_at`, INTERVAL 10 MINUTE)
WHERE `expires_at` IS NULL;

-- Step 3: Make the column NOT NULL now that all records have values
ALTER TABLE `pending_orders`
  MODIFY COLUMN `expires_at` datetime NOT NULL;

-- Step 4: Add index for cleanup queries (as recommended in the guide)
ALTER TABLE `pending_orders`
  ADD INDEX `idx_expires_at` (`expires_at`);

-- Note: The application should set expires_at = NOW() + 10 minutes when creating new pending orders
-- Example: INSERT INTO pending_orders (..., expires_at) VALUES (..., DATE_ADD(NOW(), INTERVAL 10 MINUTE))