<?php

if (!defined('ABSPATH')) {
    exit;
}
/**
 * This file is responsible for all database realted functionality.
 */
class ccpwp_database
{
    public $table_name;
    public $primary_key;
    public $version;
    /**
     * Get things started
     *
     * @access  public
     * @since   1.0
     */
    public function __construct()
    {

        global $wpdb;

        $this->table_name = $wpdb->base_prefix . 'cmc_coins_v2';
        $this->primary_key = 'id';
        $this->version = '1.0';
        //$this->ccpw_refresh_database();
    }

    /**
     * Get columns and formats
     *
     * @access  public
     * @since   1.0
     */
    public function get_columns()
    {
        return array(
            'id' => '%d',
            'coin_id' => '%s',
            'name' => '%s',
            'symbol' => '%s',
            'price' => '%f',
            'percent_change_24h' => '%f',
            'percent_change_1y' => '%f',
            'percent_change_30d' => '%f',
            'percent_change_7d' => '%f',
            'market_cap' => '%f',
            'total_volume' => '%f',
            'circulating_supply' => '%d',
            'weekly_price_data' => '%s',
            'coin_category' => '%s',
            'ath' => '%f',
            'high_24h' => '%f',
            'low_24h' => '%f',
            'ath_change_percentage' => '%f',
            'coin_status' => '%s',
        );
    }

    /*
    |-----------------------------------------------------------------------
    |    Call this function to insert/update data for single or multiple coin
    |-----------------------------------------------------------------------
     */
    public function ccpw_insert($coins_data)
    {
        if (is_array($coins_data) && count($coins_data) > 0) {
            return $this->wp_insert_rows($coins_data, $this->table_name, true, 'coin_id');
        }
    }

    /*
    |-------------------------------------------------------------------------------
    |    This function is used to insert/update weekly_data field in database table
    |-------------------------------------------------------------------------------
     */
    public function ccpw_insert_coin_weekly_data($coin_id, $data)
    {

        if (!empty($coin_id) && !empty($data)) {
            $raw_data['coin_id'] = $coin_id;
            $raw_data['weekly_price_data'] = empty($data) ? null : $data;
            return $this->ccpw_insert(array($raw_data));
        }
    }

    /**
     * This function will remove the coins from database if any coin found after supplied number
     *
     * @param int Rows to be removed after this number
     */
    public function ccpw_refresh_database($number_of_coins = 2500)
    {

        if (get_option('cmc-dynamic-links') != false || get_option('cmc-dynamic-links') == "") {
            return;
        }

        global $wpdb;
        $table = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name));
        if ($table == $this->table_name) {
            $wpdb->query($wpdb->prepare("DELETE FROM $this->table_name WHERE id > %d ", $number_of_coins));
        }

    }
    public function ccpwp_refresh_db($rows = null)
    {

        global $wpdb;
        $table = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name));
        $date = date('Y-m-d h:m:s', strtotime("-3 days"));

        if ($table == $this->table_name) {
            $wpdb->query($wpdb->prepare("DELETE FROM $this->table_name WHERE last_updated <= %s ", $date));
        }

    }

    /**
     * Get default column values
     *
     * @access  public
     * @since   1.0
     */
    public function get_column_defaults()
    {
        return array(
            'coin_id' => '',
            'name' => '',
            'symbol' => '',
            'price' => '',
            'percent_change_24h' => '',
            'percent_change_1y' => '',
            'percent_change_30d' => '',
            'percent_change_7d' => '',
            'market_cap' => '',
            'total_volume' => '',
            'circulating_supply' => '',
            'weekly_price_data' => '',
            'last_updated' => date('Y-m-d H:i:s'),
            'coin_status' => 'enable',
            'high_24h ' => '',
            'low_24h ' => '',
            'ath ' => '',
            'ath_change_percentage ' => '',
            'ath_date ' => '',
        );
    }

    /**
     * Sanitize and escape user input for a database query
     *
     * @param string $coin_id The coin ID input from the user
     * @return string|false Sanitized and escaped coin ID or false if input is empty
     */
    public function get_current_coin_price($coin_id = null)
    {
        if ($coin_id != null) {
            global $wpdb;
            $coin_id = sanitize_text_field($coin_id); // Sanitize user input
            $data = $wpdb->get_var($wpdb->prepare("SELECT price FROM $this->table_name WHERE coin_id = %s", $coin_id)); // Use $wpdb->prepare() for insertion
            return $data;
        }
        return false;
    }
    /**
     * Get the logo of a coin by its ID
     *
     * @param string $coin_id The ID of the coin
     * @return string|false The logo URL or false if the coin ID is missing
     */
    public function get_coin_logo($coin_id)
    {
        global $wpdb;

        // Sanitize and escape the coin ID to prevent SQL injection
        $coin_id = sanitize_text_field($coin_id);

        // Check if the coin ID is missing
        if (empty($coin_id)) {
            return false;
        }

        // Prepare the SQL query with $wpdb->prepare() to prevent SQL injection
        $query = $wpdb->prepare("SELECT * FROM $this->table_name WHERE coin_id = %s LIMIT 1", $coin_id);

        // Retrieve the logo URL from the database
        $logo = $wpdb->get_row($query, ARRAY_A);

        // Return the logo URL or false if not found
        return $logo ? $logo : false;
    }

    public function get_coin_weekly_data($coin_id)
    {
        global $wpdb;

        // Sanitize and escape the coin ID to prevent SQL injection
        $coin_id = sanitize_text_field($coin_id);

        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $response = $wpdb->get_var($wpdb->prepare("SELECT weekly_price_data FROM $this->table_name WHERE coin_id = %s", $coin_id));
        if (empty($response) || !is_string($response)) {
            return false;
        }

        $weekly_data = unserialize($response);
        $lastIndex = count($weekly_data);

        $currentPrice = $this->get_current_coin_price($coin_id);
        if ($weekly_data[$lastIndex - 1] != $currentPrice) {
            array_push($weekly_data, $currentPrice);
        }

        return $weekly_data;
    }
    /**
     * Retrieves extra info data for a coin from the database
     *
     * @param string $coin_id The ID of the coin
     * @return array|false The extra info data or false if not found
     */
    public function get_coin_extra_info_data($coin_id)
    {
        global $wpdb;

        // Sanitize and escape the coin ID to prevent SQL injection
        $coin_id = sanitize_text_field($coin_id);

        // Sanitize user input for a database query and use $wpdb->prepare() for insertion
        $query = $wpdb->prepare("SELECT weekly_price_data FROM $this->table_name WHERE coin_id = %s", $coin_id);
        $response = $wpdb->get_var($query);

        if (empty($response) || !is_string($response)) {
            return false;
        }

        // Explode the response to an array
        $weekly_data = explode(',', $response);

        // Get the total count of the array
        $total = count($weekly_data);

        // Slice the array to get the last 23 elements
        $OneDay = array_slice($weekly_data, $total - 23);
        $lastIndex = count($OneDay);

        // Add current price at the end of the chart
        $currentPrice = $this->get_current_coin_price($coin_id);
        if ($OneDay[$lastIndex - 1] != $currentPrice) {
            array_push($OneDay, $currentPrice);
        }

        return $OneDay;
    }
    /**
     * Sanitize and escape user input for checking if a coin exists by ID
     *
     * @param string $coin_id The ID of the coin
     * @return bool Whether the coin exists or not
     */
    public function coin_exists_by_id($coin_id)
    {
        global $wpdb;

        // Sanitize and escape the coin ID to prevent SQL injection
        $coin_id = sanitize_text_field($coin_id);

        $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $this->table_name WHERE coin_id = %s", $coin_id));

        return $count == 1 ? true : false;
    }
    /**
     * Retrieve orders from the database
     *
     * @access  public
     * @since   1.0
     * @param   array $args
     * @param   bool  $count  Return only the total number of results found (optional)
     */
    public function get_coins($args = array(), $count = false)
    {
        global $wpdb;

        $defaults = array(
            'number' => 20,
            'offset' => 0,
            'id' => '',
            'coin_id' => '',
            'name' => '',
            'status' => '',
            'email' => '',
            'orderby' => 'market_cap',
            'market_cap' => '',
            'order' => 'DESC',
        );

        $args = wp_parse_args($args, $defaults);

        if ($args['number'] < 1) {
            $args['number'] = 999999999999;
        }

        $where = '';

        // specific referrals
        if (!empty($args['id'])) {
            $order_ids = is_array($args['id']) ? implode(',', array_map('absint', $args['id'])) : absint($args['id']);
            $where .= "WHERE `id` IN( {$order_ids} ) ";
        }

        // where market cap larger than zero
        if (!empty($args['market_cap'])) {
            $where .= "WHERE `market_cap` != 0";
        }

        if (!empty($args['coin_id'])) {
            $coin_id = is_array($args['coin_id']) ? array_map('sanitize_text_field', $args['coin_id']) : sanitize_text_field($args['coin_id']);

            if (empty($where)) {
                $where .= " WHERE";
            } else {
                $where .= " AND";
            }

            if (is_array($coin_id)) {
                // Use placeholders for array values to prevent SQL injection
                $placeholders = array_fill(0, count($coin_id), '%s');
                $where .= " `coin_id` IN(" . implode(',', $placeholders) . ") ";
                $coin_id_values = $coin_id; // Store for use in prepare
            } else {
                $where .= " `coin_id` = %s ";
                $coin_id_values = array($coin_id); // Store for use in prepare
            }
        }

        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];

        if ('total' === $args['orderby']) {
            $args['orderby'] = 'total+0';
        } else if ('subtotal' === $args['orderby']) {
            $args['orderby'] = 'subtotal+0';
        }

        $cache_key = (true === $count) ? md5('ccpw_coins_count' . serialize($args)) : md5('ccpw_coins_' . serialize($args));

        $results = wp_cache_get($cache_key, 'coins');

        if (false === $results) {
            if (true === $count) {
                // Prepare the query with coin_id values if they exist
                if (isset($coin_id_values)) {
                    $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT({$this->primary_key}) FROM {$this->table_name} {$where};", $coin_id_values)));
                } else {
                    $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT({$this->primary_key}) FROM {$this->table_name} {$where};")));
                }
            } else {
                // Prepare the query with coin_id values if they exist
                if (isset($coin_id_values)) {
                    $prepare_args = array_merge($coin_id_values, array(absint($args['offset']), absint($args['number'])));
                    $results = $wpdb->get_results(
                        $wpdb->prepare(
                            "SELECT * FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} {$args['order']} LIMIT %d, %d;",
                            $prepare_args
                        )
                    );
                } else {
                    $results = $wpdb->get_results(
                        $wpdb->prepare(
                            "SELECT * FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} {$args['order']} LIMIT %d, %d;",
                            absint($args['offset']),
                            absint($args['number'])
                        )
                    );
                }
            }
            wp_cache_set($cache_key, $results, 'coins', 3600);
        }

        return $results;
    }
    public function get_column_specific_data($args = array(), $count = false)
    {
        global $wpdb;
        $defaults = array(
            'number' => 20,
            'column' => '',
            'orderby' => 'market_cap',

        );
        $args = wp_parse_args($args, $defaults);
        if (!empty($args['column'])) {
            if (is_array($args['column'])) {
                $column = implode(',', array_map('esc_sql', $args['column']));
            } else {
                $column = intval($args['column']);
            }
        } else {
            $args['column'] = '*';
        }
        if ($args['number'] < 1) {
            $args['number'] = 999999999999;
        }
        $results = $wpdb->get_results(
            $wpdb->prepare("SELECT {$args['column']} FROM {$this->table_name} ORDER BY {$args['orderby']} DESC LIMIT  %d", absint($args['number']))
        );

        return $results;

    }
    public function get_coin_id_by_symbol($coin_symbol = '')
    {
        global $wpdb;
        // Sanitize the input parameter $coin_symbol to prevent SQL injection
        $coin_symbol = sanitize_text_field($coin_symbol);

        if (empty($coin_symbol)) {
            // Use prepare() for table name to prevent SQL injection
            $response = $wpdb->get_results($wpdb->prepare("SELECT coin_id, symbol FROM %i", $this->table_name));
            $list = array();
            foreach ($response as $value) {
                // Sanitize output data before using it in the array
                $list[sanitize_text_field($value->symbol)] = sanitize_text_field($value->coin_id);
            }
            // Convert the array to a plain array
            return ccpwp_objectToArray($list);
        } else {
            if (is_array($coin_symbol)) {
                // Use placeholders for array values to prevent SQL injection
                $placeholders = array_fill(0, count($coin_symbol), '%s');
                $response = $wpdb->get_results($wpdb->prepare("SELECT coin_id FROM %i WHERE symbol IN (" . implode(',', $placeholders) . ")", array_merge(array($this->table_name), $coin_symbol)));
            } else {
                // Sanitize the input parameter to prevent SQL injection
                $coin_symbol = strtoupper(sanitize_text_field($coin_symbol));
                // Use prepare() to prevent SQL injection
                $response = $wpdb->get_var($wpdb->prepare("SELECT coin_id FROM %i WHERE symbol = %s", $this->table_name, $coin_symbol));
            }
            // Return sanitized response
            return $response;
        }
    }
    public function get_coins_listdata($args = array(), $count = false)
    {
        global $wpdb;

        $defaults = array(
            'number' => 20,
            'offset' => 0,
            'coin_id' => '',
            'name' => '',
            'status' => '',
            'email' => '',
            'orderby' => 'id',
            'order' => 'ASC',
        );

        // Sanitize and escape the input arguments to prevent SQL injection and ensure data integrity
        $args = wp_parse_args($args, $defaults);
        $args['number'] = absint($args['number']); // Ensure the number is an integer

        $where = '';

        // Specific referrals
        if (!empty($args['id'])) {
            $order_ids = is_array($args['id']) ? array_map('absint', $args['id']) : absint($args['id']); // Sanitize and escape the order IDs
            $where .= "WHERE `id` IN( " . implode(',', $order_ids) . " ) ";
        }

        if (!empty($args['coin_id'])) {
            if (empty($where)) {
                $where .= " WHERE";
            } else {
                $where .= " AND";
            }

            if (is_array($args['coin_id'])) {
                $coin_ids = array_map('esc_sql', $args['coin_id']); // Escape and sanitize the array values to prevent SQL injection
                $where .= " `coin_id` IN('" . implode("','", $coin_ids) . "') ";
            } else {
                $coin_id = esc_sql($args['coin_id']); // Escape and sanitize the coin ID to prevent SQL injection
                $where .= " `coin_id` = '" . $coin_id . "' ";
            }
        }

        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];

        if ('total' === $args['orderby']) {
            $args['orderby'] = 'total+0';
        } else if ('subtotal' === $args['orderby']) {
            $args['orderby'] = 'subtotal+0';
        }

        $cache_key = (true === $count) ? md5('ccpw_coins_list_count' . serialize($args)) : md5('ccpw_coins_list_' . serialize($args));

        $results = wp_cache_get($cache_key, 'coins');

        if (false === $results) {
            if (true === $count) {
                $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT({$this->primary_key}) FROM {$this->table_name} {$where};"))); // Sanitize and escape the SQL query
            } else {
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT name,price,symbol,coin_id FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} {$args['order']} LIMIT %d, %d;",
                        absint($args['offset']),
                        absint($args['number'])
                    )
                ); // Sanitize and escape the SQL query and limit parameters
            }

            wp_cache_set($cache_key, $results, 'coins', 3600);
        }

        return $results;
    }

    /**
     *  A method for inserting multiple rows into the specified table
     *  Updated to include the ability to Update existing rows by primary key
     *
     *  Usage Example for insert:
     *
     *  $insert_arrays = array();
     *  foreach($assets as $asset) {
     *  $time = current_time( 'mysql' );
     *  $insert_arrays[] = array(
     *  'type' => "multiple_row_insert",
     *  'status' => 1,
     *  'name'=>$asset,
     *  'added_date' => $time,
     *  'last_update' => $time);
     *
     *  }
     *
     *
     *  wp_insert_rows($insert_arrays, $wpdb->tablename);
     *
     *  Usage Example for update:
     *
     *  wp_insert_rows($insert_arrays, $wpdb->tablename, true, "primary_column");
     *
     *
     * @param array $row_arrays
     * @param string $wp_table_name
     * @param boolean $update
     * @param string $primary_key
     * @return false|int
     *
     */
    public function wp_insert_rows($row_arrays, $wp_table_name, $update = false, $primary_key = null)
    {
        global $wpdb;
        $wp_table_name = esc_sql($wp_table_name); // Sanitize the table name to prevent SQL injection
        // Setup arrays for Actual Values, and Placeholders
        $values = array();
        $place_holders = array();
        $query = "";
        $query_columns = "";

        $floatCols = array('percent_change_24h', 'market_cap', 'total_volume', 'circulating_supply');
        $query .= "INSERT INTO `{$wp_table_name}` ("; // Sanitize and escape the table name
        foreach ($row_arrays as $count => $row_array) {
            foreach ($row_array as $key => $value) {
                if ($count == 0) {
                    if ($query_columns) {
                        $query_columns .= ", `" . esc_sql($key) . "`"; // Sanitize and escape the column name
                    } else {
                        $query_columns .= "`" . esc_sql($key) . "`"; // Sanitize and escape the column name
                    }
                }

                $values[] = $value;

                $symbol = "%s";
                // if (is_numeric($value)) {
                //     $symbol = "%d";
                // }

                if (in_array($key, $floatCols)) {
                    $symbol = "%f";
                }
                if (isset($place_holders[$count])) {
                    $place_holders[$count] .= ", '$symbol'";
                } else {
                    $place_holders[$count] = "( '$symbol'";
                }
            }
            // mind closing the GAP
            $place_holders[$count] .= ")";
        }

        $query .= " $query_columns ) VALUES ";

        $query .= implode(', ', $place_holders);

        if ($update) {
            $update = " ON DUPLICATE KEY UPDATE `" . esc_sql($primary_key) . "`=VALUES( `" . esc_sql($primary_key) . "` ),"; // Sanitize and escape the primary key
            $cnt = 0;
            foreach ($row_arrays[0] as $key => $value) {
                if ($cnt == 0) {
                    $update .= "`" . esc_sql($key) . "`=VALUES(`" . esc_sql($key) . "`)";
                    $cnt = 1;
                } else {
                    $update .= ", `" . esc_sql($key) . "`=VALUES(`" . esc_sql($key) . "`)";
                }
            }
            $query .= $update;
        }

        $sql = $wpdb->prepare($query, $values); // Sanitize and escape the SQL query and values

        if ($wpdb->query($sql)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Return the number of results found for a given query
     *
     * @param  array  $args
     * @return int
     */
    public function count($args = array())
    {
        return $this->get_coins($args, true);
    }

    /**
     * Create the table
     *
     * @access  public
     * @since   1.0
     */
    public function create_table()
    {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $sql = "CREATE TABLE " . $this->table_name . " (
		id bigint(20) NOT NULL AUTO_INCREMENT,
		coin_id varchar(30) NOT NULL UNIQUE,
		name varchar(30) NOT NULL,
		symbol varchar(20) NOT NULL,
		logo text(300),
		price decimal(30,18),
		percent_change_24h decimal(6,2) ,
		percent_change_1y decimal(20,2) ,
		percent_change_30d decimal(6,2) ,
		percent_change_7d decimal(6,2) ,
		market_cap decimal(24,2),
		total_volume decimal(24,2) ,
		circulating_supply varchar(250),
		weekly_price_data longtext NOT NULL,
		coin_status varchar(20) NOT NULL DEFAULT 'enable',
		coin_category longtext,
		last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		high_24h decimal(30,18),
		low_24h decimal(30,18),
		ath decimal(30,18),
		ath_change_percentage decimal(20,6),
		ath_date TIMESTAMP NOT NULL,
        extradata longtext,
		PRIMARY KEY (id)
		) CHARACTER SET utf8 COLLATE utf8_general_ci;";

        dbDelta($sql);

        update_option($this->table_name . '_db_version', $this->version);
    }

    /**
     * Drop database table
     */
    public function drop_table()
    {
        global $wpdb;
        delete_transient('ccpw-all-gecko-coins');
        $wpdb->query("DROP TABLE IF EXISTS " . $this->table_name);

    }

    /*-----------------------------------------------------------------------|
    |                                                                         |
    |                    Check if a database table exists or not                 |
    |                                                                         |
    |------------------------------------------------------------------------|
     */
    public function is_table_exists()
    {
        global $wpdb;
        $table = $wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name);

        if ($wpdb->get_var($table) == $this->table_name) {
            return true;
        } else {
            return false;
            delete_transient('ccpw-all-gecko-coins');
        }
    }

}
