WHMCS WhatsApp Notifications Complete Guide

// Step 1: Create Twilio account and get credentials
// Visit: https://www.twilio.com/console

// Step 2: Enable WhatsApp for your Twilio account
// Navigate to: Messaging > WhatsApp > Senders

// Step 3: Set up WhatsApp Business Profile
// Add business information and get approved

// Your credentials will look like:
$twilioSid = 'AC1234567890abcdef1234567890abcdef';
$twilioToken = 'your_auth_token_here';
$twilioWhatsAppNumber = 'whatsapp:+14155238886'; // Twilio sandbox or approved number

-- WhatsApp notification log table
CREATE TABLE `whatsapp_notifications` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `client_id` int(11) NOT NULL,
    `invoice_id` int(11) DEFAULT NULL,
    `order_id` int(11) DEFAULT NULL,
    `service_id` int(11) DEFAULT NULL,
    `notification_type` varchar(50) NOT NULL,
    `phone_number` varchar(20) NOT NULL,
    `message_content` text NOT NULL,
    `status` enum('sent','failed','pending') DEFAULT 'pending',
    `error_message` text DEFAULT NULL,
    `sent_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `client_id` (`client_id`),
    KEY `notification_type` (`notification_type`),
    KEY `status` (`status`),
    KEY `sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Customer WhatsApp preferences
CREATE TABLE `whatsapp_preferences` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `client_id` int(11) NOT NULL UNIQUE,
    `phone_number` varchar(20) DEFAULT NULL,
    `notifications_enabled` tinyint(1) DEFAULT 1,
    `order_notifications` tinyint(1) DEFAULT 1,
    `invoice_notifications` tinyint(1) DEFAULT 1,
    `payment_reminders` tinyint(1) DEFAULT 1,
    `service_updates` tinyint(1) DEFAULT 1,
    `support_notifications` tinyint(1) DEFAULT 1,
    `language_preference` varchar(5) DEFAULT 'en',
    `timezone` varchar(50) DEFAULT 'UTC',
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- WhatsApp templates for reusability
CREATE TABLE `whatsapp_templates` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `template_name` varchar(100) NOT NULL UNIQUE,
    `template_type` varchar(50) NOT NULL,
    `language` varchar(5) DEFAULT 'en',
    `subject` varchar(200) DEFAULT NULL,
    `message_body` text NOT NULL,
    `variables` text DEFAULT NULL, -- JSON format
    `is_active` tinyint(1) DEFAULT 1,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `template_type` (`template_type`),
    KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
// File: includes/whatsapp/WhatsAppIntegration.php

class WhatsAppIntegration
{
    private $config;
    private $db;
    
    public function __construct()
    {
        $this->config = [
            'provider' => 'twilio', // or 'direct', 'messagebird', 'wati'
            'twilio_sid' => 'your_twilio_sid',
            'twilio_token' => 'your_twilio_token',
            'twilio_whatsapp_number' => 'whatsapp:+14155238886',
            'webhook_url' => 'https://yourdomain.com/whatsapp/webhook.php',
            'debug_mode' => false
        ];
        
        $this->db = \WHMCS\Database\Capsule::connection();
    }
    
    /**
     * Send WhatsApp message
     */
    public function sendMessage($phoneNumber, $message, $clientId = null, $context = [])
    {
        try {
            // Format phone number
            $formattedPhone = $this->formatPhoneNumber($phoneNumber);
            if (!$formattedPhone) {
                throw new Exception('Invalid phone number format');
            }
            
            // Check if customer has opted in
            if ($clientId && !$this->hasOptedIn($clientId)) {
                $this->logNotification($clientId, $context, 'skipped', 'Customer has not opted in');
                return false;
            }
            
            // Send based on provider
            $result = $this->sendViaProvider($formattedPhone, $message);
            
            // Log the notification
            $this->logNotification(
                $clientId,
                $context,
                $result['success'] ? 'sent' : 'failed',
                $result['error'] ?? null,
                $message
            );
            
            return $result['success'];
            
        } catch (Exception $e) {
            $this->logNotification($clientId, $context, 'failed', $e->getMessage(), $message);
            return false;
        }
    }
    
    /**
     * Send via Twilio
     */
    private function sendViaTwilio($phoneNumber, $message)
    {
        require_once 'vendor/autoload.php';
        
        $client = new \Twilio\Rest\Client(
            $this->config['twilio_sid'],
            $this->config['twilio_token']
        );
        
        try {
            $messageInstance = $client->messages->create(
                "whatsapp:$phoneNumber",
                [
                    'from' => $this->config['twilio_whatsapp_number'],
                    'body' => $message
                ]
            );
            
            return [
                'success' => true,
                'message_id' => $messageInstance->sid,
                'status' => $messageInstance->status
            ];
            
        } catch (\Twilio\Exceptions\RestException $e) {
            return [
                'success' => false,
                'error' => $e->getMessage(),
                'error_code' => $e->getCode()
            ];
        }
    }
    
    /**
     * Format phone number to international format
     */
    private function formatPhoneNumber($phone)
    {
        // Remove all non-numeric characters
        $phone = preg_replace('/[^0-9]/', '', $phone);
        
        // Handle different country codes
        if (strlen($phone) == 10) {
            // Assume US/Canada if 10 digits
            $phone = '1' . $phone;
        } elseif (strlen($phone) == 11 && substr($phone, 0, 1) === '1') {
            // Already has US/Canada code
            // Keep as is
        } elseif (strlen($phone) > 11) {
            // International number, keep as is
            // Keep as is
        } else {
            return false; // Invalid format
        }
        
        return '+' . $phone;
    }
    
    /**
     * Check if customer has opted in for WhatsApp notifications
     */
    private function hasOptedIn($clientId)
    {
        $preference = $this->db->table('whatsapp_preferences')
            ->where('client_id', $clientId)
            ->where('notifications_enabled', 1)
            ->first();
            
        return $preference !== null;
    }
    
    /**
     * Log notification attempt
     */
    private function logNotification($clientId, $context, $status, $error = null, $message = '')
    {
        $this->db->table('whatsapp_notifications')->insert([
            'client_id' => $clientId,
            'invoice_id' => $context['invoice_id'] ?? null,
            'order_id' => $context['order_id'] ?? null,
            'service_id' => $context['service_id'] ?? null,
            'notification_type' => $context['type'] ?? 'general',
            'phone_number' => $context['phone'] ?? '',
            'message_content' => $message,
            'status' => $status,
            'error_message' => $error,
            'sent_at' => $status === 'sent' ? now() : null
        ]);
    }
    
    /**
     * Get message template
     */
    public function getTemplate($templateName, $language = 'en')
    {
        $template = $this->db->table('whatsapp_templates')
            ->where('template_name', $templateName)
            ->where('language', $language)
            ->where('is_active', 1)
            ->first();
            
        return $template;
    }
    
    /**
     * Process template variables
     */
    public function processTemplate($templateBody, $variables)
    {
        foreach ($variables as $key => $value) {
            $templateBody = str_replace('{' . $key . '}', $value, $templateBody);
        }
        
        return $templateBody;
    }
}
?>

<?php
// File: includes/hooks/whatsapp_order_notifications.php

require_once dirname(__FILE__) . '/../whatsapp/WhatsAppIntegration.php';

add_hook('AcceptOrder', 1, function($vars) {
    $orderId = $vars['orderid'];
    $whatsapp = new WhatsAppIntegration();
    
    try {
        // Get order details
        $order = localAPI('GetOrders', [
            'id' => $orderId,
            'status' => 'Active'
        ]);
        
        if (!empty($order['orders']['order'][0])) {
            $orderData = $order['orders']['order'][0];
            
            // Get client details
            $client = localAPI('GetClientsDetails', [
                'clientid' => $orderData['userid']
            ]);
            
            // Check if client has phone number
            if (!empty($client['phonenumber'])) {
                
                // Create order confirmation message
                $message = createOrderConfirmationMessage($orderData, $client);
                
                // Send WhatsApp notification
                $context = [
                    'type' => 'order_confirmation',
                    'order_id' => $orderId,
                    'phone' => $client['phonenumber']
                ];
                
                $whatsapp->sendMessage(
                    $client['phonenumber'],
                    $message,
                    $client['userid'],
                    $context
                );
                
                logActivity("WhatsApp order confirmation sent for Order #$orderId");
            }
        }
        
    } catch (Exception $e) {
        logActivity("WhatsApp order confirmation error: " . $e->getMessage());
    }
});

// Also hook into service creation for hosting orders
add_hook('AfterModuleCreate', 1, function($vars) {
    if ($vars['result'] == 'success') {
        $whatsapp = new WhatsAppIntegration();
        
        try {
            // Get service details
            $service = localAPI('GetClientsProducts', [
                'serviceid' => $vars['serviceid']
            ]);
            
            if (!empty($service['products']['product'][0])) {
                $serviceData = $service['products']['product'][0];
                
                // Get client details
                $client = localAPI('GetClientsDetails', [
                    'clientid' => $serviceData['clientid']
                ]);
                
                if (!empty($client['phonenumber'])) {
                    $message = createServiceActivationMessage($serviceData, $client, $vars);
                    
                    $context = [
                        'type' => 'service_activation',
                        'service_id' => $vars['serviceid'],
                        'phone' => $client['phonenumber']
                    ];
                    
                    $whatsapp->sendMessage(
                        $client['phonenumber'],
                        $message,
                        $client['userid'],
                        $context
                    );
                    
                    logActivity("WhatsApp service activation sent for Service #" . $vars['serviceid']);
                }
            }
            
        } catch (Exception $e) {
            logActivity("WhatsApp service activation error: " . $e->getMessage());
        }
    }
});

function createOrderConfirmationMessage($order, $client) {
    $companyName = \WHMCS\Config\Setting::getValue('CompanyName');
    
    $message = "*Order Confirmed - $companyName*\n\n";
    $message .= "Hi " . $client['firstname'] . "!\n\n";
    $message .= "Great news! Your order has been confirmed and is being processed.\n\n";
    $message .= "*Order Details:*\n";
    $message .= "Order #: " . $order['id'] . "\n";
    $message .= "Date: " . date('M j, Y') . "\n";
    $message .= "Amount: " . $client['currency'] . " " . $order['amount'] . "\n\n";
    
    // Add product details
    if (!empty($order['lineitems']['lineitem'])) {
        $message .= "*Products/Services:*\n";
        foreach ($order['lineitems']['lineitem'] as $item) {
            $message .= "• " . $item['product'];
            if (!empty($item['domain'])) {
                $message .= " (" . $item['domain'] . ")";
            }
            $message .= "\n";
        }
        $message .= "\n";
    }
    
    $message .= "*What's Next?*\n";
    $message .= "• Setup will begin within 24 hours\n";
    $message .= "• You'll receive login details via email\n";
    $message .= "• Our support team is here to help\n\n";
    $message .= "Questions? Reply to this message!\n\n";
    $message .= "Thank you for choosing us!";
    
    return $message;
}

function createServiceActivationMessage($service, $client, $moduleVars) {
    $companyName = \WHMCS\Config\Setting::getValue('CompanyName');
    
    $message = "*Service Activated - $companyName*\n\n";
    $message .= "Hi " . $client['firstname'] . "!\n\n";
    $message .= "Your " . $service['productname'] . " is now ready!\n\n";
    
    $message .= "*Service Details:*\n";
    $message .= "Product: " . $service['productname'] . "\n";
    if (!empty($service['domain'])) {
        $message .= "Domain: " . $service['domain'] . "\n";
    }
    $message .= "Status: Active\n\n";
    
    // Add login details if available
    if (!empty($moduleVars['username'])) {
        $message .= "*Login Information:*\n";
        $message .= "Username: " . $moduleVars['username'] . "\n";
        $message .= "Control Panel: " . ($moduleVars['serverdata']['hostname'] ?? 'Check your email') . "\n\n";
        $message .= "Password and detailed instructions have been sent to your email.\n\n";
    }
    
    $message .= "*Need Help?*\n";
    $message .= "• Check your email for detailed setup guide\n";
    $message .= "• Reply to this message for support\n";
    $message .= "• Visit our knowledge base\n\n";
    $message .= "Welcome aboard!";
    
    return $message;
}
?>

<?php
// File: includes/hooks/whatsapp_invoice_notifications.php

require_once dirname(__FILE__) . '/../whatsapp/WhatsAppIntegration.php';

add_hook('InvoiceCreated', 1, function($vars) {
    $invoiceId = $vars['invoiceid'];
    $whatsapp = new WhatsAppIntegration();
    
    try {
        // Get invoice details
        $invoice = localAPI('GetInvoice', [
            'invoiceid' => $invoiceId
        ]);
        
        // Get client details
        $client = localAPI('GetClientsDetails', [
            'clientid' => $invoice['userid']
        ]);
        
        // Check if client has phone and wants invoice notifications
        if (!empty($client['phonenumber']) && shouldSendInvoiceNotification($client['userid'])) {
            
            $message = createInvoiceNotificationMessage($invoice, $client);
            
            $context = [
                'type' => 'invoice_created',
                'invoice_id' => $invoiceId,
                'phone' => $client['phonenumber']
            ];
            
            $whatsapp->sendMessage(
                $client['phonenumber'],
                $message,
                $client['userid'],
                $context
            );
            
            logActivity("WhatsApp invoice notification sent for Invoice #" . $invoice['invoicenum']);
        }
        
    } catch (Exception $e) {
        logActivity("WhatsApp invoice notification error: " . $e->getMessage());
    }
});

add_hook('InvoicePaymentReminder', 1, function($vars) {
    $invoiceId = $vars['invoiceid'];
    $whatsapp = new WhatsAppIntegration();
    
    try {
        // Get invoice details
        $invoice = localAPI('GetInvoice', [
            'invoiceid' => $invoiceId
        ]);
        
        // Get client details
        $client = localAPI('GetClientsDetails', [
            'clientid' => $invoice['userid']
        ]);
        
        if (!empty($client['phonenumber']) && shouldSendPaymentReminder($client['userid'])) {
            
            $reminderType = determineReminderType($invoice);
            $message = createPaymentReminderMessage($invoice, $client, $reminderType);
            
            $context = [
                'type' => 'payment_reminder',
                'invoice_id' => $invoiceId,
                'phone' => $client['phonenumber'],
                'reminder_type' => $reminderType
            ];
            
            $whatsapp->sendMessage(
                $client['phonenumber'],
                $message,
                $client['userid'],
                $context
            );
            
            logActivity("WhatsApp payment reminder ($reminderType) sent for Invoice #" . $invoice['invoicenum']);
        }
        
    } catch (Exception $e) {
        logActivity("WhatsApp payment reminder error: " . $e->getMessage());
    }
});

function createInvoiceNotificationMessage($invoice, $client) {
    $companyName = \WHMCS\Config\Setting::getValue('CompanyName');
    $systemUrl = \WHMCS\Config\Setting::getValue('SystemURL');
    
    $message = "*New Invoice - $companyName*\n\n";
    $message .= "Hi " . $client['firstname'] . ",\n\n";
    $message .= "Your invoice is ready for payment.\n\n";
    $message .= "*Invoice Details:*\n";
    $message .= "Invoice #: " . $invoice['invoicenum'] . "\n";
    $message .= "Amount: " . $invoice['currencyprefix'] . $invoice['total'] . " " . $invoice['currencysuffix'] . "\n";
    $message .= "Due Date: " . date('M j, Y', strtotime($invoice['duedate'])) . "\n\n";
    
    // Add line items
    if (!empty($invoice['items']['item'])) {
        $message .= "*Services:*\n";
        foreach ($invoice['items']['item'] as $item) {
            $message .= "• " . $item['description'] . "\n";
        }
        $message .= "\n";
    }
    
    $message .= "*Pay Now:*\n";
    $paymentUrl = $systemUrl . '/viewinvoice.php?id=' . $invoice['invoiceid'];
    $message .= $paymentUrl . "\n\n";
    
    $message .= "*Payment Options:*\n";
    $message .= "• Credit/Debit Card\n";
    $message .= "• PayPal\n";
    $message .= "• Bank Transfer\n\n";
    
    $message .= "Questions? Reply to this message!";
    
    return $message;
}

function determineReminderType($invoice) {
    $dueDate = strtotime($invoice['duedate']);
    $today = strtotime(date('Y-m-d'));
    $daysDiff = ($today - $dueDate) / 86400;
    
    if ($daysDiff <= -3) return 'pre_reminder';
    if ($daysDiff >= -3 && $daysDiff <= 0) return 'due_soon';
    if ($daysDiff > 0 && $daysDiff <= 7) return 'overdue_week';
    if ($daysDiff > 7) return 'overdue_final';
    
    return 'standard';
}

function createPaymentReminderMessage($invoice, $client, $reminderType) {
    $companyName = \WHMCS\Config\Setting::getValue('CompanyName');
    $systemUrl = \WHMCS\Config\Setting::getValue('SystemURL');
    $paymentUrl = $systemUrl . '/viewinvoice.php?id=' . $invoice['invoiceid'];
    
    $daysOverdue = max(0, floor((strtotime(date('Y-m-d')) - strtotime($invoice['duedate'])) / 86400));
    
    switch ($reminderType) {
        case 'pre_reminder':
            $message = "*Upcoming Payment - $companyName*\n\n";
            $message .= "Hi " . $client['firstname'] . ",\n\n";
            $message .= "Friendly reminder - your invoice is due soon.\n\n";
            $urgency = "Due in 3 days";
            break;
            
        case 'due_soon':
            $message = "*Payment Due Soon - $companyName*\n\n";
            $message .= "Hi " . $client['firstname'] . ",\n\n";
            $message .= "Your invoice payment is due within the next few days.\n\n";
            $urgency = "Due: " . date('M j, Y', strtotime($invoice['duedate']));
            break;
            
        case 'overdue_week':
            $message = "*Payment Overdue - $companyName*\n\n";
            $message .= "Hi " . $client['firstname'] . ",\n\n";
            $message .= "Your invoice payment is now $daysOverdue day(s) overdue.\n\n";
            $urgency = "Action Required";
            break;
            
        case 'overdue_final':
            $message = "*FINAL NOTICE - $companyName*\n\n";
            $message .= "Hi " . $client['firstname'] . ",\n\n";
            $message .= "This is our final payment reminder.\n\n";
            $urgency = "Immediate Action Required";
            break;
            
        default:
            $message = "*Payment Reminder - $companyName*\n\n";
            $message .= "Hi " . $client['firstname'] . ",\n\n";
            $urgency = "Payment Due";
    }
    
    $message .= "*Invoice Details:*\n";
    $message .= "Invoice #: " . $invoice['invoicenum'] . "\n";
    $message .= "Amount: " . $invoice['currencyprefix'] . $invoice['total'] . " " . $invoice['currencysuffix'] . "\n";
    $message .= "Status: $urgency\n\n";
    
    if ($reminderType === 'overdue_final') {
        $message .= "*Important Notice:*\n";
        $message .= "Services may be suspended without immediate payment.\n\n";
    }
    
    $message .= "*Pay Now:* $paymentUrl\n\n";
    
    if ($daysOverdue > 3) {
        $message .= "*Need Help?*\n";
        $message .= "If you're experiencing difficulties, please reply to discuss payment options.\n\n";
    }
    
    $message .= "Thank you for your prompt attention.";
    
    return $message;
}

function shouldSendInvoiceNotification($clientId) {
    // Check client preferences
    $pref = \WHMCS\Database\Capsule::table('whatsapp_preferences')
        ->where('client_id', $clientId)
        ->where('invoice_notifications', 1)
        ->first();
        
    return $pref !== null;
}

function shouldSendPaymentReminder($clientId) {
    // Check client preferences
    $pref = \WHMCS\Database\Capsule::table('whatsapp_preferences')
        ->where('client_id', $clientId)
        ->where('payment_reminders', 1)
        ->first();
        
    return $pref !== null;
}
?>

<?php
// File: includes/whatsapp/TemplateManager.php

class WhatsAppTemplateManager
{
    private $db;
    
    public function __construct()
    {
        $this->db = \WHMCS\Database\Capsule::connection();
    }
    
    /**
     * Get and process template
     */
    public function getProcessedTemplate($templateName, $variables = [], $language = 'en')
    {
        $template = $this->getTemplate($templateName, $language);
        
        if (!$template) {
            throw new Exception("Template '$templateName' not found for language '$language'");
        }
        
        return $this->processVariables($template->message_body, $variables);
    }
    
    /**
     * Get template from database
     */
    private function getTemplate($templateName, $language)
    {
        return $this->db->table('whatsapp_templates')
            ->where('template_name', $templateName)
            ->where('language', $language)
            ->where('is_active', 1)
            ->first();
    }
    
    /**
     * Process template variables
     */
    private function processVariables($templateBody, $variables)
    {
        foreach ($variables as $key => $value) {
            $templateBody = str_replace('{{' . $key . '}}', $value, $templateBody);
        }
        
        // Clean up any remaining variables
        $templateBody = preg_replace('/\{\{[^}]+\}\}/', '', $templateBody);
        
        return trim($templateBody);
    }
    
    /**
     * Create new template
     */
    public function createTemplate($templateName, $templateType, $messageBody, $language = 'en', $variables = [])
    {
        $data = [
            'template_name' => $templateName,
            'template_type' => $templateType,
            'language' => $language,
            'message_body' => $messageBody,
            'variables' => json_encode($variables),
            'is_active' => 1
        ];
        
        return $this->db->table('whatsapp_templates')->insert($data);
    }
    
    /**
     * Update existing template
     */
    public function updateTemplate($templateId, $updates)
    {
        $updates['updated_at'] = now();
        return $this->db->table('whatsapp_templates')
            ->where('id', $templateId)
            ->update($updates);
    }
    
    /**
     * Get all templates by type
     */
    public function getTemplatesByType($templateType, $language = 'en')
    {
        return $this->db->table('whatsapp_templates')
            ->where('template_type', $templateType)
            ->where('language', $language)
            ->where('is_active', 1)
            ->get();
    }
    
    /**
     * Validate template variables
     */
    public function validateTemplate($templateBody, $requiredVariables)
    {
        $missing = [];
        
        foreach ($requiredVariables as $variable) {
            if (strpos($templateBody, '{{' . $variable . '}}') === false) {
                $missing[] = $variable;
            }
        }
        
        return $missing;
    }
}

// Initialize default templates
function initializeDefaultTemplates()
{
    $templateManager = new WhatsAppTemplateManager();
    
    // Order Confirmation Template
    $templateManager->createTemplate(
        'order_confirmation',
        'order',
        "*Order Confirmed - {{company_name}}*\n\nHi {{customer_name}}!\n\nGreat news! Your order has been confirmed.\n\n*Order Details:*\nOrder #: {{order_id}}\nAmount: {{currency}} {{amount}}\nDate: {{order_date}}\n\n*What's Next?*\n• Setup will begin within 24 hours\n• You'll receive login details via email\n• Our support team is here to help\n\nQuestions? Reply to this message!\n\nThank you for choosing us!",
        'en',
        ['company_name', 'customer_name', 'order_id', 'currency', 'amount', 'order_date']
    );
    
    // Invoice Created Template
    $templateManager->createTemplate(
        'invoice_created',
        'invoice',
        "*New Invoice - {{company_name}}*\n\nHi {{customer_name}},\n\nYour invoice is ready for payment.\n\n*Invoice Details:*\nInvoice #: {{invoice_number}}\nAmount: {{currency}} {{amount}}\nDue Date: {{due_date}}\n\n*Pay Now:*\n{{payment_url}}\n\n*Payment Options:*\n• Credit/Debit Card\n• PayPal\n• Bank Transfer\n\nQuestions? Reply to this message!",
        'en',
        ['company_name', 'customer_name', 'invoice_number', 'currency', 'amount', 'due_date', 'payment_url']
    );
    
    // Payment Reminder Template
    $templateManager->createTemplate(
        'payment_reminder',
        'reminder',
        "*Payment Reminder - {{company_name}}*\n\nHi {{customer_name}},\n\n{{reminder_message}}\n\n*Invoice Details:*\nInvoice #: {{invoice_number}}\nAmount: {{currency}} {{amount}}\nStatus: {{status}}\n\n*Pay Now:*\n{{payment_url}}\n\nNeed help? Reply to this message!",
        'en',
        ['company_name', 'customer_name', 'reminder_message', 'invoice_number', 'currency', 'amount', 'status', 'payment_url']
    );
    
    // Service Activation Template
    $templateManager->createTemplate(
        'service_activation',
        'service',
        "*Service Activated - {{company_name}}*\n\nHi {{customer_name}}!\n\nYour {{product_name}} is now ready!\n\n*Service Details:*\nProduct: {{product_name}}\n{{domain_info}}\nStatus: Active\n\n*Login Information:*\n{{login_details}}\n\n*Need Help?*\n• Check your email for setup guide\n• Reply to this message for support\n\nWelcome aboard!",
        'en',
        ['company_name', 'customer_name', 'product_name', 'domain_info', 'login_details']
    );
    
    // Support Ticket Template
    $templateManager->createTemplate(
        'support_update',
        'support',
        "*Support Update - {{company_name}}*\n\nHi {{customer_name}},\n\nYour support ticket has been updated.\n\n*Ticket Details:*\nTicket #: {{ticket_id}}\nSubject: {{subject}}\nStatus: {{status}}\n\n*Latest Response:*\n{{response}}\n\n*View Ticket:*\n{{ticket_url}}\n\nNeed more help? Reply here!",
        'en',
        ['company_name', 'customer_name', 'ticket_id', 'subject', 'status', 'response', 'ticket_url']
    );
}

// Run initialization (only run once)
// initializeDefaultTemplates();
?>

<?php
// Updated notification functions using templates

function createTemplatedOrderConfirmation($order, $client) {
    $templateManager = new WhatsAppTemplateManager();
    
    $variables = [
        'company_name' => \WHMCS\Config\Setting::getValue('CompanyName'),
        'customer_name' => $client['firstname'],
        'order_id' => $order['id'],
        'currency' => $client['currency'],
        'amount' => $order['amount'],
        'order_date' => date('M j, Y')
    ];
    
    return $templateManager->getProcessedTemplate('order_confirmation', $variables, $client['language'] ?? 'en');
}

function createTemplatedInvoiceNotification($invoice, $client) {
    $templateManager = new WhatsAppTemplateManager();
    $systemUrl = \WHMCS\Config\Setting::getValue('SystemURL');
    
    $variables = [
        'company_name' => \WHMCS\Config\Setting::getValue('CompanyName'),
        'customer_name' => $client['firstname'],
        'invoice_number' => $invoice['invoicenum'],
        'currency' => $invoice['currencyprefix'],
        'amount' => $invoice['total'] . ' ' . $invoice['currencysuffix'],
        'due_date' => date('M j, Y', strtotime($invoice['duedate'])),
        'payment_url' => $systemUrl . '/viewinvoice.php?id=' . $invoice['invoiceid']
    ];
    
    return $templateManager->getProcessedTemplate('invoice_created', $variables, $client['language'] ?? 'en');
}

function createTemplatedPaymentReminder($invoice, $client, $reminderType) {
    $templateManager = new WhatsAppTemplateManager();
    $systemUrl = \WHMCS\Config\Setting::getValue('SystemURL');
    
    // Customize reminder message based on type
    $reminderMessages = [
        'pre_reminder' => 'Friendly reminder - your invoice is due in 3 days.',
        'due_soon' => 'Your invoice payment is due within the next few days.',
        'overdue_week' => 'Your invoice payment is now overdue.',
        'overdue_final' => 'This is our final payment reminder. Services may be suspended without immediate payment.'
    ];
    
    $variables = [
        'company_name' => \WHMCS\Config\Setting::getValue('CompanyName'),
        'customer_name' => $client['firstname'],
        'reminder_message' => $reminderMessages[$reminderType] ?? 'Payment reminder for your invoice.',
        'invoice_number' => $invoice['invoicenum'],
        'currency' => $invoice['currencyprefix'],
        'amount' => $invoice['total'] . ' ' . $invoice['currencysuffix'],
        'status' => ucfirst(str_replace('_', ' ', $reminderType)),
        'payment_url' => $systemUrl . '/viewinvoice.php?id=' . $invoice['invoiceid']
    ];
    
    return $templateManager->getProcessedTemplate('payment_reminder', $variables, $client['language'] ?? 'en');
}
?>

<?php
// File: includes/whatsapp/NotificationScheduler.php

class WhatsAppNotificationScheduler
{
    private $db;
    private $whatsapp;
    
    public function __construct()
    {
        $this->db = \WHMCS\Database\Capsule::connection();
        $this->whatsapp = new WhatsAppIntegration();
    }
    
    /**
     * Schedule notification for optimal delivery time
     */
    public function scheduleNotification($clientId, $message, $notificationType, $context = [], $priority = 'normal')
    {
        $client = localAPI('GetClientsDetails', ['clientid' => $clientId]);
        $preferences = $this->getClientPreferences($clientId);
        
        // Determine optimal send time
        $optimalTime = $this->calculateOptimalSendTime($clientId, $priority);
        
        // If optimal time is now or has passed, send immediately
        if ($optimalTime <= time()) {
            return $this->whatsapp->sendMessage(
                $client['phonenumber'],
                $message,
                $clientId,
                $context
            );
        }
        
        // Otherwise, queue for later
        return $this->queueNotification($clientId, $message, $notificationType, $context, $optimalTime);
    }
    
    /**
     * Calculate optimal send time based on customer preferences
     */
    private function calculateOptimalSendTime($clientId, $priority = 'normal')
    {
        $preferences = $this->getClientPreferences($clientId);
        $timezone = $preferences->timezone ?? 'UTC';
        
        // Priority notifications send immediately during business hours
        if ($priority === 'high') {
            $currentHour = (new DateTime('now', new DateTimeZone($timezone)))->format('H');
            if ($currentHour >= 8 && $currentHour <= 20) {
                return time(); // Send now
            }
        }
        
        // Regular notifications - optimize for 10 AM local time
        $tomorrow = new DateTime('tomorrow 10:00:00', new DateTimeZone($timezone));
        $today10am = new DateTime('today 10:00:00', new DateTimeZone($timezone));
        
        // If it's before 10 AM today, send at 10 AM today
        if (time() < $today10am->getTimestamp()) {
            return $today10am->getTimestamp();
        }
        
        // Otherwise, send at 10 AM tomorrow
        return $tomorrow->getTimestamp();
    }
    
    /**
     * Queue notification for later delivery
     */
    private function queueNotification($clientId, $message, $type, $context, $sendTime)
    {
        return $this->db->table('whatsapp_notification_queue')->insert([
            'client_id' => $clientId,
            'message_content' => $message,
            'notification_type' => $type,
            'context_data' => json_encode($context),
            'scheduled_send_time' => date('Y-m-d H:i:s', $sendTime),
            'status' => 'queued',
            'created_at' => now()
        ]);
    }
    
    /**
     * Process queued notifications (run via cron)
     */
    public function processQueue()
    {
        $queuedNotifications = $this->db->table('whatsapp_notification_queue')
            ->where('status', 'queued')
            ->where('scheduled_send_time', '<=', now())
            ->limit(50) // Process in batches
            ->get();
        
        foreach ($queuedNotifications as $notification) {
            try {
                $client = localAPI('GetClientsDetails', ['clientid' => $notification->client_id]);
                $context = json_decode($notification->context_data, true);
                
                $success = $this->whatsapp->sendMessage(
                    $client['phonenumber'],
                    $notification->message_content,
                    $notification->client_id,
                    $context
                );
                
                // Update queue status
                $this->db->table('whatsapp_notification_queue')
                    ->where('id', $notification->id)
                    ->update([
                        'status' => $success ? 'sent' : 'failed',
                        'processed_at' => now()
                    ]);
                
            } catch (Exception $e) {
                // Mark as failed
                $this->db->table('whatsapp_notification_queue')
                    ->where('id', $notification->id)
                    ->update([
                        'status' => 'failed',
                        'error_message' => $e->getMessage(),
                        'processed_at' => now()
                    ]);
            }
        }
        
        // Clean up old processed notifications (older than 30 days)
        $this->db->table('whatsapp_notification_queue')
            ->where('processed_at', '<', date('Y-m-d H:i:s', strtotime('-30 days')))
            ->delete();
    }
    
    /**
     * Get client notification preferences
     */
    private function getClientPreferences($clientId)
    {
        return $this->db->table('whatsapp_preferences')
            ->where('client_id', $clientId)
            ->first();
    }
}

// Add queue table
/*
CREATE TABLE `whatsapp_notification_queue` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `client_id` int(11) NOT NULL,
    `message_content` text NOT NULL,
    `notification_type` varchar(50) NOT NULL,
    `context_data` text DEFAULT NULL,
    `scheduled_send_time` datetime NOT NULL,
    `status` enum('queued','sent','failed') DEFAULT 'queued',
    `error_message` text DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    `processed_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `client_id` (`client_id`),
    KEY `scheduled_send_time` (`scheduled_send_time`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/
?>

<?php
// File: includes/hooks/whatsapp_support_notifications.php

require_once dirname(__FILE__) . '/../whatsapp/WhatsAppIntegration.php';
require_once dirname(__FILE__) . '/../whatsapp/TemplateManager.php';

// New ticket creation
add_hook('TicketOpen', 1, function($vars) {
    $whatsapp = new WhatsAppIntegration();
    $templateManager = new WhatsAppTemplateManager();
    
    try {
        // Get client details
        $client = localAPI('GetClientsDetails', [
            'clientid' => $vars['userid']
        ]);
        
        // Check if client wants support notifications
        if (!empty($client['phonenumber']) && shouldSendSupportNotification($client['userid'])) {
            
            $variables = [
                'company_name' => \WHMCS\Config\Setting::getValue('CompanyName'),
                'customer_name' => $client['firstname'],
                'ticket_id' => $vars['tid'],
                'subject' => $vars['subject'],
                'status' => 'Open',
                'response' => 'Your ticket has been created and assigned to our team.',
                'ticket_url' => \WHMCS\Config\Setting::getValue('SystemURL') . '/viewticket.php?tid=' . $vars['tid']
            ];
            
            $message = $templateManager->getProcessedTemplate('support_update', $variables, $client['language'] ?? 'en');
            
            $context = [
                'type' => 'ticket_created',
                'ticket_id' => $vars['tid'],
                'phone' => $client['phonenumber']
            ];
            
            $whatsapp->sendMessage(
                $client['phonenumber'],
                $message,
                $client['userid'],
                $context
            );
            
            logActivity("WhatsApp ticket notification sent for Ticket #" . $vars['tid']);
        }
        
    } catch (Exception $e) {
        logActivity("WhatsApp ticket notification error: " . $e->getMessage());
    }
});

// Ticket reply notifications
add_hook('TicketAdminReply', 1, function($vars) {
    $whatsapp = new WhatsAppIntegration();
    
    try {
        // Get ticket details
        $ticket = localAPI('GetTicket', [
            'ticketid' => $vars['ticketid']
        ]);
        
        // Get client details
        $client = localAPI('GetClientsDetails', [
            'clientid' => $ticket['userid']
        ]);
        
        if (!empty($client['phonenumber']) && shouldSendSupportNotification($client['userid'])) {
            
            $message = createTicketReplyMessage($ticket, $client, $vars['message']);
            
            $context = [
                'type' => 'ticket_reply',
                'ticket_id' => $vars['ticketid'],
                'phone' => $client['phonenumber']
            ];
            
            $whatsapp->sendMessage(
                $client['phonenumber'],
                $message,
                $client['userid'],
                $context
            );
            
            logActivity("WhatsApp ticket reply sent for Ticket #" . $vars['ticketid']);
        }
        
    } catch (Exception $e) {
        logActivity("WhatsApp ticket reply error: " . $e->getMessage());
    }
});

// Ticket status change notifications
add_hook('TicketStatusChange', 1, function($vars) {
    $whatsapp = new WhatsAppIntegration();
    
    try {
        // Get ticket details
        $ticket = localAPI('GetTicket', [
            'ticketid' => $vars['ticketid']
        ]);
        
        // Get client details
        $client = localAPI('GetClientsDetails', [
            'clientid' => $ticket['userid']
        ]);
        
        if (!empty($client['phonenumber']) && shouldSendSupportNotification($client['userid'])) {
            
            $message = createTicketStatusMessage($ticket, $client, $vars['status']);
            
            $context = [
                'type' => 'ticket_status',
                'ticket_id' => $vars['ticketid'],
                'phone' => $client['phonenumber']
            ];
            
            $whatsapp->sendMessage(
                $client['phonenumber'],
                $message,
                $client['userid'],
                $context
            );
            
            logActivity("WhatsApp ticket status update sent for Ticket #" . $vars['ticketid']);
        }
        
    } catch (Exception $e) {
        logActivity("WhatsApp ticket status error: " . $e->getMessage());
    }
});

function createTicketReplyMessage($ticket, $client, $replyMessage) {
    $companyName = \WHMCS\Config\Setting::getValue('CompanyName');
    $systemUrl = \WHMCS\Config\Setting::getValue('SystemURL');
    
    // Truncate long replies
    $shortReply = strlen($replyMessage) > 200 
        ? substr($replyMessage, 0, 197) . '...' 
        : $replyMessage;
    
    $message = "*Support Update - $companyName*\n\n";
    $message .= "Hi " . $client['firstname'] . ",\n\n";
    $message .= "Our team has replied to your support ticket.\n\n";
    $message .= "*Ticket Details:*\n";
    $message .= "Ticket #: " . $ticket['tid'] . "\n";
    $message .= "Subject: " . $ticket['subject'] . "\n";
    $message .= "Status: " . $ticket['status'] . "\n\n";
    $message .= "*Latest Response:*\n";
    $message .= $shortReply . "\n\n";
    $message .= "*View Full Ticket:*\n";
    $message .= $systemUrl . '/viewticket.php?tid=' . $ticket['tid'] . "\n\n";
    $message .= "Reply to continue the conversation!";
    
    return $message;
}

function createTicketStatusMessage($ticket, $client, $newStatus) {
    $companyName = \WHMCS\Config\Setting::getValue('CompanyName');
    $systemUrl = \WHMCS\Config\Setting::getValue('SystemURL');
    
    $statusMessages = [
        'Open' => 'Your ticket is now open and being reviewed.',
        'Answered' => 'Your ticket has been answered. Please check for updates.',
        'Customer-Reply' => 'Thank you for your reply. We\'ll review it soon.',
        'In Progress' => 'We\'re actively working on your ticket.',
        'On Hold' => 'Your ticket is temporarily on hold.',
        'Closed' => 'Your ticket has been resolved and closed.'
    ];
    
    $statusMessage = $statusMessages[$newStatus] ?? "Your ticket status has been updated to: $newStatus";
    
    $message = "*Ticket Status Update - $companyName*\n\n";
    $message .= "Hi " . $client['firstname'] . ",\n\n";
    $message .= $statusMessage . "\n\n";
    $message .= "*Ticket Details:*\n";
    $message .= "Ticket #: " . $ticket['tid'] . "\n";
    $message .= "Subject: " . $ticket['subject'] . "\n";
    $message .= "Status: " . $newStatus . "\n\n";
    
    if ($newStatus !== 'Closed') {
        $message .= "*View Ticket:*\n";
        $message .= $systemUrl . '/viewticket.php?tid=' . $ticket['tid'] . "\n\n";
        $message .= "Need more help? Reply here!";
    } else {
        $message .= "Thank you for choosing us!";
    }
    
    return $message;
}

function shouldSendSupportNotification($clientId) {
    $pref = \WHMCS\Database\Capsule::table('whatsapp_preferences')
        ->where('client_id', $clientId)
        ->where('support_notifications', 1)
        ->first();
        
    return $pref !== null;
}
?>

<?php
// File: includes/whatsapp/PreferenceManager.php

class WhatsAppPreferenceManager
{
    private $db;
    
    public function __construct()
    {
        $this->db = \WHMCS\Database\Capsule::connection();
    }
    
    /**
     * Get client preferences (create default if not exists)
     */
    public function getClientPreferences($clientId)
    {
        $preferences = $this->db->table('whatsapp_preferences')
            ->where('client_id', $clientId)
            ->first();
            
        if (!$preferences) {
            $preferences = $this->createDefaultPreferences($clientId);
        }
        
        return $preferences;
    }
    
    /**
     * Create default preferences for new client
     */
    private function createDefaultPreferences($clientId)
    {
        $client = localAPI('GetClientsDetails', ['clientid' => $clientId]);
        
        $defaultPrefs = [
            'client_id' => $clientId,
            'phone_number' => $this->formatPhoneNumber($client['phonenumber'] ?? ''),
            'notifications_enabled' => 1,
            'order_notifications' => 1,
            'invoice_notifications' => 1,
            'payment_reminders' => 1,
            'service_updates' => 1,
            'support_notifications' => 1,
            'language_preference' => $client['language'] ?? 'en',
            'timezone' => $client['timezone'] ?? 'UTC'
        ];
        
        $this->db->table('whatsapp_preferences')->insert($defaultPrefs);
        
        return (object) $defaultPrefs;
    }
    
    /**
     * Update client preferences
     */
    public function updatePreferences($clientId, $updates)
    {
        $updates['updated_at'] = now();
        
        return $this->db->table('whatsapp_preferences')
            ->where('client_id', $clientId)
            ->update($updates);
    }
    
    /**
     * Opt-in client for WhatsApp notifications
     */
    public function optInClient($clientId, $phoneNumber)
    {
        $preferences = $this->getClientPreferences($clientId);
        
        return $this->updatePreferences($clientId, [
            'phone_number' => $this->formatPhoneNumber($phoneNumber),
            'notifications_enabled' => 1
        ]);
    }
    
    /**
     * Opt-out client from WhatsApp notifications
     */
    public function optOutClient($clientId)
    {
        return $this->updatePreferences($clientId, [
            'notifications_enabled' => 0
        ]);
    }
    
    /**
     * Check if client has opted in for specific notification type
     */
    public function hasOptedIn($clientId, $notificationType = 'general')
    {
        $preferences = $this->getClientPreferences($clientId);
        
        if (!$preferences->notifications_enabled) {
            return false;
        }
        
        $typeMapping = [
            'order_confirmation' => 'order_notifications',
            'service_activation' => 'service_updates',
            'invoice_created' => 'invoice_notifications',
            'payment_reminder' => 'payment_reminders',
            'ticket_created' => 'support_notifications',
            'ticket_reply' => 'support_notifications',
            'ticket_status' => 'support_notifications'
        ];
        
        $prefField = $typeMapping[$notificationType] ?? 'notifications_enabled';
        
        return (bool) $preferences->$prefField;
    }
    
    /**
     * Format phone number
     */
    private function formatPhoneNumber($phone)
    {
        $phone = preg_replace('/[^0-9]/', '', $phone);
        
        if (strlen($phone) == 10) {
            $phone = '1' . $phone;
        }
        
        return '+' . $phone;
    }
    
    /**
     * Get preference statistics
     */
    public function getPreferenceStats()
    {
        $total = $this->db->table('whatsapp_preferences')->count();
        $enabled = $this->db->table('whatsapp_preferences')->where('notifications_enabled', 1)->count();
        
        $typeStats = $this->db->table('whatsapp_preferences')
            ->select(
                \WHMCS\Database\Capsule::raw('SUM(order_notifications) as order_count'),
                \WHMCS\Database\Capsule::raw('SUM(invoice_notifications) as invoice_count'),
                \WHMCS\Database\Capsule::raw('SUM(payment_reminders) as reminder_count'),
                \WHMCS\Database\Capsule::raw('SUM(service_updates) as service_count'),
                \WHMCS\Database\Capsule::raw('SUM(support_notifications) as support_count')
            )
            ->where('notifications_enabled', 1)
            ->first();
        
        return [
            'total_clients' => $total,
            'opted_in' => $enabled,
            'opt_in_rate' => $total > 0 ? round(($enabled / $total) * 100, 2) : 0,
            'notification_types' => [
                'orders' => $typeStats->order_count ?? 0,
                'invoices' => $typeStats->invoice_count ?? 0,
                'reminders' => $typeStats->reminder_count ?? 0,
                'services' => $typeStats->service_count ?? 0,
                'support' => $typeStats->support_count ?? 0
            ]
        ];
    }
}
?>

<?php
// File: includes/whatsapp/AnalyticsManager.php

class WhatsAppAnalyticsManager
{
    private $db;
    
    public function __construct()
    {
        $this->db = \WHMCS\Database\Capsule::connection();
    }
    
    /**
     * Get comprehensive analytics data
     */
    public function getAnalytics($period = 30)
    {
        $startDate = date('Y-m-d', strtotime("-$period days"));
        
        return [
            'summary' => $this->getSummaryStats($startDate),
            'by_type' => $this->getStatsByType($startDate),
            'delivery_rates' => $this->getDeliveryRates($startDate),
            'customer_engagement' => $this->getCustomerEngagement($startDate),
            'business_impact' => $this->getBusinessImpact($startDate),
            'trends' => $this->getTrends($startDate)
        ];
    }
    
    /**
     * Get summary statistics
     */
    private function getSummaryStats($startDate)
    {
        $total = $this->db->table('whatsapp_notifications')
            ->where('created_at', '>=', $startDate)
            ->count();
            
        $sent = $this->db->table('whatsapp_notifications')
            ->where('created_at', '>=', $startDate)
            ->where('status', 'sent')
            ->count();
            
        $failed = $this->db->table('whatsapp_notifications')
            ->where('created_at', '>=', $startDate)
            ->where('status', 'failed')
            ->count();
        
        return [
            'total_notifications' => $total,
            'successful_deliveries' => $sent,
            'failed_deliveries' => $failed,
            'success_rate' => $total > 0 ? round(($sent / $total) * 100, 2) : 0,
            'failure_rate' => $total > 0 ? round(($failed / $total) * 100, 2) : 0
        ];
    }
    
    /**
     * Get statistics by notification type
     */
    private function getStatsByType($startDate)
    {
        $stats = $this->db->table('whatsapp_notifications')
            ->select('notification_type', 'status', \WHMCS\Database\Capsule::raw('COUNT(*) as count'))
            ->where('created_at', '>=', $startDate)
            ->groupBy('notification_type', 'status')
            ->get();
        
        $organized = [];
        foreach ($stats as $stat) {
            $organized[$stat->notification_type][$stat->status] = $stat->count;
        }
        
        // Calculate rates for each type
        foreach ($organized as $type => &$data) {
            $total = array_sum($data);
            $sent = $data['sent'] ?? 0;
            $data['total'] = $total;
            $data['success_rate'] = $total > 0 ? round(($sent / $total) * 100, 2) : 0;
        }
        
        return $organized;
    }
    
    /**
     * Get delivery rates over time
     */
    private function getDeliveryRates($startDate)
    {
        return $this->db->table('whatsapp_notifications')
            ->select(
                \WHMCS\Database\Capsule::raw('DATE(created_at) as date'),
                \WHMCS\Database\Capsule::raw('COUNT(*) as total'),
                \WHMCS\Database\Capsule::raw('SUM(CASE WHEN status = "sent" THEN 1 ELSE 0 END) as sent')
            )
            ->where('created_at', '>=', $startDate)
            ->groupBy(\WHMCS\Database\Capsule::raw('DATE(created_at)'))
            ->orderBy('date')
            ->get()
            ->map(function($item) {
                $item->success_rate = $item->total > 0 ? round(($item->sent / $item->total) * 100, 2) : 0;
                return $item;
            });
    }
    
    /**
     * Get customer engagement metrics
     */
    private function getCustomerEngagement($startDate)
    {
        // Customers who received notifications
        $recipients = $this->db->table('whatsapp_notifications')
            ->where('created_at', '>=', $startDate)
            ->where('status', 'sent')
            ->distinct()
            ->count('client_id');
        
        // Average notifications per customer
        $totalNotifications = $this->db->table('whatsapp_notifications')
            ->where('created_at', '>=', $startDate)
            ->where('status', 'sent')
            ->count();
        
        $avgPerCustomer = $recipients > 0 ? round($totalNotifications / $recipients, 2) : 0;
        
        // Most active notification types
        $topTypes = $this->db->table('whatsapp_notifications')
            ->select('notification_type', \WHMCS\Database\Capsule::raw('COUNT(*) as count'))
            ->where('created_at', '>=', $startDate)
            ->where('status', 'sent')
            ->groupBy('notification_type')
            ->orderByDesc('count')
            ->limit(5)
            ->get();
        
        return [
            'unique_recipients' => $recipients,
            'avg_notifications_per_customer' => $avgPerCustomer,
            'top_notification_types' => $topTypes
        ];
    }
    
    /**
     * Get business impact metrics
     */
    private function getBusinessImpact($startDate)
    {
        // Payment reminders effectiveness
        $remindersSent = $this->db->table('whatsapp_notifications')
            ->where('notification_type', 'payment_reminder')
            ->where('created_at', '>=', $startDate)
            ->where('status', 'sent')
            ->count();
        
        // Invoices paid after WhatsApp reminder (rough estimate)
        $invoicesPaidAfterReminder = $this->db->table('whatsapp_notifications as wn')
            ->join('tblinvoices as i', 'wn.invoice_id', '=', 'i.id')
            ->where('wn.notification_type', 'payment_reminder')
            ->where('wn.created_at', '>=', $startDate)
            ->where('wn.status', 'sent')
            ->where('i.status', 'Paid')
            ->whereRaw('i.datepaid >= wn.sent_at')
            ->count();
        
        // Support ticket response improvements
        $ticketNotifications = $this->db->table('whatsapp_notifications')
            ->whereIn('notification_type', ['ticket_created', 'ticket_reply', 'ticket_status'])
            ->where('created_at', '>=', $startDate)
            ->where('status', 'sent')
            ->count();
        
        return [
            'payment_reminders_sent' => $remindersSent,
            'estimated_payments_recovered' => $invoicesPaidAfterReminder,
            'payment_recovery_rate' => $remindersSent > 0 ? round(($invoicesPaidAfterReminder / $remindersSent) * 100, 2) : 0,
            'support_notifications_sent' => $ticketNotifications
        ];
    }
    
    /**
     * Get trend data
     */
    private function getTrends($startDate)
    {
        $weeklyData = $this->db->table('whatsapp_notifications')
            ->select(
                \WHMCS\Database\Capsule::raw('WEEK(created_at) as week'),
                \WHMCS\Database\Capsule::raw('YEAR(created_at) as year'),
                \WHMCS\Database\Capsule::raw('COUNT(*) as total'),
                \WHMCS\Database\Capsule::raw('SUM(CASE WHEN status = "sent" THEN 1 ELSE 0 END) as sent')
            )
            ->where('created_at', '>=', $startDate)
            ->groupBy(\WHMCS\Database\Capsule::raw('YEAR(created_at), WEEK(created_at)'))
            ->orderBy('year')
            ->orderBy('week')
            ->get();
        
        return [
            'weekly_volume' => $weeklyData,
            'growth_rate' => $this->calculateGrowthRate($weeklyData)
        ];
    }
    
    /**
     * Calculate growth rate
     */
    private function calculateGrowthRate($weeklyData)
    {
        if (count($weeklyData) < 2) {
            return 0;
        }
        
        $first = $weeklyData->first()->total;
        $last = $weeklyData->last()->total;
        
        if ($first == 0) {
            return 0;
        }
        
        return round((($last - $first) / $first) * 100, 2);
    }
    
    /**
     * Generate analytics report
     */
    public function generateReport($period = 30)
    {
        $analytics = $this->getAnalytics($period);
        
        $report = "WhatsApp Notifications Analytics Report\n";
        $report .= "Period: Last $period days\n";
        $report .= str_repeat("=", 50) . "\n\n";
        
        $report .= "SUMMARY:\n";
        $report .= "Total Notifications: " . number_format($analytics['summary']['total_notifications']) . "\n";
        $report .= "Successful Deliveries: " . number_format($analytics['summary']['successful_deliveries']) . "\n";
        $report .= "Success Rate: " . $analytics['summary']['success_rate'] . "%\n";
        $report .= "Failed Deliveries: " . number_format($analytics['summary']['failed_deliveries']) . "\n\n";
        
        $report .= "BUSINESS IMPACT:\n";
        $report .= "Payment Reminders Sent: " . number_format($analytics['business_impact']['payment_reminders_sent']) . "\n";
        $report .= "Estimated Payments Recovered: " . number_format($analytics['business_impact']['estimated_payments_recovered']) . "\n";
        $report .= "Payment Recovery Rate: " . $analytics['business_impact']['payment_recovery_rate'] . "%\n";
        $report .= "Support Notifications Sent: " . number_format($analytics['business_impact']['support_notifications_sent']) . "\n\n";
        
        $report .= "CUSTOMER ENGAGEMENT:\n";
        $report .= "Unique Recipients: " . number_format($analytics['customer_engagement']['unique_recipients']) . "\n";
        $report .= "Avg Notifications per Customer: " . $analytics['customer_engagement']['avg_notifications_per_customer'] . "\n\n";
        
        return $report;
    }
}

// Usage example
/*
$analytics = new WhatsAppAnalyticsManager();
$report = $analytics->generateReport(30);
echo $report;
*/
?>