<?php

/*
 * @component UserPoints
 * @copyright Copyright (C) 2015-2022 Bernard Gilly - Adrien Roussel - Martin Brampton
 * @license : GNU/GPL
 * @Website : https://remository.com
 */

namespace BlackSheepResearch\Component\UserPoints\Site\Problems;

defined('_JEXEC') or die;

use Joomla\CMS\Factory;
use Joomla\CMS\Language\Text;

class SQL {

    protected static $instance = null;
    protected static $datetime = '';
    protected static $date = '';
    protected static $log_file = '';
    protected $database = null;
    protected $sql = '';
    protected $debug = false;

    protected function __construct() {
        $this->database = Factory::getDbo();
        if (!self::$datetime) {
            $this->database->setQuery("SELECT NOW()");
            self::$datetime = $this->database->loadResult();
            self::$date = substr(self::$datetime, 0, 10);
        }
        $this->setLogging();
    }

    public static function getInstance() {
        return (self::$instance instanceof self) ? self::$instance : (self::$instance = new self());
    }

    public function doSQL($sql) {
        $this->setQuery($sql);
        $this->database->execute();
    }

    public function doSQLget(string $sql, string $classname = 'stdClass', string $key = '', array $bind = [], int $max = 0): array {
        $rows = $this->SQLgetrows($sql, $bind);
        return $this->rowsToClass($rows, $classname, $key, $max);
    }

    private function SQLgetrows(string $sql, array $bind = []): array {
        if ($sql) {
            $this->setQuery($sql);
            $this->bind($bind);
            return (array) $this->loadObjectList();
        } else {
            return [];
        }
    }

    private function bind(array $bind) {
        if (count ($bind)) {
            if (3 == count($bind[0])) {
                foreach ($bind as $i => $bindrow) {
                    $marker[] = $bindrow[0];
                    $value[] = $bindrow[1] ?? $bind[$i - 1][1];
                    $type[] = $bindrow[2] ?? $bind[$i - 1][2];
                }
                $this->database->getQuery()->bind($marker, $value, $type);
            } else {
                throw new \Exception(Text::_('LIB_BLACKSHEEP_DATABASE_BIND_INVALID_PARAMETERS'));
            }
        }
    }

    private function rowsToClass(array $rows, string $classname = 'stdClass', string $key = '', int $max = 0): array {
        $result = [];
        if ('stdClass' == $classname) {
            return $rows;
        }
        if (count($rows)) {
            foreach ($rows as $row) {
                $next = new $classname();
                if (!isset($objfields)) {
                    $objfields = array_keys(get_object_vars($row));
                }
                foreach ($objfields as $field) {
                    $next->$field = $row->$field;
                }
                if (method_exists($next, 'onDataLoad')) {
                    $next->onDataLoad();
                }
                if ($key) {
                    $result[$row->$key] = $next;
                } else {
                    $result[] = $next;
                }
                if ($max AND count($result) >= $max) {
                    break;
                }
            }
        }
        return $result;
    }

    public function setQuery($sql, $offset = 0, $limit = 0) {
        $this->sql = $sql;
        if (self::$log_file) {
            $this->logSQL();
        }
        $this->database->setQuery($sql, $offset, $limit);
    }

    public function loadObjectList() {
        try {
            return $this->database->loadObjectList();
        } catch (Exception $ex) {
            $this->logSQL(true, $ex);
            return null;
        }
    }

    public function loadObject() {
        try {
            return $this->database->loadObject();
        } catch (Exception $ex) {
            $this->logSQL(true, $ex);
            return null;
        }
    }

    public function loadResult() {
        try {
            return $this->database->loadResult();
        } catch (Exception $ex) {
            $this->logSQL(true, $ex);
            return null;
        }
    }

    public function loadColumn() {
        try {
            return $this->database->loadColumn();
        } catch (Exception $ex) {
            $this->logSQL(true, $ex);
            return null;
        }
    }

    public function updateObject($table, $object, $keyname) {
        try {
            return $this->database->updateObject($table, $object, $keyname);
        } catch (Exception $ex) {
            $this->logSQL(true, $ex);
            return null;
        }
    }

    public function insertObject($table, $object, $keyname) {
        try {
            return $this->database->insertObject($table, $object, $keyname);
        } catch (Exception $ex) {
            $this->logSQL(true, $ex);
            return null;
        }
    }

    public function execute() {
        try {
            return $this->database->execute();
        } catch (Exception $ex) {
            $this->logSQL(true, $ex);
            return null;
        }
    }

    public function quote($value) {
        return $this->database->quote($value);
    }

    public function escape(string $text, bool $extra = false): string {
        return $this->database->escape($text, $extra);
    }

    public function getNullDate(): string {
        return self::nulldatetimeSQL();
    }

    public function getTableList() {
        $this->database->setQuery('SHOW tables');
        return $this->database->loadResultArray();
    }

    public function getVersion () {
        return $this->database->getVersion();
    }

    public static function datetimeSQL() {
        self::getInstance();
        return self::$datetime;
    }

    public static function dateSQL() {
        self::getInstance();
        return self::$date;
    }

    public static function nulldatetimeSQL() {
        return '1000-01-01 00:00:00';
    }

    public static function nulldateSQL() {
        return '1000-01-01';
    }

    public static function unixtime() {
        $db = self::getInstance();
        $db->setQuery("SELECT UNIX_TIMESTAMP(NOW())");
        return $db->loadResult();
    }

    public static function nicetime($date) {
        $unix_date = strtotime($date);
        if (false !== $unix_date) {
            $period = ['singular' =>
                [Text::_('AUP_SECOND'), Text::_('AUP_MINUTE'), Text::_('AUP_HOUR'), Text::_('AUP_DAY'), Text::_('AUP_WEEK'), Text::_('AUP_MONTH'), Text::_('AUP_YEAR'), Text::_('AUP_DECADE')],
                'plural' =>
                [Text::_('AUP_SECONDS'), Text::_('AUP_MINUTES'), Text::_('AUP_HOURS'), Text::_('AUP_DAYS'), Text::_('AUP_WEEKS'), Text::_('AUP_MONTHS'), Text::_('AUP_YEARS'), Text::_('AUP_DECADES')]];
            $lengths = array("60", "60", "24", "7", "4.35", "12", "10");
            $diff = self::unixtime() - $unix_date;
            $tense = ($diff < 0) ? Text::_('AUP_FROM_NOW') : Text::_('AUP_AGO');
            $difference = abs($diff);
            for ($j = 0; $difference >= $lengths[$j] && $j < count($lengths) - 1; $j++) {
                $difference /= $lengths[$j];
            }
            $time_units = (int) round($difference);
            return \sprintf($tense, "$time_units {$period[1 == $time_units ? 'singular' : 'plural'][$j]}");
        }
    }

    private function setLogging() {
        if (Factory::getConfig()->get('debug') AND !self::$log_file) {
            $log_path = Factory::getConfig()->get('log_path');
            self::$log_file = "$log_path/BlackSheepUserPointsLog.php";
            if (file_exists(self::$log_file) AND filesize(self::$log_file) > 1000000) {
                $oldfile = "$log_path/old.BlackSheepUserPointsLog.php";
                if (file_exists($oldfile)) {
                    unlink($oldfile);
                }
                rename(self::$log_file, $oldfile);
            }
        }
        if (self::$log_file) {
            $time = self::$datetime;
            file_put_contents(self::$log_file, "========== Start of Request $time ==========\n", FILE_APPEND);
        }
    }

    private function logSQL($failed = false, $ex = null) {
        if ($failed) {
            $message = Text::_('AUP_DATABASE_ERROR');
            Factory::getApplication()->enqueueMessage($message);
            if ($ex instanceof \Exception) {
                $message .= $ex->getMessage();
            }
            file_put_contents(self::$log_file, $message . "\n", FILE_APPEND);
        }
        if ($failed AND self::$log_file) {
            file_put_contents(self::$log_file, self::trace() . "\n", FILE_APPEND);
        } elseif (self::$log_file) {
            file_put_contents(self::$log_file, $this->sql . "\n", FILE_APPEND);
        }
    }

    public static function trace() {
        static $counter = 0;
        $text = '';
        foreach (debug_backtrace() as $back) {
            if (isset($back['file']) AND $back['file']) {
                $text .= "\n" . $back['file'] . ':' . $back['line'];
            }
        }
        $counter++;
        if (1000 < $counter) {
            die('Program killed - Probably looping - ' . nl2br($text));
        }
        return $text;
    }
}
