<?php

namespace AIbender\ArticleVault\Databases;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use AIbender\ArticleVault\Core\Constants;

class PurchaseRepository {

	public function create_pending_purchase(
		string $session_id,
		int $article_id,
		string $email,
		int $amount,
		string $currency
	): void {

		global $wpdb;

		$table = $this->get_table_name();
		$now   = current_time( 'mysql' );

		$wpdb->replace(
			$table,
			array(
				'session_id'      => $session_id,
				'article_id'      => $article_id,
				'purchaser_email' => $email,
				'amount'          => $amount,
				'currency'        => strtolower( $currency ),
				'gateway'         => 'stripe',
				'status'          => 'pending',
				'access_token'    => '',
				'created_at'      => $now,
				'updated_at'      => $now,
			),
			array(
				'%s',
				'%d',
				'%s',
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
			)
		);

	}

	public function mark_paid(
		string $session_id,
		string $payment_intent,
		int $article_id,
		string $email,
		int $amount,
		string $currency,
		string $token_hash
	): void {

		global $wpdb;

		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$this->get_table_name()}
				SET payment_intent = %s,
					article_id = %d,
					purchaser_email = %s,
					amount = %d,
					currency = %s,
					gateway = 'stripe',
					status = 'paid',
					access_token = %s,
					updated_at = %s
				WHERE session_id = %s
				AND status = 'pending'",
				$payment_intent,
				$article_id,
				$email,
				$amount,
				strtolower( $currency ),
				$token_hash,
				current_time( 'mysql' ),
				$session_id
			)
		);

	}

	public function claim_access_token(
		string $session_id,
		string $token_hash
	): bool {

		global $wpdb;

		$updated = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$this->get_table_name()}
				SET access_token = %s,
					access_claimed_at = %s,
					updated_at = %s
				WHERE session_id = %s
				AND status = 'paid'
				AND access_claimed_at IS NULL",
				$token_hash,
				current_time( 'mysql' ),
				current_time( 'mysql' ),
				$session_id
			)
		);

		return 1 === $updated;

	}

	public function mark_failed(
		string $session_id
	): void {

		global $wpdb;

		$wpdb->update(
			$this->get_table_name(),
			array(
				'status' => 'failed',
				'updated_at' => current_time( 'mysql' ),
			),
			array(
				'session_id' => $session_id,
			),
			array(
				'%s',
				'%s',
			),
			array(
				'%s',
			)
		);

	}

	public function mark_refunded(
		string $session_id
	): void {

		global $wpdb;

		$wpdb->update(
			$this->get_table_name(),
			array(
				'status' => 'refunded',
				'updated_at' => current_time( 'mysql' ),
			),
			array(
				'session_id' => $session_id,
			),
			array(
				'%s',
				'%s',
			),
			array(
				'%s',
			)
		);

	}

	public function find_paid_by_token(
		int $article_id,
		string $token_hash
	): ?array {

		global $wpdb;

		$result = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->get_table_name()}
				WHERE article_id=%d
				AND access_token=%s
				AND status='paid'
				LIMIT 1",
				$article_id,
				$token_hash
			),
			ARRAY_A
		);

		return is_array( $result ) ? $result : null;

	}

	public function find_by_session_id(
		string $session_id
	): ?array {

		global $wpdb;

		$result = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->get_table_name()}
				WHERE session_id=%s
				LIMIT 1",
				$session_id
			),
			ARRAY_A
		);

		return is_array( $result ) ? $result : null;

	}

	public function purchase_exists(
		int $article_id,
		string $email
	): bool {

		global $wpdb;

		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*)
				FROM {$this->get_table_name()}
				WHERE article_id = %d
				AND purchaser_email = %s
				AND status = 'paid'",
				$article_id,
				$email
			)
		);

		return $count > 0;

	}

	public function find_by_email(
		string $email
	): array {

		global $wpdb;

		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT *
				FROM {$this->get_table_name()}
				WHERE purchaser_email = %s
				ORDER BY created_at DESC",
				$email
			),
			ARRAY_A
		);

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

	}

	public function get_purchases(
	int $page = 1,
	int $per_page = 20,
	string $search = '',
	string $orderby = 'created_at',
	string $order = 'DESC'
): array {

	global $wpdb;

	$allowed_orderby = array(
		'created_at',
		'status',
		'amount',
		'purchaser_email',
		'article_id',
	);

	if ( ! in_array( $orderby, $allowed_orderby, true ) ) {
		$orderby = 'created_at';
	}

	$order = ( 'ASC' === strtoupper( $order ) ) ? 'ASC' : 'DESC';

	$table = $this->get_table_name();

	$where = '';
	$params = array();

	if ( '' !== $search ) {

		$like = '%' . $wpdb->esc_like( $search ) . '%';

		$where = ' WHERE purchaser_email LIKE %s OR session_id LIKE %s';

		$params[] = $like;
		$params[] = $like;
	}

	if ( empty( $params ) ) {

		$total = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$table}"
		);

	} else {

		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}{$where}",
				$params
			)
		);

	}

	$offset = ( $page - 1 ) * $per_page;

	if ( empty( $params ) ) {

		$items = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT *
				FROM {$table}
				ORDER BY {$orderby} {$order}
				LIMIT %d OFFSET %d",
				$per_page,
				$offset
			),
			ARRAY_A
		);

	} else {

		$params[] = $per_page;
		$params[] = $offset;

		$items = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT *
				FROM {$table}
				{$where}
				ORDER BY {$orderby} {$order}
				LIMIT %d OFFSET %d",
				$params
			),
			ARRAY_A
		);

	}

	return array(
		'items' => is_array( $items ) ? $items : array(),
		'total' => $total,
	);

}

	
	private function get_table_name(): string {

		global $wpdb;

		return $wpdb->prefix . Constants::PURCHASE_TABLE_NAME;

	}

}