<?php
use Jaga\Models\PAK\PAKIsianSekolah;
use Jaga\Models\PAK\PAKIsianSekolahAttachment;
use Jaga\Models\PAK\PAKIsianSekolahDetail;

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

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['nama'] = array_key_exists('nama', $getParams) ? $getParams['nama'] : '';
        $getParams['jenjang'] = array_key_exists('jenjang', $getParams) ?  $getParams['jenjang'] : -1;
        $getParams['kota'] = array_key_exists('kota', $getParams) ? $getParams['kota'] : '';
        $getParams['id_provinsi'] = array_key_exists('id_provinsi', $getParams) ? $getParams['id_provinsi'] : 0;
        $getParams['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $getParams) ? $getParams['id_kota_kabupaten'] : 0;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : -1;
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? $getParams['order_direction'] : 'asc';

        $validatedRequest = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );

        $OR = '';
        $arrBind = [
            'nama' => '%' . $getParams['nama'] . '%'
        ];

        if($getParams['id_kota_kabupaten'] > 0) {
            $OR .= 'city_id = :kodekabkota:';
            $arrBind['kodekabkota'] = $getParams['id_kota_kabupaten'];
        }

        else if($getParams['id_provinsi'] > 0) {
            $OR .= "province_id = :provinsi_code:";
            $arrBind['provinsi_code'] = $getParams['id_provinsi'];
        }
        else {
            $arrBind = array_merge($arrBind, ['kota' => '%' . strtolower($getParams['kota']) . '%']);
            $OR .= "lower(city) like :kota:
                    OR lower(MasterKotaKabupaten.kota_kabupaten_name) like :kota:
                    OR lower(province) like :kota:
                    OR lower(MasterProvinsi.provinsi_name) like :kota:";
        }

        $where = '('.$OR.') and lower(name) like :nama:';

        if(ArrayHelper::isStringJson($getParams['jenjang'])) {
            $where .= ' AND Jenjang.id IN ({jenjang:array})';
            $arrBind['jenjang'] = array_values(json_decode($getParams['jenjang']));
        }
        else if ($getParams['jenjang'] > 0) {
            $where .= ' and Jenjang.id = :jenjang:';
            $arrBind['jenjang'] = $getParams['jenjang'];
        }
        else if ($getParams['id_instansi'] > 0) {
            $instansi = Instansi::findOrFail("id = " . $getParams['id_instansi']);
            $jenjangs = Jenjang::findByInstansiTipe($instansi->id_tipe);
            $arrJenjang = [];
            foreach ($jenjangs as $jenjang) {
                array_push($arrJenjang, $jenjang->id);
            }
            if (sizeof($arrJenjang) > 0) {
                $where .= ' AND Jenjang.id IN ({jenjang:array})';
                $arrBind['jenjang'] = $arrJenjang;
            }
            else {
                $where .= ' AND Jenjang.id = 0';
            }
        }

        // Show PAK Condition
        $arrBind['pak_status'] = \Jaga\Models\PAK\PAKIsianSekolah::STATUS_VERIFIED;
        $arrBind['is_published'] = true;

        $totalResult = Madrasah::query()
            ->columns('count(distinct id_emis) as jumlah')
            ->leftjoin('Jenjang', 'Madrasah.jenjang_id = Jenjang.id')
            ->leftjoin('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code=Madrasah.city_id')
            ->leftjoin('MasterProvinsi', 'MasterProvinsi.provinsi_code=Madrasah.province_id')
            ->leftjoin('Jaga\Models\PAK\PAKIsianSekolah', 'Madrasah.npsn=Jaga\Models\PAK\PAKIsianSekolah.sekolah_npsn AND Jaga\Models\PAK\PAKIsianSekolah.status = :pak_status: AND Jaga\Models\PAK\PAKIsianSekolah.is_published = :is_published:')
            ->where($where)
            ->bind($arrBind)
            ->execute()[0]['jumlah'];

        $columns = [
            "distinct Madrasah.id, name as nama,
            COALESCE(city, MasterKotaKabupaten.kota_kabupaten_name) as kota_kab,
            COALESCE(province, MasterProvinsi.provinsi_name) as provinsi,
            npsn, latitude, longitude, jenjang_id, Jenjang.nama_jenjang,
            COALESCE(Jaga\Models\PAK\PAKIsianSekolah.status = :pak_status: AND Jaga\Models\PAK\PAKIsianSekolah.is_published = :is_published:, false) as is_pak,
            COUNT(MadrasahLike.madrasah_id) AS liked_count,
            'madrasah' as src
            "
        ];

        $columns['is_liked'] = 0;

        $result = Madrasah::query()
            ->columns($columns)
            ->leftjoin('Jenjang', 'Madrasah.jenjang_id = Jenjang.id')
            ->leftjoin('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code=Madrasah.city_id')
            ->leftjoin('MasterProvinsi', 'MasterProvinsi.provinsi_code=Madrasah.province_id')
            ->leftjoin('Jaga\Models\PAK\PAKIsianSekolah', 'Madrasah.npsn=Jaga\Models\PAK\PAKIsianSekolah.sekolah_npsn AND Jaga\Models\PAK\PAKIsianSekolah.status = :pak_status: AND Jaga\Models\PAK\PAKIsianSekolah.is_published = :is_published:')
            ->leftjoin('MadrasahLike', 'Madrasah.id = MadrasahLike.madrasah_id')
            ->where($where)
            ->bind($arrBind)
            ->limit($getParams['limit'], $getParams['offset'])
            ->groupBy("Madrasah.id, Madrasah.name, Madrasah.city, MasterKotaKabupaten.kota_kabupaten_name, Madrasah.province, MasterProvinsi.provinsi_name, npsn, latitude, longitude, jenjang_id, Jenjang.nama_jenjang, Jaga\Models\PAK\PAKIsianSekolah.status, Jaga\Models\PAK\PAKIsianSekolah.is_published, MadrasahLike.madrasah_id")
            ->orderBy("Madrasah.name " . $getParams['order_direction'])
            ->execute()
            ->toArray();

        $resultMadrasahId = [];
        foreach ($result as $item) {
            $resultMadrasahId[] = $item['id'];
        }

        if(!empty($resultMadrasahId) && $this->userID != null) {
            $liked = array_column(MadrasahLike::find([
                'conditions' => 'user_id = :user_id: AND madrasah_id IN ({result_madrasah_id:array})',
                'bind' => [
                    'user_id' => $this->userID,
                    'result_madrasah_id' => $resultMadrasahId
                ]
            ])->toArray(),
            'madrasah_id'
            );

            if(!empty($liked)) {
                foreach ($result as $key => $value) {
                    $result[$key]['is_liked'] = in_array($value['id'], $liked) ? 1 : 0;
                }
            }
        }

        return new PaginatedResponse($result, $totalResult, $getParams['limit'], $getParams['offset']);
    }

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

        $getParams['limit']  = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['nama']   = array_key_exists('nama', $getParams) ? $getParams['nama'] : '';
        $getParams['kota']   = array_key_exists('kota', $getParams) ? $getParams['kota'] : '';
        $getParams['id_provinsi']       = array_key_exists('id_provinsi', $getParams) ? $getParams['id_provinsi'] : 0;
        $getParams['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $getParams) ? $getParams['id_kota_kabupaten'] : 0;
        $getParams['jenjang']           = array_key_exists('jenjang', $getParams) ?  $getParams['jenjang'] : -1;
        $getParams['id_instansi']       = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : -1;
        $getParams['order_direction']   = array_key_exists('order_direction', $getParams) ? $getParams['order_direction'] : 'asc';

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

        $OR = '';
        $arrBind = [
            'nama' => '%' . $getParams['nama'] . '%'
        ];

        if ($getParams['id_kota_kabupaten'] > 0) {
            $OR .= 'Madrasah.city_id = :kodekabkota:';
            $arrBind['kodekabkota'] = $getParams['id_kota_kabupaten'];
        } elseif ($getParams['id_provinsi'] > 0) {
            $OR .= 'Madrasah.province_id = :provinsi_code:';
            $arrBind['provinsi_code'] = $getParams['id_provinsi'];
        } else {
            $arrBind['kota'] = '%' . strtolower($getParams['kota']) . '%';
            $OR .= "lower(Madrasah.city) like :kota:
                OR lower(MasterKotaKabupaten.kota_kabupaten_name) like :kota:
                OR lower(Madrasah.province) like :kota:
                OR lower(MasterProvinsi.provinsi_name) like :kota:";
        }

        $where = '(' . $OR . ') AND lower(Madrasah.name) like :nama:';

        if (ArrayHelper::isStringJson($getParams['jenjang'])) {
            $where .= ' AND Jenjang.id IN ({jenjang:array})';
            $arrBind['jenjang'] = array_values(json_decode($getParams['jenjang']));
        } elseif ($getParams['jenjang'] > 0) {
            $where .= ' AND Jenjang.id = :jenjang:';
            $arrBind['jenjang'] = $getParams['jenjang'];
        } elseif ($getParams['id_instansi'] > 0) {
            $instansi = Instansi::findOrFail("id = " . $getParams['id_instansi']);
            $jenjangs = Jenjang::findByInstansiTipe($instansi->id_tipe);
            $arrJenjang = [];
            foreach ($jenjangs as $jenjang) {
                $arrJenjang[] = $jenjang->id;
            }
            if (count($arrJenjang) > 0) {
                $where .= ' AND Jenjang.id IN ({jenjang:array})';
                $arrBind['jenjang'] = $arrJenjang;
            } else {
                $where .= ' AND Jenjang.id = 0';
            }
        }

        $totalResult = Madrasah::query()
            ->columns('COUNT(DISTINCT Madrasah.id) AS jumlah')
            ->leftJoin('Jenjang', 'Madrasah.jenjang_id = Jenjang.id')
            ->leftJoin('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code = Madrasah.city_id')
            ->leftJoin('MasterProvinsi', 'MasterProvinsi.provinsi_code = Madrasah.province_id')
            ->where($where)
            ->bind($arrBind)
            ->execute()[0]['jumlah'];

        $columns = [
            'Madrasah.name AS nama',
            'MasterKotaKabupaten.kota_kabupaten_name',
            'MasterProvinsi.provinsi_name',
        ];

        $result = Madrasah::query()
            ->columns($columns)
            ->leftJoin('Jenjang', 'Madrasah.jenjang_id = Jenjang.id')
            ->leftJoin('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code = Madrasah.city_id')
            ->leftJoin('MasterProvinsi', 'MasterProvinsi.provinsi_code = Madrasah.province_id')
            ->where($where)
            ->bind($arrBind)
            ->orderBy('Madrasah.name ' . $getParams['order_direction'])
            ->limit($getParams['limit'], $getParams['offset'])
            ->execute()
            ->toArray();

        return new PaginatedResponse($result, $totalResult, $getParams['limit'], $getParams['offset']);
    }

    public function detail($id) {
        $madrasah = Madrasah::findFirst($id);

        if (!$madrasah) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'Not Found');
        }

        $result = $madrasah->toArray();
        $result['source'] = 'DATABASE';
        $liveData = false;
        if ($madrasah->isNeedUpdate()) {
            try {
                $client = new EmisKemenagClient($this->config['emis_kemenag_prop']);
                $client->partnerLogin();
                $liveData = $client->institutionInfoByStatisticNum($madrasah->statistic_num);
                $client->saveDataMadrasah($liveData);

                $madrasah = Madrasah::findFirst($id);
                $liveData = true;
                $result = $madrasah->toArray();
                $result['source'] = 'EMISKEMENAG';
            }
            catch (Exception $e) {
                $result['source'] = 'DATABASE';
                $result['error_message'] = $e->getMessage();
            }
        }
        $result['is_live_data'] = $liveData;

        // takeoout dlu, #1395
//        try{
//            $client = new EmisKemenagClient($this->config['emis_kemenag_prop']);
//            $client->partnerLogin();
//            $rombel = $client->rombelSiswa($result['id_emis']);
//            $result['rombel'] = $rombel;
//        }
//        catch (Exception $e) {
//            $result['error_message_rombel'] = $e->getMessage();
//        }

        $liked = MadrasahLike::count([
            'conditions' => 'madrasah_id = ?0 AND user_id = ?1',
            'bind' => [
                $result['id'],
                $this->userID
            ]
        ]);
        $result['liked'] = ($liked > 0);

        return new SimpleResponse($result);
    }

    public function pipToken() {
        try {
            $client = new PipMadrasahClient($this->config["pipmadrasah_prop"], $this);
            $client->login();
            $client->encryptToken();
            return new SimpleResponse($client->encryptedToken);
        } catch (CustomErrorException $e) {
            throw $e;
        }
    }

    public function pipTracking() {
        $request = $this->request->getJsonRawBody(true);
        $request = $this->validate($request, [
            'level' => ['validators' => ['required']],
            'nisn' => ['validators' => ['required']]
        ]);

        $level = $request['level'];
        $nisn = $request['nisn'];

        $client = new PipMadrasahClient($this->config["pipmadrasah_prop"], $this);
        $result = $client->tracking($level, $nisn);
        return new SimpleResponse($result);
    }

    public function distribusiByJenjang() {
        $request = $this->request;
        $params = $request->get();
        $params['tahun'] = $request->get('tahun', null, null);
        $params['level'] = $request->get('level', null, null);

        $validatedParams = $this->validate($params, [
            'tahun' => ['validators' => ['required']],
            'level' => ['validators' => []],
        ]);

        // additional validate
        if (!empty($params['tahun'])) {
            if (
                !preg_match('/^\d{4}$/', $params['tahun']) ||
                (int)$params['tahun'] <= 2021 && (int)$params['tahun'] >= (int) date('Y')
            ) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Parameter tidak valid');
            }
        }

        try {

            $client = new PipMadrasahClient($this->config["pipmadrasah_prop"], $this);
            $data = $client->distribusiByJenjang($validatedParams['tahun'], $validatedParams['level']);
            return new SimpleResponse($data);
        }
        catch (Exception $exception) {
            if($exception->getCode() == 401) {
                throw new Exception("Belum berhasil terhubung. Mohon coba beberapa saat lagi.");
            }
            else {
                throw $exception;
            }
        }
    }

    public function distribusiWilayah() {
        $request = $this->request;
        $params = $request->get();
        $params['tahun'] = $request->get('tahun', null, null);
        $params['province'] = $request->get('province', null, null);
        $params['city'] = $request->get('province', null, null);

        $validatedParams = $this->validate($params, [
            'tahun' => ['validators' => ['required']],
            'province' => ['validators' => ['required']],
            'city' => ['validators' => ['required']],
        ]);

        try {

            $client = new PipMadrasahClient($this->config["pipmadrasah_prop"], $this);
            $data = $client->distribusiWilayah($validatedParams['tahun'], $validatedParams['province'], $validatedParams['city']);
            return new SimpleResponse($data);
        }
        catch (Exception $exception) {
            if($exception->getCode() == 401) {
                throw new Exception("Belum berhasil terhubung. Mohon coba beberapa saat lagi.");
            }
            else {
                throw $exception;
            }
        }
    }

    public function distribusiNasional() {
        $request = $this->request;
        $params = $request->get();
        $params['tahun'] = $request->get('tahun', null, null);
        $params['province'] = $request->get('province', null, null);

        $validatedParams = $this->validate($params, [
            'tahun' => ['validators' => ['required']],
            'province' => ['validators' => []],
        ]);

        try {

            $client = new PipMadrasahClient($this->config["pipmadrasah_prop"], $this);
            $data = $client->distribusiNasional($validatedParams['tahun'], $validatedParams['province']);
            return new SimpleResponse($data);
        }
        catch (Exception $exception) {
            if($exception->getCode() == 401) {
                throw new Exception("Belum berhasil terhubung. Mohon coba beberapa saat lagi.");
            }
            else {
                throw $exception;
            }
        }
    }

    public function filterSearch() {
        $result = [];
        $jenjangs = Jenjang::query()
            ->columns("id, nama_jenjang, src, group_jenjang")
            ->where("is_shown = 1")
            ->andWhere("src = 'madrasah'")
            ->orderBy("sort_order")
            ->execute();
        $result["jenjang"] = $jenjangs;
        $provinsis = MasterProvinsi::query()
            ->columns("provinsi_code, provinsi_name")
            ->orderBy("provinsi_name")
            ->execute();
        $result["provinsi"] = $provinsis;
        $kabKotas = MasterKotaKabupaten::query()
            ->columns("provinsi_code, kota_kabupaten_code, kota_kabupaten_name")
            ->orderBy("provinsi_code, kota_kabupaten_name")
            ->execute();
        $result["kab_kota"] = $kabKotas;

        return new SimpleResponse($result);
    }
}