<?php
require '../vendor/autoload.php'; // Pastikan path ini sesuai dengan autoload.php Anda

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

// Koneksi ke database
require __DIR__ . '/../config/connect.php';
require __DIR__ . '/../config/encrypt.php';

// Dekripsi parameter data
$datadecrypt = decrypt_url($_GET['data'] ?? '');
$dataurl     = json_decode($datadecrypt, true);

$SqlSvData   = $dataurl[0] ?? '';

// Kondisi awal
$where = "WHERE {$SqlSvData} status = 'aktif'";

// Query dasar
$query = "
    SELECT nama, nik, akun, user_hotspot, alamat, latitude, longitude, nomor_whatsapp, email, 
           nama_server, nama_mikrotik, nama_paket, status_koneksi, ip_address, time_update, time_start 
    FROM v_pelanggan 
    {$where}
";


$dataparam = json_decode($_POST['datatable_params'], true);
// Tambahkan kondisi pencarian
$search = $dataparam['search']['value'] ?? '';
if (!empty($search)) {
    $where .= " AND (
        nama LIKE :search OR 
        nik LIKE :search OR 
        akun LIKE :search OR 
        status_koneksi LIKE :search OR 
        nomor_whatsapp LIKE :search OR 
        nama_paket LIKE :search OR 
        nama_server LIKE :search OR 
        alamat LIKE :search  OR 
        time_start LIKE :search
    )";

    $query = "
        SELECT nama, nik, akun, user_hotspot, alamat, latitude, longitude, nomor_whatsapp, email, 
               nama_server, nama_mikrotik, nama_paket, status_koneksi, ip_address, time_update, time_start 
        FROM v_pelanggan 
        {$where}
    ";
}

// Siapkan statement
$stmt = $pdo->prepare($query);

// Bind parameter pencarian
if (!empty($search)) {
    $stmt->bindValue(':search', "%{$search}%", PDO::PARAM_STR);
}

$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Buat Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet       = $spreadsheet->getActiveSheet();

// Set header kolom
$sheet->setCellValue('A1', 'No')
      ->setCellValue('B1', 'Nama')
      ->setCellValue('C1', 'NIK')
      ->setCellValue('D1', 'ID Pelanggan')
      ->setCellValue('E1', 'Akun Hotspot')
      ->setCellValue('F1', 'Alamat')
      ->setCellValue('G1', 'Latitude')
      ->setCellValue('H1', 'Longitude')
      ->setCellValue('I1', 'Nomor Whatsapp')
      ->setCellValue('J1', 'Email')
      ->setCellValue('K1', 'Mulai Berlangganan')
      ->setCellValue('L1', 'Server')
      ->setCellValue('M1', 'Mikrotik')
      ->setCellValue('N1', 'Paket Berlangganan')
      ->setCellValue('O1', 'Status Koneksi')
      ->setCellValue('P1', 'IP Address')
      ->setCellValue('Q1', 'Last Update')
      ->setCellValue('R1', 'RX Power ONU')
      ->setCellValue('S1', 'TX Power ONU');

// Isi data
$row = 2;
$no  = 1;
foreach ($data as $item) {
    // Kolom No otomatis
    $sheet->setCellValue('A' . $row, $no);

    // Kolom text sensitif (pakai Explicit supaya tidak error di Excel)
    $sheet->setCellValueExplicit('B' . $row, $item['nama'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('C' . $row, $item['nik'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('D' . $row, $item['akun'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('E' . $row, $item['user_hotspot'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('F' . $row, $item['alamat'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('G' . $row, $item['latitude'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('H' . $row, $item['longitude'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('I' . $row, $item['nomor_whatsapp'] ?? '', DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('J' . $row, $item['email'] ?? '', DataType::TYPE_STRING);

    // Kolom tanggal & teks biasa
    $sheet->setCellValue('K' . $row, $item['time_start'] ?? '');
    $sheet->setCellValue('L' . $row, $item['nama_server'] ?? '');
    $sheet->setCellValue('M' . $row, $item['nama_mikrotik'] ?? '');
    $sheet->setCellValue('N' . $row, $item['nama_paket'] ?? '');
    $sheet->setCellValue('O' . $row, $item['status_koneksi'] ?? '');
    $sheet->setCellValue('P' . $row, $item['ip_address'] ?? '');
    $sheet->setCellValue('Q' . $row, $item['time_update'] ?? '');

    // RX & TX Power dummy
    $sheet->setCellValue('R' . $row, 'inf');
    $sheet->setCellValue('S' . $row, 'inf');

    $row++;
    $no++;
}

// Set header download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="data_export_pelanggan_aktif.xlsx"');
header('Cache-Control: max-age=0');

// Output file
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;