<?php

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

class SpiMonitoringService
{
    private $controller;
    const TYPE_KEMENTERIAN_LEMBAGA = "KL";
    const TYPE_PEMDA = "PEMDA";
    const STATUS_TARGET = "TARGET";
    const STATUS_REALISASI = "REALISASI";

    private $id_instansi = null;
    private $user_id = null;
    private $tahun = null;
    private $templateId = 0;

    public function __construct($controller, $templateId = 0) {
        try {
            $this->controller = $controller;
            $this->templateId = $templateId;
        }
        catch (Exception $exception) {
            throw new Exception($exception);
        }
    }

    public function tableForm($params) {
        $this->id_instansi = $this->controller->shared->user->id_instansi;

        return $this->getTableData($params);
    }

    public function tableFormAllInstansi($params) {
        return $this->getTableData($params);
    }
    private function getTableData($params)
    {
        if($params['id_instansi'] != null) {
            $this->id_instansi = $params['id_instansi'];
        }

        $conditions = [];
        $bind = [];

        if($this->id_instansi != null) {
            $conditions[] = 'id_instansi = :id_instansi:';
            $bind['id_instansi'] = $this->id_instansi;
        }

        if(!is_null($params['tahun'])) {
            $conditions[] = 'tahun = :tahun:';
            $bind['tahun'] = $params['tahun'];
        }

        $findParams = [
            'limit' => $params['limit'],
            'offset' => $params['offset'],
        ];
        $countParams = [];

        if(!empty($conditions)) {
            $findParams['conditions'] = implode(" AND ", $conditions);
            $findParams['bind'] = $bind;

            $countParams['conditions'] = implode(" AND ", $conditions);
            $countParams['bind'] = $bind;
        }

        $data = SpiMonForm::find(
            $findParams
        )->toArray();

        $totalCount = SpiMonForm::count($countParams);

        foreach ($data as $key => $item) {
            $data[$key]['instansi'] = Instansi::findFirst($item['id_instansi']);
        }

        return new PaginatedResponse($data, $totalCount, $params['limit'], $params['offset']);
    }

    public function tableFormDetails($params)
    {
        $data = $this->getDataFormDetails($params);

        $count = $this->getCountFormDetails($params);

        return new PaginatedResponse($data, $count, $params['limit'], $params['offset']);
    }

    public function exportTableFormDetailsAsExcel($params) {
        $data = $this->getDataFormDetails($params);

        $spreadSheet = new Spreadsheet();
        $sheet = $spreadSheet->getActiveSheet();

        $sheet->setCellValueByColumnAndRow(1, 1, 'No');
        $sheet->setCellValueByColumnAndRow(2, 1, 'Instansi');
        $sheet->setCellValueByColumnAndRow(3, 1, 'Tahun');
        $sheet->setCellValueByColumnAndRow(4, 1, 'Tanggal Buat Form');
        $sheet->setCellValueByColumnAndRow(5, 1, 'Risiko');
        $sheet->setCellValueByColumnAndRow(6, 1, 'Rekomendasi');
        $sheet->setCellValueByColumnAndRow(7, 1, 'Rencana Aksi');
        $sheet->setCellValueByColumnAndRow(8, 1, 'Unit Kerja Penanggung Jawab');
        $sheet->setCellValueByColumnAndRow(9, 1, 'Area Intervensi');
        $sheet->setCellValueByColumnAndRow(10, 1, 'Kriteria Keberhasilan');
        $sheet->setCellValueByColumnAndRow(11, 1, 'Ukuran Keberhasilan');
        $sheet->setCellValueByColumnAndRow(12, 1, 'b03');
        $sheet->setCellValueByColumnAndRow(13, 1, 'Capaian b03 (%)');
        $sheet->setCellValueByColumnAndRow(14, 1, 'b06');
        $sheet->setCellValueByColumnAndRow(15, 1, 'Capaian b06 (%)');
        $sheet->setCellValueByColumnAndRow(16, 1, 'b09');
        $sheet->setCellValueByColumnAndRow(17, 1, 'Capaian b09 (%)');
        $sheet->setCellValueByColumnAndRow(18, 1, 'b12');
        $sheet->setCellValueByColumnAndRow(19, 1, 'Capaian b12 (%)');

        $rowCount = 2;
        foreach ($data as $item) {
            $sheet->setCellValueByColumnAndRow(1, $rowCount, $rowCount-1);
            $sheet->setCellValueByColumnAndRow(2, $rowCount, $item['nama_instansi']);
            $sheet->setCellValueByColumnAndRow(3, $rowCount, $item['tahun']);
            $sheet->setCellValueByColumnAndRow(4, $rowCount, $item['form_created_at']);
            $sheet->setCellValueByColumnAndRow(5, $rowCount, $item['rekomendasi_group_name']);
            $sheet->setCellValueByColumnAndRow(6, $rowCount, $item['rekomendasi_item_name']);
            $sheet->setCellValueByColumnAndRow(7, $rowCount, $item['rencana_aksi']);
            $sheet->setCellValueByColumnAndRow(8, $rowCount, $item['unit_kerja_pj']);
            $sheet->setCellValueByColumnAndRow(9, $rowCount, $item['nama_area_intervensi']);
            $sheet->setCellValueByColumnAndRow(10, $rowCount, $item['kriteria_keberhasilan']);
            $sheet->setCellValueByColumnAndRow(11, $rowCount, $item['ukuran_keberhasilan']);
            $sheet->setCellValueByColumnAndRow(12, $rowCount, $item['b03']);
            $sheet->setCellValueByColumnAndRow(13, $rowCount, $item['capaian_b03']);
            $sheet->setCellValueByColumnAndRow(14, $rowCount, $item['b06']);
            $sheet->setCellValueByColumnAndRow(15, $rowCount, $item['capaian_b06']);
            $sheet->setCellValueByColumnAndRow(16, $rowCount, $item['b09']);
            $sheet->setCellValueByColumnAndRow(17, $rowCount, $item['capaian_b09']);
            $sheet->setCellValueByColumnAndRow(18, $rowCount, $item['b12']);
            $sheet->setCellValueByColumnAndRow(19, $rowCount, $item['capaian_b12']);

            $rowCount++;
        }

        $todayLabel = DateHelper::formatTodayIndex();

        $writer = new Xlsx($spreadSheet);
        $filename = $todayLabel . "-MonitoringSPI" . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');

    }

    private function getDataFormDetails($params)
    {
        $where = '';
        $limitOffset = '';
        $conditions = [];
        $bind = [];

        if($params['tahun'] != null) {
            $conditions[] = 'mf.tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        if($params['id_instansi'] != null) {
            $conditions[] = 'mf.id_instansi = :id_instansi';
            $bind['id_instansi'] = $params['id_instansi'];
        }

        if($params['type'] != null) {
            $conditions[] = 'LOWER(mf.type) = :type';
            $bind['type'] = strtolower($params['type']);
        }

        if($params['keyword'] != null) {
            $conditions[] = '(fd.rekomendasi_item_name ILIKE :keyword OR fd.rencana_aksi ILIKE :keyword OR fd.unit_kerja_pj ILIKE :keyword OR kai.nama ILIKE :keyword OR fd.kriteria_keberhasilan ILIKE :keyword OR fd.ukuran_keberhasilan ILIKE :keyword OR fd.b03 ILIKE :keyword OR fd.b06 ILIKE :keyword OR fd.b09 ILIKE :keyword OR fd.b12 ILIKE :keyword)';
            $bind['keyword'] = '%' . strtolower($params['keyword']) . '%';
        }

        if(!empty($conditions)) {
            $where = ' WHERE ' . implode(' AND ', $conditions);
        }

        if(array_key_exists('limit', $params)) {
            $limitOffset = ' LIMIT '. $params['limit'] . ' OFFSET '. $params['offset'];
        }

        $q = "
        SELECT
        	fd.*,
        	mf.id_instansi,
        	mf.type AS type,
        	mf.status AS status_form,
        	mf.tahun AS tahun,
        	mf.user_id AS form_user_id,
        	mf.created_at AS form_created_at,
        	rg.display_order AS rekomendasi_group_display_order,
        	ins.nama AS nama_instansi,
        	kai.nama AS nama_area_intervensi
        FROM
        	jaga.spi_mon_form_detail fd
        	LEFT JOIN jaga.spi_mon_form mf ON mf.id = fd.id_mon_form
        	LEFT JOIN jaga.spi_mon_rekomendasi_group rg ON rg.id = fd.id_rekomendasi_group
        	LEFT JOIN jaga.instansi ins ON ins.id = mf.id_instansi
        	LEFT JOIN jaga.korsupgah_area_intervensi kai ON kai.id = fd.id_area_intervensi
            $where
        	ORDER BY fd.id
            $limitOffset
	    ";

        $result = $this->controller->rawQuery($q, $bind);

        return $result;
    }

    private function getCountFormDetails($params)
    {
        $where = '';
        $conditions = [];
        $bind = [];

        if($params['tahun'] != null) {
            $conditions[] = 'mf.tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        if($params['id_instansi'] != null) {
            $conditions[] = 'mf.id_instansi = :id_instansi';
            $bind['id_instansi'] = $params['id_instansi'];
        }

        if($params['type'] != null) {
            $conditions[] = 'LOWER(mf.type) = :type';
            $bind['type'] = strtolower($params['type']);
        }

        if($params['keyword'] != null) {
            $conditions[] = '(fd.rekomendasi_item_name ILIKE :keyword OR fd.rencana_aksi ILIKE :keyword OR fd.unit_kerja_pj ILIKE :keyword OR kai.nama ILIKE :keyword OR fd.kriteria_keberhasilan ILIKE :keyword OR fd.ukuran_keberhasilan ILIKE :keyword OR fd.b03 ILIKE :keyword OR fd.b06 ILIKE :keyword OR fd.b09 ILIKE :keyword OR fd.b12 ILIKE :keyword)';
            $bind['keyword'] = '%' . strtolower($params['keyword']) . '%';
        }

        if(!empty($conditions)) {
            $where = ' WHERE ' . implode(' AND ', $conditions);
        }

        $q = "
        SELECT
        	COUNT(*) AS total
        FROM
        	jaga.spi_mon_form_detail fd
        	LEFT JOIN jaga.spi_mon_form mf ON mf.id = fd.id_mon_form
        	LEFT JOIN jaga.spi_mon_rekomendasi_group rg ON rg.id = fd.id_rekomendasi_group
        	LEFT JOIN jaga.instansi ins ON ins.id = mf.id_instansi
        	LEFT JOIN jaga.korsupgah_area_intervensi kai ON kai.id = fd.id_area_intervensi
            $where
	    ";

        $result = $this->controller->rawQuery($q, $bind);

        return $result[0]['total'];
    }

    public function createTarget() {
        try {
            $this->user_id = $this->controller->shared->user->um_id;
            $this->id_instansi = $this->controller->shared->user->id_instansi;
            $this->tahun = date('Y');

            $dataRekomGroup = $this->getDataRekomendasiGroup();
            $dataRekomGroupKeyVal = ArrayHelper::asKeyValue($dataRekomGroup, 'id');

            $dataRekomItem = $this->getAllRekomendasiItemByInstansiId();

            $formHeader = $this->saveFormHeader();

            $this->saveFormDetails($dataRekomItem, $formHeader, $dataRekomGroupKeyVal);

            return $formHeader->id;
        }
        catch (\Exception $e) {
            throw $e;
        }
    }

    private function getAllRekomendasiItemByInstansiId()
    {
        $data = null;

        if($this->templateId == 0) {
            $data = SpiMonRekomendasiItem::find([
                'conditions' => '(id_instansi = ?0 OR id_instansi = 0) AND tahun = ?1',
                'bind' => [
                    $this->id_instansi,
                    date('Y')
                ]
            ])->toArray();

            if(count($data) > 0) {
                $this->templateId = $data[0]['id_mon_template'];
            }
        }
        else {
            $data = SpiMonRekomendasiItem::find([
                'conditions' => '(id_instansi = ?0 OR id_instansi = 0) AND id_mon_template = ?1',
                'bind' => [
                    $this->id_instansi,
                    $this->templateId
                ]
            ])->toArray();
        }

        return $data;
    }

    private function saveFormHeader()
    {
        $id_instansi_tipe = Instansi::findFirst($this->id_instansi)->id_tipe;
        $type = '0';
        if ($id_instansi_tipe == 1 || $id_instansi_tipe == 2)
            $type = self::TYPE_PEMDA;
        elseif ($id_instansi_tipe == 3)
            $type = self::TYPE_KEMENTERIAN_LEMBAGA;

        $formHeader = new SpiMonForm();
        $formHeader->id_instansi = $this->id_instansi;
        $formHeader->type = $type;
        $formHeader->status = self::STATUS_TARGET;
        $formHeader->tahun = $this->tahun;
        $formHeader->user_id = $this->user_id;
        $formHeader->id_mon_template = $this->templateId;
        $formHeader->save();

        return $formHeader;
    }

    private function saveFormDetails($dataRekomItem, $formHeader, $dataRekomGroupKeyVal)
    {
        foreach ($dataRekomItem as $item) {
            $formDetail = new SpiMonFormDetail();
            $formDetail->id_mon_form = $formHeader->id;
            $formDetail->id_rekomendasi_group = $item['id_group'];
            $formDetail->id_rekomendasi_item = $item['id'];
            $formDetail->rekomendasi_group_name = $dataRekomGroupKeyVal[$item['id_group']]['name'];
            $formDetail->rekomendasi_item_name = $item['name'];
            $formDetail->status = self::STATUS_TARGET;
            $formDetail->user_id = $this->user_id;
            $formDetail->save();
        }
    }

    public function getListFormDetail($id_form) {
        $dataFormDetail = $this->getFormDetailByFormHeaderId($id_form);
        $dataRekomGroup = $this->getDataRekomendasiGroup();
        $dataRekomGroupKeyVal = ArrayHelper::asKeyValue($dataRekomGroup, 'id');

        $result= $this->createResultStructureForRekomendasiForm($dataFormDetail, $dataRekomGroupKeyVal);
        return $result;
    }

    private function getFormDetailByFormHeaderId($formId)
    {
        $result = SpiMonFormDetail::find([
            'conditions' => 'id_mon_form = ?0',
            'bind' => [
                $formId
            ],
            'order' => 'id'
        ])->toArray();

        foreach ($result as $key => $item) {
            $result[$key]['attachments'] = $this->getAttachments($item['id']);
        }

        return $result;
    }

    private function getDataRekomendasiGroup()
    {
        return SpiMonRekomendasiGroup::query()
            ->orderBy("display_order ASC")
            ->execute()
            ->toArray();
    }

    private function createResultStructureForRekomendasiForm($data, $dataRekomGroupKeyVal)
    {
        $result = [];
        foreach ($data as $item) {
            if (!array_key_exists($item['id_rekomendasi_group'], $result)) {
                $result[$item['id_rekomendasi_group']] = [
                    'id_rekomendasi_group' => $item['id_rekomendasi_group'],
                    'rekomendasi_group_name' => $item['rekomendasi_group_name'],
                    'display_order' => $dataRekomGroupKeyVal[$item['id_rekomendasi_group']]['display_order'],
                    'details' => []
                ];
            }
            $result[$item['id_rekomendasi_group']]['details'][] = $item;
        }
        uasort($result, function ($a, $b) { return $a['display_order'] - $b['display_order'];});
        return $result;
    }

    private function compareKeys($a, $b) {
        return $a - $b;
    }

    public function saveFormDetail($id, $postData)
    {
        try {
            $dataDetail = $this->retrieveFormDetail($id);
            $dataHeader = $this->retrieveForm($dataDetail->id_mon_form);

            $dataDetail->rencana_aksi = $postData['rencana_aksi'];
            if ($dataHeader->type == self::TYPE_PEMDA) {
                $dataDetail->id_area_intervensi = $postData['id_area_intervensi'];
            } elseif ($dataHeader->type == self::TYPE_KEMENTERIAN_LEMBAGA) {
                $dataDetail->unit_kerja_pj = $postData['unit_kerja_pj'];
            }
            $dataDetail->kriteria_keberhasilan = $postData['kriteria_keberhasilan'];
            $dataDetail->ukuran_keberhasilan = $postData['ukuran_keberhasilan'];
            if ($dataDetail->status == self::STATUS_REALISASI) {
                $dataDetail->b03 = $postData['b03'];
                $dataDetail->capaian_b03 = $postData['capaian_b03'];
                $dataDetail->b06 = $postData['b06'];
                $dataDetail->capaian_b06 = $postData['capaian_b06'];
                $dataDetail->b09 = $postData['b09'];
                $dataDetail->capaian_b09 = $postData['capaian_b09'];
                $dataDetail->b12 = $postData['b12'];
                $dataDetail->capaian_b12 = $postData['capaian_b12'];
                $dataDetail->realisasi_updated_at = 'now()';
            }

            if ($dataDetail->status == self::STATUS_TARGET) {
                $dataDetail->status = self::STATUS_REALISASI;
                $dataDetail->target_updated_at = 'now()';
            }
            $dataDetail->save();

            $this->checkAllStatus($dataHeader->id);
        }
        catch (Exception $e){
            throw $e;
        }

    }

    public function retrieveFormDetail($id) {
        $result = SpiMonFormDetail::findFirst($id);
        return $result;
    }

    private function retrieveForm($id) {
        $result = SpiMonForm::findFirst($id);
        return $result;
    }

    public function saveFile($validation = null, $extraPath = '')
    {
        $attachment = null;
        if($this->controller->request->hasFiles()) {
            $validationConfig = !empty($validation) ? $validation : [
                'allowed-types' => [
                    "image/bmp", "image/x-windows-bmp", "image/jpeg", "image/png", "image/tiff", "image/gif",
                    "application/pdf", "application/msword", "application/excel", "application/vnd.ms-excel",
                    "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    "application/vnd.openxmlformats-officedocument.presentationml.presentation",
                    "application/vnd.ms-powerpoint",
                    "application/vnd.ms-office",
                    "text/plain",
                ],
                'max-file-count' => 1,
                'max-size-kb' => 20480
            ];

            /** @var MinioService $minioService */
            $minioService = $this->controller->minio;
            $attachmentService = new AttachmentService($this->controller->shared, $this->controller->minio);
            if ($minioService->isSecondaryS3Available()) {
                $attachment = $attachmentService->uploadUsingSecondaryS3(
                    getenv("FILESYSTEM_CLOUD"),
                    $validationConfig,
                    ['tipe' => Attachments::TIPE_PRIVATE],
                    $this->controller->request,
                    'spi-monitoring-attachment' . DIRECTORY_SEPARATOR . $extraPath . DIRECTORY_SEPARATOR
                );
            } else {
                $attachment = $attachmentService->upload(
                    getenv("FILESYSTEM_CLOUD"),
                    $validationConfig,
                    ['tipe' => Attachments::TIPE_PRIVATE],
                    $this->controller->request,
                    'spi-monitoring-attachment' . DIRECTORY_SEPARATOR . $extraPath . DIRECTORY_SEPARATOR
                );
            }
        }
        return $attachment;
    }

    public function getAttachments($id)
    {
        $idAttachments = array_column(SpiMonFormAttachment::find([
            'conditions' => 'id_mon_form_detail = ?0',
            'bind' => [
                $id
            ]
        ])->toArray(), 'id_attachment');

        $attachments = [];

        if(!empty($idAttachments)) {
            $attachments = Attachments::find([
                'conditions' => 'id IN ({id_attachment:array})',
                'bind' => [
                    'id_attachment' => array_values($idAttachments)
                ]
            ]);
        }
        return $attachments;
    }

    private function checkAllStatus(int $idForm)
    {
        $countStatusTarget = SpiMonFormDetail::count([
            'conditions' => 'id_mon_form = ?0 AND status = ?1',
            'bind' => [
                $idForm,
                self::STATUS_TARGET
            ]
        ]);
        if($countStatusTarget == 0) {
            $dataForm = SpiMonForm::findFirst($idForm);
            $dataForm->status = self::STATUS_REALISASI;
            $dataForm->save();
        }
    }

    public function updateDetailAttachment($id, $body)
    {
        $deletedFiles = $body['deleted_files'];
        $addedFiles = $body['added_files'];

        if(!empty($deletedFiles)) {
            $deleteAttachments = SpiMonFormAttachment::find([
                'conditions' => 'id_mon_form_detail = :id: AND id_attachment IN ({id_attachment:array})',
                'bind' => [
                    'id' => $id,
                    'id_attachment' => array_values($deletedFiles)
                ]
            ]);

            foreach ($deleteAttachments as $item) {
                $attachment = Attachments::findFirst($item->id_attachment);
                $attachment->deleted_at = 'now()';
                $attachment->save();

                $item->delete();
            }
        }

        if(!empty($addedFiles)) {
            $existingIdAttachments = array_column(SpiMonFormAttachment::find([
                'conditions' => 'id_mon_form_detail = :id: AND id_attachment IN ({id_attachment:array})',
                'bind' => [
                    'id' => $id,
                    'id_attachment' => array_values($addedFiles)
                ]
            ])->toArray(), 'id_attachment');

            if(!empty($existingIdAttachments)) {
                $addedFiles = array_diff($addedFiles, $existingIdAttachments);
            }

            if(!empty($addedFiles)) {
                foreach ($addedFiles as $idAttachment) {
                    $spiMonAttachment = new SpiMonFormAttachment();
                    $spiMonAttachment->id_attachment = $idAttachment;
                    $spiMonAttachment->id_mon_form_detail = $id;
                    $spiMonAttachment->save();
                }
            }
        }
    }
}