<?php
/**
 * Return requests and their items (custom tables).
 *
 * Requests live in their own tables rather than as posts or order meta: they
 * are queried by status, customer and order on every screen, and an order can
 * have several requests over its life. Orders are referenced by ID, so HPOS
 * and legacy order storage both work.
 *
 * @package ReturnKit
 */

namespace ReturnKit\Store;

use ReturnKit\Domain\Request;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- custom tables, cached per request where it matters.

/**
 * Request storage.
 */
class Requests {

	/**
	 * Request statuses that still hold (or held) items: anything not
	 * rejected or cancelled counts against an item's returnable quantity.
	 */
	const HOLDING = array( 'pending', 'approved', 'received', 'completed' );

	/**
	 * Table names.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'rtkt_requests';
	}

	/**
	 * Items table.
	 *
	 * @return string
	 */
	public static function items_table() {
		global $wpdb;
		return $wpdb->prefix . 'rtkt_request_items';
	}

	/**
	 * CREATE TABLE statements for dbDelta.
	 *
	 * @param string $collate Charset/collation clause.
	 * @return string[]
	 */
	public static function schema( $collate ) {
		$requests = self::table();
		$items    = self::items_table();
		return array(
			"CREATE TABLE {$requests} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				order_id bigint(20) unsigned NOT NULL DEFAULT 0,
				customer_id bigint(20) unsigned NOT NULL DEFAULT 0,
				email varchar(190) NOT NULL DEFAULT '',
				name varchar(190) NOT NULL DEFAULT '',
				type varchar(20) NOT NULL DEFAULT 'return',
				status varchar(20) NOT NULL DEFAULT 'pending',
				resolution varchar(20) NOT NULL DEFAULT 'refund',
				note text NULL,
				source varchar(20) NOT NULL DEFAULT 'account',
				currency char(3) NOT NULL DEFAULT '',
				total decimal(19,4) NOT NULL DEFAULT 0,
				refunded decimal(19,4) NOT NULL DEFAULT 0,
				credited decimal(19,4) NOT NULL DEFAULT 0,
				exchange_order_id bigint(20) unsigned NOT NULL DEFAULT 0,
				flags varchar(255) NOT NULL DEFAULT '',
				ip varchar(64) NOT NULL DEFAULT '',
				meta longtext NULL,
				created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				PRIMARY KEY  (id),
				KEY order_id (order_id),
				KEY customer_id (customer_id),
				KEY email (email),
				KEY status (status),
				KEY created_at (created_at)
			) {$collate};",
			"CREATE TABLE {$items} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				request_id bigint(20) unsigned NOT NULL,
				order_item_id bigint(20) unsigned NOT NULL DEFAULT 0,
				product_id bigint(20) unsigned NOT NULL DEFAULT 0,
				variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
				name varchar(255) NOT NULL DEFAULT '',
				qty int(11) NOT NULL DEFAULT 0,
				unit_value decimal(19,4) NOT NULL DEFAULT 0,
				reason varchar(100) NOT NULL DEFAULT '',
				exchange_product_id bigint(20) unsigned NOT NULL DEFAULT 0,
				exchange_name varchar(255) NOT NULL DEFAULT '',
				exchange_price decimal(19,4) NOT NULL DEFAULT 0,
				received_qty int(11) NOT NULL DEFAULT 0,
				restocked_qty int(11) NOT NULL DEFAULT 0,
				refunded_qty int(11) NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY request_id (request_id),
				KEY order_item_id (order_item_id),
				KEY product_id (product_id)
			) {$collate};",
		);
	}

	/**
	 * Insert a request with its items.
	 *
	 * @param array $data  Request columns.
	 * @param array $items Item rows (order_item_id, product_id, variation_id, name, qty, unit_value, reason, exchange_*).
	 * @return int Request ID (0 on failure).
	 */
	public static function insert( array $data, array $items ) {
		global $wpdb;
		$now  = current_time( 'mysql', true );
		$meta = isset( $data['meta'] ) && is_array( $data['meta'] ) ? $data['meta'] : array();
		$row  = array(
			'order_id'          => absint( $data['order_id'] ?? 0 ),
			'customer_id'       => absint( $data['customer_id'] ?? 0 ),
			'email'             => substr( sanitize_email( $data['email'] ?? '' ), 0, 190 ),
			'name'              => substr( sanitize_text_field( $data['name'] ?? '' ), 0, 190 ),
			'type'              => sanitize_key( $data['type'] ?? 'return' ),
			'status'            => sanitize_key( $data['status'] ?? 'pending' ),
			'resolution'        => sanitize_key( $data['resolution'] ?? 'refund' ),
			'note'              => sanitize_textarea_field( $data['note'] ?? '' ),
			'source'            => sanitize_key( $data['source'] ?? 'account' ),
			'currency'          => substr( sanitize_text_field( $data['currency'] ?? '' ), 0, 3 ),
			'total'             => (float) ( $data['total'] ?? 0 ),
			'flags'             => substr( sanitize_text_field( $data['flags'] ?? '' ), 0, 255 ),
			'ip'                => substr( sanitize_text_field( $data['ip'] ?? '' ), 0, 64 ),
			'meta'              => wp_json_encode( $meta ),
			'created_at'        => $now,
			'updated_at'        => $now,
		);
		self::flush();
		if ( ! $wpdb->insert( self::table(), $row ) ) {
			return 0;
		}
		$id = (int) $wpdb->insert_id;
		foreach ( $items as $item ) {
			$wpdb->insert(
				self::items_table(),
				array(
					'request_id'          => $id,
					'order_item_id'       => absint( $item['order_item_id'] ?? 0 ),
					'product_id'          => absint( $item['product_id'] ?? 0 ),
					'variation_id'        => absint( $item['variation_id'] ?? 0 ),
					'name'                => substr( wp_strip_all_tags( (string) ( $item['name'] ?? '' ) ), 0, 255 ),
					'qty'                 => max( 0, (int) ( $item['qty'] ?? 0 ) ),
					'unit_value'          => (float) ( $item['unit_value'] ?? 0 ),
					'reason'              => substr( sanitize_text_field( $item['reason'] ?? '' ), 0, 100 ),
					'exchange_product_id' => absint( $item['exchange_product_id'] ?? 0 ),
					'exchange_name'       => substr( wp_strip_all_tags( (string) ( $item['exchange_name'] ?? '' ) ), 0, 255 ),
					'exchange_price'      => (float) ( $item['exchange_price'] ?? 0 ),
				)
			);
		}
		return $id;
	}

	/**
	 * One request.
	 *
	 * @param int $id Request ID.
	 * @return Request|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$id = absint( $id );
		if ( ! $id ) {
			return null;
		}
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), $id ), ARRAY_A );
		return $row ? new Request( $row ) : null;
	}

	/**
	 * Items of a request.
	 *
	 * @param int $id Request ID.
	 * @return array[]
	 */
	public static function items( $id ) {
		global $wpdb;
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE request_id = %d ORDER BY id ASC', self::items_table(), absint( $id ) ), ARRAY_A );
		return $rows ? $rows : array();
	}

	/**
	 * Update request columns (updated_at is set automatically).
	 *
	 * @param int   $id     Request ID.
	 * @param array $fields Columns.
	 * @return bool
	 */
	public static function update( $id, array $fields ) {
		global $wpdb;
		$allowed = array( 'status', 'resolution', 'refunded', 'credited', 'exchange_order_id', 'flags', 'meta', 'email', 'name', 'note', 'ip', 'order_id', 'customer_id', 'total', 'type' );
		$fields  = array_intersect_key( $fields, array_flip( $allowed ) );
		if ( isset( $fields['meta'] ) && is_array( $fields['meta'] ) ) {
			$fields['meta'] = wp_json_encode( $fields['meta'] );
		}
		$fields['updated_at'] = current_time( 'mysql', true );
		self::flush();
		return false !== $wpdb->update( self::table(), $fields, array( 'id' => absint( $id ) ) );
	}

	/**
	 * Update one item row.
	 *
	 * @param int   $item_id Item row ID.
	 * @param array $fields  Columns.
	 * @return bool
	 */
	public static function update_item( $item_id, array $fields ) {
		global $wpdb;
		$allowed = array( 'received_qty', 'restocked_qty', 'refunded_qty', 'exchange_product_id', 'exchange_name', 'exchange_price', 'qty' );
		$fields  = array_intersect_key( $fields, array_flip( $allowed ) );
		if ( ! $fields ) {
			return false;
		}
		self::flush();
		return false !== $wpdb->update( self::items_table(), $fields, array( 'id' => absint( $item_id ) ) );
	}

	/**
	 * Build the WHERE clause for a list query.
	 *
	 * @param array $args status, type, search, order_id, customer_id, email, since (GMT datetime).
	 * @return array [ sql, params ]
	 */
	private static function where( array $args ) {
		global $wpdb;
		$where  = array( '1=1' );
		$params = array();
		if ( ! empty( $args['status'] ) ) {
			$statuses = (array) $args['status'];
			$where[]  = 'status IN (' . implode( ',', array_fill( 0, count( $statuses ), '%s' ) ) . ')';
			$params   = array_merge( $params, array_map( 'sanitize_key', $statuses ) );
		}
		if ( ! empty( $args['type'] ) ) {
			$where[]  = 'type = %s';
			$params[] = sanitize_key( $args['type'] );
		}
		if ( ! empty( $args['order_id'] ) ) {
			$where[]  = 'order_id = %d';
			$params[] = absint( $args['order_id'] );
		}
		if ( ! empty( $args['customer_id'] ) && ! empty( $args['email'] ) ) {
			// A customer's requests: by account, plus guest requests made with
			// the account's email before they had an account.
			$where[]  = '(customer_id = %d OR (customer_id = 0 AND email = %s))';
			$params[] = absint( $args['customer_id'] );
			$params[] = (string) $args['email'];
		} elseif ( ! empty( $args['customer_id'] ) ) {
			$where[]  = 'customer_id = %d';
			$params[] = absint( $args['customer_id'] );
		} elseif ( ! empty( $args['email'] ) ) {
			$where[]  = 'email = %s';
			$params[] = (string) $args['email'];
		}
		if ( ! empty( $args['since'] ) ) {
			$where[]  = 'created_at >= %s';
			$params[] = (string) $args['since'];
		}
		if ( isset( $args['flagged'] ) && $args['flagged'] ) {
			$where[] = "flags <> ''";
		}
		if ( ! empty( $args['search'] ) ) {
			$search = trim( (string) $args['search'] );
			$like   = '%' . $wpdb->esc_like( $search ) . '%';
			$any    = array( 'email LIKE %s', 'name LIKE %s' );
			$values = array( $like, $like );
			if ( preg_match( '/^#?R-?(\d+)$/i', $search, $m ) ) {
				$any[]    = 'id = %d';
				$values[] = (int) $m[1];
			}
			if ( preg_match( '/^#?(\d+)$/', $search, $m ) ) {
				// A bare number: an order number or a request ID.
				$any[]    = 'order_id = %d';
				// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- WooCommerce's own hook.
				$values[] = absint( apply_filters( 'woocommerce_shortcode_order_tracking_order_id', $m[1] ) );
				$any[]    = 'id = %d';
				$values[] = (int) $m[1];
			}
			$where[] = '(' . implode( ' OR ', $any ) . ')';
			$params  = array_merge( $params, $values );
		}
		return array( implode( ' AND ', $where ), $params );
	}

	/**
	 * List requests.
	 *
	 * @param array $args   Filters (see where()).
	 * @param int   $limit  Rows.
	 * @param int   $offset Offset.
	 * @return Request[]
	 */
	public static function query( array $args = array(), $limit = 50, $offset = 0 ) {
		global $wpdb;
		list( $where, $params ) = self::where( $args );
		$params[]               = max( 1, (int) $limit );
		$params[]               = max( 0, (int) $offset );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where is built from fixed fragments with placeholders; the values are in $params.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE ' . $where . ' ORDER BY id DESC LIMIT %d OFFSET %d', array_merge( array( self::table() ), $params ) ), ARRAY_A );
		return array_map(
			static function ( $row ) {
				return new Request( $row );
			},
			$rows ? $rows : array()
		);
	}

	/**
	 * Count requests.
	 *
	 * @param array $args Filters (see where()).
	 * @return int
	 */
	public static function count( array $args = array() ) {
		global $wpdb;
		list( $where, $params ) = self::where( $args );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- see query().
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE ' . $where, array_merge( array( self::table() ), $params ) ) );
	}

	/**
	 * Request counts per status.
	 *
	 * @param string $type Optional type filter.
	 * @return array<string,int>
	 */
	public static function status_counts( $type = '' ) {
		global $wpdb;
		if ( $type ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i WHERE type = %s GROUP BY status', self::table(), $type ), ARRAY_A );
		} else {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i GROUP BY status', self::table() ), ARRAY_A );
		}
		$out = array();
		foreach ( (array) $rows as $row ) {
			$out[ $row['status'] ] = (int) $row['n'];
		}
		return $out;
	}

	/**
	 * Per-request cache of order summaries: order ID => [ held, full ].
	 *
	 * @var array<int,array>
	 */
	private static $summaries = array();

	/**
	 * Forget cached summaries (after any write, and inside the order lock).
	 *
	 * @return void
	 */
	public static function flush() {
		self::$summaries = array();
	}

	/**
	 * Load the summaries of several orders in one query: what each order has
	 * in holding requests, and whether it was withdrawn from in full. Pages
	 * that check many orders (My Account) call this first.
	 *
	 * @param int[] $order_ids Order IDs.
	 * @return void
	 */
	public static function prime( array $order_ids ) {
		global $wpdb;
		$ids = array_values( array_diff( array_unique( array_filter( array_map( 'absint', $order_ids ) ) ), array_keys( self::$summaries ) ) );
		if ( ! $ids ) {
			return;
		}
		foreach ( $ids as $id ) {
			self::$summaries[ $id ] = array(
				'held' => array(),
				'full' => false,
			);
		}
		$in = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- placeholders built above.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT r.order_id, r.id, r.type, r.meta, i.order_item_id, GREATEST(i.qty - i.refunded_qty, 0) AS qty
				FROM %i r LEFT JOIN %i i ON i.request_id = r.id
				WHERE r.order_id IN ($in) AND r.status IN (%s, %s, %s, %s)",
				array_merge( array( self::table(), self::items_table() ), $ids, self::HOLDING )
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		$full_checked = array();
		foreach ( (array) $rows as $row ) {
			$order = (int) $row['order_id'];
			if ( null !== $row['order_item_id'] ) {
				$item                                    = (int) $row['order_item_id'];
				self::$summaries[ $order ]['held'][ $item ] = ( self::$summaries[ $order ]['held'][ $item ] ?? 0 ) + (int) $row['qty'];
			}
			if ( 'withdrawal' === $row['type'] && ! isset( $full_checked[ $row['id'] ] ) ) {
				$full_checked[ $row['id'] ] = true;
				$meta                       = json_decode( (string) $row['meta'], true );
				if ( is_array( $meta ) && isset( $meta['scope'] ) && 'full' === $meta['scope'] ) {
					self::$summaries[ $order ]['full'] = true;
				}
			}
		}
	}

	/**
	 * Quantities of each order item already in a holding request.
	 *
	 * Units refunded through WooCommerce with line quantities (refunded_qty)
	 * are left out: they already count in the order's refunded quantities,
	 * and counting both would make the rest of the line unreturnable. Units
	 * settled another way (exchange, credit, amount-only refund) stay held.
	 *
	 * @param int $order_id   Order ID.
	 * @param int $exclude_id Request to leave out (when re-checking itself).
	 * @return array<int,int> order_item_id => qty.
	 */
	public static function held_quantities( $order_id, $exclude_id = 0 ) {
		global $wpdb;
		$order_id = absint( $order_id );
		if ( ! $exclude_id ) {
			self::prime( array( $order_id ) );
			return self::$summaries[ $order_id ]['held'];
		}
		// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- the statuses are spread from self::HOLDING.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT i.order_item_id, SUM(GREATEST(i.qty - i.refunded_qty, 0)) AS qty FROM %i i INNER JOIN %i r ON r.id = i.request_id
				WHERE r.order_id = %d AND r.id <> %d AND r.status IN (%s, %s, %s, %s) GROUP BY i.order_item_id',
				self::items_table(),
				self::table(),
				$order_id,
				absint( $exclude_id ),
				...self::HOLDING
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		$out = array();
		foreach ( (array) $rows as $row ) {
			$out[ (int) $row['order_item_id'] ] = (int) $row['qty'];
		}
		return $out;
	}

	/**
	 * Whether a holding withdrawal for the whole order already exists.
	 *
	 * @param int $order_id Order ID.
	 * @return bool
	 */
	public static function has_full_withdrawal( $order_id ) {
		$order_id = absint( $order_id );
		self::prime( array( $order_id ) );
		return self::$summaries[ $order_id ]['full'];
	}

	/**
	 * All request IDs with a given email (privacy tools).
	 *
	 * @param string $email Email.
	 * @param int    $limit Rows.
	 * @param int    $page  1-based page.
	 * @return Request[]
	 */
	public static function by_email( $email, $limit = 50, $page = 1 ) {
		return self::query( array( 'email' => strtolower( (string) $email ) ), $limit, ( max( 1, (int) $page ) - 1 ) * $limit );
	}

	/**
	 * Delete a request, its items and its log (admin "delete", tests).
	 *
	 * @param int $id Request ID.
	 * @return void
	 */
	public static function delete( $id ) {
		global $wpdb;
		$id = absint( $id );
		self::flush();
		$wpdb->delete( self::items_table(), array( 'request_id' => $id ) );
		$wpdb->delete( Log::table(), array( 'request_id' => $id ) );
		$wpdb->delete( self::table(), array( 'id' => $id ) );
	}

	/**
	 * Empty every table (tests only).
	 *
	 * @return void
	 */
	public static function truncate() {
		global $wpdb;
		self::flush();
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', self::items_table() ) );
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', self::table() ) );
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', Log::table() ) );
	}
}