<?php
/**
 * Data layer for stocktake sessions and their count items.
 *
 * @package Stocktake_For_WooCommerce
 */

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- custom-table plugin: the schema lives in our own stwc_* tables; every query is fully $wpdb->prepare()d with %i table identifiers.

class STWC_Sessions {

	/**
	 * @return string Sessions table name.
	 */
	public static function sessions_table() {
		global $wpdb;
		return $wpdb->prefix . 'stwc_sessions';
	}

	/**
	 * @return string Items table name.
	 */
	public static function items_table() {
		global $wpdb;
		return $wpdb->prefix . 'stwc_items';
	}

	/* ---------------------------------------------------------------------
	 * Sessions
	 * ------------------------------------------------------------------- */

	/**
	 * Create a session and snapshot the in-scope products as count items.
	 *
	 * @param array $args name, scope_type ('all'|'category'), scope_value (array of term ids), blind (bool), note.
	 * @return int|WP_Error New session id.
	 */
	public static function create_session( $args ) {
		global $wpdb;

		$defaults = array(
			'name'        => '',
			'scope_type'  => 'all',
			'scope_value' => array(),
			'count_mode'  => 'partial',
			'blind'       => 0,
			'approval'    => 0,
			'note'        => '',
		);
		$args     = wp_parse_args( $args, $defaults );

		// The accumulation workflow modes (tally, goods-in) exist only in the Pro
		// build — the free build's whitelist simply doesn't know them, so no UI
		// or REST path can create such a session there.
		$stwc_modes = array( 'partial', 'full' );
		$mode = in_array( $args['count_mode'], $stwc_modes, true ) ? $args['count_mode'] : 'partial';

		// A category scope must name at least one category, otherwise it would
		// fall back to the entire catalogue.
		if ( 'category' === $args['scope_type'] && empty( array_filter( array_map( 'absint', (array) $args['scope_value'] ) ) ) ) {
			return new WP_Error( 'stwc_no_scope', __( 'Choose at least one category, or select “All products”.', 'stocktake-for-woocommerce' ) );
		}

		$name = trim( (string) $args['name'] );
		if ( '' === $name ) {
			$name = sprintf(
				/* translators: %s: date/time */
				__( 'Stocktake %s', 'stocktake-for-woocommerce' ),
				wp_date( 'Y-m-d H:i' )
			);
		}

		$stwc_approval = 0;

		$inserted = $wpdb->insert(
			self::sessions_table(),
			array(
				'name'        => $name,
				'status'      => 'open',
				'scope_type'  => in_array( $args['scope_type'], array( 'all', 'category' ), true ) ? $args['scope_type'] : 'all',
				'scope_value' => wp_json_encode( array_map( 'absint', (array) $args['scope_value'] ) ),
				'count_mode'  => $mode,
				'blind'       => $args['blind'] ? 1 : 0,
				'approval'    => $stwc_approval,
				'note'        => sanitize_textarea_field( $args['note'] ),
				'created_by'  => get_current_user_id(),
				'created_at'  => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%s' )
		);

		if ( false === $inserted ) {
			return new WP_Error( 'stwc_insert_failed', __( 'Could not create the stocktake session.', 'stocktake-for-woocommerce' ) );
		}

		$session_id = (int) $wpdb->insert_id;
		$count      = self::start_population( $session_id, $args['scope_type'], (array) $args['scope_value'] );

		STWC_Log::add( array( 'session_id' => $session_id, 'event' => 'created', 'note' => $mode ) );

		/**
		 * Fires after a session is created and its items snapshotted.
		 *
		 * @param int $session_id
		 * @param int $count Number of items snapshotted.
		 */
		do_action( 'stwc_session_created', $session_id, $count );

		return $session_id;
	}

	/**
	 * Fetch one session row.
	 *
	 * @param int $id
	 * @return array|null
	 */
	public static function get_session( $id ) {
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::sessions_table(), $id ),
			ARRAY_A
		);
		// Always-hit read path: cheapest place to notice a build whose queued
		// batch was lost and would otherwise leave building=1 stuck forever.
		if ( $row && ! empty( $row['building'] ) ) {
			self::maybe_resume_build( $row );
		}
		return $row ?: null;
	}

	/**
	 * List sessions, newest first.
	 *
	 * @param array $args limit, offset.
	 * @return array
	 */
	public static function get_sessions( $args = array() ) {
		global $wpdb;
		$limit  = isset( $args['limit'] ) ? absint( $args['limit'] ) : 50;
		$offset = isset( $args['offset'] ) ? absint( $args['offset'] ) : 0;

		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i ORDER BY id DESC LIMIT %d OFFSET %d',
				self::sessions_table(),
				$limit,
				$offset
			),
			ARRAY_A
		);
	}

	/**
	 * Update a session's status.
	 *
	 * @param int    $id
	 * @param string $status
	 * @return bool
	 */
	public static function set_status( $id, $status ) {
		global $wpdb;
		$data = array( 'status' => $status );
		$fmt  = array( '%s' );
		if ( 'completed' === $status ) {
			$data['completed_at'] = current_time( 'mysql' );
			$fmt[]                = '%s';
		}
		$ok = false !== $wpdb->update( self::sessions_table(), $data, array( 'id' => $id ), $fmt, array( '%d' ) );
		if ( $ok ) {
			STWC_Log::add( array( 'session_id' => $id, 'event' => 'status', 'note' => $status ) );
		}
		return $ok;
	}

	/**
	 * Delete a session and its items.
	 *
	 * @param int $id
	 */
	public static function delete_session( $id ) {
		global $wpdb;
		STWC_Log::delete_for_session( $id );
		$wpdb->delete( self::items_table(), array( 'session_id' => $id ), array( '%d' ) );
		$wpdb->delete( self::sessions_table(), array( 'id' => $id ), array( '%d' ) );
	}

	/* ---------------------------------------------------------------------
	 * Items
	 * ------------------------------------------------------------------- */

	/**
	 * Snapshot in-scope stock-managed products/variations into count items.
	 *
	 * Processes in pages to stay memory-safe on large catalogs.
	 *
	 * @param int    $session_id
	 * @param string $scope_type
	 * @param array  $term_ids
	 * @return int Number of items created.
	 */
	public static function populate_items( $session_id, $scope_type, $term_ids = array() ) {
		$created = 0;
		$after   = 0;
		do {
			$r        = self::populate_page( $session_id, $scope_type, $term_ids, $after, self::batch_size() );
			$created += $r['created'];
			$after    = $r['last_id'];
		} while ( $r['more'] );
		return $created;
	}

	/**
	 * Products snapshotted per page. Filterable for tuning / testing.
	 *
	 * @return int
	 */
	public static function batch_size() {
		return max( 1, (int) apply_filters( 'stwc_snapshot_batch_size', 200 ) );
	}

	/**
	 * Snapshot one batch of in-scope products.
	 *
	 * Pages with a keyset cursor (ID > cursor, ID ASC) instead of an offset
	 * over a live date-ordered query: a product published or unpublished
	 * mid-build cannot shift the window and silently drop catalogue items.
	 *
	 * @param int    $session_id
	 * @param string $scope_type
	 * @param array  $term_ids
	 * @param int    $after_id Keyset cursor: only products with ID above this.
	 * @param int    $per
	 * @return array { created:int, fetched:int, more:bool, last_id:int }
	 */
	public static function populate_page( $session_id, $scope_type, $term_ids, $after_id = 0, $per = 200 ) {
		global $wpdb;

		if ( 'category' === $scope_type ) {
			$cat_ids = array();
			foreach ( (array) $term_ids as $tid ) {
				$term = get_term( (int) $tid, 'product_cat' );
				if ( $term && ! is_wp_error( $term ) ) {
					$cat_ids[] = (int) $term->term_id;
					// Products in a child category belong to the scope too,
					// matching the tax-query semantics the offset query had.
					$children = get_term_children( (int) $term->term_id, 'product_cat' );
					if ( ! is_wp_error( $children ) ) {
						$cat_ids = array_merge( $cat_ids, array_map( 'intval', $children ) );
					}
				}
			}
			$cat_ids = array_unique( array_filter( $cat_ids ) );
			if ( empty( $cat_ids ) ) {
				// A category scope with no resolvable terms must NOT fall through
				// to the whole catalogue — that could zero the entire store on
				// a full-mode Apply. Return nothing instead.
				return array( 'created' => 0, 'fetched' => 0, 'more' => false, 'last_id' => (int) $after_id );
			}
			// One %d placeholder per category id; table names go through %i.
			$stwc_in  = implode( ', ', array_fill( 0, count( $cat_ids ), '%d' ) );
			$post_ids = $wpdb->get_col(
				$wpdb->prepare(
					'SELECT DISTINCT p.ID FROM %i p' .
					' INNER JOIN %i tr ON tr.object_id = p.ID' .
					" INNER JOIN %i tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat'" .
					" WHERE p.post_type = 'product' AND p.post_status = 'publish' AND p.ID > %d AND tt.term_id IN ({$stwc_in})" .
					' ORDER BY p.ID ASC LIMIT %d', // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $stwc_in is only %d markers.
					array_merge(
						array( $wpdb->posts, $wpdb->term_relationships, $wpdb->term_taxonomy, (int) $after_id ),
						array_map( 'intval', array_values( $cat_ids ) ),
						array( (int) $per )
					)
				)
			);
		} else {
			$post_ids = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT DISTINCT p.ID FROM %i p WHERE p.post_type = 'product' AND p.post_status = 'publish' AND p.ID > %d ORDER BY p.ID ASC LIMIT %d",
					$wpdb->posts,
					(int) $after_id,
					(int) $per
				)
			);
		}

		$created = 0;
		$last_id = (int) $after_id;

		foreach ( $post_ids as $post_id ) {
			$last_id = (int) $post_id;
			$product = wc_get_product( (int) $post_id );
			if ( ! $product || ! $product->is_type( array( 'simple', 'variable' ) ) ) {
				continue;
			}
			if ( $product->is_type( 'variable' ) ) {
				$parent_line = false;
				foreach ( $product->get_children() as $child_id ) {
					$variation = wc_get_product( $child_id );
					if ( ! $variation || ! $variation->managing_stock() ) {
						continue;
					}
					if ( 'parent' === $variation->managing_stock() ) {
						// Stock pooled at the parent: ONE line for the parent
						// product — per-variation lines would each carry the
						// pooled qty and every stock write to them is inert.
						if ( ! $parent_line ) {
							$created    += self::insert_item( $session_id, $product ) ? 1 : 0;
							$parent_line = true;
						}
						continue;
					}
					$created += self::insert_item( $session_id, $variation ) ? 1 : 0;
				}
			} elseif ( $product->managing_stock() ) {
				$created += self::insert_item( $session_id, $product ) ? 1 : 0;
			}
		}

		return array(
			'created' => $created,
			'fetched' => count( $post_ids ),
			'more'    => count( $post_ids ) === (int) $per,
			'last_id' => $last_id,
		);
	}

	/**
	 * Snapshot page 1 inline (instant for small stores) and, if there is more,
	 * continue in the background so large catalogues never time out the request.
	 *
	 * @param int    $session_id
	 * @param string $scope_type
	 * @param array  $term_ids
	 * @return int Items created on the first page.
	 */
	public static function start_population( $session_id, $scope_type, $term_ids ) {
		$r = self::populate_page( $session_id, $scope_type, $term_ids, 0, self::batch_size() );
		if ( $r['more'] ) {
			self::set_building( $session_id, 1 );
			self::schedule_populate( $session_id, (int) $r['last_id'] );
		}
		return $r['created'];
	}

	/**
	 * Background callback: snapshot one more batch, then chain or finish.
	 *
	 * @param int $session_id
	 * @param int $after_id Keyset cursor from the previous batch.
	 */
	public static function populate_batch( $session_id, $after_id ) {
		$session = self::get_session( $session_id );
		if ( ! $session ) {
			return;
		}
		// A cancelled/completed session must not keep consuming background
		// workers and inserting rows; clear the flag so it never reads as stuck.
		$stwc_live = array( 'open' );
		if ( ! in_array( $session['status'], $stwc_live, true ) ) {
			self::set_building( $session_id, 0 );
			return;
		}
		$term_ids = json_decode( (string) $session['scope_value'], true );
		$r        = self::populate_page( $session_id, $session['scope_type'], (array) $term_ids, (int) $after_id, self::batch_size() );

		if ( $r['more'] ) {
			self::schedule_populate( $session_id, (int) $r['last_id'] );
		} else {
			self::set_building( $session_id, 0 );
		}
	}

	/**
	 * Queue the next snapshot batch (Action Scheduler preferred, WP-Cron fallback).
	 *
	 * @param int $session_id
	 * @param int $after_id Keyset cursor to resume from.
	 */
	protected static function schedule_populate( $session_id, $after_id ) {
		if ( function_exists( 'as_enqueue_async_action' ) ) {
			as_enqueue_async_action( 'stwc_populate', array( (int) $session_id, (int) $after_id ), 'stwc' );
		} else {
			wp_schedule_single_event( time() + 5, 'stwc_populate', array( (int) $session_id, (int) $after_id ) );
		}
	}

	/**
	 * Self-heal a stuck snapshot: a session still flagged "building" while the
	 * queue holds no populate action at all means the chained job was lost
	 * (purged queue, crashed worker) and no user retry can ever succeed.
	 * Re-enqueue from cursor 0 — INSERT IGNORE makes the re-walk idempotent.
	 *
	 * @param array $session Session row.
	 */
	public static function maybe_resume_build( $session ) {
		static $checking = false;
		if ( $checking || empty( $session['building'] ) || empty( $session['id'] ) ) {
			return;
		}
		// Without Action Scheduler (WP-Cron fallback) pending jobs cannot be
		// inspected, so the chain must be left alone.
		if ( ! function_exists( 'as_next_scheduled_action' ) ) {
			return;
		}
		$checking  = true;
		$stwc_live = array( 'open' );
		if ( ! in_array( $session['status'], $stwc_live, true ) ) {
			self::set_building( (int) $session['id'], 0 );
		} elseif ( false === as_next_scheduled_action( 'stwc_populate', null, 'stwc' ) ) {
			// No pending or running populate job anywhere: this build is lost.
			self::schedule_populate( (int) $session['id'], 0 );
		}
		$checking = false;
	}

	/**
	 * @param int  $session_id
	 * @param bool $building
	 */
	public static function set_building( $session_id, $building ) {
		global $wpdb;
		$wpdb->update( self::sessions_table(), array( 'building' => $building ? 1 : 0 ), array( 'id' => $session_id ), array( '%d' ), array( '%d' ) );
	}

	/**
	 * Insert a single count item snapshotting expected qty and unit cost.
	 *
	 * @param int        $session_id
	 * @param WC_Product $product Simple product, variation, or a variable parent
	 *                            that pools its variations' stock.
	 * @return bool
	 */
	public static function insert_item( $session_id, $product ) {
		global $wpdb;

		$is_variation = $product->is_type( 'variation' );
		$product_id   = $is_variation ? $product->get_parent_id() : $product->get_id();
		$variation_id = $is_variation ? $product->get_id() : 0;
		$expected     = wc_stock_amount( $product->get_stock_quantity() );

		// A SKU-less variation inherits the parent SKU through get_sku(); a
		// snapshotted inherited SKU would let a parent-SKU scan match an
		// arbitrary sibling line. Store the variation's OWN sku ('' if none —
		// find_item_by_code never matches an empty code).
		$sku = $is_variation ? (string) get_post_meta( $variation_id, '_sku', true ) : (string) $product->get_sku();

		// INSERT IGNORE + the unique (session_id, product_id, variation_id) key make
		// re-running a snapshot page idempotent (safe against Action Scheduler
		// retries or a catalogue changing mid-build). Fresh snapshot rows always
		// have NULL counted_qty/counted_by/counted_at, so those are literal NULLs.
		$sql = $wpdb->prepare(
			'INSERT IGNORE INTO %i' .
			' (session_id, product_id, variation_id, sku, name, expected_qty, counted_qty, unit_cost, applied, applied_from, counted_by, counted_at)' .
			' VALUES (%d, %d, %d, %s, %s, %f, NULL, %f, 0, NULL, NULL, NULL)',
			self::items_table(),
			$session_id,
			$product_id,
			$variation_id,
			$sku,
			$product->get_name(),
			$expected,
			self::get_unit_cost( $product )
		);
		return false !== $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is fully prepared above.
	}

	/**
	 * Read one cost meta key without going through the WC_Data accessor.
	 *
	 * `$product->get_meta( '_cogs_value' )` looks like the right call and is
	 * not. Since WooCommerce 9.5 that key is INTERNAL: the accessor routes it
	 * to WC_Product::get_cogs_value(), which returns nothing at all while the
	 * Cost of Goods Sold feature flag is off — the default on most stores —
	 * and emits two _doing_it_wrong notices every time it is asked.
	 *
	 * The effect was silent and expensive: a store that records cost only in
	 * WooCommerce's own field got an empty read here, fell through to the
	 * regular price, and had every variance valued at RETAIL while the
	 * valuation report — which reads postmeta by SQL — showed the true cost.
	 * Same product, two costs, depending on the screen.
	 *
	 * So read the row, the way the report already does. A variation with no
	 * cost of its own inherits the parent's, which is where cost is usually
	 * recorded for a variable product.
	 *
	 * @param WC_Product $product
	 * @param int        $pid
	 * @param string     $key
	 * @return string
	 */
	private static function read_cost_meta( $product, $pid, $key ) {
		$val = get_post_meta( $pid, $key, true );
		if ( ( '' === $val || null === $val ) && $product->is_type( 'variation' ) ) {
			$parent = $product->get_parent_id();
			if ( $parent ) {
				$val = get_post_meta( $parent, $key, true );
			}
		}
		return $val;
	}

	/**
	 * Resolve a unit cost for valuing variances.
	 *
	 * Defaults to the WooCommerce "Cost of Goods" meta if present, else the
	 * product price as a rough fallback. Hook `stwc_item_unit_cost` to override.
	 *
	 * @param WC_Product $product
	 * @return float
	 */
	public static function get_unit_cost( $product ) {
		$cost = '';
		$pid  = $product->get_id();
		foreach ( STWC_Report::cost_meta_keys() as $key ) {
			$val = self::read_cost_meta( $product, $pid, $key );
			if ( '' !== $val && null !== $val ) {
				$cost = $val;
				break;
			}
		}
		if ( '' === $cost || null === $cost ) {
			// No real cost meta: fall back to the regular price (not the sale
			// price) so the variance value isn't understated by promotions.
			$cost = $product->get_regular_price();
		}
		if ( '' === $cost || null === $cost ) {
			$cost = $product->get_price();
		}
		$cost = (float) $cost;

		/**
		 * Filter the per-unit cost used to value stock variances.
		 *
		 * @param float      $cost
		 * @param WC_Product $product
		 */
		return (float) apply_filters( 'stwc_item_unit_cost', $cost, $product );
	}

	/**
	 * Get items for a session.
	 *
	 * @param int   $session_id
	 * @param array $args 'only' => 'counted'|'uncounted'|'variance'
	 * @return array
	 */
	public static function get_items( $session_id, $args = array() ) {
		global $wpdb;
		// Only literal conditions are appended here; the single value
		// (session_id) stays a %d placeholder and is bound by prepare() below.
		$where = 'session_id = %d';

		if ( isset( $args['only'] ) ) {
			if ( 'counted' === $args['only'] ) {
				$where .= ' AND counted_qty IS NOT NULL';
			} elseif ( 'uncounted' === $args['only'] ) {
				$where .= ' AND counted_qty IS NULL';
			} elseif ( 'variance' === $args['only'] ) {
				// Epsilon, not <>: DOUBLE accumulation drift (0.1-step scans)
				// must not read as a variance.
				$where .= ' AND counted_qty IS NOT NULL AND ABS(counted_qty - expected_qty) > 0.000001';
			} elseif ( 'applied' === $args['only'] ) {
				$where .= ' AND applied = 1';
			}
		}

		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM %i WHERE {$where} ORDER BY name ASC", self::items_table(), $session_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where holds only literal clauses plus the %d bound here.
			ARRAY_A
		);
	}

	/**
	 * Resolve a scanned barcode via WooCommerce's own GTIN / UPC / EAN / ISBN
	 * product field (WC 9.2+). Real-world barcodes usually live there rather
	 * than in the SKU. Returns the product or variation id, 0 when no match.
	 *
	 * @param string $code
	 * @return int
	 */
	public static function product_id_by_barcode( $code ) {
		global $wpdb;
		$code = trim( (string) $code );
		if ( '' === $code ) {
			return 0;
		}

		$lookup = $wpdb->prefix . 'wc_product_meta_lookup';

		static $has_column = null;
		if ( null === $has_column ) {
			// The lookup column ships with WC 9.2; older stores fall back to postmeta.
			$has_column = (bool) $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $lookup, 'global_unique_id' ) );
		}

		$stwc_found = 0;
		if ( $has_column ) {
			$stwc_found = (int) $wpdb->get_var(
				$wpdb->prepare( 'SELECT product_id FROM %i WHERE global_unique_id = %s LIMIT 1', $lookup, $code )
			);
		} else {
			$stwc_found = (int) $wpdb->get_var(
				$wpdb->prepare( "SELECT post_id FROM %i WHERE meta_key = '_global_unique_id' AND meta_value = %s LIMIT 1", $wpdb->postmeta, $code )
			);
		}
		if ( $stwc_found ) {
			return $stwc_found;
		}

		// `_barcode` is where label printers, scanners and the POS in this suite
		// keep the number actually printed on the package - the POS resolves a
		// scan against it FIRST. Without this fallback the same physical barcode
		// worked at the till and found nothing during a stocktake, on the very
		// stores most likely to own both. Checked second so a shop that already
		// relies on WooCommerce's GTIN field keeps the behaviour (and the indexed
		// lookup) it has today; the two only disagree if a product carries two
		// different numbers, which is a data problem either way.
		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT post_id FROM %i WHERE meta_key = '_barcode' AND meta_value = %s LIMIT 1", $wpdb->postmeta, $code )
		);
	}

	/**
	 * Find a single item in a session by scanned code — snapshotted SKU first,
	 * then the WooCommerce SKU index, then the GTIN/UPC/EAN/ISBN barcode field.
	 * Variations resolve to their own line (parent id + variation id).
	 *
	 * @param int    $session_id
	 * @param string $code
	 * @return array|null
	 */
	public static function find_item_by_code( $session_id, $code ) {
		global $wpdb;
		$code = trim( $code );

		// An empty code must never match — a SKU-less item stores sku = '' and
		// would otherwise be returned for a mis-scan or a blank submit.
		if ( '' === $code ) {
			return null;
		}

		// 1) Direct SKU match on a snapshotted item.
		$item = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE session_id = %d AND sku = %s LIMIT 1',
				self::items_table(),
				$session_id,
				$code
			),
			ARRAY_A
		);
		if ( $item ) {
			return $item;
		}

		// 2) WooCommerce SKU index, then the core barcode (GTIN) field.
		$product_id = wc_get_product_id_by_sku( $code );
		if ( ! $product_id ) {
			$product_id = self::product_id_by_barcode( $code );
		}
		if ( $product_id ) {
			$product      = wc_get_product( $product_id );
			$is_variation = $product && $product->is_type( 'variation' );
			$pid          = $is_variation ? $product->get_parent_id() : $product_id;
			$vid          = $is_variation ? $product_id : 0;

			// A variation whose stock pools at the parent is snapshotted as ONE
			// parent line (variation_id = 0) — its scan must land there.
			if ( $is_variation && 'parent' === $product->managing_stock() ) {
				$vid = 0;
			}

			$item = $wpdb->get_row(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE session_id = %d AND product_id = %d AND variation_id = %d LIMIT 1',
					self::items_table(),
					$session_id,
					$pid,
					$vid
				),
				ARRAY_A
			);
			if ( $item ) {
				return $item;
			}
		}

		return null;
	}

	/**
	 * Find a session item by its product / variation id.
	 *
	 * @param int $session_id
	 * @param int $product_id
	 * @param int $variation_id
	 * @return array|null
	 */
	public static function find_item_by_target( $session_id, $product_id, $variation_id ) {
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE session_id = %d AND product_id = %d AND variation_id = %d LIMIT 1',
				self::items_table(),
				$session_id,
				$product_id,
				$variation_id
			),
			ARRAY_A
		);
		return $row ?: null;
	}

	/**
	 * Record a counted quantity for an item (overwrite).
	 *
	 * Re-counting clears the applied flag so a correction after an Apply is not
	 * silently skipped by the next Apply. applied_from is deliberately KEPT: it
	 * marks "this session already pushed this line once", which lets Apply
	 * re-push a recount that lands back on the snapshot expected while still
	 * leaving never-pushed matched lines alone (sales during the count must
	 * not be reverted). Undo clears it via the backup rollback.
	 *
	 * @param int   $item_id
	 * @param float $qty
	 * @return bool
	 */
	public static function record_count( $item_id, $qty ) {
		global $wpdb;
		$old = self::get_item( $item_id );
		$ok  = false !== $wpdb->update(
			self::items_table(),
			array(
				'counted_qty' => (float) $qty,
				'counted_by'  => get_current_user_id(),
				'counted_at'  => current_time( 'mysql' ),
				'applied'     => 0,
			),
			array( 'id' => $item_id ),
			array( '%f', '%d', '%s', '%d' ),
			array( '%d' )
		);
		if ( $ok && $old ) {
			STWC_Log::add(
				array(
					'session_id'   => (int) $old['session_id'],
					'item_id'      => $item_id,
					'product_id'   => (int) $old['product_id'],
					'variation_id' => (int) $old['variation_id'],
					'name'         => $old['name'],
					'event'        => is_null( $old['counted_qty'] ) ? 'counted' : 'recount',
					'qty_before'   => is_null( $old['counted_qty'] ) ? null : (float) $old['counted_qty'],
					'qty_after'    => (float) $qty,
				)
			);
		}
		return $ok;
	}

	/**
	 * Remove an item's count (the row goes back to "not counted yet").
	 * Idempotent: uncounting an already-uncounted line is a no-op success.
	 *
	 * @param int $item_id
	 * @return bool
	 */
	public static function uncount( $item_id, $require_unapplied = false ) {
		global $wpdb;
		$old = self::get_item( $item_id );
		if ( ! $old ) {
			return false;
		}
		if ( is_null( $old['counted_qty'] ) ) {
			return true;
		}
		// Conditional on "still counted" so two overlapping uncounts can't both
		// claim the removal: only the request that actually flips the row logs.
		{
			$affected = $wpdb->query(
				$wpdb->prepare(
					'UPDATE %i SET counted_qty = NULL, counted_by = NULL, counted_at = NULL, applied = 0 WHERE id = %d AND counted_qty IS NOT NULL',
					self::items_table(),
					$item_id
				)
			);
		}
		if ( false === $affected ) {
			return false;
		}
		$ok = true;
		if ( 0 === (int) $affected ) {
			// Someone else changed the row first: removed by a parallel uncount
			// (idempotent success) — or blocked by the applied_qty guard.
			$fresh = self::get_item( $item_id );
			return $fresh && is_null( $fresh['counted_qty'] );
		}
		if ( $ok ) {
			STWC_Log::add(
				array(
					'session_id'   => (int) $old['session_id'],
					'item_id'      => $item_id,
					'product_id'   => (int) $old['product_id'],
					'variation_id' => (int) $old['variation_id'],
					'name'         => $old['name'],
					'event'        => 'uncounted',
					'qty_before'   => (float) $old['counted_qty'],
					'qty_after'    => null,
				)
			);
		}
		return $ok;
	}


	/**
	 * Add a product to a session that was outside the original scope (over-count).
	 *
	 * @param int $session_id
	 * @param int $product_id A simple product id or variation id.
	 * @return array|WP_Error The new item row.
	 */
	public static function add_extra_item( $session_id, $product_id ) {
		$product = wc_get_product( $product_id );
		if ( ! $product ) {
			return new WP_Error( 'stwc_no_product', __( 'Product not found.', 'stocktake-for-woocommerce' ) );
		}
		if ( ! $product->is_type( 'simple' ) && ! $product->is_type( 'variation' ) ) {
			// A variable PARENT is a valid count line only when it pools the
			// stock itself (one shared quantity for all variations).
			if ( ! ( $product->is_type( 'variable' ) && $product->managing_stock() ) ) {
				return new WP_Error( 'stwc_bad_type', __( 'Only simple products and variations can be counted.', 'stocktake-for-woocommerce' ) );
			}
		}
		// Never add or later apply stock to a draft/trashed product: check the
		// effective status (a variation follows its variable parent).
		$stwc_status_id = $product->is_type( 'variation' ) ? $product->get_parent_id() : $product->get_id();
		if ( 'publish' !== get_post_status( $stwc_status_id ) ) {
			return new WP_Error( 'stwc_not_published', __( 'That product is not published and cannot be counted.', 'stocktake-for-woocommerce' ) );
		}
		// Parent-level stock pool: a stock write to such a variation is inert
		// (managing_stock() returns 'parent'), so the count line must be the
		// parent product itself.
		if ( $product->is_type( 'variation' ) && 'parent' === $product->managing_stock() ) {
			$stwc_parent = wc_get_product( $product->get_parent_id() );
			if ( ! $stwc_parent ) {
				return new WP_Error( 'stwc_no_product', __( 'Product not found.', 'stocktake-for-woocommerce' ) );
			}
			$product = $stwc_parent;
		}
		// Resolve the target BEFORE inserting. INSERT IGNORE looks identical
		// whether the row is new or was already in the snapshot, and marking an
		// IN-SCOPE row as "extra" would exempt it from the zero rule it genuinely
		// belongs to - the opposite mistake, and a worse one.
		$is_variation = $product->is_type( 'variation' );
		$pid          = $is_variation ? $product->get_parent_id() : $product->get_id();
		$vid          = $is_variation ? $product->get_id() : 0;
		$stwc_existed = (bool) self::find_item_by_target( $session_id, $pid, $vid );

		if ( ! self::insert_item( $session_id, $product ) ) {
			return new WP_Error( 'stwc_insert_failed', __( 'Could not add the product to the session.', 'stocktake-for-woocommerce' ) );
		}

		$item = self::find_item_by_target( $session_id, $pid, $vid );
		if ( $item && ! $stwc_existed ) {
			// Out-of-scope line: nobody promised to count it, so a full count must
			// not zero it if its count is later removed (see effective_counted()).
			global $wpdb;
			$wpdb->update( self::items_table(), array( 'extra' => 1 ), array( 'id' => (int) $item['id'] ), array( '%d' ), array( '%d' ) );
			$item['extra'] = 1;
		}

		return $item ?: new WP_Error( 'stwc_insert_failed', __( 'Could not add the product to the session.', 'stocktake-for-woocommerce' ) );
	}

	/**
	 * @param int $item_id
	 * @return array|null
	 */
	public static function get_item( $item_id ) {
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::items_table(), $item_id ),
			ARRAY_A
		);
		return $row ?: null;
	}

	/**
	 * Aggregate counts and variance totals for a session.
	 *
	 * In "full" (zero-based) mode, uncounted items are treated as a counted 0,
	 * so they contribute negative variance. In other modes only counted lines
	 * count toward the variance.
	 *
	 * @param int         $session_id
	 * @param string|null $mode Force a mode; null = read from the session.
	 * @return array
	 */
	public static function get_summary( $session_id, $mode = null ) {
		global $wpdb;
		$table = self::items_table();

		if ( null === $mode ) {
			$session = self::get_session( $session_id );
			$mode    = $session ? $session['count_mode'] : 'partial';
		}

		if ( 'full' === $mode ) {
			// Uncounted -> effective 0, EXCEPT for a line the counter added
			// mid-count and then left uncounted. effective_counted() exempts
			// exactly that case, with the reason written beside it: removing the
			// count of an out-of-scope line means "forget this line", never "this
			// line is empty". This has to agree with it, or the screen reports a
			// shortfall that Apply will refuse to make.
			//
			// Measured before this was fixed, on a session holding one counted
			// in-scope line and one added-then-uncounted line of 40 @ 3.00: the
			// summary said 1 variance line, -40 units and -120.00, and Apply
			// moved nothing. A shopkeeper reads that money figure, applies the
			// count, and the shortfall they were shown never happens.
			$stwc_forgotten = '( counted_qty IS NULL AND extra = 1 )';
			$variance_items = "SUM(CASE WHEN {$stwc_forgotten} THEN 0 WHEN ABS(COALESCE(counted_qty, 0) - expected_qty) > 0.000001 THEN 1 ELSE 0 END)";
			$variance_units = "SUM(CASE WHEN {$stwc_forgotten} THEN 0 ELSE COALESCE(counted_qty, 0) - expected_qty END)";
			$variance_value = "SUM(CASE WHEN {$stwc_forgotten} THEN 0 ELSE ( COALESCE(counted_qty, 0) - expected_qty ) * unit_cost END)";
		} else {
			$variance_items = 'SUM(CASE WHEN counted_qty IS NOT NULL AND ABS(counted_qty - expected_qty) > 0.000001 THEN 1 ELSE 0 END)';
			$variance_units = 'SUM(CASE WHEN counted_qty IS NOT NULL THEN (counted_qty - expected_qty) ELSE 0 END)';
			$variance_value = 'SUM(CASE WHEN counted_qty IS NOT NULL THEN (counted_qty - expected_qty) * unit_cost ELSE 0 END)';
		}

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) AS total_items,
					SUM(CASE WHEN counted_qty IS NOT NULL THEN 1 ELSE 0 END) AS counted_items,
					{$variance_items} AS variance_items,
					{$variance_units} AS variance_units,
					{$variance_value} AS variance_value
				FROM %i WHERE session_id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- the variance_* fragments are literal SQL built above from constants.
				$table,
				$session_id
			),
			ARRAY_A
		);

		return array(
			'total_items'    => (int) ( $row['total_items'] ?? 0 ),
			'counted_items'  => (int) ( $row['counted_items'] ?? 0 ),
			'variance_items' => (int) ( $row['variance_items'] ?? 0 ),
			'variance_units' => (float) ( $row['variance_units'] ?? 0 ),
			'variance_value' => (float) ( $row['variance_value'] ?? 0 ),
		);
	}



	/**
	 * Effective counted quantity for an item given the session mode.
	 * In "full" mode an uncounted line resolves to 0 (it will be zeroed out).
	 *
	 * @param array  $item
	 * @param string $mode
	 * @return float|null Null means "not counted and not forced" (no variance).
	 */
	public static function effective_counted( $item, $mode ) {
		if ( ! is_null( $item['counted_qty'] ) ) {
			return (float) $item['counted_qty'];
		}
		// A full count treats "not counted" as zero - that is the whole point of a
		// zero-based count. But a line the counter ADDED mid-count (a product that
		// was outside the session's scope) was never part of that promise: nobody
		// undertook to count it. Scan the wrong item, correct it with the X, and
		// the row is left uncounted - the zero rule would then set a product to 0
		// that no one ever counted, and Apply would empty a shelf that is full.
		// Removing the count of an out-of-scope line means "forget this line",
		// never "this line is empty".
		if ( 'full' === $mode && ! empty( $item['extra'] ) ) {
			return null;
		}
		return ( 'full' === $mode ) ? 0.0 : null;
	}

	/**
	 * The most recently counted items (for resuming a count).
	 *
	 * @param int $session_id
	 * @param int $limit
	 * @return array
	 */
	public static function recent_counted( $session_id, $limit = 12 ) {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE session_id = %d AND counted_qty IS NOT NULL ORDER BY counted_at DESC, id DESC LIMIT %d',
				self::items_table(),
				$session_id,
				$limit
			),
			ARRAY_A
		);
	}

	/**
	 * How many items have not been counted yet.
	 *
	 * @param int $session_id
	 * @return int
	 */
	public static function remaining_count( $session_id ) {
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE session_id = %d AND counted_qty IS NULL', self::items_table(), $session_id )
		);
	}

}
