<?php
/**
 * SureDonation Database Donations Table Class.
 *
 * @package SureDonation
 */

namespace SureDonation\Inc\Database\Tables;

use SureDonation\Inc\Database\Base;
use SureDonation\Inc\Traits\Get_Instance;

// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

/**
 * SureDonation Database Donations Table Class.
 *
 * @since 0.0.1
 */
class Donations extends Base {
	use Get_Instance;

	/**
	 * Table suffix.
	 *
	 * @var string
	 * @since 0.0.1
	 */
	protected $table_suffix = 'donations';

	/**
	 * Table version.
	 *
	 * @var int
	 * @since 0.0.1
	 */
	protected $table_version = 1;

	/**
	 * Valid payment statuses.
	 *
	 * @var array<string>
	 * @since 0.0.1
	 */
	private static $valid_statuses = [
		'pending',
		'processing',
		'completed',
		'failed',
		'refunded',
		'partially_refunded',
		'cancelled',
		'suspicious',
	];

	/**
	 * Valid order columns.
	 *
	 * @var array<string>
	 * @since 0.0.1
	 */
	private static $valid_order_columns = [
		'id',
		'campaign_id',
		'amount',
		'created_at',
		'updated_at',
		'payment_status',
		'donor_name',
		'donor_email',
	];

	/**
	 * {@inheritDoc}
	 */
	public function get_schema() {
		return [
			'id'              => [
				'type' => 'number',
			],
			'campaign_id'     => [
				'type' => 'number',
			],
			'donor_id'        => [
				'type'    => 'number',
				'default' => 0,
			],
			'form_id'         => [
				'type'    => 'number',
				'default' => 0,
			],
			'amount'          => [
				'type'    => 'string',
				'default' => '0.00000000',
			],
			'fees_covered'    => [
				'type'    => 'string',
				'default' => '0.00000000',
			],
			'refunded_amount' => [
				'type'    => 'string',
				'default' => '0.00000000',
			],
			'currency'        => [
				'type'    => 'string',
				'default' => 'USD',
			],
			'transaction_id'  => [
				'type'    => 'string',
				'default' => '',
			],
			'customer_id'     => [
				'type'    => 'string',
				'default' => '',
			],
			'gateway'         => [
				'type'    => 'string',
				'default' => 'stripe',
			],
			'payment_status'  => [
				'type'    => 'string',
				'default' => 'pending',
			],
			'payment_mode'    => [
				'type'    => 'string',
				'default' => 'test',
			],
			'donor_name'      => [
				'type'    => 'string',
				'default' => '',
			],
			'donor_email'     => [
				'type'    => 'string',
				'default' => '',
			],
			'donor_phone'     => [
				'type'    => 'string',
				'default' => '',
			],
			'is_anonymous'    => [
				'type'    => 'boolean',
				'default' => false,
			],
			'donation_type'   => [
				'type'    => 'string',
				'default' => 'one-time',
			],
			'donor_comment'   => [
				'type'    => 'string',
				'default' => '',
			],
			'receipt_sent'    => [
				'type'    => 'boolean',
				'default' => false,
			],
			'receipt_pdf_url' => [
				'type'    => 'string',
				'default' => '',
			],
			'donation_data'   => [
				'type'    => 'array',
				'default' => [],
			],
			'log'             => [
				'type'    => 'array',
				'default' => [],
			],
			'ip_address'      => [
				'type'    => 'string',
				'default' => '',
			],
			'user_agent'      => [
				'type'    => 'string',
				'default' => '',
			],
			'referer_url'     => [
				'type'    => 'string',
				'default' => '',
			],
			'created_at'      => [
				'type' => 'datetime',
			],
			'updated_at'      => [
				'type' => 'datetime',
			],
		];
	}

	/**
	 * {@inheritDoc}
	 */
	public function get_columns_definition() {
		return [
			'id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
			'campaign_id BIGINT(20) UNSIGNED NOT NULL',
			'donor_id BIGINT(20) UNSIGNED NULL',
			'form_id BIGINT(20) UNSIGNED NULL',
			'amount DECIMAL(26,8) NOT NULL',
			'fees_covered DECIMAL(26,8) NOT NULL DEFAULT 0',
			'refunded_amount DECIMAL(26,8) NOT NULL DEFAULT 0',
			'currency VARCHAR(10) NOT NULL',
			'transaction_id VARCHAR(255) NOT NULL',
			'customer_id VARCHAR(50) NOT NULL',
			'gateway VARCHAR(20) NOT NULL',
			'payment_status VARCHAR(50) NOT NULL',
			'payment_mode VARCHAR(20) NOT NULL',
			'donor_name VARCHAR(255) NOT NULL',
			'donor_email VARCHAR(255) NOT NULL',
			'donor_phone VARCHAR(50) NOT NULL',
			'is_anonymous TINYINT(1) NOT NULL DEFAULT 0',
			'donation_type VARCHAR(30) NOT NULL',
			'donor_comment TEXT',
			'receipt_sent TINYINT(1) NOT NULL DEFAULT 0',
			'receipt_pdf_url VARCHAR(255) NOT NULL',
			'donation_data LONGTEXT',
			'log LONGTEXT',
			'ip_address VARCHAR(45) NOT NULL',
			'user_agent TEXT',
			'referer_url TEXT',
			'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
			'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			'INDEX idx_campaign (campaign_id)',
			'INDEX idx_donor (donor_id)',
			'INDEX idx_status (payment_status)',
			'INDEX idx_email (donor_email)',
			'INDEX idx_created (created_at)',
			'INDEX idx_form (form_id)',
		];
	}

	/**
	 * Add a new donation record.
	 *
	 * @param array<mixed> $data Donation data to insert.
	 * @return int|false The donation ID on success, false on error.
	 * @since 0.0.1
	 */
	public static function add( $data ) {
		if ( empty( $data['campaign_id'] ) ) {
			return false;
		}

		$instance = self::get_instance();

		// Set created_at if not provided.
		if ( ! isset( $data['created_at'] ) ) {
			$data['created_at'] = current_time( 'mysql' );
		}

		return $instance->use_insert( $data );
	}

	/**
	 * Update a donation record.
	 *
	 * @param int                 $donation_id Donation ID to update.
	 * @param array<string,mixed> $data        Data to update.
	 * @return int|false Number of rows updated or false on error.
	 * @since 0.0.1
	 */
	public static function update( $donation_id, $data = [] ) {
		if ( empty( $donation_id ) ) {
			return false;
		}

		// Set updated_at.
		$data['updated_at'] = current_time( 'mysql' );

		return self::get_instance()->use_update( $data, [ 'id' => absint( $donation_id ) ] );
	}

	/**
	 * Get a single donation by ID.
	 *
	 * @param int $donation_id Donation ID.
	 * @return array<mixed>|null Donation data or null if not found.
	 * @since 0.0.1
	 */
	public static function get( $donation_id ) {
		if ( empty( $donation_id ) ) {
			return null;
		}

		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE id = %d',
				$instance->get_tablename(),
				absint( $donation_id )
			),
			ARRAY_A
		);

		if ( ! $result ) {
			return null;
		}

		return $instance->decode_by_datatype( $result );
	}

	/**
	 * Get all donations with pagination.
	 *
	 * @param int    $limit   Number of records to return.
	 * @param int    $offset  Offset for pagination.
	 * @param string $orderby Column to order by.
	 * @param string $order   Order direction (ASC or DESC).
	 * @return array<mixed> Array of donations.
	 * @since 0.0.1
	 */
	public static function get_all( $limit = 10, $offset = 0, $orderby = 'created_at', $order = 'DESC' ) {
		$instance = self::get_instance();
		global $wpdb;
		$table = $instance->get_tablename();

		// Validate orderby column.
		if ( ! in_array( $orderby, self::$valid_order_columns, true ) ) {
			$orderby = 'created_at';
		}

		// Validate order direction.
		$order = strtoupper( $order );
		if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
			$order = 'DESC';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Data changes frequently, caching would show stale results.
		$results = 'ASC' === $order
			? $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i ORDER BY %i ASC LIMIT %d, %d',
					$table,
					$orderby,
					absint( $offset ),
					absint( $limit )
				),
				ARRAY_A
			)
			: $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i ORDER BY %i DESC LIMIT %d, %d',
					$table,
					$orderby,
					absint( $offset ),
					absint( $limit )
				),
				ARRAY_A
			);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( ! $results || ! is_array( $results ) ) {
			return [];
		}

		return array_map( [ $instance, 'decode_by_datatype' ], $results );
	}

	/**
	 * Get donations for admin listing with optional filters.
	 *
	 * @param string $status      Payment status filter ('all' for no filter).
	 * @param int    $campaign_id Campaign ID filter (0 for no filter).
	 * @param string $search      Search term for donor_name, donor_email, or transaction_id.
	 * @param int    $limit       Number of records to return.
	 * @param int    $offset      Offset for pagination.
	 * @param string $orderby     Column to order by.
	 * @param string $order       Order direction (ASC or DESC).
	 * @return array<mixed> Array of donations.
	 * @since 0.0.1
	 */
	public static function get_admin_list( $status = 'all', $campaign_id = 0, $search = '', $limit = 10, $offset = 0, $orderby = 'created_at', $order = 'DESC' ) {
		$instance = self::get_instance();
		global $wpdb;
		$table = $instance->get_tablename();

		// Validate orderby column.
		if ( ! in_array( $orderby, self::$valid_order_columns, true ) ) {
			$orderby = 'created_at';
		}

		// Validate order direction.
		$order = strtoupper( $order );
		if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
			$order = 'DESC';
		}

		// Build query based on filters.
		$has_status   = 'all' !== $status;
		$has_campaign = $campaign_id > 0;
		$has_search   = ! empty( $search );
		$is_asc       = 'ASC' === $order;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Data changes frequently, caching would show stale results.

		// All three filters.
		if ( $has_status && $has_campaign && $has_search ) {
			$search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
			$results     = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s AND campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i ASC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						absint( $campaign_id ),
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s AND campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i DESC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						absint( $campaign_id ),
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		} elseif ( $has_status && $has_campaign ) {
			$results = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s AND campaign_id = %d ORDER BY %i ASC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						absint( $campaign_id ),
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s AND campaign_id = %d ORDER BY %i DESC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						absint( $campaign_id ),
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		} elseif ( $has_status && $has_search ) {
			$search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
			$results     = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i ASC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i DESC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		} elseif ( $has_campaign && $has_search ) {
			$search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
			$results     = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i ASC LIMIT %d, %d',
						$table,
						absint( $campaign_id ),
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE campaign_id = %d AND (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i DESC LIMIT %d, %d',
						$table,
						absint( $campaign_id ),
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		} elseif ( $has_status ) {
			$results = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s ORDER BY %i ASC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE payment_status = %s ORDER BY %i DESC LIMIT %d, %d',
						$table,
						sanitize_text_field( $status ),
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		} elseif ( $has_campaign ) {
			$results = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE campaign_id = %d ORDER BY %i ASC LIMIT %d, %d',
						$table,
						absint( $campaign_id ),
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE campaign_id = %d ORDER BY %i DESC LIMIT %d, %d',
						$table,
						absint( $campaign_id ),
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		} elseif ( $has_search ) {
			$search_term = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
			$results     = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i ASC LIMIT %d, %d',
						$table,
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE (donor_name LIKE %s OR donor_email LIKE %s OR transaction_id LIKE %s) ORDER BY %i DESC LIMIT %d, %d',
						$table,
						$search_term,
						$search_term,
						$search_term,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		} else {
			$results = $is_asc
				? $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i ORDER BY %i ASC LIMIT %d, %d',
						$table,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				)
				: $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i ORDER BY %i DESC LIMIT %d, %d',
						$table,
						$orderby,
						absint( $offset ),
						absint( $limit )
					),
					ARRAY_A
				);
		}

		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( ! $results || ! is_array( $results ) ) {
			return [];
		}

		return array_map( [ $instance, 'decode_by_datatype' ], $results );
	}

	/**
	 * Get donations by status with pagination.
	 *
	 * @param string $status  Payment status.
	 * @param int    $limit   Number of records to return.
	 * @param int    $offset  Offset for pagination.
	 * @param string $orderby Column to order by.
	 * @param string $order   Order direction (ASC or DESC).
	 * @return array<mixed> Array of donations.
	 * @since 0.0.1
	 */
	public static function get_by_status( $status, $limit = 10, $offset = 0, $orderby = 'created_at', $order = 'DESC' ) {
		$instance = self::get_instance();
		global $wpdb;
		$table = $instance->get_tablename();

		// Validate orderby column.
		if ( ! in_array( $orderby, self::$valid_order_columns, true ) ) {
			$orderby = 'created_at';
		}

		// Validate order direction.
		$order = strtoupper( $order );
		if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
			$order = 'DESC';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Data changes frequently, caching would show stale results.
		$results = 'ASC' === $order
			? $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE payment_status = %s ORDER BY %i ASC LIMIT %d, %d',
					$table,
					sanitize_text_field( $status ),
					$orderby,
					absint( $offset ),
					absint( $limit )
				),
				ARRAY_A
			)
			: $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE payment_status = %s ORDER BY %i DESC LIMIT %d, %d',
					$table,
					sanitize_text_field( $status ),
					$orderby,
					absint( $offset ),
					absint( $limit )
				),
				ARRAY_A
			);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( ! $results || ! is_array( $results ) ) {
			return [];
		}

		return array_map( [ $instance, 'decode_by_datatype' ], $results );
	}

	/**
	 * Get donations by campaign ID with pagination.
	 *
	 * @param int    $campaign_id Campaign ID.
	 * @param int    $limit       Number of records to return.
	 * @param int    $offset      Offset for pagination.
	 * @param string $orderby     Column to order by.
	 * @param string $order       Order direction (ASC or DESC).
	 * @return array<mixed> Array of donations.
	 * @since 0.0.1
	 */
	public static function get_by_campaign_id( $campaign_id, $limit = 100, $offset = 0, $orderby = 'created_at', $order = 'DESC' ) {
		if ( empty( $campaign_id ) ) {
			return [];
		}

		$instance = self::get_instance();
		global $wpdb;
		$table = $instance->get_tablename();

		// Validate orderby column.
		if ( ! in_array( $orderby, self::$valid_order_columns, true ) ) {
			$orderby = 'created_at';
		}

		// Validate order direction.
		$order = strtoupper( $order );
		if ( ! in_array( $order, [ 'ASC', 'DESC' ], true ) ) {
			$order = 'DESC';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Data changes frequently, caching would show stale results.
		$results = 'ASC' === $order
			? $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE campaign_id = %d ORDER BY %i ASC LIMIT %d, %d',
					$table,
					absint( $campaign_id ),
					$orderby,
					absint( $offset ),
					absint( $limit )
				),
				ARRAY_A
			)
			: $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE campaign_id = %d ORDER BY %i DESC LIMIT %d, %d',
					$table,
					absint( $campaign_id ),
					$orderby,
					absint( $offset ),
					absint( $limit )
				),
				ARRAY_A
			);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( ! $results || ! is_array( $results ) ) {
			return [];
		}

		return array_map( [ $instance, 'decode_by_datatype' ], $results );
	}

	/**
	 * Delete a donation.
	 *
	 * @param int $donation_id Donation ID.
	 * @return int|false Number of rows deleted or false on error.
	 * @since 0.0.1
	 */
	public static function delete( $donation_id ) {
		if ( empty( $donation_id ) ) {
			return false;
		}

		return self::get_instance()->use_delete( [ 'id' => absint( $donation_id ) ] );
	}

	/**
	 * Get donations by donor email.
	 *
	 * @param string $email Donor email.
	 * @return array<mixed> Array of donations.
	 * @since 0.0.1
	 */
	public static function get_by_donor_email( $email ) {
		if ( empty( $email ) ) {
			return [];
		}

		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE donor_email = %s ORDER BY created_at DESC',
				$instance->get_tablename(),
				sanitize_email( $email )
			),
			ARRAY_A
		);

		if ( ! $results || ! is_array( $results ) ) {
			return [];
		}

		return array_map( [ $instance, 'decode_by_datatype' ], $results );
	}

	/**
	 * Get donation by transaction ID.
	 *
	 * @param string $transaction_id Transaction ID.
	 * @return array<string, mixed>|null Donation data or null if not found.
	 * @since 0.0.1
	 */
	public static function get_by_transaction_id( $transaction_id ) {
		if ( empty( $transaction_id ) ) {
			return null;
		}

		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE transaction_id = %s LIMIT 1',
				$instance->get_tablename(),
				sanitize_text_field( $transaction_id )
			),
			ARRAY_A
		);

		if ( ! $result ) {
			return null;
		}

		return $instance->decode_by_datatype( $result );
	}

	/**
	 * Get total donations count.
	 *
	 * @return int Total count.
	 * @since 0.0.1
	 */
	public static function count_all() {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i',
				$instance->get_tablename()
			)
		);

		return is_numeric( $count ) ? (int) $count : 0;
	}

	/**
	 * Get total donations count by status.
	 *
	 * @param string $status Payment status.
	 * @return int Total count.
	 * @since 0.0.1
	 */
	public static function count_by_status( $status ) {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE payment_status = %s',
				$instance->get_tablename(),
				sanitize_text_field( $status )
			)
		);

		return is_numeric( $count ) ? (int) $count : 0;
	}

	/**
	 * Get total donations count by campaign.
	 *
	 * @param int $campaign_id Campaign ID.
	 * @return int Total count.
	 * @since 0.0.1
	 */
	public static function count_by_campaign( $campaign_id ) {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE campaign_id = %d',
				$instance->get_tablename(),
				absint( $campaign_id )
			)
		);

		return is_numeric( $count ) ? (int) $count : 0;
	}

	/**
	 * Get total donations count by status and campaign.
	 *
	 * @param string $status      Payment status ('all' for no filter).
	 * @param int    $campaign_id Optional campaign ID (0 for no filter).
	 * @return int Total count.
	 * @since 0.0.1
	 */
	public static function get_total_donations_by_status( $status = 'all', $campaign_id = 0 ) {
		$instance = self::get_instance();
		global $wpdb;

		// Both filters.
		if ( 'all' !== $status && $campaign_id > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i WHERE payment_status = %s AND campaign_id = %d',
					$instance->get_tablename(),
					sanitize_text_field( $status ),
					absint( $campaign_id )
				)
			);
			return is_numeric( $count ) ? (int) $count : 0;
		}

		// Status filter only.
		if ( 'all' !== $status ) {
			return self::count_by_status( $status );
		}

		// Campaign filter only.
		if ( $campaign_id > 0 ) {
			return self::count_by_campaign( $campaign_id );
		}

		// No filters.
		return self::count_all();
	}

	/**
	 * Get campaign statistics.
	 *
	 * @param int $campaign_id Campaign ID.
	 * @return array<string,mixed> Campaign statistics.
	 * @since 0.0.1
	 */
	public static function get_campaign_stats( $campaign_id ) {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$stats = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) as donation_count,
					COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
					COUNT(DISTINCT donor_email) as unique_donors,
					COALESCE(AVG(amount - refunded_amount), 0) as average_donation,
					COALESCE(MAX(amount - refunded_amount), 0) as largest_donation
				FROM %i
				WHERE campaign_id = %d AND payment_status IN ('completed', 'partially_refunded')",
				$instance->get_tablename(),
				absint( $campaign_id )
			),
			ARRAY_A
		);

		return $stats ? $stats : [
			'donation_count'   => 0,
			'total_raised'     => 0,
			'unique_donors'    => 0,
			'average_donation' => 0,
			'largest_donation' => 0,
		];
	}

	/**
	 * Get global dashboard statistics.
	 *
	 * @return array{total_donations: string, total_raised: string, unique_donors: string, average_donation: string, largest_donation: string} Dashboard statistics.
	 * @since 0.0.1
	 */
	public static function get_dashboard_stats() {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$stats = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) as total_donations,
					COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
					COUNT(DISTINCT donor_email) as unique_donors,
					COALESCE(AVG(amount - refunded_amount), 0) as average_donation,
					COALESCE(MAX(amount - refunded_amount), 0) as largest_donation
				FROM %i
				WHERE payment_status IN ('completed', 'partially_refunded')",
				$instance->get_tablename()
			),
			ARRAY_A
		);

		return $stats ? $stats : [
			'total_donations'  => 0,
			'total_raised'     => 0,
			'unique_donors'    => 0,
			'average_donation' => 0,
			'largest_donation' => 0,
		];
	}

	/**
	 * Get recent donations globally (all campaigns).
	 *
	 * @param int $limit Number of donations to retrieve.
	 * @return array<int, array<string, mixed>> Array of recent donations.
	 * @since 0.0.1
	 */
	public static function get_recent_donations_global( $limit = 5 ) {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE payment_status IN ('completed', 'partially_refunded') ORDER BY created_at DESC LIMIT %d",
				$instance->get_tablename(),
				absint( $limit )
			),
			ARRAY_A
		);

		if ( ! $results || ! is_array( $results ) ) {
			return [];
		}

		return array_map( [ $instance, 'decode_by_datatype' ], $results );
	}

	/**
	 * Get top campaigns by donations.
	 *
	 * @param int $limit Number of campaigns to retrieve.
	 * @return array<int, array{campaign_id: string, donation_count: string, total_raised: string, unique_donors: string}> Array of top campaigns with stats.
	 * @since 0.0.1
	 */
	public static function get_top_campaigns( $limit = 5 ) {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					campaign_id,
					COUNT(*) as donation_count,
					COALESCE(SUM(amount - refunded_amount), 0) as total_raised,
					COUNT(DISTINCT donor_email) as unique_donors
				FROM %i
				WHERE payment_status IN ('completed', 'partially_refunded')
				GROUP BY campaign_id
				ORDER BY total_raised DESC
				LIMIT %d",
				$instance->get_tablename(),
				absint( $limit )
			),
			ARRAY_A
		);

		return $results ? $results : [];
	}

	/**
	 * Get donation trends over time.
	 *
	 * @param string $after  Start date (ISO format).
	 * @param string $before End date (ISO format).
	 * @param string $group  Grouping: 'day', 'week', or 'month'.
	 * @return array<int, array{period: string, donation_count: string, total_amount: string}> Array of donation trends.
	 * @since 0.0.1
	 */
	public static function get_donation_trends( $after = '', $before = '', $group = 'day' ) {
		$instance = self::get_instance();
		global $wpdb;

		// Default to last 30 days if no dates provided.
		if ( empty( $after ) ) {
			$after = gmdate( 'Y-m-d', strtotime( '-30 days' ) );
		}
		if ( empty( $before ) ) {
			$before = gmdate( 'Y-m-d' );
		}

		// Determine date format based on grouping.
		switch ( $group ) {
			case 'month':
				$date_format = '%Y-%m-01';
				break;
			case 'week':
				$date_format = '%x-%v'; // ISO year-week.
				break;
			case 'day':
			default:
				$date_format = '%Y-%m-%d';
				break;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					DATE_FORMAT(created_at, %s) as period,
					COUNT(*) as donation_count,
					COALESCE(SUM(amount - refunded_amount), 0) as total_amount
				FROM %i
				WHERE payment_status IN ('completed', 'partially_refunded')
					AND DATE(created_at) >= %s
					AND DATE(created_at) <= %s
				GROUP BY period
				ORDER BY period ASC",
				$date_format,
				$instance->get_tablename(),
				$after,
				$before
			),
			ARRAY_A
		);

		return $results ? $results : [];
	}

	/**
	 * Get recent donations for a campaign.
	 *
	 * @param int $campaign_id Campaign ID.
	 * @param int $limit       Number of donations to retrieve.
	 * @return array<mixed> Array of recent donations.
	 * @since 0.0.1
	 */
	public static function get_recent_donations( $campaign_id, $limit = 5 ) {
		$instance = self::get_instance();
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE campaign_id = %d AND payment_status IN ('completed', 'partially_refunded') ORDER BY created_at DESC LIMIT %d",
				$instance->get_tablename(),
				absint( $campaign_id ),
				absint( $limit )
			),
			ARRAY_A
		);

		if ( ! $results || ! is_array( $results ) ) {
			return [];
		}

		return array_map( [ $instance, 'decode_by_datatype' ], $results );
	}

	/**
	 * Update donation status.
	 *
	 * @param int    $donation_id Donation ID.
	 * @param string $status      New status.
	 * @return int|false Number of rows updated or false on error.
	 * @since 0.0.1
	 */
	public static function update_status( $donation_id, $status ) {
		if ( empty( $donation_id ) || ! in_array( $status, self::$valid_statuses, true ) ) {
			return false;
		}

		return self::update( $donation_id, [ 'payment_status' => $status ] );
	}

	/**
	 * Get valid payment statuses.
	 *
	 * @return array<string> Valid statuses.
	 * @since 0.0.1
	 */
	public static function get_valid_statuses() {
		return self::$valid_statuses;
	}

	/**
	 * Add a log entry to a donation.
	 *
	 * @param int                  $donation_id Donation ID.
	 * @param string               $action      Action type (e.g., 'status_change', 'refund', 'webhook').
	 * @param string               $message     Log message.
	 * @param array<string, mixed> $data        Optional additional data.
	 * @return int|false Number of rows updated or false on error.
	 * @since 0.0.1
	 */
	public static function add_log( $donation_id, $action, $message, $data = [] ) {
		if ( empty( $donation_id ) ) {
			return false;
		}

		$donation = self::get( $donation_id );
		if ( ! $donation ) {
			return false;
		}

		// Get existing log or initialize empty array.
		// Note: decode_by_datatype() already decodes JSON to array, so check for array first.
		$log_data = $donation['log'] ?? [];
		if ( is_array( $log_data ) ) {
			$log = $log_data;
		} elseif ( is_string( $log_data ) && ! empty( $log_data ) ) {
			$log = json_decode( $log_data, true );
			if ( ! is_array( $log ) ) {
				$log = [];
			}
		} else {
			$log = [];
		}

		// Add new log entry.
		$log[] = [
			'action'    => sanitize_text_field( $action ),
			'message'   => sanitize_text_field( $message ),
			'data'      => $data,
			'timestamp' => current_time( 'mysql' ),
		];

		return self::update( $donation_id, [ 'log' => $log ] );
	}

	/**
	 * Get log entries for a donation.
	 *
	 * @param int $donation_id Donation ID.
	 * @return array<int, array<string, mixed>> Log entries.
	 * @since 0.0.1
	 */
	public static function get_log( $donation_id ) {
		if ( empty( $donation_id ) ) {
			return [];
		}

		$donation = self::get( $donation_id );
		if ( ! $donation || empty( $donation['log'] ) ) {
			return [];
		}

		// Note: decode_by_datatype() already decodes JSON to array, so check for array first.
		$log_data = $donation['log'];
		if ( is_array( $log_data ) ) {
			return $log_data;
		}

		if ( is_string( $log_data ) ) {
			$log = json_decode( $log_data, true );
			return is_array( $log ) ? $log : [];
		}

		return [];
	}

	/**
	 * Add refund data to donation_data for audit trail and duplicate prevention.
	 *
	 * Stores each refund with its ID as the key for O(1) lookups.
	 *
	 * @param int                  $donation_id Donation ID.
	 * @param array<string, mixed> $refund_data Refund data to store.
	 * @return bool True on success, false on failure.
	 * @since 0.0.1
	 */
	public static function add_refund_to_donation_data( $donation_id, $refund_data ) {
		$refund_id = $refund_data['refund_id'] ?? '';

		if ( empty( $refund_id ) || empty( $donation_id ) ) {
			return false;
		}

		$donation = self::get( $donation_id );
		if ( ! $donation ) {
			return false;
		}

		// Get existing donation_data.
		$donation_data = $donation['donation_data'] ?? [];
		if ( is_string( $donation_data ) && ! empty( $donation_data ) ) {
			$donation_data = json_decode( $donation_data, true );
		}
		if ( ! is_array( $donation_data ) ) {
			$donation_data = [];
		}

		// Initialize refunds array if not exists.
		if ( ! isset( $donation_data['refunds'] ) || ! is_array( $donation_data['refunds'] ) ) {
			$donation_data['refunds'] = [];
		}

		// Store with refund ID as key for O(1) lookup (duplicate prevention).
		$donation_data['refunds'][ $refund_id ] = $refund_data;

		// Update donation_data in database.
		$result = self::update( $donation_id, [ 'donation_data' => $donation_data ] );

		return false !== $result;
	}

	/**
	 * Check if a refund already exists in the donation data.
	 *
	 * This prevents duplicate processing of the same refund.
	 *
	 * @param int    $donation_id Donation ID.
	 * @param string $refund_id   Refund ID to check.
	 * @return bool True if refund already exists, false otherwise.
	 * @since 0.0.1
	 */
	public static function check_refund_exists( $donation_id, $refund_id ) {
		if ( empty( $donation_id ) || empty( $refund_id ) ) {
			return false;
		}

		$donation = self::get( $donation_id );
		if ( ! $donation ) {
			return false;
		}

		// Get donation_data and parse if needed.
		$donation_data = $donation['donation_data'] ?? [];
		if ( is_string( $donation_data ) && ! empty( $donation_data ) ) {
			$donation_data = json_decode( $donation_data, true );
		}
		if ( ! is_array( $donation_data ) ) {
			return false;
		}

		// Check if refunds array exists and contains this refund ID.
		if ( empty( $donation_data['refunds'] ) || ! is_array( $donation_data['refunds'] ) ) {
			return false;
		}

		// O(1) lookup using refund ID as array key.
		return isset( $donation_data['refunds'][ $refund_id ] );
	}

	/**
	 * Add a note to a donation.
	 *
	 * @param int    $donation_id Donation ID.
	 * @param string $note_content Note content.
	 * @param int    $author_id   Author user ID.
	 * @return array{success: bool, note_id: string|null} Result with success status and note ID.
	 * @since 0.0.1
	 */
	public static function add_note( $donation_id, $note_content, $author_id = 0 ) {
		$result = [
			'success' => false,
			'note_id' => null,
		];

		if ( empty( $donation_id ) || empty( $note_content ) ) {
			return $result;
		}

		$donation = self::get( $donation_id );
		if ( ! $donation ) {
			return $result;
		}

		// Get existing donation_data.
		$donation_data = $donation['donation_data'] ?? [];
		if ( is_string( $donation_data ) && ! empty( $donation_data ) ) {
			$donation_data = json_decode( $donation_data, true );
		}
		if ( ! is_array( $donation_data ) ) {
			$donation_data = [];
		}

		// Initialize notes array if not exists.
		if ( ! isset( $donation_data['notes'] ) || ! is_array( $donation_data['notes'] ) ) {
			$donation_data['notes'] = [];
		}

		// Generate unique note ID.
		$note_id = uniqid( 'note_', true );

		// Get author info.
		$author_name = __( 'System', 'suredonation' );
		if ( $author_id > 0 ) {
			$user = get_userdata( $author_id );
			if ( $user ) {
				$author_name = $user->display_name;
			}
		}

		// Add new note.
		$donation_data['notes'][ $note_id ] = [
			'id'          => $note_id,
			'content'     => wp_kses_post( $note_content ),
			'author_id'   => $author_id,
			'author_name' => $author_name,
			'created_at'  => current_time( 'mysql' ),
		];

		// Update donation_data in database.
		$update_result = self::update( $donation_id, [ 'donation_data' => $donation_data ] );

		if ( false !== $update_result ) {
			$result['success'] = true;
			$result['note_id'] = $note_id;
		}

		return $result;
	}

	/**
	 * Get notes for a donation with pagination.
	 *
	 * @param int $donation_id Donation ID.
	 * @param int $page        Current page (1-indexed).
	 * @param int $per_page    Notes per page.
	 * @return array{notes: array<int, array<string, mixed>>, total: int, total_pages: int} Paginated notes.
	 * @since 0.0.1
	 */
	public static function get_notes( $donation_id, $page = 1, $per_page = 3 ) {
		$result = [
			'notes'       => [],
			'total'       => 0,
			'total_pages' => 0,
		];

		if ( empty( $donation_id ) ) {
			return $result;
		}

		$donation = self::get( $donation_id );
		if ( ! $donation ) {
			return $result;
		}

		// Get donation_data and parse if needed.
		$donation_data = $donation['donation_data'] ?? [];
		if ( is_string( $donation_data ) && ! empty( $donation_data ) ) {
			$donation_data = json_decode( $donation_data, true );
		}
		if ( ! is_array( $donation_data ) ) {
			return $result;
		}

		// Get notes array.
		if ( empty( $donation_data['notes'] ) || ! is_array( $donation_data['notes'] ) ) {
			return $result;
		}

		// Convert to array values and sort by created_at (newest first).
		$all_notes = array_values( $donation_data['notes'] );
		usort(
			$all_notes,
			static function ( $a, $b ) {
				return strtotime( $b['created_at'] ?? '0' ) - strtotime( $a['created_at'] ?? '0' );
			}
		);

		$total       = count( $all_notes );
		$total_pages = (int) ceil( $total / $per_page );
		$offset      = ( $page - 1 ) * $per_page;

		// Get paginated notes.
		$notes = array_slice( $all_notes, $offset, $per_page );

		return [
			'notes'       => $notes,
			'total'       => $total,
			'total_pages' => $total_pages,
		];
	}

	/**
	 * Delete a note from a donation.
	 *
	 * @param int    $donation_id Donation ID.
	 * @param string $note_id     Note ID to delete.
	 * @return bool True on success, false on failure.
	 * @since 0.0.1
	 */
	public static function delete_note( $donation_id, $note_id ) {
		if ( empty( $donation_id ) || empty( $note_id ) ) {
			return false;
		}

		$donation = self::get( $donation_id );
		if ( ! $donation ) {
			return false;
		}

		// Get donation_data and parse if needed.
		$donation_data = $donation['donation_data'] ?? [];
		if ( is_string( $donation_data ) && ! empty( $donation_data ) ) {
			$donation_data = json_decode( $donation_data, true );
		}
		if ( ! is_array( $donation_data ) ) {
			return false;
		}

		// Check if note exists.
		if ( empty( $donation_data['notes'] ) || ! isset( $donation_data['notes'][ $note_id ] ) ) {
			return false;
		}

		// Remove the note.
		unset( $donation_data['notes'][ $note_id ] );

		// Update donation_data in database.
		$result = self::update( $donation_id, [ 'donation_data' => $donation_data ] );

		return false !== $result;
	}

	/**
	 * Remove a refund from donation_data.
	 *
	 * Used when a refund is canceled.
	 *
	 * @param int    $donation_id Donation ID.
	 * @param string $refund_id   Refund ID to remove.
	 * @return array{removed: bool, refund_data: array<string, mixed>|null} Result with removed status and refund data.
	 * @since 0.0.1
	 */
	public static function remove_refund_from_donation_data( $donation_id, $refund_id ) {
		$result = [
			'removed'     => false,
			'refund_data' => null,
		];

		if ( empty( $donation_id ) || empty( $refund_id ) ) {
			return $result;
		}

		$donation = self::get( $donation_id );
		if ( ! $donation ) {
			return $result;
		}

		// Get donation_data and parse if needed.
		$donation_data = $donation['donation_data'] ?? [];
		if ( is_string( $donation_data ) && ! empty( $donation_data ) ) {
			$donation_data = json_decode( $donation_data, true );
		}
		if ( ! is_array( $donation_data ) ) {
			return $result;
		}

		// Check if refund exists.
		if ( empty( $donation_data['refunds'] ) || ! isset( $donation_data['refunds'][ $refund_id ] ) ) {
			return $result;
		}

		// Store the refund data before removing.
		$result['refund_data'] = $donation_data['refunds'][ $refund_id ];

		// Remove the refund.
		unset( $donation_data['refunds'][ $refund_id ] );

		// Update donation_data in database.
		$update_result = self::update( $donation_id, [ 'donation_data' => $donation_data ] );

		$result['removed'] = false !== $update_result;

		return $result;
	}
}
