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

class Summary_Model extends CI_Model {

	public function __construct() {

	}

	public function get_link_details($linkId, $limit, $page, $fromDate, $toDate) {

        $this->db->select('LinkID, IPAddress, COUNT(*) AS ClickCount, City, OperatingSystem');
        $this->db->where('LinkID', $linkId);
        $this->db->where('links.UserID', $this->session->userdata('ID'));
        $this->db->where('ClickTime >=', $fromDate);
        $this->db->where('ClickTime <=', $toDate);
        $this->db->join('links', 'links.ID = click_log.LinkID', 'left');
        $this->db->group_by('IPAddress');
        $this->db->order_by('ClickCount', 'desc');
        $this->db->limit($limit, ($page - 1) * $limit);
        
        $q = $this->db->get('click_log');



        return $q->num_rows() > 0 ? $q->result() : false;

    }

    public function record_count($linkId, $fromDate, $toDate) {

        $this->db->select('COUNT(DISTINCT(`IPAddress`)) AS `Count`');
        $this->db->where('LinkID', $linkId);
        $this->db->where('links.UserID', $this->session->userdata('ID'));
        $this->db->where('ClickTime >=', $fromDate);
        $this->db->where('ClickTime <=', $toDate);
        $this->db->join('links', 'links.ID = click_log.LinkID', 'left');

        $q = $this->db->get('click_log');
        $countResult = $q->row();

        return $countResult->Count;
        
    }

    public function get_location_summary($linkId, $fromDate, $toDate) {

        $this->db->select('`City`, COUNT(`City`) AS `CityCount`');
        $this->db->where('LinkID', $linkId);
        $this->db->where('ClickTime >=', $fromDate);
        $this->db->where('ClickTime <=', $toDate);
        $this->db->group_by('City');
        $this->db->order_by('CityCount', 'desc');
        $this->db->limit(15);

        $q = $this->db->get('click_log');

        return $q->num_rows() > 0 ? $q->result() : false;
    }

    public function get_os_summary($linkId, $fromDate, $toDate) {

        $this->db->select("COUNT(IFNULL(`OperatingSystem`,'UNKNOWN')) AS `OSCount`, IFNULL(`OperatingSystem`,'UNKNOWN') AS `OperatingSystem`");
        $this->db->where('LinkID', $linkId);
        $this->db->where('ClickTime >=', $fromDate);
        $this->db->where('ClickTime <=', $toDate);
        $this->db->group_by('OperatingSystem');

        $q = $this->db->get('click_log');


        return $q->num_rows() > 0 ? $q->result() : false;
    }

    public function get_referrers_summary($linkId, $fromDate, $toDate) {

        $query = "SELECT (CASE WHEN `Referrer` LIKE '%facebook%' THEN 'FACEBOOK'
                               WHEN `Referrer` LIKE '%soccerking128%' THEN 'LOCAL'
                               WHEN `Referrer` LIKE '%iwasap%' THEN 'LOCAL'
                               WHEN `Referrer` LIKE '%darknet8%' THEN 'LOCAL'
                               WHEN `Referrer` LIKE '%android%' THEN 'ANDROID SEARCH'
                               WHEN `Referrer` LIKE '%www.google.com%' THEN 'GOOGLE'
                          END) as `ReferrersList`, COUNT(*) AS `Count`
                  FROM `click_log`
                  WHERE `LinkID` = $linkId
                  AND `ClickTime` BETWEEN '$fromDate' AND '$toDate'
                  GROUP BY (CASE WHEN `Referrer` LIKE '%facebook%' THEN 'FACEBOOK'
                               WHEN `Referrer` LIKE '%soccerking128%' THEN 'LOCAL'
                               WHEN `Referrer` LIKE '%iwasap%' THEN 'LOCAL'
                               WHEN `Referrer` LIKE '%darknet8%' THEN 'LOCAL'
                               WHEN `Referrer` LIKE '%android%' THEN 'ANDROID SEARCH'
                               WHEN `Referrer` LIKE '%www.google.com%' THEN 'GOOGLE'
                          END)";

        $q = $this->db->query($query);
        
        return $q->num_rows() > 0 ? $q->result() : false;
    }

    public function update_location() {

        // Get total number of IPAddress first
        $this->db->select('COUNT(DISTINCT(`IPAddress`)) AS `Count`');
        $this->db->where('City IS NULL');
        $this->db->where('Country IS NULL');
        $this->db->group_by('IPAddress');

        $q = $this->db->get('click_log');
        $countResult = $q->row();


        // For each 100, call API
        for($x = 0; $x < $countResult->Count; $x+=150) {

            // Query for 100 IPAddress
            $this->db->select('DISTINCT(`IPAddress`)');
            $this->db->where('City IS NULL');
            $this->db->where('Country IS NULL');
            $this->db->limit(100, $x);

            $q = $this->db->get('click_log');
            $ipResult = $q->result();

            $ipArray = array();
            foreach ($ipResult as $value) {
                $ipObject = array();
                $ipObject['query'] = $value->IPAddress;
                $ipObject['fields'] = 'city,country,query';
                
                array_push($ipArray, $ipObject);
            }

            $ipArray = json_encode($ipArray);

            // CURL start
            $curlInit = curl_init();
            curl_setopt($curlInit, CURLOPT_URL, 'http://ip-api.com/batch');
            curl_setopt($curlInit, CURLOPT_POST, true);
            curl_setopt($curlInit, CURLOPT_POSTFIELDS, $ipArray);
            curl_setopt($curlInit, CURLOPT_RETURNTRANSFER, true);   
            $curlResponse = curl_exec($curlInit);
            curl_close ($curlInit);
            $curlDecoded = json_decode($curlResponse);

            // Update into DB
            foreach ($curlDecoded as $value) {

                $update = array(
                    'City' => isset($value->city) ? $value->city : '',
                    'Country' => isset($value->country) ? $value->country : ''
                );

                $this->db->where('IPAddress', $value->query);
                $this->db->update('click_log', $update);

            }

            echo "DONE";exit;
        }

        
    }
}