<?php
/**
 * Data access for the `embp_rows` table.
 *
 * @package EmberPrice
 */

namespace EmberPrice\Core;

defined( 'ABSPATH' ) || exit;

/**
 * Lives in Core (not Ingestion) for the same reason as {@see Suppliers}:
 * rows are written incrementally across the whole pipeline (raw by
 * Ingestion, match_* by Matching, old/new_price by Apply).
 */
final class Rows {

	/**
	 * Bulk-inserts raw rows for a job. Array keys are used as `row_index`,
	 * so callers should pass rows with their original file row indices
	 * preserved (e.g. via `array_slice(..., preserve_keys: true)`).
	 *
	 * @param int                  $job_id Job id.
	 * @param array<int, string[]> $rows   Row index => cell values.
	 */
	public function insert_raw( int $job_id, array $rows ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		foreach ( $rows as $row_index => $row ) {
			$wpdb->insert(
				$table,
				array(
					'job_id'    => $job_id,
					'row_index' => (int) $row_index,
					'raw'       => wp_json_encode( $row ),
					'status'    => 'review',
				),
				array( '%d', '%d', '%s', '%s' )
			);
		}
	}

	/**
	 * All rows for a job, ordered by their original file position.
	 *
	 * @param int $job_id Job id.
	 */
	public function get_by_job( int $job_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$rows = $wpdb->get_results(
			$wpdb->prepare( 'SELECT * FROM %i WHERE job_id = %d ORDER BY row_index ASC', $table, $job_id ),
			ARRAY_A
		);

		return array_map( array( $this, 'hydrate' ), null !== $rows ? $rows : array() );
	}

	/**
	 * Updates a row's `parsed` column (normalized name/code/price).
	 *
	 * @param int   $row_id Row id.
	 * @param array $parsed Parsed data (name/code/price).
	 */
	public function update_parsed( int $row_id, array $parsed ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$wpdb->update(
			$table,
			array( 'parsed' => wp_json_encode( $parsed ) ),
			array( 'id' => $row_id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * A single row by id, or null if it doesn't exist.
	 *
	 * @param int $id Row id.
	 */
	public function find( int $id ): ?array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ),
			ARRAY_A
		);

		return null === $row ? null : $this->hydrate( $row );
	}

	/**
	 * A page of a job's rows, optionally filtered by status, for the
	 * review step's paginated table (GET /jobs/{id}/rows).
	 *
	 * @param int         $job_id   Job id.
	 * @param string|null $status   Status to filter by, or null for all.
	 * @param int         $page     1-based page number.
	 * @param int         $per_page Rows per page.
	 *
	 * @return array{rows: array, total: int}
	 */
	public function get_paginated( int $job_id, ?string $status, int $page, int $per_page ): array {
		global $wpdb;
		$table  = $wpdb->prefix . 'embp_rows';
		$offset = ( max( 1, $page ) - 1 ) * $per_page;

		if ( null !== $status && '' !== $status ) {
			$rows  = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i WHERE job_id = %d AND status = %s ORDER BY row_index ASC LIMIT %d OFFSET %d', $table, $job_id, $status, $per_page, $offset ),
				ARRAY_A
			);
			$total = (int) $wpdb->get_var(
				$wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE job_id = %d AND status = %s', $table, $job_id, $status )
			);
		} else {
			$rows  = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i WHERE job_id = %d ORDER BY row_index ASC LIMIT %d OFFSET %d', $table, $job_id, $per_page, $offset ),
				ARRAY_A
			);
			$total = $this->count_by_job( $job_id );
		}

		return array(
			'rows'  => array_map( array( $this, 'hydrate' ), null !== $rows ? $rows : array() ),
			'total' => $total,
		);
	}

	/**
	 * Updates only a row's `status` (confirm/reject leave match_* as-is;
	 * see update_match() for actions that also change the matched product).
	 *
	 * @param int    $row_id Row id.
	 * @param string $status New status.
	 */
	public function update_status( int $row_id, string $status ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$wpdb->update( $table, array( 'status' => $status ), array( 'id' => $row_id ), array( '%s' ), array( '%d' ) );
	}

	/**
	 * A page of a job's rows, ordered by their original file position --
	 * used by the async match batch runner (Rest\JobMatchScheduler) to walk
	 * a job in fixed-size chunks across separate Action Scheduler requests.
	 *
	 * @param int $job_id Job id.
	 * @param int $offset Rows to skip.
	 * @param int $limit  Max rows to return.
	 */
	public function get_batch( int $job_id, int $offset, int $limit ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$rows = $wpdb->get_results(
			$wpdb->prepare( 'SELECT * FROM %i WHERE job_id = %d ORDER BY row_index ASC LIMIT %d OFFSET %d', $table, $job_id, $limit, $offset ),
			ARRAY_A
		);

		return array_map( array( $this, 'hydrate' ), null !== $rows ? $rows : array() );
	}

	/**
	 * Total number of rows in a job.
	 *
	 * @param int $job_id Job id.
	 */
	public function count_by_job( int $job_id ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE job_id = %d', $table, $job_id ) );
	}

	/**
	 * Number of a job's rows already run through the match cascade (either
	 * resolved to a stage, or skipped outright for having no price) --
	 * used as the "done" count for GET /jobs/{id}/progress.
	 *
	 * @param int $job_id Job id.
	 */
	public function count_matched( int $job_id ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE job_id = %d AND (match_stage IS NOT NULL OR status = 'skipped')", $table, $job_id )
		);
	}

	/**
	 * Row counts grouped by status, for the job's `totals` once matching finishes.
	 *
	 * @param int $job_id Job id.
	 *
	 * @return array<string, int>
	 */
	public function counts_by_status( int $job_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$results = $wpdb->get_results(
			$wpdb->prepare( 'SELECT status, COUNT(*) as total FROM %i WHERE job_id = %d GROUP BY status', $table, $job_id ),
			ARRAY_A
		);

		$counts = array();

		foreach ( ( null !== $results ? $results : array() ) as $row ) {
			$counts[ $row['status'] ] = (int) $row['total'];
		}

		return $counts;
	}

	/**
	 * A job's rows whose status is one of $statuses -- used by the preview
	 * step (T-303) to price only rows that are actually going to be
	 * applied (auto/confirmed), without pulling the whole job into PHP.
	 *
	 * @param int      $job_id   Job id.
	 * @param string[] $statuses Statuses to include.
	 */
	public function get_by_job_and_statuses( int $job_id, array $statuses ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		if ( empty( $statuses ) ) {
			return array();
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $placeholders is a fixed count of %s tokens the sniff can't statically count against the leading %i.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE job_id = %d AND status IN ({$placeholders}) ORDER BY row_index ASC",
				array_merge( array( $table, $job_id ), $statuses )
			),
			ARRAY_A
		);
		// phpcs:enable

		return array_map( array( $this, 'hydrate' ), null !== $rows ? $rows : array() );
	}

	/**
	 * A page of a job's rows restricted to $statuses, ordered by their
	 * original file position -- used by Rest\JobApplyScheduler (T-305) to
	 * walk only the auto/confirmed rows in fixed-size chunks across
	 * separate Action Scheduler requests, the same way get_batch() does
	 * for the (unfiltered) match cascade.
	 *
	 * @param int      $job_id   Job id.
	 * @param string[] $statuses Statuses to include.
	 * @param int      $offset   Rows to skip.
	 * @param int      $limit    Max rows to return.
	 */
	public function get_batch_by_statuses( int $job_id, array $statuses, int $offset, int $limit ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		if ( empty( $statuses ) ) {
			return array();
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $placeholders is a fixed count of %s tokens the sniff can't statically count combined with the leading %i and trailing LIMIT/OFFSET %d's.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE job_id = %d AND status IN ({$placeholders}) ORDER BY row_index ASC LIMIT %d OFFSET %d",
				array_merge( array( $table, $job_id ), $statuses, array( $limit, $offset ) )
			),
			ARRAY_A
		);
		// phpcs:enable

		return array_map( array( $this, 'hydrate' ), null !== $rows ? $rows : array() );
	}

	/**
	 * Total number of a job's rows in one of $statuses -- the fixed
	 * "total" for the apply step's progress reporting, computed once
	 * before the first batch runs.
	 *
	 * @param int      $job_id   Job id.
	 * @param string[] $statuses Statuses to include.
	 */
	public function count_by_job_and_statuses( int $job_id, array $statuses ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		if ( empty( $statuses ) ) {
			return 0;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $placeholders is a fixed count of %s tokens the sniff can't statically count against the leading %i.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE job_id = %d AND status IN ({$placeholders})",
				array_merge( array( $table, $job_id ), $statuses )
			)
		);
		// phpcs:enable
	}

	/**
	 * Finds product/variation targets assigned to more than one eligible
	 * row. Every conflicting row must return to human review before preview.
	 *
	 * @param int      $job_id   Job id.
	 * @param string[] $statuses Eligible row statuses.
	 *
	 * @return array<int, int[]> Target id => row ids.
	 */
	public function duplicate_targets( int $job_id, array $statuses ): array {
		$targets = array();

		foreach ( $this->get_by_job_and_statuses( $job_id, $statuses ) as $row ) {
			$target_id = $row['match_variation_id'] ?? $row['match_product_id'];

			if ( null === $target_id ) {
				continue;
			}

			$targets[ (int) $target_id ][] = (int) $row['id'];
		}

		return array_filter( $targets, static fn( array $row_ids ): bool => count( $row_ids ) > 1 );
	}

	/**
	 * Appends one flag to a row's existing `flags` -- used by BatchApplier
	 * to log a per-row apply failure (e.g. the matched product was deleted
	 * after preview) without touching its status or price/status: a failed
	 * row doesn't abort its batch, and the job still finishes.
	 *
	 * @param int    $row_id Row id.
	 * @param string $flag   Flag to add, if not already present.
	 */
	public function add_flag( int $row_id, string $flag ): void {
		$row = $this->find( $row_id );

		if ( null === $row ) {
			return;
		}

		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$flags   = (array) $row['flags'];
		$flags[] = $flag;

		$wpdb->update(
			$table,
			array( 'flags' => wp_json_encode( array_values( array_unique( $flags ) ) ) ),
			array( 'id' => $row_id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Persists the preview computation (RuleEngine + Guardrails) for a row:
	 * old/new price and any flags, optionally demoting its status (e.g.
	 * Guardrails forcing `review` on a change that's too large).
	 *
	 * @param int         $row_id   Row id.
	 * @param float       $old_price Product's regular_price before this update.
	 * @param float       $new_price Computed regular_price after this update.
	 * @param string[]    $flags     Guardrail flags, if any.
	 * @param string|null $status New status, or null to leave it as-is.
	 */
	public function update_preview( int $row_id, float $old_price, float $new_price, array $flags, ?string $status = null ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$data    = array(
			'old_price' => $old_price,
			'new_price' => $new_price,
			'flags'     => wp_json_encode( $flags ),
		);
		$formats = array( '%f', '%f', '%s' );

		if ( null !== $status ) {
			$data['status'] = $status;
			$formats[]      = '%s';
		}

		$wpdb->update( $table, $data, array( 'id' => $row_id ), $formats, array( '%d' ) );
	}

	/**
	 * Updates a row's match_* columns after running it through the match
	 * cascade (Matching\MatchPipeline).
	 *
	 * @param int   $row_id      Row id.
	 * @param array $match_result Keys: product_id, variation_id, match_stage, confidence, status.
	 */
	public function update_match( int $row_id, array $match_result ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$wpdb->update(
			$table,
			array(
				'match_product_id'   => $match_result['product_id'],
				'match_variation_id' => $match_result['variation_id'],
				'match_stage'        => $match_result['match_stage'],
				'confidence'         => $match_result['confidence'],
				'status'             => $match_result['status'],
			),
			array( 'id' => $row_id ),
			array( '%d', '%d', '%s', '%f', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Deletes every row belonging to a job.
	 *
	 * @param int $job_id Job id.
	 */
	public function delete_by_job( int $job_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_rows';

		$wpdb->delete( $table, array( 'job_id' => $job_id ), array( '%d' ) );
	}

	/**
	 * Decodes the JSON columns into arrays and casts match_* column types.
	 *
	 * @param array $row Raw database row.
	 */
	private function hydrate( array $row ): array {
		$row['raw']    = json_decode( (string) $row['raw'], true ) ?? array();
		$row['parsed'] = null !== $row['parsed'] ? json_decode( (string) $row['parsed'], true ) : null;
		$row['flags']  = null !== $row['flags'] ? json_decode( (string) $row['flags'], true ) : array();

		$row['match_product_id']   = null !== $row['match_product_id'] ? (int) $row['match_product_id'] : null;
		$row['match_variation_id'] = null !== $row['match_variation_id'] ? (int) $row['match_variation_id'] : null;
		$row['confidence']         = null !== $row['confidence'] ? (float) $row['confidence'] : null;
		$row['old_price']          = null !== $row['old_price'] ? (float) $row['old_price'] : null;
		$row['new_price']          = null !== $row['new_price'] ? (float) $row['new_price'] : null;

		return $row;
	}
}
