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

class Dashboard extends Admin_Controller
{
    public function __construct()
    {
        parent::__construct();
    }

    public function index()
    {
        $initial_module = 'overview';
        $brandName = config_item('company_name') ?: (config_item('website_name') ?: (config_item('copyright_name') ?: 'ERP'));
        $data = array(
            'title' => 'ERP Dashboard | ' . $brandName,
            'page_title' => 'ERP Dashboard',
            'active_page' => 'dashboard',
            'dashboard_modules' => $this->dashboard_modules(),
            'dashboard_module_url' => base_url('admin/dashboard/module'),
            'dashboard_initial_module' => $initial_module,
            'dashboard_initial_html' => '',
            'dashboard_user_id' => (int) $this->session->userdata('user_id'),
        );

        $this->render('pages/dashboard', $data);
    }

    public function module($module = 'overview')
    {
        if (!$this->input->is_ajax_request()) {
            show_404();
        }

        $modules = $this->dashboard_modules();
        if (empty($modules[$module])) {
            show_404();
        }

        $payload = $this->build_dashboard_module($module);
        $html = $this->load->view('admin/dashboard/modules/' . $module, $payload, true);

        $this->output
            ->set_content_type('application/json')
            ->set_output(json_encode(array(
                'status' => 'success',
                'module' => $module,
                'html' => $html,
            )));
    }

    public function reset_cache()
    {
        $redirectTo = !empty($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : site_url('login');

        $this->load->driver('cache', array('adapter' => 'file'));

        if (isset($this->cache) && method_exists($this->cache, 'clean')) {
            $this->cache->clean();
        }

        $this->clear_cache_directory(APPPATH . 'cache/');
        $this->clear_all_sessions();

        redirect($redirectTo);
    }

    private function clear_cache_directory($cachePath)
    {
        if (!is_dir($cachePath)) {
            return;
        }

        $this->load->helper('file');
        delete_files($cachePath, true, true);
    }

    private function clear_all_sessions()
    {
        $sessionDriver = config_item('sess_driver');
        if (empty($sessionDriver) && config_item('sess_use_database')) {
            $sessionDriver = 'database';
        }

        if ($sessionDriver === 'database') {
            $sessionTable = config_item('sess_save_path') ?: config_item('sess_table_name');
            if (!empty($sessionTable) && $this->db->table_exists($sessionTable)) {
                $this->db->empty_table($sessionTable);
            }
        } elseif ($sessionDriver === 'files') {
            $sessionPath = config_item('sess_save_path') ?: session_save_path();
            if (!empty($sessionPath) && is_dir($sessionPath)) {
                foreach (glob(rtrim($sessionPath, '/\\') . DIRECTORY_SEPARATOR . 'sess_*') as $sessionFile) {
                    if (is_file($sessionFile)) {
                        @unlink($sessionFile);
                    }
                }
            }
        }

        if (isset($this->session) && method_exists($this->session, 'sess_destroy')) {
            $this->session->sess_destroy();
        }
    }

    public function change_report($action = null, $type = null)
    {
        $allowed_types = array('invoice', 'estimate', 'credit_note', 'proposal');
        $allowed_actions = array('show', 'hide');

        if (!in_array($type, $allowed_types, true) || !in_array($action, $allowed_actions, true)) {
            show_404();
        }

        $value = $action === 'hide' ? 'none' : 'block';
        $config_key = $type . '_state';
        $redirect_routes = array(
            'invoice' => 'admin/invoice/manage_invoice',
            'estimate' => 'admin/estimates',
            'credit_note' => 'admin/credit_note',
            'proposal' => 'admin/proposals',
        );

        $exists = $this->db->where('config_key', $config_key)->get('tbl_config')->row();
        if (!empty($exists)) {
            $this->db->where('config_key', $config_key)->update('tbl_config', array('value' => $value));
        } else {
            $this->db->insert('tbl_config', array(
                'config_key' => $config_key,
                'value' => $value,
            ));
        }
        $this->config->set_item($config_key, $value);

        set_message('success', lang('saved_successfully'));
        $referer = !empty($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : '';
        if (!empty($referer) && strpos($referer, 'admin/dashboard/change_report') === false) {
            redirect($referer);
        }
        redirect($redirect_routes[$type]);
    }

    private function dashboard_modules()
    {
        return array(
            'overview' => array('label' => 'Overview', 'icon' => 'layout-dashboard'),
            'workspace' => array('label' => 'My Workspace', 'icon' => 'check-check'),
            'overdue' => array('label' => 'Overdue', 'icon' => 'alarm-clock-check'),
            'reports' => array('label' => 'Finance Reports', 'icon' => 'file-bar-chart-2'),
            'activity' => array('label' => 'Recent Activity', 'icon' => 'history'),
            'sales' => array('label' => 'Sales & Transactions', 'icon' => 'line-chart'),
            'delivery' => array('label' => 'Projects & Tasks', 'icon' => 'kanban-square'),
            'crm' => array('label' => 'Leads, Opportunities & Tickets', 'icon' => 'headset'),
        );
    }

    private function build_dashboard_module($module)
    {
        switch ($module) {
            case 'workspace':
                return array(
                    'workspace' => $this->cached_dashboard_snapshot(
                        'dashboard_workspace_snapshot_v1_user_' . (int) $this->session->userdata('user_id'),
                        function () {
                            return $this->workspace_snapshot();
                        },
                        60
                    ),
                    'currency_symbol' => default_currency(),
                );
            case 'overdue':
                return array(
                    'overdue' => $this->cached_dashboard_snapshot(
                        'dashboard_overdue_snapshot_v1',
                        function () {
                            return $this->overdue_snapshot();
                        },
                        60
                    ),
                    'currency_symbol' => default_currency(),
                );
            case 'reports':
                return array(
                    'finance' => $this->cached_dashboard_snapshot(
                        'dashboard_finance_snapshot_v1',
                        function () {
                            return $this->finance_snapshot();
                        },
                        120
                    ),
                    'currency_symbol' => default_currency(),
                );
            case 'activity':
                return array(
                    'activity' => $this->cached_dashboard_snapshot(
                        'dashboard_activity_snapshot_v1',
                        function () {
                            return $this->activity_snapshot();
                        },
                        30
                    ),
                    'currency_symbol' => default_currency(),
                );
            case 'sales':
                return array(
                    'finance' => $this->cached_dashboard_snapshot(
                        'dashboard_finance_snapshot_v1',
                        function () {
                            return $this->finance_snapshot();
                        },
                        120
                    ),
                    'crm' => $this->cached_dashboard_snapshot(
                        'dashboard_crm_snapshot_v1',
                        function () {
                            return $this->crm_snapshot();
                        },
                        120
                    ),
                    'currency_symbol' => default_currency(),
                );
            case 'delivery':
                return array(
                    'delivery' => $this->cached_dashboard_snapshot(
                        'dashboard_delivery_snapshot_v1',
                        function () {
                            return $this->delivery_snapshot();
                        },
                        120
                    ),
                    'currency_symbol' => default_currency(),
                );
            case 'crm':
                return array(
                    'crm' => $this->cached_dashboard_snapshot(
                        'dashboard_crm_snapshot_v1',
                        function () {
                            return $this->crm_snapshot();
                        },
                        120
                    ),
                    'currency_symbol' => default_currency(),
                );
            case 'overview':
            default:
                $finance = $this->cached_dashboard_snapshot(
                    'dashboard_finance_snapshot_v1',
                    function () {
                        return $this->finance_snapshot();
                    },
                    120
                );
                $delivery = $this->cached_dashboard_snapshot(
                    'dashboard_delivery_snapshot_v1',
                    function () {
                        return $this->delivery_snapshot();
                    },
                    120
                );
                $crm = $this->cached_dashboard_snapshot(
                    'dashboard_crm_snapshot_v1',
                    function () {
                        return $this->crm_snapshot();
                    },
                    120
                );
                return array(
                    'finance' => $finance,
                    'delivery' => $delivery,
                    'crm' => $crm,
                    'activity' => $this->cached_dashboard_snapshot(
                        'dashboard_activity_snapshot_v1',
                        function () {
                            return $this->activity_snapshot();
                        },
                        30
                    ),
                    'overdue' => $this->cached_dashboard_snapshot(
                        'dashboard_overdue_snapshot_v1',
                        function () {
                            return $this->overdue_snapshot();
                        },
                        60
                    ),
                    'currency_symbol' => default_currency(),
                );
        }
    }

    private function cached_dashboard_snapshot($cacheKey, callable $resolver, $ttl = 60)
    {
        $cached = $this->cache->get($cacheKey);
        if ($cached !== FALSE) {
            return $cached;
        }

        $payload = $resolver();
        $this->cache->save($cacheKey, $payload, $ttl);

        return $payload;
    }

    private function finance_snapshot()
    {
        $invoiceTotals = $this->db->query("SELECT
            COALESCE(SUM(item_totals.subtotal), 0) AS subtotal,
            COALESCE(SUM(item_totals.taxes), 0) AS taxes,
            COALESCE(SUM(tbl_invoices.discount_total), 0) AS discounts,
            COALESCE(SUM(tbl_invoices.adjustment), 0) AS adjustments
            FROM tbl_invoices
            LEFT JOIN (
                SELECT
                    invoices_id,
                    SUM(total_cost) AS subtotal,
                    SUM(item_tax_total) AS taxes
                FROM tbl_items
                GROUP BY invoices_id
            ) AS item_totals ON item_totals.invoices_id = tbl_invoices.invoices_id
            WHERE tbl_invoices.status != 'cancelled'
              AND tbl_invoices.status != 'draft'
              AND tbl_invoices.inv_deleted = 'No'")->row();

        $invoiceTotal = 0;
        if (!empty($invoiceTotals)) {
            $invoiceTotal = (float) $invoiceTotals->subtotal - (float) $invoiceTotals->discounts + (float) $invoiceTotals->taxes + (float) $invoiceTotals->adjustments;
        }

        $paidTotal = (float) $this->db->query("SELECT COALESCE(SUM(tbl_payments.amount), 0) AS total
            FROM tbl_invoices
            LEFT JOIN tbl_payments ON tbl_payments.invoices_id = tbl_invoices.invoices_id
            WHERE tbl_invoices.status != 'cancelled'
              AND tbl_invoices.status != 'draft'
              AND tbl_invoices.inv_deleted = 'No'")->row()->total;

        $transactionTotals = $this->db->query("SELECT
            COALESCE(SUM(CASE WHEN type = 'Income' THEN amount ELSE 0 END), 0) AS income_total,
            COALESCE(SUM(CASE WHEN type = 'Expense' THEN amount ELSE 0 END), 0) AS expense_total
            FROM tbl_transactions")->row();
        $incomeTotal = (float) ($transactionTotals->income_total ?? 0);
        $expenseTotal = (float) ($transactionTotals->expense_total ?? 0);

        $invoiceStatusRows = $this->db->select('status, COUNT(*) AS total', false)
            ->from('tbl_invoices')
            ->where('status !=', 'cancelled')
            ->where('status !=', 'draft')
            ->where('inv_deleted', 'No')
            ->group_by('status')
            ->get()
            ->result();

        $invoiceStatusMap = array('paid' => 0, 'pending' => 0, 'partially_paid' => 0, 'overdue' => 0);
        if (!empty($invoiceStatusRows)) {
            foreach ($invoiceStatusRows as $row) {
                $invoiceStatusMap[strtolower($row->status)] = (int) $row->total;
            }
        }

        $recentInvoices = $this->db->select('tbl_invoices.invoices_id, tbl_invoices.reference_no, tbl_invoices.due_date, tbl_invoices.invoice_date, tbl_invoices.status, tbl_client.name AS client_name')
            ->from('tbl_invoices')
            ->join('tbl_client', 'tbl_client.client_id = tbl_invoices.client_id', 'left')
            ->where('tbl_invoices.status !=', 'cancelled')
            ->where('tbl_invoices.status !=', 'draft')
            ->where('tbl_invoices.inv_deleted', 'No')
            ->order_by('tbl_invoices.invoices_id', 'DESC')
            ->limit(6)
            ->get()
            ->result();

        $recentTransactions = $this->db->select('transactions_id, transaction_prefix, name, date, amount, type, reference, status')
            ->from('tbl_transactions')
            ->order_by('transactions_id', 'DESC')
            ->limit(8)
            ->get()
            ->result();

        $expenseCategories = $this->db->select('COALESCE(tbl_transactions.name, "General") AS label, SUM(tbl_transactions.amount) AS total', false)
            ->from('tbl_transactions')
            ->where('tbl_transactions.type', 'Expense')
            ->group_by('tbl_transactions.name')
            ->order_by('total', 'DESC')
            ->limit(6)
            ->get()
            ->result();

        $methodRows = $this->db->select('status AS label, COUNT(*) AS total', false)
            ->from('tbl_transactions')
            ->group_by('status')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $monthBuckets = $this->month_buckets(6);
        $monthlyTransactions = $this->db->query("SELECT DATE_FORMAT(date, '%Y-%m') AS bucket,
            SUM(CASE WHEN type = 'Income' THEN amount ELSE 0 END) AS income_total,
            SUM(CASE WHEN type = 'Expense' THEN amount ELSE 0 END) AS expense_total
            FROM tbl_transactions
            WHERE date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m-01')
            GROUP BY DATE_FORMAT(date, '%Y-%m')
            ORDER BY DATE_FORMAT(date, '%Y-%m') ASC")->result();
        if (!empty($monthlyTransactions)) {
            foreach ($monthlyTransactions as $row) {
                if (isset($monthBuckets[$row->bucket])) {
                    $monthBuckets[$row->bucket]['income'] = (float) $row->income_total;
                    $monthBuckets[$row->bucket]['expense'] = (float) $row->expense_total;
                }
            }
        }

        $labels = array();
        $incomeSeries = array();
        $expenseSeries = array();
        foreach ($monthBuckets as $bucket) {
            $labels[] = $bucket['label'];
            $incomeSeries[] = round($bucket['income'], 2);
            $expenseSeries[] = round($bucket['expense'], 2);
        }

        return array(
            'invoice_total' => $invoiceTotal,
            'paid_total' => $paidTotal,
            'outstanding_total' => max(0, $invoiceTotal - $paidTotal),
            'income_total' => $incomeTotal,
            'expense_total' => $expenseTotal,
            'net_cash' => $incomeTotal - $expenseTotal,
            'invoice_status_map' => $invoiceStatusMap,
            'recent_invoices' => $recentInvoices,
            'recent_transactions' => $recentTransactions,
            'finance_series' => array(
                array('name' => 'Income', 'data' => $incomeSeries, 'categories' => $labels),
                array('name' => 'Expense', 'data' => $expenseSeries),
            ),
            'invoice_status_series' => array(
                array(
                    'name' => 'Invoices',
                    'data' => array_values($invoiceStatusMap),
                    'categories' => array('Paid', 'Pending', 'Partially Paid', 'Overdue'),
                ),
            ),
            'expense_category_series' => array(
                array(
                    'name' => 'Expenses',
                    'data' => array_map(function ($row) {
                        return round((float) $row->total, 2);
                    }, $expenseCategories),
                    'categories' => array_map(function ($row) {
                        return $row->label;
                    }, $expenseCategories),
                ),
            ),
            'transaction_status_series' => array(
                array(
                    'name' => 'Transactions',
                    'data' => array_map(function ($row) {
                        return (int) $row->total;
                    }, $methodRows),
                    'categories' => array_map(function ($row) {
                        return !empty($row->label) ? ucfirst($row->label) : 'Unspecified';
                    }, $methodRows),
                ),
            ),
        );
    }

    private function delivery_snapshot()
    {
        $projectCounts = $this->db->query("SELECT
            COUNT(*) AS project_count,
            SUM(CASE WHEN project_status != 'completed' THEN 1 ELSE 0 END) AS active_project_count
            FROM tbl_project")->row();

        $taskCounts = $this->db->query("SELECT
            COUNT(*) AS task_count,
            SUM(CASE WHEN task_status != 'completed' THEN 1 ELSE 0 END) AS open_task_count
            FROM tbl_task")->row();

        $projectRows = $this->db->select('project_status, COUNT(*) AS total', false)
            ->from('tbl_project')
            ->group_by('project_status')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $projectStatusMap = array();
        foreach ($projectRows as $row) {
            $projectStatusMap[!empty($row->project_status) ? ucfirst($row->project_status) : 'Undefined'] = (int) $row->total;
        }

        $taskRows = $this->db->select('task_status, COUNT(*) AS total', false)
            ->from('tbl_task')
            ->group_by('task_status')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $taskStatusMap = array();
        foreach ($taskRows as $row) {
            $taskStatusMap[!empty($row->task_status) ? ucwords(str_replace('_', ' ', $row->task_status)) : 'Undefined'] = (int) $row->total;
        }

        $recentProjects = $this->db->query("SELECT
            tbl_project.project_id,
            tbl_project.project_name,
            tbl_project.project_status,
            tbl_project.start_date,
            tbl_project.end_date,
            COALESCE(task_totals.total_tasks, 0) AS total_tasks,
            COALESCE(task_totals.completed_tasks, 0) AS completed_tasks
            FROM tbl_project
            LEFT JOIN (
                SELECT
                    project_id,
                    COUNT(*) AS total_tasks,
                    SUM(CASE WHEN task_status = 'completed' THEN 1 ELSE 0 END) AS completed_tasks
                FROM tbl_task
                GROUP BY project_id
            ) AS task_totals ON task_totals.project_id = tbl_project.project_id
            ORDER BY tbl_project.project_id DESC
            LIMIT 6")->result();

        $recentTasks = $this->db->select('tbl_task.task_id, tbl_task.task_name, tbl_task.task_status, tbl_task.task_progress, tbl_task.due_date, tbl_project.project_name')
            ->from('tbl_task')
            ->join('tbl_project', 'tbl_project.project_id = tbl_task.project_id', 'left')
            ->order_by('tbl_task.task_id', 'DESC')
            ->limit(8)
            ->get()
            ->result();

        $departmentRows = $this->db->select('COALESCE(tbl_departments.deptname, "Unassigned") AS deptname, COUNT(tbl_account_details.user_id) AS total', false)
            ->from('tbl_account_details')
            ->join('tbl_users', 'tbl_users.user_id = tbl_account_details.user_id', 'left')
            ->join('tbl_designations', 'tbl_designations.designations_id = tbl_account_details.designations_id', 'left')
            ->join('tbl_departments', 'tbl_departments.departments_id = tbl_designations.departments_id', 'left')
            ->where('tbl_users.role_id !=', 2)
            ->group_by('deptname')
            ->order_by('total', 'DESC')
            ->limit(8)
            ->get()
            ->result();

        return array(
            'project_count' => (int) ($projectCounts->project_count ?? 0),
            'task_count' => (int) ($taskCounts->task_count ?? 0),
            'active_project_count' => (int) ($projectCounts->active_project_count ?? 0),
            'open_task_count' => (int) ($taskCounts->open_task_count ?? 0),
            'recent_projects' => $recentProjects,
            'recent_tasks' => $recentTasks,
            'project_status_series' => array(
                array(
                    'name' => 'Projects',
                    'data' => array_values($projectStatusMap),
                    'categories' => array_keys($projectStatusMap),
                ),
            ),
            'task_status_series' => array(
                array(
                    'name' => 'Tasks',
                    'data' => array_values($taskStatusMap),
                    'categories' => array_keys($taskStatusMap),
                ),
            ),
            'department_series' => array(
                array(
                    'name' => 'Workforce',
                    'data' => array_map(function ($row) {
                        return (int) $row->total;
                    }, $departmentRows),
                    'categories' => array_map(function ($row) {
                        return $row->deptname;
                    }, $departmentRows),
                ),
            ),
        );
    }

    private function crm_snapshot()
    {
        $crmCounts = $this->db->query("SELECT
            (SELECT COUNT(*) FROM tbl_leads) AS lead_count,
            (SELECT COUNT(*) FROM tbl_opportunities) AS opportunity_count,
            (SELECT COUNT(*) FROM tbl_tickets) AS ticket_count,
            (SELECT COUNT(*) FROM tbl_tickets WHERE status != 'closed' AND status != 'resolved') AS open_ticket_count,
            (SELECT COALESCE(SUM(expected_revenue), 0) FROM tbl_opportunities) AS pipeline_total")->row();

        $leadRows = $this->db->select('COALESCE(tbl_lead_status.lead_status, "Undefined") AS label, COUNT(tbl_leads.leads_id) AS total', false)
            ->from('tbl_leads')
            ->join('tbl_lead_status', 'tbl_lead_status.lead_status_id = tbl_leads.lead_status_id', 'left')
            ->group_by('label')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $sourceRows = $this->db->select('COALESCE(tbl_lead_source.lead_source, "Undefined") AS label, COUNT(tbl_leads.leads_id) AS total', false)
            ->from('tbl_leads')
            ->join('tbl_lead_source', 'tbl_lead_source.lead_source_id = tbl_leads.lead_source_id', 'left')
            ->group_by('label')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $oppRows = $this->db->select('COALESCE(stages, "Undefined") AS label, SUM(expected_revenue) AS total', false)
            ->from('tbl_opportunities')
            ->group_by('label')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $ticketRows = $this->db->select('COALESCE(status, "Undefined") AS label, COUNT(tickets_id) AS total', false)
            ->from('tbl_tickets')
            ->group_by('label')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $priorityRows = $this->db->select('COALESCE(priority, "Undefined") AS label, COUNT(tickets_id) AS total', false)
            ->from('tbl_tickets')
            ->group_by('label')
            ->order_by('total', 'DESC')
            ->get()
            ->result();

        $recentLeads = $this->db->select('tbl_leads.leads_id, tbl_leads.lead_name, tbl_lead_status.lead_status, tbl_lead_source.lead_source, tbl_leads.email')
            ->from('tbl_leads')
            ->join('tbl_lead_status', 'tbl_lead_status.lead_status_id = tbl_leads.lead_status_id', 'left')
            ->join('tbl_lead_source', 'tbl_lead_source.lead_source_id = tbl_leads.lead_source_id', 'left')
            ->order_by('tbl_leads.leads_id', 'DESC')
            ->limit(6)
            ->get()
            ->result();

        $recentOpportunities = $this->db->select('opportunities_id, opportunity_name, stages, expected_revenue, close_date')
            ->from('tbl_opportunities')
            ->order_by('opportunities_id', 'DESC')
            ->limit(6)
            ->get()
            ->result();

        $recentTickets = $this->db->select('tickets_id, ticket_code, subject, priority, status, reporter')
            ->from('tbl_tickets')
            ->order_by('tickets_id', 'DESC')
            ->limit(8)
            ->get()
            ->result();

        return array(
            'lead_count' => (int) ($crmCounts->lead_count ?? 0),
            'opportunity_count' => (int) ($crmCounts->opportunity_count ?? 0),
            'ticket_count' => (int) ($crmCounts->ticket_count ?? 0),
            'open_ticket_count' => (int) ($crmCounts->open_ticket_count ?? 0),
            'pipeline_total' => (float) ($crmCounts->pipeline_total ?? 0),
            'recent_leads' => $recentLeads,
            'recent_opportunities' => $recentOpportunities,
            'recent_tickets' => $recentTickets,
            'lead_stage_series' => array(
                array(
                    'name' => 'Lead Status',
                    'data' => array_map(function ($row) {
                        return (int) $row->total;
                    }, $leadRows),
                    'categories' => array_map(function ($row) {
                        return $row->label;
                    }, $leadRows),
                ),
            ),
            'lead_source_series' => array(
                array(
                    'name' => 'Lead Source',
                    'data' => array_map(function ($row) {
                        return (int) $row->total;
                    }, $sourceRows),
                    'categories' => array_map(function ($row) {
                        return $row->label;
                    }, $sourceRows),
                ),
            ),
            'pipeline_series' => array(
                array(
                    'name' => 'Pipeline',
                    'data' => array_map(function ($row) {
                        return round((float) $row->total, 2);
                    }, $oppRows),
                    'categories' => array_map(function ($row) {
                        return ucfirst($row->label);
                    }, $oppRows),
                ),
            ),
            'ticket_status_series' => array(
                array(
                    'name' => 'Tickets',
                    'data' => array_map(function ($row) {
                        return (int) $row->total;
                    }, $ticketRows),
                    'categories' => array_map(function ($row) {
                        return ucfirst($row->label);
                    }, $ticketRows),
                ),
            ),
            'ticket_priority_series' => array(
                array(
                    'name' => 'Priority',
                    'data' => array_map(function ($row) {
                        return (int) $row->total;
                    }, $priorityRows),
                    'categories' => array_map(function ($row) {
                        return ucfirst($row->label);
                    }, $priorityRows),
                ),
            ),
        );
    }

    private function workspace_snapshot()
    {
        $userId = (int) $this->session->userdata('user_id');

        $myTasks = $this->db->select('tbl_task.task_id, tbl_task.task_name, tbl_task.task_status, tbl_task.task_progress, tbl_task.due_date, tbl_project.project_name')
            ->from('tbl_task')
            ->join('tbl_project', 'tbl_project.project_id = tbl_task.project_id', 'left')
            ->where('tbl_task.task_status !=', 'completed')
            ->group_start()
            ->like('tbl_task.permission', '"' . $userId . '"')
            ->or_where('tbl_task.permission', 'all')
            ->group_end()
            ->order_by('tbl_task.due_date', 'ASC')
            ->limit(6)
            ->get()
            ->result();

        $myProjects = $this->db->select('project_id, project_name, project_status, end_date')
            ->from('tbl_project')
            ->where('project_status !=', 'completed')
            ->group_start()
            ->like('permission', '"' . $userId . '"')
            ->or_where('permission', 'all')
            ->group_end()
            ->order_by('end_date', 'ASC')
            ->limit(4)
            ->get()
            ->result();

        $myReminders = $this->db->select('reminder_id, module, module_id, description, date')
            ->from('tbl_reminders')
            ->where('created_by', $userId)
            ->order_by('date', 'ASC')
            ->limit(5)
            ->get()
            ->result();

        $myTodos = $this->db->select('todo_id, title, status, order')
            ->from('tbl_todo')
            ->where('user_id', $userId)
            ->order_by('order', 'ASC')
            ->limit(6)
            ->get()
            ->result();

        return array(
            'my_tasks' => $myTasks,
            'my_projects' => $myProjects,
            'my_reminders' => $myReminders,
            'my_todos' => $myTodos,
        );
    }

    private function overdue_snapshot()
    {
        $today = date('Y-m-d');

        $tasks = $this->db->select('tbl_task.task_id, tbl_task.task_name, tbl_task.due_date, tbl_task.task_status, tbl_project.project_name')
            ->from('tbl_task')
            ->join('tbl_project', 'tbl_project.project_id = tbl_task.project_id', 'left')
            ->where('tbl_task.task_status !=', 'completed')
            ->where('tbl_task.due_date <', $today)
            ->order_by('tbl_task.due_date', 'ASC')
            ->limit(10)
            ->get()
            ->result();

        $projects = $this->db->select('project_id, project_name, project_status, end_date')
            ->from('tbl_project')
            ->where('project_status !=', 'completed')
            ->where('end_date <', $today)
            ->order_by('end_date', 'ASC')
            ->limit(10)
            ->get()
            ->result();

        $invoices = $this->db->select('tbl_invoices.invoices_id, tbl_invoices.reference_no, tbl_invoices.due_date, tbl_invoices.status, tbl_client.name AS client_name')
            ->from('tbl_invoices')
            ->join('tbl_client', 'tbl_client.client_id = tbl_invoices.client_id', 'left')
            ->where('tbl_invoices.status !=', 'paid')
            ->where('tbl_invoices.status !=', 'cancelled')
            ->where('tbl_invoices.status !=', 'draft')
            ->where('tbl_invoices.inv_deleted', 'No')
            ->where('tbl_invoices.due_date <', $today)
            ->order_by('tbl_invoices.due_date', 'ASC')
            ->limit(10)
            ->get()
            ->result();

        $estimates = $this->db->select('tbl_estimates.estimates_id, tbl_estimates.reference_no, tbl_estimates.due_date, tbl_estimates.status, tbl_client.name AS client_name')
            ->from('tbl_estimates')
            ->join('tbl_client', 'tbl_client.client_id = tbl_estimates.client_id', 'left')
            ->where('tbl_estimates.status !=', 'accepted')
            ->where('tbl_estimates.status !=', 'declined')
            ->where('tbl_estimates.due_date <', $today)
            ->order_by('tbl_estimates.due_date', 'ASC')
            ->limit(10)
            ->get()
            ->result();

        $tickets = $this->db->select('tickets_id, ticket_code, subject, status, priority')
            ->from('tbl_tickets')
            ->where('status !=', 'closed')
            ->where('status !=', 'resolved')
            ->order_by('tickets_id', 'ASC')
            ->limit(10)
            ->get()
            ->result();

        return array(
            'tasks' => $tasks,
            'projects' => $projects,
            'invoices' => $invoices,
            'estimates' => $estimates,
            'tickets' => $tickets,
        );
    }

    private function activity_snapshot()
    {
        $rows = $this->db->select('tbl_activities.activities_id, tbl_activities.activity_date, tbl_activities.activity, tbl_activities.value1, tbl_activities.module, tbl_activities.link, tbl_account_details.fullname')
            ->from('tbl_activities')
            ->join('tbl_account_details', 'tbl_account_details.user_id = tbl_activities.user', 'left')
            ->order_by('tbl_activities.activities_id', 'DESC')
            ->limit(12)
            ->get()
            ->result();

        return array(
            'recent_activities' => $rows,
        );
    }

    private function month_buckets($months = 6)
    {
        $buckets = array();
        $start = new DateTime('first day of this month');
        $start->modify('-' . ($months - 1) . ' months');
        for ($i = 0; $i < $months; $i++) {
            $bucketKey = $start->format('Y-m');
            $buckets[$bucketKey] = array(
                'label' => $start->format('M'),
                'income' => 0,
                'expense' => 0,
            );
            $start->modify('+1 month');
        }

        return $buckets;
    }
}
