<?php

class PancekLogController extends BaseController
{
    const PANCEK_ROLES = "'verifikator_pancek', 'verifikator_bk_pancek', 'verifikator_kppbc_pancek', 'user_pancek'";
    public function index()
    {
        $this->checkOneOfScopes(['pancek.admin', 'admin.view']);

        $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']) : 'created_at';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'desc';

        $allowed_order = [
            'created_at',
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'created_at' => 'cl.created_at',
            'um_user_name' => 'u.um_user_name',
            'user_name' => 'u.nama',
            'nama_badan_usaha' => 'pi.nama'
        ];

        $validatedParams = $this->validate(
            $params,
            [
                'formulir_id' => ['validators' => ['digit']],
                'instansi_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
                ]]]
            ]
        );
        $conditions = [];
        $bind = [];
        if (!empty($validatedParams['formulir_id'])) {
            $conditions[] = "(cl.formulir_id = :formulir_id)";
            $bind['formulir_id'] = $validatedParams['formulir_id'];
        }
        if (!empty($validatedParams['instansi_id'])) {
            $conditions[] = "(cl.instansi_id = :instansi_id)";
            $bind['instansi_id'] = $validatedParams['instansi_id'];
        }
        if (!empty($validatedParams['keyword'])) {
            $conditions[] = "(u.nama ILIKE :keyword OR u.um_user_name LIKE :keyword)";
            $bind['keyword'] = '%' . $validatedParams['keyword'] . '%';
        }

        $where = "AND 1=1";
        if (!empty($conditions)) {
            $where .= " AND " . implode(" AND ", $conditions);
        }

        $cte = "
            WITH combined_logs AS (
              SELECT id, formulir_id, instansi_id, user_id, description, state_after, created_at, 'formulir' AS source FROM jaga.pancek_formulir_log
              UNION ALL
              SELECT id, formulir_id, instansi_id, user_id, description, state_after, created_at, 'answer' AS source FROM jaga.pancek_answer_log
            )
        ";

        $selectRows = "SELECT cl.id, cl.formulir_id, cl.instansi_id, cl.user_id, cl.description, cl.state_after, cl.created_at, cl.source, u.um_user_name, u.nama AS user_name, pi.nama AS nama_badan_usaha, STRING_AGG(DISTINCT r.name, '|') AS user_roles";
        $selectCount = "SELECT count(cl.id) as total";

        $query =
            "FROM combined_logs cl
            LEFT JOIN jaga.um_user u ON u.um_id = cl.user_id
            LEFT JOIN jaga.pancek_instansi pi ON pi.id = cl.instansi_id
            LEFT JOIN jaga.users_roles ur ON u.um_id = ur.user_id
            LEFT JOIN jaga.roles r ON r.id = ur.role_id
            WHERE r.name IN (".self::PANCEK_ROLES.") ".$where
        ;
        $group = "GROUP BY cl.id, cl.formulir_id, cl.instansi_id, cl.user_id, cl.description, cl.state_after, cl.created_at, cl.source, u.um_id, pi.id";
        $order = "ORDER BY ".$bind_order[$validatedParams["order_by"]]." ".$validatedParams["order_direction"];
        $limit = $validatedParams["limit"];
        $offset = $validatedParams["offset"];

        $queryRows = $cte . " " . $selectRows . " " . $query . " " . $group . " " . $order . " LIMIT " . $limit . " OFFSET " . $offset . ";";
        $queryCount = $cte . " " . $selectCount . " " . $query . ";";

        $rows = $this->rawQuery($queryRows, $bind);
        $count = $this->rawQuery($queryCount, $bind);

        $result = [];
        foreach ($rows as $row) {
            $row['state_after'] = json_decode($row['state_after'], true);
            $result[] = $row;
        }

        $total = $count[0]["total"];

        return new PaginatedResponse($result, $total, $limit, $offset);
    }

    public function logsByFormulir($formulirId)
    {
        $this->checkOneOfScopes(['pancek.admin', 'admin.view']);

        $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',
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'id' => 'fl.id',
        ];

        $validatedParams = $this->validate(
            $params,
            [
                'formulir_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
                ]]]
            ]
        );

        $rows = $this->rawQuery(
            "SELECT fl.id, fl.description, fl.created_at, string_agg(r.name, '|') as roles, fl.state_after
            FROM pancek_formulir_log fl
            LEFT JOIN users_roles ur ON fl.user_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE r.name IN (".self::PANCEK_ROLES.")
            AND fl.formulir_id = ".$formulirId."
            GROUP BY fl.id
            ORDER BY ".$bind_order[$validatedParams["order_by"]]." ".$validatedParams["order_direction"]." LIMIT ".$validatedParams["limit"]." OFFSET ".$validatedParams["offset"].";"
        );

        $total = $this->rawQuery(
            "SELECT COUNT(DISTINCT fl.id) as total
            FROM pancek_formulir_log fl
            LEFT JOIN users_roles ur ON fl.user_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE r.name IN ('verifikator_pancek', 'verifikator_bk_pancek', 'verifikator_kppbc_pancek', 'user_pancek')
            AND fl.formulir_id = " . $formulirId
        )[0]['total'];

        $result = [];
        foreach ($rows as $row) {
            $row['state_after'] = json_decode($row['state_after'], true);
            $result[] = $row;
        }

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

    public function logsAnswerByFormulir($formulirId)
    {
        $this->checkOneOfScopes(['pancek.admin', 'admin.view']);

        $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',
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'id' => 'fl.id',
        ];

        $validatedParams = $this->validate(
            $params,
            [
                'formulir_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
                ]]]
            ]
        );

        $rows = $this->rawQuery(
            "SELECT fl.id, fl.answer_id, ki.code, ki.label as komponsen_indikator, fl.description, fl.created_at, string_agg(r.name, '|') as roles, fl.state_after
            FROM pancek_answer_log fl
            LEFT JOIN users_roles ur ON fl.user_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            LEFT JOIN pancek_answer a ON a.id = fl.answer_id
            LEFT JOIN pancek_komponen_indikator ki ON ki.id = a.komponen_indikator_id
            WHERE r.name IN ('verifikator_pancek', 'verifikator_bk_pancek', 'verifikator_kppbc_pancek', 'user_pancek')
            AND fl.formulir_id = ".$formulirId."
            GROUP BY fl.id, ki.id
            ORDER BY ".$bind_order[$validatedParams["order_by"]]." ".$validatedParams["order_direction"]." LIMIT ".$validatedParams["limit"]." OFFSET ".$validatedParams["offset"].";"
        );

        $total = $this->rawQuery(
            "SELECT COUNT(DISTINCT fl.id) as total
            FROM pancek_answer_log fl
            LEFT JOIN users_roles ur ON fl.user_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE r.name IN ('verifikator_pancek', 'verifikator_bk_pancek', 'verifikator_kppbc_pancek', 'user_pancek')
            AND fl.formulir_id = " . $formulirId
        )[0]['total'];

        $result = [];
        foreach ($rows as $row) {
            $row['state_after'] = json_decode($row['state_after'], true);
            $result[] = $row;
        }

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