-- Migration script to add payment_id column to usdt_orders table
-- Run this script to fix the payment_id issue

-- Add payment_id column to usdt_orders table
ALTER TABLE usdt_orders ADD COLUMN payment_id VARCHAR(100) AFTER order_id;

-- Add index for payment_id for better performance
ALTER TABLE usdt_orders ADD INDEX idx_payment_id (payment_id);

-- Update existing records to extract payment_id from payment_address if possible
UPDATE usdt_orders 
SET payment_id = SUBSTRING_INDEX(SUBSTRING_INDEX(payment_address, 'iid=', -1), '&', 1)
WHERE payment_id IS NULL 
AND payment_address IS NOT NULL 
AND payment_address LIKE '%iid=%';

-- Show the results
SELECT 
    order_id,
    payment_id,
    payment_address,
    status,
    created_at
FROM usdt_orders 
ORDER BY created_at DESC 
LIMIT 10;