<?php
namespace JVBase\managers;

use Exception;

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

/**
 * Custom Table Helper
 *
 * Provides consistent interface for CRUD operations on custom tables
 * Used by routes that interact with custom tables defined in CheckCustomTables.php
 *
 * @example
 * $table = new CustomTable('favourites');
 * $result = $table->insert(['user_id' => 1, 'type' => 'tattoo', 'target_id' => 123]);
 */
class CustomTable
{
	protected \wpdb $wpdb;
	protected string $tableName;
	protected string $definition;
	protected string $fullTableName;
	protected bool $useTransactions;
	protected array $columns;
	protected array $keys = [];
	protected array $constraints =[];

	/** @var array<string, self> Instance cache for fluent interface */
	protected static array $instances = [];
	protected static string $charsetCollate;

	protected static string $userTable;
	protected static string $userIDType;
	protected static string $termTable;
	protected static string $termIDType;
	protected static string $postIDType;
	protected static string $postTable;

	protected Cache $cache;

	/**
	 * Fluent factory method
	 *
	 * @param string $tableName Table name without prefix/BASE
	 * @return self
	 *
	 * @example CustomTable::for('favourites')->insert($data);
	 */
	public static function for(string $tableName): self
	{
		if (!isset(self::$instances[$tableName])) {
			self::$instances[$tableName] = new self($tableName);
		}


		return self::$instances[$tableName];
	}
	public static function destroyInstance(string $tableName):void
	{
		if (isset(self::$instances[$tableName])) {
			unset(self::$instances[$tableName]);
		}
	}
	public static function getInstance(string $tableName):self|false
	{
		if (!isset(self::$instances[$tableName])) {
			return false;
		}
		return self::$instances[$tableName];
	}

	public function ensureDefined() {
		$this->ensureUserTable();
		$this->ensureUserIDType();
		$this->ensureTermIDType();
		$this->ensurePostIDType();
	}
		protected function ensureUserTable():void
		{
			if (!isset(static::$userTable)) {
				static::$userTable = is_multisite() ? $this->getMultisiteUsersTable() : $this->wpdb->users;
			}
		}
			protected function getMultisiteUsersTable():string
			{
				$siteUsersTable = $this->wpdb->prefix . 'users';
				$siteExists = $this->wpdb->get_var(
					$this->wpdb->prepare("SHOW TABLES LIKE %s", $siteUsersTable)
				);
				if ($siteExists) {
					return $siteUsersTable;
				}
				//fallback to main one
				return $this->wpdb->users;
			}
		protected function ensureUserIDType():void
		{
			if (!isset(static::$userIDType)) {
				$this->ensureUserTable();
				static::$userIDType = $this->getColumnType(static::$userTable, 'ID');
			}
		}

		protected function ensureTermIDType():void
		{
			if (!isset(static::$termIDType)) {
				static::$termIDType = $this->getColumnType($this->wpdb->terms, 'term_id');
			}
		}

		protected function ensureTermTable():void
		{
			if (!isset(static::$termTable)) {
				static::$termTable = $this->wpdb->terms;
			}
		}

		protected function ensurePostIDType():void
		{
			if (!isset(static::$postIDType)) {
				static::$postIDType = $this->getColumnType($this->wpdb->posts, 'ID');
			}
		}
		protected function ensurePostTable():void
		{
			if (!isset(static::$postTable)) {
				static::$postTable = $this->wpdb->posts;
			}
		}

	/**
	 *
	 * @param array $columns An array of $columnName => $columnDefinition
	 * @return $this
	 */
	public function setColumns(array $columns):self
	{
		$this->columns = $columns;
		return $this;
	}

	/**
	 * @param array $keys An array of {string} $keys. If a $key is an array, you can define a custom $key => $value, example: 'UNIQUE' => $value, or 'PRIMARY' => $value
	 * @return $this
	 */
	public function setKeys(array $keys):self
	{
		$this->keys = $keys;
		return $this;
	}

	/**
	 * @param array $constraints an array of arrays, each value a $constraint => $references
	 * @return $this
	 */
	public function setConstraints(array $constraints):self
	{
		$this->constraints = $constraints;
		return $this;
	}

	public function defineTable(): self
	{
		if (empty($this->columns)) {
			error_log('[CustomTable] No columns defined for ' . $this->tableName);
			return $this;
		}

		$parts = [];

		// Columns
		foreach ($this->columns as $name => $type) {
			$parts[] = "`{$name}` {$type}";
		}

		// Keys
		if (empty($this->keys)) {
			$parts[] = 'PRIMARY KEY (`' . array_key_first($this->columns) . '`)';
		} else {
			foreach ($this->keys as $key) {
				if (is_array($key)) {
					$value = $key['value'];
					// Ensure value is wrapped in parentheses
					if (!str_contains(trim($value), '(`')) {
						$value = '(`' . $value . '`)';
					}
					$parts[] = $key['key'] . ' KEY ' . $value;
				} else {
					$parts[] = 'KEY ' . $key;
				}
			}
		}

		// Constraints
		foreach ($this->constraints as $constraint) {
			$parts[] = $constraint;
		}

		$this->definition = "(\n    " . implode(",\n    ", $parts) . "\n)";
		$this->createTable();
		return $this;
	}

	public static function ensureTables():void
	{
		require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

		foreach (self::$instances as $instance) {
			if (!$instance->wpdb->get_var("SHOW TABLES LIKE '{$instance->fullTableName}'")) {
				$instance->createTable();
			}
		}
	}

	protected function createTable():void
	{
		if (!$this->definition && empty($this->definition)) {
			error_log('[CustomTable]No definition set for '.$this->tableName);
			return;
		}
		if ($this->wpdb->get_var("SHOW TABLES LIKE '{$this->fullTableName}'")) {
			return;
		}
		require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
		error_log('Creating Database Table: '. $this->tableName);
		$charset = self::$charsetCollate;
		$schema = "CREATE TABLE IF NOT EXISTS {$this->fullTableName} {$this->definition} {$charset}";
		$this->wpdb->flush();

		$hasForeignKey = stripos($schema, 'FOREIGN KEY') !== false;
		if ($hasForeignKey) {
			$result = $this->wpdb->query($schema);
			$success = ($result !== false && !$this->wpdb->last_error);
		} else {
			dbDelta($schema.';');
			$success = !$this->wpdb->last_error;
		}

		if (!$success) {
			$error_msg = "SQL Error creating table {$this->tableName}: " . $this->wpdb->last_error;
			error_log($error_msg);
			error_log("Failed SQL Query: " . $schema);
		} elseif ($this->wpdb->get_var("SHOW TABLES LIKE '{$this->fullTableName}'")) {
			error_log("Successfully created table: {$this->fullTableName}");
		}
	}

	/**
	 * Clear instance cache (useful for testing)
	 */
	public static function clearCache(): void
	{
		self::$instances = [];
	}

	/**
	 * @param string $tableName Table name without prefix/BASE (e.g., 'favourites', 'notifications')
	 * @param bool $useTransactions Whether to auto-wrap operations in transactions
	 */
	public function __construct(string $tableName, bool $useTransactions = false)
	{
		global $wpdb;
		$this->wpdb = $wpdb;
		$this->tableName = $tableName;
		$this->fullTableName = $wpdb->prefix . apply_filters('jvb_base', BASE) . $tableName;
		$this->useTransactions = $useTransactions;

		$this->cache = Cache::for($tableName);

		$usersStatus = $this->wpdb->get_row("SHOW TABLE STATUS LIKE '{$this->wpdb->users}'");
		$parentCollation = $usersStatus->Collation ?? 'utf8mb4_general_ci';
		self::$charsetCollate = "DEFAULT CHARACTER SET utf8mb4 COLLATE {$parentCollation}";

	}

	// =========================================================================
	// FLUENT QUERY BUILDER
	// =========================================================================

	/** @var array Query builder state */
	protected array $builder = [];

	/**
	 * Start a fluent query - set WHERE conditions
	 *
	 * @param array $conditions Associative array of column => value
	 * @return self
	 *
	 * @example CustomTable::for('favourites')->where(['user_id' => 1])->get();
	 */
	public function where(array $conditions): self
	{
		$this->builder['where'] = $conditions;
		return $this;
	}

	/**
	 * Set ORDER BY
	 *
	 * @param string $column Column to order by
	 * @param string $direction ASC or DESC
	 * @return self
	 */
	public function orderBy(string $column, string $direction = 'DESC'): self
	{
		$this->builder['orderby'] = $column;
		$this->builder['order'] = strtoupper($direction);
		return $this;
	}

	/**
	 * Set LIMIT
	 *
	 * @param int $limit Number of records
	 * @param int $offset Optional offset
	 * @return self
	 */
	public function limit(int $limit, int $offset = 0): self
	{
		$this->builder['limit'] = $limit;
		$this->builder['offset'] = $offset;
		return $this;
	}

	/**
	 * Execute the built query and get results
	 *
	 * @param string $output OBJECT, ARRAY_A, or ARRAY_N
	 * @return array
	 */
	public function getResults(string $output = OBJECT): array
	{
		$results = $this->getMany($this->builder, $output);
		$this->resetBuilder();
		return $results;
	}

	/**
	 * Execute the built query and get first result
	 *
	 * @param string $output OBJECT, ARRAY_A, or ARRAY_N
	 * @return object|array|null
	 */
	public function first(string $output = OBJECT): object|array|null
	{
		$this->builder['limit'] = 1;
		$results = $this->getMany($this->builder, $output);
		$this->resetBuilder();
		return $results[0] ?? null;
	}

	/**
	 * Count records with current builder state
	 *
	 * @return int
	 */
	public function countResults(): int
	{
		$where = $this->builder['where'] ?? [];
		$count = $this->count($where);
		$this->resetBuilder();
		return $count;
	}

	/**
	 * Check if any records exist with current builder state
	 *
	 * @return bool
	 */
	public function existsInQuery(): bool
	{
		return $this->countResults() > 0;
	}

	/**
	 * Delete records matching current builder state
	 *
	 * @return int|false Number of deleted rows
	 */
	public function deleteResults(): int|false
	{
		$where = $this->builder['where'] ?? [];
		$result = $this->delete($where);
		$this->resetBuilder();
		return $result;
	}

	/**
	 * Update records matching current builder state
	 *
	 * @param array $data Data to update
	 * @return int|false Number of updated rows
	 */
	public function updateResults(array $data): int|false
	{
		$where = $this->builder['where'] ?? [];
		$result = $this->update($data, $where);
		$this->resetBuilder();
		return $result;
	}

	/**
	 * Reset query builder state
	 */
	protected function resetBuilder(): void
	{
		$this->builder = [];
	}

	// =========================================================================
	// CREATE OPERATIONS
	// =========================================================================

	/**
	 * Insert a single record
	 *
	 * @param array $data Associative array of column => value
	 * @param array|null $format Optional array of format strings (%d, %s, %f)
	 * @return int|false Insert ID on success, false on failure
	 *
	 * @example
	 * $id = $table->insert([
	 *     'user_id' => 1,
	 *     'type' => 'tattoo',
	 *     'target_id' => 123,
	 *     'date_added' => current_time('mysql')
	 * ]);
	 */
	public function insert(array $data, ?array $format = null): int|false
	{
		// Auto-add created_at if column exists and not provided
		if (!isset($data['created_at']) && $this->hasColumn('created_at')) {
			$data['created_at'] = current_time('mysql');
		}

		$result = $this->wpdb->insert(
			$this->fullTableName,
			$data,
			$format
		);

		if ($result === false) {
			$this->logError('insert', $data);
			return false;
		}

		$this->cache->flush();

		return $this->wpdb->insert_id;
	}

	/**
	 * Alias for insert() - more semantic for fluent interface
	 *
	 * @param array $data Data to insert
	 * @return int|false Insert ID on success
	 *
	 * @example CustomTable::for('favourites')->create(['user_id' => 1]);
	 */
	public function create(array $data): int|false
	{
		return $this->insert($data);
	}

	/**
	 * Find or create a record
	 *
	 * @param array $searchData Data to search for
	 * @param array $createData Optional additional data for creation
	 * @return array ['id' => int, 'created' => bool, 'record' => object]
	 *
	 * @example
	 * $result = CustomTable::for('favourites')->findOrCreate(
	 *     ['user_id' => 1, 'target_id' => 123],
	 *     ['type' => 'tattoo']
	 * );
	 * // Returns: ['id' => 456, 'created' => false, 'record' => object]
	 */
	public function findOrCreate(array $searchData, array $createData = []):int|false
	{
		$record = $this->get($searchData);

		if ($record) {
			if (!empty($createData)) {
				return $this->update($createData, $searchData);
			}
			return $record->id;
		}

		$data = array_merge($searchData, $createData);
		return $this->insert($data);
	}

	/**
	 * Bulk insert multiple records efficiently
	 *
	 * @param array $rows Array of associative arrays
	 * @param array $columns Column names (must be same for all rows)
	 * @return int|false Number of rows inserted, false on failure
	 *
	 * @example
	 * $count = $table->bulkInsert([
	 *     ['user_id' => 1, 'type' => 'tattoo', 'target_id' => 123],
	 *     ['user_id' => 1, 'type' => 'tattoo', 'target_id' => 456],
	 * ], ['user_id', 'type', 'target_id']);
	 */
	public function bulkInsert(array $rows, array $columns): int|false
	{
		if (empty($rows)) {
			return 0;
		}

		// Auto-add created_at if column exists
		if ($this->hasColumn('created_at') && !in_array('created_at', $columns)) {
			$columns[] = 'created_at';
			$now = current_time('mysql');
			foreach ($rows as &$row) {
				$row['created_at'] = $now;
			}
		}

		$placeholders = [];
		$values = [];

		foreach ($rows as $row) {
			$row_placeholders = [];
			foreach ($columns as $column) {
				$value = $row[$column] ?? null;
				$values[] = $value;
				$row_placeholders[] = $this->getPlaceholder($value);
			}
			$placeholders[] = "(" . implode(',', $row_placeholders) . ")";
		}

		$columns_escaped = array_map(function($col) {
			return "`{$col}`";
		}, $columns);

		$query = "INSERT INTO {$this->fullTableName}
				  (" . implode(',', $columns_escaped) . ")
				  VALUES " . implode(',', $placeholders);

		$result = $this->wpdb->query($this->wpdb->prepare($query, $values));

		if ($result === false) {
			$this->logError('bulkInsert', ['rows' => count($rows)]);
			return false;
		}

		return $result;
	}

	// =========================================================================
	// READ OPERATIONS
	// =========================================================================

	/**
	 * Get a single record
	 *
	 * @param array $where Associative array of column => value conditions
	 * @param string $output OBJECT, ARRAY_A, or ARRAY_N
	 * @return object|array|null
	 *
	 * @example
	 * $fav = $table->get(['user_id' => 1, 'target_id' => 123]);
	 */
	public function get(array $where, string $output = OBJECT): object|array|null
	{
		return $this->cache->remember(
			$this->cache->generateKey(array_merge(['output' => $output], $where)),
			function() use ($where, $output) {
				$query = "SELECT * FROM {$this->fullTableName}";
				$where = $this->buildWhereClause($where);
				$query .= " WHERE {$where['sql']}";
				$values = $where['values'];

				return $this->wpdb->get_row($this->wpdb->prepare($query, $values), $output);
			}
		);
	}

	/**
	 * Get multiple records
	 *
	 * @param array $args Query arguments: where, orderby, order, limit, offset
	 * @param string $output OBJECT, ARRAY_A, or ARRAY_N
	 * @return array
	 *
	 * @example
	 * $favs = $table->getMany([
	 *     'where' => ['user_id' => 1],
	 *     'orderby' => 'date_added',
	 *     'order' => 'DESC',
	 *     'limit' => 20
	 * ]);
	 */
	public function getMany(array $args = [], string $output = OBJECT): array
	{
		return $this->cache->remember(
			$this->cache->generateKey(array_merge($args, ['output' => $output])),
			function () use ($args, $output) {
				$query = "SELECT * FROM {$this->fullTableName}";
				$values = [];

				// WHERE clause
				if (!empty($args['where'])) {
					$clause = $this->buildWhereClause($args['where']);

					$query .= " WHERE {$clause['sql']}";
					$values = array_merge($values, $clause['values']);
				}

				// ORDER BY
				if (!empty($args['orderby'])) {
					$orderby = sanitize_sql_orderby($args['orderby']);
					$order = (!empty($args['order']) && strtoupper($args['order']) === 'ASC') ? 'ASC' : 'DESC';
					$query .= " ORDER BY {$orderby} {$order}";
				}

				// LIMIT
				if (!empty($args['limit'])) {
					$limit = absint($args['limit']);
					$offset = !empty($args['offset']) ? absint($args['offset']) : 0;
					$query .= " LIMIT {$offset}, {$limit}";
				}

				if (empty($values)) {
					return $this->wpdb->get_results($query, $output);
				}

				return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
			}
		);

	}

	/**
	 * Get a specific column value from all matches
	 * @param string $column
	 * @param array $where
	 * @return array
	 */
	public function pluck(string $column, array $where = [], ?string $orderby = null, ?string $order = null, ?int $limit = null): array
	{
		$key = array_merge($where, ['column' => $column, 'orderby' => $orderby, 'order' => $order, 'limit' => $limit]);

		return $this->cache->remember(
			$this->cache->generateKey($key),
			function() use ($column, $where, $orderby, $order, $limit) {
				if (!empty($this->columns) && !array_key_exists($column, $this->columns)) {
					$this->logError('pluck', ['reason' => "Column '{$column}' not in definition"]);
					return [];
				}
				$args = ['where' => $where];
				if ($orderby) {
					$args['orderby'] = $orderby;
				}
				if ($order) {
					$args['order'] = $order;
				}
				if ($limit) {
					$args['limit'] = $limit;
				}

				return array_column($this->getMany($args), $column);
			}
		);

	}

	/**
	 * Count records
	 *
	 * @param array $where Associative array of column => value conditions
	 * @return int
	 */
	public function count(array $where = []): int
	{
		return $this->cache->remember(
			$this->cache->generateKey(array_merge(['source' => 'count'], $where)),
			function() use ($where) {
				$query = "SELECT COUNT(*) FROM {$this->fullTableName}";
				$values = [];

				if (!empty($where)) {
					$clause = $this->buildWhereClause($where);
					$query .= " WHERE {$clause['sql']}";
					$values = $clause['values'];
				}

				if (empty($values)) {
					return (int) $this->wpdb->get_var($query);
				}

				return (int) $this->wpdb->get_var($this->wpdb->prepare($query, $values));
			}
		);

	}

	/**
	 * Check if record exists
	 *
	 * @param array $where Associative array of column => value conditions
	 * @return bool
	 */
	public function exists(array $where): bool
	{
		return $this->count($where) > 0;
	}

	// =========================================================================
	// UPDATE OPERATIONS
	// =========================================================================

	/**
	 * Update records
	 *
	 * @param array $data Data to update (column => value)
	 * @param array $where Where conditions (column => value)
	 * @param array|null $format Optional format for data
	 * @param array|null $where_format Optional format for where
	 * @return int|false Number of rows updated, false on failure
	 *
	 * @example
	 * $updated = $table->update(
	 *     ['status' => 'read'],
	 *     ['id' => 123, 'user_id' => 1]
	 * );
	 */
	public function update(array $data, array $where, ?array $format = null, ?array $where_format = null): int|false
	{
		// Auto-update updated_at if column exists and not provided
		if (!isset($data['updated_at']) && $this->hasColumn('updated_at')) {
			$data['updated_at'] = current_time('mysql');
		}

		$result = $this->wpdb->update(
			$this->fullTableName,
			$data,
			$where,
			$format,
			$where_format
		);

		if ($result === false) {
			$this->logError('update', ['data' => $data, 'where' => $where]);
		}
		$this->cache->flush();

		return $result;
	}

	// =========================================================================
	// DELETE OPERATIONS
	// =========================================================================

	/**
	 * Delete records
	 *
	 * @param array $where Where conditions (column => value)
	 * @param array|null $where_format Optional format for where
	 * @return int|false Number of rows deleted, false on failure
	 *
	 * @example
	 * $deleted = $table->delete(['id' => 123]);
	 */
	public function delete(array $where, ?array $where_format = null): int|false
	{
		$result = $this->wpdb->delete(
			$this->fullTableName,
			$where,
			$where_format
		);

		if ($result === false) {
			$this->logError('delete', ['where' => $where]);
		}
		$this->cache->flush();

		return $result;
	}

	// =========================================================================
	// RAW QUERY OPERATIONS
	// =========================================================================

	/**
	 * Execute a raw query with automatic table name substitution
	 *
	 * @param string $query SQL query (use {table} as placeholder)
	 * @param array $values Values for prepare()
	 * @return mixed Query result
	 *
	 * @example
	 * $results = $table->query(
	 *     "SELECT * FROM {table} WHERE user_id = %d AND status IN (%s, %s)",
	 *     [1, 'pending', 'active']
	 * );
	 */
	public function query(string $query, array $values = []): mixed
	{
		$query = str_replace('{table}', $this->fullTableName, $query);

		if (empty($values)) {
			return $this->wpdb->query($query);
		}

		return $this->wpdb->query($this->wpdb->prepare($query, $values));
	}

	/**
	 * Get results from raw query
	 *
	 * @param string $query SQL query (use {table} as placeholder)
	 * @param array $values Values for prepare()
	 * @param string $output OBJECT, ARRAY_A, or ARRAY_N
	 * @return array
	 */
	public function queryResults(string $query, array $values = [], string $output = OBJECT): array
	{
		$query = str_replace('{table}', $this->fullTableName, $query);

		if (empty($values)) {
			return $this->wpdb->get_results($query, $output);
		}

		return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
	}

	/**
	 * Get single value from query
	 *
	 * @param string $query SQL query (use {table} as placeholder)
	 * @param array $values Values for prepare()
	 * @return mixed
	 */
	public function queryVar(string $query, array $values = []): mixed
	{
		$query = str_replace('{table}', $this->fullTableName, $query);

		if (empty($values)) {
			return $this->wpdb->get_var($query);
		}

		return $this->wpdb->get_var($this->wpdb->prepare($query, $values));
	}

	// =========================================================================
	// TRANSACTION HELPERS
	// =========================================================================

	/**
	 * Start a transaction
	 */
	public function startTransaction(): void
	{
		$this->wpdb->query('START TRANSACTION');
	}

	/**
	 * Commit a transaction
	 */
	public function commit(): void
	{
		$this->wpdb->query('COMMIT');
	}

	/**
	 * Rollback a transaction
	 */
	public function rollback(): void
	{
		$this->wpdb->query('ROLLBACK');
	}

	/**
	 * Execute callback within a transaction
	 *
	 * @param callable $callback Function to execute
	 * @return mixed Returns callback result
	 * @throws Exception Rolls back on exception
	 *
	 * @example
	 * $result = $table->transaction(function() use ($table) {
	 *     $table->insert(['user_id' => 1, ...]);
	 *     $table->update(['status' => 'active'], ['id' => 123]);
	 *     return true;
	 * });
	 */
	public function transaction(callable $callback): mixed
	{
		$this->startTransaction();

		try {
			$result = $callback($this);
			$this->commit();
			return $result;
		} catch (Exception $e) {
			$this->rollback();
			$this->logError('transaction', ['error' => $e->getMessage()]);
			return false;
		}
	}

	// =========================================================================
	// UTILITY METHODS
	// =========================================================================

	/**
	 * Get the full table name (with prefix)
	 */
	public function getFullTableName(): string
	{
		return $this->fullTableName;
	}

	/**
	 * Get last insert ID
	 */
	public function getInsertId(): int
	{
		return $this->wpdb->insert_id;
	}

	/**
	 * Get last error
	 */
	public function getLastError(): string
	{
		return $this->wpdb->last_error;
	}

	/**
	 * Get number of affected rows from last query
	 */
	public function getAffectedRows(): int
	{
		return $this->wpdb->rows_affected;
	}

	public function getUserIDType():string
	{
		$this->ensureUserIdType();
		return static::$userIDType;
	}
	public function getUserTable():string
	{
		$this->ensureUserTable();
		return static::$userTable;
	}
	public function getTermIDType():string
	{
		$this->ensureTermIDType();
		return static::$termIDType;
	}
	public function getTermTable():string
	{
		$this->ensureTermTable();
		return static::$termTable;
	}
	public function getPostIDType():string
	{
		$this->ensurePostIDType();
		return static::$postIDType;
	}
	public function getPostTable():string
	{
		$this->ensurePostTable();
		return static::$postTable;
	}

	// =========================================================================
	// PRIVATE HELPERS
	// =========================================================================
	private function getColumnType(string $table, string $column):string|false {
		$tableExists = $this->wpdb->get_var(
			$this->wpdb->prepare("SHOW TABLES LIKE %s", $table)
		);
		if (!$tableExists) {
			error_log("[CustomTable] Table {$table} does not exist for getColumnType");
			return 'bigint(20) unsigned'; // safe fallback
		}

		$result = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT COLUMN_TYPE
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = %s
            AND COLUMN_NAME = %s",
				$table,
				$column
			)
		);
		if ($result && isset($result->COLUMN_TYPE)) {
			return $result->COLUMN_TYPE;
		}
		error_log("[CustomTable] Could not determine column type for {$table}.{$column}");
		return 'bigint(20) unsigned';
	}

	private function tableExists():bool
	{
		return !is_null($this->wpdb->get_var($this->wpdb->prepare("SHOW TABLES LIKE %s", $this->fullTableName)));
	}
	/**
	 * Build WHERE clause from associative array
	 */
	private function buildWhereClause(array $where):array
	{
		$conditions = [];
		$values = [];
		foreach ($where as $column => $value) {
			$column_safe = esc_sql($column);

			if ($value === null) {
				$conditions[] = "`{$column_safe}` IS NULL";
			} elseif (is_array($value) && count($value) === 2 && is_string($value[0])) {
				[$operator, $operand] = $value;
				$operator = strtoupper(trim($operator));

				if (in_array($operator, ['IN', 'NOT IN']) && is_array($operand)) {
					$placeholders = implode(',', array_map([$this, 'getPlaceholder'], $operand));
					$conditions[] = "`{$column_safe}` {$operator} ({$placeholders})";
					$values       = array_merge($values, $operand);
				} else {
					// <, >, <=, >=, !=, LIKE, etc.
					$conditions[] = "`{$column_safe}` {$operator} " . $this->getPlaceholder($operand);
					$values[]     = $operand;
				}
			} else {
				$conditions[] = "`{$column_safe}` = " . $this->getPlaceholder($value);
				$values[]     = $value;
			}
		}

		return ['sql' => implode(' AND ', $conditions), 'values' => $values];
	}

	/**
	 * Get appropriate placeholder for value type
	 */
	private function getPlaceholder(mixed $value): string
	{
		if (is_int($value)) {
			return '%d';
		} elseif (is_float($value)) {
			return '%f';
		} else {
			return '%s';
		}
	}

	/**
	 * Check if table has a specific column
	 */
	private function hasColumn(string $column): bool
	{
		static $cache = [];

		if (!isset($cache[$this->tableName])) {
			$columns = $this->wpdb->get_col("DESCRIBE {$this->fullTableName}");
			$cache[$this->tableName] = array_flip($columns);
		}

		return isset($cache[$this->tableName][$column]);
	}

	/**
	 * Log database errors
	 */
	private function logError(string $operation, array $context = []): void
	{
		if (function_exists('JVB')) {
			JVB()->error()->log(
				$this->tableName,
				"CustomTable {$operation} failed: " . $this->wpdb->last_error,
				$context,
				'error'
			);
		} else {
			error_log("[CustomTable:{$this->tableName}] {$operation} failed: " . $this->wpdb->last_error);
		}
	}
}