<?php
/**
 * Referral System - Nexora Edition
 * Based on WebEngine Referral System 1.2.0
 */
namespace Plugin\ReferralSystem;

class ReferralSystem {
    private $_referralTable;
    private $_cronFile = 'referral_system.php';
    private $_usercpmenu = array(
        array('active'=>true,'type'=>'internal','phrase'=>'referralsystem_title','link'=>'referral/list','icon'=>'usercp_default.png','visibility'=>'user','newtab'=>false,'order'=>999)
    );

    private $realm;
    private $common;
    private $mu;
    private $me;
    private $platform;
    private $config;

    private $_id;
    private $_referralUsername;
    private $_referralCharacter;
    private $_referredUsername;
    private $_referredCharacter;

    public function __construct($realm=null, $ensureCron=true) {
        $this->realm = class_exists('NexoraRealm') ? \NexoraRealm::validate($realm === null ? \NexoraRealm::active() : $realm) : null;
        $this->_referralTable = WE_PREFIX . 'WEBENGINE_REFERRALSYSTEM';
        $this->common = new \common();
        $this->mu = \Connection::Database('MuOnline', $this->realm);
        $this->me = \Connection::Database('Me_MuOnline', $this->realm);
        $this->platform = \Connection::Database('Platform', $this->realm);
        $this->config = $this->loadConfig();
        $this->ensureTable();
        if($ensureCron) $this->ensureCron();
    }

    public static function configPath($realm=null) {
        if(class_exists('NexoraRealm')) $realm = \NexoraRealm::validate($realm === null ? \NexoraRealm::active() : $realm);
        else $realm = 'default';
        return __PATH_REFERRALSYSTEM_ROOT__.'config.'.$realm.'.xml';
    }

    public function getRealm() { return $this->realm; }

    public function loadModule($module) {
        if(!\Validator::Alpha($module)) throw new \Exception(lang('referralsystem_error_3'));
        $path = __PATH_REFERRALSYSTEM_ROOT__.'modules/'.$module.'.php';
        if(!is_file($path)) throw new \Exception(lang('referralsystem_error_3'));

        // A plugin module may intentionally use `return;` after rendering a valid
        // empty state (for example, when the account has no referrals yet).
        // PHP then makes include() return NULL, which is not a load failure.
        // File existence is validated above, so include it directly and let the
        // module control its normal flow without generating a false error.
        include $path;
    }

    public function setId($id) { $this->_id = (int)$id; }
    public function setReferralUsername($v) { $this->_referralUsername = trim((string)$v); }
    public function setReferralCharacter($v) { $this->_referralCharacter = trim((string)$v); }
    public function setReferredUsername($v) { $this->_referredUsername = trim((string)$v); }
    public function setReferredCharacter($v) { $this->_referredCharacter = trim((string)$v); }

    public function configValue($key, $default=0) {
        return array_key_exists($key, $this->config) ? $this->config[$key] : $default;
    }

    public function registerReferral() {
        if($this->_referralUsername==='' || $this->_referralCharacter==='' || $this->_referredUsername==='') return false;
        if(strcasecmp($this->_referralUsername, $this->_referredUsername)===0) return false;
        if(!$this->referrerCharacterValid()) return false;

        $currentIp = class_exists('NexoraSecurity') ? \NexoraSecurity::clientIp() : (isset($_SERVER['REMOTE_ADDR'])?(string)$_SERVER['REMOTE_ADDR']:'');
        if(!empty($this->config['block_same_ip']) && $this->sameIpAsReferrer($currentIp)) return false;

        if(!$this->me->beginTransaction()) throw new \RuntimeException('No se pudo iniciar el registro del referido.');
        try {
            $existing = $this->me->query_fetch_single(
                'SELECT TOP 1 id FROM '.$this->_referralTable.' WITH (UPDLOCK,HOLDLOCK) WHERE referred_username = ?',
                array($this->_referredUsername)
            );
            if(is_array($existing)) {
                $this->me->rollBack();
                return false;
            }
            $inserted = $this->me->query_affected(
                'INSERT INTO '.$this->_referralTable.' (referral_username,referral_character,referred_username,referred_registration_date,referred_ip,status) VALUES (?,?,?,CURRENT_TIMESTAMP,?,2)',
                array($this->_referralUsername,$this->_referralCharacter,$this->_referredUsername,$currentIp)
            );
            if($inserted !== 1) throw new \RuntimeException('No se pudo registrar la invitación.');
            if(!$this->me->commit()) throw new \RuntimeException('No se pudo confirmar la invitación.');
            if(class_exists('NexoraAudit')) \NexoraAudit::log('referral.created','account',$this->_referredUsername,null,array('referrer'=>$this->_referralUsername),$this->realm);
            return true;
        } catch(\Throwable $e) {
            if($this->me->inTransaction()) $this->me->rollBack();
            throw $e;
        }
    }

    public function checkUnverifiedReferrals() {
        $rows = $this->me->query_fetch('SELECT * FROM '.$this->_referralTable.' WHERE status = 2');
        if(!is_array($rows)) return;
        $limit = max(1,(int)$this->configValue('verification_hours',12))*3600;
        foreach($rows as $row) {
            if($this->accountExists($row['referred_username'])) {
                $this->me->query('UPDATE '.$this->_referralTable.' SET status=0,referred_last_check=CURRENT_TIMESTAMP WHERE id=? AND status=2',array((int)$row['id']));
                continue;
            }
            if(time() > strtotime($row['referred_registration_date']) + $limit) {
                $this->me->query('UPDATE '.$this->_referralTable.' SET status=3,referred_last_check=CURRENT_TIMESTAMP WHERE id=? AND status=2',array((int)$row['id']));
            }
        }
    }

    public function checkReferrals() {
        $rows = $this->me->query_fetch('SELECT * FROM '.$this->_referralTable.' WHERE status = 0 AND referred_complete_date IS NULL');
        if(!is_array($rows)) return;
        foreach($rows as $row) {
            $this->me->query('UPDATE '.$this->_referralTable.' SET referred_last_check=CURRENT_TIMESTAMP WHERE id=?',array((int)$row['id']));
            if(!$this->accountExists($row['referred_username'])) continue;
            $days=(int)$this->configValue('time_limit_days',0);
            if($days>0 && time() > strtotime($row['referred_registration_date']) + ($days*86400)) {
                $this->me->query('UPDATE '.$this->_referralTable.' SET status=3 WHERE id=? AND status=0',array((int)$row['id']));
                continue;
            }
            $character=$this->qualifyingCharacter($row['referred_username']);
            if($character===null) continue;
            $this->me->query('UPDATE '.$this->_referralTable.' SET referred_character=?,referred_complete_date=CURRENT_TIMESTAMP WHERE id=? AND status=0 AND referred_complete_date IS NULL',array($character,(int)$row['id']));
        }
    }

    public function checkPendingRewardReferrals() {
        $configId=(int)$this->configValue('reward_credit_config',0);
        $amount=(int)$this->configValue('reward_credit_amount',0);
        if($configId<1 || $amount<1) return;
        $rows=$this->me->query_fetch('SELECT id FROM '.$this->_referralTable.' WHERE status=0 AND referred_complete_date IS NOT NULL');
        if(!is_array($rows)) return;
        foreach($rows as $row) {
            try { $this->rewardReferral((int)$row['id'],$configId,$amount); } catch(\Throwable $e) { /* retry on next cron */ }
        }
    }

    public function getAccountReferrals() {
        if($this->_referralUsername==='') return;
        $r=$this->me->query_fetch('SELECT * FROM '.$this->_referralTable.' WHERE referral_username=? ORDER BY id DESC',array($this->_referralUsername));
        return is_array($r)?$r:null;
    }

    public function getReferralListByStatus($code=0) {
        $r=$this->me->query_fetch('SELECT * FROM '.$this->_referralTable.' WHERE status=? ORDER BY id DESC',array((int)$code));
        return is_array($r)?$r:null;
    }

    public function getReferralCharacters($username) {
        $rows=$this->mu->query_fetch('SELECT '. _CLMN_CHR_NAME_ .' AS character_name FROM '. _TBL_CHR_ .' WHERE '. _CLMN_CHR_ACCID_ .'=? ORDER BY '. _CLMN_CHR_NAME_ .' ASC',array($username));
        if(!is_array($rows)) return array();
        return array_values(array_filter(array_map(function($r){return isset($r['character_name'])?trim((string)$r['character_name']):'';},$rows)));
    }

    public function inviteUrl($character) {
        $url=__REFERRALSYSTEM_HOME__.'invite/player/'.rawurlencode($character).'/';
        return class_exists('NexoraRealm') ? \NexoraRealm::appendToUrl($url,$this->realm) : $url;
    }

    public function checkPluginUsercpLinks() {
        $cfg=loadConfig('usercp'); if(!is_array($cfg)) $cfg=array();
        $links=array(); foreach($cfg as $row) if(isset($row['link'])) $links[]=$row['link'];
        foreach($this->_usercpmenu as $link) if(!in_array($link['link'],$links,true)) $cfg[]=$link;
        usort($cfg,function($a,$b){return (int)$a['order'] <=> (int)$b['order'];});
        $json=json_encode($cfg,JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        if($json===false || file_put_contents(__PATH_CONFIGS__.'usercp.json',$json."\n",LOCK_EX)===false) throw new \Exception('No se pudo actualizar el menú UserCP.');
    }

    private function loadConfig() {
        $path=self::configPath($this->realm);
        if(!is_file($path)) {
            $fallback=__PATH_REFERRALSYSTEM_ROOT__.'config.xml';
            if(is_file($fallback)) @copy($fallback,$path);
        }
        if(!is_file($path)) throw new \Exception(lang('referralsystem_error_2'));
        $xml=simplexml_load_file($path); if(!$xml) throw new \Exception(lang('referralsystem_error_2'));
        return array(
            'required_level'=>(int)$xml->required_level,
            'required_master_level'=>(int)$xml->required_master_level,
            'required_resets'=>(int)$xml->required_resets,
            'time_limit_days'=>(int)$xml->time_limit_days,
            'reward_credit_config'=>(int)$xml->reward_credit_config,
            'reward_credit_amount'=>(int)$xml->reward_credit_amount,
            'block_same_ip'=>(int)$xml->block_same_ip,
            'verification_hours'=>(int)$xml->verification_hours,
        );
    }

    private function ensureTable() {
        $exists=$this->me->query_fetch_single("SELECT name FROM sys.tables WHERE name = ?",array($this->_referralTable));
        if(!is_array($exists)) {
            $sql=file_get_contents(__PATH_REFERRALSYSTEM_ROOT__.'sql/WEBENGINE_REFERRALSYSTEM.txt');
            $sql=str_replace('{TABLE_NAME}',$this->_referralTable,$sql);
            if(!$this->me->query($sql)) throw new \Exception(lang('referralsystem_error_4',true));
            return;
        }
        // Non-destructive compatibility upgrades for older Referral System tables.
        if(!$this->columnExists('referred_ip')) $this->me->query('ALTER TABLE '.$this->_referralTable.' ADD referred_ip varchar(45) NULL');
        if(!$this->columnExists('rewarded_date')) $this->me->query('ALTER TABLE '.$this->_referralTable.' ADD rewarded_date datetime NULL');
    }

    private function columnExists($column) {
        $r=$this->me->query_fetch_single('SELECT COL_LENGTH(?,?) AS len',array('dbo.'.$this->_referralTable,$column));
        return is_array($r) && $r['len'] !== null;
    }

    private function ensureCron() {
        $primary=class_exists('NexoraRealm') ? \NexoraRealm::registrationPrimaryRealm() : $this->realm;
        $db=\Connection::Database('Platform',$primary);
        $row=$db->query_fetch_single('SELECT cron_id FROM '.WEBENGINE_CRON.' WHERE cron_file_run=?',array($this->_cronFile));
        if(is_array($row)) return;
        $path=__PATH_CRON__.$this->_cronFile; if(!is_file($path)) throw new \Exception(lang('referralsystem_error_5'));
        $affected=$db->query_affected('INSERT INTO '.WEBENGINE_CRON.' (cron_name,cron_file_run,cron_run_time,cron_status,cron_protected,cron_file_md5) VALUES (?,?,?,?,?,?)',array('Referral System - Nexora',$this->_cronFile,3600,1,0,md5_file($path)));
        if($affected!==1) throw new \Exception(lang('referralsystem_error_5'));
    }

    private function accountExists($username) {
        $row=$this->me->query_fetch_single('SELECT TOP 1 '. _CLMN_USERNM_ .' AS username FROM '. _TBL_MI_ .' WHERE '. _CLMN_USERNM_ .'=?',array($username));
        return is_array($row);
    }

    private function referrerCharacterValid() {
        $row=$this->mu->query_fetch_single('SELECT TOP 1 '. _CLMN_CHR_NAME_ .' AS n FROM '. _TBL_CHR_ .' WHERE '. _CLMN_CHR_NAME_ .'=? AND '. _CLMN_CHR_ACCID_ .'=?',array($this->_referralCharacter,$this->_referralUsername));
        return is_array($row);
    }

    private function sameIpAsReferrer($ip) {
        if($ip==='') return false;
        $row=$this->me->query_fetch_single('SELECT TOP 1 '. _CLMN_MS_IP_ .' AS ip FROM '. _TBL_MS_ .' WHERE '. _CLMN_MS_MEMBID_ .'=?',array($this->_referralUsername));
        return is_array($row) && isset($row['ip']) && trim((string)$row['ip'])!=='' && hash_equals(trim((string)$row['ip']),trim((string)$ip));
    }

    private function qualifyingCharacter($username) {
        $sql='SELECT TOP 1 c.'. _CLMN_CHR_NAME_ .' AS character_name,c.'. _CLMN_CHR_LVL_ .' AS level,c.'. _CLMN_CHR_RSTS_ .' AS resets,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_ .'=? AND c.'. _CLMN_CHR_LVL_ .'>=? AND c.'. _CLMN_CHR_RSTS_ .'>=? AND ISNULL(m.'. _CLMN_ML_LVL_ .',0)>=? '
            .'ORDER BY c.'. _CLMN_CHR_RSTS_ .' DESC,c.'. _CLMN_CHR_LVL_ .' DESC,ISNULL(m.'. _CLMN_ML_LVL_ .',0) DESC';
        $row=$this->mu->query_fetch_single($sql,array($username,(int)$this->config['required_level'],(int)$this->config['required_resets'],(int)$this->config['required_master_level']));
        return is_array($row)?(string)$row['character_name']:null;
    }

    private function rewardReferral($id,$configId,$amount) {
        $cfg=$this->platform->query_fetch_single('SELECT * FROM '.WEBENGINE_CREDITS_CONFIG.' WHERE config_id=?',array($configId));
        if(!is_array($cfg)) throw new \RuntimeException('Configuración de créditos inexistente.');
        foreach(array('config_table','config_credits_col','config_user_col') as $k) $this->assertIdentifier($cfg[$k]);
        if(!in_array($cfg['config_database'],array('MuOnline','Me_MuOnline'),true)) throw new \RuntimeException('Base de créditos no permitida.');
        $this->assertSameInstance($cfg['config_database']);

        $tx=\Connection::Database('Platform',$this->realm);
        $realmAccountDb=$this->qualifiedDb(\NexoraRealm::connection('Me_MuOnline',$this->realm)['database']);
        $platformDb=$this->qualifiedDb(\NexoraRealm::connection('Platform',$this->realm)['database']);
        $targetDb=$this->qualifiedDb(\NexoraRealm::databaseName($cfg['config_database'],$this->realm));
        $refTable=$realmAccountDb.'.[dbo].'.$this->qi($this->_referralTable);
        $creditTable=$targetDb.'.[dbo].'.$this->qi($cfg['config_table']);
        $creditLog=$platformDb.'.[dbo].'.$this->qi(WEBENGINE_CREDITS_LOGS);

        if(!$tx->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la recompensa de referido.');
        try {
            $row=$tx->query_fetch_single('SELECT * FROM '.$refTable.' WITH (UPDLOCK,HOLDLOCK) WHERE id=?',array($id));
            if(!is_array($row) || (int)$row['status']!==0 || empty($row['referred_complete_date'])) { $tx->rollBack(); return false; }
            if(!empty($cfg['config_checkonline']) && $this->accountOnline($row['referral_username'])) { $tx->rollBack(); return false; }
            $identifier=$this->rewardIdentifier($cfg,$row);
            if($identifier===null) throw new \RuntimeException('No se pudo resolver el destinatario de la recompensa.');
            $creditCol=$this->qi($cfg['config_credits_col']); $userCol=$this->qi($cfg['config_user_col']);
            $affected=$tx->query_affected('UPDATE '.$creditTable.' SET '.$creditCol.'=ISNULL('.$creditCol.',0)+? WHERE '.$userCol.'=?',array($amount,$identifier));
            if($affected!==1) throw new \RuntimeException('No se pudo acreditar la recompensa.');
            $marked=$tx->query_affected('UPDATE '.$refTable.' SET status=1,rewarded_date=CURRENT_TIMESTAMP WHERE id=? AND status=0',array($id));
            if($marked!==1) throw new \RuntimeException('No se pudo cerrar el referido; la recompensa fue revertida.');
            $logged=$tx->query_affected('INSERT INTO '.$creditLog.' (log_config,log_identifier,log_credits,log_transaction,log_date,log_inadmincp,log_module,log_ip) VALUES (?,?,?,?,?,?,?,?)',array($cfg['config_title'],(string)$identifier,$amount,'add',time(),0,'referral/reward','cron'));
            if($logged!==1) throw new \RuntimeException('No se pudo registrar la recompensa; la operación fue revertida.');
            if(!$tx->commit()) throw new \RuntimeException('No se pudo confirmar la recompensa.');
            if(class_exists('NexoraAudit')) \NexoraAudit::log('economy.referral_reward','account',$row['referral_username'],null,array('referred'=>$row['referred_username'],'reward'=>$amount),$this->realm);
            return true;
        } catch(\Throwable $e) {
            if($tx->inTransaction()) $tx->rollBack();
            throw $e;
        }
    }

    private function rewardIdentifier($cfg,$row) {
        switch($cfg['config_user_col_id']) {
            case 'username': return $row['referral_username'];
            case 'character': return $row['referral_character'];
            case 'userid':
                $r=$this->me->query_fetch_single('SELECT TOP 1 '. _CLMN_MEMBID_ .' AS v FROM '. _TBL_MI_ .' WHERE '. _CLMN_USERNM_ .'=?',array($row['referral_username']));
                return is_array($r)?$r['v']:null;
            case 'email':
                $r=$this->me->query_fetch_single('SELECT TOP 1 '. _CLMN_EMAIL_ .' AS v FROM '. _TBL_MI_ .' WHERE '. _CLMN_USERNM_ .'=?',array($row['referral_username']));
                return is_array($r)?$r['v']:null;
        }
        return null;
    }

    private function accountOnline($username) {
        $r=$this->me->query_fetch_single('SELECT TOP 1 '. _CLMN_CONNSTAT_ .' AS online FROM '. _TBL_MS_ .' WHERE '. _CLMN_MS_MEMBID_ .'=?',array($username));
        return is_array($r) && (int)$r['online']===1;
    }

    private function assertSameInstance($logical) {
        $a=\NexoraRealm::connection('Platform',$this->realm); $b=\NexoraRealm::connection($logical,$this->realm);
        foreach(array('host','port','username','driver') as $k) if((string)$a[$k] !== (string)$b[$k]) throw new \RuntimeException('La recompensa atómica requiere las bases Nexora en la misma instancia SQL.');
        $m=\NexoraRealm::connection('Me_MuOnline',$this->realm);
        foreach(array('host','port','username','driver') as $k) if((string)$a[$k] !== (string)$m[$k]) throw new \RuntimeException('La recompensa atómica requiere las bases Nexora en la misma instancia SQL.');
    }
    private function assertIdentifier($v){ if(!preg_match('/^[A-Za-z0-9_]+$/D',(string)$v)) throw new \RuntimeException('Identificador SQL no permitido.'); }
    private function qi($v){ $this->assertIdentifier($v); return '['.$v.']'; }
    private function qualifiedDb($v){ $this->assertIdentifier($v); return '['.$v.']'; }
}
