prefix . 'aipt_blueprint_source_cache'; } /** * Read the cached content for one (source, product) pair. * * @param int $source_id Source id. * @param int $product_id Product id. * @return array|null */ public function get( int $source_id, int $product_id ): ?array { global $wpdb; $table = self::table(); $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE source_id = %d AND product_id = %d", $source_id, $product_id ), ARRAY_A ); return is_array( $row ) ? $this->mapRow( $row ) : null; } /** * Write (insert or replace) the cached content for one (source, * product) pair. * * Uses `REPLACE INTO` against the table's UNIQUE (source_id, * product_id) key: a fresh fetch wholesale-overwrites whatever was * cached before, including a changed URL value — there is no partial * update, so a stale cached extraction for an old URL can never survive * alongside a new one. `id` is internal only (nothing outside this * store looks it up by id), so REPLACE's delete+reinsert identity * change is harmless. * * @param int $source_id Source id. * @param int $product_id Product id. * @param array $data { * @type string $url The URL actually fetched for this product. * @type string $status SourceStatus. * @type array|null $extracted Default null. * @type string $contentHash Default ''. * @type int $contentVersion Default 0. * @type int $httpStatus Default 0. * @type string $errorCode Default ''. * @type string $fetchedAt MySQL UTC datetime; default now. * @type string|null $expiresAt MySQL UTC datetime, or null for no TTL. * } * @return bool */ public function put( int $source_id, int $product_id, array $data ): bool { global $wpdb; $table = self::table(); $now = current_time( 'mysql', true ); $url = (string) ( $data['url'] ?? '' ); $row = array( 'source_id' => $source_id, 'product_id' => $product_id, 'url' => $url, 'url_hash' => '' !== $url ? hash( 'sha256', $url ) : '', 'status' => (string) ( $data['status'] ?? 'error' ), 'extracted' => isset( $data['extracted'] ) && is_array( $data['extracted'] ) ? wp_json_encode( $data['extracted'] ) : null, 'content_hash' => (string) ( $data['contentHash'] ?? '' ), 'content_version' => (int) ( $data['contentVersion'] ?? 0 ), 'http_status' => (int) ( $data['httpStatus'] ?? 0 ), 'error_code' => (string) ( $data['errorCode'] ?? '' ), 'fetched_at' => (string) ( $data['fetchedAt'] ?? $now ), 'expires_at' => isset( $data['expiresAt'] ) ? (string) $data['expiresAt'] : null, ); $result = $wpdb->replace( $table, $row, array( '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s' ) ); return false !== $result; } /** * Delete every cache row for one product (product permanently deleted). * * @param int $product_id Product id. * @return int Rows deleted. */ public function deleteForProduct( int $product_id ): int { global $wpdb; $table = self::table(); $result = $wpdb->delete( $table, array( 'product_id' => $product_id ), array( '%d' ) ); return false !== $result ? (int) $result : 0; } /** * Delete every cache row for one Source (Source deleted). * * @param int $source_id Source id. * @return int Rows deleted. */ public function deleteForSource( int $source_id ): int { global $wpdb; $table = self::table(); $result = $wpdb->delete( $table, array( 'source_id' => $source_id ), array( '%d' ) ); return false !== $result ? (int) $result : 0; } /** * Delete cache rows whose `expires_at` is before the given cutoff. * * The caller decides the cutoff — BlueprintCleanup passes "now minus 30 * days" rather than plain "now", giving a grace window * past a cache row's own staleness before it is physically removed. * Chunked with `DELETE ... LIMIT` so a large backlog never holds the * table under one long write lock; a per-call cap bounds worst-case * runtime, leftovers are caught by the next cron tick. * * @param string $mysql_cutoff MySQL UTC datetime cutoff. * @return int Total rows deleted. */ public function purgeExpired( string $mysql_cutoff ): int { global $wpdb; $table = self::table(); $total_deleted = 0; while ( $total_deleted < self::MAX_DELETIONS_PER_CALL ) { $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE expires_at IS NOT NULL AND expires_at < %s LIMIT %d", $mysql_cutoff, self::DELETE_CHUNK_SIZE ) ); if ( false === $deleted ) { break; } $deleted = (int) $deleted; if ( $deleted <= 0 ) { break; } $total_deleted += $deleted; } return $total_deleted; } /** * Map a raw database row to the canonical camelCase response shape. * * @param array $row Database row. * @return array */ private function mapRow( array $row ): array { $extracted = null !== ( $row['extracted'] ?? null ) ? json_decode( (string) $row['extracted'], true ) : null; return array( 'id' => (int) ( $row['id'] ?? 0 ), 'sourceId' => (int) ( $row['source_id'] ?? 0 ), 'productId' => (int) ( $row['product_id'] ?? 0 ), 'url' => (string) ( $row['url'] ?? '' ), 'urlHash' => (string) ( $row['url_hash'] ?? '' ), 'status' => (string) ( $row['status'] ?? 'pending' ), 'extracted' => is_array( $extracted ) ? $extracted : null, 'contentHash' => (string) ( $row['content_hash'] ?? '' ), 'contentVersion' => (int) ( $row['content_version'] ?? 0 ), 'httpStatus' => (int) ( $row['http_status'] ?? 0 ), 'errorCode' => (string) ( $row['error_code'] ?? '' ), 'fetchedAt' => null !== ( $row['fetched_at'] ?? null ) ? (string) $row['fetched_at'] : null, 'expiresAt' => null !== ( $row['expires_at'] ?? null ) ? (string) $row['expires_at'] : null, ); } }