<?php

class StranasNewNotificationController extends BaseController {

    // get all info about K/L/PDs to notify at the moment:
    // (only who has unclaimed indicators)
    // 1. Name of instansi K/L/PD
    // 2. Its number of unclaimed indicators
    // 3. e-mail of admin
    // 4. id_instansi
    public function index() {
        $this->checkScopes(['stranas.admin']);
        $periode = $this->getDataPeriodePelaporan();

        $str_target_master = $this->getTargetMaster(
            $periode['id_periode'],
            $periode['id_triwulan']
        ); // get ['rows': array, 'id_target': array, 'id_instansi': array]

        // jaga.str_target: dapatkan nama indikator yang belum diklaim
        $str_target = $this->getTarget(
            $periode['id_periode'], // string
            $str_target_master['id_target'] // array of string
        ); // get ['id_target': array, 'unclaimedIndicator': array]

        $str_target_master = $this->filterStrTargetMasterByIdTarget(
            $str_target_master,
            $str_target['id_target']);

        $instansi = $this->getInstansi($str_target_master['id_instansi']);

        // users roles
        $admin_klpd_user_id = $this->getAdminKlpdUserId();
        // jaga.um_user: dapatkan user data semua admin K/L/PD yang berwenang
        $usersInstansi = $this->getUsersInstansi($admin_klpd_user_id,
            $str_target_master['id_instansi']);

        $target = [];
        foreach($str_target_master['rows'] as $row) {
            if (array_key_exists($row['id_instansi'], $target)) {
                $target[$row['id_instansi']]['count']++;
            } else {
                $target[$row['id_instansi']]['count'] = 1;
            }
            $target[$row['id_instansi']]['nama_instansi'] = $instansi[$row['id_instansi']]['nama'];
            $target[$row['id_instansi']]['email'] = $usersInstansi[$row['id_instansi']]['email'] ?? '';
            $target[$row['id_instansi']]['id_instansi'] = $row['id_instansi'];
        }
        return new CollectionPaginatedResponse([
            'periode' => $periode,
            'target' => array_values($target)
        ]);
    }

    public function indicators($id_instansi) {
        $this->checkScopes(['stranas.admin']);
        $periode = $this->getDataPeriodePelaporan();

        $str_target_master = $this->getTargetMaster(
            $periode['id_periode'],
            $periode['id_triwulan'],
            [$id_instansi]
        ); // get ['rows': array, 'id_target': array, 'id_instansi': array]

        $str_target = $this->getTarget(
            $periode['id_periode'], // string
            $str_target_master['id_target'] // array of string
        ); // get ['id_target': array, 'unclaimedIndicator': array]

        return new CollectionPaginatedResponse(array_values($str_target['unclaimedIndicator']));
    }

    // notify K/L/PD via email to remind about submitting tri-monthly reports
    // concerning Stranas PK achievement 
    public function notify() {

        $this->checkScopes(['stranas.admin']);
        $periode = $this->getDataPeriodePelaporan();

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
              'id_instansi' => ['validators' => []]
            ]
        );
        $str_target_master = $this->getTargetMaster(
            $periode['id_periode'],
            $periode['id_triwulan'],
            sizeof($params['id_instansi']) > 0 ? $params['id_instansi'] : null
        ); // get ['rows': array, 'id_target': array, 'id_instansi': array]

        // jaga.str_target: dapatkan nama indikator yang belum diklaim
        $str_target = $this->getTarget(
            $periode['id_periode'], // string
            $str_target_master['id_target'] // array of string
        ); // get ['id_target': array, 'unclaimedIndicator': array]

        $str_target_master = $this->filterStrTargetMasterByIdTarget(
            $str_target_master,
            $str_target['id_target']);

        // filter dan masukkan nama target ke $unclaimedIndicatorByIdInstansi
        $unclaimedIndicatorByIdInstansi =
            $this->getUnclaimedIndicatorByIdInstansi(
                $str_target_master['rows'],
                $str_target['unclaimedIndicator']);
        unset($str_target);
        
        // jaga.users_roles: dapatkan user id semua admin K/L/PD
        $admin_klpd_user_id = $this->getAdminKlpdUserId();

        // jaga.um_user: dapatkan user data semua admin K/L/PD yang berwenang
        $users = $this->getUsers($admin_klpd_user_id,
            $str_target_master['id_instansi']);

        // berapa email berhasil dikirimkan, berapa yang gagal
        $notificationSent = $this->sendNotification($users, $unclaimedIndicatorByIdInstansi, $periode);

        return new SimpleResponse($notificationSent);
    }

    // History pengiriman notifikasi K/L/PD by time
    public function historyByTime() {
        $this->checkScopes(['stranas.admin']);
        $query = "select a.id, 
                b.yearstart as periode,
                c.label as triwulan,
                d.nama as pengirim,
                to_char(a.created_at, 'YYYY-MM-dd HH24:MM:SS') as tanggal_waktu
            from jaga.str_notification a
                join jaga.str_periode b on b.id = a.id_periode
                join jaga.str_periode_triwulan c on c.id_periode = a.id_periode and c.id_triwulan = a.id_triwulan
                join jaga.um_user d on d.um_id = a.created_by
                order by tanggal_waktu desc";
        $str_notification = $this->rawQuery($query);
        for($i = 0; $i < sizeof($str_notification); $i++) {
            $str_notification[$i]['periode'] .= " - ".((int)$str_notification[$i]['periode'] + 1);
        }
        return new CollectionPaginatedResponse($str_notification);
    }

    public function historyByInstansi() {
        $this->checkScopes(['stranas.admin']);
        $periode = $this->getDataPeriodePelaporan();
        $id_periode = $periode['id_periode'];
        
        $query = "
            select distinct A.id, A.nama
            from jaga.instansi as A join
                jaga.str_target_master as B on
                A.id = B.id_instansi where
                B.id_periode = $id_periode
        ";
        $instansiSQLOutput = $this->rawQuery($query);
        $instansi = [];
        foreach($instansiSQLOutput as $row) {
            $instansi[$row['id']]['id'] = $row['id'];
            $instansi[$row['id']]['nama'] = $row['nama'];
        }

        $output = [];
        foreach($instansi as $row) {
            $output[$row['id']]['id_instansi'] = $row['id'];
            $output[$row['id']]['nama_instansi'] = $row['nama'];
            $output[$row['id']]['count'] = 0;
            $output[$row['id']]['email'] = '';
        }

        $str_target_master = $this->getTargetMaster(
            $periode['id_periode'],
            $periode['id_triwulan']
        ); // get ['rows': array, 'id_target': array, 'id_instansi': array]

        // jaga.str_target: dapatkan nama indikator yang belum diklaim
        $str_target = $this->getTarget(
            $periode['id_periode'], // string
            $str_target_master['id_target'] // array of string
        ); // get ['id_target': array, 'unclaimedIndicator': array]

        $str_target_master = $this->filterStrTargetMasterByIdTarget(
            $str_target_master,
            $str_target['id_target']);

        $admin_klpd_user_id = $this->getAdminKlpdUserId();
        $usersInstansi = $this->getUsersInstansi($admin_klpd_user_id,
            $str_target_master['id_instansi']);

        // history pengiriman notifikasi
        $history = $this->getNotificationHistoryByInstansi($str_target_master['id_instansi']);

        foreach($str_target_master['rows'] as $row) {
            if (array_key_exists($row['id_instansi'], $output)) {
                $output[$row['id_instansi']]['count']++;
            } else {
                $output[$row['id_instansi']]['count'] = 1;
            }
            $output[$row['id_instansi']]['nama_instansi'] = $instansi[$row['id_instansi']]['nama'];
            $output[$row['id_instansi']]['email'] = $usersInstansi[$row['id_instansi']]['email'] ?? '';
            $output[$row['id_instansi']]['id_instansi'] = $row['id_instansi'];
            $output[$row['id_instansi']]['last_created_by'] = 
                $history[$row['id_instansi']]['created_by'] ?? null;
            $output[$row['id_instansi']]['last_created_at'] = 
                $history[$row['id_instansi']]['created_at'] ?? null;
            $output[$row['id_instansi']]['last_success'] = 
                $history[$row['id_instansi']]['success'] ?? null;
        }
        return new CollectionPaginatedResponse(array_values($output));
    }

    public function historyDetailByInstansi($id_instansi) {
        $this->checkScopes(['stranas.admin']);
        $query = "select
            a.email,
            a.success,
            a.message,
            to_char(b.created_at, 'YYYY-MM-dd HH24:MM:SS') AS created_at,
            c.nama,
            d.yearstart,
            e.label
            from jaga.str_notification_detail a join
                 jaga.str_notification b on a.id_notification = b.id join
                 jaga.um_user c on b.created_by = c.um_id join
                 jaga.str_periode d on b.id_periode = d.id join
                 jaga.str_periode_triwulan e on e.id_periode = b.id_periode
            where a.id_instansi = $id_instansi
                and b.created_at between e.started_at and e.ended_at
            order by b.created_at desc";
        $str_notification_detail = $this->rawQuery($query);
        return new CollectionPaginatedResponse($str_notification_detail);
    }

    public function historyDetailByTime($id_notification) {
        $this->checkScopes(['stranas.admin']);
        $query = "select
            b.nama as nama_instansi,
            a.email,
            a.success,
            a.message
            from jaga.str_notification_detail a join
                 jaga.instansi b on a.id_instansi = b.id
            where id_notification = $id_notification";
        $str_notification_detail = $this->rawQuery($query);
        return new CollectionPaginatedResponse($str_notification_detail);
    }

    private function getDataPeriodePelaporan() {
        // jaga.str_periode_triwulan: dapatkan data periode pelaporan:
        // id_periode, periode_label, id_triwulan, tanggal_akhir_pelaporan
        $strWhere = 'now() between started_at and ended_at';
        $activePeriodeTriwulan = StranasNewPeriodeTriwulan::find([
            $strWhere,
            'columns' => 'id_periode, label, id_triwulan, ended_at',
            'limit' => 1,
        ]);
        $ended_at = $activePeriodeTriwulan[0]->ended_at;
        $year = substr($ended_at, 0, 4);
        $month = substr($ended_at, 5, 2);
        $day = substr($ended_at, 8, 2);
        return [
            'id_periode' => $activePeriodeTriwulan[0]->id_periode,
            'periode_label' => $activePeriodeTriwulan[0]->label,
            'id_triwulan' => $activePeriodeTriwulan[0]->id_triwulan,
            'tanggal_akhir_pelaporan' => DateHelper::formatFromDate($year, $month, $day)
        ];
    }

    private function getTargetMaster($id_periode, $id_triwulan, $id_instansi = []) {
        // get data cursors for jaga.str_target_master, fields:
            // - id_target, dan 
            // - id_instansi
        // params: id_periode: string, untuk select where jaga.str_target_master.id_periode
        //         id_triwulan: string, untuk select where jaga.str_target_master.id_triwulan
        // jaga.str_target_master: dapatkan semua indikator-instansi yang belum diklaim
        $params = [
            "id_periode = $id_periode".
            " and klaim_tw$id_triwulan is NULL".
            " and latest_verification(nilai_tw8, nilai_tw7, nilai_tw6, nilai_tw5, nilai_tw4, nilai_tw3, nilai_tw2, nilai_tw1) < 100".
            (!empty($id_instansi) ? " and id_instansi in (".
                implode(",", $id_instansi).")" : ""),
            'columns' => 'id_target, id_instansi',
        ];

        $tbl = StranasNewMasterTarget::find($params);

        // populate id_target
        $rows = [];
        $id_target = [];
        $id_instansi = [];
        foreach($tbl as $row) {
            $rows[] = [
                'id_target' => $row->id_target,
                'id_instansi' => $row->id_instansi
            ];
            if (!in_array($row->id_target, $id_target)) {
                $id_target[] = $row->id_target;
            }
            if (!in_array($row->id_instansi, $id_instansi)) {
                $id_instansi[] = $row->id_instansi;
            }
        }
        return [
            'rows' => $rows,
            'id_target' => $id_target,
            'id_instansi' => $id_instansi
        ];
    }

    private function getTarget($id_periode, $id_target) {
        // $id_periode: string
        // $id_target: array of string
        $tbl = [];
        if (sizeof($id_target) > 0) {
            $tbl = StranasNewTarget::find([
                "id in ({id_target:array})".
                " and NOT is_forced_complete".
                " and deleted_at is null".
                " and id_periode = $id_periode",
                "bind" => [
                    "id_target" => $id_target
                ],
                "columns" => "id, nama",
                "order" => "nama"
            ]);
        }

        $rows = [
            'id_target' => [],
            'unclaimedIndicator' => []
        ];
        foreach($tbl as $row) {
            $rows['id_target'][] = $row->id;
            $rows['unclaimedIndicator'][$row->id]['id'] = $row->id;
            $rows['unclaimedIndicator'][$row->id]['nama'] = $row->nama;
        }

        return $rows;
    }

    private function filterStrTargetMasterByIdTarget($str_target_master, $id_target) {
        // mengembalikan $str_target_master hanya yang ['id_target']-nya ada
        // dalam $id_target
        // params:
        //  - $str_target_master: array
        //  - $id_target: array

        $temp = $str_target_master;
        
        // filter ['id_target']
        for($i = sizeof($temp['id_target']) - 1; $i >= 0; $i--) {
            if (!in_array($temp['id_target'][$i], $id_target)) {
                unset($temp['id_target'][$i]);
            }
        }
        $temp['id_target'] = array_values($temp['id_target']);

        // filter ['rows']
        for($i = sizeof($temp['rows']) - 1; $i >= 0 ; $i--) {
            if (!in_array($temp['rows'][$i]['id_target'], $id_target)) {
                unset($temp['rows'][$i]);
            }
        }
        $temp['rows'] = array_values($temp['rows']);

        // filter ['id_instansi']
        $temp['id_instansi'] = [];
        foreach($temp['rows'] as $row) {
            if (!in_array($row['id_instansi'], $temp['id_instansi'])) {
                $temp['id_instansi'][] = $row['id_instansi'];
            }
        }

        return $temp;
    }

    private function getUnclaimedIndicatorByIdInstansi (
        $strTargetMasterRows,
        $unclaimedIndicators) {
        // get all unclaimed indicators by id_instansi

        $unclaimedIndicatorByIdInstansi = [];
        foreach($strTargetMasterRows as $row) {
            $indicator = [];
            foreach($row as $key=>$value) {
                $indicator[$key] = $value;
            }
            $indicator['name'] = $unclaimedIndicators[$row['id_target']]['nama'];
            $unclaimedIndicatorByIdInstansi[$row['id_instansi']][] = $indicator;
        }
        return $unclaimedIndicatorByIdInstansi;
    }

    private function getAdminKlpdUserId() {
        // get all admin K/L/PD users
        $usersRoles = UsersRoles::find([
            "role_id = 5".
            " and deleted_at is null",
            "order" => "user_id"
        ]);
        $admin_klpd_user_id = [];
        foreach($usersRoles as $row) {
            if (!in_array($row->user_id, $admin_klpd_user_id)) {
                $admin_klpd_user_id[] = $row->user_id;
            }
        }
        return $admin_klpd_user_id;
    }

    private function getUsers($admin_klpd_user_id, $id_instansi) {
        $users = [];
        if (sizeof($admin_klpd_user_id) > 0 && sizeof($id_instansi) > 0) {
            $admin_klpd_param = implode(",", $admin_klpd_user_id);
            $id_instansi_param = implode(",", $id_instansi);
            
            $query = "select a.um_id as um_id,
                    a.id_instansi as id_instansi,
                    a.nama as nama,
                    b.email as email
                    from jaga.um_user as a
                    join jaga.str_user_email b
                    on a.um_id = b.um_id
                    where a.um_id in ($admin_klpd_param) and a.id_instansi in ($id_instansi_param)";
            $users = $this->rawQuery($query);
        }
        return $users;
    }

    private function getUsersInstansi($admin_klpd_user_id, $id_instansi) {
        $users = $this->getUsers($admin_klpd_user_id, $id_instansi);
        $usersInstansi = [];
        foreach($users as $row) {
            $usersInstansi[$row['id_instansi']] = $row;
        }
        return $usersInstansi;
    }

    private function getInstansi($id_instansi) {
        // memperoleh nama instansi
        $instansi = [];
        if (sizeof($id_instansi) > 0) {
            $instansi = Instansi::find([
                "id in ({id_instansi:array})",
                "bind" => [
                    "id_instansi" => $id_instansi
                ],
                "columns" => 'id, nama'
            ]);
        }
        $output = [];
        foreach($instansi as $row) {
            $output[$row['id']]['id'] = $row->id;
            $output[$row['id']]['nama'] = $row->nama;
        }
        return $output;
    }

    private function getNotificationHistoryByInstansi(array $id_instansi) {
        // untuk memperoleh history notifikasi by id_instansi
        $notificationDetailRows = StranasNewNotificationDetail::find([
            "id_instansi in ({id_instansi:array})",
            "bind" => [
                "id_instansi" => $id_instansi
            ],
            "columns" => "id_notification, id_instansi, success",
            "order" => "id_instansi, id_notification desc"
        ]);

        // Ambil id_notification terbesar dari setiap id_instansi,
        // karena yang dibutuhkan adalah data terakhir

        // detail notifikasi
        $notificationDetail = [];
        $id_notification = [];
        foreach($notificationDetailRows as $row) {
            if (array_key_exists($row->id_instansi, $notificationDetail))
                continue;
            $notificationDetail[$row->id_instansi] = [
                'id_instansi' => $row->id_instansi,
                'id_notification' => $row->id_notification,
                'success' => $row->success
            ];
            $id_notification[] = $row->id_notification;
        }

        // notifikasi
        $notificationRows = StranasNewNotification::find([
            "id in ({id_notification:array})",
            "bind" => [
                "id_notification" => $id_notification
            ],
            "columns" => "id, created_by, to_char(created_at, 'YYYY-MM-dd HH24:MM:SS') as created_at"
        ]);
        $notification = [];
        $um_id = [];
        foreach($notificationRows as $row) {
            $notification[$row->id] = [
                'id' => $row->id,
                'created_by' => $row->created_by,
                'created_at' => $row->created_at
            ];
            if (!in_array($row->id, $um_id))
                $um_id[] = $row->created_by;
        }

        // Stranas PK admin users yang mengirimkan notifikasi
        $um_idWhere = implode(",", $um_id);
        $query = "select um_id, nama from jaga.um_user
            where um_id in ($um_idWhere)
        ";
        $userRows = $this->rawQuery($query);
        $users = [];
        foreach($userRows as $row) {
            $users[$row['um_id']] = [
                'um_id' => $row['um_id'],
                'nama' => $row['nama']
            ];
        }

        // sum up all!
        $output = [];
        foreach($notificationDetail as $notificationDetailRow) {
            $id_instansi = $notificationDetailRow['id_instansi'];
            $output[$id_instansi] = [
                'success' => $notificationDetailRow['success'],
                'created_at' => $notification[
                    $notificationDetailRow['id_notification']
                ]['created_at'],
                'created_by' => $users[$notification[
                    $notificationDetailRow['id_notification']
                ]['created_by']]['nama']
                    
            ];
        }
        return $output;
    }

    private function sendNotification(
        $users, 
        $unclaimedIndicatorByIdInstansi,
        $periode ) {
            
        $this->checkScopes(['stranas.admin']);
        $recipients = [];
        $id_instansi = []; // untuk mengambil kembali id_instansi sesuai users yang akan dikirimi e-mail
        foreach($users as $key => $value) {
            $recipients[$key] = $value;
            $recipients[$key]['targets'] = $unclaimedIndicatorByIdInstansi[$value['id_instansi']];
            $id_instansi[] = $value['id_instansi'];
        }

        // memperoleh nama instansi
        $userInstansi = $this->getInstansi($id_instansi);

        $strNotification = new StranasNewNotification();
        $strNotification->id_periode = $periode['id_periode'];
        $strNotification->id_triwulan = $periode['id_triwulan'];
        $strNotification->created_by = $this->shared->user->um_id;
        $strNotification->save();

        $notificationSent = [
            'success' => 0,
            'fail' => 0
        ];

        foreach($recipients as $rowRecipient) {
            // send the e-mail to each recipient
            $namaInstansi = $userInstansi[$rowRecipient['id_instansi']]['nama'];
            $mailText = "
                <div style='font-family: sans-serif; padding-bottom: 10px'>
            ";

            $mailText .= "<p>Yth Bpk/Ibu {$rowRecipient['nama']}<br/>
            Admin {$namaInstansi} untuk Stranas PK<br/>
            di tempat</p>

            <p>Dengan hormat,<p/>

            <p>
                Bersama ini disampaikan bahwa pelaporan aksi pencapaian pencegahan korupsi untuk periode <b>{$periode['periode_label']}</b>
                akan ditutup pada tanggal <b>{$periode['tanggal_akhir_pelaporan']}</b>.</p>
            <p>
                Berikut ini adalah daftar indikator yang perlu dilaporkan:
                <ol>";
            foreach($rowRecipient["targets"] as $rowTarget) {
                $mailText .= "<li>{$rowTarget['name']}</li>";
            }
                    
            $mailText .= "</ol>
            </p>
            <p>
                Mengingat betapa pentingnya laporan pencapaian aksi pencegahan korupsi ini, kami menghimbau Bpk/Ibu untuk segera melaporkannya kepada Stranas PK sebelum masa pelaporan berakhir, melalui link:
            </p>
            <section style='padding: 20px 0'>
                <div style='margin: auto; width: 50%; background-color: rgb(44, 44, 44); border-radius: 15px; box-shadow: 5px 5px #be2026;'>
                    <a class='button'
                        href='https://jaga.id/monitoring'
                        style='display: inline-block; width: 100%; text-align:center; color: white; text-decoration: none;'
                        target='_blank'>
                        <img src='https://jaga.id/logo-jaga-white-new.png'
                            style='vertical-align: text-bottom;' width='75'/>
                        <div style='display: inline-block; vertical-align: 10px; padding: 5px; font-size: 1.2em;'>
                            Laporkan Pencapaian Aksi
                        </div>
                    </a>
                </div>
            </section>
            <p>
                Atas perhatian dan kerja sama Bpk/Ibu kami ucapkan banyak terima kasih.
            </p>
            <section style='padding: 30px 0 10px 0'>Administrator Stranas PK</section>";

            $mailText .= "</div>";
            // send the e-mail
            $strNotificationDetail = new StranasNewNotificationDetail();
            $strNotificationDetail->id_notification = $strNotification->id;
            $strNotificationDetail->id_instansi = $rowRecipient['id_instansi'];
            $strNotificationDetail->email = $rowRecipient['email'];

            try {
                $mailHelper = new StranasMailHelper($this->config['mail_config']);
                $body = $mailHelper->createBody($mailText);
                $title = "Reminder Pelaporan Stranas PK periode {$periode['periode_label']}: $namaInstansi";
                $mailHelper->queue(trim($rowRecipient['email']), $title, $body, true);

                // masukkan ke table
                $strNotificationDetail->success = true;
                $strNotificationDetail->save();
                $notificationSent['success']++;

            } catch (\Exception $e) {
                // masukkan ke table
                $strNotificationDetail->success = false;
                $strNotificationDetail->message = $e->getMessage();
                $strNotificationDetail->save();
                $notificationSent['fail']++;
            }
        }
        return $notificationSent;
    }
}