Sale Notifications

Command Signature

php artisan notification:sale-one-month

Purpose

The notification:sale-one-month command processes notification settings for groups with active subscriptions to detect sales changes in wishlist products and groups. The command retrieves notification settings in chunks, dispatches background jobs to analyze sales data changes based on configured thresholds (ratio or amount), and sends notifications to group members when conditions are met. This enables automated monitoring of sales performance changes for products and product groups according to user-defined criteria.

Sequence Diagram

Step 1: Command Initialization and Settings Retrieval

sequenceDiagram
    participant System
    participant SaleCommand as notification:sale-one-month
    participant SettingRepo as NotificationSettingRepository
    participant ConsoleDB[(gb_console.notification_settings)]
    participant Logger
    participant Slack
    
    Note over System,Slack: Command Initialization (Daily Execution)
    
    rect rgb(200, 255, 200)
    Note right of System: Happy Case - Command Startup
    System->>SaleCommand: Execute Command
    SaleCommand->>Logger: Log command start
    
    SaleCommand->>SettingRepo: chunkDataSendNotification(callback, 100)
    SettingRepo->>ConsoleDB: Query WHERE group has active subscription
    ConsoleDB-->>SettingRepo: Return notification settings
    SettingRepo-->>SaleCommand: Process in chunks of 100 records
    SaleCommand->>Logger: Log chunk processing start with count
    end
    
    rect rgb(255, 200, 200)
    Note right of System: Error Handling
    rect rgb(255, 230, 230)
    alt Database Connection Error
        SettingRepo->>Logger: Log database connection error
        SettingRepo->>Slack: Send database error notification
    else No Active Subscriptions
        SettingRepo->>Logger: Log no active subscriptions found
    end
    end
    end

Step 2: Job Dispatch and Record Processing

sequenceDiagram
    participant SaleCommand as notification:sale-one-month
    participant NotificationJob as SendNotificationJob
    participant QueueSystem as Laravel Queue
    participant Logger
    participant Slack
    
    Note over SaleCommand,Slack: Batch Job Processing
    
    rect rgb(200, 255, 200)
    Note right of SaleCommand: Happy Case - Job Dispatch
    loop For each Chunk (100 records)
        SaleCommand->>QueueSystem: Dispatch SendNotificationJob(records)
        QueueSystem->>NotificationJob: Queue job for processing
        SaleCommand->>Logger: Log job dispatch with chunk size
        
        rect rgb(200, 230, 255)
        Note right of NotificationJob: Job Initialization
        NotificationJob->>NotificationJob: Initialize repositories
        NotificationJob->>Logger: Log job start with record count
        
        loop For each Record in Chunk
            NotificationJob->>NotificationJob: Determine processing type (ratio/amount)
            NotificationJob->>Logger: Log record processing start
        end
        end
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of SaleCommand: Error Handling
    rect rgb(255, 230, 230)
    alt Queue System Error
        SaleCommand->>Logger: Log queue dispatch error
        SaleCommand->>Slack: Send queue error notification
    else Job Processing Error
        NotificationJob->>Logger: Log job processing error
        NotificationJob->>Slack: Send job error notification
    end
    end
    end

Step 3: Ratio Type Processing - Product Notifications

sequenceDiagram
    participant NotificationJob as SendNotificationJob
    participant WishlistRepo as WishlistProductRepository
    participant ProductRepo as ProductRepository
    participant ProductDetailRepo as ProductDetailRepository
    participant ConsoleDB[(gb_console)]
    participant AnalyzerDB[(gb_analyzer)]
    participant Logger
    participant Slack
    
    Note over NotificationJob,Slack: Ratio Type - Product Processing
    
    rect rgb(200, 255, 200)
    Note right of NotificationJob: Happy Case - Product Entity Processing
    
    NotificationJob->>WishlistRepo: getEntity(notifiable_id)
    WishlistRepo->>ConsoleDB: Query wishlist_products with summaryProduct
    ConsoleDB-->>WishlistRepo: Return wishlist product data
    WishlistRepo-->>NotificationJob: Return wishlist product with summaryProduct
    
    NotificationJob->>ProductRepo: getByMallProductId(summaryProduct.input)
    ProductRepo->>AnalyzerDB: Query products WHERE mall_product_id
    AnalyzerDB-->>ProductRepo: Return analyzer product
    ProductRepo-->>NotificationJob: Return analyzer product
    
    rect rgb(200, 230, 255)
    Note right of NotificationJob: Date Range and Data Retrieval
    NotificationJob->>NotificationJob: getDateRangeForFrequency(frequency)
    
    NotificationJob->>ProductDetailRepo: getCurrentPeriodRecord(product, currentDateRange)
    ProductDetailRepo->>AnalyzerDB: Query product_details WHERE metrics_date IN range
    AnalyzerDB-->>ProductDetailRepo: Return current period data
    ProductDetailRepo-->>NotificationJob: Return current period product detail
    
    NotificationJob->>ProductDetailRepo: getHistoricalRecord(product, historicalDateRange)
    ProductDetailRepo->>AnalyzerDB: Query product_details WHERE metrics_date IN historical range
    AnalyzerDB-->>ProductDetailRepo: Return historical period data
    ProductDetailRepo-->>NotificationJob: Return historical period product detail
    end
    
    rect rgb(255, 255, 200)
    Note right of NotificationJob: Calculation and Threshold Check
    NotificationJob->>NotificationJob: calculateSalesValue(sales_one_month * price)
    NotificationJob->>NotificationJob: calculate_percentage_change(current, previous)
    NotificationJob->>NotificationJob: shouldNotifyForComparison(comparison_type, change, threshold)
    
    NotificationJob->>Logger: Log calculation results with values
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of NotificationJob: Error Handling
    rect rgb(255, 230, 230)
    alt Product Not Found
        NotificationJob->>Logger: Log missing product with mall_product_id
        NotificationJob->>NotificationJob: Skip record and continue
    else Insufficient Data
        NotificationJob->>Logger: Log insufficient sales data
        NotificationJob->>NotificationJob: Skip record and continue
    else Calculation Error
        NotificationJob->>Logger: Log calculation error with input data
        NotificationJob->>Slack: Send calculation error notification
    end
    end
    end

Step 4: Ratio Type Processing - Group Notifications

sequenceDiagram
    participant NotificationJob as SendNotificationJob
    participant WishlistGroupRepo as WishlistToGroupRepository
    participant ProductRepo as ProductRepository
    participant ProductDetailRepo as ProductDetailRepository
    participant ConsoleDB[(gb_console)]
    participant AnalyzerDB[(gb_analyzer)]
    participant Logger
    participant Slack
    
    Note over NotificationJob,Slack: Ratio Type - Group Processing
    
    rect rgb(200, 255, 200)
    Note right of NotificationJob: Happy Case - Group Entity Processing
    
    NotificationJob->>WishlistGroupRepo: getEntity(notifiable_id)
    WishlistGroupRepo->>ConsoleDB: Query wishlist_groups with products
    ConsoleDB-->>WishlistGroupRepo: Return wishlist group with products
    WishlistGroupRepo-->>NotificationJob: Return wishlist group with products
    
    NotificationJob->>NotificationJob: Extract mall_product_ids from group products
    NotificationJob->>ProductRepo: getByMallProductIds(mallProductIds)
    ProductRepo->>AnalyzerDB: Query products WHERE mall_product_id IN (ids)
    AnalyzerDB-->>ProductRepo: Return analyzer products collection
    ProductRepo-->>NotificationJob: Return analyzer products collection
    
    rect rgb(200, 230, 255)
    Note right of NotificationJob: Aggregated Data Processing
    NotificationJob->>NotificationJob: getDateRangeForFrequency(frequency)
    
    NotificationJob->>ProductDetailRepo: getLatestProductDetailsInDateRange(productIds, currentRange)
    ProductDetailRepo->>AnalyzerDB: Query product_details for current period
    AnalyzerDB-->>ProductDetailRepo: Return current period details
    ProductDetailRepo-->>NotificationJob: Return current period details
    
    NotificationJob->>ProductDetailRepo: getLatestProductDetailsInDateRange(productIds, historicalRange)
    ProductDetailRepo->>AnalyzerDB: Query product_details for historical period
    AnalyzerDB-->>ProductDetailRepo: Return historical period details
    ProductDetailRepo-->>NotificationJob: Return historical period details
    end
    
    rect rgb(255, 255, 200)
    Note right of NotificationJob: Aggregated Calculation
    NotificationJob->>NotificationJob: calculateTotalSalesValue(currentDetails)
    NotificationJob->>NotificationJob: calculateTotalSalesValue(historicalDetails)
    NotificationJob->>NotificationJob: calculate_percentage_change(currentTotal, historicalTotal)
    NotificationJob->>NotificationJob: shouldNotifyForComparison(comparison_type, change, threshold)
    
    NotificationJob->>Logger: Log aggregated calculation results
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of NotificationJob: Error Handling
    rect rgb(255, 230, 230)
    alt No Products in Group
        NotificationJob->>Logger: Log empty product group
        NotificationJob->>NotificationJob: Skip record and continue
    else Insufficient Aggregated Data
        NotificationJob->>Logger: Log insufficient aggregated data
        NotificationJob->>NotificationJob: Skip record and continue
    end
    end
    end

Step 5: Amount Type Processing

sequenceDiagram
    participant NotificationJob as SendNotificationJob
    participant WishlistRepo as Repository
    participant ProductRepo as ProductRepository
    participant ProductDetailRepo as ProductDetailRepository
    participant ConsoleDB[(gb_console)]
    participant AnalyzerDB[(gb_analyzer)]
    participant Logger
    participant Slack
    
    Note over NotificationJob,Slack: Amount Type Processing
    
    rect rgb(200, 255, 200)
    Note right of NotificationJob: Happy Case - Amount Threshold Processing
    
    rect rgb(200, 230, 255)
                alt notifiable_type = WishlistProduct
        Note right of NotificationJob: Individual Product Processing
                    NotificationJob->>WishlistRepo: getEntity(notifiable_id)
        WishlistRepo->>ConsoleDB: Query wishlist_products
        ConsoleDB-->>WishlistRepo: Return wishlist product
                    WishlistRepo-->>NotificationJob: Return wishlist product
        
                    NotificationJob->>ProductRepo: getByMallProductId(input)
        ProductRepo->>AnalyzerDB: Query products WHERE mall_product_id
        AnalyzerDB-->>ProductRepo: Return analyzer product
                    ProductRepo-->>NotificationJob: Return analyzer product
        
        NotificationJob->>ProductDetailRepo: getLatestProductDetail()
        ProductDetailRepo->>AnalyzerDB: Query latest product_details
        AnalyzerDB-->>ProductDetailRepo: Return latest product detail
        ProductDetailRepo-->>NotificationJob: Return latest product detail
        
                    NotificationJob->>NotificationJob: calculateSalesValue(sales_one_month * price)
                else notifiable_type = WishlistGroup
        Note right of NotificationJob: Group Processing
                    NotificationJob->>WishlistRepo: getEntity(notifiable_id)
        WishlistRepo->>ConsoleDB: Query wishlist_groups with products
        ConsoleDB-->>WishlistRepo: Return wishlist group
                    WishlistRepo-->>NotificationJob: Return wishlist group
        
        NotificationJob->>ProductRepo: getByMallProductIds(groupProductIds)
        ProductRepo->>AnalyzerDB: Query products WHERE mall_product_id IN (ids)
        AnalyzerDB-->>ProductRepo: Return analyzer products
        ProductRepo-->>NotificationJob: Return analyzer products
        
        NotificationJob->>ProductDetailRepo: getLatestProductDetailsForProducts(productIds)
        ProductDetailRepo->>AnalyzerDB: Query latest product_details for products
        AnalyzerDB-->>ProductDetailRepo: Return latest details collection
        ProductDetailRepo-->>NotificationJob: Return latest details collection
        
        NotificationJob->>NotificationJob: calculateTotalSalesFromDetails(details)
    end
    end
    
    rect rgb(255, 255, 200)
    Note right of NotificationJob: Threshold Evaluation
    NotificationJob->>NotificationJob: shouldNotifyForThreshold(comparison_type, value, threshold)
    NotificationJob->>Logger: Log threshold comparison result
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of NotificationJob: Error Handling
    rect rgb(255, 230, 230)
    alt Missing Sales Data
        NotificationJob->>Logger: Log missing sales data
        NotificationJob->>NotificationJob: Skip record and continue
    else Invalid Threshold Value
        NotificationJob->>Logger: Log invalid threshold configuration
        NotificationJob->>Slack: Send configuration error notification
    end
    end
    end

Step 6: Notification Creation and Delivery

sequenceDiagram
    participant NotificationJob as SendNotificationJob
    participant GroupRepo as GroupRepository
    participant NotificationSys as SendNotification
    participant NotificationDB[(gb_console.notifications)]
    participant PusherService as Pusher Service
    participant Logger
    participant Slack
    
    Note over NotificationJob,Slack: Notification Delivery Process
    
    rect rgb(200, 255, 200)
    Note right of NotificationJob: Happy Case - Notification Conditions Met
    
    NotificationJob->>NotificationJob: prepareNotificationData(entity, record, specificData)
                NotificationJob->>GroupRepo: findById(group_id)
    GroupRepo->>NotificationDB: Query groups with groupMembers.user
    NotificationDB-->>GroupRepo: Return group with members
    GroupRepo-->>NotificationJob: Return group with groupMembers.user
    
    NotificationJob->>NotificationJob: Extract users from group members
    
    rect rgb(200, 230, 255)
    Note right of NotificationJob: Database Storage
                loop For each User in Group
                    NotificationJob->>NotificationSys: new SendNotification(data, product, type, action)
        NotificationSys->>NotificationDB: Store notification record
        NotificationDB-->>NotificationSys: Return notification ID
        NotificationSys-->>NotificationJob: Return notification instance
        
        NotificationJob->>NotificationJob: Register NotificationSent event listener
        NotificationJob->>NotificationSys: Send notification to user
        NotificationSys-->>NotificationJob: Trigger NotificationSent event
            end
        end
    
    rect rgb(230, 200, 255)
    Note right of NotificationJob: Real-time Delivery
    NotificationJob->>NotificationSys: toPusher(userIds, notificationEvents)
    NotificationSys->>PusherService: Send real-time notifications to private channels
    PusherService-->>NotificationSys: Confirm delivery status
    NotificationSys-->>NotificationJob: Return delivery status
    
    NotificationJob->>Logger: Log notification success with user details
        end
    end
    
    rect rgb(255, 200, 200)
    Note right of NotificationJob: Error Handling
    rect rgb(255, 230, 230)
    alt Group Not Found
        NotificationJob->>Logger: Log group not found error
        NotificationJob->>NotificationJob: Skip notification
    else No Users in Group
        NotificationJob->>Logger: Log no valid users in group
        NotificationJob->>NotificationJob: Skip notification
    else Database Storage Error
        NotificationJob->>Logger: Log database storage error
        NotificationJob->>Slack: Send storage error notification
    else Pusher Service Error
        NotificationJob->>Logger: Log Pusher service error
        NotificationJob->>NotificationJob: Fallback to database-only delivery
    end
    end
    end

Step 7: Error Handling and Command Completion

sequenceDiagram
    participant NotificationJob as SendNotificationJob
    participant SaleCommand as notification:sale-one-month
    participant QueueSystem as Laravel Queue
    participant Logger
    participant Slack
    
    Note over NotificationJob,Slack: Error Handling and Command Completion
    
    rect rgb(255, 200, 200)
    Note right of NotificationJob: Error Scenarios and Recovery
    
    rect rgb(255, 230, 230)
    alt Database Connection Error
        NotificationJob->>Logger: Log database connection/query error with details
        NotificationJob->>NotificationJob: Implement exponential backoff retry
        NotificationJob->>QueueSystem: Retry job with delay
        NotificationJob->>Slack: Send database error alert
    else Product Data Missing
        NotificationJob->>Logger: Log missing product data with mall_product_id
        NotificationJob->>NotificationJob: Skip record and continue processing
    else Calculation Error
        NotificationJob->>Logger: Log calculation error with input data context
        NotificationJob->>NotificationJob: Skip record and continue processing
        NotificationJob->>Slack: Send calculation error alert
    else Pusher Service Error
        NotificationJob->>Logger: Log Pusher service error with API response
        NotificationJob->>NotificationJob: Fallback to database-only delivery
        NotificationJob->>Logger: Log fallback delivery success
    else Job Timeout
        NotificationJob->>Logger: Log job timeout with processing statistics
        NotificationJob->>QueueSystem: Release job for retry
        NotificationJob->>Slack: Send timeout alert
    end
    end
    end
    
    rect rgb(200, 255, 200)
    Note right of SaleCommand: Happy Case - Command Completion
    SaleCommand->>Logger: Log command completion with statistics
    SaleCommand->>Logger: Log total chunks processed and notifications sent
    SaleCommand->>Logger: Log execution time and performance metrics
    end

Detail

Parameters

The command has no configurable parameters. All configuration is handled through:

Chunk Size: Fixed at 100 records (defined as CHUNK_LIMIT constant)
Notification Settings: Retrieved from notification_settings table
Active Subscriptions: Only processes groups with active subscriptions

Frequency

Scheduled Execution: Daily execution configured in routes/console.php
- Schedule::command('notification:sale-one-month')->daily();
Manual Execution: Can be triggered manually for testing or immediate processing

Dependencies

Database Dependencies:

Console database (gb_console) for notification settings, groups, and user data
Analyzer database (gb_analyzer) for product details and sales metrics
Active subscription status for groups (whereHas('group.activeSubscription'))
Valid wishlist products and groups with proper relationships

External Service Dependencies:

Pusher service for real-time notification delivery
Valid Pusher credentials in environment configuration
Queue system for background job processing

Model Dependencies:

NotificationSetting model with proper enum casting (Type, Frequency, ComparisonType)
WishlistProduct and WishlistGroup models for notifiable entities
Product and ProductDetail models from Analyzer database
Group and GroupMember models for notification delivery

Output

Tables

For complete database schema and table relationships, refer to the Database Schema section in the Notification Overview.

Primary Operations:

Reads: notification_settings with active group subscriptions
Reads: Product sales data from product_details for current and historical periods
Creates: New records in notifications table for qualifying changes
Sends: Real-time notifications via Pusher service

Services

Notification Processing:

SendNotificationJob: Background job for processing notification chunks
SendNotification: Laravel notification class for database storage and Pusher delivery
Repository pattern for data access across Console and Analyzer databases

Calculation Logic:

Ratio Type: Compares percentage changes between current and historical periods
- Uses sales_one_month * price for sales value calculation
- Supports Increase/Decrease comparison types with percentage thresholds
Amount Type: Compares absolute sales values against fixed thresholds
- Supports Exceed/FallBelow comparison types with amount thresholds

Error Handling

Log

The system generates logs for monitoring and troubleshooting:

Command Execution Logs:

Command start and completion with execution context
Chunk processing statistics and timing information
Error details with full exception context and stack traces

Job Processing Logs:

Individual record processing results with notification setting details
Product data retrieval success/failure with specific product identifiers
Sales calculation results and threshold comparison outcomes
Notification delivery confirmation with user and group details

Error Categories:

Database connection failures with retry information
Missing product data with specific mall_product_id details
Calculation errors with input data context
Pusher service failures with API response details

Slack

Currently not implemented in the actual codebase. Error handling relies on Laravel's built-in logging system.

Troubleshooting

Check Data

Verify Active Notification Settings:

-- Check notification settings with active subscriptions
SELECT ns.id, ns.notifiable_type, ns.notifiable_id, ns.type, 
       ns.comparison_type, ns.comparison_value, ns.frequency,
       g.name as group_name, s.status as subscription_status
FROM notification_settings ns
JOIN groups g ON ns.group_id = g.id
JOIN subscriptions s ON g.id = s.group_id
WHERE s.status = 'active'
ORDER BY ns.created_at DESC;

Check Wishlist Products and Groups:

-- Verify wishlist products for notification
SELECT wp.id, wp.summaryProduct.input as mall_product_id,
       wg.name as group_name, wp.created_at
FROM wishlist_products wp
JOIN wishlist_groups wg ON wp.wishlist_group_id = wg.id
WHERE wp.id IN (SELECT notifiable_id FROM notification_settings 
                WHERE notifiable_type = 'App\\Models\\WishlistProduct');

-- Verify wishlist groups for notification  
SELECT wg.id, wg.name, COUNT(wp.id) as product_count
FROM wishlist_groups wg
LEFT JOIN wishlist_products wp ON wg.id = wp.wishlist_group_id
WHERE wg.id IN (SELECT notifiable_id FROM notification_settings 
                WHERE notifiable_type = 'App\\Models\\WishlistGroup')
GROUP BY wg.id, wg.name;

Check Product Sales Data:

-- Check recent product details for sales calculation
SELECT p.id, p.mall_product_id, pd.sales_one_month, pd.price,
       (pd.sales_one_month * pd.price) as sales_value,
       pd.metrics_date, pd.created_at
FROM products p
JOIN product_details pd ON p.id = pd.product_id
WHERE pd.metrics_date >= DATE_SUB(NOW(), INTERVAL 2 MONTH)
ORDER BY pd.metrics_date DESC, p.mall_product_id;

Check Logs

Application Logs:

# Check notification command execution
tail -f storage/logs/laravel.log | grep "notification:sale-one-month"

# Check SendNotificationJob processing
grep "SendNotificationJob" storage/logs/laravel.log | tail -20

# Check notification delivery
grep "SendNotification" storage/logs/laravel.log | tail -10

# Check error patterns
grep -E "(ERROR|Exception)" storage/logs/laravel.log | grep -i notification | tail -10

Queue Status:

# Check queue status for notification jobs
php artisan queue:work --once --verbose

# Check failed jobs
php artisan queue:failed

# Retry failed notification jobs
php artisan queue:retry all

Database Verification:

-- Check recent notifications created
SELECT n.id, n.type, n.notifiable_type, n.notifiable_id,
       JSON_EXTRACT(n.data, '$.setting.wishlist_product_name') as product_name,
       JSON_EXTRACT(n.data, '$.compare.change') as change_percentage,
       n.read_at, n.created_at
FROM notifications n
WHERE n.created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
AND JSON_EXTRACT(n.data, '$.type') = 'sales_one_month_change'
ORDER BY n.created_at DESC;

-- Check notification processing statistics
SELECT DATE(created_at) as notification_date,
       COUNT(*) as total_notifications,
       COUNT(CASE WHEN read_at IS NOT NULL THEN 1 END) as read_notifications
FROM notifications
WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(created_at)
ORDER BY notification_date DESC;

Backend Documentation