Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 578
0.00%
0 / 28
 CRAP
0.00%
0 / 1
EventReportService
0.00%
0 / 578
0.00%
0 / 28
 2862
0.00%
0 / 1
 __construct
0.00%
0 / 3
0.00%
0 / 1
 2
 getEventMetrics
0.00%
0 / 18
0.00%
0 / 1
 2
 getSalesLift
0.00%
0 / 42
0.00%
0 / 1
 2
 getMarketingMetrics
0.00%
0 / 40
0.00%
0 / 1
 2
 getInventoryMetrics
0.00%
0 / 22
0.00%
0 / 1
 2
 getComebackCashMetrics
0.00%
0 / 30
0.00%
0 / 1
 6
 getYearOverYear
0.00%
0 / 39
0.00%
0 / 1
 6
 exportToCsv
0.00%
0 / 124
0.00%
0 / 1
 6
 getEventSummary
0.00%
0 / 7
0.00%
0 / 1
 6
 querySalesMetrics
0.00%
0 / 14
0.00%
0 / 1
 2
 querySmsBlastMetrics
0.00%
0 / 24
0.00%
0 / 1
 6
 querySmsTriggerMetrics
0.00%
0 / 24
0.00%
0 / 1
 6
 querySignageMetrics
0.00%
0 / 24
0.00%
0 / 1
 6
 queryBackstockMetrics
0.00%
0 / 24
0.00%
0 / 1
 6
 queryBuyMetrics
0.00%
0 / 14
0.00%
0 / 1
 2
 getLinkedComebackCashEvents
0.00%
0 / 5
0.00%
0 / 1
 2
 queryComebackCashIssuance
0.00%
0 / 18
0.00%
0 / 1
 6
 queryComebackCashRedemptions
0.00%
0 / 18
0.00%
0 / 1
 6
 calculateComebackCashRevenue
0.00%
0 / 21
0.00%
0 / 1
 12
 findPreviousYearEvent
0.00%
0 / 14
0.00%
0 / 1
 30
 getEventOrFail
0.00%
0 / 4
0.00%
0 / 1
 6
 getEventDateRange
0.00%
0 / 11
0.00%
0 / 1
 2
 calculateLiftPercent
0.00%
0 / 3
0.00%
0 / 1
 12
 calculatePercent
0.00%
0 / 3
0.00%
0 / 1
 6
 getEmptyComebackCashMetrics
0.00%
0 / 19
0.00%
0 / 1
 2
 csvLine
0.00%
0 / 9
0.00%
0 / 1
 30
 formatCurrency
0.00%
0 / 1
0.00%
0 / 1
 2
 formatPercent
0.00%
0 / 3
0.00%
0 / 1
 6
1<?php
2
3namespace BuyerKiosk\EventManagement\Services;
4
5use PDO;
6use DateTime;
7use DateInterval;
8use InvalidArgumentException;
9use BuyerKiosk\EventManagement\Models\Event;
10use BuyerKiosk\EventManagement\Models\EventIntegration;
11
12/**
13 * EventReportService - Generates performance metrics and reports for events
14 *
15 * Provides comprehensive reporting capabilities for completed or active events:
16 * - Sales metrics: revenue, transactions, average ticket during event vs. baseline
17 * - Marketing metrics: SMS sent/delivered/clicked, signage impressions
18 * - Inventory metrics: bins pulled, items processed, buy totals
19 * - Comeback Cash metrics: coupons issued, redeemed, revenue impact
20 * - Year-over-year comparison when historical data is available
21 * - CSV export functionality
22 *
23 * Data is aggregated from multiple tables:
24 * - buys: Sales transactions
25 * - seller_marketing_blasts: SMS campaign data
26 * - bsEvents: Backstock/inventory events
27 * - ccEvents/ccTransactions: Comeback Cash coupons
28 * - dsloop/dsslides: Digital signage data
29 *
30 * @package BuyerKiosk\EventManagement\Services
31 */
32class EventReportService
33{
34    /**
35     * @var PDO Database connection for the store
36     */
37    private PDO $db;
38
39    /**
40     * @var EventService Event service for fetching event data
41     */
42    private EventService $eventService;
43
44    /**
45     * @var IntegrationService|null Integration service for status lookups
46     */
47    private ?IntegrationService $integrationService;
48
49    /**
50     * Constructor
51     *
52     * @param PDO $db Store database connection
53     * @param EventService $eventService Event service instance
54     * @param IntegrationService|null $integrationService Integration service instance
55     */
56    public function __construct(
57        PDO $db,
58        EventService $eventService,
59        ?IntegrationService $integrationService = null
60    ) {
61        $this->db = $db;
62        $this->eventService = $eventService;
63        $this->integrationService = $integrationService;
64    }
65
66    // =========================================================================
67    // MAIN METRICS METHODS
68    // =========================================================================
69
70    /**
71     * Get all event metrics combined
72     *
73     * Returns a comprehensive summary including sales, marketing, inventory,
74     * and Comeback Cash metrics for the specified event.
75     *
76     * @param int $eventId Event ID to generate report for
77     * @return array Complete metrics summary
78     * @throws InvalidArgumentException If event not found
79     */
80    public function getEventMetrics(int $eventId): array
81    {
82        $event = $this->getEventOrFail($eventId);
83        $dateRange = $this->getEventDateRange($event);
84
85        return [
86            'eventId' => $eventId,
87            'eventName' => $event->name,
88            'eventType' => $event->eventType,
89            'status' => $event->status,
90            'dateRange' => [
91                'startDate' => $dateRange['startDate'],
92                'endDate' => $dateRange['endDate'],
93                'durationDays' => $dateRange['durationDays'],
94            ],
95            'sales' => $this->getSalesLift($eventId),
96            'marketing' => $this->getMarketingMetrics($eventId),
97            'inventory' => $this->getInventoryMetrics($eventId),
98            'comebackCash' => $this->getComebackCashMetrics($eventId),
99            'generatedAt' => (new DateTime())->format('Y-m-d H:i:s'),
100        ];
101    }
102
103    /**
104     * Get sales lift metrics comparing event period to baseline
105     *
106     * Calculates sales performance during the event and compares it to
107     * a baseline period (same duration before the event started).
108     *
109     * @param int $eventId Event ID
110     * @return array Sales metrics with lift calculations
111     * @throws InvalidArgumentException If event not found
112     */
113    public function getSalesLift(int $eventId): array
114    {
115        $event = $this->getEventOrFail($eventId);
116        $dateRange = $this->getEventDateRange($event);
117
118        // Event period metrics
119        $eventSales = $this->querySalesMetrics(
120            $dateRange['startDate'],
121            $dateRange['endDate']
122        );
123
124        // Baseline period: same duration before event start
125        $baselineEnd = (new DateTime($dateRange['startDate']))->sub(new DateInterval('P1D'))->format('Y-m-d');
126        $baselineStart = (new DateTime($baselineEnd))
127            ->sub(new DateInterval("P{$dateRange['durationDays']}D"))
128            ->format('Y-m-d');
129
130        $baselineSales = $this->querySalesMetrics($baselineStart, $baselineEnd);
131
132        // Calculate lift percentages
133        $revenueLift = $this->calculateLiftPercent($eventSales['revenue'], $baselineSales['revenue']);
134        $transactionLift = $this->calculateLiftPercent($eventSales['transactions'], $baselineSales['transactions']);
135        $avgTicketLift = $this->calculateLiftPercent($eventSales['averageTicket'], $baselineSales['averageTicket']);
136
137        return [
138            'eventPeriod' => [
139                'startDate' => $dateRange['startDate'],
140                'endDate' => $dateRange['endDate'],
141                'durationDays' => $dateRange['durationDays'],
142                'revenue' => $eventSales['revenue'],
143                'transactions' => $eventSales['transactions'],
144                'averageTicket' => $eventSales['averageTicket'],
145                'itemsSold' => $eventSales['itemsSold'],
146            ],
147            'baselinePeriod' => [
148                'startDate' => $baselineStart,
149                'endDate' => $baselineEnd,
150                'durationDays' => $dateRange['durationDays'],
151                'revenue' => $baselineSales['revenue'],
152                'transactions' => $baselineSales['transactions'],
153                'averageTicket' => $baselineSales['averageTicket'],
154                'itemsSold' => $baselineSales['itemsSold'],
155            ],
156            'lift' => [
157                'revenuePercent' => $revenueLift,
158                'revenueAbsolute' => $eventSales['revenue'] - $baselineSales['revenue'],
159                'transactionPercent' => $transactionLift,
160                'transactionAbsolute' => $eventSales['transactions'] - $baselineSales['transactions'],
161                'averageTicketPercent' => $avgTicketLift,
162                'averageTicketAbsolute' => $eventSales['averageTicket'] - $baselineSales['averageTicket'],
163            ],
164        ];
165    }
166
167    /**
168     * Get marketing metrics for the event
169     *
170     * Aggregates SMS campaign data and digital signage impressions
171     * for all integrations linked to the event.
172     *
173     * @param int $eventId Event ID
174     * @return array Marketing metrics
175     * @throws InvalidArgumentException If event not found
176     */
177    public function getMarketingMetrics(int $eventId): array
178    {
179        $event = $this->getEventOrFail($eventId);
180        $dateRange = $this->getEventDateRange($event);
181
182        // Get SMS blast metrics
183        $smsBlasts = $this->querySmsBlastMetrics($eventId, $dateRange);
184
185        // Get SMS trigger metrics
186        $smsTriggers = $this->querySmsTriggerMetrics($eventId, $dateRange);
187
188        // Get signage metrics
189        $signage = $this->querySignageMetrics($eventId, $dateRange);
190
191        // Combine SMS metrics
192        $totalSmsSent = $smsBlasts['sent'] + $smsTriggers['sent'];
193        $totalSmsDelivered = $smsBlasts['delivered'] + $smsTriggers['delivered'];
194        $totalSmsClicked = $smsBlasts['clicked'] + $smsTriggers['clicked'];
195
196        return [
197            'sms' => [
198                'blasts' => [
199                    'campaigns' => $smsBlasts['campaigns'],
200                    'sent' => $smsBlasts['sent'],
201                    'delivered' => $smsBlasts['delivered'],
202                    'deliveryRate' => $this->calculatePercent($smsBlasts['delivered'], $smsBlasts['sent']),
203                    'clicked' => $smsBlasts['clicked'],
204                    'clickRate' => $this->calculatePercent($smsBlasts['clicked'], $smsBlasts['delivered']),
205                ],
206                'triggers' => [
207                    'activeTriggers' => $smsTriggers['activeTriggers'],
208                    'sent' => $smsTriggers['sent'],
209                    'delivered' => $smsTriggers['delivered'],
210                    'deliveryRate' => $this->calculatePercent($smsTriggers['delivered'], $smsTriggers['sent']),
211                    'clicked' => $smsTriggers['clicked'],
212                    'clickRate' => $this->calculatePercent($smsTriggers['clicked'], $smsTriggers['delivered']),
213                ],
214                'totals' => [
215                    'sent' => $totalSmsSent,
216                    'delivered' => $totalSmsDelivered,
217                    'deliveryRate' => $this->calculatePercent($totalSmsDelivered, $totalSmsSent),
218                    'clicked' => $totalSmsClicked,
219                    'clickRate' => $this->calculatePercent($totalSmsClicked, $totalSmsDelivered),
220                ],
221            ],
222            'signage' => [
223                'slides' => $signage['slides'],
224                'impressions' => $signage['impressions'],
225                'displayHours' => $signage['displayHours'],
226            ],
227        ];
228    }
229
230    /**
231     * Get inventory metrics for the event
232     *
233     * Aggregates backstock activity including bin pulls, items processed,
234     * and buy totals for all backstock integrations linked to the event.
235     *
236     * @param int $eventId Event ID
237     * @return array Inventory metrics
238     * @throws InvalidArgumentException If event not found
239     */
240    public function getInventoryMetrics(int $eventId): array
241    {
242        $event = $this->getEventOrFail($eventId);
243        $dateRange = $this->getEventDateRange($event);
244
245        // Query backstock events linked to this event
246        $backstockMetrics = $this->queryBackstockMetrics($eventId, $dateRange);
247
248        // Query buy totals during event period
249        $buyMetrics = $this->queryBuyMetrics($dateRange);
250
251        return [
252            'backstock' => [
253                'events' => $backstockMetrics['events'],
254                'binsPulled' => $backstockMetrics['binsPulled'],
255                'itemsProcessed' => $backstockMetrics['itemsProcessed'],
256                'itemsSold' => $backstockMetrics['itemsSold'],
257                'sellThroughRate' => $this->calculatePercent(
258                    $backstockMetrics['itemsSold'],
259                    $backstockMetrics['itemsProcessed']
260                ),
261            ],
262            'buying' => [
263                'totalBuys' => $buyMetrics['totalBuys'],
264                'totalItems' => $buyMetrics['totalItems'],
265                'totalPaidOut' => $buyMetrics['totalPaidOut'],
266                'averageBuyValue' => $buyMetrics['averageBuyValue'],
267            ],
268        ];
269    }
270
271    /**
272     * Get Comeback Cash metrics for the event
273     *
274     * Tracks coupon issuance, redemption, and revenue impact for
275     * all Comeback Cash integrations linked to the event.
276     *
277     * @param int $eventId Event ID
278     * @return array Comeback Cash metrics
279     * @throws InvalidArgumentException If event not found
280     */
281    public function getComebackCashMetrics(int $eventId): array
282    {
283        $event = $this->getEventOrFail($eventId);
284        $dateRange = $this->getEventDateRange($event);
285
286        // Get linked Comeback Cash events
287        $ccEventIds = $this->getLinkedComebackCashEvents($eventId);
288
289        if (empty($ccEventIds)) {
290            return $this->getEmptyComebackCashMetrics();
291        }
292
293        // Query coupon issuance
294        $issuance = $this->queryComebackCashIssuance($ccEventIds, $dateRange);
295
296        // Query redemptions
297        $redemptions = $this->queryComebackCashRedemptions($ccEventIds);
298
299        // Calculate revenue impact
300        $revenueImpact = $this->calculateComebackCashRevenue($ccEventIds);
301
302        return [
303            'events' => count($ccEventIds),
304            'issuance' => [
305                'couponsIssued' => $issuance['couponsIssued'],
306                'totalValue' => $issuance['totalValue'],
307                'uniqueCustomers' => $issuance['uniqueCustomers'],
308            ],
309            'redemptions' => [
310                'couponsRedeemed' => $redemptions['couponsRedeemed'],
311                'totalValueRedeemed' => $redemptions['totalValueRedeemed'],
312                'redemptionRate' => $this->calculatePercent(
313                    $redemptions['couponsRedeemed'],
314                    $issuance['couponsIssued']
315                ),
316                'uniqueRedemptions' => $redemptions['uniqueRedemptions'],
317            ],
318            'revenueImpact' => [
319                'revenueFromRedemptions' => $revenueImpact['revenueFromRedemptions'],
320                'averageTransactionValue' => $revenueImpact['averageTransactionValue'],
321                'roi' => $revenueImpact['roi'],
322            ],
323        ];
324    }
325
326    /**
327     * Get year-over-year comparison for the event
328     *
329     * Compares current event performance to the same event from the
330     * previous year, if historical data exists.
331     *
332     * @param int $eventId Event ID
333     * @return array|null YoY comparison data, or null if no historical data
334     * @throws InvalidArgumentException If event not found
335     */
336    public function getYearOverYear(int $eventId): ?array
337    {
338        $event = $this->getEventOrFail($eventId);
339
340        // Find the source event or previous year's instance
341        $previousYear = $event->year - 1;
342        $previousEvent = $this->findPreviousYearEvent($event, $previousYear);
343
344        if ($previousEvent === null) {
345            return null;
346        }
347
348        // Get metrics for both years
349        $currentMetrics = $this->getSalesLift($eventId);
350        $previousMetrics = $this->getSalesLift($previousEvent->id);
351
352        // Calculate YoY changes
353        $currentRevenue = $currentMetrics['eventPeriod']['revenue'];
354        $previousRevenue = $previousMetrics['eventPeriod']['revenue'];
355
356        $currentTransactions = $currentMetrics['eventPeriod']['transactions'];
357        $previousTransactions = $previousMetrics['eventPeriod']['transactions'];
358
359        $currentAvgTicket = $currentMetrics['eventPeriod']['averageTicket'];
360        $previousAvgTicket = $previousMetrics['eventPeriod']['averageTicket'];
361
362        return [
363            'currentYear' => [
364                'year' => $event->year,
365                'eventId' => $eventId,
366                'eventName' => $event->name,
367                'revenue' => $currentRevenue,
368                'transactions' => $currentTransactions,
369                'averageTicket' => $currentAvgTicket,
370            ],
371            'previousYear' => [
372                'year' => $previousYear,
373                'eventId' => $previousEvent->id,
374                'eventName' => $previousEvent->name,
375                'revenue' => $previousRevenue,
376                'transactions' => $previousTransactions,
377                'averageTicket' => $previousAvgTicket,
378            ],
379            'yoyChange' => [
380                'revenuePercent' => $this->calculateLiftPercent($currentRevenue, $previousRevenue),
381                'revenueAbsolute' => $currentRevenue - $previousRevenue,
382                'transactionPercent' => $this->calculateLiftPercent($currentTransactions, $previousTransactions),
383                'transactionAbsolute' => $currentTransactions - $previousTransactions,
384                'averageTicketPercent' => $this->calculateLiftPercent($currentAvgTicket, $previousAvgTicket),
385                'averageTicketAbsolute' => $currentAvgTicket - $previousAvgTicket,
386            ],
387        ];
388    }
389
390    /**
391     * Export event metrics to CSV format
392     *
393     * Generates a CSV string containing all event metrics suitable
394     * for download or further analysis.
395     *
396     * @param int $eventId Event ID
397     * @return string CSV formatted data
398     * @throws InvalidArgumentException If event not found
399     */
400    public function exportToCsv(int $eventId): string
401    {
402        $metrics = $this->getEventMetrics($eventId);
403        $yoy = $this->getYearOverYear($eventId);
404
405        $lines = [];
406
407        // Header section
408        $lines[] = $this->csvLine(['Event Report']);
409        $lines[] = $this->csvLine(['Generated', $metrics['generatedAt']]);
410        $lines[] = $this->csvLine([]);
411
412        // Event information
413        $lines[] = $this->csvLine(['Event Information']);
414        $lines[] = $this->csvLine(['Event ID', $metrics['eventId']]);
415        $lines[] = $this->csvLine(['Event Name', $metrics['eventName']]);
416        $lines[] = $this->csvLine(['Event Type', $metrics['eventType']]);
417        $lines[] = $this->csvLine(['Status', $metrics['status']]);
418        $lines[] = $this->csvLine(['Start Date', $metrics['dateRange']['startDate']]);
419        $lines[] = $this->csvLine(['End Date', $metrics['dateRange']['endDate']]);
420        $lines[] = $this->csvLine(['Duration (Days)', $metrics['dateRange']['durationDays']]);
421        $lines[] = $this->csvLine([]);
422
423        // Sales metrics
424        $lines[] = $this->csvLine(['Sales Metrics']);
425        $lines[] = $this->csvLine(['Metric', 'Event Period', 'Baseline Period', 'Lift %', 'Lift Absolute']);
426        $sales = $metrics['sales'];
427        $lines[] = $this->csvLine([
428            'Revenue',
429            $this->formatCurrency($sales['eventPeriod']['revenue']),
430            $this->formatCurrency($sales['baselinePeriod']['revenue']),
431            $this->formatPercent($sales['lift']['revenuePercent']),
432            $this->formatCurrency($sales['lift']['revenueAbsolute']),
433        ]);
434        $lines[] = $this->csvLine([
435            'Transactions',
436            $sales['eventPeriod']['transactions'],
437            $sales['baselinePeriod']['transactions'],
438            $this->formatPercent($sales['lift']['transactionPercent']),
439            $sales['lift']['transactionAbsolute'],
440        ]);
441        $lines[] = $this->csvLine([
442            'Average Ticket',
443            $this->formatCurrency($sales['eventPeriod']['averageTicket']),
444            $this->formatCurrency($sales['baselinePeriod']['averageTicket']),
445            $this->formatPercent($sales['lift']['averageTicketPercent']),
446            $this->formatCurrency($sales['lift']['averageTicketAbsolute']),
447        ]);
448        $lines[] = $this->csvLine([
449            'Items Sold',
450            $sales['eventPeriod']['itemsSold'],
451            $sales['baselinePeriod']['itemsSold'],
452            '',
453            '',
454        ]);
455        $lines[] = $this->csvLine([]);
456
457        // Marketing metrics
458        $lines[] = $this->csvLine(['Marketing Metrics']);
459        $lines[] = $this->csvLine(['SMS Blasts']);
460        $sms = $metrics['marketing']['sms'];
461        $lines[] = $this->csvLine(['Campaigns', $sms['blasts']['campaigns']]);
462        $lines[] = $this->csvLine(['Messages Sent', $sms['blasts']['sent']]);
463        $lines[] = $this->csvLine(['Messages Delivered', $sms['blasts']['delivered']]);
464        $lines[] = $this->csvLine(['Delivery Rate', $this->formatPercent($sms['blasts']['deliveryRate'])]);
465        $lines[] = $this->csvLine(['Clicks', $sms['blasts']['clicked']]);
466        $lines[] = $this->csvLine(['Click Rate', $this->formatPercent($sms['blasts']['clickRate'])]);
467        $lines[] = $this->csvLine([]);
468
469        $lines[] = $this->csvLine(['SMS Triggers']);
470        $lines[] = $this->csvLine(['Active Triggers', $sms['triggers']['activeTriggers']]);
471        $lines[] = $this->csvLine(['Messages Sent', $sms['triggers']['sent']]);
472        $lines[] = $this->csvLine(['Messages Delivered', $sms['triggers']['delivered']]);
473        $lines[] = $this->csvLine(['Delivery Rate', $this->formatPercent($sms['triggers']['deliveryRate'])]);
474        $lines[] = $this->csvLine(['Clicks', $sms['triggers']['clicked']]);
475        $lines[] = $this->csvLine(['Click Rate', $this->formatPercent($sms['triggers']['clickRate'])]);
476        $lines[] = $this->csvLine([]);
477
478        $lines[] = $this->csvLine(['Digital Signage']);
479        $signage = $metrics['marketing']['signage'];
480        $lines[] = $this->csvLine(['Slides Displayed', $signage['slides']]);
481        $lines[] = $this->csvLine(['Impressions', $signage['impressions']]);
482        $lines[] = $this->csvLine(['Display Hours', $signage['displayHours']]);
483        $lines[] = $this->csvLine([]);
484
485        // Inventory metrics
486        $lines[] = $this->csvLine(['Inventory Metrics']);
487        $lines[] = $this->csvLine(['Backstock Activity']);
488        $inventory = $metrics['inventory'];
489        $lines[] = $this->csvLine(['Backstock Events', $inventory['backstock']['events']]);
490        $lines[] = $this->csvLine(['Bins Pulled', $inventory['backstock']['binsPulled']]);
491        $lines[] = $this->csvLine(['Items Processed', $inventory['backstock']['itemsProcessed']]);
492        $lines[] = $this->csvLine(['Items Sold', $inventory['backstock']['itemsSold']]);
493        $lines[] = $this->csvLine(['Sell-Through Rate', $this->formatPercent($inventory['backstock']['sellThroughRate'])]);
494        $lines[] = $this->csvLine([]);
495
496        $lines[] = $this->csvLine(['Buying Activity']);
497        $lines[] = $this->csvLine(['Total Buys', $inventory['buying']['totalBuys']]);
498        $lines[] = $this->csvLine(['Total Items', $inventory['buying']['totalItems']]);
499        $lines[] = $this->csvLine(['Total Paid Out', $this->formatCurrency($inventory['buying']['totalPaidOut'])]);
500        $lines[] = $this->csvLine(['Average Buy Value', $this->formatCurrency($inventory['buying']['averageBuyValue'])]);
501        $lines[] = $this->csvLine([]);
502
503        // Comeback Cash metrics
504        $lines[] = $this->csvLine(['Comeback Cash Metrics']);
505        $cc = $metrics['comebackCash'];
506        $lines[] = $this->csvLine(['Linked Events', $cc['events']]);
507        $lines[] = $this->csvLine(['Coupons Issued', $cc['issuance']['couponsIssued']]);
508        $lines[] = $this->csvLine(['Total Value Issued', $this->formatCurrency($cc['issuance']['totalValue'])]);
509        $lines[] = $this->csvLine(['Unique Customers', $cc['issuance']['uniqueCustomers']]);
510        $lines[] = $this->csvLine(['Coupons Redeemed', $cc['redemptions']['couponsRedeemed']]);
511        $lines[] = $this->csvLine(['Total Value Redeemed', $this->formatCurrency($cc['redemptions']['totalValueRedeemed'])]);
512        $lines[] = $this->csvLine(['Redemption Rate', $this->formatPercent($cc['redemptions']['redemptionRate'])]);
513        $lines[] = $this->csvLine(['Revenue from Redemptions', $this->formatCurrency($cc['revenueImpact']['revenueFromRedemptions'])]);
514        $lines[] = $this->csvLine(['Average Transaction', $this->formatCurrency($cc['revenueImpact']['averageTransactionValue'])]);
515        $lines[] = $this->csvLine(['ROI', $this->formatPercent($cc['revenueImpact']['roi'])]);
516        $lines[] = $this->csvLine([]);
517
518        // Year-over-year comparison if available
519        if ($yoy !== null) {
520            $lines[] = $this->csvLine(['Year-over-Year Comparison']);
521            $lines[] = $this->csvLine(['Metric', 'Current Year (' . $yoy['currentYear']['year'] . ')', 'Previous Year (' . $yoy['previousYear']['year'] . ')', 'YoY Change %', 'YoY Change Absolute']);
522            $lines[] = $this->csvLine([
523                'Revenue',
524                $this->formatCurrency($yoy['currentYear']['revenue']),
525                $this->formatCurrency($yoy['previousYear']['revenue']),
526                $this->formatPercent($yoy['yoyChange']['revenuePercent']),
527                $this->formatCurrency($yoy['yoyChange']['revenueAbsolute']),
528            ]);
529            $lines[] = $this->csvLine([
530                'Transactions',
531                $yoy['currentYear']['transactions'],
532                $yoy['previousYear']['transactions'],
533                $this->formatPercent($yoy['yoyChange']['transactionPercent']),
534                $yoy['yoyChange']['transactionAbsolute'],
535            ]);
536            $lines[] = $this->csvLine([
537                'Average Ticket',
538                $this->formatCurrency($yoy['currentYear']['averageTicket']),
539                $this->formatCurrency($yoy['previousYear']['averageTicket']),
540                $this->formatPercent($yoy['yoyChange']['averageTicketPercent']),
541                $this->formatCurrency($yoy['yoyChange']['averageTicketAbsolute']),
542            ]);
543        }
544
545        return implode("\n", $lines);
546    }
547
548    /**
549     * Get comprehensive event summary with all metrics combined
550     *
551     * Alias for getEventMetrics with optional year-over-year data included.
552     *
553     * @param int $eventId Event ID
554     * @return array Complete event summary
555     * @throws InvalidArgumentException If event not found
556     */
557    public function getEventSummary(int $eventId): array
558    {
559        $metrics = $this->getEventMetrics($eventId);
560        $yoy = $this->getYearOverYear($eventId);
561
562        $metrics['yearOverYear'] = $yoy;
563
564        // Add integration status if service is available
565        if ($this->integrationService !== null) {
566            $metrics['integrationStatus'] = $this->integrationService->getAggregatedStatus($eventId);
567            $metrics['integrationCounts'] = $this->integrationService->getStatusCounts($eventId);
568        }
569
570        return $metrics;
571    }
572
573    // =========================================================================
574    // QUERY HELPER METHODS
575    // =========================================================================
576
577    /**
578     * Query sales metrics for a date range
579     *
580     * @param string $startDate Start date (Y-m-d)
581     * @param string $endDate End date (Y-m-d)
582     * @return array Sales metrics
583     */
584    private function querySalesMetrics(string $startDate, string $endDate): array
585    {
586        $sql = "SELECT
587                    COUNT(*) as transactions,
588                    COALESCE(SUM(total), 0) as revenue,
589                    COALESCE(AVG(total), 0) as average_ticket,
590                    COALESCE(SUM(quantity), 0) as items_sold
591                FROM buys
592                WHERE DATE(created_at) BETWEEN :startDate AND :endDate
593                AND status = 'completed'";
594
595        $stmt = $this->db->prepare($sql);
596        $stmt->execute([
597            ':startDate' => $startDate,
598            ':endDate' => $endDate,
599        ]);
600
601        $row = $stmt->fetch(PDO::FETCH_ASSOC);
602
603        return [
604            'revenue' => (float) ($row['revenue'] ?? 0),
605            'transactions' => (int) ($row['transactions'] ?? 0),
606            'averageTicket' => round((float) ($row['average_ticket'] ?? 0), 2),
607            'itemsSold' => (int) ($row['items_sold'] ?? 0),
608        ];
609    }
610
611    /**
612     * Query SMS blast metrics for an event
613     *
614     * @param int $eventId Event ID
615     * @param array $dateRange Date range array
616     * @return array SMS blast metrics
617     */
618    private function querySmsBlastMetrics(int $eventId, array $dateRange): array
619    {
620        // Get linked SMS blast IDs
621        $sql = "SELECT foreign_id FROM event_integrations
622                WHERE event_id = :eventId AND integration_type = 'sms_blast'";
623        $stmt = $this->db->prepare($sql);
624        $stmt->execute([':eventId' => $eventId]);
625        $blastIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
626
627        if (empty($blastIds)) {
628            return [
629                'campaigns' => 0,
630                'sent' => 0,
631                'delivered' => 0,
632                'clicked' => 0,
633            ];
634        }
635
636        $placeholders = implode(',', array_fill(0, count($blastIds), '?'));
637        $sql = "SELECT
638                    COUNT(DISTINCT smb.id) as campaigns,
639                    COALESCE(SUM(smb.sent_count), 0) as sent,
640                    COALESCE(SUM(smb.delivered_count), 0) as delivered,
641                    COALESCE(SUM(smb.clicked_count), 0) as clicked
642                FROM seller_marketing_blasts smb
643                WHERE smb.id IN ({$placeholders})";
644
645        $stmt = $this->db->prepare($sql);
646        $stmt->execute($blastIds);
647        $row = $stmt->fetch(PDO::FETCH_ASSOC);
648
649        return [
650            'campaigns' => (int) ($row['campaigns'] ?? 0),
651            'sent' => (int) ($row['sent'] ?? 0),
652            'delivered' => (int) ($row['delivered'] ?? 0),
653            'clicked' => (int) ($row['clicked'] ?? 0),
654        ];
655    }
656
657    /**
658     * Query SMS trigger metrics for an event
659     *
660     * @param int $eventId Event ID
661     * @param array $dateRange Date range array
662     * @return array SMS trigger metrics
663     */
664    private function querySmsTriggerMetrics(int $eventId, array $dateRange): array
665    {
666        // Get linked SMS trigger IDs
667        $sql = "SELECT foreign_id FROM event_integrations
668                WHERE event_id = :eventId AND integration_type = 'sms_trigger'";
669        $stmt = $this->db->prepare($sql);
670        $stmt->execute([':eventId' => $eventId]);
671        $triggerIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
672
673        if (empty($triggerIds)) {
674            return [
675                'activeTriggers' => 0,
676                'sent' => 0,
677                'delivered' => 0,
678                'clicked' => 0,
679            ];
680        }
681
682        $placeholders = implode(',', array_fill(0, count($triggerIds), '?'));
683        $sql = "SELECT
684                    COUNT(DISTINCT smt.id) as active_triggers,
685                    COALESCE(SUM(smt.sent_count), 0) as sent,
686                    COALESCE(SUM(smt.delivered_count), 0) as delivered,
687                    COALESCE(SUM(smt.clicked_count), 0) as clicked
688                FROM seller_marketing_triggers smt
689                WHERE smt.id IN ({$placeholders})";
690
691        $stmt = $this->db->prepare($sql);
692        $stmt->execute($triggerIds);
693        $row = $stmt->fetch(PDO::FETCH_ASSOC);
694
695        return [
696            'activeTriggers' => (int) ($row['active_triggers'] ?? 0),
697            'sent' => (int) ($row['sent'] ?? 0),
698            'delivered' => (int) ($row['delivered'] ?? 0),
699            'clicked' => (int) ($row['clicked'] ?? 0),
700        ];
701    }
702
703    /**
704     * Query signage metrics for an event
705     *
706     * @param int $eventId Event ID
707     * @param array $dateRange Date range array
708     * @return array Signage metrics
709     */
710    private function querySignageMetrics(int $eventId, array $dateRange): array
711    {
712        // Get linked signage slide IDs
713        $sql = "SELECT foreign_id FROM event_integrations
714                WHERE event_id = :eventId AND integration_type = 'signage'";
715        $stmt = $this->db->prepare($sql);
716        $stmt->execute([':eventId' => $eventId]);
717        $slideIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
718
719        if (empty($slideIds)) {
720            return [
721                'slides' => 0,
722                'impressions' => 0,
723                'displayHours' => 0,
724            ];
725        }
726
727        $placeholders = implode(',', array_fill(0, count($slideIds), '?'));
728
729        // Query slide display data from dsloop
730        $sql = "SELECT
731                    COUNT(DISTINCT dsl.slide_id) as slides,
732                    COALESCE(SUM(dsl.display_count), 0) as impressions,
733                    COALESCE(SUM(dsl.display_seconds) / 3600, 0) as display_hours
734                FROM dsloop dsl
735                WHERE dsl.slide_id IN ({$placeholders})
736                AND DATE(dsl.displayed_at) BETWEEN ? AND ?";
737
738        $params = array_merge($slideIds, [$dateRange['startDate'], $dateRange['endDate']]);
739        $stmt = $this->db->prepare($sql);
740        $stmt->execute($params);
741        $row = $stmt->fetch(PDO::FETCH_ASSOC);
742
743        return [
744            'slides' => (int) ($row['slides'] ?? count($slideIds)),
745            'impressions' => (int) ($row['impressions'] ?? 0),
746            'displayHours' => round((float) ($row['display_hours'] ?? 0), 1),
747        ];
748    }
749
750    /**
751     * Query backstock metrics for an event
752     *
753     * @param int $eventId Event ID
754     * @param array $dateRange Date range array
755     * @return array Backstock metrics
756     */
757    private function queryBackstockMetrics(int $eventId, array $dateRange): array
758    {
759        // Get linked backstock event IDs
760        $sql = "SELECT foreign_id FROM event_integrations
761                WHERE event_id = :eventId AND integration_type = 'backstock'";
762        $stmt = $this->db->prepare($sql);
763        $stmt->execute([':eventId' => $eventId]);
764        $bsEventIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
765
766        if (empty($bsEventIds)) {
767            return [
768                'events' => 0,
769                'binsPulled' => 0,
770                'itemsProcessed' => 0,
771                'itemsSold' => 0,
772            ];
773        }
774
775        $placeholders = implode(',', array_fill(0, count($bsEventIds), '?'));
776        $sql = "SELECT
777                    COUNT(DISTINCT bse.id) as events,
778                    COALESCE(SUM(bse.bins_pulled), 0) as bins_pulled,
779                    COALESCE(SUM(bse.items_processed), 0) as items_processed,
780                    COALESCE(SUM(bse.items_sold), 0) as items_sold
781                FROM bsEvents bse
782                WHERE bse.id IN ({$placeholders})";
783
784        $stmt = $this->db->prepare($sql);
785        $stmt->execute($bsEventIds);
786        $row = $stmt->fetch(PDO::FETCH_ASSOC);
787
788        return [
789            'events' => (int) ($row['events'] ?? 0),
790            'binsPulled' => (int) ($row['bins_pulled'] ?? 0),
791            'itemsProcessed' => (int) ($row['items_processed'] ?? 0),
792            'itemsSold' => (int) ($row['items_sold'] ?? 0),
793        ];
794    }
795
796    /**
797     * Query buy metrics for a date range
798     *
799     * @param array $dateRange Date range array
800     * @return array Buy metrics
801     */
802    private function queryBuyMetrics(array $dateRange): array
803    {
804        $sql = "SELECT
805                    COUNT(*) as total_buys,
806                    COALESCE(SUM(quantity), 0) as total_items,
807                    COALESCE(SUM(amount_paid), 0) as total_paid_out,
808                    COALESCE(AVG(amount_paid), 0) as average_buy_value
809                FROM buys
810                WHERE DATE(created_at) BETWEEN :startDate AND :endDate
811                AND type = 'buy'";
812
813        $stmt = $this->db->prepare($sql);
814        $stmt->execute([
815            ':startDate' => $dateRange['startDate'],
816            ':endDate' => $dateRange['endDate'],
817        ]);
818
819        $row = $stmt->fetch(PDO::FETCH_ASSOC);
820
821        return [
822            'totalBuys' => (int) ($row['total_buys'] ?? 0),
823            'totalItems' => (int) ($row['total_items'] ?? 0),
824            'totalPaidOut' => (float) ($row['total_paid_out'] ?? 0),
825            'averageBuyValue' => round((float) ($row['average_buy_value'] ?? 0), 2),
826        ];
827    }
828
829    /**
830     * Get linked Comeback Cash event IDs for an event
831     *
832     * @param int $eventId Event ID
833     * @return array Array of ccEvent IDs
834     */
835    private function getLinkedComebackCashEvents(int $eventId): array
836    {
837        $sql = "SELECT foreign_id FROM event_integrations
838                WHERE event_id = :eventId AND integration_type = 'comeback_cash'";
839        $stmt = $this->db->prepare($sql);
840        $stmt->execute([':eventId' => $eventId]);
841        return $stmt->fetchAll(PDO::FETCH_COLUMN);
842    }
843
844    /**
845     * Query Comeback Cash coupon issuance
846     *
847     * @param array $ccEventIds Comeback Cash event IDs
848     * @param array $dateRange Date range array
849     * @return array Issuance metrics
850     */
851    private function queryComebackCashIssuance(array $ccEventIds, array $dateRange): array
852    {
853        if (empty($ccEventIds)) {
854            return [
855                'couponsIssued' => 0,
856                'totalValue' => 0,
857                'uniqueCustomers' => 0,
858            ];
859        }
860
861        $placeholders = implode(',', array_fill(0, count($ccEventIds), '?'));
862        $sql = "SELECT
863                    COUNT(*) as coupons_issued,
864                    COALESCE(SUM(amount), 0) as total_value,
865                    COUNT(DISTINCT customer_id) as unique_customers
866                FROM ccTransactions
867                WHERE ccEvent_id IN ({$placeholders})
868                AND transaction_type = 'issued'";
869
870        $stmt = $this->db->prepare($sql);
871        $stmt->execute($ccEventIds);
872        $row = $stmt->fetch(PDO::FETCH_ASSOC);
873
874        return [
875            'couponsIssued' => (int) ($row['coupons_issued'] ?? 0),
876            'totalValue' => (float) ($row['total_value'] ?? 0),
877            'uniqueCustomers' => (int) ($row['unique_customers'] ?? 0),
878        ];
879    }
880
881    /**
882     * Query Comeback Cash coupon redemptions
883     *
884     * @param array $ccEventIds Comeback Cash event IDs
885     * @return array Redemption metrics
886     */
887    private function queryComebackCashRedemptions(array $ccEventIds): array
888    {
889        if (empty($ccEventIds)) {
890            return [
891                'couponsRedeemed' => 0,
892                'totalValueRedeemed' => 0,
893                'uniqueRedemptions' => 0,
894            ];
895        }
896
897        $placeholders = implode(',', array_fill(0, count($ccEventIds), '?'));
898        $sql = "SELECT
899                    COUNT(*) as coupons_redeemed,
900                    COALESCE(SUM(amount), 0) as total_value_redeemed,
901                    COUNT(DISTINCT customer_id) as unique_redemptions
902                FROM ccTransactions
903                WHERE ccEvent_id IN ({$placeholders})
904                AND transaction_type = 'redeemed'";
905
906        $stmt = $this->db->prepare($sql);
907        $stmt->execute($ccEventIds);
908        $row = $stmt->fetch(PDO::FETCH_ASSOC);
909
910        return [
911            'couponsRedeemed' => (int) ($row['coupons_redeemed'] ?? 0),
912            'totalValueRedeemed' => (float) ($row['total_value_redeemed'] ?? 0),
913            'uniqueRedemptions' => (int) ($row['unique_redemptions'] ?? 0),
914        ];
915    }
916
917    /**
918     * Calculate Comeback Cash revenue impact
919     *
920     * @param array $ccEventIds Comeback Cash event IDs
921     * @return array Revenue impact metrics
922     */
923    private function calculateComebackCashRevenue(array $ccEventIds): array
924    {
925        if (empty($ccEventIds)) {
926            return [
927                'revenueFromRedemptions' => 0,
928                'averageTransactionValue' => 0,
929                'roi' => 0,
930            ];
931        }
932
933        $placeholders = implode(',', array_fill(0, count($ccEventIds), '?'));
934
935        // Get redemption transactions with associated sale amounts
936        $sql = "SELECT
937                    COALESCE(SUM(cct.sale_total), 0) as revenue_from_redemptions,
938                    COALESCE(AVG(cct.sale_total), 0) as average_transaction_value,
939                    COALESCE(SUM(cct.amount), 0) as coupon_cost
940                FROM ccTransactions cct
941                WHERE cct.ccEvent_id IN ({$placeholders})
942                AND cct.transaction_type = 'redeemed'
943                AND cct.sale_total IS NOT NULL";
944
945        $stmt = $this->db->prepare($sql);
946        $stmt->execute($ccEventIds);
947        $row = $stmt->fetch(PDO::FETCH_ASSOC);
948
949        $revenue = (float) ($row['revenue_from_redemptions'] ?? 0);
950        $couponCost = (float) ($row['coupon_cost'] ?? 0);
951
952        // Calculate ROI: (Revenue - Cost) / Cost * 100
953        $roi = $couponCost > 0 ? (($revenue - $couponCost) / $couponCost) * 100 : 0;
954
955        return [
956            'revenueFromRedemptions' => $revenue,
957            'averageTransactionValue' => round((float) ($row['average_transaction_value'] ?? 0), 2),
958            'roi' => round($roi, 1),
959        ];
960    }
961
962    /**
963     * Find previous year's event for YoY comparison
964     *
965     * @param Event $event Current event
966     * @param int $previousYear Previous year to search
967     * @return Event|null Previous year's event or null
968     */
969    private function findPreviousYearEvent(Event $event, int $previousYear): ?Event
970    {
971        // First try to find by source_event_id chain
972        if ($event->sourceEventId !== null) {
973            $sourceEvent = $this->eventService->get($event->sourceEventId);
974            if ($sourceEvent !== null && $sourceEvent->year === $previousYear) {
975                return $sourceEvent;
976            }
977        }
978
979        // Search by name and type for the previous year
980        $sql = "SELECT * FROM events
981                WHERE name = :name
982                AND eventType = :eventType
983                AND year = :year
984                AND status NOT IN ('cancelled', 'archived')
985                LIMIT 1";
986
987        $stmt = $this->db->prepare($sql);
988        $stmt->execute([
989            ':name' => $event->name,
990            ':eventType' => $event->eventType,
991            ':year' => $previousYear,
992        ]);
993
994        $row = $stmt->fetch(PDO::FETCH_ASSOC);
995
996        return $row ? Event::fromRow($row) : null;
997    }
998
999    // =========================================================================
1000    // UTILITY HELPER METHODS
1001    // =========================================================================
1002
1003    /**
1004     * Get event or throw exception if not found
1005     *
1006     * @param int $eventId Event ID
1007     * @return Event Event instance
1008     * @throws InvalidArgumentException If event not found
1009     */
1010    private function getEventOrFail(int $eventId): Event
1011    {
1012        $event = $this->eventService->get($eventId);
1013        if ($event === null) {
1014            throw new InvalidArgumentException("Event not found: {$eventId}");
1015        }
1016        return $event;
1017    }
1018
1019    /**
1020     * Get date range for an event
1021     *
1022     * @param Event $event Event instance
1023     * @return array Date range with startDate, endDate, durationDays
1024     */
1025    private function getEventDateRange(Event $event): array
1026    {
1027        $startDate = $event->startDate?->format('Y-m-d') ?? date('Y-m-d');
1028        $endDate = $event->endDate?->format('Y-m-d') ?? date('Y-m-d');
1029
1030        $start = new DateTime($startDate);
1031        $end = new DateTime($endDate);
1032        $diff = $start->diff($end);
1033        $durationDays = $diff->days + 1; // Include both start and end days
1034
1035        return [
1036            'startDate' => $startDate,
1037            'endDate' => $endDate,
1038            'durationDays' => $durationDays,
1039        ];
1040    }
1041
1042    /**
1043     * Calculate lift percentage between two values
1044     *
1045     * @param float $current Current value
1046     * @param float $baseline Baseline value
1047     * @return float|null Lift percentage or null if baseline is zero
1048     */
1049    private function calculateLiftPercent(float $current, float $baseline): ?float
1050    {
1051        if ($baseline == 0) {
1052            return $current > 0 ? 100.0 : null;
1053        }
1054        return round((($current - $baseline) / $baseline) * 100, 1);
1055    }
1056
1057    /**
1058     * Calculate percentage
1059     *
1060     * @param float $numerator Numerator value
1061     * @param float $denominator Denominator value
1062     * @return float|null Percentage or null if denominator is zero
1063     */
1064    private function calculatePercent(float $numerator, float $denominator): ?float
1065    {
1066        if ($denominator == 0) {
1067            return null;
1068        }
1069        return round(($numerator / $denominator) * 100, 1);
1070    }
1071
1072    /**
1073     * Get empty Comeback Cash metrics structure
1074     *
1075     * @return array Empty metrics structure
1076     */
1077    private function getEmptyComebackCashMetrics(): array
1078    {
1079        return [
1080            'events' => 0,
1081            'issuance' => [
1082                'couponsIssued' => 0,
1083                'totalValue' => 0,
1084                'uniqueCustomers' => 0,
1085            ],
1086            'redemptions' => [
1087                'couponsRedeemed' => 0,
1088                'totalValueRedeemed' => 0,
1089                'redemptionRate' => null,
1090                'uniqueRedemptions' => 0,
1091            ],
1092            'revenueImpact' => [
1093                'revenueFromRedemptions' => 0,
1094                'averageTransactionValue' => 0,
1095                'roi' => 0,
1096            ],
1097        ];
1098    }
1099
1100    /**
1101     * Create a CSV line from array values
1102     *
1103     * @param array $values Values to format as CSV line
1104     * @return string CSV formatted line
1105     */
1106    private function csvLine(array $values): string
1107    {
1108        $escaped = array_map(function ($value) {
1109            if ($value === null) {
1110                return '';
1111            }
1112            $value = (string) $value;
1113            // Escape quotes and wrap in quotes if contains comma, quote, or newline
1114            if (strpos($value, ',') !== false || strpos($value, '"') !== false || strpos($value, "\n") !== false) {
1115                return '"' . str_replace('"', '""', $value) . '"';
1116            }
1117            return $value;
1118        }, $values);
1119
1120        return implode(',', $escaped);
1121    }
1122
1123    /**
1124     * Format currency value for display
1125     *
1126     * @param float $value Currency value
1127     * @return string Formatted currency string
1128     */
1129    private function formatCurrency(float $value): string
1130    {
1131        return '$' . number_format($value, 2);
1132    }
1133
1134    /**
1135     * Format percentage value for display
1136     *
1137     * @param float|null $value Percentage value
1138     * @return string Formatted percentage string
1139     */
1140    private function formatPercent(?float $value): string
1141    {
1142        if ($value === null) {
1143            return 'N/A';
1144        }
1145        return number_format($value, 1) . '%';
1146    }
1147}