<?php

use Carbon\Carbon;

class ElhkpnMartController extends BaseController
{
    const DB = 'databaseElhkpnMart';
    const YEARS = [2018, 2019, 2020, 2021, 2022, 2023];

    public function summaryMap()
    {
        $req = $this->request;
        $this->validate(
            $req->get(),
            [
                'level' => ['validators' => ['required']],
                'year' => ['validators' => ['required']]
            ]
        );
        $persons = $this->summaryPerson();
        $level = $this->request->get('level');
//        $year = $this->request->get('year');
//        $rows = [
//            ['year' => $year - 0, 'rows' => $this->summaryMapYearly($level, $year - 0)],
//            ['year' => $year - 1, 'rows' => $this->summaryMapYearly($level, $year - 1)]
//        ];
        // update 09032022, tampilkan data semua tahun
        $rows = [];
        foreach (self::YEARS as $year) {
            array_push($rows, ['year' => $year, 'rows' => $this->summaryMapYearly($level, $year)]);
        }
        return new SimpleResponse([
            'rows' => $rows,
            'persons' => $persons
        ]);
    }

    public function annualSummaryMap() {
        // get annual summary map of ELHKPN only for the year
        $req = $this->request;
        $this->validate(
            $req->get(),
            [
                'level' => ['validators' => ['required']],
                'year' => ['validators' => ['required', 'digit']]
            ]
        );
        $level = $this->request->get('level');
        $year = $this->request->get('year');
        return new SimpleResponse(
            $this->summaryMapYearly($level, $year)
        );
    }

    protected function summaryMapYearly($level, $year)
    {
        $q_where = 't.year = :year and t.level = :level';
        $params = compact('year');
        if ($level === 'nasional') {
            $params['level'] = 'provinsi';
        } elseif ($level === 'provinsi' || $level === 'kabupaten') {
            $params['level'] = 'kabupaten';
            $idProvinsi = $this->request->get('provinsi_id', 'int', 0);
            $idKotaKabupaten = $this->request->get('', 'int', 0);
            $params['wilayah_id'] = $this->request->get('wilayah_id', 'int', 0);
            $col = $level === 'provinsi' ? 't.parent_wilayah_id' : 't.wilayah_id';
            $q_where .= " and $col = :wilayah_id";
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Invalid Level:' . $level);
        }

        $rows = $this->rawQuery("
        select t.*,
          w.code as wilayah_code
        from elhkpn_kepatuhan as t
        left join master_wilayah as w on
          w.id = t.wilayah_id
        where
          $q_where
        ", $params);

        return $rows;
    }

    public function summaryPerson()
    {
        $service = new JendelaPencegahanService($this);
        $year = $this->request->get('year');
        $wilayah_id = $this->request->get('wilayah_id', 'int', 0);
        $level = $wilayah_id === 0 ? 'nasional' : $this->request->get('level');
        return $service->elhkpnPerson($year, $level, $wilayah_id);
    }

    public function syncData()
    {
        $this->checkScopes(['admin.view']);
        try {
            AuthHelper::validateToolsKey($this->request, $this->config);
        } catch (CustomErrorException $e) {
            throw $e;
        }
        set_time_limit(10 * 60);
        $params = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                'year' => ['validators' => ['required', 'digit']]
            ]
        );
        $year = $params['year'];
        $service = new ElhkpnMartService($this);
        try {
            $res = $service->syncData($year);
            return new SimpleResponse($res);
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function peekData()
    {
        $this->checkScopes(['admin.view']);
        try {
            AuthHelper::validateToolsKey($this->request, $this->config);
        } catch (CustomErrorException $e) {
            throw $e;
        }
        set_time_limit(10 * 60);
        $params = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                'year' => ['validators' => ['required', 'digit']]
            ]
        );
        $year = $params['year'];
        $service = new ElhkpnMartService($this);
        try {
            $res = $service->peekData($year);
            return new SimpleResponse($res);
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function syncDataSummary($year)
    {
        $table = 'elhkpn_kepatuhan';
        $this->db->delete($table, 'year = ?', [$year]);
        $rows = array_merge(
            $this->getSummary($year, 'provinsi'),
            $this->getSummary($year, 'kabupaten')
        );
        $cols = [
            'year', 'wilayah_id', 'parent_wilayah_id', 'level',
            'provinsi', 'kabupaten', 'wilayah_join', 'bidang', 'cnt', 'cnt_kirim'
        ];
        foreach ($rows as $r) {
            $r['wilayah_id'] = 0;
            $r['parent_wilayah_id'] = 0;
            $values = array_map(function ($col) use ($r) {
                return $r[$col];
            }, $cols);
            $this->db->insert($table, $values, $cols);
        }
        $this->db->query("
            update $table as t
            set wilayah_id = w.wilayah_id
            from elhkpn_wilayah as w
            where
              w.join_name = t.wilayah_join
        ");
        $this->db->query("
            update $table as t
            set parent_wilayah_id = p.wilayah_id
            from $table as p
            where t.level = 'kabupaten' and
              p.provinsi = t.provinsi and
              p.level = 'provinsi'
        ");
        return count($rows);
    }

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

        $file = $this->request->hasFiles() ?
            $this->request->getUploadedFiles()[0] : null;
        if (!$file) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "File is empty");
        }
        $csv = new ParseCsv\Csv();
        $csv->parse($file->getTempName());
        $cols = $csv->titles;
        $rows = $csv->data;
        if ($cols !== ['join_name', 'wilayah_id']) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Header not match");
        }
        $table = (new ElhkpnWilayah())->getSource();
        $this->db->begin();
        $this->db->delete($table);
        foreach ($rows as $r) {
            $values = array_map(function ($col) use ($r) {
                return $r[$col];
            }, $cols);
            $this->db->insert($table, $values, $cols);
        }
        $this->db->commit();
        return ['inserted' => count($rows)];
    }

    public function mapGeojson()
    {
        $dir = $this->config['application']['publicDir'];
        $wilayah_id = $this->request->get('wilayah_id', 'int', 0);
        $version = !is_null($this->request->get('version')) ? $this->request->get('version') : getenv("MAP_VERSION");
        $geojson = [
            'features' => []
        ];
        if ($version == 1) {
            if ($wilayah_id === 0) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi.json'));
                return new SimpleResponse($geojson);
            }
            $wilayah = $this->rawQuery('select * from master_wilayah where id = ?', [$wilayah_id])[0];
            $level = $wilayah['level'];
            $code = (int)$wilayah['code'];
            $geojson = json_decode(file_get_contents("$dir/maps/kabupaten.json"), true);
            $filter = function ($e) use ($level, $code) {
                return ($level === 'provinsi' && $e['properties']['A1CODE'] === $code) || ($level === 'kabupaten' && $e['properties']['A2CODE'] === $code);
            };
            $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
        } else if ($version == 2) {
            if ($wilayah_id === 0) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2020.json'), true);
            } else {
                $qResult = $this->rawQuery('select * from master_wilayah where id = ?', [$wilayah_id]);
                if(!empty($qResult) && $qResult != null) {
                    $wilayah = $qResult[0];
                    $level = $wilayah['level'];
                    $code = (int)$wilayah['code'];
                    $geojson = json_decode(file_get_contents($dir . "/maps/kabupaten_2020.json"), true);
                    $filter = function ($e) use ($level, $code) {
                        return ($level === 'provinsi' && $e['properties']['ADM1_PCODE'] === 'ID' . $code) || ($level === 'kabupaten' && $e['properties']['ADM2_PCODE'] === 'ID' . $code);
                    };
                    $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
                }
            }


            $geojson['features'] = MapsHelper::ConvertNewModelToGeneral($geojson['features']);
        } else if ($version == 3) {
            if ($wilayah_id === 0) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2023.json'), true);
            } else {
                $qResult = $this->rawQuery('select * from master_wilayah where id = ?', [$wilayah_id]);
                if(!empty($qResult) && $qResult != null) {
                    $wilayah = $qResult[0];
                    $level = $wilayah['level'];
                    $code = (int)$wilayah['code'];
                    $geojson = json_decode(file_get_contents($dir . "/maps/kabupaten_2023.json"), true);
                    $filter = function ($e) use ($level, $code) {
                        return ($level === 'provinsi' && $e['properties']['ADM1_PCODE'] === 'ID' . $code) || ($level === 'kabupaten' && $e['properties']['ADM2_PCODE'] === 'ID' . $code);
                    };
                    $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
                }
            }


            $geojson['features'] = MapsHelper::ConvertNewModelToGeneral($geojson['features']);
        }

        return new SimpleResponse($geojson);
    }

    public function getAnnouncementLink()
    {
        $this->checkIsAuthenticated();

        $params = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                'id_lhkpn' => ['validators' => ['required']],
            ]
        );

        try {
            $client = new ElhkpnClient();

            $result = $client->getAnnouncementUrl($this->config["elhkpn_prop"]["server"], $params['id_lhkpn']);

            return new SimpleResponse($this->config["elhkpn_prop"]["external_server"] . '/portal/user/' . $result);
        } catch (\Exception $ex) {
            $api_params = http_build_query($params);
            $this->apiLog->log_error("ELHKPN - getAnnouncementUrl", $api_params, $ex->getMessages());
            throw $ex;
        }
    }

    public function getReportLink()
    {
        $this->checkIsAuthenticated();

        $params = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                'id_lhkpn' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'jabatan' => ['validators' => ['required']],
                'instansi' => ['validators' => ['required']]
            ]
        );

        try {
            $client = new ElhkpnClient();

            $result = $client->getReportUrl($this->config["elhkpn_prop"]["server"], $params['id_lhkpn'], $params["nama"], $params["jabatan"], $params["instansi"]);

            return new SimpleResponse($this->config["elhkpn_prop"]["external_server"] . '/portal/user/' . $result);
        } catch (\Exception $ex) {
            $api_params = http_build_query($params);
            $this->apiLog->log_error("ELHKPN - getReportLink", $api_params, $ex->getMessages());
            throw $ex;
        }
    }

    public function summaryYearly() {
        $params = $this->request->get();
        $this->validate(
            $params,
            [
                'year' => ['validators' => ['required']],
                'level' => ['validators' => ['required', [
                    'name' => 'inclusion_in',
                    'params' => [JendelaPencegahanService::LEVEL_NASIONAL, JendelaPencegahanService::LEVEL_PROVINSI, JendelaPencegahanService::LEVEL_KABUPATEN]
                ]]],
                'wilayah_id' => ['validators' => ['digit']],
            ]
        );
        $year = $params["year"];
        $level = $params["level"];
        $wilayahId = array_key_exists('wilayah_id', $params) ? $params['wilayah_id'] : 0;
        $service = new JendelaPencegahanService($this);
        $person = $service->elhkpnPerson($year, $level, $wilayahId);
        $kepatuhan = $service->elhkpnKepatuhan($year, $level, $wilayahId);

        return new SimpleResponse([
            "person" => $person,
            "kepatuhan" => $kepatuhan
        ]);
    }

    public function tren() {
        $years = $this->rawQuery("SELECT DISTINCT year FROM elhkpn_kepatuhan ORDER BY year DESC;");
        $service = new JendelaPencegahanService($this);
        $result = [];
        foreach ($years as $year) {
            $tahun = $year["year"];
            $result[$tahun] = $service->elhkpnSkorByBidang($tahun);
        }

        return new SimpleResponse($result);
    }
}