<?php

namespace RtmBusiness\PostSync;

use Exception;
use RtmBusiness\PostSync\Model\Log;

class Query
{
    protected mixed $entity;
    protected int $limit = 0;
    protected int $offset = 0;
    protected array $where = [];
    protected string $sort = 'id';
    protected string $order = 'ASC';
    protected string $search;

    public function __construct($entity)
    {
        $this->entity = $entity;
        $this->sort = $this->entity::getPrimaryKey();
    }

    /**
     * Set the limit of the SQL query
     * @param int $limit amount to limit
     * @return $this query instance
     */
    public function limit(int $limit): Query
    {
        $this->limit = $limit;
        return $this;
    }

    /**
     * Set the offset of the SQL query
     * @param int $offset amount to offset
     * @return $this query instance
     */
    public function offset(int $offset): Query
    {
        $this->offset = $offset;
        return $this;
    }

    /**
     * Set the order of the SQL query
     * @param string $order order direction (DESC or ASC)
     * @return $this query instance
     */
    public function order(string $order): Query
    {
        $this->order = strtoupper($order);
        return $this;
    }

    /**
     * Set the sort column of the SQL query
     * @param string $sort column to sort
     * @return $this query instance
     */
    public function sort(string $sort): Query
    {
        $this->sort = $sort;
        return $this;
    }

    /**
     * Set the search value of the SQL query
     * @param string $search_value value to search
     * @return $this query instance
     */
    public function search(string $search_value): Query
    {
        $this->search = $search_value;
        return $this;
    }

    /**
     * Set the where clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function where(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => '=', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Set the where not clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function whereNot(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => '!=', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Set the where like clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function whereLike(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => 'LIKE', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Set the where not like clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function whereNotLike(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => 'NOT LIKE', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Set the where less than clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function whereLessThan(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => '<', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Set the where less than or equal clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function whereLessThanOrEqual(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => '<=', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Set the where greater than clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function whereGreaterThan(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => '>', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Set the where greater than or equal clause of the SQL query
     * @param string $column column for the where clause
     * @param $value mixed value to compare
     * @param string $which combinable clause (OR/AND)
     * @return $this query instance
     */
    public function whereGreaterThanOrEqual(string $column, mixed $value, string $which = 'AND'): Query
    {
        $this->where[] = ['type' => '>=', 'column' => $column, 'value' => $value, 'which' => strtoupper($which)];
        return $this;
    }

    /**
     * Builds up the query string
     * @param bool $count return count query
     * @return string the SQL query
     */
    private function buildQuery(bool $count = false): string
    {
        $where = '';
        // Search item in every search column
        if (!empty($this->search)) {
            $where .= 'AND (';
            foreach ($this->entity::getSearchFields() as $field) {
                $where .= '`' . $field . '` LIKE "%' . esc_sql($this->search) . '%" OR ';
            }
            $where = substr($where, 0, -4) . ')';
        }
        // Append where clauses
        if (!empty($this->where)) {
            foreach ($this->where as $item) {
                if ($item['type'] === "LIKE" || $item['type'] === "NOT LIKE") {
                    $where .= ' ' . $item['which'] . ' `' . $item['column'] . '` ' . $item['type'] . ' "%' . esc_sql($item['value']) . '%"';
                } else {
                    $where .= ' ' . $item['which'] . ' `' . $item['column'] . '` ' . $item['type'] . ' "' . esc_sql($item['value']) . '"';
                }
            }
        }
        // Substr the beginning of where clause
        if (!empty($where)) {
            if (substr($where, 0, 3) === " OR") {
                $where = ' WHERE ' . substr($where, 3);
            } else {
                $where = ' WHERE ' . substr($where, 4);
            }
        }
        // Ordering
        $where .= ' ORDER BY `' . $this->sort . '` ' . $this->order;
        // Set limit if it's not 0
        if ($this->limit > 0) {
            $where .= ' LIMIT ' . $this->limit;
        }
        // Set offset if it's not 0
        if ($this->offset > 0) {
            $where .= ' OFFSET ' . $this->offset;
        }
        // Complete query
        if ($count) {
            return "SELECT COUNT(*) FROM `{$this->entity::getTable()}`$where";
        }
        return "SELECT * FROM `{$this->entity::getTable()}`$where";
    }

    /**
     * Returns the build query string
     * @param bool $count use count query
     * @return string the SQL query
     */
    public function queryString(bool $count = false): string
    {
        return $this->buildQuery($count);
    }

    /**
     * Executes the build SQL query
     * @return array list of entities from query result
     * @throws Exception
     */
    public function execute(): array
    {
        global $wpdb;
        $results = $wpdb->get_results($this->buildQuery());
        $items = [];

        foreach ($results as $index => $result) {
            $items[$index] = new Log((array)$result);
        }

        return $items;
    }

    /**
     * Returns count of build query
     * @return int amount of items from SQL query
     */
    public function count(): int
    {
        global $wpdb;
        return (int)$wpdb->get_var($this->buildQuery(true));
    }
}
