<?php
/**
 * EMAILER – SM PRODUCE
 * Gestisce:
 *  - invio email tramite SMTP (PHPMailer)
 *  - salvataggio impostazioni email
 *  - salvataggio destinatari
 *  - invio REPORT PARZIALE (PDF + HTML business)
 *  - gestione alert inattività Keyence (solo logica dati)
 *
 * Versione: 2025-12-11 (stile C: email business + PDF factory, con logo)
 */

// =====================================================
// JSON HELPERS
// =====================================================
if (!function_exists('sp_load_json')) {
    function sp_load_json(string $path): array {
        if (!is_file($path)) return [];
        $txt = @file_get_contents($path);
        if ($txt === false || $txt === '') return [];
        $data = json_decode($txt, true);
        return is_array($data) ? $data : [];
    }
}

if (!function_exists('sp_save_json')) {
    function sp_save_json(string $path, array $data): bool {
        $dir = dirname($path);
        if (!is_dir($dir)) @mkdir($dir, 0777, true);
        return @file_put_contents(
            $path,
            json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)
        ) !== false;
    }
}

// =====================================================
// CONFIG PATHS
// =====================================================
define('SP_EMAIL_SETTINGS_FILE',   __DIR__ . '/email_settings.json');
define('SP_EMAIL_RECIPIENTS_FILE', __DIR__ . '/email_recipients.json');
define('SP_ALERTS_SETTINGS_FILE',  __DIR__ . '/production_alerts.json');

// =====================================================
// PHPMailer INCLUDES
// =====================================================
$phpmailerPath = __DIR__ . '/../lib/PHPMailer/src/';
if (is_file($phpmailerPath . 'PHPMailer.php')) {
    require_once $phpmailerPath . 'Exception.php';
    require_once $phpmailerPath . 'PHPMailer.php';
    require_once $phpmailerPath . 'SMTP.php';
}

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// =====================================================
// SETTINGS FUNCTIONS
// =====================================================
function sp_email_get_settings(): array {
    $cfg = sp_load_json(SP_EMAIL_SETTINGS_FILE);
    $defaults = [
        'smtp_host'   => 'smtp.gmail.com',
        'smtp_port'   => 587,
        'smtp_user'   => 'itsmproduce@gmail.com',
        'smtp_pass'   => '',
        'smtp_secure' => 'tls',
        'from_email'  => 'itsmproduce@gmail.com',
        'from_name'   => 'SM Produce Reports'
    ];
    return array_merge($defaults, is_array($cfg) ? $cfg : []);
}

function sp_email_save_settings(array $cfg): bool {
    return sp_save_json(SP_EMAIL_SETTINGS_FILE, $cfg);
}

// =====================================================
// RECIPIENT LIST FUNCTIONS
// =====================================================
function sp_email_get_recipients(): array {
    $data = sp_load_json(SP_EMAIL_RECIPIENTS_FILE);
    if (!isset($data['to']) || !is_array($data['to'])) {
        $data['to'] = [];
    }

    $out = [];
    foreach ($data['to'] as $addr) {
        $addr = trim((string)$addr);
        if ($addr !== '') $out[] = $addr;
    }
    return $out;
}

function sp_email_set_recipients(array $list): bool {
    $clean = [];
    foreach ($list as $addr) {
        $addr = trim((string)$addr);
        if ($addr !== '') $clean[] = $addr;
    }
    return sp_save_json(SP_EMAIL_RECIPIENTS_FILE, ['to' => $clean]);
}

// =====================================================
// KEYENCE INACTIVITY ALERT (data only – NO AUTOSEND)
// =====================================================
function sp_email_get_alerts(): array {
    $cfg = sp_load_json(SP_ALERTS_SETTINGS_FILE);
    $defaults = [
        'enabled'          => true,
        'timeout_minutes'  => 60,
        'last_alert_time'  => null
    ];
    return array_merge($defaults, is_array($cfg) ? $cfg : []);
}

function sp_email_save_alerts(array $cfg): bool {
    return sp_save_json(SP_ALERTS_SETTINGS_FILE, $cfg);
}

// =====================================================
// SMTP SEND WRAPPER
// =====================================================
function sp_smtp_send(
    string $subject,
    string $bodyHtml,
    array  $to = null,
    array  $cc = null,
    array  $bcc = null,
    array  $attachments = []
): bool {

    if (!class_exists('PHPMailer\\PHPMailer\\PHPMailer')) {
        error_log("sp_smtp_send: PHPMailer missing.");
        return false;
    }

    $cfg = sp_email_get_settings();

    if (!$to || !count($to)) {
        $to = sp_email_get_recipients();
    }
    if (!count($to)) {
        error_log("sp_smtp_send: No recipients.");
        return false;
    }

    try {
        $mail = new PHPMailer(true);
        $mail->isSMTP();
        $mail->Host     = $cfg['smtp_host'];
        $mail->Port     = (int)$cfg['smtp_port'];
        $mail->SMTPAuth = true;
        $mail->Username = $cfg['smtp_user'];
        $mail->Password = $cfg['smtp_pass'];

        $secure = strtolower($cfg['smtp_secure'] ?? 'tls');
        $mail->SMTPSecure = ($secure === 'ssl')
            ? PHPMailer::ENCRYPTION_SMTPS
            : PHPMailer::ENCRYPTION_STARTTLS;

        $mail->setFrom($cfg['from_email'], $cfg['from_name']);

        foreach ($to as $addr) {
            $mail->addAddress($addr);
        }
        if ($cc) {
            foreach ($cc as $addr) $mail->addCC($addr);
        }
        if ($bcc) {
            foreach ($bcc as $addr) $mail->addBCC($addr);
        }

        $mail->isHTML(true);
        $mail->Subject = $subject;
        $mail->Body    = $bodyHtml;
        $mail->AltBody = strip_tags(str_replace("<br>", "\n", $bodyHtml));

        foreach ($attachments as $att) {
            if (is_array($att) && isset($att['path'])) {
                $mail->addAttachment($att['path'], $att['name'] ?? null);
            }
        }

        return $mail->send();

    } catch (Exception $e) {
        error_log("sp_smtp_send error: " . $e->getMessage());
        return false;
    }
}

// =====================================================
// REPORT PARZIALE (PDF + EMAIL HTML)
// =====================================================
function sp_send_partial_production(
    mysqli $mysqli,
    string $rangeKey = 'day',
    ?string $customStart = null,
    ?string $customEnd   = null
): bool {

    // --------------------------------------------------
    // 1. Range
    // --------------------------------------------------
    $rangeKey   = strtolower($rangeKey);
    $rangeLabel = "Today";

    switch ($rangeKey) {
        case '7d':
            $where      = "br.read_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
            $rangeLabel = "Last 7 days";
            break;

        case '30d':
            $where      = "br.read_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
            $rangeLabel = "Last 30 days";
            break;

        case 'all':
            $where      = "1=1";
            $rangeLabel = "All time";
            break;

        case 'custom':
            if (
                $customStart && $customEnd &&
                preg_match('/^\d{4}-\d{2}-\d{2}$/', $customStart) &&
                preg_match('/^\d{4}-\d{2}-\d{2}$/', $customEnd)
            ) {
                $where      = "br.read_time BETWEEN '{$customStart} 00:00:00' AND '{$customEnd} 23:59:59'";
                $rangeLabel = "Custom: {$customStart} → {$customEnd}";
            } else {
                $today      = date('Y-m-d');
                $where      = "DATE(br.read_time) = '{$today}'";
                $rangeLabel = "Today";
            }
            break;

        default:
            $today      = date('Y-m-d');
            $where      = "DATE(br.read_time) = '{$today}'";
            $rangeLabel = "Today";
            break;
    }

    // --------------------------------------------------
    // 2. Totali
    // --------------------------------------------------
    $sqlTotals = "
        SELECT
            COUNT(*) AS total_boxes,
            COUNT(DISTINCT COALESCE(NULLIF(TRIM(cc.grower), ''), '(n/a)')) AS growers,
            COUNT(DISTINCT COALESCE(NULLIF(TRIM(COALESCE(s.size, cc.size)), ''), '(n/a)')) AS sizes
        FROM barcode_readings br
        LEFT JOIN casecodes cc ON cc.serial = br.code
        LEFT JOIN skus s ON s.SKU = cc.SKU
        WHERE $where
    ";

    $resTotals = $mysqli->query($sqlTotals);
    $rowTotals = $resTotals ? $resTotals->fetch_assoc() : null;
    if ($resTotals) $resTotals->free();

    $totalBoxes   = (int)($rowTotals['total_boxes'] ?? 0);
    $growersCount = (int)($rowTotals['growers']     ?? 0);
    $sizesCount   = (int)($rowTotals['sizes']       ?? 0);

    // --------------------------------------------------
    // 3. Top SKU
    // --------------------------------------------------
    $topSku = [];
    $sqlSku = "
        SELECT
            COALESCE(NULLIF(TRIM(COALESCE(s.variety,   cc.variety)),''),'(n/a)') AS variety,
            COALESCE(NULLIF(TRIM(COALESCE(s.packaging, cc.packaging)),''),'(n/a)') AS packaging,
            COALESCE(NULLIF(TRIM(COALESCE(s.size,      cc.size)),''),'(n/a)') AS size,
            COUNT(*) AS total
        FROM barcode_readings br
        LEFT JOIN casecodes cc ON cc.serial = br.code
        LEFT JOIN skus s ON s.SKU = cc.SKU
        WHERE $where
        GROUP BY variety, packaging, size
        ORDER BY total DESC
    ";
    if ($resSku = $mysqli->query($sqlSku)) {
        while ($r = $resSku->fetch_assoc()) {
            $desc = trim(
                (string)$r['variety']   . " - " .
                (string)$r['packaging'] . " - " .
                (string)$r['size'],
                " -"
            );
            if ($desc === "") $desc = "(n/a)";
            $topSku[] = [
                'description' => $desc,
                'total'       => (int)$r['total']
            ];
        }
        $resSku->free();
    }

    // --------------------------------------------------
    // 4. Products by grower
    // --------------------------------------------------
    $growerProducts = [];
    $growerTotals   = [];
    $growersList    = [];

    $sqlGrowers = "
        SELECT
            COALESCE(NULLIF(TRIM(cc.grower),''),'(n/a)') AS grower,
            COALESCE(NULLIF(TRIM(COALESCE(s.variety,   cc.variety)),''),'(n/a)') AS variety,
            COALESCE(NULLIF(TRIM(COALESCE(s.packaging, cc.packaging)),''),'(n/a)') AS packaging,
            COALESCE(NULLIF(TRIM(COALESCE(s.size,      cc.size)),''),'(n/a)') AS size,
            COUNT(*) AS total
        FROM barcode_readings br
        LEFT JOIN casecodes cc ON cc.serial = br.code
        LEFT JOIN skus s ON s.SKU = cc.SKU
        WHERE $where
        GROUP BY grower, variety, packaging, size
        ORDER BY grower ASC, total DESC
    ";
    if ($resGrow = $mysqli->query($sqlGrowers)) {
        while ($r = $resGrow->fetch_assoc()) {
            $g    = (string)$r['grower'];
            $desc = trim(
                (string)$r['variety']   . " - " .
                (string)$r['packaging'] . " - " .
                (string)$r['size'],
                " -"
            );
            if ($desc === "") $desc = "(n/a)";

            if (!isset($growerProducts[$g])) {
                $growerProducts[$g] = [];
                $growerTotals[$g]   = 0;
                $growersList[]      = $g;
            }

            $growerProducts[$g][] = [
                'description' => $desc,
                'total'       => (int)$r['total'],
            ];
            $growerTotals[$g] += (int)$r['total'];
        }
        $resGrow->free();
    }

    sort($growersList);

    // --------------------------------------------------
    // 5. Common data + logo
    // --------------------------------------------------
    $timestamp = date('Y-m-d H:i:s');
    $logoFile  = __DIR__ . '/../assets/logo.png';

    $logoHtmlPdf = is_file($logoFile)
        ? '<div style="text-align:center;margin-bottom:10px;">
               <img src="file://' . $logoFile . '" style="height:60px;">
           </div>'
        : '<!-- logo missing in PDF -->';

    // --------------------------------------------------
    // 6. HTML PDF (factory, compatto)
    // --------------------------------------------------
    $pdfHtml  = '<html><body style="font-family:Arial;font-size:11px;">';
    $pdfHtml .= $logoHtmlPdf;
    $pdfHtml .= "<h2>Partial Production Report</h2>";
    $pdfHtml .= "<p><strong>Generated:</strong> {$timestamp}<br>"
              . "<strong>Range:</strong> {$rangeLabel}</p>";

    $pdfHtml .= "
        <table border='1' cellspacing='0' cellpadding='3'
               style='margin-bottom:10px;font-size:11px;border-collapse:collapse;'>
          <tr><td>Total boxes</td><td><strong>{$totalBoxes}</strong></td></tr>
          <tr><td>Growers</td><td><strong>{$growersCount}</strong></td></tr>
          <tr><td>Sizes</td><td><strong>{$sizesCount}</strong></td></tr>
        </table>";

    $pdfHtml .= "
        <h3>Top Products by SKU</h3>
        <table border='1' cellspacing='0' cellpadding='3' width='100%'
               style='font-size:11px;border-collapse:collapse;'>
          <tr><th align='left'>Product</th><th align='right'>Boxes</th></tr>";
    foreach ($topSku as $row) {
        $d = htmlspecialchars($row['description'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        $t = (int)$row['total'];
        $pdfHtml .= "<tr>
                        <td>{$d}</td>
                        <td align='right'>{$t}</td>
                     </tr>";
    }
    $pdfHtml .= "</table>";

    $pdfHtml .= "<h3>Products by Grower</h3>";
    foreach ($growersList as $g) {
        $gSafe = htmlspecialchars($g, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        $totG  = (int)($growerTotals[$g] ?? 0);
        $pdfHtml .= "<h4>{$gSafe} ({$totG} boxes)</h4>";
        $pdfHtml .= "
          <table border='1' cellspacing='0' cellpadding='3' width='100%'
                 style='font-size:11px;border-collapse:collapse;margin-bottom:5px;'>
            <tr><th align='left'>Product</th><th align='right'>Boxes</th></tr>";
        foreach ($growerProducts[$g] as $row) {
            $d = htmlspecialchars($row['description'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
            $t = (int)$row['total'];
            $pdfHtml .= "<tr>
                           <td>{$d}</td>
                           <td align='right'>{$t}</td>
                         </tr>";
        }
        $pdfHtml .= "</table>";
    }
    $pdfHtml .= "</body></html>";

    // --------------------------------------------------
    // 7. HTML EMAIL (business, con logo HTTP)
    // --------------------------------------------------
    $emailLogoUrl  = 'https://smproduceprod.uk/smproduce/assets/logo.png';
    $emailLogoHtml = $emailLogoUrl
        ? '<div style="text-align:center;margin-bottom:16px;">
               <img src="' . htmlspecialchars($emailLogoUrl, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') . '" style="height:60px;">
           </div>'
        : '';

    $emailHtml  = '<div style="font-family:Arial,Helvetica,sans-serif;font-size:14px;color:#333;">';
    $emailHtml .= $emailLogoHtml;
    $emailHtml .= "<h2 style='margin-bottom:4px;'>Partial Production Report</h2>";
    $emailHtml .= "<p style='margin-top:0;margin-bottom:12px;'>
                     <strong>Generated:</strong> {$timestamp}<br>
                     <strong>Range:</strong> {$rangeLabel}
                   </p>";

    $emailHtml .= "
        <h3 style='margin-bottom:4px;'>Summary</h3>
        <table style='border-collapse:collapse;font-size:13px;margin-bottom:16px;'>
          <tr>
            <td style='border:1px solid #ccc;padding:6px 10px;'>Total boxes</td>
            <td style='border:1px solid #ccc;padding:6px 10px;'><strong>{$totalBoxes}</strong></td>
          </tr>
          <tr>
            <td style='border:1px solid #ccc;padding:6px 10px;'>Growers</td>
            <td style='border:1px solid #ccc;padding:6px 10px;'><strong>{$growersCount}</strong></td>
          </tr>
          <tr>
            <td style='border:1px solid #ccc;padding:6px 10px;'>Sizes</td>
            <td style='border:1px solid #ccc;padding:6px 10px;'><strong>{$sizesCount}</strong></td>
          </tr>
        </table>";

    $emailHtml .= "
        <h3 style='margin-bottom:4px;'>Top Products by SKU</h3>
        <table style='border-collapse:collapse;width:100%;font-size:13px;margin-bottom:16px;'>
          <tr>
            <th style='border:1px solid #ccc;padding:6px 8px;text-align:left;'>Product</th>
            <th style='border:1px solid #ccc;padding:6px 8px;text-align:right;'>Boxes</th>
          </tr>";
    foreach ($topSku as $row) {
        $d = htmlspecialchars($row['description'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        $t = (int)$row['total'];
        $emailHtml .= "<tr>
            <td style='border:1px solid #eee;padding:6px 8px;'>{$d}</td>
            <td style='border:1px solid #eee;padding:6px 8px;text-align:right;'>{$t}</td>
          </tr>";
    }
    $emailHtml .= "</table>";

    $emailHtml .= "
        <h3 style='margin-bottom:4px;'>Products by Grower</h3>";
    foreach ($growersList as $g) {
        $gSafe = htmlspecialchars($g, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        $totG  = (int)($growerTotals[$g] ?? 0);
        $emailHtml .= "<h4 style='margin:8px 0 4px;'>{$gSafe} ({$totG} boxes)</h4>";
        $emailHtml .= "
          <table style='border-collapse:collapse;width:100%;font-size:13px;margin-bottom:10px;'>
            <tr>
              <th style='border:1px solid #ccc;padding:6px 8px;text-align:left;'>Product</th>
              <th style='border:1px solid #ccc;padding:6px 8px;text-align:right;'>Boxes</th>
            </tr>";
        foreach ($growerProducts[$g] as $row) {
            $d = htmlspecialchars($row['description'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
            $t = (int)$row['total'];
            $emailHtml .= "<tr>
              <td style='border:1px solid #eee;padding:6px 8px;'>{$d}</td>
              <td style='border:1px solid #eee;padding:6px 8px;text-align:right;'>{$t}</td>
            </tr>";
        }
        $emailHtml .= "</table>";
    }

    $emailHtml .= "
        <p style='margin-top:16px;font-size:12px;color:#777;'>
          This is an automatically generated report from the SM Produce system.
        </p>";
    $emailHtml .= "</div>";

    // --------------------------------------------------
    // 8. Caricamento DOMPDF
    // --------------------------------------------------
    $autoload1 = __DIR__ . '/../lib/dompdf/autoload.inc.php';
    $autoload2 = __DIR__ . '/../lib/dompdf/autoload.php';

    if (is_file($autoload1)) {
        require_once $autoload1;
    } elseif (is_file($autoload2)) {
        require_once $autoload2;
    }

    if (!class_exists('Dompdf\\Dompdf')) {
        error_log("sp_send_partial_production: dompdf load failed.");
        return false;
    }

    // --------------------------------------------------
    // 9. Genera PDF
    // --------------------------------------------------
    try {
        $dompdf = new Dompdf\Dompdf();
        $dompdf->set_option('isRemoteEnabled', true);
        $dompdf->loadHtml($pdfHtml);
        $dompdf->setPaper('A4', 'portrait');
        $dompdf->render();
        $pdf = $dompdf->output();
    } catch (\Throwable $e) {
        error_log("DOMPDF ERROR: " . $e->getMessage());
        return false;
    }

    $tmpPdf = sys_get_temp_dir() . '/partial_report_' . time() . '.pdf';
    file_put_contents($tmpPdf, $pdf);

    // --------------------------------------------------
    // 10. Invio email
    // --------------------------------------------------
    $recipients = sp_email_get_recipients();
    if (!count($recipients)) {
        error_log("sp_send_partial_production: no recipients.");
        @unlink($tmpPdf);
        return false;
    }

    $subject = "Partial Production Report - {$rangeLabel}";

    $ok = sp_smtp_send(
        $subject,
        $emailHtml,
        $recipients,
        null,
        null,
        [['path' => $tmpPdf, 'name' => 'partial_production_report.pdf']]
    );

    @unlink($tmpPdf);
    return $ok;
}

?>
