<?php
/**
 * Orders: create, find, change status, history.
 *
 * @package PAYGRAB
 */

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- PAYGRAB's own order tables: the names come from Paygrab_Install::tables() (prefix + fixed names), never from input, and orders are always read fresh.

/**
 * Order repository and status machine.
 */
class Paygrab_Orders {

	/**
	 * Allowed status changes.
	 *
	 * @return array<string,string[]>
	 */
	public static function transitions() {
		return apply_filters(
			'paygrab_order_transitions',
			array(
				'pending'   => array( 'paid', 'cancelled', 'failed' ),
				'paid'      => array( 'refunded' ),
				'failed'    => array(),
				'cancelled' => array(),
				'refunded'  => array(),
			)
		);
	}

	/**
	 * Create an order for one product.
	 *
	 * @param int           $user_id    Buyer.
	 * @param int           $product_id Product.
	 * @param Paygrab_Gateway $gateway    Payment gateway.
	 * @param string        $method     Payment method within the gateway.
	 * @param array         $args       Extra values: source_id (post the buyer started from).
	 * @return Paygrab_Order|WP_Error
	 */
	public static function create( $user_id, $product_id, Paygrab_Gateway $gateway, $method = '', $args = array() ) {
		global $wpdb;
		$t    = Paygrab_Install::tables();
		$user = get_userdata( $user_id );
		if ( ! $user ) {
			return new WP_Error( 'paygrab_no_user', __( 'Please log in again.', 'paygrab' ) );
		}
		$data  = Paygrab_Product::data( $product_id );
		$price = (int) $data['price'];
		$line  = Paygrab_Product::line_price( $product_id );
		$now   = paygrab_now();

		$row = array(
			'order_number'   => '',
			'transfer_code'  => '',
			'user_id'        => (int) $user_id,
			'status'         => 'pending',
			'gateway'        => $gateway->id(),
			'payment_method' => $method ? $method : $gateway->id(),
			'subtotal'       => $price,
			'discount'       => max( 0, $price - $line ),
			'total'          => $line,
			'tax_total'      => paygrab_tax_included( $line, $data['tax_rate'] ),
			'billing_name'   => $user->display_name,
			'billing_email'  => $user->user_email,
			'source_id'      => (int) ( $args['source_id'] ?? 0 ),
			'created_at'     => $now,
			'updated_at'     => $now,
		);
		/**
		 * Filters a new order row before it is saved.
		 *
		 * @param array $row        Row.
		 * @param int   $product_id Product.
		 */
		$row = apply_filters( 'paygrab_new_order_row', $row, $product_id );

		if ( false === $wpdb->insert( $t['orders'], $row ) ) {
			return new WP_Error( 'paygrab_db', __( 'The order could not be saved. Please try again.', 'paygrab' ) );
		}
		$id = (int) $wpdb->insert_id;

		$wpdb->insert(
			$t['items'],
			array(
				'order_id'   => $id,
				'product_id' => (int) $product_id,
				'name'       => get_the_title( $product_id ),
				'unit_price' => $price,
				'quantity'   => 1,
				'tax_rate'   => (int) $data['tax_rate'],
			)
		);

		$wpdb->update(
			$t['orders'],
			array(
				'order_number'  => 'PG-' . wp_date( 'Ymd' ) . '-' . str_pad( (string) $id, 4, '0', STR_PAD_LEFT ),
				'transfer_code' => self::new_transfer_code(),
			),
			array( 'id' => $id )
		);

		$order = self::get( $id );
		self::add_event( $id, 'created', __( 'Order received (awaiting payment).', 'paygrab' ), $user_id );
		/**
		 * Fires after an order was created (still awaiting payment).
		 *
		 * @param Paygrab_Order $order Order.
		 */
		do_action( 'paygrab_order_created', $order );
		return $order;
	}

	/**
	 * A 6-digit code not used by another order awaiting payment.
	 *
	 * @return string
	 */
	private static function new_transfer_code() {
		global $wpdb;
		$t = Paygrab_Install::tables();
		for ( $i = 0; $i < 20; $i++ ) {
			$code  = (string) wp_rand( 100000, 999999 );
			$taken = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$t['orders']} WHERE transfer_code = %s AND status = 'pending' LIMIT 1", $code ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( ! $taken ) {
				return $code;
			}
		}
		return (string) wp_rand( 100000, 999999 );
	}

	/**
	 * Update some columns.
	 *
	 * @param int   $id     Order ID.
	 * @param array $fields Column => value.
	 * @return void
	 */
	public static function update( $id, array $fields ) {
		global $wpdb;
		$t                    = Paygrab_Install::tables();
		$fields['updated_at'] = paygrab_now();
		$wpdb->update( $t['orders'], $fields, array( 'id' => (int) $id ) );
	}

	/**
	 * Find an order by ID.
	 *
	 * @param int $id Order ID.
	 * @return Paygrab_Order|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$t   = Paygrab_Install::tables();
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t['orders']} WHERE id = %d", (int) $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $row ? new Paygrab_Order( $row ) : null;
	}

	/**
	 * Find an order by its number.
	 *
	 * @param string $number Order number.
	 * @return Paygrab_Order|null
	 */
	public static function get_by_number( $number ) {
		global $wpdb;
		$t   = Paygrab_Install::tables();
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t['orders']} WHERE order_number = %s", (string) $number ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $row ? new Paygrab_Order( $row ) : null;
	}

	/**
	 * Find an order by the payment provider's reference.
	 *
	 * @param string $ref Reference.
	 * @return Paygrab_Order|null
	 */
	public static function get_by_ref( $ref ) {
		global $wpdb;
		if ( '' === (string) $ref ) {
			return null;
		}
		$t   = Paygrab_Install::tables();
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t['orders']} WHERE gateway_ref = %s", (string) $ref ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $row ? new Paygrab_Order( $row ) : null;
	}

	/**
	 * The buyer's order for a product that still waits for payment.
	 *
	 * @param int $user_id    Buyer.
	 * @param int $product_id Product.
	 * @return Paygrab_Order|null
	 */
	public static function pending_for( $user_id, $product_id ) {
		global $wpdb;
		$t  = Paygrab_Install::tables();
		$id = $wpdb->get_var( $wpdb->prepare( "SELECT o.id FROM {$t['orders']} o INNER JOIN {$t['items']} i ON i.order_id = o.id WHERE o.user_id = %d AND i.product_id = %d AND o.status = 'pending' ORDER BY o.id DESC LIMIT 1", (int) $user_id, (int) $product_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $id ? self::get( $id ) : null;
	}

	/**
	 * A buyer's orders, newest first.
	 *
	 * @param int $user_id Buyer.
	 * @return Paygrab_Order[]
	 */
	public static function for_user( $user_id ) {
		global $wpdb;
		$t    = Paygrab_Install::tables();
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$t['orders']} WHERE user_id = %d ORDER BY id DESC LIMIT 200", (int) $user_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return array_map( static fn( $r ) => new Paygrab_Order( $r ), $rows );
	}

	/**
	 * Orders for the admin list.
	 *
	 * @param array $args status, search, paged, per_page.
	 * @return array{items:Paygrab_Order[],total:int}
	 */
	public static function query( array $args ) {
		global $wpdb;
		$t     = Paygrab_Install::tables();
		$where = array( '1=1' );
		$vals  = array();
		if ( ! empty( $args['status'] ) && isset( paygrab_statuses()[ $args['status'] ] ) ) {
			$where[] = 'o.status = %s';
			$vals[]  = $args['status'];
		}
		if ( ! empty( $args['user_id'] ) ) {
			$where[] = 'o.user_id = %d';
			$vals[]  = (int) $args['user_id'];
		}
		if ( ! empty( $args['search'] ) ) {
			$like    = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
			$where[] = '(o.order_number LIKE %s OR o.billing_name LIKE %s OR o.billing_email LIKE %s OR o.transfer_code LIKE %s OR EXISTS (SELECT 1 FROM ' . $t['items'] . ' i WHERE i.order_id = o.id AND i.name LIKE %s))';
			array_push( $vals, $like, $like, $like, $like, $like );
		}
		$sql_where = implode( ' AND ', $where );
		$per_page  = max( 1, (int) ( $args['per_page'] ?? 20 ) );
		$offset    = ( max( 1, (int) ( $args['paged'] ?? 1 ) ) - 1 ) * $per_page;
		// Orders awaiting payment come first, the closest deadline on top.
		$order_by = "CASE WHEN o.status = 'pending' THEN 0 ELSE 1 END ASC, CASE WHEN o.status = 'pending' THEN o.pay_deadline END ASC, o.id DESC";

		$count_sql = "SELECT COUNT(*) FROM {$t['orders']} o WHERE {$sql_where}";
		$list_sql  = "SELECT o.* FROM {$t['orders']} o WHERE {$sql_where} ORDER BY {$order_by} LIMIT %d OFFSET %d";
		$total     = (int) ( $vals ? $wpdb->get_var( $wpdb->prepare( $count_sql, $vals ) ) : $wpdb->get_var( $count_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows      = $wpdb->get_results( $wpdb->prepare( $list_sql, array_merge( $vals, array( $per_page, $offset ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return array(
			'items' => array_map( static fn( $r ) => new Paygrab_Order( $r ), $rows ),
			'total' => $total,
		);
	}

	/**
	 * Number of orders awaiting payment for each payment method.
	 *
	 * @return array<string,int> gateway id => count
	 */
	public static function count_pending_by_gateway() {
		global $wpdb;
		$t   = Paygrab_Install::tables();
		$out = array();
		foreach ( $wpdb->get_results( "SELECT gateway, COUNT(*) AS n FROM {$t['orders']} WHERE status = 'pending' GROUP BY gateway" ) as $row ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$out[ (string) $row->gateway ] = (int) $row->n;
		}
		return $out;
	}

	/**
	 * Number of orders in each status.
	 *
	 * @return array<string,int>
	 */
	public static function count_by_status() {
		global $wpdb;
		$t    = Paygrab_Install::tables();
		$rows = $wpdb->get_results( "SELECT status, COUNT(*) AS n FROM {$t['orders']} GROUP BY status" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$out  = array_fill_keys( array_keys( paygrab_statuses() ), 0 );
		foreach ( $rows as $row ) {
			$out[ $row->status ] = (int) $row->n;
		}
		return $out;
	}

	/**
	 * Change an order's status. Only allowed changes go through, and only once.
	 *
	 * @param Paygrab_Order $order Order.
	 * @param string      $to    New status.
	 * @param array       $args  actor (user ID), note (history text), notify (send mail, default true).
	 * @return Paygrab_Order|WP_Error The updated order.
	 */
	public static function set_status( Paygrab_Order $order, $to, array $args = array() ) {
		global $wpdb;
		$from    = $order->status;
		$allowed = self::transitions()[ $from ] ?? array();
		if ( ! in_array( $to, $allowed, true ) ) {
			return new WP_Error(
				'paygrab_status',
				/* translators: 1: current status, 2: requested status */
				sprintf( __( 'This order cannot be changed from “%1$s” to “%2$s”.', 'paygrab' ), paygrab_status_label( $from ), paygrab_status_label( $to ) )
			);
		}
		$t      = Paygrab_Install::tables();
		$now    = paygrab_now();
		$fields = array(
			'status'     => $to,
			'updated_at' => $now,
		);
		if ( 'paid' === $to ) {
			$fields['paid_at'] = $now;
		} elseif ( 'cancelled' === $to || 'failed' === $to ) {
			$fields['cancelled_at'] = $now;
		} elseif ( 'refunded' === $to ) {
			$fields['refunded_at'] = $now;
		}
		// The status in WHERE makes this safe against two requests at once.
		$changed = $wpdb->update( $t['orders'], $fields, array( 'id' => $order->id, 'status' => $from ) );
		if ( ! $changed ) {
			return new WP_Error( 'paygrab_status_race', __( 'This order was already updated. Reload the page.', 'paygrab' ) );
		}
		$order = self::get( $order->id );

		if ( 'paid' === $to ) {
			Paygrab_Downloads::grant( $order );
		} elseif ( in_array( $to, array( 'refunded', 'cancelled', 'failed' ), true ) ) {
			Paygrab_Downloads::revoke( $order );
		}

		$args = wp_parse_args(
			$args,
			array(
				'actor'  => get_current_user_id(),
				'note'   => '',
				'notify' => true,
			)
		);
		$messages = array(
			'paid'      => __( 'Payment confirmed. Downloads are now available.', 'paygrab' ),
			'cancelled' => __( 'Order cancelled.', 'paygrab' ),
			'failed'    => __( 'Payment failed.', 'paygrab' ),
			'refunded'  => __( 'Marked as refunded. Downloads were stopped.', 'paygrab' ),
		);
		self::add_event( $order->id, 'status_' . $to, trim( ( $messages[ $to ] ?? '' ) . ' ' . $args['note'] ), (int) $args['actor'] );

		/**
		 * Fires after an order's status changed.
		 *
		 * @param Paygrab_Order $order Order (already updated).
		 * @param string      $from  Previous status.
		 * @param string      $to    New status.
		 * @param array       $args  actor, note, notify, by (who changed it: admin, buyer, system).
		 */
		do_action( 'paygrab_order_status_changed', $order, $from, $to, $args );
		return $order;
	}

	/**
	 * Add a line to an order's history.
	 *
	 * @param int    $order_id Order.
	 * @param string $type     Event type.
	 * @param string $message  Text shown in the admin.
	 * @param int    $actor_id User who did it (0 = system).
	 * @return void
	 */
	public static function add_event( $order_id, $type, $message, $actor_id = 0 ) {
		global $wpdb;
		$t = Paygrab_Install::tables();
		$wpdb->insert(
			$t['events'],
			array(
				'order_id'   => (int) $order_id,
				'created_at' => paygrab_now(),
				'actor_id'   => (int) $actor_id,
				'type'       => sanitize_key( $type ),
				'message'    => wp_strip_all_tags( (string) $message ),
			)
		);
	}

	/**
	 * An order's history, oldest first.
	 *
	 * @param int $order_id Order.
	 * @return array<int,object>
	 */
	public static function events( $order_id ) {
		global $wpdb;
		$t = Paygrab_Install::tables();
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$t['events']} WHERE order_id = %d ORDER BY id ASC", (int) $order_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Paid and pending counts per product.
	 *
	 * @param int[] $product_ids Products.
	 * @return array<int,array{paid:int,pending:int}>
	 */
	public static function product_counts( array $product_ids ) {
		global $wpdb;
		$product_ids = array_filter( array_map( 'intval', $product_ids ) );
		$out         = array();
		if ( ! $product_ids ) {
			return $out;
		}
		$t    = Paygrab_Install::tables();
		$in   = implode( ',', $product_ids );
		$rows = $wpdb->get_results( "SELECT i.product_id, o.status, COUNT(*) AS n FROM {$t['items']} i INNER JOIN {$t['orders']} o ON o.id = i.order_id WHERE i.product_id IN ({$in}) AND o.status IN ('paid','pending') GROUP BY i.product_id, o.status" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		foreach ( $rows as $row ) {
			$out[ (int) $row->product_id ][ $row->status ] = (int) $row->n;
		}
		return $out;
	}

	/**
	 * Sales total and count of paid orders since a date.
	 *
	 * @param string $since_gmt UTC datetime.
	 * @return array{total:int,count:int}
	 */
	public static function sales_since( $since_gmt ) {
		global $wpdb;
		$t   = Paygrab_Install::tables();
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE(SUM(total),0) AS total, COUNT(*) AS n FROM {$t['orders']} WHERE status = 'paid' AND paid_at >= %s", $since_gmt ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return array(
			'total' => (int) ( $row->total ?? 0 ),
			'count' => (int) ( $row->n ?? 0 ),
		);
	}

	/**
	 * Figures for a period: paid total (by payment date), orders and cancellations (by order date).
	 *
	 * @param string $from_gmt UTC datetime (inclusive).
	 * @param string $to_gmt   UTC datetime (exclusive).
	 * @return array{total:int,paid:int,orders:int,cancelled:int}
	 */
	public static function stats_between( $from_gmt, $to_gmt ) {
		global $wpdb;
		$t    = Paygrab_Install::tables();
		$paid = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE(SUM(total),0) AS total, COUNT(*) AS n FROM {$t['orders']} WHERE status = 'paid' AND paid_at >= %s AND paid_at < %s", $from_gmt, $to_gmt ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$made = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS n, SUM(status = 'cancelled') AS c FROM {$t['orders']} WHERE created_at >= %s AND created_at < %s", $from_gmt, $to_gmt ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return array(
			'total'     => (int) ( $paid->total ?? 0 ),
			'paid'      => (int) ( $paid->n ?? 0 ),
			'orders'    => (int) ( $made->n ?? 0 ),
			'cancelled' => (int) ( $made->c ?? 0 ),
		);
	}

	/**
	 * The newest orders.
	 *
	 * @param int $limit How many.
	 * @return Paygrab_Order[]
	 */
	public static function latest( $limit = 5 ) {
		global $wpdb;
		$t    = Paygrab_Install::tables();
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$t['orders']} ORDER BY id DESC LIMIT %d", max( 1, (int) $limit ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return array_map( static fn( $r ) => new Paygrab_Order( $r ), $rows );
	}
}