<?php

use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class RelInstansiWilayahController extends BaseController
{
    public function index()
    {
        $getParams = $this->request->get();

        $getParams['id_provinsi'] = array_key_exists('id_provinsi', $getParams) ? $getParams['id_provinsi'] : null;
        $getParams['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $getParams) ? $getParams['id_kota_kabupaten'] : null;
        $getParams['year'] = array_key_exists('year', $getParams) ? (!empty($getParams['year']) ? $getParams['year'] : null) : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : null;
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? $getParams['order_by'] : 'id';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? $getParams['order_direction'] : 'asc';

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

        $strWhere = "1 = 1";
        $bind = [];

        if (!is_null($params['keyword'])) {
            $params['keyword'] = "%". $params['keyword'] . "%";
            $strWhere .= " and (";
            $strWhere .= "lower(Instansi.nama) ilike lower(:keyword:)";
            $strWhere .= ")";
            $bind["keyword"] = $params['keyword'];
        }

        if (!is_null($params["id_provinsi"])) {
            $strWhere .= " and RelInstansiWilayah.id_provinsi = (:id_provinsi:)";
            $bind["id_provinsi"] = $params["id_provinsi"];
        }
        
        if (!is_null($params["id_kota_kabupaten"])) {
            if ($params["id_kota_kabupaten"] === "-1") {
                $strWhere .= " and RelInstansiWilayah.id_kota_kabupaten is null";
            } else {
                $strWhere .= " and RelInstansiWilayah.id_kota_kabupaten = (:id_kota_kabupaten:)";
                $bind["id_kota_kabupaten"] = $params["id_kota_kabupaten"];
            }
        }

        if (!is_null($params["year"])) {
            $strWhere .= " and RelInstansiWilayah.start_year <= :year: and RelInstansiWilayah.end_year >= :year: ";
            $bind["year"] = $params["year"];
        }

        $order = " ";
        if (!is_null($params['order_by'])) {
            switch ($params['order_by']) {
                case 'id_instansi':
                    $order = 'id_instansi';
                    break;
                case 'id_provinsi':
                    $order = 'id_provinsi';
                    break;
                case 'id_kota_kabupaten':
                    $order = 'id_kota_kabupaten';
                    break;
                case 'start_year':
                    $order = 'start_year';
                    break;
                case 'end_year':
                    $order = 'end_year';
                    break;
                default:
                    $order = 'id';
            }
        }
        
        if (!is_null($params['order_direction'])) {
            switch ($params['order_direction']) {
                case 'desc':
                    $order .= ' desc';
                    break;
                default:
                    $order .= ' asc';
            }
        }

        $query = RelInstansiWilayah::query()
            ->columns("
                RelInstansiWilayah.id AS id,
                RelInstansiWilayah.id_instansi AS id_instansi,
                Instansi.nama AS nama,
                RelInstansiWilayah.id_provinsi AS id_provinsi,
                MasterProvinsi.provinsi_name AS provinsi_name,
                RelInstansiWilayah.id_kota_kabupaten AS id_kota_kabupaten,
                MasterKotaKabupaten.kota_kabupaten_name AS kota_kabupaten_name,
                RelInstansiWilayah.start_year AS start_year,
                RelInstansiWilayah.end_year AS end_year,
                RelInstansiWilayah.created_at AS created_at,
                RelInstansiWilayah.updated_at AS updated_at,
                RelInstansiWilayah.deleted_at AS deleted_at
                "
            )
            ->join('Instansi', 'RelInstansiWilayah.id_instansi = Instansi.id')
            ->join('MasterProvinsi', 'RelInstansiWilayah.id_provinsi =  CAST(MasterProvinsi.provinsi_code AS integer)')
            ->leftjoin('MasterKotaKabupaten', 'RelInstansiWilayah.id_kota_kabupaten = CAST(MasterKotaKabupaten.kota_kabupaten_code AS integer)')
            ->where($strWhere)
            ->bind($bind)
            ->orderBy($order);
            
        $resultCount = sizeof($query->execute());
        $result = $query
            ->limit((int)$params["limit"], (int)$params["offset"])
            ->execute();

        return new PaginatedResponse($result, $resultCount , $params["limit"], $params["offset"]);
    }

    private function quote($string) 
    {
        return "'" . $string . "'";
    }

    private function quoteWildCard($string) 
    {
        return "'%" . $string . "%'";
    }

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

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_instansi' => ['validators' => ['digit','required']],
                'id_provinsi' => ['validators' => ['digit','required']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'start_year' => ['validators' => ['digit','required']],
                'end_year' => ['validators' => ['digit','required']]
            ]
        );

        if($params['start_year'] < 1970) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data start_year tidak boleh lebih kecil dari 1970");
        }

        if($params['end_year'] > 2099) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data end_year tidak boleh lebih besar dari 2099");
        }

        if($params['start_year'] > $params['end_year']) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data start_year tidak boleh lebih besar dari end_year");
        }

        $checkRows = RelInstansiWilayah::find(
            [
                'id_instansi = :id_instansi: AND deleted_at IS NULL',
                'bind' => [
                    'id_instansi' => $params['id_instansi']
                ]
            ]
        );
        foreach ($checkRows as $checkRow) {
            if ((($params['start_year'] <= $checkRow->start_year) && ($params['end_year'] >= $checkRow->end_year)) ||
                (($params['end_year'] >= $checkRow->start_year) && ($params['end_year'] <= $checkRow->end_year)) ||
                (($params['start_year'] >= $checkRow->start_year) && ($params['start_year'] <= $checkRow->end_year))
            ) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Ditemukan irisan pada data tahun yang sudah ada pada instansi tersebut");
            }
        }

        $row = new RelInstansiWilayah();
        $row->id_instansi = $params['id_instansi'];
        $row->id_provinsi = $params['id_provinsi'];
        $row->id_kota_kabupaten = $params['id_kota_kabupaten'];
        $row->start_year = $params['start_year'];
        $row->end_year = $params['end_year'];
        $row->created_at = date('Y-m-d H:i:s');

        $status = $row->save();

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

            foreach ($errors as $key => $error) {
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            }
            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$row]);
        }
    }

    public function update($id)
    {
        $this->checkScopes(["admin.view"]);

        $row = RelInstansiWilayah::findFirst(
            [
                'id = :id: ',
                'bind' => [
                    'id' => $id
                ]
            ]
        );
        
        if (empty($row)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data dengan ID ' . $id . ' tidak ditemukan');
        }

        if (!empty($row->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data dengan ID ' . $id . ' sudah terhapus');
        }

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

        $params = $this->validate(
            $rawBody,
            [
                'id_instansi' => ['validators' => ['digit','required']],
                'id_provinsi' => ['validators' => ['digit','required']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'start_year' => ['validators' => ['digit','required']],
                'end_year' => ['validators' => ['digit','required']]
            ]
        );
        
        if($params['start_year'] < 1970) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data start_year tidak boleh lebih kecil dari 1970");
        }

        if($params['end_year'] > 2099) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data end_year tidak boleh lebih besar dari 2099");
        }

        if($params['start_year'] > $params['end_year']) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data start_year tidak boleh lebih besar dari end_year");
        }

        $checkRows = RelInstansiWilayah::find(
            [
                'id != :id: AND id_instansi = :id_instansi:',
                'bind' => [
                    'id' => $id,
                    'id_instansi' => $params['id_instansi']
                ],
            ]
        );
        foreach ($checkRows as $checkRow) {
            if (($params['start_year']<=$checkRow->start_year) && ($params['end_year']>=$checkRow->end_year) ||
                ($params['end_year']>=$checkRow->start_year) && ($params['end_year']<=$checkRow->end_year) ||
                ($params['start_year']>=$checkRow->start_year) && ($params['start_year']<=$checkRow->end_year)
            ) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Ditemukan irisan pada data tahun yang sudah ada pada instansi tersebut");
            }
        }
        
        $row->id_instansi = $params['id_instansi'];
        $row->id_provinsi = $params['id_provinsi'];
        $row->id_kota_kabupaten = $params['id_kota_kabupaten'];
        $row->start_year = $params['start_year'];
        $row->end_year = $params['end_year'];
        $row->updated_at = date('Y-m-d H:i:s');

        $status = $row->save();

        if (!$status) {
            $errors = $row->getMessage();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = [
                    "field" => $error->getField(), 
                    "message" => $error->getMessage()
                ];
            }
            throw new ValidationsException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$row]);
        }
    }

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

        $row = RelInstansiWilayah::findFirst(
            [
                'id = :id: ',
                'bind' => [
                    'id' => $id
                ]
            ]
        );

        if (empty($row)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data dengan ID ' . $id . ' tidak ditemukan');
        }

        if (!empty($row->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data dengan ID ' . $id . ' sudah terhapus');
        }

        $date = date('Y-m-d H:i:s');
        $row->updated_at = $date;
        $row->deleted_at = $date;

        $status = $row->save();

        if (!$status) {
            $errors = $row->getMessage();
            $error_messages = [];
            foreach ($errors as $e) {
                $error_messages[] = [
                    'field' => $e->getField(),
                    'message' => $e->getMessage()
                ];
            }
        } else {
            return new CollectionPaginatedResponse([$row]);
        }
    }
    
    public function restore($id)
    {
        $this->checkScopes(['admin.view']);

        $row = RelInstansiWilayah::findFirst(
            [
                'id = :id: ',
                'bind' => [
                    'id' => $id
                ]
            ]
        );

        if (empty($row)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data dengan ID ' . $id . ' tidak ditemukan');
        }

        if (empty($row->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data dengan ID ' . $id . ' belum terhapus');
        }

        $checkRows = RelInstansiWilayah::find(
            [
                'id != :id: AND id_instansi = :id_instansi: AND deleted_at IS NULL',
                'bind' => [
                    'id' => $id,
                    'id_instansi' => $row->id_instansi
                ],
            ]
        );
        foreach ($checkRows as $checkRow) {
            if (($row->start_year<=$checkRow->start_year) && ($row->end_year>=$checkRow->end_year) ||
                ($row->end_year>=$checkRow->start_year) && ($row->end_year<=$checkRow->end_year) ||
                ($row->start_year>=$checkRow->start_year) && ($row->start_year<=$checkRow->end_year)
            ) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Gagal aktivasi, ditemukan irisan pada data tahun yang sudah ada pada instansi tersebut");
            }
        }

        $date = date('Y-m-d H:i:s');
        $row->updated_at = $date;
        $row->deleted_at = null;

        $status = $row->save();

        if (!$status) {
            $errors = $row->getMessage();
            $error_messages = [];
            foreach ($errors as $e) :
                $error_messages[] = [
                    'field' => $e->getField(),
                    'message' => $e->getMessage()
                ];
            endforeach;
        } else {
            return new CollectionPaginatedResponse([$row]);
        }
    }

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

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

        $fails = [];

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

            $spreadsheet = $reader->load($document->getTempName());
            $worksheet = $spreadsheet->getSheetByName('input_user');

            // validate template
            $expectedHeaders = [
                'id',
                'id_instansi',
                'nama_instansi',
                'id_provinsi',
                'id_kota_kabupaten',
                'start_year',
                'end_year'
            ];

            $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) {
                    $values[] = $cell->getValue();
                }
                
                if (is_null($values[0]) && is_null($values[1])) {
                    // if headers empty
                    if ($rowCount === 0) {
                        throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Judul kolom (headers) tidak ditemukan');
                        break;
                    }
                    // if end of table
                    else {
                        break;
                    }
                }

                // if columns not complete
                if (count($values) < count($expectedHeaders)) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Jumlah kolom < ' . count($expectedHeaders) . ' (id, id_instansi, nama_instansi, id_provinsi, id_kota_kabupaten, start_year, end_year)');
                    break;
                }
    
                if ($rowCount === 0) {
                    $headers = $values;
                } else {
                    $rows[] = $values;
                }

                $rowCount++;
            }

            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 = [
                'id' => ['validators' => ['digit', 'required']],
                'id_instansi' => ['validators' => ['digit', 'required']],
                'nama_instansi' => ['validators' => ['required']],
                'id_provinsi' => ['validators' => ['required']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'start_year' => ['validators' => ['digit', 'required']],
                'end_year' => ['validators' => ['digit', 'required']]
            ];

            foreach ($rows as $key => $row) {
                $rowPayload = [
                    'id' => trim($row[0]),
                    'id_instansi' => trim($row[1]),
                    'nama_instansi' => trim($row[2]),
                    'id_provinsi' => trim($row[3]),
                    'id_kota_kabupaten' => trim($row[4]),
                    'start_year' => trim($row[5]),
                    'end_year' => trim($row[6])
                ];

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

                if (count($validationResult['errors']) > 0) {
                    $message = 'Error row ' . (string)($key + 2) . ': ';
                    $lastKey = array_key_last($validationResult['errors']);
                    foreach ($validationResult['errors'] as $key => $error) {
                        $message .= $error["message"];
                        if ($key !== $lastKey) {
                            $message .= ', ';
                        } else {
                            $message .= '. ';
                        }
                    }
                    $fails[] = $message;
                }
            }

            $this->db->begin();
            try {
                if (empty($fails)) {
                    foreach ($rows as $key => $row) {
                        $rowPayload = [
                            'id' => trim($row[0]),
                            'id_instansi' => trim($row[1]),
                            'nama_instansi' => trim($row[2]),
                            'id_provinsi' => trim($row[3]),
                            'id_kota_kabupaten' => trim($row[4]),
                            'start_year' => trim($row[5]),
                            'end_year' => trim($row[6])
                        ];
    
                        // if update, validate id matches instansi id
                        if ((int)$rowPayload['id'] !== 0) {
                            $id = RelInstansiWilayah::findFirst(
                                [
                                    "id = :id: and id_instansi = :id_instansi: and deleted_at is null",
                                    "bind" => [
                                        "id" => $rowPayload['id'],
                                        "id_instansi" => $rowPayload['id_instansi']
                                    ]
                                ]
                            );
                            if (empty($id)) {
                                $message = 'Error A' . (string)($key + 2) . ' dan B' . (string)($key + 2) . ': ';
                                $message .= 'ID (' . $rowPayload['id'] . ') ';
                                $message .= 'tidak sesuai dengan ';
                                $message .= 'ID Instansi (' . $rowPayload['id_instansi'] . ')';
                                $fails[] = $message;
                            };
                        }
    
                        // validate instansi id exists
                        $id_instansi = Instansi::findFirst(
                            [
                                "id = :id: and deleted_at is null",
                                "bind" => ["id" => $rowPayload['id_instansi']]
                            ]
                        );
                        if (empty($id_instansi)) {
                            $message = 'Error B' . (string)($key + 2) . ': ';
                            $message .= 'ID Instansi (' . $rowPayload['id_instansi'] . ') tidak valid';
                            $fails[] = $message;
                        };
    
                        // validate instansi id matches name
                        $instansi = Instansi::findFirst(
                            [
                                "id = :id: and lower(nama) like :nama: and deleted_at is null",
                                "bind" => [
                                    "id" => $rowPayload['id_instansi'],
                                    "nama" => "%" . strtolower($rowPayload['nama_instansi']) . "%"
                                ]
                            ]
                        );
                        if (empty($instansi)) {
                            $message = 'Error B' . (string)($key + 2) . ' dan C' . (string)($key + 2) . ': ';
                            $message .= 'ID Instansi (' . $rowPayload['id_instansi'] . ') ';
                            $message .= 'tidak sesuai dengan ';
                            $message .= 'Nama Instansi (' . $rowPayload['nama_instansi'] . ')';
                            $fails[] = $message;
                        };
    
                        // validate id_provinsi exists
                        $provinsi = MasterProvinsi::findFirst(
                            [
                                "provinsi_code = :id_provinsi:",
                                "bind" => ["id_provinsi" => $rowPayload['id_provinsi']]
                            ]
                        );
                        if (empty($provinsi)) {
                            $message = 'Error D' . (string)($key + 2) . ': ';
                            $message .= 'ID Provinsi (' . $rowPayload['id_provinsi'] . ') tidak valid';
                            $fails[] = $message;
                        };
    
                        // if kotakab, validate id_kota_kabupaten corresponds to id_provinsi
                        if (!empty($rowPayload['id_kota_kabupaten'])) {
                            $kotakab = MasterKotaKabupaten::findFirst(
                                [
                                    "kota_kabupaten_code = :id_kota_kabupaten: and provinsi_code = :id_provinsi:",
                                    "bind" => [
                                        "id_kota_kabupaten" => $rowPayload['id_kota_kabupaten'],
                                        "id_provinsi" => $rowPayload['id_provinsi']
                                    ]
                                ]
                            );
                            if (empty($kotakab)) {
                                $message = 'Error D' . (string)($key + 2) . ' dan E' . (string)($key + 2) . ': ';
                                $message .= 'ID Kota/Kabupaten (' . $rowPayload['id_kota_kabupaten'] . ') ';
                                $message .= 'tidak sesuai dengan ';
                                $message .= 'ID Provinsi (' . $rowPayload['id_provinsi'] . ')';
                                $fails[] = $message;
                            };
                        }
    
                        // validate start_year <= end_year
                        if ($rowPayload['start_year'] > $rowPayload['end_year']) {
                            $message = 'Error F' . (string)($key + 2) . ' dan G' . (string)($key + 2) . ': ';
                            $message .= 'Start Year (' . $rowPayload['start_year'] . ') ';
                            $message .= 'lebih besar dari ';
                            $message .= 'End Year (' . $rowPayload['end_year'] . ')';
                            $fails[] = $message;
                        }

                        // validate start_year >= 1970 & end_year <= 2099
                        if ($rowPayload['start_year'] < 1970 || $rowPayload['end_year'] > 2099) {
                            $message = 'Error F' . (string)($key + 2) . ' dan G' . (string)($key + 2) . ': ';
                            $message .= 'Start Year (' . $rowPayload['start_year'] . ') dan ';
                            $message .= 'End Year (' . $rowPayload['end_year'] . ') ';
                            $message .= 'harus berada diantara 1970 ';
                            $message .= 'dan 2099 ';
                            $fails[] = $message;
                        }
    
                        // validate years do not clash
                        $checkRows = RelInstansiWilayah::find(
                            [
                                "id != :id: AND id_instansi = :id_instansi:",
                                "bind" => [
                                    "id" => $rowPayload['id'],
                                    "id_instansi" => $rowPayload['id_instansi']
                                    ]
                            ]);
                        foreach ($checkRows as $checkRow) {
                            if ((($rowPayload['start_year'] <= $checkRow->start_year) && ($rowPayload['end_year'] >= $checkRow->end_year)) ||
                                (($rowPayload['end_year'] >= $checkRow->start_year) && ($rowPayload['end_year'] <= $checkRow->end_year)) ||
                                (($rowPayload['start_year'] >= $checkRow->start_year) && ($rowPayload['start_year'] <= $checkRow->end_year))
                            ) {
                                $message = 'Error F' . (string)($key + 2) . ' dan G' . (string)($key + 2) . ': ';
                                $message .= 'Terdapat irisan rentang tahun baru (' . $rowPayload['start_year'] . '-' . $rowPayload['end_year'] . ') ';
                                $message .= 'dengan rentang tahun eksisting (' . $checkRow->start_year . '-' . $checkRow->end_year . ')';
                                $fails[] = $message;
                                break;
                            }
                        }
    
                        if (!empty(!$fails)) {
                            // if update, update db
                            if ((int)$rowPayload['id'] !== 0) {
                                $updateRow = RelInstansiWilayah::findFirst(
                                    [
                                        "id = :id: and deleted_at is null",
                                        "bind" => ["id" => $rowPayload['id']]
                                    ]
                                );
        
                                $updateRow->id_provinsi = $rowPayload['id_provinsi'];
                                $updateRow->id_kota_kabupaten = !empty($rowPayload['id_kota_kabupaten']) ? $rowPayload['id_kota_kabupaten'] : null;
                                $updateRow->start_year = $rowPayload['start_year'];
                                $updateRow->end_year = $rowPayload['end_year'];
                                $updateRow->updated_at = date('Y-m-d H:i:s');
                                $result = $updateRow->save();
        
                                if (!$result) {
                                    $errorMessage = '';
        
                                    foreach ($updateRow->getMessages() as $message) {
                                        $errorMessage .= $message . ", ";
                                    }
        
                                    $message = 'Error row ' . (string)($key + 2) . ': ';
                                    $message .= $errorMessage;
                                    $fails[] = $message;
                                }
                            } else {
                                $newRow = new RelInstansiWilayah();
                                $newRow->id_instansi = $rowPayload['id_instansi'];
                                $newRow->id_provinsi = $rowPayload['id_provinsi'];
                                $newRow->id_kota_kabupaten = !empty($rowPayload['id_kota_kabupaten']) ? $rowPayload['id_kota_kabupaten'] : null;
                                $newRow->start_year = $rowPayload['start_year'];
                                $newRow->end_year = $rowPayload['end_year'];
                                $newRow->created_at = date('Y-m-d H:i:s');
                                $result = $newRow->save();
        
                                if (!$result) {
                                    $errorMessage = '';
        
                                    foreach ($updateRow->getMessages() as $message) {
                                        $errorMessage .= $message . ", ";
                                    }
        
                                    $message = 'Error row ' . (string)($key + 2) . ': ';
                                    $message .= $errorMessage;
                                    $fails[] = $message;
                                }
                            }
                        }
                    }
                }
                if (empty($fails)) {
                    $this->db->commit();
                } else {
                    $this->db->rollback();
                }
            } catch (\Exception $e) {
                $this->db->rollback();
                throw $e;
            }

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

    public function export()
    {
        $this->checkScopes(['admin.view']);
        
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle("input_user");

        $sheet->setCellValue("A1", 'id');
        $sheet->setCellValue("B1", 'id_instansi');
        $sheet->setCellValue("C1", 'nama_instansi');
        $sheet->setCellValue("D1", "id_provinsi");
        $sheet->setCellValue("E1", "id_kota_kabupaten");
        $sheet->setCellValue("F1", "start_year");
        $sheet->setCellValue("G1", "end_year");
        $sheet->getStyle('A1:G1')->getFont()->setBold(true);

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:G50')->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(4);
        $sheet->getColumnDimension("B")->setAutoSize(true);
        $sheet->getColumnDimension("C")->setAutoSize(true);
        $sheet->getColumnDimension("D")->setAutoSize(true);
        $sheet->getColumnDimension("E")->setAutoSize(true);
        $sheet->getColumnDimension("F")->setAutoSize(true);
        $sheet->getColumnDimension("G")->setAutoSize(true);
        

        // generate sheet referensi contoh_input_user
        $this->generateSheetContohInputUser($spreadsheet);

        // generate sheet referensi rel_instansi_wilayah
        $this->generateSheetRelInstansiWilayah($spreadsheet);
        
        // generate sheet referensi master_instansi
        $this->generateSheetMasterInstansi($spreadsheet);

        // generate sheet referensi master_provinsi
        $this->generateSheetMasterProvinsi($spreadsheet);

        // generate sheet referensi master_kota_kabupaten
        $this->generateSheetMasterKotaKabupaten($spreadsheet);

        $writer = new Xlsx($spreadsheet);
        $filename = "Template_Relasi_Instansi_Wilayah_".date('Y-m-d').".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function generateSheetContohInputUser(Spreadsheet $spreadsheet)
    {
        $sheet = new PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'contoh_input_user');
        $spreadsheet->addSheet($sheet);

        $sheet->setCellValue("A1", 'id');
        $sheet->setCellValue("B1", 'id_instansi');
        $sheet->setCellValue("C1", 'nama_instansi');
        $sheet->setCellValue("D1", "id_provinsi");
        $sheet->setCellValue("E1", "id_kota_kabupaten");
        $sheet->setCellValue("F1", "start_year");
        $sheet->setCellValue("G1", "end_year");
        $sheet->getStyle('A1:G1')->getFont()->setBold(true);

        $sheet->setCellValue("A2", "0");
        $sheet->setCellValue("B2", "10");
        $sheet->setCellValue("C2", "Contoh Data Baru");
        $sheet->setCellValue("D2", "33");
        $sheet->setCellValue("E2", "3301");
        $sheet->setCellValue("F2", "1990");
        $sheet->setCellValue("G2", "1999");

        $sheet->setCellValue("A3", "5");
        $sheet->setCellValue("B3", "6");
        $sheet->setCellValue("C3", "Contoh Data Update");
        $sheet->setCellValue("D3", "51");
        $sheet->setCellValue("E3", "5101");
        $sheet->setCellValue("F3", "2020");
        $sheet->setCellValue("G3", "2040");
        
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $index = 4;
        $sheet->getStyle('A1:G' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(4);
        $sheet->getColumnDimension("B")->setAutoSize(true);
        $sheet->getColumnDimension("C")->setAutoSize(true);
        $sheet->getColumnDimension("D")->setAutoSize(true);
        $sheet->getColumnDimension("E")->setAutoSize(true);
        $sheet->getColumnDimension("F")->setAutoSize(true);
        $sheet->getColumnDimension("G")->setAutoSize(true);
        
        // petunjuk pengisian
        $index = $index + 2;
        $sheet->setCellValue("A$index", "Petunjuk Pengisian:");
        $sheet->getStyle("A$index")->getFont()->setBold(true);
        $sheet->getStyle("A$index")->getFont()->setSize(14);

        $richText = new RichText();
        $richText->createText('Selalu unduh template terbaru sebelum melakukan perubahan ');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createText('Lakukan pengisian data hanya pada sheet "input_user"');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createTextRun('Tidak boleh ')->getFont()->setBold(true);
        $richText->createText('ada row kosong diantara data yang terisi');
        $index++;
        $sheet->setCellValue("A$index", $richText);
        
        $richText = new RichText();
        $richText->createText('Kolom ');
        $richText->createTextRun('id ')->getFont()->setBold(true);
        $richText->createText('diisi dengan ');
        $richText->createTextRun('0 ')->getFont()->setBold(true);
        $richText->createText('jika ingin ');
        $richText->createTextRun('menambahkan data baru')->getFont()->setBold(true);
        $richText->createText(', dan diisi dengan ');
        $richText->createTextRun('id ')->getFont()->setBold(true);
        $richText->createText('jika ingin ');
        $richText->createTextRun('mengubah data dengan id tersebut')->getFont()->setBold(true);
        $richText->createText('.');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createText('Kolom ');
        $richText->createTextRun('id_instansi ')->getFont()->setBold(true);
        $richText->createText('diisi dengan id instansi sesuai penomoran pada aplikasi JAGA.');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createText('Kolom ');
        $richText->createTextRun('nama_instansi ')->getFont()->setBold(true);
        $richText->createText('diisi dengan nama instansi sesuai penamaan pada aplikasi JAGA.');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createText('Kolom ');
        $richText->createTextRun('id_provinsi ')->getFont()->setBold(true);
        $richText->createText('diisi dengan id provinsi (lihat sheet ');
        $richText->createTextRun('master_provinsi')->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createText('Kolom ');
        $richText->createTextRun('id_kota_kabupaten ')->getFont()->setBold(true);
        $richText->createText('diisi dengan id kota/kabupaten (lihat sheet ');
        $richText->createTextRun('master_kota_kabupaten')->getFont()->setBold(true);
        $richText->createText(').');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createText('Kolom ');
        $richText->createTextRun('start_year ')->getFont()->setBold(true);
        $richText->createText('diisi dengan tahun awal berlakunya id_provinsi dan id_kota_kabupaten pada instansi tersebut.');
        $index++;
        $sheet->setCellValue("A$index", $richText);

        $richText = new RichText();
        $richText->createText('Kolom ');
        $richText->createTextRun('end_year ')->getFont()->setBold(true);
        $richText->createText('diisi dengan tahun akhir berlakunya id_provinsi dan id_kota_kabupaten pada instansi tersebut.');
        $index++;
        $sheet->setCellValue("A$index", $richText);
    }

    private function generateSheetRelInstansiWilayah(Spreadsheet $spreadsheet)
    {
        $sheet = new PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'master_rel_instansi_wilayah');
        $spreadsheet->addSheet($sheet);
        
        $rows = RelInstansiWilayah::find(
            [
                'order' => "id"
            ]
        );

        $sheet->setTitle("rel_instansi_wilayah");
        $sheet->setCellValue("A1", 'id');
        $sheet->setCellValue("B1", 'id_instansi');
        $sheet->setCellValue("C1", 'nama_instansi');
        $sheet->setCellValue("D1", "id_provinsi");
        $sheet->setCellValue("E1", "id_kota_kabupaten");
        $sheet->setCellValue("F1", "start_year");
        $sheet->setCellValue("G1", "end_year");
        $sheet->setCellValue("H1", "deleted_at");
        $sheet->getStyle('A1:H1')->getFont()->setBold(true);

        $index = 1;

        $instansiBinds = [];

        foreach ($rows as $row) {
            $instansiBinds["id_instansi"] = $row->id_instansi;
            $instansiRow = Instansi::findFirst(
                [
                    'id = :id_instansi: AND deleted_at IS NULL',
                    'bind' => $instansiBinds
                ]
                );

            if (!empty($instansiRow)) {
                $index++;
                $sheet->setCellValue("A$index", $row->id);
                $sheet->setCellValue("B$index", $row->id_instansi);
                $sheet->setCellValue("C$index", $instansiRow->nama);
                $sheet->setCellValue("D$index", $row->id_provinsi);
                $sheet->setCellValue("E$index", $row->id_kota_kabupaten);
                $sheet->setCellValue("F$index", $row->start_year);
                $sheet->setCellValue("G$index", $row->end_year);
                $sheet->setCellValue("H$index", $row->deleted_at);
            }
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:H' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(4);
        $sheet->getColumnDimension("B")->setAutoSize(true);
        $sheet->getColumnDimension("C")->setAutoSize(true);
        $sheet->getColumnDimension("D")->setAutoSize(true);
        $sheet->getColumnDimension("E")->setAutoSize(true);
        $sheet->getColumnDimension("F")->setAutoSize(true);
        $sheet->getColumnDimension("G")->setAutoSize(true);
        $sheet->getColumnDimension("H")->setAutoSize(true);
    }

    private function generateSheetMasterProvinsi(Spreadsheet $spreadsheet)
    {
        $sheet = new PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'master_provinsi');
        $spreadsheet->addSheet($sheet);
        $sheet->setCellValue("A1", 'KODE');
        $sheet->setCellValue("B1", "PROVINSI");
        $sheet->getStyle("A1:B1")->getFont()->setBold(true);

        $rows = MasterProvinsi::find([
            'order' => 'provinsi_code'
        ]);
        $index = 1;
        foreach ($rows as $row) {
            $index++;
            $sheet->setCellValue("A$index", $row->provinsi_code);
            $sheet->setCellValue("B$index", $row->provinsi_name);
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet->getStyle('A1:B' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(7);
        $sheet->getColumnDimension("B")->setAutoSize(true);

        $spreadsheet->setActiveSheetIndex(0);
    }

    private function generateSheetMasterKotaKabupaten(Spreadsheet $spreadsheet)
    {
        $sheet = new PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'master_kota_kabupaten');
        $spreadsheet->addSheet($sheet);
        $sheet->setCellValue("A1", 'KODE');
        $sheet->setCellValue("B1", "KOTA/KABUPATEN");
        $sheet->getStyle("A1:B1")->getFont()->setBold(true);

        $rows = MasterKotaKabupaten::find([
            'conditions' => 'deleted_at is null',
            'order' => 'kota_kabupaten_code'
        ]);
        $index = 1;
        foreach ($rows as $row) {
            $index++;
            $sheet->setCellValue("A$index", $row->kota_kabupaten_code);
            $sheet->setCellValue("B$index", $row->kota_kabupaten_name);
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet->getStyle('A1:B' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(7);
        $sheet->getColumnDimension("B")->setAutoSize(true);

        $spreadsheet->setActiveSheetIndex(0);
    }

    private function generateSheetMasterInstansi(Spreadsheet $spreadsheet)
    {
        $sheet = new PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'master_instansi');
        $spreadsheet->addSheet($sheet);
        $sheet->setCellValue("A1", 'ID_INSTANSI');
        $sheet->setCellValue("B1", "NAMA_INSTANSI");
        $sheet->setCellValue("C1", "KODE_PROVINSI");
        $sheet->setCellValue("D1", "KODE_KOTA_KABUPATEN");
        $sheet->getStyle("A1:D1")->getFont()->setBold(true);

        $rows = Instansi::find([
            'conditions' => 'deleted_at is null AND (id_tipe = 1 OR id_tipe = 2)',
            'order' => 'id'
        ]);
        $index = 1;
        foreach ($rows as $row) {
            $index++;
            $sheet->setCellValue("A$index", $row->id);
            $sheet->setCellValue("B$index", $row->nama);
            $sheet->setCellValue("C$index", $row->id_provinsi);
            $sheet->setCellValue("D$index", $row->id_kota_kabupaten);
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet->getStyle('A1:D' . $index)->applyFromArray($styleArray);
        $sheet->getColumnDimension("A")->setWidth(12);
        $sheet->getColumnDimension("B")->setAutoSize(true);
        $sheet->getColumnDimension("C")->setAutoSize(true);
        $sheet->getColumnDimension("D")->setAutoSize(true);

        $spreadsheet->setActiveSheetIndex(0);
    }
}