<?php
/**
 * 2007-2021 PrestaShop
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Academic Free License (AFL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/afl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@prestashop.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to http://www.prestashop.com for more information.
 *
 * @author    PrestaShop SA <contact@prestashop.com>
 * @copyright 2007-2021 PrestaShop SA
 * @license   http://opensource.org/licenses/afl-3.0.php  Academic Free License
 *            (AFL 3.0) International Registered Trademark & Property of
 *            PrestaShop SA
 */

// @codingStandardsIgnoreStart
/** @noinspection AutoloadingIssuesInspection */
/** @noinspection PhpMultipleClassDeclarationsInspection */
/** @noinspection PhpIllegalPsrClassPathInspection */
/** @noinspection PhpDeprecationInspection */
/** @noinspection ReturnTypeCanBeDeclaredInspection */
// @codingStandardsIgnoreEnd

/**
 * Object model for transaction data.
 *
 * @fixme Query select methods should return an single instance or an array of instances of \Transactions object.
 */
class Transactions extends ObjectModel
{
    public static $definition
        = [
            'table' => 'addonpayments_transactions',
            'primary' => 'id_transaction',
            'multilang' => false,
            'fields' => [
                'id_order' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedInt'],
                'payFrexTransactionId' => [
                    'type' => self::TYPE_STRING,
                    'size' => 255,
                    'validate' => 'isString',
                ],
                'merchantTransactionId' => [
                    'type' => self::TYPE_STRING,
                    'size' => 255,
                    'validate' => 'isString',
                ],
                'sortedOrder' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedInt'],
                'EPGTransactionID' => [
                    'type' => self::TYPE_STRING,
                    'size' => 255,
                    'validate' => 'isString',
                ],
                'amount' => ['type' => self::TYPE_FLOAT],
                'currency' => ['type' => self::TYPE_STRING, 'size' => 16, 'validate' => 'isString'],
                'originalPayfrexTransactionId' => [
                    'type' => self::TYPE_STRING,
                    'size' => 255,
                    'validate' => 'isString',
                ],
                'originalAmount' => ['type' => self::TYPE_FLOAT],
                'originalCurrency' => ['type' => self::TYPE_STRING, 'size' => 16, 'validate' => 'isString'],
                'remainingAmount' => ['type' => self::TYPE_FLOAT],
                'fee' => ['type' => self::TYPE_FLOAT],
                'details' => ['type' => self::TYPE_STRING],
                'message' => ['type' => self::TYPE_STRING, 'size' => 255],
                'operationType' => ['type' => self::TYPE_STRING, 'size' => 64],
                'paymentDetails' => ['type' => self::TYPE_STRING],
                'optionalTransactionParams' => ['type' => self::TYPE_STRING, 'size' => 1024],
                'paymentSolution' => ['type' => self::TYPE_STRING, 'size' => 64, 'validate' => 'isString'],
                'paySolTransactionId' => [
                    'type' => self::TYPE_STRING,
                    'size' => 255,
                    'validate' => 'isString',
                ],
                'respCode' => ['type' => self::TYPE_STRING],
                'status' => [
                    'type' => self::TYPE_STRING,
                    'size' => 255,
                    'validate' => 'isString',
                ],
                'lastOp' => ['type' => self::TYPE_BOOL],
                'createdAt' => ['type' => self::TYPE_DATE, 'validate' => 'isDate', 'required' => true],
                'updatedAt' => ['type' => self::TYPE_DATE, 'validate' => 'isDate', 'required' => false],
            ],
        ];

    /**
     * @var int
     */
    public $id_transaction;

    /**
     * @var int
     */
    public $id_order;

    /**
     * @var string
     */
    public $payFrexTransactionId;

    /**
     * @var string
     */
    public $merchantTransactionId;

    /**
     * @var int
     */
    public $sortedOrder;

    /**
     * @var string
     */
    public $EPGTransactionID;

    /**
     * @var float
     */
    public $amount;

    /**
     * @var string
     */
    public $currency;

    /**
     * @var string
     */
    public $originalPayfrexTransactionId;

    /**
     * @var float
     */
    public $originalAmount;

    /**
     * @var string
     */
    public $originalCurrency;

    /**
     * @var float
     */
    public $remainingAmount;

    /**
     * @var float
     */
    public $fee;

    /**
     * @var string
     */
    public $details;

    /**
     * @var string
     */
    public $message;

    /**
     * @var string
     */
    public $operationType;

    /**
     * @var string
     */
    public $paymentDetails;

    /**
     * @var string
     */
    public $optionalTransactionParams;

    /**
     * @var string
     */
    public $paymentSolution;

    /**
     * @var string
     */
    public $paySolTransactionId;

    /**
     * @var string
     */
    public $respCode;

    /**
     * @var string
     */
    public $status;

    /**
     * @var bool
     */
    public $lastOp;

    /**
     * @var string
     */
    public $createdAt;

    /**
     * @var string
     */
    public $updatedAt;

    /**
     * Get all transactions data
     */
    public static function getTransactionsTable(): array
    {
        $dbPrefix = _DB_PREFIX_;
        $sql = "SELECT
            t1.*
            t2.`id_order`,
            t2.`reference`
            FROM `{$dbPrefix}addonpayments_transactions` t1
            LEFT JOIN `{$dbPrefix}orders` t2 ON (t1.`id_order` = t2.`id_order`)
            ORDER BY t1.`payFrexTransactionId`, t1.`sortedOrder` DESC";

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
    }

    /**
     * Get all the transactions associated with an order.
     */
    public static function getByOrder(int $orderId): array
    {
        $dbPrefix = _DB_PREFIX_;
        $sql = "SELECT
            t1.`id_transaction`,
            t1.`payFrexTransactionId`,
            t1.`merchantTransactionId`,
            (SELECT COUNT(1) FROM `{$dbPrefix}addonpayments_transactions` t2 WHERE t2.`payFrexTransactionId` = t1.`payFrexTransactionId`) AS `numberOfOperations`,
            ROUND(t1.`amount`, 2) as `amount`,
            t1.`currency`,
            t1.`operationType`,
            t1.`paymentSolution`,
            t1.`status`,
            t1.`message`,
            t1.`lastOp`,
            t1.`createdAt`,
            t1.`updatedAt`
            FROM `{$dbPrefix}addonpayments_transactions` t1
            WHERE t1.`id_order` = {$orderId} AND t1.`lastOp` = 1
            ORDER BY t1.`updatedAt` DESC,
                     `t1`.`id_transaction` DESC";

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
    }

    /**
     * Returns an array of refundable transactions for a given order.
     *
     * This query only checks that a transaction was not REFUNDED by another transaction. Later on must call
     * ::getRemainingAmountByTransaction method to calculate the remaining refundable amount.
     *
     * @return \Transactions[]
     *
     * @throws \PrestaShopDatabaseException
     * @throws \PrestaShopException
     */
    public static function getRefundableByOrder(int $orderId): array
    {
        $dbPrefix = _DB_PREFIX_;
        $sql = "SELECT t1.*
            FROM `{$dbPrefix}addonpayments_transactions` t1
            WHERE t1.`id_order` = {$orderId}
                AND t1.`payFrexTransactionId` NOT IN (
                    SELECT t2.`originalPayFrexTransactionId`
                    FROM `{$dbPrefix}addonpayments_transactions` 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 ObjectModel::hydrateCollection(
            'Transactions',
            Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql)
        );
    }

    /**
     * Returns the refundable amount for a given transaction.
     *
     * @param $payFrexTransactionId
     *
     * @return float|bool returns refundable amount as float or false if cannot be determined, which means
     *                    we can refund the whole transaction
     */
    public static function getRemainingAmountByTransaction($payFrexTransactionId)
    {
        // @TODO Add change operation enums to include all possible operations in one: DEBIT, CREDIT, REFUND, VOID
        $dbPrefix = _DB_PREFIX_;
        $sql = "SELECT MIN(t1.`remainingAmount`) AS `remainingAmount`
            FROM `{$dbPrefix}addonpayments_transactions` t1
            WHERE t1.`originalPayFrexTransactionId` = {$payFrexTransactionId}
              AND t1.`operationType` = 'REFUND'";

        $value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);

        if (false !== $value && null !== $value) {
            return (float) $value;
        }

        return false;
    }

    /**
     * Returns an array of voidable transactions for a given order.
     *
     * This query gets all final transactions with PENDING state.
     *
     * @return \Transactions[]
     *
     * @throws \PrestaShopDatabaseException
     * @throws \PrestaShopException
     */
    public static function getVoidableByOrder(int $orderId): array
    {
        $dbPrefix = _DB_PREFIX_;
        $sql = "SELECT t1.*
            FROM `{$dbPrefix}addonpayments_transactions` t1
            WHERE t1.`id_order` = {$orderId}
                AND t1.`payFrexTransactionId` NOT IN (
                    SELECT t2.`originalPayFrexTransactionId`
                    FROM `{$dbPrefix}addonpayments_transactions` 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 ObjectModel::hydrateCollection(
            'Transactions',
            Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql)
        );
    }

    /**
     * Count number of transactions for a given order.
     */
    public static function countByOrder(int $orderId): int
    {
        $dbPrefix = _DB_PREFIX_;
        $sql = "SELECT COUNT(t1.`payFrexTransactionId`)
            FROM `{$dbPrefix}addonpayments_transactions` t1
            WHERE t1.`id_order` = {$orderId}
                AND t1.`lastOp` = 1
            GROUP BY t1.`payFrexTransactionId`";

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
    }

    /**
     * Count number of attempts transactions for a given order.
     */
    public static function countAttemptsByOrder(int $orderId): int
    {
        $dbPrefix = _DB_PREFIX_;
        $sql = "SELECT COUNT(t1.`payFrexTransactionId`)
            FROM `{$dbPrefix}addonpayments_transactions` t1
            WHERE t1.`id_order` = {$orderId}
            GROUP BY t1.`id_order`";

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
    }

    /**
     * Get number of transactions by the user between 2 dates
     */
    public static function getTransactionsAttemptsByUserBetweenDates(int $idCustomer, DateTime $from, DateTime $to): int
    {
        $dateFrom = $from->format('Y-m-d');
        $dateTo = $to->format('Y-m-d');
        $dbPrefix = _DB_PREFIX_;

        $sql = "SELECT COUNT(*) FROM `{$dbPrefix}addonpayments_transactions` t1
            INNER JOIN `{$dbPrefix}orders` t2 ON (et.`id_order` = t2.`id_order`)
            WHERE t1.`createdAt` BETWEEN '$dateFrom' AND '$dateTo'
                AND t2.`id_customer` = ${idCustomer}";

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
    }

    /**
     * Update generated transactions once an order is placed
     */
    public static function setOrderByCart($orderCartId, $orderId)
    {
        $dbPrefix = _DB_PREFIX_;
        $sql = "UPDATE `{$dbPrefix}addonpayments_transactions` t1
            SET t1.`id_order` = {$orderId}
            WHERE t1.`id_order` = {$orderCartId}";

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->execute($sql);
    }

    /**
     * Check if a transaction exists.
     */
    public static function transactionExists($merchantTransactionId): bool
    {
        $dbPrefix = _DB_PREFIX_;
        $merchantTransactionId = pSQL($merchantTransactionId);
        $sql = "SELECT COUNT(t1.`sortedOrder`) AS `operations`
            FROM `{$dbPrefix}addonpayments_transactions` t1
            WHERE t1.`merchantTransactionId` = '$merchantTransactionId'";
        $value = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);

        return !empty($value);
    }

    /**
     * Get operation types.
     */
    public static function getOperationTypes()
    {
        try {
            $dbPrefix = _DB_PREFIX_;
            $sql = "SELECT t1.`operationType` FROM `{$dbPrefix}addonpayments_transactions` t1 GROUP BY t1.`operationType`";

            return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
        } catch (Exception $e) {
            PrestaShopLogger::addLog($e->getMessage(), 3, $e->getCode());

            return [];
        }
    }

    /**
     * Get payment solutions.
     */
    public static function getPaymentSolutions()
    {
        try {
            $dbPrefix = _DB_PREFIX_;
            $sql = "SELECT t1.`paymentSolution` FROM `{$dbPrefix}addonpayments_transactions` t1 GROUP BY t1.`paymentSolution`";

            return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
        } catch (Exception $e) {
            PrestaShopLogger::addLog($e->getMessage(), 3, $e->getCode());

            return [];
        }
    }

    /**
     * Get statuses.
     */
    public static function getStatuses()
    {
        try {
            $dbPrefix = _DB_PREFIX_;
            $sql = "SELECT t1.`status` FROM `{$dbPrefix}addonpayments_transactions` t1 GROUP BY t1.`status`";

            return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
        } catch (Exception $e) {
            PrestaShopLogger::addLog($e->getMessage(), 3, $e->getCode());

            return [];
        }
    }

    /**
     * Get the rounded value of the amount
     */
    public function getRoundedAmount(): float
    {
        return self::roundAmount($this->amount);
    }

    /**
     * Do the rounding of the amount
     */
    public static function roundAmount(float $amount): float
    {
        return round($amount, 2);
    }

    /**
     * Get last transaction by merchant transaction ID
     *
     * @param      $merchantTransactionId
     * @param null $status
     *
     * @return array|bool|\mysqli_result|\PDOStatement|resource|null
     * @throws \PrestaShopDatabaseException
     */
    public static function getByMerchTransactionId($merchantTransactionId, $status = null) {

        $sql = new DbQuery();
        $sql->select('id_order, status, message');
        $sql->from('addonpayments_transactions');
        $sql->where('merchantTransactionId = "' . $merchantTransactionId .'"');
        $sql->where('lastOp = 1');
        $sql->orderBy('updatedAt DESC');

        if(!empty($status)) {

            if(is_array($status)) {
                $status = implode('","', $status);
            }

            $sql->where('status in ("' . $status .'")');
        }

          return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
    }
}
