<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class SpiDataAccessorController extends BaseController
{

    /**
     * Fetching the data of SPI Data Accessor
     * @return PaginatedResponse
     */
    public function getAll()
    {
        $this->checkIsAuthenticated();

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';

        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
            ]
        );

        $listSpiDataAccessor = SpiDataAccessor::query()
            ->columns("SpiDataAccessor.accessed_at, UmUser.nama, UmUser.phone, UmUser.email, UmUser.jenis_kelamin, SpiDataAccessor.background, SpiDataAccessor.purpose")
            ->join('UmUser', 'UmUser.um_id = SpiDataAccessor.user_id')
            ->where("background ILIKE :keyword: OR purpose ILIKE :keyword:")
            ->bind(['keyword' => '%'.$params['keyword'].'%'])
            ->orderBy('SpiDataAccessor.accessed_at DESC')
            ->limit($params['limit'], $params['offset'])
            ->execute()
            ->toArray();

        $totalCount = SpiDataAccessor::find([
            'conditions' => 'background ILIKE :keyword: OR purpose ILIKE :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ]
        ])->count();
        return new PaginatedResponse($listSpiDataAccessor, $totalCount, $params["limit"], $params["offset"]);
    }

    /**
     * Storing the data of SPI Data Accessor
     * @return SimpleResponse
     */
    public function store()
    {
        $this->checkIsAuthenticated();
        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'background' => ['validators' => ['required']],
                'purpose' => ['validators' => ['required']]
            ]
        );

        $spiDataAccessor = new SpiDataAccessor([
            'user_id' => $this->shared->user->um_id,
            'background' => $params['background'],
            'purpose' => $params['purpose'],
            'accessed_at' => DateHelper::getNowTimezoned()
        ]);
        $spiDataAccessor->save();
        return new SimpleResponse($spiDataAccessor->toDetail());
    }


    /**
     * Download spreadsheet of all SPI Data Accessor
     * @return mixed
     */
    public function download() {
        $this->checkIsAuthenticated();

        $this->checkOneOfScopes(['spi.manage_master_data']);
        
        // 1. Fetch the data
        $columns = [
            "TO_CHAR(SpiDataAccessor.accessed_at, 'DD-MM-YYYY HH24:MI:SS') AS accessed_at",
            "UmUser.nama",
            "UmUser.phone",
            "UmUser.email",
            "UmUser.jenis_kelamin",
            "SpiDataAccessor.background",
            "SpiDataAccessor.purpose"
        ];
        $listSpiDataAccessor = SpiDataAccessor::query()
            ->columns(implode(',', $columns))
            ->join('UmUser', 'UmUser.um_id = SpiDataAccessor.user_id')
            ->orderBy('SpiDataAccessor.accessed_at DESC')
            ->execute()
            ->toArray();

        // 2. Write the data into a spreadsheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $todayLabel = DateHelper::formatToday();

        $sheet->setCellValue("A1", 'Daftar Laporan Akses Data Detail SPI');
        $sheet->setCellValue("A2", 'Diunduh Pada ' . $todayLabel);
        $sheet->getStyle('A1:A2')
            ->getAlignment()
            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:G1');
        $sheet->mergeCells('A2:G2');

        $sheet->setCellValue('A4', 'Tanggal Akses');
        $sheet->setCellValue('B4', 'Nama');
        $sheet->setCellValue('C4', 'Gender');
        $sheet->setCellValue('D4', 'Phone');
        $sheet->setCellValue('E4', 'Email');
        $sheet->setCellValue('F4', 'Profesi');
        $sheet->setCellValue('G4', 'Kebutuhan');

        $sheet->getStyle('A4:G4')
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

        $index = 4;
        foreach($listSpiDataAccessor as $row) {
            $index++;
            $sheet->setCellValue("A$index", $row['accessed_at']);
            $sheet->setCellValue("B$index", $row['nama']);
            $sheet->setCellValue("C$index", $row['jenis_kelamin']);
            $sheet->setCellValue("D$index", $row['phone']);
            $sheet->setCellValue("E$index", $row['email']);
            $sheet->setCellValue("F$index", $row['background']);
            $sheet->setCellValue("G$index", $row['purpose']);
        }

        $writer = new Xlsx($spreadsheet);
        $todayLabel = DateHelper::formatTodayIndex();
        $filename = $todayLabel . "-SPI-data-accessor.xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }
}