-- Dashboard Performance Optimization
-- Add indexes to speed up dashboard queries

-- 1. Index for create_orders table (most important)
ALTER TABLE `create_orders` 
ADD INDEX IF NOT EXISTS `idx_user_date_status` (`user_id`, `date`, `txn_status`);

-- 2. Index for date-based queries
ALTER TABLE `create_orders` 
ADD INDEX IF NOT EXISTS `idx_date_status` (`date`, `txn_status`);

-- 3. Index for user-based queries
ALTER TABLE `create_orders` 
ADD INDEX IF NOT EXISTS `idx_user_status` (`user_id`, `txn_status`);

-- 4. Index for payout_requests table
ALTER TABLE `payout_requests` 
ADD INDEX IF NOT EXISTS `idx_user_date_status` (`user`, `created_at`, `status`);

-- 5. Index for callback_logs table (if used in dashboard)
ALTER TABLE `callback_logs` 
ADD INDEX IF NOT EXISTS `idx_order_created` (`order_id`, `created_at`);

-- 6. Index for upi_rotation_log table
ALTER TABLE `upi_rotation_log` 
ADD INDEX IF NOT EXISTS `idx_upi_assigned` (`upi_id`, `assigned_at`, `status`);

-- Verify indexes
SHOW INDEX FROM create_orders;
SHOW INDEX FROM payout_requests;

-- Analyze tables for better query optimization
ANALYZE TABLE create_orders;
ANALYZE TABLE payout_requests;
ANALYZE TABLE callback_logs;
ANALYZE TABLE upi_rotation_log;

SELECT 'Dashboard indexes created successfully!' as status;
