<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class PpjUserUnitController extends BaseController {
    public function list() {
        $this->checkScopes(['admin.view']);
        $params = $this->request->get();
        $params['um_id'] = array_key_exists('um_id', $params) ? $params['um_id'] : -1;
        $params['unit_id'] = array_key_exists('unit_id', $params) ? $params['unit_id'] : -1;
        $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', 'um_id', 'unit_id', 'created_at', 'updated_at'];
        $allowed_order_direction = ['asc', 'desc'];
        $validated = $this->validate(
            $params,
            [
                'um_id' => ['validators' => ['digit']],
                'unit_id' => ['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
                ]]]
            ]
        );

        $where = "(PpjUserUnit.um_id = :um_id: OR 0 > :um_id:) AND 
                (PpjUserUnit.unit_id = :unit_id: OR 0 > :unit_id:) AND 
                (UmUser.nama ILIKE :keyword: OR PpjUnitInduk.nama ILIKE :keyword:)";
        $bind = [
            'um_id' => $validated['um_id'],
            'unit_id' => $validated['unit_id'],
            'keyword' => "%" . $validated['keyword'] . "%"
        ];
        $order = "PpjUserUnit.".$validated['order_by'] . " " . $validated['order_direction'];

        $query = PpjUserUnit::query()
            ->columns("
            PpjUserUnit.id, PpjUserUnit.um_id, PpjUserUnit.unit_id, PpjUserUnit.created_at, PpjUserUnit.updated_at, PpjUserUnit.deleted_at,
            PpjUnitInduk.nama as nama_unit, UmUser.nama as nama_user, UmUser.um_user_name as username
            ")
            ->leftJoin('PpjUnitInduk', 'PpjUserUnit.unit_id = PpjUnitInduk.id')
            ->leftJoin('UmUser', 'PpjUserUnit.um_id = UmUser.um_id')
            ->where($where)
            ->bind($bind)
            ->orderBy($order)
            ->limit($validated["limit"], $validated["offset"])
            ->execute();

        $count = PpjUserUnit::query()
            ->columns("
                PpjUserUnit.id
            ")
            ->leftJoin('PpjUnitInduk', 'PpjUserUnit.unit_id = PpjUnitInduk.id')
            ->leftJoin('UmUser', 'PpjUserUnit.um_id = UmUser.um_id')
            ->where($where)
            ->bind($bind)
            ->execute()
            ->count();

        return new PaginatedResponse($query, $count, $validated['limit'], $validated['offset']);
    }

    public function wilayah() {
        if (!$this->hasScopes(['ppj.monitoring.unitinduk'])) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }

        $userUnits = PpjUserUnit::find('um_id = ' . $this->shared->user->um_id)->toArray();
        if (empty($userUnits)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki Unit Induk');
        }
        $unitIds = array();
        foreach ($userUnits as $userUnit) {
            array_push($unitIds, $userUnit['unit_id']);
        }
        $units = PpjUnitInduk::find('id IN (' . implode($unitIds, ",") . ')')->toArray();
        if (empty($units)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Unit Induk tidak ditemukan');
        }
        $unitNamas = array();
        foreach ($units as $unit) {
            array_push($unitNamas, $unit['nama']);
        }

        $params = $this->request->get();
        $params['nama_kabmendagri'] = array_key_exists('nama_kabmendagri', $params) ? $params['nama_kabmendagri'] : '';
        $validated = $this->validate(
            $params,
            [
                'nama_kabmendagri' => ['validators' => [
                    [
                        'name' => 'max_string',
                        'params' => 100
                    ]
                ]]
            ]
        );

        $result = $this->rawQuery("
            SELECT DISTINCT(kd_kabmendagri), nama_kabmendagri FROM ppj_hal1 
            WHERE namaunitpln IN ('".implode("','", $unitNamas)."')
            AND nama_kabmendagri ILIKE '%".$validated['nama_kabmendagri']."%'
        ");
        return new CollectionPaginatedResponse($result);
    }
}