<?php

class PartResponController extends BaseController
{
    public function listJumlahDiskusi()
    {
        $this->checkIsAuthenticated();

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

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';

        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []]
            ]
        );

        if (!empty($params['keyword'])) {
            $jmlh_diskusi = $this->rawQuery("
                SELECT * FROM jaga.produk p
                JOIN (SELECT * FROM jaga.kategori_diskusi) kd on kd.produk_id = p.id_index
                JOIN (SELECT * FROM jaga.diskusi) d on d.kategori_diskusi_id = kd.id
                WHERE kd.id = :id_sektor
            ", ["id_sektor" => $params['keyword']]);
        } else {
            $jmlh_diskusi = DiskusiLikeComment::find();
        }

        $total_record = DiskusiLikeComment::count();

        return new PaginatedResponse($jmlh_diskusi, $total_record, $params['limit'], $params['offset']);
    }

    public function listJumlahResponDiskusi()
    {
        $this->checkIsAuthenticated();

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

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';

        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []]
            ]
        );

        if (!empty($params['keyword'])) {
            $diskusi_respon = $this->rawQuery("
                SELECT * FROM jaga.produk p
                JOIN (SELECT * FROM jaga.kategori_diskusi) kd on kd.produk_id = p.id_index
                JOIN (SELECT * FROM jaga.diskusi) d on d.kategori_diskusi_id = kd.id
                WHERE kd.id = :id_sektor
            ", ["id_sektor" => $params['keyword']]);
        } else {
            $diskusi_respon = DiskusiRespon::find();
        }

        $total_record = DiskusiRespon::count();

        return new PaginatedResponse($diskusi_respon, $total_record, $params['limit'], $params['offset']);
    }

    public function diskusiRespon()
    {
        $getParams = $this->request->get();

        $getParams['start'] = array_key_exists('start', $getParams) ? $getParams['start'] : date('Y-m-d', strtotime('-1 week'));
        $getParams['end'] = array_key_exists('end', $getParams) ? $getParams['end'] : date('Y-m-d');

        $params = $this->validate(
            $getParams,
            [
                'start' => ['validators' => ['date']],
                'end' => ['validators' => ['date']]
            ]
        );

        if ($params['start'] > $params['end']) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tanggal mulai harus lebih awal dari tanggal akhir");
            return false;
        }

        $result = $this->rawQuery("
            SELECT 
            summary.nama,
            avg(summary.response_time),
            max(summary.response_time),
            min(summary.response_time),
            stddev(summary.response_time)
            FROM
            (
                SELECT 
                diskusi.id,
                produk.nama,
                (extract(epoch from comment.created_at) - extract(epoch from diskusi.created_at)) / 3600 as response_time
                FROM
                (SELECT diskusi_id, min(created_at) as created_at FROM jaga.diskusi_comment group by diskusi_id order by diskusi_id) as comment
                JOIN jaga.diskusi on comment.diskusi_id = diskusi.id 
                JOIN jaga.kategori_diskusi on diskusi.kategori_diskusi_id = kategori_diskusi.id
                JOIN jaga.produk on produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi.created_at >= :start AND diskusi.created_at <= :end AND diskusi.type = 0
                ) as summary
            GROUP BY summary.nama
        ", [
            "start" => $params['start'],
            "end" => $params['end']
        ]);

        return new CollectionPaginatedResponse($result);
    }

    public function diskusiNoRespon()
    {
        $getParams = $this->request->get();

        $getParams['start'] = array_key_exists('start', $getParams) ? $getParams['start'] : '';
        $getParams['end'] = array_key_exists('end', $getParams) ? $getParams['end'] : '';

        $params = $this->validate(
            $getParams,
            [
                'start' => ['validators' => []],
                'end' => ['validators' => []]
            ]
        );

        if ($params['start'] > $params['end']) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tanggal mulai harus lebih awal dari tanggal akhir");
        }

        if (empty($params['start']) && empty($params['end'])) {
            $result = $this->rawQuery("
                SELECT produk.nama, count(*) as jumlah FROM jaga.diskusi LEFT OUTER JOIN jaga.diskusi_comment on diskusi.id = diskusi_comment.diskusi_id
                    JOIN jaga.kategori_diskusi on diskusi.kategori_diskusi_id = kategori_diskusi.id
                    JOIN jaga.produk on produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi_comment.diskusi_id is null AND diskusi.type = 0
                GROUP BY produk.nama
            ");
        } else {
            $result = $this->rawQuery("
                SELECT produk.nama, count(*) as jumlah FROM jaga.diskusi LEFT OUTER JOIN jaga.diskusi_comment on diskusi.id = diskusi_comment.diskusi_id
                    JOIN jaga.kategori_diskusi on diskusi.kategori_diskusi_id = kategori_diskusi.id
                    JOIN jaga.produk on produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi_comment.diskusi_id is null AND diskusi.created_at >= :start AND diskusi.created_at <= :end AND diskusi.type = 0
                GROUP BY produk.nama
            ", [
                'start' => $params['start'],
                'end' => $params['end']
            ]);
        }

        return new CollectionPaginatedResponse($result);
    }
    public function diskusiTotalLike()
    {
        $getParams = $this->request->get();

        $getParams['start'] = array_key_exists('start', $getParams) ? $getParams['start'] : '';
        $getParams['end'] = array_key_exists('end', $getParams) ? $getParams['end'] : '';

        $params = $this->validate(
            $getParams,
            [
                'start' => ['validators' => []],
                'end' => ['validators' => []]
            ]
        );

        if ($params['start'] > $params['end']) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tanggal mulai harus lebih awal dari tanggal akhir");
        }

        if (empty($params['start']) && empty($params['end'])) {
            $result = $this->rawQuery("
                SELECT produk.nama, count(produk.nama) as jumlah FROM jaga.diskusi
                JOIN jaga.diskusi_like on diskusi_like.diskusi_id = diskusi.id
                JOIN jaga.kategori_diskusi on diskusi.kategori_diskusi_id = kategori_diskusi.id
                Join jaga.produk on produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi.type = 0
                GROUP BY produk.nama
            ");
        } else {
            $result = $this->rawQuery("
                SELECT produk.nama, count(produk.nama) as jumlah FROM jaga.diskusi
                JOIN jaga.diskusi_like on diskusi_like.diskusi_id = diskusi.id
                JOIN jaga.kategori_diskusi on diskusi.kategori_diskusi_id = kategori_diskusi.id
                Join jaga.produk on produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi.type = 0 AND diskusi.created_at >= :start AND diskusi.created_at <= :end
                GROUP BY produk.nama
            ", [
                'start' => $params['start'],
                'end' => $params['end']
            ]);
        }

        return new CollectionPaginatedResponse($result);
    }

    public function diskusiTotalKomentar()
    {
        $getParams = $this->request->get();

        $getParams['start'] = array_key_exists('start', $getParams) ? $getParams['start'] : '';
        $getParams['end'] = array_key_exists('end', $getParams) ? $getParams['end'] : '';

        $params = $this->validate(
            $getParams,
            [
                'start' => ['validators' => []],
                'end' => ['validators' => []]
            ]
        );

        if ($params['start'] > $params['end']) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tanggal mulai harus lebih awal dari tanggal akhir");
        }

        if (empty($params['start']) && empty($params['end'])) {
            $result = $this->rawQuery("
                SELECT produk.nama, count(produk.nama) as jumlah FROM jaga.diskusi 
                JOIN jaga.diskusi_comment on diskusi_comment.diskusi_id = diskusi.id
                JOIN jaga.kategori_diskusi ON kategori_diskusi.id = diskusi.kategori_diskusi_id
                JOIN jaga.produk ON produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi.type = 0
                GROUP BY produk.nama
            ");
        } else {
            $result = $this->rawQuery("
                SELECT produk.nama, count(produk.nama) as jumlah FROM jaga.diskusi 
                JOIN jaga.diskusi_comment on diskusi_comment.diskusi_id = diskusi.id
                JOIN jaga.kategori_diskusi ON kategori_diskusi.id = diskusi.kategori_diskusi_id
                JOIN jaga.produk ON produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi.type = 0 AND diskusi.created_at >= :start AND diskusi.created_at <= :end
                GROUP BY produk.nama
            ", [
                'start' => $params['start'],
                'end' => $params['end']
            ]);
        }

        return new CollectionPaginatedResponse($result);
    }

    public function totalDiskusi()
    {
        $getParams = $this->request->get();

        $getParams['start'] = array_key_exists('start', $getParams) ? $getParams['start'] : '';
        $getParams['end'] = array_key_exists('end', $getParams) ? $getParams['end'] : '';

        $params = $this->validate(
            $getParams,
            [
                'start' => ['validators' => []],
                'end' => ['validators' => []]
            ]
        );

        if ($params['start'] > $params['end']) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tanggal mulai harus lebih awal dari tanggal akhir");
        }

        if (empty($params['start']) && empty($params['end'])) {
            $result = $this->rawQuery("
                SELECT produk.nama, count(produk.nama) as jumlah FROM jaga.diskusi 
                JOIN jaga.kategori_diskusi ON kategori_diskusi.id = diskusi.kategori_diskusi_id
                JOIN jaga.produk ON produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi.type = 0
                GROUP BY produk.nama
            ");
        } else {
            $result = $this->rawQuery("
                SELECT produk.nama, count(produk.nama) as jumlah FROM jaga.diskusi 
                JOIN jaga.kategori_diskusi ON kategori_diskusi.id = diskusi.kategori_diskusi_id
                JOIN jaga.produk ON produk.id_index = kategori_diskusi.produk_id
                WHERE diskusi.type = 0 AND diskusi.created_at >= :start AND diskusi.created_at <= :end
                GROUP BY produk.nama
            ", [
                'start' => $params['start'],
                'end' => $params['end']
            ]);
        }

        return new CollectionPaginatedResponse($result);
    }
}