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

header('Content-Type: application/json');

if (!isset($_SESSION['user_id'])) {
    echo json_encode(['status' => 'error', 'message' => 'Unauthorized']);
    exit;
}

$instansi_id = $_SESSION['instansi_id'];
$periode = $_POST['periode'] ?? 'harian';
$start_date = $_POST['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$end_date = $_POST['end_date'] ?? date('Y-m-d');
$skpd_id = $_POST['skpd_id'] ?? '';

// Validasi
if (empty($start_date) || empty($end_date)) {
    echo json_encode(['status' => 'error', 'message' => 'Tanggal awal dan akhir harus diisi']);
    exit;
}

try {
    // Bangun Where Clause
    $where = "b.instansi_id = :instansi_id AND b.tanggal >= :start_date AND b.tanggal <= :end_date";
    $params = [
        ':instansi_id' => $instansi_id,
        ':start_date' => $start_date . " 00:00:00",
        ':end_date' => $end_date . " 23:59:59"
    ];

    if (!empty($skpd_id)) {
        // Ambil SKPD info
        $stmt_skpd = $pdo->prepare("SELECT nama_skpd, singkatan, keywords FROM skpd WHERE id = ? AND instansi_id = ?");
        $stmt_skpd->execute([$skpd_id, $instansi_id]);
        $skpd_data = $stmt_skpd->fetch(PDO::FETCH_ASSOC);

        if ($skpd_data) {
            $key_name = !empty($skpd_data['singkatan']) ? strtolower($skpd_data['singkatan']) : strtolower($skpd_data['nama_skpd']);
            $kw_array = array_filter(array_map('trim', explode(',', strtolower($skpd_data['keywords'] ?? ''))));
            
            // Query filter SKPD berdasarkan skpd_id di tabel atau keyword di judul/cuplikan
            $skpd_conds = ["b.skpd_id = :skpd_id"];
            $params[':skpd_id'] = $skpd_id;
            
            $skpd_conds[] = "LOWER(b.judul) LIKE :skpd_name OR LOWER(b.cuplikan) LIKE :skpd_name";
            $params[':skpd_name'] = "%$key_name%";
            
            foreach ($kw_array as $idx => $kw) {
                if(strlen($kw) > 2) {
                    $pname = ":kw_$idx";
                    $skpd_conds[] = "LOWER(b.judul) LIKE $pname OR LOWER(b.cuplikan) LIKE $pname";
                    $params[$pname] = "%$kw%";
                }
            }
            
            $where .= " AND (" . implode(" OR ", $skpd_conds) . ")";
        }
    }

    // Tentukan Grouping Berdasarkan Periode
    $group_expr = "";
    switch ($periode) {
        case 'harian':
            $group_expr = "DATE(b.tanggal)";
            break;
        case 'mingguan':
            $group_expr = "YEARWEEK(b.tanggal, 1)"; // 1 = Monday as start of week
            break;
        case 'bulanan':
            $group_expr = "DATE_FORMAT(b.tanggal, '%Y-%m')";
            break;
        case 'tahunan':
            $group_expr = "YEAR(b.tanggal)";
            break;
        default:
            $group_expr = "DATE(b.tanggal)";
    }

    $query = "SELECT $group_expr as time_period, b.sentimen, COUNT(b.id) as jumlah 
              FROM berita b 
              WHERE $where 
              GROUP BY time_period, b.sentimen 
              ORDER BY time_period ASC";
              
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    $raw_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Proses Data menjadi Format Terstruktur
    $grouped_data = [];
    foreach ($raw_data as $row) {
        $tp = $row['time_period'];
        $sentimen = strtolower($row['sentimen']);
        
        if (!isset($grouped_data[$tp])) {
            $grouped_data[$tp] = ['positif' => 0, 'negatif' => 0, 'netral' => 0];
        }
        
        if ($sentimen == 'positif' || $sentimen == 'baik') {
            $grouped_data[$tp]['positif'] += (int)$row['jumlah'];
        } elseif ($sentimen == 'negatif' || $sentimen == 'buruk') {
            $grouped_data[$tp]['negatif'] += (int)$row['jumlah'];
        } else {
            $grouped_data[$tp]['netral'] += (int)$row['jumlah'];
        }
    }

    // Generate Rentang Waktu Lengkap (Data Imputation)
    $start = new DateTime($start_date);
    $end = new DateTime($end_date);
    
    // Pastikan tidak infinite loop jika start > end
    if ($start > $end) {
        $temp = $start;
        $start = $end;
        $end = $temp;
    }

    $labels = [];
    $data_pos = [];
    $data_neg = [];
    $data_net = [];

    $current = clone $start;
    
    // Helper function to format labels
    function getLabel($period, $dateObj, $tp_key) {
        $bulan_indo = ['01'=>'Jan', '02'=>'Feb', '03'=>'Mar', '04'=>'Apr', '05'=>'Mei', '06'=>'Jun', '07'=>'Jul', '08'=>'Agt', '09'=>'Sep', '10'=>'Okt', '11'=>'Nov', '12'=>'Des'];
        switch ($period) {
            case 'harian':
                return $dateObj->format('d ') . $bulan_indo[$dateObj->format('m')] . $dateObj->format(' y');
            case 'mingguan':
                $year = substr($tp_key, 0, 4);
                $week = substr($tp_key, 4, 2);
                return "W$week $year";
            case 'bulanan':
                return $bulan_indo[$dateObj->format('m')] . ' ' . $dateObj->format('Y');
            case 'tahunan':
                return $dateObj->format('Y');
        }
        return $tp_key;
    }

    while ($current <= $end) {
        $tp_key = "";
        $label = "";
        
        switch ($periode) {
            case 'harian':
                $tp_key = $current->format('Y-m-d');
                $label = getLabel('harian', $current, $tp_key);
                $current->modify('+1 day');
                break;
            case 'mingguan':
                $tp_key = $current->format('oW'); // o = ISO-8601 year, W = week number
                $label = getLabel('mingguan', $current, $tp_key);
                $current->modify('+1 week');
                break;
            case 'bulanan':
                $tp_key = $current->format('Y-m');
                $label = getLabel('bulanan', $current, $tp_key);
                $current->modify('+1 month');
                break;
            case 'tahunan':
                $tp_key = $current->format('Y');
                $label = getLabel('tahunan', $current, $tp_key);
                $current->modify('+1 year');
                break;
        }

        // Hindari duplikasi label (terutama untuk mingguan/bulanan jika melintasi hari)
        if (!in_array($label, $labels)) {
            $labels[] = $label;
            
            if (isset($grouped_data[$tp_key])) {
                $data_pos[] = $grouped_data[$tp_key]['positif'];
                $data_neg[] = $grouped_data[$tp_key]['negatif'];
                $data_net[] = $grouped_data[$tp_key]['netral'];
            } else {
                $data_pos[] = 0;
                $data_neg[] = 0;
                $data_net[] = 0;
            }
        }
    }

    echo json_encode([
        'status' => 'success',
        'labels' => $labels,
        'series' => [
            ['name' => 'Positif', 'data' => $data_pos],
            ['name' => 'Negatif', 'data' => $data_neg],
            ['name' => 'Netral', 'data' => $data_net]
        ]
    ]);

} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}
