<?php

class PancekKppbcController extends BaseController
{
    public function list()
    {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
         if($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin'])) {
            PancekUserAccess::checkEligibleForRoleObserver($this);
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        $params = $this->request->get();

        $params['id_provinsi'] = array_key_exists('id_provinsi', $params) ? $params['id_provinsi'] : null;
        $params['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'id';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'asc';

        $allowed_order = [
            'id',
            'nama',
            'provinsi_name',
            'kota_kabupaten_name',
            'created_at',
            'updated_at'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'id' => 'PancekKppbc.id',
            'nama' => 'PancekKppbc.nama',
            'provinsi_name' => 'MasterProvinsi.provinsi_name',
            'kota_kabupaten_name' => 'MasterKotaKabupaten.kota_kabupaten_name',
            'created_at' => 'PancekKppbc.created_at',
            'updated_at' => 'PancekKppbc.updated_at'
        ];

        $validatedParams = $this->validate(
            $params,
            [
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $conditions = [];
        $conditionsCount = [];
        $bind = [];

        $conditions[] = 'PancekKppbc.deleted_at IS NULL';
        $conditionsCount[] = 'deleted_at IS NULL';

        $conditions[] = 'PancekKppbc.nama ILIKE :keyword:';
        $conditionsCount[] = 'nama ILIKE :keyword';
        $bind['keyword'] = '%' . $validatedParams['keyword'] . '%';

        if($validatedParams['id_provinsi'] != null) {
            $conditions[] = 'PancekKppbc.id_provinsi = :id_provinsi:';
            $conditionsCount[] = 'id_provinsi = :id_provinsi';
            $bind['id_provinsi'] = $validatedParams['id_provinsi'];
        }

        if($validatedParams['id_kota_kabupaten'] != null) {
            $conditions[] = 'PancekKppbc.id_kota_kabupaten = :id_kota_kabupaten:';
            $conditionsCount[] = 'id_kota_kabupaten = :id_kota_kabupaten';
            $bind['id_kota_kabupaten'] = $validatedParams['id_kota_kabupaten'];
        }

        $where = implode(' AND ', $conditions);
        $whereCount = 'WHERE ' . implode(' AND ', $conditionsCount);

        $result = PancekKppbc::query()
            ->columns("
                PancekKppbc.id,
                PancekKppbc.nama,
                PancekKppbc.id_provinsi,
                PancekKppbc.id_kota_kabupaten,
                PancekKppbc.created_at,
                PancekKppbc.updated_at,
                PancekKppbc.deleted_at,
                MasterProvinsi.provinsi_name,
                MasterKotaKabupaten.kota_kabupaten_name
            ")
            ->leftJoin('MasterProvinsi', 'CAST(PancekKppbc.id_provinsi AS TEXT) = MasterProvinsi.provinsi_code')
            ->leftJoin('MasterKotaKabupaten', 'CAST(PancekKppbc.id_kota_kabupaten AS TEXT) = MasterKotaKabupaten.kota_kabupaten_code')
            ->where($where)
            ->bind($bind)
            ->orderBy($bind_order[$params['order_by']] . " " . $params['order_direction'])
            ->limit($params['limit'], $params['offset'])
            ->execute()
            ->toArray();

        $total = $this->rawQuery("
            SELECT count(*) as total FROM jaga.pancek_kppbc 
            $whereCount
        ",
            $bind
        )[0]['total'];

        return new PaginatedResponse($result, $total, $params['limit'], $params['offset']);
    }

    public function detail($id) {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
         if($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin'])) {
            PancekUserAccess::checkEligibleForRoleObserver($this);
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        $data = PancekKppbc::findFirst($id);
        if(!$data) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data KPPBC tidak ditemukan");
        }

        $dataProvinsi = null;
        $dataKotaKab = null;
        if($data->id_provinsi != null) {
            $dataProvinsi = $data->provinsi->toArray();
        }
        if($data->id_kota_kabupaten != null) {
            $dataKotaKab = $data->kota_kabupaten->toArray();
        }

        $data = $data->toArray();
        $data['provinsi'] = $dataProvinsi;
        $data['kota_kabupaten'] = $dataKotaKab;

        return new SimpleResponse($data);
    }

    public function create() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
         if($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin'])) {
            PancekUserAccess::checkEligibleForRoleObserver($this);
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validatePayload($payload);

        try {
            $this->db->begin();

            $data = new PancekKppbc();
            $data->nama = $payload['nama'];
            $data->id_provinsi = $payload['id_provinsi'];
            $data->id_kota_kabupaten = $payload['id_kota_kabupaten'];
            $data->save();

            $this->db->commit();

            return new SimpleResponse($data);

        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function update($id) {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
         if($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin'])) {
             PancekUserAccess::checkEligibleForRoleObserver($this);
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validatePayload($payload);

        try {
            $this->db->begin();
            $datetime = new DateTime('now', new DateTimeZone('Asia/Jakarta'));
            $date = $datetime->format('Y-m-d H:i:s');

            $data = PancekKppbc::findFirst($id);
            if(!$data) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data KPPBC tidak ditemukan!");
            }
            $data->nama = $payload['nama'];
            $data->id_provinsi = $payload['id_provinsi'];
            $data->id_kota_kabupaten = $payload['id_kota_kabupaten'];
            $data->updated_at = $date;
            $data->save();

            $this->db->commit();

            return new SimpleResponse($data);

        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    private function validatePayload($payload)
    {
        $payload = $this->validate($payload, [
            "nama" => ["validators" => ["required"]],
            "id_provinsi" => ["validators" => ["digit"]],
            "id_kota_kabupaten" => ["validators" => ["digit"]],
        ]);

        return $payload;
    }

    public function delete($id) {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
         if($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin'])) {
             PancekUserAccess::checkEligibleForRoleObserver($this);
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        try {
            $this->db->begin();
            $now = DateHelper::getNowTimezoned();

            $data = PancekKppbc::findFirst($id);
            if(!$data) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data KPPBC tidak ditemukan!");
            }

            $data->deleted_at = $now;
            $data->save();

            $users = $data->kppbc_users;
            if(!empty($users)) {
                foreach ($users as $user) {
                    $user->deleted_at = $now;
                    $user->save();
                }
            }

            $this->db->commit();

            return new SimpleResponse($data);

        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function listInstansiPancek($id)
    {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
         if($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin'])) {
            PancekUserAccess::checkEligibleForRoleObserver($this);
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        $params = $this->request->get();

        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'id';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'asc';

        $allowed_order = [
            'id',
            'nama',
            'created_at',
            'updated_at'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'id' => 'pi.id',
            'nama' => 'pi.nama',
            'created_at' => 'pi.created_at',
            'updated_at' => 'pi.updated_at'
        ];

        $validatedParams = $this->validate(
            $params,
            [
                'keyword' => ['validators' => []],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $conditions = [];
        $bind = [];

        $conditions[] = 'pi.kppbc_id = :kppbc_id';
        $bind['kppbc_id'] = $id;

        $conditions[] = 'pi.deleted_at IS NULL';

        $conditions[] = 'pi.nama ILIKE :keyword';
        $bind['keyword'] = '%' . $validatedParams['keyword'] . '%';

        $where = implode(' AND ', $conditions);

        $formulirValidityPeriod = getenv("PANCEK_FORMULIR_VALIDITY_PERIOD");
        $orderBy = $bind_order[$params['order_by']] . " " . $params['order_direction'];
        $limit = $params['limit'];
        $offset = $params['offset'];

        $q = "
            WITH ActiveFormulir AS (
                SELECT 
                    pf.*
                FROM 
                    pancek_formulir pf
                WHERE 
                    pf.deleted_at IS NULL
                    AND now() <= pf.created_at + INTERVAL '$formulirValidityPeriod' YEAR
            )
            SELECT
                pi.*,
                af.id AS formulir_id,
                af.status AS formulir_status,
                af.kppbc_um_id,
                af.kppbc_verify_start_date,
                af.kppbc_verify_end_date,
            	uu.um_user_name AS um_user_name_kppbc,
            	uu.nama AS nama_user_kppbc,
		        (CASE WHEN COUNT(pfl.id) > 0 THEN TRUE ELSE FALSE END) AS is_sla_extended,
                COUNT(pfl.id) AS extend_sla_count
            FROM
                pancek_instansi pi
            LEFT JOIN
                ActiveFormulir af ON pi.id = af.instansi_id
            LEFT JOIN
            	um_user uu ON af.kppbc_um_id = uu.um_id
            LEFT JOIN
                pancek_formulir_log_verifikasi pfl ON pfl.formulir_id = af.id AND pfl.sla_started_at = af.kppbc_verify_start_date
            WHERE
                $where
            GROUP BY
                pi.id, pi.npwp, pi.jenis_instansi_id, pi.sektor_usaha_id, pi.sektor_usaha_nama, pi.nama, pi.provinsi_id, pi.kab_kota_id, pi.alamat, pi.telepon, pi.created_at, pi.updated_at, pi.deleted_at, pi.email, pi.status, pi.verified_at, pi.verified_by, pi.foto, pi.nomor, pi.nib, pi.ceklis_kepentingan, pi.kppbc_id, af.id, af.status, af.kppbc_um_id, af.kppbc_verify_start_date, af.kppbc_verify_end_date, uu.um_user_name, uu.nama
            ORDER BY
                $orderBy
            LIMIT $limit OFFSET $offset;
        ";

        $qCount = "
            WITH ActiveFormulir AS (
                SELECT 
                    pf.*
                FROM 
                    pancek_formulir pf
                WHERE 
                    pf.deleted_at IS NULL
                    AND now() <= pf.created_at + INTERVAL '$formulirValidityPeriod' YEAR
            )
            SELECT
                COUNT(*) AS total
            FROM
                pancek_instansi pi
            LEFT JOIN
                ActiveFormulir af ON pi.id = af.instansi_id
            WHERE
                $where
        ";

        $result = $this->rawQuery($q, $bind);

        $total = $this->rawQuery($qCount,
            $bind
        )[0]['total'];

        return new PaginatedResponse($result, $total, $params['limit'], $params['offset']);
    }

    public function simpleList()
    {
        $params = $this->request->get();

        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'nama';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'asc';

        $allowed_order = [
            'id',
            'nama',
            'created_at',
            'updated_at'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'id' => 'id',
            'nama' => 'nama',
            'created_at' => 'created_at',
            'updated_at' => 'updated_at'
        ];

        $validatedParams = $this->validate(
            $params,
            [
                'keyword' => ['validators' => []],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $conditions = [];
        $bind = [];

        $conditions[] = 'deleted_at IS NULL';

        $conditions[] = 'nama ILIKE :keyword:';
        $bind['keyword'] = '%' . $validatedParams['keyword'] . '%';

        $where = implode(' AND ', $conditions);

        $result = PancekKppbc::find([
            'conditions' => $where,
            'bind' => $bind,
            'order' => $bind_order[$params['order_by']] . " " . $params['order_direction']
        ]);

        return new SimpleResponse($result);
    }
}