<?php
class MainWP_Team_Control_DB {

	private $mainwp_users_management_db_version = '1.1';
	private $table_prefix;

	// Singleton
	private static $instance = null;

	static function get_instance() {
		if ( null == self::$instance ) {
			self::$instance = new MainWP_Team_Control_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() {

		/** @var $wpdb wpdb */
		if ( ! function_exists( 'mysqli_connect' ) ) {
			return false;
		}

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

	// Installs new DB
	public function install() {

		global $wpdb;
		$currentVersion = get_site_option( 'mainwp_users_management_db_version' );
		if ( $currentVersion == $this->mainwp_users_management_db_version ) {
			return; }
		$charset_collate = $wpdb->get_charset_collate();
		$sql             = array();

		$tbl = 'CREATE TABLE `' . $this->table_name( 'roles' ) . '` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(512) NOT NULL DEFAULT "",
`role_name_id` varchar(512) NOT NULL DEFAULT "",
`role_description` text NOT NULL,
`role_capabilities` longtext NOT NULL,
`type` tinyint(1) NOT NULL DEFAULT 0'; // type = 1 is default mainwp role
		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 );
		}

		// echo $wpdb->last_error;
		// exit("Last error");
		update_option( 'mainwp_users_management_db_version', $this->mainwp_users_management_db_version );
	}

	public function add_role( $role ) {

		/** @var $wpdb wpdb */
		global $wpdb;
		if ( ! empty( $role['role_name'] ) && ! empty( $role['role_description'] ) ) {
			if ( $current  = $this->get_roles_by( 'role_name', $role['role_name'] ) ) {
				return false; }

			if ( $wpdb->insert( $this->table_name( 'roles' ), $role ) ) {
				return $this->get_roles_by( 'id', $wpdb->insert_id );
			}
		}
		return false;
	}

	public function update_role( $id, $role ) {

		 /** @var $wpdb wpdb */
		global $wpdb;
		if ( ! empty( $id ) ) {
			if ( $wpdb->update( $this->table_name( 'roles' ), $role, array( 'id' => intval( $id ) ) ) ) {
				return $this->get_roles_by( 'id', $id ); }
		}
		return false;
	}

	public function get_roles_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( 'roles' ) . ' WHERE `id`=%d ', $value );
		} elseif ( 'role_name' == $by ) {
			$sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'roles' ) . " WHERE `role_name` = '%s' ", $value );
		} elseif ( 'role_name_id' == $by ) {
			$sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'roles' ) . " WHERE `role_name_id` = '%s' ", $value );
		}

		$role = null;

		if ( ! empty( $sql ) ) {
			$role = $wpdb->get_row( $sql );
		}
		return $role;
	}

	public function get_caps_by_role( $role ) {
		global $wpdb;
		if ( empty( $role ) ) {
			return array();
		}
		$sql  = $wpdb->prepare( 'SELECT role_capabilities FROM ' . $this->table_name( 'roles' ) . " WHERE `role_name_id` = '%s' ", $role );
		$caps = $wpdb->get_row( $sql );
		if ( $caps && isset( $caps->role_capabilities ) ) {
			return unserialize( base64_decode( $caps->role_capabilities ) );
		}
		return array();
	}



	public function get_roles() {
		global $wpdb;
		return $wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'roles' ) . ' WHERE 1 = 1 ORDER BY type DESC, role_name ASC' );
	}

	public function delete_role_by( $by = 'id', $value = null ) {
		global $wpdb;
		if ( 'id' == $by ) {
			if ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'roles' ) . ' WHERE id= %d ', $value ) ) ) {
				return true;
			}
		}
		return false;
	}

	protected function escape( $data ) {

		/** @var $wpdb wpdb */
		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; }
		/** @var $wpdb wpdb */
		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 );
		}
	}
}
