<?php
/**
 * QueryBuilder — Fluent SQL query builder for AdFuz.
 *
 * Refactored to use prepare() with placeholders for all dynamic values.
 *
 * @package AdFuz
 */

namespace AdFuz\Database;

use InvalidArgumentException;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class QueryBuilder {

	// -------------------------------------------------------------------------
	// Properties
	// -------------------------------------------------------------------------

	protected string $table;
	protected string $metaTable;
	protected array $where       = array();
	protected array $orderBy     = array();
	protected ?int $limit        = null;
	protected array $select      = array();
	protected array $joins       = array();
	protected string $tableAlias = '';
	protected array $groupBy     = array();
	protected array $having      = array();
	protected ?int $id           = null;

	// -------------------------------------------------------------------------
	// Constructor
	// -------------------------------------------------------------------------

	public function __construct( string $table ) {
		global $wpdb;
		$this->table     = $wpdb->prefix . $table;
		$this->metaTable = $this->table . '_meta';
	}

	// -------------------------------------------------------------------------
	// Chainable builder methods
	// -------------------------------------------------------------------------

	public function alias( string $alias ): static {
		$this->tableAlias = $alias;

		return $this;
	}

	public function select( ...$columns ): static {
		$this->select = is_array( $columns[0] ) ? $columns[0] : $columns;

		return $this;
	}

	public function groupBy( string ...$columns ): static {
		$this->groupBy = array_merge( $this->groupBy, $columns );

		return $this;
	}

	public function having( string $column, $operator = null, $value = null ): static {
		if ( 2 === func_num_args() ) {
			$value    = $operator;
			$operator = '=';
		}
		$this->having[] = array(
			'column'   => $column,
			'operator' => $operator,
			'value'    => $value,
		);

		return $this;
	}

	public function where( $column, $operator = null, $value = null ): static {
		if ( 2 === func_num_args() ) {
			$value    = $operator;
			$operator = '=';
		}
		$this->where[] = array(
			'type'     => 'AND',
			'column'   => $column,
			'operator' => $operator,
			'value'    => $value,
		);

		return $this;
	}

	public function orWhere( $column, $operator = null, $value = null ): static {
		if ( 2 === func_num_args() ) {
			$value    = $operator;
			$operator = '=';
		}
		$this->where[] = array(
			'type'     => 'OR',
			'column'   => $column,
			'operator' => $operator,
			'value'    => $value,
		);

		return $this;
	}

	public function whereIn( string $column, array $values ): static {
		if ( empty( $values ) ) {
			$this->where[] = array(
				'type'   => 'AND',
				'custom' => '1=0',
			);

			return $this;
		}

		$this->where[] = array(
			'type'     => 'AND',
			'column'   => $column,
			'operator' => 'IN',
			'value'    => $values,
		);

		return $this;
	}

	public function whereNotIn( string $column, array $values ): static {
		if ( empty( $values ) ) {
			return $this;
		}

		$this->where[] = array(
			'type'     => 'AND',
			'column'   => $column,
			'operator' => 'NOT IN',
			'value'    => $values,
		);

		return $this;
	}

	public function orderBy( $column, $direction = 'ASC' ): static {
		$direction = strtoupper( (string) $direction );
		if ( ! in_array( $direction, array( 'ASC', 'DESC' ), true ) ) {
			$direction = 'ASC';
		}
		$this->orderBy = array( $column, $direction );

		return $this;
	}

	public function limit( $count ): static {
		$this->limit = intval( $count );

		return $this;
	}

	// -------------------------------------------------------------------------
	// JOIN helpers
	// -------------------------------------------------------------------------

	public function join( string $table, string $first, string $second, string $alias = '' ): static {
		return $this->addJoin( 'INNER', $table, $first, $second, $alias );
	}

	public function leftJoin( string $table, string $first, string $second, string $alias = '' ): static {
		return $this->addJoin( 'LEFT', $table, $first, $second, $alias );
	}

	public function rightJoin( string $table, string $first, string $second, string $alias = '' ): static {
		return $this->addJoin( 'RIGHT', $table, $first, $second, $alias );
	}

	private function addJoin( string $type, string $table, string $first, string $second, string $alias = '' ): static {
		$this->joins[] = array(
			'type'   => $type,
			'table'  => $this->applyPrefix( $table ),
			'first'  => $first,
			'second' => $second,
			'alias'  => $alias,
		);

		return $this;
	}

	// -------------------------------------------------------------------------
	// Single-record retrieval
	// -------------------------------------------------------------------------

	public function find( $id ) {
		global $wpdb;
		$this->id = intval( $id );

		if ( null !== $this->id ) {
			$cacheKey = $this->table . $this->id;
			$cached   = wp_cache_get( $cacheKey, 'adfuz_data' );
			if ( false !== $cached ) {
				return $cached ?: null;
			}

			$table = $this->escapeIdentifierPath( $this->table );

			// Build select clause
			list( $selectSql, $selectVals ) = $this->buildSelectParts();

			$query   = "SELECT {$selectSql} FROM {$table} WHERE id = %d";
			$allVals = array_merge( $selectVals, array( $this->id ) );

			$row = $wpdb->get_row(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
				$wpdb->prepare( $query, ...$allVals ),
				ARRAY_A
			);

			if ( $row ) {
				wp_cache_set( $cacheKey, $row, 'adfuz_data', 3600 );
			}

			return $row ?: null;
		}

		return $this->get()[0] ?? null;
	}

	public function findWithMeta( $id ) {
		global $wpdb;
		$this->id = intval( $id );

		if ( null !== $this->id ) {
			$cacheKey = $this->table . $this->id;
			$cached   = wp_cache_get( $cacheKey, 'adfuz_data' );
			if ( false !== $cached ) {
				return $cached ?: null;
			}

			$table = $this->escapeIdentifierPath( $this->table );

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
			$row = $wpdb->get_row(
				$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $this->id ),
				ARRAY_A
			);

			$result = $row ? $this->mergeWithMeta( $row ) : null;
			if ( $result ) {
				wp_cache_set( $cacheKey, $result, 'adfuz_data', 3600 );
			}

			return $result;
		}

		return $this->get()[0] ?? null;
	}

	public function first() {
		$results = $this->limit( 1 )->get();

		return is_array( $results ) ? reset( $results ) : $results;
	}

	// -------------------------------------------------------------------------
	// Multi-record retrieval
	// -------------------------------------------------------------------------

	public function get(): array {
		global $wpdb;

		$selectSql                  = $this->buildSelectSql();
		$fromSql                    = $this->buildFromSql();
		$joinSql                    = $this->buildJoinSql();
		[ $whereSql, $whereVals ]   = $this->buildWhereParts();
		$groupBySql                 = $this->buildGroupBySql();
		[ $havingSql, $havingVals ] = $this->buildHavingParts();
		$orderBySql                 = $this->buildOrderBySql();
		[ $limitSql, $limitVals ]   = $this->buildLimitParts();

		$query  = "SELECT {$selectSql} FROM {$fromSql}{$joinSql}{$whereSql}{$groupBySql}{$havingSql}{$orderBySql}{$limitSql}";
		$values = array_merge( $whereVals, $havingVals, $limitVals );

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			$query = $wpdb->prepare( $query, ...$values );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $query, ARRAY_A );

		return $this->multipeMergeWithMeta( $rows ?? array() );
	}

	protected function buildSelectSql(): string {
		if ( empty( $this->select ) ) {
			return '*';
		}

		$columns = array_map( array( $this, 'escapeSqlExpression' ), $this->select );

		return implode( ', ', array_filter( $columns ) );
	}

	protected function buildFromSql(): string {
		$table = $this->escapeIdentifierPath( $this->table );

		if ( $this->tableAlias ) {
			return $table . ' ' . $this->escapeIdentifier( $this->tableAlias );
		}

		return $table;
	}

	protected function buildJoinSql(): string {
		if ( empty( $this->joins ) ) {
			return '';
		}

		$sql = '';
		foreach ( $this->joins as $join ) {
			$type = strtoupper( $join['type'] );
			if ( ! in_array( $type, array( 'INNER', 'LEFT', 'RIGHT' ), true ) ) {
				throw new InvalidArgumentException( 'Invalid join type.' );
			}

			$table = $this->escapeIdentifierPath( $join['table'] );
			if ( ! empty( $join['alias'] ) ) {
				$table .= ' ' . $this->escapeIdentifier( $join['alias'] );
			}

			$sql .= " {$type} JOIN {$table} ON " .
					$this->escapeColumn( $join['first'] ) .
					' = ' .
					$this->escapeColumn( $join['second'] );
		}

		return $sql;
	}

	protected function buildGroupBySql(): string {
		if ( empty( $this->groupBy ) ) {
			return '';
		}

		$cols = array_map( array( $this, 'escapeColumn' ), $this->groupBy );
		$cols = array_filter( $cols );

		return empty( $cols ) ? '' : ' GROUP BY ' . implode( ', ', $cols );
	}

	protected function buildOrderBySql(): string {
		if ( empty( $this->orderBy ) ) {
			return '';
		}

		$column    = $this->escapeColumn( $this->orderBy[0] );
		$direction = $this->orderBy[1];

		return " ORDER BY {$column} {$direction}";
	}

	protected function buildLimitParts(): array {
		if ( null === $this->limit ) {
			return array( '', array() );
		}

		return array( ' LIMIT %d', array( $this->limit ) );
	}

	// -------------------------------------------------------------------------
	// Build parts methods (each returns [$sql, $values])
	// -------------------------------------------------------------------------

	protected function buildSelectParts(): array {
		if ( empty( $this->select ) ) {
			return array( '*', array() );
		}

		$columns = array();
		$values  = array();

		foreach ( $this->select as $column ) {
			if ( is_string( $column ) ) {
				$columns[] = $this->escapeSqlExpression( $column );
			}
		}

		return array( ! empty( $columns ) ? implode( ', ', $columns ) : '*', $values );
	}

	protected function buildFromParts(): array {
		$table = $this->escapeIdentifierPath( $this->table );
		if ( $this->tableAlias ) {
			return array( $table . ' ' . $this->escapeIdentifier( $this->tableAlias ), array() );
		}

		return array( $table, array() );
	}

	protected function buildJoinParts(): array {
		if ( empty( $this->joins ) ) {
			return array( '', array() );
		}

		$sql    = '';
		$values = array();

		foreach ( $this->joins as $join ) {
			$type = strtoupper( $join['type'] );
			if ( ! in_array( $type, array( 'INNER', 'LEFT', 'RIGHT' ), true ) ) {
				throw new InvalidArgumentException( 'Invalid join type.' );
			}

			$table = $this->escapeIdentifierPath( $join['table'] );
			if ( ! empty( $join['alias'] ) ) {
				$table .= ' ' . $this->escapeIdentifier( $join['alias'] );
			}

			$first  = $this->escapeColumn( $join['first'] );
			$second = $this->escapeColumn( $join['second'] );

			$sql .= " {$type} JOIN {$table} ON {$first} = {$second}";
		}

		return array( $sql, $values );
	}

	protected function buildWhereParts(): array {
		if ( empty( $this->where ) ) {
			return array( '', array() );
		}

		$clauses = array();
		$values  = array();

		foreach ( $this->where as $condition ) {
			$prefix = empty( $clauses ) ? '' : $condition['type'] . ' ';

			if ( ! empty( $condition['custom'] ) ) {
				$clauses[] = $prefix . $condition['custom'];
				continue;
			}

			$column   = $this->escapeColumn( $condition['column'] );
			$operator = strtoupper( $condition['operator'] );

			if ( in_array( $operator, array( 'IN', 'NOT IN' ), true ) && is_array( $condition['value'] ) ) {
				$vals = array_values( $condition['value'] );
				if ( empty( $vals ) ) {
					$clauses[] = $prefix . ( $operator === 'IN' ? '1=0' : '1=1' );
					continue;
				}

				$placeholders = implode( ', ', array_fill( 0, count( $vals ), '%s' ) );
				$clauses[]    = $prefix . "{$column} {$operator} ({$placeholders})";
				$values       = array_merge( $values, $vals );
				continue;
			}

			$ph        = $this->inferPlaceholder( $condition['value'] );
			$clauses[] = $prefix . "{$column} {$operator} {$ph}";
			$values[]  = $condition['value'];
		}

		return array( ' WHERE ' . implode( ' ', $clauses ), $values );
	}

	protected function buildGroupByParts(): array {
		if ( empty( $this->groupBy ) ) {
			return array( '', array() );
		}

		$cols = array_filter(
			array_map( array( $this, 'escapeColumn' ), $this->groupBy )
		);

		if ( empty( $cols ) ) {
			return array( '', array() );
		}

		return array( ' GROUP BY ' . implode( ', ', $cols ), array() );
	}

	protected function buildHavingParts(): array {
		if ( empty( $this->having ) ) {
			return array( '', array() );
		}

		$clauses = array();
		$values  = array();

		foreach ( $this->having as $condition ) {
			$column    = $this->escapeSqlExpression( $condition['column'] );
			$operator  = $condition['operator'];
			$value     = $condition['value'];
			$ph        = $this->inferPlaceholder( $value );
			$clauses[] = "$column $operator $ph";
			$values[]  = $value;
		}

		return array( ' HAVING ' . implode( ' AND ', $clauses ), $values );
	}

	protected function buildOrderByParts(): array {
		if ( empty( $this->orderBy ) ) {
			return array( '', array() );
		}

		$column    = $this->escapeColumn( $this->orderBy[0] );
		$direction = $this->orderBy[1];

		return array( " ORDER BY {$column} {$direction}", array() );
	}

	public function exists(): bool {
		global $wpdb;

		list( $fromSql, $fromVals )   = $this->buildFromParts();
		list( $whereSql, $whereVals ) = $this->buildWhereParts();

		$query     = "SELECT 1 FROM {$fromSql} {$whereSql} LIMIT 1";
		$allValues = array_merge( $fromVals, $whereVals );

		if ( ! empty( $allValues ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return (bool) $wpdb->get_var( $wpdb->prepare( $query, ...$allValues ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return (bool) $wpdb->get_var( $query );
		}
	}

	public function count( $column = '*' ): int {
		global $wpdb;

		$col = '*' === $column ? '*' : $this->escapeColumn( $column );

		$fromSql                  = $this->buildFromSql();
		[ $whereSql, $whereVals ] = $this->buildWhereParts();

		$query = "SELECT COUNT({$col}) FROM {$fromSql}{$whereSql}";

		if ( ! empty( $whereVals ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			$query = $wpdb->prepare( $query, ...$whereVals );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->get_var( $query );
	}

	public function max( $column ) {
		global $wpdb;

		$col                          = $this->escapeColumn( $column );
		list( $fromSql, $fromVals )   = $this->buildFromParts();
		list( $whereSql, $whereVals ) = $this->buildWhereParts();

		$query     = "SELECT MAX({$col}) FROM {$fromSql} {$whereSql}";
		$allValues = array_merge( $fromVals, $whereVals );

		if ( ! empty( $allValues ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $wpdb->prepare( $query, ...$allValues ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $query );
		}
	}

	public function min( $column ) {
		global $wpdb;

		$col                          = $this->escapeColumn( $column );
		list( $fromSql, $fromVals )   = $this->buildFromParts();
		list( $whereSql, $whereVals ) = $this->buildWhereParts();

		$query     = "SELECT MIN({$col}) FROM {$fromSql} {$whereSql}";
		$allValues = array_merge( $fromVals, $whereVals );

		if ( ! empty( $allValues ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $wpdb->prepare( $query, ...$allValues ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $query );
		}
	}

	public function avg( $column ) {
		global $wpdb;

		$col                          = $this->escapeColumn( $column );
		list( $fromSql, $fromVals )   = $this->buildFromParts();
		list( $whereSql, $whereVals ) = $this->buildWhereParts();

		$query     = "SELECT AVG({$col}) FROM {$fromSql} {$whereSql}";
		$allValues = array_merge( $fromVals, $whereVals );

		if ( ! empty( $allValues ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $wpdb->prepare( $query, ...$allValues ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $query );
		}
	}

	public function sum( $column ) {
		global $wpdb;

		$col                          = $this->escapeColumn( $column );
		list( $fromSql, $fromVals )   = $this->buildFromParts();
		list( $whereSql, $whereVals ) = $this->buildWhereParts();

		$query     = "SELECT SUM({$col}) FROM {$fromSql} {$whereSql}";
		$allValues = array_merge( $fromVals, $whereVals );

		if ( ! empty( $allValues ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $wpdb->prepare( $query, ...$allValues ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_var( $query );
		}
	}

	public function paginate( $perPage = 10, $page = 1 ): array {
		global $wpdb;

		$perPage = max( 1, (int) $perPage );
		$page    = max( 1, (int) $page );
		$offset  = ( $page - 1 ) * $perPage;

		$selectSql                  = $this->buildSelectSql();
		$fromSql                    = $this->buildFromSql();
		$joinSql                    = $this->buildJoinSql();
		[ $whereSql, $whereVals ]   = $this->buildWhereParts();
		$groupBySql                 = $this->buildGroupBySql();
		[ $havingSql, $havingVals ] = $this->buildHavingParts();
		$orderBySql                 = $this->buildOrderBySql();

		$query  = "SELECT {$selectSql} FROM {$fromSql}{$joinSql}{$whereSql}{$groupBySql}{$havingSql}{$orderBySql} LIMIT %d OFFSET %d";
		$values = array_merge( $whereVals, $havingVals, array( $perPage, $offset ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
		$results = $wpdb->get_results( $wpdb->prepare( $query, ...$values ), ARRAY_A );

		$countQuery = "SELECT COUNT(*) FROM {$fromSql}{$whereSql}";

		$total = (int) $wpdb->get_var(
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			empty( $whereVals ) ? $countQuery : $wpdb->prepare( $countQuery, ...$whereVals )
		);

		return array(
			'data'         => $results ?? array(),
			'total'        => $total,
			'per_page'     => $perPage,
			'current_page' => $page,
			'last_page'    => (int) ceil( $total / $perPage ),
		);
	}

	public function delete(): int|false {
		global $wpdb;

		if ( empty( $this->where ) ) {
			return false;
		}

		list( $fromSql, $fromVals )   = $this->buildFromParts();
		list( $whereSql, $whereVals ) = $this->buildWhereParts();
		$pk                           = $this->escapeColumn( $this->getPrimaryKey() );

		// Get IDs to delete
		$idsQuery = "SELECT {$pk} FROM {$fromSql} {$whereSql}";
		$idsVals  = array_merge( $fromVals, $whereVals );

		if ( ! empty( $idsVals ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			$ids = $wpdb->get_col( $wpdb->prepare( $idsQuery, ...$idsVals ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			$ids = $wpdb->get_col( $idsQuery );
		}

		foreach ( (array) $ids as $id ) {
			wp_cache_delete( $this->table . $id, 'adfuz_data' );
		}

		// Delete query
		$deleteQuery = "DELETE FROM {$fromSql} {$whereSql}";

		if ( ! empty( $idsVals ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			$result = $wpdb->query( $wpdb->prepare( $deleteQuery, ...$idsVals ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
			$result = $wpdb->query( $deleteQuery );
		}

		$this->reset();

		return $result;
	}

	public function hardDelete( $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->delete( $this->table, array( 'id' => $id ) );
		if ( $this->metaTable ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->delete( $this->metaTable, array( $this->getForeignKey() => $id ) );
		}
		wp_cache_delete( $this->table . $id, 'adfuz_data' );

		return true;
	}

	public function softDelete( $id ): bool {
		global $wpdb;
		if ( null === $id ) {
			throw new \Exception( 'Cannot soft-delete without an ID.' );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return (bool) $wpdb->update(
			$this->table,
			array(
				'status'     => 'deleted',
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $id )
		);
	}

	public function insert( array $data ): ?int {
		global $wpdb;

		$insertImmutable = array( 'id', 'created_at' );
		$mainData        = array();
		$metaData        = array();

		foreach ( $data as $key => $value ) {
			if ( in_array( $key, $insertImmutable, true ) ) {
				continue;
			}
			if ( $this->hasColumn( $key ) ) {
				$mainData[ $key ] = $value;
			} else {
				$metaData[ $key ] = $value;
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->insert( $this->table, $mainData );
		$id = $wpdb->insert_id;

		if ( ! $id ) {
			return null;
		}

		if ( ! empty( $metaData ) && $this->metaTableExists() ) {
			$foreignKey = $this->getForeignKey();
			foreach ( $metaData as $key => $value ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$wpdb->insert(
					$this->metaTable,
					array(
						$foreignKey  => $id,
						'meta_key'   => sanitize_key( $key ),
						'meta_value' => maybe_serialize( $value ),
					)
				);
			}
		}

		return $id ?: null;
	}

	public function update( $id, array $data ): bool {
		global $wpdb;

		if ( null === $id ) {
			return false;
		}

		$immutable = array( 'id', 'created_at' );
		$mainData  = array();
		$metaData  = array();

		foreach ( $data as $key => $value ) {
			if ( in_array( $key, $immutable, true ) ) {
				continue;
			}
			if ( $this->hasColumn( $key ) ) {
				$mainData[ $key ] = $value;
			} else {
				$metaData[ $key ] = $value;
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->update( $this->table, $mainData, array( 'id' => $id ) );

		$foreignKey = $this->getForeignKey();

		foreach ( $metaData as $key => $value ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$exists = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->metaTable} WHERE $foreignKey = %d AND meta_key = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$id,
					$key
				)
			);

			if ( $exists ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$wpdb->update(
					$this->metaTable,
					array( 'meta_value' => $value ),
					array(
						$foreignKey => $id,
						'meta_key'  => $key,
					)
				);
			} else {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$wpdb->insert(
					$this->metaTable,
					array(
						$foreignKey  => $id,
						'meta_key'   => $key,
						'meta_value' => $value,
					)
				);
			}
		}

		wp_cache_delete( $this->table . $id, 'adfuz_data' );

		return true;
	}

	public function toArray( $results = null ): array {
		$results = $results ?? $this->get();
		if ( is_object( $results ) ) {
			return (array) $results;
		}
		if ( is_array( $results ) ) {
			return array_map( static fn( $item ) => (array) $item, $results );
		}

		return array();
	}

	public function raw( string $sql, array $args = array(), string $return = 'results' ) {
		global $wpdb;

		$sql = trim( $sql );
		if ( '' === $sql ) {
			return false;
		}

		if ( ! empty( $args ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$sql = $wpdb->prepare( $sql, ...$args );
		} elseif ( preg_match( '/(?<!%)%(?:s|d|f|i)/', $sql ) ) {
			throw new InvalidArgumentException( 'SQL placeholders found without prepare arguments.' );
		}

		switch ( $return ) {
			case 'row':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
				return $wpdb->get_row( $sql, ARRAY_A );
			case 'var':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
				return $wpdb->get_var( $sql );
			case 'col':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
				return $wpdb->get_col( $sql );
			case 'results':
			default:
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
				return $wpdb->get_results( $sql, ARRAY_A );
		}
	}

	public function toSql(): string {
		if ( ! ( defined( 'WP_DEBUG' ) && WP_DEBUG ) ) {
			return '/* toSql() is disabled in production. Enable WP_DEBUG to use it. */';
		}

		list( $selectSql, $selectVals )   = $this->buildSelectParts();
		list( $fromSql, $fromVals )       = $this->buildFromParts();
		list( $joinSql, $joinVals )       = $this->buildJoinParts();
		list( $whereSql, $whereVals )     = $this->buildWhereParts();
		list( $groupBySql, $groupByVals ) = $this->buildGroupByParts();
		list( $havingSql, $havingVals )   = $this->buildHavingParts();
		list( $orderBySql, $orderByVals ) = $this->buildOrderByParts();
		list( $limitSql, $limitVals )     = $this->buildLimitParts();

		$sql  = "SELECT {$selectSql} FROM {$fromSql}";
		$sql .= $joinSql;
		$sql .= $whereSql;
		$sql .= $groupBySql;
		$sql .= $havingSql;
		$sql .= $orderBySql;
		$sql .= $limitSql;

		return $sql;
	}

	protected function escapeIdentifierPath( string $identifier ): string {
		$identifier = trim( $identifier );
		if ( '' === $identifier ) {
			throw new InvalidArgumentException( 'Empty SQL identifier.' );
		}

		$parts = explode( '.', $identifier );
		$parts = array_map( array( $this, 'escapeIdentifier' ), $parts );

		return implode( '.', $parts );
	}

	protected function escapeIdentifier( string $identifier ): string {
		$identifier = trim( $identifier );
		if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $identifier ) ) {
			throw new InvalidArgumentException( 'Invalid SQL identifier.' );
		}

		return '`' . $identifier . '`';
	}

	protected function escapeColumn( string $column ): string {
		$column = trim( $column );

		if ( '*' === $column ) {
			return '*';
		}

		if ( preg_match( '/^[A-Za-z0-9_]+$/', $column ) ) {
			return '`' . $column . '`';
		}

		if ( preg_match( '/^[A-Za-z0-9_]+\.[A-Za-z0-9_]+$/', $column ) ) {
			$parts = explode( '.', $column );

			return '`' . $parts[0] . '`.`' . $parts[1] . '`';
		}

		if ( preg_match( '/^[A-Za-z0-9_]+(\.[A-Za-z0-9_]+)?\s+AS\s+[A-Za-z0-9_]+$/i', $column ) ) {
			$parts = preg_split( '/\s+AS\s+/i', $column );
			$name  = $this->escapeColumn( $parts[0] );
			$alias = $this->escapeIdentifier( $parts[1] );

			return $name . ' AS ' . $alias;
		}

		throw new InvalidArgumentException( 'Invalid SQL column.' );
	}

	protected function mergeWithMeta( array $row ): array {
		global $wpdb;
		if ( ! $this->metaTableExists() ) {
			return $row;
		}
		$foreignKey = $this->getForeignKey();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$meta = $wpdb->get_results(
			$wpdb->prepare( "SELECT meta_key, meta_value FROM {$this->metaTable} WHERE $foreignKey = %d", $row['id'] ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);
		foreach ( (array) $meta as $metaRow ) {
			$row[ $metaRow['meta_key'] ] = $metaRow['meta_value'];
		}

		return $row;
	}

	protected function multipeMergeWithMeta( array $rows ): array {
		$results = array();
		foreach ( $rows as $row ) {
			$results[ $row['id'] ?? count( $results ) ] = isset( $row['id'] )
				? $this->mergeWithMeta( $row )
				: $row;
		}

		return array_values( $results );
	}

	protected function metaTableExists(): bool {
		global $wpdb;
		static $cache = array();
		if ( isset( $cache[ $this->metaTable ] ) ) {
			return $cache[ $this->metaTable ];
		}
		$result                    = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $this->metaTable ) );
		$cache[ $this->metaTable ] = (bool) $result;

		return $cache[ $this->metaTable ];
	}

	protected function reset(): void {
		$this->where      = array();
		$this->orderBy    = array();
		$this->limit      = null;
		$this->joins      = array();
		$this->tableAlias = '';
		$this->groupBy    = array();
		$this->having     = array();
		$this->select     = array();
	}

	protected function getForeignKey(): string {
		$base     = str_replace( $GLOBALS['wpdb']->prefix . 'adfuz_', '', $this->table );
		$singular = rtrim( $base, 's' );

		return $singular . '_id';
	}

	protected function escapeSqlExpression( string $expression ): string {
		$expression = trim( $expression );
		if ( '' === $expression ) {
			throw new InvalidArgumentException( 'Empty SQL expression.' );
		}

		// Plain identifier or aliased identifier.
		if (
			'*' === $expression ||
			preg_match( '/^[A-Za-z0-9_]+$/', $expression ) ||
			preg_match( '/^[A-Za-z0-9_]+\.[A-Za-z0-9_]+$/', $expression ) ||
			preg_match( '/^[A-Za-z0-9_]+(?:\.[A-Za-z0-9_]+)?\s+AS\s+[A-Za-z0-9_]+$/i', $expression )
		) {
			return $this->escapeColumn( $expression );
		}

		// COUNT(*) [AS alias]
		if ( preg_match( '/^(COUNT)\s*\(\s*\*\s*\)(?:\s+AS\s+([A-Za-z0-9_]+))?$/i', $expression, $matches ) ) {
			$function = strtoupper( $matches[1] );
			$alias    = ! empty( $matches[2] ) ? ' AS ' . $this->escapeIdentifier( $matches[2] ) : '';

			return $function . '(*)' . $alias;
		}

		// COUNT(col), SUM(col), AVG(col), MIN(col), MAX(col) [AS alias]
		if ( preg_match( '/^(COUNT|SUM|AVG|MIN|MAX)\s*\(\s*([A-Za-z0-9_]+(?:\.[A-Za-z0-9_]+)?)\s*\)(?:\s+AS\s+([A-Za-z0-9_]+))?$/i', $expression, $matches ) ) {
			$function = strtoupper( $matches[1] );
			$column   = $this->escapeColumn( $matches[2] );
			$alias    = ! empty( $matches[3] ) ? ' AS ' . $this->escapeIdentifier( $matches[3] ) : '';

			return $function . '(' . $column . ')' . $alias;
		}

		throw new InvalidArgumentException( 'Invalid SQL expression.' );
	}

	protected function hasColumn( string $column ): bool {
		global $wpdb;
		$table = $this->escapeIdentifierPath( $this->table );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$columns = $wpdb->get_col( "DESC {$table}", 0 );

		return in_array( $column, $columns, true );
	}

	protected function getPrimaryKey(): string {
		return str_ends_with( $this->table, '_meta' ) ? 'meta_id' : 'id';
	}

	private function applyPrefix( string $table ): string {
		global $wpdb;
		if ( str_starts_with( $table, $wpdb->prefix ) ) {
			return $table;
		}

		return $wpdb->prefix . $table;
	}

	private function inferPlaceholder( $value ): string {
		if ( is_int( $value ) ) {
			return '%d';
		}
		if ( is_float( $value ) ) {
			return '%f';
		}

		return '%s';
	}

	// -------------------------------------------------------------------------
	// Static utilities
	// -------------------------------------------------------------------------

	public static function metaTableOf( string $table ): string {
		global $wpdb;

		return $wpdb->prefix . $table . '_meta';
	}

	public static function isMetaKey( string $key ): bool {
		return in_array( $key, array( 'meta_key', 'meta_value' ), true );
	}

	public static function now(): string {
		return current_time( 'mysql' );
	}

	public static function formatDateTime( string $datetime ): string {
		return date_i18n( get_option( 'date_format' ) . ' ' . get_option( 'time_format' ), strtotime( $datetime ) );
	}
}