<?php

class KorsupgahController extends BaseController
{

  const BASE = '
select *,
  progress_verif * bobot as progress
  from (
select
  cast(tahun as int) as year,
  "WILAYAH PROVINSI" as provinsi,
  "KAB / KOTA" as kabupaten,
  area as areainter,
  indikator,
  progres_verif as progress_verif,
  bobot as bobot_indikator,
  cast("Bobot Area" as float) as bobot_area,
  cast("Bobot Area" as float) * 100 * bobot as bobot,
  urut as ord
from mart_data_korsupgah_all
where
  "WILAYAH PROVINSI" is not null
) as t where
  (year = :year) and
  (provinsi = :provinsi or :provinsi is null) and
  (kabupaten = :kabupaten or :kabupaten is null) and
  (areainter = :areainter or :areainter is null) and
  (indikator = :indikator or :indikator is null)
';

  public function summaryMap()
  {
    $req = $this->request;
    $level = $req->get('level');
    $params = [
      'year' => $req->get('year', 'int'),
      'provinsi' => $req->get('provinsi'),
      'kabupaten' => $req->get('kabupaten'),
      'areainter' => $req->get('areainter'),
      'indikator' => $req->get('indikator')
    ];
    if ($level === 'nasional') {
      $col = 'provinsi';
    } else if ($level === 'provinsi' || $level === 'kabupaten') {
      $col = 'kabupaten';
    }

    $base = self::BASE;
    $query = "select
           $col, sum(progress) / sum(bobot) as value
        from ($base) as t group by $col";
    $data = $this->rawQuery($query, $params);

    return new SimpleResponse($data);
  }

  public function summaryIndikator()
  {
    $req = $this->request;
    $level = $req->get('level');
    $params = [
      'year' => $req->get('year', 'int'),
      'provinsi' => $req->get('provinsi'),
      'kabupaten' => $req->get('kabupaten'),
      'areainter' => null,
      'indikator' => null
    ];
    $base = self::BASE;
    $query = "select
          areainter, indikator, sum(progress) as progress, sum(bobot) as bobot,
          min(ord) as ord
          from ($base) as t group by areainter, indikator";
    $data = $this->rawQuery($query, $params);
    return new SimpleResponse($data);
  }

  public function mapGeojson()
  {
    $provinsi = $this->request->get('provinsi');
    $kabupaten = $this->request->get('kabupaten');
    $version = !is_null($this->request->get('version')) ? $this->request->get('version') : getenv("MAP_VERSION");
    $dir = $this->config['application']['publicDir'];
    $geojson = null;
    if ($version == 1) {
      if ($provinsi === null) {
        $geojson = json_decode(file_get_contents($dir . '/maps/provinsi.json'));
      } else {
        $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten.json'), true);
        $filter = function ($e) use ($provinsi, $kabupaten) {
          return ($provinsi === null || $e['properties']['A1KPKNAME'] === $provinsi) && ($kabupaten === null || $e['properties']['A2KPKNAME'] === $kabupaten);
        };
        $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
      }
    }
    else if ($version == 2) {
      if ($provinsi === null) {
        $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2020.json'), true);
      } else {
        $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten_2020.json'), true);
        $filter = function ($e) use ($provinsi, $kabupaten) {
          return ($provinsi === null || $e['properties']['ADM1_PCODE'] === 'ID' . $provinsi) && ($kabupaten === null || $e['properties']['ADM2_PCODE'] === 'ID' . $kabupaten);
        };
        $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
      }

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

    else if ($version == 3) {
        if ($provinsi === null) {
            $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2023.json'), true);
        } else {
            $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten_2023.json'), true);
            $filter = function ($e) use ($provinsi, $kabupaten) {
                return ($provinsi === null || $e['properties']['ADM1_PCODE'] === 'ID' . $provinsi) && ($kabupaten === null || $e['properties']['ADM2_PCODE'] === 'ID' . $kabupaten);
            };
            $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
        }

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

    return new SimpleResponse($geojson);
  }

  public function capaianAreaIntervensi()
  {
    $result = $this->rawQuery("
select summary.id,
    summary.nama,
    summary.presentase_bobot,
    sum(nilai) as presentase_nilai,
    sum(nilai_verifikasi) as presentase_nilai_verifikasi
from
    ( SELECT distinct on (s.id_instansi, tw.id) i.id as id,
            i.nama as nama,
            i.presentase_bobot as presentase_bobot,
            coalesce(a.nilai, 0)::decimal/temp.total_instansi * tw.presentase_bobot::decimal/100 * si.presentase_bobot::decimal/100 as nilai,
            coalesce(a.nilai_verifikasi,0)::decimal/temp.total_instansi * tw.presentase_bobot::decimal/100 * si.presentase_bobot::decimal/100 as nilai_verifikasi
        FROM jaga. korsupgah_survey s join jaga.korsupgah_template t on s.id_template = t.id
            join jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
            left outer join jaga.korsupgah_answer a on s.id = a.id_survey and ktsi.id_sub_indikator = a.id_question
            join jaga.korsupgah_sub_indikator tw on tw.id = ktsi.id_sub_indikator
            join jaga.korsupgah_indikator si on tw.id_indikator = si.id
            join jaga.korsupgah_area_intervensi i on si.id_area_intervensi = i.id
            join (
                SELECT ktsi.id_sub_indikator as id_sub_indikator, count(distinct s.id_instansi) as total_instansi
                FROM jaga.korsupgah_survey s join jaga.korsupgah_template t on s.id_template = t.id
                    JOIN jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
                GROUP BY ktsi.id_sub_indikator
                order by ktsi.id_sub_indikator
            ) as temp on tw.id = temp.id_sub_indikator
            order by s.id_instansi, tw.id, a.nilai_verifikasi DESC NULLS LAST, a.nilai DESC NULLS LAST
    ) AS summary
group by id, nama, presentase_bobot
order by id
        ", []);

    return new CollectionPaginatedResponse($result);
  }

  public function capaianIndikator()
  {
    $params = $this->validate(
      $this->request->get(),
      [
        'id_area_intervensi' => ['validators' => ['required', 'digit']]
      ]
    );

    $result = $this->rawQuery("
select summary.id,
    summary.nama,
    summary.presentase_bobot,
    sum(nilai) as presentase_nilai,
    sum(nilai_verifikasi) as presentase_nilai_verifikasi
from
    ( SELECT distinct on (s.id_instansi, tw.id) si.id as id,
            si.nama as nama,
            si.presentase_bobot as presentase_bobot,
            coalesce(a.nilai, 0)::decimal/temp.total_instansi * tw.presentase_bobot::decimal/100 as nilai,
            coalesce(a.nilai_verifikasi,0)::decimal/temp.total_instansi * tw.presentase_bobot::decimal/100 as nilai_verifikasi
        FROM jaga. korsupgah_survey s join jaga.korsupgah_template t on s.id_template = t.id
            join jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
            left outer join jaga.korsupgah_answer a on s.id = a.id_survey and ktsi.id_sub_indikator = a.id_question
            join jaga.korsupgah_sub_indikator tw on tw.id = ktsi.id_sub_indikator
            join jaga.korsupgah_indikator si on tw.id_indikator = si.id
            join jaga.korsupgah_area_intervensi i on si.id_area_intervensi = i.id
            join (
                SELECT ktsi.id_sub_indikator as id_sub_indikator, count(distinct s.id_instansi) as total_instansi
                FROM jaga.korsupgah_survey s join jaga.korsupgah_template t on s.id_template = t.id
                    JOIN jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
                GROUP BY ktsi.id_sub_indikator
                order by ktsi.id_sub_indikator
            ) as temp on tw.id = temp.id_sub_indikator
            where i.id = :id_area_intervensi
            order by s.id_instansi, tw.id, a.nilai_verifikasi DESC NULLS LAST, a.nilai DESC NULLS LAST
    ) AS summary
group by id, nama, presentase_bobot
order by id
        ", [
      'id_area_intervensi' => $params['id_area_intervensi']
    ]);

    return new CollectionPaginatedResponse($result);
  }

  public function capaianSubIndikator()
  {
    $params = $this->validate(
      $this->request->get(),
      [
        'id_indikator' => ['validators' => ['required', 'digit']]
      ]
    );

    $result = $this->rawQuery("
select summary.id,
    summary.nama,
    summary.presentase_bobot,
    sum(nilai) as presentase_nilai,
    sum(nilai_verifikasi) as presentase_nilai_verifikasi
from
    ( SELECT distinct on (s.id_instansi, tw.id) tw.id as id,
            tw.nama as nama,
            tw.presentase_bobot as presentase_bobot,
            coalesce(a.nilai, 0)::decimal/temp.total_instansi as nilai,
            coalesce(a.nilai_verifikasi,0)::decimal/temp.total_instansi as nilai_verifikasi
        FROM jaga. korsupgah_survey s join jaga.korsupgah_template t on s.id_template = t.id
            join jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
            left outer join jaga.korsupgah_answer a on s.id = a.id_survey and ktsi.id_sub_indikator = a.id_question
            join jaga.korsupgah_sub_indikator tw on tw.id = ktsi.id_sub_indikator
            join jaga.korsupgah_indikator si on tw.id_indikator = si.id
            join jaga.korsupgah_area_intervensi i on si.id_area_intervensi = i.id
            join (
                SELECT ktsi.id_sub_indikator as id_sub_indikator, count(distinct s.id_instansi) as total_instansi
                FROM jaga.korsupgah_survey s join jaga.korsupgah_template t on s.id_template = t.id
                    JOIN jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
                GROUP BY ktsi.id_sub_indikator
                order by ktsi.id_sub_indikator
            ) as temp on tw.id = temp.id_sub_indikator
            where si.id = :id_indikator
            order by s.id_instansi, tw.id, a.nilai_verifikasi DESC NULLS LAST, a.nilai DESC NULLS LAST
    ) AS summary
group by id, nama, presentase_bobot
order by id
        ", [
      'id_indikator' => $params['id_indikator']
    ]);

    return new CollectionPaginatedResponse($result);
  }

  public function capaianInstansiPerSubIndikator()
  {
    $params = $this->validate(
      $this->request->get(),
      [
        'id_sub_indikator' => ['validators' => ['required', 'digit']]
      ]
    );

    $result = $this->rawQuery("
select
  summary.id,
  summary.nama,
  summary.nilai,
  summary.nilai_verifikasi
from
  (
    SELECT
      distinct on (s.id_instansi, tw.id) instansi.id as id,
      instansi.nama as nama,
      coalesce(a.nilai, 0) :: decimal as nilai,
      coalesce(a.nilai_verifikasi, 0) :: decimal as nilai_verifikasi
    FROM
      jaga. korsupgah_survey s
      join jaga.korsupgah_template t on s.id_template = t.id
      join jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
      join jaga.instansi instansi on s.id_instansi = instansi.id
      left outer join jaga.korsupgah_answer a on s.id = a.id_survey
      and ktsi.id_sub_indikator = a.id_question
      join jaga.korsupgah_sub_indikator tw on tw.id = ktsi.id_sub_indikator
      join jaga.korsupgah_indikator si on tw.id_indikator = si.id
      join jaga.korsupgah_area_intervensi i on si.id_area_intervensi = i.id
    where
      tw.id = :id_sub_indikator
    order by
      s.id_instansi,
      tw.id,
      a.nilai_verifikasi DESC NULLS LAST,
      a.nilai DESC NULLS LAST
  ) summary
order by
  summary.nilai_verifikasi DESC NULLS LAST,
  summary.nilai DESC NULLS LAST
        ", [
      'id_sub_indikator' => $params['id_sub_indikator']
    ]);

    return new CollectionPaginatedResponse($result);
  }

  public function progressPerSubIndikatorInstansi()
  {
    $params = $this->validate(
      $this->request->get(),
      [
        'id_instansi' => ['validators' => ['required', 'digit']],
        'id_sub_indikator' => ['validators' => ['required', 'digit']]
      ]
    );

    $result = $this->rawQuery(
      "
SELECT
  p.id as id,
  p.nama as nama,
  coalesce(a.nilai, 0) :: decimal as nilai,
  coalesce(a.nilai_verifikasi, 0) :: decimal as nilai_verifikasi
FROM
  jaga. korsupgah_survey s
  join jaga.korsupgah_template t on s.id_template = t.id
  join jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
  join jaga.korsupgah_periode p on s.id_periode = p.id
  left outer join jaga.korsupgah_answer a on s.id = a.id_survey
  and ktsi.id_sub_indikator = a.id_question
where
  ktsi.id_sub_indikator = :id_sub_indikator
  AND s.id_instansi = :id_instansi
order by
  p.id",
      [
        'id_instansi' => $params['id_instansi'],
        'id_sub_indikator' => $params['id_sub_indikator']
      ]
    );

    return new CollectionPaginatedResponse($result);
  }

  public function capaianInstansiPerIndikator()
  {
    $params = $this->validate(
      $this->request->get(),
      [
        'id_indikator' => ['validators' => ['required', 'digit']],
      ]
    );

    $result = $this->rawQuery("
select
  summary.id,
  summary.nama,
  sum(nilai) as presentase_nilai,
  sum(nilai_verifikasi) as presentase_nilai_verifikasi
from
  (
    SELECT
      distinct on (s.id_instansi, tw.id) instansi.id as id,
      instansi.nama as nama,
      coalesce(a.nilai, 0) :: decimal / temp.total_instansi * tw.presentase_bobot:: decimal / 100 as nilai ,
      coalesce(a.nilai_verifikasi, 0) :: decimal / temp.total_instansi * tw.presentase_bobot:: decimal / 100 as nilai_verifikasi
    FROM
      jaga. korsupgah_survey s
      join jaga.korsupgah_template t on s.id_template = t.id
      join jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
      join jaga.instansi instansi on s.id_instansi = instansi.id
      left outer join jaga.korsupgah_answer a on s.id = a.id_survey
      and ktsi.id_sub_indikator = a.id_question
      join jaga.korsupgah_sub_indikator tw on tw.id = ktsi.id_sub_indikator
      join jaga.korsupgah_indikator si on tw.id_indikator = si.id
      join jaga.korsupgah_area_intervensi i on si.id_area_intervensi = i.id
      join (
        SELECT
          ktsi.id_sub_indikator as id_sub_indikator,
          count(distinct s.id_instansi) as total_instansi
        FROM
          jaga.korsupgah_survey s
          join jaga.korsupgah_template t on s.id_template = t.id
          JOIN jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
        GROUP BY
          ktsi.id_sub_indikator
        order by
          ktsi.id_sub_indikator
      ) as temp on tw.id = temp.id_sub_indikator
    where
      si.id = :id_indikator
    order by
      s.id_instansi,
      tw.id,
      a.nilai_verifikasi DESC NULLS LAST,
      a.nilai DESC NULLS LAST
  ) AS summary
group by
  id,
  nama
order by
  sum(nilai) DESC NULLS LAST", [
      'id_indikator' => $params['id_indikator']
    ]);

    return new CollectionPaginatedResponse($result);
  }

  public function capaianInstansiPerAreaIntervensi()
  {
    $params = $this->validate(
      $this->request->get(),
      [
        'id_area_intervensi' => ['validators' => ['required', 'digit']],
      ]
    );


    $result = $this->rawQuery("
select
  summary.id,
  summary.nama,
  sum(nilai) as presentase_nilai,
  sum(nilai_verifikasi) as presentase_nilai_verifikasi
from
  (
    SELECT
      distinct on (s.id_instansi, tw.id) instansi.id as id,
      instansi.nama as nama,
      coalesce(a.nilai, 0) :: decimal / temp.total_instansi * tw.presentase_bobot:: decimal / 100 * si.presentase_bobot::decimal/100 as nilai,
      coalesce(a.nilai_verifikasi, 0) :: decimal / temp.total_instansi * tw.presentase_bobot :: decimal / 100 * si.presentase_bobot::decimal/100 as nilai_verifikasi
    FROM
      jaga. korsupgah_survey s
      join jaga.korsupgah_template t on s.id_template = t.id
      join jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
      join jaga.instansi instansi on s.id_instansi = instansi.id
      left outer join jaga.korsupgah_answer a on s.id = a.id_survey
      and ktsi.id_sub_indikator = a.id_question
      join jaga.korsupgah_sub_indikator tw on tw.id = ktsi.id_sub_indikator
      join jaga.korsupgah_indikator si on tw.id_indikator = si.id
      join jaga.korsupgah_area_intervensi i on si.id_area_intervensi = i.id
      join (
        SELECT
          ktsi.id_sub_indikator as id_sub_indikator,
          count(distinct s.id_instansi) as total_instansi
        FROM
          jaga.korsupgah_survey s
          join jaga.korsupgah_template t on s.id_template = t.id
          JOIN jaga.korsupgah_template_sub_indikator ktsi on t.id = ktsi.id_template
        GROUP BY
          ktsi.id_sub_indikator
        order by
          ktsi.id_sub_indikator
      ) as temp on tw.id = temp.id_sub_indikator
    where
      i.id = :id_area_intervensi
    order by
      s.id_instansi,
      tw.id,
      a.nilai_verifikasi DESC NULLS LAST,
      a.nilai DESC NULLS LAST
  ) AS summary
group by
  id,
  nama
order by
  sum(nilai_verifikasi) NULLS LAST,
  sum(nilai) DESC NULLS LAST", [
      'id_area_intervensi' => $params['id_area_intervensi']
    ]);
    return new CollectionPaginatedResponse($result);
  }

  private function setAreaIntervensi2019($year, $data)
  {
    $temp = $data;
    if ($year == 2019) {
      for ($i=0; $i < count($temp); $i++) {
        if ($temp[$i]['areainter'] == 'DANA DESA') {
          unset($temp[$i]);
        }
      }

      return $temp;
    }

    return $temp;
  }

  public function downloadMcpContent() {
    // view get url
    $params = $this->request->get();

    $validatedRequest = $this->validate(
      $params,
      [
        'key' => ['validators' => ['required']],
        'filename' => ['validators' => ['required']],
        'download' => ['validators' => []],
      ]
    );
    
    $filename = $params['filename'];
    $key = $params['key'] . DIRECTORY_SEPARATOR . $filename;
    $download = $params['download'];
    
    $url = $this->minio->basePublicUrlUsingSecondaryS3($key, $filename, $download);
    return new SimpleResponse($url);
  }
}