<?php
/**
 * Class for parameter-based Appointment querying.
 */

namespace JET_APB\Resources;

use JET_APB\Resources\Appointment_Model;

defined( 'ABSPATH' ) || exit;

class Appointment_Query {

	protected $query_vars = [];

	public function __construct( $args = [] ) {
		$this->query_vars = wp_parse_args( $args, $this->get_default_query_vars() );
	}

	/**
	 * Get the current query vars.
	 *
	 * @return array
	 */
	public function get_query_vars() {
		return $this->query_vars;
	}

	/**
	 * Get default query vars.
	 *
	 * Returns list of default valid query variables for appointments.
	 *
	 * @return array
	 */
	protected function get_default_query_vars() {
		return [
			'status'     => '',
			'type'       => '',
			'include'    => '',
			'exclude'    => '',
			'group_ID'   => '',
			'service'    => '',
			'provider'   => '',
			'order_id'   => '',
			'user_id'    => '',
			'date_query' => [],
			'meta_query' => [],
			'sorting'    => [],
			'limit'      => 0,
			'offset'     => 0,
			'return'     => 'objects',
		];
	}

	/**
	 * Get appointments.
	 *
	 * Returns appointments matching the current query vars.
	 *
	 * @return array|object of Appointments objects.
	 */
	public function get_appointments() {
		return $this->query( $this->get_query_vars() );
	}

    /**
	 * Return current appointments table name
	 *
	 * @return string
	 */
	protected function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'jet_appointments';
	}

	/**
	 * Query for appointments matching specific criteria.
	 *
	 * @param array $query_vars Query variables.
	 *
	 * @return array
	 */
	public function query( $query_vars ) {

		global $wpdb;

		$query_clause = [];

		if ( ! empty( $query_vars['include'] ) ) {
			$val = is_array( $query_vars['include'] ) ? implode( ", ", array_map( 'intval', $query_vars['include'] ) ) : $query_vars['include'];
			$query_clause[] = sprintf( "ID IN ( %s )", $val );
		} else {
			if ( ! empty( $query_vars['exclude'] ) ) {
				$val = is_array( $query_vars['exclude'] ) ? implode( ", ", array_map( 'intval', $query_vars['exclude'] ) ) : $query_vars['exclude'];
				$query_clause[] = sprintf( "ID NOT IN ( %s )", $val );
			}
		}

		foreach ( [ 'order_id', 'service', 'provider' ] as $col ) {
			if ( ! empty( $query_vars[ $col ] ) ) {
				$val = is_array( $query_vars[ $col ] ) ? implode( ", ", array_map( 'intval', $query_vars[ $col ] ) ) : $query_vars[ $col ];
				$query_clause[] = sprintf( "%s IN ( %s )", $col, $val );
			}
		}

		foreach ([ 'group_ID', 'user_id' ] as $col ) {

			if ( ! isset( $query_vars[ $col ] ) || '' === $query_vars[ $col ] || [] === $query_vars[ $col ] ) {
				continue;
			}

			$raw = is_array( $query_vars[ $col ] ) ? $query_vars[ $col ] : [ $query_vars[ $col ] ];

			$include_null = false;
			$ids          = [];

			foreach ( $raw as $single ) {

				if ( null === $single || 'NULL' === $single || 'null' === $single || '0' === $single || 0 === $single ) {
					$include_null = true;
					continue;
				}

				if ( is_numeric( $single ) ) {
					$ids[] = $single;
				}
			}

			$ids = array_values( array_unique( $ids ) );

			$parts = [];

			if ( ! empty( $ids ) ) {
				$parts[] = sprintf( "%s IN ( %s )", $col, implode( ', ', $ids ) );
			}

			if ( $include_null ) {
				$parts[] = sprintf( "%s IS NULL", $col );
			}

			if ( ! empty( $parts ) ) {
				$query_clause[] = '( ' . implode( ' OR ', $parts ) . ' )';
			}
		}
		
		foreach ( [ 'type', 'status' ] as $col ) {
			if ( ! empty( $query_vars[ $col ] ) ) {
				$val = is_array( $query_vars[ $col ] )
					? implode( "', '", array_map( 'esc_sql', $query_vars[ $col ] ) )
					: esc_sql( $query_vars[ $col ] );

				$query_clause[] = sprintf( "%s IN ( '%s' )", $col, $val );
			}
		}

		if ( ! empty( $query_vars['date_query'] ) ) {
			$date_query_clause = [];
			$date_query_rel    = 'AND';

			foreach ( $query_vars['date_query'] as $key => $clause ) {
				if ( 'relation' === $key ) {
					$date_query_rel = 'OR' === strtoupper( $clause ) ? 'OR' : 'AND';
					continue;
				}

				if ( ! is_array( $clause ) || empty( $clause['column'] ) ) {
					continue;
				}

				$col      = $clause['column'];
				$operator = ! empty( $clause['operator'] ) ? $clause['operator'] : '=';
				$value    = $clause['value'];

				if ( '' === $value || null === $value ) {
					continue;
				}

				$ts = is_numeric( $value ) ? (int) $value : strtotime( (string) $value );

				if ( ! $ts ) {
					continue;
				}

				$val_sql = (string) $ts;

				$date_query_clause[] = sprintf( "%s %s %s", $col, $operator, $val_sql );
			}

			if ( ! empty( $date_query_clause ) ) {
				$query_clause[] = sprintf( "( %s )", implode( ' ' . $date_query_rel . ' ', $date_query_clause ) );
			}
		}

		if ( ! empty( $query_vars['meta_query'] ) ) {
			$meta_query_clause = [];
			$meta_query_rel    = 'AND';

			$allowed_casts = [
				'CHAR'      => 'CHAR',
				'NUMERIC'   => 'SIGNED',
				'DECIMAL'   => 'DECIMAL',
				'SIGNED'    => 'SIGNED',
				'UNSIGNED'  => 'UNSIGNED',
				'DATE'      => 'DATE',
				'DATETIME'  => 'DATETIME',
				'TIME'      => 'TIME',
				'TIMESTAMP' => 'UNSIGNED',
				'BINARY'    => 'BINARY',
			];

			foreach ( $query_vars['meta_query'] as $key => $clause ) {
				if ( 'relation' === $key ) {
					$meta_query_rel = 'OR' === strtoupper( $clause ) ? 'OR' : 'AND';
					continue;
				}

				if ( ! is_array( $clause ) || empty( $clause['column'] ) ) {
					continue;
				}

				$operator = ! empty( $clause['operator'] ) ? $clause['operator'] : '=';
				$value    = $clause['value'] ?? false;

				$column_sql = $clause['column'];

				if ( ! empty( $clause['type'] ) ) {
					if ( isset( $allowed_casts[ $clause['type'] ] ) ) {
						$column_sql = sprintf( "CAST(%s AS %s)", $clause['column'], $allowed_casts[ $clause['type'] ] );
					}
				}

				if ( false === $value ) {
					$meta_query_clause[] = sprintf( "%s %s", $column_sql, $operator );
				} else {

					switch ( $operator ) {
						case 'LIKE':
						case 'NOT LIKE': 
							$value_sql = "'" . '%' . $wpdb->esc_like( $value ) . '%' . "'";
							break;

						case 'IN':
						case 'NOT IN':
							$parts = array_filter( array_map( 'trim', explode( ',', $value ) ) );

							if ( count( $parts ) > 1 ) {
								$escaped = array_map( function( $item ) {
									return "'" . esc_sql( $item ) . "'";
								}, $parts );

								$value_sql = '( ' . implode( ', ', $escaped ) . ' )';
							} else {
								$single = "'" . esc_sql( reset( $parts ) ) . "'";

								if ( 'NOT IN' === $operator ) {
									$operator = '!=';
								} else {
									$operator = '=';
								}

								$value_sql = $single;
							}
							break;

						case 'BETWEEN':
						case 'NOT BETWEEN':
							$parts = array_values( array_filter( array_map( 'trim', explode( ',', $value ) ) ) );

							$first_part  = $parts[0] ?? '';
							$second_part = $parts[1] ?? '';

							if ( ! empty( $first_part ) && ! empty( $second_part ) ) {

								$value_sql = sprintf(
									"'%s' AND '%s'",
									esc_sql( $first_part ),
									esc_sql( $second_part )
								);

							} else {

								$value_sql = "'" . esc_sql( $value ) . "'";

								if ( 'NOT BETWEEN' === $operator ) {
									$operator = '!=';
								} else {
									$operator = '=';
								}

							}
							break;

						default:
							$value_sql = is_array( $value )
								? sprintf( "%s AND %s", esc_sql( $value[0] ), esc_sql( $value[1] ) )
								: "'" . esc_sql( $value ) . "'";
					}

					$meta_query_clause[] = sprintf( "%s %s %s", $column_sql, $operator, $value_sql );
				}
			}

			if ( ! empty( $meta_query_clause ) ) {
				$query_clause[] = sprintf( "( %s )", implode( ' ' . $meta_query_rel . ' ', $meta_query_clause ) );
			}
		}

		$query = sprintf( "SELECT * FROM %s", $this->table_name() );

		if ( ! empty( $query_clause ) ) {
			$query .= sprintf( " WHERE %s", implode( ' AND ', $query_clause ) );
		}

		if ( ! empty( $query_vars['sorting'] ) ) {
			$sorting_query_clause = [];

			foreach ( $query_vars['sorting'] as $sorting ) {
				if ( empty( $sorting['orderby'] ) ) {
					continue;
				}
				$order = ! empty( $sorting['order'] ) ? $sorting['order'] : 'ASC';
				$sorting_query_clause[] = sprintf( "%s %s", $sorting['orderby'], $order );
			}

			if ( ! empty( $sorting_query_clause ) ) {
				$query .= sprintf( " ORDER BY %s", implode( ', ', $sorting_query_clause ) );
			}
		}

		if ( ! empty( $query_vars['limit'] ) && intval( $query_vars['limit'] ) > 0 ) {
			$query .= sprintf( " LIMIT %d", intval( $query_vars['limit'] ) );
			if ( ! empty( $query_vars['offset'] ) ) {
				$query .= sprintf( " OFFSET %d", intval( $query_vars['offset'] ) );
			}
		}

		$results = $wpdb->get_results( $query, ARRAY_A );

		return array_values(
			array_filter(
				array_map(
					function ( $item ) use ( $query_vars ) {
						$obj = new Appointment_Model( $item );

						return ( isset( $query_vars['return'] ) && 'arrays' === $query_vars['return'] )
							? $obj->to_array()
							: $obj;
					},
					$results
				)
			)
		);
	}
}
