<?php
/**
 * 2026 Paysera
 *
 * 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.
 *
 * @author Paysera <plugins@paysera.com>
 * @copyright 2026 Paysera
 * @license http://opensource.org/licenses/afl-3.0.php Academic Free License (AFL 3.0)
 * International Registered Trademark & Property of Paysera
 */

declare(strict_types=1);

namespace Paysera\PrestashopPayments\Installer;

use Paysera\CheckoutSdk\Entity\PaymentStatus;
use Paysera\PrestashopPayments\Enum\ConfigurationKey;
use Psr\Log\LoggerInterface;

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

class DatabaseInstaller
{
    public const TABLE_PAYMENT = 'paysera_payment';
    public const TABLE_STATUS_TRANSITION = 'paysera_payment_status_transition';
    public const TABLE_PAYMENT_METHOD = 'paysera_payment_method';
    private const TABLE_ORDERS = 'orders';

    public function __construct(
        private LoggerInterface $logger
    ) {
    }

    public function install(): void
    {
        $this->logger->debug('Creating Paysera database tables');

        $db = \Db::getInstance();
        $prefix = _DB_PREFIX_;
        $engine = _MYSQL_ENGINE_;

        $paymentTable = $prefix . self::TABLE_PAYMENT;
        $transitionTable = $prefix . self::TABLE_STATUS_TRANSITION;
        $paymentMethodTable = $prefix . self::TABLE_PAYMENT_METHOD;

        $db->execute(
            "CREATE TABLE IF NOT EXISTS `$paymentMethodTable` (
                `key` VARCHAR(64) NOT NULL,
                `title` VARCHAR(255) NOT NULL,
                `date_add` DATETIME NOT NULL,
                `date_upd` DATETIME NOT NULL,
                PRIMARY KEY (`key`)
            ) ENGINE=$engine"
        );

        $db->execute(
            "CREATE TABLE IF NOT EXISTS `$paymentTable` (
                `id_order` INT UNSIGNED NOT NULL,
                `paysera_payment_id` VARCHAR(36) DEFAULT NULL,
                `paysera_order_id` VARCHAR(36) DEFAULT NULL,
                `payment_link` TEXT DEFAULT NULL,
                `payment_method` VARCHAR(64) DEFAULT NULL,
                `status` VARCHAR(50) NOT NULL DEFAULT 'initiated',
                `original_amount` BIGINT DEFAULT NULL,
                `original_currency` VARCHAR(3) DEFAULT NULL,
                `paid_amount` BIGINT DEFAULT NULL,
                `paid_currency` VARCHAR(3) DEFAULT NULL,
                `payer_name` VARCHAR(255) DEFAULT NULL,
                `payer_email` VARCHAR(255) DEFAULT NULL,
                `date_add` DATETIME NOT NULL,
                `date_upd` DATETIME NOT NULL,
                PRIMARY KEY (`id_order`),
                KEY `idx_paysera_payment_id` (`paysera_payment_id`),
                KEY `idx_paysera_order_id` (`paysera_order_id`),
                KEY `idx_payment_method` (`payment_method`),
                KEY `idx_status` (`status`)
            ) ENGINE=$engine"
        );

        $db->execute(
            "CREATE TABLE IF NOT EXISTS `$transitionTable` (
                `id_transition` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `id_order` INT UNSIGNED NOT NULL,
                `from_status` VARCHAR(50) NOT NULL DEFAULT '',
                `to_status` VARCHAR(50) NOT NULL,
                `date_add` DATETIME NOT NULL,
                PRIMARY KEY (`id_transition`),
                UNIQUE KEY `uniq_order_transition` (`id_order`, `from_status`, `to_status`),
                KEY `idx_order_date` (`id_order`, `date_add`)
            ) ENGINE=$engine"
        );

        $this->ensurePaymentTableColumns($paymentTable);

        $this->logger->info('Paysera database tables created');
    }

    private function ensurePaymentTableColumns(string $paymentTable): void
    {
        if (!$this->columnExists($paymentTable, 'payment_method')) {
            \Db::getInstance()->execute(
                'ALTER TABLE `' . $paymentTable . '` '
                . 'ADD COLUMN `payment_method` VARCHAR(64) DEFAULT NULL AFTER `payment_link`, '
                . 'ADD KEY `idx_payment_method` (`payment_method`)'
            );
        }

        if (!$this->columnExists($paymentTable, 'status')) {
            \Db::getInstance()->execute(
                'ALTER TABLE `' . $paymentTable . '` '
                . "ADD COLUMN `status` VARCHAR(50) NOT NULL DEFAULT 'initiated' AFTER `payment_method`, "
                . 'ADD KEY `idx_status` (`status`)'
            );

            $this->backfillStatusFromOrderState($paymentTable);
        }
    }

    private function backfillStatusFromOrderState(string $paymentTable): void
    {
        $statusByStateIds = [
            PaymentStatus::SETTLED => $this->confirmedStateIds(),
            PaymentStatus::CANCELLED => $this->stateIds(['PS_OS_CANCELED']),
            PaymentStatus::FAILED => $this->stateIds(['PS_OS_ERROR']),
        ];

        $ordersTable = _DB_PREFIX_ . self::TABLE_ORDERS;

        foreach ($statusByStateIds as $status => $stateIds) {
            if ($stateIds === []) {
                continue;
            }

            \Db::getInstance()->execute(sprintf(
                'UPDATE `%1$s` pp INNER JOIN `%2$s` o ON o.id_order = pp.id_order '
                . 'SET pp.status = \'%3$s\' WHERE o.current_state IN (%4$s)',
                $paymentTable,
                $ordersTable,
                pSQL($status),
                implode(', ', $stateIds)
            ));
        }
    }

    /**
     * @return int[]
     */
    private function confirmedStateIds(): array
    {
        return $this->stateIds([
            ConfigurationKey::STATUS_CONFIRMED_PAYMENT,
            'PS_OS_PAYMENT',
        ]);
    }

    /**
     * @param string[] $configurationKeys
     *
     * @return int[]
     */
    private function stateIds(array $configurationKeys): array
    {
        $ids = [];
        foreach ($configurationKeys as $key) {
            $id = (int) \Configuration::get($key);
            if ($id > 0) {
                $ids[$id] = $id;
            }
        }

        return array_values($ids);
    }

    private function columnExists(string $table, string $column): bool
    {
        $sql = sprintf(
            'SELECT COUNT(*) FROM information_schema.COLUMNS '
            . 'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = \'%s\' AND COLUMN_NAME = \'%s\'',
            pSQL($table),
            pSQL($column)
        );

        return (int) \Db::getInstance()->getValue($sql) > 0;
    }

    public function uninstall(): void
    {
        $this->logger->debug('Dropping Paysera database tables');

        $db = \Db::getInstance();
        $prefix = _DB_PREFIX_;

        $paymentTable = $prefix . self::TABLE_PAYMENT;
        $transitionTable = $prefix . self::TABLE_STATUS_TRANSITION;
        $paymentMethodTable = $prefix . self::TABLE_PAYMENT_METHOD;

        $db->execute("DROP TABLE IF EXISTS `$transitionTable`");
        $db->execute("DROP TABLE IF EXISTS `$paymentTable`");
        $db->execute("DROP TABLE IF EXISTS `$paymentMethodTable`");

        $this->logger->info('Paysera database tables dropped');
    }
}
