<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Dashboard extends AdminController
{
    public function __construct()
    {
        parent::__construct();
        $this->load->model('dashboard_model');
    }

    // This is admin dashboard view
    public function index()
    {
        $this->load->model('tickets_model');
        $this->load->model('clients_model');

        $isAdmin  = is_admin();
        $staffId  = get_staff_user_id();
        $range30  = date('Y-m-d 00:00:00', strtotime('-30 days'));

        $ticketWhere = '';
        if (!$isAdmin) {
            $ticketWhere = ' AND assigned = ' . (int)$staffId;
        }
        $customerJoin = '';
        if (!$isAdmin) {
            $customerJoin = ' AND userid IN (SELECT customer_id FROM ' . db_prefix() . 'customer_admins WHERE staff_id = ' . (int)$staffId . ')';
        }

        $data['total_customers']   = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'clients WHERE 1=1' . $customerJoin)->row()->c;
        $data['new_customers_30']  = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'clients WHERE datecreated >= ' . $this->db->escape($range30) . $customerJoin)->row()->c;
        $data['dormant_customers'] = $this->db->query('
            SELECT COUNT(*) c FROM ' . db_prefix() . 'clients cl
            WHERE 1=1' . str_replace('userid', 'cl.userid', $customerJoin) . '
            AND NOT EXISTS (SELECT 1 FROM ' . db_prefix() . 'tickets t WHERE t.userid = cl.userid AND t.date >= ' . $this->db->escape($range30) . ')')->row()->c;
        $data['open_tickets']    = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'tickets WHERE status IN (1,2)' . $ticketWhere)->row()->c;
        $data['overdue_tickets'] = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'tickets WHERE status IN (1,2) AND date < DATE_SUB(NOW(), INTERVAL 3 DAY)' . $ticketWhere)->row()->c;
        $data['closed_30']       = $this->db->query('SELECT COUNT(*) c FROM ' . db_prefix() . 'tickets WHERE status = 5 AND lastreply >= ' . $this->db->escape($range30) . $ticketWhere)->row()->c;

        $frt = $this->db->query('
            SELECT AVG(TIMESTAMPDIFF(MINUTE, t.date, r.date)) avg_minutes
            FROM ' . db_prefix() . 'tickets t
            JOIN ' . db_prefix() . 'ticket_replies r ON r.ticketid = t.ticketid
            WHERE r.admin IS NOT NULL AND t.date >= ' . $this->db->escape($range30) . str_replace('assigned', 't.assigned', $ticketWhere) . '
            AND r.date = (SELECT MIN(r2.date) FROM ' . db_prefix() . 'ticket_replies r2 WHERE r2.ticketid = t.ticketid AND r2.admin IS NOT NULL)')->row();
        $data['avg_first_response_min'] = $frt && $frt->avg_minutes ? (int)$frt->avg_minutes : 0;

        $art = $this->db->query('
            SELECT AVG(TIMESTAMPDIFF(HOUR, date, lastreply)) avg_hours
            FROM ' . db_prefix() . 'tickets WHERE status = 5 AND lastreply >= ' . $this->db->escape($range30) . $ticketWhere)->row();
        $data['avg_resolution_hours'] = $art && $art->avg_hours ? (int)$art->avg_hours : 0;

        $data['by_status'] = $this->db->query('
            SELECT s.name, COUNT(t.ticketid) c FROM ' . db_prefix() . 'tickets_status s
            LEFT JOIN ' . db_prefix() . 'tickets t ON t.status = s.ticketstatusid AND t.date >= ' . $this->db->escape($range30) . str_replace('assigned', 't.assigned', $ticketWhere) . '
            GROUP BY s.ticketstatusid, s.name ORDER BY s.statusorder')->result_array();

        $data['by_priority'] = $this->db->query('
            SELECT p.name, COUNT(t.ticketid) c FROM ' . db_prefix() . 'tickets_priorities p
            LEFT JOIN ' . db_prefix() . 'tickets t ON t.priority = p.priorityid AND t.date >= ' . $this->db->escape($range30) . str_replace('assigned', 't.assigned', $ticketWhere) . '
            GROUP BY p.priorityid, p.name')->result_array();

        $data['by_department'] = $this->db->query('
            SELECT d.name, COUNT(t.ticketid) c FROM ' . db_prefix() . 'departments d
            LEFT JOIN ' . db_prefix() . 'tickets t ON t.department = d.departmentid AND t.date >= ' . $this->db->escape($range30) . str_replace('assigned', 't.assigned', $ticketWhere) . '
            GROUP BY d.departmentid, d.name')->result_array();

        $data['tickets_trend']   = $this->buildDailySeries('tickets', 'date', $ticketWhere);
        $data['customers_trend'] = $this->buildDailySeries('clients', 'datecreated', $customerJoin);

        $data['recent_open_tickets'] = [];
        $data['top_customers']       = [];
        $data['dormant_list']        = [];

        $data['title'] = 'Dashboard';
        $this->load->view('admin/support_dashboard/index', $data);
    }

    private function buildDailySeries($table, $dateCol, $extraWhere)
    {
        $rows = $this->db->query('
            SELECT DATE(' . $dateCol . ') d, COUNT(*) c FROM ' . db_prefix() . $table . '
            WHERE ' . $dateCol . ' >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)' . $extraWhere . '
            GROUP BY DATE(' . $dateCol . ')')->result_array();
        $lookup = [];
        foreach ($rows as $r) { $lookup[$r['d']] = (int)$r['c']; }
        $series = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = date('Y-m-d', strtotime("-$i days"));
            $series[] = ['date' => $d, 'count' => $lookup[$d] ?? 0];
        }
        return $series;
    }

    public function legacy()
    {
        close_setup_menu();
        $this->load->model('departments_model');
        $this->load->model('todo_model');
        $data['departments'] = $this->departments_model->get();

        $data['todos'] = $this->todo_model->get_todo_items(0);
        // Only show last 5 finished todo items
        $this->todo_model->setTodosLimit(5);
        $data['todos_finished']            = $this->todo_model->get_todo_items(1);
        $data['upcoming_events_next_week'] = $this->dashboard_model->get_upcoming_events_next_week();
        $data['upcoming_events']           = $this->dashboard_model->get_upcoming_events();
        $data['title']                     = _l('dashboard_string');

        $this->load->model('contracts_model');
        $data['expiringContracts'] = $this->contracts_model->get_contracts_about_to_expire(get_staff_user_id());

        $this->load->model('currencies_model');
        $data['currencies']    = $this->currencies_model->get();
        $data['base_currency'] = $this->currencies_model->get_base_currency();
        $data['activity_log']  = $this->misc_model->get_activity_log();
        // Tickets charts
        $tickets_awaiting_reply_by_status     = $this->dashboard_model->tickets_awaiting_reply_by_status();
        $tickets_awaiting_reply_by_department = $this->dashboard_model->tickets_awaiting_reply_by_department();

        $data['tickets_reply_by_status']              = json_encode($tickets_awaiting_reply_by_status);
        $data['tickets_awaiting_reply_by_department'] = json_encode($tickets_awaiting_reply_by_department);

        $data['tickets_reply_by_status_no_json']              = $tickets_awaiting_reply_by_status;
        $data['tickets_awaiting_reply_by_department_no_json'] = $tickets_awaiting_reply_by_department;

        $data['projects_status_stats'] = json_encode($this->dashboard_model->projects_status_stats());
        $data['leads_status_stats']    = json_encode($this->dashboard_model->leads_status_stats());
        $data['google_ids_calendars']  = $this->misc_model->get_google_calendar_ids();
        $data['bodyclass']             = 'dashboard invoices-total-manual';
        $this->load->model('announcements_model');
        $data['staff_announcements']             = $this->announcements_model->get();
        $data['total_undismissed_announcements'] = $this->announcements_model->get_total_undismissed_announcements();

        $this->load->model('projects_model');
        $data['projects_activity'] = $this->projects_model->get_activity('', hooks()->apply_filters('projects_activity_dashboard_limit', 20));
        add_calendar_assets();
        $this->load->model('utilities_model');
        $this->load->model('estimates_model');
        $data['estimate_statuses'] = $this->estimates_model->get_statuses();

        $this->load->model('proposals_model');
        $data['proposal_statuses'] = $this->proposals_model->get_statuses();

        $wps_currency = 'undefined';
        if (is_using_multiple_currencies()) {
            $wps_currency = $data['base_currency']->id;
        }
        $data['weekly_payment_stats'] = json_encode($this->dashboard_model->get_weekly_payments_statistics($wps_currency));

        $data['dashboard'] = true;

        $data['user_dashboard_visibility'] = get_staff_meta(get_staff_user_id(), 'dashboard_widgets_visibility');

        if (! $data['user_dashboard_visibility']) {
            $data['user_dashboard_visibility'] = [];
        } else {
            $data['user_dashboard_visibility'] = unserialize($data['user_dashboard_visibility']);
        }
        $data['user_dashboard_visibility'] = json_encode($data['user_dashboard_visibility']);

        $data['tickets_report'] = [];
        if (is_admin()) {
            $data['tickets_report'] = (new app\services\TicketsReportByStaff())->filterBy('this_month');
        }

        $data = hooks()->apply_filters('before_dashboard_render', $data);
        $this->load->view('admin/dashboard/dashboard', $data);
    }

    // Chart weekly payments statistics on home page / ajax
    public function weekly_payments_statistics($currency)
    {
        if ($this->input->is_ajax_request()) {
            echo json_encode($this->dashboard_model->get_weekly_payments_statistics($currency));

            exit();
        }
    }

    // Chart monthly payments statistics on home page / ajax
    public function monthly_payments_statistics($currency)
    {
        if ($this->input->is_ajax_request()) {
            echo json_encode($this->dashboard_model->get_monthly_payments_statistics($currency));

            exit();
        }
    }

    public function ticket_widget($type)
    {
        $data['tickets_report'] = (new app\services\TicketsReportByStaff())->filterBy($type);
        $this->load->view('admin/dashboard/widgets/tickets_report_table', $data);
    }
}
