<?php
/**
 * SureDonation Database Donors 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 Donors Table Class.
 *
 * @since 0.0.1
 */
class Donors extends Base {
	use Get_Instance;

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

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

	/**
	 * Valid donor statuses.
	 *
	 * @var array<string>
	 * @since 0.0.1
	 */
	private static $valid_statuses = [
		'active',
		'inactive',
		'blocked',
	];

	/**
	 * Valid order columns.
	 *
	 * @var array<string>
	 * @since 0.0.1
	 */
	private static $valid_order_columns = [
		'id',
		'email',
		'name',
		'total_donated',
		'donation_count',
		'created_at',
		'updated_at',
		'last_donation_date',
	];

	/**
	 * {@inheritDoc}
	 */
	public function get_schema() {
		return [
			'id'                  => [
				'type' => 'number',
			],
			'email'               => [
				'type' => 'string',
			],
			'name'                => [
				'type'    => 'string',
				'default' => '',
			],
			'phone'               => [
				'type'    => 'string',
				'default' => '',
			],
			'user_id'             => [
				'type'    => 'number',
				'default' => 0,
			],
			'total_donated'       => [
				'type'    => 'decimal',
				'default' => 0,
			],
			'donation_count'      => [
				'type'    => 'number',
				'default' => 0,
			],
			'largest_donation'    => [
				'type'    => 'decimal',
				'default' => 0,
			],
			'first_donation_date' => [
				'type' => 'datetime',
			],
			'last_donation_date'  => [
				'type' => 'datetime',
			],
			'donor_tags'          => [
				'type'    => 'array',
				'default' => [],
			],
			'donor_status'        => [
				'type'    => 'string',
				'default' => 'active',
			],
			'donor_data'          => [
				'type'    => 'array',
				'default' => [],
			],
			'stripe_customer_id'  => [
				'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',
			'email VARCHAR(255) NOT NULL UNIQUE',
			'name VARCHAR(255) NOT NULL',
			'phone VARCHAR(50) NOT NULL',
			'user_id BIGINT(20) UNSIGNED NULL',
			'total_donated DECIMAL(26,8) NOT NULL DEFAULT 0',
			'donation_count INT(11) NOT NULL DEFAULT 0',
			'largest_donation DECIMAL(26,8) NOT NULL DEFAULT 0',
			'first_donation_date TIMESTAMP NULL',
			'last_donation_date TIMESTAMP NULL',
			'donor_tags LONGTEXT',
			'donor_status VARCHAR(20) NOT NULL',
			'donor_data LONGTEXT',
			'stripe_customer_id VARCHAR(255) DEFAULT NULL',
			'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
			'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			'INDEX idx_email (email)',
			'INDEX idx_user (user_id)',
			'INDEX idx_total (total_donated)',
			'INDEX idx_status (donor_status)',
		];
	}

	/**
	 * Add a new donor record.
	 *
	 * @param array<mixed> $data Donor data to insert.
	 * @return int|false The donor ID on success, false on error.
	 * @since 0.0.1
	 */
	public static function add( $data ) {
		if ( empty( $data['email'] ) ) {
			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 donor record.
	 *
	 * @param int                 $donor_id Donor 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( $donor_id, $data = [] ) {
		if ( empty( $donor_id ) ) {
			return false;
		}

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

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

	/**
	 * Get a single donor by ID.
	 *
	 * @param int $donor_id Donor ID.
	 * @return array<mixed>|null Donor data or null if not found.
	 * @since 0.0.1
	 */
	public static function get( $donor_id ) {
		if ( empty( $donor_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( $donor_id )
			),
			ARRAY_A
		);

		if ( ! $result ) {
			return null;
		}

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

	/**
	 * Get donor by email.
	 *
	 * @param string $email Donor email.
	 * @return array<mixed>|null Donor data or null if not found.
	 * @since 0.0.1
	 */
	public static function get_by_email( $email ) {
		if ( empty( $email ) ) {
			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 email = %s',
				$instance->get_tablename(),
				sanitize_email( $email )
			),
			ARRAY_A
		);

		if ( ! $result ) {
			return null;
		}

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

	/**
	 * Get all donors 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 donors.
	 * @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 donors by status with pagination.
	 *
	 * @param string $status  Donor 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 donors.
	 * @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 donor_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 donor_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 );
	}

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

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

	/**
	 * Get or create donor by email.
	 *
	 * @param string $email Donor email.
	 * @param string $name  Donor name.
	 * @param string $phone Donor phone.
	 * @return int|false Donor ID or false on error.
	 * @since 0.0.1
	 */
	public static function get_or_create( $email, $name = '', $phone = '' ) {
		if ( empty( $email ) ) {
			return false;
		}

		$existing = self::get_by_email( $email );

		if ( $existing ) {
			// Update name/phone if provided and different.
			$updates = [];

			if ( ! empty( $name ) && $name !== $existing['name'] ) {
				$updates['name'] = $name;
			}

			if ( ! empty( $phone ) && $phone !== $existing['phone'] ) {
				$updates['phone'] = $phone;
			}

			if ( ! empty( $updates ) && isset( $existing['id'] ) ) {
				$existing_id = is_numeric( $existing['id'] ) ? (int) $existing['id'] : 0;
				if ( $existing_id > 0 ) {
					self::update( $existing_id, $updates );
				}
			}

			$existing_id = isset( $existing['id'] ) && is_numeric( $existing['id'] ) ? (int) $existing['id'] : 0;
			return $existing_id > 0 ? $existing_id : false;
		}

		// Create new donor.
		return self::add(
			[
				'email'               => sanitize_email( $email ),
				'name'                => sanitize_text_field( $name ),
				'phone'               => sanitize_text_field( $phone ),
				'first_donation_date' => current_time( 'mysql' ),
			]
		);
	}

	/**
	 * Update donor statistics after a donation.
	 *
	 * @param int   $donor_id Donor ID.
	 * @param float $amount   Donation amount.
	 * @return int|false Number of rows updated or false on error.
	 * @since 0.0.1
	 */
	public static function record_donation( $donor_id, $amount ) {
		if ( empty( $donor_id ) || $amount <= 0 ) {
			return false;
		}

		$donor = self::get( $donor_id );

		if ( ! $donor ) {
			return false;
		}

		$total_value     = $donor['total_donated'] ?? 0;
		$current_total   = is_numeric( $total_value ) ? (float) $total_value : 0.0;
		$count_value     = $donor['donation_count'] ?? 0;
		$current_count   = is_numeric( $count_value ) ? (int) $count_value : 0;
		$largest_value   = $donor['largest_donation'] ?? 0;
		$current_largest = is_numeric( $largest_value ) ? (float) $largest_value : 0.0;

		$updates = [
			'total_donated'      => $current_total + $amount,
			'donation_count'     => $current_count + 1,
			'last_donation_date' => current_time( 'mysql' ),
		];

		if ( $amount > $current_largest ) {
			$updates['largest_donation'] = $amount;
		}

		return self::update( $donor_id, $updates );
	}

	/**
	 * Get top donors by total donated.
	 *
	 * @param int $limit Number of donors to retrieve.
	 * @return array<int, array<string, mixed>> Array of top donors.
	 * @since 0.0.1
	 */
	public static function get_top_donors( $limit = 10 ) {
		$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_status = %s ORDER BY total_donated DESC LIMIT %d',
				$instance->get_tablename(),
				'active',
				absint( $limit )
			),
			ARRAY_A
		);

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

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

	/**
	 * Get total donors 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 donors count by status.
	 *
	 * @param string $status Donor status ('all' for no filter).
	 * @return int Total count.
	 * @since 0.0.1
	 */
	public static function get_total_donors( $status = 'all' ) {
		$instance = self::get_instance();
		global $wpdb;

		if ( 'all' === $status ) {
			return self::count_all();
		}

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

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

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

	/**
	 * Get Stripe customer ID for a donor by email.
	 *
	 * @param string $email Donor email.
	 * @return string|null Stripe customer ID or null if not found.
	 * @since 0.0.1
	 */
	public static function get_stripe_customer_id_by_email( $email ) {
		if ( empty( $email ) ) {
			return null;
		}

		$donor = self::get_by_email( $email );

		if ( $donor && ! empty( $donor['stripe_customer_id'] ) && is_string( $donor['stripe_customer_id'] ) ) {
			return $donor['stripe_customer_id'];
		}

		return null;
	}

	/**
	 * Update Stripe customer ID for a donor by email.
	 *
	 * @param string $email              Donor email.
	 * @param string $stripe_customer_id Stripe customer ID.
	 * @return bool True on success, false on failure.
	 * @since 0.0.1
	 */
	public static function set_stripe_customer_id_by_email( $email, $stripe_customer_id ) {
		if ( empty( $email ) || empty( $stripe_customer_id ) ) {
			return false;
		}

		$donor = self::get_by_email( $email );

		if ( ! $donor || empty( $donor['id'] ) ) {
			return false;
		}

		$donor_id = is_numeric( $donor['id'] ) ? (int) $donor['id'] : 0;
		if ( $donor_id <= 0 ) {
			return false;
		}

		$result = self::update( $donor_id, [ 'stripe_customer_id' => sanitize_text_field( $stripe_customer_id ) ] );

		return false !== $result;
	}

	/**
	 * Clear Stripe customer ID for a donor by email.
	 *
	 * This is used when a cached customer ID is no longer valid
	 * (e.g., customer was deleted from Stripe or mode switched).
	 *
	 * @param string $email Donor email.
	 * @return bool True on success, false on failure.
	 * @since 0.0.1
	 */
	public static function clear_stripe_customer_id_by_email( $email ) {
		if ( empty( $email ) ) {
			return false;
		}

		$donor = self::get_by_email( $email );

		if ( ! $donor || empty( $donor['id'] ) ) {
			return false;
		}

		$donor_id = is_numeric( $donor['id'] ) ? (int) $donor['id'] : 0;
		if ( $donor_id <= 0 ) {
			return false;
		}

		$result = self::update( $donor_id, [ 'stripe_customer_id' => '' ] );

		return false !== $result;
	}
}
