<?php
/**
 * Run item repository.
 *
 * @package Recast\WCS
 */

namespace Recast\WCS\Run;

use Recast\WCS\Install\Schema;
use Recast\WCS\Support\Json;
use Recast\WCS\Support\Money;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- This class owns the recast_* tables; nothing here touches WordPress or WooCommerce tables.

/**
 * Persistence for run items (the only place that writes recast_run_items).
 */
final class RunItemRepository {

	/**
	 * Table name.
	 *
	 * @return string
	 */
	private function table() {
		return Schema::table( 'run_items' );
	}

	/**
	 * Idempotency key for a run/subscription pair.
	 *
	 * @param string $run_uuid        Run uuid.
	 * @param int    $subscription_id Subscription.
	 * @return string 40 chars.
	 */
	public static function idem_key( $run_uuid, $subscription_id ) {
		return substr( hash( 'sha256', $run_uuid . '|' . (int) $subscription_id ), 0, 40 );
	}

	/**
	 * Inserts snapshot rows (INSERT IGNORE, so re-running a resolve page never duplicates).
	 *
	 * @param Run   $run              Run.
	 * @param int[] $subscription_ids IDs in cohort order.
	 * @param int   $start_position   Position of the first id.
	 * @return int Rows inserted.
	 */
	public function insert_items( Run $run, array $subscription_ids, $start_position = 0 ) {
		global $wpdb;
		if ( empty( $subscription_ids ) ) {
			return 0;
		}
		$inserted = 0;
		$position = (int) $start_position;
		foreach ( array_chunk( $subscription_ids, 200 ) as $chunk ) {
			$values = array();
			$vals   = array();
			foreach ( $chunk as $subscription_id ) {
				$values[] = '(%d, %d, %d, %s, %s)';
				$vals[]   = $run->id;
				$vals[]   = (int) $subscription_id;
				$vals[]   = $position++;
				$vals[]   = self::idem_key( $run->uuid, (int) $subscription_id );
				$vals[]   = ItemStatus::PENDING;
			}
			$sql       = "INSERT IGNORE INTO {$this->table()} (run_id, subscription_id, position, idem_key, status) VALUES " . implode( ',', $values );
			$inserted += (int) $wpdb->query( $wpdb->prepare( $sql, $vals ) ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		}
		return $inserted;
	}

	/**
	 * Loads one item.
	 *
	 * @param int $id Id.
	 * @return RunItem|null
	 */
	public function get( $id ) {
		global $wpdb;
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $this->table(), $id ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return $row ? RunItem::from_row( $row ) : null;
	}

	/**
	 * Loads the item for a subscription within a run.
	 *
	 * @param int $run_id          Run.
	 * @param int $subscription_id Subscription.
	 * @return RunItem|null
	 */
	public function get_by_subscription( $run_id, $subscription_id ) {
		global $wpdb;
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE run_id = %d AND subscription_id = %d', $this->table(), $run_id, $subscription_id ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return $row ? RunItem::from_row( $row ) : null;
	}

	/**
	 * Next batch of items in a status, by id.
	 *
	 * @param int    $run_id Run.
	 * @param string $status Status.
	 * @param int    $limit  Limit.
	 * @return RunItem[]
	 */
	public function batch( $run_id, $status, $limit ) {
		global $wpdb;
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE run_id = %d AND status = %s ORDER BY id ASC LIMIT %d', $this->table(), $run_id, $status, $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return array_map( array( RunItem::class, 'from_row' ), $rows ? $rows : array() );
	}

	/**
	 * Updates an item, optionally only when the worker token still matches.
	 *
	 * @param int                  $id     Id.
	 * @param array<string, mixed> $fields Column => value (arrays JSON-encoded).
	 * @param string|null          $token  Required worker token.
	 * @return bool Whether a row changed.
	 */
	public function update( $id, array $fields, $token = null ) {
		global $wpdb;
		foreach ( $fields as $key => $value ) {
			if ( is_array( $value ) ) {
				$fields[ $key ] = Json::encode( $value );
			}
		}
		$where = array( 'id' => (int) $id );
		if ( null !== $token ) {
			$where['worker_token'] = $token;
		}
		$result = $wpdb->update( $this->table(), $fields, $where ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return false !== $result && $result > 0;
	}

	/**
	 * Stores a computed preview on an item.
	 *
	 * @param RunItem $item Item (mutated in place).
	 * @return void
	 */
	public function save_preview( RunItem $item ) {
		$this->update(
			$item->id,
			array(
				'status'       => $item->status,
				'before_hash'  => $item->before_hash,
				'after_hash'   => $item->after_hash,
				'before_state' => $item->before_state,
				'after_state'  => $item->after_state,
				'diff'         => $item->diff,
				'warnings'     => $item->warnings,
				'error'        => null === $item->error ? null : Json::encode( $item->error ),
			)
		);
	}

	/**
	 * Counts by status.
	 *
	 * @param int $run_id Run.
	 * @return array<string, int>
	 */
	public function counts( $run_id ) {
		global $wpdb;
		$rows   = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i WHERE run_id = %d GROUP BY status', $this->table(), $run_id ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		$counts = array_fill_keys( ItemStatus::all(), 0 );
		foreach ( $rows ? $rows : array() as $row ) {
			$counts[ $row['status'] ] = (int) $row['n'];
		}
		return $counts;
	}

	/**
	 * Total items in a run.
	 *
	 * @param int $run_id Run.
	 * @return int
	 */
	public function total( $run_id ) {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE run_id = %d', $this->table(), $run_id ) ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
	}

	/**
	 * Subscription IDs in a status.
	 *
	 * @param int             $run_id   Run.
	 * @param string|string[] $statuses Statuses.
	 * @return int[]
	 */
	public function subscription_ids( $run_id, $statuses ) {
		global $wpdb;
		$statuses     = (array) $statuses;
		$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
		$ids          = $wpdb->get_col( $wpdb->prepare( "SELECT subscription_id FROM %i WHERE run_id = %d AND status IN ({$placeholders}) ORDER BY id ASC", array_merge( array( $this->table(), $run_id ), $statuses ) ) ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return array_map( 'intval', $ids ? $ids : array() );
	}

	/**
	 * Lists items for the UI.
	 *
	 * @param int                  $run_id   Run.
	 * @param array<string, mixed> $filters  status[], changed (bool).
	 * @param int                  $page     Page.
	 * @param int                  $per_page Per page.
	 * @return array{items: RunItem[], total: int}
	 */
	public function list( $run_id, array $filters = array(), $page = 1, $per_page = 25 ) {
		global $wpdb;
		$where = array( 'run_id = %d' );
		$vals  = array( (int) $run_id );
		if ( ! empty( $filters['status'] ) ) {
			$statuses = (array) $filters['status'];
			$where[]  = 'status IN (' . implode( ',', array_fill( 0, count( $statuses ), '%s' ) ) . ')';
			$vals     = array_merge( $vals, $statuses );
		}
		if ( ! empty( $filters['changed'] ) ) {
			$where[] = "status <> %s AND diff IS NOT NULL AND diff <> '[]'";
			$vals[]  = ItemStatus::NO_CHANGE;
		}
		if ( ! empty( $filters['subscription_id'] ) ) {
			$where[] = 'subscription_id = %d';
			$vals[]  = (int) $filters['subscription_id'];
		}
		$where_sql = implode( ' AND ', $where );
		$offset    = max( 0, ( (int) $page - 1 ) * (int) $per_page );
		$total     = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where_sql}", array_merge( array( $this->table() ), $vals ) ) ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		$rows      = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE {$where_sql} ORDER BY id ASC LIMIT %d OFFSET %d", array_merge( array( $this->table() ), $vals, array( (int) $per_page, $offset ) ) ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return array(
			'items' => array_map( array( RunItem::class, 'from_row' ), $rows ? $rows : array() ),
			'total' => $total,
		);
	}

	/**
	 * Bulk status change for a run.
	 *
	 * @param int                  $run_id Run.
	 * @param string|string[]      $from   From statuses.
	 * @param string               $to     To status.
	 * @param array<string, mixed> $error  Error JSON to set (optional).
	 * @return int Rows changed.
	 */
	public function transition_all( $run_id, $from, $to, ?array $error = null ) {
		global $wpdb;
		$from         = (array) $from;
		$placeholders = implode( ',', array_fill( 0, count( $from ), '%s' ) );
		$sql          = "UPDATE {$this->table()} SET status = %s, error = %s, worker_token = NULL WHERE run_id = %d AND status IN ({$placeholders})";
		return (int) $wpdb->query( $wpdb->prepare( $sql, array_merge( array( $to, null === $error ? null : Json::encode( $error ), (int) $run_id ), $from ) ) ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
	}

	/**
	 * Applied items of a run, for undo planning and history (chunked by id).
	 *
	 * @param int $run_id   Run.
	 * @param int $after_id Id cursor.
	 * @param int $limit    Limit.
	 * @return RunItem[]
	 */
	public function applied_after( $run_id, $after_id, $limit = 500 ) {
		global $wpdb;
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE run_id = %d AND status = %s AND id > %d ORDER BY id ASC LIMIT %d', $this->table(), $run_id, ItemStatus::APPLIED, $after_id, $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return array_map( array( RunItem::class, 'from_row' ), $rows ? $rows : array() );
	}

	/**
	 * The recurring total the preview predicted for one item: the after value of its
	 * total change, or its total before when the change did not touch the total.
	 *
	 * @param RunItem $item Item.
	 * @return string
	 */
	private static function predicted_total( RunItem $item ) {
		foreach ( $item->diff as $change ) {
			if ( isset( $change['field'] ) && 'total' === $change['field'] && isset( $change['after'] ) ) {
				return (string) $change['after'];
			}
		}
		return (string) $item->before_state['total'];
	}

	/**
	 * Items a run wrote to, including those undone since (chunked by id).
	 *
	 * @param int $run_id   Run.
	 * @param int $after_id Id cursor.
	 * @param int $limit    Limit.
	 * @return RunItem[]
	 */
	private function written_after( $run_id, $after_id, $limit = 500 ) {
		global $wpdb;
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE run_id = %d AND applied_at IS NOT NULL AND status IN (%s, %s) AND id > %d ORDER BY id ASC LIMIT %d', $this->table(), $run_id, ItemStatus::APPLIED, ItemStatus::REVERTED, $after_id, $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return array_map( array( RunItem::class, 'from_row' ), $rows ? $rows : array() );
	}

	/**
	 * Recurring totals before and after, per currency, over the items a run wrote (applied,
	 * or applied and later undone). "predicted" is what the preview predicted for the same
	 * items, so a batch stopped part way compares like with like.
	 * Reads the after state recorded from the saved subscription, so the figures match
	 * what WooCommerce stored.
	 *
	 * @param int $run_id Run.
	 * @return array<string, array{before: string, after: string, delta: string}>
	 */
	public function applied_money( $run_id ) {
		$money  = array();
		$cursor = 0;
		do {
			$items = $this->written_after( $run_id, $cursor );
			$full  = 500 === count( $items );
			foreach ( $items as $item ) {
				$cursor = $item->id;
				if ( ! isset( $item->before_state['total'], $item->after_state['total'] ) ) {
					continue;
				}
				$currency = isset( $item->before_state['currency'] ) ? (string) $item->before_state['currency'] : get_woocommerce_currency();
				if ( ! isset( $money[ $currency ] ) ) {
					$money[ $currency ] = array(
						'before'    => '0',
						'after'     => '0',
						'delta'     => '0',
						'predicted' => '0',
					);
				}
				$money[ $currency ]['before']    = Money::add( $money[ $currency ]['before'], $item->before_state['total'] );
				$money[ $currency ]['after']     = Money::add( $money[ $currency ]['after'], $item->after_state['total'] );
				$money[ $currency ]['delta']     = Money::sub( $money[ $currency ]['after'], $money[ $currency ]['before'] );
				$money[ $currency ]['predicted'] = Money::add( $money[ $currency ]['predicted'], self::predicted_total( $item ) );
			}
		} while ( $full );
		return $money;
	}

	/**
	 * Recurring totals per month before and after, per currency, over the items a run wrote.
	 * Each total is divided by the length of its billing cycle (before and after separately, so
	 * a billing schedule change counts too). Totals include tax, as customers pay them.
	 *
	 * @param int $run_id Run.
	 * @return array<string, array{before: string, after: string, delta: string, subscriptions: int}>
	 */
	public function applied_monthly( $run_id ) {
		$sums   = array();
		$cursor = 0;
		do {
			$items = $this->written_after( $run_id, $cursor );
			$full  = 500 === count( $items );
			foreach ( $items as $item ) {
				$cursor = $item->id;
				if ( ! isset( $item->before_state['total'], $item->after_state['total'] ) ) {
					continue;
				}
				$before = self::months( $item->before_state );
				$after  = self::months( $item->after_state );
				if ( ! $before || ! $after ) {
					$current = self::cycle_of( $item->subscription_id );
					$before  = $before ? $before : $current;
					$after   = $after ? $after : $before;
				}
				if ( ! $before || ! $after ) {
					continue;
				}
				$currency = isset( $item->before_state['currency'] ) ? (string) $item->before_state['currency'] : get_woocommerce_currency();
				if ( ! isset( $sums[ $currency ] ) ) {
					$sums[ $currency ] = array( 0.0, 0.0, 0 );
				}
				$sums[ $currency ][0] += (float) $item->before_state['total'] / $before;
				$sums[ $currency ][1] += (float) $item->after_state['total'] / $after;
				++$sums[ $currency ][2];
			}
		} while ( $full );
		$money = array();
		foreach ( $sums as $currency => $sum ) {
			$money[ $currency ] = array(
				'before'        => Money::format( $sum[0] ),
				'after'         => Money::format( $sum[1] ),
				'delta'         => Money::sub( Money::format( $sum[1] ), Money::format( $sum[0] ) ),
				'subscriptions' => $sum[2],
			);
		}
		return $money;
	}

	/**
	 * Billing cycle length in months from a recorded state, or null when the state has no
	 * billing schedule.
	 *
	 * @param array<string, mixed> $state Recorded state.
	 * @return float|null
	 */
	private static function months( array $state ) {
		if ( isset( $state['billing_period'] ) && '' !== (string) $state['billing_period'] ) {
			$interval = isset( $state['billing_interval'] ) ? (int) $state['billing_interval'] : 1;
			return \Recast\WCS\Health\PriceGap::months_per_cycle( (string) $state['billing_period'], $interval );
		}
		return null;
	}

	/**
	 * Current billing cycle length in months of a subscription, or null when it is gone.
	 *
	 * @param int $subscription_id Subscription.
	 * @return float|null
	 */
	private static function cycle_of( $subscription_id ) {
		$subscription = function_exists( 'wcs_get_subscription' ) ? wcs_get_subscription( $subscription_id ) : null;
		if ( ! $subscription instanceof \WC_Subscription ) {
			return null;
		}
		return \Recast\WCS\Health\PriceGap::months_per_cycle( (string) $subscription->get_billing_period(), (int) $subscription->get_billing_interval() );
	}

	/**
	 * History of one subscription across runs.
	 *
	 * @param int $subscription_id Subscription.
	 * @param int $limit           Limit.
	 * @return RunItem[]
	 */
	public function history( $subscription_id, $limit = 50 ) {
		global $wpdb;
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE subscription_id = %d AND applied_at IS NOT NULL ORDER BY applied_at DESC LIMIT %d', $this->table(), $subscription_id, $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB, PluginCheck.Security.DirectDB -- Recast-owned table, prepared query.
		return array_map( array( RunItem::class, 'from_row' ), $rows ? $rows : array() );
	}
}