<?php

namespace MartianooCore\Api\Database;

/**
 * This class represents a database table.
 * @package MartianooCore\Api\Database
 */
class Model
{
    /**
     * The table name. It's overwritten by extending classes.
     * @var string
     */
    protected static $tableName;

    /**
     * A real database table name.
     * @var string
     */
    private $childTableName = "";

    /**
     * It can be INSERT, SELECT, UPDATE, DELETE. The default is INSERT
     * @var string
     */
    private $queryType = "INSERT";

    /**
     * Used to verify if the where has been called.
     * @var bool
     */
    private $whereCalled = false;

    /**
     * Used to verify if the groupBy method has been called.
     * @var bool
     */
    private $groupByCalled = false;

    /**
     * Used to verify if the orderBy method has been called.
     * @var bool
     */
    private $orderByCalled = false;

    /**
     * A comma-separated list of columns to include in the SELECT query.
     * @var string
     */
    private $selectColumns = "";

    /**
     * The WHERE clause of a sql statement
     * @var string
     */
    private $sqlWherePart = "";

    /**
     * The values of columns involved in the where clause of a sql statement
     * @var array
     */
    private $sqlWhereValues = [];

    /**
     * The GROUP BY clause of a sql statement
     * @var string
     */
    private $sqlGroupByPart = '';

    /**
     * The ORDER BY clause of a sql statement
     * @var string
     */
    private $sqlOrderByPart = '';

    private $tableInfos = [];

    private $sqlSelectColumns = "";

    private $primaryKey = "";

    private $nbOfpages = 0;


    /**
     * Creates an instance or throws an exception  in the following cases:
     *
     * * **$tableName** is in invalid; the exception's the message is **Invalid table name**.
     * * No connection the database was found; the exception's message is **Connection to db failed**
     * * An error occurred while setting the table's details; the exception's message corresponds to the error message.
     * This constructor should not be called directly. But it's recommended to use the
     * select, delete and update methods as entry points.
     * @throws \Exception
     */
    function __construct()
    {
        $childClassProperties = get_class_vars(get_class($this));

        if (! isset($childClassProperties["tableName"]) || ! is_string($childClassProperties["tableName"]) ||
            strlen($childClassProperties["tableName"]) == 0) {
            throw new \Exception("Invalid table name");
        }

        $this->childTableName = $childClassProperties["tableName"];
        $this->setTableInfos();
    }

    /**
     * Creates the entry point to a select query on the underlying database table; or throws an exception in the following cases:
     *
     * * Function is called on the Model class; The exception's message is **Cannot be called on Model**
     * * **$columns** is invalid; The exception's message is **Invalid columns**
     * @param string $columns a comma-separated list of columns to include in the SELECT query. The default is *****
     * corresponding to all columns.
     * @return Model
     * @throws \Exception
     */
    public static function select($columns = "*")
    {
        $calledClass = get_called_class();
        if (! is_subclass_of($calledClass, self::class)) {
            throw new \Exception("Cannot be called on Model");
        }

        $model = new $calledClass();
        $model->setQueryType("SELECT");
        $model->setSelectColumns($columns);
        return $model;
    }


    /**
     * * Creates the entry point to a delete query on the underlying database table; or throws an exception in the following cases:
     *
     * * Function is called on the Model class; The exception's message is **Cannot be called on Model**
     * * **$columns** is invalid; The exception's message is **Invalid columns**
     * @return Model
     * @throws \Exception
     */
    public static function delete()
    {
        $calledClass = get_called_class();
        if (! is_subclass_of($calledClass, self::class)) {
            throw new \Exception("Cannot be called on Model");
        }

        $model = new $calledClass();
        $model->setQueryType("DELETE");
        return $model;
    }


    /**
     * Executes a SQL statement built with calls to either the delete or update methods. Throws an exception in the following cases:
     *
     * * method is not called after the delete and either where or orWhere methods; the exception's message is **Invalid call**
     * * No connection the database was found; the exception's message is **Connection to db failed**
     * * An error occurred while executing the statement; the exception's message corresponds to the error message.
     * ######Example
     * ```
     * class DummyModel extends Model {
     *  protected static $tableName = "dummy_table";
     * }
     *
     * DummyModel::delete()->where("id", 77)->execute(); // Good call
     * DummyModel::delete()->execute(); // Bad call
     * ```
     *
     * @throws \Exception
     */
    public function execute()
    {
        if (! ($this->queryType == "DELETE" || $this->queryType == "UPDATE") || strlen($this->sqlWherePart) == 0) {
            throw new \Exception("Invalid call");
        }

        global $wpdb;
        if (is_null($wpdb) || ! $wpdb->check_connection()) {
            throw new \Exception("Connection to db failed");
        }

        if ($this->queryType == "DELETE") {
            $sql = $this->buildDeleteSql($wpdb);
        }

        $wpdb->hide_errors();
        $wpdb->query($sql);

        if (strlen($wpdb->last_error) > 0) {
            throw new \Exception($wpdb->last_error);
        }
    }


    /**
     * Appends the **WHERE** or **AND** keywords to  SELECT, UPDATE and DELETE queries and returns a model (for chained calls).
     * Throws an exception if the method was not called after any of select, delete or update methods;
     * the exception's message is **Invalid call**
     * @param string $columnName The column name
     * @param string $operator the operator to filter data. The default value is the **=** operator
     * @param string $value the value to be used to filter data
     * @return Model
     * @throws \Exception
     */
    public function where($columnName, $operator = "=", $value)
    {
        if ($this->queryType == "INSERT" || $this->groupByCalled) {
            throw new \Exception("Invalid call");
        }

        if (! $this->whereCalled) {
            if (is_string($value) || preg_match('/[^0-9]/', $value)) {
                $this->sqlWherePart .= "WHERE LOWER(" . $columnName . ") " . $operator . " %s";
                $this->sqlWhereValues[] = strtolower($value);
            } else {
                $this->sqlWherePart .= "WHERE " . $columnName . " " . $operator . " %s";
                $this->sqlWhereValues[] = $value;
            }
            $this->whereCalled = true;
        } else {
            if (is_string($value) || preg_match('/[^0-9]/', $value)) {
                $this->sqlWherePart .= " AND LOWER(" . $columnName . ") " . $operator . " %s";
                $this->sqlWhereValues[] = strtolower($value);
            } else {
                $this->sqlWherePart .= " AND " . $columnName . " " . $operator . " %s";
                $this->sqlWhereValues[] = $value;
            }
        }

        return $this;
    }

    /**
     * Appends the **OR** keyword to SELECT, UPDATE and DELETE queries and returns a model (for chained calls).
     * Throws an exception if the method was not called after any of select, delete ,update methods or groupBy;
     * the exception's message is **Invalid call**
     * @param string $columnName The column name
     * @param string $operator the operator to filter data. The default value is the **=** operator
     * @param string $value the value to be used to filter data
     * @return Model
     * @throws \Exception
     */
    public function orWhere($columnName, $operator = "=", $value)
    {
        if ($this->queryType == "INSERT" || $this->groupByCalled) {
            throw new \Exception("Invalid call");
        }

        if (! $this->whereCalled) {
            $this->where($columnName, $value, $operator);
        } else {
            if (is_string($value) || preg_match('/[^0-9]/', $value)) {
                $this->sqlWherePart .= " OR LOWER(" . $columnName . ") " . $operator . " %s";
                $this->sqlWhereValues[] = strtolower($value);
            } else {
                $this->sqlWherePart .= " OR " . $columnName . " " . $operator . " %s";
                $this->sqlWhereValues[] = $value;
            }
        }

        return $this;
    }

    /**
     * Appends the **GROUP BY** keyword to a SQL statement. It throws an exception in case the method was previously called;
     * the exception's message is **Invalid call**
     * @param string $columnName the column to be used in the group by clause
     * @return Model
     * @throws \Exception
     */
    public function groupBy($columnName)
    {
        if ($this->groupByCalled) {
            throw new \Exception("Invalid call");
        }

        $this->sqlGroupByPart = "GROUP BY " . $columnName;

        return $this;
    }

    public function orderBy($columnName, $order = "ASC")
    {
        if ($order != 'ASC' && $order != 'DESC') {
            throw new \Exception("Only ASC and DESC are allowed");
        }

        if ($this->orderByCalled) {
            throw new \Exception("Invalid call");
        }

        $this->sqlOrderByPart = "ORDER BY " . $columnName . " $order";

        return $this;
    }

    /**
     * Returns an empty array or one containing instances of the Row class. This method should be called after the a SELECT
     * SQL statement has been constructed with calls to the select, where, orWhere methods. It returns all the rows found
     * as opposed to the paginate method.
     * It throws an exception in the following cases:
     *
     * * No connection the database was found; the exception's message is **Connection to db failed**
     * * An error occurred while retrieving data from the database; the exception's message corresponds to the error message.
     * @return array
     * @throws \Exception
     */
    public function getRows()
    {
        global $wpdb;
        if ($this->queryType == "SELECT") {
            $queryRows = $this->getSelectQueryRows($wpdb);
            $returnedRows = [];
            if (is_array($queryRows) && count($queryRows) > 0) {
                foreach ($queryRows as $queryRowInfos) {
                    $this->addRowToReturnedArray($queryRowInfos, $returnedRows);
                }
                return $returnedRows;
            }
        }

        return [];
    }

    /**
     * Returns an instance of Row or null. This method should be called after the a SELECT
     * SQL statement has been constructed with calls to the select, where, orWhere methods. It returns a single row among
     * those found as opposed to the getRows method.
     * @return Row|null
     */
    public function getOneRow()
    {
        global $wpdb;
        if ($this->queryType == "SELECT") {
            $queryRows = $this->getSelectQueryRows($wpdb);

            if (is_array($queryRows) && count($queryRows) > 0) {
                $currentRowInfos = [];
                $queryRowInfos = $queryRows[0];
                foreach ($queryRowInfos as $columnName => $columnValue) {
                    $key = "";
                    if ($columnName == $this->primaryKey) {
                        $key = "pri";
                    }
                    $currentRowInfos[] = [
                        "field" => $columnName,
                        "key" => $key,
                        "value" => $columnValue,
                    ];
                }
                return new Row($this->childTableName, $currentRowInfos);
            }
        }

        return null;
    }

    /**
     * Returns an empty array or one containing instances of the Row class. This method should be called after a SELECT
     * SQL statement has been constructed with calls to the select, where, orWhere methods. It returns a paginated-version of the rows found
     * as opposed to the getRows method. The array returned in called a page and contains a precise number of rows.
     * It throws an exception in the following cases:
     *
     * * No connection the database was found; the exception's message is **Connection to db failed**
     * * An error occurred while retrieving data from the database; the exception's message corresponds to the error message.
     * @param $totalPerPage
     * @param int $page
     * @return array
     */
    public function paginate($totalPerPage, $page = 1)
    {
        if (! is_int($totalPerPage) || $totalPerPage <= 0) {
            return [];
        }

        global $wpdb;
        if ($this->queryType == "SELECT") {
            $queryRows = $this->getSelectQueryRows($wpdb);


            $returnedRows = [];
            if (is_array($queryRows) && count($queryRows) > 0) {
                if (isset($_GET["_page"]) && preg_match('/\d/', $_GET["_page"])) {
                    $currentPage = (integer) $_GET["_page"];
                } else {
                    $currentPage = (integer) $page;
                }

                if ($currentPage >= 1) {
                    $arraySize = count($queryRows);
                    $nbOfPages = (integer) ceil($arraySize / $totalPerPage);

                    if ($currentPage > $arraySize || $currentPage > $nbOfPages) {
                        return [];
                    }

                    $startIndex = ($currentPage - 1) * $totalPerPage;
                    $endIndex = $startIndex + ($totalPerPage - 1);
                    for ($currentIndex = $startIndex; ($currentIndex <= $endIndex && $currentIndex <= ($arraySize - 1)); $currentIndex++) {
                        $queryRowInfos = $queryRows[$currentIndex];
                        $this->addRowToReturnedArray($queryRowInfos, $returnedRows);
                    }
                    $this->nbOfpages = $nbOfPages;
                }
                return $returnedRows;
            }
        }

        return [];
    }

    /**
     * Returns the number of pages obtained after calling the paginate method. The default is number is 0.
     * @return int
     */
    public function getNbOfpages()
    {
        return $this->nbOfpages;
    }

    public function getPaginationHtml($url)
    {
        $nbOfPages = $this->getNbOfpages();
        $baseUrl = $url;
        require_once __DIR__ . '/list-paginator.php';
    }


    /**
     * Adds a new row to the underlying table. Returns true on success or throws an exception in the following cases:
     *
     * * method is called after calls to either select or delete methods; the exception's message is **Invalid call**
     * * an error occurs while saving the data; the exception's message contains the error message.
     * @param null $rowObject An injected Row object, used mainly for testing purposes.
     * @return bool
     * @throws \Exception
     */
    public function save($rowObject = null)
    {
        if ($this->queryType != "INSERT") {
            throw new \Exception("Invalid call");
        }

        if (is_null($rowObject)) {
            $row = new Row($this->childTableName, $this->copyInsertValues());
        } else {
            $row = $rowObject;
        }

        try {
            $row->save();
            return true;
        } catch (\Exception $exception) {
            throw new \Exception($exception->getMessage());
        }
    }

    /**
     * @throws \Exception
     */
    public static function flush()
    {
        $calledClass = get_called_class();
        if (! is_subclass_of($calledClass, self::class)) {
            throw new \Exception("Cannot be called on Model");
        }

        $model = new $calledClass();

        global $wpdb;
        if (is_null($wpdb) || ! $wpdb->check_connection()) {
            throw new \Exception("Connection to db failed");
        }

        $sql = "TRUNCATE TABLE " . $model->childTableName . ";";

        $wpdb->hide_errors();
        $wpdb->query($sql);

        if (strlen($wpdb->last_error) > 0) {
            throw new \Exception($wpdb->last_error);
        }
    }


    /**
     * @param $queryType
     * @throws \Exception
     * @codeCoverageIgnore
     */
    protected function setQueryType($queryType)
    {
        if (! ($queryType == "INSERT" || $queryType == "SELECT" || $queryType == "DELETE" || $queryType == "UPDATE")) {
            throw new \Exception("Invalid query type");
        }

        $this->queryType = $queryType;
    }

    protected function setSelectColumns($columns)
    {
        if (! is_string($columns) || strlen($columns) == 0) {
            throw new \Exception("Invalid columns");
        }

        $this->selectColumns = $columns;
    }

    private function setTableInfos()
    {
        global $wpdb;
        if (is_null($wpdb) || ! $wpdb->check_connection()) {
            throw new \Exception("Connection to db failed");
        }

        if (count($this->tableInfos) == 0) {
            $wpdb->hide_errors();
            $sql = "DESCRIBE " . $this->childTableName;
            $tableInfos = $wpdb->get_results($sql);

            if (strlen($wpdb->last_error) == 0) {
                foreach ($tableInfos as $columnInfos) {
                    if (strtolower($columnInfos->Key) == "pri") {
                        $this->primaryKey = $columnInfos->Field;
                    }

                    $this->tableInfos[$columnInfos->Field] = [
                        "field" => $columnInfos->Field,
                        "key" => strtolower($columnInfos->Key),
                        "value" => "",
                    ];
                }
            } else {
                throw new \Exception($wpdb->last_error);
            }
        }
    }

    private function buildSelectSql($wpdb)
    {
        $sql = $this->queryType . " " . $this->selectColumns . " FROM " . $this->childTableName;
        if (strlen($this->sqlWherePart) > 0) {
            $sql .= " " . $this->sqlWherePart;
            $sql = $wpdb->prepare($sql, $this->sqlWhereValues);
        }

        if (strlen($this->sqlGroupByPart) > 0) {
            $sql .= " " . $this->sqlGroupByPart . "";
        }

        if (strlen($this->sqlOrderByPart) > 0) {
            $sql .= " " . $this->sqlOrderByPart . "";
        }

        $sql .= ";";
        //var_dump($sql);
        return $sql;
    }

    private function addRowToReturnedArray($queryRowInfos, &$returnedRows)
    {
        $currentRowInfos = [];
        foreach ($queryRowInfos as $columnName => $columnValue) {
            $key = "";
            if ($columnName == $this->primaryKey) {
                $key = "pri";
            }
            $currentRowInfos[] = [
                "field" => $columnName,
                "key" => $key,
                "value" => $columnValue,
            ];
        }
        $returnedRows[] = new Row($this->childTableName, $currentRowInfos);
    }

    /**
     * @param $wpdb
     * @return mixed
     * @throws \Exception
     * @codeCoverageIgnore
     */
    private function getSelectQueryRows($wpdb)
    {
        if (is_null($wpdb) || ! $wpdb->check_connection()) {
            throw new \Exception("Connection to db failed");
        }

        $sql = $this->buildSelectSql($wpdb);
        $wpdb->hide_errors();
        $queryRows = $wpdb->get_results($sql, "ARRAY_A");

        if (strlen($wpdb->last_error) > 0) {
            throw new \Exception($wpdb->last_error);
        }

        return $queryRows;
    }

    private function buildDeleteSql($wpdb)
    {
        $sql = "DELETE FROM " . $this->childTableName;
        $sql .= " " . $this->sqlWherePart;
        $sql = $wpdb->prepare($sql, $this->sqlWhereValues);
        $sql .= ";";
        //var_dump($sql);
        return $sql;
    }

    private function copyInsertValues()
    {
        $newRowValues = [];

        foreach ($this->tableInfos as $infos) {
            if ($infos["key"] == "pri") {
                $value = null;
            } else {
                if (! property_exists($this, $infos["field"])) {
                    $value = null;
                } else {
                    $value = $this->{$infos["field"]};
                }
            }
            $newRowValues[] = [
                "field" => $infos["field"],
                "key" => $infos["key"],
                "value" => $value,
            ];
        }

        return $newRowValues;
    }
}