<?php
declare(strict_types=1);

namespace Help4BuilderSuite\Modules\Commerce;

use WP_Error;

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

// Custom commerce tables are intentional and every dynamic value uses a prepared placeholder.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

final class PromotionRepository
{
    private string $promotionsTable;
    private string $redemptionsTable;

    public function __construct()
    {
        global $wpdb;
        $this->promotionsTable = $wpdb->prefix . 'h4_promotions';
        $this->redemptionsTable = $wpdb->prefix . 'h4_promotion_redemptions';
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function list(int $limit = 200): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT * FROM %i ORDER BY status ASC, updated_at DESC, id DESC LIMIT %d',
                $this->promotionsTable,
                max(1, min(1000, $limit))
            ),
            ARRAY_A
        );

        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<string, mixed>|null
     */
    public function get(int $promotionId): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM %i WHERE id = %d LIMIT 1', $this->promotionsTable, $promotionId),
            ARRAY_A
        );

        return is_array($row) ? $row : null;
    }

    /**
     * @return array<string, mixed>|null
     */
    public function getByCode(string $code): ?array
    {
        $code = $this->normalizeCode($code);
        if ($code === '') {
            return null;
        }

        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM %i WHERE code = %s LIMIT 1', $this->promotionsTable, $code),
            ARRAY_A
        );

        return is_array($row) ? $row : null;
    }

    /**
     * @param array<string, mixed> $promotion
     */
    public function save(array $promotion, int $promotionId = 0): int|WP_Error
    {
        global $wpdb;

        $code = $this->normalizeCode((string) ($promotion['code'] ?? ''));
        if ($code === '') {
            return new WP_Error('h4bs_promotion_code_required', __('Enter a promotion code.', 'help4-builder-suite'));
        }

        $type = sanitize_key((string) ($promotion['discount_type'] ?? 'percent'));
        if (! in_array($type, ['percent', 'fixed_cart', 'fixed_product', 'free_shipping'], true)) {
            $type = 'percent';
        }
        $status = sanitize_key((string) ($promotion['status'] ?? 'active'));
        if (! in_array($status, ['active', 'disabled'], true)) {
            $status = 'active';
        }

        $data = [
            'code' => $code,
            'name' => sanitize_text_field((string) ($promotion['name'] ?? $code)),
            'status' => $status,
            'discount_type' => $type,
            'amount' => max(0.0, (float) ($promotion['amount'] ?? 0)),
            'currency' => strtoupper(substr(sanitize_text_field((string) ($promotion['currency'] ?? '')), 0, 8)),
            'minimum_subtotal' => max(0.0, (float) ($promotion['minimum_subtotal'] ?? 0)),
            'maximum_discount' => max(0.0, (float) ($promotion['maximum_discount'] ?? 0)),
            'usage_limit' => max(0, (int) ($promotion['usage_limit'] ?? 0)),
            'per_customer_limit' => max(0, (int) ($promotion['per_customer_limit'] ?? 0)),
            'starts_at' => $this->nullableDate((string) ($promotion['starts_at'] ?? '')),
            'ends_at' => $this->nullableDate((string) ($promotion['ends_at'] ?? '')),
            'payload' => wp_json_encode(is_array($promotion['payload'] ?? null) ? $promotion['payload'] : []),
        ];
        $format = ['%s', '%s', '%s', '%s', '%f', '%s', '%f', '%f', '%d', '%d', '%s', '%s', '%s'];

        if ($promotionId > 0) {
            $updated = $wpdb->update($this->promotionsTable, $data, ['id' => $promotionId], $format, ['%d']);
            if ($updated === false) {
                return new WP_Error('h4bs_promotion_save_failed', __('The promotion could not be saved.', 'help4-builder-suite'));
            }
            return $promotionId;
        }

        $inserted = $wpdb->insert($this->promotionsTable, $data, $format);
        if ($inserted === false) {
            return new WP_Error('h4bs_promotion_save_failed', __('The promotion code must be unique.', 'help4-builder-suite'));
        }

        return (int) $wpdb->insert_id;
    }

    public function delete(int $promotionId): bool
    {
        if ($promotionId <= 0) {
            return false;
        }

        global $wpdb;
        $redemptions = (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM %i WHERE promotion_id = %d',
                $this->redemptionsTable,
                $promotionId
            )
        );
        if ($redemptions > 0) {
            return $wpdb->update(
                $this->promotionsTable,
                ['status' => 'disabled'],
                ['id' => $promotionId],
                ['%s'],
                ['%d']
            ) !== false;
        }

        return $wpdb->delete($this->promotionsTable, ['id' => $promotionId], ['%d']) !== false;
    }

    /**
     * Reserve one use under a row lock so global and per-customer limits cannot oversell.
     *
     * @return array<string, mixed>|WP_Error
     */
    public function claim(int $promotionId, int $orderId, string $customerEmail, float $discount): array|WP_Error
    {
        if ($promotionId <= 0 || $orderId <= 0) {
            return new WP_Error('h4bs_promotion_claim_invalid', __('The promotion reservation is invalid.', 'help4-builder-suite'));
        }

        global $wpdb;
        $customerHash = hash('sha256', strtolower(trim($customerEmail)));
        if (! $this->beginTransaction()) {
            return new WP_Error('h4bs_promotion_claim_failed', __('The promotion could not be reserved.', 'help4-builder-suite'));
        }
        try {
            $promotion = $this->lockedPromotion($promotionId);
            if (! is_array($promotion) || sanitize_key((string) ($promotion['status'] ?? '')) !== 'active') {
                $wpdb->query('ROLLBACK');
                return new WP_Error('h4bs_promotion_unavailable', __('That promotion is no longer available.', 'help4-builder-suite'));
            }

            $usageLimit = max(0, (int) ($promotion['usage_limit'] ?? 0));
            $usageCount = max(0, (int) ($promotion['usage_count'] ?? 0));
            if ($usageLimit > 0 && $usageCount >= $usageLimit) {
                $wpdb->query('ROLLBACK');
                return new WP_Error('h4bs_promotion_limit_reached', __('That promotion has reached its usage limit.', 'help4-builder-suite'));
            }

            $perCustomerLimit = max(0, (int) ($promotion['per_customer_limit'] ?? 0));
            if ($perCustomerLimit > 0) {
                $customerUses = (int) $wpdb->get_var(
                    $wpdb->prepare(
                        'SELECT COUNT(*) FROM %i WHERE promotion_id = %d AND customer_hash = %s AND status = %s',
                        $this->redemptionsTable,
                        $promotionId,
                        $customerHash,
                        'reserved'
                    )
                );
                if ($customerUses >= $perCustomerLimit) {
                    $wpdb->query('ROLLBACK');
                    return new WP_Error('h4bs_promotion_customer_limit', __('This customer has already used that promotion.', 'help4-builder-suite'));
                }
            }

            $incremented = $wpdb->update(
                $this->promotionsTable,
                ['usage_count' => $usageCount + 1],
                ['id' => $promotionId, 'usage_count' => $usageCount],
                ['%d'],
                ['%d', '%d']
            );
            if ($incremented !== 1) {
                $wpdb->query('ROLLBACK');
                return new WP_Error('h4bs_promotion_race', __('The promotion changed during checkout. Try again.', 'help4-builder-suite'));
            }

            $inserted = $wpdb->insert(
                $this->redemptionsTable,
                [
                    'promotion_id' => $promotionId,
                    'order_id' => $orderId,
                    'customer_hash' => $customerHash,
                    'code' => (string) ($promotion['code'] ?? ''),
                    'discount' => max(0.0, $discount),
                    'status' => 'reserved',
                ],
                ['%d', '%d', '%s', '%s', '%f', '%s']
            );
            if ($inserted === false) {
                $wpdb->query('ROLLBACK');
                return new WP_Error('h4bs_promotion_claim_failed', __('The promotion could not be reserved.', 'help4-builder-suite'));
            }

            $redemptionId = (int) $wpdb->insert_id;
            $wpdb->query('COMMIT');
            return [
                'redemption_id' => $redemptionId,
                'promotion_id' => $promotionId,
                'order_id' => $orderId,
                'code' => (string) ($promotion['code'] ?? ''),
                'discount' => max(0.0, $discount),
                'state' => 'reserved',
            ];
        } catch (\Throwable $throwable) {
            $wpdb->query('ROLLBACK');
            return new WP_Error('h4bs_promotion_claim_failed', __('The promotion could not be reserved.', 'help4-builder-suite'));
        }
    }

    public function releaseForOrder(int $orderId): bool
    {
        return $this->changeOrderRedemptions($orderId, 'released');
    }

    public function reclaimForOrder(int $orderId): bool
    {
        return $this->changeOrderRedemptions($orderId, 'reserved');
    }

    private function changeOrderRedemptions(int $orderId, string $targetStatus): bool
    {
        if ($orderId <= 0 || ! in_array($targetStatus, ['released', 'reserved'], true)) {
            return false;
        }

        global $wpdb;
        if (! $this->beginTransaction()) {
            return false;
        }
        try {
            $rows = $this->lockedOrderRedemptions($orderId);
            if (! is_array($rows) || ! $rows) {
                $wpdb->query('COMMIT');
                return true;
            }

            foreach ($rows as $row) {
                if (! is_array($row)) {
                    continue;
                }
                $currentStatus = sanitize_key((string) ($row['status'] ?? ''));
                if ($currentStatus === $targetStatus) {
                    continue;
                }
                $promotionId = absint($row['promotion_id'] ?? 0);
                $promotion = $this->lockedPromotion($promotionId);
                if (! is_array($promotion)) {
                    $wpdb->query('ROLLBACK');
                    return false;
                }

                $usageCount = max(0, (int) ($promotion['usage_count'] ?? 0));
                if ($targetStatus === 'reserved') {
                    $usageLimit = max(0, (int) ($promotion['usage_limit'] ?? 0));
                    if (sanitize_key((string) ($promotion['status'] ?? '')) !== 'active' || ($usageLimit > 0 && $usageCount >= $usageLimit)) {
                        $wpdb->query('ROLLBACK');
                        return false;
                    }
                    $perCustomerLimit = max(0, (int) ($promotion['per_customer_limit'] ?? 0));
                    if ($perCustomerLimit > 0) {
                        $customerUses = (int) $wpdb->get_var(
                            $wpdb->prepare(
                                'SELECT COUNT(*) FROM %i WHERE promotion_id = %d AND customer_hash = %s AND status = %s AND id <> %d',
                                $this->redemptionsTable,
                                $promotionId,
                                (string) ($row['customer_hash'] ?? ''),
                                'reserved',
                                absint($row['id'] ?? 0)
                            )
                        );
                        if ($customerUses >= $perCustomerLimit) {
                            $wpdb->query('ROLLBACK');
                            return false;
                        }
                    }
                    $nextUsageCount = $usageCount + 1;
                } else {
                    $nextUsageCount = max(0, $usageCount - 1);
                }

                if ($wpdb->update(
                    $this->promotionsTable,
                    ['usage_count' => $nextUsageCount],
                    ['id' => $promotionId, 'usage_count' => $usageCount],
                    ['%d'],
                    ['%d', '%d']
                ) !== 1) {
                    $wpdb->query('ROLLBACK');
                    return false;
                }
                if ($wpdb->update(
                    $this->redemptionsTable,
                    ['status' => $targetStatus],
                    ['id' => absint($row['id'] ?? 0), 'status' => $currentStatus],
                    ['%s'],
                    ['%d', '%s']
                ) !== 1) {
                    $wpdb->query('ROLLBACK');
                    return false;
                }
            }

            $wpdb->query('COMMIT');
            return true;
        } catch (\Throwable $throwable) {
            $wpdb->query('ROLLBACK');
            return false;
        }
    }

    public function normalizeCode(string $code): string
    {
        $code = strtoupper(sanitize_text_field($code));
        return substr((string) preg_replace('/[^A-Z0-9_-]/', '', $code), 0, 80);
    }

    private function beginTransaction(): bool
    {
        global $wpdb;
        if ($this->usesSqlite()) {
            return $wpdb->query('BEGIN IMMEDIATE TRANSACTION') !== false;
        }
        return $wpdb->query('START TRANSACTION') !== false;
    }

    /**
     * @return array<string, mixed>|null
     */
    private function lockedPromotion(int $promotionId): ?array
    {
        global $wpdb;
        if ($this->usesSqlite()) {
            $row = $wpdb->get_row(
                $wpdb->prepare('SELECT * FROM %i WHERE id = %d LIMIT 1', $this->promotionsTable, $promotionId),
                ARRAY_A
            );
        } else {
            $row = $wpdb->get_row(
                $wpdb->prepare('SELECT * FROM %i WHERE id = %d LIMIT 1 FOR UPDATE', $this->promotionsTable, $promotionId),
                ARRAY_A
            );
        }
        return is_array($row) ? $row : null;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function lockedOrderRedemptions(int $orderId): array
    {
        global $wpdb;
        if ($this->usesSqlite()) {
            $rows = $wpdb->get_results(
                $wpdb->prepare('SELECT * FROM %i WHERE order_id = %d', $this->redemptionsTable, $orderId),
                ARRAY_A
            );
        } else {
            $rows = $wpdb->get_results(
                $wpdb->prepare('SELECT * FROM %i WHERE order_id = %d FOR UPDATE', $this->redemptionsTable, $orderId),
                ARRAY_A
            );
        }
        return is_array($rows) ? $rows : [];
    }

    private function usesSqlite(): bool
    {
        global $wpdb;
        if (defined('DB_ENGINE') && strtolower((string) constant('DB_ENGINE')) === 'sqlite') {
            return true;
        }
        if (defined('DATABASE_TYPE') && strtolower((string) constant('DATABASE_TYPE')) === 'sqlite') {
            return true;
        }
        return str_contains(strtolower(get_class($wpdb)), 'sqlite');
    }

    private function nullableDate(string $value): ?string
    {
        $value = trim($value);
        if ($value === '') {
            return null;
        }
        $timestamp = strtotime($value);
        return $timestamp === false ? null : gmdate('Y-m-d H:i:s', $timestamp);
    }
}
