<?php
declare(strict_types=1);

namespace Help4BuilderSuite\Modules\Commerce;

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

// Custom catalog tables are intentional for large product sets.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

final class ProductRepository
{
    private string $table;
    private string $variantTable;

    public function __construct()
    {
        global $wpdb;
        $this->table        = $wpdb->prefix . 'h4_products';
        $this->variantTable = $wpdb->prefix . 'h4_product_variants';
    }

    /**
     * @param array<string, mixed> $args
     * @return array<int, array<string, mixed>>
     */
    public function list(array $args = []): array
    {
        global $wpdb;

        $status = sanitize_key((string) ($args['status'] ?? ''));
        $search = sanitize_text_field((string) ($args['search'] ?? ''));
        $searchMode = sanitize_key((string) ($args['search_mode'] ?? 'prefix'));
        if (! in_array($searchMode, ['prefix', 'contains', 'exact'], true)) {
            $searchMode = 'prefix';
        }
        $sort = sanitize_key((string) ($args['sort'] ?? 'updated_desc'));
        if (! in_array($sort, ['updated_desc', 'updated_asc', 'price_desc', 'price_asc', 'name_asc', 'name_desc', 'sku_asc', 'sku_desc', 'id_desc', 'id_asc'], true)) {
            $sort = 'updated_desc';
        }
        $limit  = max(1, min(100, (int) ($args['limit'] ?? 24)));
        $offset = max(0, (int) ($args['offset'] ?? 0));
        $minPrice = max(0, (float) ($args['min_price'] ?? 0));
        $maxPrice = max(0, (float) ($args['max_price'] ?? 0));
        $inStockOnly = ! empty($args['in_stock_only']);
        $cursorUpdatedAt = sanitize_text_field((string) ($args['cursor_updated_at'] ?? ''));
        $cursorId = absint($args['cursor_id'] ?? 0);
        $useCursor = $cursorUpdatedAt !== '' && $cursorId > 0 && in_array($sort, ['updated_desc', 'updated_asc'], true);

        $where  = ['1=1'];
        $params = [];

        if ($status !== '') {
            $where[]  = 'status = %s';
            $params[] = $status;
        }

        if ($search !== '') {
            $likePrefix = $wpdb->esc_like($search) . '%';
            if ($searchMode === 'exact') {
                $where[]   = '(sku = %s OR slug = %s OR name = %s)';
                $params[]  = $search;
                $params[]  = $search;
                $params[]  = $search;
            } elseif ($searchMode === 'contains') {
                $likeContains = '%' . $wpdb->esc_like($search) . '%';
                $where[]   = '(sku = %s OR sku LIKE %s OR slug = %s OR slug LIKE %s OR name LIKE %s)';
                $params[]  = $search;
                $params[]  = $likePrefix;
                $params[]  = $search;
                $params[]  = $likePrefix;
                $params[]  = $likeContains;
            } else {
                // Prefix mode keeps searches index-friendly for large catalogs.
                $where[]   = '(sku = %s OR sku LIKE %s OR slug = %s OR slug LIKE %s OR name LIKE %s)';
                $params[]  = $search;
                $params[]  = $likePrefix;
                $params[]  = $search;
                $params[]  = $likePrefix;
                $params[]  = $likePrefix;
            }
        }

        if ($useCursor) {
            if ($sort === 'updated_asc') {
                $where[] = '(updated_at > %s OR (updated_at = %s AND id > %d))';
                $params[] = $cursorUpdatedAt;
                $params[] = $cursorUpdatedAt;
                $params[] = $cursorId;
            } else {
                $where[] = '(updated_at < %s OR (updated_at = %s AND id < %d))';
                $params[] = $cursorUpdatedAt;
                $params[] = $cursorUpdatedAt;
                $params[] = $cursorId;
            }
        }

        if ($inStockOnly) {
            $where[] = 'stock_qty > 0';
        }

        if ($minPrice > 0) {
            $where[] = 'price >= %f';
            $params[] = $minPrice;
        }
        if ($maxPrice > 0 && $maxPrice >= $minPrice) {
            $where[] = 'price <= %f';
            $params[] = $maxPrice;
        }

        array_unshift($params, $this->table);
        $params[] = $limit;
        $sql = 'SELECT * FROM %i WHERE ' . implode(' AND ', $where) . ' ORDER BY ' . $this->catalogOrderBySql($sort) . ' LIMIT %d';
        if ($useCursor) {
            $rows = $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is built from a table identifier, whitelisted sort fragments, and generated placeholders only.
                $wpdb->prepare($sql, ...$params),
                ARRAY_A
            );
        } else {
            $params[] = $offset;
            $sql .= ' OFFSET %d';
            $rows = $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is built from a table identifier, whitelisted sort fragments, and generated placeholders only.
                $wpdb->prepare($sql, ...$params),
                ARRAY_A
            );
        }

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

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

        if (! is_array($row)) {
            return null;
        }

        return $row;
    }

    /**
     * @return array<string, mixed>|null
     */
    public function getBySku(string $sku): ?array
    {
        if ($sku === '') {
            return null;
        }

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

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

    /**
     * @param array<string, mixed> $product
     */
    public function create(array $product): int
    {
        global $wpdb;

        $slug = sanitize_title((string) ($product['slug'] ?? $product['name'] ?? 'product'));
        $data = [
            'sku'      => sanitize_text_field((string) ($product['sku'] ?? '')),
            'name'     => sanitize_text_field((string) ($product['name'] ?? 'Untitled Product')),
            'slug'     => $slug ?: 'product-' . wp_rand(10, 99999),
            'status'   => sanitize_key((string) ($product['status'] ?? 'active')),
            'price'    => (float) ($product['price'] ?? 0),
            'currency' => strtoupper(sanitize_text_field((string) ($product['currency'] ?? 'USD'))),
            'stock_qty'=> (int) ($product['stock_qty'] ?? 0),
            'payload'  => wp_json_encode($product['payload'] ?? []),
        ];

        $wpdb->insert(
            $this->table,
            $data,
            ['%s', '%s', '%s', '%s', '%f', '%s', '%d', '%s']
        );

        return (int) $wpdb->insert_id;
    }

    /**
     * @param array<string, mixed> $product
     */
    public function update(int $id, array $product): bool
    {
        global $wpdb;

        $fields = [];
        $format = [];

        if (isset($product['sku'])) {
            $fields['sku'] = sanitize_text_field((string) $product['sku']);
            $format[] = '%s';
        }
        if (isset($product['name'])) {
            $fields['name'] = sanitize_text_field((string) $product['name']);
            $format[] = '%s';
        }
        if (isset($product['slug'])) {
            $fields['slug'] = sanitize_title((string) $product['slug']);
            $format[] = '%s';
        }
        if (isset($product['status'])) {
            $fields['status'] = sanitize_key((string) $product['status']);
            $format[] = '%s';
        }
        if (isset($product['price'])) {
            $fields['price'] = (float) $product['price'];
            $format[] = '%f';
        }
        if (isset($product['currency'])) {
            $fields['currency'] = strtoupper(sanitize_text_field((string) $product['currency']));
            $format[] = '%s';
        }
        if (isset($product['stock_qty'])) {
            $fields['stock_qty'] = (int) $product['stock_qty'];
            $format[] = '%d';
        }
        if (isset($product['payload'])) {
            $fields['payload'] = wp_json_encode($product['payload']);
            $format[] = '%s';
        }

        if (! $fields) {
            return false;
        }

        $updated = $wpdb->update($this->table, $fields, ['id' => $id], $format, ['%d']);
        return $updated !== false;
    }

    public function delete(int $id): bool
    {
        global $wpdb;
        $wpdb->delete($this->variantTable, ['product_id' => $id], ['%d']);
        $deleted = $wpdb->delete($this->table, ['id' => $id], ['%d']);
        return $deleted !== false;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function listVariants(int $productId): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE product_id = %d ORDER BY position ASC, id ASC',
                $this->variantTable,
                $productId
            ),
            ARRAY_A
        );
        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<string, mixed>|null
     */
    public function getVariantBySku(int $productId, string $sku): ?array
    {
        $sku = sanitize_text_field($sku);
        if ($sku === '') {
            return null;
        }

        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE product_id = %d AND sku = %s LIMIT 1',
                $this->variantTable,
                $productId,
                $sku
            ),
            ARRAY_A
        );

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

    /**
     * @param array<string, mixed> $variant
     */
    public function createVariant(int $productId, array $variant): int
    {
        global $wpdb;
        $wpdb->insert(
            $this->variantTable,
            [
                'product_id'       => $productId,
                'sku'              => sanitize_text_field((string) ($variant['sku'] ?? '')),
                'attributes_json'  => wp_json_encode($variant['attributes'] ?? []),
                'price'            => (float) ($variant['price'] ?? 0),
                'stock_qty'        => (int) ($variant['stock_qty'] ?? 0),
                'image_id'         => absint($variant['image_id'] ?? 0),
                'position'         => (int) ($variant['position'] ?? 0),
            ],
            ['%d', '%s', '%s', '%f', '%d', '%d', '%d']
        );

        return (int) $wpdb->insert_id;
    }

    /**
     * @param array<string, mixed> $variant
     */
    public function updateVariant(int $variantId, array $variant): bool
    {
        global $wpdb;
        $fields = [];
        $format = [];
        if (isset($variant['sku'])) {
            $fields['sku'] = sanitize_text_field((string) $variant['sku']);
            $format[] = '%s';
        }
        if (isset($variant['attributes'])) {
            $fields['attributes_json'] = wp_json_encode($variant['attributes']);
            $format[] = '%s';
        }
        if (isset($variant['price'])) {
            $fields['price'] = (float) $variant['price'];
            $format[] = '%f';
        }
        if (isset($variant['stock_qty'])) {
            $fields['stock_qty'] = (int) $variant['stock_qty'];
            $format[] = '%d';
        }
        if (isset($variant['image_id'])) {
            $fields['image_id'] = absint($variant['image_id']);
            $format[] = '%d';
        }
        if (isset($variant['position'])) {
            $fields['position'] = (int) $variant['position'];
            $format[] = '%d';
        }

        if (! $fields) {
            return false;
        }

        $updated = $wpdb->update($this->variantTable, $fields, ['id' => $variantId], $format, ['%d']);
        return $updated !== false;
    }

    public function deleteVariant(int $variantId): bool
    {
        global $wpdb;
        $deleted = $wpdb->delete($this->variantTable, ['id' => $variantId], ['%d']);
        return $deleted !== false;
    }

    public function deleteVariantsByProduct(int $productId): int
    {
        global $wpdb;
        $deleted = $wpdb->delete($this->variantTable, ['product_id' => $productId], ['%d']);
        return is_int($deleted) ? $deleted : 0;
    }

    /**
     * @param array<int, int> $keepIds
     */
    public function deleteVariantsExcept(int $productId, array $keepIds): int
    {
        global $wpdb;

        $keepIds = array_values(
            array_filter(
                array_map('absint', $keepIds),
                static fn (int $id): bool => $id > 0
            )
        );
        if (! $keepIds) {
            return $this->deleteVariantsByProduct($productId);
        }

        $deleted = $wpdb->query(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is built from internal table names and generated placeholders only.
            $wpdb->prepare('DELETE FROM %i WHERE product_id = %d AND id NOT IN (' . implode(',', array_fill(0, count($keepIds), '%d')) . ')', ...array_merge([$this->variantTable, $productId], $keepIds))
        );

        return is_int($deleted) ? $deleted : 0;
    }

    public function claimStock(int $productId, int $variantId, int $quantity, bool $allowBackorder): bool
    {
        if ($productId <= 0 || $quantity <= 0) {
            return false;
        }

        global $wpdb;
        if ($variantId > 0) {
            if ($allowBackorder) {
                $updated = $wpdb->query(
                    $wpdb->prepare(
                        'UPDATE %i SET stock_qty = stock_qty - %d WHERE id = %d AND product_id = %d',
                        $this->variantTable,
                        $quantity,
                        $variantId,
                        $productId
                    )
                );
            } else {
                $updated = $wpdb->query(
                    $wpdb->prepare(
                        'UPDATE %i SET stock_qty = stock_qty - %d WHERE id = %d AND product_id = %d AND stock_qty >= %d',
                        $this->variantTable,
                        $quantity,
                        $variantId,
                        $productId,
                        $quantity
                    )
                );
            }
        } elseif ($allowBackorder) {
            $updated = $wpdb->query(
                $wpdb->prepare(
                    'UPDATE %i SET stock_qty = stock_qty - %d WHERE id = %d',
                    $this->table,
                    $quantity,
                    $productId
                )
            );
        } else {
            $updated = $wpdb->query(
                $wpdb->prepare(
                    'UPDATE %i SET stock_qty = stock_qty - %d WHERE id = %d AND stock_qty >= %d',
                    $this->table,
                    $quantity,
                    $productId,
                    $quantity
                )
            );
        }

        return $updated === 1;
    }

    public function releaseStock(int $productId, int $variantId, int $quantity): bool
    {
        if ($productId <= 0 || $quantity <= 0) {
            return false;
        }

        global $wpdb;
        if ($variantId > 0) {
            $updated = $wpdb->query(
                $wpdb->prepare(
                    'UPDATE %i SET stock_qty = stock_qty + %d WHERE id = %d AND product_id = %d',
                    $this->variantTable,
                    $quantity,
                    $variantId,
                    $productId
                )
            );
        } else {
            $updated = $wpdb->query(
                $wpdb->prepare(
                    'UPDATE %i SET stock_qty = stock_qty + %d WHERE id = %d',
                    $this->table,
                    $quantity,
                    $productId
                )
            );
        }

        return $updated === 1;
    }

    public function stockQuantity(int $productId, int $variantId = 0): ?int
    {
        if ($productId <= 0) {
            return null;
        }

        global $wpdb;
        if ($variantId > 0) {
            $value = $wpdb->get_var(
                $wpdb->prepare(
                    'SELECT stock_qty FROM %i WHERE id = %d AND product_id = %d LIMIT 1',
                    $this->variantTable,
                    $variantId,
                    $productId
                )
            );
        } else {
            $value = $wpdb->get_var(
                $wpdb->prepare('SELECT stock_qty FROM %i WHERE id = %d LIMIT 1', $this->table, $productId)
            );
        }

        return $value === null ? null : (int) $value;
    }

    private function catalogOrderBySql(string $sort): string
    {
        return match ($sort) {
            'updated_asc' => 'updated_at ASC, id ASC',
            'price_desc' => 'price DESC, id DESC',
            'price_asc'  => 'price ASC, id ASC',
            'name_asc'   => 'name ASC, id ASC',
            'name_desc'  => 'name DESC, id DESC',
            'sku_asc'    => 'sku ASC, id ASC',
            'sku_desc'   => 'sku DESC, id DESC',
            'id_desc'    => 'id DESC',
            'id_asc'     => 'id ASC',
            default      => 'updated_at DESC, id DESC',
        };
    }
}
