<?php

require_once BASE_PATH
    . '/config/database.php';


class ProductReview
{
    private PDO $db;


    public function __construct()
    {
        $database = new Database();

        $this->db =
            $database->connect();
    }


    // ========================================================
    // CUSTOMER
    // ========================================================

    public function getForCustomer(
        int $customerId
    ): array {

        $stmt =
            $this->db->prepare("
                SELECT
                    pr.*,

                    p.name AS product_name,
                    p.slug AS product_slug,
                    p.featured_image

                FROM product_reviews pr

                INNER JOIN products p
                    ON p.id = pr.product_id

                WHERE pr.customer_id = :customer_id

                ORDER BY pr.created_at DESC
            ");


        $stmt->execute([
            'customer_id' =>
                $customerId
        ]);


        return $stmt->fetchAll();
    }


    public function getReviewableItems(
        int $customerId
    ): array {

        $stmt =
            $this->db->prepare("
                SELECT
                    oi.id AS order_item_id,
                    oi.product_id,
                    oi.product_name,
                    oi.quantity,

                    o.order_code,
                    o.completed_at,

                    p.slug AS product_slug,
                    p.featured_image

                FROM order_items oi

                INNER JOIN orders o
                    ON o.id = oi.order_id

                INNER JOIN products p
                    ON p.id = oi.product_id

                LEFT JOIN product_reviews pr
                    ON pr.order_item_id = oi.id

                WHERE o.customer_id = :customer_id
                  AND o.status = 'completed'
                  AND pr.id IS NULL

                ORDER BY o.completed_at DESC
            ");


        $stmt->execute([
            'customer_id' =>
                $customerId
        ]);


        return $stmt->fetchAll();
    }


    public function createForCustomer(
        int $customerId,
        int $orderItemId,
        int $rating,
        ?string $comment
    ): int {

        $this->db->beginTransaction();


        try {

            $stmt =
                $this->db->prepare("
                    SELECT
                        oi.id,
                        oi.product_id,

                        o.customer_id,
                        o.status

                    FROM order_items oi

                    INNER JOIN orders o
                        ON o.id = oi.order_id

                    WHERE oi.id = :order_item_id

                    LIMIT 1

                    FOR UPDATE
                ");


            $stmt->execute([
                'order_item_id' =>
                    $orderItemId
            ]);


            $item =
                $stmt->fetch();


            if (
                !$item
                || (int)
                $item['customer_id']
                !== $customerId
            ) {

                throw new RuntimeException(
                    'Sản phẩm đánh giá không hợp lệ.'
                );
            }


            if (
                $item['status']
                !== 'completed'
            ) {

                throw new RuntimeException(
                    'Chỉ có thể đánh giá sản phẩm từ đơn hàng đã hoàn thành.'
                );
            }


            if (
                $rating < 1
                || $rating > 5
            ) {

                throw new RuntimeException(
                    'Số sao phải từ 1 đến 5.'
                );
            }


            $stmt =
                $this->db->prepare("
                    SELECT COUNT(*)

                    FROM product_reviews

                    WHERE order_item_id =
                        :order_item_id
                ");


            $stmt->execute([
                'order_item_id' =>
                    $orderItemId
            ]);


            if (
                (int)
                $stmt->fetchColumn()
                > 0
            ) {

                throw new RuntimeException(
                    'Sản phẩm này đã được đánh giá.'
                );
            }


            $stmt =
                $this->db->prepare("
                    INSERT INTO product_reviews (
                        customer_id,
                        product_id,
                        order_item_id,
                        rating,
                        comment,
                        status
                    )
                    VALUES (
                        :customer_id,
                        :product_id,
                        :order_item_id,
                        :rating,
                        :comment,
                        'visible'
                    )
                ");


            $stmt->execute([
                'customer_id' =>
                    $customerId,

                'product_id' =>
                    $item['product_id'],

                'order_item_id' =>
                    $orderItemId,

                'rating' =>
                    $rating,

                'comment' =>
                    $comment ?: null
            ]);


            $reviewId =
                (int)
                $this->db->lastInsertId();


            $this->db->commit();


            return $reviewId;


        } catch (Throwable $e) {

            if (
                $this->db->inTransaction()
            ) {

                $this->db->rollBack();
            }


            throw $e;
        }
    }


    // ========================================================
    // PUBLIC PRODUCT
    // ========================================================

    public function getVisibleForProduct(
        int $productId,
        int $limit = 20
    ): array {

        $limit =
            max(1, $limit);


        $stmt =
            $this->db->prepare("
                SELECT
                    pr.*,
                    u.full_name AS customer_name

                FROM product_reviews pr

                INNER JOIN users u
                    ON u.id = pr.customer_id

                WHERE pr.product_id = :product_id
                  AND pr.status = 'visible'

                ORDER BY pr.created_at DESC

                LIMIT {$limit}
            ");


        $stmt->execute([
            'product_id' =>
                $productId
        ]);


        return $stmt->fetchAll();
    }


    public function getSummary(
        int $productId
    ): array {

        $stmt =
            $this->db->prepare("
                SELECT
                    COUNT(*) AS total_reviews,

                    COALESCE(
                        AVG(rating),
                        0
                    ) AS average_rating

                FROM product_reviews

                WHERE product_id = :product_id
                  AND status = 'visible'
            ");


        $stmt->execute([
            'product_id' =>
                $productId
        ]);


        return $stmt->fetch()
            ?: [
                'total_reviews' => 0,
                'average_rating' => 0
            ];
    }


    // ========================================================
    // OWNER
    // ========================================================

    public function countForOwner(
        string $search = '',
        string $status = ''
    ): int {

        $params = [];

        $where = '1 = 1';


        if ($search !== '') {

            $where .= "
                AND (
                    p.name LIKE :search_product
                    OR u.full_name LIKE :search_customer
                    OR pr.comment LIKE :search_comment
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_product'] = $searchValue;
            $params['search_customer'] = $searchValue;
            $params['search_comment'] = $searchValue;
        }


        if (
            in_array(
                $status,
                [
                    'visible',
                    'hidden'
                ],
                true
            )
        ) {

            $where .= "
                AND pr.status = :status
            ";

            $params['status'] =
                $status;
        }


        $stmt =
            $this->db->prepare("
                SELECT COUNT(*)

                FROM product_reviews pr

                INNER JOIN products p
                    ON p.id = pr.product_id

                INNER JOIN users u
                    ON u.id = pr.customer_id

                WHERE {$where}
            ");


        $stmt->execute($params);


        return (int)
            $stmt->fetchColumn();
    }


    public function getForOwner(
        string $search = '',
        string $status = '',
        int $limit = 15,
        int $offset = 0
    ): array {

        $params = [];

        $where = '1 = 1';


        if ($search !== '') {

            $where .= "
                AND (
                    p.name LIKE :search_product
                    OR u.full_name LIKE :search_customer
                    OR pr.comment LIKE :search_comment
                )
            ";

            $searchValue = '%' . $search . '%';

            $params['search_product'] = $searchValue;
            $params['search_customer'] = $searchValue;
            $params['search_comment'] = $searchValue;
        }


        if (
            in_array(
                $status,
                [
                    'visible',
                    'hidden'
                ],
                true
            )
        ) {

            $where .= "
                AND pr.status = :status
            ";

            $params['status'] =
                $status;
        }


        $limit = max(
            1,
            $limit
        );


        $offset = max(
            0,
            $offset
        );


        $stmt =
            $this->db->prepare("
                SELECT
                    pr.*,

                    p.name AS product_name,
                    p.slug AS product_slug,
                    p.featured_image,

                    u.full_name AS customer_name,
                    u.phone AS customer_phone

                FROM product_reviews pr

                INNER JOIN products p
                    ON p.id = pr.product_id

                INNER JOIN users u
                    ON u.id = pr.customer_id

                WHERE {$where}

                ORDER BY pr.created_at DESC

                LIMIT {$limit}
                OFFSET {$offset}
            ");


        $stmt->execute($params);


        return $stmt->fetchAll();
    }


    public function findById(
        int $id
    ): ?array {

        $stmt =
            $this->db->prepare("
                SELECT
                    pr.*,

                    p.name AS product_name,
                    p.slug AS product_slug,

                    u.full_name AS customer_name

                FROM product_reviews pr

                INNER JOIN products p
                    ON p.id = pr.product_id

                INNER JOIN users u
                    ON u.id = pr.customer_id

                WHERE pr.id = :id

                LIMIT 1
            ");


        $stmt->execute([
            'id' => $id
        ]);


        $review =
            $stmt->fetch();


        return $review ?: null;
    }


    public function reply(
        int $id,
        string $reply
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE product_reviews

                SET
                    owner_reply = :owner_reply,
                    replied_at = NOW()

                WHERE id = :id
            ");


        $stmt->execute([
            'owner_reply' =>
                $reply,

            'id' =>
                $id
        ]);
    }


    public function toggleStatus(
        int $id
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE product_reviews

                SET status =
                    CASE
                        WHEN status = 'visible'
                        THEN 'hidden'
                        ELSE 'visible'
                    END

                WHERE id = :id
            ");


        $stmt->execute([
            'id' => $id
        ]);
    }

    public function updateModerationStatus(
        int $id,
        string $status
    ): void {
        $stmt = $this->db->prepare(
            'UPDATE product_reviews SET status = :status WHERE id = :id'
        );
        $stmt->execute([
            'status' => $status,
            'id' => $id
        ]);
    }
}
