-- Add payment verification columns to device_subscriptions table
ALTER TABLE device_subscriptions 
ADD COLUMN IF NOT EXISTS razorpay_payment_id VARCHAR(255) NULL AFTER status,
ADD COLUMN IF NOT EXISTS razorpay_order_id VARCHAR(255) NULL AFTER razorpay_payment_id,
ADD COLUMN IF NOT EXISTS start_date TIMESTAMP NULL AFTER end_date,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

-- Add payment gateway columns to payment_transactions table
ALTER TABLE payment_transactions 
ADD COLUMN IF NOT EXISTS gateway_response JSON NULL AFTER gateway_transaction_id,
ADD COLUMN IF NOT EXISTS processed_at TIMESTAMP NULL AFTER gateway_response,
ADD COLUMN IF NOT EXISTS metadata JSON NULL AFTER processed_at;

-- Show updated schema
SELECT 'device_subscriptions columns:' as info;
DESCRIBE device_subscriptions;

SELECT 'payment_transactions columns:' as info;
DESCRIBE payment_transactions;