<?php

namespace WKO\Model\Repository;

use WKO\Model\Entity\InstallmentRule;
use WKO\Model\Infrastructure\Entity;
use WKO\Model\Infrastructure\Repository;

class InstallmentRules extends Repository
{
    public function __construct()
    {
        parent::__construct(InstallmentRule::class, 'wko_installment_rules');
    }

    public function getInstallmentRules($cardBrand, $cardNumber, $cartData = [])
    {
        global $wpdb;
        $brandClause = $cardBrand ? $wpdb->prepare("AND (card_brands LIKE %s OR card_brands IS NULL OR card_brands = '')", '%' . $wpdb->esc_like($cardBrand) . "%") : '';
        $cardClause = $cardNumber ? $wpdb->prepare(
            "AND (
                (payment_method_bins IS NULL)
                OR (payment_method_bins = '')
                OR (%s REGEXP payment_method_bins)
                OR (%s REGEXP payment_method_bins)
                OR (%s REGEXP payment_method_bins)
                )
                AND (
                    (exclude_payment_method_bins IS NULL)
                    OR (exclude_payment_method_bins = '')
                    OR (%s NOT REGEXP exclude_payment_method_bins)
                    OR (%s NOT REGEXP exclude_payment_method_bins)
                    OR (%s NOT REGEXP exclude_payment_method_bins)
                )",
            substr($cardNumber, 0, 6),
            substr($cardNumber, 0, 8),
            $cardNumber,
            substr($cardNumber, 0, 6),
            substr($cardNumber, 0, 8),
            $cardNumber
        ) : '';

        $additionalClauses = '';

        if (!empty($cartData)) {
            $filters = [
                'products' => $cartData['product_ids'] ?? [],
                'brands' => $cartData['brand_ids'] ?? [],
                'categories' => $cartData['category_ids'] ?? []
            ];

            $additionalClauses = $this->buildCartFilters($wpdb, $filters);
        }

        $time = current_time('mysql');

        $query = $this->db->prepare(
            "SELECT *
            FROM {$this->table}
            WHERE enable_rule = 1
            $brandClause
            $cardClause
            $additionalClauses
            AND start_date <= %s AND (end_date >= %s OR end_date IS NULL)
            ORDER BY updated_at ASC
            ",
            $time,
            $time
        );

        $rows = $this->db->get_results($query);
        return $this->fillAll($rows);
    }

    public function fillAll($rows)
    {
        $entities = [];

        if (!$rows) {
            return $entities;
        }

        foreach ($rows as $row) {
            $entities[] = $this->fill($row);
        }

        return $entities;
    }

    public function findByProduct(int $productId, string $orderBy = '', string $order = 'ASC'): array
    {

        $query = $this->db->prepare("SELECT * FROM {$this->table}
                  WHERE JSON_CONTAINS(products, %s)", "\"{$productId}\"");

        if ($orderBy) {
            $order = strtoupper($order) === 'DESC' ? 'DESC' : 'ASC';
            $query .= " ORDER BY $orderBy $order";
        }

        $rows = $this->query($query) ?? [];
        return $this->fillAll($rows);
    }

    /**
     * Build cart-based filters for products, brands, and categories
     *
     * @param \wpdb $wpdb WordPress database instance
     * @param array $filters Associative array with field => ids mapping
     * @return string Combined SQL clauses
     */
    private function buildCartFilters($wpdb, $filters)
    {
        $clauses = [];

        foreach ($filters as $field => $ids) {
            $clauses[] = $this->buildJsonFilterClause($wpdb, $field, $ids);
        }

        return implode(' ', $clauses);
    }

    /**
     * Build JSON filter clause for a given field and IDs
     *
     * @param \wpdb $wpdb WordPress database instance
     * @param string $fieldName Field name (products, brands, categories)
     * @param array $ids Array of IDs to filter
     * @return string SQL clause
     */
    private function buildJsonFilterClause($wpdb, $fieldName, $ids)
    {
        $allowedFields = ['products', 'brands', 'categories'];
        if (!in_array($fieldName, $allowedFields, true)) {
            return '';
        }

        $jsonContains = [];
        foreach ($ids as $id) {
            $id = intval($id);
            $jsonContains[] = $wpdb->prepare('JSON_CONTAINS(%i, %s)', $fieldName, "\"{$id}\"");
        }

        return $wpdb->prepare(
            "AND (%i IS NULL OR %i IN ('', '[]')" . ($jsonContains ?  " OR (" . implode(' OR ', $jsonContains) . ")" : "") . ")",
            $fieldName,
            $fieldName
        );
    }

    protected function fill(\stdClass $row): InstallmentRule
    {
        $entity = new InstallmentRule();
        $entity->id = $row->id;
        $entity->enableRule = $row->enable_rule;
        $entity->overrideRules = $row->override_rules;
        $entity->ruleTitle = $row->rule_title;
        $entity->accountNumber = $row->account_number;
        $entity->minInstallments = $row->min_installments;
        $entity->maxInstallments = $row->max_installments;
        $entity->minInstallmentValue = $row->min_installment_value;
        $entity->rateTransferType = $row->rate_transfer_type;
        $entity->rateTransferFormat = $row->rate_transfer_format;
        $entity->rateTransferSimple = $row->rate_transfer_simple;
        $entity->rateTransferByInstallments = $row->rate_transfer_by_installments;
        $entity->installmentDisplay = isset($row->installment_display) ? $row->installment_display : '';
        $entity->startDate = $row->start_date ? new \DateTime($row->start_date) : null;
        $entity->endDate = $row->end_date ? new \DateTime($row->end_date) : null;
        $entity->paymentMethodBins = $row->payment_method_bins;
        $entity->excludePaymentMethodBins = $row->exclude_payment_method_bins;
        $entity->cardBrands = $row->card_brands;
        $entity->products = $row->products;
        $entity->brands = $row->brands;
        $entity->categories = $row->categories;
        $entity->setCreatedAt(new \DateTime($row->created_at));
        $entity->setUpdatedAt(new \DateTime($row->updated_at));

        return $entity;
    }

    protected function getEntityData(Entity $entity): array
    {
        return [
            'rule_title' => $entity->ruleTitle,
            'account_number' => $entity->accountNumber,
            'min_installments' => $entity->minInstallments,
            'max_installments' => $entity->maxInstallments,
            'min_installment_value' => $entity->minInstallmentValue,
            'rate_transfer_type' => $entity->rateTransferType,
            'rate_transfer_format' => $entity->rateTransferFormat,
            'rate_transfer_simple' => $entity->rateTransferSimple,
            'rate_transfer_by_installments' => html_entity_decode($entity->rateTransferByInstallments),
            'installment_display' => $entity->installmentDisplay ?? '',
            'start_date' => $entity->startDate ? $entity->startDate->format('Y-m-d H:i:s') : null,
            'end_date' => $entity->endDate ? $entity->endDate->format('Y-m-d H:i:s') : null,
            'payment_method_bins' => $entity->paymentMethodBins,
            'exclude_payment_method_bins' => $entity->excludePaymentMethodBins,
            'card_brands' => $entity->cardBrands ? $entity->cardBrands : '',
            'products' => $entity->products ? $entity->products : '',
            'brands' => $entity->brands ? $entity->brands : '',
            'categories' => $entity->categories ? $entity->categories : '',
            'enable_rule' => $entity->enableRule,
            'override_rules' => $entity->overrideRules,

        ];
    }

    public function up(): void
    {
        $this->create([
            'id' => ['INT AUTO_INCREMENT primary key'],
            'enable_rule' => ['BOOLEAN'],
            'override_rules' => ['BOOLEAN'],
            'start_date' => ['DATETIME'],
            'end_date' => ['DATETIME'],
            'rule_title' => ['TEXT'],
            'account_number' => ['TEXT'],
            'min_installments' => ['INT'],
            'max_installments' => ['INT'],
            'min_installment_value' => ['FLOAT'],
            'rate_transfer_type' => ['INT'],
            'rate_transfer_format' => ['INT'],
            'rate_transfer_simple' => ['TEXT'],
            'rate_transfer_by_installments' => ['LONGTEXT'],
            'installment_display' => ['TEXT'],
            'payment_method_bins' => ['TEXT'],
            'exclude_payment_method_bins' => ['TEXT'],
            'card_brands' => ['TEXT'],
            'products' => ['LONGTEXT'],
            'brands' => ['LONGTEXT'],
            'categories' => ['LONGTEXT'],
            'created_at' => ['DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP'],
            'updated_at' => ['DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'],
        ]);
    }
}
