<?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\QueryBuilder;

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

class PayseraOrdersQueryBuilder
{
    private const MODULE_NAME = 'payserapayments';
    private const TABLE_NAME = 'orders';
    private const TABLE_ALIAS = 'o';
    private const CUSTOMER_TABLE_NAME = 'customer';
    private const CUSTOMER_TABLE_ALIAS = 'c';
    private const PAYSERA_PAYMENT_TABLE = 'paysera_payment';
    private const PAYSERA_PAYMENT_ALIAS = 'pp';
    private const PAYSERA_METHOD_TABLE = 'paysera_payment_method';
    private const PAYSERA_METHOD_ALIAS = 'pm';

    private const ORDER_BY_EXPRESSIONS = [
        'id_order' => self::TABLE_ALIAS . '.id_order',
        'reference' => self::TABLE_ALIAS . '.reference',
        'date_add' => self::TABLE_ALIAS . '.date_add',
        'total_paid' => self::TABLE_ALIAS . '.total_paid',
        'current_state' => self::TABLE_ALIAS . '.current_state',
        'customerName' => 'CONCAT(' . self::CUSTOMER_TABLE_ALIAS . '.firstname, \' \', ' . self::CUSTOMER_TABLE_ALIAS . '.lastname)',
        'customerEmail' => self::CUSTOMER_TABLE_ALIAS . '.email',
    ];

    private const ALLOWED_DIRECTION = ['ASC', 'DESC'];

    private const DEFAULT_ORDER_BY = 'date_add';
    private const DEFAULT_DIRECTION = 'DESC';
    private const DEFAULT_PAGE = 1;
    private const DEFAULT_PER_PAGE = 20;

    /** @var array<string, mixed> */
    private array $filters;
    private string $orderByColumn;
    private string $orderByDirection;
    private int $page;
    private int $perPage;

    public function __construct()
    {
        $this->filters = [];
        $this->orderByColumn = self::DEFAULT_ORDER_BY;
        $this->orderByDirection = self::DEFAULT_DIRECTION;
        $this->page = self::DEFAULT_PAGE;
        $this->perPage = self::DEFAULT_PER_PAGE;
    }

    /**
     * @param array<string, mixed> $filters
     */
    public function withFilters(array $filters): self
    {
        $clone = clone $this;
        $clone->filters = $filters;

        return $clone;
    }

    public function orderBy(string $column, string $direction = self::DEFAULT_DIRECTION): self
    {
        $clone = clone $this;
        $clone->orderByColumn = $this->sanitizeOrderBy($column);
        $clone->orderByDirection = $this->sanitizeDirection($direction);

        return $clone;
    }

    public function paginate(int $page, int $perPage): self
    {
        $clone = clone $this;
        $clone->page = $page;
        $clone->perPage = $perPage;

        return $clone;
    }

    public function buildCountQuery(): string
    {
        $where = $this->buildWhereClause();
        $joins = $this->buildJoinClauses();

        return sprintf(
            'SELECT COUNT(*) FROM `%s%s` %s %s WHERE %s.module = \'%s\'%s',
            _DB_PREFIX_,
            self::TABLE_NAME,
            self::TABLE_ALIAS,
            $joins,
            self::TABLE_ALIAS,
            pSQL(self::MODULE_NAME),
            $where
        );
    }

    public function buildSelectQuery(): string
    {
        $offset = ($this->page - 1) * $this->perPage;
        $where = $this->buildWhereClause();
        $order = self::TABLE_ALIAS;
        $customer = self::CUSTOMER_TABLE_ALIAS;
        $payment = self::PAYSERA_PAYMENT_ALIAS;
        $method = self::PAYSERA_METHOD_ALIAS;

        $columns = sprintf(
            '%1$s.id_order, %1$s.reference, %1$s.id_customer, %1$s.current_state, %1$s.total_paid, '
            . '%1$s.id_currency, %1$s.date_add, %1$s.date_upd, %1$s.payment, %1$s.id_cart, '
            . '%2$s.email AS customer_email, %2$s.firstname AS customer_firstname, '
            . '%2$s.lastname AS customer_lastname, '
            . '%3$s.payment_method AS paysera_payment_method_key, '
            . '%3$s.paysera_payment_id, %3$s.paysera_order_id, %3$s.status, '
            . '%4$s.title AS paysera_payment_method_title',
            $order,
            $customer,
            $payment,
            $method
        );

        return sprintf(
            'SELECT %s '
            . 'FROM `%s%s` %s '
            . '%s '
            . 'WHERE %s.module = \'%s\'%s '
            . 'ORDER BY %s %s '
            . 'LIMIT %d, %d',
            $columns,
            _DB_PREFIX_,
            self::TABLE_NAME,
            $order,
            $this->buildJoinClauses(),
            $order,
            pSQL(self::MODULE_NAME),
            $where,
            self::ORDER_BY_EXPRESSIONS[$this->orderByColumn],
            $this->orderByDirection,
            $offset,
            $this->perPage
        );
    }

    private function buildJoinClauses(): string
    {
        return sprintf(
            'LEFT JOIN `%1$s%2$s` %3$s ON %3$s.id_customer = %4$s.id_customer '
            . 'LEFT JOIN `%1$s%5$s` %6$s ON %6$s.id_order = %4$s.id_order '
            . 'LEFT JOIN `%1$s%7$s` %8$s ON %8$s.`key` = %6$s.payment_method',
            _DB_PREFIX_,
            self::CUSTOMER_TABLE_NAME,
            self::CUSTOMER_TABLE_ALIAS,
            self::TABLE_ALIAS,
            self::PAYSERA_PAYMENT_TABLE,
            self::PAYSERA_PAYMENT_ALIAS,
            self::PAYSERA_METHOD_TABLE,
            self::PAYSERA_METHOD_ALIAS
        );
    }

    private function buildWhereClause(): string
    {
        $conditions = array_filter([
            $this->buildOrderIdCondition(),
            $this->buildSearchCondition(),
            $this->buildStatusCondition(),
            $this->buildDateCondition(),
            $this->buildCustomerNameCondition(),
            $this->buildCustomerEmailCondition(),
            $this->buildPaymentIdCondition(),
            $this->buildPaymentMethodCondition(),
        ], static fn (string $c): bool => $c !== '');

        if ($conditions === []) {
            return '';
        }

        return ' AND ' . implode(' AND ', $conditions);
    }

    private function buildOrderIdCondition(): string
    {
        if (!isset($this->filters['orderId']) || $this->filters['orderId'] === '') {
            return '';
        }

        $orderId = pSQL((string) $this->filters['orderId']);

        return sprintf('%s.id_order LIKE \'%%%s%%\'', self::TABLE_ALIAS, $orderId);
    }

    private function buildSearchCondition(): string
    {
        if (!isset($this->filters['search']) || $this->filters['search'] === '') {
            return '';
        }

        $like = '%' . pSQL((string) $this->filters['search']) . '%';

        return sprintf(
            '(%1$s.reference LIKE \'%4$s\''
            . ' OR %1$s.id_order LIKE \'%4$s\''
            . ' OR %2$s.paysera_payment_id LIKE \'%4$s\''
            . ' OR %3$s.email LIKE \'%4$s\''
            . ' OR CONCAT_WS(\' \', %3$s.firstname, %3$s.lastname) LIKE \'%4$s\')',
            self::TABLE_ALIAS,
            self::PAYSERA_PAYMENT_ALIAS,
            self::CUSTOMER_TABLE_ALIAS,
            $like
        );
    }

    private function buildStatusCondition(): string
    {
        if (!isset($this->filters['status'])) {
            return '';
        }

        $status = $this->filters['status'];
        $values = is_array($status) ? $status : [$status];

        $escaped = [];
        foreach ($values as $value) {
            $value = (string) $value;
            if ($value === '') {
                continue;
            }

            $escaped[] = '\'' . pSQL($value) . '\'';
        }

        if ($escaped === []) {
            return '';
        }

        return sprintf('%s.status IN (%s)', self::PAYSERA_PAYMENT_ALIAS, implode(', ', $escaped));
    }

    private function buildDateCondition(): string
    {
        if (!isset($this->filters['date']) || $this->filters['date'] === '') {
            return '';
        }

        $date = $this->filters['date'];

        if (is_array($date)) {
            $from = $this->normalizeDate($date['from'] ?? null);
            $to = $this->normalizeDate($date['to'] ?? null);

            if ($from !== null && $to !== null) {
                return sprintf(
                    '%s.date_add BETWEEN \'%s 00:00:00\' AND \'%s 23:59:59\'',
                    self::TABLE_ALIAS,
                    $from,
                    $to
                );
            }

            if ($from !== null) {
                return sprintf('%s.date_add >= \'%s 00:00:00\'', self::TABLE_ALIAS, $from);
            }

            if ($to !== null) {
                return sprintf('%s.date_add <= \'%s 23:59:59\'', self::TABLE_ALIAS, $to);
            }

            return '';
        }

        $normalized = $this->normalizeDate((string) $date);
        if ($normalized === null) {
            return '';
        }

        return sprintf('DATE(%s.date_add) = \'%s\'', self::TABLE_ALIAS, $normalized);
    }

    private function buildCustomerNameCondition(): string
    {
        if (!isset($this->filters['customerName']) || $this->filters['customerName'] === '') {
            return '';
        }

        $name = pSQL((string) $this->filters['customerName']);

        return sprintf(
            '%s.id_customer IN (SELECT id_customer FROM `%scustomer` '
            . 'WHERE CONCAT_WS(\' \', firstname, lastname) LIKE \'%%%s%%\')',
            self::TABLE_ALIAS,
            _DB_PREFIX_,
            $name
        );
    }

    private function buildCustomerEmailCondition(): string
    {
        if (!isset($this->filters['customerEmail']) || $this->filters['customerEmail'] === '') {
            return '';
        }

        $email = pSQL((string) $this->filters['customerEmail']);

        return sprintf(
            '%s.id_customer IN (SELECT id_customer FROM `%scustomer` WHERE email LIKE \'%%%s%%\')',
            self::TABLE_ALIAS,
            _DB_PREFIX_,
            $email
        );
    }

    private function buildPaymentIdCondition(): string
    {
        if (!isset($this->filters['paymentId']) || $this->filters['paymentId'] === '') {
            return '';
        }

        $paymentId = pSQL((string) $this->filters['paymentId']);

        return sprintf(
            '%s.id_order IN (SELECT id_order FROM `%spaysera_payment` WHERE paysera_payment_id LIKE \'%%%s%%\')',
            self::TABLE_ALIAS,
            _DB_PREFIX_,
            $paymentId
        );
    }

    private function buildPaymentMethodCondition(): string
    {
        if (!isset($this->filters['paymentMethod'])) {
            return '';
        }

        $methods = $this->filters['paymentMethod'];

        if (is_array($methods)) {
            $escaped = array_values(array_filter(array_map(
                static fn ($value): string => pSQL((string) $value),
                $methods
            ), static fn (string $value): bool => $value !== ''));

            if ($escaped === []) {
                return '';
            }

            $quoted = array_map(static fn (string $value): string => "'" . $value . "'", $escaped);

            return sprintf('%s.payment_method IN (%s)', self::PAYSERA_PAYMENT_ALIAS, implode(', ', $quoted));
        }

        if ($methods === '') {
            return '';
        }

        return sprintf('%s.payment_method = \'%s\'', self::PAYSERA_PAYMENT_ALIAS, pSQL((string) $methods));
    }

    private function normalizeDate(?string $date): ?string
    {
        if ($date === null || $date === '') {
            return null;
        }

        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date) !== 1) {
            return null;
        }

        [$year, $month, $day] = array_map('intval', explode('-', $date));
        if (!checkdate($month, $day, $year)) {
            return null;
        }

        return $date;
    }

    private function sanitizeOrderBy(string $orderBy): string
    {
        if (!array_key_exists($orderBy, self::ORDER_BY_EXPRESSIONS)) {
            return self::DEFAULT_ORDER_BY;
        }

        return $orderBy;
    }

    private function sanitizeDirection(string $direction): string
    {
        $direction = strtoupper($direction);

        if (!in_array($direction, self::ALLOWED_DIRECTION, true)) {
            return self::DEFAULT_DIRECTION;
        }

        return $direction;
    }
}
