<?php

namespace WooCommerce\Plugin\AddonPayments\Entities;

class Transaction {

	public $id_epg_transaction;
	public $id_order;
	public $payFrexTransactionId;
	public $merchantTransactionId;
	public $sortedOrder;
	public $EPGTransactionID;
	public $amount;
	public $currency;
	public $originalPayfrexTransactionId;
	public $originalAmount;
	public $originalCurrency;
	public $remainingAmount;
	public $fee;
	public $details;
	public $message;
	public $operationType;
	public $paymentDetails;
	public $optionalTransactionParams;
	public $paymentSolution;
	public $paySolTransactionId;
	public $respCode;
	public $status;
	public $lastOp;
	public $rawData;
	public $createdAt;
	public $updatedAt;

	public function save() {
		global $wpdb;
		$dbPrefix                 = $wpdb->prefix;
		$this->id_epg_transaction = $wpdb->insert(
			"{$dbPrefix}epg_transaction",
			array(
				'id_order'                     => $this->id_order,
				'payFrexTransactionId'         => $this->payFrexTransactionId,
				'merchantTransactionId'        => $this->merchantTransactionId,
				'sortedOrder'                  => $this->sortedOrder,
				'EPGTransactionID'             => $this->EPGTransactionID,
				'amount'                       => $this->amount,
				'currency'                     => $this->currency,
				'originalPayfrexTransactionId' => $this->originalPayfrexTransactionId,
				'originalAmount'               => $this->originalAmount,
				'originalCurrency'             => $this->originalCurrency,
				'remainingAmount'              => $this->remainingAmount,
				'fee'                          => $this->fee,
				'details'                      => $this->details,
				'message'                      => $this->message,
				'operationType'                => $this->operationType,
				'paymentDetails'               => $this->paymentDetails,
				'optionalTransactionParams'    => $this->optionalTransactionParams,
				'paymentSolution'              => $this->paymentSolution,
				'paySolTransactionId'          => $this->paySolTransactionId,
				'respCode'                     => $this->respCode,
				'status'                       => $this->status,
				'lastOp'                       => $this->lastOp,
				'rawData'                      => $this->rawData,
				'createdAt'                    => $this->createdAt,
				'updatedAt'                    => $this->updatedAt,
			)
		);

		return $this->id_epg_transaction;
	}

	public static function getTotalTransactions() {
		global $wpdb;
		$dbPrefix = $wpdb->prefix;
		$sql      = "SELECT COUNT(a.`payFrexTransactionId`) FROM (SELECT `payFrexTransactionId` FROM {$dbPrefix}epg_transaction GROUP BY `payFrexTransactionId`) a";
		return $wpdb->get_var( $sql );
	}

	public static function getRefundableByPayFrexTransactionId( $payFrexTransactionId ) {
		global $wpdb;
		$dbPrefix = $wpdb->prefix;
		$sql      = "SELECT t1.id_epg_transaction,
            t1.payFrexTransactionId,
            t1.merchantTransactionId,
            t1.sortedOrder,
            t1.EPGTransactionID,
            t1.originalPayFrexTransactionId,
            t1.amount,
            t1.currency,
            t1.details,
            t1.message,
            t1.operationType,
            t1.paymentDetails,
            t1.optionalTransactionParams,
            t1.paymentSolution,
            t1.paySolTransactionId,
            t1.respCode,
            t1.status,
            t1.createdAt,
            t1.updatedAt
            FROM {$dbPrefix}epg_transaction t1
            WHERE t1.payFrexTransactionId = {$payFrexTransactionId} AND
                UPPER(t1.operationType) IN ('CREDIT', 'DEBIT', 'REBATE') AND
                t1.lastOp = 1
            ORDER BY t1.payFrexTransactionId, t1.sortedOrder DESC";

		return $wpdb->get_results( $sql, OBJECT );
	}


	public static function getVoidableByOrder( int $orderId ): array {
		global $wpdb;
		$dbPrefix = $wpdb->prefix;
		$sql      = "SELECT t1.*
            FROM `{$dbPrefix}epg_transaction` t1
            WHERE t1.`id_order` = {$orderId}
                AND t1.`payFrexTransactionId` NOT IN (
                    SELECT t2.`originalPayFrexTransactionId`
                    FROM `{$dbPrefix}epg_transaction` t2
                    WHERE t2.`id_order` = {$orderId}
                        AND t2.`originalPayFrexTransactionId` IS NOT NULL
                        AND t2.`operationType` = 'VOID'
                )
                AND UPPER(t1.`operationType`) IN ('CREDIT', 'DEBIT')
                AND t1.`status` = 'PENDING'
                AND t1.`lastOp` = 1
            ORDER BY t1.`payFrexTransactionId`, t1.`sortedOrder` DESC";

		return $wpdb->get_results( $sql, OBJECT );
	}

	public static function getRefundableByOrder( $orderId ) {
		global $wpdb;
		$dbPrefix = $wpdb->prefix;
		$sql      = "SELECT t1.*
            FROM {$dbPrefix}epg_transaction t1
            WHERE t1.id_order = {$orderId} AND
                t1.payFrexTransactionId NOT IN (
                SELECT t2.originalPayFrexTransactionId
                FROM {$dbPrefix}epg_transaction t2
                WHERE t2.id_order = {$orderId} AND
                    t2.originalPayFrexTransactionId IS NOT NULL AND
                    t2.operationType = 'VOID'
                ) AND
                UPPER(t1.operationType) IN ('CREDIT', 'DEBIT') 
              	AND t1.status = 'SUCCESS' 
              	AND t1.lastOp = 1
            ORDER BY t1.payFrexTransactionId, t1.sortedOrder DESC";

		return $wpdb->get_results( $sql, OBJECT );
	}

	public static function getRemainingAmountByTransaction( $payFrexTransactionId ) {
		// @TODO Add change operation enums to include all possible operations in one: DEBIT, CREDIT, REFUND, VOID
		global $wpdb;
		$dbPrefix = $wpdb->prefix;

		$sql = "SELECT MIN(remainingAmount)
            FROM {$dbPrefix}epg_transaction t1
            WHERE t1.originalPayFrexTransactionId = {$payFrexTransactionId} 
             AND UPPER(t1.operationType) IN ('REFUND', 'REBATE')";

		return $wpdb->get_var( $sql );
	}

	public static function getRemainingAmountByOrderId( $orderId ) {
		// @TODO Add change operation enums to include all possible operations in one: DEBIT, CREDIT, REFUND, VOID
		global $wpdb;
		$dbPrefix = $wpdb->prefix;

		$sql = "SELECT MIN(remainingAmount)
            FROM {$dbPrefix}epg_transaction t1
            WHERE t1.id_order = {$orderId} 
              AND (operationType = 'REFUND' OR operationType = 'REBATE')";

		return $wpdb->get_var( $sql );
	}

	public static function getTransactions( $page = 0, $elements = 50 ) {
		global $wpdb;
		$dbPrefix  = $wpdb->prefix;
		$startPage = $page * $elements;

		return $wpdb->get_results(
			"SELECT `a`.`payFrexTransactionId`, `a`.`id_order`, `a`.`amount`, `a`.`currency`, `a`.`operationType`, UPPER(`a`.`paymentSolution`) AS `paymentSolution`, `a`.`status`, `a`.`createdAt`, `a`.`updatedAt`
				FROM {$dbPrefix}epg_transaction a WHERE `a`.`lastOp` = 1 ORDER BY `a`.`payFrexTransactionId` LIMIT $startPage, $elements",
			OBJECT
		);
	}

	public static function getByOrder( $orderId ) {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT `a`.`payFrexTransactionId`, `a`.`id_order`, 
       		`a`.`amount`, `a`.`currency`, `a`.`operationType`, 
       		`a`.`paymentSolution` AS `paymentSolution`, `a`.`status`,
       		`a`.`createdAt`, `a`.`updatedAt`
			FROM {$wpdb->prefix}epg_transaction a 
			WHERE `a`.`id_order`={$orderId} 
			  AND `a`.`lastOp` = 1 
			ORDER BY `a`.`updatedAt` DESC,
			         `a`.`id_epg_transaction` DESC",
			OBJECT
		);
	}

	public static function getByPayFrexTransactionId( $payFrexTransactionId ) {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT `a`.`id_order`, `a`.`amount`, `a`.`currency`, `a`.`operationType`, UPPER(`a`.`paymentSolution`) AS `paymentSolution`, `a`.`status`, COUNT(`a`.`sortedOrder`) AS `numberOfOperations`, `a`.`createdAt`, `a`.`updatedAt`, `a`.`merchantTransactionId`, `a`.`payFrexTransactionId`
			FROM {$wpdb->prefix}epg_transaction a WHERE `a`.`payFrexTransactionId`={$payFrexTransactionId} AND `a`.`lastOp` = 1",
			OBJECT
		);
	}

	/**
	 * Returns the last transaction by merchantTransactionId
	 *
	 * @param string $merchantTransactionId
	 *
	 * @return array
	 */
	public static function getByMerchTransactionId( $merchantTransactionId, $status ) {
		global $wpdb;

		$query = "SELECT `a`.`id_order`,`a`.`status`, `a`.`createdAt`, `a`.`updatedAt`, `a`.`payFrexTransactionId`, `a`.`message`
			FROM {$wpdb->prefix}epg_transaction a 
			WHERE `a`.`merchantTransactionId`='{$merchantTransactionId}' 
			  AND `a`.`lastOp` = 1 ";

		if ( ! empty( $status ) ) {
			if ( is_array( $status ) ) {
				$status = implode( '","', $status );
			}
			$query .= 'AND `a`.`status` in ("' . $status . '")';
		}

		$query .= 'order by `a`.`updatedAt` limit 1';

		return $wpdb->get_results( $query, OBJECT );
	}

	/**
	 * Returns the number of transactions made by an order.
	 *
	 * @param null|int $order_id The order id.
	 *
	 * @return int The number of transactions.
	 */
	public static function countByOrder( $order_id ) {
		global $wpdb;
		$dbPrefix = $wpdb->prefix;
		$sql      =
			"SELECT COUNT(`a`.`id_epg_transaction`) FROM `{$dbPrefix}epg_transaction` a WHERE `a`.`id_order` = {$order_id}";

		return (int) $wpdb->get_var( $sql );
	}

}
