<?php
/**
 * Donation Log Repository
 *
 * Repository pattern for interacting with the donation logs table.
 *
 * @package DonorCore\Database
 */

namespace DonorCore\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Donation Log Repository Class
 */
class Donation_Log_Repository {

	/**
	 * Table name (without prefix).
	 */
	const TABLE = 'donorcore_donation_logs';

	/**
	 * Get table name with prefix.
	 *
	 * @return string
	 */
	private function get_table_name() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * Insert a new donation log.
	 *
	 * @param array $data Donation data.
	 * @return int|false Insert ID or false on failure.
	 */
	public function insert( $data ) {
		global $wpdb;

		$defaults = array(
			'order_id'    => 0,
			'campaign_id' => 0,
			'donor_id'    => 0,
			'amount'      => 0.00,
			'currency'    => get_woocommerce_currency(),
			'type'        => 'one-time',
			'gateway'     => 'unknown',
			'created_at'  => current_time( 'mysql' ),
		);

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

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Insert record into custom plugin table
		$result = $wpdb->insert(
			$this->get_table_name(),
			$data,
			array(
				'%d', // order_id
				'%d', // campaign_id
				'%d', // donor_id
				'%f', // amount
				'%s', // currency
				'%s', // type
				'%s', // gateway
				'%s', // created_at
			)
		);

		return $result ? $wpdb->insert_id : false;
	}

	/**
	 * Get donation logs by campaign ID.
	 *
	 * @param int $campaign_id Campaign ID.
	 * @param int $limit       Limit results.
	 * @return array
	 */
	public function get_by_campaign( $campaign_id, $limit = 100 ) {
		global $wpdb;

		$table = $this->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query custom plugin table
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE campaign_id = %d ORDER BY created_at DESC LIMIT %d',
				$table,
				$campaign_id,
				$limit
			),
			ARRAY_A
		);
	}

	/**
	 * Get total donations for a campaign.
	 *
	 * @param int $campaign_id Campaign ID.
	 * @return float
	 */
	public function get_campaign_total( $campaign_id ) {
		// Try cache first.
		$cache_key = 'donorcore_campaign_total_' . $campaign_id;
		$total     = get_transient( $cache_key );

		if ( false !== $total ) {
			return floatval( $total );
		}

		// Calculate from database.
		global $wpdb;
		$table = $this->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT SUM(amount) FROM %i WHERE campaign_id = %d',
				$table,
				$campaign_id
			)
		);

		$total = $total ? floatval( $total ) : 0;

		// Cache for 30 minutes.
		set_transient( $cache_key, $total, 1800 );

		return $total;
	}

	/**
	 * Get donation logs by donor email.
	 *
	 * @param string $donor_email Donor email address.
	 * @return array
	 */
	public function get_by_donor_email( $donor_email ) {
		global $wpdb;
		$table = $this->get_table_name();

		// Get all donor IDs associated with this email
		$donor_ids = $this->get_donor_ids_by_email( $donor_email );

		if ( empty( $donor_ids ) ) {
			return array();
		}

		// Get all donation records for these donor IDs
		$placeholders = implode( ',', array_fill( 0, count( $donor_ids ), '%d' ) );
		$query_args   = array_merge( array( $table ), $donor_ids );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		$results = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				"SELECT * FROM %i WHERE donor_id IN ($placeholders) ORDER BY created_at DESC",
				...$query_args
			),
			ARRAY_A
		);

		return $results ?: array();
	}

	/**
	 * Helper: Get all donor IDs (WordPress user IDs) by email.
	 * Checks both WordPress users and WooCommerce order billing emails.
	 *
	 * @param string $email Donor email.
	 * @return array Array of donor IDs.
	 */
	public function get_donor_ids_by_email( $email ) {
		$donor_ids = array();

		// Get WordPress user ID if exists
		$user = get_user_by( 'email', $email );
		if ( $user ) {
			$donor_ids[] = $user->ID;
		}

		// Get all order IDs with this billing email
		$order_ids = wc_get_orders( array(
			'billing_email' => $email,
			'return'        => 'ids',
			'limit'         => -1,
		) );

		// Extract customer IDs from orders
		foreach ( $order_ids as $order_id ) {
			$order = wc_get_order( $order_id );
			if ( $order ) {
				$customer_id = $order->get_customer_id();
				if ( $customer_id > 0 ) {
					$donor_ids[] = intval( $customer_id );
				}
			}
		}

		$unique_ids = array_unique( $donor_ids );

		return $unique_ids;
	}

	/**
	 * Get aggregate statistics for date range.
	 *
	 * @param string $start_date Start date (Y-m-d).
	 * @param string $end_date   End date (Y-m-d).
	 * @return array
	 */
	public function get_stats_by_date_range( $start_date, $end_date ) {
		global $wpdb;

		$table = $this->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT 
					COUNT(*) as donation_count,
					SUM(amount) as total_amount,
					AVG(amount) as average_amount,
					COUNT(DISTINCT donor_id) as unique_donors
				FROM %i
				WHERE created_at BETWEEN %s AND %s',
				$table,
				$start_date . ' 00:00:00',
				$end_date . ' 23:59:59'
			),
			ARRAY_A
		);
	}

	/**
	 * Get donations grouped by date for charting.
	 *
	 * @param int $days Number of days back.
	 * @return array
	 */
	public function get_donations_by_day( $days = 30 ) {
		global $wpdb;

		$table = $this->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT 
					DATE(created_at) as date,
					COUNT(*) as count,
					SUM(amount) as total
				FROM %i
				WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
				GROUP BY DATE(created_at)
				ORDER BY date ASC',
				$table,
				$days
			),
			ARRAY_A
		);
	}
}
