<?php

namespace Sibs\Stargate\Models;

use Sibs\Stargate\Constants\PaymentMethods;
use Sibs\Stargate\Constants\PaymentStatus;
use Sibs\Stargate\Constants\PaymentTypes;
use Sibs\Stargate\Loggers\SibsLogger;

if (!defined('ABSPATH')) {
    exit; // Prevent direct access
}

class SibsTransactions extends SibsModels
{
    /**
     * Constructor.
     */
    public function __construct()
    {
        parent::__construct('transactions', 'id');
    }

    /**
     * Get Table Name.
     */
    public function getTableName(): string
    {
        return $this->getModulePrefix().$this->table_name;
    }

    /**
     * Get Config Name.
     */
    public function getConfigName(): string
    {
        return 'CREATED_SIBS_STG_TRANSACTION_TABLE';
    }

    /**
     * Create Model.
     */
    public function create(): bool
    {
        $this->db->hide_errors();

        $table = $this->getTableName();
        $order_table = $this->getPrefix().'wc_orders';
        $customer_table = $this->getPrefix().'users';
        $charset_collate = $this->db->get_charset_collate();

        // $paymentMethods = implode("','", PaymentMethods::all());
        // $paymentTypes = implode("','", PaymentTypes::all());
        // $paymentStatus = implode("','", PaymentStatuses::all());

        if ($this->table_exists()) {
            // Table exists, no need to create it again
            SibsLogger::debug("Table '{$table}' already exists, skipping creation.");

            return true;
        }

        SibsLogger::debug("Table '{$table}' not exists and will be created.");

        // Recuperar tipos reais das colunas referenciadas
        $order_id_type = $this->get_column_type($order_table, 'id') ?: 'BIGINT(20) UNSIGNED';
        $customer_id_type = $this->get_column_type($customer_table, 'ID') ?: 'BIGINT(20) UNSIGNED';

        $sql = "CREATE TABLE {$table} (
            `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `order_id` {$order_id_type} NULL,
            `customer_id` {$customer_id_type} NULL,
            `original_transaction_id` VARCHAR(64) NULL,
            `parent_transaction_id` VARCHAR(64) NULL,
            `transaction_id` VARCHAR(64) NULL,
            `merchant_transaction_id` VARCHAR(64) NOT NULL,
            `woocommerce_method` VARCHAR(64) NOT NULL,
            `payment_method` VARCHAR(64) NOT NULL,
            `payment_brand` VARCHAR(64) NULL,
            `payment_type` VARCHAR(64) NOT NULL,
            `payment_status` VARCHAR(64) NOT NULL,
            `transaction_datetime` DATETIME NOT NULL,
            `currency` VARCHAR(3) NOT NULL,
            `amount` DECIMAL(17,2) NOT NULL,
            KEY `idx_transaction_id` (`transaction_id`),
            /* FOREIGN KEY (`order_id`) REFERENCES `{$order_table}`(`id`),
            FOREIGN KEY (`customer_id`) REFERENCES `{$customer_table}`(`ID`),
            FOREIGN KEY (`original_transaction_id`) REFERENCES `{$table}`(`transaction_id`),
            FOREIGN KEY (`parent_transaction_id`) REFERENCES `{$table}`(`transaction_id`), */
            UNIQUE KEY `unique_{$table}_transaction` (`transaction_id`)
        ) {$charset_collate};";

        SibsLogger::debug("dbDelta will execute: {$sql}");

        $result = dbDelta($sql);

        if (!empty($this->db->last_error)) {
            $this->logDbError("Failed to create table {$table}", ['result' => $result]);

            return false;
        }

        if (empty($result)) {
            SibsLogger::warning("dbDelta returned no changes for table {$table}");
        } else {
            SibsLogger::debug("dbDelta executed for table {$table} - ", [
                'changes' => $result,
            ]);
        }

        return $this->is_table_created($result, $table);
    }

    /**
     * Migrate Model.
     */
    public function migrate(string $installedVersion): bool
    {
        return true;
    }

    /**
     * Check.
     */
    public function check(bool $suppressLog = false): bool
    {
        $this->setSuppressLog($suppressLog);

        $table = $this->getTableName();
        $order_table = $this->getPrefix().'wc_orders';
        $customer_table = $this->getPrefix().'users';

        $order_id_type = $this->get_column_type($order_table, 'id') ?: 'BIGINT(20) UNSIGNED';
        $customer_id_type = $this->get_column_type($customer_table, 'ID') ?: 'BIGINT(20) UNSIGNED';

        return $this->health_check(
            $table,
            // Expected columns
            [
                'id' => 'BIGINT(20) UNSIGNED',
                'order_id' => $order_id_type,
                'customer_id' => $customer_id_type,
                'original_transaction_id' => 'VARCHAR(64)',
                'parent_transaction_id' => 'VARCHAR(64)',
                'transaction_id' => 'VARCHAR(64)',
                'merchant_transaction_id' => 'VARCHAR(64)',
                'woocommerce_method' => 'VARCHAR(64)',
                'payment_method' => 'VARCHAR(64)',
                'payment_brand' => 'VARCHAR(64)',
                'payment_type' => 'VARCHAR(64)',
                'payment_status' => 'VARCHAR(64)',
                'transaction_datetime' => 'DATETIME',
                'currency' => 'VARCHAR(3)',
                'amount' => 'DECIMAL(17,2)',
            ],
            // Indexes
            [
                'idx_transaction_id',
                'unique_'.$table.'_transaction',
            ],
            // Foreign keys
            [
                // 'fk_order' => ['column' => 'order_id', 'referenced_table' => $order_table, 'referenced_column' => 'id'],
                // 'fk_customer' => ['column' => 'customer_id', 'referenced_table' => $customer_table, 'referenced_column' => 'ID'],
                // 'fk_original_transaction' => ['column' => 'original_transaction_id', 'referenced_table' => $table, 'referenced_column' => 'transaction_id'],
                // 'fk_parent_transaction' => ['column' => 'parent_transaction_id', 'referenced_table' => $table, 'referenced_column' => 'transaction_id'],
            ]
        );
    }

    /**
     * Select the correct transaction data for displaying payment content.
     *
     * Priority:
     * 1. PREF
     * 2. PURS
     * 3. AUTH
     */
    public function getOriginalReceiptTransaction(array $transactionsData): ?array
    {
        return $this->resolvePaymentContentTransaction($transactionsData, [
            PaymentTypes::PREF,
            PaymentTypes::PURS,
            PaymentTypes::AUTH,
        ]);
    }

    /**
     * Select the correct transaction data for displaying payment content.
     *
     * Priority:
     * 1. PURS
     * 2. AUTH
     */
    public function getOriginalTransaction(array $transactionsData): ?array
    {
        return $this->resolvePaymentContentTransaction($transactionsData, [
            PaymentTypes::PURS,
            PaymentTypes::AUTH,
        ]);
    }

    /**
     * Get By Order.
     *
     * @param bool $all
     *
     * @return null|array
     */
    public function getLatestByOrder(\WC_Order $order, $all = false)
    {
        $conditions = [
            'order_id' => $order->get_id(),
            'woocommerce_method' => $order->get_payment_method(),
        ];

        if ($all) {
            return $this->get_all($conditions, 'id', 'DESC');
        }

        $conditions = array_merge($conditions, [
            'amount' => $order->get_total(),
            'currency' => $order->get_currency(),
        ]); // TODO: Remove amount and currency to get the latest transaction when we have partial captures/refunds

        $transactionsData = $this->get_all($conditions, 'id', 'DESC');

        return !empty($transactionsData) ? $transactionsData[0] : [];
    }

    /**
     * Get Original By Order.
     *
     * @param bool $all
     *
     * @return null|array
     */
    public function getOriginalByOrder(\WC_Order $order, $all = false)
    {
        $conditions = [
            'order_id' => $order->get_id(),
            'woocommerce_method' => $order->get_payment_method(),
        ];

        if ($all) {
            return $this->get_all($conditions, 'id', 'DESC');
        }

        $conditions = array_merge($conditions, [
            'amount' => $order->get_total(),
            'currency' => $order->get_currency(),
            'original_transaction_id' => ['operator' => 'IS', 'value' => null],
        ]);

        $transactionsData = $this->get_all($conditions, 'id', 'DESC');

        return !empty($transactionsData) ? $transactionsData[0] : [];
    }

    /**
     * Get By Order Id.
     *
     * @param int  $order_id
     * @param bool $all
     *
     * @return null|array|object
     */
    public function getByOrderId($order_id, $all = false)
    {
        if ($all) {
            return $this->get_all([
                'order_id' => $order_id,
            ], 'id', 'DESC');
        }

        return $this->get_by('order_id', $order_id);
    }

    /**
     * Get By Customer Id.
     *
     * @param int  $customer_id
     * @param bool $all
     *
     * @return null|array|object
     */
    public function getByCustomerId($customer_id, $all = false)
    {
        if ($all) {
            return $this->get_all([
                'customer_id' => $customer_id,
            ], 'id', 'DESC');
        }

        return $this->get_by('customer_id', $customer_id);
    }

    /**
     * Get By Original Transaction Id.
     *
     * @param string $original_transaction_id
     * @param bool   $all
     *
     * @return null|array|object
     */
    public function getByOriginalTransactionId($original_transaction_id, $all = false)
    {
        if ($all) {
            return $this->get_all([
                'original_transaction_id' => $original_transaction_id,
            ], 'id', 'DESC');
        }

        return $this->get_by('original_transaction_id', $original_transaction_id);
    }

    /**
     * Get By Transaction Id.
     *
     * @param string $transaction_id
     * @param bool   $all
     *
     * @return null|array|object
     */
    public function getByTransactionId($transaction_id, $all = false)
    {
        if ($all) {
            return $this->get_all([
                'transaction_id' => $transaction_id,
            ], 'id', 'DESC');
        }

        return $this->get_by('transaction_id', $transaction_id);
    }

    /**
     * Get By Merchant Transaction Id.
     *
     * @param string $merchant_transaction_id
     * @param bool   $all
     *
     * @return null|array|object
     */
    public function getByMerchantTransactionId($merchant_transaction_id, $all = false)
    {
        if ($all) {
            return $this->get_all([
                'merchant_transaction_id' => $merchant_transaction_id,
            ], 'id', 'DESC');
        }

        return $this->get_by('merchant_transaction_id', $merchant_transaction_id);
    }

    /**
     * Upsert Transaction.
     *
     * @return false|int
     *
     * @throws \PDOException
     */
    public function upsertTransaction(array $data, array $where = [])
    {
        $transactionUpserted = $this->upsert(
            $this->processDataOrWhere($data),
            $this->processDataOrWhere($where)
        );

        $params = [
            'data' => $data,
            'where' => $where,
        ];

        if (false === $transactionUpserted) {
            throw new \PDOException('Transaction was not upserted: '.SibsLogger::prettify($params));
        }
        SibsLogger::debug('Transaction upserted', [
            'params' => $params,
        ]);

        return $transactionUpserted;
    }

    /**
     * Update Transaction.
     *
     * @return false|int
     *
     * @throws \PDOException
     */
    public function updateTransaction(array $data, array $where = [])
    {
        $transactionUpdated = $this->update(
            $this->processDataOrWhere($data),
            $this->processDataOrWhere($where)
        );

        $params = [
            'data' => $data,
            'where' => $where,
        ];

        if (false === $transactionUpdated) {
            throw new \PDOException('Transaction was not updated: '.SibsLogger::prettify($params));
        }
        SibsLogger::debug('Transaction updated', [
            'params' => $params,
        ]);

        return $transactionUpdated;
    }

    /**
     * Save Transaction.
     *
     * @return false|int
     *
     * @throws \PDOException
     */
    public function saveTransaction(array $data, array $where = [])
    {
        $transactionSaved = $this->insert(
            $this->processDataOrWhere($data),
            $this->processDataOrWhere($where)
        );

        $params = [
            'data' => $data,
            'where' => $where,
        ];

        if (false === $transactionSaved) {
            throw new \PDOException('Transaction was not saved: '.SibsLogger::prettify($params));
        }
        SibsLogger::debug('Transaction saved', [
            'params' => $params,
        ]);

        return $transactionSaved;
    }

    /**
     * Process Data Or Where.
     *
     * @return array
     */
    protected function processDataOrWhere(array $data)
    {
        $updateOrSaveData = [];

        if (!empty($data['order_id'])) {
            $updateOrSaveData['order_id'] = $data['order_id'];
        }

        if (!empty($data['customer_id'])) {
            $updateOrSaveData['customer_id'] = $data['customer_id'];
        }

        if (!empty($data['transaction_id'])) {
            $updateOrSaveData['transaction_id'] = $data['transaction_id'];
        }

        if (!empty($data['merchant_transaction_id'])) {
            $updateOrSaveData['merchant_transaction_id'] = $data['merchant_transaction_id'];
        }

        if (!empty($data['original_transaction_id'])) {
            $updateOrSaveData['original_transaction_id'] = $data['original_transaction_id'];
        }

        if (!empty($data['parent_transaction_id'])) {
            $updateOrSaveData['parent_transaction_id'] = $data['parent_transaction_id'];
        }

        if (!empty($data['woocommerce_method'])) {
            $updateOrSaveData['woocommerce_method'] = $data['woocommerce_method'];
        }

        if (!empty($data['payment_method'])) {
            if (!in_array($data['payment_method'], PaymentMethods::all())) {
                new \Exception('Payment Method Not Recognized');
            }

            $updateOrSaveData['payment_method'] = $data['payment_method'];
        }

        if (!empty($data['payment_brand'])) {
            $updateOrSaveData['payment_brand'] = $data['payment_brand'];
        }

        if (!empty($data['payment_type'])) {
            if (!in_array($data['payment_type'], PaymentTypes::all())) {
                new \Exception('Payment Type Not Recognized');
            }

            $updateOrSaveData['payment_type'] = $data['payment_type'];
        }

        if (!empty($data['payment_status'])) {
            if (!in_array($data['payment_status'], PaymentStatus::all())) {
                new \Exception('Payment Status Not Recognized');
            }

            $updateOrSaveData['payment_status'] = $data['payment_status'];
        }

        if (!empty($data['transaction_datetime'])) {
            $updateOrSaveData['transaction_datetime'] = $data['transaction_datetime'];
        }

        if (!empty($data['currency'])) {
            $updateOrSaveData['currency'] = $data['currency'];
        }

        if (!empty($data['amount'])) {
            $updateOrSaveData['amount'] = $data['amount'];
        }

        return $updateOrSaveData;
    }

    /**
     * Select the correct transaction data for displaying payment content.
     */
    private function resolvePaymentContentTransaction(array $transactionsData, array $priority): ?array
    {
        if (empty($transactionsData)) {
            return null;
        }

        foreach ($priority as $type) {
            foreach ($transactionsData as $tx) {
                if (!empty($tx['payment_type']) && $tx['payment_type'] === $type) {
                    return $tx;
                }
            }
        }

        return null;
    }
}
