<?php
require_once 'db.php';
session_start();

if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit;
}
$instansi_id = $_SESSION['instansi_id'];
$source = $_GET['source'] ?? 'utama'; // 'utama' atau 'executive'

// Parameter Filter (Khusus Utama)
$filter_bulan = $_GET['bulan'] ?? date('m');
$filter_tahun = $_GET['tahun'] ?? date('Y');
$filter_tanggal = $_GET['tanggal'] ?? '';
$filter_search = $_GET['search'] ?? '';

$start_date = "$filter_tahun-" . str_pad($filter_bulan, 2, '0', STR_PAD_LEFT) . "-01 00:00:00";
$end_date = date('Y-m-t 23:59:59', strtotime($start_date));

$where_clause = "b.instansi_id = :instansi_id AND b.tanggal >= :start_date AND b.tanggal <= :end_date";
$params = [
    ':instansi_id' => $instansi_id,
    ':start_date' => $start_date,
    ':end_date' => $end_date
];

if ($filter_tanggal != '') {
    $exact_date = "$filter_tahun-" . str_pad($filter_bulan, 2, '0', STR_PAD_LEFT) . "-" . str_pad($filter_tanggal, 2, '0', STR_PAD_LEFT);
    $where_clause = "b.instansi_id = :instansi_id AND b.tanggal >= :start_date_day AND b.tanggal <= :end_date_day";
    $params = [
        ':instansi_id' => $instansi_id,
        ':start_date_day' => "$exact_date 00:00:00",
        ':end_date_day' => "$exact_date 23:59:59"
    ];
}

if ($filter_search != '') {
    $where_clause .= " AND (b.judul LIKE :search OR b.cuplikan LIKE :search)";
    $params[':search'] = "%$filter_search%";
}

// Khusus Executive: "Hari Ini" untuk KPI
if ($source === 'executive') {
    $hari_ini = date('Y-m-d');
    $where_clause = "b.instansi_id = :instansi_id AND b.tanggal >= :start_date AND b.tanggal <= :end_date";
    $params = [
        ':instansi_id' => $instansi_id,
        ':start_date' => "$hari_ini 00:00:00",
        ':end_date' => "$hari_ini 23:59:59"
    ];
}

// 1. KPI Stats
$stmt_stat = $pdo->prepare("SELECT 
    COUNT(b.id) as total,
    SUM(CASE WHEN b.sentimen IN ('positif', 'baik') THEN 1 ELSE 0 END) as positif,
    SUM(CASE WHEN b.sentimen IN ('negatif', 'buruk') THEN 1 ELSE 0 END) as negatif,
    COUNT(DISTINCT b.sumber) as jml_media
    FROM berita b WHERE $where_clause");
$stmt_stat->execute($params);
$stat_data = $stmt_stat->fetch(PDO::FETCH_ASSOC);

$stat_total = $stat_data['total'] ?? 0;
$stat_positif = $stat_data['positif'] ?? 0;
$stat_negatif = $stat_data['negatif'] ?? 0;
$stat_netral = $stat_total - $stat_positif - $stat_negatif;
$stat_media = $stat_data['jml_media'] ?? 0;

// 2. Data Berita (Table)
$stmt_berita = $pdo->prepare("SELECT b.judul, b.tanggal, b.sumber, b.sentimen, b.kategori_berita, b.urgensi, b.link FROM berita b WHERE $where_clause ORDER BY b.tanggal DESC");
$stmt_berita->execute($params);
$berita_list = $stmt_berita->fetchAll(PDO::FETCH_ASSOC);

// Header Excel
header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=\"Export_Dashboard_BAPINTAR_" . date('Ymd_His') . ".xls\"");
header("Pragma: no-cache");
header("Expires: 0");

echo "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">";
echo "<head><meta charset='UTF-8'></head>";
echo "<body>";
echo "<table border='1'>";
echo "<tr><td colspan='7' style='font-size:16px; font-weight:bold; text-align:center;'>LAPORAN DASHBOARD BAPINTAR</td></tr>";
echo "<tr><td colspan='7' style='text-align:center;'>Dicetak pada: " . date('Y-m-d H:i:s') . "</td></tr>";
echo "<tr><td colspan='7'></td></tr>";

echo "<tr><th colspan='7' style='background-color:#0f172a; color:#ffffff;'>RINGKASAN METRIK (" . strtoupper($source) . ")</th></tr>";
echo "<tr>";
echo "<th>Total Berita</th>";
echo "<th>Positif</th>";
echo "<th>Negatif</th>";
echo "<th>Netral</th>";
echo "<th colspan='3'>Jumlah Media</th>";
echo "</tr>";
echo "<tr>";
echo "<td align='center'>" . number_format($stat_total) . "</td>";
echo "<td align='center'>" . number_format($stat_positif) . "</td>";
echo "<td align='center'>" . number_format($stat_negatif) . "</td>";
echo "<td align='center'>" . number_format($stat_netral) . "</td>";
echo "<td align='center' colspan='3'>" . number_format($stat_media) . "</td>";
echo "</tr>";
echo "<tr><td colspan='7'></td></tr>";

echo "<tr><th colspan='7' style='background-color:#0f172a; color:#ffffff;'>DAFTAR BERITA</th></tr>";
echo "<tr>";
echo "<th>No</th>";
echo "<th>Judul Berita</th>";
echo "<th>Sumber</th>";
echo "<th>Tanggal</th>";
echo "<th>Sentimen</th>";
echo "<th>Kategori</th>";
echo "<th>Link</th>";
echo "</tr>";

$no = 1;
foreach ($berita_list as $b) {
    echo "<tr>";
    echo "<td>" . $no++ . "</td>";
    echo "<td>" . htmlspecialchars($b['judul'] ?? '') . "</td>";
    echo "<td>" . htmlspecialchars($b['sumber'] ?? '') . "</td>";
    echo "<td>" . htmlspecialchars($b['tanggal'] ?? '') . "</td>";
    echo "<td>" . htmlspecialchars($b['sentimen'] ?? '') . "</td>";
    echo "<td>" . htmlspecialchars($b['kategori_berita'] ?? '') . "</td>";
    echo "<td>" . htmlspecialchars($b['link'] ?? '') . "</td>";
    echo "</tr>";
}
echo "</table>";
echo "</body></html>";
exit;
