/**
 * WhatsApp Cloud Controller
 * Handles WhatsApp Cloud API integration and Facebook Embedded Signup
 */

const BaseController = require('./BaseController');
const { pool } = require('../config/database');
const { logger } = require('../config/logger');
const axios = require('axios');
const { sendMessage: queueMessage } = require('../services/MessageService');
const { getWhatsAppService } = require('../services/WhatsAppService');
const { CloudFlowService } = require('../services/whatsapp/CloudFlowService');
const eventBus = require('../core/whatsapp/EventBus');
const Events = require('../core/whatsapp/CoreEvents');
const { logInfo } = require('../core/whatsapp/LogContext');
/* const WhatsAppCloudFAQService = require('../services/WhatsAppCloudFAQService'); */

class WhatsAppCloudController extends BaseController {
  /**
   * Process Facebook Embedded Signup callback OR manual connection
   * POST /api/whatsapp-cloud/facebook-callback
   */
  static async facebookCallback(req, res) {
    try {
      const { code, auth_response, manual, account_name, waba_id, phone_number_id, phone_number, access_token } = req.body;
      const tenantId = req.tenantId || req.user.tenantId;

      logger.info('🔍 Facebook callback initiated', {
        tenantId,
        hasCode: !!code,
        hasAuthResponse: !!auth_response,
        isManual: !!manual,
        authResponseKeys: auth_response ? Object.keys(auth_response) : [],
        userId: req.user?.id,
        userRole: req.user?.role
      });

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

      // Handle manual connection
      if (manual) {
        logger.info('Processing manual WhatsApp connection', { tenantId, account_name });

        if (!account_name || !phone_number_id || !access_token) {
          return res.status(400).json({
            success: false,
            message: 'Account name, phone number ID, and access token are required for manual connection'
          });
        }

        // If phone_number is not provided, try to fetch it from Meta API
        let finalPhoneNumber = phone_number;
        if (!finalPhoneNumber) {
          try {
            logger.info('Phone number not provided, fetching from Meta API', { phone_number_id });
            const phoneResponse = await axios.get(
              `https://graph.facebook.com/v18.0/${phone_number_id}`,
              {
                params: {
                  access_token: access_token,
                  fields: 'display_phone_number,verified_name'
                }
              }
            );
            finalPhoneNumber = phoneResponse.data.display_phone_number;
            logger.info('Phone number fetched successfully', { phone_number: finalPhoneNumber });
          } catch (error) {
            logger.warn('Failed to fetch phone number from Meta API', { 
              error: error.message,
              response: error.response?.data 
            });
            // Continue without phone number - it's optional
            finalPhoneNumber = null;
          }
        }

        // Check if account already exists
        const [existing] = await pool.execute(
          'SELECT id FROM whatsapp_cloud_accounts WHERE phone_number_id = ? AND tenant_id = ?',
          [phone_number_id, tenantId]
        );

        let accountId;

        if (existing.length > 0) {
          // Update existing account
          accountId = existing[0].id;
          await pool.execute(
            `UPDATE whatsapp_cloud_accounts 
             SET account_name = ?, waba_id = ?, phone_number = ?, 
                 access_token = ?, status = 'connected', updated_at = NOW()
             WHERE id = ?`,
            [account_name, waba_id, finalPhoneNumber, access_token, accountId]
          );
          logger.info('WhatsApp account updated (manual)', { tenantId, accountId });
        } else {
          // Check plan limits before creating new account
          const [accountCount] = await pool.execute(
            'SELECT COUNT(*) as count FROM whatsapp_cloud_accounts WHERE tenant_id = ?',
            [tenantId]
          );

          // Get tenant plan limits
          const [tenantLimits] = await pool.execute(
            `SELECT 
              t.id as tenant_id,
              t.plan_id,
              t.max_whatsapp_cloud_accounts as tenant_max,
              sp.max_whatsapp_cloud_accounts as plan_max,
              COALESCE(t.max_whatsapp_cloud_accounts, sp.max_whatsapp_cloud_accounts, 1) as max_accounts
            FROM tenants t
            LEFT JOIN subscription_plans sp ON t.plan_id = sp.id
            WHERE t.id = ?`,
            [tenantId]
          );

          logger.info('Tenant limits query result', {
            tenantId,
            result: tenantLimits[0]
          });

          const maxAccounts = tenantLimits[0]?.max_accounts || 1;
          const currentCount = accountCount[0].count;

          logger.info('Plan limit check', {
            tenantId,
            currentCount,
            maxAccounts,
            willBlock: maxAccounts !== -1 && currentCount >= maxAccounts
          });

          // Check if limit is reached (-1 means unlimited)
          if (maxAccounts !== -1 && currentCount >= maxAccounts) {
            logger.warn('WhatsApp Cloud account limit reached', {
              tenantId,
              currentCount,
              maxAccounts
            });

            return res.status(403).json({
              success: false,
              error: 'LIMIT_REACHED',
              message: `Your plan allows a maximum of ${maxAccounts} WhatsApp Cloud account(s). You currently have ${currentCount}. Please upgrade your plan or contact support.`,
              limit: {
                current: currentCount,
                max: maxAccounts
              }
            });
          }

          const isDefault = currentCount === 0;

          // Create new account
          const [result] = await pool.execute(
            `INSERT INTO whatsapp_cloud_accounts 
             (tenant_id, account_name, waba_id, phone_number_id, phone_number, access_token, status, is_default, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, 'connected', ?, NOW(), NOW())`,
            [tenantId, account_name, waba_id, phone_number_id, finalPhoneNumber, access_token, isDefault]
          );

          accountId = result.insertId;
          logger.info('WhatsApp account created (manual)', { tenantId, accountId });
        }

        return res.json({
          success: true,
          message: 'Account connected successfully',
          data: { account_id: accountId }
        });
      }

      // Handle Facebook OAuth flow
      if (!code && !auth_response) {
        logger.error('❌ No authorization code or auth response provided', {
          tenantId,
          bodyKeys: Object.keys(req.body)
        });
        return res.status(400).json({
          success: false,
          message: 'Authorization code or auth response is required'
        });
      }

      logger.info('🔄 Processing Facebook OAuth flow', { 
        tenantId, 
        hasCode: !!code,
        codeLength: code?.length,
        hasAuthResponse: !!auth_response
      });

      // Get Meta App credentials from system settings
      const [settings] = await pool.execute(
        `SELECT setting_key, setting_value FROM system_settings_kv 
         WHERE setting_key IN ('meta_app_id', 'meta_app_secret', 'meta_embedded_signup_enabled')`
      );

      const settingsObj = {};
      settings.forEach(row => {
        settingsObj[row.setting_key] = row.setting_value;
      });

      // Check if Embedded Signup is enabled
      if (settingsObj.meta_embedded_signup_enabled !== '1' && settingsObj.meta_embedded_signup_enabled !== 'true') {
        logger.error('❌ Embedded Signup not enabled', {
          tenantId,
          embeddedSignupValue: settingsObj.meta_embedded_signup_enabled
        });
        return res.status(400).json({
          success: false,
          message: 'Facebook Embedded Signup is not enabled in system settings'
        });
      }

      if (!settingsObj.meta_app_id || !settingsObj.meta_app_secret) {
        logger.error('❌ Meta App credentials missing', {
          tenantId,
          hasAppId: !!settingsObj.meta_app_id,
          hasAppSecret: !!settingsObj.meta_app_secret
        });
        return res.status(400).json({
          success: false,
          message: 'Meta App credentials are not configured'
        });
      }

      logger.info('✅ Meta credentials validated', {
        tenantId,
        appId: settingsObj.meta_app_id,
        hasSecret: !!settingsObj.meta_app_secret
      });

      // Construct redirect URI - MUST match the one used in frontend OAuth dialog
      // Frontend uses: window.location.origin + '/admin/facebook-callback.html'
      const redirectUri = `${req.protocol}://${req.get('host')}/admin/facebook-callback.html`;
      logger.info('🔄 Redirect URI constructed', { redirectUri });

      // Step 1: Exchange code for access token
      logger.info('🔄 Step 1: Exchanging code for access token', { tenantId });
      const tokenData = await WhatsAppCloudController.exchangeCodeForToken(
        code || auth_response.code,
        settingsObj.meta_app_id,
        settingsObj.meta_app_secret,
        redirectUri
      );

      logger.info('✅ Step 1 completed: Token exchange successful', { 
        tenantId,
        hasAccessToken: !!tokenData.access_token,
        tokenType: tokenData.token_type
      });

      // Step 2: Get WABA details from Meta API
      logger.info('🔄 Step 2: Getting WABA details', { tenantId });
      
      let wabaDetails;
      try {
        wabaDetails = await WhatsAppCloudController.getWABADetails(tokenData.access_token);
      } catch (wabaError) {
        logger.warn('⚠️ Failed to get WABA from API, checking auth_response', { 
          error: wabaError.message 
        });
        
        // Fallback: Check if auth_response contains WABA info
        if (auth_response && (auth_response.waba_id || auth_response.phone_number_id)) {
          logger.info('✅ Using WABA info from auth_response');
          wabaDetails = {
            id: auth_response.waba_id || auth_response.business_id,
            name: auth_response.account_name || auth_response.business_name || 'WhatsApp Business Account',
            phone_number_id: auth_response.phone_number_id,
            phone_number: auth_response.phone_number || auth_response.display_phone_number,
            verified_name: auth_response.verified_name
          };
        } else {
          // If no WABA info available, throw the original error
          throw wabaError;
        }
      }

      logger.info('✅ Step 2 completed: WABA details retrieved', { 
        tenantId, 
        wabaId: wabaDetails.id,
        wabaName: wabaDetails.name,
        phoneNumberId: wabaDetails.phone_number_id,
        phoneNumber: wabaDetails.phone_number
      });

      // Step 3: Check if account already exists
      logger.info('🔄 Step 3: Checking for existing account', { 
        tenantId, 
        wabaId: wabaDetails.id 
      });
      const [existing] = await pool.execute(
        'SELECT id FROM whatsapp_cloud_accounts WHERE waba_id = ? AND tenant_id = ?',
        [wabaDetails.id, tenantId]
      );

      let accountId;

      if (existing.length > 0) {
        // Update existing account
        accountId = existing[0].id;
        logger.info('🔄 Updating existing account', { tenantId, accountId });
        
        await pool.execute(
          `UPDATE whatsapp_cloud_accounts 
           SET account_name = ?, phone_number_id = ?, phone_number = ?, 
               access_token = ?, status = 'connected', updated_at = NOW()
           WHERE id = ?`,
          [
            wabaDetails.name,
            wabaDetails.phone_number_id,
            wabaDetails.phone_number,
            tokenData.access_token,
            accountId
          ]
        );
        logger.info('✅ WhatsApp account updated successfully', { tenantId, accountId });
      } else {
        // Check if this is the first account for this tenant
        logger.info('🔄 Creating new account', { tenantId });
        const [accountCount] = await pool.execute(
          'SELECT COUNT(*) as count FROM whatsapp_cloud_accounts WHERE tenant_id = ?',
          [tenantId]
        );

        // Get tenant plan limits
        const [tenantLimits] = await pool.execute(
          `SELECT 
            t.id as tenant_id,
            t.plan_id,
            t.max_whatsapp_cloud_accounts as tenant_max,
            sp.max_whatsapp_cloud_accounts as plan_max,
            COALESCE(t.max_whatsapp_cloud_accounts, sp.max_whatsapp_cloud_accounts, 1) as max_accounts
          FROM tenants t
          LEFT JOIN subscription_plans sp ON t.plan_id = sp.id
          WHERE t.id = ?`,
          [tenantId]
        );

        logger.info('Tenant limits query result (OAuth flow)', {
          tenantId,
          result: tenantLimits[0]
        });

        const maxAccounts = tenantLimits[0]?.max_accounts || 1;
        const currentCount = accountCount[0].count;

        logger.info('Plan limit check (OAuth flow)', {
          tenantId,
          currentCount,
          maxAccounts,
          willBlock: maxAccounts !== -1 && currentCount >= maxAccounts
        });

        // Check if limit is reached (-1 means unlimited)
        if (maxAccounts !== -1 && currentCount >= maxAccounts) {
          logger.warn('WhatsApp Cloud account limit reached (OAuth flow)', {
            tenantId,
            currentCount,
            maxAccounts
          });

          return res.status(403).json({
            success: false,
            error: 'LIMIT_REACHED',
            message: `Your plan allows a maximum of ${maxAccounts} WhatsApp Cloud account(s). You currently have ${currentCount}. Please upgrade your plan or contact support.`,
            limit: {
              current: currentCount,
              max: maxAccounts
            }
          });
        }

        const isFirstAccount = currentCount === 0;
        
        logger.info('📊 Account count check', { 
          tenantId, 
          existingCount: accountCount[0].count,
          isFirstAccount 
        });

        // Insert new account
        const [result] = await pool.execute(
          `INSERT INTO whatsapp_cloud_accounts 
           (tenant_id, account_name, waba_id, phone_number_id, phone_number, 
            access_token, status, is_default)
           VALUES (?, ?, ?, ?, ?, ?, 'connected', ?)`,
          [
            tenantId,
            wabaDetails.name,
            wabaDetails.id,
            wabaDetails.phone_number_id,
            wabaDetails.phone_number,
            tokenData.access_token,
            isFirstAccount
          ]
        );
        accountId = result.insertId;
        logger.info('✅ WhatsApp account created successfully', { 
          tenantId, 
          accountId,
          insertId: result.insertId,
          affectedRows: result.affectedRows
        });
      }

      logger.info('🎉 Facebook callback completed successfully', {
        tenantId,
        accountId,
        wabaId: wabaDetails.id,
        accountName: wabaDetails.name,
        phoneNumber: wabaDetails.phone_number
      });

      return res.json({
        success: true,
        message: 'WhatsApp account connected successfully',
        data: {
          account_id: accountId,
          account_name: wabaDetails.name,
          phone_number: wabaDetails.phone_number,
          waba_id: wabaDetails.id
        }
      });
    } catch (error) {
      logger.error('❌ Facebook callback error', { 
        error: error.message, 
        stack: error.stack,
        response: error.response?.data,
        tenantId: req.tenantId || req.user?.tenantId,
        userId: req.user?.id
      });
      
      return res.status(500).json({
        success: false,
        message: error.response?.data?.error?.message || error.message || 'Failed to process Facebook login'
      });
    }
  }

  /**
   * Exchange authorization code for access token
   */
  static async exchangeCodeForToken(code, appId, appSecret, redirectUri = null) {
    try {
      logger.info('🔄 Attempting token exchange', {
        hasCode: !!code,
        codeLength: code?.length,
        hasAppId: !!appId,
        hasAppSecret: !!appSecret,
        appId: appId,
        redirectUri: redirectUri
      });

      const params = {
        client_id: appId,
        client_secret: appSecret,
        code: code
      };

      // Add redirect_uri if provided (required by some OAuth flows)
      if (redirectUri) {
        params.redirect_uri = redirectUri;
      }

      const response = await axios.get('https://graph.facebook.com/v18.0/oauth/access_token', {
        params: params
      });

      logger.info('✅ Token exchange successful', {
        hasAccessToken: !!response.data?.access_token,
        tokenType: response.data?.token_type
      });

      return response.data;
    } catch (error) {
      logger.error('❌ Token exchange failed', { 
        error: error.message,
        responseStatus: error.response?.status,
        responseData: error.response?.data,
        hasCode: !!code,
        hasAppId: !!appId,
        hasAppSecret: !!appSecret
      });
      
      // Return more specific error message from Facebook
      const fbError = error.response?.data?.error;
      if (fbError) {
        throw new Error(`Facebook API Error: ${fbError.message || fbError.error_user_msg || 'Token exchange failed'}`);
      }
      
      throw new Error('Failed to exchange authorization code for access token');
    }
  }

  /**
   * Get WABA details from Meta API
   */
  static async getWABADetails(accessToken) {
    try {
      logger.info('🔄 Getting WABA details from Meta API');

      // Try multiple approaches to get WABA information
      let wabaList = [];

      // Approach 1: Try to get directly from /me/whatsapp_business_accounts
      try {
        logger.info('Trying approach 1: /me/whatsapp_business_accounts');
        const wabaResponse = await axios.get('https://graph.facebook.com/v18.0/me/whatsapp_business_accounts', {
          params: {
            access_token: accessToken,
            fields: 'id,name,phone_numbers{id,display_phone_number,verified_name}'
          }
        });
        wabaList = wabaResponse.data.data || [];
        logger.info('Approach 1 result', { count: wabaList.length });
      } catch (err) {
        logger.warn('Approach 1 failed', { error: err.message });
      }

      // Approach 2: Try to get from businesses
      if (wabaList.length === 0) {
        try {
          logger.info('Trying approach 2: /me/businesses');
          const businessesResponse = await axios.get('https://graph.facebook.com/v18.0/me/businesses', {
            params: {
              access_token: accessToken,
              fields: 'owned_whatsapp_business_accounts{id,name,phone_numbers{id,display_phone_number,verified_name}}'
            }
          });
          
          if (businessesResponse.data.data && businessesResponse.data.data.length > 0) {
            for (const business of businessesResponse.data.data) {
              if (business.owned_whatsapp_business_accounts?.data) {
                wabaList.push(...business.owned_whatsapp_business_accounts.data);
              }
            }
          }
          logger.info('Approach 2 result', { count: wabaList.length });
        } catch (err) {
          logger.warn('Approach 2 failed', { error: err.message });
        }
      }

      // Approach 3: Try to get from client_whatsapp_business_accounts
      if (wabaList.length === 0) {
        try {
          logger.info('Trying approach 3: Get user ID first');
          const meResponse = await axios.get('https://graph.facebook.com/v18.0/me', {
            params: {
              access_token: accessToken,
              fields: 'id'
            }
          });
          
          const userId = meResponse.data.id;
          logger.info('Got user ID', { userId });

          const clientWabaResponse = await axios.get(`https://graph.facebook.com/v18.0/${userId}/client_whatsapp_business_accounts`, {
            params: {
              access_token: accessToken,
              fields: 'id,name,phone_numbers{id,display_phone_number,verified_name}'
            }
          });
          wabaList = clientWabaResponse.data.data || [];
          logger.info('Approach 3 result', { count: wabaList.length });
        } catch (err) {
          logger.warn('Approach 3 failed', { error: err.message, response: err.response?.data });
        }
      }

      if (!wabaList || wabaList.length === 0) {
        logger.error('No WABA found with any approach');
        throw new Error('No WhatsApp Business Accounts found. Please make sure you have completed the Embedded Signup flow and have access to a WhatsApp Business Account.');
      }

      logger.info('✅ Found WABAs', { count: wabaList.length });

      // Get the first WABA and its first phone number
      const waba = wabaList[0];
      const phoneNumbers = waba.phone_numbers?.data || [];

      logger.info('Selected WABA', { 
        wabaId: waba.id, 
        wabaName: waba.name,
        phoneNumbersCount: phoneNumbers.length 
      });

      if (phoneNumbers.length === 0) {
        throw new Error('No phone numbers found for this WhatsApp Business Account. Please add a phone number in Meta Business Manager.');
      }

      const phoneNumber = phoneNumbers[0];

      return {
        id: waba.id,
        name: waba.name || 'WhatsApp Business Account',
        phone_number_id: phoneNumber.id,
        phone_number: phoneNumber.display_phone_number,
        verified_name: phoneNumber.verified_name
      };
    } catch (error) {
      logger.error('❌ Failed to get WABA details', { 
        error: error.message,
        response: error.response?.data 
      });
      throw new Error(error.response?.data?.error?.message || 'Failed to retrieve WhatsApp Business Account details');
    }
  }

  /**
   * Get all WhatsApp Cloud accounts for tenant
   * GET /api/whatsapp-cloud/accounts
   * Returns both Cloud accounts and Evolution instances
   */
  static async getAccounts(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;

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

      // Check if user is admin
      if (!req.user || (req.user.role !== 'admin' && req.user.role !== 'superadmin')) {
        return res.status(403).json({
          success: false,
          message: 'Access denied. Admin privileges required.'
        });
      }

      let cloudAccounts = [];
      let normalizedEvolutionAccounts = [];

      // Get Cloud accounts
      try {
        const [accounts] = await pool.execute(
          `SELECT id, account_name, waba_id, phone_number_id, phone_number, 
                  status, is_default, webhook_verified, verify_token, templates_synced_at, templates_count,
                  created_at, updated_at
           FROM whatsapp_cloud_accounts 
           WHERE tenant_id = ?
           ORDER BY is_default DESC, created_at DESC`,
          [tenantId]
        );
        cloudAccounts = accounts;
      } catch (cloudError) {
        logger.error('Error loading Cloud accounts', {
          tenantId,
          error: cloudError.message
        });
        // Continue even if Cloud accounts fail
      }

      // Get Evolution instances (ALL instances, not just connected, but only active ones)
      try {
        logger.info('🔍 Attempting to load Evolution instances', { tenantId });
        
        const [evolutionInstances] = await pool.execute(
          `SELECT id, evolution_instance_id, instance_name, phone_number, status, created_at, updated_at
           FROM evolution_instances
           WHERE tenant_id = ? AND (is_active = 1 OR is_active IS NULL)
           ORDER BY instance_name ASC`,
          [tenantId]
        );

        logger.info('✅ Evolution instances loaded successfully', {
          tenantId,
          count: evolutionInstances.length,
          instances: evolutionInstances.map(i => ({
            id: i.id,
            name: i.instance_name,
            status: i.status,
            phone: i.phone_number
          }))
        });

        // Normalize Evolution instances to match Cloud account format
        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',
          webhook_verified: instance.status === 'connected',
          is_default: false,
          is_evolution: true,
          created_at: instance.created_at,
          updated_at: instance.updated_at
        }));
        
        logger.info('✅ Evolution instances normalized', {
          tenantId,
          normalizedCount: normalizedEvolutionAccounts.length
        });
      } catch (evolutionError) {
        logger.error('❌ Error loading Evolution instances', {
          tenantId,
          error: evolutionError.message,
          code: evolutionError.code,
          sqlState: evolutionError.sqlState,
          stack: evolutionError.stack
        });
        // Continue even if Evolution instances fail (table might not exist)
      }

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

      logger.info('📊 Final accounts summary', {
        tenantId,
        cloudAccountsCount: cloudAccounts.length,
        evolutionAccountsCount: normalizedEvolutionAccounts.length,
        totalAccountsCount: allAccounts.length,
        cloudAccounts: cloudAccounts.map(a => ({ id: a.id, name: a.account_name, status: a.status })),
        evolutionAccounts: normalizedEvolutionAccounts.map(a => ({ id: a.id, name: a.account_name, status: a.status }))
      });

      return res.json({
        success: true,
        data: allAccounts
      });
    } catch (error) {
      logger.error('Error getting WhatsApp accounts', { error: error.message, stack: error.stack });
      return res.status(500).json({
        success: false,
        message: 'Failed to load WhatsApp accounts',
        error: error.message
      });
    }
  }

  /**
   * Set default WhatsApp account
   * PUT /api/whatsapp-cloud/accounts/:id/set-default
   */
  static async setDefaultAccount(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const accountId = req.params.id;

      // Verify account belongs to tenant
      const [accounts] = await pool.execute(
        'SELECT id FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

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

      // Remove default from all accounts
      await pool.execute(
        'UPDATE whatsapp_cloud_accounts SET is_default = FALSE WHERE tenant_id = ?',
        [tenantId]
      );

      // Set new default
      await pool.execute(
        'UPDATE whatsapp_cloud_accounts SET is_default = TRUE WHERE id = ?',
        [accountId]
      );

      logger.info('Default WhatsApp account updated', { tenantId, accountId });

      return res.json({
        success: true,
        message: 'Default account updated successfully'
      });
    } catch (error) {
      logger.error('Error setting default account', { error: error.message });
      return res.status(500).json({
        success: false,
        message: 'Failed to update default account'
      });
    }
  }

  /**
   * Delete WhatsApp account
   * DELETE /api/whatsapp-cloud/accounts/:id
   */
  static async deleteAccount(req, res) {
    const connection = await pool.getConnection();
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const accountId = req.params.id;

      logger.info('Delete account request', { tenantId, accountId, userId: req.user?.id });

      // Verify account belongs to tenant
      const [accounts] = await connection.execute(
        'SELECT id, account_name, is_default FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

      if (accounts.length === 0) {
        logger.warn('Account not found for deletion', { tenantId, accountId });
        return res.status(404).json({
          success: false,
          message: 'Account not found'
        });
      }

      const account = accounts[0];
      const wasDefault = account.is_default;

      logger.info('Starting account deletion process', { 
        tenantId, 
        accountId, 
        accountName: account.account_name,
        wasDefault 
      });

      // Start transaction for complete deletion
      await connection.beginTransaction();

      try {
        // Delete related messages first
        const [messageResult] = await connection.execute(
          `DELETE wm FROM whatsapp_cloud_messages wm 
           INNER JOIN whatsapp_cloud_conversations wc ON wm.conversation_id = wc.id 
           WHERE wc.account_id = ?`,
          [accountId]
        );
        logger.info('Deleted messages', { accountId, deletedMessages: messageResult.affectedRows });

        // Delete related conversations
        const [conversationResult] = await connection.execute(
          'DELETE FROM whatsapp_cloud_conversations WHERE account_id = ?',
          [accountId]
        );
        logger.info('Deleted conversations', { accountId, deletedConversations: conversationResult.affectedRows });

        const [campaignResult] = await connection.execute(
          'DELETE FROM whatsapp_cloud_campaigns WHERE account_id = ?',
          [accountId]
        );
        logger.info('Deleted campaigns', { accountId, deletedCampaigns: campaignResult.affectedRows });

        const [templateResult] = await connection.execute(
          'DELETE FROM whatsapp_cloud_templates WHERE account_id = ?',
          [accountId]
        );
        logger.info('Deleted templates', { accountId, deletedTemplates: templateResult.affectedRows });

        // Delete the account itself
        const [accountResult] = await connection.execute(
          'DELETE FROM whatsapp_cloud_accounts WHERE id = ?',
          [accountId]
        );
        logger.info('Deleted account', { accountId, deletedAccounts: accountResult.affectedRows });

        // If deleted account was default, set another as default
        if (wasDefault) {
          const [defaultResult] = await connection.execute(
            `UPDATE whatsapp_cloud_accounts 
             SET is_default = TRUE 
             WHERE tenant_id = ? 
             ORDER BY created_at DESC 
             LIMIT 1`,
            [tenantId]
          );
          logger.info('Updated default account', { tenantId, updatedRows: defaultResult.affectedRows });
        }

        // Commit transaction
        await connection.commit();

        logger.info('WhatsApp account completely deleted from database', { 
          tenantId, 
          accountId, 
          accountName: account.account_name,
          deletedMessages: messageResult.affectedRows,
          deletedConversations: conversationResult.affectedRows
        });

        return res.json({
          success: true,
          message: `Account "${account.account_name}" and all related data have been permanently deleted from the database`
        });

      } catch (transactionError) {
        // Rollback transaction on error
        await connection.rollback();
        throw transactionError;
      }

    } catch (error) {
      logger.error('Error deleting account', { 
        error: error.message, 
        stack: error.stack,
        tenantId: req.tenantId || req.user?.tenantId,
        accountId: req.params.id
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to delete account from database'
      });
    } finally {
      connection.release();
    }
  }

  /**
   * Update WhatsApp account manually
   * PUT /api/whatsapp-cloud/accounts/:id
   */
  static async updateAccount(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const accountId = req.params.id;
      const {
        account_name,
        waba_id,
        phone_number_id,
        phone_number,
        access_token,
        app_id,
        app_secret,
        verify_token
      } = req.body;

      // Verify account belongs to tenant
      const [accounts] = await pool.execute(
        'SELECT id FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

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

      const updates = [];
      const values = [];

      if (account_name !== undefined) {
        updates.push('account_name = ?');
        values.push(account_name);
      }
      if (waba_id !== undefined) {
        updates.push('waba_id = ?');
        values.push(waba_id);
      }
      if (phone_number_id !== undefined) {
        updates.push('phone_number_id = ?');
        values.push(phone_number_id);
      }
      if (phone_number !== undefined) {
        updates.push('phone_number = ?');
        values.push(phone_number);
      }
      if (access_token !== undefined && access_token !== '********') {
        updates.push('access_token = ?');
        values.push(access_token);
      }
      if (app_id !== undefined) {
        updates.push('app_id = ?');
        values.push(app_id);
      }
      if (app_secret !== undefined && app_secret !== '********') {
        updates.push('app_secret = ?');
        values.push(app_secret);
      }
      if (verify_token !== undefined) {
        updates.push('verify_token = ?');
        values.push(verify_token);
      }

      if (updates.length === 0) {
        return res.status(400).json({
          success: false,
          message: 'No fields to update'
        });
      }

      updates.push('updated_at = NOW()');
      values.push(accountId);

      await pool.execute(
        `UPDATE whatsapp_cloud_accounts SET ${updates.join(', ')} WHERE id = ?`,
        values
      );

      logger.info('WhatsApp account updated', { tenantId, accountId });

      return res.json({
        success: true,
        message: 'Account updated successfully'
      });
    } catch (error) {
      logger.error('Error updating account', { error: error.message });
      return res.status(500).json({
        success: false,
        message: 'Failed to update account'
      });
    }
  }

  /**
   * Test WhatsApp account connection
   * POST /api/whatsapp-cloud/accounts/:id/test
   */
  static async testAccount(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const accountId = req.params.id;

      // Get account details
      const [accounts] = await pool.execute(
        'SELECT phone_number_id, access_token FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

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

      const account = accounts[0];

      // Test connection by getting phone number details
      const response = await axios.get(
        `https://graph.facebook.com/v18.0/${account.phone_number_id}`,
        {
          params: {
            access_token: account.access_token,
            fields: 'id,display_phone_number,verified_name,quality_rating'
          }
        }
      );

      return res.json({
        success: true,
        message: 'Connection test successful',
        data: {
          phone_number: response.data.display_phone_number,
          verified_name: response.data.verified_name,
          quality_rating: response.data.quality_rating
        }
      });
    } catch (error) {
      logger.error('Connection test failed', { 
        error: error.message,
        response: error.response?.data 
      });
      
      return res.status(400).json({
        success: false,
        message: error.response?.data?.error?.message || 'Connection test failed',
        error: error.response?.data
      });
    }
  }

  /**
   * Webhook verification (GET)
   * Facebook will call this to verify the webhook
   */
  static async webhookVerify(req, res) {
    try {
      const mode = req.query['hub.mode'];
      const token = req.query['hub.verify_token'];
      const challenge = req.query['hub.challenge'];

      logger.info('Webhook verification request', { mode, token: token ? '***' : 'none' });

      if (mode !== 'subscribe') {
        logger.warn('Invalid webhook mode', { mode });
        return res.sendStatus(403);
      }

      if (!token) {
        logger.warn('No verify token provided');
        return res.sendStatus(403);
      }

      // Check if any account has this verify token
      const [accounts] = await pool.execute(
        'SELECT id, account_name FROM whatsapp_cloud_accounts WHERE verify_token = ? AND webhook_verified = TRUE',
        [token]
      );

      if (accounts.length > 0) {
        logger.info('Webhook verified successfully', { 
          accountId: accounts[0].id, 
          accountName: accounts[0].account_name 
        });
        return res.status(200).send(challenge);
      } else {
        logger.warn('No account found with matching verify token');
        return res.sendStatus(403);
      }
    } catch (error) {
      logger.error('Webhook verification error', { error: error.message });
      return res.sendStatus(403);
    }
  }

  static verifyWebhook(req, res) {
    return WhatsAppCloudController.webhookVerify(req, res);
  }

  /**
   * Webhook receiver (POST)
   * Receives messages and events from WhatsApp
   */
  static async webhookReceive(req, res) {
    try {
      const body = req.body;

      logger.info('Webhook event received', { 
        object: body.object,
        entries: body.entry?.length,
        fullBody: JSON.stringify(body)
      });

      // Respond quickly to Facebook
      res.sendStatus(200);

      // Process webhook asynchronously
      if (body.object === 'whatsapp_business_account') {
        if (body.entry && body.entry.length > 0) {
          for (const entry of body.entry) {
            logger.info('Processing entry', { entryId: entry.id });
            
            if (entry.changes && entry.changes.length > 0) {
              for (const change of entry.changes) {
                logger.info('Processing change', { field: change.field });
                
                if (change.field === 'messages') {
                  await WhatsAppCloudController.processMessage(change.value);
                }
              }
            }
          }
        }
      } else {
        logger.warn('Unknown webhook object type', { object: body.object });
      }
    } catch (error) {
      logger.error('Webhook processing error', { 
        error: error.message, 
        stack: error.stack,
        body: JSON.stringify(req.body)
      });
      // Still return 200 to Facebook to avoid retries
      if (!res.headersSent) {
        res.sendStatus(200);
      }
    }
  }

  /**
   * Process incoming WhatsApp message
   */
  static async processMessage(value) {
    try {
      logger.info('Processing WhatsApp message', { 
        phoneNumberId: value.metadata?.phone_number_id,
        displayPhoneNumber: value.metadata?.display_phone_number,
        messagesCount: value.messages?.length,
        statusesCount: value.statuses?.length
      });

      // Handle message statuses (sent, delivered, read)
      if (value.statuses && value.statuses.length > 0) {
        logger.info('Received message statuses', { 
          count: value.statuses.length,
          statuses: value.statuses.map(s => ({ id: s.id, status: s.status }))
        });
        
        // Update message status in database
        for (const status of value.statuses) {
          await pool.execute(
            `UPDATE whatsapp_cloud_messages 
             SET status = ? 
             WHERE message_id = ?`,
            [status.status, status.id]
          );
          logger.info('Message status updated', { messageId: status.id, status: status.status });
        }
        return;
      }

      if (!value.messages || value.messages.length === 0) {
        logger.info('No messages to process');
        return;
      }

      const phoneNumberId = value.metadata?.phone_number_id;
      
      if (!phoneNumberId) {
        logger.error('No phone_number_id in metadata');
        return;
      }

      // Find which tenant owns this phone number
      const [accounts] = await pool.execute(
        'SELECT id, tenant_id, account_name, access_token, phone_number_id FROM whatsapp_cloud_accounts WHERE phone_number_id = ?',
        [phoneNumberId]
      );

      if (accounts.length === 0) {
        logger.warn('No account found for phone number', { phoneNumberId });
        return;
      }

      const account = accounts[0];
      const tenantId = account.tenant_id;
      const accountId = account.id;
      
      logger.info('Found tenant for message', { tenantId, accountId, accountName: account.account_name });
      if (!account.access_token || !account.phone_number_id) {
        logger.error('Account missing WhatsApp credentials', { accountId, hasAccessToken: !!account.access_token, hasPhoneNumberId: !!account.phone_number_id });
      }

      // Mark webhook as verified for this account upon receiving any event
      try {
        await pool.execute(
          'UPDATE whatsapp_cloud_accounts SET webhook_verified = TRUE, updated_at = NOW() WHERE id = ?',
          [accountId]
        );
      } catch (e) {
        logger.warn('Failed to mark webhook verified', { accountId, error: e.message });
      }

      // Process each message
      for (const message of value.messages) {
        try {
          const contactPhone = message.from;
          const contactName = value.contacts?.[0]?.profile?.name || contactPhone;
          
          logger.info('Processing message', {
            tenantId,
            accountId,
            from: contactPhone,
            type: message.type,
            messageId: message.id,
            timestamp: message.timestamp
          });

          // Get or create conversation
          let conversationId;
          const [existingConv] = await pool.execute(
            'SELECT id FROM whatsapp_cloud_conversations WHERE account_id = ? AND contact_phone = ?',
            [accountId, contactPhone]
          );

          if (existingConv.length > 0) {
            conversationId = existingConv[0].id;
            
            // Update conversation
            await pool.execute(
              `UPDATE whatsapp_cloud_conversations 
               SET last_message_text = ?, 
                   last_message_time = FROM_UNIXTIME(?), 
                   last_message_from = 'customer',
                   unread_count = unread_count + 1,
                   contact_name = ?,
                   updated_at = NOW()
               WHERE id = ?`,
              [
                message.text?.body || `[${message.type}]`,
                message.timestamp,
                contactName,
                conversationId
              ]
            );
          } else {
            // Create new conversation (unassigned - no store/department)
            const [result] = await pool.execute(
              `INSERT INTO whatsapp_cloud_conversations 
               (tenant_id, account_id, contact_phone, contact_name, last_message_text, 
                last_message_time, last_message_from, unread_count, status, store_id, department_id)
               VALUES (?, ?, ?, ?, ?, FROM_UNIXTIME(?), 'customer', 1, 'open', NULL, NULL)`,
              [
                tenantId,
                accountId,
                contactPhone,
                contactName,
                message.text?.body || `[${message.type}]`,
                message.timestamp
              ]
            );
            conversationId = result.insertId;
          }

          // Extract message content based on type
          let textBody = null;
          let mediaUrl = null;
          let mediaMimeType = null;
          let mediaCaption = null;
          let mediaFilename = null;

          switch (message.type) {
            case 'text':
              textBody = message.text?.body;
              break;
            case 'image':
              mediaUrl = message.image?.id; // Store media ID, will need to download later
              mediaMimeType = message.image?.mime_type;
              mediaCaption = message.image?.caption;
              break;
            case 'video':
              mediaUrl = message.video?.id;
              mediaMimeType = message.video?.mime_type;
              mediaCaption = message.video?.caption;
              break;
            case 'audio':
              mediaUrl = message.audio?.id;
              mediaMimeType = message.audio?.mime_type;
              break;
            case 'document':
              mediaUrl = message.document?.id;
              mediaMimeType = message.document?.mime_type;
              mediaCaption = message.document?.caption;
              mediaFilename = message.document?.filename;
              break;
            case 'interactive': {
              const buttonTitle = message.interactive?.button_reply?.title;
              const listTitle = message.interactive?.list_reply?.title;
              const listDescription = message.interactive?.list_reply?.description;
              const replyId = message.interactive?.button_reply?.id || message.interactive?.list_reply?.id;
              textBody = buttonTitle || listTitle || listDescription || replyId || null;
              break;
            }
            case 'location':
              textBody = `Location: ${message.location?.latitude}, ${message.location?.longitude}`;
              break;
            case 'sticker':
              mediaUrl = message.sticker?.id;
              mediaMimeType = message.sticker?.mime_type;
              break;
          }

          if (!textBody && mediaCaption) {
            textBody = mediaCaption;
          }

          // Save message to database
          // Simple INSERT with only the columns that definitely exist
          await pool.execute(
            `INSERT INTO whatsapp_cloud_messages 
             (conversation_id, message_id, message_type, text_content, media_url, status)
             VALUES (?, ?, ?, ?, ?, 'delivered')`,
            [
              conversationId,
              message.id,
              message.type,
              textBody || mediaCaption || `[${message.type}]`,
              mediaUrl
            ]
          );

          logger.info('Message saved to database', {
            tenantId,
            accountId,
            conversationId,
            messageId: message.id,
            type: message.type
          });

          const flowContext = {
            tenantId,
            accountId,
            account,
            conversationId,
            contactPhone,
            contactName,
            message,
            textBody
          };
          eventBus.emit(Events.MESSAGE_RECEIVED, {
            channel: 'cloud',
            tenantId,
            sessionId: accountId || null,
            conversationId,
            accountId,
            contactPhone,
            contactName,
            messageId: message?.id || null,
            messageType: message?.type || 'text',
            textBody,
            flowContext,
            enableFaq: false
          });
          logInfo('Cloud message received', {
            tenant_id: tenantId,
            session_id: accountId || null,
            message_id: message?.id || null,
            module: 'whatsapp_cloud_controller',
            event: 'message_received',
            step: 'emit'
          });

          /* FAQ auto-reply disabled for now
          let faqHandled = false;
          try {
            const faqResult = await WhatsAppCloudFAQService.processMessage(
              tenantId,
              accountId,
              conversationId,
              textBody || '',
              contactPhone,
              { contactName, messageType: message?.type || 'text' }
            );
            faqHandled = !!faqResult?.matched;
          } catch (faqError) {
            logger.error('Error processing FAQ auto-reply', { error: faqError.message });
          }
          if (!faqHandled) {
            logger.debug('No flow or FAQ match');
          }
          */

        } catch (msgError) {
          console.error('❌ ERROR PROCESSING MESSAGE:', {
            error: msgError.message,
            stack: msgError.stack,
            messageId: message?.id,
            messageType: message?.type,
            sqlMessage: msgError.sqlMessage,
            code: msgError.code,
            errno: msgError.errno,
            sql: msgError.sql
          });
          
          logger.error('Error processing individual message', {
            error: msgError.message,
            stack: msgError.stack,
            messageId: message?.id,
            messageType: message?.type,
            sqlMessage: msgError.sqlMessage,
            code: msgError.code,
            errno: msgError.errno
          });
        }
      }
    } catch (error) {
      logger.error('Error processing message', { 
        error: error.message, 
        stack: error.stack,
        value: JSON.stringify(value)
      });
    }
  }

  static async processFlowAutoReply({
    tenantId,
    accountId,
    account,
    conversationId,
    contactPhone,
    contactName,
    message,
    textBody,
    retry = false
  }) {
    return CloudFlowService.processFlowAutoReply({
      tenantId,
      accountId,
      account,
      conversationId,
      contactPhone,
      contactName,
      message,
      textBody,
      retry
    });
  }

  static async sendFlowMediaMessage({ tenantId, account, conversationId, contactPhone, node }) {
    return CloudFlowService.sendFlowMediaMessage({
      tenantId,
      account,
      conversationId,
      contactPhone,
      node
    });
  }

  static async sendFlowMenuOptionsMessage({ tenantId, account, conversationId, contactPhone, node }) {
    return CloudFlowService.sendFlowMenuOptionsMessage({
      tenantId,
      account,
      conversationId,
      contactPhone,
      node
    });
  }

  static safeJsonParse(value, fallback) {
    return CloudFlowService.safeJsonParse(value, fallback);
  }

  static normalizeFlowText(text) {
    return CloudFlowService.normalizeFlowText(text);
  }

  static isKeywordTriggerMatch(triggerValue, normalizedText, messageWords) {
    return CloudFlowService.isKeywordTriggerMatch(triggerValue, normalizedText, messageWords);
  }

  static async ensureFlowTables() {
    return CloudFlowService.ensureFlowTables();
  }

  static resolveWaitingNextNode(node, message, connections) {
    return CloudFlowService.resolveWaitingNextNode(node, message, connections);
  }

  static getStartNode(flow) {
    return CloudFlowService.getStartNode(flow);
  }

  static getNextNodeId(flow, nodeId, handle = 'default') {
    return CloudFlowService.getNextNodeId(flow, nodeId, handle);
  }

  static async executeFlowFromNode({
    tenantId,
    accountId,
    account,
    conversationId,
    contactPhone,
    contactName,
    flow,
    startNodeId,
    sessionId,
    message,
    textBody
  }) {
    return CloudFlowService.executeFlowFromNode({
      tenantId,
      accountId,
      account,
      conversationId,
      contactPhone,
      contactName,
      flow,
      startNodeId,
      sessionId,
      message,
      textBody
    });
  }

  static async sendFlowTextMessage({ tenantId, account, conversationId, contactPhone, message }) {
    return CloudFlowService.sendFlowTextMessage({
      tenantId,
      account,
      conversationId,
      contactPhone,
      message
    });
  }

  static async sendFlowReaction({ tenantId, account, contactPhone, messageId, emoji }) {
    return CloudFlowService.sendFlowReaction({
      tenantId,
      account,
      contactPhone,
      messageId,
      emoji
    });
  }

  static decryptApiKey(encryptedText) {
    return CloudFlowService.decryptApiKey(encryptedText);
  }

  static async processCloudAIResponse({ tenantId, account, conversationId, contactPhone, message, textBody, aiSettings }) {
    return CloudFlowService.processCloudAIResponse({
      tenantId,
      account,
      conversationId,
      contactPhone,
      message,
      textBody,
      aiSettings
    });
  }

  static async sendFlowButtonsMessage({ tenantId, account, conversationId, contactPhone, node }) {
    return CloudFlowService.sendFlowButtonsMessage({
      tenantId,
      account,
      conversationId,
      contactPhone,
      node
    });
  }

  static async sendFlowListMessage({ tenantId, account, conversationId, contactPhone, node }) {
    return CloudFlowService.sendFlowListMessage({
      tenantId,
      account,
      conversationId,
      contactPhone,
      node
    });
  }

  static async sendFlowProductsMessage({ tenantId, account, conversationId, contactPhone, node }) {
    return CloudFlowService.sendFlowProductsMessage({
      tenantId,
      account,
      conversationId,
      contactPhone,
      node
    });
  }

  static async sendFlowCtaMessage({ tenantId, account, conversationId, contactPhone, node }) {
    return CloudFlowService.sendFlowCtaMessage({
      tenantId,
      account,
      conversationId,
      contactPhone,
      node
    });
  }

  /**
   * Sync message templates from Meta Graph API
   * POST /api/whatsapp-cloud/accounts/:id/sync-templates
   */
  static async syncTemplates(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const accountId = req.params.id;

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

      // Get account details
      const [accounts] = await pool.execute(
        'SELECT waba_id, access_token FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

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

      const account = accounts[0];

      if (!account.waba_id || !account.access_token) {
        return res.status(400).json({
          success: false,
          message: 'Account is missing WABA ID or access token'
        });
      }

      logger.info('Syncing templates from Meta', { tenantId, accountId, wabaId: account.waba_id });

      // Call Meta Graph API to get message templates
      const response = await axios.get(
        `https://graph.facebook.com/v18.0/${account.waba_id}/message_templates`,
        {
          params: {
            access_token: account.access_token,
            fields: 'name,language,status,category,components,id'
          }
        }
      );

      const templates = response.data.data || [];

      logger.info('Templates retrieved from Meta', { 
        tenantId, 
        accountId, 
        count: templates.length 
      });

      // Save templates to database
      for (const template of templates) {
        const body = template.components?.find(c => c.type === 'BODY')?.text || '';
        const header = template.components?.find(c => c.type === 'HEADER')?.text || '';
        const footer = template.components?.find(c => c.type === 'FOOTER')?.text || '';
        const buttons = template.components?.find(c => c.type === 'BUTTONS')?.buttons || [];

        // Insert or update template
        await pool.execute(
          `INSERT INTO whatsapp_cloud_templates 
           (tenant_id, account_id, template_id, name, language, status, category, components, body, header, footer, buttons)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
           ON DUPLICATE KEY UPDATE
           name = VALUES(name),
           language = VALUES(language),
           status = VALUES(status),
           category = VALUES(category),
           components = VALUES(components),
           body = VALUES(body),
           header = VALUES(header),
           footer = VALUES(footer),
           buttons = VALUES(buttons),
           updated_at = NOW()`,
          [
            tenantId,
            accountId,
            template.id,
            template.name,
            template.language,
            template.status,
            template.category,
            JSON.stringify(template.components || []),
            body,
            header,
            footer,
            JSON.stringify(buttons)
          ]
        );
      }

      logger.info('Templates saved to database', { tenantId, accountId, count: templates.length });

      // Update account with sync info
      await pool.execute(
        `UPDATE whatsapp_cloud_accounts 
         SET templates_synced_at = NOW(), templates_count = ?
         WHERE id = ?`,
        [templates.length, accountId]
      );

      // Format templates for frontend
      const formattedTemplates = templates.map(template => ({
        id: template.id,
        name: template.name,
        language: template.language,
        status: template.status,
        category: template.category,
        components: template.components || [],
        body: template.components?.find(c => c.type === 'BODY')?.text || '',
        header: template.components?.find(c => c.type === 'HEADER')?.text || '',
        footer: template.components?.find(c => c.type === 'FOOTER')?.text || '',
        buttons: template.components?.find(c => c.type === 'BUTTONS')?.buttons || []
      }));

      return res.json({
        success: true,
        message: `${templates.length} templates synced successfully`,
        data: {
          count: templates.length,
          templates: formattedTemplates
        }
      });
    } catch (error) {
      logger.error('Error syncing templates', { 
        error: error.message,
        stack: error.stack,
        response: error.response?.data 
      });
      
      return res.status(500).json({
        success: false,
        message: error.response?.data?.error?.message || 'Failed to sync templates from Meta',
        error: error.response?.data
      });
    }
  }

  /**
   * Get templates for account
   * GET /api/whatsapp-cloud/accounts/:id/templates
   */
  static async getTemplates(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const accountId = req.params.id;

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

      // Get templates from database
      const [templates] = await pool.execute(
        `SELECT template_id as id, name, language, status, category, components, body, header, footer, buttons
         FROM whatsapp_cloud_templates
         WHERE tenant_id = ? AND account_id = ?
         ORDER BY name ASC`,
        [tenantId, accountId]
      );

      // Parse JSON fields
      const formattedTemplates = templates.map(template => ({
        ...template,
        components: typeof template.components === 'string' ? JSON.parse(template.components) : template.components,
        buttons: typeof template.buttons === 'string' ? JSON.parse(template.buttons) : template.buttons
      }));

      return res.json({
        success: true,
        data: {
          count: formattedTemplates.length,
          templates: formattedTemplates
        }
      });
    } catch (error) {
      logger.error('Error getting templates', { 
        error: error.message,
        stack: error.stack
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to load templates'
      });
    }
  }

  static async markWebhookVerified(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const accountId = req.params.id;
      const { verify_token } = req.body || {};
      
      logger.info('Mark webhook verified request', { 
        tenantId, 
        accountId, 
        hasVerifyToken: !!verify_token
      });
      
      if (!tenantId) {
        logger.error('No tenant ID in mark webhook verified request');
        return res.status(400).json({ success: false, message: 'Tenant ID not found in request' });
      }
      
      if (!verify_token) {
        return res.status(400).json({ success: false, message: 'Verify token is required' });
      }
      
      const [accounts] = await pool.execute(
        'SELECT id FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );
      
      if (accounts.length === 0) {
        logger.warn('Account not found for webhook verification', { accountId, tenantId });
        return res.status(404).json({ success: false, message: 'Account not found' });
      }
      
      // Save the verify token to the account and mark as verified
      await pool.execute(
        'UPDATE whatsapp_cloud_accounts SET webhook_verified = TRUE, verify_token = ?, updated_at = NOW() WHERE id = ?',
        [verify_token, accountId]
      );
      
      logger.info('Webhook marked as verified successfully', { accountId, tenantId });
      return res.json({ success: true, message: 'Webhook verified and token saved' });
    } catch (error) {
      logger.error('Error marking webhook verified', { error: error.message, stack: error.stack });
      return res.status(500).json({ success: false, message: 'Failed to verify webhook' });
    }
  }
  /**
   * Get all conversations for active account
   * GET /api/user/whatsapp-cloud/conversations
   */
  static async getWebConversations(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found in request'
        });
      }

      const io = req.app.get('io');
      const whatsappService = getWhatsAppService(io);

      const conversations = await whatsappService.getWebConversations(tenantId);
      const normalizedTenantId = Number(tenantId);
      const instance = whatsappService.getInstance?.(normalizedTenantId);
      const connected = instance?.connection?.isConnected?.(normalizedTenantId) || false;
      const hasSession = instance?.connection?.stateManager?.hasSession?.(normalizedTenantId) || false;

      return res.json({
        success: true,
        data: conversations,
        meta: {
          connected,
          hasSession,
          chatsCount: conversations?.length || 0
        }
      });
    } catch (error) {
      logger.error('Error fetching 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'
      });
    }
  }

  /**
   * Get all conversations for active account
   * GET /api/user/whatsapp-cloud/conversations
   */
  static async getConversations(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const { accountId } = req.query;

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

      // 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 (user)', { accountId, tenantId, isEvolutionInstance });
        } catch (err) {
          logger.warn('Error checking if account is Evolution instance (user)', { error: err.message });
        }
      }

      // If it's an Evolution API account, get conversations from conversations table
      if (isEvolutionInstance) {
        const instanceId = parseInt(accountId);
        
        logger.info('Fetching Evolution API conversations (user)', { tenantId, instanceId, accountId });
        
        // Get conversations that have messages from this Evolution instance
        const [conversations] = await pool.execute(
          `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.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
           WHERE c.tenant_id = ?
           AND (c.remote_jid IS NULL OR c.remote_jid NOT LIKE '%@g.us%')
           ORDER BY c.last_message_time DESC`,
          [accountId, instanceId, instanceId, tenantId]
        );

        logger.info('Evolution API conversations retrieved (user)', { 
          tenantId, 
          instanceId,
          accountId,
          count: conversations.length 
        });

        // 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
        });
      }

      // Get conversations with account info (WhatsApp Cloud)
      const query = accountId
        ? `SELECT c.*, a.account_name, a.phone_number as account_phone
           FROM whatsapp_cloud_conversations c
           JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
           WHERE c.tenant_id = ? AND c.account_id = ?
           ORDER BY c.last_message_time DESC`
        : `SELECT c.*, a.account_name, a.phone_number as account_phone
           FROM whatsapp_cloud_conversations c
           JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
           WHERE c.tenant_id = ?
           ORDER BY c.last_message_time DESC`;

      const params = accountId ? [tenantId, accountId] : [tenantId];
      const [conversations] = await pool.execute(query, params);

      return res.json({
        success: true,
        data: conversations
      });
    } catch (error) {
      logger.error('Error getting conversations', { 
        error: error.message,
        stack: error.stack
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to load conversations'
      });
    }
  }

  /**
   * Get messages for a conversation
   * GET /api/whatsapp-cloud/conversations/:id/messages
   */
  static async getMessages(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const conversationId = req.params.id;
      const page = parseInt(req.query.page) || 1;
      const limit = parseInt(req.query.limit) || 50;
      const offset = (page - 1) * limit;

      logger.info('📨 Get messages request', {
        conversationId,
        tenantId,
        page,
        limit,
        userRole: req.user?.role
      });

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

      // Check if this is an Evolution conversation
      logger.info('🔍 Checking if Evolution conversation...', { conversationId, tenantId });
      const [evolutionCheck] = await pool.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]
      );

      logger.info('📊 Evolution check result:', {
        found: evolutionCheck.length > 0,
        conversationId
      });

      if (evolutionCheck.length > 0) {
        logger.info('✅ Evolution conversation detected (admin), fetching from whatsapp_messages', {
          conversationId,
          tenantId
        });
        
        // Get messages from whatsapp_messages table
        const [messages] = await pool.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 pool.execute(
          'SELECT COUNT(*) as total FROM whatsapp_messages WHERE conversation_id = ? AND tenant_id = ?',
          [conversationId, tenantId]
        );

        logger.info('✅ Evolution messages retrieved', {
          conversationId,
          count: messages.length,
          total: countResult[0].total
        });

        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)
          }
        });
      }

      logger.info('🔍 Not Evolution, checking WhatsApp Cloud...', { conversationId, tenantId });

      // Verify conversation belongs to tenant (WhatsApp Cloud)
      const [conversations] = await pool.execute(
        'SELECT id FROM whatsapp_cloud_conversations WHERE id = ? AND tenant_id = ?',
        [conversationId, tenantId]
      );

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

      // Get total count
      const [countResult] = await pool.execute(
        'SELECT COUNT(*) as total FROM whatsapp_cloud_messages WHERE conversation_id = ?',
        [conversationId]
      );
      const total = countResult[0]?.total || 0;

      // Get messages with pagination
      const [messages] = await pool.execute(
        `SELECT m.*,
                u.name as sent_by_name,
                COALESCE(u.store, s.name) as sent_by_store,
                COALESCE(u.department, d.name) as sent_by_department
         FROM whatsapp_cloud_messages m
         LEFT JOIN users u ON m.sent_by_user_id = u.id
         LEFT JOIN stores s ON u.store_id = s.id
         LEFT JOIN departments d ON u.department_id = d.id
         WHERE m.conversation_id = ?
         ORDER BY m.created_at ASC
         LIMIT ? OFFSET ?`,
        [conversationId, limit, offset]
      );

      // Mark messages as read
      await pool.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET unread_count = 0 
         WHERE id = ?`,
        [conversationId]
      );

      return res.json({
        success: true,
        data: messages,
        pagination: {
          page,
          limit,
          total,
          pages: Math.ceil(total / limit)
        }
      });
    } catch (error) {
      logger.error('Error getting messages', { 
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to load messages'
      });
    }
  }

  /**
   * Send text message
   * POST /api/whatsapp-cloud/send-message
   */
  static async sendMessage(req, res) {
    try {
      if (!res || typeof res.status !== 'function' || typeof res.json !== 'function') {
        return false;
      }
      const { accountId, to, message, conversationId } = req.body;
      const tenantId = req.tenantId || req.user.tenantId;

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

      if (!accountId || !to || !message) {
        return res.status(400).json({
          success: false,
          message: 'Account ID, recipient, and message are required'
        });
      }

      // Get account details
      const [accounts] = await pool.execute(
        'SELECT * FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

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

      const sendResult = await queueMessage({
        channel: 'cloud',
        tenantId,
        accountId,
        to,
        messageType: 'text',
        content: message,
        awaitSend: true
      });

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

      const messageId = sendResult.messageId || `msg_${Date.now()}`;

      logger.info('Message sent via WhatsApp Cloud pipeline', {
        tenantId,
        accountId,
        to,
        messageId
      });

      // Save message to database
      let convId = conversationId;
      
      // If no conversation ID provided, find or create conversation
      if (!convId) {
        const [existingConv] = await pool.execute(
          'SELECT id FROM whatsapp_cloud_conversations WHERE account_id = ? AND contact_phone = ?',
          [accountId, to]
        );

        if (existingConv.length > 0) {
          convId = existingConv[0].id;
        } else {
          // Create new conversation (unassigned - no store/department)
          const [newConv] = await pool.execute(
            `INSERT INTO whatsapp_cloud_conversations 
             (tenant_id, account_id, contact_phone, contact_name, last_message_text, last_message_time, last_message_from, unread_count, status, created_at, updated_at, store_id, department_id)
             VALUES (?, ?, ?, ?, ?, NOW(), 'agent', 0, 'open', NOW(), NOW(), NULL, NULL)`,
            [tenantId, accountId, to, to, message]
          );
          convId = newConv.insertId;
        }
      }

      // Save message
      await pool.execute(
        `INSERT INTO whatsapp_cloud_messages 
         (conversation_id, message_id, direction, message_type, text_content, status, timestamp, created_at)
         VALUES (?, ?, 'outbound', 'text', ?, 'sent', NOW(), NOW())`,
        [convId, messageId, message]
      );

      // Update conversation and set attended_at if first response
      await pool.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 = ?`,
        [message, convId]
      );

      return res.json({
        success: true,
        message: 'Message sent successfully',
        data: {
          messageId,
          conversationId: convId
        }
      });
    } catch (error) {
      logger.error('Error sending message', {
        error: error.message,
        stack: error.stack,
        response: error.response?.data
      });

      if (!res || typeof res.status !== 'function' || typeof res.json !== 'function') {
        return false;
      }
      return res.status(500).json({
        success: false,
        message: error.response?.data?.error?.message || 'Failed to send message'
      });
    }
  }

  static normalizePhone(value) {
    return String(value || '').replace(/[^\d]/g, '');
  }

  static parseAudienceNumbers(input) {
    if (Array.isArray(input)) {
      const unique = new Set();
      input.forEach(item => {
        const value = String(item || '').trim();
        if (value) unique.add(value);
      });
      return Array.from(unique);
    }
    if (typeof input === 'string') {
      const unique = new Set();
      input
        .split(/[\r\n,]+/)
        .map(item => item.trim())
        .filter(item => item.length > 0)
        .forEach(item => unique.add(item));
      return Array.from(unique);
    }
    return [];
  }

  static normalizeScheduleAt(value) {
    if (!value) return null;
    if (typeof value === 'string') {
      const trimmed = value.trim();
      if (trimmed.length === 0) return null;
      let normalized = trimmed.replace('T', ' ');
      if (/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$/.test(normalized)) {
        normalized += ':00';
      }
      return normalized;
    }
    if (value instanceof Date && !Number.isNaN(value.getTime())) {
      const pad = (n) => String(n).padStart(2, '0');
      return `${value.getFullYear()}-${pad(value.getMonth() + 1)}-${pad(value.getDate())} ${pad(value.getHours())}:${pad(value.getMinutes())}:${pad(value.getSeconds())}`;
    }
    return null;
  }

  static async getCampaignRecipients({ tenantId, accountId: _accountId, audienceType, audienceGroups, audienceCustomNumbers }) {
    const recipients = [];
    const normalizedGroups = Array.isArray(audienceGroups) ? audienceGroups : [];
    const customNumbers = WhatsAppCloudController.parseAudienceNumbers(audienceCustomNumbers);

    if (audienceType === 'custom') {
      const seen = new Set();
      customNumbers.forEach(number => {
        const normalized = WhatsAppCloudController.normalizePhone(number);
        if (!normalized || seen.has(normalized)) return;
        seen.add(normalized);
        recipients.push({ phone: normalized, name: normalized });
      });
      return recipients;
    }

    let query = `SELECT name, phone FROM contacts WHERE tenant_id = ? AND phone IS NOT NULL AND phone <> ''`;
    const params = [tenantId];

    if (audienceType === 'groups') {
      if (!normalizedGroups.length) {
        return [];
      }
      const placeholders = normalizedGroups.map(() => '?').join(', ');
      query += ` AND group_id IN (${placeholders})`;
      params.push(...normalizedGroups);
    }

    const [rows] = await pool.execute(query, params);
    const seen = new Set();
    rows.forEach(row => {
      const normalized = WhatsAppCloudController.normalizePhone(row.phone);
      if (!normalized || seen.has(normalized)) return;
      seen.add(normalized);
      recipients.push({ phone: normalized, name: row.name || normalized });
    });
    return recipients;
  }

  static async ensureCloudConversation({ tenantId, accountId, contactPhone, contactName }) {
    const [existing] = await pool.execute(
      'SELECT id FROM whatsapp_cloud_conversations WHERE account_id = ? AND contact_phone = ?',
      [accountId, contactPhone]
    );
    if (existing.length > 0) {
      return existing[0].id;
    }
    try {
      const [result] = await pool.execute(
        `INSERT INTO whatsapp_cloud_conversations
         (tenant_id, account_id, contact_phone, contact_name, last_message_text, last_message_time, last_message_from, unread_count, status, created_at, updated_at)
         VALUES (?, ?, ?, ?, NULL, NOW(), 'business', 0, 'open', NOW(), NOW())`,
        [tenantId, accountId, contactPhone, contactName || contactPhone]
      );
      return result.insertId;
    } catch (error) {
      const [existingRetry] = await pool.execute(
        'SELECT id FROM whatsapp_cloud_conversations WHERE account_id = ? AND contact_phone = ?',
        [accountId, contactPhone]
      );
      if (existingRetry.length > 0) {
        return existingRetry[0].id;
      }
      throw error;
    }
  }

  /**
   * Send campaign template messages
   * POST /api/whatsapp-cloud/campaigns/send
   */
  static async sendTemplateCampaign(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const {
        accountId,
        templateId,
        audienceType,
        audienceGroups,
        audienceCustomNumbers,
        filters,
        source,
        timezone
      } = req.body || {};
      const resolvedAudienceType = audienceType || req.body?.audienceId || 'all';

      logger.info('📤 Campaign send request received', {
        tenantId,
        accountId,
        templateId,
        audienceType: resolvedAudienceType,
        audienceGroupsCount: audienceGroups?.length || 0,
        audienceCustomNumbersCount: audienceCustomNumbers?.length || 0,
        source,
        userId: req.user?.id
      });

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

      if (!accountId || !templateId) {
        logger.error('❌ Missing required fields', { accountId, templateId });
        return res.status(400).json({
          success: false,
          message: 'Account ID and template ID are required'
        });
      }

      if (source && String(source).toLowerCase() !== 'meta') {
        logger.warn('⚠️ Invalid source for Cloud templates', { source });
        return res.status(400).json({
          success: false,
          message: 'Custom source is not supported for Cloud templates'
        });
      }

      if (resolvedAudienceType === 'groups' && (!Array.isArray(audienceGroups) || audienceGroups.length === 0)) {
        logger.warn('⚠️ No groups selected for group campaign');
        return res.status(400).json({
          success: false,
          message: 'At least one group is required'
        });
      }

      if (resolvedAudienceType === 'custom') {
        const customNumbers = WhatsAppCloudController.parseAudienceNumbers(audienceCustomNumbers);
        if (customNumbers.length === 0) {
          logger.warn('⚠️ No custom numbers provided for custom campaign');
          return res.status(400).json({
            success: false,
            message: 'At least one custom number is required'
          });
        }
      }

      const [accounts] = await pool.execute(
        'SELECT id, access_token, phone_number_id FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

      if (accounts.length === 0) {
        logger.error('❌ Account not found', { accountId, tenantId });
        return res.status(404).json({
          success: false,
          message: 'Account not found'
        });
      }

      const account = accounts[0];
      if (!account.access_token || !account.phone_number_id) {
        logger.error('❌ Account missing credentials', {
          accountId,
          hasAccessToken: !!account.access_token,
          hasPhoneNumberId: !!account.phone_number_id
        });
        return res.status(400).json({
          success: false,
          message: 'Account is missing access token or phone number id'
        });
      }

      const [templateRows] = await pool.execute(
        `SELECT template_id, name, language 
         FROM whatsapp_cloud_templates 
         WHERE tenant_id = ? AND account_id = ? AND template_id = ? 
         LIMIT 1`,
        [tenantId, accountId, templateId]
      );

      if (templateRows.length === 0) {
        logger.error('❌ Template not found', { templateId, accountId, tenantId });
        return res.status(404).json({
          success: false,
          message: 'Template not found'
        });
      }

      const template = templateRows[0];
      const languageCode = template.language || 'en';

      logger.info('✅ Template found', {
        templateId: template.template_id,
        templateName: template.name,
        language: languageCode
      });

      const recipients = await WhatsAppCloudController.getCampaignRecipients({
        tenantId,
        accountId,
        audienceType: resolvedAudienceType,
        audienceGroups,
        audienceCustomNumbers
      });
      
      const total = recipients.length;
      
      logger.info('📊 Recipients loaded', {
        total,
        audienceType: resolvedAudienceType,
        firstRecipient: recipients[0]
      });

      if (total === 0) {
        logger.warn('⚠️ No recipients found for campaign');
        return res.status(400).json({
          success: false,
          message: 'No recipients found. Please check your audience selection.'
        });
      }

      let sent = 0;
      let failed = 0;
      const failures = [];

      for (const recipient of recipients) {
        const contactPhone = recipient.phone;
        if (!contactPhone) {
          failed += 1;
          failures.push({ to: contactPhone, error: 'Missing contact phone' });
          continue;
        }

        const payload = {
          messaging_product: 'whatsapp',
          to: contactPhone,
          type: 'template',
          template: {
            name: template.name,
            language: { code: languageCode }
          }
        };

        try {
          const response = await axios.post(
            `https://graph.facebook.com/v18.0/${account.phone_number_id}/messages`,
            payload,
            {
              headers: {
                'Authorization': `Bearer ${account.access_token}`,
                'Content-Type': 'application/json'
              }
            }
          );

          const messageId = response?.data?.messages?.[0]?.id || `campaign_${Date.now()}`;
          const conversationId = await WhatsAppCloudController.ensureCloudConversation({
            tenantId,
            accountId,
            contactPhone,
            contactName: recipient.name || contactPhone
          });

          await pool.execute(
            `INSERT INTO whatsapp_cloud_messages 
             (conversation_id, message_id, direction, message_type, text_content, status, timestamp, created_at)
             VALUES (?, ?, 'outbound', 'template', ?, 'sent', NOW(), NOW())`,
            [conversationId, messageId, template.name]
          );

          await pool.execute(
            `UPDATE whatsapp_cloud_conversations 
             SET last_message_text = ?, last_message_time = NOW(), last_message_from = 'business', updated_at = NOW()
             WHERE id = ?`,
            [template.name, conversationId]
          );

          sent += 1;
        } catch (sendError) {
          failed += 1;
          failures.push({
            to: contactPhone,
            error: sendError.response?.data?.error?.message || sendError.message
          });
        }
      }

      return res.json({
        success: true,
        data: {
          audienceType: resolvedAudienceType,
          filters: filters || {},
          timezone: timezone || 'UTC',
          total,
          sent,
          failed,
          failures
        }
      });
    } catch (error) {
      logger.error('Error sending template campaign', {
        error: error.message,
        stack: error.stack,
        response: error.response?.data
      });

      return res.status(500).json({
        success: false,
        message: error.response?.data?.error?.message || 'Failed to send campaign'
      });
    }
  }

  static async listTemplateCampaigns(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const { status, accountId } = req.query || {};
      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found in request'
        });
      }
      const params = [tenantId];
      let where = 'tenant_id = ?';
      if (status) {
        where += ' AND status = ?';
        params.push(status);
      }
      if (accountId) {
        where += ' AND account_id = ?';
        params.push(accountId);
      }
      const [rows] = await pool.execute(
        `SELECT id, account_id, template_id, template_name, audience_type, schedule_at, timezone, status, total_count, sent_count, failed_count, created_at
         FROM whatsapp_cloud_campaigns
         WHERE ${where}
         ORDER BY schedule_at ASC`,
        params
      );
      return res.json({
        success: true,
        data: rows
      });
    } catch (error) {
      logger.error('Error listing template campaigns', {
        error: error.message,
        stack: error.stack
      });
      return res.status(500).json({
        success: false,
        message: 'Failed to list campaigns'
      });
    }
  }

  static async createTemplateCampaign(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const {
        accountId,
        templateId,
        scheduleAt,
        timezone,
        audienceType,
        audienceGroups,
        audienceCustomNumbers,
        filters,
        source
      } = req.body || {};
      const resolvedAudienceType = audienceType || 'all';

      if (!tenantId) {
        return res.status(400).json({
          success: false,
          message: 'Tenant ID not found in request'
        });
      }
      if (!accountId || !templateId || !scheduleAt) {
        return res.status(400).json({
          success: false,
          message: 'Account ID, template ID, and schedule time are required'
        });
      }
      const normalizedScheduleAt = WhatsAppCloudController.normalizeScheduleAt(scheduleAt);
      if (!normalizedScheduleAt) {
        return res.status(400).json({
          success: false,
          message: 'Invalid schedule time'
        });
      }
      if (source && String(source).toLowerCase() !== 'meta') {
        return res.status(400).json({
          success: false,
          message: 'Custom source is not supported for Cloud templates'
        });
      }
      if (resolvedAudienceType === 'groups' && (!Array.isArray(audienceGroups) || audienceGroups.length === 0)) {
        return res.status(400).json({
          success: false,
          message: 'At least one group is required'
        });
      }
      if (resolvedAudienceType === 'custom') {
        const customNumbers = WhatsAppCloudController.parseAudienceNumbers(audienceCustomNumbers);
        if (customNumbers.length === 0) {
          return res.status(400).json({
            success: false,
            message: 'At least one custom number is required'
          });
        }
      }

      const [accounts] = await pool.execute(
        'SELECT id FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );
      if (accounts.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Account not found'
        });
      }

      const [templateRows] = await pool.execute(
        `SELECT template_id, name
         FROM whatsapp_cloud_templates
         WHERE tenant_id = ? AND account_id = ? AND template_id = ?
         LIMIT 1`,
        [tenantId, accountId, templateId]
      );
      if (templateRows.length === 0) {
        return res.status(404).json({
          success: false,
          message: 'Template not found'
        });
      }

      const recipients = await WhatsAppCloudController.getCampaignRecipients({
        tenantId,
        accountId,
        audienceType: resolvedAudienceType,
        audienceGroups,
        audienceCustomNumbers
      });
      const total = recipients.length;

      const [result] = await pool.execute(
        `INSERT INTO whatsapp_cloud_campaigns
         (tenant_id, account_id, template_id, template_name, source, audience_type, audience_groups, audience_custom_numbers, filters, schedule_at, timezone, total_count)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          tenantId,
          accountId,
          templateId,
          templateRows[0].name,
          source || 'meta',
          resolvedAudienceType,
          JSON.stringify(Array.isArray(audienceGroups) ? audienceGroups : []),
          JSON.stringify(WhatsAppCloudController.parseAudienceNumbers(audienceCustomNumbers)),
          JSON.stringify(filters || {}),
          normalizedScheduleAt,
          timezone || 'UTC',
          total
        ]
      );

      return res.json({
        success: true,
        data: {
          id: result.insertId,
          total
        }
      });
    } catch (error) {
      logger.error('Error creating template campaign', {
        error: error.message,
        stack: error.stack
      });
      return res.status(500).json({
        success: false,
        message: 'Failed to create campaign'
      });
    }
  }

  static async updateTemplateCampaign(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const { id } = req.params;
      const { scheduleAt, timezone } = req.body || {};
      if (!tenantId || !id || !scheduleAt) {
        return res.status(400).json({
          success: false,
          message: 'Campaign ID and schedule time are required'
        });
      }
      const normalizedScheduleAt = WhatsAppCloudController.normalizeScheduleAt(scheduleAt);
      if (!normalizedScheduleAt) {
        return res.status(400).json({
          success: false,
          message: 'Invalid schedule time'
        });
      }
      const [result] = await pool.execute(
        `UPDATE whatsapp_cloud_campaigns
         SET schedule_at = ?, timezone = ?, updated_at = NOW()
         WHERE id = ? AND tenant_id = ? AND status = 'scheduled'`,
        [normalizedScheduleAt, timezone || 'UTC', id, tenantId]
      );
      if (result.affectedRows === 0) {
        return res.status(404).json({
          success: false,
          message: 'Campaign not found or cannot be updated'
        });
      }
      return res.json({
        success: true
      });
    } catch (error) {
      logger.error('Error updating template campaign', {
        error: error.message,
        stack: error.stack
      });
      return res.status(500).json({
        success: false,
        message: 'Failed to update campaign'
      });
    }
  }

  static async deleteTemplateCampaign(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const { id } = req.params;
      if (!tenantId || !id) {
        return res.status(400).json({
          success: false,
          message: 'Campaign ID is required'
        });
      }
      const [result] = await pool.execute(
        `UPDATE whatsapp_cloud_campaigns
         SET status = 'cancelled', updated_at = NOW()
         WHERE id = ? AND tenant_id = ? AND status = 'scheduled'`,
        [id, tenantId]
      );
      if (result.affectedRows === 0) {
        return res.status(404).json({
          success: false,
          message: 'Campaign not found or cannot be deleted'
        });
      }
      return res.json({
        success: true
      });
    } catch (error) {
      logger.error('Error deleting template campaign', {
        error: error.message,
        stack: error.stack
      });
      return res.status(500).json({
        success: false,
        message: 'Failed to delete campaign'
      });
    }
  }

  /**
   * Send media message
   * POST /api/whatsapp-cloud/send-media
   */
  static async sendMedia(req, res) {
    try {
      const { accountId, to, conversationId } = req.body;
      const tenantId = req.tenantId || req.user.tenantId;

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

      if (!accountId || !to || !req.file) {
        return res.status(400).json({
          success: false,
          message: 'Account ID, recipient, and media file are required'
        });
      }

      // Get account details
      const [accounts] = await pool.execute(
        'SELECT * FROM whatsapp_cloud_accounts WHERE id = ? AND tenant_id = ?',
        [accountId, tenantId]
      );

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

      const account = accounts[0];
      const file = req.file;

      // Determine media type
      let mediaType = 'document';
      if (file.mimetype.startsWith('image/')) mediaType = 'image';
      else if (file.mimetype.startsWith('video/')) mediaType = 'video';
      else if (file.mimetype.startsWith('audio/')) mediaType = 'audio';

      // Upload media to WhatsApp Cloud API
      const FormData = require('form-data');
      const fs = require('fs');
      const formData = new FormData();
      formData.append('messaging_product', 'whatsapp');
      formData.append('file', fs.createReadStream(file.path), {
        filename: file.originalname,
        contentType: file.mimetype
      });

      const uploadResponse = await axios.post(
        `https://graph.facebook.com/v18.0/${account.phone_number_id}/media`,
        formData,
        {
          headers: {
            'Authorization': `Bearer ${account.access_token}`,
            ...formData.getHeaders()
          }
        }
      );

      const mediaId = uploadResponse.data.id;

      // Send message with media
      const messagePayload = {
        messaging_product: 'whatsapp',
        to: to,
        type: mediaType,
        [mediaType]: {
          id: mediaId
        }
      };

      const response = await axios.post(
        `https://graph.facebook.com/v18.0/${account.phone_number_id}/messages`,
        messagePayload,
        {
          headers: {
            'Authorization': `Bearer ${account.access_token}`,
            'Content-Type': 'application/json'
          }
        }
      );

      logger.info('Media message sent via WhatsApp Cloud API', {
        tenantId,
        accountId,
        to,
        mediaType,
        messageId: response.data.messages[0].id
      });

      // Save message to database
      let convId = conversationId;
      
      if (!convId) {
        const [existingConv] = await pool.execute(
          'SELECT id FROM whatsapp_cloud_conversations WHERE account_id = ? AND contact_phone = ?',
          [accountId, to]
        );

        if (existingConv.length > 0) {
          convId = existingConv[0].id;
        } else {
          const [newConv] = await pool.execute(
            `INSERT INTO whatsapp_cloud_conversations 
             (tenant_id, account_id, contact_phone, contact_name, last_message_text, last_message_time, last_message_from, unread_count, status, created_at, updated_at, store_id, department_id)
             VALUES (?, ?, ?, ?, '[Media]', NOW(), 'agent', 0, 'open', NOW(), NOW(), NULL, NULL)`,
            [tenantId, accountId, to, to]
          );
          convId = newConv.insertId;
        }
      }

      // Save message
      await pool.execute(
        `INSERT INTO whatsapp_cloud_messages 
         (conversation_id, message_id, direction, message_type, media_url, filename, status, timestamp, created_at)
         VALUES (?, ?, 'outbound', ?, ?, ?, 'sent', NOW(), NOW())`,
        [convId, response.data.messages[0].id, mediaType, file.path, file.originalname]
      );

      // Update conversation
      await pool.execute(
        `UPDATE whatsapp_cloud_conversations 
         SET last_message_text = '[Media]', last_message_time = NOW(), last_message_from = 'agent', updated_at = NOW()
         WHERE id = ?`,
        [convId]
      );

      // Clean up uploaded file
      fs.unlinkSync(file.path);

      return res.json({
        success: true,
        message: 'Media sent successfully',
        data: {
          messageId: response.data.messages[0].id,
          conversationId: convId
        }
      });
    } catch (error) {
      logger.error('Error sending media', {
        error: error.message,
        stack: error.stack,
        response: error.response?.data
      });

      // Clean up file on error
      if (req.file) {
        const fs = require('fs');
        try {
          fs.unlinkSync(req.file.path);
        } catch (e) {
          logger.error('Error deleting temp file', { error: e.message });
        }
      }

      return res.status(500).json({
        success: false,
        message: error.response?.data?.error?.message || 'Failed to send media'
      });
    }
  }

  /**
   * Get all conversations for tenant (admin view)
   * GET /api/whatsapp-cloud/admin/conversations
   * 
   * Supports both WhatsApp Cloud API and Evolution API conversations
   * - If accountId starts with 'evolution_', fetches from conversations table
   * - Otherwise, fetches from whatsapp_cloud_conversations table
   */
  static async getAdminConversations(req, res) {
    try {
      const tenantId = req.tenantId || req.user.tenantId;
      const { accountId } = req.query;

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

      // 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', { 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);
        const userId = req.user?.id;
        const userRole = req.user?.role;
        
        logger.info('🔍 Fetching Evolution API conversations', { 
          tenantId, 
          instanceId, 
          accountId,
          userId,
          userRole,
          userName: req.user?.name,
          userStore: req.user?.store || req.user?.store_id,
          userDepartment: req.user?.department || req.user?.department_id
        });
        
        // Build WHERE clause based on user role and assignments
        let whereClause = 'WHERE c.tenant_id = ?';
        const queryParams = [accountId, instanceId, instanceId, tenantId];
        
        // If user is not admin, filter conversations
        if (userRole !== 'admin' && userRole !== 'superadmin' && userId) {
          logger.info('🔒 Applying user-specific filters for Evolution conversations', {
            userId,
            userRole,
            userStore: req.user?.store || req.user?.store_id,
            userDepartment: req.user?.department || req.user?.department_id
          });
          
          // Get user's store and department IDs
          let userStoreId = req.user?.store_id;
          let userDepartmentId = req.user?.department_id;
          const userStoreName = req.user?.store;
          const userDepartmentName = req.user?.department;
          
          // Resolve store ID from name if needed
          if (!userStoreId && userStoreName) {
            const [storeRows] = await pool.execute(
              'SELECT id FROM stores WHERE tenant_id = ? AND name = ? LIMIT 1',
              [tenantId, userStoreName]
            );
            userStoreId = storeRows[0]?.id;
          }
          
          // Resolve department ID from name if needed
          if (!userDepartmentId && userDepartmentName) {
            const [deptRows] = await pool.execute(
              'SELECT id FROM departments WHERE tenant_id = ? AND name = ? LIMIT 1',
              [tenantId, userDepartmentName]
            );
            userDepartmentId = deptRows[0]?.id;
          }
          
          logger.info('✅ Resolved user store/department IDs', {
            userId,
            userStoreId,
            userStoreName,
            userDepartmentId,
            userDepartmentName
          });
          
          // Build filter conditions
          const conditions = [];
          
          // Condition 1: Conversations assigned to this user
          conditions.push('c.assigned_user_id = ?');
          queryParams.push(userId);
          
          // Condition 2: Conversations transferred to user's store
          if (userStoreId || userStoreName) {
            const storeValues = [];
            if (userStoreId) storeValues.push(String(userStoreId));
            if (userStoreName && !storeValues.includes(String(userStoreName))) {
              storeValues.push(String(userStoreName));
            }
            conditions.push(`c.transferred_to_store IN (${storeValues.map(() => '?').join(', ')})`);
            queryParams.push(...storeValues);
          }
          
          // Condition 3: Conversations transferred to user's department
          if (userDepartmentId || userDepartmentName) {
            const deptValues = [];
            if (userDepartmentId) deptValues.push(String(userDepartmentId));
            if (userDepartmentName && !deptValues.includes(String(userDepartmentName))) {
              deptValues.push(String(userDepartmentName));
            }
            conditions.push(`c.transferred_to_department IN (${deptValues.map(() => '?').join(', ')})`);
            queryParams.push(...deptValues);
          }
          
          // Apply the OR conditions
          if (conditions.length > 0) {
            whereClause += ` AND (${conditions.join(' OR ')})`;
            logger.info('📝 Applied Evolution conversation filters', {
              userId,
              conditions: conditions.length,
              whereClause
            });
          } else {
            // If user has no store/department, only show assigned conversations
            whereClause += ' AND c.assigned_user_id = ?';
            queryParams.push(userId);
            logger.info('📝 User has no store/department - showing only assigned conversations', {
              userId
            });
          }
        } else {
          logger.info('👑 Admin user - showing all Evolution conversations', {
            userId,
            userRole
          });
        }
        
        // 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`;
        
        logger.info('🚀 Executing Evolution conversations query', {
          tenantId,
          instanceId,
          userId,
          queryParamsCount: queryParams.length,
          query: query.replace(/\s+/g, ' ').substring(0, 500)
        });
        
        const [conversations] = await pool.execute(query, queryParams);

        logger.info('✅ Evolution API conversations retrieved', { 
          tenantId, 
          instanceId,
          accountId,
          userId,
          count: conversations.length,
          conversationIds: conversations.map(c => c.id)
        });

        // 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
        });
      }

      // Original WhatsApp Cloud API logic
      const query = accountId 
        ? `SELECT c.*, 
                  a.account_name, 
                  a.phone_number as account_phone,
                  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
           FROM whatsapp_cloud_conversations c
           JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
           LEFT JOIN users u ON c.claimed_by_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
           WHERE c.tenant_id = ? AND c.account_id = ?
           ORDER BY c.last_message_time DESC`
        : `SELECT c.*, 
                  a.account_name, 
                  a.phone_number as account_phone,
                  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
           FROM whatsapp_cloud_conversations c
           JOIN whatsapp_cloud_accounts a ON c.account_id = a.id
           LEFT JOIN users u ON c.claimed_by_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
           WHERE c.tenant_id = ?
           ORDER BY c.last_message_time DESC`;

      const params = accountId ? [tenantId, accountId] : [tenantId];
      const [conversations] = await pool.execute(query, params);

      logger.info('WhatsApp Cloud conversations retrieved', { 
        tenantId, 
        accountId,
        count: conversations.length 
      });

      return res.json({
        success: true,
        data: conversations
      });
    } catch (error) {
      logger.error('Error getting admin conversations', { 
        error: error.message,
        stack: error.stack
      });
      
      return res.status(500).json({
        success: false,
        message: 'Failed to load conversations'
      });
    }
  }

  /**
   * Update conversation tags (admin mode)
   * PUT /api/whatsapp-cloud/conversations/:id/tags
   */
  static async updateTags(req, res) {
    try {
      const conversationId = req.params.id;
      const { tags, action = 'set' } = req.body;
      const tenantId = req.tenantId || req.user.tenantId;

      logger.info('🏷️ Update tags request', {
        conversationId,
        tags,
        action,
        tenantId
      });

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

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

      if (evolutionCheck.length > 0) {
        logger.info('✅ Evolution conversation detected, updating tags in conversations table', {
          conversationId,
          tenantId
        });

        // Get current tags
        const [conversations] = await pool.execute(
          'SELECT id, tags FROM conversations WHERE id = ? AND tenant_id = ?',
          [conversationId, tenantId]
        );

        const conversation = conversations[0];
        let currentTags = [];
        
        try {
          if (conversation.tags) {
            // Handle both JSON string and parsed object
            currentTags = typeof conversation.tags === 'string' 
              ? JSON.parse(conversation.tags) 
              : conversation.tags;
            
            // Ensure it's an array
            if (!Array.isArray(currentTags)) {
              currentTags = [];
            }
          }
        } catch (e) {
          logger.warn('Error parsing tags:', e.message);
          currentTags = [];
        }

        let newTags = [];

        if (action === 'add') {
          newTags = [...new Set([...currentTags, ...tags.map(t => t.toLowerCase())])];
        } else if (action === 'remove') {
          newTags = currentTags.filter(t => !tags.map(tag => tag.toLowerCase()).includes(t));
        } else {
          newTags = tags.map(t => t.toLowerCase());
        }

        // Limit to 3 tags
        newTags = newTags.slice(0, 3);

        await pool.execute(
          'UPDATE conversations SET tags = ? WHERE id = ?',
          [JSON.stringify(newTags), conversationId]
        );

        logger.info('✅ Evolution conversation tags updated', {
          conversationId,
          tenantId,
          oldTags: currentTags,
          newTags
        });

        return res.json({
          success: true,
          message: 'Tags updated successfully',
          tags: newTags
        });
      }

      // Validate conversation belongs to tenant (WhatsApp Cloud)
      const [conversations] = await pool.execute(
        'SELECT id, tags 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];
      let currentTags = [];
      
      try {
        currentTags = conversation.tags ? JSON.parse(conversation.tags) : [];
      } catch (e) {
        currentTags = [];
      }

      let newTags = [];

      if (action === 'add') {
        newTags = [...new Set([...currentTags, ...tags.map(t => t.toLowerCase())])];
      } else if (action === 'remove') {
        newTags = currentTags.filter(t => !tags.map(tag => tag.toLowerCase()).includes(t));
      } else {
        newTags = tags.map(t => t.toLowerCase());
      }

      // Limit to 3 tags
      newTags = newTags.slice(0, 3);

      await pool.execute(
        'UPDATE whatsapp_cloud_conversations SET tags = ? WHERE id = ?',
        [JSON.stringify(newTags), conversationId]
      );

      logger.info('Conversation tags updated', {
        conversationId,
        tenantId,
        action,
        tags: newTags
      });

      return res.json({
        success: true,
        data: { tags: newTags }
      });
    } catch (error) {
      logger.error('Error updating conversation tags', {
        error: error.message,
        stack: error.stack,
        conversationId: req.params.id,
        tenantId: req.tenantId || req.user?.tenantId,
        sqlMessage: error.sqlMessage,
        code: error.code
      });

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

  /**
   * Get users for transfer (admin mode)
   * GET /api/whatsapp-cloud/users
   */
  static async getUsers(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 [users] = await pool.execute(
        `SELECT u.id, u.name, u.username, u.email, u.role,
                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.role IN ('user', 'admin')
         ORDER BY u.name ASC`,
        [tenantId]
      );

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

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

  /**
   * Get stores for transfer (admin mode)
   * GET /api/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 FROM stores WHERE tenant_id = ? 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 (admin mode)
   * GET /api/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 FROM departments WHERE tenant_id = ? 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'
      });
    }
  }
}

module.exports = WhatsAppCloudController;
