<?php

if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Point_model extends CI_Model {

    public function __constructor() {
        $this->read_replica = $this->load->database('savvy_replica', TRUE);
        parrent::__constructor();
    }

    public function getSystemicPointsSummary()
    {
        $query_arr = [
            'last_24_hrs' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()",
            'last_7_days' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN '" . date('Y-m-d', strtotime('-6 days')) . "' AND '" . date('Y-m-d') . "'",
            'last_14_days' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN '" . date('Y-m-d', strtotime('-13 days')) . "' AND '" . date('Y-m-d') . "'",
            'last_30_days' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN '" . date('Y-m-d', strtotime('-29 days')) . "' AND '" . date('Y-m-d') . "'",
            'all_time' => "",
        ];

        $countries = [];
        
        foreach ($query_arr as $key => $value) {
            $sql = "SELECT COUNT(*) AS total_assigned_times,
            CASE WHEN m.country IS NULL THEN 'Unknown' ELSE m.country END AS location 
            FROM members_points AS mp
            LEFT JOIN members AS m ON m.id = mp.member_id
            WHERE mp.points > 0 $value
            GROUP BY CASE WHEN m.country IS NULL THEN 'Unknown' ELSE m.country END
            ORDER BY total_assigned_times DESC";

            $query = $this->db->query($sql);

            $countries[$key] = $query->result_array();
        }
        
        foreach ($countries['all_time'] as &$country) {
            foreach ($countries['last_30_days'] as $tempCountry) {
                if ($tempCountry['location'] === $country['location']) {
                    $country['last_30_days'] = $tempCountry['total_assigned_times'];
                    break;
                }
            }
            foreach ($countries['last_14_days'] as $tempCountry) {
                if ($tempCountry['location'] === $country['location']) {
                    $country['last_14_days'] = $tempCountry['total_assigned_times'];
                    break;
                }
            }
            foreach ($countries['last_7_days'] as $tempCountry) {
                if ($tempCountry['location'] === $country['location']) {
                    $country['last_7_days'] = $tempCountry['total_assigned_times'];
                    break;
                }
            }
            foreach ($countries['last_24_hrs'] as $tempCountry) {
                if ($tempCountry['location'] === $country['location']) {
                    $country['last_24_hrs'] = $tempCountry['total_assigned_times'];
                    break;
                }
            }
            
            $country['last_30_days'] = $country['last_30_days'] ?? 0;
            $country['last_14_days'] = $country['last_14_days'] ?? 0;
            $country['last_7_days'] = $country['last_7_days'] ?? 0;
            $country['last_24_hrs'] = $country['last_24_hrs'] ?? 0;
        }
    
        return $countries['all_time'];
    }

    public function getSystemicPointsDatatables($postData, $mode = 'search')
    {
        ## Save param for CSV
        $this->load->library('session');
        $this->session->set_userdata("SPR_PARAM", $postData);
        ## Read value
;       $from = DateTime::createFromFormat('m/d/Y', '1/1/1970');
        $to = (new DateTime());
        $date_range = $postData['date_range'] ?? '';
        if (!empty($date_range)) {
            $date_range_part = array_map('trim', explode('-', $date_range));
            $from = DateTime::createFromFormat('m/d/Y', $date_range_part[0]);
            $to = DateTime::createFromFormat('m/d/Y', $date_range_part[1]);
        }
        $draw = $postData['draw'] ?? 0;
        $start = $postData['start'];
        $rowperpage = $postData['length']; // Rows display per page
        $columnIndex = $postData['order'][0]['column'] ?? 0; // Column index
        $columnName = $postData['columns'][$columnIndex]['data'] ?? ''; // Column name
        $columnSortOrder = $postData['order'][0]['dir']; // asc or desc

        $searchValue = $postData['email'] ?? ''; // Search value
        $pointKey = $postData['point_key'] ?? ''; // Point key
        $pointValue = $postData['point_value'] ?? ''; // Point key
        
        $where = " m.id>0 AND m.email!='' AND mp.points > 0";
        $where .= " AND date(m.added) >='" . $from->format('Y-m-d') . "' and date(m.added)<='" . $to->format('Y-m-d') . "' ";
        
        if (!empty($postData['country_filter'])) {
            if (strtolower($postData['country_filter']) === 'unknown') {
                $where .= " AND (m.country = '' or m.country IS NULL) ";
            } else {
                $where .= " AND m.country = '" . pg_escape_string($postData['country_filter']) . "' ";
            }
        }
        
        if (!empty($searchValue)) {
            $searchStr = pg_escape_string($searchValue);
            $where .= " AND m.email ILIKE '%" . $searchStr . "%' ";
        }

        if (!empty($pointKey)) {
            $searchStr = pg_escape_string($pointKey);
            $where .= " AND mp.point_key = '" . $searchStr . "' ";
        }

        if (!empty($pointValue)) {
            $searchStr = pg_escape_string($pointValue);
            $where .= " AND mp.points = '" . $searchStr . "' ";
        }

        $map_order_by = [
            'allocation_date' => 'mp.added',
            'sign_up_date' => 'm.added',
            'country' => 'm.country',
            'member_id' => 'mp.member_id',
        ];
        
        if (isset($map_order_by[$columnName])) {
            $order_by = ' ORDER BY ' . $map_order_by[$columnName] . ' ' . $columnSortOrder;
        } else {
            $order_by = ' ORDER BY ' . $map_order_by['sign_up_date'] . ' DESC';
        }

        $limit = $mode === 'search' ? " LIMIT $rowperpage OFFSET $start" : '';

        $mysql = "SELECT
                    to_char(mp.added, 'MM/DD/YYYY HH24:MI:SS') as allocation_date,
                    to_char(m.added, 'MM/DD/YYYY HH24:MI:SS') as sign_up_date,
                    CASE WHEN m.country IS NOT NULL THEN m.country ELSE 'Unknown' END as country, 
                    mp.member_id, m.email, mp.point_key, ps.name, mp.points
                    FROM members_points mp
                    INNER JOIN members m on m.id = mp.member_id INNER JOIN points_settings ps on ps.point_key = mp.point_key
                    WHERE $where
                    $order_by $limit";
        # Response Search or CSV
        if ($mode === 'export_csv') {
            $report = $this->db->query($mysql);
            $this->exportToCSV($report, 'systemic-points-report-' . date("Y-m-d") . '.csv');
            exit;
        }
        $report = $this->db->query($mysql)->result_array();
        $mysql_count = "SELECT COUNT(*)
                        FROM members_points mp
                        INNER JOIN members m on m.id = mp.member_id INNER JOIN points_settings ps on ps.point_key = mp.point_key
                        WHERE $where;";
        $totalRecords = $this->db->query($mysql_count)->row_array()['count'];
        $totalRecordwithFilter = $totalRecords;
        $response = array(
            "draw" => intval($draw),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalRecordwithFilter,
            "aaData" => $report
        );
        return $response;
    }

    public function exportToCSV($data, $file_name)
    {
        set_time_limit(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$file_name);
        header('Cache-Control: max-age=0');
        if (ob_get_contents())
            ob_end_clean();
        $fp = fopen('php://output', 'w');
        $i = 0;
        while ($row = $data->unbuffered_row()) {
            // header
            if ($i == 0) {
                fputcsv($fp, array_keys((array)$row));
            }
            // records
            fputcsv($fp, (array)$row);
            $i++;
        }
        fclose($fp);
        exit;
    }

}

?>