<?php

class SatuanBosBopController extends BaseController{
    public function listProvinsi() {
        $request = $this->request->get();
        $validatedRequest = $this->validate($request, [
            'keyword' => ['validators' => []],
        ]);

        $rows = DataSatuanBosBop::find([
            'columns' => 'distinct provinsi',
            'conditions' => 'provinsi ILIKE :keyword:',
            'order' => 'provinsi',
            'bind' => ['keyword' => '%' . $validatedRequest['keyword'] . '%']
        ]);

        return new CollectionPaginatedResponse($rows);
    }

    public function listByProvinsi() {
        $request = $this->request->get();
        $request['limit'] = array_key_exists('limit', $request) ? $request['limit'] : 10;
        $request['offset'] = array_key_exists('offset', $request) ? $request['offset'] : 0;
        $validatedRequest = $this->validate($request, [
            'provinsi' => ['validators' => []],
            'tahun' => ['validators' => ['required', 'digit']],
            'tipe' => ['validators' => ['required']],
            'limit' => ['validators' => ['required', 'digit']],
            'offset' => ['validators' => ['required', 'digit']]
        ]);
        $conditions = [
            "tahun = :tahun",
            "tipe = :tipe"
        ];
        $bind = [
            'tahun' => $validatedRequest['tahun'],
            'tipe' => $validatedRequest['tipe']
        ];
        if (!empty($validatedRequest['provinsi'])) {
            array_push($conditions, "provinsi ILIKE :provinsi");
            $bind['provinsi'] = $validatedRequest['provinsi'];
        }

        $where = join(" and ", $conditions);
        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];

        $jenjangs = $this->rawQuery("SELECT distinct jenjang FROM jaga.data_satuan_bos_bop WHERE $where", $bind);
        $selects = [];
        foreach ($jenjangs as $jenjang) {
            $j = $jenjang['jenjang'];
            array_push($selects, "SUM(satuan) FILTER (WHERE jenjang ILIKE '$j') AS $j");
        }
        $selects = implode(", ", $selects);

        $rows = $this->rawQuery("SELECT
            provinsi,
            kab_kota,
            $selects
            FROM jaga.data_satuan_bos_bop
            WHERE $where
            GROUP BY provinsi, kab_kota, tipe, tahun
            ORDER BY provinsi, kab_kota
            LIMIT $limit OFFSET $offset;
        ", $bind);

        $total = $this->rawQuery("SELECT count(distinct(kab_kota)) as total FROM jaga.data_satuan_bos_bop WHERE $where", $bind)[0]['total'];

        return new PaginatedResponse($rows, $total, $validatedRequest['limit'], $validatedRequest['offset']);
    }
}