<?php
if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly
final class W2PShopDbHelper {
	public $wpdb;
	public function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
	}

	/**
	 * Get results from database table with optional where filter.
	 *
	 * @param string $table_name Table name to query*
	 * @param array $where Key Value pair to filter results from.
	 *                            Example: array('id' => '1')
	 *                            For IN clauses, use an array as the value:
	 *                            Example: array('id' => array(1, 2, 3)) will generate "WHERE `id` IN (1, 2, 3)"
	 * @param string $return_type Default is ARRAY_A
	 *                             Other list of possible return types are:
	 *                             OBJECT – result will be output as a numerically indexed array of row objects.
	 *                             OBJECT_K – result will be output as an associative array of row objects, using first column’s values as keys (duplicates will be discarded).
	 *                             ARRAY_A – result will be output as a numerically indexed array of associative arrays, using column names as keys.
	 *                             ARRAY_N – result will be output as a numerically indexed array of numerically indexed arrays.
	 * @param string $order_by
	 * @param array $columns
	 * @param bool $unique
	 *
	 * @return array
	 */
	public function get_results(
		string $table_name,
		array $where = array(),
		string $return_type = ARRAY_A,
		string $order_by = '',
		array $columns = array('*'),
		bool $unique = false
	): array {
		if ( ! $this->is_table_whitelisted( $table_name ) ) {
			throw new InvalidArgumentException( 'Invalid table name.' );
		}

		list( $sql, $where_values ) = $this->build_select_sql(
			$table_name,
			$where,
			$order_by,
			$columns,
			$unique
		);

		if ( ! empty( $where_values ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is prepared with placeholders and bound values; method approved during WP submission.
			$prepared_sql = $this->wpdb->prepare( $sql, ...$where_values );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No placeholders required; identifiers are escaped; approach approved during WP submission.
			$prepared_sql = $this->wpdb->prepare( $sql );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using a prepared SQL statement variable; reviewed and approved during WP submission.
		return $this->wpdb->get_results( $prepared_sql, $return_type );
	}

	private function build_select_sql(
		string $table_name,
		array $where,
		string $order_by,
		array $columns,
		bool $unique
	): array {
		$table_name_escaped = esc_sql( $this->wpdb->prefix . $table_name );

		// Validate and render columns
		$cols = $this->validate_select_columns( $table_name, $columns );

		$sql = "SELECT {$cols} FROM {$table_name_escaped}";
		$where_values = array();

		if ( ! empty( $where ) ) {
			$sql .= " WHERE ";
			$where_parts = array();
			foreach ( $where as $column => $value ) {
				$column_escaped = esc_sql( $column );
				if ( is_array( $value ) ) {
					if ( ! empty( $value ) ) {
						$placeholders = implode( ', ', array_fill( 0, count( $value ), '%s' ) );
						$where_parts[] = "`{$column_escaped}` IN ( {$placeholders} )";
						foreach ( $value as $item ) {
							$where_values[] = $item;
						}
					} else {
						$where_parts[] = "0 = 1"; // Empty IN -> no results
					}
				} else {
					$where_parts[] = "`{$column_escaped}` = %s";
					$where_values[] = $value;
				}
			}
			$sql .= implode( ' AND ', $where_parts );
		}

		$allowed_order_by = array( 'id', 'modify_date' );
		if ( in_array( $order_by, $allowed_order_by, true ) ) {
			$order_by_escaped = esc_sql( $order_by );
			$sql .= " ORDER BY `{$order_by_escaped}` DESC";
		}

		if ( $unique ) {
			$sql .= " LIMIT 1";
		}

		return array( $sql, $where_values );
	}

	private function validate_select_columns( string $table_name, array $columns ): string {
		if ( empty( $columns ) || ( count( $columns ) === 1 && $columns[0] === '*' ) ) {
			return '*';
		}

		$allowed = $this->get_table_columns( $table_name );
		if ( empty( $allowed ) ) {
			return '*';
		}

		$safe_cols = array();
		foreach ( $columns as $col ) {
			if ( $col === '*' ) { continue; }
			if ( in_array( $col, $allowed, true ) ) {
				$safe_cols[] = '`' . esc_sql( $col ) . '`';
			}
		}

		return empty( $safe_cols ) ? '*' : implode( ', ', $safe_cols );
	}

	private function get_table_columns( string $table_name ): array {
		static $cache = array();

		if ( isset( $cache[ $table_name ] ) ) {
			return $cache[ $table_name ];
		}

		$full_table = esc_sql( $this->wpdb->prefix . $table_name );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is validated via whitelist and escaped; placeholders cannot be used for identifiers in DESCRIBE.
		$sql = "DESC {$full_table}";
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No placeholders possible for identifier-only DESCRIBE; safe due to whitelist and esc_sql.
		$cols = $this->wpdb->get_col( $sql, 0 );

		$cols = is_array( $cols ) ? array_values( array_filter( array_map( 'strval', $cols ) ) ) : array();
		$cache[ $table_name ] = $cols;

		return $cols;
	}

	private function is_table_whitelisted( string $table ): bool {
		$allowed_tables = array(
			'w2pshop_pricematrix',
			'w2pshop_udraw_templates',
			'w2pshop_data_presets',
			'w2pshop_saved_data',
		);

		if ( ! in_array( $table, $allowed_tables, true ) ) {
			throw new InvalidArgumentException( 'Invalid table name.' );
		}
		return true;
	}

	/**
	 * Insert a record into database table
	 *
	 * @param  string $table_name
	 * @param  array  $data
	 * @return bool|int
	 */
	public function insert_record( string $table_name, array $data ) : bool|int {
		if ( empty( $data ) ) {
			return false; }

		return $this->wpdb->insert( $this->parse_table_name( $table_name ), $data );
	}

	/**
	 * Update a record in database table
	 *
	 * @param  string $table_name
	 * @param  array  $data
	 * @param  array  $where
	 * @return bool|int
	 */
	public function update_record( string $table_name, array $data, array $where ) : bool|int {
		if ( empty( $data ) || empty( $where ) ) {
			return false; }

		return $this->wpdb->update( $this->parse_table_name( $table_name ), $data, $where );
	}

	/**
	 * Delete a record from database table
	 *
	 * @param string $table_name
	 * @param array  $where
	 *
	 * @return bool|int
	 */
	public function delete_record( string $table_name, array $where ) : bool|int {
		if ( empty( $where ) ) {
			return false; }

		return $this->wpdb->delete( $this->parse_table_name( $table_name ), $where );
	}

	/**
	 * Parses the table name to ensure that it is prefixed with the correct WordPress DB prefix
	 *
	 * @param string $table_name
	 *
	 * @return string parsed table name with prefix ( If not already prefixed )
	 */
	private function parse_table_name( string $table_name ): string {
		return ( str_contains( $table_name, $this->wpdb->prefix ) ) ? $table_name : $this->wpdb->prefix . $table_name;
	}
}
