<?php

if (!defined('ABSPATH')) {
    exit; // Exit if accessed directly.
}

function salesloo_insert_affiliate_click($data)
{

    $args = [];

    foreach (Salesloo\Models\Affiliate_Click::get_columns() as $key => $type) {

        if (isset($data[$key])) {
            $args[$key] = $data[$key];
        }
    }


    $affiliate_click_id = Salesloo\Models\Affiliate_Click::data($args)->create();

    return $affiliate_click_id;
}


/**
 * salesloo_get_clicks_count
 *
 * @param  array $args
 * @return mixed
 */
function salesloo_get_affiliate_clicks_count($args)
{
    [$affiliate_id, $product_id, $date_from, $date_to] = $args;

    $wheres = [
        'affiliate_id = ' . absint($affiliate_id),
    ];

    if ($product_id) {
        $wheres[] =  'product_id = ' . absint($product_id);
    }

    $wheres[] =  'visited_at >= "' . $date_from . '"';
    $wheres[] =  'visited_at <= "' . $date_to . '"';

    $query = '';

    if ($wheres) {
        $ww = implode(' AND ', $wheres);
        $query = 'WHERE ' . $ww;
    }

    $query .= <<<SQL
            GROUP BY DATE(visited_at)
        SQL;


    $results = Salesloo\Models\Affiliate_Click::select(
        <<<SQL
        COUNT(*) AS clicks,
        COUNT(DISTINCT ip) AS uclicks,
        DATE(visited_at) as date
        SQL
    )->query($query)->result();

    $clicks = [];
    foreach ((array)$results as $result) {
        $clicks[$result->date] = $result;
    }

    return $clicks;
}

/**
 * get affiliate clicc devices count
 *
 * @param  integer $user_id
 * @param  integer $product_id
 * @param  integer $daterange
 * @return object
 */
function salesloo_get_affiliate_clicks_devices($args)
{
    [$user_id, $product_id, $date_from, $date_to] = $args;

    $select = <<<SQL
            DISTINCT device as name,
            COUNT(device) AS count
        SQL;

    $wheres = [
        'affiliate_id = "' . absint($user_id) . '"',
    ];

    if ($product_id) {
        $wheres[] =  'product_id = ' . absint($product_id);
    }

    $wheres[] =  'visited_at >= "' . $date_from . '"';
    $wheres[] =  'visited_at <= "' . $date_to . '"';

    $query = '';

    if ($wheres) {
        $ww = implode(' AND ', $wheres);
        $query = <<<SQL
                WHERE $ww
            SQL;
    }

    $query .= <<<SQL
            GROUP BY name
        SQL;


    $result = Salesloo\Models\Affiliate_Click::select($select)->query($query)->order('count', 'DESC')->result();

    return $result;
}

/**
 * get affiliate click platforms count
 *
 * @param  integer $user_id
 * @param  integer $product_id
 * @param  integer $daterange
 * @return object
 */
function salesloo_get_affiliate_clicks_platforms($args)
{
    [$user_id, $product_id, $date_from, $date_to] = $args;

    $select = <<<SQL
            DISTINCT platform as name,
            COUNT(platform) AS count
        SQL;

    $wheres = [
        'affiliate_id = "' . absint($user_id) . '"',
    ];

    if ($product_id) {
        $wheres[] =  'product_id = ' . absint($product_id);
    }

    $wheres[] =  'visited_at >= "' . $date_from . '"';
    $wheres[] =  'visited_at <= "' . $date_to . '"';

    $query = '';

    if ($wheres) {
        $ww = implode(' AND ', $wheres);
        $query = <<<SQL
                WHERE $ww
            SQL;
    }

    $query .= <<<SQL
            GROUP BY name
        SQL;


    $result = Salesloo\Models\Affiliate_Click::select($select)->query($query)->order('count', 'DESC')->result();

    return $result;
}

/**
 * get affiliate clicc browsers count
 *
 * @param  integer $user_id
 * @param  integer $product_id
 * @param  integer $daterange
 * @return object
 */
function salesloo_get_affiliate_clicks_browsers($args)
{
    [$user_id, $product_id, $date_from, $date_to] = $args;

    $select = <<<SQL
            SUBSTRING_INDEX(browser,"/",1) as name,
            COUNT(browser) AS count
        SQL;

    $wheres = [
        'affiliate_id = "' . absint($user_id) . '"',
    ];

    if ($product_id) {
        $wheres[] =  'product_id = ' . absint($product_id);
    }

    $wheres[] =  'visited_at >= "' . $date_from . '"';
    $wheres[] =  'visited_at <= "' . $date_to . '"';

    $query = '';

    if ($wheres) {
        $ww = implode(' AND ', $wheres);
        $query = <<<SQL
                WHERE $ww
            SQL;
    }

    $query .= <<<SQL
            GROUP BY name
        SQL;


    $result = Salesloo\Models\Affiliate_Click::select($select)->query($query)->order('count', 'DESC')->result();

    return $result;
}

/**
 * get affiliate clicc referers count
 *
 * @param  integer $user_id
 * @param  integer $product_id
 * @param  integer $daterange
 * @return object
 */
function salesloo_get_affiliate_clicks_referers($args)
{
    [$user_id, $product_id, $date_from, $date_to] = $args;

    $select = <<<SQL
            SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(referer,"http://",""),"https://",""),"www.",""),"/",1) as domain,
            COUNT(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(referer,"http://",""),"https://",""),"www.",""),"/",1)) as count
        SQL;

    $wheres = [
        'affiliate_id = "' . absint($user_id) . '"',
    ];

    if ($product_id) {
        $wheres[] =  'product_id = ' . absint($product_id);
    }

    $wheres[] =  'visited_at >= "' . $date_from . '"';
    $wheres[] =  'visited_at <= "' . $date_to . '"';

    $query = '';

    if ($wheres) {
        $ww = implode(' AND ', $wheres);
        $query = <<<SQL
                WHERE $ww
            SQL;
    }

    $query .= <<<SQL
            GROUP BY domain
        SQL;


    $result = Salesloo\Models\Affiliate_Click::select($select)->query($query)->order('count', 'DESC')->result();

    return $result;
}

/**
 * get affiliate clicc devices count
 *
 * @param  integer $user_id
 * @param  integer $product_id
 * @param  integer $daterange
 * @return object
 */
function salesloo_get_affiliate_clicks_uris($args)
{
    [$user_id, $product_id, $date_from, $date_to] = $args;

    $select = <<<SQL
            DISTINCT uri as path,
            count(uri) as count
        SQL;

    $wheres = [
        'affiliate_id = "' . absint($user_id) . '"',
    ];

    if ($product_id) {
        $wheres[] =  'product_id = ' . absint($product_id);
    }

    $wheres[] =  'visited_at >= "' . $date_from . '"';
    $wheres[] =  'visited_at <= "' . $date_to . '"';

    $query = '';

    if ($wheres) {
        $ww = implode(' AND ', $wheres);
        $query = <<<SQL
                WHERE $ww
            SQL;
    }

    $query .= <<<SQL
            GROUP BY path
        SQL;


    $result = Salesloo\Models\Affiliate_Click::select($select)->query($query)->order('count', 'DESC')->result();

    return $result;
}
