<?php
/**
 * Vote & Reward - Nexora Edition v2 Anti-Farm
 *
 * Click claims grant Vote Points, not game credits. Credits are issued only
 * during a protected conversion step with progression, age and device gates.
 */
class Vote {
    private $_userid;
    private $_username;
    private $_votesideId;
    private $_accountInfo;
    private $_ip;
    private $_realm;
    private $_configXml = 'usercp.vote';
    private $_active = true;
    private $_saveLogs = true;
    private $_creditConfig = 0;
    private $_minAccountAgeDays = 5;
    private $_minLevel = 400;
    private $_minResets = 5;
    private $_minMasterLevel = 0;
    private $_minOnlineHours = 5;
    private $_deviceTrustHours = 24;
    private $_maxAccountsPerIp24h = 2;
    private $_maxAccountsPerDevice24h = 1;
    private $_pointsPerConversion = 10;
    private $_creditsPerConversion = 10;
    private $_dailyUserCreditCap = 20;
    private $_dailyDeviceCreditCap = 20;
    private $_dailyIpCreditCap = 40;
    private $_deviceHash = '';
    protected $common;
    protected $platform;
    protected $game;
    protected $accountDb;
    protected $xml;

    function __construct() {
        $this->common = new common();
        $this->_realm = class_exists('NexoraRealm') ? NexoraRealm::active() : 'legacy';
        $this->platform = Connection::Database('Platform', $this->_realm);
        $this->game = Connection::Database('MuOnline', $this->_realm);
        $this->accountDb = Connection::Database('Me_MuOnline', $this->_realm);
        $configPath = nexoraModuleConfigPath($this->_configXml, $this->_realm);
        if(!$configPath) throw new Exception(lang('error_100'));
        $this->xml = simplexml_load_file($configPath);
        if(!$this->xml) throw new Exception(lang('error_100'));
        $cfg = convertXML($this->xml);
        $this->_active = !empty($cfg['active']);
        $this->_saveLogs = !empty($cfg['vote_save_logs']);
        $this->_creditConfig = isset($cfg['credit_config']) ? (int)$cfg['credit_config'] : 0;
        $this->_minAccountAgeDays = $this->cfgInt($cfg, 'antifarm_min_account_days', 5, 0, 3650);
        $this->_minLevel = $this->cfgInt($cfg, 'antifarm_min_level', 400, 0, 10000);
        $this->_minResets = $this->cfgInt($cfg, 'antifarm_min_resets', 5, 0, 1000000);
        $this->_minMasterLevel = $this->cfgInt($cfg, 'antifarm_min_master_level', 0, 0, 10000);
        $this->_minOnlineHours = $this->cfgInt($cfg, 'antifarm_min_online_hours', 5, 0, 1000000);
        $this->_deviceTrustHours = $this->cfgInt($cfg, 'antifarm_device_trust_hours', 24, 0, 720);
        $this->_maxAccountsPerIp24h = $this->cfgInt($cfg, 'antifarm_max_accounts_ip_24h', 2, 0, 100);
        $this->_maxAccountsPerDevice24h = $this->cfgInt($cfg, 'antifarm_max_accounts_device_24h', 1, 0, 100);
        $this->_pointsPerConversion = $this->cfgInt($cfg, 'antifarm_points_per_conversion', 10, 1, 1000000);
        $this->_creditsPerConversion = $this->cfgInt($cfg, 'antifarm_credits_per_conversion', 10, 1, 1000000);
        $this->_dailyUserCreditCap = $this->cfgInt($cfg, 'antifarm_daily_user_credit_cap', 20, 0, 100000000);
        $this->_dailyDeviceCreditCap = $this->cfgInt($cfg, 'antifarm_daily_device_credit_cap', 20, 0, 100000000);
        $this->_dailyIpCreditCap = $this->cfgInt($cfg, 'antifarm_daily_ip_credit_cap', 40, 0, 100000000);
        $this->ensureSchema();
        $this->_deviceHash = $this->touchDevice();
    }

    private function cfgInt($cfg, $key, $default, $min, $max) {
        $value = isset($cfg[$key]) ? (int)$cfg[$key] : (int)$default;
        if($value < $min) $value = $min;
        if($value > $max) $value = $max;
        return $value;
    }

    public function getRealm() { return $this->_realm; }
    public function getDeviceHash() { return $this->_deviceHash; }

    public function setUserid($userid) {
        if(!check_value($userid) || !Validator::UnsignedNumber($userid)) throw new Exception(lang('error_23', true));
        $accountInfo = $this->common->accountInformation($userid);
        if(!is_array($accountInfo)) throw new Exception(lang('error_23', true));
        $this->_accountInfo = $accountInfo;
        $this->_userid = (int)$userid;
        $this->_username = (string)$this->_accountInfo[_CLMN_USERNM_];
        $this->ensureAccountMeta();
    }

    public function setVotesiteId($votesiteid) {
        if(!check_value($votesiteid) || !Validator::UnsignedNumber($votesiteid)) throw new Exception(lang('error_23', true));
        if(!$this->_siteExists((int)$votesiteid)) throw new Exception(lang('error_23', true));
        $this->_votesideId = (int)$votesiteid;
    }

    public function setIp($ip) {
        if(!check_value($ip) || !Validator::Ip($ip)) throw new Exception(lang('error_101'));
        $this->_ip = (string)$ip;
    }

    public function vote() {
        if(!$this->_userid || !$this->_ip || !$this->_votesideId) throw new Exception(lang('error_23', true));
        if(!$this->_active) throw new Exception(lang('error_47', true));
        $site = $this->retrieveVotesites($this->_votesideId);
        if(!is_array($site) || empty($site['enabled'])) throw new Exception(lang('error_23', true));

        $eligibility = $this->eligibility(false);
        if(empty($eligibility['eligible'])) {
            $this->securityEvent('claim_denied_eligibility', implode(' | ', $eligibility['reasons']));
            throw new RuntimeException('Tu cuenta aun no cumple los requisitos para recibir Vote Points: '.implode(' / ', $eligibility['reasons']));
        }

        $result = $this->awardVotePoints(
            (int)$site['votesite_id'],
            (int)$site['votesite_reward'],
            (int)$site['votesite_time']
        );
        if(class_exists('NexoraAudit')) {
            NexoraAudit::log('economy.vote_points','account',$this->_username,null,array(
                'site'=>(int)$site['votesite_id'],
                'points'=>(int)$site['votesite_reward'],
                'balance'=>(int)$result['points']
            ),$this->_realm);
        }
        $this->externalRedirect($site['votesite_link']);
    }

    public function convertPoints() {
        if(!$this->_userid || !$this->_ip) throw new Exception(lang('error_23', true));
        if(!$this->_active) throw new Exception(lang('error_47', true));
        if($this->_creditConfig < 1) throw new RuntimeException('La moneda de conversion no esta configurada.');

        $eligibility = $this->eligibility(true);
        if(empty($eligibility['eligible'])) {
            $this->securityEvent('conversion_denied_eligibility', implode(' | ', $eligibility['reasons']));
            throw new RuntimeException('No puedes convertir puntos todavia: '.implode(' / ', $eligibility['reasons']));
        }

        $identifier = $this->creditIdentifier();
        $economy = new NexoraEconomy($this->_realm);
        try {
            $result = $economy->convertVotePoints(
                $this->_creditConfig,
                $identifier,
                $this->_userid,
                $this->_username,
                $this->_ip,
                $this->_deviceHash,
                $this->_pointsPerConversion,
                $this->_creditsPerConversion,
                $this->_dailyUserCreditCap,
                $this->_dailyDeviceCreditCap,
                $this->_dailyIpCreditCap
            );
        } catch(Throwable $e) {
            $this->securityEvent('conversion_denied', $e->getMessage());
            throw $e;
        }
        if(class_exists('NexoraAudit')) {
            NexoraAudit::log('economy.vote_points_conversion','account',$this->_username,null,array(
                'points_spent'=>$this->_pointsPerConversion,
                'credits'=>$this->_creditsPerConversion,
                'points_balance'=>$result['points_balance']??null,
                'credit_balance'=>$result['credit_balance']??null
            ),$this->_realm);
        }
        return $result;
    }

    private function creditIdentifier() {
        $creditSystem = new CreditSystem();
        $creditSystem->setConfigId($this->_creditConfig);
        $settings = $creditSystem->showConfigs(true);
        if(!is_array($settings)) throw new Exception(lang('error_73'));
        switch($settings['config_user_col_id']) {
            case 'userid': return $this->_userid;
            case 'username': return $this->_username;
            case 'email':
                if(!isset($this->_accountInfo[_CLMN_EMAIL_])) throw new Exception(lang('error_73'));
                return $this->_accountInfo[_CLMN_EMAIL_];
            default: throw new Exception(lang('error_73'));
        }
    }

    public function eligibility($forConversion=false) {
        if(!$this->_userid || !$this->_username) return array('eligible'=>false,'reasons'=>array('Cuenta no cargada.'),'stats'=>array());
        $reasons = array();
        $stats = $this->progressStats();
        $age = $this->accountAgeDays();
        if($this->_minAccountAgeDays > 0 && $age < $this->_minAccountAgeDays) $reasons[] = 'antiguedad minima '.$this->_minAccountAgeDays.' dias';
        if($this->_minLevel > 0 && (int)$stats['level'] < $this->_minLevel) $reasons[] = 'nivel minimo '.$this->_minLevel;
        if($this->_minResets > 0 && (int)$stats['resets'] < $this->_minResets) $reasons[] = 'resets minimos '.$this->_minResets;
        if($this->_minMasterLevel > 0 && (int)$stats['master_level'] < $this->_minMasterLevel) $reasons[] = 'Master Level minimo '.$this->_minMasterLevel;
        if($this->_minOnlineHours > 0 && (int)$stats['online_hours'] < $this->_minOnlineHours) $reasons[] = 'horas jugadas minimas '.$this->_minOnlineHours;
        if(isset($this->_accountInfo[_CLMN_BLOCCODE_]) && (int)$this->_accountInfo[_CLMN_BLOCCODE_] !== 0) $reasons[] = 'cuenta bloqueada';
        if($forConversion && $this->_deviceTrustHours > 0) {
            $deviceAge = $this->deviceAgeHours();
            if($deviceAge < $this->_deviceTrustHours) $reasons[] = 'dispositivo en validacion ('.$this->_deviceTrustHours.' h)';
        }
        return array(
            'eligible'=>empty($reasons),
            'reasons'=>$reasons,
            'stats'=>$stats,
            'account_age_days'=>$age,
            'device_age_hours'=>$this->deviceAgeHours()
        );
    }

    private function progressStats() {
        $sql = 'SELECT MAX(ISNULL(c.['._CLMN_CHR_LVL_.'],0)) AS level, '
             . 'MAX(ISNULL(c.['._CLMN_CHR_RSTS_.'],0)) AS resets, '
             . 'MAX(ISNULL(c.['._CLMN_CHR_GRSTS_.'],0)) AS master_resets, '
             . 'MAX(ISNULL(m.['._CLMN_ML_LVL_.'],0)) AS master_level '
             . 'FROM ['._TBL_CHR_.'] c LEFT JOIN ['._TBL_MASTERLVL_.'] m ON m.['._CLMN_ML_NAME_.']=c.['._CLMN_CHR_NAME_.'] '
             . 'WHERE c.['._CLMN_CHR_ACCID_.']=?';
        $row = $this->game->query_fetch_single($sql, array($this->_username));
        if(!is_array($row)) $row = array();
        $online = $this->accountDb->query_fetch_single('SELECT TOP 1 ISNULL(['._CLMN_MS_ONLINEHRS_.'],0) AS online_hours FROM ['._TBL_MS_.'] WHERE ['._CLMN_MS_MEMBID_.']=?', array($this->_username));
        return array(
            'level'=>isset($row['level'])?(int)$row['level']:0,
            'resets'=>isset($row['resets'])?(int)$row['resets']:0,
            'master_resets'=>isset($row['master_resets'])?(int)$row['master_resets']:0,
            'master_level'=>isset($row['master_level'])?(int)$row['master_level']:0,
            'online_hours'=>is_array($online)&&isset($online['online_hours'])?(int)$online['online_hours']:0
        );
    }

    public function walletSummary() {
        if(!$this->_userid) return array();
        $row = $this->platform->query_fetch_single('SELECT points,lifetime_earned,lifetime_spent FROM NEXORA_VOTE_POINTS WHERE realm=? AND user_id=?',array($this->_realm,$this->_userid));
        $points = is_array($row) ? (int)$row['points'] : 0;
        $today = $this->conversionUsageToday();
        return array(
            'points'=>$points,
            'lifetime_earned'=>is_array($row)?(int)$row['lifetime_earned']:0,
            'lifetime_spent'=>is_array($row)?(int)$row['lifetime_spent']:0,
            'points_per_conversion'=>$this->_pointsPerConversion,
            'credits_per_conversion'=>$this->_creditsPerConversion,
            'daily_user_cap'=>$this->_dailyUserCreditCap,
            'daily_device_cap'=>$this->_dailyDeviceCreditCap,
            'daily_ip_cap'=>$this->_dailyIpCreditCap,
            'today_user_credits'=>$today['user'],
            'today_device_credits'=>$today['device'],
            'today_ip_credits'=>$today['ip'],
            'can_convert'=>$points >= $this->_pointsPerConversion
        );
    }

    private function conversionUsageToday() {
        $user = $this->platform->query_fetch_single('SELECT ISNULL(SUM(credits_granted),0) AS total FROM NEXORA_VOTE_CONVERSIONS WHERE realm=? AND user_id=? AND created_at>=CONVERT(date,SYSUTCDATETIME())',array($this->_realm,$this->_userid));
        $device = $this->platform->query_fetch_single('SELECT ISNULL(SUM(credits_granted),0) AS total FROM NEXORA_VOTE_CONVERSIONS WHERE realm=? AND device_hash=? AND created_at>=CONVERT(date,SYSUTCDATETIME())',array($this->_realm,$this->_deviceHash));
        $ip = $this->platform->query_fetch_single('SELECT ISNULL(SUM(credits_granted),0) AS total FROM NEXORA_VOTE_CONVERSIONS WHERE realm=? AND user_ip=? AND created_at>=CONVERT(date,SYSUTCDATETIME())',array($this->_realm,$this->_ip));
        return array(
            'user'=>is_array($user)?(int)$user['total']:0,
            'device'=>is_array($device)?(int)$device['total']:0,
            'ip'=>is_array($ip)?(int)$ip['total']:0
        );
    }

    public function availability($siteId, $userid=null, $ip=null) {
        $siteId=(int)$siteId;
        $userid=$userid===null?$this->_userid:(int)$userid;
        $ip=$ip===null?$this->_ip:(string)$ip;
        if($siteId<1 || $userid<1 || $ip==='') return array('available'=>false,'remaining'=>0);
        $rows=$this->platform->query_fetch(
            'SELECT timestamp FROM '.WEBENGINE_VOTES.' WHERE realm=? AND vote_site_id=? AND (user_id=? OR user_ip=? OR device_hash=?)',
            array($this->_realm,$siteId,$userid,$ip,$this->_deviceHash)
        );
        $until=0;
        if(is_array($rows)) foreach($rows as $row) $until=max($until,(int)$row['timestamp']);
        return array('available'=>$until<=time(),'remaining'=>max(0,$until-time()),'until'=>$until);
    }

    private function awardVotePoints($siteId,$points,$cooldownHours) {
        $siteId=(int)$siteId; $points=(int)$points; $cooldownHours=(int)$cooldownHours;
        if($siteId<1 || $points<1 || $cooldownHours<1) throw new InvalidArgumentException('Datos de votacion no validos.');
        $now=time(); $expires=$now+($cooldownHours*3600); $since=$now-86400;
        if(!$this->platform->beginTransaction()) throw new RuntimeException('No se pudo iniciar el registro de voto.');
        try {
            $this->assertClaimNetworkLimits($since);
            $this->clearExpiredCooldowns('user_id',$this->_userid,$siteId,$now,'Esta cuenta ya recibio la recompensa de este sitio.');
            $this->clearExpiredCooldowns('user_ip',$this->_ip,$siteId,$now,'Esta IP ya recibio la recompensa de este sitio.');
            $this->clearExpiredCooldowns('device_hash',$this->_deviceHash,$siteId,$now,'Este dispositivo ya recibio la recompensa de este sitio.');

            $claim=$this->platform->query_affected(
                'INSERT INTO '.WEBENGINE_VOTES.' (user_id,user_ip,vote_site_id,timestamp,realm,device_hash) VALUES (?,?,?,?,?,?)',
                array($this->_userid,$this->_ip,$siteId,$expires,$this->_realm,$this->_deviceHash)
            );
            if($claim!==1) throw new RuntimeException('No se pudo reservar el cooldown de votacion.');

            $wallet=$this->platform->query_fetch_single('SELECT points FROM NEXORA_VOTE_POINTS WITH (UPDLOCK,HOLDLOCK) WHERE realm=? AND user_id=?',array($this->_realm,$this->_userid));
            if(is_array($wallet)) {
                $updated=$this->platform->query_affected('UPDATE NEXORA_VOTE_POINTS SET points=points+?, lifetime_earned=lifetime_earned+?, account_username=?, updated_at=SYSUTCDATETIME() WHERE realm=? AND user_id=?',array($points,$points,$this->_username,$this->_realm,$this->_userid));
                if($updated!==1) throw new RuntimeException('No se pudieron acreditar los Vote Points.');
            } else {
                $inserted=$this->platform->query_affected('INSERT INTO NEXORA_VOTE_POINTS (realm,user_id,account_username,points,lifetime_earned,lifetime_spent,created_at,updated_at) VALUES (?,?,?,?,?,0,SYSUTCDATETIME(),SYSUTCDATETIME())',array($this->_realm,$this->_userid,$this->_username,$points,$points));
                if($inserted!==1) throw new RuntimeException('No se pudieron crear los Vote Points.');
            }

            // This security ledger is mandatory in v2 because IP/device limits
            // depend on it. The historical save-logs switch is kept only for
            // configuration compatibility and cannot disable Anti-Farm records.
            $logged=$this->platform->query_affected(
                'INSERT INTO '.WEBENGINE_VOTE_LOGS.' (user_id,votesite_id,timestamp,realm,credits_reward,user_ip,account_username,points_reward,device_hash,reward_status) VALUES (?,?,?,?,0,?,?,?,?,?)',
                array($this->_userid,$siteId,$now,$this->_realm,$this->_ip,$this->_username,$points,$this->_deviceHash,'points')
            );
            if($logged!==1) throw new RuntimeException('No se pudo registrar el voto; los puntos fueron revertidos.');
            if(!$this->platform->commit()) throw new RuntimeException('No se pudo confirmar el voto.');
            $row=$this->platform->query_fetch_single('SELECT points FROM NEXORA_VOTE_POINTS WHERE realm=? AND user_id=?',array($this->_realm,$this->_userid));
            return array('points'=>is_array($row)?(int)$row['points']:0,'earned'=>$points,'expires'=>$expires);
        } catch(Throwable $e) {
            if($this->platform->inTransaction()) $this->platform->rollBack();
            $this->securityEvent('claim_denied', $e->getMessage());
            throw $e;
        }
    }

    private function clearExpiredCooldowns($column,$value,$siteId,$now,$error) {
        if(!in_array($column,array('user_id','user_ip','device_hash'),true)) throw new RuntimeException('Filtro de cooldown invalido.');
        $rows=$this->platform->query_fetch('SELECT id,timestamp FROM '.WEBENGINE_VOTES.' WITH (UPDLOCK,HOLDLOCK) WHERE realm=? AND vote_site_id=? AND ['.$column.']=?',array($this->_realm,$siteId,$value));
        if(is_array($rows)) {
            foreach($rows as $row) {
                if((int)$row['timestamp']>$now) throw new RuntimeException($error);
                $this->platform->query_affected('DELETE FROM '.WEBENGINE_VOTES.' WHERE id=?',array((int)$row['id']));
            }
        }
    }

    private function assertClaimNetworkLimits($since) {
        if($this->_maxAccountsPerIp24h>0) {
            $row=$this->platform->query_fetch_single('SELECT COUNT(DISTINCT user_id) AS total FROM '.WEBENGINE_VOTE_LOGS.' WITH (UPDLOCK,HOLDLOCK) WHERE realm=? AND user_ip=? AND timestamp>=?',array($this->_realm,$this->_ip,$since));
            $mine=$this->platform->query_fetch_single('SELECT TOP 1 user_id FROM '.WEBENGINE_VOTE_LOGS.' WHERE realm=? AND user_ip=? AND user_id=? AND timestamp>=?',array($this->_realm,$this->_ip,$this->_userid,$since));
            if(is_array($row) && (int)$row['total'] >= $this->_maxAccountsPerIp24h && !is_array($mine)) throw new RuntimeException('Limite de cuentas recompensadas desde esta red alcanzado por 24 horas.');
        }
        if($this->_maxAccountsPerDevice24h>0) {
            $row=$this->platform->query_fetch_single('SELECT COUNT(DISTINCT user_id) AS total FROM '.WEBENGINE_VOTE_LOGS.' WITH (UPDLOCK,HOLDLOCK) WHERE realm=? AND device_hash=? AND timestamp>=?',array($this->_realm,$this->_deviceHash,$since));
            $mine=$this->platform->query_fetch_single('SELECT TOP 1 user_id FROM '.WEBENGINE_VOTE_LOGS.' WHERE realm=? AND device_hash=? AND user_id=? AND timestamp>=?',array($this->_realm,$this->_deviceHash,$this->_userid,$since));
            if(is_array($row) && (int)$row['total'] >= $this->_maxAccountsPerDevice24h && !is_array($mine)) throw new RuntimeException('Este dispositivo ya esta asociado a otra cuenta recompensada durante las ultimas 24 horas.');
        }
    }

    public function getVoteUrl($siteId) {
        $site=$this->retrieveVotesites($siteId);
        if(!is_array($site)) return '';
        $url=trim((string)$site['votesite_link']);
        $scheme=strtolower((string)parse_url($url,PHP_URL_SCHEME));
        return filter_var($url,FILTER_VALIDATE_URL)&&in_array($scheme,array('http','https'),true)?$url:'';
    }

    private function externalRedirect($url) {
        $url=trim((string)$url);
        $scheme=strtolower((string)parse_url($url,PHP_URL_SCHEME));
        if(!filter_var($url,FILTER_VALIDATE_URL) || !in_array($scheme,array('http','https'),true)) throw new RuntimeException('La URL del sitio de votacion no es valida.');
        redirect(3,$url);
    }

    public function addVotesite($title, $link, $reward, $time) {
        $title=trim((string)$title); $link=trim((string)$link); $reward=(int)$reward; $time=(int)$time;
        if($title==='' || strlen($title)>50 || $reward<1 || $time<1) throw new InvalidArgumentException('Completa correctamente los datos del sitio de votacion.');
        if(!filter_var($link,FILTER_VALIDATE_URL) || !in_array(strtolower((string)parse_url($link,PHP_URL_SCHEME)),array('http','https'),true)) throw new InvalidArgumentException('La URL del sitio de votacion no es valida.');
        $affected=$this->platform->query_affected('INSERT INTO '.WEBENGINE_VOTE_SITES.' (votesite_title,votesite_link,votesite_reward,votesite_time,realm,enabled) VALUES (?,?,?,?,?,1)',array($title,$link,$reward,$time,$this->_realm));
        if($affected!==1) throw new RuntimeException('No se pudo agregar el sitio de votacion.');
        return true;
    }

    public function deleteVotesite($id) {
        $id=(int)$id; if($id<1 || !$this->_siteExists($id)) return false;
        return $this->platform->query_affected('DELETE FROM '.WEBENGINE_VOTE_SITES.' WHERE votesite_id=? AND realm=?',array($id,$this->_realm))===1;
    }

    public function setVotesiteEnabled($id,$enabled) {
        $id=(int)$id; if($id<1 || !$this->_siteExists($id)) return false;
        return $this->platform->query_affected('UPDATE '.WEBENGINE_VOTE_SITES.' SET enabled=? WHERE votesite_id=? AND realm=?',array($enabled?1:0,$id,$this->_realm))===1;
    }

    public function retrieveVotesites($id=null) {
        if(check_value($id)) return $this->platform->query_fetch_single('SELECT * FROM '.WEBENGINE_VOTE_SITES.' WHERE votesite_id=? AND realm=?',array((int)$id,$this->_realm));
        $rows=$this->platform->query_fetch('SELECT * FROM '.WEBENGINE_VOTE_SITES.' WHERE realm=? ORDER BY votesite_id ASC',array($this->_realm));
        return is_array($rows)?$rows:array();
    }

    public function recentSecurityEvents($limit=50) {
        $limit=max(1,min(200,(int)$limit));
        $rows=$this->platform->query_fetch('SELECT TOP '.$limit.' event_type,account_username,user_ip,detail,created_at FROM NEXORA_VOTE_SECURITY_EVENTS WHERE realm=? ORDER BY id DESC',array($this->_realm));
        return is_array($rows)?$rows:array();
    }

    private function _siteExists($id) {
        return is_array($this->platform->query_fetch_single('SELECT votesite_id FROM '.WEBENGINE_VOTE_SITES.' WHERE votesite_id=? AND realm=?',array((int)$id,$this->_realm)));
    }

    private function accountAgeDays() {
        $row=$this->platform->query_fetch_single('SELECT DATEDIFF(SECOND,created_at,SYSUTCDATETIME()) AS age_seconds FROM NEXORA_VOTE_ACCOUNT_META WHERE realm=? AND user_id=?',array($this->_realm,$this->_userid));
        return is_array($row)?max(0,(int)floor(((int)$row['age_seconds'])/86400)):0;
    }

    private function ensureAccountMeta() {
        $row=$this->platform->query_fetch_single('SELECT user_id FROM NEXORA_VOTE_ACCOUNT_META WHERE realm=? AND user_id=?',array($this->_realm,$this->_userid));
        if(is_array($row)) return;
        // Accounts that predate installation are grandfathered. New accounts are
        // timestamped by NexoraAccountProvisioner::createAccounts().
        $this->platform->query_affected(
            "INSERT INTO NEXORA_VOTE_ACCOUNT_META (realm,user_id,account_username,created_at,source) VALUES (?,?,?,DATEADD(DAY,-3650,SYSUTCDATETIME()),'legacy-grandfather')",
            array($this->_realm,$this->_userid,$this->_username)
        );
    }

    private function touchDevice() {
        if(defined('access') && access==='cron') return '';
        $name='nx_vote_device';
        $raw=isset($_COOKIE[$name])?(string)$_COOKIE[$name]:'';
        if(!preg_match('/^[a-f0-9]{64}$/D',$raw)) {
            try { $raw=bin2hex(random_bytes(32)); }
            catch(Throwable $e) { $raw=hash('sha256',session_id().'|'.microtime(true).'|'.mt_rand()); }
            $secure=!empty($_SERVER['HTTPS']) && strtolower((string)$_SERVER['HTTPS'])==='on';
            @setcookie($name,$raw,time()+31536000,'/','',$secure,true);
            $_COOKIE[$name]=$raw;
        }
        $hash=hash('sha256',$raw);
        $ua=hash('sha256',isset($_SERVER['HTTP_USER_AGENT'])?(string)$_SERVER['HTTP_USER_AGENT']:'');
        $ip=class_exists('NexoraSecurity')?NexoraSecurity::clientIp():(isset($_SERVER['REMOTE_ADDR'])?(string)$_SERVER['REMOTE_ADDR']:'0.0.0.0');
        $row=$this->platform->query_fetch_single('SELECT device_hash FROM NEXORA_VOTE_DEVICES WHERE device_hash=?',array($hash));
        if(is_array($row)) {
            $this->platform->query_affected('UPDATE NEXORA_VOTE_DEVICES SET last_seen_at=SYSUTCDATETIME(),last_ip=?,ua_hash=? WHERE device_hash=?',array($ip,$ua,$hash));
        } else {
            $this->platform->query_affected('INSERT INTO NEXORA_VOTE_DEVICES (device_hash,first_seen_at,last_seen_at,first_ip,last_ip,ua_hash) VALUES (?,SYSUTCDATETIME(),SYSUTCDATETIME(),?,?,?)',array($hash,$ip,$ip,$ua));
        }
        return $hash;
    }

    private function deviceAgeHours() {
        if($this->_deviceHash==='') return 0;
        $row=$this->platform->query_fetch_single('SELECT DATEDIFF(SECOND,first_seen_at,SYSUTCDATETIME()) AS age_seconds FROM NEXORA_VOTE_DEVICES WHERE device_hash=?',array($this->_deviceHash));
        return is_array($row)?max(0,(int)floor(((int)$row['age_seconds'])/3600)):0;
    }

    private function securityEvent($type,$detail) {
        try {
            $this->platform->query_affected('INSERT INTO NEXORA_VOTE_SECURITY_EVENTS (realm,user_id,account_username,event_type,detail,user_ip,device_hash,created_at) VALUES (?,?,?,?,?,?,?,SYSUTCDATETIME())',array(
                $this->_realm,$this->_userid?:null,$this->_username?:null,substr((string)$type,0,50),substr((string)$detail,0,500),$this->_ip?:null,$this->_deviceHash?:null
            ));
        } catch(Throwable $ignored) {}
    }

    public static function recordAccountCreation($username,$realms=null) {
        if(!class_exists('NexoraRealm')) return false;
        $username=trim((string)$username); if($username==='') return false;
        if(!is_array($realms) || empty($realms)) $realms=NexoraRealm::registrationRealms();
        foreach($realms as $realm) {
            try {
                $realm=NexoraRealm::validate($realm);
                $p=Connection::Database('Platform',$realm);
                self::ensureAccountMetaTableStatic($p);
                $a=Connection::Database('Me_MuOnline',$realm);
                $acct=$a->query_fetch_single('SELECT TOP 1 ['._CLMN_MEMBID_.'] AS user_id FROM ['._TBL_MI_.'] WHERE ['._CLMN_USERNM_.']=?',array($username));
                if(!is_array($acct)) continue;
                $exists=$p->query_fetch_single('SELECT user_id FROM NEXORA_VOTE_ACCOUNT_META WHERE realm=? AND user_id=?',array($realm,(int)$acct['user_id']));
                if(!is_array($exists)) $p->query_affected("INSERT INTO NEXORA_VOTE_ACCOUNT_META (realm,user_id,account_username,created_at,source) VALUES (?,?,?,SYSUTCDATETIME(),'registration')",array($realm,(int)$acct['user_id'],$username));
            } catch(Throwable $ignored) {}
        }
        return true;
    }

    private static function ensureAccountMetaTableStatic($db) {
        $exists=$db->query_fetch_single("SELECT name FROM sys.objects WHERE name='NEXORA_VOTE_ACCOUNT_META' AND type='U'");
        if(!is_array($exists)) {
            $db->query("CREATE TABLE [NEXORA_VOTE_ACCOUNT_META] ([realm] varchar(20) NOT NULL,[user_id] int NOT NULL,[account_username] varchar(50) NOT NULL,[created_at] datetime2 NOT NULL,[source] varchar(30) NOT NULL,CONSTRAINT [PK_NEXORA_VOTE_ACCOUNT_META] PRIMARY KEY ([realm],[user_id]))");
        }
    }

    private function ensureSchema() {
        $this->addColumnIfMissing(WEBENGINE_VOTE_SITES,'realm',"varchar(20) NOT NULL CONSTRAINT DF_".WEBENGINE_VOTE_SITES."_realm DEFAULT ('legacy') WITH VALUES");
        $this->addColumnIfMissing(WEBENGINE_VOTE_SITES,'enabled',"bit NOT NULL CONSTRAINT DF_".WEBENGINE_VOTE_SITES."_enabled DEFAULT (1) WITH VALUES");
        $this->addColumnIfMissing(WEBENGINE_VOTES,'realm',"varchar(20) NOT NULL CONSTRAINT DF_".WEBENGINE_VOTES."_realm DEFAULT ('legacy') WITH VALUES");
        $this->addColumnIfMissing(WEBENGINE_VOTES,'device_hash','varchar(64) NULL');
        $this->addColumnIfMissing(WEBENGINE_VOTE_LOGS,'realm',"varchar(20) NOT NULL CONSTRAINT DF_".WEBENGINE_VOTE_LOGS."_realm DEFAULT ('legacy') WITH VALUES");
        $this->addColumnIfMissing(WEBENGINE_VOTE_LOGS,'credits_reward','int NULL');
        $this->addColumnIfMissing(WEBENGINE_VOTE_LOGS,'user_ip','varchar(50) NULL');
        $this->addColumnIfMissing(WEBENGINE_VOTE_LOGS,'account_username','varchar(50) NULL');
        $this->addColumnIfMissing(WEBENGINE_VOTE_LOGS,'points_reward','int NULL');
        $this->addColumnIfMissing(WEBENGINE_VOTE_LOGS,'device_hash','varchar(64) NULL');
        $this->addColumnIfMissing(WEBENGINE_VOTE_LOGS,'reward_status','varchar(30) NULL');
        $this->ensureAntiFarmTables();
        $this->createIndexIfMissing('IX_'.WEBENGINE_VOTES.'_realm_user_site',WEBENGINE_VOTES,'(realm,user_id,vote_site_id)');
        $this->createIndexIfMissing('IX_'.WEBENGINE_VOTES.'_realm_ip_site',WEBENGINE_VOTES,'(realm,user_ip,vote_site_id)');
        $this->createIndexIfMissing('IX_'.WEBENGINE_VOTES.'_realm_device_site',WEBENGINE_VOTES,'(realm,device_hash,vote_site_id)');
        $this->createIndexIfMissing('IX_'.WEBENGINE_VOTE_LOGS.'_realm_device_time',WEBENGINE_VOTE_LOGS,'(realm,device_hash,timestamp)');
        $this->createIndexIfMissing('IX_'.WEBENGINE_VOTE_LOGS.'_realm_ip_time',WEBENGINE_VOTE_LOGS,'(realm,user_ip,timestamp)');
    }

    private function ensureAntiFarmTables() {
        self::ensureAccountMetaTableStatic($this->platform);
        $tables=array(
            'NEXORA_VOTE_POINTS'=>"CREATE TABLE [NEXORA_VOTE_POINTS] ([realm] varchar(20) NOT NULL,[user_id] int NOT NULL,[account_username] varchar(50) NOT NULL,[points] int NOT NULL CONSTRAINT [DF_NVP_points] DEFAULT(0),[lifetime_earned] int NOT NULL CONSTRAINT [DF_NVP_earned] DEFAULT(0),[lifetime_spent] int NOT NULL CONSTRAINT [DF_NVP_spent] DEFAULT(0),[created_at] datetime2 NOT NULL,[updated_at] datetime2 NOT NULL,CONSTRAINT [PK_NEXORA_VOTE_POINTS] PRIMARY KEY ([realm],[user_id]))",
            'NEXORA_VOTE_DEVICES'=>"CREATE TABLE [NEXORA_VOTE_DEVICES] ([device_hash] varchar(64) NOT NULL,[first_seen_at] datetime2 NOT NULL,[last_seen_at] datetime2 NOT NULL,[first_ip] varchar(50) NULL,[last_ip] varchar(50) NULL,[ua_hash] varchar(64) NULL,CONSTRAINT [PK_NEXORA_VOTE_DEVICES] PRIMARY KEY ([device_hash]))",
            'NEXORA_VOTE_CONVERSIONS'=>"CREATE TABLE [NEXORA_VOTE_CONVERSIONS] ([id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,[realm] varchar(20) NOT NULL,[user_id] int NOT NULL,[account_username] varchar(50) NOT NULL,[points_spent] int NOT NULL,[credits_granted] int NOT NULL,[credit_config] int NOT NULL,[device_hash] varchar(64) NULL,[user_ip] varchar(50) NULL,[created_at] datetime2 NOT NULL)",
            'NEXORA_VOTE_SECURITY_EVENTS'=>"CREATE TABLE [NEXORA_VOTE_SECURITY_EVENTS] ([id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,[realm] varchar(20) NOT NULL,[user_id] int NULL,[account_username] varchar(50) NULL,[event_type] varchar(50) NOT NULL,[detail] varchar(500) NULL,[user_ip] varchar(50) NULL,[device_hash] varchar(64) NULL,[created_at] datetime2 NOT NULL)"
        );
        foreach($tables as $name=>$ddl) {
            $exists=$this->platform->query_fetch_single('SELECT name FROM sys.objects WHERE name=? AND type=?',array($name,'U'));
            if(!is_array($exists) && !$this->platform->query($ddl)) throw new RuntimeException('No se pudo crear la estructura Anti-Farm de Vote & Reward.');
        }
        $this->createIndexIfMissing('IX_NEXORA_VOTE_CONV_REALM_USER_DATE','NEXORA_VOTE_CONVERSIONS','(realm,user_id,created_at)');
        $this->createIndexIfMissing('IX_NEXORA_VOTE_CONV_REALM_DEVICE_DATE','NEXORA_VOTE_CONVERSIONS','(realm,device_hash,created_at)');
        $this->createIndexIfMissing('IX_NEXORA_VOTE_CONV_REALM_IP_DATE','NEXORA_VOTE_CONVERSIONS','(realm,user_ip,created_at)');
    }

    private function addColumnIfMissing($table,$column,$definition) {
        $exists=$this->platform->query_fetch_single("SELECT c.name FROM sys.columns c INNER JOIN sys.objects o ON c.object_id=o.object_id WHERE o.name=? AND c.name=?",array($table,$column));
        if(!$exists && !$this->platform->query('ALTER TABLE ['.$table.'] ADD ['.$column.'] '.$definition)) throw new RuntimeException('No se pudo actualizar la estructura de Vote & Reward.');
    }

    private function createIndexIfMissing($index,$table,$columns) {
        $exists=$this->platform->query_fetch_single('SELECT name FROM sys.indexes WHERE name=? AND object_id=OBJECT_ID(?)',array($index,$table));
        if(!$exists) $this->platform->query('CREATE NONCLUSTERED INDEX ['.$index.'] ON ['.$table.'] '.$columns);
    }
}
