<?php // phpcs:ignore WordPress.Files.FileName.InvalidClassFileName
/**
 * Payout Schedules Database Abstraction Layer
 *
 * @package     AffiliateWP
 * @subpackage  Database
 * @copyright   Copyright (c) 2026, Awesome Motive, Inc
 * @license     http://opensource.org/licenses/gpl-2.0.php GNU Public License
 * @since       2.35.0
 */

#[AllowDynamicProperties]

/**
 * Core class that implements a database layer for payout schedules.
 *
 * @since 2.35.0
 *
 * @see \Affiliate_WP_DB
 */
class Affiliate_WP_Payout_Schedules_DB extends Affiliate_WP_DB {

	/**
	 * Cache group for queries.
	 *
	 * @since 2.35.0
	 * @var   string
	 */
	public $cache_group = 'payout_schedules';

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

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

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

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

		$this->primary_key = 'schedule_id';
		$this->version     = '1.2';
	}

	/**
	 * Retrieves a payout schedule object.
	 *
	 * @since 2.35.0
	 *
	 * @param int|object $schedule Schedule ID or object.
	 * @return AffWP\Affiliate\PayoutSchedule|false Schedule object, false otherwise.
	 */
	public function get_object( $schedule ) {
		return $this->get_core_object( $schedule, $this->query_object_type );
	}

	/**
	 * Retrieves table columns and data types.
	 *
	 * A schedule no longer stores a payout method: at run time each affiliate is
	 * routed through their own configured method ({@see affwp_get_affiliate_payout_method()}),
	 * so a single schedule produces one (possibly multi-method) batch.
	 *
	 * @since 2.35.0
	 *
	 * @return array Column name => format pairs.
	 */
	public function get_columns() {
		return array(
			'schedule_id'            => '%d',
			'name'                   => '%s',
			'frequency'              => '%s',
			'frequency_day'          => '%d',
			'affiliate_group_ids'    => '%s',
			'affiliate_ids'          => '%s',
			'minimum_amount'         => '%s',
			'bypass_holding_period'  => '%d',
			'status'                 => '%s',
			'last_run'               => '%s',
			'next_run'               => '%s',
			'created_by'             => '%d',
			'date'                   => '%s',
		);
	}

	/**
	 * Retrieves default column values.
	 *
	 * @since 2.35.0
	 *
	 * @return array Default column values.
	 */
	public function get_column_defaults() {
		return array(
			'name'                  => '',
			'frequency'             => 'monthly',
			'frequency_day'         => 1,
			'affiliate_group_ids'   => null,
			'affiliate_ids'         => null,
			'minimum_amount'        => 50,
			'bypass_holding_period' => 0,
			'status'                => 'active',
			'last_run'              => '',
			'next_run'              => '',
			'created_by'            => 0,
			'date'                  => gmdate( 'Y-m-d H:i:s' ),
		);
	}

	/**
	 * Adds a new payout schedule.
	 *
	 * @since 2.35.0
	 *
	 * @param array $args {
	 *     Arguments for adding a new schedule.
	 *
	 *     @type string $name                   Schedule name.
	 *     @type string $frequency              Frequency: 'weekly' or 'monthly'.
	 *     @type int    $frequency_day           Day of week (1-7) or month (1-31, 99=last).
	 *     @type string $affiliate_group_ids    JSON array of group IDs to include. Empty/null for all.
	 *     @type string $affiliate_ids          JSON array of affiliate IDs to include. Empty/null for all.
	 *     @type float  $minimum_amount         Minimum payout threshold.
	 *     @type int    $bypass_holding_period  Whether to bypass holding period.
	 *     @type string $status                 Schedule status: 'active', 'paused'.
	 *     @type string $next_run               Next scheduled run datetime.
	 * }
	 * @return int|false Schedule ID if successfully added, otherwise false.
	 */
	public function add( $args = array() ) {

		$args = wp_parse_args( $args, array(
			'name'                  => '',
			'frequency'             => 'monthly',
			'frequency_day'         => 1,
			'affiliate_group_ids'   => null,
			'affiliate_ids'         => null,
			'minimum_amount'        => 0, // 0 = no minimum; an omitted filter shouldn't silently skip affiliates.
			'bypass_holding_period' => 0,
			'status'                => 'active',
			'next_run'              => '',
			'created_by'            => get_current_user_id(),
		) );

		if ( empty( $args['name'] ) ) {
			return false;
		}

		$valid_frequencies = array( 'weekly', 'monthly' );
		if ( ! in_array( $args['frequency'], $valid_frequencies, true ) ) {
			$args['frequency'] = 'monthly';
		}

		$valid_statuses = array( 'active', 'paused' );
		if ( ! in_array( $args['status'], $valid_statuses, true ) ) {
			$args['status'] = 'active';
		}

		$args['name']           = mb_substr( sanitize_text_field( $args['name'] ), 0, 45 ); // Same cap as schedule-actions.php.
		$args['minimum_amount'] = affwp_sanitize_amount( $args['minimum_amount'] );
		$args['frequency_day']  = absint( $args['frequency_day'] );

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

		if ( $add ) {
			/**
			 * Fires immediately after a payout schedule has been inserted.
			 *
			 * @since 2.35.0
			 *
			 * @param int $add New schedule ID.
			 */
			do_action( 'affwp_insert_payout_schedule', $add );

			return $add;
		}

		return false;
	}

	/**
	 * Retrieves payout schedules from the database.
	 *
	 * @since 2.35.0
	 *
	 * @param array $args {
	 *     Optional. Arguments for querying schedules.
	 *
	 *     @type int       $number              Number of schedules to query. Default 20.
	 *     @type int       $offset              Offset for the query. Default 0.
	 *     @type int|array $schedule_id         Schedule ID or array of IDs.
	 *     @type string    $status              Filter by status.
	 *     @type string    $order               Order direction. Default 'DESC'.
	 *     @type string    $orderby             Column to order by. Default 'schedule_id'.
	 *     @type string    $fields              Fields to return.
	 * }
	 * @param bool  $count Whether to return only the count.
	 * @return array|int Array of schedule objects or count.
	 */
	public function get_schedules( $args = array(), $count = false ) {
		global $wpdb;

		$defaults = array(
			'number'             => 20,
			'offset'             => 0,
			'schedule_id'        => 0,
			'status'             => '',
			'order'              => 'DESC',
			'orderby'            => 'schedule_id',
			'fields'             => '',
			'search'             => false,
		);

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

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

		$where = $join = '';

		// Specific schedules.
		if ( ! empty( $args['schedule_id'] ) ) {

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

			if ( is_array( $args['schedule_id'] ) ) {
				$schedule_ids = implode( ',', array_map( 'intval', $args['schedule_id'] ) );
			} else {
				$schedule_ids = intval( $args['schedule_id'] );
			}

			$schedule_ids = esc_sql( $schedule_ids );
			$where .= "`schedule_id` IN( {$schedule_ids} ) ";
		}

		// Status. Validate against the whitelist BEFORE appending WHERE/AND — an
		// out-of-whitelist status would otherwise leave a dangling clause (malformed SQL).
		if ( ! empty( $args['status'] ) ) {

			$valid_statuses = array( 'active', 'paused' );

			if ( is_array( $args['status'] ) ) {
				$statuses = array_intersect( $args['status'], $valid_statuses );
				if ( ! empty( $statuses ) ) {
					$where .= empty( $where ) ? "WHERE " : "AND ";
					$where .= "`status` IN('" . implode( "','", array_map( 'esc_sql', $statuses ) ) . "') ";
				}
			} elseif ( in_array( $args['status'], $valid_statuses, true ) ) {
				$where .= empty( $where ) ? "WHERE " : "AND ";
				$where .= "`status` = '" . esc_sql( $args['status'] ) . "' ";
			}
		}

		// Search.
		if ( ! empty( $args['search'] ) ) {
			$where .= empty( $where ) ? "WHERE " : "AND ";
			$search = esc_sql( $args['search'] );
			$where .= "`name` LIKE '%%" . $search . "%%' ";
		}

		// Select valid schedules only.
		$where .= empty( $where ) ? "WHERE " : "AND ";
		$where .= "`$this->primary_key` > 0";

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

		if ( 'DESC' === strtoupper( $args['order'] ) ) {
			$order = 'DESC';
		} else {
			$order = 'ASC';
		}

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

		// Fields.
		$callback = '';

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

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

		$key = ( true === $count ) ? md5( 'affwp_payout_schedules_count' . serialize( $args ) ) : md5( 'affwp_payout_schedules_' . 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 number of results found for a given query.
	 *
	 * @since 2.35.0
	 *
	 * @param array $args Arguments to pass to get_schedules().
	 * @return int Number of schedules.
	 */
	public function count( $args = array() ) {
		return $this->get_schedules( $args, true );
	}

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

		$sql = "CREATE TABLE " . $this->table_name . " (
			schedule_id bigint(20) NOT NULL AUTO_INCREMENT,
			name varchar(255) NOT NULL,
			frequency varchar(20) NOT NULL DEFAULT 'monthly',
			frequency_day int(11) NOT NULL DEFAULT 1,
			affiliate_group_ids longtext DEFAULT NULL,
			affiliate_ids longtext DEFAULT NULL,
			minimum_amount mediumtext NOT NULL,
			bypass_holding_period tinyint(1) NOT NULL DEFAULT 0,
			status varchar(20) NOT NULL DEFAULT 'active',
			last_run datetime DEFAULT NULL,
			next_run datetime DEFAULT NULL,
			created_by bigint(20) NOT NULL DEFAULT 0,
			date datetime NOT NULL,
			PRIMARY KEY  (schedule_id),
			KEY status (status),
			KEY next_run (next_run)
			) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;";

		dbDelta( $sql );

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