<?php
/**
 * Donor Analytics Repository
 *
 * Repository for pre-calculated donor metrics.
 *
 * @package DonorCore\Database
 */

namespace DonorCore\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Donor Analytics Repository Class
 */
class Donor_Analytics_Repository {

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

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

	/**
	 * Upsert donor analytics.
	 *
	 * @param string $donor_email Donor email.
	 * @param array  $data        Analytics data.
	 * @return bool
	 */
	public function upsert( $donor_email, $data ) {
		global $wpdb;

		$table = $this->get_table_name();

		// Check if record exists.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE donor_email = %s',
				$table,
				$donor_email
			)
		);

		$data['donor_email'] = $donor_email;

		if ( $exists ) {
			// Update existing.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->update(
				$table,
				$data,
				array( 'donor_email' => $donor_email )
			);
		} else {
			// Insert new.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->insert( $table, $data );
		}
	}

	/**
	 * Get analytics for a donor.
	 *
	 * @param string $donor_email Donor email.
	 * @return array|null
	 */
	public function get_by_email( $donor_email ) {
		global $wpdb;

		$table = $this->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE donor_email = %s',
				$table,
				$donor_email
			),
			ARRAY_A
		);
	}

	/**
	 * Update donor lifetime value after a donation.
	 *
	 * @param string $donor_email Donor email.
	 * @param int    $donor_id    Donor user ID.
	 * @param float  $amount      Donation amount.
	 */
	public function record_donation( $donor_email, $donor_id, $amount ) {
		$analytics = $this->get_by_email( $donor_email );

		if ( $analytics ) {
			// Update existing analytics.
			$this->upsert(
				$donor_email,
				array(
					'donor_id'              => $donor_id,
					'total_lifetime_value'  => floatval( $analytics['total_lifetime_value'] ) + $amount,
					'donation_count'        => intval( $analytics['donation_count'] ) + 1,
					'last_donation_date'    => current_time( 'mysql' ),
				)
			);
		} else {
			// Create new analytics record.
			$this->upsert(
				$donor_email,
				array(
					'donor_id'              => $donor_id,
					'total_lifetime_value'  => $amount,
					'donation_count'        => 1,
					'first_donation_date'   => current_time( 'mysql' ),
					'last_donation_date'    => current_time( 'mysql' ),
				)
			);
		}
	}

	/**
	 * Get top donors by lifetime value.
	 *
	 * @param int $limit Limit results.
	 * @return array
	 */
	public function get_top_donors( $limit = 10 ) {
		global $wpdb;

		$table = $this->get_table_name();

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

	/**
	 * Get total donor count.
	 *
	 * @return int
	 */
	public function get_total_donor_count() {
		global $wpdb;

		$table = $this->get_table_name();

		return intval(
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i',
					$table
				)
			)
		);
	}

	/**
	 * Anonymize donor data (GDPR compliance).
	 *
	 * @param string $donor_email Donor email to anonymize.
	 * @return bool
	 */
	public function anonymize_donor( $donor_email ) {
		global $wpdb;

		$table = $this->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->update(
			$table,
			array(
				'donor_email' => 'anonymized-' . wp_generate_password( 12, false ),
			),
			array( 'donor_email' => $donor_email )
		);
	}

	/**
	 * Recalculate analytics for a donor from donation logs.
	 *
	 * @param string $donor_email Donor email.
	 * @return bool
	 */
	public function recalculate_for_donor( $donor_email ) {
		global $wpdb;

		// Get donor user ID.
		$user = get_user_by( 'email', $donor_email );
		if ( ! $user ) {
			return false;
		}

		$donor_id = $user->ID;
		$log_table = $wpdb->prefix . 'donorcore_donation_logs';

		// Calculate aggregates from logs.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$stats = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT 
					SUM(amount) as total,
					COUNT(*) as count,
					MIN(created_at) as first_date,
					MAX(created_at) as last_date
				FROM %i
				WHERE donor_id = %d",
				$log_table,
				$donor_id
			),
			ARRAY_A
		);

		if ( ! $stats || ! $stats['total'] ) {
			return false;
		}

		return $this->upsert(
			$donor_email,
			array(
				'donor_id'              => $donor_id,
				'total_lifetime_value'  => floatval( $stats['total'] ),
				'donation_count'        => intval( $stats['count'] ),
				'first_donation_date'   => $stats['first_date'],
				'last_donation_date'    => $stats['last_date'],
			)
		);
	}
}
