<?php

use Carbon\Carbon;

class ElhkpnMartService
{
    protected $context;

    const DB = 'databaseElhkpnMart';

    public function __construct($context)
    {
        $this->context = $context;
    }

    public function syncData($year)
    {
        set_time_limit(10 * 60);

        $this->context->db->begin();
        try {
            $cnt_summary = $this->syncDataSummary($year);
            $cnt_persons = $this->syncDataPersons($year);
            $this->context->db->commit();
            return compact('cnt_summary', 'cnt_persons');
        } catch (\Exception $e) {
            $this->context->db->rollback();
            throw $e;
        }
    }

    public function peekData($year)
    {
        set_time_limit(10 * 60);

        $this->context->db->begin();
        try {
            $cnt_persons = $this->peekDataPersons($year);
            $this->context->db->commit();
            return compact('cnt_persons');
        } catch (\Exception $e) {
            $this->context->db->rollback();
            throw $e;
        }
    }

    public function syncDataSummary($year)
    {
        $table = 'elhkpn_kepatuhan';
        $this->context->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->context->db->insert($table, $values, $cols);
        }
        $this->context->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->context->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);
    }

    protected function syncDataPersons($year)
    {
        $rows = $this->getPersons($year);
        $table = 'elhkpn_kepatuhan_person';
        $this->context->db->delete($table, 'year = ?', [$year]);
        $cols = [
            'year', 'wilayah_id', 'level', 'provinsi', 'kabupaten', 'wilayah_join',
            'bidang', 'nama', 'id_lhkpn', 'tanggal_lapor', 'status_laporan',
            'jabatan', 'instansi', 'has_kirim', 'nik',
            'total_harta_tidak_bergerak', 'total_alat_transportasi', 'total_harta_bergerak_lain',
            'total_surat_berharga', 'total_kas', 'total_harta_lainnya', 'total_hutang'
        ];
        foreach ($rows as $r) {
            $r['year'] = $year;
            $r['wilayah_id'] = 0;
            $values = array_map(function ($col) use ($r) {
                return $r[$col];
            }, $cols);
            $this->context->db->insert($table, $values, $cols);
        }
        $this->context->db->query("
            update $table as t
            set wilayah_id = w.wilayah_id
            from elhkpn_wilayah as w
            where
              w.join_name = t.wilayah_join
        ");
        return count($rows);
    }

    private function getBaseQuery($tahun)
    {
        switch ($tahun) {
            case (2018):
                $suffix = '2018';
                break;
            case (2019):
                $suffix = '2019';
                break;
            case(2020):
                $suffix = '2020';
                break;
            case(2021):
                $suffix = '2021';
                break;
            case(2022):
                $suffix = '2022';
                break;
            default:
                $suffix = 'v1';
                break;
        }

        return "
            select
                `Tahun WL` as year,
                id_lhkpn,
                trim(Provinsi) as provinsi,
                trim(Kabupaten) as kabupaten,
                Bidang as bidang,
                Nama as nama,
                `Tanggal Lapor` as tanggal_lapor,
                `Status Laporan` as status_laporan,
                Jabatan as jabatan,
                `NIK` as nik,
                `Nama Instansi` as instansi,
                case when Sudah_belum_kirim = 'Sudah Kirim' then 1 else 0 end as has_kirim,
                `Total Harta Tidak Bergerak` as total_harta_tidak_bergerak,
                `Total Alat Transportasi` as total_alat_transportasi,
                `Total Harta Bergerak Lain` as total_harta_bergerak_lain,
                `Total Surat Berharga` as total_surat_berharga,
                `Total Kas` as total_kas,
                `Total Harta Lainnya` as total_harta_lainnya,
                `Total Hutang` as total_hutang
            from mart_data_elhkpn_kepatuhan_$suffix
            ";
    }


    protected function getSummary($year, $level)
    {
        $q_wilayah = $level === 'provinsi' ? 'provinsi' : "concat(provinsi, ' @@ ', kabupaten)";
        $q_kab = $level === 'provinsi' ? 'null' : 'kabupaten';
        $q_group = $level === 'provinsi' ? 'provinsi, bidang' : 'provinsi, kabupaten, bidang';
        $base = $this->getBaseQuery($year);
        $query = "
        select
          $year as year,
          '$level' as level,
          provinsi,
          $q_kab as kabupaten,
          $q_wilayah as wilayah_join,
          bidang,
          count(*) as cnt,
          sum(has_kirim) as cnt_kirim
        from ($base) as t
        where year = :year
        group by
          $q_group
        ";
        return $this->rawQuery($query, compact('year'), self::DB);
    }

    protected function getPersons($year)
    {
        $level_col = "
        case
        when jabatan like 'presiden republik indonesia' or
            jabatan like 'wakil presiden republik indonesia'
            then 'nasional'
        when kabupaten = '[unknown]' and
            instansi like 'pemerintah%' and
            (
                jabatan like 'gubernur%' or jabatan like 'wakil gubernur%' or
                jabatan like 'sekretaris daerah%'
            )
            then 'provinsi'
        when
            kabupaten !='[unknown]' and
            instansi like 'pemerintah%' and
            (
                jabatan like 'walikota%' or jabatan like 'wakil walikota%' or
                jabatan like 'sekretaris daerah%'
            )
            then 'kabupaten'
        else null end";
        $base = $this->getBaseQuery($year);
        $query = "
        select
          t.*,
          case
            when t.level = 'nasional' then ''
            when t.level = 'provinsi' then provinsi
            when t.level = 'kabupaten' then concat(provinsi, ' @@ ', kabupaten)
            else null end as wilayah_join
        from (
            select *, $level_col as level
            from ($base) as base
        ) as t
        where
          t.year = :year and
          t.level is not null
        ";
        return $this->rawQuery($query, compact('year'), self::DB);
    }

    public function rawQuery($query, $params = [], $connectionName = "db")
    {
        $q = $this->context->$connectionName->query($query, $params);
        $q->setFetchMode(\Phalcon\Db::FETCH_ASSOC);
        $pdo = $q->getInternalResult();
        $rows = $q->fetchAll();
        $float_cols = [
            'float', 'float4', 'float8', 'double', 'numeric', // for postgresql
            'LONGLONG', 'NEWDECIMAL' // for mysql
        ];
        $int_cols = ['int8'];
        $ncol = $pdo->columnCount();
        for ($i = 0; $i < $ncol; $i++) {
            $col = $pdo->getColumnMeta($i);
            $cname = $col['name'];
            $type = isset($col['native_type']) ? $col['native_type'] : null;
            if (in_array($type, $float_cols)) {
                foreach ($rows as &$row) {
                    $row[$cname] = floatval($row[$cname]);
                }
            }
            if (in_array($type, $int_cols)) {
                foreach ($rows as &$row) {
                    $row[$cname] = intval($row[$cname]);
                }
            }
        }
        return $rows;
    }

    public function retrieveKepalaDaerah($year, $nama_instansi) {
        $nama_instansi = strtolower($nama_instansi);
        $query = "
            SELECT
                `Nama Instansi` as nama_instansi,
                `Tahun` as tahun,
                `Provinsi` as provinsi,
                kabupaten,
                nama,
                jabatan
            FROM
                v_kepala_daerah_jaga
            WHERE
                `Tahun` = $year AND LOWER(`Nama Instansi`) LIKE '$nama_instansi' 
        ";
        return $this->rawQuery($query, [], self::DB);
    }

    private function peekDataPersons($year)
    {
        $query = "
            SELECT 
                * 
            FROM mart_data_elhkpn_kepatuhan_v1 
            WHERE `Tahun WL` = :year 
              AND `Nama Instansi` = 'KPU (CALON PRESIDEN DAN WAKIL PRESIDEN 2024)'
        ";

        return $this->rawQuery($query, compact('year'), self::DB);
    }
}