<?php
/**
 * Low Balance Alert Manager
 * Handles low balance alerts for users
 */

class LowBalanceAlert {
    private $conn;
    
    public function __construct($connection) {
        $this->conn = $connection;
        $this->createTablesIfNotExist();
    }
    
    /**
     * Create necessary tables if they don't exist
     */
    private function createTablesIfNotExist() {
        // Create low_balance_alerts table to track when alerts were sent
        $createTable = "CREATE TABLE IF NOT EXISTS `low_balance_alerts` (
            `id` INT(11) NOT NULL AUTO_INCREMENT,
            `user_id` INT(11) NOT NULL,
            `balance` DECIMAL(10,2) NOT NULL,
            `alert_sent_at` DATETIME NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `user_id` (`user_id`),
            KEY `alert_sent_at` (`alert_sent_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        mysqli_query($this->conn, $createTable);
    }
    
    /**
     * Get low balance alert configuration
     */
    public function getConfig() {
        $config = [
            'enabled' => false,
            'threshold_amount' => 50.00,
            'alert_interval_days' => 2
        ];
        
        // Get from payment_config table
        $keys = ['low_balance_alert_enabled', 'low_balance_threshold', 'low_balance_alert_interval'];
        foreach ($keys as $key) {
            $stmt = $this->conn->prepare("SELECT config_value FROM payment_config WHERE config_key = ?");
            if ($stmt) {
                $stmt->bind_param("s", $key);
                $stmt->execute();
                $result = $stmt->get_result();
                if ($row = $result->fetch_assoc()) {
                    switch ($key) {
                        case 'low_balance_alert_enabled':
                            $config['enabled'] = ($row['config_value'] == '1' || $row['config_value'] === '1' || $row['config_value'] == 1);
                            break;
                        case 'low_balance_threshold':
                            $config['threshold_amount'] = floatval($row['config_value']);
                            break;
                        case 'low_balance_alert_interval':
                            $config['alert_interval_days'] = intval($row['config_value']);
                            break;
                    }
                }
                $stmt->close();
            }
        }
        
        error_log("Low balance config loaded: enabled=" . ($config['enabled'] ? 'true' : 'false') . ", threshold=" . $config['threshold_amount'] . ", interval=" . $config['alert_interval_days']);
        
        return $config;
    }
    
    /**
     * Update configuration
     */
    public function updateConfig($enabled, $thresholdAmount, $intervalDays) {
        $configs = [
            'low_balance_alert_enabled' => $enabled ? '1' : '0',
            'low_balance_threshold' => (string)$thresholdAmount,
            'low_balance_alert_interval' => (string)$intervalDays
        ];
        
        foreach ($configs as $key => $value) {
            $checkStmt = $this->conn->prepare("SELECT id FROM payment_config WHERE config_key = ?");
            $checkStmt->bind_param("s", $key);
            $checkStmt->execute();
            $result = $checkStmt->get_result();
            
            if ($result->num_rows > 0) {
                $updateStmt = $this->conn->prepare("UPDATE payment_config SET config_value = ? WHERE config_key = ?");
                $updateStmt->bind_param("ss", $value, $key);
                $updateStmt->execute();
                $updateStmt->close();
            } else {
                $description = ucwords(str_replace('_', ' ', $key)) . ' configuration';
                $insertStmt = $this->conn->prepare("INSERT INTO payment_config (config_key, config_value, description) VALUES (?, ?, ?)");
                $insertStmt->bind_param("sss", $key, $value, $description);
                $insertStmt->execute();
                $insertStmt->close();
            }
            $checkStmt->close();
        }
        
        return true;
    }
    
    /**
     * Get users with low balance who need alerts
     * @param bool $ignoreInterval If true, ignores the interval check and returns all users with low balance
     */
    public function getUsersNeedingAlerts($ignoreInterval = false) {
        $config = $this->getConfig();
        
        if (!$config['enabled']) {
            error_log("Low balance alerts are disabled in configuration");
            return [];
        }
        
        $threshold = floatval($config['threshold_amount']);
        $intervalDays = intval($config['alert_interval_days']);
        $cutoffDate = date('Y-m-d H:i:s', strtotime("-{$intervalDays} days"));
        
        error_log("Low balance check: Threshold = {$threshold}, Interval = {$intervalDays} days, Cutoff = {$cutoffDate}");
        
        // Simplified query: Get users with balance below threshold
        // Handle NULL balances by treating them as 0
        $query = "SELECT a.id, a.username, a.email, COALESCE(a.balance, 0) as balance, a.role, a.phone
                  FROM agents a
                  WHERE COALESCE(a.balance, 0) < ?
                  ORDER BY a.balance ASC";
        
        $stmt = $this->conn->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare query: " . $this->conn->error);
            return [];
        }
        
        $stmt->bind_param("d", $threshold);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if (!$result) {
            error_log("Query execution failed: " . $stmt->error);
            $stmt->close();
            return [];
        }
        
        $allLowBalanceUsers = [];
        $users = [];
        
        while ($row = $result->fetch_assoc()) {
            $row['balance'] = floatval($row['balance']); // Ensure balance is float
            $allLowBalanceUsers[] = $row;
            $userId = $row['id'];
            
            // If ignoring interval, include all users with low balance
            if ($ignoreInterval) {
                $alertStmt = $this->conn->prepare("SELECT MAX(alert_sent_at) as last_alert_date FROM low_balance_alerts WHERE user_id = ?");
                if ($alertStmt) {
                    $alertStmt->bind_param("i", $userId);
                    $alertStmt->execute();
                    $alertResult = $alertStmt->get_result();
                    $alertData = $alertResult->fetch_assoc();
                    $alertStmt->close();
                    $row['last_alert_date'] = $alertData['last_alert_date'] ?? '1970-01-01 00:00:00';
                }
                $users[] = $row;
                continue;
            }
            
            // Check last alert date for this user
            $alertStmt = $this->conn->prepare("SELECT MAX(alert_sent_at) as last_alert_date FROM low_balance_alerts WHERE user_id = ?");
            if ($alertStmt) {
                $alertStmt->bind_param("i", $userId);
                $alertStmt->execute();
                $alertResult = $alertStmt->get_result();
                $alertData = $alertResult->fetch_assoc();
                $alertStmt->close();
                
                $lastAlertDate = $alertData['last_alert_date'] ?? null;
                
                // Include user if:
                // 1. Never received an alert (lastAlertDate is NULL), OR
                // 2. Last alert was more than interval_days ago
                if ($lastAlertDate === null || strtotime($lastAlertDate) < strtotime($cutoffDate)) {
                    $row['last_alert_date'] = $lastAlertDate ?? '1970-01-01 00:00:00';
                    $users[] = $row;
                } else {
                    error_log("User {$row['username']} (ID: {$userId}) skipped - last alert on {$lastAlertDate} (cutoff: {$cutoffDate})");
                }
            }
        }
        $stmt->close();
        
        error_log("Low balance check: Found " . count($allLowBalanceUsers) . " users with low balance, " . count($users) . " need alerts");
        
        return $users;
    }
    
    /**
     * Get all users with low balance (for debugging - ignores alert interval)
     */
    public function getAllUsersWithLowBalance() {
        $config = $this->getConfig();
        
        if (!$config['enabled']) {
            return [];
        }
        
        $threshold = floatval($config['threshold_amount']);
        
        // Handle NULL balances by treating them as 0
        $query = "SELECT a.id, a.username, a.email, COALESCE(a.balance, 0) as balance, a.role, a.phone,
                         (SELECT MAX(alert_sent_at) FROM low_balance_alerts WHERE user_id = a.id) as last_alert_date
                  FROM agents a
                  WHERE COALESCE(a.balance, 0) < ?
                  ORDER BY a.balance ASC";
        
        $stmt = $this->conn->prepare($query);
        if (!$stmt) {
            error_log("Failed to prepare getAllUsersWithLowBalance query: " . $this->conn->error);
            return [];
        }
        
        $stmt->bind_param("d", $threshold);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if (!$result) {
            error_log("getAllUsersWithLowBalance query execution failed: " . $stmt->error);
            $stmt->close();
            return [];
        }
        
        $users = [];
        while ($row = $result->fetch_assoc()) {
            $row['balance'] = floatval($row['balance']); // Ensure balance is float
            $users[] = $row;
        }
        $stmt->close();
        
        return $users;
    }
    
    /**
     * Record that an alert was sent to a user
     */
    public function recordAlertSent($userId, $balance) {
        $stmt = $this->conn->prepare("INSERT INTO low_balance_alerts (user_id, balance, alert_sent_at) VALUES (?, ?, NOW())");
        $stmt->bind_param("id", $userId, $balance);
        $stmt->execute();
        $stmt->close();
    }
    
    /**
     * Send low balance alert to a user via SMS only
     */
    public function sendAlert($userId, $username, $email, $balance, $threshold, $phone = null) {
        require_once __DIR__ . '/NotificationManager.php';
        $notificationManager = new NotificationManager($this->conn);
        
        // Get phone number if not provided
        if (empty($phone)) {
            $stmt = $this->conn->prepare("SELECT phone FROM agents WHERE id = ?");
            $stmt->bind_param("i", $userId);
            $stmt->execute();
            $result = $stmt->get_result();
            if ($row = $result->fetch_assoc()) {
                $phone = $row['phone'] ?? '';
            }
            $stmt->close();
        }
        
        // If no phone number, cannot send SMS
        if (empty($phone)) {
            error_log("Cannot send low balance alert to user {$username} (ID: {$userId}): No phone number");
            return false;
        }
        
        // Create SMS message
        $balanceFormatted = number_format($balance, 2);
        $thresholdFormatted = number_format($threshold, 2);
        $message = "⚠️ Low Balance Alert - D-Bundles Stores\n\n";
        $message .= "Hello {$username},\n\n";
        $message .= "Your account balance is running low!\n\n";
        $message .= "Current Balance: ₵{$balanceFormatted}\n";
        $message .= "Threshold: ₵{$thresholdFormatted}\n\n";
        $message .= "Please top up your account to continue enjoying our services.\n\n";
        $message .= "Thank you,\nD-Bundles Stores";
        
        // Send SMS using NotificationManager's private method via reflection or create public method
        // Since sendSMS is private, we'll use a workaround by accessing it through getUserInfo and sendNotification
        // But better approach: send SMS directly using the SMS sending logic
        
        // Get SMS config and send directly
        $smsSent = false;
        
        // Use reflection to access private sendSMS method, or better: create a public method
        // For now, let's check if SMS is enabled and send directly
        $smsResult = mysqli_query($this->conn, "SELECT * FROM notification_config WHERE type = 'sms'");
        if ($smsResult && mysqli_num_rows($smsResult) > 0) {
            $smsData = mysqli_fetch_assoc($smsResult);
            $smsConfigData = json_decode($smsData['config_data'], true) ?: [];
            
            if (isset($smsConfigData['enabled']) && $smsConfigData['enabled']) {
                // Use NotificationManager's sendSMS via reflection or create helper
                // Actually, let's create a simple SMS sending method here
                $smsSent = $this->sendSMSDirect($phone, $message, $smsConfigData);
            }
        }
        
        if ($smsSent) {
            // Record that alert was sent only if SMS was successfully sent
            $this->recordAlertSent($userId, $balance);
            error_log("Low balance SMS alert sent successfully to user {$username} (ID: {$userId}, Phone: {$phone})");
            return true;
        } else {
            error_log("Failed to send low balance SMS to user {$username} (ID: {$userId}, Phone: {$phone})");
            // Don't record alert if SMS failed - so user can receive alert when SMS is working
            return false;
        }
    }
    
    /**
     * Send SMS directly using SMS configuration
     */
    private function sendSMSDirect($phoneNumber, $message, $smsConfig) {
        // Clean phone number
        $phoneNumber = preg_replace('/[^0-9+]/', '', $phoneNumber);
        if (empty($phoneNumber)) {
            return false;
        }
        
        $provider = $smsConfig['provider'] ?? 'generic';
        
        switch ($provider) {
            case 'smsonlinegh':
                return $this->sendSMSViaSMSOnlineGH($phoneNumber, $message, $smsConfig);
            case 'generic':
            default:
                return $this->sendSMSViaGeneric($phoneNumber, $message, $smsConfig);
        }
    }
    
    /**
     * Send SMS via SMSOnlineGH
     */
    private function sendSMSViaSMSOnlineGH($phoneNumber, $message, $config) {
        $apiKey = $config['api_key'] ?? '';
        $senderId = $config['sender_id'] ?? '';
        
        if (empty($apiKey) || empty($senderId)) {
            error_log("SMS Online Ghana: Missing API key or sender ID");
            return false;
        }
        
        // Prepare phone number (ensure it starts with country code)
        if (strpos($phoneNumber, '233') !== 0) {
            if (strpos($phoneNumber, '0') === 0) {
                $phoneNumber = '233' . substr($phoneNumber, 1);
            } else {
                $phoneNumber = '233' . $phoneNumber;
            }
        }
        
        // Set up the request headers according to SMS Online Ghana API documentation
        $headers = [
            'Host: api.smsonlinegh.com',
            'Content-Type: application/json',
            'Accept: application/json',
            'Authorization: key ' . $apiKey
        ];
        
        // Set up the message data according to SMS Online Ghana API documentation
        $messageData = [
            'text' => $message,
            'type' => 0, // GSM default
            'sender' => $senderId,
            'destinations' => [$phoneNumber]
        ];
        
        // Initialize cURL with the correct endpoint
        $ch = curl_init('https://api.smsonlinegh.com/v5/sms/send');
        
        // Set cURL options according to SMS Online Ghana API documentation
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($messageData));
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        
        $response = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $curlError = curl_error($ch);
        curl_close($ch);
        
        if ($curlError) {
            error_log("SMS Online Ghana cURL error: " . $curlError);
            return false;
        }
        
        if ($httpCode == 200) {
            $responseData = json_decode($response, true);
            if (isset($responseData['status']) && $responseData['status'] == 'success') {
                return true;
            } else {
                error_log("SMS Online Ghana API error: " . ($response ?: "No response"));
                return false;
            }
        } else {
            error_log("SMS Online Ghana: Failed - HTTP Code: $httpCode, Response: $response");
            return false;
        }
    }
    
    /**
     * Send SMS via Generic API
     */
    private function sendSMSViaGeneric($phoneNumber, $message, $config) {
        $apiUrl = $config['api_url'] ?? '';
        $apiKey = $config['api_key'] ?? '';
        
        if (empty($apiUrl)) {
            return false;
        }
        
        $data = [
            'phone' => $phoneNumber,
            'message' => $message
        ];
        
        if (!empty($apiKey)) {
            $data['api_key'] = $apiKey;
        }
        
        $ch = curl_init($apiUrl);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);
        
        $response = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);
        
        return ($httpCode == 200 && $response !== false);
    }
    
    /**
     * Process and send alerts to all users who need them
     * @param bool $ignoreInterval If true, sends alerts to all users with low balance regardless of interval
     */
    public function processAlerts($ignoreInterval = false) {
        $config = $this->getConfig();
        
        if (!$config['enabled']) {
            error_log("Low balance alerts are disabled - skipping process");
            return [
                'total' => 0,
                'sent' => 0,
                'failed' => 0
            ];
        }
        
        $intervalNote = $ignoreInterval ? " (ignoring interval)" : "";
        error_log("Starting low balance alert process - Threshold: {$config['threshold_amount']}, Interval: {$config['alert_interval_days']} days{$intervalNote}");
        
        $users = $this->getUsersNeedingAlerts($ignoreInterval);
        $sent = 0;
        $failed = 0;
        
        foreach ($users as $user) {
            try {
                // Check if user has phone number before attempting to send
                if (empty($user['phone'])) {
                    $failed++;
                    error_log("Skipping user {$user['username']} (ID: {$user['id']}) - No phone number");
                    continue;
                }
                
                $result = $this->sendAlert(
                    $user['id'],
                    $user['username'],
                    $user['email'],
                    $user['balance'],
                    $config['threshold_amount'],
                    $user['phone']
                );
                if ($result) {
                    $sent++;
                    error_log("Low balance SMS alert sent successfully to user {$user['username']} (ID: {$user['id']}) - Balance: {$user['balance']}");
                } else {
                    $failed++;
                    error_log("Failed to send low balance SMS alert to user {$user['username']} (ID: {$user['id']}, Phone: {$user['phone']}) - SMS sending failed");
                }
            } catch (Exception $e) {
                $failed++;
                error_log("Exception sending low balance alert to user {$user['username']} (ID: {$user['id']}): " . $e->getMessage());
            }
        }
        
        return [
            'total' => count($users),
            'sent' => $sent,
            'failed' => $failed
        ];
    }
}

