<?php
// Our own table {prefix}spotfind_items: one row per searchable product. WordPress' posts and postmeta tables are never searched.
// Product saves upsert one row; the static index files are built from this table with a single SELECT.
defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- this file talks to the plugin's own table only; there is nothing in the object cache to read or invalidate

const SPOTFIND_DB = 2;

function spotfind_table(): string {
	global $wpdb;
	return $wpdb->prefix . 'spotfind_items';
}

function spotfind_install_table(): void {
	global $wpdb;
	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	$t = spotfind_table();
	dbDelta( "CREATE TABLE $t (
  product_id bigint(20) unsigned NOT NULL,
  name text NOT NULL,
  sku varchar(100) NOT NULL DEFAULT '',
  price decimal(14,2) NOT NULL DEFAULT 0,
  regular_price decimal(14,2) NOT NULL DEFAULT 0,
  img text NOT NULL,
  url text NOT NULL,
  in_stock tinyint(1) NOT NULL DEFAULT 1,
  sales int(10) unsigned NOT NULL DEFAULT 0,
  brands text NOT NULL,
  cats text NOT NULL,
  synced int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (product_id),
  KEY synced (synced)
) " . $wpdb->get_charset_collate() . ';' );
	update_option( 'spotfind_db', SPOTFIND_DB, false );
}
add_action( 'plugins_loaded', static function () {
	if ( (int) get_option( 'spotfind_db' ) !== SPOTFIND_DB ) {
		spotfind_install_table();
	}
} );

function spotfind_brand_taxonomies(): array {
	return array_values( array_filter( apply_filters( 'spotfind_brand_taxonomies', array( 'product_brand', 'pa_brands', 'pwb-brand' ) ), 'taxonomy_exists' ) );
}

function spotfind_term_names( int $id, string $tax ): array {
	$terms = get_the_terms( $id, $tax );
	return is_wp_error( $terms ) || ! $terms ? array() : wp_list_pluck( $terms, 'name' );
}

// A row for one product, or null when it should not be searchable (draft, private, hidden from search, variation).
function spotfind_row( $p ): ?array {
	if ( ! $p || $p->is_type( 'variation' ) || 'publish' !== $p->get_status() || ! in_array( $p->get_catalog_visibility(), array( 'visible', 'search' ), true ) ) {
		return null;
	}
	$id = $p->get_id();
	$b  = array();
	// variable products: the lowest price in the range and its regular price
	$price   = $p->is_type( 'variable' ) ? $p->get_variation_price( 'min' ) : $p->get_price();
	$regular = $p->is_type( 'variable' ) ? $p->get_variation_regular_price( 'min' ) : $p->get_regular_price();
	foreach ( spotfind_brand_taxonomies() as $tax ) {
		$b = array_merge( $b, spotfind_term_names( $id, $tax ) );
	}
	$row = array(
		'product_id' => $id,
		'name'       => $p->get_name(),
		'sku'        => $p->get_sku(),
		'price'      => (float) $price,
		'regular_price' => (float) $regular,
		'img'        => (string) get_the_post_thumbnail_url( $id, 'thumbnail' ),
		'url'        => $p->get_permalink(),
		'in_stock'   => $p->is_in_stock() ? 1 : 0,
		'sales'      => (int) $p->get_total_sales(),
		'brands'     => wp_json_encode( $b ),
		'cats'       => wp_json_encode( spotfind_term_names( $id, 'product_cat' ) ),
		'synced'     => time(),
	);
	if ( ! $row['in_stock'] && 'hide' === spotfind_options()['oos'] ) {
		return null;
	}
	return apply_filters( 'spotfind_row', $row, $p ); // null drops the product from the index
}

function spotfind_save( array $row ): void {
	global $wpdb;
	$wpdb->replace( spotfind_table(), $row, array( '%d', '%s', '%s', '%f', '%f', '%s', '%s', '%d', '%d', '%s', '%s', '%d' ) );
}

function spotfind_remove( int $id ): void {
	global $wpdb;
	$wpdb->delete( spotfind_table(), array( 'product_id' => $id ), array( '%d' ) );
}

// One product changed: refresh just its row, then queue the file rebuild.
function spotfind_on_product( int $id ): void {
	$p = wc_get_product( $id );
	if ( $p && $p->is_type( 'variation' ) ) {
		$p = wc_get_product( $p->get_parent_id() );
	}
	$row = spotfind_row( $p );
	if ( $row ) {
		spotfind_save( $row );
	} elseif ( $p ) {
		spotfind_remove( $p->get_id() );
	} else {
		spotfind_remove( $id );
	}
	spotfind_queue();
}

// Walk every product once: upsert what is searchable, delete rows nobody touched this run. Returns rows kept.
function spotfind_sync_all(): int {
	global $wpdb;
	if ( function_exists( 'set_time_limit' ) ) {
		set_time_limit( 300 ); // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- a long background job, never a visitor request
	}
	$start = time();
	$n     = 0;
	$page  = 1;
	do {
		$ids = wc_get_products( array( 'status' => 'publish', 'visibility' => 'search', 'limit' => 200, 'page' => $page++, 'return' => 'ids' ) );
		foreach ( $ids as $id ) {
			$row = spotfind_row( wc_get_product( $id ) );
			if ( $row ) {
				spotfind_save( $row );
				++$n;
			}
		}
	} while ( count( $ids ) === 200 );
	$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE synced < %d', spotfind_table(), $start ) );
	return $n;
}

// Rows in the shape builder.php expects. Images are blanked here when the setting is off, so toggling it needs no resync.
function spotfind_items(): array {
	global $wpdb;
	$imgs = ! empty( spotfind_options()['images'] );
	$out  = array();
	foreach ( (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY product_id ASC', spotfind_table() ), ARRAY_A ) as $r ) {
		$out[] = array(
			'id'    => (int) $r['product_id'],
			'n'     => $r['name'],
			'sku'   => $r['sku'],
			'p'     => (float) $r['price'],
			'r'     => (float) $r['regular_price'],
			'img'   => $imgs ? $r['img'] : '',
			'u'     => $r['url'],
			's'     => (bool) $r['in_stock'],
			'sales' => (int) $r['sales'],
			'b'     => json_decode( $r['brands'], true ) ?: array(),
			'c'     => json_decode( $r['cats'], true ) ?: array(),
		);
	}
	return apply_filters( 'spotfind_items', $out );
}

function spotfind_table_stats(): array {
	global $wpdb;
	$t = spotfind_table();
	$wpdb->suppress_errors( true ); // SHOW TABLE STATUS is MySQL only
	$st = $wpdb->get_row( $wpdb->prepare( 'SHOW TABLE STATUS LIKE %s', $t ), ARRAY_A );
	$wpdb->suppress_errors( false );
	return array(
		'name'  => $t,
		'rows'  => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $t ) ),
		'data'  => $st ? (int) $st['Data_length'] : null,
		'index' => $st ? (int) $st['Index_length'] : null,
		'free'  => $st ? (int) ( $st['Data_free'] ?? 0 ) : null,
	);
}

// OPTIMIZE TABLE rewrites the table and returns freed space. Not every database supports it.
function spotfind_optimize(): bool {
	global $wpdb;
	$wpdb->suppress_errors( true );
	$res = $wpdb->get_results( $wpdb->prepare( 'OPTIMIZE TABLE %i', spotfind_table() ), ARRAY_A );
	$wpdb->suppress_errors( false );
	return (bool) $res && 'error' !== strtolower( (string) end( $res )['Msg_type'] );
}