<?php

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

class StranasUtilityController extends BaseController
{
    public function unfinishedTargetList()
    {
        $this->checkScopes(['stranas.admin']);

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

        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
            ]
        );

        $periode = StranasPeriode::findFirst($params['id_periode']);

        if (empty($periode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode tidak ditemukan');
        }
        $result = $this->rawQuery("
        select p.nama as periode,
            i.nama as instansi,
            i.id as id_instansi,
            s.id as id_survey,
            q.id_target as id_target,
            tw.keterangan as target,
            answer.nilai as nilai,
            answer.keterangan as keterangan,
            answer.nilai_verification as nilai_verif,
            answer.keterangan_verification as keterangan_verif
        from jaga.stranas_survey s join jaga.stranas_survey_template t on s.id_survey_template = t.id
        	join jaga.stranas_survey_template_question q on t.id = q.id_survey_template
        	join jaga.stranas_target tw on q.id_target = tw.id
        	join jaga.instansi i on s.id_instansi_satker = i.id
        	join jaga.stranas_periode p on s.id_periode = p.id
        	left outer join jaga.stranas_survey_answer answer on answer.id_survey = s.id AND answer.id_question = q.id_target
        where
        	s.id_periode = :id_periode AND (answer.nilai_verification is null OR answer.nilai_verification < 100)
        order by i.id", [
            'id_periode' => $params['id_periode']
        ]);

        $header = ["Periode", "Instansi", "ID Instansi", "ID Survey", "Target", "ID Target",  "Nilai", "Keterangan", "Nilai Verif", "Keterangan Verif"];
        $rows = [];
        $rows[] = $header;
        foreach ($result as $index => $data) {
            $rows[] = [
                $data['periode'],
                $data['instansi'],
                $data['id_instansi'],
                $data['id_survey'],
                $data['target'],
                $data['id_target'],
                $data['nilai'],
                $data['keterangan'],
                $data['nilai_verif'],
                $data['keterangan_verif']
            ];
        }
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray($rows, NULL, 'A1');

        $writer = new Xlsx($spreadsheet);
        $filename = "unfinished_target_" . $periode->nama . "_" . date('Ymd') . ".xls";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $writer->save('php://output');
    }

    public function importTargetToPeriode()
    {
        $this->checkScopes(['stranas.admin']);

        $postParams = $this->request->getPost();

        $params = $this->validate($postParams, [
            'id_periode' => ['validators' => ['required', 'digit']]
        ]);
        $params['document'] = $this->getAndValidateImportFile('document', ['text/csv', 'application/vnd.ms-excel']);


        $periode = StranasPeriode::findFirst($params['id_periode']);

        if (empty($periode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode tidak ditemukan');
        }

        $failed = [];
        $skipped = [];
        $added = [];

        if (!empty($params['document'])) {
            $path = $params['document']['tmp_name'];
            $csv = new ParseCsv\Csv();
            $csv->parse($path);
            // AttachmentHelper::delete($attachment, false);

            foreach ($csv->data as $key => $row) {
                if (empty($row['id_instansi']) || empty($row['id_target'])) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Id instansi atau id target tidak ditemukan. Baris: ' . $key);
                }

                $instansi = Instansi::findFirst($row['id_instansi']);
                if (empty($instansi)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi tidak ditemukan: ' . $row['id_instansi']);
                }

                $target = StranasTarget::findFirst($row['id_target']);
                if (empty($target)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Target tidak ditemukan: " . $row['id_target']);
                }

                $survey_periode_list = StranasSurvey::find([
                    'conditions' => 'id_instansi_satker=:id_instansi: AND id_periode=:id_periode:',
                    'bind' => [
                        'id_instansi' => $row['id_instansi'],
                        'id_periode' => $params['id_periode']
                    ]
                ]);

                if (count($survey_periode_list) < 1) {
                    $survey_template = new StranasSurveyTemplate();
                    $survey_template->nama = $instansi->nama . ' - ' . $periode->nama;
                    $survey_template->created_by = $this->shared->user->um_id;
                    $survey_template->save();

                    usleep(20000); // Sleep 20 ms

                    $survey = new StranasSurvey();
                    $survey->id_periode = $periode->id;
                    $survey->id_instansi_satker = $instansi->id;
                    $survey->id_survey_template = $survey_template->id;
                    $survey_template->created_by = $this->shared->user->um_id;
                    $survey->save();

                    usleep(20000); // Sleep 20 ms

                    $survey_periode_list = [$survey];
                }

                $target_included = false;

                foreach ($survey_periode_list as $key => $survey) {
                    $template = StranasSurveyTemplateQuestion::find([
                        'conditions' => "id_survey_template=:id_template: AND id_target=:id_target:",
                        'bind' => [
                            'id_template' => $survey->id_survey_template,
                            'id_target' => $row['id_target']
                        ]
                    ]);

                    if (count($template) > 0) {
                        $target_included = true;
                    }
                }

                if (!$target_included) {
                    // Target belum terdaftar pada instansi dan periode terkait
                    // Tambahkan target pada survey pertama
                    $survey = $survey_periode_list[0];

                    $survey_template_question = new StranasSurveyTemplateQuestion();
                    $survey_template_question->id_survey_template = $survey->id_survey_template;
                    $survey_template_question->id_target = $row['id_target'];
                    $survey_template_question->created_by = $this->shared->user->um_id;
                    $status = $survey_template_question->save();

                    if ($status) {
                        $added[] = ['id_instansi' => $row['id_instansi'], 'id_target' => $row['id_target']];
                    } else {
                        $failed[] = ['id_instansi' => $row['id_instansi'], 'id_target' => $row['id_target']];
                    }
                } else {
                    $skipped[] = ['id_instansi' => $row['id_instansi'], 'id_target' => $row['id_target']];
                }
            }
        }

        return new SimpleResponse(compact('added', 'failed', 'skipped'));
    }
}