jakevan/jvb.git

			@@ -34,8 +34,12 @@

			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
			@@ -54,6 +58,19 @@

			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();
			@@ -94,12 +111,25 @@
			}
			}

			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;
			}
			}

			/**
			*
			@@ -154,7 +184,7 @@
			if (is_array($key)) {
			$value = $key['value'];
			// Ensure value is wrapped in parentheses
			if (!str_starts_with(trim($value), '(')) {
			if (!str_contains(trim($value), '(`')) {
			$value = '(`' . $value . '`)';
			}
			$parts[] = $key['key'] . ' KEY ' . $value;
			@@ -165,11 +195,12 @@
			}

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

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

			@@ -190,6 +221,11 @@
			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();
			@@ -232,6 +268,8 @@
			$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}";
			@@ -409,6 +447,8 @@
			return false;
			}

			$this->cache->flush();

			return $this->wpdb->insert_id;
			}

			@@ -439,26 +479,19 @@
			* );
			* // Returns: ['id' => 456, 'created' => false, 'record' => object]
			*/
			public function findOrCreate(array $searchData, array $createData = []): array
			public function findOrCreate(array $searchData, array $createData = []):int\|false
			{
			$record = $this->get($searchData);

			if ($record) {
			return [
			'id' => $record->id ?? 0,
			'created' => false,
			'record' => $record
			];
			if (!empty($createData)) {
			return $this->update($createData, $searchData);
			}
			return $record->id;
			}

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

			return [
			'id' => $id,
			'created' => true,
			'record' => $this->get(['id' => $id])
			];
			return $this->insert($data);
			}

			/**
			@@ -536,10 +569,17 @@
			*/
			public function get(array $where, string $output = OBJECT): object\|array\|null
			{
			$query = "SELECT * FROM {$this->fullTableName} WHERE " . $this->buildWhereClause($where);
			$values = array_values($where);
			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);
			return $this->wpdb->get_row($this->wpdb->prepare($query, $values), $output);
			}
			);
			}

			/**
			@@ -559,34 +599,76 @@
			*/
			public function getMany(array $args = [], string $output = OBJECT): array
			{
			$query = "SELECT * FROM {$this->fullTableName}";
			$values = [];
			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'])) {
			$query .= " WHERE " . $this->buildWhereClause($args['where']);
			$values = array_merge($values, array_values($args['where']));
			}
			// WHERE clause
			if (!empty($args['where'])) {
			$clause = $this->buildWhereClause($args['where']);

			// 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}";
			}
			$query .= " WHERE {$clause['sql']}";
			$values = array_merge($values, $clause['values']);
			}

			// LIMIT
			if (!empty($args['limit'])) {
			$limit = absint($args['limit']);
			$offset = !empty($args['offset']) ? absint($args['offset']) : 0;
			$query .= " LIMIT {$offset}, {$limit}";
			}
			// 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}";
			}

			if (empty($values)) {
			return $this->wpdb->get_results($query, $output);
			}
			// LIMIT
			if (!empty($args['limit'])) {
			$limit = absint($args['limit']);
			$offset = !empty($args['offset']) ? absint($args['offset']) : 0;
			$query .= " LIMIT {$offset}, {$limit}";
			}

			return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
			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);
			}
			);

			}

			/**
			@@ -597,19 +679,26 @@
			*/
			public function count(array $where = []): int
			{
			$query = "SELECT COUNT(*) FROM {$this->fullTableName}";
			$values = [];
			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)) {
			$query .= " WHERE " . $this->buildWhereClause($where);
			$values = array_values($where);
			}
			if (!empty($where)) {
			$clause = $this->buildWhereClause($where);
			$query .= " WHERE {$clause['sql']}";
			$values = $clause['values'];
			}

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

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

			}

			/**
			@@ -660,6 +749,7 @@
			if ($result === false) {
			$this->logError('update', ['data' => $data, 'where' => $where]);
			}
			$this->cache->flush();

			return $result;
			}
			@@ -689,6 +779,7 @@
			if ($result === false) {
			$this->logError('delete', ['where' => $where]);
			}
			$this->cache->flush();

			return $result;
			}
			@@ -811,7 +902,7 @@
			} catch (Exception $e) {
			$this->rollback();
			$this->logError('transaction', ['error' => $e->getMessage()]);
			throw $e;
			return false;
			}
			}

			@@ -866,11 +957,21 @@
			$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
			@@ -909,18 +1010,35 @@
			/**
			* Build WHERE clause from associative array
			*/
			private function buildWhereClause(array $where): string
			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 implode(' AND ', $conditions);

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

			/**