<?php

namespace App\Http\Controllers;

use App\Models\Tamu;

class LaporanController extends Controller
{
    public function index()
    {
        $totalPengunjung = Tamu::count();
        $dataPengunjung = Tamu::latest()->get();
        $laporanBulanan = Tamu::selectRaw('MONTH(created_at) as bulan, COUNT(*) as jumlah')
            ->groupBy('bulan')
            ->get();

        return view('laporan', compact(
            'totalPengunjung',
            'dataPengunjung',
            'laporanBulanan'
        ));
    }

    public function exportExcel()
    {
        if (!session('login')) {
            return redirect('/login');
        }

        $month = request('month');
        $query = Tamu::query();

        if ($month) {
            $query->whereMonth('created_at', $month);
        }

        $dataPengunjung = $query->latest()->get();
        $filename = $month ? 'laporan-pengunjung-' . $month . '.xlsx' : 'laporan-pengunjung.xlsx';

        $rows = [];
        $rows[] = ['Nama', 'Instansi', 'Keperluan', 'Tanggal', 'Jam', 'Bertemu Dengan'];

        foreach ($dataPengunjung as $item) {
            $rows[] = [$item->nama, $item->instansi, $item->keperluan, $item->tanggal, $item->jam, $item->bertemu_dengan];
        }

        $content = $this->buildXlsxContent($rows);
        $path = tempnam(sys_get_temp_dir(), 'laporan_xlsx_');
        file_put_contents($path, $content);

        return response()->download($path, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ])->deleteFileAfterSend(true);
    }

    private function buildXlsxContent(array $rows): string
    {
        $workbookXml = <<<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets>
    <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
  </sheets>
</workbook>
XML;

        $workbookRelsXml = <<<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
</Relationships>
XML;

        $contentTypesXml = <<<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml" ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
</Types>
XML;

        $sheetRows = '';

        foreach ($rows as $rowIndex => $row) {
            $rowNumber = $rowIndex + 1;
            $sheetRows .= "    <row r=\"{$rowNumber}\">\n";

            foreach ($row as $colIndex => $value) {
                $cellRef = $this->getExcelColumn($colIndex) . $rowNumber;
                $safeValue = htmlspecialchars((string) $value, ENT_XML1 | ENT_QUOTES, 'UTF-8');
                $sheetRows .= "      <c r=\"{$cellRef}\" t=\"inlineStr\"><is><t>{$safeValue}</t></is></c>\n";
            }

            $sheetRows .= "    </row>\n";
        }

        $sheetXml = <<<XML
<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheetData>
{$sheetRows}  </sheetData>
</worksheet>
XML;

        $tmpFile = tempnam(sys_get_temp_dir(), 'xlsx');
        $zip = new \ZipArchive();
        $zip->open($tmpFile, \ZipArchive::CREATE | \ZipArchive::OVERWRITE);
        $zip->addFromString('[Content_Types].xml', $contentTypesXml);
        $zip->addFromString('_rels/.rels', <<<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>
XML
);
        $zip->addFromString('xl/workbook.xml', $workbookXml);
        $zip->addFromString('xl/_rels/workbook.xml.rels', $workbookRelsXml);
        $zip->addFromString('xl/worksheets/sheet1.xml', $sheetXml);
        $zip->close();

        $content = file_get_contents($tmpFile);
        unlink($tmpFile);

        return $content;
    }

    private function getExcelColumn(int $index): string
    {
        $letters = '';
        $index++;

        while ($index > 0) {
            $remainder = ($index - 1) % 26;
            $letters = chr(65 + $remainder) . $letters;
            $index = intdiv($index - 1, 26);
        }

        return $letters;
    }
}
