<?php
declare(strict_types=1);

namespace Help4BuilderSuite\Modules\Commerce;

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

// Custom review/order tables are intentional for native commerce verification.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

final class ProductReviewRepository
{
    private string $reviewsTable;
    private string $ordersTable;
    private string $orderItemsTable;

    public function __construct()
    {
        global $wpdb;
        $prefix = $wpdb->prefix;
        $this->reviewsTable = $prefix . 'h4_product_reviews';
        $this->ordersTable = $prefix . 'h4_orders';
        $this->orderItemsTable = $prefix . 'h4_order_items';
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function listForProduct(int $productId, string $status = 'approved', int $limit = 10, int $offset = 0): array
    {
        global $wpdb;

        $productId = absint($productId);
        if ($productId <= 0) {
            return [];
        }

        $limit = max(1, min(100, $limit));
        $offset = max(0, $offset);

        if ($status === 'all') {
            $query = $wpdb->prepare(
                'SELECT * FROM %i WHERE product_id = %d ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d',
                $this->reviewsTable,
                $productId,
                $limit,
                $offset
            );
        } else {
            $status = $this->sanitizeStatus($status);
            $query = $wpdb->prepare(
                'SELECT * FROM %i WHERE product_id = %d AND status = %s ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d',
                $this->reviewsTable,
                $productId,
                $status,
                $limit,
                $offset
            );
        }

        $rows = $wpdb->get_results($query, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function listAll(string $status = 'all', int $limit = 50, int $offset = 0): array
    {
        global $wpdb;

        $limit = max(1, min(500, $limit));
        $offset = max(0, $offset);

        if ($status === 'all') {
            $query = $wpdb->prepare(
                'SELECT * FROM %i ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d',
                $this->reviewsTable,
                $limit,
                $offset
            );
        } else {
            $query = $wpdb->prepare(
                'SELECT * FROM %i WHERE status = %s ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d',
                $this->reviewsTable,
                $this->sanitizeStatus($status),
                $limit,
                $offset
            );
        }
        $rows = $wpdb->get_results($query, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<string, mixed>|null
     */
    public function get(int $reviewId): ?array
    {
        global $wpdb;

        $reviewId = absint($reviewId);
        if ($reviewId <= 0) {
            return null;
        }

        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM %i WHERE id = %d LIMIT 1', $this->reviewsTable, $reviewId),
            ARRAY_A
        );
        return is_array($row) ? $row : null;
    }

    /**
     * @return array{average:float,count:int}
     */
    public function getSummaryForProduct(int $productId, string $status = 'approved'): array
    {
        global $wpdb;
        $productId = absint($productId);
        if ($productId <= 0) {
            return [
                'average' => 0.0,
                'count' => 0,
            ];
        }

        $status = $this->sanitizeStatus($status);
        $row = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT COALESCE(AVG(rating), 0) AS average_rating, COUNT(*) AS review_count
                 FROM %i
                 WHERE product_id = %d AND status = %s',
                $this->reviewsTable,
                $productId,
                $status
            ),
            ARRAY_A
        );

        if (! is_array($row)) {
            return [
                'average' => 0.0,
                'count' => 0,
            ];
        }

        return [
            'average' => max(0.0, min(5.0, (float) ($row['average_rating'] ?? 0))),
            'count' => max(0, (int) ($row['review_count'] ?? 0)),
        ];
    }

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

        $data = [
            'product_id' => absint($input['product_id'] ?? 0),
            'order_id' => absint($input['order_id'] ?? 0) ?: null,
            'customer_user_id' => absint($input['customer_user_id'] ?? 0) ?: null,
            'customer_email' => sanitize_email((string) ($input['customer_email'] ?? '')),
            'rating' => max(1, min(5, (int) ($input['rating'] ?? 5))),
            'title' => sanitize_text_field((string) ($input['title'] ?? '')),
            'review_text' => sanitize_textarea_field((string) ($input['review_text'] ?? '')),
            'status' => $this->sanitizeStatus((string) ($input['status'] ?? 'pending')),
            'verified_purchase' => ! empty($input['verified_purchase']) ? 1 : 0,
            'payload' => wp_json_encode(is_array($input['payload'] ?? null) ? $input['payload'] : []),
        ];

        if ($data['product_id'] <= 0 || $data['customer_email'] === '' || $data['review_text'] === '') {
            return 0;
        }

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

        return (int) $wpdb->insert_id;
    }

    public function setStatus(int $reviewId, string $status): bool
    {
        global $wpdb;
        $reviewId = absint($reviewId);
        if ($reviewId <= 0) {
            return false;
        }

        $updated = $wpdb->update(
            $this->reviewsTable,
            ['status' => $this->sanitizeStatus($status)],
            ['id' => $reviewId],
            ['%s'],
            ['%d']
        );

        return $updated !== false;
    }

    public function hasExistingReviewForOrderProduct(int $orderId, int $productId, int $userId, string $email): bool
    {
        global $wpdb;
        $orderId = absint($orderId);
        $productId = absint($productId);
        $userId = absint($userId);
        $email = sanitize_email($email);
        if ($orderId <= 0 || $productId <= 0 || ($userId <= 0 && $email === '')) {
            return false;
        }

        if ($userId > 0 && $email !== '') {
            $found = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id
                     FROM %i
                     WHERE order_id = %d
                       AND product_id = %d
                       AND (customer_user_id = %d OR customer_email = %s)
                     LIMIT 1",
                    $this->reviewsTable,
                    $orderId,
                    $productId,
                    $userId,
                    $email
                )
            );
        } elseif ($userId > 0) {
            $found = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id
                     FROM %i
                     WHERE order_id = %d
                       AND product_id = %d
                       AND customer_user_id = %d
                     LIMIT 1",
                    $this->reviewsTable,
                    $orderId,
                    $productId,
                    $userId
                )
            );
        } else {
            $found = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id
                     FROM %i
                     WHERE order_id = %d
                       AND product_id = %d
                       AND customer_email = %s
                     LIMIT 1",
                    $this->reviewsTable,
                    $orderId,
                    $productId,
                    $email
                )
            );
        }

        return (int) $found > 0;
    }

    public function delete(int $reviewId): bool
    {
        global $wpdb;
        $reviewId = absint($reviewId);
        if ($reviewId <= 0) {
            return false;
        }
        $deleted = $wpdb->delete($this->reviewsTable, ['id' => $reviewId], ['%d']);
        return $deleted !== false;
    }

    /**
     * @return array{verified:bool,order_id:int}
     */
    public function verifyPurchaseForCustomer(int $productId, int $userId, string $email): array
    {
        global $wpdb;

        $productId = absint($productId);
        $userId = absint($userId);
        $email = sanitize_email($email);
        if ($productId <= 0 || ($userId <= 0 && $email === '')) {
            return ['verified' => false, 'order_id' => 0];
        }

        $validStatuses = ['paid', 'completed', 'processing', 'fulfilled'];
        if ($userId > 0 && $email !== '') {
            $query = $wpdb->prepare(
                'SELECT o.id
                 FROM %i o
                 INNER JOIN %i oi ON oi.order_id = o.id
                 WHERE oi.product_id = %d
                   AND (o.customer_user_id = %d OR o.customer_email = %s)
                   AND o.status IN (%s,%s,%s,%s)
                 ORDER BY o.id DESC
                 LIMIT 1',
                $this->ordersTable,
                $this->orderItemsTable,
                $productId,
                $userId,
                $email,
                $validStatuses[0],
                $validStatuses[1],
                $validStatuses[2],
                $validStatuses[3]
            );
        } elseif ($userId > 0) {
            $query = $wpdb->prepare(
                'SELECT o.id
                 FROM %i o
                 INNER JOIN %i oi ON oi.order_id = o.id
                 WHERE oi.product_id = %d
                   AND o.customer_user_id = %d
                   AND o.status IN (%s,%s,%s,%s)
                 ORDER BY o.id DESC
                 LIMIT 1',
                $this->ordersTable,
                $this->orderItemsTable,
                $productId,
                $userId,
                $validStatuses[0],
                $validStatuses[1],
                $validStatuses[2],
                $validStatuses[3]
            );
        } else {
            $query = $wpdb->prepare(
                'SELECT o.id
                 FROM %i o
                 INNER JOIN %i oi ON oi.order_id = o.id
                 WHERE oi.product_id = %d
                   AND o.customer_email = %s
                   AND o.status IN (%s,%s,%s,%s)
                 ORDER BY o.id DESC
                 LIMIT 1',
                $this->ordersTable,
                $this->orderItemsTable,
                $productId,
                $email,
                $validStatuses[0],
                $validStatuses[1],
                $validStatuses[2],
                $validStatuses[3]
            );
        }

        $orderId = (int) $wpdb->get_var($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return [
            'verified' => $orderId > 0,
            'order_id' => $orderId > 0 ? $orderId : 0,
        ];
    }

    private function sanitizeStatus(string $status): string
    {
        $status = sanitize_key($status);
        if (! in_array($status, ['pending', 'approved', 'rejected', 'spam'], true)) {
            return 'pending';
        }
        return $status;
    }
}
