<?php
/**
 * Payout data access.
 *
 * @package PPL_AFW
 */

namespace PPL_AFW\Payouts;

use PPL_AFW\Core\Repository;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This plugin stores affiliate data in its own custom tables, which the WordPress data APIs cannot read or write, so direct queries are required. Every query is assembled from string literals and executed through $wpdb->prepare(), with %i for table names and %s/%d/%f for values, so nothing is ever interpolated into SQL.

/**
 * Repository for the payouts table.
 */
class Payout_Repository extends Repository {

	/**
	 * Table key.
	 *
	 * @var string
	 */
	protected $table_key = 'payouts';

	/**
	 * Payout history for an affiliate.
	 *
	 * @since 1.0.0
	 * @param int $affiliate_id Affiliate id.
	 * @param int $page         Page number.
	 * @param int $per_page     Rows per page.
	 * @return object[]
	 */
	public function for_affiliate( $affiliate_id, $page = 1, $per_page = 20 ) {
		global $wpdb;
		$offset = ( max( 1, (int) $page ) - 1 ) * (int) $per_page;
		return $wpdb->get_results( $wpdb->prepare(
				"SELECT * FROM %i WHERE affiliate_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d",
				$this->table(),
				absint( $affiliate_id
			), (int) $per_page, $offset ) );
	}

	/**
	 * Paginated payouts with filters for the admin list.
	 *
	 * @since 1.0.0
	 * @param array $args affiliate_id, status, method, page, per_page.
	 * @return array{rows: object[], total: int}
	 */
	public function query( array $args = array() ) {
		global $wpdb;
		$args = wp_parse_args(
			$args,
			array(
				'affiliate_id' => 0,
				'status'       => '',
				'method'       => '',
				'page'         => 1,
				'per_page'     => 20,
			)
		);

		$where  = 'WHERE 1=1';
		$params = array();
		if ( $args['affiliate_id'] ) {
			$where   .= ' AND affiliate_id = %d';
			$params[] = absint( $args['affiliate_id'] );
		}
		if ( '' !== $args['status'] ) {
			$where   .= ' AND status = %s';
			$params[] = $args['status'];
		}
		if ( '' !== $args['method'] ) {
			$where   .= ' AND method = %s';
			$params[] = $args['method'];
		}

		$offset = ( max( 1, (int) $args['page'] ) - 1 ) * (int) $args['per_page'];
		$p      = array_merge( $params, array( (int) $args['per_page'], $offset ) );

		$list  = 'SELECT * FROM %i ' . $where . ' ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d';
		$count = 'SELECT COUNT(*) FROM %i ' . $where;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is concatenated from the string literals above; the table name and every value are bound by prepare() below.
		$rows = $wpdb->get_results( $wpdb->prepare( $list, $this->table(), ...$p ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Same query fragments, bound the same way.
		$total = (int) $wpdb->get_var( $wpdb->prepare( $count, $this->table(), ...$params ) );

		return array(
			'rows'  => $rows,
			'total' => $total,
		);
	}

	/**
	 * Generates the next sequential invoice number.
	 *
	 * @since 1.0.0
	 * @return string
	 */
	public function next_invoice_number() {
		global $wpdb;

		// One sequence for the whole store, so two payout runs at the same
		// time (the schedule and an admin, or two affiliates) must never read
		// the same value. A named lock serialises the read and the write, and
		// the value is read from the database rather than a cache.
		$lock   = $wpdb->prefix . 'ppl_afw_invoice_sequence';
		$locked = 1 === (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 10)', $lock ) );
		try {
			$current = $wpdb->get_var( $wpdb->prepare( 'SELECT option_value FROM %i WHERE option_name = %s', $wpdb->options, 'ppl_afw_invoice_sequence' ) );
			$seq     = (int) $current + 1;
			if ( null === $current ) {
				add_option( 'ppl_afw_invoice_sequence', $seq, '', false );
			} else {
				update_option( 'ppl_afw_invoice_sequence', $seq, false );
			}
		} finally {
			if ( $locked ) {
				$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) );
			}
		}

		/**
		 * Filters the invoice number prefix.
		 *
		 * @since 1.3.0
		 * @param string $prefix Prefix, AFF by default. Invoices issued before 1.3.0 keep their PPL prefix.
		 */
		$prefix = (string) apply_filters( 'ppl_afw_invoice_prefix', 'AFF' );

		return sprintf( '%s-%s-%06d', $prefix, gmdate( 'Y' ), $seq );
	}
}