<?php

namespace Flyerimage\Core\Database;

/**
 * Database class controller
 */
class QueryBuilder
{
    
    /**
     * Table name to operate database functions
     * 
     * @var string
     */
    protected $table_name;

    /**
     * Table alias
     * 
     * @var string
     */
    protected $table_as;

    /**
     * Params array
     * 
     * @var array
     */
    protected $params_array;

    /**
     * Where array
     * 
     * @var array
     */
    protected $where_array;
    protected $update_in_progress = 0;

    /**
     * Select array
     * 
     * @var array
     */
    protected $select_array;

    /**
     * Inner join array
     * 
     * @var array
     */
    protected $inner_join_array;

    /**
     * Left join array
     * 
     * @var array
     */
    protected $left_join_array;

    /**
     * order by string
     * 
     * @var string
     */
    protected $order_by;

    /**
     * group by string
     * 
     * @var string
     */
    protected $group_by;

    /**
     * having string
     * 
     * @var string
     */
    protected $having;

    /**
     * result limit
     * 
     * @var int
     */
    protected $limit;

    /**
     * result page
     * 
     * @var int
     */
    protected $page;

    /**
     * Initialize the DB class
     * 
     * @param string $table_name
     * @param string $as
     * 
     * @return QueryBuilder
     */
    public static function table($table_name, $as = null)
    {
        $instance = new self();
        $instance->table_as = $as;
        $instance->table_name = $instance->get_table($table_name);
        if(!empty($as)) {
            $instance->table_name .=  ' ' . $as;
        }
        $instance->clear();
        return $instance;
    }

    /**
     * Clear all the parameters those are used to every query
     * 
     * @return void
     */
    private function clear()
    {
        $this->inner_join_array = [];
        $this->left_join_array = [];
        $this->params_array = [];
        $this->where_array = [];
        $this->select_array = [];
        $this->order_by = "";
        $this->group_by = "";
        $this->having = "";
        $this->limit = null;
        $this->page = null;
    }

    /**
     * Get table with wordpress prefix
     * 
     * @param string $table_name
     * 
     * @return string
     */
    public function get_table($table_name) {
        global $wpdb;
        return sprintf("%s%s%s", $wpdb->prefix, MigrationBase::$table_prefix, $table_name);
    }

    /**
     * Set the inner join table
     *
     * Example  ->inner_join(ITables::$TABLE_NAME, 'ALIAS_NAME', 'ALIAS_NAME.id = OTHER_TABLE.reference_id')
     * 
     * @param string $join_table_name
     * @param string $as
     * @param string $on_query on query
     * 
     * @return QueryBuilder
     */
    public function inner_join($join_table_name, $as, $on_query)
    {
        $join_table = $this->get_table($join_table_name);
        array_push($this->inner_join_array, $join_table . ' ' . $as . ' on ' . $on_query);
        return $this;
    }

    /**
     * Set the left join table
     * 
     * @param string $join_table_name
     * @param string $on_query on query
     * 
     * @return QueryBuilder
     */
    public function left_join($join_table_name, $on_query)
    {
        $join_table = $this->get_table($join_table_name);
        array_push($this->left_join_array, $join_table . ' on ' . $on_query);
        return $this;
    }
     
    /**
     * Set the where and param values
     * 
     * @param array|string $args
     * @return QueryBuilder
     */
    public function where($args)
    {
        if (is_string($args)) {
            array_push($this->where_array, $args);
        }
        else if ($this->is_associative($args)) {
            foreach ($args as $key => $value) {
                array_push($this->params_array, $value);
                array_push($this->where_array, "$key = " . $this->get_type($value));
            }
        }
        else if(count($args) == 3) {
            if (is_array($args[2])) {
                $array_type = [];
                foreach ( $args[2] as $value ) {
                    $array_type[] = $this->get_type($value);
                    array_push($this->params_array, $value);
                }
                array_push($this->where_array, "{$args[0]} {$args[1]} (" . implode(",", $array_type) . ")");
            }
            else {
                array_push($this->where_array, "{$args[0]} {$args[1]} " . $this->get_type($args[2]));
                array_push($this->params_array, $args[2]);
            }
        }
        return $this;
    }

    /**
     * Set the where and param values
     *
     * @param string $where
     * @param array $args
     * @return QueryBuilder
     */
    public function whereRaw($where, $args = []) {
        foreach ( $args as $value ) {
            array_push($this->params_array, $value);
        }
        array_push($this->where_array, $where);
        return $this;
    }

    /**
     * @return QueryBuilder
     */
    public function blockStart() {
        array_push($this->where_array, " ( ");
        return $this;
    }

    /**
     * @return QueryBuilder
     */
    public function blockEnd() {
        array_push($this->where_array, " ) ");
        return $this;
    }

    /**
     * Set the select columns into array
     * 
     * @param array $args
     * @return QueryBuilder
     */
    public function select($args = [], $reset = false)
    {
        if ($reset) {
            $this->select_array = [];
        }
        foreach($args as $value) {
            array_push($this->select_array, $value);
        }   
        return $this;
    }

    /**
     * Set the group by query
     * 
     * @param string $query
     * 
     * @return QueryBuilder
     */
    public function group_by($query) {
        $this->group_by = $query;
        return $this;
    }

    /**
     * Set the order by query
     * 
     * @param string $query
     * 
     * @return QueryBuilder
     */
    public function order_by($query) {
        $this->order_by = $query;
        return $this;
    }

    /**
     * Set the having query
     * 
     * @param string $query
     * 
     * @return QueryBuilder
     */
    public function having($query) {
        $this->having = $query;
        return $this;
    }

    /**
     * Set the page number
     * 
     * @param int $number
     * 
     * @return QueryBuilder
     */
    public function page($number) {
        $this->page = $number;
        return $this;
    }

    /**
     * Set the result limit
     * 
     * @param int $limit
     * 
     * @return QueryBuilder
     */
    public function limit($limit) {
        $this->limit = $limit;
        return $this;
    }

    /**
     * Prepare select query based on select array
     * 
     * @return string
     */
    private function prepare_select_query()
    {
        $select = "*";
        if(count($this->select_array) > 0) {
            $select = implode(" , ", $this->select_array);
        }
        return $select;
    }

    /**
     * Prepare query except select
     * 
     * @return string
     */
    private function prepare_query()
    {
        $query = $this->table_name;
        
        // collect inner join
        $inner_join_query = '';
        if(count($this->inner_join_array) > 0) {
            $inner_join_query = implode(' inner join  ', $this->inner_join_array);
        }
        if(!empty($inner_join_query)) {
            $query = $query . ' inner join ' . $inner_join_query;
        }

        // collect left join
        $left_join_query = '';
        if(count($this->left_join_array) > 0) {
            $left_join_query = implode(' left join  ', $this->left_join_array);
        }
        if(!empty($left_join_query)) {
            $query = $query . ' left join ' . $left_join_query;
        }

        // collect where condition
        $where = '';
        if(count($this->where_array) > 0) {
            $where = implode(' and ', $this->where_array);
        }
        if ($this->update_in_progress == 1) {
            $query = $where;
        }
        else if(!empty($where)) {
            $query = $query . ' where ' . $where;
        }

        // prepare order by
        if(!empty($this->order_by)) {
            $query = $query . ' order by ' . $this->order_by;
        }

        // prepare group by
        if(!empty($this->group_by)) {
            $query = $query . ' group by ' . $this->group_by;
        }

        // prepare having by
        if(!empty($this->having)) {
            $query = $query . ' having ' . $this->having;
        }
    
        return $query;
    }

    /**
     * Prepare pagination query
     * 
     * @return string
     */
    private function prepare_pagination_query() {
        $query = "";
        if($this->limit != null && $this->page == null) {
            $query = " limit {$this->limit}";
        }

        if($this->limit != null && $this->page != null) {
            $offset = $this->limit * ($this->page - 1);
            $query = " limit $offset, {$this->limit}";
        }
        return $query;
    }

    /**
     * Count by column
     * 
     * @param string $column_name - default 'id'
     * 
     * @return int
     */
    public function count($column_name = 'id')
    {
        global $wpdb;
		//$column_name = sanitize_key($column_name);
		$query = $this->prepare_query();

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$result = $wpdb->get_row(
			$wpdb->prepare("SELECT count({$column_name}) as count from {$query}", $this->params_array)
		);

		return isset($result->count) ? (int) $result->count : 0;
    }

    /**
     * Find the first row
     * 
     * @return object
     */
    public function first()
    {   
        global $wpdb;

		// Sanitize column selection to avoid injection
		$select = esc_sql($this->prepare_select_query());

		// Build the WHERE/join query part
		$query = $this->prepare_query();

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_row(
			$wpdb->prepare("SELECT $select FROM $query", $this->params_array)
		);
    }

    /**
     * Get list of rows
     * 
     * @return array
     * 
     */
    public function get()
    {
        $select           = $this->prepare_select_query();
        $query            = $this->prepare_query();
        $pagination_query = $this->prepare_pagination_query();
        if(!empty($pagination_query)) {
            $query = "$query $pagination_query";
        }

        global $wpdb;
        //return ["query" => "SELECT {$select} FROM {$query}", "args" => $this->params_array];
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $select and $query are built internally by QueryBuilder and contain only table/column names, not user input. User values are parameterized via $this->params_array.
        return $wpdb->get_results(
            $wpdb->prepare("SELECT $select FROM $query", $this->params_array)
        );
    }

    /**
     * Get list with pagination
     * 
     * @return array
     * 
     */
    public function pagination()
    {
        $total = $this->count("{$this->table_as}.id");
        
        $select           = $this->prepare_select_query();
        $query            = $this->prepare_query();
        $pagination_query = $this->prepare_pagination_query();
        if (!empty($pagination_query)) {
            $query = "$query $pagination_query";
        }

        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $data = $wpdb->get_results(
            $wpdb->prepare("SELECT $select FROM $query", $this->params_array)
        );

        return [
            'currentPage' => $this->page,
            'numOfPages'  => ceil($total / $this->limit),
            'showingFrom' => (($this->page - 1) * $this->limit) + 1,
            'showingTo'   => (($this->page - 1) * $this->limit) + count($data),
            'has_next'    => $total > ($this->page * $this->limit),
            'has_prev'    => ($this->page - 1) > 0,
            'itemCount'   => intval($total),
            'list'        => $data,
            //'query'       => "SELECT {$select} FROM {$query}"
        ];
    }

    /**
     * Find by id
     * 
     * @param int $id
     * 
     * @return object
     */
    function find_by_id($id)
    {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE id = %d", $id)
        );
    }

    /**
     * Get mysql data type based on data 
     * 
     * @param obj $data
     * @return string
     */
    public function get_type($data) {
        $type = gettype($data);
        if($type === 'integer' || $type === 'boolean') return '%d';
        if($type === 'double')  return '%f';
        else return '%s';
    }

    public function is_associative(array $args) {
        if (array() === $args) return false;
        return array_keys($args) !== range(0, count($args) - 1);
    }

    /**
     * Delete an row
     *
     * @param int $id
     *
     * @return int|boolean
     */
    function delete_by_id($id)
    {
        global $wpdb;
        return $wpdb->delete($this->table_name, ['id' => $id], ['%d']);
    }

    /**
     * Create the records
     * 
     * @param array $args
     * 
     * @return int|WP_Error
     */
    function create(&$data)
    {
        global $wpdb;

        $type = [];
        foreach($data as $key => $value) {
            array_push($type, $this->get_type($value));
        }        

        $inserted = $wpdb->insert(
            $this->table_name,
            $data,
            $type
        );

        $data["last_query"] = $wpdb->last_query;
        $data["last_error"] = $wpdb->last_error;

        return $inserted ? $wpdb->insert_id : null;
    }

    /**
     * Update the records
     * 
     * @param array $data
     * 
     * @return int
     */
    function save($data = [])
    {
        global $wpdb;

        if (isset($data['id'])) {
            $id = $data['id'];
            unset($data['id']);
            
            $types = [];
            foreach($data as $key => $value) {
                array_push($types, $this->get_type($value));
            }

            //error_log( json_encode($data) );
            
            $updated = $wpdb->update(
                $this->table_name,
                $data,
                ['id' => $id],
                $types,
                ['%d']
            );
            return $updated;
        }
        return 0;
    }

    public function update($data)
    {
        $this->update_in_progress = 1;
        $query = $this->prepare_query();

        $update = [];
        $args0 = [];
        foreach ($data as $key => $value) {
            $update[] = "$key={$this->get_type($value)}";
            $args0[] = $value;
        }
        foreach ($this->params_array as $val) {
            $args0[] = $val;
        }
        $update = implode(", ", $update);

        $query = "UPDATE {$this->table_name} SET {$update} WHERE {$query}";

        global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->query( $wpdb->prepare( $query, ...$args0 ) );
    }

    public function delete($delete = "")
    {
        $this->update_in_progress = 1;
        $query = $this->prepare_query();

        $query = "DELETE $delete FROM {$this->table_name} WHERE {$query}";

        global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->query( $wpdb->prepare( $query, ...$this->params_array ) );
    }
}