<?php

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

class DashboardController extends BaseController
{
    public function report()
    {
        $this->checkScopes(['admin.view']);

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

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

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

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

        $userCount = UmUser::count();
        $userReport = UmUser::getCreationReportBetween($validatedRequest['start'], $validatedRequest['end']);
        $commentReport = DiskusiComment::getCreationReportBetween($validatedRequest['start'], $validatedRequest['end']);
        $discussionReport = Diskusi::getCreationReportBetween($validatedRequest['start'], $validatedRequest['end'], Diskusi::TYPE_DISKUSI);
        $commentNewsReport = DiskusiComment::getCreationReportBetween($validatedRequest['start'], $validatedRequest['end'], Diskusi::TYPE_BERITA);
        $newsReport = Diskusi::getCreationReportBetween($validatedRequest['start'], $validatedRequest['end'], Diskusi::TYPE_BERITA);
        $commentComplaintReport = DiskusiComment::getCreationReportBetween($validatedRequest['start'], $validatedRequest['end'], Diskusi::TYPE_KELUHAN);
        $complaintReport = Diskusi::getCreationReportBetween($validatedRequest['start'], $validatedRequest['end'], Diskusi::TYPE_KELUHAN);

        $result = [
            'user_count' => $userCount,
            'user' => $userReport,
            'diskusi' => $discussionReport,
            'komentar' => $commentReport,
            'berita' => $newsReport,
            'komentar_berita' => $commentNewsReport,
            'keluhan' => $complaintReport,
            'komentar_keluhan' => $commentComplaintReport
        ];

        return new CollectionPaginatedResponse([$result]);
    }

    public function newsReport()
    {
        $this->checkScopes(['admin.view']);

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

        $params['start'] = array_key_exists('start', $params) ? $params['start'] : date('Y-m-d', strtotime('-1 week'));
        $params['release_start'] = array_key_exists('release_start', $params) ? $params['release_start'] : date('Y-m-d', strtotime('-10 year'));
        $params['end'] = array_key_exists('end', $params) ? $params['end'] : date('Y-m-d');
        $params['release_end'] = array_key_exists('release_end', $params) ? $params['release_end'] : date('Y-m-d');
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;

        $validatedRequest = $this->validate(
            $params,
            [
                'start' => ['validators' => ['date']],
                'end' => ['validators' => ['date']],
                'release_start' => ['validators' => ['date']],
                'release_end' => ['validators' => ['date']],
                'keyword' => ['validators' => []],
                'limit' => ['validators' => ['required', 'digit']],
                'offset' => ['validators' => ['required', 'digit']],
            ]
        );
        $start = $validatedRequest['start'];
        $releaseStart = $validatedRequest['release_start'];
        $end = $validatedRequest['end'];
        $releaseEnd = $validatedRequest['release_end'];
        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];

        if ($start > $end || $releaseStart > $releaseEnd) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tanggal mulai harus lebih awal dari tanggal akhir");
        }

        $bind = [
            'start' => $start . ' 00:00:00',
            'end' => $end . ' 23:59:59',
            'release_start' => $releaseStart,
            'release_end' => $releaseEnd,
            'keyword' => '%' . $validatedRequest['keyword'] . '%'
        ];
        $query = "SELECT diskusi.id, diskusi.uuid, diskusi.title, diskusi.release_date, diskusi.created_at, diskusi.views,COALESCE(komentar.jml, 0) as jml_komentar,COALESCE(suka.jml, 0) as jml_like,COALESCE(share.jml, 0) as jml_share
            FROM jaga.diskusi
            LEFT OUTER JOIN (
                SELECT diskusi_id, count(*) as jml FROM diskusi_comment WHERE is_deleted = 0 AND created_at::date >= :start AND created_at::date <= :end GROUP BY diskusi_id 
            ) komentar ON komentar.diskusi_id = diskusi.id
            LEFT OUTER JOIN (
                SELECT diskusi_id, count(*) as jml FROM diskusi_like WHERE created_at::date >= :start AND created_at::date <= :end GROUP BY diskusi_id
            ) suka ON suka.diskusi_id = diskusi.id
            LEFT OUTER JOIN (
                SELECT diskusi_id, count(*) as jml FROM diskusi_share WHERE created_at::date >= :start AND created_at::date <= :end GROUP BY diskusi_id
            ) share ON share.diskusi_id = diskusi.id
            WHERE diskusi.is_deleted = 0 AND diskusi.type = 1 AND diskusi.title ILIKE :keyword AND diskusi.release_date::date >= :release_start AND diskusi.release_date::date <= :release_end
            ORDER BY diskusi.release_date desc
            LIMIT $limit OFFSET $offset;";
        $result = $this->rawQuery($query, $bind);

        $query = "SELECT count(*) as total FROM jaga.diskusi WHERE is_deleted = 0 AND type = 1 AND title ILIKE :keyword AND release_date::date >= :release_start AND release_date::date <= :release_end";
        unset($bind['start']);
        unset($bind['end']);
        $total = $this->rawQuery($query, $bind)[0]['total'];

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

    public function exportNewsReport()
    {
        $this->checkScopes(['admin.view']);

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

        $params['start'] = array_key_exists('start', $params) ? $params['start'] : date('Y-m-d', strtotime('-1 week'));
        $params['release_start'] = array_key_exists('release_start', $params) ? $params['release_start'] : date('Y-m-d', strtotime('-10 year'));
        $params['end'] = array_key_exists('end', $params) ? $params['end'] : date('Y-m-d');
        $params['release_end'] = array_key_exists('release_end', $params) ? $params['release_end'] : date('Y-m-d');

        $validatedRequest = $this->validate(
            $params,
            [
                'start' => ['validators' => ['date']],
                'end' => ['validators' => ['date']],
                'release_start' => ['validators' => ['date']],
                'release_end' => ['validators' => ['date']],
                'keyword' => ['validators' => []],
            ]
        );
        $start = $validatedRequest['start'];
        $releaseStart = $validatedRequest['release_start'];
        $end = $validatedRequest['end'];
        $releaseEnd = $validatedRequest['release_end'];

        if ($start > $end || $releaseStart > $releaseEnd) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Tanggal mulai harus lebih awal dari tanggal akhir");
        }

        $query = "SELECT diskusi.id, diskusi.uuid, diskusi.title, diskusi.release_date, diskusi.created_at, diskusi.views,COALESCE(komentar.jml, 0) as jml_komentar,COALESCE(suka.jml, 0) as jml_like,COALESCE(share.jml, 0) as jml_share
            FROM jaga.diskusi
            LEFT OUTER JOIN (
                SELECT diskusi_id, count(*) as jml FROM diskusi_comment WHERE is_deleted = 0 AND created_at::date >= :start AND created_at::date <= :end GROUP BY diskusi_id 
            ) komentar ON komentar.diskusi_id = diskusi.id
            LEFT OUTER JOIN (
                SELECT diskusi_id, count(*) as jml FROM diskusi_like WHERE created_at::date >= :start AND created_at::date <= :end GROUP BY diskusi_id
            ) suka ON suka.diskusi_id = diskusi.id
            LEFT OUTER JOIN (
                SELECT diskusi_id, count(*) as jml FROM diskusi_share WHERE created_at::date >= :start AND created_at::date <= :end GROUP BY diskusi_id
            ) share ON share.diskusi_id = diskusi.id
            WHERE diskusi.is_deleted = 0 AND diskusi.type = 1 AND diskusi.title ILIKE :keyword AND diskusi.release_date::date >= :release_start AND diskusi.release_date::date <= :release_end
            ORDER BY diskusi.release_date desc;";

        $rows = $this->rawQuery($query, [
            'start' => $start . ' 00:00:00',
            'end' => $end . ' 23:59:59',
            'release_start' => $releaseStart,
            'release_end' => $releaseEnd,
            'keyword' => '%' . $validatedRequest['keyword'] . '%'
        ]);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $column = "A";
        $sheet->setCellValue($column . "1", "No.");
        $sheet->setCellValue(++$column . "1", "Judul Berita");
        $sheet->setCellValue(++$column . "1", "Tanggal Tayang");
        $sheet->setCellValue(++$column . "1", "Dilihat");
        $sheet->setCellValue(++$column . "1", "Komentar");
        $sheet->setCellValue(++$column . "1", "Disukai");
        $sheet->setCellValue(++$column . "1", "Dibagikan");
        $lastColumn = $column;

        $rowIndex = 1;
        foreach ($rows as $row) {
            $column = "A";
            $rowIndex++;
            $sheet->setCellValue($column . "" . $rowIndex, ($rowIndex - 1));
            $sheet->setCellValue(++$column . "" . $rowIndex, $row["title"]);
            $sheet->setCellValue(++$column . "" . $rowIndex, $row["release_date"]);
            $sheet->setCellValue(++$column . "" . $rowIndex, $row["views"]);
            $sheet->setCellValue(++$column . "" . $rowIndex, $row["jml_komentar"]);
            $sheet->setCellValue(++$column . "" . $rowIndex, $row["jml_like"]);
            $sheet->setCellValue(++$column . "" . $rowIndex, $row["jml_share"]);
        }

        $styleHeader = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => [
                'bold'  => true,
                'size'  => 12,
                'name'  => 'Arial'
            ]
        ];

        $styleBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'font'  => array(
                'size'  => 9,
                'name'  => 'Arial'
            ),
        ];

        $rangeHeager = 'A1:' . $lastColumn . '1';
        $sheet->getStyle($rangeHeager)->applyFromArray($styleHeader);

        $rangeBody = "A2:" . $lastColumn . $rowIndex;
        $sheet->getStyle($rangeBody)->applyFromArray($styleBody);

        $todayLabel = DateHelper::formatTodayIndex();
        $writer = new Xlsx($spreadsheet);
        $filename = "Rekapitulasi Berita - " . $todayLabel .  ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    public function keluhanRecap()
    {

        $params = $this->request->get();
        $validatedRequest = $this->validate(
            $params,
            [
                'start' => ['validators' => ['digit']],
                'end' => ['validators' => ['digit']],
            ]
        );
        $start = $validatedRequest['start'];
        $end = $validatedRequest['end'];

        $query = "
        WITH tahun AS (
            SELECT 
                EXTRACT(YEAR FROM d.created_at) AS tahun
            FROM diskusi AS d
            WHERE EXTRACT(YEAR FROM d.created_at) BETWEEN 2023 AND 2025
                AND is_deleted = 0
            GROUP BY EXTRACT(YEAR FROM d.created_at)
        )
        SELECT 
            EXTRACT(YEAR FROM d.created_at) AS tahun,
            COALESCE(tdb.type, 'LAINNYA') AS tema_keluhan,
            COUNT(d.id) AS total_keluhan
        FROM diskusi AS d
        JOIN kategori_diskusi AS kd 
            ON d.kategori_diskusi_id = kd.id
        JOIN produk AS p
            ON kd.produk_id = p.id_index
        LEFT JOIN diskusi_objek AS dio
            ON d.id = dio.diskusi_id
        LEFT JOIN diskusi_bansos AS db 
            ON d.id = db.id_diskusi
        LEFT JOIN topik_diskusi_bansos AS tdb
            ON db.id_topik = tdb.id
        JOIN um_user AS u
            ON u.um_id = d.user_id
        JOIN diskusi_comment AS dc
            ON d.id = dc.diskusi_id
        WHERE d.is_deleted = 0
            AND um_user_name NOT LIKE '%Admin.%'
            AND p.id_index = 9
            AND db.deleted_at IS NULL
            AND tdb.deleted_at IS NULL
            AND EXTRACT(YEAR FROM d.created_at) BETWEEN :start AND :end
        GROUP BY 
            EXTRACT(YEAR FROM d.created_at),
            tdb.type
        ORDER BY 
                tdb.type,
                EXTRACT(YEAR FROM d.created_at)
            ASC
        ";

        $result = $this->rawQuery($query, [
            'start' => $start,
            'end' => $end
        ]);

        return new SimpleResponse($result);
    }

    public function diskusiRecap()
    {
        $params = $this->request->get();
        $validatedRequest = $this->validate(
            $params,
            [
                'start' => ['validators' => ['digit']],
                'end' => ['validators' => ['digit']],
            ]
        );
        $start = $validatedRequest['start'];
        $end = $validatedRequest['end'];

        $query = "
            SELECT 
                EXTRACT(YEAR FROM d.created_at) AS tahun,
                kd.title AS tema_diskusi,
                COUNT(d.id) AS total_diskusi
            FROM produk AS t
            RIGHT JOIN kategori_diskusi AS kd
                ON t.id_index = kd.produk_id
            JOIN diskusi AS d
                ON d.kategori_diskusi_id = kd.id
            JOIN um_user AS u
                ON u.um_id = d.user_id
            RIGHT JOIN diskusi_objek AS do
                ON d.id = do.diskusi_id
            JOIN diskusi_comment AS dc
                ON d.id = dc.diskusi_id
            WHERE t.is_show = 1
                AND t.is_active = 't'
                AND d.is_deleted = 0
                AND EXTRACT(YEAR FROM d.created_at) BETWEEN :start AND :end
                AND um_user_name NOT LIKE '%Admin.%'
            GROUP BY
                kd.title,
                EXTRACT(YEAR FROM d.created_at)
            ORDER BY
                    kd.title,
                    EXTRACT(YEAR FROM d.created_at)
                ASC
        ";

        $result = $this->rawQuery($query, [
            'start' => $start,
            'end' => $end
        ]);

        return new SimpleResponse($result);
    }

    public function mcpRecap()
    {
        $params = $this->request->get();
        $validatedRequest = $this->validate(
            $params,
            [
                'start' => ['validators' => ['digit']],
                'end' => ['validators' => ['digit']],
            ]
        );
        $start = $validatedRequest['start'];
        $end = $validatedRequest['end'];

        $query = "
            WITH agg_bobot AS (
                SELECT
                    provinsi_name, 
                    kota_kabupaten_name, 
                    ai.nama AS nama_area,
                    tem.tahun AS tahun,
                    -- CASE 
                    --     WHEN tem.id = 11
                    --         THEN
                    --             0
                    --     ELSE
                    --         SUM(
                    --             COALESCE(
                    --                 answer.nilai_verifikasi_pic, 
                    --                 answer.nilai_verifikasi_qa, 
                    --                 answer.nilai_verifikasi, 
                    --                 0
                    --             )::float * si.presentase_bobot * i.presentase_bobot / 10000
                    --         ) 
                    -- END AS total_presentase_bobot
                    SUM(
                        COALESCE(
                            answer.nilai_verifikasi_pic, 
                            answer.nilai_verifikasi_qa, 
                            answer.nilai_verifikasi, 
                            0
                        )::float * si.presentase_bobot * i.presentase_bobot / 10000
                    ) AS total_presentase_bobot
                FROM korsupgah_survey AS surv
                JOIN korsupgah_template AS tem 
                    ON tem.id = surv.id_template
                        AND tem.deleted_at IS NULL
                JOIN korsupgah_template_sub_indikator AS tsi 
                    ON tem.id = tsi.id_template 
                        AND tsi.deleted_at IS NULL
                JOIN korsupgah_sub_indikator AS si 
                    ON tsi.id_sub_indikator = si.id
                        AND si.deleted_at IS NULL
                JOIN korsupgah_indikator AS i 
                    ON i.id = si.id_indikator
                        AND i.deleted_at IS NULL
                JOIN korsupgah_area_intervensi AS ai 
                    ON ai.id = i.id_area_intervensi
                        AND ai.deleted_at IS NULL
                JOIN korsupgah_indikator_tipe AS it 
                    ON i.id_tipe = it.id
                        AND it.deleted_at IS NULL
                JOIN rel_instansi_wilayah AS riw
                    ON riw.id_instansi = surv.id_instansi
                        AND riw.deleted_at IS NULL
                JOIN instansi AS ins
                    ON ins.id = riw.id_instansi
                        AND ins.deleted_at IS NULL
                JOIN master_provinsi AS mprov
                    ON mprov.provinsi_code::INTEGER = riw.id_provinsi
                JOIN master_kota_kabupaten AS mkotkab
                    ON mkotkab.kota_kabupaten_code::INTEGER = riw.id_kota_kabupaten
                JOIN korsupgah_periode AS p 
                    ON p.id = surv.id_periode
                        AND p.deleted_at IS NULL
                LEFT OUTER JOIN (
                    SELECT 
                        distinct ON (1, 2) a.id_questiON AS id_sub_indikator, 
                        a.id_survey, 
                        a.id AS answer_id, 
                        a.nilai_verifikasi,
                        a.nilai_verifikasi_pic, 
                        a.nilai_verifikasi_qa
                    FROM korsupgah_answer a
                    WHERE a.status >= 1 AND a.deleted_at IS NULL
                    ORDER BY a.id_questiON, a.id_survey, a.created_at DESC
                ) answer ON answer.id_sub_indikator = tsi.id_sub_indikator AND surv.id = answer.id_survey
                WHERE surv.deleted_at IS NULL
                    AND tem.id IN (1, 4, 6, 9, 12, 13) --2018-2024
                GROUP BY
                    tem.tahun,
                    tem.id,
                    provinsi_name, 
                    kota_kabupaten_name,
                    ai.nama
            )
            SELECT 
                tahun,
                nama_area,
                -- AVG(total_presentase_bobot) AS bobot,
                AVG(total_presentase_bobot) AS bobot_round
            FROM agg_bobot
            WHERE tahun BETWEEN :start AND :end
            GROUP BY tahun, nama_area
        ";

        $result = $this->rawQuery($query, [
            'start' => $start,
            'end' => $end
        ]);

        return new SimpleResponse($result);
    }
}