<?php
/**
 * Points Repository.
 *
 * Handles CRUD operations for the wp_ugcify_points table.
 *
 * @package Ugcify\Database
 */

namespace Ugcify\Database;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class PointsRepository
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- All queries use $wpdb->prepare(); table name is a trusted internal constant set in constructor.
 */
class PointsRepository {

	/**
	 * WordPress database instance.
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Table name.
	 *
	 * @var string
	 */
	private $table;

	/**
	 * Constructor.
	 *
	 * @param \wpdb|null $wpdb WordPress database instance. Falls back to global.
	 */
	public function __construct( ?\wpdb $wpdb = null ) {
		if ( null === $wpdb ) {
			global $wpdb;
		}
		$this->wpdb  = $wpdb;
		$this->table = $this->wpdb->prefix . 'ugcify_points';
	}

	/**
	 * Insert a points transaction row.
	 *
	 * Uses an advisory lock to serialise concurrent writes per user.
	 * Duplicate prevention is handled via reference_key (unique index).
	 *
	 * @param int    $user_id     WordPress user ID.
	 * @param int    $points      Points to credit (positive) or debit (negative).
	 * @param string $action      Machine-readable action slug, e.g. 'photo_approved'.
	 * @param string $description Human-readable description for the ledger UI.
	 * @param int    $review_id   Optional review ID to link. 0 if not applicable.
	 * @param array  $extra       Optional extra fields: transaction_type, source, reference_key, meta, created_by, status.
	 * @return int|false Inserted row ID on success, false on failure.
	 */
	public function insert( int $user_id, int $points, string $action, string $description, int $review_id = 0, array $extra = array() ) {
		// Acquire advisory lock scoped to this user to serialise concurrent writes.
		$lock_name = 'ugcify_points_user_' . $user_id;
		$locked    = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT GET_LOCK(%s, 5)', $lock_name ) );

		if ( '1' !== (string) $locked ) {
			// Could not acquire lock within 5 seconds — bail to avoid duplicate.
			return false;
		}

		try {
			// If a reference_key is provided, use it for deduplication (preferred).
			$reference_key = isset( $extra['reference_key'] ) ? sanitize_text_field( $extra['reference_key'] ) : null;
			if ( $reference_key && $this->has_reference_key( $reference_key ) ) {
				return false;
			}

			// Legacy fallback: if no reference_key and review_id is given, check by review_id + action.
			if ( ! $reference_key && $review_id > 0 && $this->has_been_credited_for_review( $review_id, $action ) ) {
				return false;
			}

			// Compute balance_after atomically while inside the lock.
			$current_balance = $this->get_balance( $user_id );
			$balance_after   = max( 0, $current_balance + $points );

			$data = array(
				'user_id'          => $user_id,
				'review_id'        => $review_id > 0 ? $review_id : null,
				'points'           => $points,
				'action'           => sanitize_key( $action ),
				'description'      => sanitize_text_field( $description ),
				'transaction_type' => sanitize_key( $extra['transaction_type'] ?? ( $points >= 0 ? 'earn' : 'redeem' ) ),
				'source'           => sanitize_text_field( $extra['source'] ?? '' ),
				'reference_key'    => $reference_key,
				'balance_after'    => $balance_after,
				'meta'             => isset( $extra['meta'] ) ? wp_json_encode( $extra['meta'] ) : null,
				'created_by'       => isset( $extra['created_by'] ) ? absint( $extra['created_by'] ) : null,
				'status'           => sanitize_key( $extra['status'] ?? 'completed' ),
				'created_at'       => current_time( 'mysql' ),
			);

			$format = array( '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s' );

			$result = $this->wpdb->insert( $this->table, $data, $format );

			return false !== $result ? (int) $this->wpdb->insert_id : false;

		} finally {
			$this->wpdb->query( $this->wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
		}
	}

	/**
	 * Get the total points balance for a user (sum of all transactions).
	 *
	 * @param int $user_id WordPress user ID.
	 * @return int Balance (may be 0, never negative — floor at 0).
	 */
	public function get_balance( int $user_id ): int {
		if ( function_exists( 'apply_filters' ) ) {
			$pre = apply_filters( 'ugcify_points_balance_pre', null, $user_id );
			if ( null !== $pre ) {
				return max( 0, (int) $pre );
			}
		}

		$balance = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COALESCE( SUM( points ), 0 ) FROM {$this->table} WHERE user_id = %d",
				$user_id
			)
		);

		return max( 0, (int) $balance );
	}

	/**
	 * Get all transaction rows for a user, ordered newest-first.
	 *
	 * @param int $user_id WordPress user ID.
	 * @param int $limit   Max rows to return. Default 50.
	 * @param int $offset  Pagination offset. Default 0.
	 * @return array Array of row objects.
	 */
	public function get_ledger( int $user_id, int $limit = 50, int $offset = 0 ): array {
		$results = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT id, user_id, review_id, points, action, description,
						transaction_type, source, reference_key, balance_after,
						meta, created_by, status, created_at
				FROM {$this->table}
				WHERE user_id = %d
				ORDER BY created_at DESC, id DESC
				LIMIT %d OFFSET %d",
				$user_id,
				$limit,
				$offset
			)
		);

		return is_array( $results ) ? $results : array();
	}

	/**
	 * Check whether a reward has already been credited for a specific review.
	 *
	 * When $action is provided, only checks for that specific action (e.g. distinguishing
	 * approval points from featured bonus for the same review).
	 *
	 * @param int    $review_id Review ID.
	 * @param string $action    Optional action slug to scope the check.
	 * @return bool
	 */
	public function has_been_credited_for_review( int $review_id, string $action = '' ): bool {
		if ( $action ) {
			$count = $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->table} WHERE review_id = %d AND action = %s AND points > 0",
					$review_id,
					$action
				)
			);
		} else {
			$count = $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->table} WHERE review_id = %d AND points > 0",
					$review_id
				)
			);
		}

		return (int) $count > 0;
	}

	/**
	 * Check whether a reference_key already exists in the ledger.
	 *
	 * @param string $reference_key The unique reference key.
	 * @return bool
	 */
	public function has_reference_key( string $reference_key ): bool {
		$count = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table} WHERE reference_key = %s",
				$reference_key
			)
		);

		return (int) $count > 0;
	}

	/**
	 * Insert a points row directly — no advisory lock, no duplicate check.
	 *
	 * Used by the recalculator which handles idempotency at a higher level
	 * (delete-all-for-user then reinserting from scratch).
	 *
	 * @param int    $user_id     WordPress user ID.
	 * @param int    $points      Points to credit.
	 * @param string $action      Machine-readable action slug.
	 * @param string $description Human-readable description.
	 * @param int    $review_id   Optional review ID. Default 0.
	 * @param array  $extra       Optional extra fields: transaction_type, source, reference_key, etc.
	 * @return int|false Inserted row ID on success, false on failure.
	 */
	public function insert_direct( int $user_id, int $points, string $action, string $description, int $review_id = 0, array $extra = array() ) {
		$data = array(
			'user_id'          => $user_id,
			'review_id'        => $review_id > 0 ? $review_id : null,
			'points'           => $points,
			'action'           => sanitize_key( $action ),
			'description'      => sanitize_text_field( $description ),
			'transaction_type' => sanitize_key( $extra['transaction_type'] ?? ( $points >= 0 ? 'earn' : 'redeem' ) ),
			'source'           => sanitize_text_field( $extra['source'] ?? '' ),
			'reference_key'    => isset( $extra['reference_key'] ) ? sanitize_text_field( $extra['reference_key'] ) : null,
			'balance_after'    => absint( $extra['balance_after'] ?? 0 ),
			'meta'             => isset( $extra['meta'] ) ? wp_json_encode( $extra['meta'] ) : null,
			'created_by'       => isset( $extra['created_by'] ) ? absint( $extra['created_by'] ) : null,
			'status'           => sanitize_key( $extra['status'] ?? 'completed' ),
			'created_at'       => current_time( 'mysql' ),
		);

		$format = array( '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s' );

		$result = $this->wpdb->insert( $this->table, $data, $format );

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

	/**
	 * Delete all point transactions linked to a specific review.
	 *
	 * @param int $review_id Review ID.
	 * @return int Number of rows deleted.
	 */
	public function delete_by_review( int $review_id ): int {
		$deleted = $this->wpdb->delete(
			$this->table,
			array( 'review_id' => $review_id ),
			array( '%d' )
		);

		return false !== $deleted ? (int) $deleted : 0;
	}

	/**
	 * Delete all point transactions for a specific user.
	 *
	 * Used by the recalculator to clear stale data before recomputing.
	 *
	 * @param int $user_id WordPress user ID.
	 * @return int Number of rows deleted.
	 */
	public function delete_all_for_user( int $user_id ): int {
		$deleted = $this->wpdb->delete(
			$this->table,
			array( 'user_id' => $user_id ),
			array( '%d' )
		);

		return false !== $deleted ? (int) $deleted : 0;
	}

	/**
	 * Get distinct user IDs that have approved reviews, paginated.
	 *
	 * @param int $limit  Batch size.
	 * @param int $offset Offset.
	 * @return int[] Array of user IDs.
	 */
	public function get_reviewers_with_approved_reviews( int $limit = 50, int $offset = 0 ): array {
		global $wpdb;
		$reviews_table = $wpdb->prefix . 'ugcify_reviews';

		$results = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT user_id FROM {$reviews_table} WHERE user_id > 0 AND status = 'approved' ORDER BY user_id ASC LIMIT %d OFFSET %d",
				$limit,
				$offset
			)
		);

		return array_map( 'absint', $results );
	}

	/**
	 * Get all approved reviews for a user.
	 *
	 * @param int $user_id WordPress user ID.
	 * @return array Array of review row objects.
	 */
	public function get_approved_reviews_for_user( int $user_id ): array {
		global $wpdb;
		$reviews_table = $wpdb->prefix . 'ugcify_reviews';

		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, type, is_featured FROM {$reviews_table} WHERE user_id = %d AND status = 'approved' ORDER BY created_at ASC",
				$user_id
			)
		);

		return is_array( $results ) ? $results : array();
	}

	/**
	 * Get total earned points for a user (sum of positive transactions).
	 *
	 * @param int $user_id WordPress user ID.
	 * @return int
	 */
	public function get_total_earned( int $user_id ): int {
		$total = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COALESCE( SUM( points ), 0 ) FROM {$this->table} WHERE user_id = %d AND points > 0 AND status = 'completed'",
				$user_id
			)
		);

		return max( 0, (int) $total );
	}

	/**
	 * Get total used/redeemed points for a user (sum of negative transactions as positive value).
	 *
	 * @param int $user_id WordPress user ID.
	 * @return int
	 */
	public function get_total_used( int $user_id ): int {
		$total = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COALESCE( ABS( SUM( points ) ), 0 ) FROM {$this->table} WHERE user_id = %d AND points < 0 AND status = 'completed'",
				$user_id
			)
		);

		return max( 0, (int) $total );
	}

	/**
	 * Get full reward summary from the ledger for a user.
	 *
	 * @param int $user_id WordPress user ID.
	 * @return array { total_earned: int, total_used: int, available_balance: int }
	 */
	public function get_reward_summary( int $user_id ): array {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT
					COALESCE( SUM( CASE WHEN points > 0 THEN points ELSE 0 END ), 0 ) AS total_earned,
					COALESCE( ABS( SUM( CASE WHEN points < 0 AND transaction_type = 'redeem' THEN points ELSE 0 END ) ), 0 ) AS total_used,
					COALESCE( SUM( points ), 0 ) AS available_balance
				FROM {$this->table}
				WHERE user_id = %d AND status = 'completed'",
				$user_id
			),
			ARRAY_A
		);

		$total_earned = (int) ( $row['total_earned'] ?? 0 );
		$total_used   = (int) ( $row['total_used'] ?? 0 );
		$balance      = (int) ( $row['available_balance'] ?? 0 );

		return array(
			'total_earned'      => $total_earned,
			'total_used'        => $total_used,
			'available_balance' => max( 0, $balance ),
		);
	}

	/**
	 * Count total transaction rows for a user (for pagination).
	 *
	 * @param int $user_id WordPress user ID.
	 * @return int
	 */
	public function count_transactions( int $user_id ): int {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table} WHERE user_id = %d",
				$user_id
			)
		);
	}
}
