<?php
/**
 * config/email_send.php
 *
 * Generazione report produzione (HTML + PDF) e invio via email.
 * Usa:
 *  - config/emailer.php  per l'SMTP (PHPMailer + Gmail)
 *  - lib/dompdf         per generare il PDF
 */

require_once __DIR__ . '/emailer.php';
require_once __DIR__ . '/db_remote.php'; // per $mysqli

// ---- Dompdf ----
$dompdfAutoload = dirname(__DIR__) . '/lib/dompdf/autoload.inc.php';
if (is_file($dompdfAutoload)) {
    require_once $dompdfAutoload;
}
use Dompdf\Dompdf;
use Dompdf\Options;

/**
 * Percorso WEB del logo da usare nel PDF.
 * Esempio: /smproduce/assets/logo.png
 * (metti il file fisico in C:\xampp\htdocs\smproduce\assets\logo.png)
 */
if (!defined('SP_REPORT_LOGO_WEB')) {
    define('SP_REPORT_LOGO_WEB', '/smproduce/assets/logo.png');
}

/**
 * Costruisce i dati per il report produzione:
 * - ultimi N barcode letti
 * - statistiche hourly, daily, monthly, cumulative
 */
function sp_build_production_report_data(mysqli $mysqli, int $limitRows = 200): array
{
    $data = [
        'rows'             => [],
        'hourlyCounts'     => array_fill(0, 24, 0),
        'dailyLabels'      => [],
        'dailyCounts'      => [],
        'monthlyLabels'    => [],
        'monthlyCounts'    => [],
        'cumulativeCounts' => [],
        'todayTotal'       => 0,
        'totalAll'         => 0,
        'topHourCount'     => 0,
        'topHourLabel'     => '-',
        'topDayCount'      => 0,
        'topDayLabel'      => '-',
        'topMonthCount'    => 0,
        'topMonthLabel'    => '-',
    ];

    // Ultimi N scans (come live feed)
    $stmt = $mysqli->prepare("
        SELECT
          br.id,
          br.code,
          br.read_time,
          br.operator,
          br.station,
          br.note,
          cc.serial,
          cc.SKU,
          cc.crop,
          cc.variety,
          cc.packaging,
          cc.size,
          cc.grower,
          cc.DateTime,
          cc.shipment_id,
          cc.pallet_id
        FROM barcode_readings br
        LEFT JOIN casecodes cc ON cc.serial = br.code
        ORDER BY br.read_time DESC
        LIMIT ?
    ");
    if ($stmt) {
        $stmt->bind_param("i", $limitRows);
        $stmt->execute();
        $res = $stmt->get_result();
        while ($row = $res->fetch_assoc()) {
            $data['rows'][] = $row;
        }
        $stmt->close();
    }

    $today      = date('Y-m-d');
    $start30    = date('Y-m-d', strtotime('-29 days'));
    $start12mon = date('Y-m-01', strtotime('-11 months'));

    // Hourly (oggi)
    $hourly = $mysqli->query("
        SELECT HOUR(read_time) AS h, COUNT(*) AS c
        FROM barcode_readings
        WHERE DATE(read_time) = '{$today}'
        GROUP BY HOUR(read_time)
    ");
    if ($hourly) {
        while ($r = $hourly->fetch_assoc()) {
            $h = (int)$r['h'];
            if ($h >= 0 && $h < 24) {
                $data['hourlyCounts'][$h] = (int)$r['c'];
            }
        }
    }

    // Daily (ultimi 30 giorni)
    $daily = $mysqli->query("
        SELECT DATE(read_time) AS d, COUNT(*) AS c
        FROM barcode_readings
        WHERE read_time >= '{$start30} 00:00:00'
        GROUP BY DATE(read_time)
        ORDER BY DATE(read_time)
    ");
    if ($daily) {
        while ($r = $daily->fetch_assoc()) {
            $data['dailyLabels'][] = $r['d'];
            $data['dailyCounts'][] = (int)$r['c'];
        }
    }

    // Monthly (ultimi 12 mesi)
    $monthly = $mysqli->query("
        SELECT DATE_FORMAT(read_time,'%Y-%m') AS m, COUNT(*) AS c
        FROM barcode_readings
        WHERE read_time >= '{$start12mon} 00:00:00'
        GROUP BY DATE_FORMAT(read_time,'%Y-%m')
        ORDER BY DATE_FORMAT(read_time,'%Y-%m')
    ");
    if ($monthly) {
        while ($r = $monthly->fetch_assoc()) {
            $data['monthlyLabels'][] = $r['m'];
            $data['monthlyCounts'][] = (int)$r['c'];
        }
    }

    // Cumulative
    $running = 0;
    foreach ($data['dailyCounts'] as $v) {
        $running += $v;
        $data['cumulativeCounts'][] = $running;
    }

    // Totali
    $data['todayTotal'] = array_sum($data['hourlyCounts']);
    $totalAll = 0;
    $q = $mysqli->query("SELECT COUNT(*) AS c FROM barcode_readings");
    if ($q) {
        $r = $q->fetch_assoc();
        $totalAll = (int)($r['c'] ?? 0);
    }
    $data['totalAll'] = $totalAll;

    // Top hour
    $topHourCount = 0;
    $topHourLabel = '-';
    foreach ($data['hourlyCounts'] as $h => $c) {
        if ($c > $topHourCount) {
            $topHourCount = $c;
            $topHourLabel = sprintf('%02d:00 - %02d:00', $h, $h + 1);
        }
    }
    $data['topHourCount'] = $topHourCount;
    $data['topHourLabel'] = $topHourLabel;

    // Top day
    $topDayCount = 0;
    $topDayLabel = '-';
    foreach ($data['dailyLabels'] as $i => $d) {
        $c = $data['dailyCounts'][$i];
        if ($c > $topDayCount) {
            $topDayCount = $c;
            $topDayLabel = $d;
        }
    }
    $data['topDayCount'] = $topDayCount;
    $data['topDayLabel'] = $topDayLabel;

    // Top month
    $topMonthCount = 0;
    $topMonthLabel = '-';
    foreach ($data['monthlyLabels'] as $i => $m) {
        $c = $data['monthlyCounts'][$i];
        if ($c > $topMonthCount) {
            $topMonthCount = $c;
            $topMonthLabel = $m;
        }
    }
    $data['topMonthCount'] = $topMonthCount;
    $data['topMonthLabel'] = $topMonthLabel;

    return $data;
}

/**
 * HTML avanzato per il PDF report produzione.
 * Usa layout "business" con logo, KPI, tabella.
 */
function sp_render_production_report_html(array $data, ?string $logoWebPath = null): string
{
    $rows = $data['rows'];

    $logoHtml = '';
    if ($logoWebPath) {
        $logoHtml = '<img src="' . htmlspecialchars($logoWebPath) . '" style="height:50px;">';
    }

    $generatedAt = date('Y-m-d H:i:s');

    // Costruisco le righe tabella
    $rowsHtml = '';
    if (empty($rows)) {
        $rowsHtml = '<tr><td colspan="10" style="text-align:center;color:#777;">No scans yet.</td></tr>';
    } else {
        foreach ($rows as $r) {
            $rowsHtml .= '<tr>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['code'] ?? '')          . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['SKU'] ?? '')           . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['crop'] ?? '')          . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['variety'] ?? '')       . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['packaging'] ?? '')     . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['size'] ?? '')          . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['grower'] ?? '')        . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['DateTime'] ?? '')      . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['shipment_id'] ?? '')   . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['pallet_id'] ?? '')     . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['operator'] ?? '')      . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['station'] ?? '')       . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['note'] ?? '')          . '</td>';
            $rowsHtml .= '<td>' . htmlspecialchars($r['read_time'] ?? '')     . '</td>';
            $rowsHtml .= '</tr>';
        }
    }

    $html = '
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Production Report</title>
<style>
    body {
        font-family: DejaVu Sans, Arial, Helvetica, sans-serif;
        font-size: 11px;
        color: #333;
    }
    .header {
        display: flex;
        justify-content: space-between;
        align-items: center;
        border-bottom: 2px solid #4a90e2;
        padding-bottom: 8px;
        margin-bottom: 10px;
    }
    .header-title {
        font-size: 18px;
        font-weight: bold;
        color: #2c3e50;
    }
    .header-sub {
        font-size: 11px;
        color: #777;
    }
    .kpi-container {
        display: flex;
        flex-wrap: wrap;
        gap: 8px;
        margin-bottom: 10px;
    }
    .kpi-card {
        flex: 1 1 22%;
        border: 1px solid #ddd;
        border-radius: 6px;
        padding: 6px 8px;
        background: #f9fafb;
    }
    .kpi-label {
        font-size: 10px;
        color: #777;
        margin-bottom: 2px;
    }
    .kpi-value {
        font-size: 16px;
        font-weight: bold;
        color: #2c3e50;
    }
    .kpi-note {
        font-size: 10px;
        color: #999;
    }
    table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 8px;
    }
    th, td {
        border: 1px solid #e0e0e0;
        padding: 4px 5px;
        font-size: 9px;
    }
    th {
        background: #f0f3f7;
        font-weight: bold;
        text-align: left;
    }
    .footer {
        margin-top: 12px;
        font-size: 9px;
        color: #999;
        text-align: right;
    }
</style>
</head>
<body>

<div class="header">
  <div>
    <div class="header-title">Production Report</div>
    <div class="header-sub">Generated at ' . htmlspecialchars($generatedAt) . '</div>
  </div>
  <div>' . $logoHtml . '</div>
</div>

<div class="kpi-container">
  <div class="kpi-card">
    <div class="kpi-label">Top hour (today)</div>
    <div class="kpi-value">' . (int)$data['topHourCount'] . '</div>
    <div class="kpi-note">' . htmlspecialchars($data['topHourLabel']) . '</div>
  </div>
  <div class="kpi-card">
    <div class="kpi-label">Top day (last 30 days)</div>
    <div class="kpi-value">' . (int)$data['topDayCount'] . '</div>
    <div class="kpi-note">' . htmlspecialchars($data['topDayLabel']) . '</div>
  </div>
  <div class="kpi-card">
    <div class="kpi-label">Top month (last 12 months)</div>
    <div class="kpi-value">' . (int)$data['topMonthCount'] . '</div>
    <div class="kpi-note">' . htmlspecialchars($data['topMonthLabel']) . '</div>
  </div>
  <div class="kpi-card">
    <div class="kpi-label">Today / All-time</div>
    <div class="kpi-value">' . (int)$data['todayTotal'] . '</div>
    <div class="kpi-note">Total: ' . (int)$data['totalAll'] . '</div>
  </div>
</div>

<table>
  <thead>
    <tr>
      <th>Serial</th>
      <th>SKU</th>
      <th>Crop</th>
      <th>Variety</th>
      <th>Packaging</th>
      <th>Size</th>
      <th>Grower</th>
      <th>Case date/time</th>
      <th>Shipment</th>
      <th>Pallet</th>
      <th>Operator</th>
      <th>Station</th>
      <th>Note</th>
      <th>Scan time</th>
    </tr>
  </thead>
  <tbody>
    ' . $rowsHtml . '
  </tbody>
</table>

<div class="footer">
  SM Produce · Production Summary Report
</div>

</body>
</html>
';

    return $html;
}

/**
 * Output PDF al browser (Download PDF).
 */
function sp_output_production_pdf(mysqli $mysqli, string $filename = 'production_report.pdf')
{
    if (!class_exists('Dompdf\\Dompdf')) {
        die('Dompdf not available (check /lib/dompdf).');
    }

    $data = sp_build_production_report_data($mysqli);
    $html = sp_render_production_report_html($data, SP_REPORT_LOGO_WEB);

    $options = new Options();
    $options->set('isRemoteEnabled', true);

    $dompdf = new Dompdf($options);
    $dompdf->loadHtml($html);
    $dompdf->setPaper('A4', 'landscape');
    $dompdf->render();

    $output = $dompdf->output();

    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Content-Length: ' . strlen($output));
    echo $output;
    exit;
}

/**
 * Output CSV (Excel-friendly) al browser.
 */
function sp_output_production_csv(mysqli $mysqli, string $filename = 'production_report.csv')
{
    // riuso stessi dati (ultimi 200)
    $data = sp_build_production_report_data($mysqli, 200);
    $rows = $data['rows'];

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    $out = fopen('php://output', 'w');
    // intestazione
    fputcsv($out, [
        'Serial', 'SKU', 'Crop', 'Variety', 'Packaging', 'Size',
        'Grower', 'Case date/time', 'Shipment', 'Pallet',
        'Operator', 'Station', 'Note', 'Scan time'
    ]);

    foreach ($rows as $r) {
        fputcsv($out, [
            $r['code']        ?? '',
            $r['SKU']         ?? '',
            $r['crop']        ?? '',
            $r['variety']     ?? '',
            $r['packaging']   ?? '',
            $r['size']        ?? '',
            $r['grower']      ?? '',
            $r['DateTime']    ?? '',
            $r['shipment_id'] ?? '',
            $r['pallet_id']   ?? '',
            $r['operator']    ?? '',
            $r['station']     ?? '',
            $r['note']        ?? '',
            $r['read_time']   ?? '',
        ]);
    }

    fclose($out);
    exit;
}

/**
 * Invia il report produzione via email con allegato PDF.
 * - $reason: "manual" o "auto" (per soggetto / body).
 */
function sp_send_production_report(mysqli $mysqli, string $reason = 'manual'): bool
{
    if (!class_exists('Dompdf\\Dompdf')) {
        error_log('sp_send_production_report: Dompdf not available.');
        return false;
    }

    $data = sp_build_production_report_data($mysqli);
    $htmlReport = sp_render_production_report_html($data, SP_REPORT_LOGO_WEB);

    // genera PDF temporaneo
    $options = new Options();
    $options->set('isRemoteEnabled', true);

    $dompdf = new Dompdf($options);
    $dompdf->loadHtml($htmlReport);
    $dompdf->setPaper('A4', 'landscape');
    $dompdf->render();

    $pdfData = $dompdf->output();

    $tmpFile = tempnam(sys_get_temp_dir(), 'prod_report_');
    $pdfFile = $tmpFile . '.pdf';
    file_put_contents($pdfFile, $pdfData);

    $subject = 'Production report (' . ucfirst($reason) . ')';
    $body    = '<p>Automatic production report (' . htmlspecialchars($reason) . ').</p>' .
               '<p>Attached you can find the PDF report.</p>';

    $ok = sp_smtp_send(
        $subject,
        $body,
        null,   // recipients: usa quelli configurati
        null,
        null,
        [ ['path' => $pdfFile, 'name' => 'production_report.pdf'] ]
    );

    @unlink($pdfFile);
    @unlink($tmpFile);

    return $ok;
}
