<?php

use idoit\Component\Location\Coordinate;

define("IDOIT_C__DAO_RESULT_TYPE_ARRAY", 1);
define("IDOIT_C__DAO_RESULT_TYPE_ROW", 2);
define("IDOIT_C__DAO_RESULT_TYPE_ALL", 3);

/**
 * i-doit
 *
 * DAO Base classes.
 *
 * @package     i-doit›
 * @subpackage  Components
 * @author      Andre Woesten <awoesten@i-doit.de>
 * @version     Dennis Stücken <dstuecken@i-doit.org>
 * @version     0.9
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 *
 * This component is the client to the database component.
 */
class isys_component_dao extends isys_component
{
    /**
     * @var isys_component_dao[]
     */
    protected static $instances = [];

    /**
     * Database component.
     *
     * @var isys_component_database
     */
    protected $m_db;

    /**
     * Last known error
     *
     * @var string
     */
    protected $m_last_error;

    /**
     * The last executed query.
     *
     * @var  string
     */
    protected $m_last_query;

    /**
     * Return instance of current class
     *
     * @return static
     */
    public static function factory($p_database)
    {
        return new static($p_database);
    }

    /**
     * Return singleton instance of current class
     *
     * @param isys_component_database $p_database
     *
     * @return static
     */
    public static function instance(isys_component_database $p_database)
    {
        $db_name = $p_database->get_db_name();
        $class = get_called_class();

        if (!isset(self::$instances[$class . ':' . $db_name])) {
            self::$instances[$class . ':' . $db_name] = new $class($p_database);
        }

        return self::$instances[$class . ':' . $db_name];
    }

    /**
     * Return last insert id.
     *
     * @return  integer
     */
    public function get_last_insert_id()
    {
        // Retrieving last insert id from MySQL instead of the database driver
        // since we are experiencing several issues with insert id being "0".
        return $this->retrieve('SELECT LAST_INSERT_ID() as id;')
            ->get_row_value('id');
        //return $this->m_db->get_last_insert_id();
    }

    /**
     * Executes $p_query and returns DAO result or NULL on failure.
     * This is only for read access! For write access use self::update().
     *
     * @param   string $p_query
     *
     * @throws  isys_exception_database
     * @return  isys_component_dao_result
     */
    public function retrieve($p_query)
    {
        try {
            $this->m_last_query = $p_query;

            if ($this->m_db) {
                return new isys_component_dao_result($this->m_db, $this->m_db->query($p_query, false), $p_query);
            } else {
                throw new isys_exception_database("Retrieve failed. Database component not loaded!");
            }
        } catch (isys_exception_database $e) {
            throw $e;
        }
    }

    /**
     * Executes $p_query and returns a boolean result. This is for write access (UPDATE, INSERT etc.) only.
     * All write queries have to be executed in a transaction, so we need to start one if there is noone running.
     *
     * @param   string $p_query
     *
     * @throws  isys_exception_dao
     * @return  boolean
     */
    public function update($p_query)
    {
        if ($this->m_db->is_connected()) {
            $this->m_last_query = $p_query;
            $l_ret = $this->m_db->query($p_query) or $this->m_last_error = $this->m_db->get_last_error_as_string();

            if ($l_ret) {
                unset($p_query);

                return $l_ret;
            } else {
                $l_mailto_support = isys_helper_link::create_mailto('support@i-doit.org', ['subject' => 'i-doit Exception: ' . $this->m_last_error]);

                throw new isys_exception_dao(nl2br("<strong>MySQL-Error</strong>: " . $this->m_last_error . "\n\n" . "<strong>Query</strong>: " . $this->m_last_query .
                    "\n\n" . "Try <a href=\"./updates\">updating</a> your database. If this error occurs permanently, contact the i-doit team, please. " .
                    "(<a href=\"http://i-doit.org/forum\" target=\"_new\">http://i-doit.org/forum</a>, " . "<a href=\"" . $l_mailto_support . "\">support@i-doit.org</a>)"));
            }
        }

        return false;
    }

    /**
     * Change transaction behaviour
     *
     * @param $bool
     *
     * @return $this
     */
    public function set_autocommit($bool)
    {
        $this->m_db->set_autocommit($bool);

        return $this;
    }

    /**
     * Begins a transaction.
     */
    public function begin_update()
    {
        return $this->m_db->begin();
    }

    /**
     * After you made some update()-queries, this function will commit the transaction.
     *
     * @return  boolean
     */
    public function apply_update()
    {
        return $this->m_db->commit();
    }

    /**
     * Use this, if you want to rollback a transaction
     */
    public function cancel_update()
    {
        return $this->m_db->rollback();
    }

    /**
     * Returns how many rows were affected after an update.
     *
     * @return  integer
     */
    public function affected_after_update()
    {
        return $this->m_db->affected_rows();
    }

    /**
     * Returns the last query string.
     *
     * @return  string
     */
    public function get_last_query()
    {
        return $this->m_last_query;
    }

    /**
     * Returns the associated database component.
     *
     * @return  isys_component_database
     */
    public function get_database_component()
    {
        return $this->m_db;
    }

    /**
     * Convert id in sql compliant syntax depending on the value of $p_value.
     * It is used almost everywhere in i-doit.
     *
     * @param   mixed $p_value
     *
     * @return  boolean
     */
    public function convert_sql_id($p_value)
    {
        $l_id = (int)$p_value;

        if ($l_id <= 0) {
            return "NULL";
        }

        return $l_id;
    }

    /**
     * Converts a numeric value or a string to a integer.
     *
     * @param   mixed $p_value Can be something numeric or a string.
     *
     * @return  integer
     */
    public function convert_sql_int($p_value)
    {
        if ($p_value === null) {
            return "NULL";
        }

        return (int)$p_value;
    }

    /**
     * @param Coordinate $p_coord
     *
     * @return string
     */
    public function convert_sql_point($p_coord)
    {
        if (is_a($p_coord, 'idoit\Component\Location\Coordinate')) {
            return "POINT(" . $this->convert_sql_text($p_coord->getLatitude()) . ", " . $this->convert_sql_text($p_coord->getLongitude()) . ")";
        } else {
            return "NULL";
        }
    }

    /**
     * Convert text in SQL compliant syntax depending on system settings it is used in the methode save_element.
     *
     * @param   string $p_value
     *
     * @return  string
     * @author  Niclas Potthast <npotthast@i-doit.info>
     * @author  Dennis Stücken <dstuecken@i-doit.org>
     * @todo    Do something about un-escaped single- and double-quotes.
     */
    public function convert_sql_text($p_value)
    {
        return "'" . $this->m_db->escape_string($p_value) . "'";
    }

    /**
     * Method for converting a numeric value to a float-variable as SQL understands it.
     *
     * @param   mixed $p_value Can be a string or anything numeric.
     *
     * @return  string
     * @author  Dennis Stücken <dstuecken@i-doit.org>
     * @author  Leonard Fischer <lfischer@i-doit.org>
     * @uses    isys_helper::filter_number()
     */
    public function convert_sql_float($p_value)
    {
        // @see  ID-5297  Replaced the "is_numeric" check with "trim + strlen" because values like "199,99" are not numeric.
        // Also we can not use "empty" because 0 would be considered empty.
        if (is_null($p_value) || strlen(trim($p_value)) === 0) {
            return "NULL";
        }

        return "'" . isys_helper::filter_number($p_value) . "'";
    }

    /**
     * Method for avoiding SQL to saving an empty date string.
     *
     * @param   string $p_strDate
     *
     * @return  string
     * @author  Niclas Potthast <npotthast@i-doit.org>
     * @author  Dennis Stücken <dstuecken@i-doit.org>
     */
    public function convert_sql_datetime($p_strDate)
    {
        if (!empty($p_strDate) && $p_strDate != '1970-01-01' && $p_strDate != '0000-00-00') {
            // ID-1933  Because of the data type DATE the "NOW()" function will not work, so we need "CURDATE()".
            if ($p_strDate == "NOW()" || $p_strDate == "CURDATE()") {
                return $p_strDate;
            } else {
                if (is_numeric($p_strDate)) {
                    return "'" . date("Y-m-d H:i:s", (int)$p_strDate) . "'";
                } else {
                    $l_date = strtotime($p_strDate);

                    if ($l_date === false) {
                        return 'NULL';
                    }

                    return "'" . date("Y-m-d H:i:s", $l_date) . "'";
                }
            }
        } else {
            return "NULL";
        }
    }

    /**
     * Method for converting a boolean value to something, SQL can understand.
     *
     * @param   mixed $p_value Can be a boolean, (numeric) string or integer - Should be true (bool), 1 or "1" (NOT "false" or "true").
     *
     * @return  integer
     * @author  Dennis Stücken <dstuecken@i-doit.org>
     * @author  Leonard Fischer <lfischer@i-doit.org>
     */
    public function convert_sql_boolean($p_value)
    {
        return (int)!!$p_value;
    }

    /**
     * Prepares a MySQL conform IN() condition.
     *
     * @param   array   $p_array
     * @param   boolean $p_negate
     *
     * @return  string
     */
    public function prepare_in_condition(array $p_array, $p_negate = false)
    {
        $l_items = [];

        if (count($p_array)) {
            foreach ($p_array as $l_item) {
                if (!is_numeric($l_item)) {
                    if (defined($l_item)) {
                        $l_item = constant($l_item);
                    } else {
                        continue;
                    }
                }

                $l_items[] = $this->convert_sql_int($l_item);
            }

            if (count($l_items)) {
                return (($p_negate) ? "NOT " : "") . "IN(" . implode(',', $l_items) . ")";
            }
        }

        return "IS NULL";
    }

    /**
     * Constructor. Assigns database component.
     *
     * @param   isys_component_database $p_db
     *
     * @author  Dennis Stücken <dstuecken@i-doit.org>
     */
    public function __construct(isys_component_database $p_db)
    {
        $this->m_db = $p_db;
        $this->m_last_query = "";
    }
}
