<?php
/**
 * Class represents records from table aff_clicks
 * {autogenerated}
 * @property int $log_id PK
 * @property int|null $aff_id
 * @property datetime $time
 * @property int|null $banner_id FK aff3_banner
 * @property string|null $remote_addr
 * @property string|null $referer
 * @property int|null $keyword_id FK aff_keyword
 * @property string|null $user_agent
 * {/autogenerated}
 */

class AffClick extends Am_Record { }

/** @extends Am_Table<AffClick> */
class AffClickTable extends Am_Table {
    protected $_key = 'log_id';
    protected $_table = '?_aff_click';
    protected $_recordClass = 'AffClick';
  
    public function insert(array $values, $returnInserted = false)
    {
        if (empty($values['time']))
            $values['time'] = $this->getDi()->sqlDateTime;
        return parent::insert($values, $returnInserted);
    }
    
    function log(User $aff, ?AffBanner $banner = null, $referrer = null, $keyword_id = null)
    {
        $this->_db->query("INSERT INTO ?_aff_click
            SET
                aff_id=?d,
                time=?,
                banner_id=?d,
                remote_addr=?,
                referer=?,
                keyword_id = ?, 
                user_agent = ?
            ", 
            $aff->pk(), 
            $this->getDi()->sqlDateTime,
            $banner ? $banner->pk() : null, 
            $_SERVER['REMOTE_ADDR'], ($referrer ? $referrer : @$_SERVER['HTTP_REFERER']), 
            $keyword_id, @$_SERVER['HTTP_USER_AGENT']
        );
        return $this->_db->selectCell("SELECT LAST_INSERT_ID()");
    }

    /** @return TRecord[] */
    function fetchByDate($date, $aff_id=null)
    {
        return $this->selectObjects(
                "SELECT * FROM ?_aff_click WHERE time BETWEEN ? AND ? { AND aff_id=?d}",
                date('Y-m-d 00:00:00', amstrtotime($date)), date('Y-m-d 23:59:59', amstrtotime($date)),
                $aff_id === null ? DBSIMPLE_SKIP : $aff_id);
    }

    /** @return TRecord[] */
    function fetchByDateInterval($from, $to, $aff_id=null)
    {
        return $this->selectObjects(
                "SELECT * FROM ?_aff_click WHERE time BETWEEN ? AND ? { AND aff_id=?d}",
                date('Y-m-d 00:00:00', amstrtotime($from)), date('Y-m-d 23:59:59', amstrtotime($to)),
                $aff_id === null ? DBSIMPLE_SKIP : $aff_id);
    }
    /**
     * Find affililate by IP address if cookie is empty
     * @param type $ip 
     * @return last aff_id in record with matching IP 
     */
    function findAffIdByIp($ip)
    {
        $startTm = sqlTime($this->getDi()->time - $this->getDi()->config->get('aff.cookie_lifetime', 365) * 24 * 3600);
        return $this->_db->selectCell("SELECT CONCAT(aff_id, '-', IFNULL(banner_id, ''))
            FROM $this->_table
            WHERE time >= ? AND remote_addr=? AND aff_id>0
            ORDER BY time DESC
            LIMIT 1", $startTm, $ip);
    }

    function clearOld($date)
    {
        $this->_db->query("DELETE FROM ?_aff_click WHERE time < ?", $date);
    }
    
    /** @return TRecord[] */
    function selectLast($num)
    {
        return $this->selectObjects(
            "SELECT "
            . "ac.*, aff.login, aff.name_f, aff.name_l, aff.email, "
            . "ab.title as banner_title "
            . " FROM ?_aff_click ac LEFT JOIN ?_aff_banner ab USING (banner_id) "
            . "     LEFT JOIN ?_user aff ON (aff.user_id=ac.aff_id) "
            . " ORDER BY ac.`time` DESC "
            . " LIMIT ?d", $num);
    }
}
