<?php
require_once 'includes/config.php';
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

// Ambil data dengan pressed_button = 1
$stmt = $db->query("SELECT phone_number, pressed_button, status, call_date 
                    FROM target_numbers 
                    WHERE pressed_button = '1' 
                    ORDER BY call_date DESC");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

// Set header kolom
$sheet->setCellValue('A1', 'No');
$sheet->setCellValue('B1', 'Phone Number');
$sheet->setCellValue('C1', 'Pressed Button');
$sheet->setCellValue('D1', 'Status');
$sheet->setCellValue('E1', 'Date and Time');

// Isi data
$row = 2;
$no = 1;
foreach ($data as $item) {
    $sheet->setCellValue('A' . $row, $no);
    // Set nomor telepon sebagai string dengan format khusus
    $sheet->setCellValueExplicit(
        'B' . $row, 
        $item['phone_number'],
        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
    );
    $sheet->setCellValue('C' . $row, $item['pressed_button']);
    $sheet->setCellValue('D' . $row, $item['status']);
    $sheet->setCellValue('E' . $row, date('d-M-Y H:i:s', strtotime($item['call_date'])));
    $row++;
    $no++;
}

// Set format kolom nomor telepon sebagai text
$sheet->getStyle('B2:B' . ($row-1))
    ->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_TEXT);

// Auto size kolom
foreach (range('A', 'E') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

// Set style header
$headerStyle = [
    'font' => [
        'bold' => true,
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => [
            'rgb' => 'E2EFDA',
        ],
    ],
];
$sheet->getStyle('A1:E1')->applyFromArray($headerStyle);

// Buat border untuk semua sel yang berisi data
$borderStyle = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
    ],
];
$sheet->getStyle('A1:E' . ($row - 1))->applyFromArray($borderStyle);

// Set nama file
$filename = 'Success_Report_' . date('Y-m-d_H-i-s') . '.xlsx';

// Hapus output buffer
if (ob_get_length()) ob_end_clean();

// Set header untuk download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: max-age=0');
header('Cache-Control: must-revalidate');
header('Pragma: public');

// Tulis file ke temporary file dulu
$temp_file = tempnam(sys_get_temp_dir(), 'excel_');
$writer = new Xlsx($spreadsheet);
$writer->save($temp_file);

// Baca dan output file
readfile($temp_file);
unlink($temp_file); // Hapus temporary file
exit; 