<?php
/**
 * CRUD operations for the donations table.
 *
 * @package Donatico\Database
 */

declare(strict_types=1);

namespace Donatico\Database;

use Donatico\Models\Donation;

/**
 * All database reads and writes for donations go through this class.
 * Uses $wpdb exclusively; never raw PDO/mysqli.
 */
class DonationRepository {

	/** @var \wpdb */
	private \wpdb $db;

	/** @var string Fully-qualified table name with prefix. */
	private string $table;

	public function __construct() {
		global $wpdb;
		$this->db    = $wpdb;
		$this->table = Schema::donationsTable();
	}

	// -------------------------------------------------------------------------
	// Reads
	// -------------------------------------------------------------------------

	/**
	 * Fetch a single donation by primary key.
	 *
	 * @param int $id
	 * @return Donation|null
	 */
	public function find( int $id ): ?Donation {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $this->db->get_row(
			$this->db->prepare( "SELECT * FROM {$this->table} WHERE id = %d", $id ),
			ARRAY_A
		);

		return $row ? Donation::fromArray( $row ) : null;
	}

	/**
	 * Fetch a single donation by Stripe session ID.
	 *
	 * @param string $sessionId
	 * @return Donation|null
	 */
	public function findByStripeSessionId( string $sessionId ): ?Donation {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $this->db->get_row(
			$this->db->prepare( "SELECT * FROM {$this->table} WHERE stripe_session_id = %s", $sessionId ),
			ARRAY_A
		);

		return $row ? Donation::fromArray( $row ) : null;
	}

	/**
	 * Fetch a single donation by its Stripe invoice ID.
	 * Used to prevent double-insertion when invoice.paid replays.
	 *
	 * @param string $invoiceId Stripe invoice ID (in_xxx).
	 * @return Donation|null
	 */
	public function findByStripeInvoiceId( string $invoiceId ): ?Donation {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $this->db->get_row(
			$this->db->prepare( "SELECT * FROM {$this->table} WHERE stripe_invoice_id = %s", $invoiceId ),
			ARRAY_A
		);

		return $row ? Donation::fromArray( $row ) : null;
	}

	/**
	 * Fetch a single donation by its Stripe PaymentIntent ID.
	 * Used by the charge.refunded and payment_intent.payment_failed webhooks.
	 *
	 * @param string $paymentIntentId
	 * @return Donation|null
	 */
	public function findByStripePaymentIntentId( string $paymentIntentId ): ?Donation {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $this->db->get_row(
			$this->db->prepare( "SELECT * FROM {$this->table} WHERE stripe_payment_intent_id = %s", $paymentIntentId ),
			ARRAY_A
		);

		return $row ? Donation::fromArray( $row ) : null;
	}

	/**
	 * Return a paginated list of donations.
	 *
	 * Supported $args keys:
	 *   status  string   Filter by status constant.
	 *   search  string   Partial match on donor_name or donor_email.
	 *   orderby string   Column name (validated against allowlist).
	 *   order   string   ASC or DESC.
	 *   limit   int      Rows per page.
	 *   offset  int      Row offset for pagination.
	 *
	 * @param array<string, mixed> $args
	 * @return Donation[]
	 */
	public function findAll( array $args = array() ): array {
		list( $where, $values ) = $this->buildWhere( $args );

		$orderby = $this->sanitizeOrderby( $args['orderby'] ?? 'created_at' );
		$order   = strtoupper( $args['order'] ?? 'DESC' ) === 'ASC' ? 'ASC' : 'DESC';
		$limit   = max( 1, (int) ( $args['limit'] ?? 20 ) );
		$offset  = max( 0, (int) ( $args['offset'] ?? 0 ) );

		$sql = "SELECT * FROM {$this->table}{$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

		$query_args   = array_merge( $values, array( $limit, $offset ) );
		$prepared_sql = $this->db->prepare( $sql, $query_args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $this->db->get_results( $prepared_sql, ARRAY_A );

		if ( ! $rows ) {
			return array();
		}

		return array_map( static fn( array $row ) => Donation::fromArray( $row ), $rows );
	}

	/**
	 * Count donations matching the given filters (same args as findAll).
	 *
	 * @param array<string, mixed> $args
	 * @return int
	 */
	public function count( array $args = array() ): int {
		list( $where, $values ) = $this->buildWhere( $args );

		$sql = "SELECT COUNT(*) FROM {$this->table}{$where}";

		if ( $values ) {
			$sql = $this->db->prepare( $sql, $values ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $this->db->get_var( $sql );
	}

	/**
	 * Aggregate statistics for the dashboard.
	 *
	 * @return array{total_count: int, total_amount: int, avg_amount: float, monthly: list<array<string, mixed>>}
	 */
	public function getStats(): array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$summary = $this->db->get_row(
			$this->db->prepare(
				"SELECT COUNT(*) AS total_count, COALESCE(SUM(amount),0) AS total_amount, COALESCE(AVG(amount),0) AS avg_amount
				 FROM {$this->table} WHERE status = %s",
				Donation::STATUS_COMPLETED
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$monthly = $this->db->get_results(
			$this->db->prepare(
				"SELECT DATE_FORMAT(created_at, '%%Y-%%m') AS month,
				        COUNT(*) AS count,
				        COALESCE(SUM(amount), 0) AS amount
				 FROM {$this->table}
				 WHERE status = %s
				   AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
				 GROUP BY month
				 ORDER BY month ASC",
				Donation::STATUS_COMPLETED
			),
			ARRAY_A
		);

		return array(
			'total_count'  => (int) ( $summary['total_count'] ?? 0 ),
			'total_amount' => (int) ( $summary['total_amount'] ?? 0 ),
			'avg_amount'   => (float) ( $summary['avg_amount'] ?? 0 ),
			'monthly'      => $monthly ?: array(),
		);
	}

	// -------------------------------------------------------------------------
	// Writes
	// -------------------------------------------------------------------------

	/**
	 * Insert a new donation and return its new ID, or 0 on failure.
	 *
	 * @param Donation $donation
	 * @return int New row ID, or 0 on failure.
	 */
	public function insert( Donation $donation ): int {
		$data = $donation->toArray();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $this->db->insert( $this->table, $data, $this->getFormats( $data ) );

		return $result === false ? 0 : (int) $this->db->insert_id;
	}

	/**
	 * Update arbitrary columns on a donation row.
	 *
	 * @param int                  $id
	 * @param array<string, mixed> $data Column => value pairs.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $this->db->update(
			$this->table,
			$data,
			array( 'id' => $id ),
			$this->getFormats( $data ),
			array( '%d' )
		);
	}

	/**
	 * Update a donation row identified by its Stripe session ID.
	 * Used when the webhook arrives before we know the row's integer PK.
	 *
	 * @param string               $sessionId
	 * @param array<string, mixed> $data
	 * @return bool
	 */
	public function updateByStripeSessionId( string $sessionId, array $data ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $this->db->update(
			$this->table,
			$data,
			array( 'stripe_session_id' => $sessionId ),
			$this->getFormats( $data ),
			array( '%s' )
		);
	}

	/**
	 * Hard-delete a donation row.
	 *
	 * @param int $id
	 * @return bool
	 */
	public function delete( int $id ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $this->db->delete( $this->table, array( 'id' => $id ), array( '%d' ) );
	}

	// -------------------------------------------------------------------------
	// Helpers
	// -------------------------------------------------------------------------

	/**
	 * Build a WHERE clause and corresponding prepared values from $args.
	 *
	 * @param array<string, mixed> $args
	 * @return array{string, list<mixed>}
	 */
	private function buildWhere( array $args ): array {
		$clauses = array();
		$values  = array();

		if ( ! empty( $args['status'] ) && in_array( $args['status'], Donation::STATUSES, true ) ) {
			$clauses[] = 'status = %s';
			$values[]  = $args['status'];
		}

		if ( ! empty( $args['search'] ) ) {
			$clauses[] = '(donor_name LIKE %s OR donor_email LIKE %s)';
			$like      = '%' . $this->db->esc_like( sanitize_text_field( $args['search'] ) ) . '%';
			$values[]  = $like;
			$values[]  = $like;
		}

		$where = $clauses ? ' WHERE ' . implode( ' AND ', $clauses ) : '';

		return array( $where, $values );
	}

	/**
	 * Validate the orderby column against an allowlist to prevent SQL injection.
	 *
	 * @param string $orderby
	 * @return string
	 */
	private function sanitizeOrderby( string $orderby ): string {
		$allowed = array( 'id', 'donor_name', 'donor_email', 'amount', 'status', 'created_at' );
		return in_array( $orderby, $allowed, true ) ? $orderby : 'created_at';
	}

	/**
	 * Map column names to $wpdb format strings (%d for ints, %s for everything else).
	 *
	 * @param array<string, mixed> $data
	 * @return list<string>
	 */
	private function getFormats( array $data ): array {
		$int_columns = array( 'amount', 'is_anonymous', 'is_renewal', 'subscription_id', 'tier_id' );
		$formats     = array();

		foreach ( array_keys( $data ) as $col ) {
			$formats[] = in_array( $col, $int_columns, true ) ? '%d' : '%s';
		}

		return $formats;
	}
}
