Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 228
0.00%
0 / 17
 CRAP
0.00%
0 / 1
SellerMarketingTriggerProcessor
0.00%
0 / 228
0.00%
0 / 17
 2550
0.00%
0 / 1
 __construct
0.00%
0 / 5
0.00%
0 / 1
 6
 processAllTriggers
0.00%
0 / 30
0.00%
0 / 1
 20
 processStoreTriggers
0.00%
0 / 30
0.00%
0 / 1
 30
 processTrigger
0.00%
0 / 46
0.00%
0 / 1
 132
 getCustomersForDaysSinceEvent
0.00%
0 / 26
0.00%
0 / 1
 72
 getCustomersForDaysSinceSold
0.00%
0 / 10
0.00%
0 / 1
 2
 getCustomersForBirthday
0.00%
0 / 10
0.00%
0 / 1
 2
 getCustomersForExpiringPoints
0.00%
0 / 12
0.00%
0 / 1
 2
 getCustomersForCustomTrigger
0.00%
0 / 18
0.00%
0 / 1
 56
 personalizeMessage
0.00%
0 / 13
0.00%
0 / 1
 6
 logCustomerMessage
0.00%
0 / 10
0.00%
0 / 1
 6
 getAllStores
0.00%
0 / 2
0.00%
0 / 1
 2
 getTriggersToProcess
0.00%
0 / 6
0.00%
0 / 1
 2
 logActivity
0.00%
0 / 7
0.00%
0 / 1
 2
 setMaxTriggersPerRun
0.00%
0 / 1
0.00%
0 / 1
 2
 setMaxCustomersPerTrigger
0.00%
0 / 1
0.00%
0 / 1
 2
 getProcessorIdentifier
0.00%
0 / 1
0.00%
0 / 1
 2
1<?php
2
3namespace BuyerKiosk\SellerMarketing;
4
5use Exception;
6use BuyerKiosk\SellerMarketing\SmsQueue;
7
8/**
9 * Processes seller marketing triggers automatically
10 * Runs periodically to check triggers and queue messages for eligible customers
11 */
12class SellerMarketingTriggerProcessor
13{
14    private $centralDb;
15    private $processorIdentifier;
16    private $maxTriggersPerRun = 50;
17    private $maxCustomersPerTrigger = 500;
18    private $smsQueue;
19
20    public function __construct($centralDb = null, $processorIdentifier = null)
21    {
22        if ($centralDb === null) {
23            $this->centralDb = dbConnectByName('kiosk_buykiosk');
24        } else {
25            $this->centralDb = $centralDb;
26        }
27
28        // Initialize SmsQueue with central database
29        $this->smsQueue = new SmsQueue($this->centralDb);
30
31        $this->processorIdentifier = $processorIdentifier ?? ('trigger_processor_' . gethostname() . '_' . getmypid());
32    }
33
34    /**
35     * Process all active triggers across all stores
36     */
37    public function processAllTriggers()
38    {
39        $startTime = microtime(true);
40        $this->logActivity('started');
41
42        try {
43            $results = [
44                'processor' => $this->processorIdentifier,
45                'start_time' => date('Y-m-d H:i:s', (int)$startTime),
46                'stores_processed' => 0,
47                'triggers_processed' => 0,
48                'messages_queued' => 0,
49                'errors' => []
50            ];
51
52            // Get all active stores
53            $stores = $this->getAllStores();
54
55            foreach ($stores as $storeRow) {
56                try {
57                    $storeResults = $this->processStoreTriggers($storeRow);
58
59                    $results['stores_processed']++;
60                    $results['triggers_processed'] += $storeResults['triggers_processed'];
61                    $results['messages_queued'] += $storeResults['messages_queued'];
62                    $results['errors'] = array_merge($results['errors'], $storeResults['errors']);
63
64                } catch (Exception $e) {
65                    $error = "Store {$storeRow['typeNum']}: " . $e->getMessage();
66                    $results['errors'][] = $error;
67                    error_log("Trigger processor error: " . $error);
68                }
69            }
70
71            $endTime = microtime(true);
72            $results['end_time'] = date('Y-m-d H:i:s', (int)$endTime);
73            $results['duration_seconds'] = round($endTime - $startTime, 2);
74            $results['error_count'] = count($results['errors']);
75
76            $this->logActivity('completed', $results);
77
78            return $results;
79
80        } catch (Exception $e) {
81            $this->logActivity('error', ['error' => $e->getMessage()]);
82            throw $e;
83        }
84    }
85
86    /**
87     * Process triggers for a specific store
88     */
89    public function processStoreTriggers($storeRow)
90    {
91        try {
92            $store = new \Store();
93            $store->createStore($storeRow['typeNum']);
94
95            if (!$store) {
96                throw new Exception("Failed to load store {$storeRow['typeNum']}");
97            }
98
99            $storeDb = dbConnectByName($store->getDbName());
100
101            $results = [
102                'store' => $storeRow['typeNum'],
103                'triggers_processed' => 0,
104                'messages_queued' => 0,
105                'errors' => []
106            ];
107
108            // Get active triggers that should be processed
109            $triggers = $this->getTriggersToProcess($storeDb);
110
111            foreach ($triggers as $triggerRow) {
112                try {
113                    $trigger = new SellerMarketingTrigger($triggerRow['id'], $storeDb);
114
115                    // Process the trigger
116                    $triggerResults = $this->processTrigger($trigger, $store, $storeDb, $storeRow);
117
118                    $results['triggers_processed']++;
119                    $results['messages_queued'] += $triggerResults['messages_queued'];
120
121                    // Mark trigger as processed
122                    $trigger->markAsProcessed();
123
124                } catch (Exception $e) {
125                    $error = "Trigger {$triggerRow['id']} ({$triggerRow['name']}): " . $e->getMessage();
126                    $results['errors'][] = $error;
127                    error_log("Error processing trigger: " . $error);
128                }
129            }
130
131            return $results;
132
133        } catch (Exception $e) {
134            return [
135                'store' => $storeRow['typeNum'],
136                'triggers_processed' => 0,
137                'messages_queued' => 0,
138                'errors' => [$e->getMessage()]
139            ];
140        }
141    }
142
143    /**
144     * Process a single trigger
145     *
146     * @param SellerMarketingTrigger $trigger The trigger to process
147     * @param Store $store The store object
148     * @param PDO $storeDb The store database connection
149     * @param array $storeRow Row from stores table with typeNum
150     */
151    private function processTrigger($trigger, $store, $storeDb, $storeRow)
152    {
153        $results = [
154            'trigger_id' => $trigger->getId(),
155            'trigger_name' => $trigger->getName(),
156            'messages_queued' => 0,
157            'customers_matched' => 0
158        ];
159
160        // Get trigger configuration
161        $type = $trigger->getType();
162        $config = $trigger->getConfig();
163
164        // Get eligible customers based on trigger type
165        $customers = [];
166
167        switch ($type) {
168            case 'days_since_event':
169                $customers = $this->getCustomersForDaysSinceEvent($trigger, $config, $storeDb);
170                break;
171
172            case 'days_since_sold':
173                $customers = $this->getCustomersForDaysSinceSold($trigger, $config, $storeDb);
174                break;
175
176            case 'birthday':
177                $customers = $this->getCustomersForBirthday($trigger, $config, $storeDb);
178                break;
179
180            case 'expiring_points':
181                $customers = $this->getCustomersForExpiringPoints($trigger, $config, $storeDb);
182                break;
183
184            case 'custom':
185                $customers = $this->getCustomersForCustomTrigger($trigger, $config, $storeDb);
186                break;
187
188            default:
189                throw new Exception("Unknown trigger type: {$type}");
190        }
191
192        $results['customers_matched'] = count($customers);
193
194        // Limit customers to prevent runaway processes
195        if (count($customers) > $this->maxCustomersPerTrigger) {
196            $customers = array_slice($customers, 0, $this->maxCustomersPerTrigger);
197            error_log("Trigger {$trigger->getId()}: Limited to {$this->maxCustomersPerTrigger} customers (matched: {$results['customers_matched']})");
198        }
199
200        // Get the message template
201        $message = new SellerMarketingMessage($trigger->getMessageId(), $storeDb);
202        $messageContent = $message->getContent();
203
204        // Queue messages for each eligible customer
205        foreach ($customers as $customer) {
206            try {
207                // Personalize the message
208                $personalizedMessage = $this->personalizeMessage($messageContent, $customer, $store);
209
210                // Add message to central queue
211                // SmsQueue->add() signature: add($typeNum, $phone, $message, $triggerId)
212                $queueId = $this->smsQueue->add(
213                    $storeRow['typeNum'],
214                    $customer['phone'],
215                    $personalizedMessage,
216                    $trigger->getId()
217                );
218
219                if ($queueId !== false) {
220                    $results['messages_queued']++;
221
222                    // Log to customer log
223                    $this->logCustomerMessage($storeDb, $trigger->getId(), $customer['customerID'] ?? null, $personalizedMessage);
224                } else {
225                    error_log("Failed to queue message for customer {$customer['customerID']}: queue->add() returned false");
226                }
227
228            } catch (Exception $e) {
229                error_log("Error queueing message for customer {$customer['customerID']}: " . $e->getMessage());
230            }
231        }
232
233        return $results;
234    }
235
236    /**
237     * Get customers for days_since_event trigger
238     */
239    private function getCustomersForDaysSinceEvent($trigger, $config, $storeDb)
240    {
241        $days = $config['days'] ?? 30;
242        $event = $config['event'] ?? 'last_visit';
243        $customerType = $config['customer_type'] ?? 'all';
244        $minRating = $config['min_rating'] ?? 0;
245
246        $eventColumn = match($event) {
247            'last_visit' => 'lastVisit',
248            'last_purchase' => 'lastBuy',
249            'last_sold' => 'lastSold',
250            'signup' => 'memberSince',
251            default => 'lastVisit'
252        };
253
254        $sql = "SELECT customerID, firstName, lastName, phone, email, {$eventColumn} as event_date
255                FROM customers
256                WHERE {$eventColumn} IS NOT NULL
257                AND DATEDIFF(CURDATE(), {$eventColumn}) = :days
258                AND phone IS NOT NULL
259                AND phone != ''
260                AND rating >= :min_rating
261                AND optInText = 1";
262
263        // Add customer type filter if specified
264        if ($customerType === 'buyers') {
265            $sql .= " AND lastBuy IS NOT NULL";
266        } elseif ($customerType === 'sellers') {
267            $sql .= " AND lastSold IS NOT NULL";
268        }
269
270        $sql .= " LIMIT :max_customers";
271
272        $stmt = $storeDb->prepare($sql);
273        $stmt->bindParam(':days', $days, \PDO::PARAM_INT);
274        $stmt->bindParam(':min_rating', $minRating, \PDO::PARAM_INT);
275        $stmt->bindParam(':max_customers', $this->maxCustomersPerTrigger, \PDO::PARAM_INT);
276        $stmt->execute();
277
278        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
279    }
280
281    /**
282     * Get customers for days_since_sold trigger
283     */
284    private function getCustomersForDaysSinceSold($trigger, $config, $storeDb)
285    {
286        $days = $config['days'] ?? 30;
287        $minRating = $config['min_rating'] ?? 0;
288
289        $sql = "SELECT customerID, firstName, lastName, phone, email, lastSold
290                FROM customers
291                WHERE lastSold IS NOT NULL
292                AND DATEDIFF(CURDATE(), lastSold) = :days
293                AND phone IS NOT NULL
294                AND phone != ''
295                AND rating >= :min_rating
296                AND optInText = 1
297                LIMIT :max_customers";
298
299        $stmt = $storeDb->prepare($sql);
300        $stmt->bindParam(':days', $days, \PDO::PARAM_INT);
301        $stmt->bindParam(':min_rating', $minRating, \PDO::PARAM_INT);
302        $stmt->bindParam(':max_customers', $this->maxCustomersPerTrigger, \PDO::PARAM_INT);
303        $stmt->execute();
304
305        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
306    }
307
308    /**
309     * Get customers for birthday trigger
310     */
311    private function getCustomersForBirthday($trigger, $config, $storeDb)
312    {
313        $daysOffset = $config['days_offset'] ?? 0; // 0 = on birthday, negative = days before, positive = days after
314        $minRating = $config['min_rating'] ?? 0;
315
316        $sql = "SELECT customerID, firstName, lastName, phone, email, birthday
317                FROM customers
318                WHERE birthday IS NOT NULL
319                AND MONTH(birthday) = MONTH(CURDATE() + INTERVAL :days_offset DAY)
320                AND DAY(birthday) = DAY(CURDATE() + INTERVAL :days_offset DAY)
321                AND phone IS NOT NULL
322                AND phone != ''
323                AND rating >= :min_rating
324                AND optInText = 1
325                LIMIT :max_customers";
326
327        $stmt = $storeDb->prepare($sql);
328        $stmt->bindParam(':days_offset', $daysOffset, \PDO::PARAM_INT);
329        $stmt->bindParam(':min_rating', $minRating, \PDO::PARAM_INT);
330        $stmt->bindParam(':max_customers', $this->maxCustomersPerTrigger, \PDO::PARAM_INT);
331        $stmt->execute();
332
333        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
334    }
335
336    /**
337     * Get customers for expiring_points trigger
338     */
339    private function getCustomersForExpiringPoints($trigger, $config, $storeDb)
340    {
341        $daysUntilExpiry = $config['days_until_expiry'] ?? 30;
342        $minPoints = $config['min_points'] ?? 100;
343        $minRating = $config['min_rating'] ?? 0;
344
345        // This assumes a points_expiration column exists - adjust based on your schema
346        $sql = "SELECT customerID, firstName, lastName, phone, email, loyaltyPoints, points_expiration
347                FROM customers
348                WHERE loyaltyPoints >= :min_points
349                AND points_expiration IS NOT NULL
350                AND DATEDIFF(points_expiration, CURDATE()) = :days_until_expiry
351                AND phone IS NOT NULL
352                AND phone != ''
353                AND rating >= :min_rating
354                AND optInText = 1
355                LIMIT :max_customers";
356
357        $stmt = $storeDb->prepare($sql);
358        $stmt->bindParam(':min_points', $minPoints, \PDO::PARAM_INT);
359        $stmt->bindParam(':days_until_expiry', $daysUntilExpiry, \PDO::PARAM_INT);
360        $stmt->bindParam(':min_rating', $minRating, \PDO::PARAM_INT);
361        $stmt->bindParam(':max_customers', $this->maxCustomersPerTrigger, \PDO::PARAM_INT);
362        $stmt->execute();
363
364        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
365    }
366
367    /**
368     * Get customers for custom trigger
369     */
370    private function getCustomersForCustomTrigger($trigger, $config, $storeDb)
371    {
372        // Custom triggers can have flexible SQL queries
373        // For security, we'll use a whitelist approach with predefined criteria
374
375        $criteria = $config['criteria'] ?? [];
376        $minRating = $config['min_rating'] ?? 0;
377
378        $sql = "SELECT customerID, firstName, lastName, phone, email
379                FROM customers
380                WHERE phone IS NOT NULL
381                AND phone != ''
382                AND rating >= :min_rating
383                AND optInText = 1";
384
385        // Add custom criteria (simplified example)
386        $params = [':min_rating' => $minRating, ':max_customers' => $this->maxCustomersPerTrigger];
387
388        foreach ($criteria as $criterion) {
389            if (isset($criterion['field']) && isset($criterion['operator']) && isset($criterion['value'])) {
390                // Whitelist allowed fields for security
391                $allowedFields = ['rating', 'memberSince', 'lastVisit', 'lastBuy', 'lastSold', 'loyaltyPoints'];
392
393                if (in_array($criterion['field'], $allowedFields)) {
394                    $paramName = ':custom_' . $criterion['field'];
395                    $sql .= " AND {$criterion['field']} {$criterion['operator']} {$paramName}";
396                    $params[$paramName] = $criterion['value'];
397                }
398            }
399        }
400
401        $sql .= " LIMIT :max_customers";
402
403        $stmt = $storeDb->prepare($sql);
404        foreach ($params as $param => $value) {
405            $stmt->bindValue($param, $value);
406        }
407        $stmt->execute();
408
409        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
410    }
411
412    /**
413     * Personalize message with customer and store data
414     */
415    private function personalizeMessage($message, $customer, $store)
416    {
417        $replacements = [
418            '%customer%' => $customer['firstName'] ?? 'Valued Customer',
419            '%firstname%' => $customer['firstName'] ?? 'Valued Customer',
420            '%lastname%' => $customer['lastName'] ?? '',
421            '%company%' => $store->getCompanyName(),
422            '%store%' => $store->getCity(),
423            '%coop%' => $store->getCompanyName(),
424            '%points%' => $customer['loyaltyPoints'] ?? '0',
425            '%days%' => '' // Can be calculated based on trigger config if needed
426        ];
427
428        foreach ($replacements as $var => $value) {
429            $message = str_replace($var, $value, $message);
430        }
431
432        return $message;
433    }
434
435    /**
436     * Log message to customer communication log
437     */
438    private function logCustomerMessage($storeDb, $triggerId, $customerId, $message)
439    {
440        try {
441            $sql = "INSERT INTO seller_marketing_customer_log
442                    (trigger_id, customer_id, message, sent_at, message_type)
443                    VALUES (:trigger_id, :customer_id, :message, NOW(), 'trigger')";
444
445            $stmt = $storeDb->prepare($sql);
446            $stmt->execute([
447                ':trigger_id' => $triggerId,
448                ':customer_id' => $customerId,
449                ':message' => $message
450            ]);
451        } catch (Exception $e) {
452            // Log but don't fail the whole process
453            error_log("Failed to log customer message: " . $e->getMessage());
454        }
455    }
456
457    /**
458     * Get all active stores
459     */
460    private function getAllStores()
461    {
462        $stmt = $this->centralDb->query("SELECT id, typeNum FROM stores WHERE active = 1");
463        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
464    }
465
466    /**
467     * Get triggers that should be processed
468     */
469    private function getTriggersToProcess($storeDb)
470    {
471        // Get active triggers that haven't been processed recently
472        // Process frequency depends on trigger type
473        $sql = "SELECT * FROM seller_marketing_triggers
474                WHERE status = 'active'
475                AND (expire_date IS NULL OR expire_date >= CURDATE())
476                AND (
477                    last_processed IS NULL
478                    OR last_processed < DATE_SUB(NOW(), INTERVAL 1 HOUR)
479                )
480                ORDER BY last_processed ASC NULLS FIRST
481                LIMIT :max_triggers";
482
483        $stmt = $storeDb->prepare($sql);
484        $stmt->bindParam(':max_triggers', $this->maxTriggersPerRun, \PDO::PARAM_INT);
485        $stmt->execute();
486
487        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
488    }
489
490    /**
491     * Log processor activity
492     *
493     * Logs to central queue operations using error_log.
494     * Messages go to central seller_marketing_queue table via SmsQueue class.
495     *
496     * @param string $activity Activity type (started, completed, error)
497     * @param array $data Additional data to log
498     */
499    private function logActivity($activity, $data = [])
500    {
501        $logData = array_merge([
502            'processor' => $this->processorIdentifier,
503            'timestamp' => date('Y-m-d H:i:s'),
504            'activity' => $activity,
505            'queue_type' => 'central'  // Indicates we're using central queue
506        ], $data);
507
508        error_log("Trigger Processor: " . json_encode($logData));
509    }
510
511    /**
512     * Set maximum triggers per run
513     */
514    public function setMaxTriggersPerRun($max)
515    {
516        $this->maxTriggersPerRun = $max;
517    }
518
519    /**
520     * Set maximum customers per trigger
521     */
522    public function setMaxCustomersPerTrigger($max)
523    {
524        $this->maxCustomersPerTrigger = $max;
525    }
526
527    /**
528     * Get processor identifier
529     */
530    public function getProcessorIdentifier()
531    {
532        return $this->processorIdentifier;
533    }
534}