<?php
namespace JET_APB\DB;

use JET_APB\Plugin;

/**
 * Database manager class
 */

// If this file is called directly, abort.
if ( ! defined( 'WPINC' ) ) {
	die;
}

/**
 * Define Base DB class
 */
abstract class Base {

	/**
	 * Check if booking DB table already exists
	 *
	 * @var bool
	 */
	public $table_exists = null;

	/**
	 * Return format flag;
	 *
	 * @var null
	 */
	public $format_flag = null;

	/**
	 * Stores latest queried result to use it
	 *
	 * @var null
	 */
	public $latest_result = null;

	/**
	 *
	 */
	public $defaults = array();

	/**
	 *
	 */
	public $queried_booking = false;

	/**
	 * Constructor for the class
	 */
	public function __construct() {
		if ( is_admin() && ! wp_doing_ajax() ) {
			add_action( 'init', array( $this, 'install_table' ) );
		}
	}

	/**
	 * Returns table name
	 * @return [type] [description]
	 */
	public function table() {
		return $this->wpdb()->prefix . 'jet_' . $this->table_slug();
	}

	/**
	 * Return table slug
	 *
	 * @return [type] [description]
	 */
	public function table_slug() {
		return '';
	}

	/**
	 * Returns defaults for the new item of current DB instance
	 * @return [type] [description]
	 */
	public function get_defaults() {
		return $this->defaults;
	}

	/**
	 * Insert booking
	 *
	 * @param  array  $booking [description]
	 * @return [type]          [description]
	 */
	public function insert( $data = array() ) {

		$data = $this->sanitize_data_before_db( $data );

		if ( ! empty( $this->get_defaults() ) ) {
			foreach ( $this->get_defaults() as $default_key => $default_value ) {
				if ( ! isset( $data[ $default_key ] ) ) {
					$data[ $default_key ] = $default_value;
				}
			}
		}

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

		if ( $inserted ) {
			return $this->wpdb()->insert_id;
		} else {
			return false;
		}
	}

	/**
	 * Update appointment info
	 *
	 * @param  array  $new_data [description]
	 * @param  array  $where    [description]
	 * @return [type]           [description]
	 */
	public function update( $new_data = array(), $where = array() ) {

		$old_data = [];

		if ( ! empty( $where['ID'] ) ) {
			$old_data = $this->query( $where );

			if ( ! empty( $old_data ) ) {
				$old_data = $old_data[0];
				$new_data = array_merge( $old_data, $new_data );
			}

		}

		$new_data = $this->sanitize_data_before_db( $new_data );

		$this->wpdb()->update( $this->table(), $new_data, $where );

		do_action( 'jet-apb/db/update/' . $this->table_slug(), $new_data, $where, $old_data );

		if ( isset( $new_data['status'] ) ) {
			$new_status = $new_data['status'];
			$old_status = ! empty( $old_data['status'] ) ? $old_data['status'] : false;

			if ( $new_status !== $old_status ) {
				do_action( 'jet-apb/db/update/' . $this->table_slug() . '/status', $new_data );
			}

		}

	}

	/**
	 * Allow child classes do own sanitize of the data before write it into DB
	 *
	 * @rewitten in JET_APB\DB\Appointments to ensure provider is stored correctly
	 *
	 * @param  array  $data [description]
	 * @return [type]       [description]
	 */
	public function sanitize_data_before_db( $data = array() ) {
		return $data;
	}

	/**
	 * Delete column
	 * @return [type] [description]
	 */
	public function delete( $where = array() ) {
		$this->wpdb()->delete( $this->table(), $where );
	}

	/**
	 * Check if booking table alredy exists
	 *
	 * @return boolean [description]
	 */
	public function is_table_exists() {

		if ( null !== $this->table_exists ) {
			return $this->table_exists;
		}

		$table = $this->table();

		if ( $table === $this->wpdb()->get_var( "SHOW TABLES LIKE '$table'" ) ) {
			$this->table_exists = true;
		} else {
			$this->table_exists = false;
		}

		return $this->table_exists;
	}

	/**
	 * Try to recreate DB table by request
	 *
	 * @return void
	 */
	public function install_table() {

		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}

		$this->create_table();

	}

	/**
	 * Returns WPDB instance
	 * @return [type] [description]
	 */
	public function wpdb() {
		global $wpdb;
		return $wpdb;
	}

	/**
	 * Create DB table for apartment units
	 *
	 * @return [type] [description]
	 */
	public function create_table( $delete_if_exists = false ) {

		if ( ! function_exists( 'dbDelta' ) ) {
			require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		}

		if ( $delete_if_exists && $this->is_table_exists() ) {
			$table = $this->table();
			$this->wpdb()->query( "DROP TABLE $table;" );
			$this->table_exists = null;
		}

		if ( $this->is_table_exists() ) {
			return;
		}

		$sql = $this->get_table_schema();

		dbDelta( $sql );

	}

	/**
	 * Insert new columns into existing bookings table
	 *
	 * @param  [type] $columns [description]
	 * @return [type]          [description]
	 */
	public function insert_table_columns( $columns = array() ) {

		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}

		$table          = $this->table();
		$columns_schema = '';

		foreach ( $columns as $column ) {
			$columns_schema .= " ADD COLUMN $column text,";
		}

		$columns_schema = rtrim( $columns_schema, ',' );

		$sql = "ALTER TABLE $table $columns_schema;";

		$this->wpdb()->query( $sql );

	}

	/**
	 * Check if booking DB column is exists
	 *
	 * @return [type] [description]
	 */
	public function get_column_list( $table = false ) {
		$table = $table ? $table : $this->table();

		return $this->wpdb()->get_col("DESC {$table}", 0);
	}

	/**
	 * Check if booking DB column is exists
	 *
	 * @return [type] [description]
	 */
	public function column_exists( $column ) {

		$table = $this->table();
		return $this->wpdb()->query( "SHOW COLUMNS FROM `$table` LIKE '$column'" );

	}

	/**
	 * Delete columns into existing bookings table
	 *
	 * @param  [type] $columns [description]
	 * @return [type]          [description]
	 */
	public function delete_table_columns( $columns ) {

		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}

		$table          = $this->table();
		$columns_schema = '';

		foreach ( $columns as $column ) {
			$columns_schema .= "DROP COLUMN $column,";
		}

		$columns_schema = rtrim( $columns_schema, ',' );

		$sql = "ALTER TABLE $table $columns_schema;";

		$this->wpdb()->query( $sql );

	}

	/**
	 * Add nested query arguments
	 *
	 * @param  [type]  $key    [description]
	 * @param  [type]  $value  [description]
	 * @param  boolean $format [description]
	 * @return [type]          [description]
	 */
	public function get_sub_query( $key, $value, $table_alias = '', $format = false ) {

		$query = '';
		$glue  = '';

		if ( ! $format ) {

			if ( false !== strpos( $key, '!' ) ) {
				$format = '`%1$s` != \'%2$s\'';
				$key    = ltrim( $key, '!' );
			} else {
				$format = '`%1$s` = \'%2$s\'';
			}

		}

		foreach ( $value as $child ) {
			$query .= $glue;
			$query .= sprintf( $format, esc_sql( $key ), esc_sql( $child ), esc_sql( $table_alias ) );
			$glue   = ' OR ';
		}

		return $query;

	}

	/**
	 * Return count of queried items
	 *
	 * @return [type] [description]
	 */
	public function count( $args = array(), $rel = 'AND' ) {

		$table = $this->table();
		$query = "SELECT count(*) FROM $table";

		if ( ! $rel ) {
			$rel = 'AND';
		}

		if ( isset( $args['after'] ) ) {
			$after = $args['after'];
			unset( $args['after'] );
			$args['ID>'] = $after;
		}

		if ( isset( $args['before'] ) ) {
			$before = $args['before'];
			unset( $args['before'] );
			$args['ID<'] = $before;
		}

		$query .= $this->add_where_args( $args, $rel );

		return $this->wpdb()->get_var( $query );

	}

	/**
	 * Add where arguments to query
	 *
	 * @param array  $args [description]
	 * @param string $rel  [description]
	 */
	public function add_where_args( $args = array(), $rel = 'AND', $table_alias = '' ) {

		$query      = '';
		$multi_args = false;

		if ( ! empty( $args ) ) {

			$query  .= ' WHERE ';
			$glue    = '';
			$props   = array();

			if ( count( $args ) > 1 ) {
				$multi_args = true;
			}

			foreach ( $args as $key => $value ) {

				$format = '%3$s%1$s = \'%2$s\'';

				$query .= $glue;

				if ( false !== strpos( $key, '!' ) ) {
					$key    = ltrim( $key, '!' );
					$format = '%3$s%1$s != \'%2$s\'';
				} elseif ( false !== strpos( $key, '><' ) ) {
					$key    = str_replace( '><', '', $key );
					$format = '%3$s%1$s BETWEEN %2$s';
				} elseif ( false !== strpos( $key, '<<' ) ) {
					$key    = rtrim( $key, '<<' );
					$format = '%3$s%1$s < \'%2$s\'';
				} elseif ( false !== strpos( $key, '>>' ) ) {
					$key    = rtrim( $key, '>>' );
					$format = '%3$s%1$s > \'%2$s\'';
				} elseif ( false !== strpos( $key, '>=' ) ) {
					$key    = rtrim( $key, '>=' );
					$format = '%3$s%1$s >= %2$d';
				} elseif ( false !== strpos( $key, '>' ) ) {
					$key    = rtrim( $key, '>' );
					$format = '%3$s%1$s > %2$d';
				} elseif ( false !== strpos( $key, '<=' ) ) {
					$key    = rtrim( $key, '<=' );
					$format = '%3$s%1$s <= %2$d';
				} elseif ( false !== strpos( $key, '<' ) ) {
					$key    = rtrim( $key, '<' );
					$format = '%3$s%1$s < %2$d';
				}

				if  ( is_array( $value ) && ! empty( $value['operator'] ) ){
					$query .= sprintf( '%3$s%1$s %4$s %2$s', esc_sql( $key ), esc_sql( $value['value'] ), $table_alias, esc_sql( $value['operator'] ) );
				} elseif ( is_array( $value ) ) {
					$query .= sprintf( '( %s )', $this->get_sub_query( $key, $value, $table_alias, $format ) );
				} else {
					$query .= sprintf( $format, esc_sql( $key ), esc_sql( $value ), $table_alias );
				}

				$glue = ' ' . $rel . ' ';

			}
		}

		return $query;

	}

	/**
	 * Add order arguments to query
	 *
	 * @param array $args [description]
	 */
	public function add_order_args( $order = array(), $table_alias = '' ) {

		$query = '';

		if ( ! empty( $order['orderby'] ) ) {

			$orderby = $order['orderby'];
			$order   = ! empty( $order['order'] ) ? $order['order'] : 'desc';
			$order   = strtoupper( $order );
			$query  .= " ORDER BY $table_alias$orderby $order";

		}

		return $query;

	}

	/**
	 * Clear table data
	 * @return [type] [description]
	 */
	public function clear() {
		$table = $this->table();
		$this->wpdb()->query( "TRUNCATE `$table`;" );
	}

	/**
	 * Set current format flag
	 *
	 * @param [type] $flag [description]
	 */
	public function set_format_flag( $flag = null ) {
		$this->format_flag = $flag;
	}

	/**
	 * Get current format flag
	 *
	 */
	public function get_format_flag() {
		if ( $this->format_flag ) {
			return $this->format_flag;
		} else {
			return ARRAY_A;
		}
	}

	/**
	 * Get current format flag
	 *
	 */
	public function get_max_int( $column = false, $table = false ) {
		if( ! $column ){
			return NULL;
		}

		$table = $table ? $table : $this->table();

		return intval( $this->wpdb()->get_var( "SELECT MAX( `$column` ) FROM `$table`" ) );
	}

	/**
	 * Get SQL query string
	 *
	 * @param  array   $args      [description]
	 * @param  integer $limit     [description]
	 * @param  integer $offset    [description]
	 * @param  array   $order     [description]
	 * @param  boolean $search    [description]
	 * @param  boolean $search_in [description]
	 * @param  string  $rel       [description]
	 * @return [type]             [description]
	 */
	public function get_query_string( $format = 'data', $args = array(), $limit = 0, $offset = 0, $order = array(), $search = false, $search_in = false, $rel = 'AND' ) {

		$table = $this->table();
		$table_alias = 'app';

		switch ( $format ) {
			case 'count':
				$select = "COUNT(*)";
				break;

			case 'external':
				$table_alias = 'app_ext';

				$app_table   = $this->wpdb()->prefix . 'jet_' . 'appointments';
				$app_columns = $this->wpdb()->get_col( "DESC " . $app_table );
				$ext_columns = $this->wpdb()->get_col( "DESC " . $this->table() );

				$select = '';

				foreach ( $app_columns as $app_column ) {
					if ( 'ID' === $app_column ) {
						$select = 'DISTINCT ' . $table_alias . '.' . $app_column;
					} elseif ( 'type' === $app_column ) {
						$string = '"external" as ' . $app_column;
						$select = $select . ',' . ' ' . $string;
					} elseif ( in_array( $app_column, $ext_columns ) ) {
						$select = $select . ',' . ' ' . $table_alias . '.' . $app_column;
					} else {
						$string = 'NULL as ' . $app_column;
						$select = $select . ',' . ' ' . $string;
					}
				}
				break;
			default:
				$select = "DISTINCT $table_alias.*";
				break;
		}

		$query = "SELECT $select FROM $table as $table_alias";
		$posts_table = $this->wpdb()->posts;
		$query .= $search ? " INNER JOIN $posts_table as posts ON ( $table_alias.service = posts.ID OR $table_alias.provider = posts.ID )" : '';

		if ( ! $rel ) {
			$rel = 'AND';
		}

		if ( isset( $args['after'] ) ) {
			$after = $args['after'];
			unset( $args['after'] );
			$args['ID>'] = $after;
		}

		if ( isset( $args['before'] ) ) {
			$before = $args['before'];
			unset( $args['before'] );
			$args['ID<'] = $before;
		}

		$by_user = ! empty( $args['by_user'] ) ? $args['by_user'] : false;

		if ( isset( $args['by_user'] ) ) {
			unset( $args['by_user'] );
		}

		$query .= $this->add_where_args( $args, $rel, "$table_alias." );

		if ( $by_user ) {
			$uid  = $by_user;
			$user = get_user_by( 'ID', $uid );

			if ( $user ) {
				if ( ! empty( $args ) ) {
					$st = $rel;
				} else {
					$st = 'WHERE';
				}

				$query .= ' ' . $st . " $table_alias.user_id = ". $uid;
			}

		}

		if ( $search && $search_in ) {

			$search_rel = empty( $args ) ? ' WHERE' : ' AND';
			$search_in  = str_replace(' ', '', $search_in );
			$search_in  = explode( ',', $search_in );

			$query .= "{$search_rel} ( posts.post_title LIKE '%{$search}%'";

			foreach ( $search_in as $key ) {
				$query .= " OR {$table_alias}.{$key} LIKE '%{$search}%'";
			}

			$query .= ")";
		}

		$query .= $this->add_order_args( $order, "$table_alias." );

		if ( intval( $limit ) > 0 ) {
			$limit  = absint( $limit );
			$offset = absint( $offset );
			$query .= " LIMIT $offset, $limit";
		}

		return $query;

	}

	/**
	 * Query data from db table
	 *
	 * @return [type] [description]
	 */
	public function query( $args = array(), $limit = 0, $offset = 0, $order = array(), $search = false, $search_in = false, $rel = 'AND', $show_external = false ) {

		$sync_events_from_calendar = Plugin::instance()->settings->get_integration_setting( 'google-calendar', 'sync_events_from_calendar' );

		if ( $show_external && $sync_events_from_calendar ) {
			$app_query = $this->get_query_string( 'data', $args, 0, 0, array(), $search, $search_in, $rel );
			$query     = Plugin::instance()->db->appointments_external->get_query_string_with_external( $app_query, $args, $limit, $offset, $order, $search, $search_in, $rel );
		} else {
			$query = $this->get_query_string( 'data', $args, $limit, $offset, $order, $search, $search_in, $rel );
		}

		$raw = $this->wpdb()->get_results( $query, $this->get_format_flag() );

		$this->format_flag = null;

		return $raw;

	}
}
