<?php // phpcs:ignore WordPress.Files.FileName.InvalidClassFileName
/**
 * Payout Batches Database Abstraction Layer
 *
 * @package     AffiliateWP
 * @subpackage  Database
 * @copyright   Copyright (c) 2026, Sandhills Development, LLC
 * @license     http://opensource.org/licenses/gpl-2.0.php GNU Public License
 * @since       2.33.0
 *
 * phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
 */

#[AllowDynamicProperties]

/**
 * Database layer for payout batches — the container record that groups payouts
 * generated in a single run, so operators can reconcile, retry, and report on
 * them as a unit.
 *
 * @since 2.33.0
 *
 * @see \Affiliate_WP_DB
 */
class Affiliate_WP_Payout_Batches_DB extends Affiliate_WP_DB {

	/**
	 * Cache group used both for caching and for `affiliate_wp()->{group}->*` access.
	 *
	 * @since 2.33.0
	 * @var   string
	 */
	public $cache_group = 'payout_batches';

	/**
	 * Database group value.
	 *
	 * @since 2.33.0
	 * @var   string
	 */
	public $db_group = 'affiliates:payout_batches';

	/**
	 * Object type to query for.
	 *
	 * @since 2.33.0
	 * @var   string
	 */
	public $query_object_type = 'AffWP\\Affiliate\\Payout_Batch';

	/**
	 * Constructor.
	 *
	 * @since 2.33.0
	 */
	public function __construct() {
		global $wpdb;

		if ( defined( 'AFFILIATE_WP_NETWORK_WIDE' ) && AFFILIATE_WP_NETWORK_WIDE ) {
			$this->table_name = 'affiliate_wp_payout_batches';
		} else {
			$this->table_name = $wpdb->prefix . 'affiliate_wp_payout_batches';
		}

		$this->primary_key = 'batch_id';
		$this->version     = '1.1'; // 1.1 (2.35.0): schedule_id column + index — which schedule, if any, produced the batch.
	}

	/**
	 * Retrieves a payout batch object.
	 *
	 * @since 2.33.0
	 *
	 * @param int|AffWP\Affiliate\Payout_Batch $batch Batch ID or object.
	 * @return AffWP\Affiliate\Payout_Batch|false Batch object on success, false otherwise.
	 */
	public function get_object( $batch ) {
		return $this->get_core_object( $batch, $this->query_object_type );
	}

	/**
	 * Retrieves table columns and their placeholder types.
	 *
	 * @since 2.33.0
	 */
	public function get_columns() {
		return array(
			'batch_id'          => '%d',
			'date_created'      => '%s',
			'created_by'        => '%d',
			'payout_method'     => '%s',
			'status'            => '%s',
			'total_amount'      => '%s',
			'total_count'       => '%d',
			'processed_count'   => '%d',
			'failed_count'      => '%d',
			'sender_batch_id'   => '%s',
			'provider_batch_id' => '%s',
			'retry_of_batch_id' => '%d',
			'schedule_id'       => '%d',
			'processing_errors' => '%s',
			'meta'              => '%s',
		);
	}

	/**
	 * Retrieves default column values.
	 *
	 * @since 2.33.0
	 */
	public function get_column_defaults() {
		return array(
			'date_created'      => gmdate( 'Y-m-d H:i:s' ),
			'created_by'        => get_current_user_id(),
			'status'            => 'processing',
			'total_amount'      => 0,
			'total_count'       => 0,
			'processed_count'   => 0,
			'failed_count'      => 0,
			'schedule_id'       => 0,
			'processing_errors' => '',
			'meta'              => '',
		);
	}

	/**
	 * Adds a new payout batch.
	 *
	 * @since 2.33.0
	 *
	 * @param array $args Batch column values.
	 * @return int|false Batch ID on success, false on failure.
	 */
	public function add( $args = array() ) {

		$args = wp_parse_args(
			$args,
			array(
				'created_by'    => get_current_user_id(),
				'payout_method' => '',
				'status'        => 'processing',
				'date_created'  => '',
			)
		);

		if ( ! empty( $args['payout_method'] ) ) {
			$args['payout_method'] = sanitize_text_field( $args['payout_method'] );
		}

		if ( ! empty( $args['status'] ) ) {
			$args['status'] = sanitize_key( $args['status'] );
		}

		if ( empty( $args['date_created'] ) ) {
			unset( $args['date_created'] );
		}

		if ( isset( $args['meta'] ) && is_array( $args['meta'] ) ) {
			$args['meta'] = wp_json_encode( $args['meta'] );
		}

		if ( isset( $args['processing_errors'] ) && is_array( $args['processing_errors'] ) ) {
			$args['processing_errors'] = wp_json_encode( $args['processing_errors'] );
		}

		$add = $this->insert( $args, 'payout_batch' );

		if ( $add ) {
			/**
			 * Fires immediately after a payout batch has been inserted.
			 *
			 * @since 2.33.0
			 *
			 * @param int $add New batch ID.
			 */
			do_action( 'affwp_insert_payout_batch', $add );
		}

		return $add;
	}

	/**
	 * Retrieves payout batches matching the supplied query arguments.
	 *
	 * @since 2.33.0
	 *
	 * @param array $args  Query arguments.
	 * @param bool  $count Whether to return only a count.
	 * @return array|int Array of batch objects (or IDs), or int when $count is true.
	 */
	public function get_batches( $args = array(), $count = false ) {
		global $wpdb;

		$defaults = array(
			'number'            => 20,
			'offset'            => 0,
			'batch_id'          => 0,
			'created_by'        => 0,
			'payout_method'     => '',
			'status'            => '',
			'retry_of_batch_id' => 0,
			'search'            => '',
			'order'             => 'DESC',
			'orderby'           => 'batch_id',
			'fields'            => '',
		);

		$args = wp_parse_args( $args, $defaults );

		if ( $args['number'] < 1 ) {
			$args['number'] = 999999999999;
		}

		$where = $join = '';

		if ( ! empty( $args['batch_id'] ) ) {
			$where .= empty( $where ) ? 'WHERE ' : 'AND ';
			if ( is_array( $args['batch_id'] ) ) {
				$ids = implode( ',', array_map( 'intval', $args['batch_id'] ) );
			} else {
				$ids = intval( $args['batch_id'] );
			}
			$where .= "`batch_id` IN( {$ids} ) ";
		}

		if ( ! empty( $args['created_by'] ) ) {
			$where .= empty( $where ) ? 'WHERE ' : 'AND ';
			$where .= $wpdb->prepare( '`created_by` = %d ', intval( $args['created_by'] ) );
		}

		if ( ! empty( $args['payout_method'] ) ) {
			$where .= empty( $where ) ? 'WHERE ' : 'AND ';
			$where .= $wpdb->prepare( '`payout_method` = %s ', $args['payout_method'] );
		}

		if ( ! empty( $args['status'] ) ) {
			$where .= empty( $where ) ? 'WHERE ' : 'AND ';
			if ( is_array( $args['status'] ) ) {
				$where .= "`status` IN('" . implode( "','", array_map( 'esc_sql', $args['status'] ) ) . "') ";
			} else {
				$where .= $wpdb->prepare( '`status` = %s ', $args['status'] );
			}
		}

		if ( ! empty( $args['retry_of_batch_id'] ) ) {
			$where .= empty( $where ) ? 'WHERE ' : 'AND ';
			$where .= $wpdb->prepare( '`retry_of_batch_id` = %d ', intval( $args['retry_of_batch_id'] ) );
		}

		if ( ! empty( $args['search'] ) ) {
			$search = trim( (string) $args['search'] );

			$where .= empty( $where ) ? 'WHERE ' : 'AND ';

			if ( ctype_digit( ltrim( $search, '#' ) ) ) {
				$where .= $wpdb->prepare( '`batch_id` = %d ', intval( ltrim( $search, '#' ) ) );
			} else {
				$where .= $wpdb->prepare( '`payout_method` LIKE %s ', '%' . $wpdb->esc_like( $search ) . '%' );
			}
		}

		$where .= empty( $where ) ? 'WHERE ' : 'AND ';
		$where .= "`{$this->primary_key}` > 0";

		$orderby = array_key_exists( $args['orderby'], $this->get_columns() ) ? $args['orderby'] : $this->primary_key;
		$order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$args['orderby'] = $orderby;
		$args['order']   = $order;

		$callback = '';

		if ( 'ids' === $args['fields'] ) {
			$fields   = $this->primary_key;
			$callback = 'intval';
		} else {
			$fields = $this->parse_fields( $args['fields'] );

			if ( '*' === $fields ) {
				$callback = 'affwp_get_payout_batch';
			}
		}

		$key = ( true === $count )
			? md5( 'affwp_payout_batches_count' . serialize( $args ) )
			: md5( 'affwp_payout_batches_' . serialize( $args ) );

		$last_changed = wp_cache_get( 'last_changed', $this->cache_group );
		if ( ! $last_changed ) {
			$last_changed = microtime();
			wp_cache_set( 'last_changed', $last_changed, $this->cache_group );
		}

		$cache_key = "{$key}:{$last_changed}";
		$results   = wp_cache_get( $cache_key, $this->cache_group );

		if ( false === $results ) {
			$clauses = compact( 'fields', 'join', 'where', 'orderby', 'order', 'count' );
			$results = $this->get_results( $clauses, $args, $callback );
		}

		wp_cache_add( $cache_key, $results, $this->cache_group, HOUR_IN_SECONDS );

		return $results;
	}

	/**
	 * Retrieves the total number of batches matching a query.
	 *
	 * @since 2.33.0
	 *
	 * @param array $args Query arguments.
	 * @return int
	 */
	public function count( $args = array() ) {
		return $this->get_batches( $args, true );
	}

	/**
	 * Recomputes total / processed / failed counts (and rolls up status) from the batch's
	 * constituent payouts, and persists them on the batch row.
	 *
	 * Status roll-up:
	 *   all paid → completed
	 *   all failed → failed
	 *   any failed + any paid → partial
	 *   any processing → processing
	 *
	 * @since 2.33.0
	 *
	 * @param int $batch_id Batch ID to recount.
	 * @return bool True on update success.
	 */
	public function recount( $batch_id ) {
		global $wpdb;

		$batch_id = absint( $batch_id );

		if ( empty( $batch_id ) ) {
			return false;
		}

		// Status before this recount, to detect a transition INTO a terminal state below.
		$prior_status = (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT status FROM {$this->table_name} WHERE {$this->primary_key} = %d",
			$batch_id
		) );

		/*
		 * Aggregated query — previously this loaded every payout in the batch as a
		 * PHP object to compute four counters. On a 50k-affiliate batch that's 50k
		 * object hydrations per recount, and recount fires on every per-affiliate
		 * AS worker. Single GROUP BY keeps this O(distinct status × method) regardless
		 * of batch size.
		 */
		$payouts_tbl = affiliate_wp()->affiliates->payouts->table_name;
		$rows        = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT status, LOWER(payout_method) AS payout_method, COUNT(*) AS cnt, SUM(amount) AS amt
				 FROM {$payouts_tbl}
				 WHERE batch_id = %d AND status IN ('paid','failed','processing')
				 GROUP BY status, LOWER(payout_method)",
				$batch_id
			)
		);

		$total         = 0;
		$processed     = 0;
		$failed        = 0;
		$processing    = 0;
		$amount        = 0.0;
		$method_counts = array();

		foreach ( (array) $rows as $row ) {
			$cnt = (int) $row->cnt;
			$total += $cnt;
			$amount += (float) $row->amt;

			if ( 'paid' === $row->status ) {
				$processed += $cnt;
			} elseif ( 'failed' === $row->status ) {
				$failed += $cnt;
			} else {
				$processing += $cnt;
			}

			$method = (string) $row->payout_method;
			if ( '' !== $method ) {
				$method_counts[ $method ] = ( $method_counts[ $method ] ?? 0 ) + $cnt;
			}
		}

		if ( $processing > 0 ) {
			$status = 'processing';
		} elseif ( $total > 0 && $failed === $total ) {
			$status = 'failed';
		} elseif ( $failed > 0 ) {
			$status = 'partial';
		} else {
			$status = 'completed';
		}

		$fields = array(
			'total_count'     => $total,
			'processed_count' => $processed,
			'failed_count'    => $failed,
			'total_amount'    => $amount,
			'status'          => $status,
		);

		/*
		 * Collapse the 'mixed' placeholder to the concrete method once the batch
		 * is terminal and its children all resolved to the same method. The
		 * batch row is created with payout_method='mixed' up-front because each
		 * affiliate's method is resolved at AS-worker time, not submit time —
		 * and a truly per-affiliate routed batch only earns the 'Mixed' label
		 * if >1 method actually shows up in the children. Scoped to terminal
		 * states so the drawer doesn't flip-flop mid-processing as children land.
		 */
		if ( 'processing' !== $status && 1 === count( $method_counts ) ) {
			$fields['payout_method'] = array_key_first( $method_counts );
		}

		$updated = $this->update(
			$batch_id,
			$fields,
			'',
			'payout_batch'
		);

		/*
		 * Fire once when the batch transitions INTO a terminal state from a
		 * non-terminal one. recount() is the single chokepoint every status
		 * change flows through — the synchronous generation finalize and
		 * asynchronous gateway settlement (webhooks) both call it — so this is
		 * the one place "this batch is done" can be observed for either path.
		 */
		$terminal = array( 'completed', 'partial', 'failed' );

		if ( $updated && in_array( $status, $terminal, true ) && ! in_array( $prior_status, $terminal, true ) ) {
			/**
			 * Fires when a payout batch reaches a terminal state (completed,
			 * partial, or failed) from a non-terminal one.
			 *
			 * @since 2.35.0
			 *
			 * @param int    $batch_id Batch ID.
			 * @param string $status   New terminal status.
			 */
			do_action( 'affwp_payout_batch_finalized', $batch_id, $status );
		}

		return $updated;
	}

	/**
	 * Creates the payout batches table.
	 *
	 * @since 2.33.0
	 */
	public function create_table() {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$sql = "CREATE TABLE {$this->table_name} (
			batch_id bigint(20) NOT NULL AUTO_INCREMENT,
			date_created datetime NOT NULL,
			created_by bigint(20) NOT NULL DEFAULT 0,
			payout_method tinytext NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'processing',
			total_amount mediumtext NOT NULL,
			total_count int(11) NOT NULL DEFAULT 0,
			processed_count int(11) NOT NULL DEFAULT 0,
			failed_count int(11) NOT NULL DEFAULT 0,
			sender_batch_id varchar(191) NOT NULL DEFAULT '',
			provider_batch_id varchar(191) NOT NULL DEFAULT '',
			retry_of_batch_id bigint(20) NOT NULL DEFAULT 0,
			schedule_id bigint(20) NOT NULL DEFAULT 0,
			processing_errors longtext NOT NULL,
			meta longtext NOT NULL,
			PRIMARY KEY  (batch_id),
			KEY created_by (created_by),
			KEY status (status),
			KEY retry_of_batch_id (retry_of_batch_id),
			KEY schedule_id (schedule_id),
			KEY sender_batch_id (sender_batch_id)
			) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;";

		dbDelta( $sql );

		update_option( $this->table_name . '_db_version', $this->version );
	}
}
