prefix . 'aipt_blueprints'; } /** * Read a single Blueprint by id. * * @param int $id Blueprint id. * @return array|null */ public function get( int $id ): ?array { global $wpdb; $table = self::table(); $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ), ARRAY_A ); return is_array( $row ) ? $this->mapRow( $row ) : null; } /** * Read a single Blueprint by its migration `origin` tag. * * Used by LegacyPromptMigrator to stay idempotent: a migrated Blueprint * is created at most once per generator, keyed by origin (e.g. * `migration:description`). * * @param string $origin Origin tag. * @return array|null */ public function findByOrigin( string $origin ): ?array { global $wpdb; $table = self::table(); $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE origin = %s ORDER BY id ASC LIMIT 1", $origin ), ARRAY_A ); return is_array( $row ) ? $this->mapRow( $row ) : null; } /** * List Blueprints with keyset pagination and optional filters. * * Keyset cursor is (updated_at, id), encoded as the opaque string * "|". `has_more`/total use the limit+1 look-ahead so * the last full page never looks like a phantom empty page, exactly * like {@see \AIPT\Core\ContentReview\ContentReviewStore::list()}. * * @param array $filters Optional: generator, status, search (name, via esc_like). * @param string $cursor Opaque cursor from a previous page, or '' for the first page. * @param int $limit Requested page size (max 100). * @return array{items: array>, total: int, hasMore: bool, nextCursor: string} */ public function list( array $filters, string $cursor = '', int $limit = 25 ): array { global $wpdb; $table = self::table(); $limit = $limit > 0 ? min( self::MAX_LIST_LIMIT, $limit ) : self::DEFAULT_LIST_LIMIT; $where = $this->buildWhere( $filters ); $count_where_sql = empty( $where ) ? '' : ' WHERE ' . implode( ' AND ', $where ); $decoded_cursor = $this->decodeCursor( $cursor ); if ( null !== $decoded_cursor ) { $where[] = $wpdb->prepare( '(updated_at, id) < (%s, %d)', $decoded_cursor['updated_at'], $decoded_cursor['id'] ); } $where_sql = empty( $where ) ? '' : ' WHERE ' . implode( ' AND ', $where ); $fetch_limit = $limit + 1; $rows = $wpdb->get_results( "SELECT * FROM {$table}{$where_sql} ORDER BY updated_at DESC, id DESC LIMIT {$fetch_limit}", ARRAY_A ); $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}{$count_where_sql}" ); if ( ! is_array( $rows ) ) { $rows = array(); } $has_more = count( $rows ) > $limit; $next_cursor = ''; if ( $has_more ) { array_pop( $rows ); } $last = end( $rows ); if ( is_array( $last ) ) { $next_cursor = $this->encodeCursor( (string) ( $last['updated_at'] ?? '' ), (int) ( $last['id'] ?? 0 ) ); } reset( $rows ); return array( 'items' => array_map( array( $this, 'mapRow' ), array_values( $rows ) ), 'total' => $total, 'hasMore' => $has_more, 'nextCursor' => $has_more ? $next_cursor : '', ); } /** * Every Blueprint for one generator, unpaginated up to $limit. * * For callers that need the whole set (assignment pickers, templates * fallback lists) rather than a paged library view. * * @param string $generator Generator key. * @param int $limit Hard cap (bounded — never an unbounded scan). * @return array> */ public function allForGenerator( string $generator, int $limit = 500 ): array { global $wpdb; $table = self::table(); $limit = $limit > 0 ? min( $limit, 2000 ) : 500; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE generator = %s ORDER BY name ASC LIMIT %d", $generator, $limit ), ARRAY_A ); return is_array( $rows ) ? array_map( array( $this, 'mapRow' ), $rows ) : array(); } /** * Every Blueprint that currently selects the given Brand profile. * * @param int $brand_profile_id Brand profile id. * @return array> */ public function listByBrandProfile( int $brand_profile_id ): array { global $wpdb; $table = self::table(); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE brand_profile_id = %d ORDER BY name ASC LIMIT %d", $brand_profile_id, self::REFERENCE_SCAN_LIMIT ), ARRAY_A ); return is_array( $rows ) ? array_map( array( $this, 'mapRow' ), $rows ) : array(); } /** * Every Blueprint whose `definition.sources.items` lists the given * Source — "Changing this source affects N blueprints". The reference * lives inside the JSON `definition` column, which * MySQL cannot index, so this is a bounded in-PHP scan rather than a * WHERE clause. * * @param int $source_id Source id. * @return array> */ public function listReferencingSource( int $source_id ): array { global $wpdb; $table = self::table(); $ref = 'src:' . $source_id; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} ORDER BY id DESC LIMIT %d", self::REFERENCE_SCAN_LIMIT ), ARRAY_A ); if ( ! is_array( $rows ) ) { return array(); } $matches = array(); foreach ( $rows as $row ) { $mapped = $this->mapRow( $row ); $items = $mapped['definition']['sources']['items'] ?? null; if ( ! is_array( $items ) ) { continue; } foreach ( $items as $item ) { if ( is_array( $item ) && isset( $item['ref'] ) && $ref === (string) $item['ref'] ) { $matches[] = $mapped; break; } } } return $matches; } /** * Total Blueprint count across every generator. * * @return int */ public function countAll(): int { global $wpdb; $table = self::table(); return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); } /** * Create a new Blueprint. * * @param array $data { * @type string $name Required. * @type string $generator Required. * @type string $status Default 'active'. * @type int $schemaVersion Default 1. * @type array $definition Required; stored via wp_json_encode(). * @type string $definitionHash Default ''. * @type int $brandProfileId Default 0. * @type string $origin Default ''. * @type int $createdBy Default current user. * } * @return int New Blueprint id (0 on failure). */ public function create( array $data ): int { global $wpdb; $table = self::table(); $now = current_time( 'mysql', true ); $user = isset( $data['createdBy'] ) ? (int) $data['createdBy'] : (int) ( function_exists( 'get_current_user_id' ) ? get_current_user_id() : 0 ); $row = array( 'generator' => (string) ( $data['generator'] ?? '' ), 'name' => (string) ( $data['name'] ?? '' ), 'status' => (string) ( $data['status'] ?? 'active' ), 'schema_version' => (int) ( $data['schemaVersion'] ?? 1 ), 'revision' => 1, 'definition' => wp_json_encode( is_array( $data['definition'] ?? null ) ? $data['definition'] : array() ), 'definition_hash' => (string) ( $data['definitionHash'] ?? '' ), 'brand_profile_id' => (int) ( $data['brandProfileId'] ?? 0 ), 'origin' => (string) ( $data['origin'] ?? '' ), 'created_by' => $user, 'updated_by' => $user, 'created_at' => $now, 'updated_at' => $now, ); $result = $wpdb->insert( $table, $row, array( '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%d', '%s', '%d', '%d', '%s', '%s' ) ); return false !== $result ? (int) $wpdb->insert_id : 0; } /** * Revision-checked update. * * A single conditional `UPDATE ... SET ..., revision = revision + 1 * WHERE id = %d AND revision = %d` — never a check-then-write pair — so * two editors saving the same Blueprint cannot silently overwrite one * another (two tabs must not silently overwrite each other's changes). * `revision` is a plain INT UNSIGNED counter, not * the microsecond-epoch scheme other stores use for their BIGINT * `version` columns — the SQL-level increment is simplest and the * column is deliberately too small to hold a microsecond stamp. * * The revision only bumps when `name`, `definitionHash` or * `brandProfileId` actually differ from the stored row (the three * fields that make up "this Blueprint's content"): a save that re-sends * the same content (a redundant click, or a detach path that happens to * restore the same brand profile id) is revision-honest — it never * fires "changed since its last run" for other people watching this * Blueprint's revision. * * @param int $id Blueprint id. * @param array $data Any of: name, status, schemaVersion, definition (array), definitionHash, brandProfileId, updatedBy. * @param int $expected_revision The revision the caller last read. * @return array{ok: bool, revision: int, current: array|null} */ public function update( int $id, array $data, int $expected_revision ): array { global $wpdb; $table = self::table(); $now = current_time( 'mysql', true ); $current = $this->get( $id ); $columns = array(); $values = array(); if ( array_key_exists( 'name', $data ) ) { $columns[] = 'name = %s'; $values[] = (string) $data['name']; } if ( array_key_exists( 'status', $data ) ) { $columns[] = 'status = %s'; $values[] = (string) $data['status']; } if ( array_key_exists( 'schemaVersion', $data ) ) { $columns[] = 'schema_version = %d'; $values[] = (int) $data['schemaVersion']; } if ( array_key_exists( 'definition', $data ) ) { $columns[] = 'definition = %s'; $values[] = wp_json_encode( is_array( $data['definition'] ) ? $data['definition'] : array() ); } if ( array_key_exists( 'definitionHash', $data ) ) { $columns[] = 'definition_hash = %s'; $values[] = (string) $data['definitionHash']; } if ( array_key_exists( 'brandProfileId', $data ) ) { $columns[] = 'brand_profile_id = %d'; $values[] = (int) $data['brandProfileId']; } if ( array_key_exists( 'updatedBy', $data ) ) { $columns[] = 'updated_by = %d'; $values[] = (int) $data['updatedBy']; } $columns[] = 'updated_at = %s'; $values[] = $now; $set_sql = implode( ', ', $columns ); if ( self::contentChanged( $current, $data ) ) { $set_sql .= ', revision = revision + 1'; } $values[] = $id; $values[] = $expected_revision; $affected = $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET {$set_sql} WHERE id = %d AND revision = %d", $values ) ); $current_after = $this->get( $id ); $matched_unchanged = false !== $affected && 0 === (int) $affected && null !== $current_after && (int) $current_after['revision'] === $expected_revision && ! self::contentChanged( $current_after, $data ); if ( ( false !== $affected && (int) $affected >= 1 ) || $matched_unchanged ) { return array( 'ok' => true, 'revision' => null !== $current_after ? (int) $current_after['revision'] : $expected_revision, 'current' => $current_after, ); } return array( 'ok' => false, 'revision' => null !== $current_after ? (int) $current_after['revision'] : $expected_revision, 'current' => $current_after, ); } /** * Whether $data's `name`/`definitionHash`/`brandProfileId` (whichever * of the three are present) actually differ from $current — the only * fields {@see update()} treats as "this Blueprint's content" for * revision-bump purposes. A key $data omits is never a reason to bump: * the caller is not touching it. A missing $current (the id does not * exist, or this is effectively a first write) always counts as * changed, so the write attempt still runs and update() reports the * real not-found/conflict outcome instead of a silent no-op. * * @param array|null $current Current row, or null. * @param array $data Requested changes. * @return bool */ private static function contentChanged( ?array $current, array $data ): bool { if ( null === $current ) { return true; } if ( array_key_exists( 'name', $data ) && (string) $data['name'] !== (string) $current['name'] ) { return true; } if ( array_key_exists( 'definitionHash', $data ) && (string) $data['definitionHash'] !== (string) $current['definitionHash'] ) { return true; } if ( array_key_exists( 'brandProfileId', $data ) && (int) $data['brandProfileId'] !== (int) $current['brandProfileId'] ) { return true; } return false; } /** * Delete a Blueprint by id. * * Does not cascade — the REST delete handler calls * {@see \AIPT\Core\Database\OrphanCleaner::on_blueprint_deleted()} * separately, after its own impact-preview confirmation. * * @param int $id Blueprint id. * @return bool */ public function delete( int $id ): bool { global $wpdb; $table = self::table(); $result = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) ); return false !== $result && (int) $result >= 1; } /** * Build WHERE clauses for list(). * * @param array $filters Read filters. * @return array */ private function buildWhere( array $filters ): array { global $wpdb; $where = array(); if ( ! empty( $filters['generator'] ) ) { $where[] = $wpdb->prepare( 'generator = %s', (string) $filters['generator'] ); } if ( ! empty( $filters['status'] ) ) { $where[] = $wpdb->prepare( 'status = %s', (string) $filters['status'] ); } if ( ! empty( $filters['search'] ) ) { $like = '%' . $wpdb->esc_like( (string) $filters['search'] ) . '%'; $where[] = $wpdb->prepare( 'name LIKE %s', $like ); } return $where; } /** * Encode a keyset cursor as an opaque string. * * @param string $updated_at MySQL UTC datetime of the last row. * @param int $id Id of the last row. * @return string */ private function encodeCursor( string $updated_at, int $id ): string { return $updated_at . '|' . $id; } /** * Decode a cursor produced by encodeCursor(). Malformed input is * treated as "no cursor" (first page) rather than an error. * * @param string $cursor Opaque cursor string. * @return array{updated_at: string, id: int}|null */ private function decodeCursor( string $cursor ): ?array { if ( '' === $cursor ) { return null; } $parts = explode( '|', $cursor, 2 ); if ( 2 !== count( $parts ) || '' === $parts[0] || ! is_numeric( $parts[1] ) ) { return null; } return array( 'updated_at' => $parts[0], 'id' => (int) $parts[1], ); } /** * Map a raw database row to the canonical camelCase response shape. * * @param array $row Database row. * @return array */ private function mapRow( array $row ): array { $definition = json_decode( (string) ( $row['definition'] ?? '' ), true ); return array( 'id' => (int) ( $row['id'] ?? 0 ), 'generator' => (string) ( $row['generator'] ?? '' ), 'name' => (string) ( $row['name'] ?? '' ), 'status' => (string) ( $row['status'] ?? 'active' ), 'schemaVersion' => (int) ( $row['schema_version'] ?? 1 ), 'revision' => (int) ( $row['revision'] ?? 1 ), 'definition' => is_array( $definition ) ? $definition : array(), 'definitionHash' => (string) ( $row['definition_hash'] ?? '' ), 'brandProfileId' => (int) ( $row['brand_profile_id'] ?? 0 ), 'origin' => (string) ( $row['origin'] ?? '' ), 'createdBy' => (int) ( $row['created_by'] ?? 0 ), 'updatedBy' => (int) ( $row['updated_by'] ?? 0 ), 'createdAt' => (string) ( $row['created_at'] ?? '' ), 'updatedAt' => (string) ( $row['updated_at'] ?? '' ), ); } }