/**
 * WhatsApp Cloud User Controller - ENHANCED ROBUST VERSION
 */

const BaseController = require('./BaseController');
const { pool } = require('../config/database');
const { logger } = require('../config/logger');
const axios = require('axios');
const InvoiceRepository = require('../repositories/InvoiceRepository');
const StripeService = require('../services/stripeService');
const PayPalService = require('../services/paypalService');
const { getWhatsAppService } = require('../services/WhatsAppService');
const { sendMessage: queueMessage } = require('../services/MessageService');
const multer = require('multer');
const path = require('path');
const fs = require('fs');

// Configure multer for file uploads
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    const tenantId = req.user.tenantId || req.tenantId;
    const uploadPath = path.join(__dirname, '../uploads', `tenant_${tenantId}`);
    
    // Ensure directory exists
    if (!fs.existsSync(uploadPath)) {
      fs.mkdirSync(uploadPath, { recursive: true });
    }
    
    cb(null, uploadPath);
  },
  filename: function (req, file, cb) {
    // Generate unique filename with timestamp and random string
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    const extension = path.extname(file.originalname);
    cb(null, file.fieldname + '-' + uniqueSuffix + extension);
  }
});

const upload = multer({ 
  storage: storage,
  limits: {
    fileSize: 50 * 1024 * 1024 // 50MB limit
  },
  fileFilter: function (req, file, cb) {
    // Allow all file types for maximum flexibility
    cb(null, true);
  }
});

class WhatsAppCloudUserController extends BaseController {
  /**
   * Get conversations for current user - ENHANCED VERSION
   * Supports both WhatsApp Cloud and WhatsApp Web sources
   * Only shows unclaimed or conversations claimed by this user
   * Implements proper store/department filtering
   * GET /api/user/whatsapp-cloud/conversations
   */
  static async getConversations(req, res) {
    try {
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;
      let storeId = req.user.store_id || null;
      let departmentId = req.user.department_id || null;
      const storeName = req.user.store || null;
      const departmentName = req.user.department || null;
      const accountId = req.query.accountId;
      const source = req.query.source === 'whatsapp_web' ? 'whatsapp_web' : 'whatsapp_cloud';

      console.log('🔍 USER requesting conversations - DETAILED:', {
        userId,
        tenantId,
        storeId,
        departmentId,
        storeName,
        departmentName,
        userRole: req.user.role,
        source,
        accountId,
        userFullData: {
          id: req.user.id,
          name: req.user.name,
          email: req.user.email,
          role: req.user.role,
          store_id: req.user.store_id,
          department_id: req.user.department_id,
          store: req.user.store,
          department: req.user.department
        }
      });
      
      logger.info('🔍 USER requesting conversations - DETAILED', {
        userId,
        tenantId,
        storeId,
        departmentId,
        storeName,
        departmentName,
        userRole: req.user.role,
        source,
        accountId,
        userFullData: {
          id: req.user.id,
          name: req.user.name,
          email: req.user.email,
          role: req.user.role,
          store_id: req.user.store_id,
          department_id: req.user.department_id,
          store: req.user.store,
          department: req.user.department
        }
      });

      if (!tenantId) {
        logger.error('Tenant ID not found in request', {
          userId,
          user: req.user
        });
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      // Check if this accountId belongs to an Evolution instance
      let isEvolutionInstance = false;
      if (accountId) {
        try {
          const [evolutionCheck] = await pool.execute(
            'SELECT id FROM evolution_instances WHERE id = ? AND tenant_id = ?',
            [accountId, tenantId]
          );
          isEvolutionInstance = evolutionCheck.length > 0;
          logger.info('✅ Account type check for USER', { accountId, tenantId, isEvolutionInstance });
        } catch (err) {
          logger.warn('Error checking if account is Evolution instance', { error: err.message });
        }
      }

      // If it's an Evolution API account, get conversations from conversations table
      if (isEvolutionInstance) {
        const instanceId = parseInt(accountId);
        
        logger.info('📱 USER fetching Evolution API conversations', { 
          tenantId, 
          instanceId, 
          accountId,
          userId,
          userName: req.user.name,
          userStore: storeName || storeId,
          userDepartment: departmentName || departmentId
        });
        
        // Get user's store and department IDs
        if (!storeId && storeName) {
          const [storeRows] = await pool.execute(
            'SELECT id FROM stores WHERE tenant_id = ? AND name = ? LIMIT 1',
            [tenantId, storeName]
          );
          storeId = storeRows[0]?.id;
        }
        
        if (!departmentId && departmentName) {
          const [deptRows] = await pool.execute(
            'SELECT id FROM departments WHERE tenant_id = ? AND name = ? LIMIT 1',
            [tenantId, departmentName]
          );
          departmentId = deptRows[0]?.id;
        }
        
        console.log('✅ Resolved user store/department IDs for Evolution:', {
          userId,
          storeId,
          storeName,
          departmentId,
          departmentName,
          hasStore: !!(storeId || storeName),
          hasDepartment: !!(departmentId || departmentName)
        });
        
        logger.info('✅ Resolved user store/department IDs for Evolution', {
          userId,
          storeId,
          storeName,
          departmentId,
          departmentName,
          hasStore: !!(storeId || storeName),
          hasDepartment: !!(departmentId || departmentName)
        });
        
        // Build WHERE clause with user-specific filters
        let whereClause = 'WHERE c.tenant_id = ?';
        const queryParams = [accountId, instanceId, instanceId, tenantId];
        
        console.log('🔧 Building Evolution WHERE clause - INITIAL STATE:', {
          userId,
          tenantId,
          instanceId,
          initialWhereClause: whereClause,
          initialParamsCount: queryParams.length
        });
        
        logger.info('🔧 Building Evolution WHERE clause - INITIAL STATE', {
          userId,
          tenantId,
          instanceId,
          initialWhereClause: whereClause,
          initialParamsCount: queryParams.length
        });
        
        // Build filter conditions
        const conditions = [];
        
        // FOR EVOLUTION INSTANCES: Users can ONLY see conversations that were explicitly transferred to them
        // They CANNOT see new/unclaimed conversations
        // CRITICAL: A conversation is only visible if it has been TRANSFERRED (has transferred_at set)
        
        // Condition 1: Conversations transferred to user's store (must have transfer timestamp)
        if (storeId || storeName) {
          const storeValues = [];
          if (storeId) storeValues.push(String(storeId));
          if (storeName && !storeValues.includes(String(storeName))) {
            storeValues.push(String(storeName));
          }
          conditions.push(`(c.transferred_to_store IN (${storeValues.map(() => '?').join(', ')}) AND c.transferred_at IS NOT NULL)`);
          queryParams.push(...storeValues);
        }
        
        // Condition 2: Conversations transferred to user's department (must have transfer timestamp)
        if (departmentId || departmentName) {
          const deptValues = [];
          if (departmentId) deptValues.push(String(departmentId));
          if (departmentName && !deptValues.includes(String(departmentName))) {
            deptValues.push(String(departmentName));
          }
          conditions.push(`(c.transferred_to_department IN (${deptValues.map(() => '?').join(', ')}) AND c.transferred_at IS NOT NULL)`);
          queryParams.push(...deptValues);
        }
        
        // Condition 3: Conversations directly assigned to this user AND transferred (must have transfer timestamp)
        conditions.push('(c.assigned_user_id = ? AND c.transferred_at IS NOT NULL)');
        queryParams.push(userId);
        
        // Apply the OR conditions - user must have at least one match AND conversation must be transferred
        if (conditions.length > 0) {
          whereClause += ` AND (${conditions.join(' OR ')})`;
          
          console.log('📝 Applied USER Evolution conversation filters (TRANSFER REQUIRED):', {
            userId,
            conditions: conditions.length,
            requiresTransfer: true,
            finalWhereClause: whereClause,
            finalParams: queryParams
          });
          
          logger.info('📝 Applied USER Evolution conversation filters (TRANSFER REQUIRED)', {
            userId,
            conditions: conditions.length,
            requiresTransfer: true,
            whereClause
          });
        } else {
          // If user has no store/department and no direct assignment, show nothing
          whereClause += ' AND 1=0'; // No results
          
          console.log('📝 USER has no store/department/assignment - showing NO conversations:', {
            userId
          });
          
          logger.info('📝 USER has no store/department/assignment - showing NO conversations', {
            userId
          });
        }
        
        // Get conversations that have messages from this Evolution instance
        const query = `SELECT DISTINCT
                  c.id,
                  c.tenant_id,
                  c.phone_number as contact_phone,
                  c.contact_name,
                  c.last_message as last_message_text,
                  c.last_message_time,
                  'customer' as last_message_from,
                  0 as unread_count,
                  c.status,
                  c.assigned_user_id as claimed_by_user_id,
                  c.is_claimed,
                  c.claimed_at,
                  c.transferred_to_store,
                  c.transferred_to_department,
                  c.transferred_at,
                  c.transferred_by_user_id,
                  c.stage_id,
                  c.stage_id as pipeline_stage,
                  c.tags,
                  c.created_at,
                  c.updated_at,
                  u.name as claimed_by_name,
                  u.username as claimed_by_username,
                  COALESCE(u.store, s.name) as claimed_by_store,
                  COALESCE(u.department, d.name) as claimed_by_department,
                  'evolution' as source,
                  ? as account_id,
                  ? as instance_id
           FROM conversations c
           INNER JOIN whatsapp_messages wm ON c.id = wm.conversation_id AND wm.instance_id = ?
           LEFT JOIN users u ON c.assigned_user_id = u.id AND u.tenant_id = c.tenant_id
           LEFT JOIN stores s ON u.store_id = s.id
           LEFT JOIN departments d ON u.department_id = d.id
           ${whereClause}
           AND (c.remote_jid IS NULL OR c.remote_jid NOT LIKE '%@g.us%')
           ORDER BY c.last_message_time DESC`;
        
        console.log('🚀 Executing USER Evolution conversations query:', {
          tenantId,
          instanceId,
          userId,
          queryParamsCount: queryParams.length,
          fullQuery: query.substring(0, 500),
          params: queryParams
        });
        
        logger.info('🚀 Executing USER Evolution conversations query', {
          tenantId,
          instanceId,
          userId,
          queryParamsCount: queryParams.length,
          fullQuery: query,
          params: queryParams
        });
        
        const [conversations] = await pool.execute(query, queryParams);

        console.log('✅ USER Evolution API conversations retrieved - DETAILED:', { 
          tenantId, 
          instanceId,
          accountId,
          userId,
          count: conversations.length,
          conversationDetails: conversations.map(c => ({
            id: c.id,
            phone: c.contact_phone,
            assigned_user_id: c.claimed_by_user_id,
            transferred_to_store: c.transferred_to_store,
            transferred_to_department: c.transferred_to_department,
            transferred_at: c.transferred_at,
            hasTransfer: !!c.transferred_at
          }))
        });

        logger.info('✅ USER Evolution API conversations retrieved - DETAILED', { 
          tenantId, 
          instanceId,
          accountId,
          userId,
          count: conversations.length,
          conversationDetails: conversations.map(c => ({
            id: c.id,
            phone: c.contact_phone,
            assigned_user_id: c.claimed_by_user_id,
            transferred_to_store: c.transferred_to_store,
            transferred_to_department: c.transferred_to_department,
            transferred_at: c.transferred_at,
            hasTransfer: !!c.transferred_at
          }))
        });

        // Process tags to ensure they're always arrays
        const processedConversations = conversations.map(conv => {
          let tags = [];
          if (conv.tags) {
            try {
              tags = typeof conv.tags === 'string' ? JSON.parse(conv.tags) : conv.tags;
              if (!Array.isArray(tags)) tags = [];
            } catch (e) {
              tags = [];
            }
          }
          return { ...conv, tags };
        });

        return res.json({
          success: true,
          data: processedConversations
        });
      }

      let conversations = [];

      if (source === 'whatsapp_web') {
        // Get WhatsApp Web conversations
        const io = req.app.get('io');
        const whatsappService = getWhatsAppService(io);
        
        if (whatsappService) {
          const webConversations = await whatsappService.getWebConversations(tenantId);
          
          // Transform web conversations to match our format
          conversations = webConversations.map(conv => ({
            id: conv.conversationId || conv.id,
            contact_name: conv.name,
            phone_number: conv.phone,
            last_message_text: conv.lastMessage,
            last_message_time: new Date(conv.timestamp),
            unread_count: conv.unreadCount || 0,
            source: 'whatsapp_web',
            stage_id: conv.stageId || 'unassigned',
            tags: conv.tags || [],
            claimed_by_user_id: null, // Web conversations start unclaimed
            claimed_by_name: null,
            account_name: 'WhatsApp Web',
            account_phone: null
          }));
        }
      } else {
        // Get WhatsApp Cloud conversations from database
        
        // First, check tenant's visibility settings
        const [tenantSettings] = await pool.execute(`
          SELECT stores_can_see_new_conversations
          FROM tenants
          WHERE id = ?
        `, [tenantId]);
        
        const storesCanSeeNewConversations = tenantSettings[0]?.stores_can_see_new_conversations === 1;
        
        logger.info('Tenant visibility settings', {
          tenantId,
          userId,
          storesCanSeeNewConversations
        });
        
        let query = `
          SELECT c.*, 
                 u.name as claimed_by_name,
                 a.account_name,
                 a.phone_number as account_phone,
                 ps.stage_name,
                 ps.stage_color
          FROM whatsapp_cloud_conversations c
          LEFT JOIN users u ON c.claimed_by_user_id = u.id
          LEFT JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
          LEFT JOIN pipeline_stages ps ON c.pipeline_stage = ps.stage_key AND ps.tenant_id = c.tenant_id
          WHERE c.tenant_id = ? 
            AND c.source = ?
        `;

        const params = [tenantId, source];

        // CRITICAL: Users should ONLY see conversations that were explicitly transferred to them
        // This means:
        // 1. Conversations where transferred_to_store matches their store
        // 2. Conversations where transferred_to_department matches their department
        // 3. Conversations they personally claimed
        // NO other conversations should be visible to users!

        // Get user's store and department IDs
        if (!storeId && storeName) {
          const [storeRows] = await pool.execute(
            `SELECT id FROM stores WHERE tenant_id = ? AND name = ? LIMIT 1`,
            [tenantId, storeName]
          );
          storeId = storeRows[0]?.id || null;
        }

        if (!departmentId && departmentName) {
          const [deptRows] = await pool.execute(
            `SELECT id FROM departments WHERE tenant_id = ? AND name = ? LIMIT 1`,
            [tenantId, departmentName]
          );
          departmentId = deptRows[0]?.id || null;
        }

        logger.info('User store/department info', {
          userId,
          storeId,
          storeName,
          departmentId,
          departmentName
        });

        // Build the filter conditions
        const conditions = [];
        
        // FOR WHATSAPP CLOUD: Users can see conversations that were:
        // 1. Explicitly transferred to them by admin (has transferred_at)
        // 2. Assigned by flow builder (has assigned_by_flow = 1)
        // They CANNOT see new/unclaimed conversations without transfer or flow assignment
        
        // Condition 1: Conversations transferred to user's department (must have transfer timestamp)
        if (departmentId || departmentName) {
          const deptValues = [];
          if (departmentId) deptValues.push(String(departmentId));
          if (departmentName && !deptValues.includes(String(departmentName))) {
            deptValues.push(String(departmentName));
          }
          conditions.push(`(c.transferred_to_department IN (${deptValues.map(() => '?').join(', ')}) AND c.transferred_at IS NOT NULL)`);
          params.push(...deptValues);
        }

        // Condition 2: Conversations transferred to user's store (must have transfer timestamp)
        if (storeId || storeName) {
          const storeValues = [];
          if (storeId) storeValues.push(String(storeId));
          if (storeName && !storeValues.includes(String(storeName))) {
            storeValues.push(String(storeName));
          }
          conditions.push(`(c.transferred_to_store IN (${storeValues.map(() => '?').join(', ')}) AND c.transferred_at IS NOT NULL)`);
          params.push(...storeValues);
        }
        
        // Condition 3: Conversations claimed/assigned to this user AND transferred (must have transfer timestamp)
        if (source === 'whatsapp_web') {
          conditions.push('(c.assigned_user_id = ? AND c.transferred_at IS NOT NULL)');
        } else {
          conditions.push('(c.claimed_by_user_id = ? AND c.transferred_at IS NOT NULL)');
        }
        params.push(userId);
        
        // Condition 4: Conversations assigned by flow builder (for Cloud only) - these don't need transfer
        if (source === 'whatsapp_cloud') {
          conditions.push('c.assigned_by_flow = 1');
        }

        // Apply the filter - user must match at least one condition
        if (conditions.length > 0) {
          query += ` AND (${conditions.join(' OR ')})`;
          logger.info('Applying Cloud conversation visibility filter (TRANSFER OR FLOW REQUIRED)', {
            userId,
            tenantId,
            conditions: conditions.length,
            hasStore: !!(storeId || storeName),
            hasDepartment: !!(departmentId || departmentName),
            allowsFlowAssignment: source === 'whatsapp_cloud',
            requiresTransfer: true
          });
        } else {
          // No valid conditions - show nothing
          query += ` AND 1=0`;
          logger.info('User has no valid conditions - showing NO conversations', {
            userId,
            tenantId
          });
        }

        // Filter by account if specified
        if (accountId && source === 'whatsapp_cloud') {
          query += ` AND c.account_id = ?`;
          params.push(accountId);
        }

        // Add debug logging for the query
        logger.info('Conversation query debug', {
          userId,
          storeId,
          departmentId,
          query: query.replace(/\s+/g, ' ').trim(),
          params
        });

        query += ` ORDER BY c.last_message_time DESC LIMIT 100`;

        const [rows] = await pool.execute(query, params);
        conversations = rows;
      }

      logger.info('Enhanced conversations loaded', {
        userId,
        tenantId,
        source,
        count: conversations.length
      });

      return res.json({
        success: true,
        data: conversations,
        meta: {
          source,
          count: conversations.length,
          user: {
            id: userId,
            store: req.user.store,
            department: req.user.department
          }
        }
      });
    } catch (error) {
      logger.error('Error getting enhanced user conversations', {
        error: error.message,
        stack: error.stack,
        userId: req.user?.id,
        tenantId: req.tenantId || req.user?.tenantId
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load conversations'
      });
    }
  }

  /**
   * Get WhatsApp Web conversations - ENHANCED VERSION
   * Provides robust connection handling and conversation sync
   * GET /api/user/whatsapp-cloud/web-conversations
   */
  static async getWebConversations(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const requestId = req.headers['x-request-id'] || `req_${Date.now()}`;
      
      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      logger.info('Enhanced Web conversations requested', {
        tenantId,
        userId: req.user?.id,
        requestId,
        userStore: req.user?.store,
        userDepartment: req.user?.department
      });

      const io = req.app.get('io');
      const whatsappService = getWhatsAppService(io);
      
      if (!whatsappService) {
        return res.status(503).json({
          success: false,
          message: 'WhatsApp service not available'
        });
      }

      // Get conversations with enhanced error handling
      let conversations = [];
      let connectionStatus = {
        connected: false,
        hasSession: false,
        error: null
      };

      try {
        conversations = await whatsappService.getWebConversations(tenantId);
        
        const normalizedTenantId = Number(tenantId);
        const instance = whatsappService.getInstance?.(normalizedTenantId);
        
        const rawStatus = instance?.connection?.getStatus?.(normalizedTenantId) || null;
        const hasSession = instance?.connection?.stateManager?.hasSession?.(normalizedTenantId) || false;
        const phoneNumber = whatsappService.getPhoneNumber?.(normalizedTenantId) || null;
        const connected = Boolean(
          instance?.connection?.isConnected?.(normalizedTenantId) ||
          rawStatus === 'connected' ||
          hasSession ||
          phoneNumber
        );

        connectionStatus = {
          connected,
          hasSession,
          status: connected ? 'connected' : (rawStatus || 'disconnected'),
          phoneNumber,
          storeStats: instance?.connection?.getStoreStats?.(normalizedTenantId) || null
        };
      } catch (serviceError) {
        logger.error('WhatsApp service error', {
          tenantId,
          error: serviceError.message,
          requestId
        });
        
        connectionStatus.error = serviceError.message;
      }

      logger.info('Enhanced Web conversations response', {
        tenantId,
        userId: req.user?.id,
        requestId,
        connected: connectionStatus.connected,
        hasSession: connectionStatus.hasSession,
        chatsCount: conversations?.length || 0,
        hasError: !!connectionStatus.error
      });

      return res.json({
        success: true,
        data: conversations || [],
        meta: {
          connection: connectionStatus,
          chatsCount: conversations?.length || 0,
          source: 'whatsapp_web',
          user: {
            id: req.user.id,
            store: req.user.store,
            department: req.user.department
          }
        }
      });
    } catch (error) {
      logger.error('Error fetching Enhanced Web WhatsApp conversations', {
        tenantId: req.tenantId || req.user.tenantId,
        error: error.message,
        stack: error.stack
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to fetch Web WhatsApp conversations',
        error: process.env.NODE_ENV === 'development' ? error.message : undefined
      });
    }
  }

  /**
   * Force Web conversations sync - ENHANCED VERSION
   * Provides robust sync with comprehensive error handling
   * POST/GET /api/user/whatsapp-cloud/web-conversations/force-sync
   */
  static async forceWebConversationsSync(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const requestId = req.headers['x-request-id'] || `sync_${Date.now()}`;
      
      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      logger.info('Enhanced force web conversations sync requested', {
        tenantId,
        userId: req.user?.id,
        requestId,
        method: req.method
      });

      const io = req.app.get('io');
      const whatsappService = getWhatsAppService(io);
      
      if (!whatsappService) {
        return res.status(503).json({
          success: false,
          message: 'WhatsApp service not available'
        });
      }

      // Perform enhanced sync with timeout and error handling
      let syncResult = false;
      let conversations = [];
      let connectionStatus = {
        connected: false,
        hasSession: false,
        error: null
      };

      try {
        // Set a timeout for the sync operation
        const syncPromise = whatsappService.forceWebSync(tenantId);
        const timeoutPromise = new Promise((_, reject) => 
          setTimeout(() => reject(new Error('Sync timeout after 30 seconds')), 30000)
        );

        syncResult = await Promise.race([syncPromise, timeoutPromise]);
        
        // Get conversations after sync
        conversations = await whatsappService.getWebConversations(tenantId);
        
        const normalizedTenantId = Number(tenantId);
        const instance = whatsappService.getInstance?.(normalizedTenantId);
        
        connectionStatus = {
          connected: instance?.connection?.isConnected?.(normalizedTenantId) || false,
          hasSession: instance?.connection?.stateManager?.hasSession?.(normalizedTenantId) || false,
          phoneNumber: whatsappService.getPhoneNumber?.(normalizedTenantId) || null,
          storeStats: instance?.connection?.getStoreStats?.(normalizedTenantId) || null
        };
      } catch (syncError) {
        logger.error('Enhanced sync error', {
          tenantId,
          error: syncError.message,
          requestId
        });
        
        connectionStatus.error = syncError.message;
        
        // Still try to get existing conversations
        try {
          conversations = await whatsappService.getWebConversations(tenantId);
        } catch (getError) {
          logger.error('Error getting conversations after failed sync', {
            tenantId,
            error: getError.message,
            requestId
          });
        }
      }

      logger.info('Enhanced force web conversations sync response', {
        tenantId,
        userId: req.user?.id,
        requestId,
        syncResult,
        connected: connectionStatus.connected,
        hasSession: connectionStatus.hasSession,
        chatsCount: conversations?.length || 0,
        hasError: !!connectionStatus.error
      });

      return res.json({
        success: true,
        data: conversations || [],
        meta: {
          syncResult,
          connection: connectionStatus,
          chatsCount: conversations?.length || 0,
          source: 'whatsapp_web',
          syncedAt: new Date().toISOString()
        }
      });
    } catch (error) {
      logger.error('Error forcing Enhanced Web WhatsApp conversations sync', {
        tenantId: req.tenantId || req.user.tenantId,
        error: error.message,
        stack: error.stack
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to force Web WhatsApp conversations sync',
        error: process.env.NODE_ENV === 'development' ? error.message : undefined
      });
    }
  }

  /**
   * Claim a conversation - ENHANCED ROBUST VERSION
   * Implements exclusive conversation ownership with proper validation
   * POST /api/user/whatsapp-cloud/conversations/:id/claim
   */
  static async claimConversation(req, res) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const conversationId = req.params.id;
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;
      const userStore = req.user.store_id || req.user.store;
      const userDepartment = req.user.department_id || req.user.department;

      logger.info('Enhanced conversation claim attempt', {
        conversationId,
        userId,
        tenantId,
        userStore,
        userDepartment,
        userObject: {
          store: req.user.store,
          store_id: req.user.store_id,
          department: req.user.department,
          department_id: req.user.department_id
        }
      });

      // Check if this is an Evolution conversation
      const [evolutionCheck] = await connection.execute(
        'SELECT id FROM conversations WHERE id = ? AND tenant_id = ?',
        [conversationId, tenantId]
      );

      if (evolutionCheck.length > 0) {
        logger.info('✅ Evolution conversation detected for claim', {
          conversationId,
          tenantId
        });

        // Check if conversation exists with row-level locking
        const [conversations] = await connection.execute(
          `SELECT c.*, u.name as claimed_by_name, u.store as claimed_by_store, u.department as claimed_by_department
           FROM conversations c
           LEFT JOIN users u ON c.assigned_user_id = u.id
           WHERE c.id = ? AND c.tenant_id = ?
           FOR UPDATE`,
          [conversationId, tenantId]
        );

        if (conversations.length === 0) {
          await connection.rollback();
          connection.release();
          return res.status(404).json({
            success: false,
            message: 'Conversation not found'
          });
        }

        const conversation = conversations[0];

        // Check if already claimed by another user
        if (conversation.assigned_user_id && conversation.assigned_user_id !== userId) {
          const claimTime = new Date(conversation.claimed_at || conversation.updated_at);
          const now = new Date();
          const minutesSinceClaim = Math.floor((now - claimTime) / 60000);
          
          await connection.rollback();
          connection.release();
          return res.status(409).json({
            success: false,
            message: `Conversation is already claimed by ${conversation.claimed_by_name} (${conversation.claimed_by_store || conversation.claimed_by_department}) ${minutesSinceClaim} minutes ago`,
            claimedBy: {
              name: conversation.claimed_by_name,
              store: conversation.claimed_by_store,
              department: conversation.claimed_by_department,
              claimedAt: conversation.claimed_at
            }
          });
        }

        // Claim the conversation
        await connection.execute(
          `UPDATE conversations 
           SET assigned_user_id = ?, 
               is_claimed = 1,
               claimed_at = NOW(), 
               updated_at = NOW()
           WHERE id = ?`,
          [userId, conversationId]
        );

        await connection.commit();
        connection.release();

        logger.info('✅ Evolution conversation claimed successfully', {
          conversationId,
          userId,
          tenantId
        });

        return res.json({
          success: true,
          message: 'Conversation claimed successfully',
          data: {
            conversationId,
            claimedBy: userId,
            claimedAt: new Date()
          }
        });
      }

      // Check if conversation exists with row-level locking (WhatsApp Cloud)
      const [conversations] = await connection.execute(
        `SELECT c.*, u.name as claimed_by_name, u.store as claimed_by_store, u.department as claimed_by_department
         FROM whatsapp_cloud_conversations c
         LEFT JOIN users u ON c.claimed_by_user_id = u.id
         WHERE c.id = ? AND c.tenant_id = ?
         FOR UPDATE`,
        [conversationId, tenantId]
      );

      if (conversations.length === 0) {
        await connection.rollback();
        return res.status(404).json({
          success: false,
          message: 'Conversation not found'
        });
      }

      const conversation = conversations[0];

      // ROBUST VALIDATION: Check if already claimed by another user
      if (conversation.claimed_by_user_id && conversation.claimed_by_user_id !== userId) {
        const claimTime = new Date(conversation.claimed_at);
        const now = new Date();
        const minutesSinceClaim = Math.floor((now - claimTime) / 60000);
        
        await connection.rollback();
        return res.status(409).json({
          success: false,
          message: `Conversation is already claimed by ${conversation.claimed_by_name} (${conversation.claimed_by_store || conversation.claimed_by_department}) ${minutesSinceClaim} minutes ago`,
          claimedBy: {
            name: conversation.claimed_by_name,
            store: conversation.claimed_by_store,
            department: conversation.claimed_by_department,
            claimedAt: conversation.claimed_at
          }
        });
      }

      // ROBUST VALIDATION: Check store/department permissions
      const canClaim = WhatsAppCloudUserController.validateConversationAccess(
        conversation, 
        { 
          store: userStore || req.user.store_id, 
          department: userDepartment || req.user.department_id,
          role: req.user.role 
        }
      );

      if (!canClaim.allowed) {
        await connection.rollback();
        return res.status(403).json({
          success: false,
          message: canClaim.reason
        });
      }

      // Claim the conversation with enhanced metadata
      await connection.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET claimed_by_user_id = ?, 
             claimed_at = NOW(), 
             updated_at = NOW(),
             store_id = ?,
             department_id = ?
         WHERE id = ?`,
        [userId, userStore, userDepartment, conversationId]
      );

      // Log the claim action (with error handling)
      try {
        await connection.execute(
          `INSERT INTO conversation_logs (conversation_id, user_id, action, details, created_at)
           VALUES (?, ?, 'claimed', ?, NOW())`,
          [conversationId, userId, JSON.stringify({
            userStore,
            userDepartment,
            claimTime: new Date().toISOString()
          })]
        );
      } catch (logError) {
        logger.warn('Could not log claim action (table may not exist)', {
          error: logError.message,
          conversationId,
          userId
        });
        // Continue without failing - logging is not critical
      }

      await connection.commit();

      logger.info('Enhanced conversation claimed successfully', {
        conversationId,
        userId,
        tenantId,
        userStore,
        userDepartment
      });

      // Emit enhanced WebSocket event
      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        tenantNamespace.emit('whatsapp-cloud:conversation-claimed', {
          conversationId,
          userId,
          userName: req.user.name,
          userStore,
          userDepartment,
          timestamp: new Date().toISOString()
        });
        tenantNamespace.emit('conversation-claimed', {
          conversationId,
          userId,
          userName: req.user.name,
          userStore,
          userDepartment,
          timestamp: new Date().toISOString()
        });
      }

      return res.json({
        success: true,
        message: 'Conversation claimed successfully',
        data: {
          conversationId,
          claimedBy: {
            id: userId,
            name: req.user.name,
            store: userStore,
            department: userDepartment
          },
          claimedAt: new Date().toISOString()
        }
      });
    } catch (error) {
      await connection.rollback();
      logger.error('Error in enhanced conversation claim', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        userId: req.user.id
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to claim conversation'
      });
    } finally {
      connection.release();
    }
  }

  /**
   * Get messages for a conversation - ENHANCED ROBUST VERSION
   * Auto-claims the conversation when opened with proper validation
   * Supports both WhatsApp Cloud and Web sources
   * GET /api/user/whatsapp-cloud/conversations/:id/messages
   */
  static async getMessages(req, res) {
    const connection = await pool.getConnection();
    
    try {
      const conversationId = req.params.id;
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;
      const source = req.query.source || 'whatsapp_cloud';

      logger.info('Enhanced messages request', {
        conversationId,
        userId,
        tenantId,
        source,
        userStore: req.user.store_id || req.user.store,
        userDepartment: req.user.department_id || req.user.department
      });

      // Check if this is an Evolution conversation (exists in conversations table with messages in whatsapp_messages)
      const [evolutionCheck] = await connection.execute(
        `SELECT c.id FROM conversations c
         INNER JOIN whatsapp_messages wm ON c.id = wm.conversation_id
         WHERE c.id = ? AND c.tenant_id = ?
         LIMIT 1`,
        [conversationId, tenantId]
      );

      if (evolutionCheck.length > 0) {
        logger.info('Evolution conversation detected, fetching from whatsapp_messages', {
          conversationId,
          tenantId
        });
        
        // Get messages from whatsapp_messages table
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 50;
        const offset = (page - 1) * limit;

        const [messages] = await connection.execute(
          `SELECT 
            id,
            conversation_id,
            direction,
            message_type,
            content,
            content as text_body,
            phone_number,
            contact_name,
            status,
            created_at,
            updated_at
           FROM whatsapp_messages
           WHERE conversation_id = ? AND tenant_id = ?
           ORDER BY created_at DESC
           LIMIT ? OFFSET ?`,
          [conversationId, tenantId, limit, offset]
        );

        const [countResult] = await connection.execute(
          'SELECT COUNT(*) as total FROM whatsapp_messages WHERE conversation_id = ? AND tenant_id = ?',
          [conversationId, tenantId]
        );

        connection.release();

        return res.json({
          success: true,
          data: messages.reverse(), // Reverse to show oldest first
          pagination: {
            page,
            limit,
            total: countResult[0].total,
            totalPages: Math.ceil(countResult[0].total / limit)
          }
        });
      }

      if (source === 'whatsapp_web') {
        // Handle WhatsApp Web messages
        connection.release();
        return await WhatsAppCloudUserController.getWebMessages(req, res);
      }

      await connection.beginTransaction();

      // Verify access and get conversation with locking
      let conversations;
      try {
        [conversations] = await connection.execute(
          `SELECT c.*, u.name as claimed_by_name 
           FROM whatsapp_cloud_conversations c
           LEFT JOIN users u ON c.claimed_by_user_id = u.id
           WHERE c.id = ? AND c.tenant_id = ?
           FOR UPDATE`,
          [conversationId, tenantId]
        );
      } catch (dbError) {
        logger.error('Database error getting conversation', {
          error: dbError.message,
          conversationId,
          tenantId,
          userId
        });
        await connection.rollback();
        return res.status(500).json({
          success: false,
          message: 'Database error accessing conversation'
        });
      }

      if (conversations.length === 0) {
        await connection.rollback();
        return res.status(404).json({
          success: false,
          message: 'Conversation not found'
        });
      }

      const conversation = conversations[0];

      // ROBUST VALIDATION: Check if already claimed by another user
      if (conversation.claimed_by_user_id && conversation.claimed_by_user_id !== userId) {
        await connection.rollback();
        return res.status(403).json({
          success: false,
          message: `This conversation is already claimed by ${conversation.claimed_by_name}`,
          claimedBy: conversation.claimed_by_name
        });
      }

      // ROBUST VALIDATION: Check access permissions
      let userForValidation;
      let userStoreId = req.user.store_id || null;
      let userDepartmentId = req.user.department_id || null;
      let userStoreName = req.user.store || null;
      let userDepartmentName = req.user.department || null;
      try {
        if (!userStoreId && userStoreName) {
          const [storeRows] = await connection.execute(
            `SELECT id FROM stores WHERE tenant_id = ? AND name = ? LIMIT 1`,
            [tenantId, userStoreName]
          );
          userStoreId = storeRows[0]?.id || null;
        }

        if (!userDepartmentId && userDepartmentName) {
          const [deptRows] = await connection.execute(
            `SELECT id FROM departments WHERE tenant_id = ? AND name = ? LIMIT 1`,
            [tenantId, userDepartmentName]
          );
          userDepartmentId = deptRows[0]?.id || null;
        }

        if (!userStoreName && userStoreId) {
          const [storeRows] = await connection.execute(
            `SELECT name FROM stores WHERE tenant_id = ? AND id = ? LIMIT 1`,
            [tenantId, userStoreId]
          );
          userStoreName = storeRows[0]?.name || null;
        }

        if (!userDepartmentName && userDepartmentId) {
          const [deptRows] = await connection.execute(
            `SELECT name FROM departments WHERE tenant_id = ? AND id = ? LIMIT 1`,
            [tenantId, userDepartmentId]
          );
          userDepartmentName = deptRows[0]?.name || null;
        }

        userForValidation = {
          store: userStoreName,
          store_id: userStoreId,
          department: userDepartmentName,
          department_id: userDepartmentId,
          role: req.user.role
        };
        
        logger.info('User validation data', {
          userId,
          userData: {
            store_id: req.user.store_id,
            department_id: req.user.department_id,
            store: req.user.store,
            department: req.user.department,
            role: req.user.role
          },
          userForValidation,
          conversationData: {
            id: conversation.id,
            store_id: conversation.store_id,
            department_id: conversation.department_id,
            transferred_to_store: conversation.transferred_to_store,
            transferred_to_department: conversation.transferred_to_department
          }
        });
      } catch (validationError) {
        logger.error('Error preparing user validation data', {
          error: validationError.message,
          user: req.user
        });
        await connection.rollback();
        return res.status(500).json({
          success: false,
          message: 'User validation error'
        });
      }

      const canAccess = WhatsAppCloudUserController.validateConversationAccess(
        conversation, 
        userForValidation
      );

      logger.info('Conversation access validation', {
        conversationId,
        userId,
        conversation: {
          store_id: conversation.store_id,
          department_id: conversation.department_id,
          transferred_to_store: conversation.transferred_to_store,
          transferred_to_department: conversation.transferred_to_department,
          claimed_by_user_id: conversation.claimed_by_user_id
        },
        user: userForValidation,
        canAccess: canAccess
      });

      if (!canAccess.allowed) {
        await connection.rollback();
        return res.status(403).json({
          success: false,
          message: canAccess.reason
        });
      }

      // Auto-claim if not claimed (ROBUST: Only if user has permission)
      if (!conversation.claimed_by_user_id) {
        try {
          await connection.execute(
            `UPDATE whatsapp_cloud_conversations 
             SET claimed_by_user_id = ?, 
                 claimed_at = NOW(),
                 store_id = ?,
                 department_id = ?,
                 updated_at = NOW()
             WHERE id = ?`,
            [userId, userStoreId, userDepartmentId, conversationId]
          );
        } catch (claimError) {
          logger.error('Error auto-claiming conversation', {
            error: claimError.message,
            conversationId,
            userId
          });
          await connection.rollback();
          return res.status(500).json({
            success: false,
            message: 'Failed to claim conversation'
          });
        }

        // Log auto-claim (with error handling for missing table)
        try {
          await connection.execute(
            `INSERT INTO conversation_logs (conversation_id, user_id, action, details, created_at)
             VALUES (?, ?, 'auto_claimed', ?, NOW())`,
            [conversationId, userId, JSON.stringify({
              trigger: 'message_view',
              userStore: req.user.store_id || req.user.store,
              userDepartment: req.user.department_id || req.user.department
            })]
          );
        } catch (logError) {
          logger.warn('Could not log auto-claim action (table may not exist)', {
            error: logError.message,
            conversationId,
            userId
          });
          // Continue without failing - logging is not critical
        }

        // Emit claim event
        const io = req.app.get('io');
        if (io) {
          const tenantNamespace = io.of(`/tenant/${tenantId}`);
          tenantNamespace.emit('whatsapp-cloud:conversation-claimed', {
            conversationId,
            userId,
            userName: req.user.name,
            type: 'auto_claim',
            timestamp: new Date().toISOString()
          });
          tenantNamespace.emit('conversation-claimed', {
            conversationId,
            userId,
            userName: req.user.name,
            type: 'auto_claim',
            timestamp: new Date().toISOString()
          });
        }
      }

      await connection.commit();

      // Get messages with enhanced metadata
      let messages;
      try {
        [messages] = await pool.execute(
          `SELECT m.*, 
                 u.name as sent_by_name,
                 u.store as sent_by_store,
                 u.department as sent_by_department
           FROM whatsapp_cloud_messages m
           LEFT JOIN users u ON m.sent_by_user_id = u.id
           WHERE m.conversation_id = ?
           ORDER BY m.created_at ASC
           LIMIT 1000`,
          [conversationId]
        );
      } catch (messagesError) {
        logger.error('Database error getting messages', {
          error: messagesError.message,
          conversationId,
          userId
        });
        return res.status(500).json({
          success: false,
          message: 'Database error loading messages'
        });
      }

      // Mark conversation as read (with error handling)
      try {
        await pool.execute(
          `UPDATE whatsapp_cloud_conversations 
           SET unread_count = 0, 
               updated_at = NOW()
           WHERE id = ?`,
          [conversationId]
        );
      } catch (updateError) {
        logger.warn('Could not mark conversation as read', {
          error: updateError.message,
          conversationId
        });
        // Continue without failing - this is not critical
      }

      messages = messages.map((message) => ({
        ...message,
        text_content: message.text_content || message.content || message.text_body || message.text || message.message || null,
        timestamp: message.timestamp || message.created_at || message.sent_at || null,
        direction: message.direction || (message.sent_by_user_id ? 'outbound' : 'inbound')
      }));

      logger.info('Enhanced messages loaded', {
        conversationId,
        userId,
        messageCount: messages.length
      });

      return res.json({
        success: true,
        data: messages,
        meta: {
          conversationId,
          messageCount: messages.length,
          source: 'whatsapp_cloud',
          claimedBy: {
            id: userId,
            name: req.user.name,
            store: req.user.store_id || req.user.store,
            department: req.user.department_id || req.user.department
          }
        }
      });
    } catch (error) {
      await connection.rollback();
      logger.error('Error getting enhanced messages', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        userId: req.user.id
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load messages'
      });
    } finally {
      connection.release();
    }
  }

  /**
   * Release a conversation
   * POST /api/user/whatsapp-cloud/conversations/:id/release
   */
  static async releaseConversation(req, res) {
    try {
      const conversationId = req.params.id;
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;

      // Validate conversation ID
      if (!conversationId || conversationId === 'null' || conversationId === 'undefined') {
        return res.status(400).json({
          success: false,
          message: 'Invalid conversation ID'
        });
      }

      const [conversations] = await pool.execute(
        `SELECT * FROM whatsapp_cloud_conversations 
         WHERE id = ? AND tenant_id = ? AND claimed_by_user_id = ?`,
        [conversationId, tenantId, userId]
      );

      if (conversations.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Conversation not found or not claimed by you'
        });
      }

      await pool.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET claimed_by_user_id = NULL, claimed_at = NULL, updated_at = NOW()
         WHERE id = ?`,
        [conversationId]
      );

      logger.info('Conversation released', {
        conversationId,
        userId,
        tenantId
      });

      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        tenantNamespace.emit('whatsapp-cloud:conversation-released', {
          conversationId,
          userId
        });
        tenantNamespace.emit('conversation-released', {
          conversationId,
          userId
        });
      }

      return res.json({
        success: true,
        message: 'Conversation released successfully'
      });
    } catch (error) {
      logger.error('Error releasing conversation', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to release conversation'
      });
    }
  }

  /**
   * Send text message - ENHANCED ROBUST VERSION
   * Supports both WhatsApp Cloud API and WhatsApp Web
   * Includes comprehensive validation and error handling
   * POST /api/user/whatsapp-cloud/conversations/:id/send-message
   */
  static async sendMessage(req, res) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const conversationId = req.params.id;
      const { message, source = 'whatsapp_cloud' } = req.body;
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;

      logger.info('Enhanced message send request', {
        conversationId,
        userId,
        tenantId,
        messageLength: message?.length,
        source,
        userStore: req.user.store,
        userDepartment: req.user.department
      });

      // ROBUST VALIDATION
      if (!message || message.trim().length === 0) {
        await connection.rollback();
        logger.error('Message validation failed - empty message', { conversationId, userId });
        return res.status(400).json({
          success: false,
          message: 'Message text is required and cannot be empty'
        });
      }

      if (message.length > 4096) {
        await connection.rollback();
        logger.error('Message validation failed - too long', { conversationId, userId, messageLength: message.length });
        return res.status(400).json({
          success: false,
          message: 'Message too long. Maximum 4096 characters allowed.'
        });
      }

      logger.info('Message validation passed', { conversationId, userId });
      const trimmedMessage = message.trim();

      // Check if this is an Evolution conversation
      const [evolutionCheck] = await connection.execute(
        'SELECT id FROM conversations WHERE id = ? AND tenant_id = ?',
        [conversationId, tenantId]
      );

      if (evolutionCheck.length > 0) {
        logger.info('✅ Evolution conversation detected for send message', {
          conversationId,
          tenantId
        });

        // Get conversation and verify claim
        const [conversations] = await connection.execute(
          `SELECT c.* FROM conversations c
           WHERE c.id = ? AND c.tenant_id = ? AND c.assigned_user_id = ?
           FOR UPDATE`,
          [conversationId, tenantId, userId]
        );

        if (conversations.length === 0) {
          await connection.rollback();
          connection.release();
          logger.error('Evolution conversation not found or not claimed', { conversationId, tenantId, userId });
          return res.status(403).json({
            success: false,
            message: 'Conversation not found or not claimed by you'
          });
        }

        const conversation = conversations[0];

        // Get Evolution instance for this conversation
        const [messages] = await connection.execute(
          'SELECT instance_id FROM whatsapp_messages WHERE conversation_id = ? LIMIT 1',
          [conversationId]
        );

        if (messages.length === 0) {
          await connection.rollback();
          connection.release();
          return res.status(400).json({
            success: false,
            message: 'Could not determine Evolution instance for this conversation'
          });
        }

        const instanceId = messages[0].instance_id;

        // Get Evolution instance details
        const [instances] = await connection.execute(
          'SELECT * FROM evolution_instances WHERE id = ? AND tenant_id = ?',
          [instanceId, tenantId]
        );

        if (instances.length === 0) {
          await connection.rollback();
          connection.release();
          logger.error('❌ Evolution instance not found', {
            conversationId,
            instanceId,
            tenantId
          });
          return res.status(400).json({
            success: false,
            message: 'Evolution instance not found'
          });
        }

        const instance = instances[0];
        
        // Log instance status
        logger.info('📱 Evolution instance found', {
          conversationId,
          instanceId,
          instanceName: instance.instance_name,
          evolutionInstanceId: instance.evolution_instance_id,
          instanceStatus: instance.status,
          hasEvolutionInstanceId: !!instance.evolution_instance_id
        });
        
        // Warn if not connected but continue anyway
        if (instance.status !== 'connected') {
          logger.warn('⚠️ Evolution instance is not connected', {
            conversationId,
            instanceId,
            instanceName: instance.instance_name,
            currentStatus: instance.status
          });
        }

        // Send message via Evolution API
        let evolutionApiUrl = 'http://localhost:8080'; // Default value
        try {
          const axios = require('axios');
          // Get Evolution API URL from database settings
          const [settings] = await pool.execute(
            `SELECT setting_value FROM system_settings_kv WHERE setting_key = 'evolution_api_url'`
          );
          evolutionApiUrl = settings[0]?.setting_value || process.env.EVOLUTION_API_URL || 'http://localhost:8080';
          
          // Get global API key as fallback
          const [globalApiKeyResult] = await pool.execute(
            `SELECT setting_value FROM system_settings_kv WHERE setting_key = 'evolution_api_key'`
          );
          const globalApiKey = globalApiKeyResult[0]?.setting_value;
          
          // Use evolution_instance_id (technical name) instead of instance_name (display name)
          // If evolution_instance_id is not set, fall back to instance_name
          const evolutionInstanceName = instance.evolution_instance_id || instance.instance_name;
          
          if (!instance.evolution_instance_id) {
            logger.warn('⚠️ evolution_instance_id is not set, using instance_name as fallback', {
              instanceId: instance.id,
              instanceName: instance.instance_name,
              tenantId
            });
          }
          
          // Use instance API key, or fall back to global API key
          const apiKey = instance.evolution_api_key || globalApiKey;
          
          if (!apiKey) {
            throw new Error('API key not configured. Please configure either instance API key or global API key in system settings.');
          }
          
          logger.info('📤 Sending message via Evolution API', {
            url: `${evolutionApiUrl}/message/sendText/${evolutionInstanceName}`,
            number: conversation.phone_number,
            messageLength: trimmedMessage.length,
            instanceName: instance.instance_name,
            evolutionInstanceId: instance.evolution_instance_id,
            usingInstanceId: evolutionInstanceName,
            instanceId: instance.id,
            instanceStatus: instance.status,
            fromDatabase: !!settings[0]?.setting_value,
            hasApiKey: !!apiKey,
            usingGlobalApiKey: !instance.evolution_api_key && !!globalApiKey,
            apiKeyField: instance.evolution_api_key ? 'evolution_api_key' : 'global'
          });
          
          const response = await axios.post(
            `${evolutionApiUrl}/message/sendText/${evolutionInstanceName}`,
            {
              number: conversation.phone_number,
              text: trimmedMessage
            },
            {
              headers: {
                'Content-Type': 'application/json',
                'apikey': apiKey
              },
              timeout: 30000
            }
          );

          logger.info('✅ Message sent via Evolution API', {
            conversationId,
            instanceId,
            responseStatus: response.status,
            response: response.data
          });

          // Save message to database
          await connection.execute(
            `INSERT INTO whatsapp_messages 
             (conversation_id, instance_id, tenant_id, direction, message_type, content, phone_number, contact_name, status, created_at, updated_at)
             VALUES (?, ?, ?, 'outgoing', 'text', ?, ?, ?, 'sent', NOW(), NOW())`,
            [conversationId, instanceId, tenantId, trimmedMessage, conversation.phone_number, conversation.contact_name || conversation.phone_number]
          );

          // Update conversation
          await connection.execute(
            `UPDATE conversations 
             SET last_message = ?, 
                 last_message_time = NOW(), 
                 status = CASE 
                   WHEN status = 'waiting' THEN 'attended'
                   ELSE status 
                 END,
                 attended_at = CASE 
                   WHEN status = 'waiting' AND attended_at IS NULL THEN NOW()
                   ELSE attended_at 
                 END,
                 updated_at = NOW()
             WHERE id = ?`,
            [trimmedMessage, conversationId]
          );

          await connection.commit();
          connection.release();

          return res.json({
            success: true,
            message: 'Message sent successfully',
            data: {
              conversationId,
              timestamp: new Date().toISOString(),
              source: 'evolution'
            }
          });

        } catch (error) {
          await connection.rollback();
          connection.release();
          
          const errorDetails = {
            conversationId,
            instanceId,
            instanceName: instance.instance_name,
            evolutionInstanceId: instance.evolution_instance_id,
            instanceStatus: instance.status,
            error: error.message,
            statusCode: error.response?.status,
            statusText: error.response?.statusText,
            responseData: error.response?.data,
            url: `${evolutionApiUrl}/message/sendText/${instance.evolution_instance_id || instance.instance_name}`,
            phoneNumber: conversation.phone_number
          };
          
          logger.error('❌ Error sending message via Evolution API', errorDetails);
          
          // Provide more specific error message based on status code
          let errorMessage = 'Failed to send message via Evolution API';
          if (error.response?.status === 404) {
            errorMessage = `Evolution instance "${instance.instance_name}" not found in Evolution API. Please check if the instance exists and is properly configured.`;
          } else if (error.response?.status === 401 || error.response?.status === 403) {
            errorMessage = 'Authentication failed. Please check the API key configuration.';
          } else if (error.response?.data?.message) {
            errorMessage = error.response.data.message;
          }
          
          return res.status(500).json({
            success: false,
            message: errorMessage,
            error: error.response?.data?.message || error.message,
            details: process.env.NODE_ENV === 'development' ? errorDetails : undefined
          });
        }
      }

      if (source === 'whatsapp_web') {
        let resolvedConversationId = conversationId;
        let webConversation = null;
        if (String(conversationId).includes('@')) {
          const rawJid = String(conversationId);
          let numberPart = rawJid.split('@')[0] || '';
          if (numberPart.includes(':')) {
            numberPart = numberPart.split(':')[0];
          }
          let normalizedPhone = numberPart.replace(/^\+/, '');
          let normalizedRemoteJid = rawJid;
          if (normalizedRemoteJid.includes(':')) {
            const parts = normalizedRemoteJid.split('@');
            if (parts.length === 2) {
              const num = parts[0].split(':')[0];
              normalizedRemoteJid = `${num}@${parts[1]}`;
            }
          }
          const lidJid = normalizedPhone ? `${normalizedPhone}@lid` : normalizedRemoteJid;
          const waJid = normalizedPhone ? `${normalizedPhone}@s.whatsapp.net` : normalizedRemoteJid;
          const phoneLike = normalizedPhone ? `%${normalizedPhone.slice(-10)}` : normalizedRemoteJid;
          const [existing] = await connection.execute(
            `SELECT * FROM conversations 
             WHERE tenant_id = ? AND (
               remote_jid = ?
               OR remote_jid = ?
               OR remote_jid = ?
               OR phone_number = ?
               OR phone_number = ?
               OR phone_number LIKE ?
             )
             ORDER BY updated_at DESC
             LIMIT 1`,
            [tenantId, normalizedRemoteJid, lidJid, waJid, normalizedPhone, `+${normalizedPhone}`, phoneLike]
          );
          if (existing.length) {
            webConversation = existing[0];
            resolvedConversationId = webConversation.id;
          } else if (normalizedPhone) {
            const [insertResult] = await connection.execute(
              'INSERT INTO conversations (tenant_id, phone_number, remote_jid, contact_name, last_message, last_message_time, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())',
              [tenantId, normalizedPhone, normalizedRemoteJid, normalizedPhone, trimmedMessage, 'waiting']
            );
            resolvedConversationId = insertResult.insertId;
            const [created] = await connection.execute(
              'SELECT * FROM conversations WHERE id = ? AND tenant_id = ? LIMIT 1',
              [resolvedConversationId, tenantId]
            );
            webConversation = created[0] || null;
          }
        } else {
          const [webConversations] = await connection.execute(
            `SELECT * FROM conversations WHERE id = ? AND tenant_id = ? LIMIT 1`,
            [conversationId, tenantId]
          );
          webConversation = webConversations[0] || null;
        }

        if (!webConversation) {
          await connection.rollback();
          return res.status(404).json({
            success: false,
            message: 'Conversation not found'
          });
        }
        const targetPhone = webConversation.phone_number || webConversation.remote_jid;
        const sendResult = await WhatsAppCloudUserController.sendWebMessage(
          tenantId,
          targetPhone,
          trimmedMessage,
          conversationId,
          resolvedConversationId,
          {
            senderUserId: userId,
            senderName: req.user.name || null,
            senderStore: req.user.store || null,
            senderDepartment: req.user.department || null,
            metadata: { source: 'whatsapp_web' }
          }
        );

        if (!sendResult.success) {
          await connection.rollback();
          return res.status(500).json({
            success: false,
            message: sendResult.error || 'Failed to send message'
          });
        }

        await connection.commit();

        return res.json({
          success: true,
          message: 'Message sent successfully',
          data: {
            messageId: sendResult.messageId,
            conversationId: resolvedConversationId,
            timestamp: new Date().toISOString(),
            source: 'whatsapp_web'
          }
        });
      }

      // Get conversation and verify claim with locking
      logger.info('Fetching conversation details', { conversationId, tenantId, userId });
      const [conversations] = await connection.execute(
        `SELECT c.*, a.phone_number_id, a.access_token, a.account_name, a.status as account_status
         FROM whatsapp_cloud_conversations c
         LEFT JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
         WHERE c.id = ? AND c.tenant_id = ? AND c.claimed_by_user_id = ?
         FOR UPDATE`,
        [conversationId, tenantId, userId]
      );

      logger.info('Conversation query result', { 
        conversationId, 
        found: conversations.length > 0,
        conversationData: conversations.length > 0 ? {
          id: conversations[0].id,
          accountId: conversations[0].account_id,
          hasPhoneNumberId: !!conversations[0].phone_number_id,
          hasAccessToken: !!conversations[0].access_token,
          accountStatus: conversations[0].account_status
        } : null
      });

      if (conversations.length === 0) {
        await connection.rollback();
        logger.error('Conversation not found or not claimed', { conversationId, tenantId, userId });
        return res.status(403).json({
          success: false,
          message: 'Conversation not found or not claimed by you'
        });
      }

      const conversation = conversations[0];

      // Log conversation details for debugging
      logger.info('Conversation details for message send', {
        conversationId,
        accountId: conversation.account_id,
        phoneNumberId: conversation.phone_number_id,
        hasAccessToken: !!conversation.access_token,
        accountStatus: conversation.account_status,
        contactPhone: conversation.contact_phone
      });

      // Validate account configuration
      logger.info('Starting account validation', { conversationId, accountId: conversation.account_id });
      
      if (!conversation.account_id) {
        await connection.rollback();
        logger.error('Account validation failed - no account_id', { conversationId });
        return res.status(400).json({
          success: false,
          message: 'Conversation has no associated WhatsApp account'
        });
      }

      if (!conversation.phone_number_id) {
        await connection.rollback();
        logger.error('Account validation failed - no phone_number_id', { conversationId, accountId: conversation.account_id });
        return res.status(400).json({
          success: false,
          message: 'WhatsApp account is missing phone_number_id configuration'
        });
      }

      if (!conversation.access_token) {
        await connection.rollback();
        logger.error('Account validation failed - no access_token', { conversationId, accountId: conversation.account_id });
        return res.status(400).json({
          success: false,
          message: 'WhatsApp account is missing access_token configuration'
        });
      }

      if (conversation.account_status !== 'connected') {
        await connection.rollback();
        logger.error('Account validation failed - not connected', { 
          conversationId, 
          accountId: conversation.account_id, 
          status: conversation.account_status 
        });
        return res.status(400).json({
          success: false,
          message: `WhatsApp account is not connected (status: ${conversation.account_status})`
        });
      }

      logger.info('Account validation passed', { conversationId, accountId: conversation.account_id });

      // Handle different sources
      logger.info('Preparing to send message', { conversationId, source });
      let sendResult;
      if (source === 'whatsapp_web') {
        logger.info('Sending via WhatsApp Web', { conversationId });
        sendResult = await WhatsAppCloudUserController.sendWebMessage(
          tenantId,
          conversation.contact_phone,
          message,
          conversationId,
          {
            senderUserId: userId,
            senderName: req.user.name || null,
            senderStore: req.user.store || null,
            senderDepartment: req.user.department || null,
            metadata: { source: 'whatsapp_web' }
          }
        );
      } else {
        logger.info('Sending via WhatsApp Cloud', { conversationId });
        sendResult = await WhatsAppCloudUserController.sendCloudMessage(
          conversation, message, conversationId
        );
      }

      logger.info('Send result received', { 
        conversationId, 
        success: sendResult.success, 
        error: sendResult.error,
        messageId: sendResult.messageId
      });

      if (!sendResult.success) {
        await connection.rollback();
        logger.error('Message send failed', { 
          conversationId, 
          error: sendResult.error,
          details: sendResult.details
        });
        return res.status(500).json({
          success: false,
          message: sendResult.error || 'Failed to send message'
        });
      }

      // Save message to database with enhanced metadata
      logger.info('Saving message to database', { 
        conversationId, 
        messageId: sendResult.messageId,
        tenantId,
        accountId: conversation.account_id
      });
      
      let messageResult;
      try {
        [messageResult] = await connection.execute(
          `INSERT INTO whatsapp_cloud_messages 
           (conversation_id, message_id, direction, message_type, text_content, status, timestamp, sent_by_user_id, created_at)
           VALUES (?, ?, 'outbound', 'text', ?, 'sent', NOW(), ?, NOW())`,
          [
            conversationId, 
            sendResult.messageId || `msg_${Date.now()}`, 
            message.trim(),
            userId
          ]
        );
        
        logger.info('Message saved to database successfully', { 
          conversationId, 
          messageId: messageResult.insertId,
          dbMessageId: sendResult.messageId
        });
      } catch (messageInsertError) {
        logger.error('Error inserting message to database', {
          error: messageInsertError.message,
          stack: messageInsertError.stack,
          conversationId,
          userId,
          tenantId,
          accountId: conversation.account_id
        });
        // Continue without failing - the message was sent successfully
        messageResult = { insertId: Date.now() };
      }

      // Update conversation metadata (with error handling)
      try {
        await connection.execute(
          `UPDATE whatsapp_cloud_conversations 
           SET last_message_text = ?, 
               last_message_time = NOW(), 
               last_message_from = 'business',
               attended_at = CASE 
                 WHEN attended_at IS NULL THEN NOW()
                 ELSE attended_at 
               END,
               message_count = message_count + 1,
               updated_at = NOW()
           WHERE id = ?`,
          [message.substring(0, 255), conversationId]
        );
      } catch (updateError) {
        logger.warn('Could not update conversation metadata', {
          error: updateError.message,
          conversationId
        });
        // Continue without failing
      }

      // Log the message send action (with error handling)
      try {
        await connection.execute(
          `INSERT INTO conversation_logs (conversation_id, user_id, action, details, created_at)
           VALUES (?, ?, 'message_sent', ?, NOW())`,
          [conversationId, userId, JSON.stringify({
            messageLength: message.length,
            source,
            messageId: sendResult.messageId
          })]
        );
      } catch (logError) {
        logger.warn('Could not log message send action', {
          error: logError.message,
          conversationId,
          userId
        });
        // Continue without failing - logging is not critical
      }

      await connection.commit();

      logger.info('Enhanced message sent successfully', {
        conversationId,
        userId,
        messageId: sendResult.messageId,
        source
      });

      // Emit enhanced WebSocket event
      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        tenantNamespace.emit('message-sent', {
          conversationId,
          message: {
            id: messageResult.insertId,
            messageId: sendResult.messageId,
            text: message,
            direction: 'outbound',
            timestamp: new Date().toISOString(),
            sent_by_name: req.user.name,
            sent_by_store: req.user.store,
            sent_by_department: req.user.department,
            source
          }
        });
      }

      return res.json({
        success: true,
        message: 'Message sent successfully',
        data: {
          messageId: sendResult.messageId,
          conversationId,
          timestamp: new Date().toISOString(),
          source
        }
      });
    } catch (error) {
      await connection.rollback();
      logger.error('Error in enhanced message send', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        userId: req.user.id,
        errorName: error.name,
        errorCode: error.code
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to send message',
        error: error.message,
        details: process.env.NODE_ENV === 'development' ? error.stack : undefined
      });
    } finally {
      connection.release();
    }
  }

  /**
   * Add internal note
   * POST /api/user/whatsapp-cloud/conversations/:id/internal-note
   */
  static async addInternalNote(req, res) {
    try {
      const conversationId = req.params.id;
      const { note } = req.body;
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;

      if (!note) {
        return res.status(400).json({
          success: false,
          message: 'Note text is required'
        });
      }

      // Verify access and get conversation with account details
      const [conversations] = await pool.execute(
        `SELECT c.*, a.phone_number_id, a.account_name
         FROM whatsapp_cloud_conversations c
         LEFT JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
         WHERE c.id = ? AND c.tenant_id = ?`,
        [conversationId, tenantId]
      );

      if (conversations.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Conversation not found'
        });
      }

      const conversation = conversations[0];

      // Save internal note
      const [result] = await pool.execute(
        `INSERT INTO whatsapp_cloud_messages 
         (conversation_id, message_id, direction, message_type, text_content, is_internal_note, status, timestamp, sent_by_user_id, created_at)
         VALUES (?, ?, 'outbound', 'text', ?, TRUE, 'sent', NOW(), ?, NOW())`,
        [conversationId, `note_${Date.now()}`, note, userId]
      );

      logger.info('Internal note added', {
        conversationId,
        userId,
        noteId: result.insertId
      });

      return res.json({
        success: true,
        message: 'Internal note added successfully',
        data: {
          noteId: result.insertId
        }
      });
    } catch (error) {
      logger.error('Error adding internal note', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to add internal note'
      });
    }
  }

  /**
   * Update conversation tags - ENHANCED ROBUST VERSION
   * Supports adding, removing, and managing conversation tags
   * PUT /api/user/whatsapp-cloud/conversations/:id/tags
   */
  static async updateTags(req, res) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const conversationId = req.params.id;
      const { tags, action = 'replace' } = req.body; // action: 'replace', 'add', 'remove'
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;

      logger.info('Enhanced tags update request', {
        conversationId,
        userId,
        tenantId,
        tags,
        action
      });

      // Permission: tenant admin only
      if (req.user?.role !== 'admin') {
        await connection.rollback();
        return res.status(403).json({
          success: false,
          message: 'Only tenant admins can edit tags'
        });
      }

      // ROBUST VALIDATION
      if (!Array.isArray(tags)) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Tags must be an array'
        });
      }

      // Validate tag format and length
      const validTags = tags.filter(tag => {
        return typeof tag === 'string' && 
               tag.trim().length > 0 && 
               tag.trim().length <= 50 &&
               /^[a-zA-Z0-9\s\-_]+$/.test(tag.trim());
      }).map(tag => tag.trim().toLowerCase());

      if (validTags.length !== tags.length) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Invalid tag format. Tags must be 1-50 characters, alphanumeric with spaces, hyphens, and underscores only.'
        });
      }

      // Verify conversation exists and user has access
      const [conversations] = await connection.execute(
        `SELECT c.*, c.tags as current_tags
         FROM whatsapp_cloud_conversations c
         WHERE c.id = ? AND c.tenant_id = ?
         FOR UPDATE`,
        [conversationId, tenantId]
      );

      if (conversations.length === 0) {
        await connection.rollback();
        return res.status(404).json({
          success: false,
          message: 'Conversation not found'
        });
      }

      const conversation = conversations[0];
      let currentTags = [];
      
      try {
        currentTags = conversation.current_tags ? JSON.parse(conversation.current_tags) : [];
      } catch (e) {
        currentTags = [];
      }

      // Apply tag action
      let newTags = [];
      switch (action) {
        case 'add':
          newTags = [...new Set([...currentTags, ...validTags])];
          break;
        case 'remove':
          newTags = currentTags.filter(tag => !validTags.includes(tag));
          break;
        case 'replace':
        default:
          newTags = [...new Set(validTags)];
          break;
      }

      // Limit to maximum 3 tags
      if (newTags.length > 3) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Maximum 3 tags allowed per conversation'
        });
      }

      // Update conversation tags
      await connection.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET tags = ?, updated_at = NOW()
         WHERE id = ? AND tenant_id = ?`,
        [JSON.stringify(newTags), conversationId, tenantId]
      );

      // Log tag update
      await connection.execute(
        `INSERT INTO conversation_logs (conversation_id, user_id, action, details, created_at)
         VALUES (?, ?, 'tags_updated', ?, NOW())`,
        [conversationId, userId, JSON.stringify({
          action,
          previousTags: currentTags,
          newTags,
          addedTags: validTags
        })]
      );

      await connection.commit();

      logger.info('Enhanced tags updated successfully', {
        conversationId,
        userId,
        previousCount: currentTags.length,
        newCount: newTags.length,
        action
      });

      // Emit WebSocket event
      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        tenantNamespace.emit('conversation-tags-updated', {
          conversationId,
          tags: newTags,
          action,
          updatedBy: req.user.name,
          timestamp: new Date().toISOString()
        });
      }

      return res.json({
        success: true,
        message: 'Tags updated successfully',
        data: {
          conversationId,
          tags: newTags,
          action,
          previousTags: currentTags
        }
      });
    } catch (error) {
      await connection.rollback();
      logger.error('Error updating enhanced tags', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        userId: req.user.id
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to update tags'
      });
    } finally {
      connection.release();
    }
  }

  /**
   * Update conversation priority
   * PUT /api/user/whatsapp-cloud/conversations/:id/priority
   */
  static async updatePriority(req, res) {
    try {
      const conversationId = req.params.id;
      const { priority } = req.body;
      const tenantId = req.tenantId || req.user.tenantId;

      if (!['low', 'normal', 'high', 'urgent'].includes(priority)) {
        return res.status(400).json({
          success: false,
          message: 'Invalid priority value'
        });
      }

      await pool.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET priority = ?, updated_at = NOW()
         WHERE id = ? AND tenant_id = ?`,
        [priority, conversationId, tenantId]
      );

      return res.json({
        success: true,
        message: 'Priority updated successfully'
      });
    } catch (error) {
      logger.error('Error updating priority', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to update priority'
      });
    }
  }

  /**
   * Update conversation stage (pipeline)
   * PUT /api/user/whatsapp-cloud/conversations/:id/stage
   */
  static async updateStage(req, res) {
    try {
      const conversationId = req.params.id;
      const { stage } = req.body;
      const tenantId = req.tenantId || req.user.tenantId;

      logger.info('🎯 Stage update request', {
        conversationId,
        stage,
        tenantId,
        body: req.body
      });

      if (!stage) {
        logger.warn('❌ Stage is missing in request');
        return res.status(400).json({
          success: false,
          message: 'Stage is required'
        });
      }

      // Try to update in whatsapp_cloud_conversations first
      logger.info('🔄 Attempting to update in whatsapp_cloud_conversations...');
      const [cloudResult] = await pool.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET stage_id = ?, pipeline_stage = ?, updated_at = NOW()
         WHERE id = ? AND tenant_id = ?`,
        [stage, stage, conversationId, tenantId]
      );

      logger.info('📊 Cloud update result:', {
        affectedRows: cloudResult.affectedRows,
        changedRows: cloudResult.changedRows
      });

      // If no rows affected, try updating in conversations table (WhatsApp Web / Evolution)
      if (cloudResult.affectedRows === 0) {
        logger.info('🔄 Not found in cloud, trying conversations table...');
        const [webResult] = await pool.execute(
          `UPDATE conversations 
           SET stage_id = ?, pipeline_stage = ?, updated_at = NOW()
           WHERE id = ? AND tenant_id = ?`,
          [stage, stage, conversationId, tenantId]
        );

        logger.info('📊 Conversations table update result:', {
          affectedRows: webResult.affectedRows,
          changedRows: webResult.changedRows
        });

        if (webResult.affectedRows === 0) {
          logger.error('❌ Conversation not found in any table', {
            conversationId,
            tenantId
          });
          return res.status(404).json({
            success: false,
            message: 'Conversation not found'
          });
        }

        logger.info('✅ Evolution/Web conversation stage updated', {
          conversationId,
          stage,
          tenantId
        });
      } else {
        logger.info('✅ WhatsApp Cloud conversation stage updated', {
          conversationId,
          stage,
          tenantId
        });
      }

      return res.json({
        success: true,
        message: 'Stage updated successfully'
      });
    } catch (error) {
      logger.error('❌ Error updating stage', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        stage: req.body.stage
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to update stage'
      });
    }
  }

  /**
   * Get WhatsApp Cloud accounts for current user
   * GET /api/user/whatsapp-cloud/accounts
   * Returns both Cloud accounts and Evolution instances
   * Users can see all connected accounts regardless of store/department
   */
  static async getAccounts(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const userId = req.user.id;

      logger.info('User requesting accounts', {
        userId,
        tenantId,
        userRole: req.user.role,
        storeId: req.user.store_id,
        departmentId: req.user.department_id
      });

      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      let normalizedCloudAccounts = [];
      let normalizedEvolutionAccounts = [];

      // Get all Cloud accounts for the tenant (no filtering by store/department)
      try {
        const cloudQuery = `
          SELECT id, account_name, phone_number, status, webhook_verified, waba_id as business_account_id
          FROM whatsapp_cloud_accounts
          WHERE tenant_id = ?
          ORDER BY account_name ASC
        `;

        const [cloudAccounts] = await pool.execute(cloudQuery, [tenantId]);
        
        logger.info('Cloud accounts found', {
          userId,
          tenantId,
          count: cloudAccounts.length,
          accounts: cloudAccounts.map(a => ({
            id: a.id,
            name: a.account_name,
            status: a.status,
            webhook_verified: a.webhook_verified
          }))
        });
        
        normalizedCloudAccounts = cloudAccounts.map(account => {
          // Consider account connected if status is 'connected' (webhook_verified is optional)
          const isConnected = account.status === 'connected';
          return {
            ...account,
            type: 'cloud',
            connection_status: isConnected ? 'connected' : 'disconnected'
          };
        });
        
        logger.info('Cloud accounts normalized', {
          userId,
          tenantId,
          connectedCount: normalizedCloudAccounts.filter(a => a.connection_status === 'connected').length,
          disconnectedCount: normalizedCloudAccounts.filter(a => a.connection_status === 'disconnected').length,
          details: normalizedCloudAccounts.map(a => ({
            id: a.id,
            name: a.account_name,
            status: a.status,
            connection_status: a.connection_status
          }))
        });
      } catch (cloudError) {
        logger.error('Error loading Cloud accounts', {
          userId,
          tenantId,
          error: cloudError.message
        });
        // Continue even if Cloud accounts fail
      }

      // Get all Evolution instances for the tenant (ALL instances, not just connected, but only active ones)
      try {
        logger.info('🔍 User requesting Evolution instances', { userId, tenantId });
        
        const evolutionQuery = `
          SELECT id, evolution_instance_id, instance_name, phone_number, status
          FROM evolution_instances
          WHERE tenant_id = ? AND (is_active = 1 OR is_active IS NULL)
          ORDER BY instance_name ASC
        `;

        const [evolutionInstances] = await pool.execute(evolutionQuery, [tenantId]);
        
        logger.info('✅ Evolution instances found for user', {
          userId,
          tenantId,
          count: evolutionInstances.length,
          instances: evolutionInstances.map(i => ({
            id: i.id,
            name: i.instance_name,
            status: i.status
          }))
        });
        
        normalizedEvolutionAccounts = evolutionInstances.map(instance => ({
          id: instance.id,
          type: 'evolution',
          instance_id: instance.evolution_instance_id,
          account_name: instance.instance_name,
          phone_number: instance.phone_number,
          status: instance.status || 'disconnected',
          connection_status: instance.status === 'connected' ? 'connected' : 'disconnected',
          is_evolution: true
        }));
        
        logger.info('✅ Evolution instances normalized for user', {
          userId,
          tenantId,
          normalizedCount: normalizedEvolutionAccounts.length
        });
      } catch (evolutionError) {
        logger.error('❌ Error loading Evolution instances for user', {
          userId,
          tenantId,
          error: evolutionError.message,
          code: evolutionError.code,
          sqlState: evolutionError.sqlState
        });
        // Continue even if Evolution instances fail (table might not exist)
      }

      // Combine both sources
      const allAccounts = [...normalizedCloudAccounts, ...normalizedEvolutionAccounts];

      logger.info('📊 Total accounts returned to user', {
        userId,
        tenantId,
        cloudCount: normalizedCloudAccounts.length,
        evolutionCount: normalizedEvolutionAccounts.length,
        totalCount: allAccounts.length,
        accounts: allAccounts.map(a => ({
          id: a.id,
          name: a.account_name,
          type: a.type,
          status: a.status || a.connection_status
        }))
      });

      return res.json({
        success: true,
        data: allAccounts
      });
    } catch (error) {
      logger.error('Error getting accounts', {
        error: error.message,
        stack: error.stack,
        userId: req.user?.id,
        tenantId: req.tenantId || req.user?.tenantId
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load accounts',
        error: error.message
      });
    }
  }

  /**
   * Get pipeline stages for current tenant (read-only)
   * GET /api/user/whatsapp-cloud/pipeline-stages
   */
  static async getPipelineStages(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;

      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      const [stages] = await pool.execute(`
        SELECT stage_key, stage_name, stage_color, stage_icon, stage_order
        FROM pipeline_stages
        WHERE tenant_id = ? AND active = TRUE
        ORDER BY stage_order ASC
      `, [tenantId]);

      return res.json({
        success: true,
        data: stages
      });
    } catch (error) {
      logger.error('Error getting pipeline stages', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load pipeline stages'
      });
    }
  }

  /**
   * Get stores for transfer
   * GET /api/user/whatsapp-cloud/stores
   */
  static async getStores(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;

      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      const [stores] = await pool.execute(`
        SELECT id, name, description
        FROM stores
        WHERE tenant_id = ? AND is_active = TRUE
        ORDER BY name ASC
      `, [tenantId]);

      return res.json({
        success: true,
        data: stores
      });
    } catch (error) {
      logger.error('Error getting stores', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load stores'
      });
    }
  }

  /**
   * Get departments for transfer
   * GET /api/user/whatsapp-cloud/departments
   */
  static async getDepartments(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;

      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      const [departments] = await pool.execute(`
        SELECT id, name, description
        FROM departments
        WHERE tenant_id = ? AND is_active = TRUE
        ORDER BY name ASC
      `, [tenantId]);

      return res.json({
        success: true,
        data: departments
      });
    } catch (error) {
      logger.error('Error getting departments', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load departments'
      });
    }
  }

  // ============================================================================
  // ENHANCED AUXILIARY METHODS - ROBUST IMPLEMENTATIONS
  // ============================================================================

  /**
   * Validate conversation access based on user store/department
   * @param {Object} conversation - Conversation object
   * @param {Object} user - User object with store/department
   * @returns {Object} { allowed: boolean, reason: string }
   */
  static validateConversationAccess(conversation, user) {
    try {
      // Admin users can access any conversation
      if (user.role === 'admin') {
        return { allowed: true, reason: 'Admin access' };
      }

      const hasStore = !!(user.store || user.store_id);
      const hasDepartment = !!(user.department || user.department_id);
      if (!hasStore && !hasDepartment) {
        return { allowed: true, reason: 'User without assignment' };
      }

      // If conversation is not assigned to any store/department, allow access (stores only)
      if (!conversation.store_id && !conversation.department_id && 
          !conversation.transferred_to_store && !conversation.transferred_to_department) {
        return user.department
          ? { allowed: false, reason: 'This conversation is not assigned to your department' }
          : { allowed: true, reason: 'Unassigned conversation' };
      }

      // Check department access (departments only see transferred conversations)
      if (user.department || user.department_id) {
        const departmentValue = user.department ? String(user.department) : null;
        const departmentIdValue = user.department_id ? String(user.department_id) : null;
        const transferredValue = conversation.transferred_to_department !== null && conversation.transferred_to_department !== undefined
          ? String(conversation.transferred_to_department)
          : '';
        if (transferredValue && (transferredValue === departmentValue || transferredValue === departmentIdValue)) {
          return { allowed: true, reason: 'Department transfer match' };
        }
        return { 
          allowed: false, 
          reason: 'This conversation is not transferred to your department' 
        };
      }

      // Check store access (stores see unassigned conversations and their store conversations)
      if (user.store || user.store_id) {
        // Store users can access:
        // 1. Unassigned conversations (no store_id, no department_id, no transfers)
        // 2. Conversations assigned to their store
        // 3. Conversations transferred to their store
        // 4. Conversations they personally claimed (even if transferred elsewhere)
        // IMPORTANT: Stores cannot see conversations transferred to departments (unless they claimed it)
        
        // If user already claimed this conversation, allow access
        if (conversation.claimed_by_user_id && String(conversation.claimed_by_user_id) === String(user.id)) {
          return { allowed: true, reason: 'Already claimed by user' };
        }
        
        // If conversation was transferred to a department, store cannot access it
        if (conversation.transferred_to_department) {
          return { 
            allowed: false, 
            reason: 'This conversation was transferred to a department' 
          };
        }
        
        const isUnassigned = !conversation.store_id && !conversation.department_id && 
                           !conversation.transferred_to_store && !conversation.transferred_to_department;
        const storeValue = user.store ? String(user.store) : null;
        const storeIdValue = user.store_id ? String(user.store_id) : null;
        const storedStoreId = conversation.store_id !== null && conversation.store_id !== undefined
          ? String(conversation.store_id)
          : '';
        const storedTransferredStore = conversation.transferred_to_store !== null && conversation.transferred_to_store !== undefined
          ? String(conversation.transferred_to_store)
          : '';
        const isStoreConversation = (storedStoreId && (storedStoreId === storeValue || storedStoreId === storeIdValue)) || 
                                  (storedTransferredStore && (storedTransferredStore === storeValue || storedTransferredStore === storeIdValue));
        const hasNoDepartment = !conversation.department_id && !conversation.transferred_to_department;
        
        if (isUnassigned || (isStoreConversation && hasNoDepartment)) {
          return { allowed: true, reason: 'Store access' };
        }
        return { 
          allowed: false, 
          reason: 'This conversation is not available to your store' 
        };
      }

      return { allowed: false, reason: 'No store or department assigned to user' };
    } catch (error) {
      logger.error('Error validating conversation access', { error: error.message });
      return { allowed: false, reason: 'Access validation error' };
    }
  }

  /**
   * Send message via WhatsApp Cloud API
   * @param {Object} conversation - Conversation with account details
   * @param {string} message - Message text
   * @param {string} conversationId - Conversation ID
   * @returns {Object} { success: boolean, messageId?: string, error?: string }
   */
  static async sendCloudMessage(conversation, message, conversationId) {
    try {
      logger.info('Attempting to send Cloud message', {
        conversationId,
        phoneNumberId: conversation.phone_number_id,
        hasAccessToken: !!conversation.access_token,
        contactPhone: conversation.contact_phone,
        messageLength: message?.length
      });

      // Validate required fields
      if (!conversation.phone_number_id) {
        return {
          success: false,
          error: 'Missing phone_number_id in conversation account'
        };
      }

      if (!conversation.access_token) {
        return {
          success: false,
          error: 'Missing access_token in conversation account'
        };
      }

      if (!conversation.contact_phone) {
        return {
          success: false,
          error: 'Missing contact_phone in conversation'
        };
      }

      // Check customer care window (last inbound within 24h)
      const [lastInboundRows] = await pool.execute(
        `SELECT timestamp, created_at FROM whatsapp_cloud_messages 
         WHERE conversation_id = ? AND direction = 'inbound' 
         ORDER BY COALESCE(timestamp, created_at) DESC LIMIT 1`,
        [conversationId]
      );
      
      let lastInbound = null;
      let hoursSinceLastInbound = null;
      
      if (lastInboundRows.length > 0) {
        const timestamp = lastInboundRows[0].timestamp || lastInboundRows[0].created_at;
        // Handle both Unix timestamp formats (seconds or milliseconds)
        if (timestamp > 1000000000000) {
          // Timestamp in milliseconds
          lastInbound = new Date(timestamp);
        } else {
          // Timestamp in seconds (convert to milliseconds)
          lastInbound = new Date(timestamp * 1000);
        }
        hoursSinceLastInbound = (Date.now() - lastInbound.getTime()) / (1000 * 60 * 60);
      }
      
      logger.info('Customer care window check', {
        conversationId,
        rawTimestamp: lastInboundRows.length > 0 ? lastInboundRows[0].timestamp : null,
        lastInbound: lastInbound?.toISOString(),
        hoursSinceLastInbound,
        withinWindow: hoursSinceLastInbound ? hoursSinceLastInbound < 24 : false
      });

      if (!lastInbound || hoursSinceLastInbound > 24) {
        return {
          success: false,
          error: `Customer care window expired. Last inbound message was ${hoursSinceLastInbound ? Math.round(hoursSinceLastInbound) : 'never'} hours ago. Please use an approved template.`
        };
      }

      // Normalize destination phone (digits only, international format)
      let toPhone = String(conversation.contact_phone || '').trim();
      toPhone = toPhone.replace(/[^\d]/g, '');

      if (!toPhone) {
        return {
          success: false,
          error: 'Invalid phone number'
        };
      }

      const result = await queueMessage({
        channel: 'cloud',
        tenantId: conversation.tenant_id || null,
        accountId: conversation.account_id,
        to: toPhone,
        messageType: 'text',
        content: message,
        awaitSend: true
      });

      if (!result.success) {
        return {
          success: false,
          error: result.error || 'Failed to send message'
        };
      }

      return {
        success: true,
        messageId: result.messageId || null
      };
    } catch (error) {
      logger.error('Error in sendCloudMessage', { 
        error: error.message, 
        stack: error.stack,
        conversationId,
        phoneNumberId: conversation?.phone_number_id,
        hasAccessToken: !!conversation?.access_token
      });
      return {
        success: false,
        error: error.message
      };
    }
  }

  /**
   * Send media message via WhatsApp Cloud API
   * @param {Object} conversation - Conversation object with account details
   * @param {Object} file - Uploaded file object
   * @param {string} mediaType - Type of media (image, video, audio, document)
   * @param {string} caption - Optional caption
   * @param {string} conversationId - Conversation ID for logging
   * @returns {Object} { success: boolean, whatsappMessageId?: string, error?: string }
   */
  static async sendCloudMediaMessage(conversation, file, mediaType, caption, conversationId) {
    try {
      logger.info('Attempting to send Cloud media message', {
        conversationId,
        phoneNumberId: conversation.phone_number_id,
        hasAccessToken: !!conversation.access_token,
        contactPhone: conversation.contact_phone,
        mediaType,
        fileName: file.filename,
        fileSize: file.size,
        hasCaption: !!caption
      });

      // Validate required fields
      if (!conversation.phone_number_id) {
        return {
          success: false,
          error: 'Missing phone_number_id in conversation account'
        };
      }

      if (!conversation.access_token) {
        return {
          success: false,
          error: 'Missing access_token in conversation account'
        };
      }

      if (!conversation.contact_phone) {
        return {
          success: false,
          error: 'Missing contact_phone in conversation'
        };
      }

      // Normalize destination phone
      let toPhone = String(conversation.contact_phone || '').trim();
      if (toPhone && !toPhone.startsWith('+')) {
        toPhone = `+${toPhone}`;
      }

      if (!toPhone) {
        return {
          success: false,
          error: 'Invalid phone number'
        };
      }
      const result = await queueMessage({
        channel: 'cloud',
        tenantId: conversation.tenant_id || null,
        accountId: conversation.account_id,
        to: toPhone,
        messageType: 'media',
        awaitSend: true,
        metadata: {
          payload: {
            media: {
              mediaType,
              filePath: file.path,
              fileName: file.filename,
              mimeType: file.mimetype,
              caption: caption?.trim() || null
            }
          }
        }
      });

      if (!result.success) {
        return {
          success: false,
          error: result.error || 'Failed to send media'
        };
      }

      return {
        success: true,
        whatsappMessageId: result.messageId || null
      };

    } catch (error) {
      logger.error('Error in sendCloudMediaMessage', { 
        error: error.message, 
        stack: error.stack,
        conversationId,
        phoneNumberId: conversation?.phone_number_id,
        hasAccessToken: !!conversation?.access_token
      });
      return {
        success: false,
        error: error.message
      };
    }
  }

  /**
   * Send message via WhatsApp Web
   * @param {number} tenantId - Tenant ID
   * @param {string} phoneNumber - Phone number
   * @param {string} message - Message text
   * @param {string} conversationId - Conversation ID
   * @returns {Object} { success: boolean, messageId?: string, error?: string }
   */
  static async sendWebMessage(tenantId, phoneNumber, message, conversationId, options = {}) {
    try {
      const result = await queueMessage({
        channel: 'web',
        tenantId,
        to: phoneNumber,
        messageType: 'text',
        content: message,
        conversationId,
        awaitSend: true,
        metadata: {
          options
        }
      });

      return {
        success: result.success,
        messageId: result.messageId,
        error: result.error
      };
    } catch (error) {
      logger.error('Error in sendWebMessage', { error: error.message, tenantId, conversationId });
      return {
        success: false,
        error: error.message
      };
    }
  }

  /**
   * Get WhatsApp Web messages for a conversation
   * @param {Object} req - Express request
   * @param {Object} res - Express response
   */
  static async getWebMessages(req, res) {
    try {
      const conversationId = req.params.id;
      const tenantId = req.tenantId || req.user.tenantId;
      logger.info('Getting WhatsApp Web messages', {
        conversationId,
        tenantId,
        userId: req.user.id
      });

      const page = Math.max(1, parseInt(req.query.page || '1', 10));
      const limit = Math.min(200, Math.max(1, parseInt(req.query.limit || '50', 10)));
      const offset = (page - 1) * limit;

      let resolvedConversationId = conversationId;
      if (String(conversationId).includes('@')) {
        const rawJid = String(conversationId);
        let numberPart = rawJid.split('@')[0] || '';
        if (numberPart.includes(':')) {
          numberPart = numberPart.split(':')[0];
        }
        let normalizedPhone = numberPart.replace(/^\+/, '');
        let normalizedRemoteJid = rawJid;
        if (normalizedRemoteJid.includes(':')) {
          const parts = normalizedRemoteJid.split('@');
          if (parts.length === 2) {
            const num = parts[0].split(':')[0];
            normalizedRemoteJid = `${num}@${parts[1]}`;
          }
        }
        const lidJid = normalizedPhone ? `${normalizedPhone}@lid` : normalizedRemoteJid;
        const waJid = normalizedPhone ? `${normalizedPhone}@s.whatsapp.net` : normalizedRemoteJid;
        const phoneLike = normalizedPhone ? `%${normalizedPhone.slice(-10)}` : normalizedRemoteJid;
        const [existing] = await pool.query(
          `SELECT id FROM conversations 
           WHERE tenant_id = ? AND (
             remote_jid = ?
             OR remote_jid = ?
             OR remote_jid = ?
             OR phone_number = ?
             OR phone_number = ?
             OR phone_number LIKE ?
           )
           ORDER BY updated_at DESC
           LIMIT 1`,
          [tenantId, normalizedRemoteJid, lidJid, waJid, normalizedPhone, `+${normalizedPhone}`, phoneLike]
        );
        if (existing.length) {
          resolvedConversationId = existing[0].id;
        } else if (normalizedPhone) {
          const [insertResult] = await pool.query(
            'INSERT INTO conversations (tenant_id, phone_number, remote_jid, contact_name, last_message, last_message_time, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())',
            [tenantId, normalizedPhone, normalizedRemoteJid, normalizedPhone, '', 'waiting']
          );
          resolvedConversationId = insertResult.insertId;
        }
      } else {
        const [conversations] = await pool.query(
          'SELECT id FROM conversations WHERE id = ? AND tenant_id = ? LIMIT 1',
          [conversationId, tenantId]
        );
        if (conversations.length === 0) {
          return res.status(404).json({
            success: false,
            message: 'Conversation not found'
          });
        }
      }

      const [[countRow]] = await pool.query(
        'SELECT COUNT(*) as total FROM whatsapp_messages WHERE tenant_id = ? AND conversation_id = ?',
        [tenantId, resolvedConversationId]
      );

      const [messages] = await pool.query(
        `SELECT * FROM whatsapp_messages
         WHERE tenant_id = ? AND conversation_id = ?
         ORDER BY created_at ASC
         LIMIT ? OFFSET ?`,
        [tenantId, resolvedConversationId, limit, offset]
      );

      const formattedMessages = messages.map(message => ({
        ...message,
        direction: message.direction === 'outgoing' ? 'outbound' : 'inbound',
        text_content: message.content,
        sent_by_user_id: message.sender_user_id || null,
        sent_by_name: message.sender_name || null,
        sent_by_store: message.sender_store || null,
        sent_by_department: message.sender_department || null
      }));

      return res.json({
        success: true,
        data: formattedMessages,
        pagination: {
          page,
          pages: Math.max(1, Math.ceil((countRow?.total || 0) / limit)),
          total: countRow?.total || 0,
          limit
        },
        meta: {
          source: 'whatsapp_web',
          conversationId: resolvedConversationId,
          messageCount: formattedMessages.length
        }
      });
    } catch (error) {
      logger.error('Error getting WhatsApp Web messages', {
        error: error.message,
        conversationId: req.params.id
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load WhatsApp Web messages'
      });
    }
  }

  /**
   * Upload and send media file - ENHANCED ROBUST VERSION
   * Supports images, videos, documents, audio files
   * POST /api/user/whatsapp-cloud/conversations/:id/send-media
   */
  static async sendMediaMessage(req, res) {
    const uploadSingle = upload.single('media');
    
    uploadSingle(req, res, async (uploadError) => {
      if (uploadError) {
        logger.error('Media upload error', { error: uploadError.message });
        return res.status(400).json({
          success: false,
          message: 'File upload failed: ' + uploadError.message
        });
      }

      const connection = await pool.getConnection();
      
      try {
        await connection.beginTransaction();
        
        const conversationId = req.params.id;
        const { caption = '', source = 'whatsapp_cloud' } = req.body;
        const userId = req.user.id;
        const tenantId = req.tenantId || req.user.tenantId;
        const file = req.file;

        if (!file) {
          await connection.rollback();
          return res.status(400).json({
            success: false,
            message: 'No media file provided'
          });
        }

        logger.info('Enhanced media send request', {
          conversationId,
          userId,
          tenantId,
          fileName: file.filename,
          fileSize: file.size,
          mimeType: file.mimetype,
          source
        });

        if (source === 'whatsapp_web') {
          let resolvedConversationId = conversationId;
          let webConversation = null;
          if (String(conversationId).includes('@')) {
            const rawJid = String(conversationId);
            let numberPart = rawJid.split('@')[0] || '';
            if (numberPart.includes(':')) {
              numberPart = numberPart.split(':')[0];
            }
            let normalizedPhone = numberPart.replace(/^\+/, '');
            let normalizedRemoteJid = rawJid;
            if (normalizedRemoteJid.includes(':')) {
              const parts = normalizedRemoteJid.split('@');
              if (parts.length === 2) {
                const num = parts[0].split(':')[0];
                normalizedRemoteJid = `${num}@${parts[1]}`;
              }
            }
            const lidJid = normalizedPhone ? `${normalizedPhone}@lid` : normalizedRemoteJid;
            const waJid = normalizedPhone ? `${normalizedPhone}@s.whatsapp.net` : normalizedRemoteJid;
            const phoneLike = normalizedPhone ? `%${normalizedPhone.slice(-10)}` : normalizedRemoteJid;
            const [existing] = await connection.execute(
              `SELECT * FROM conversations 
               WHERE tenant_id = ? AND (
                 remote_jid = ?
                 OR remote_jid = ?
                 OR remote_jid = ?
                 OR phone_number = ?
                 OR phone_number = ?
                 OR phone_number LIKE ?
               )
               ORDER BY updated_at DESC
               LIMIT 1`,
              [tenantId, normalizedRemoteJid, lidJid, waJid, normalizedPhone, `+${normalizedPhone}`, phoneLike]
            );
            if (existing.length) {
              webConversation = existing[0];
              resolvedConversationId = webConversation.id;
            } else if (normalizedPhone) {
              const [insertResult] = await connection.execute(
                'INSERT INTO conversations (tenant_id, phone_number, remote_jid, contact_name, last_message, last_message_time, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())',
                [tenantId, normalizedPhone, normalizedRemoteJid, normalizedPhone, caption || '[Media]', 'waiting']
              );
              resolvedConversationId = insertResult.insertId;
              const [created] = await connection.execute(
                'SELECT * FROM conversations WHERE id = ? AND tenant_id = ? LIMIT 1',
                [resolvedConversationId, tenantId]
              );
              webConversation = created[0] || null;
            }
          } else {
            const [webConversations] = await connection.execute(
              `SELECT * FROM conversations WHERE id = ? AND tenant_id = ? LIMIT 1`,
              [conversationId, tenantId]
            );
            webConversation = webConversations[0] || null;
          }
          if (!webConversation) {
            await connection.rollback();
            return res.status(404).json({
              success: false,
              message: 'Conversation not found'
            });
          }
          let mediaType = 'document';
          if (file.mimetype.startsWith('image/')) mediaType = 'image';
          else if (file.mimetype.startsWith('audio/')) mediaType = 'audio';

          const mediaUrl = `/uploads/tenant_${tenantId}/${file.filename}`;
          const io = req.app.get('io');
          const whatsappService = getWhatsAppService(io);
          const sendResult = await whatsappService.sendMediaMessage(
            tenantId,
            webConversation.phone_number || webConversation.remote_jid,
            file.path,
            caption,
            resolvedConversationId,
            {
              senderUserId: userId,
              senderName: req.user.name || null,
              senderStore: req.user.store || null,
              senderDepartment: req.user.department || null,
              metadata: { source: 'whatsapp_web' },
              mediaUrl,
              mediaSize: file.size
            }
          );

          if (!sendResult.success) {
            await connection.rollback();
            return res.status(500).json({
              success: false,
              message: sendResult.error || 'Failed to send media'
            });
          }

          await connection.commit();

          return res.json({
            success: true,
            message: 'Media sent successfully',
            data: {
              messageId: sendResult.whatsappMessageId || sendResult.messageId,
              mediaType,
              mediaUrl,
              fileSize: file.size,
              source: 'whatsapp_web',
              conversationId: resolvedConversationId
            }
          });
        }

        // Check if this is an Evolution instance conversation first
        let isEvolutionConversation = false;
        let evolutionConversation = null;
        
        // Try to find Evolution conversation
        const [evolutionCheck] = await connection.execute(
          `SELECT c.*, ei.id as instance_id, ei.instance_name
           FROM conversations c
           INNER JOIN whatsapp_messages wm ON c.id = wm.conversation_id
           INNER JOIN evolution_instances ei ON wm.instance_id = ei.id
           WHERE c.id = ? AND c.tenant_id = ?
           LIMIT 1`,
          [conversationId, tenantId]
        );

        if (evolutionCheck.length > 0) {
          isEvolutionConversation = true;
          evolutionConversation = evolutionCheck[0];
          
          // For Evolution conversations, check if user has access based on transfer/assignment
          const userHasAccess = (
            // User is assigned to the conversation
            evolutionConversation.assigned_user_id === userId ||
            // Conversation is transferred to user's store
            (req.user.store && evolutionConversation.transferred_to_store === req.user.store) ||
            (req.user.store_id && evolutionConversation.transferred_to_store === String(req.user.store_id)) ||
            // Conversation is transferred to user's department
            (req.user.department && evolutionConversation.transferred_to_department === req.user.department) ||
            (req.user.department_id && evolutionConversation.transferred_to_department === String(req.user.department_id))
          );

          if (!userHasAccess) {
            await connection.rollback();
            return res.status(403).json({
              success: false,
              message: 'Conversation not found or not claimed by you'
            });
          }

          // Handle Evolution media sending - Direct DB + Background API
          let mediaType = 'document';
          if (file.mimetype.startsWith('image/')) mediaType = 'image';
          else if (file.mimetype.startsWith('audio/')) mediaType = 'audio';

          // Construct full media URL for Evolution API (needs to be publicly accessible)
          let baseUrl = process.env.APP_URL || `${req.protocol}://${req.get('host')}`;
          // Remove trailing slash to avoid double slashes
          baseUrl = baseUrl.replace(/\/$/, '');
          const mediaUrl = `${baseUrl}/uploads/tenant_${tenantId}/${file.filename}`;
          const messageId = `msg_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
          
          // Save message to database first (ensures it's recorded)
          await connection.execute(
            `INSERT INTO whatsapp_messages 
             (conversation_id, instance_id, tenant_id, phone_number, whatsapp_message_id, direction, message_type, content, media_url, caption, status, created_at)
             VALUES (?, ?, ?, ?, ?, 'outgoing', ?, ?, ?, ?, 'sent', NOW())`,
            [
              conversationId,
              evolutionConversation.instance_id,
              tenantId,
              evolutionConversation.phone_number || evolutionConversation.remote_jid,
              messageId,
              mediaType,
              caption || `[${mediaType.toUpperCase()}]`,
              mediaUrl,
              caption || null
            ]
          );
          
          // Update conversation last message
          await connection.execute(
            `UPDATE conversations 
             SET last_message = ?, 
                 last_message_time = NOW(), 
                 status = CASE 
                   WHEN status = 'waiting' THEN 'attended'
                   ELSE status 
                 END,
                 attended_at = CASE 
                   WHEN status = 'waiting' AND attended_at IS NULL THEN NOW()
                   ELSE attended_at 
                 END,
                 updated_at = NOW()
             WHERE id = ?`,
            [caption || `[${mediaType.toUpperCase()}]`, conversationId]
          );

          await connection.commit();

          // Try to send via Evolution API in background (don't wait for response)
          setImmediate(async () => {
            try {
              const result = await WhatsAppCloudUserController.sendEvolutionMediaMessage(
                evolutionConversation,
                file,
                mediaType,
                caption,
                conversationId,
                {
                  senderUserId: userId,
                  senderName: req.user.name || null,
                  senderStore: req.user.store || null,
                  senderDepartment: req.user.department || null,
                  mediaUrl,
                  mediaSize: file.size,
                  tenantId
                }
              );
              
              if (!result.success) {
                logger.error('Evolution API media send failed', { 
                  conversationId,
                  error: result.error
                });
              }
            } catch (error) {
              logger.error('Background Evolution API call failed', { 
                conversationId,
                error: error.message
              });
            }
          });

          logger.info('Evolution media message saved successfully (DB + background API)', {
            conversationId,
            messageId,
            mediaType,
            fileSize: file.size
          });

          return res.json({
            success: true,
            message: 'Media sent successfully',
            data: {
              messageId: messageId,
              mediaType,
              mediaUrl,
              fileSize: file.size,
              source: 'evolution',
              conversationId: conversationId
            }
          });
        }

        // Verify conversation and claim for WhatsApp Cloud
        const [conversations] = await connection.execute(
          `SELECT c.*, a.phone_number_id, a.access_token
           FROM whatsapp_cloud_conversations c
           LEFT JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
           WHERE c.id = ? AND c.tenant_id = ? AND c.claimed_by_user_id = ?
           FOR UPDATE`,
          [conversationId, tenantId, userId]
        );

        if (conversations.length === 0) {
          await connection.rollback();
          return res.status(403).json({
            success: false,
            message: 'Conversation not found or not claimed by you'
          });
        }

        const conversation = conversations[0];

        // Determine media type
        let mediaType = 'document';
        if (file.mimetype.startsWith('image/')) mediaType = 'image';
        // else if (file.mimetype.startsWith('video/')) mediaType = 'video'; // DISABLED: Video upload temporarily disabled
        else if (file.mimetype.startsWith('audio/')) mediaType = 'audio';

        const trimmedCaption = caption ? caption.trim() : '';
        const shouldSendCaptionAsText = source !== 'whatsapp_web' && mediaType === 'audio' && trimmedCaption;
        const cloudCaption = shouldSendCaptionAsText ? '' : caption;

        // Send media based on source
        let sendResult;
        if (source === 'whatsapp_web') {
          const io = req.app.get('io');
          const whatsappService = getWhatsAppService(io);
          sendResult = await whatsappService.sendMediaMessage(
            tenantId, 
            conversation.contact_phone, 
            file.path, 
            caption, 
            conversationId
          );
        } else {
          // Send via WhatsApp Cloud API - REAL IMPLEMENTATION
          sendResult = await WhatsAppCloudUserController.sendCloudMediaMessage(
            conversation, 
            file, 
            mediaType, 
            cloudCaption, 
            conversationId
          );
        }

        if (!sendResult.success) {
          await connection.rollback();
          return res.status(500).json({
            success: false,
            message: sendResult.error || 'Failed to send media'
          });
        }

        // Save media message to database
        const mediaUrl = `/uploads/tenant_${tenantId}/${file.filename}`;
        
        const insertQuery = `INSERT INTO whatsapp_cloud_messages 
           (conversation_id, message_id, direction, message_type, text_content, media_url, filename, caption, status, timestamp, sent_by_user_id, created_at)
           VALUES (?, ?, 'outbound', ?, ?, ?, ?, ?, 'sent', NOW(), ?, NOW())`;
        
        const mediaTextContent = shouldSendCaptionAsText ? `[${mediaType.toUpperCase()}]` : (caption || '[Media]');
        const mediaCaption = shouldSendCaptionAsText ? null : (caption || null);
        const insertParams = [
          conversationId,
          sendResult.whatsappMessageId || sendResult.messageId,
          mediaType,
          mediaTextContent,
          mediaUrl,
          file.originalname,
          mediaCaption,
          userId
        ];
        
        const [messageResult] = await connection.execute(insertQuery, insertParams);

        let captionMessageId = null;
        if (shouldSendCaptionAsText) {
          const captionSendResult = await WhatsAppCloudUserController.sendCloudMessage(
            conversation,
            trimmedCaption,
            conversationId
          );

          if (captionSendResult.success) {
            captionMessageId = captionSendResult.messageId || `msg_${Date.now()}`;
            await connection.execute(
              `INSERT INTO whatsapp_cloud_messages 
               (conversation_id, message_id, direction, message_type, text_content, status, timestamp, sent_by_user_id, created_at)
               VALUES (?, ?, 'outbound', 'text', ?, 'sent', NOW(), ?, NOW())`,
              [
                conversationId,
                captionMessageId,
                trimmedCaption,
                userId
              ]
            );
          }
        }

        const lastMessageText = shouldSendCaptionAsText
          ? (captionMessageId ? trimmedCaption : `[${mediaType.toUpperCase()}]`)
          : (caption || `[${mediaType.toUpperCase()}]`);

        // Update conversation
        await connection.execute(
          `UPDATE whatsapp_cloud_conversations 
           SET last_message_text = ?, 
               last_message_time = NOW(), 
               last_message_from = 'business',
               attended_at = CASE 
                 WHEN attended_at IS NULL THEN NOW()
                 ELSE attended_at 
               END,
               updated_at = NOW()
           WHERE id = ?`,
          [lastMessageText, conversationId]
        );

        await connection.commit();

        logger.info('Enhanced media sent successfully', {
          conversationId,
          userId,
          mediaType,
          fileSize: file.size
        });

        // Emit WebSocket event
        const io = req.app.get('io');
        if (io) {
          const tenantNamespace = io.of(`/tenant/${tenantId}`);
          tenantNamespace.emit('media-message-sent', {
            conversationId,
            message: {
              id: messageResult ? messageResult.insertId : null,
              messageId: sendResult.whatsappMessageId || sendResult.messageId,
              mediaType,
              mediaUrl,
              caption,
              direction: 'outbound',
              timestamp: new Date().toISOString(),
              sent_by_name: req.user.name
            }
          });
        }

        return res.json({
          success: true,
          message: 'Media sent successfully',
          data: {
            messageId: sendResult.whatsappMessageId || sendResult.messageId,
            mediaType,
            mediaUrl,
            fileSize: file.size
          }
        });
      } catch (error) {
        await connection.rollback();
        logger.error('Error sending enhanced media', {
          error: error.message,
          stack: error.stack,
          conversationId: req.params.id,
          userId: req.user.id,
          tenantId: req.tenantId || req.user.tenantId,
          fileName: req.file?.filename,
          fileSize: req.file?.size,
          mimeType: req.file?.mimetype
        });

        return res.status(500).json({
          success: false,
          message: 'Failed to send media: ' + error.message
        });
      } finally {
        connection.release();
      }
    });
  }

  /**
   * Send WooCommerce product - ENHANCED VERSION
   * Shares product information with customer
   * POST /api/user/whatsapp-cloud/conversations/:id/send-product
   */
  static async sendProductMessage(req, res) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const conversationId = req.params.id;
      const { productId, productData, customMessage, source = 'whatsapp_cloud' } = req.body;
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;

      logger.info('Enhanced product send request', {
        conversationId,
        userId,
        tenantId,
        productId,
        hasCustomMessage: !!customMessage
      });

      // Validate input
      if (!productId && !productData) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Product ID or product data is required'
        });
      }

      let conversation = null;
      if (source !== 'whatsapp_web') {
        const [conversations] = await connection.execute(
          `SELECT c.*, a.phone_number_id, a.access_token, a.account_name, a.status as account_status
           FROM whatsapp_cloud_conversations c
           LEFT JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
           WHERE c.id = ? AND c.tenant_id = ? AND c.claimed_by_user_id = ?
           FOR UPDATE`,
          [conversationId, tenantId, userId]
        );

        if (conversations.length === 0) {
          await connection.rollback();
          return res.status(403).json({
            success: false,
            message: 'Conversation not found or not claimed by you'
          });
        }

        conversation = conversations[0];
      }

      // Get or validate product data
      let product = productData;
      const resolvedProductId = productId || productData?.id || productData?.wc_product_id || null;
      if (resolvedProductId) {
        const [products] = await connection.execute(
          `SELECT 
             id, wc_product_id, name, description, short_description, sku, price, regular_price, sale_price,
             stock_status, image_url, thumbnail_url, permalink
           FROM woocommerce_products
           WHERE tenant_id = ? AND is_active = TRUE AND (id = ? OR wc_product_id = ?)
           LIMIT 1`,
          [tenantId, resolvedProductId, resolvedProductId]
        );
        if (products.length > 0) {
          const row = products[0];
          product = {
            id: row.id,
            wc_product_id: row.wc_product_id,
            name: row.name,
            description: row.short_description || row.description || '',
            price: row.price,
            regular_price: row.regular_price,
            sale_price: row.sale_price,
            stock_status: row.stock_status,
            sku: row.sku,
            permalink: row.permalink,
            images: row.thumbnail_url || row.image_url ? [{ src: row.thumbnail_url || row.image_url }] : []
          };
        }
      }

      // Validate required product fields
      if (!product.name || !product.price) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Product must have name and price'
        });
      }

      // Format product message
      const productMessage = WhatsAppCloudUserController.formatProductMessage(product, customMessage);

      if (source === 'whatsapp_web') {
        let resolvedConversationId = conversationId;
        let webConversation = null;
        if (String(conversationId).includes('@')) {
          const rawJid = String(conversationId);
          let numberPart = rawJid.split('@')[0] || '';
          if (numberPart.includes(':')) {
            numberPart = numberPart.split(':')[0];
          }
          let normalizedPhone = numberPart.replace(/^\+/, '');
          let normalizedRemoteJid = rawJid;
          if (normalizedRemoteJid.includes(':')) {
            const parts = normalizedRemoteJid.split('@');
            if (parts.length === 2) {
              const num = parts[0].split(':')[0];
              normalizedRemoteJid = `${num}@${parts[1]}`;
            }
          }
          const lidJid = normalizedPhone ? `${normalizedPhone}@lid` : normalizedRemoteJid;
          const waJid = normalizedPhone ? `${normalizedPhone}@s.whatsapp.net` : normalizedRemoteJid;
          const phoneLike = normalizedPhone ? `%${normalizedPhone.slice(-10)}` : normalizedRemoteJid;
          const [existing] = await connection.execute(
            `SELECT * FROM conversations 
             WHERE tenant_id = ? AND (
               remote_jid = ?
               OR remote_jid = ?
               OR remote_jid = ?
               OR phone_number = ?
               OR phone_number = ?
               OR phone_number LIKE ?
             )
             ORDER BY updated_at DESC
             LIMIT 1`,
            [tenantId, normalizedRemoteJid, lidJid, waJid, normalizedPhone, `+${normalizedPhone}`, phoneLike]
          );
          if (existing.length) {
            webConversation = existing[0];
            resolvedConversationId = webConversation.id;
          } else if (normalizedPhone) {
            const [insertResult] = await connection.execute(
              'INSERT INTO conversations (tenant_id, phone_number, remote_jid, contact_name, last_message, last_message_time, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())',
              [tenantId, normalizedPhone, normalizedRemoteJid, normalizedPhone, productMessage, 'waiting']
            );
            resolvedConversationId = insertResult.insertId;
            const [created] = await connection.execute(
              'SELECT * FROM conversations WHERE id = ? AND tenant_id = ? LIMIT 1',
              [resolvedConversationId, tenantId]
            );
            webConversation = created[0] || null;
          }
        } else {
          const [webConversations] = await connection.execute(
            `SELECT * FROM conversations WHERE id = ? AND tenant_id = ? LIMIT 1`,
            [conversationId, tenantId]
          );
          webConversation = webConversations[0] || null;
        }
        if (!webConversation) {
          await connection.rollback();
          return res.status(404).json({
            success: false,
            message: 'Conversation not found'
          });
        }
        const sendResult = await WhatsAppCloudUserController.sendWebMessage(
          tenantId,
          webConversation.phone_number || webConversation.remote_jid,
          productMessage,
          resolvedConversationId,
          {
            senderUserId: userId,
            senderName: req.user.name || null,
            senderStore: req.user.store || null,
            senderDepartment: req.user.department || null,
            metadata: { source: 'whatsapp_web', productId: product.id || product.wc_product_id || null }
          }
        );

        if (!sendResult.success) {
          await connection.rollback();
          return res.status(500).json({
            success: false,
            message: sendResult.error || 'Failed to send product'
          });
        }

        await connection.commit();

        return res.json({
          success: true,
          message: 'Product sent successfully',
          data: {
            messageId: sendResult.messageId,
            product,
            productMessage,
            source: 'whatsapp_web',
            conversationId: resolvedConversationId
          }
        });
      }

      // Send product message
      const sendResult = await WhatsAppCloudUserController.sendCloudMessage(
        conversation, 
        productMessage, 
        conversationId
      );

      if (!sendResult.success) {
        await connection.rollback();
        return res.status(500).json({
          success: false,
          message: sendResult.error || 'Failed to send product'
        });
      }

      // Save product message to database
      const [messageResult] = await connection.execute(
        `INSERT INTO whatsapp_cloud_messages 
         (tenant_id, account_id, conversation_id, message_id, from_phone, to_phone, direction, message_type, text_body, 
          status, timestamp, sent_by_user_id, created_at)
         VALUES (?, ?, ?, ?, ?, ?, 'outbound', 'product', ?, 'sent', ?, ?, NOW())`,
        [
          tenantId,
          conversation.account_id,
          conversationId,
          sendResult.messageId,
          conversation.phone_number_id,
          conversation.contact_phone,
          productMessage,
          Math.floor(Date.now() / 1000),
          userId
        ]
      );

      // Update conversation
      await connection.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET last_message_text = ?, 
             last_message_time = NOW(), 
             last_message_from = 'business',
             updated_at = NOW()
         WHERE id = ?`,
        [`Product: ${product.name}`, conversationId]
      );

      await connection.commit();

      logger.info('Enhanced product sent successfully', {
        conversationId,
        userId,
        productId,
        productName: product.name
      });

      // Emit WebSocket event
      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        tenantNamespace.emit('product-message-sent', {
          conversationId,
          message: {
            id: messageResult.insertId,
            messageId: sendResult.messageId,
            product,
            direction: 'outbound',
            timestamp: new Date().toISOString(),
            sent_by_name: req.user.name
          }
        });
      }

      return res.json({
        success: true,
        message: 'Product sent successfully',
        data: {
          messageId: sendResult.messageId,
          product,
          productMessage
        }
      });

    } catch (error) {
      await connection.rollback();
      logger.error('Error sending enhanced product', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        userId: req.user.id
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to send product'
      });
    } finally {
      connection.release();
    }
  }

  /**
   * Send invoice/payment link - ENHANCED VERSION
   * Creates and sends payment request to customer
   * POST /api/user/whatsapp-cloud/conversations/:id/send-invoice
   */
  static async sendInvoiceMessage(req, res) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const conversationId = req.params.id;
      const { 
        amount, 
        currency, 
        description, 
        paymentMethod
      } = req.body;
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;

      let resolvedCurrency = currency;
      if (!resolvedCurrency) {
        const [currencyRows] = await connection.execute(
          'SELECT code FROM currencies WHERE is_default = TRUE AND active = TRUE ORDER BY id LIMIT 1'
        );
        resolvedCurrency = currencyRows?.[0]?.code || 'USD';
      }
      resolvedCurrency = String(resolvedCurrency).toUpperCase();

      logger.info('Enhanced invoice send request', {
        conversationId,
        userId,
        tenantId,
        amount,
        currency: resolvedCurrency,
        paymentMethod
      });

      // Validate input
      if (!amount || amount <= 0) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Valid amount is required'
        });
      }

      if (!description) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Description is required'
        });
      }

      // Get conversation and verify claim
      const [conversations] = await connection.execute(
        `SELECT c.*, a.phone_number_id, a.access_token, a.account_name, a.status as account_status
         FROM whatsapp_cloud_conversations c
         LEFT JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
         WHERE c.id = ? AND c.tenant_id = ? AND c.claimed_by_user_id = ?
         FOR UPDATE`,
        [conversationId, tenantId, userId]
      );

      if (conversations.length === 0) {
        await connection.rollback();
        return res.status(403).json({
          success: false,
          message: 'Conversation not found or not claimed by you'
        });
      }

      const conversation = conversations[0];

      if (!paymentMethod || !['stripe', 'paypal'].includes(String(paymentMethod).toLowerCase())) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Payment method must be stripe or paypal'
        });
      }

      const normalizedMethod = String(paymentMethod).toLowerCase();
      const invoiceNumber = `PAY-${Date.now()}-${Math.random().toString(36).substr(2, 5).toUpperCase()}`;
      const invoiceRepository = new InvoiceRepository();
      const gateway = await invoiceRepository.getTenantPaymentGateway(tenantId, normalizedMethod);

      if (!gateway) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: `${normalizedMethod.toUpperCase()} is not configured`
        });
      }

      let paymentResult;
      if (normalizedMethod === 'stripe') {
        if (!gateway.stripe_secret_key) {
          await connection.rollback();
          return res.status(400).json({
            success: false,
            message: 'Stripe is not configured'
          });
        }
        const stripeService = new StripeService(gateway.stripe_secret_key);
        paymentResult = await stripeService.createPayment({
          amount,
          currency: resolvedCurrency,
          description,
          customer_name: conversation.contact_name,
          customer_phone: conversation.contact_phone,
          reference_id: invoiceNumber
        });
      } else {
        if (!gateway.paypal_client_id || !gateway.paypal_client_secret) {
          await connection.rollback();
          return res.status(400).json({
            success: false,
            message: 'PayPal is not configured'
          });
        }
        const sandboxMode = gateway.sandbox_mode !== false && gateway.sandbox_mode !== 0;
        const paypalService = new PayPalService(gateway.paypal_client_id, gateway.paypal_client_secret, sandboxMode);
        paymentResult = await paypalService.createPayment({
          amount,
          currency: resolvedCurrency,
          description,
          customer_name: conversation.contact_name,
          customer_phone: conversation.contact_phone,
          reference_id: invoiceNumber
        });
      }

      if (!paymentResult?.success) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: paymentResult?.error || 'Failed to create payment link'
        });
      }

      const invoice = {
        number: invoiceNumber,
        amount,
        currency: resolvedCurrency,
        description,
        paymentLink: paymentResult.payment_url,
        paymentMethod: normalizedMethod,
        customerInfo: {
          name: conversation.contact_name,
          phone: conversation.contact_phone
        }
      };

      const invoiceMessage = WhatsAppCloudUserController.formatInvoiceMessage(invoice);

      // Send invoice message
      const sendResult = await WhatsAppCloudUserController.sendCloudMessage(
        conversation, 
        invoiceMessage, 
        conversationId
      );

      if (!sendResult.success) {
        await connection.rollback();
        return res.status(500).json({
          success: false,
          message: sendResult.error || 'Failed to send invoice'
        });
      }

      // Save invoice message to database
      const [messageResult] = await connection.execute(
        `INSERT INTO whatsapp_cloud_messages 
         (tenant_id, account_id, conversation_id, message_id, from_phone, to_phone, direction, message_type, text_body, 
          status, timestamp, sent_by_user_id, created_at)
         VALUES (?, ?, ?, ?, ?, ?, 'outbound', 'invoice', ?, 'sent', ?, ?, NOW())`,
        [
          tenantId,
          conversation.account_id,
          conversationId,
          sendResult.messageId,
          conversation.phone_number_id,
          conversation.contact_phone,
          invoiceMessage,
          Math.floor(Date.now() / 1000),
          userId
        ]
      );

      // Update conversation
      await connection.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET last_message_text = ?, 
             last_message_time = NOW(), 
             last_message_from = 'business',
             updated_at = NOW()
         WHERE id = ?`,
        [`Invoice: ${resolvedCurrency} ${amount}`, conversationId]
      );

      await connection.commit();

      logger.info('Enhanced invoice sent successfully', {
        conversationId,
        userId,
        invoiceNumber,
        amount,
        currency: resolvedCurrency,
        paymentMethod: normalizedMethod
      });

      // Emit WebSocket event
      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        tenantNamespace.emit('invoice-message-sent', {
          conversationId,
          message: {
            id: messageResult.insertId,
            messageId: sendResult.messageId,
            invoice,
            paymentMethod: normalizedMethod,
            direction: 'outbound',
            timestamp: new Date().toISOString(),
            sent_by_name: req.user.name
          }
        });
      }

      return res.json({
        success: true,
        message: 'Payment link sent successfully',
        data: {
          messageId: sendResult.messageId,
          invoice,
          invoiceMessage
        }
      });

    } catch (error) {
      await connection.rollback();
      logger.error('Error sending enhanced invoice', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        userId: req.user.id
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to send invoice'
      });
    } finally {
      connection.release();
    }
  }

  // ============================================
  // HELPER METHODS FOR PRODUCTS & BILLING
  // ============================================

  /**
   * Format product message for WhatsApp
   */
  static formatProductMessage(product, customMessage = '') {
    const priceText = product.sale_price 
      ? `~~$${product.regular_price}~~ *$${product.sale_price}*` 
      : `*$${product.price}*`;

    const stockText = product.stock_status === 'instock' 
      ? '✅ In Stock' 
      : '❌ Out of Stock';

    let message = `🛍️ *${product.name}*\n\n`;
    
    if (product.description) {
      message += `${product.description}\n\n`;
    }
    
    message += `💰 Price: ${priceText}\n`;
    message += `📦 ${stockText}\n`;
    
    if (product.sku) {
      message += `🏷️ SKU: ${product.sku}\n`;
    }

    if (customMessage) {
      message += `\n💬 ${customMessage}`;
    }

    if (product.permalink) {
      message += `\n\n🔗 View Product: ${product.permalink}`;
    }

    return message;
  }

  /**
   * Format invoice message for WhatsApp
   */
  static formatInvoiceMessage(invoice) {
    const methodLabel = invoice.paymentMethod === 'paypal' ? 'PayPal' : 'Stripe';
    let message = `💳 *Payment Link #${invoice.number}*\n\n`;
    message += `📝 ${invoice.description}\n\n`;
    message += `💰 *Total: ${invoice.currency} ${invoice.amount}*\n`;
    message += `🔗 ${invoice.paymentLink}\n`;
    message += `🧾 ${methodLabel}`;
    return message;
  }

  /**
   * Health check endpoint for debugging
   * GET /api/user/whatsapp-cloud/health
   */
  static async healthCheck(req, res) {
    try {
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;
      
      logger.info('Health check request', {
        userId,
        tenantId,
        user: {
          id: req.user.id,
          name: req.user.name,
          role: req.user.role,
          store: req.user.store,
          store_id: req.user.store_id,
          department: req.user.department,
          department_id: req.user.department_id
        }
      });

      // Test basic database connection
      const [testResult] = await pool.execute('SELECT 1 as test');
      
      // Test conversations table
      const [conversationsTest] = await pool.execute(
        'SELECT COUNT(*) as count FROM whatsapp_cloud_conversations WHERE tenant_id = ?',
        [tenantId]
      );

      return res.json({
        success: true,
        data: {
          userId,
          tenantId,
          user: req.user,
          database: {
            connected: true,
            conversationsCount: conversationsTest[0].count
          },
          timestamp: new Date().toISOString()
        }
      });
    } catch (error) {
      logger.error('Health check error', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Health check failed',
        error: error.message
      });
    }
  }

  /**
   * Get conversation notes
   * GET /api/user/whatsapp-cloud/conversations/:conversationId/notes
   */
  static async getConversationNotes(req, res) {
    try {
      const userId = req.user.id;
      const tenantId = req.tenantId || req.user.tenantId;
      const conversationId = req.params.conversationId;

      logger.info('Getting conversation notes', {
        userId,
        tenantId,
        conversationId
      });

      // Check if this is an Evolution conversation
      const [evolutionCheck] = await pool.execute(
        'SELECT id, phone_number FROM conversations WHERE id = ? AND tenant_id = ?',
        [conversationId, tenantId]
      );

      let contactPhone;

      if (evolutionCheck.length > 0) {
        logger.info('Evolution conversation detected for notes', {
          conversationId,
          tenantId
        });
        contactPhone = evolutionCheck[0].phone_number;
      } else {
        // Get conversation to verify access and get contact phone (WhatsApp Cloud)
        const [conversations] = await pool.execute(`
          SELECT contact_phone, claimed_by_user_id, store_id, department_id
          FROM whatsapp_cloud_conversations 
          WHERE id = ? AND tenant_id = ?
        `, [conversationId, tenantId]);

        if (conversations.length === 0) {
          return res.status(404).json({
            success: false,
            message: 'Conversation not found'
          });
        }

        contactPhone = conversations[0].contact_phone;
      }

      // Get all notes for this contact phone (conversation history)
      const [notes] = await pool.execute(`
        SELECT 
          cn.*,
          u.name as created_by_user_name
        FROM conversation_notes cn
        LEFT JOIN users u ON cn.created_by_user_id = u.id
        WHERE cn.tenant_id = ? AND cn.contact_phone = ?
        ORDER BY cn.created_at DESC
      `, [tenantId, contactPhone]);

      return res.json({
        success: true,
        data: notes.map(note => ({
          id: note.id,
          noteText: note.note_text,
          noteType: note.note_type,
          createdBy: note.created_by_name || note.created_by_user_name || 'System',
          createdAt: note.created_at,
          transferFrom: note.transfer_from_department || note.transfer_from_store,
          transferTo: note.transfer_to_department || note.transfer_to_store,
          isVisible: note.is_visible_to_users
        }))
      });
    } catch (error) {
      logger.error('Error getting conversation notes', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.conversationId
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to get conversation notes'
      });
    }
  }

  /**
   * Add conversation note
   * POST /api/user/whatsapp-cloud/conversations/:conversationId/notes
   */
  static async addConversationNote(req, res) {
    try {
      const userId = req.user.id;
      const userName = req.user.name;
      const tenantId = req.tenantId || req.user.tenantId;
      const conversationId = req.params.conversationId;
      const { noteText, noteType = 'general' } = req.body;

      if (!noteText || !noteText.trim()) {
        return res.status(400).json({
          success: false,
          message: 'Note text is required'
        });
      }

      if (req.user.role !== 'admin' && noteType !== 'transfer') {
        return res.status(403).json({
          success: false,
          message: 'Only transfer notes are allowed for users'
        });
      }

      logger.info('Adding conversation note', {
        userId,
        tenantId,
        conversationId,
        noteType
      });

      // Get conversation to verify access and get contact phone
      const [conversations] = await pool.execute(`
        SELECT contact_phone, claimed_by_user_id, store_id, department_id
        FROM whatsapp_cloud_conversations 
        WHERE id = ? AND tenant_id = ?
      `, [conversationId, tenantId]);

      if (conversations.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Conversation not found'
        });
      }

      const conversation = conversations[0];
      const contactPhone = conversation.contact_phone;

      // Insert note
      const [result] = await pool.execute(`
        INSERT INTO conversation_notes (
          tenant_id, conversation_id, contact_phone, note_text, note_type,
          created_by_user_id, created_by_name, is_visible_to_users
        ) VALUES (?, ?, ?, ?, ?, ?, ?, TRUE)
      `, [tenantId, conversationId, contactPhone, noteText.trim(), noteType, userId, userName]);

      // Get the created note
      const [newNote] = await pool.execute(`
        SELECT * FROM conversation_notes WHERE id = ?
      `, [result.insertId]);

      // Emit WebSocket event
      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        tenantNamespace.emit('conversation-note-added', {
          conversationId,
          contactPhone,
          note: {
            id: newNote[0].id,
            noteText: newNote[0].note_text,
            noteType: newNote[0].note_type,
            createdBy: userName,
            createdAt: newNote[0].created_at
          }
        });
      }

      return res.json({
        success: true,
        message: 'Note added successfully',
        data: {
          id: newNote[0].id,
          noteText: newNote[0].note_text,
          noteType: newNote[0].note_type,
          createdBy: userName,
          createdAt: newNote[0].created_at
        }
      });
    } catch (error) {
      logger.error('Error adding conversation note', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.conversationId
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to add note'
      });
    }
  }

  /**
   * Enhanced transfer conversation with note creation
   * PUT /api/user/whatsapp-cloud/conversations/:conversationId/transfer
   */
  static async transferConversationEnhanced(req, res) {
    try {
      const userId = req.user.id;
      const userName = req.user.name;
      const tenantId = req.tenantId || req.user.tenantId;
      const userStoreId = req.user.store_id;
      const userDepartmentId = req.user.department_id;
      const userRole = req.user.role;
      const conversationId = req.params.conversationId;
      const { newStoreId, newDepartmentId, newUserId, reason } = req.body;

      // Log request details for debugging
      logger.info('Transfer request received', {
        userId,
        tenantId,
        conversationId,
        newStoreId,
        newDepartmentId,
        newUserId,
        hasReason: !!reason,
        params: req.params,
        body: req.body,
        url: req.url,
        method: req.method
      });

      // Validate conversation ID
      if (!conversationId || conversationId === 'null' || conversationId === 'undefined') {
        return res.status(400).json({
          success: false,
          message: 'Invalid conversation ID'
        });
      }

      logger.info('Enhanced conversation transfer', {
        userId,
        tenantId,
        conversationId,
        newStoreId,
        newDepartmentId,
        newUserId,
        hasReason: !!reason
      });

      // Get conversation details (Cloud first, fallback to Web table)
      const [cloudColumnRows] = await pool.execute(
        `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'whatsapp_cloud_conversations'`
      );
      const cloudColumns = new Set(cloudColumnRows.map(row => row.COLUMN_NAME));
      const cloudSelectFields = ['contact_phone', 'contact_name', 'claimed_by_user_id'];
      if (cloudColumns.has('store_id')) cloudSelectFields.push('store_id');
      if (cloudColumns.has('department_id')) cloudSelectFields.push('department_id');
      let conversationTable = 'whatsapp_cloud_conversations';
      let [conversations] = await pool.execute(
        `SELECT ${cloudSelectFields.join(', ')}
         FROM whatsapp_cloud_conversations 
         WHERE id = ? AND tenant_id = ?`,
        [conversationId, tenantId]
      );

      if (conversations.length === 0) {
        conversationTable = 'conversations';
        [conversations] = await pool.execute(`
          SELECT phone_number as contact_phone, contact_name, assigned_store, assigned_department, assigned_user_id
          FROM conversations
          WHERE id = ? AND tenant_id = ?
        `, [conversationId, tenantId]);
      }

      if (conversations.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Conversation not found'
        });
      }

      const conversation = conversations[0];

      let resolvedStoreId = newStoreId || null;
      let resolvedDepartmentId = newDepartmentId || null;
      let resolvedUserId = newUserId || null;

      if (resolvedStoreId && !/^\d+$/.test(String(resolvedStoreId))) {
        const [storeRows] = await pool.execute(
          `SELECT id FROM stores WHERE tenant_id = ? AND name = ? LIMIT 1`,
          [tenantId, String(resolvedStoreId)]
        );
        resolvedStoreId = storeRows[0]?.id || null;
      }

      if (resolvedDepartmentId && !/^\d+$/.test(String(resolvedDepartmentId))) {
        const [deptRows] = await pool.execute(
          `SELECT id FROM departments WHERE tenant_id = ? AND name = ? LIMIT 1`,
          [tenantId, String(resolvedDepartmentId)]
        );
        resolvedDepartmentId = deptRows[0]?.id || null;
      }

      if (resolvedUserId && !/^\d+$/.test(String(resolvedUserId))) {
        const [userRows] = await pool.execute(
          `SELECT id FROM users WHERE tenant_id = ? AND (name = ? OR username = ?) LIMIT 1`,
          [tenantId, String(resolvedUserId), String(resolvedUserId)]
        );
        resolvedUserId = userRows[0]?.id || null;
      }

      // If transferring to user, get their store/department
      if (resolvedUserId) {
        const [toUser] = await pool.execute(`
          SELECT store_id, department_id FROM users WHERE id = ? AND tenant_id = ?
        `, [resolvedUserId, tenantId]);
        if (toUser.length > 0) {
          // Set store/department from user if not already set
          if (!resolvedStoreId && toUser[0].store_id) {
            resolvedStoreId = toUser[0].store_id;
          }
          if (!resolvedDepartmentId && toUser[0].department_id) {
            resolvedDepartmentId = toUser[0].department_id;
          }
        }
      }

      if (!resolvedStoreId && !resolvedDepartmentId && !resolvedUserId) {
        logger.error('Transfer validation failed - no destination', {
          newStoreId,
          newDepartmentId,
          newUserId,
          resolvedStoreId,
          resolvedDepartmentId,
          resolvedUserId
        });
        return res.status(400).json({
          success: false,
          message: 'Destination store, department, or user is required'
        });
      }

      const transferTargetCount = [resolvedStoreId, resolvedDepartmentId, resolvedUserId].filter(Boolean).length;
      if (transferTargetCount > 1) {
        return res.status(400).json({
          success: false,
          message: 'Transfer must be to a store, department, or user - not multiple'
        });
      }

      // Get current store/department names
      let fromStoreName = null, fromDepartmentName = null, fromUserName = null;
      let toStoreName = null, toDepartmentName = null, toUserName = null;

      if (conversationTable === 'conversations') {
        fromStoreName = conversation.assigned_store || null;
        fromDepartmentName = conversation.assigned_department || null;
        
        if (conversation.assigned_user_id) {
          const [fromUser] = await pool.execute(`
            SELECT name FROM users WHERE id = ? AND tenant_id = ?
          `, [conversation.assigned_user_id, tenantId]);
          fromUserName = fromUser[0]?.name;
        }
      } else {
        if (conversation.store_id) {
          const [fromStore] = await pool.execute(`
            SELECT name FROM stores WHERE id = ? AND tenant_id = ?
          `, [conversation.store_id, tenantId]);
          fromStoreName = fromStore[0]?.name;
        }

        if (conversation.department_id) {
          const [fromDept] = await pool.execute(`
            SELECT name FROM departments WHERE id = ? AND tenant_id = ?
          `, [conversation.department_id, tenantId]);
          fromDepartmentName = fromDept[0]?.name;
        }

        if (conversation.claimed_by_user_id) {
          const [fromUser] = await pool.execute(`
            SELECT name FROM users WHERE id = ? AND tenant_id = ?
          `, [conversation.claimed_by_user_id, tenantId]);
          fromUserName = fromUser[0]?.name;
        }
      }

      if (resolvedStoreId) {
        const [toStore] = await pool.execute(`
          SELECT name FROM stores WHERE id = ? AND tenant_id = ?
        `, [resolvedStoreId, tenantId]);
        toStoreName = toStore[0]?.name;
      }

      if (resolvedDepartmentId) {
        const [toDept] = await pool.execute(`
          SELECT name FROM departments WHERE id = ? AND tenant_id = ?
        `, [resolvedDepartmentId, tenantId]);
        toDepartmentName = toDept[0]?.name;
      }

      if (resolvedUserId) {
        const [toUser] = await pool.execute(`
          SELECT name, store_id, department_id FROM users WHERE id = ? AND tenant_id = ?
        `, [resolvedUserId, tenantId]);
        if (toUser.length > 0) {
          toUserName = toUser[0].name;
          // Store/department already set above, just get names
          if (resolvedStoreId && !toStoreName) {
            const [userStore] = await pool.execute(`
              SELECT name FROM stores WHERE id = ? AND tenant_id = ?
            `, [resolvedStoreId, tenantId]);
            toStoreName = userStore[0]?.name;
          }
          if (resolvedDepartmentId && !toDepartmentName) {
            const [userDept] = await pool.execute(`
              SELECT name FROM departments WHERE id = ? AND tenant_id = ?
            `, [resolvedDepartmentId, tenantId]);
            toDepartmentName = userDept[0]?.name;
          }
        }
      }

      // Determine next state based on transfer type
      let nextStoreId, nextDepartmentId, nextTransferredStore, nextTransferredDepartment, nextClaimedByUserId;
      
      const currentClaimedUserId = conversationTable === 'conversations' 
        ? conversation.assigned_user_id 
        : conversation.claimed_by_user_id;
      
      if (currentClaimedUserId && currentClaimedUserId !== userId && userRole !== 'admin') {
        return res.status(403).json({
          success: false,
          message: 'Conversation is claimed by another user'
        });
      }

      if (resolvedUserId) {
        // Transfer to user: assign to user and their store/department
        nextStoreId = resolvedStoreId;
        nextDepartmentId = resolvedDepartmentId;
        nextTransferredStore = resolvedStoreId;
        nextTransferredDepartment = resolvedDepartmentId;
        nextClaimedByUserId = resolvedUserId;
      } else if (resolvedStoreId) {
        // Transfer to store: assign to store, clear department and user
        nextStoreId = resolvedStoreId;
        nextDepartmentId = null;
        nextTransferredStore = resolvedStoreId;
        nextTransferredDepartment = null;
        nextClaimedByUserId = null;
      } else if (resolvedDepartmentId) {
        // Transfer to department: clear store and user
        nextStoreId = null;
        nextDepartmentId = null;
        nextTransferredStore = null;
        nextTransferredDepartment = resolvedDepartmentId;
        nextClaimedByUserId = null;
      }

      // Update conversation with transfer tracking
      logger.info('Transferring conversation', {
        conversationId,
        fromStoreId: conversation.store_id,
        fromDepartmentId: conversation.department_id,
        toStoreId: nextStoreId,
        toDepartmentId: nextDepartmentId,
        transferredToStore: nextTransferredStore,
        transferredToDepartment: nextTransferredDepartment,
        userId
      });

      if (conversationTable === 'whatsapp_cloud_conversations') {
        const updateFields = [];
        const updateValues = [];
        if (cloudColumns.has('store_id')) {
          updateFields.push('store_id = ?');
          updateValues.push(nextStoreId);
        }
        if (cloudColumns.has('department_id')) {
          updateFields.push('department_id = ?');
          updateValues.push(nextDepartmentId);
        }
        if (cloudColumns.has('transferred_to_store')) {
          updateFields.push('transferred_to_store = ?');
          updateValues.push(nextTransferredStore);
        }
        if (cloudColumns.has('transferred_to_department')) {
          updateFields.push('transferred_to_department = ?');
          updateValues.push(nextTransferredDepartment);
        }
        if (cloudColumns.has('transferred_at')) {
          updateFields.push('transferred_at = NOW()');
        }
        if (cloudColumns.has('transferred_by_user_id')) {
          updateFields.push('transferred_by_user_id = ?');
          updateValues.push(userId);
        }
        if (cloudColumns.has('claimed_by_user_id')) {
          updateFields.push('claimed_by_user_id = ?');
          updateValues.push(nextClaimedByUserId);
        }
        if (cloudColumns.has('claimed_at')) {
          if (nextClaimedByUserId) {
            updateFields.push('claimed_at = NOW()');
          } else {
            updateFields.push('claimed_at = NULL');
          }
        }
        if (cloudColumns.has('updated_at')) {
          updateFields.push('updated_at = NOW()');
        }
        if (updateFields.length > 0) {
          updateValues.push(conversationId, tenantId);
          
          logger.info('Executing conversation update', {
            conversationId,
            tenantId,
            updateFields,
            updateValues,
            nextStoreId,
            nextDepartmentId,
            nextClaimedByUserId
          });
          
          await pool.execute(
            `UPDATE whatsapp_cloud_conversations 
             SET ${updateFields.join(', ')}
             WHERE id = ? AND tenant_id = ?`,
            updateValues
          );
          
          logger.info('Conversation updated successfully', {
            conversationId,
            tenantId
          });
        }
      } else {
        const targetStoreName = toStoreName || (resolvedStoreId ? String(resolvedStoreId) : null);
        const targetDepartmentName = toDepartmentName || (resolvedDepartmentId ? String(resolvedDepartmentId) : null);
        await pool.execute(`
          UPDATE conversations
          SET assigned_store = ?, 
              assigned_department = ?, 
              transferred_to_store = ?,
              transferred_to_department = ?,
              transferred_at = NOW(),
              assigned_user_id = ?, 
              claimed_at = ${nextClaimedByUserId ? 'NOW()' : 'NULL'},
              is_claimed = ${nextClaimedByUserId ? 'TRUE' : 'FALSE'},
              updated_at = NOW()
          WHERE id = ? AND tenant_id = ?
        `, [
          targetStoreName, 
          targetDepartmentName,
          targetStoreName,
          targetDepartmentName,
          nextClaimedByUserId,
          conversationId, 
          tenantId
        ]);
      }

      // Create transfer note if reason provided
      if (reason && reason.trim()) {
        await pool.execute(`
          INSERT INTO conversation_notes (
            tenant_id, conversation_id, contact_phone, note_text, note_type,
            created_by_user_id, created_by_name, 
            transfer_from_store, transfer_to_store,
            transfer_from_department, transfer_to_department,
            is_visible_to_users
          ) VALUES (?, ?, ?, ?, 'transfer', ?, ?, ?, ?, ?, ?, TRUE)
        `, [
          tenantId, conversationId, conversation.contact_phone, reason.trim(),
          userId, userName, fromStoreName, toStoreName, fromDepartmentName, toDepartmentName
        ]);
      }

      // Emit WebSocket events
      const io = req.app.get('io');
      if (io) {
        const tenantNamespace = io.of(`/tenant/${tenantId}`);
        
        // Emit transfer event
        tenantNamespace.emit('conversation-transferred', {
          conversationId,
          fromStore: fromStoreName,
          toStore: toStoreName,
          fromDepartment: fromDepartmentName,
          toDepartment: toDepartmentName,
          fromUser: fromUserName,
          toUser: toUserName,
          transferredBy: userName,
          reason: reason?.trim()
        });

        // Emit conversation update
        tenantNamespace.emit('conversation-updated', {
          conversationId,
          updates: {
            store_id: nextStoreId,
            department_id: nextDepartmentId,
            claimed_by_user_id: nextClaimedByUserId
          }
        });
      }

      return res.json({
        success: true,
        message: 'Conversation transferred successfully',
        data: {
          conversationId,
          transferredTo: {
            userId: resolvedUserId,
            userName: toUserName,
            storeId: nextStoreId,
            storeName: toStoreName,
            departmentId: nextDepartmentId,
            departmentName: toDepartmentName
          }
        }
      });
    } catch (error) {
      logger.error('Error transferring conversation', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.conversationId
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to transfer conversation'
      });
    }
  }

  /**
   * Get enhanced transfer options with robust store/department loading
   * GET /api/user/whatsapp-cloud/transfer-options
   */
  static async getTransferOptions(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const userId = req.user.id;

      logger.info('Getting enhanced transfer options', { tenantId, userId });

      // Get all active stores for this tenant
      const [stores] = await pool.execute(`
        SELECT id, name, description, address, phone, email, active
        FROM stores 
        WHERE tenant_id = ? AND active = TRUE
        ORDER BY name ASC
      `, [tenantId]);

      // Get all active departments for this tenant
      const [departments] = await pool.execute(`
        SELECT id, name, description, active
        FROM departments 
        WHERE tenant_id = ? AND active = TRUE
        ORDER BY name ASC
      `, [tenantId]);

      // Get all active users (excluding current user)
      const [users] = await pool.execute(`
        SELECT u.id, u.name, u.email, u.role, u.store_id, u.department_id,
               s.name as store_name, d.name as department_name
        FROM users u
        LEFT JOIN stores s ON u.store_id = s.id
        LEFT JOIN departments d ON u.department_id = d.id
        WHERE u.tenant_id = ? AND u.active = TRUE AND u.id != ?
        ORDER BY u.name ASC
      `, [tenantId, userId]);

      logger.info('Transfer options retrieved', {
        tenantId,
        userId,
        storesCount: stores.length,
        departmentsCount: departments.length,
        usersCount: users.length
      });

      return res.json({
        success: true,
        data: {
          stores: stores.map(store => ({
            id: store.id,
            name: store.name,
            description: store.description,
            address: store.address,
            phone: store.phone,
            email: store.email
          })),
          departments: departments.map(dept => ({
            id: dept.id,
            name: dept.name,
            description: dept.description
          })),
          users: users.map(user => ({
            id: user.id,
            name: user.name,
            email: user.email,
            role: user.role,
            store_id: user.store_id,
            department_id: user.department_id,
            store_name: user.store_name,
            department_name: user.department_name
          }))
        }
      });
    } catch (error) {
      logger.error('Error getting transfer options', {
        error: error.message,
        stack: error.stack
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to get transfer options'
      });
    }
  }
  /**
   * Get pipeline stages for current tenant (read-only for users)
   * GET /api/user/whatsapp-cloud/pipeline-stages
   */
  static async getPipelineStages(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;

      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found'
        });
      }

      logger.info('User requesting pipeline stages', {
        userId: req.user.id,
        tenantId
      });

      const [stages] = await pool.execute(`
        SELECT id, stage_key, stage_name, stage_color, stage_icon, stage_order, is_default, active
        FROM pipeline_stages
        WHERE tenant_id = ? AND active = TRUE
        ORDER BY stage_order ASC
      `, [tenantId]);

      logger.info('Pipeline stages loaded for user', {
        tenantId,
        stagesCount: stages.length
      });

      return res.json({
        success: true,
        data: stages
      });
    } catch (error) {
      logger.error('Error getting pipeline stages for user', {
        error: error.message,
        stack: error.stack,
        tenantId: req.tenantId || req.user.tenantId
      });

      return res.status(500).json({
        success: false,
        message: 'Failed to load pipeline stages'
      });
    }
  }
  /**
   * Send media message via Evolution API
   * @param {Object} conversation - Evolution conversation object
   * @param {Object} file - Uploaded file object
   * @param {string} mediaType - Type of media (image, audio, document)
   * @param {string} caption - Media caption
   * @param {number} conversationId - Conversation ID
   * @param {Object} options - Additional options
   * @returns {Promise<Object>} Send result
   */
  static async sendEvolutionMediaMessage(conversation, file, mediaType, caption, conversationId, options = {}) {
    try {
      logger.info('Starting Evolution media send', {
        conversationId,
        instanceId: conversation.instance_id,
        mediaType,
        fileName: file.originalname || file.filename
      });

      const axios = require('axios');
      const fs = require('fs');
      
      // Get Evolution instance configuration
      const [instanceRows] = await pool.execute(
        'SELECT * FROM evolution_instances WHERE id = ? LIMIT 1',
        [conversation.instance_id]
      );
      
      if (instanceRows.length === 0) {
        logger.error('Evolution instance not found', { instanceId: conversation.instance_id });
        return {
          success: false,
          error: 'Evolution instance not found'
        };
      }
      
      const instance = instanceRows[0];
      
      // Get Evolution API URL from database settings (same as text messages)
      let evolutionApiUrl = 'http://localhost:8080'; // Default value
      const [settings] = await pool.execute(
        `SELECT setting_value FROM system_settings_kv WHERE setting_key = 'evolution_api_url'`
      );
      evolutionApiUrl = settings[0]?.setting_value || process.env.EVOLUTION_API_URL || 'http://localhost:8080';
      
      // Remove trailing slash if present
      evolutionApiUrl = evolutionApiUrl.replace(/\/$/, '');
      
      // Get global API key as fallback (same as text messages)
      const [globalApiKeyResult] = await pool.execute(
        `SELECT setting_value FROM system_settings_kv WHERE setting_key = 'evolution_api_key'`
      );
      const globalApiKey = globalApiKeyResult[0]?.setting_value;
      
      // Use evolution_instance_id (technical name) instead of instance_name (display name) - same as text messages
      const evolutionInstanceName = instance.evolution_instance_id || instance.instance_name;
      
      if (!instance.evolution_instance_id) {
        logger.warn('⚠️ evolution_instance_id is not set, using instance_name as fallback', {
          instanceId: instance.id,
          instanceName: instance.instance_name,
          tenantId: options.tenantId
        });
      }
      
      // Use instance API key, or fall back to global API key (same as text messages)
      const apiKey = instance.evolution_api_key || globalApiKey;
      
      logger.info('Evolution API configuration', {
        instanceName: instance.instance_name,
        evolutionInstanceId: instance.evolution_instance_id,
        usingInstanceId: evolutionInstanceName,
        apiUrl: evolutionApiUrl,
        hasApiKey: !!apiKey
      });
      
      if (!apiKey) {
        logger.error('API key not configured. Please configure either instance API key or global API key in system settings.');
        return {
          success: false,
          error: 'API key not configured. Please configure either instance API key or global API key in system settings.'
        };
      }

      // Check if file exists
      if (!fs.existsSync(file.path)) {
        logger.error('File not found', { filePath: file.path });
        return {
          success: false,
          error: 'File not found'
        };
      }
      
      // Prepare phone number - same format as text messages
      const phoneNumber = conversation.phone_number || conversation.remote_jid;
      let cleanPhoneNumber = phoneNumber;
      
      // Clean phone number for Evolution API (same as text messages)
      if (phoneNumber.includes('@')) {
        cleanPhoneNumber = phoneNumber.split('@')[0];
      }
      
      // Remove any non-numeric characters except +
      cleanPhoneNumber = cleanPhoneNumber.replace(/[^\d+]/g, '');
      
      // Remove leading + if present (Evolution API expects number without +)
      if (cleanPhoneNumber.startsWith('+')) {
        cleanPhoneNumber = cleanPhoneNumber.substring(1);
      }
      
      // Create media URL - Evolution API expects a publicly accessible URL
      let mediaUrl = options.mediaUrl;
      if (!mediaUrl) {
        // Fallback: construct URL if not provided
        const baseUrl = process.env.APP_URL;
        if (!baseUrl) {
          logger.error('APP_URL not configured and mediaUrl not provided');
          return {
            success: false,
            error: 'APP_URL environment variable not configured'
          };
        }
        mediaUrl = `${baseUrl}/uploads/tenant_${options.tenantId}/${file.filename}`;
      }
      
      // Verify the media URL is accessible
      try {
        const testResponse = await axios.head(mediaUrl, { 
          timeout: 5000,
          validateStatus: (status) => status < 500
        });
        
        if (testResponse.status !== 200) {
          logger.warn('Media URL returned non-200 status', {
            mediaUrl,
            status: testResponse.status
          });
        }
      } catch (urlError) {
        logger.warn('Media URL accessibility check failed', { 
          mediaUrl, 
          error: urlError.message
        });
      }
      
      logger.info('Sending media via Evolution API', {
        url: `${evolutionApiUrl}/message/sendMedia/${evolutionInstanceName}`,
        number: cleanPhoneNumber,
        mediaType: mediaType
      });
      
      // Prepare payload - same structure as EvolutionAPIService
      const payload = {
        number: cleanPhoneNumber,
        mediatype: mediaType,
        media: mediaUrl
      };
      
      // Add caption if provided
      if (caption && caption.trim()) {
        payload.caption = caption.trim();
      }
      
      // Send to Evolution API
      const response = await axios.post(
        `${evolutionApiUrl}/message/sendMedia/${evolutionInstanceName}`,
        payload,
        {
          headers: {
            'Content-Type': 'application/json',
            'apikey': apiKey
          },
          timeout: 30000
        }
      );
      
      logger.info('Media sent via Evolution API', {
        conversationId,
        responseStatus: response.status
      });
      
      // Check for successful response
      if (response.status === 200 || response.status === 201) {
        // Save message to database
        const messageId = response.data?.key?.id || 
                         response.data?.messageId || 
                         response.data?.id || 
                         response.data?.message?.key?.id ||
                         `msg_${Date.now()}`;
        
        await pool.execute(
          `INSERT INTO whatsapp_messages 
           (conversation_id, instance_id, tenant_id, phone_number, whatsapp_message_id, direction, message_type, content, media_url, caption, status, created_at)
           VALUES (?, ?, ?, ?, ?, 'outgoing', ?, ?, ?, ?, 'sent', NOW())`,
          [
            conversationId,
            conversation.instance_id,
            options.tenantId,
            conversation.phone_number || conversation.remote_jid,
            messageId,
            mediaType,
            caption || `[${mediaType.toUpperCase()}]`,
            mediaUrl,
            caption || null
          ]
        );
        
        // Update conversation last message
        await pool.execute(
          `UPDATE conversations 
           SET last_message = ?, 
               last_message_time = NOW(), 
               status = CASE 
                 WHEN status = 'waiting' THEN 'attended'
                 ELSE status 
               END,
               attended_at = CASE 
                 WHEN status = 'waiting' AND attended_at IS NULL THEN NOW()
                 ELSE attended_at 
               END,
               updated_at = NOW()
           WHERE id = ?`,
          [caption || `[${mediaType.toUpperCase()}]`, conversationId]
        );
        
        logger.info('Evolution media message saved successfully', {
          conversationId,
          messageId
        });
        
        return {
          success: true,
          whatsappMessageId: messageId,
          messageId: messageId
        };
      } else {
        logger.error('Unexpected response status from Evolution API', { 
          status: response.status
        });
        return {
          success: false,
          error: `Unexpected response status: ${response.status}`
        };
      }
      
    } catch (error) {
      logger.error('Error sending media via Evolution API', {
        error: error.message,
        conversationId,
        statusCode: error.response?.status,
        responseData: error.response?.data
      });
      
      return {
        success: false,
        error: error.response?.data?.message || error.message || 'Failed to send media via Evolution API'
      };
    }
  }
}

module.exports = WhatsAppCloudUserController;
