<?php
require_once __DIR__ . '/../config/user_functions.php';
if (session_status() === PHP_SESSION_NONE) { session_start(); }
require_once __DIR__ . '/../config/users_local.php';

if (function_exists('user_has_permission') && !user_has_permission('reports')) {
    http_response_code(403);
    echo "Access denied";
    exit;
}

require_once __DIR__ . '/../config/db_remote.php';
require_once __DIR__ . '/../config/emailer.php';

$format = strtolower($_GET['format'] ?? 'pdf');
$range  = strtolower($_GET['range']  ?? 'today');
$start  = $_GET['start'] ?? null;
$end    = $_GET['end']   ?? null;

// ===== RANGE =====
$today = date('Y-m-d');
$where = '';
$rangeLabel = '';

switch ($range) {
    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':
        $startDate = $start ?: $today;
        $endDate   = $end   ?: $today;
        $startEsc  = $mysqli->real_escape_string($startDate);
        $endEsc    = $mysqli->real_escape_string($endDate);
        $where = "br.read_time >= '{$startEsc} 00:00:00' AND br.read_time <= '{$endEsc} 23:59:59'";
        $rangeLabel = "From {$startDate} to {$endDate}";
        break;
    case 'today':
    default:
        $range = 'today';
        $where = "DATE(br.read_time) = '{$today}'";
        $rangeLabel = "Today ({$today})";
        break;
}

// ===== SUMMARY =====
$sql = "
    SELECT
        COUNT(*) AS total_cases,
        COUNT(DISTINCT cc.grower)  AS growers,
        COUNT(DISTINCT cc.size)    AS sizes,
        COUNT(DISTINCT cc.variety) AS varieties
    FROM barcode_readings br
    LEFT JOIN casecodes cc ON cc.serial = br.code
    WHERE {$where}
";
$res = $mysqli->query($sql);
if (!$res) {
    http_response_code(500);
    echo "Query error";
    exit;
}
$summary = $res->fetch_assoc() ?: [];
$res->free();

$totalCases = (int)($summary['total_cases'] ?? 0);
$growers    = (int)($summary['growers'] ?? 0);
$sizes      = (int)($summary['sizes'] ?? 0);
$varieties  = (int)($summary['varieties'] ?? 0);

if ($totalCases <= 0) {
    // Nessun dato: esportiamo comunque un file "vuoto" semplice
    if ($format === 'csv') {
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="partial_production_empty.csv"');
        $out = fopen('php://output', 'w');
        fputcsv($out, ['Message']);
        fputcsv($out, ['No data for selected range.']);
        fclose($out);
        exit;
    } elseif ($format === 'excel') {
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="partial_production_empty.xls"');
        echo "<table><tr><th>Message</th></tr><tr><td>No data for selected range.</td></tr></table>";
        exit;
    } else { // pdf o altro
        header('Content-Type: text/plain; charset=utf-8');
        echo "No data for selected range.";
        exit;
    }
}

// ===== DETAIL: grower / size / variety =====
$sql = "
    SELECT
        COALESCE(NULLIF(TRIM(cc.grower), ''),  '(n/a)') AS grower_label,
        COALESCE(NULLIF(TRIM(cc.size), ''),    '(n/a)') AS size_label,
        COALESCE(NULLIF(TRIM(cc.variety), ''), '(n/a)') AS variety_label,
        COUNT(*) AS total
    FROM barcode_readings br
    LEFT JOIN casecodes cc ON cc.serial = br.code
    WHERE {$where}
    GROUP BY
        COALESCE(NULLIF(TRIM(cc.grower),  ''), '(n/a)'),
        COALESCE(NULLIF(TRIM(cc.size),    ''), '(n/a)'),
        COALESCE(NULLIF(TRIM(cc.variety), ''), '(n/a)')
    ORDER BY grower_label, size_label, variety_label
";
$rows = [];
$res = $mysqli->query($sql);
if ($res) {
    while ($r = $res->fetch_assoc()) {
        $rows[] = $r;
    }
    $res->free();
}

// ===== EXPORT =====
$filenameBase = 'partial_production_' . $today;

if ($format === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filenameBase . '.csv"');
    $out = fopen('php://output', 'w');

    fputcsv($out, ['Range', $rangeLabel]);
    fputcsv($out, []);
    fputcsv($out, ['Total cases', $totalCases]);
    fputcsv($out, ['Unique growers', $growers]);
    fputcsv($out, ['Unique sizes', $sizes]);
    fputcsv($out, ['Unique varieties', $varieties]);
    fputcsv($out, []);
    fputcsv($out, ['Grower', 'Size', 'Variety', 'Cases']);

    foreach ($rows as $r) {
        fputcsv($out, [
            $r['grower_label'],
            $r['size_label'],
            $r['variety_label'],
            $r['total'],
        ]);
    }

    fclose($out);
    exit;
}

if ($format === 'excel') {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="' . $filenameBase . '.xls"');
    echo "<table border='1' cellspacing='0' cellpadding='4'>";
    echo "<tr><th colspan='4'>Partial production report</th></tr>";
    echo "<tr><td colspan='4'>Range: " . htmlspecialchars($rangeLabel) . "</td></tr>";
    echo "<tr><td>Total cases</td><td>" . (int)$totalCases . "</td><td>Unique growers</td><td>" . (int)$growers . "</td></tr>";
    echo "<tr><td>Unique sizes</td><td>" . (int)$sizes . "</td><td>Unique varieties</td><td>" . (int)$varieties . "</td></tr>";
    echo "<tr><td colspan='4'></td></tr>";
    echo "<tr><th>Grower</th><th>Size</th><th>Variety</th><th>Cases</th></tr>";

    foreach ($rows as $r) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($r['grower_label']) . "</td>";
        echo "<td>" . htmlspecialchars($r['size_label']) . "</td>";
        echo "<td>" . htmlspecialchars($r['variety_label']) . "</td>";
        echo "<td align='right'>" . (int)$r['total'] . "</td>";
        echo "</tr>";
    }

    echo "</table>";
    exit;
}

// ===== PDF (default) =====
if (!class_exists('Dompdf\\Dompdf')) {
    header('Content-Type: text/plain; charset=utf-8');
    echo "Dompdf not available.";
    exit;
}

$logoPath = realpath(__DIR__ . '/../assets/logo.png');
$logoHtml = '';
if ($logoPath && is_file($logoPath)) {
    $logoHtml = '<img src="file://' . $logoPath . '" style="height:60px;">';
}

ob_start();
?>
<html>
<head>
  <meta charset="UTF-8">
  <style>
    body { font-family: DejaVu Sans, Arial, sans-serif; font-size: 11px; }
    h1, h2, h3 { margin: 0 0 6px 0; }
    table { border-collapse: collapse; width: 100%; }
    th, td { border: 1px solid #ccc; padding: 4px; }
    th { background-color: #f0f0f0; }
    .summary-table td { border: none; }
    .logo-row { margin-bottom: 10px; }
  </style>
</head>
<body>
  <div class="logo-row">
    <?= $logoHtml ?>
  </div>
  <h1>Partial Production Report</h1>
  <p>Range: <strong><?= htmlspecialchars($rangeLabel) ?></strong></p>

  <h3>Summary</h3>
  <table class="summary-table">
    <tr>
      <td>Total cases:</td>
      <td><strong><?= (int)$totalCases ?></strong></td>
    </tr>
    <tr>
      <td>Unique growers:</td>
      <td><strong><?= (int)$growers ?></strong></td>
    </tr>
    <tr>
      <td>Unique sizes:</td>
      <td><strong><?= (int)$sizes ?></strong></td>
    </tr>
    <tr>
      <td>Unique varieties:</td>
      <td><strong><?= (int)$varieties ?></strong></td>
    </tr>
  </table>

  <h3>Breakdown by grower / size / variety</h3>
  <table>
    <thead>
      <tr>
        <th align="left">Grower</th>
        <th align="left">Size</th>
        <th align="left">Variety</th>
        <th align="right">Cases</th>
      </tr>
    </thead>
    <tbody>
      <?php if (empty($rows)): ?>
        <tr><td colspan="4" align="center">No detail rows.</td></tr>
      <?php else: ?>
        <?php foreach ($rows as $r): ?>
          <tr>
            <td><?= htmlspecialchars($r['grower_label']) ?></td>
            <td><?= htmlspecialchars($r['size_label']) ?></td>
            <td><?= htmlspecialchars($r['variety_label']) ?></td>
            <td align="right"><?= (int)$r['total'] ?></td>
          </tr>
        <?php endforeach; ?>
      <?php endif; ?>
    </tbody>
  </table>
</body>
</html>
<?php
$html = ob_get_clean();

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

$pdfOutput = $dompdf->output();

header('Content-Type: application/pdf');
header('Content-Disposition: attachment; filename="' . $filenameBase . '.pdf"');
echo $pdfOutput;
exit;
