<?php
session_name('kkn_session');
session_start();

require_once dirname(__DIR__) . '/includes/config.php';
require_once dirname(__DIR__) . '/includes/db.php';

if (!isset($_SESSION['kkn_user_id']) || ($_SESSION['kkn_role'] ?? '') !== 'admin') {
    http_response_code(403);
    exit('Akses ditolak. Silakan login sebagai admin terlebih dahulu.');
}

$tbl_luaran_kel = fetch("SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='kkn_luaran_kelompok'");
if (!$tbl_luaran_kel) { echo 'Tabel belum ada.'; exit; }

$rows = fetchAll("SELECT d.kecamatan, d.kabupaten, d.nama as nama_dpl,
    MAX(CASE WHEN lk.jenis='rpk' THEN lk.judul END) as j_rpk,
    MAX(CASE WHEN lk.jenis='rpk' THEN lk.link_url END) as f_rpk,
    MAX(CASE WHEN lk.jenis='laporan' THEN lk.judul END) as j_lap,
    MAX(CASE WHEN lk.jenis='laporan' THEN lk.link_url END) as f_lap,
    MAX(CASE WHEN lk.jenis='poster' THEN lk.judul END) as j_pos,
    MAX(CASE WHEN lk.jenis='poster' THEN lk.link_url END) as f_pos,
    MAX(CASE WHEN lk.jenis='video' THEN lk.judul END) as j_vid,
    MAX(CASE WHEN lk.jenis='video' THEN lk.link_url END) as l_vid,
    (SELECT CONCAT(m2.nama, ' (', m2.nim, ')')
     FROM kkn_luaran_kelompok lk2
     JOIN kkn_mahasiswa m2 ON m2.id=lk2.ketua_id
     WHERE lk2.dpl_id=d.id AND lk2.ketua_id IS NOT NULL
     ORDER BY lk2.uploaded_at DESC LIMIT 1) as input_nama
    FROM kkn_dpl d
    LEFT JOIN kkn_luaran_kelompok lk ON lk.dpl_id=d.id
    GROUP BY d.id ORDER BY d.kecamatan");

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=luaran_kelompok_'.date('Ymd').'.csv');
header('Cache-Control: no-cache, no-store, must-revalidate');
echo "\xEF\xBB\xBF";
echo "No,Kecamatan,Kabupaten,DPL,Judul RPK,Link RPK,St.RPK,Judul Laporan,Link Laporan,St.Laporan,Judul Poster,Link Poster,St.Poster,Judul Video,Link Video,St.Video,Diinput oleh\n";
$no = 1;
foreach ($rows as $r) {
    echo implode(',', [
        $no++,
        '"'.$r['kecamatan'].'"',
        '"'.$r['kabupaten'].'"',
        '"'.str_replace('"','""',$r['nama_dpl']).'"',
        '"'.str_replace('"','""',($r['j_rpk']??'')).'"',
        '"'.str_replace('"','""',($r['f_rpk']??'')).'"',
        $r['f_rpk'] ? 'Sudah' : 'Belum',
        '"'.str_replace('"','""',($r['j_lap']??'')).'"',
        '"'.str_replace('"','""',($r['f_lap']??'')).'"',
        $r['f_lap'] ? 'Sudah' : 'Belum',
        '"'.str_replace('"','""',($r['j_pos']??'')).'"',
        '"'.str_replace('"','""',($r['f_pos']??'')).'"',
        $r['f_pos'] ? 'Sudah' : 'Belum',
        '"'.str_replace('"','""',($r['j_vid']??'')).'"',
        '"'.str_replace('"','""',($r['l_vid']??'')).'"',
        $r['l_vid'] ? 'Sudah' : 'Belum',
        '"'.str_replace('"','""',($r['input_nama']??'')).'"',
    ])."\n";
}
exit;
