<?php

namespace App\Utils;

use App\Config\Database;

class ExpiryAlertHelper {
    /**
     * Retrieve all expiring soon batches for a given tenant.
     *
     * @param int $tenantId
     * @param string|null $referenceDate Reference date (defaults to current date)
     * @return array
     */
    public static function getExpiringSoonItems(int $tenantId, string $referenceDate = null): array {
        $db = Database::getInstance()->getConnection();
        $expiryAfter = $referenceDate ?: date('Y-m-d');
        
        // Match the logic in DashboardController:
        // Active batches, quantity > 0, expiry date between reference date and m.expiry_alert_days (default 30)
        $expiryDateClause = "AND ib.expiry_date >= ?";
        $expiryDateClause .= " AND ib.expiry_date <= DATE_ADD(?, INTERVAL COALESCE(m.expiry_alert_days, 30) DAY)";
        
        $params = [$tenantId, $expiryAfter, $expiryAfter];
        
        $stmt = $db->prepare("
            SELECT ib.batch_number, ib.expiry_date, ib.quantity, b.name as branch_name, 
                   m.name as medicine_name, m.generic_name,
                   DATEDIFF(ib.expiry_date, ?) as days_left
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              $expiryDateClause
            ORDER BY ib.expiry_date ASC
        ");
        
        // Bind parameters: referenceDate, tenantId, referenceDate, referenceDate
        $stmt->execute([$expiryAfter, $tenantId, $expiryAfter, $expiryAfter]);
        return $stmt->fetchAll();
    }
}
