<?php

/**
 * Suppress "error - 0 - No summary was found for this file" on phpdoc generation
 *
 * @package WPDataProjects\List_Table
 */

namespace WPDataProjects\List_Table {

	use WPDataAccess\Connection\WPDADB;
	use WPDataAccess\WPDA;
	use WPDataAccess\List_Table\WPDA_List_Table;
	use WPDataProjects\Data_Dictionary\WPDP_List_Columns_Cache;
	use WPDataAccess\Plugin_Table_Models\WPDP_Project_Design_Table_Model;

	/**
	 * Class WPDP_List_Table_Lookup extends WPDA_List_Table
	 *
	 * This class implements the lookup functionality. A lookup is the opposite of a one to many relationship.
	 * (1) A lookup provides the user with a listbox of possible values
	 * (2) A lookup offers the user comfort to search in lookup strings (e.g. titles or descriptions)
	 *
	 * @see WPDA_List_Table
	 *
	 * @author  Peter Schulz
	 * @since   2.0.0
	 */
	class WPDP_List_Table_Lookup extends WPDA_List_Table {

		/**
		 * Column options for the given table
		 *
		 * @var array|null
		 */
		protected $column_options_listtable;

		/**
		 * Options set name
		 *
		 * @var string
		 */
		protected $setname = 'default';

		protected $page_id			 = null;
		protected $has_default_where = false;

		/**
		 * WPDP_List_Table_Lookup constructor
		 *
		 * @param array $args
		 *
		 * @see WPDA_List_Table
		 */
		public function __construct( $args = array() ) {
			$args['pid'] = isset( $args['pid'] ) ? $args['pid'] : '';

			parent::__construct( $args );

			$this->setname                  = $this->wpda_list_columns->get_setname();
			$this->column_options_listtable = WPDP_Project_Design_Table_Model::get_column_options( $this->table_name, 'listtable', $this->setname, $this->schema_name );
			$this->has_default_where 	    = isset( $args['where_clause'] ) && null !== $args['where_clause'] && '' !== trim( $args['where_clause'] );
			$this->page_id					= $args['pid'];
		}

		/**
		 * Overwrites method get_sortable_columns
		 *
		 * @return array
		 * @see WPDA_List_Table::get_sortable_columns()
		 */
		public function get_sortable_columns() {
			if ( false !== strpos( $this->orderby, '-' ) ) {
				return array();
			}

			if ( null != $this->column_options_listtable ) {
				$columns = array();
				foreach ( $this->column_options_listtable as $column_option ) {
					if ( isset( $column_option->lookup ) && $column_option->lookup !== false ) {
						// Sorting on lookup columns is not possible.
					} else {
						$columns[ $column_option->column_name ] = array( $column_option->column_name, false );
					}
				}

				return $columns;
			} else {
				return parent::get_sortable_columns();
			}
		}

		/**
		 * Overwrites method column_default
		 *
		 * @param array  $item List of all available items
		 * @param string $column_name Database column name
		 *
		 * @return mixed|string
		 * @see WPDA_List_Table::column_default()
		 */
		public function column_default( $item, $column_name ) {
			if ( null != $this->column_options_listtable ) {
				foreach ( $this->column_options_listtable as $column_option ) {
					if ( isset( $column_option->lookup ) ) {
						if ( $column_option->column_name === $column_name && $column_option->lookup !== false ) {
							$column_options_relationships = WPDP_Project_Design_Table_Model::get_column_options( $this->table_name, 'relationships', $this->setname, $this->schema_name );
							if ( null !== $column_options_relationships ) {
								foreach ( $column_options_relationships['relationships'] as $column_options_relationship ) {
									if (
										(
											'lookup' === $column_options_relationship->relation_type ||
											'autocomplete' === $column_options_relationship->relation_type
										) &&
										$column_options_relationship->source_column_name[0] === $column_name
									) {
										if ( isset( $column_options_relationship->target_schema_name ) ) {
											$target_schema_name = $column_options_relationship->target_schema_name;
										} else {
											$target_schema_name = $this->schema_name;
										}
										$target_table_name  = $column_options_relationship->target_table_name;
										$target_column_name = $column_options_relationship->target_column_name[0];

										$data_type = null;
										foreach ( $column_options_relationships['table'] as $tableinfo ) {
											if ( $tableinfo->column_name === $column_name ) {
												$data_type = $tableinfo->data_type;
											}
										}

										$wpdadb = WPDADB::get_db_connection( $target_schema_name );
										if ( null !== $wpdadb ) {
											$where = '';
											for ( $i = 1; $i < count( $column_options_relationship->source_column_name ); $i++ ) {//phpcs:ignore - 8.1 proof
												if ( isset( $item[ $column_options_relationship->source_column_name[ $i ] ] ) ) {
													$value            = $item[ $column_options_relationship->source_column_name[ $i ] ];
													$data_type_lookup = null;
													foreach ( $column_options_relationships['table'] as $tableinfo ) {
														if ( $tableinfo->column_name === $column_options_relationship->source_column_name[ $i ] ) {
															$data_type_lookup = $tableinfo->data_type;
														}
													}
													if ( 'number' == WPDA::get_type( $data_type_lookup ) ) {
														$where .= $wpdadb->prepare( " and {$column_options_relationship->target_column_name[ $i ]} = %d", array( $value ) );
													} else {
														$where .= $wpdadb->prepare( " and {$column_options_relationship->target_column_name[ $i ]} = %s", array( $value ) );
													}
												}
											}

											$lookup_value = $this->column_lookup(
												$target_column_name,
												$target_table_name,
												$column_option->lookup,
												$item[ $column_name ],
												$data_type,
												$where,
												$target_schema_name
											);

											$item[ "lookup_id_$column_name" ]     = $item[ $column_name ];
											$item[ "lookup_column_$column_name" ] = $column_option->lookup;
											$item[ "lookup_value_$column_name" ]  = null === $lookup_value || '' === $lookup_value ? '' : $lookup_value;
										}
									}
								}
							}
						}
					}
				}
			}

			return parent::column_default( $item, $column_name );
		}

		/**
		 * Performs lookup query
		 *
		 * @param string $target_column_name Lookup key column
		 * @param string $target_table_name Lookup table name
		 * @param string $source_column_name Source column name
		 * @param mixed  $value Lookup key value
		 * @param string $data_type Lookup key data type
		 * @param string $and Extension of where clause
		 * @param string $target_schema_name Database schema name
		 *
		 * @return mixed Lookup value
		 */
		protected function column_lookup( $target_column_name, $target_table_name, $source_column_name, $value, $data_type, $and, $target_schema_name ) {
			$wpdadb = WPDADB::get_db_connection( $target_schema_name );
			if ( $wpdadb === null ) {
				return null;
			}

			// Value is taken from database query. No need to prepare.
			if ( '' === $target_schema_name ) {
				$lookup_sql_table_name = '`' . str_replace( '`', '', $target_table_name ) . '`';
			} else {
				$lookup_sql_table_name = "`{$wpdadb->dbname}`.`" . str_replace( '`', '', $target_table_name ) . '`';
			}
			$sql = 'select `' . str_replace( '`', '', $source_column_name ) . "` from $lookup_sql_table_name where `" . str_replace( '`', '', $target_column_name ) . '` = ';
			if ( 'number' === WPDA::get_type( $data_type ) ) {
				$sql = $wpdadb->prepare( $sql . "%d $and", array( $value ) );
			} else {
				$sql = $wpdadb->prepare( $sql . "%s $and", array( $value ) );
			}
			$set = $wpdadb->get_results( $sql, 'ARRAY_A' );

			if ( 1 === $wpdadb->num_rows ) {
				return $set[0][ $source_column_name ];
			} else {
				return null;
			}
		}

		/**
		 * Constructs the where clause
		 *
		 * Uses the table options to determine lookup table and columns. A subquery is added if a lookup is found.
		 */
		protected function construct_where_clause() {
			$perform_default_method = true;
			if ( null !== $this->search_value && '' !== $this->search_value ) {
				if ( null != $this->column_options_listtable ) {
					global $wpdb;
					$nowhere       = ( '' === $this->where );
					$search_values = '%' . esc_attr( $this->search_value ) . '%';
					$where_current = '';
					$where_first   = true;
					foreach ( $this->column_options_listtable as $column_option ) {
						if ( isset( $column_option->lookup ) && $column_option->lookup !== false ) {
							// Add a subquery for text columns.
							$column_options_relationships = WPDP_Project_Design_Table_Model::get_column_options( $this->table_name, 'relationships', $this->setname, $this->schema_name );
							if ( null !== $column_options_relationships ) {
								foreach ( $column_options_relationships['relationships'] as $column_options_relationship ) {
									if (
										(
											'lookup' === $column_options_relationship->relation_type ||
											'autocomplete' === $column_options_relationship->relation_type
										) &&
										$column_options_relationship->source_column_name[0] === $column_option->column_name
									) {
										$target_table_name  = str_replace( '`', '', $column_options_relationship->target_table_name );
										$target_column_name = str_replace( '`', '', $column_options_relationship->target_column_name[0] );

										$relationship_table         = WPDP_List_Columns_Cache::get_list_columns( $wpdb->dbname, $target_table_name, 'listtable', $this->setname );
										$relationship_table_columns = $relationship_table->get_table_columns();
										foreach ( $relationship_table_columns as $relationship_table_column ) {
											if ( $column_option->lookup === $relationship_table_column['column_name'] ) {
												if ( 'varchar' === $relationship_table_column['data_type'] || 'enum' === $relationship_table_column['data_type'] ) {
													if ( '' === $this->schema_name ) {
														$lookup_sql_table_name = "`$target_table_name`";
													} else {
														$lookup_sql_table_name = "`{$this->schema_name}`.`$target_table_name`";
													}
													$where_current_prepare = "`{$column_option->column_name}` in (select `$target_column_name` from $lookup_sql_table_name where `{$column_option->lookup}` like %s)";
													if ( ! $where_first ) {
														$where_current .= ' or ';
													}
													$where_current .= $wpdb->prepare( $where_current_prepare, $search_values ); // phpcs:ignore WordPress.DB.PreparedSQL
													$where_first    = false;
												}
												break;
											}
										}
									}
								}
							}
						} else {
							// This is already handled by the parent.
						}
					}

					$where_user_search = $this->get_where_clause();
					if ( '' !== $where_user_search ) {
						if ( '' === $where_current ) {
							$where_current = $where_user_search;
						} else {
							$where_current = "($where_current or $where_user_search)";
						}
					}

					if ( '' !== $where_current ) {
						$this->where            = $nowhere ? " where $where_current " : " {$this->where} and ($where_current) ";
						$perform_default_method = false;
					}
				}
			}

			if ( $perform_default_method ) {
				parent::construct_where_clause();
			} else {
				$whereclause = $this->get_where_clause();

				if ( '' === $this->where ) {
					$this->where = " where {$whereclause} ";
				}

				if ( ' (1=2) ' !== $whereclause ) {
					// TODO Add other (non lookup) columns
					// $this->where .= " and {$whereclause} "; // Needs to be tested
				}
			}
		}

		protected function get_order_by() {
			$orderby = parent::get_order_by();

			if ( '' === $orderby && null !== $this->orderby && '' !== trim( $this->orderby ) ) {
				$orderby =
					' order by ' .
					trim( str_replace( 'order by ', '', $this->orderby ) );
			}

			return $orderby;
		}

		protected function add_full_table_downloads_add_args() {
			if ( $this->has_default_where && '' !== trim( $this->page_id ) ) {
				$esc_attr = 'esc_attr';
				echo "<input type='hidden' name='pid_default_where' value='{$esc_attr( $this->page_id )}'/>";
			}
		}

	}

}
