<?php

use DusanKasan\Knapsack\Collection;

class PilkadaController extends BaseController
{
    public function importCalon()
    {
        $this->checkScopes(['admin.view']);

        $document = $this->getAndValidateImportFile2(
            'document',
            ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'],
            $this->request->getUploadedFiles()
        );

        $successCount = 0;
        $fails = [];

        if (!empty($document)) {
            $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($document->getTempName());
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(false);
            $reader->setLoadSheetsOnly(['Pilkada_Data_Calon']);

            $spreadsheet = $reader->load($document->getTempName());
            $spreadsheet->setActiveSheetIndexByName('Pilkada_Data_Calon');
            $worksheet = $spreadsheet->getActiveSheet();

            $expectedHeaders = [
                'WILAYAH ID',
                'KODE KABUPATEN',
                'KODE PROVINSI',
                'DAERAH PEMILIHAN',
                'PROVINSI',
                'TANGGAL DEKLARASI',
                'NIK',
                'NAMA',
                'JABATAN',
                'JABATAN SAAT INI',
                'STATUS PETAHANA',
                'INSTANSI TERAKHIR DI APLIKASI',
                'PEKERJAAN',
                'STATUS PELAPORAN',
                'TANGGAL KIRIM',
                'STATUS LHKPN',
                'TOTAL HARTA PENGUMUMAN'
            ];

            $rows = [];
            $headers = null;
            $rowCount = 0;

            foreach ($worksheet->getRowIterator() as $row) {
                $values = [];

                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,

                foreach ($cellIterator as $cell) {
                    if ($rowCount > 0 && count($values) == 16) {
                        $values[] = $cell->getValue();
                    } else {
                        $values[] = $cell->getFormattedValue();
                    }
                }

                if (count($values) < count($expectedHeaders)) {
                    break;
                }

                if (empty($values[1]) && empty($values[2]) && empty($values[6])) {
                    break;
                }

                if ($rowCount == 0) {
                    $headers = $values;
                } else {
                    $rows[] = $values;
                }

                $rowCount++;
            }

            if (empty($headers)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Judul kolom/headers tidak ditemukan');
            }

            if (count($headers) < count($expectedHeaders)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Jumlah kolom < ' . count($expectedHeaders));
            }

            if (count($headers) > count($expectedHeaders)) {
                $headers = array_slice($headers, 0, count($expectedHeaders));
            }

            for ($i = 0; $i < count($expectedHeaders); $i++) {
                if ($expectedHeaders[$i] != $headers[$i]) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Nama kolom ke-$i tidak valid. Expected: " . $expectedHeaders[$i] . '. Got: ' . $headers[$i]);
                }
            }

            $errors = [];

            $rules = [
                'wilayah_id' => ['validators' => ['required', 'numeric']],
                'kode_kabupaten_provinsi' => ['validators' => ['required']],
                'daerah_pemilihan' => ['validators' => ['required']],
                'provinsi' => ['validators' => ['required']],
                'tanggal_deklarasi' => ['validators' => ['required', 'date']],
                'nik' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'jabatan' => ['validators' => ['required']],
                'jabatan_saat_ini' => ['validators' => ['required']],
                'status_petahana' => ['validators' => []],
                'instansi_terakhir_di_aplikasi' => ['validators' => ['required']],
                'pekerjaan' => ['validators' => ['required']],
                'status_pelaporan' => ['validators' => ['required']],
                'tanggal_kirim' => ['validators' => ['required', 'date']],
                'status_lhkpn' => ['validators' => ['required']],
                'total_harta_pengumuman' => ['validators' => ['required', 'numeric']]
            ];

            $nikList = [];

            foreach ($rows as $key => $row) {
                $rowPayload = [
                    'wilayah_id' => trim($row[0]),
                    'kode_kabupaten_provinsi' => trim($row[1]) . trim($row[2]),
                    'daerah_pemilihan' => trim($row[3]),
                    'provinsi' => trim($row[4]),
                    'tanggal_deklarasi' => trim($row[5]),
                    'nik' => trim($row[6]),
                    'nama' => trim($row[7]),
                    'jabatan' => trim($row[8]),
                    'jabatan_saat_ini' => trim($row[9]),
                    'status_petahana' => trim($row[10]),
                    'instansi_terakhir_di_aplikasi' => trim($row[11]),
                    'pekerjaan' => trim($row[12]),
                    'status_pelaporan' => trim($row[13]),
                    'tanggal_kirim' => trim($row[14]),
                    'status_lhkpn' => trim($row[15]),
                    'total_harta_pengumuman' => trim($row[16])
                ];

                $nikList[] = $rowPayload['nik'];

                $validationResult = $this->baseValidate($rowPayload, $rules);

                if (count($validationResult['errors']) > 0) {
                    $errors[] = [
                        'row_id' => $key + 1,
                        'errors' => $validationResult['errors']
                    ];
                }
            }

            $nikCounts = array_count_values($nikList);
            $nikDuplicates = array_filter($nikCounts, function ($value) {
                return $value > 1;
            });

            if (count($nikDuplicates) > 0) {
                $errors[] = [
                    'row_id' => -1,
                    'errors' => [
                        [
                            'field' => 'nik',
                            'message' => 'Duplicate pada excel: ' . implode(',', array_keys($nikDuplicates))
                        ]
                    ]
                ];
            }

            if (count($errors) > 0) {
                throw new ValidationException($errors);
            }

            $this->db->begin();
            try {
                foreach ($rows as $key => $row) {
                    $rowPayload = [
                        'wilayah_id' => trim($row[0]),
                        'kode_kabupaten' => trim($row[1]),
                        'kode_provinsi' => trim($row[2]),
                        'daerah_pemilihan' => trim($row[3]),
                        'provinsi' => trim($row[4]),
                        'tanggal_deklarasi' => trim($row[5]),
                        'nik' => trim($row[6]),
                        'nama' => trim($row[7]),
                        'jabatan' => trim($row[8]),
                        'jabatan_saat_ini' => trim($row[9]),
                        'status_petahana' => trim($row[10]),
                        'instansi_terakhir_di_aplikasi' => trim($row[11]),
                        'pekerjaan' => trim($row[12]),
                        'status_pelaporan' => trim($row[13]),
                        'tanggal_kirim' => trim($row[14]),
                        'status_lhkpn' => trim($row[15]),
                        'total_harta_pengumuman' => trim($row[16])
                    ];

                    $existingCakada = PilkadaCalon::findByNik($rowPayload['nik']);
                    if (!empty($existingCakada) && count($existingCakada) > 0) {
                        $fails[] = [
                            'nik' => $existingCakada[0]->nik,
                            'error_message' => 'already exist'
                        ];
                        continue;
                    }

                    $cakada = new PilkadaCalon();
                    $cakada->wilayah_id = $rowPayload['wilayah_id'] ?: null;
                    $cakada->kode_kabupaten = $rowPayload['kode_kabupaten'] ?: null;
                    $cakada->kode_provinsi = $rowPayload['kode_provinsi'] ?: null;
                    $cakada->daerah_pemilihan = $rowPayload['daerah_pemilihan'];
                    $cakada->provinsi = $rowPayload['provinsi'];
                    $cakada->tanggal_deklarasi = $rowPayload['tanggal_deklarasi'];
                    $cakada->nik = $rowPayload['nik'];
                    $cakada->nama = $rowPayload['nama'];
                    $cakada->jabatan = $rowPayload['jabatan'];
                    $cakada->jabatan_saat_ini = $rowPayload['jabatan_saat_ini'];
                    $cakada->status_petahana = $rowPayload['status_petahana'];
                    $cakada->instansi_terakhir_di_aplikasi = $rowPayload['instansi_terakhir_di_aplikasi'];
                    $cakada->pekerjaan = $rowPayload['pekerjaan'];
                    $cakada->status_pelaporan = $rowPayload['status_pelaporan'];
                    $cakada->tanggal_kirim = $rowPayload['tanggal_kirim'];
                    $cakada->status_lhkpn = $rowPayload['status_lhkpn'];
                    $cakada->total_harta_pengumuman = $rowPayload['total_harta_pengumuman'] ?: null;

                    $result = $cakada->save();

                    if (!$result) {
                        $errorMessage = '';

                        foreach ($cakada->getMessages() as $message) {
                            $errorMessage .= $message . ", ";
                        }

                        $fails[] = [
                            'nik' => $cakada->nik,
                            'error_message' => $errorMessage
                        ];
                    } else {
                        $successCount++;
                    }
                }
                $this->db->commit();
            } catch (\Exception $e) {
                $this->db->rollback();

                throw $e;
            }


            return new CollectionPaginatedResponse([
                'success_count' => $successCount,
                'fails' => $fails
            ]);
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found or Not active");
        }
    }

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

        $result = $this->rawQuery('DELETE FROM jaga.pilkada_calon', []);

        return new SimpleResponse([]);
    }

    public function getCalon()
    {
        $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'] : '';
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'kode_provinsi';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = [
            'kode_provinsi',
            'kode_kabupaten',
            'nik',
            'nama'
        ];
        $allowed_order_direction = ['asc', 'desc'];

        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $conditions = 'nik ilike :keyword: or nama ilike :keyword: or daerah_pemilihan ilike :keyword:';
        $queryParams = [
            'keyword' => '%' . $params['keyword'] . '%'
        ];

        $records = PilkadaCalon::find([
            'conditions' => $conditions,
            'bind' => $queryParams,
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => "{$params['order_by']} {$params['order_direction']}"
        ]);

        $total_record = PilkadaCalon::count([
            'conditions' => $conditions,
            'bind' => $queryParams
        ]);

        return new PaginatedResponse($records, $total_record, $params['limit'], $params['offset']);
    }

    private function removeKeys($arr, $keys)
    {
        for ($i = 0; $i < count($arr); $i++) {
            for ($j = 0; $j < count($keys); $j++) {
                unset($arr[$i][$keys[$j]]);
            }
        }

        return $arr;
    }

    private function censorDateOfBirth($arr)
    {
        for ($i = 0; $i < count($arr); $i++) {
            if (array_key_exists('TANGGAL_LAHIR', $arr[$i])) {
                $dateTime = DateTime::createFromFormat('Y-m-d', $arr[$i]['TANGGAL_LAHIR']);
                $errors = DateTime::getLastErrors();

                unset($arr[$i]['TANGGAL_LAHIR']);

                if (!empty($errors['warning_count'])) {
                    $arr[$i]['TAHUN LAHIR'] = null;
                } else {
                    $arr[$i]['TAHUN LAHIR'] = $dateTime->format('Y');
                }
            }
        }

        return $arr;
    }

    public function getCalonWithElhkpnByWilayahId($id)
    {
        $wilayah = MasterWilayah::findFirst([
            'conditions' => 'id = :id:',
            'bind' => [
                'id' => $id
            ]
        ]);

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

        return $this->getCalonWithElhkpn($wilayah);
    }

    public function getCalonWithElhkpnByWilayahKode($kode)
    {
        $wilayah = MasterWilayah::findFirst([
            'conditions' => 'code = :kode:',
            'bind' => [
                'kode' => $kode
            ]
        ]);

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

        return $this->getCalonWithElhkpn($wilayah);
    }

    protected function getCalonWithElhkpn($wilayah)
    {
        $calonList = Collection::from(
            PilkadaCalon::find([
                'conditions' => 'wilayah_id = :id:',
                'bind' => [
                    'id' => $wilayah->id
                ]
            ])->toArray()
        );

        $nikArray = $calonList->extract('nik')
            ->distinct()
            ->values()
            ->toArray();

        if (empty($nikArray)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Calon Pilkada tidak ditemukan");
        }

        $laporanKepatuhanResult = ElhkpnKepatuhanPerson::find([
            'conditions' => 'nik in ({nik_array:array})',
            'bind' => [
                'nik_array' => $nikArray
            ]
        ])->toArray();
        $laporanKepatuhanList = Collection::from($laporanKepatuhanResult)
            ->groupBy(function ($value) {
                return 'nik-' . $value['nik'];
            });

        $laporanCakadaList = Collection::from([]);

        try {
            $laporanCakadaResult = MartDataHistoryLaporanCakada2020::find([
                'conditions' => 'nik in ({nik_array:array})',
                'bind' => [
                    'nik_array' => $nikArray
                ]
            ])->toArray();
            $laporanCakadaList = Collection::from($laporanCakadaResult)
                ->groupBy(function ($value) {
                    return 'nik-' . $value['nik'];
                });
        } catch (\PDOException $e) {
            $stackTrace = str_replace("\n", "||", $e->getTraceAsString());
            $this->logger->addItem("stack_trace", $stackTrace);
            $this->logger->error('Connection to DB Mart ELHKPN is failed');
        }

        $result = [];

        foreach ($calonList as $key => $calon) {
            $row = $calon;

            $row['elhkpn'] = [
                'tahunan' => $laporanKepatuhanList->has('nik-' . $calon['nik'])
                    ? $this->removeKeys($laporanKepatuhanList->get('nik-' . $calon['nik'])->toArray(), ['nik'])
                    : [],
                'pilkada_2020' => $laporanCakadaList->has('nik-' . $calon['nik'])
                    ? $this->censorDateOfBirth($this->removeKeys($laporanCakadaList->get('nik-' . $calon['nik'])->toArray(), ['nik', 'ALAMAT_RUMAH', 'AGAMA', 'EMAIL_PRIBADI', 'HP', 'TEMPAT_LAHIR']))
                    : []
            ];

            unset($row['nik']);

            $result[] = $row;
        }

        return new CollectionPaginatedResponse($result);
    }
}