<?php

use DusanKasan\Knapsack\Collection;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class KorwilAnggaranController extends BaseController
{

    public function list()
    {
        // 
        $getParams = $this->request->get();

        if (array_key_exists('limit', $getParams)) {
            $getParams['limit'] =  $getParams['limit'];
        }
        if (array_key_exists('offset', $getParams)) {
            $getParams['offset'] = $getParams['offset'];
        }
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['template'] = array_key_exists('template', $getParams) ? $getParams['template'] : '';
        $getParams['instansi'] = array_key_exists('instansi', $getParams) ? $getParams['instansi'] : '';

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

        // query
        $q = [
            // 'sort' => ['id' => -1],
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ];

        if ($getParams['template'] !== '' || $getParams['instansi'] !== '') {
            $con1 = $getParams['template'] !== '' ? 'id_template = :template:' : '';
            $con2 = $getParams['instansi'] !== '' ? 'id_instansi = :instansi:' : '';

            $subclause = ($getParams['template'] !== '') && ($getParams['instansi'] !== '') ? ' and ' : '';

            $q['conditions'] = $con1 . $subclause . $con2 . ' and deleted_at is null';
            // $q['conditions'] = [
            //     'template' => $params['template'],
            //     'instansi' => $params['instansi'],
            //     'deleted_at' => 'null'
            // ];
            if ($getParams['template'] !== '') {
                $q['bind']['template'] = $params['template'];
            }
            if ($getParams['instansi'] !== '') {
                $q['bind']['instansi'] = $params['instansi'];
            }
        }

        $kp = KorwilAnggaran::find($q);

        $total_record = KorwilAnggaran::count();

        return new PaginatedResponse($kp, $total_record, $params['limit'], $params['offset'], ['total_data_found' => count($kp)]);
    }

    public function create()
    {
        // $this->checkScopes(['korsupgah.admin']);
        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'id_template' => ['validators' => ['required']],
                'id_instansi' => ['validators' => ['required']],
                'id_periode' => ['validators' => ['required']],
                'anggaran' => ['validators' => ['required']]
            ]
        );

        $ka = new KorwilAnggaran();
        $ka->id_template = $params['id_template'];
        $ka->id_instansi = $params['id_instansi'];
        $ka->id_periode = $params['id_periode'];
        $ka->anggaran = json_encode($params['anggaran']);
        $ka->created_at = date('Y-m-d H:i:s');

        // $ka->created_by = $this->shared->user->um_id;

        $status = $ka->save();

        if (!$status) {
            $errors = $ka->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$ka]);
        }
    }

    public function update($id)
    {
        // $this->checkScopes(['korsupgah.admin']);

        $ka = KorwilAnggaran::findFirst($id);

        if (empty($ka)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'tidak ditemukan');
        endif;

        if (!empty($ka->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id  . 'sudah terhapus');
        endif;

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_template' => ['validators' => ['required']],
                'id_instansi' => ['validators' => ['required']],
                'id_periode' => ['validators' => ['required']],
                'anggaran' => ['validators' => ['required']]
            ]
        );

        $ka->id_template = $params['id_template'];
        $ka->id_instansi = $params['id_instansi'];
        $ka->id_periode = $params['id_periode'];
        $ka->anggaran = json_encode($params['anggaran']);
        $ka->updated_at = date('Y-m-d H:i:s');
        // $ka->updated_by = $this->shared->user->um_id;
        $status = $ka->save();

        if (!$status) {
            $errors = $ka->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$ka]);
        }
    }

    public function delete($id)
    {
        // $this->checkScopes(['korsupgah.admin']);

        $ka = KorwilAnggaran::findFirst($id);

        if (empty($ka)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'tidak diketemukan');
        endif;
        if (!empty($ka->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'sudah terhapus');
        endif;

        $date = date('Y-m-d H:i:s');
        $ka->updated_at = $date;
        $ka->deleted_at = $date;
        // $ka->updated_by = $this->shared->user->um_id;
        $status = $ka->save();

        if (!$status) {
            $errors = $ka->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$ka]);
        }
    }

    public function restore($id)
    {
        // $this->checkScopes(['korsupgah.admin']);
        $ka = KorwilAnggaran::findFirst($id);

        if (empty($ka)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'tidak diketemukan');
        endif;
        if (empty($ka->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'Sudah Aktif');
        endif;

        $date = date('Y-m-d H:i:s');
        $ka->updated_at = $date;
        $ka->deleted_at = null;
        // $ka->updated_by = $this->shared->user->um_id;
        $status = $ka->save();

        if (!$status) {
            $errors = $ka->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$ka]);
        }
    }

    public function detail($id)
    {

        $kp = KorwilAnggaran::findFirst($id);
        if (empty($kp)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id . ' tidak diketemukan');
        endif;

        $result = $kp->toDetail();
        return new CollectionPaginatedResponse([$result]);
    }

    public function updateVerif($id)
    {
        // $this->checkScopes(['korsupgah.admin']);


        $ka = KorwilAnggaran::findFirst($id);

        if (empty($ka)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'tidak ditemukan');
        endif;

        if (!empty($ka->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id  . ' sudah dihapus');
        endif;

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'anggaran_id' => ['validators' => ['required']],
                'verified_status' => ['validators' => ['required']],
                'verified_message' => ['validators' => []],
                'verificator_id' => ['validators' => ['required']],
            ]
        );

        // $ka->id = $params['anggaran_id'];
        $ka->verified_status = $params['verified_status'];
        $ka->verified_message = $params['verified_message'];
        $ka->updated_at = date('Y-m-d H:i:s');
        $ka->verified_at = date('Y-m-d H:i:s');
        $ka->updated_by = $params['verificator_id'];

        $status = $ka->save();

        if (!$status) {
            $errors = $ka->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$ka]);
        }
    }

    private function baseGetReport($id_instansi)
    {
        $res;

        if ($id_instansi === "-1") {
            $res = KorwilAnggaran::query()
                ->columns('*')
                ->leftJoin('KorwilAnggaranTemplate')
                ->leftJoin('Instansi')
                ->leftJoin('KorsupgahPeriode')
                ->leftJoin(MasterProvinsi::class, 'CAST(MasterProvinsi.provinsi_code as INT) = Instansi.id_provinsi')
                ->Where('KorwilAnggaran.deleted_at is NULL')
                ->execute();
        } else {
            $res = KorwilAnggaran::query()
                ->columns('*')
                ->leftJoin('KorwilAnggaranTemplate')
                ->leftJoin('Instansi')
                ->leftJoin('KorsupgahPeriode')
                ->Where('KorwilAnggaran.deleted_at is NULL')
                ->andwhere('KorwilAnggaran.id_instansi = :id_instansi:', ['id_instansi' => $id_instansi])
                ->execute();
        }

        return $res;
    }

    public function getReport($id_instansi)
    {
        $result = $this->baseGetReport($id_instansi);

        return new CollectionPaginatedResponse($result);
    }

    public function getReportAsExcel($id_instansi)
    {
        $report = $this->baseGetReport($id_instansi)->toArray();
        $templates = KorwilAnggaranTemplate::find()->toArray();
        $isReport = $id_instansi === "-1";

        $groupedReport = Collection::from($report)
            ->groupBy(function ($row) {
                return $row->korwilAnggaran->id_template;
            })
            ->toArray();

        $spreadsheet = new Spreadsheet();
        $instansiName = null;

        foreach ($templates as $key => $template) {
            $templateId = $template['id'];
            $sheetName = $template['name'];
            if (strlen($sheetName) > 25) {
                $sheetName = trim(substr($sheetName, 0, 25));
            }
            $sheet = new Worksheet($spreadsheet, $sheetName);
            $spreadsheet->addSheet($sheet);

            $columnIndex = 1;
            $rowIndex = 1;

            $columns = json_decode($template['template'], TRUE)['columns'];

            if ($isReport) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Provinsi');
            }
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Instansi');

            foreach ($columns as $key => $column) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $column['label']);
            }

            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Tanggal Lapor');


            $rowIndex++;

            if (array_key_exists($templateId, $groupedReport)) {

                $templateReport = $groupedReport[$templateId];

                foreach ($templateReport as $key => $record) {
                    $columnIndex = 1;
                    $anggaran = json_decode($record->korwilAnggaran->anggaran, TRUE);

                    if ($isReport) {
                        $provinsiLabel = '-';
                        if (!empty($record->masterProvinsi)) {
                            $provinsiLabel = $record->masterProvinsi->provinsi_name;
                        }
                        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $provinsiLabel);
                    }

                    $instansiLabel = '-';
                    if (!empty($record->instansi)) {
                        $instansiLabel = $record->instansi->nama;

                        if (!$isReport) {
                            $instansiName = $instansiLabel;
                        }
                    }
                    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $instansiLabel);


                    foreach ($columns as $key => $column) {
                        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, (array_key_exists($column['name'], $anggaran) ? $anggaran[$column['name']] : null));
                    }

                    $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $record->korwilAnggaran->created_at);

                    $rowIndex++;
                }
            }
        }

        $spreadsheet->removeSheetByIndex(0);

        $todayLabel = DateHelper::formatTodayIndex();
        $writer = new Xlsx($spreadsheet);

        $fileLabel = $instansiName ?? 'Rekap';
        $filename = $todayLabel . "-PAK-" . $fileLabel .  ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    public function detailProv($id_prov)
    {
        $res = MasterProvinsi::query()
            ->where('MasterProvinsi.provinsi_code = :id_prov:', ['id_prov' => $id_prov])
            ->execute();
        return new CollectionPaginatedResponse($res);
    }
}