<?php


class CourierLoadsheet {

    private $table_name;
    private $default_ordering = 'id DESC';
    private $connection = null;

    public static $NEXT = 'NEXT';
    public static $PREV = 'PREV';

    public $paginate_by; // ADD

    // related to pagination
//     public $paginate_by = 100;
    private $next_id = null;
    private $previous_id = null;

    private $shop_url;
    private $courier_name;
    private $page_num = 1;
    private $where_clause;
    private $default_where_params = [
        [
            'shop_url', 'required',
        ],
        [
            'courier_name', 'optional',
        ],
    ];

    public $current = [];

    private function execute_query($query)
    {
        global $wpdb;

        $result = $wpdb->get_results($query);

        // $result = mysqli_query($this->connection, $query);

        if ($wpdb->last_error) {
            echo 'You done bad! ' . $wpdb->last_error;
        }
        // if (!$result) {
        //     echo ("Error description: " . mysqli_error($this->connection));
        //     return null;
        // }
        return $result;
    }

    /** 
     * @param params array keys 'shop_url', 'courier_name'
     */
    public function __construct($params = [])
    {

        global $wpdb;
        $this->table_name = $wpdb->prefix . 'dvs_ucp_loadsheet';
        $this->connection = $wpdb; // optional but useful

        $this->shop_url = $params['shop_url'];
        $this->courier_name = $params['courier_name'] ?? null;
        $this->connection = $params['connection'] ?? null;
    }

    private function resolve_default_where_parameters()
    {
        $clause = '';
        foreach ($this->default_where_params as $param) {
            if ($param[1] == 'optional' && $this->{$param[0]} === null)
                continue;
            if (trim($clause) !== '')
                $clause .= " AND ";
            $clause .= "{$param[0]} = '{$this->{$param[0]}}' ";
        }
        return $clause;
    }

    private function resolve_single_parameter($param)
    {
        $operator = $param['operator'] ?? '=';

        // TODO: need to copy escaping code in both scenraios and also pass array as parameter in in functions
        // need to update common-logs as well as common report file
        $prepared_value = $param['value'];
        if (!in_array($operator, ['IN', 'BETWEEN'])) {
            $prepared_value = esc_sql($param['value']);
            $prepared_value = "'{$prepared_value}'";
        }

        return " {$param['key']} $operator {$prepared_value} ";
    }

    private function resolve_where_params($where_clause, $params)
    {
        $relation = 'AND';
        $where_clauses = [];
        if (is_string($params[0]))
            $relation = $params[0];
        foreach ($params as $id => $param) {
            if (is_string($param))
                continue;

            if (!array_key_exists('key', $param)) {
                $where_clause = $this->resolve_where_params($where_clause, $param);
                continue;
            }

            $where_clauses[] = $this->resolve_single_parameter($param);
        }
        // add conditional relation in between of those if where_clause is not empty
        $where_clause = empty(trim($where_clause)) ? '' : " $relation ( $where_clause )";
        $prepared_condition = join(" $relation ", $where_clauses) . $where_clause;

        return $prepared_condition;
    }

    private function resolve_where_clause($params, $id_clause)
    {
        $default_params = $this->resolve_default_where_parameters();
        $where_clause = "WHERE {$default_params} $id_clause ";
        if (count($params)) {
            $where_clause .= " AND " . $this->resolve_where_params("", $params);
        }
        $this->where_clause = $where_clause;
        return $where_clause;
    }


    public function get($params = [], $flags = [], $page = 1, $last_id = null, $dir = null)
    {
        $group_by = $flags['group_by'] ?? null;

        $group_by_clause = $group_by ? "GROUP BY $group_by" : "";

        $this->page_num = $page;
        $id_clause = '';
        if ($dir === self::$NEXT) {
            $id_clause = "AND id < $last_id";
        } else if ($dir === self::$PREV) {
            $id_clause = "AND id > $last_id";
            $this->default_ordering = 'id ASC';
        }
        $where_clause = $this->resolve_where_clause($params, $id_clause);

        // $offset = ($this->page_num - 1 ) * $this->paginate_by;
        $paginate_by = $this->paginate_by + 1;
        $query = "
            SELECT * from {$this->table_name}
            $where_clause
            {$group_by_clause}
            ORDER BY {$this->default_ordering}
            LIMIT {$paginate_by}
        ";

//         echo $query; 

        $results = $this->execute_query($query);

        $parcels = [];
        $count = 0;
        foreach ( $results as $row ){
            if ($count === $this->paginate_by) {
                // set the next_id to last id of current set
                // paginate + 1 indicates that next exists
                $this->next_id = $parcels[$count - 1]->id;
                break;
            }
            // Previous page query returns rows in reverse order so insert always at 0th index to reverse the result set
            if ($dir === self::$PREV)
                array_unshift($parcels, $row);
            else
                $parcels[] = $row;
            $count += 1;
        }
        if ($dir === self::$PREV)
            $this->next_id = $parcels[$count - 1]->id;

        $this->current = $parcels;
        return $parcels;
    }

    public function get_next()
    {
        if ($this->next_id !== null)
            return array(
                'page_num' => $this->page_num + 1,
                'last_seen' => $this->next_id,
            );

        return null;
    }

    public function get_prev()
    {
        if ($this->page_num > 1)
            return array(
                'page_num' => $this->page_num - 1,
                'last_seen' => $this->current[0]->id,
            );

        return null;
    }

    public function next()
    {
        $this->page_num += 1;
        return $this->get([], false);
    }

    public function create($args)
    {
        $params = "";
        foreach ($args as $key => $value) {
            $formatted_val =  mysqli_real_escape_string($this->connection, $value);
            if (strpos($value, 'NOW()') === false)
                $formatted_val = "'{$formatted_val}'";
            $params .= "{$key} = $formatted_val, ";
        }

        $params .= "shop_url = '{$this->shop_url}', courier_name = '{$this->courier_name}'";

        $query = "
            INSERT INTO {$this->table_name}
            SET $params
        ";

        // echo $query;

        $results = $this->execute_query($query);


        return mysqli_insert_id($this->connection);
    }
}