<?php

class MainWP_Wordfence_DB {
	private $mainwp_wordfence_db_version = '2.1';
	private $table_prefix;

	// Singleton
	private static $instance = null;

	static function get_instance() {
		if ( null == self::$instance ) {
			self::$instance = new MainWP_Wordfence_DB();
		}

		return self::$instance;
	}

	// Constructor
	function __construct() {
		global $wpdb;
		$this->table_prefix = $wpdb->prefix . 'mainwp_';
	}

	function table_name( $suffix ) {
		return $this->table_prefix . $suffix;
	}

	// Support old & new versions of WordPress (3.9+)
	public static function use_mysqli() {
		if ( ! function_exists( 'mysqli_connect' ) ) {
			return false;
		}

		global $wpdb;

		return ( $wpdb->dbh instanceof mysqli );
	}

	// Installs new DB
	function install() {
		global $wpdb;
		$currentVersion = get_site_option( 'mainwp_wordfence_db_version' );
		if ( $currentVersion == $this->mainwp_wordfence_db_version ) {
			return;
		}

		$charset_collate = $wpdb->get_charset_collate();
		$sql             = array();

		$tbl = 'CREATE TABLE `' . $this->table_name( 'wordfence' ) . '` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`site_id` int(11) NOT NULL,
`status` tinyint(1) DEFAULT 0,
`apiKey` text NOT NULL,
`isPaid` tinyint(1) DEFAULT 0,
`isHidden` tinyint(1) DEFAULT 0,
`isNginx` tinyint(1) DEFAULT 0,
`lastscan` int(11) DEFAULT 0,
`todayAttBlocked` int UNSIGNED NOT NULL DEFAULT 0,
`weekAttBlocked` int UNSIGNED NOT NULL DEFAULT 0,
`monthAttBlocked` int UNSIGNED NOT NULL DEFAULT 0,
`extra_settings` text NOT NULL,
`settings` text NOT NULL,
`cacheType` VARCHAR(10),
`override` tinyint(1) NOT NULL DEFAULT 0';
		if ( '' == $currentVersion ) {
			$tbl .= ',
PRIMARY KEY  (`id`)  ';
		}
		$tbl  .= ') ' . $charset_collate;
		$sql[] = $tbl;

		error_reporting( 0 ); // make sure to disable any error output
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		foreach ( $sql as $query ) {
			dbDelta( $query );
		}
		update_option( 'mainwp_wordfence_db_version', $this->mainwp_wordfence_db_version );
	}

	/**
	 * Get sql to query websites and extension data.
	 *
	 * @return string sql to query.
	 */
	public function get_ext_websites_for_current_user( $actived, $site_id = false, $params = array() ) {
		if ( ! is_array( $params ) ) {
			$params = array();
		}

		$full_data      = isset( $params['full_data'] ) && $params['full_data'] && ( 'no' !== $params['full_data'] ) ? true : false;
		$format         = isset( $params['format'] ) ? $params['format'] : '';
		$selected_group = isset( $params['selected_group'] ) ? $params['selected_group'] : false;
		$join_ext_dt    = isset( $params['join_ext_data'] ) && $params['join_ext_data'] ? true : false;

		// Those params for process in extension only.
		if ( isset( $params['full_data'] ) ) {
			unset( $params['full_data'] );
		}
		if ( isset( $params['format'] ) ) {
			unset( $params['format'] );
		}
		if ( isset( $params['selected_group'] ) ) {
			unset( $params['selected_group'] );
		}
		// end.

		if ( $selected_group ) {
			$params['select_groups'] = true; // must select groups.
		}

		$sql_sites = $this->get_sql_websites_ext_data( $site_id, $params );

		$map_data = array(
			'id',
			'url',
			'name',
			'sync_errors',
		);

		if ( $join_ext_dt ) {
			$map_data = array_merge(
				$map_data,
				array(
					'apiKey',
					'isPaid',
					'status',
					'isHidden',
					'isNginx',
					'lastscan',
					'todayAttBlocked',
					'weekAttBlocked',
					'monthAttBlocked',
					'extra_settings',
					'settings',
					'cacheType',
					'override',
				)
			);
		}

		if ( $full_data || $join_ext_dt ) {
			$map_data = array_merge(
				$map_data,
				array(
					'plugin_upgrades',
					'plugins',
				)
			);
		}

		if ( isset( $params['select_groups'] ) && $params['select_groups'] ) {
			$map_data = array_merge(
				$map_data,
				array(
					'wpgroupids',
				)
			);
		}

		$dbwebsites = array();
		$websites   = $this->query( $sql_sites );
		while ( $websites && ( $website = self::fetch_object( $websites ) ) ) {
			$item_data = array();

			if ( ! $join_ext_dt ) {
				$item_data = MainWP_Wordfence_Utility::map_site( $website, $map_data, true );
			} elseif ( empty( $selected_group ) ) {
				if ( $website->plugins != '' ) {
					$plugins = json_decode( $website->plugins, 1 );
					if ( is_array( $plugins ) && count( $plugins ) != 0 ) {
						foreach ( $plugins as $plugin ) {
							if ( 'wordfence/wordfence.php' == $plugin['slug'] ) {
								if ( $actived && ! $plugin['active'] ) {
									continue;
								}
								$item_data = MainWP_Wordfence_Utility::map_site( $website, array( 'id', 'name', 'url' ) );
								if ( $plugin['active'] ) {
									$item_data['wordfence_active'] = 1;
								} else {
									$item_data['wordfence_active'] = 0;
								}
								// get upgrade info
								$item_data['wordfence_plugin_version'] = $plugin['version'];
								$plugin_upgrades                       = json_decode( $website->plugin_upgrades, 1 );
								if ( is_array( $plugin_upgrades ) && count( $plugin_upgrades ) > 0 ) {
									if ( isset( $plugin_upgrades['wordfence/wordfence.php'] ) ) {
										$upgrade = $plugin_upgrades['wordfence/wordfence.php'];
										if ( isset( $upgrade['update'] ) ) {
											$item_data['wordfence_upgrade'] = $upgrade['update'];
										}
									}
								}

								$item_data['lastscan']       = $website->lastscan;
								$item_data['status']         = $website->status;
								$item_data['hide_wordfence'] = $website->isHidden;
								break; // plugins.
							}
						}
					}
				}
			} else { // $selected_group is not empty.

				$site_groupids = implode( ',', $website->wpgroupids );

				if ( is_numeric( $selected_group ) && in_array( $selected_group, $site_groupids ) ) {
					if ( '' != $website->plugins ) {
						$plugins = json_decode( $website->plugins, 1 );
						if ( is_array( $plugins ) && count( $plugins ) != 0 ) {
							foreach ( $plugins as $plugin ) {
								if ( 'wordfence/wordfence.php' == $plugin['slug'] ) {
									if ( $actived && ! $plugin['active'] ) {
										continue;
									}
									$item_data = MainWP_Wordfence_Utility::map_site( $website, array( 'id', 'name', 'url' ) );
									if ( $plugin['active'] ) {
										$item_data['wordfence_active'] = 1;
									} else {
										$item_data['wordfence_active'] = 0;
									}
									$item_data['wordfence_plugin_version'] = $plugin['version'];

									// get upgrade info
									$plugin_upgrades = json_decode( $website->plugin_upgrades, 1 );
									if ( is_array( $plugin_upgrades ) && count( $plugin_upgrades ) > 0 ) {
										if ( isset( $plugin_upgrades['wordfence/wordfence.php'] ) ) {
											$upgrade = $plugin_upgrades['wordfence/wordfence.php'];
											if ( isset( $upgrade['update'] ) ) {
												$item_data['wordfence_upgrade'] = $upgrade['update'];
											}
										}
									}
									$item_data['hide_wordfence'] = $website->isHidden;
									$item_data['lastscan']       = $website->lastscan;
									$item_data['status']         = $website->status;
									break; // plugins.
								}
							}
						}
					}
				}
			}

			if ( ! empty( $item_data ) ) {
				if ( 'array' == $format ) {
					$dbwebsites[] = $item_data;
				} else {
					$dbwebsites[ $website->id ] = $item_data;
				}
			}
		}

		self::free_result( $websites );

		return $dbwebsites;
	}


	/**
	 * Get sql to query websites and extension data.
	 *
	 * @param int|false $site_id Website ID.
	 * @param bool      $for_widget Get query for widget display.
	 *
	 * @return string sql to query.
	 */
	public function get_sql_websites_ext_data( $site_id = false, $params = array() ) {

		global $mainWPWordfenceExtensionActivator;

		$join_ext_dt = isset( $params['join_ext_data'] ) && $params['join_ext_data'] ? true : false;

		$hook_params = array(
			'select_groups' => true,
		);
		$where       = '';
		if ( $site_id ) {
			$where = ' wp.id = ' . intval( $site_id );
		}
		$hook_params['extra_where'] = $where;
		if ( $join_ext_dt ) {
			$join = ' LEFT JOIN ' . $this->table_name( 'wordfence' ) . ' ext_tbl ON wp.id = ext_tbl.site_id ';

			$hook_params['extra_select_sql_fields'] = ' ext_tbl.id as item_id, ext_tbl.apiKey, ext_tbl.status, ext_tbl.isPaid, ext_tbl.isHidden, ext_tbl.isNginx, ext_tbl.lastscan, ext_tbl.todayAttBlocked, ext_tbl.weekAttBlocked, ext_tbl.monthAttBlocked, ext_tbl.extra_settings, ext_tbl.settings, ext_tbl.cacheType, ext_tbl.override ';
			$hook_params['extra_select_wp_fields']  = array( 'plugins', 'plugin_upgrades' );

			$hook_params['extra_join'] = $join;
		}
		if ( ! empty( $params ) && is_array( $params ) ) {
			$hook_params = array_merge( $hook_params, $params );
		}
		$sql_sites = apply_filters( 'mainwp_getsqlwebsites_for_current_user', false, $mainWPWordfenceExtensionActivator->get_child_file(), $mainWPWordfenceExtensionActivator->get_child_key(), $hook_params );
		//error_log($sql_sites);
		return $sql_sites;
	}

	public function update_setting( $setting ) {
		global $wpdb;
		$id      = isset( $setting['id'] ) ? $setting['id'] : 0;
		$site_id = isset( $setting['site_id'] ) ? $setting['site_id'] : 0;

		if ( $id ) {
			if ( $wpdb->update( $this->table_name( 'wordfence' ), $setting, array( 'id' => intval( $id ) ) ) ) {
				return $this->get_setting_by( 'id', $id );
			}
		} elseif ( $site_id ) {
			$current = $this->get_setting_by( 'site_id', $site_id );
			if ( $current ) {
				if ( $wpdb->update( $this->table_name( 'wordfence' ), $setting, array( 'site_id' => intval( $site_id ) ) ) ) {
					return $this->get_setting_by( 'site_id', $site_id );
				}
			} elseif ( $wpdb->insert( $this->table_name( 'wordfence' ), $setting ) ) {
					return $this->get_setting_by( 'id', $wpdb->insert_id );
			}
		} elseif ( $wpdb->insert( $this->table_name( 'wordfence' ), $setting ) ) {
			return $this->get_setting_by( 'id', $wpdb->insert_id );
		}

		return false;
	}

	public function update_setting_fields_values_by( $by, $value, $data ) {
		$current_settings = $this->get_setting_fields_by( $by, $value );

		foreach ( $data as $key => $val ) {
			if ( in_array( $key, MainWP_Wordfence_Config::$options_filter ) ) {
				$current_settings[ $key ] = $val;
			}
		}
		$this->update_setting_fields_by( $by, $value, $current_settings );
	}

	public function update_setting_fields_by( $by, $value, $data ) {
		$id      = ( 'id' == $by ) ? $value : 0;
		$site_id = ( 'site_id' == $by ) ? $value : 0;

		if ( $id ) {
			$current = $this->get_setting_by( 'id', $id );
		} elseif ( $site_id ) {
			$current = $this->get_setting_by( 'site_id', $site_id );
		} else {
			return false;
		}

		if ( empty( $current ) ) {
			// add new
			if ( $site_id ) {
				$update = array(
					'site_id'  => $site_id,
					'settings' => serialize( $data ),
				);
				return $this->update_setting( $update );
			} else {
				return false;
			}
		}

		$new_setting = unserialize( $current->settings );

		if ( ! is_array( $new_setting ) ) {
			$new_setting = array(); }

		foreach ( $data as $key => $value ) {
			$new_setting[ $key ] = $value;
		}

		return $this->update_setting(
			array(
				'site_id'  => $current->site_id,
				'settings' => serialize( $new_setting ),
			)
		);
	}

	public function update_extra_settings_fields_values_by( $site_id, $data ) {
		$current_settings = $this->get_extra_settings_by( 'site_id', $site_id );
		foreach ( $data as $key => $val ) {
			$current_settings[ $key ] = $val;
		}
		$this->update_extra_settings_fields_by( $site_id, $current_settings );
	}

	public function update_extra_settings_fields_by( $site_id, $data ) {
		if ( empty( $site_id ) ) {
				return;
		}

			$current = $this->get_extra_settings_by( 'site_id', $site_id );

		if ( empty( $current ) ) {
			if ( $site_id ) {
				$update = array(
					'site_id'        => $site_id,
					'extra_settings' => base64_encode( serialize( $data ) ),
				);
				return $this->update_setting( $update );
			} else {
				return false;
			}
		}

		$new_extra_settings = $current;

		if ( ! is_array( $new_extra_settings ) ) {
			$new_extra_settings = array(); }

		foreach ( $data as $key => $value ) {
			$new_extra_settings[ $key ] = $value;
		}

		return $this->update_setting(
			array(
				'site_id'        => $site_id,
				'extra_settings' => base64_encode( serialize( $new_extra_settings ) ),
			)
		);
	}

	public function get_extra_settings_by( $by = 'id', $value = null, $output = OBJECT ) {
			$site_settings = self::get_instance()->get_setting_by( $by, $value );
		$extra_settings    = array();
		if ( $site_settings ) {
			$extra_settings = unserialize( base64_decode( $site_settings->extra_settings ) );
		}
		if ( ! is_array( $extra_settings ) ) {
			$extra_settings = array();
		}
		return $extra_settings;
	}

	public function get_setting_fields_by( $by = 'id', $value = null ) {
		$site_settings = self::get_instance()->get_setting_by( $by, $value );
		$settings      = array();
		if ( $site_settings ) {
			$settings = unserialize( $site_settings->settings );
		}
		if ( ! is_array( $settings ) ) {
			$settings = array();
		}
		return $settings;
	}
	public function get_setting_by( $by = 'id', $value = null ) {
		global $wpdb;

		if ( empty( $by ) || empty( $value ) ) {
			return null;
		}

		$sql = '';
		if ( 'id' == $by ) {
			$sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wordfence' ) . ' WHERE `id`=%d ', $value );
		} elseif ( 'site_id' == $by ) {
			$sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wordfence' ) . ' WHERE `site_id` = %d ', $value );
		}

		$setting = null;
		if ( ! empty( $sql ) ) {
			$setting = $wpdb->get_row( $sql );
		}

		return $setting;
	}

	public function get_settings() {
		global $wpdb;
		$sql = 'SELECT * FROM ' . $this->table_name( 'wordfence' ) . ' WHERE 1 ';
		return $wpdb->get_results( $sql );
	}

	public function get_count_wfc() {
		global $wpdb;
		$sql = 'SELECT count(*) FROM ' . $this->table_name( 'wordfence' ) . ' WHERE 1 ';
		return $wpdb->get_results( $sql );
	}

	public function get_settings_fields( $fields = array(), $site_ids = array() ) {

		global $wpdb;
		$sql_site_ids = '';
		$str_site_ids = '';

		if ( is_array( $site_ids ) && count( $site_ids ) > 0 ) {
			$sql_site_ids = implode( ',', $site_ids );
			$sql_site_ids = ' AND `site_id` IN (' . $sql_site_ids . ')';
		}

		if ( is_array( $fields ) && count( $fields ) > 0 ) {
			$sql_fields = implode( ',', $fields );
		} else {
			$sql_fields = '*';
		}

		$sql = 'SELECT ' . $sql_fields . ' FROM ' . $this->table_name( 'wordfence' ) . ' WHERE 1 ' . $sql_site_ids;
		return $wpdb->get_results( $sql );
	}

	public function delete_wordfence( $site_ids ) {
		global $wpdb;
		if ( empty( $site_ids ) ) {
			return false;
		}
		$sql = $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wordfence' ) . ' WHERE `site_id` IN ( %s ) ', implode( ',', $site_ids ) );
		return $wpdb->query( $sql );
	}


	protected function escape( $data ) {
		global $wpdb;
		if ( function_exists( 'esc_sql' ) ) {
			return esc_sql( $data );
		} else {
			return $wpdb->escape( $data );
		}
	}

	public function query( $sql ) {
		if ( null == $sql ) {
			return false;
		}

		global $wpdb;
		$result = @self::_query( $sql, $wpdb->dbh );

		if ( ! $result || ( @self::num_rows( $result ) == 0 ) ) {
			return false;
		}

		return $result;
	}

	public static function _query( $query, $link ) {
		if ( self::use_mysqli() ) {
			return mysqli_query( $link, $query );
		} else {
			return mysql_query( $query, $link );
		}
	}

	public static function fetch_object( $result ) {
		if ( self::use_mysqli() ) {
			return mysqli_fetch_object( $result );
		} else {
			return mysql_fetch_object( $result );
		}
	}

	public static function free_result( $result ) {
		if ( self::use_mysqli() ) {
			return mysqli_free_result( $result );
		} else {
			return mysql_free_result( $result );
		}
	}

	public static function data_seek( $result, $offset ) {
		if ( self::use_mysqli() ) {
			return mysqli_data_seek( $result, $offset );
		} else {
			return mysql_data_seek( $result, $offset );
		}
	}

	public static function fetch_array( $result, $result_type = null ) {
		if ( self::use_mysqli() ) {
			return mysqli_fetch_array( $result, ( null == $result_type ? MYSQLI_BOTH : $result_type ) );
		} else {
			return mysql_fetch_array( $result, ( null == $result_type ? MYSQL_BOTH : $result_type ) );
		}
	}

	public static function num_rows( $result ) {
		if ( self::use_mysqli() ) {
			return mysqli_num_rows( $result );
		} else {
			return mysql_num_rows( $result );
		}
	}

	public static function is_result( $result ) {
		if ( self::use_mysqli() ) {
			return ( $result instanceof mysqli_result );
		} else {
			return is_resource( $result );
		}
	}

	public function get_results_result( $sql ) {
		if ( null == $sql ) {
			return null;
		}

		global $wpdb;

		return $wpdb->get_results( $sql, OBJECT_K );
	}
}
