from datetime import datetime
from importlib.resources import as_file
from typing import Optional, List

from sqlalchemy import select, desc, func, update
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from src.cache.cache import CATEGORIES_CACHE, CATEGORIES_SEO_CACHE, PRODUCTS_SEO_CACHE
from src.category.models import Category
from src.customer.models import Customer
from src.error import _ERROR
from src.main import PRODUCTS_CACHE
from src.product.models import Product
from src.public.schemas import ProductReviewOut, StoreReviewOut, CustomerOutReview, StoreOut, StoreOutReview, \
    ServiceReviewOut, ManufacturerOutReview, ManufacturerReviewOut
from src.review.models import ProductReview, ReviewStatusEnum, StoreReview, ServiceReview, ManufacturerReview, \
    ReviewResponse
from src.store.models import Store, StoreAddress, ProductStore
from src.manufacturer.models import Manufacturer
from src.address.models import City, Zone
from src.cart.models import CurrentCart, SelectedProduct


class ServicePublic:
    @staticmethod
    async def get_public_products(
            db: AsyncSession,
            customer_id: Optional[int] = None,
            page: int = 1,
            limit: int = 10,
            needle: Optional[str] = None,
            category_id: Optional[int] = None,
            manufacturer_id: Optional[int] = None,
            price_min: Optional[float] = None,
            price_max: Optional[float] = None,
            sort_field: str = "name",
            sort_order: str = "asc",
            filter_groups: Optional[dict] = None,
            special: Optional[bool] = False,
            selected: Optional[bool] = False,
            stores: Optional[list] = None,
    ) -> dict:
        # Получаем товары пользователя в корзине и избранных
        customer_cart_products = set()
        customer_selected_products = set()

        if customer_id:
            # Товары в текущей корзине
            cart_query = await db.execute(
                select(CurrentCart.product_id)
                .where(CurrentCart.customer_id == customer_id)
            )
            customer_cart_products = {row.product_id for row in cart_query}

            # Товары в избранных
            selected_query = await db.execute(
                select(SelectedProduct.product_id)
                .where(SelectedProduct.customer_id == customer_id)
            )
            customer_selected_products = {row.product_id for row in selected_query}

        products = list(PRODUCTS_CACHE.values())

        if needle:
            needle_lower = needle.lower()
            products = [
                p for p in products
                if needle_lower in (p.get("name") or "").lower()
            ]

        if category_id is not None:
            products = [
                p for p in products
                if any(c["id"] == category_id for c in p.get("categories", []))
            ]

        if manufacturer_id is not None:
            products = [
                p for p in products
                if p.get("manufacturer", {}).get("id") == manufacturer_id
            ]

        if special:
            products = [
                p for p in products
                if p.get("price_old") and p.get("price_old") > 0
            ]

        if selected and customer_id:
            products = [
                p for p in products
                if p.get("product_id") in customer_selected_products
            ]

        if stores:
            products = [
                p for p in products
                if any(store.get("id") in stores for store in p.get("stores", []))
            ]

        # Собираем ценовой диапазон ДО применения фильтров по цене
        prices_for_range = []
        for product in products:
            product_price = product.get("price")
            if product_price is not None:
                try:
                    price_value = float(product_price)
                    prices_for_range.append(price_value)
                except (ValueError, TypeError):
                    pass

        if price_min is not None:
            products = [p for p in products if float(p.get("price") or 0) >= price_min]
        if price_max is not None:
            products = [p for p in products if float(p.get("price") or 0) <= price_max]

        # Оптимизированная фильтрация по группам фильтров
        if filter_groups:
            filtered_products = []
            for product in products:
                # Получаем все filter_id товара один раз
                product_filter_ids = [
                    item.get("filter_data", {}).get("filter_id")
                    for item in product.get("filters", [])
                ]

                # Проверяем каждую группу (AND между группами)
                matches_all_groups = True
                for group_id, group_filter_ids in filter_groups.items():
                    # Проверяем есть ли хотя бы один фильтр из группы (OR внутри группы)
                    has_filter_from_group = any(
                        filter_id in product_filter_ids
                        for filter_id in group_filter_ids
                    )
                    if not has_filter_from_group:
                        matches_all_groups = False
                        break

                if matches_all_groups:
                    filtered_products.append(product)

            products = filtered_products

        # Сортування
        if sort_order.lower() == "random":
            import random
            random.shuffle(products)
        else:
            reverse = sort_order.lower() == "desc"
            if sort_field in ["name", "price", "date_added", "date_modify", "rating", "reviews_count"]:
                if sort_field in ["price", "rating", "reviews_count", "viewed"]:
                    products.sort(key=lambda x: x.get(sort_field) or 0, reverse=reverse)
                else:  # Для рядкових полів
                    products.sort(key=lambda x: x.get(sort_field) or "", reverse=reverse)

        # Каскадная фильтрация доступных фильтров
        available_filters = {}

        if filter_groups:
            # Для каждой группы фильтров рассчитываем доступные варианты
            selected_group_ids = set(filter_groups.keys())

            for check_group_id in selected_group_ids:
                # Создаем временные filter_groups БЕЗ текущей проверяемой группы
                temp_filter_groups = {
                    gid: fids for gid, fids in filter_groups.items()
                    if gid != check_group_id
                }

                # Фильтруем товары без учета текущей группы
                temp_products = list(PRODUCTS_CACHE.values())

                # Применяем базовые фильтры
                if needle:
                    needle_lower = needle.lower()
                    temp_products = [
                        p for p in temp_products
                        if needle_lower in (p.get("name") or "").lower()
                    ]
                if category_id is not None:
                    temp_products = [
                        p for p in temp_products
                        if any(c["id"] == category_id for c in p.get("categories", []))
                    ]
                if manufacturer_id is not None:
                    temp_products = [
                        p for p in temp_products
                        if p.get("manufacturer", {}).get("id") == manufacturer_id
                    ]
                if special:
                    temp_products = [
                        p for p in temp_products
                        if p.get("price_old") and p.get("price_old") > 0
                    ]
                if selected and customer_id:
                    temp_products = [
                        p for p in temp_products
                        if p.get("product_id") in customer_selected_products
                    ]
                if stores:
                    temp_products = [
                        p for p in temp_products
                        if any(store.get("id") in stores for store in p.get("stores", []))
                    ]
                if price_min is not None:
                    temp_products = [p for p in temp_products if float(p.get("price") or 0) >= price_min]
                if price_max is not None:
                    temp_products = [p for p in temp_products if float(p.get("price") or 0) <= price_max]

                # Применяем фильтры других групп
                if temp_filter_groups:
                    filtered_temp_products = []
                    for product in temp_products:
                        product_filter_ids = [
                            item.get("filter_data", {}).get("filter_id")
                            for item in product.get("filters", [])
                        ]

                        matches_all_groups = True
                        for group_id, group_filter_ids in temp_filter_groups.items():
                            has_filter_from_group = any(
                                filter_id in product_filter_ids
                                for filter_id in group_filter_ids
                            )
                            if not has_filter_from_group:
                                matches_all_groups = False
                                break

                        if matches_all_groups:
                            filtered_temp_products.append(product)

                    temp_products = filtered_temp_products

                # Собираем доступные фильтры для текущей проверяемой группы
                for product in temp_products:
                    product_filters = product.get("filters", [])
                    for filter_item in product_filters:
                        filter_data = filter_item.get("filter_data", {})
                        filter_group_data = filter_data.get("filter_group_data", {})

                        group_id = filter_group_data.get("filter_group_id")
                        filter_id = filter_data.get("filter_id")

                        # Собираем только фильтры для текущей проверяемой группы
                        if group_id == check_group_id and filter_id:
                            if group_id not in available_filters:
                                available_filters[group_id] = {
                                    "filter_group_id": group_id,
                                    "name": filter_group_data.get("name"),
                                    "sort_order": filter_group_data.get("sort_order", 0),
                                    "filters": {}
                                }

                            if filter_id not in available_filters[group_id]["filters"]:
                                available_filters[group_id]["filters"][filter_id] = {
                                    "filter_id": filter_id,
                                    "name": filter_data.get("name"),
                                    "sort_order": filter_data.get("sort_order", 0),
                                    "count": 0
                                }

                            available_filters[group_id]["filters"][filter_id]["count"] += 1

            # Добавляем доступные фильтры для невыбранных групп
            # Фильтруем товары со ВСЕМИ выбранными фильтрами
            temp_products = list(PRODUCTS_CACHE.values())

            # Применяем базовые фильтры
            if needle:
                needle_lower = needle.lower()
                temp_products = [
                    p for p in temp_products
                    if needle_lower in (p.get("name") or "").lower()
                ]
            if category_id is not None:
                temp_products = [
                    p for p in temp_products
                    if any(c["id"] == category_id for c in p.get("categories", []))
                ]
            if manufacturer_id is not None:
                temp_products = [
                    p for p in temp_products
                    if p.get("manufacturer", {}).get("id") == manufacturer_id
                ]
            if special:
                temp_products = [
                    p for p in temp_products
                    if p.get("special")
                ]

            if selected and customer_id:
                temp_products = [
                    p for p in temp_products
                    if p.get("product_id") in customer_selected_products
                ]
            if stores:
                temp_products = [
                    p for p in temp_products
                    if any(store.get("id") in stores for store in p.get("stores", []))
                ]
            if price_min is not None:
                temp_products = [p for p in temp_products if float(p.get("price") or 0) >= price_min]
            if price_max is not None:
                temp_products = [p for p in temp_products if float(p.get("price") or 0) <= price_max]

            # Применяем ВСЕ выбранные фильтры
            filtered_temp_products = []
            for product in temp_products:
                product_filter_ids = [
                    item.get("filter_data", {}).get("filter_id")
                    for item in product.get("filters", [])
                ]

                matches_all_groups = True
                for group_id, group_filter_ids in filter_groups.items():
                    has_filter_from_group = any(
                        filter_id in product_filter_ids
                        for filter_id in group_filter_ids
                    )
                    if not has_filter_from_group:
                        matches_all_groups = False
                        break

                if matches_all_groups:
                    filtered_temp_products.append(product)

            # Собираем доступные фильтры для всех остальных групп
            for product in filtered_temp_products:
                product_filters = product.get("filters", [])
                for filter_item in product_filters:
                    filter_data = filter_item.get("filter_data", {})
                    filter_group_data = filter_data.get("filter_group_data", {})

                    group_id = filter_group_data.get("filter_group_id")
                    filter_id = filter_data.get("filter_id")

                    # Собираем фильтры для групп, которые НЕ выбраны
                    if group_id and filter_id and group_id not in selected_group_ids:
                        if group_id not in available_filters:
                            available_filters[group_id] = {
                                "filter_group_id": group_id,
                                "name": filter_group_data.get("name"),
                                "sort_order": filter_group_data.get("sort_order", 0),
                                "filters": {}
                            }

                        if filter_id not in available_filters[group_id]["filters"]:
                            available_filters[group_id]["filters"][filter_id] = {
                                "filter_id": filter_id,
                                "name": filter_data.get("name"),
                                "sort_order": filter_data.get("sort_order", 0),
                                "count": 0
                            }

                        available_filters[group_id]["filters"][filter_id]["count"] += 1
        else:
            # Если фильтры не выбраны, показываем все доступные из текущих товаров
            for product in products:
                product_filters = product.get("filters", [])
                for filter_item in product_filters:
                    filter_data = filter_item.get("filter_data", {})
                    filter_group_data = filter_data.get("filter_group_data", {})

                    group_id = filter_group_data.get("filter_group_id")
                    filter_id = filter_data.get("filter_id")

                    if group_id and filter_id:
                        if group_id not in available_filters:
                            available_filters[group_id] = {
                                "filter_group_id": group_id,
                                "name": filter_group_data.get("name"),
                                "sort_order": filter_group_data.get("sort_order", 0),
                                "filters": {}
                            }

                        if filter_id not in available_filters[group_id]["filters"]:
                            available_filters[group_id]["filters"][filter_id] = {
                                "filter_id": filter_id,
                                "name": filter_data.get("name"),
                                "sort_order": filter_data.get("sort_order", 0),
                                "count": 0
                            }

                        available_filters[group_id]["filters"][filter_id]["count"] += 1

        # Преобразуем в финальную структуру и сортируем
        formatted_filters = []
        for group_data in available_filters.values():
            # Преобразуем фильтры в список и сортируем
            filters_list = list(group_data["filters"].values())
            filters_list.sort(key=lambda x: x["name"])

            formatted_filters.append({
                "filter_group_id": group_data["filter_group_id"],
                "name": group_data["name"],
                "sort_order": group_data["sort_order"],
                "filters": filters_list
            })

        # Сортируем группы по sort_order
        formatted_filters.sort(key=lambda x: x["sort_order"])

        # Собираем доступные магазины с количеством товаров
        available_stores = {}

        for product in products:
            # Собираем магазины
            product_stores = product.get("stores", [])
            for store in product_stores:
                store_id = store.get("id")
                if store_id:
                    if store_id not in available_stores:
                        available_stores[store_id] = {
                            "store_id": store_id,
                            "name": store.get("name"),
                            "description": store.get("description"),
                            "phone": store.get("phone"),
                            "email": store.get("email"),
                            "rating": store.get("rating"),
                            "logo": store.get("logo"),
                            "count": 0
                        }
                    available_stores[store_id]["count"] += 1

        # Формируем список магазинов отсортированный по названию
        formatted_stores = list(available_stores.values())
        formatted_stores.sort(key=lambda x: x.get("name", ""))

        # Формируем ценовой диапазон из цен, собранных ДО применения фильтров по цене
        available_price = {"min": 0, "max": 0}
        if prices_for_range:
            available_price["min"] = min(prices_for_range)
            available_price["max"] = max(prices_for_range)

        category_counts = {}
        # Собираем все ID категорий из отфильтрованных товаров
        available_categories_ids = set()
        for product in products:
            product_categories = product.get("categories", [])
            for category in product_categories:
                category_id = category.get("id")
                if category_id:
                    available_categories_ids.add(category_id)
                    if category_id not in category_counts:
                        category_counts[category_id] = 0
                    category_counts[category_id] += 1

        # Фильтруем только категории с товарами
        available_categories = [
            {**category, "total_product_count": category_counts[category.get("id_category")]}
            for category in CATEGORIES_CACHE.values()
            if category.get("id_category") in category_counts
        ]

        # Строим карту для дерева
        category_map = {c["id_category"]: {**c, "children": []} for c in available_categories}

        tree = []

        for cat in category_map.values():
            parent_id = cat.get("parent_id")
            if parent_id and parent_id in category_map:
                category_map[parent_id]["children"].append(cat)
            else:
                tree.append(cat)

        available_categories = tree

        # Добавляем флаги in_cart и in_selected к каждому товару
        for product in products:
            product_id = product.get("product_id")
            product["in_cart"] = product_id in customer_cart_products if product_id else False
            product["in_selected"] = product_id in customer_selected_products if product_id else False

        total = len(products)
        start = (page - 1) * limit
        end = start + limit
        paginated = products[start:end]

        return {
            "data": paginated,
            "total": total,
            "page": page,
            "limit": limit,
            "available_filters": formatted_filters,
            "available_stores": formatted_stores,
            "available_price": available_price,
            "available_categories": available_categories,
            "status": True,
            "category_counts": category_counts,
            "customer_selected_products" : customer_selected_products,
            "customer_id": customer_id,
        }

    @staticmethod
    async def list_categories_tree(
            page: int = 1,
            limit: int = 10,
            needle: Optional[str] = None,
            status: Optional[str] = None,
            sort_field: str = "name",
            sort_order: str = "asc",
    ) -> dict:
        categories = list(CATEGORIES_CACHE.values())

        # Фільтрація
        if needle:
            categories = [c for c in categories if needle.lower() in (c["name"] or "").lower()]
        if status is not None:
            categories = [c for c in categories if c["status"] == status]

        # Сортування
        def sort_key_function(item):
            sort_order_val = item.get("sort_order")
            secondary_value = item.get(sort_field) or ""
            if sort_order_val is not None:
                return (0, sort_order_val, secondary_value)
            return (1, secondary_value)

        reverse = sort_order.lower() == "desc"
        if sort_field in ["name", "date_added", "date_modify", "status"]:
            categories.sort(key=sort_key_function, reverse=reverse)
        else:
            categories.sort(key=lambda x: x.get(sort_field) or "", reverse=reverse)

        # Побудова дерева
        category_map = {c["id_category"]: {**c, "children": []} for c in categories}
        tree = []

        for cat in category_map.values():
            parent_id = cat.get("parent_id")
            if parent_id and parent_id in category_map:
                category_map[parent_id]["children"].append(cat)
            else:
                tree.append(cat)

        total = len(tree)
        start = (page - 1) * limit
        end = start + limit
        paginated = tree[start:end]

        return {
            "data": paginated,
            "total": total,
            "page": page,
            "limit": limit,
            "status": True,
        }

    @staticmethod
    async def get_category_id_by_seo(keyword: str):
        """
        Швидкий пошук категорії за SEO keyword через SEO-кеш
        """
        # Спочатку шукаємо в SEO-кеші - O(1)
        category = CATEGORIES_SEO_CACHE.get(keyword)

        # return CATEGORIES_SEO_CACHE

        if category is None:
            return {
                "data": None,
                "status": False
            }

        # Якщо категорія не має дітей, повертаємо її батька
        if len(category['children']) == 0:
            parent_id = category['parent_id']
            if parent_id and parent_id in CATEGORIES_CACHE:
                category = CATEGORIES_CACHE[parent_id]

        return {
            "data": category,
            "status": True
        }

    @staticmethod
    async def get_product_id_by_seo(db: AsyncSession, keyword: str, customer_id: Optional[int] = None) -> dict:
        """
        Швидкий пошук товару за SEO keyword через SEO-кеш
        """
        # Шукаємо в SEO-кеші - O(1) замість O(n)
        product_data = PRODUCTS_SEO_CACHE.get(keyword)

        if product_data:
            # Получаем данные о корзине и избранном пользователя
            customer_cart_products = set()
            customer_selected_products = set()

            if customer_id:
                cart_query = await db.execute(
                    select(CurrentCart.product_id)
                    .where(CurrentCart.customer_id == customer_id)
                )
                customer_cart_products = {row.product_id for row in cart_query}

                selected_query = await db.execute(
                    select(SelectedProduct.product_id)
                    .where(SelectedProduct.customer_id == customer_id)
                )
                customer_selected_products = {row.product_id for row in selected_query}

            product_id = product_data.get("product_id")

            # Створюємо копію даних без глибоких вкладень для уникнення рекурсії
            safe_product_data = {
                "product_id": product_id,
                "name": product_data.get("name"),
                "description": product_data.get("description"),
                "seo_keyword": product_data.get("seo_keyword"),
                "meta_title": product_data.get("meta_title"),
                "meta_description": product_data.get("meta_description"),
                "meta_keyword": product_data.get("meta_keyword"),
                "image": product_data.get("image"),
                "status": product_data.get("status"),
                "date_added": product_data.get("date_added"),
                "date_modify": product_data.get("date_modify"),
                "price": product_data.get("price"),
                "price_old": product_data.get("price_old"),
                "special": product_data.get("special"),
                "model": product_data.get("model"),
                "rating": product_data.get("rating"),
                "manufacturer": product_data.get("manufacturer"),
                "viewed": product_data.get("viewed"),
                "reviews_count": product_data.get("reviews_count"),
                "categories": product_data.get("categories", []),
                "images": product_data.get("images", []),
                "attributes": product_data.get("attributes", []),
                "in_cart": product_id in customer_cart_products if product_id else False,
                "in_selected": product_id in customer_selected_products if product_id else False,
                # Спрощуємо stores без глибоких вкладень
                "stores": [
                    {
                        "id": store.get("id"),
                        "name": store.get("name"),
                        "rating": store.get("rating"),
                        "price": store.get("price"),
                        "price_old": store.get("price_old"),
                        "seo_keyword": store.get("seo_keyword"),
                    }
                    for store in product_data.get("stores", [])
                ]
            }

            return {
                "data": safe_product_data,
                "status": True
            }

        return {
            "data": None,
            "status": False
        }
    @staticmethod
    async def get_manufacturer_id_by_seo(db: AsyncSession, keyword: str):
        stmt = select(Manufacturer).where(Manufacturer.seo_keyword == keyword)
        result = await db.execute(stmt)
        manufacturer = result.scalar_one_or_none()

        if not manufacturer:
            _ERROR("ManufacturerNotFound")

        return {
            "data" : manufacturer,
            "status": True
        }

    @staticmethod
    async def get_public_product(product_id):
        product_data = PRODUCTS_CACHE.get(product_id)

        if product_data:
            return {
                "data": product_data,
                "status": True
            }

        return {
            "data": None,
            "status": False
        }


    @staticmethod
    async def get_product_review(
            db: AsyncSession,
            product_id: int,
            page: int = 1,
            limit: int = 10
    ) -> dict:
        offset = (page - 1) * limit

        # Get total count
        count_result = await db.execute(
            select(func.count(ProductReview.product_review_id))
            .where(
                ProductReview.status == ReviewStatusEnum.approved,
                ProductReview.product_id == product_id
            )
        )
        total = count_result.scalar()

        # Get reviews with pagination
        result = await db.execute(
            select(ProductReview).options(
                selectinload(ProductReview.customer),
                selectinload(ProductReview.manufacturer)
            )
            .where(
                ProductReview.status == ReviewStatusEnum.approved,
                ProductReview.product_id == product_id
            )
            .order_by(desc(ProductReview.date_added))
            .offset(offset)
            .limit(limit)
        )

        reviews = result.scalars().all()

        reviews_out = []
        for r in reviews:
            # Отримуємо відповіді на цей відгук
            responses_result = await db.execute(
                select(ReviewResponse)
                .where(
                    ReviewResponse.review_type == "product",
                    ReviewResponse.review_id == r.product_review_id,
                    ReviewResponse.status == ReviewStatusEnum.approved
                )
                .order_by(ReviewResponse.date_added.asc())
            )
            responses = responses_result.scalars().all()

            review_responses = [
                {
                    "response_id": resp.response_id,
                    "review_type": resp.review_type,
                    "review_id": resp.review_id,
                    "author_type": resp.author_type,
                    "author_id": resp.author_id,
                    "author_name": resp.author_name,
                    "response_text": resp.response_text,
                    "date_added": resp.date_added.isoformat() if resp.date_added else None,
                    "date_modify": resp.date_modify.isoformat() if resp.date_modify else None,
                    "status": resp.status.name
                }
                for resp in responses
            ]

            reviews_out.append(
                ProductReviewOut(
                    product_review_id=r.product_review_id,
                    product_id=r.product_id,
                    manufacturer_id=r.manufacturer_id,
                    date_added=r.date_added.isoformat() if r.date_added else None,
                    date_modify=r.date_modify.isoformat() if r.date_modify else None,
                    status=r.status.name,
                    text=r.text,
                    rating=r.rating,
                    ip=r.ip,
                    has_response=r.has_response,
                    responses=review_responses,  # Додаємо відповіді
                    customer=CustomerOutReview(
                        customer_id=r.customer.customer_id,
                        name=r.customer.name,
                        phone=r.customer.phone,
                        email=r.customer.email,
                        comment=r.customer.comment,
                        logo=r.customer.logo,
                        date_added=r.customer.date_added.isoformat() if r.customer.date_added else None,
                        date_modify=r.customer.date_modify.isoformat() if r.customer.date_modify else None,
                        ip=r.customer.ip,
                        auth_type=r.customer.auth_type,
                        banned=r.customer.banned
                    ) if r.customer else None
                )
            )

        return {
            "data": reviews_out,
            "total": total,
            "page": page,
            "limit": limit,
            "status": True
        }

    @staticmethod
    async def get_store_review(
            db: AsyncSession,
            store_id: Optional[int] = None,
            page: int = 1,
            limit: int = 10
    ) -> dict:
        offset = (page - 1) * limit

        # Build count query
        count_query = (
            select(func.count(StoreReview.store_review_id))
            .where(StoreReview.status == ReviewStatusEnum.approved)
        )

        if store_id:
            count_query = count_query.where(StoreReview.store_id == store_id)

        count_result = await db.execute(count_query)
        total = count_result.scalar()

        # Build data query
        query = (
            select(StoreReview).options(
                selectinload(StoreReview.customer),
                selectinload(StoreReview.store)
            )
            .where(StoreReview.status == ReviewStatusEnum.approved)
            .order_by(desc(StoreReview.date_added))
            .offset(offset)
            .limit(limit)
        )

        if store_id:
            query = query.where(StoreReview.store_id == store_id)

        result = await db.execute(query)
        reviews = result.scalars().all()

        reviews_out = []
        for r in reviews:
            # Отримуємо кількість відгуків для цього магазину з БД
            store_reviews_count_result = await db.execute(
                select(func.count(StoreReview.store_review_id))
                .where(
                    StoreReview.store_id == r.store_id,
                    StoreReview.status == ReviewStatusEnum.approved
                )
            )
            store_reviews_count = store_reviews_count_result.scalar()

            # Отримуємо відповіді на цей відгук
            responses_result = await db.execute(
                select(ReviewResponse)
                .where(
                    ReviewResponse.review_type == "store",
                    ReviewResponse.review_id == r.store_review_id,
                    ReviewResponse.status == ReviewStatusEnum.approved
                )
                .order_by(ReviewResponse.date_added.asc())
            )
            responses = responses_result.scalars().all()

            review_responses = [
                {
                    "response_id": resp.response_id,
                    "review_type": resp.review_type,
                    "review_id": resp.review_id,
                    "author_type": resp.author_type,
                    "author_id": resp.author_id,
                    "author_name": resp.author_name,
                    "response_text": resp.response_text,
                    "date_added": resp.date_added.isoformat() if resp.date_added else None,
                    "date_modify": resp.date_modify.isoformat() if resp.date_modify else None,
                    "status": resp.status.name
                }
                for resp in responses
            ]

            reviews_out.append(
                StoreReviewOut(
                    store_review_id=r.store_review_id,
                    store_id=r.store_id,
                    customer_id=r.customer_id,
                    date_added=r.date_added.isoformat() if r.date_added else None,
                    date_modify=r.date_modify.isoformat() if r.date_modify else None,
                    status=r.status.name,
                    text=r.text,
                    rating=r.rating,
                    ip=r.ip,
                    has_response=r.has_response,
                    responses=review_responses,  # Додаємо відповіді
                    customer=CustomerOutReview(
                        customer_id=r.customer.customer_id,
                        name=r.customer.name,
                        phone=r.customer.phone,
                        email=r.customer.email,
                        comment=r.customer.comment,
                        logo=r.customer.logo,
                        date_added=r.customer.date_added.isoformat() if r.customer.date_added else None,
                        date_modify=r.customer.date_modify.isoformat() if r.customer.date_modify else None,
                        ip=r.customer.ip,
                        auth_type=r.customer.auth_type,
                        banned=r.customer.banned
                    ) if r.customer else None,
                    store=StoreOutReview(
                        store_id=r.store.store_id,
                        name=r.store.name,
                        logo=r.store.logo,
                        website=r.store.website if hasattr(r.store, 'website') else None,
                        url_youtube=r.store.url_youtube if hasattr(r.store, 'url_youtube') else None,
                        url_viber=r.store.url_viber if hasattr(r.store, 'url_viber') else None,
                        url_facebook=r.store.url_facebook if hasattr(r.store, 'url_facebook') else None,
                        url_telegram=r.store.url_telegram if hasattr(r.store, 'url_telegram') else None,
                        date_added=r.store.date_added.isoformat() if r.store.date_added else None,
                        date_modify=r.store.date_modify.isoformat() if r.store.date_modify else None,
                        reviews_count=store_reviews_count,
                    ) if r.store else None,
                )
            )

        return {
            "data": reviews_out,
            "total": total,
            "page": page,
            "limit": limit,
            "status": True
        }

    @staticmethod
    async def get_manufacturer_review(
            db: AsyncSession,
            manufacturer_id: Optional[int] = None,
            page: int = 1,
            limit: int = 10
    ) -> dict:
        offset = (page - 1) * limit

        # Build count query
        count_query = (
            select(func.count(ManufacturerReview.manufacturer_review_id))
            .where(ManufacturerReview.status == ReviewStatusEnum.approved)
        )

        if manufacturer_id:
            count_query = count_query.where(ManufacturerReview.manufacturer_id == manufacturer_id)

        count_result = await db.execute(count_query)
        total = count_result.scalar()

        # Build data query
        query = (
            select(ManufacturerReview).options(
                selectinload(ManufacturerReview.customer),
                selectinload(ManufacturerReview.manufacturer)
            )
            .where(ManufacturerReview.status == ReviewStatusEnum.approved)
            .order_by(desc(ManufacturerReview.date_added))
            .offset(offset)
            .limit(limit)
        )

        if manufacturer_id:
            query = query.where(ManufacturerReview.manufacturer_id == manufacturer_id)

        result = await db.execute(query)
        reviews = result.scalars().all()

        reviews_out = []
        for r in reviews:
            # Отримуємо кількість відгуків для цього виробника з БД
            manufacturer_reviews_count_result = await db.execute(
                select(func.count(ManufacturerReview.manufacturer_review_id))
                .where(
                    ManufacturerReview.manufacturer_id == r.manufacturer_id,
                    ManufacturerReview.status == ReviewStatusEnum.approved
                )
            )
            manufacturer_reviews_count = manufacturer_reviews_count_result.scalar()

            # Отримуємо відповіді на цей відгук
            responses_result = await db.execute(
                select(ReviewResponse)
                .where(
                    ReviewResponse.review_type == "manufacturer",
                    ReviewResponse.review_id == r.manufacturer_review_id,
                    ReviewResponse.status == ReviewStatusEnum.approved
                )
                .order_by(ReviewResponse.date_added.asc())
            )
            responses = responses_result.scalars().all()

            review_responses = [
                {
                    "response_id": resp.response_id,
                    "review_type": resp.review_type,
                    "review_id": resp.review_id,
                    "author_type": resp.author_type,
                    "author_id": resp.author_id,
                    "author_name": resp.author_name,
                    "response_text": resp.response_text,
                    "date_added": resp.date_added.isoformat() if resp.date_added else None,
                    "date_modify": resp.date_modify.isoformat() if resp.date_modify else None,
                    "status": resp.status.name
                }
                for resp in responses
            ]

            reviews_out.append(
                ManufacturerReviewOut(
                    manufacturer_review_id=r.manufacturer_review_id,
                    manufacturer_id=r.manufacturer_id,
                    customer_id=r.customer_id,
                    date_added=r.date_added.isoformat() if r.date_added else None,
                    date_modify=r.date_modify.isoformat() if r.date_modify else None,
                    status=r.status.name,
                    text=r.text,
                    rating=r.rating,
                    ip=r.ip,
                    has_response=r.has_response,
                    responses=review_responses,  # Додаємо відповіді
                    customer=CustomerOutReview(
                        customer_id=r.customer.customer_id,
                        name=r.customer.name,
                        phone=r.customer.phone,
                        email=r.customer.email,
                        comment=r.customer.comment,
                        logo=r.customer.logo,
                        date_added=r.customer.date_added.isoformat() if r.customer.date_added else None,
                        date_modify=r.customer.date_modify.isoformat() if r.customer.date_modify else None,
                        ip=r.customer.ip,
                        auth_type=r.customer.auth_type,
                        banned=r.customer.banned
                    ) if r.customer else None,
                    manufacturer=ManufacturerOutReview(
                        manufacturer_id=r.manufacturer.manufacturer_id,
                        name=r.manufacturer.name,
                        logo=r.manufacturer.logo,
                        date_added=r.manufacturer.date_added.isoformat() if r.manufacturer.date_added else None,
                        date_modify=r.manufacturer.date_modify.isoformat() if r.manufacturer.date_modify else None,
                        reviews_count=manufacturer_reviews_count,
                    ) if r.manufacturer else None,
                )
            )

        return {
            "data": reviews_out,
            "total": total,
            "page": page,
            "limit": limit,
            "status": True
        }

    @staticmethod
    async def get_service_review(
            db: AsyncSession,
            page: int = 1,
            limit: int = 10
    ) -> dict:
        offset = (page - 1) * limit

        # Get total count
        count_result = await db.execute(
            select(func.count(ServiceReview.service_review_id))
            .where(ServiceReview.status == ReviewStatusEnum.approved)
        )
        total = count_result.scalar()

        # Get reviews with pagination
        query = (
            select(ServiceReview).options(
                selectinload(ServiceReview.customer)
            )
            .where(ServiceReview.status == ReviewStatusEnum.approved)
            .order_by(desc(ServiceReview.date_added))
            .offset(offset)
            .limit(limit)
        )

        result = await db.execute(query)
        reviews = result.scalars().all()

        reviews_out = []
        for r in reviews:
            # Отримуємо відповіді на цей відгук
            responses_result = await db.execute(
                select(ReviewResponse)
                .where(
                    ReviewResponse.review_type == "service",
                    ReviewResponse.review_id == r.service_review_id,
                    ReviewResponse.status == ReviewStatusEnum.approved
                )
                .order_by(ReviewResponse.date_added.asc())
            )
            responses = responses_result.scalars().all()

            review_responses = [
                {
                    "response_id": resp.response_id,
                    "review_type": resp.review_type,
                    "review_id": resp.review_id,
                    "author_type": resp.author_type,
                    "author_id": resp.author_id,
                    "author_name": resp.author_name,
                    "response_text": resp.response_text,
                    "date_added": resp.date_added.isoformat() if resp.date_added else None,
                    "date_modify": resp.date_modify.isoformat() if resp.date_modify else None,
                    "status": resp.status.name
                }
                for resp in responses
            ]

            reviews_out.append(
                ServiceReviewOut(
                    service_review_id=r.service_review_id,
                    customer_id=r.customer_id,
                    date_added=r.date_added.isoformat() if r.date_added else None,
                    date_modify=r.date_modify.isoformat() if r.date_modify else None,
                    status=r.status.name,
                    text=r.text,
                    rating=r.rating,
                    ip=r.ip,
                    has_response=r.has_response,
                    responses=review_responses,  # Додаємо відповіді
                    customer=CustomerOutReview(
                        customer_id=r.customer.customer_id,
                        name=r.customer.name,
                        phone=r.customer.phone,
                        email=r.customer.email,
                        comment=r.customer.comment,
                        logo=r.customer.logo,
                        date_added=r.customer.date_added.isoformat() if r.customer.date_added else None,
                        date_modify=r.customer.date_modify.isoformat() if r.customer.date_modify else None,
                        ip=r.customer.ip,
                        auth_type=r.customer.auth_type,
                        banned=r.customer.banned
                    ) if r.customer else None,
                )
            )

        return {
            "data": reviews_out,
            "total": total,
            "page": page,
            "limit": limit,
            "status": True
        }

    @staticmethod
    async def get_store_id_by_seo(keyword: str, db: AsyncSession) -> dict:
        query = (
            select(
                Store,
                func.count(StoreReview.store_review_id).label("reviews_count")
            )
            .outerjoin(StoreReview,
                       (StoreReview.store_id == Store.store_id) & (StoreReview.status == ReviewStatusEnum.approved))
            .options(
                selectinload(Store.photos),
                selectinload(Store.addresses).selectinload(StoreAddress.city),
                selectinload(Store.addresses).selectinload(StoreAddress.zone),
            )
            .where(Store.seo_keyword == keyword)
            .group_by(Store.store_id)
        )

        result = await db.execute(query)
        row = result.first()

        if not row:
            return {"data": None, "status": False}

        store, reviews_count = row

        store_data = {
            "store_id": store.store_id,
            "name": store.name,
            "description": store.description,
            "address": store.address,
            "phone": store.phone,
            "email": store.email,
            "website": store.website,
            "url_youtube": store.url_youtube,
            "url_viber": store.url_viber,
            "url_facebook": store.url_facebook,
            "url_telegram": store.url_telegram,
            "status": store.status,
            "date_added": store.date_added,
            "date_modify": store.date_modify,
            "rating": store.rating,
            "seo_keyword": store.seo_keyword,
            "meta_title": store.meta_title,
            "meta_description": store.meta_description,
            "meta_keyword": store.meta_keyword,
            "logo": store.logo,
            "viewed": store.viewed,
            "reviews_count": reviews_count,
            "photos": [p.image for p in store.photos],
        }

        return {"data": store_data, "status": True}

    @staticmethod
    async def get_store_addresses(
            db: AsyncSession,
            store_id: int,
            page: int = 1,
            limit: int = 10,
            city_id: Optional[int] = None,
            zone_id: Optional[int] = None,
            needle: Optional[str] = None
    ) -> dict:
        offset = (page - 1) * limit

        # Базовый запрос для подсчета
        count_query = (
            select(func.count(StoreAddress.store_address_id))
            .where(StoreAddress.store_id == store_id)
        )

        # Базовый запрос для данных
        query = (
            select(StoreAddress)
            .options(
                selectinload(StoreAddress.city),
                selectinload(StoreAddress.zone),
            )
            .where(StoreAddress.store_id == store_id)
        )

        # Добавляем фильтрацию по city_id
        if city_id is not None:
            count_query = count_query.where(StoreAddress.city_id == city_id)
            query = query.where(StoreAddress.city_id == city_id)

        # Добавляем фильтрацию по zone_id
        if zone_id is not None:
            count_query = count_query.where(StoreAddress.zone_id == zone_id)
            query = query.where(StoreAddress.zone_id == zone_id)

        # Добавляем поиск по адресу
        if needle:
            search_filter = StoreAddress.address.ilike(f"%{needle}%")
            count_query = count_query.where(search_filter)
            query = query.where(search_filter)

        # Выполняем запрос для подсчета
        total_query = await db.execute(count_query)
        total = total_query.scalar_one()

        # Добавляем пагинацию и выполняем запрос для данных
        query = query.offset(offset).limit(limit)
        result = await db.execute(query)
        addresses = result.scalars().all()

        data = [
            {
                "store_address_id": addr.store_address_id,
                "address": addr.address,
                "phone_number": addr.phone_number,
                "schedule": addr.schedule,
                "geolocation": addr.geolocation,
                "city": addr.city.name if addr.city else None,
                "zone": addr.zone.name if addr.zone else None,
                "city_id": addr.city.city_id if addr.city.city_id else None,
                "zone_id": addr.zone.zone_id if addr.zone.zone_id else None,
            }
            for addr in addresses
        ]

        # Получаем уникальные города для данного магазина
        cities_query = await db.execute(
            select(City.city_id, City.name)
            .join(StoreAddress, StoreAddress.city_id == City.city_id)
            .where(StoreAddress.store_id == store_id)
            .distinct()
            .order_by(City.name)
        )
        available_cities = [
            {"city_id": city.city_id, "name": city.name}
            for city in cities_query
        ]

        # Получаем уникальные области для данного магазина
        zones_query = await db.execute(
            select(Zone.zone_id, Zone.name)
            .join(StoreAddress, StoreAddress.zone_id == Zone.zone_id)
            .where(StoreAddress.store_id == store_id)
            .distinct()
            .order_by(Zone.name)
        )
        available_zones = [
            {"zone_id": zone.zone_id, "name": zone.name}
            for zone in zones_query
        ]

        return {
            "data": data,
            "total": total,
            "page": page,
            "limit": limit,
            "available_filters": {
                "cities": available_cities,
                "zones": available_zones
            },
            "status": True
        }


    @staticmethod
    async def get_stores_list(
            db: AsyncSession,
            needle: str = None,
            sort_field: str = "name",
            sort_order: str = "ASC",
            page: int = 1,
            limit: int = 10,
    ):
        # Отримуємо кількість відгуків для кожного магазину
        store_reviews_query = await db.execute(
            select(
                StoreReview.store_id,
                func.count(StoreReview.store_review_id).label('reviews_count')
            )
            .where(StoreReview.status == ReviewStatusEnum.approved)
            .group_by(StoreReview.store_id)
        )
        store_reviews_counts = {row.store_id: row.reviews_count for row in store_reviews_query}

        # Базовий запит
        query = select(Store).options(
            selectinload(Store.photos),
            selectinload(Store.addresses)
        ).where(Store.status == 1)  # Тільки активні магазини

        # Пошук
        if needle:
            query = query.where(Store.name.ilike(f"%{needle}%"))

        # Підрахунок загальної кількості
        count_query = select(func.count(Store.store_id)).where(Store.status == 1)
        if needle:
            count_query = count_query.where(Store.name.ilike(f"%{needle}%"))

        total_result = await db.execute(count_query)
        total = total_result.scalar()

        # Сортування
        if sort_field == "name":
            order_column = Store.name
        elif sort_field == "date_added":
            order_column = Store.date_added
        elif sort_field == "rating":
            order_column = Store.rating
        else:
            order_column = Store.store_id

        if sort_order.upper() == "DESC":
            query = query.order_by(order_column.desc())
        else:
            query = query.order_by(order_column.asc())

        # Пагінація
        offset = (page - 1) * limit
        query = query.offset(offset).limit(limit)

        # Виконання запиту
        result = await db.execute(query)
        stores = result.scalars().all()

        # Формування результату з метрикою відгуків
        items = []
        for store in stores:
            reviews_count = store_reviews_counts.get(store.store_id, 0)

            store_data = {
                "store_id": store.store_id,
                "name": store.name,
                "description": store.description,
                "address": store.address,
                "phone": store.phone,
                "email": store.email,
                "website": store.website,
                "url_youtube": store.url_youtube,
                "url_viber": store.url_viber,
                "url_facebook": store.url_facebook,
                "url_telegram": store.url_telegram,
                "status": store.status,
                "viewed": store.viewed,
                "date_added": store.date_added.isoformat() if store.date_added else None,
                "date_modify": store.date_modify.isoformat() if store.date_modify else None,
                "rating": store.rating,
                "seo_keyword": store.seo_keyword,
                "meta_title": store.meta_title,
                "meta_description": store.meta_description,
                "meta_keyword": store.meta_keyword,
                "logo": store.logo,
                "reviews_count": reviews_count,  # Додано: кількість відгуків магазину
                "photos": [{"store_photo_id": p.store_photo_id, "image": p.image} for p in store.photos],
                "addresses": [
                    {
                        "store_address_id": a.store_address_id,
                        "address": a.address,
                        "phone_number": a.phone_number,
                        "schedule": a.schedule,
                        "geolocation": a.geolocation,
                        "city_id": a.city_id,
                        "zone_id": a.zone_id,
                    }
                    for a in store.addresses
                ],
            }
            items.append(store_data)

        return {
            "items": items,
            "total": total,
            "page": page,
            "limit": limit
        }

    @staticmethod
    async def update_product_views(db: AsyncSession, product_id: int) -> dict:
        """
        Оновлює кількість переглядів товару в БД та в кеші
        """
        try:
            # Оновлюємо viewed у базі даних
            await db.execute(
                update(Product)
                .where(Product.product_id == product_id)
                .values(viewed=Product.viewed + 1)
            )
            await db.commit()

            # Оновлюємо кеш, якщо товар присутній
            if product_id in PRODUCTS_CACHE:
                product_cache = PRODUCTS_CACHE[product_id]
                current_views = product_cache.get("viewed", 0)
                product_cache["viewed"] = current_views + 1

            return {
                "status": True,
                "message": "Кількість переглядів товару успішно оновлена"
            }

        except Exception as e:
            await db.rollback()
            return {
                "status": False,
                "message": f"Помилка при оновленні кількості переглядів: {str(e)}"
            }

    @staticmethod
    async def update_category_views(db: AsyncSession, category_id: int) -> dict:
        """
        Оновлює кількість переглядів товару в БД та в кеші
        """
        try:
            # Оновлюємо viewed у базі даних
            await db.execute(
                update(Category)
                .where(Category.category_id == category_id)
                .values(viewed=Category.viewed + 1)
            )
            await db.commit()

            # Оновлюємо кеш, якщо товар присутній
            if category_id in CATEGORIES_CACHE:
                category_cache = CATEGORIES_CACHE[category_id]
                current_views = category_cache.get("viewed", 0)
                category_cache["viewed"] = current_views + 1

            return {
                "status": True,
                "message": "Кількість переглядів товару успішно оновлена"
            }

        except Exception as e:
            await db.rollback()
            return {
                "status": False,
                "message": f"Помилка при оновленні кількості переглядів: {str(e)}"
            }

    @staticmethod
    async def get_store_by_id(db: AsyncSession, store_id: int):
        # Отримуємо кількість відгуків для магазину
        reviews_count_result = await db.execute(
            select(func.count(StoreReview.store_review_id))
            .where(
                StoreReview.store_id == store_id,
                StoreReview.status == ReviewStatusEnum.approved
            )
        )
        reviews_count = reviews_count_result.scalar()

        query = (
            select(Store)
            .options(
                selectinload(Store.photos),
                selectinload(Store.addresses)
            )
            .where(Store.store_id == store_id)
        )

        result = await db.execute(query)
        store = result.scalar_one_or_none()

        if not store:
            from fastapi import HTTPException
            raise HTTPException(status_code=404, detail="Store not found")

        return {
            "store_id": store.store_id,
            "name": store.name,
            "description": store.description,
            "address": store.address,
            "phone": store.phone,
            "email": store.email,
            "website": store.website,
            "url_youtube": store.url_youtube,
            "url_viber": store.url_viber,
            "url_facebook": store.url_facebook,
            "url_telegram": store.url_telegram,
            "status": store.status,
            "viewed": store.viewed,
            "date_added": store.date_added.isoformat() if store.date_added else None,
            "date_modify": store.date_modify.isoformat() if store.date_modify else None,
            "rating": store.rating,
            "seo_keyword": store.seo_keyword,
            "meta_title": store.meta_title,
            "meta_description": store.meta_description,
            "meta_keyword": store.meta_keyword,
            "logo": store.logo,
            "reviews_count": reviews_count,  # Додано: кількість відгуків магазину
            "photos": [{"store_photo_id": p.store_photo_id, "image": p.image} for p in store.photos],
            "addresses": [
                {
                    "store_address_id": a.store_address_id,
                    "address": a.address,
                    "phone_number": a.phone_number,
                    "schedule": a.schedule,
                    "geolocation": a.geolocation,
                    "city_id": a.city_id,
                    "zone_id": a.zone_id,
                }
                for a in store.addresses
            ],
        }

    @staticmethod
    async def get_store_reviews_statistics(db: AsyncSession, store_id: int) -> dict:
        """
        Получение статистики отзывов по ID магазина
        """
        try:
            # Проверяем существование магазина
            store_result = await db.execute(
                select(Store).where(Store.store_id == store_id)
            )
            store = store_result.scalar_one_or_none()

            if not store:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Магазин с ID {store_id} не найден"
                }

            # Получаем все одобренные отзывы для магазина
            reviews_result = await db.execute(
                select(StoreReview.rating)
                .where(
                    StoreReview.store_id == store_id,
                    StoreReview.status == ReviewStatusEnum.approved
                )
            )
            ratings = [row.rating for row in reviews_result]

            if not ratings:
                return {
                    "data": {
                        "total_reviews": 0,
                        "average_rating": 0.0,
                        "rating_distribution": {
                            "1": 0,
                            "2": 0,
                            "3": 0,
                            "4": 0,
                            "5": 0
                        }
                    },
                    "status": True
                }

            # Подсчитываем статистику
            total_reviews = len(ratings)
            average_rating = round(sum(ratings) / total_reviews, 1)

            # Подсчитываем распределение оценок
            rating_distribution = {"1": 0, "2": 0, "3": 0, "4": 0, "5": 0}
            for rating in ratings:
                rating_key = str(rating)
                if rating_key in rating_distribution:
                    rating_distribution[rating_key] += 1

            return {
                "data": {
                    "total_reviews": total_reviews,
                    "average_rating": average_rating,
                    "rating_distribution": rating_distribution
                },
                "status": True
            }

        except Exception as e:
            return {
                "data": None,
                "status": False,
                "message": f"Ошибка при получении статистики отзывов: {str(e)}"
            }

    @staticmethod
    async def search_product(limit, needle, category_id):
        if not needle:
            return {
                "data": [],
                "total": 0,
                "status": True,
            }

        needle_lower = needle.lower()
        found_products = []

        # Перебираем товары и останавливаемся когда найдем нужное количество
        for product in PRODUCTS_CACHE.values():
            # Проверяем соответствие названию
            name_matches = needle_lower in (product.get("name") or "").lower()
            if not name_matches:
                continue

            # Проверяем соответствие категории если указана
            if category_id is not None:
                category_matches = any(c["id"] == category_id for c in product.get("categories", []))
                if not category_matches:
                    continue

            # Добавляем товар в результаты
            found_products.append(product)

            # Останавливаемся когда достигли лимита
            if len(found_products) >= limit:
                break

        return {
            "data": found_products,
            "total": len(found_products),
            "status": True,
        }


class ReviewResponseService:
    """Сервіс для роботи з відповідями на відгуки"""

    # Константа для автора відповідей (хардкод)
    DEFAULT_CUSTOMER_ID = 2

    @staticmethod
    async def create_product_review_response(
            db: AsyncSession,
            product_review_id: int,
            response_text: str,
            ip: str
    ) -> dict:
        """Створити відповідь на відгук про товар"""
        try:
            # Перевіряємо існування відгуку про товар
            review_result = await db.execute(
                select(ProductReview)
                .options(selectinload(ProductReview.customer))
                .where(ProductReview.product_review_id == product_review_id)
            )
            review = review_result.scalar_one_or_none()

            if not review:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Відгук про товар з ID {product_review_id} не знайдено"
                }

            # Отримуємо інформацію про customer для автора відповіді
            customer_result = await db.execute(
                select(Customer).where(Customer.customer_id == ReviewResponseService.DEFAULT_CUSTOMER_ID)
            )
            customer = customer_result.scalar_one_or_none()

            if not customer:
                return {
                    "data": None,
                    "status": False,
                    "message": "Автор відповіді не знайдено"
                }

            # Створюємо відповідь
            response_db = ReviewResponse(
                review_type="product",
                review_id=product_review_id,
                author_type="customer",
                author_id=customer.customer_id,
                author_name=customer.name,
                response_text=response_text,
                ip=ip,
                status=ReviewStatusEnum.approved,
                date_added=datetime.utcnow(),
                date_modify=datetime.utcnow()
            )

            db.add(response_db)

            # Оновлюємо флаг has_response в відгуці про товар
            await db.execute(
                update(ProductReview)
                .where(ProductReview.product_review_id == product_review_id)
                .values(has_response=True)
            )

            await db.commit()
            await db.refresh(response_db)

            return {
                "data": {
                    "response_id": response_db.response_id,
                    "review_type": response_db.review_type,
                    "review_id": response_db.review_id,
                    "author_type": response_db.author_type,
                    "author_id": response_db.author_id,
                    "author_name": response_db.author_name,
                    "response_text": response_db.response_text,
                    "date_added": response_db.date_added.isoformat() if response_db.date_added else None,
                    "date_modify": response_db.date_modify.isoformat() if response_db.date_modify else None,
                    "status": response_db.status.name
                },
                "status": True,
                "message": "Відповідь успішно створено"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Помилка при створенні відповіді: {str(e)}"
            }

    @staticmethod
    async def create_store_review_response(
            db: AsyncSession,
            store_review_id: int,
            response_text: str,
            ip: str
    ) -> dict:
        """Створити відповідь на відгук про магазин"""
        try:
            # Перевіряємо існування відгуку про магазин
            review_result = await db.execute(
                select(StoreReview)
                .options(selectinload(StoreReview.customer))
                .where(StoreReview.store_review_id == store_review_id)
            )
            review = review_result.scalar_one_or_none()

            if not review:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Відгук про магазин з ID {store_review_id} не знайдено"
                }

            # Отримуємо інформацію про customer для автора відповіді
            customer_result = await db.execute(
                select(Customer).where(Customer.customer_id == ReviewResponseService.DEFAULT_CUSTOMER_ID)
            )
            customer = customer_result.scalar_one_or_none()

            if not customer:
                return {
                    "data": None,
                    "status": False,
                    "message": "Автор відповіді не знайдено"
                }

            # Створюємо відповідь
            response_db = ReviewResponse(
                review_type="store",
                review_id=store_review_id,
                author_type="customer",
                author_id=customer.customer_id,
                author_name=customer.name,
                response_text=response_text,
                ip=ip,
                status=ReviewStatusEnum.approved,
                date_added=datetime.utcnow(),
                date_modify=datetime.utcnow()
            )

            db.add(response_db)

            # Оновлюємо флаг has_response в відгуці про магазин
            await db.execute(
                update(StoreReview)
                .where(StoreReview.store_review_id == store_review_id)
                .values(has_response=True)
            )

            await db.commit()
            await db.refresh(response_db)

            return {
                "data": {
                    "response_id": response_db.response_id,
                    "review_type": response_db.review_type,
                    "review_id": response_db.review_id,
                    "author_type": response_db.author_type,
                    "author_id": response_db.author_id,
                    "author_name": response_db.author_name,
                    "response_text": response_db.response_text,
                    "date_added": response_db.date_added.isoformat() if response_db.date_added else None,
                    "date_modify": response_db.date_modify.isoformat() if response_db.date_modify else None,
                    "status": response_db.status.name
                },
                "status": True,
                "message": "Відповідь успішно створено"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Помилка при створенні відповіді: {str(e)}"
            }

    @staticmethod
    async def create_manufacturer_review_response(
            db: AsyncSession,
            manufacturer_review_id: int,
            response_text: str,
            ip: str
    ) -> dict:
        """Створити відповідь на відгук про виробника"""
        try:
            # Перевіряємо існування відгуку про виробника
            review_result = await db.execute(
                select(ManufacturerReview)
                .options(selectinload(ManufacturerReview.customer))
                .where(ManufacturerReview.manufacturer_review_id == manufacturer_review_id)
            )
            review = review_result.scalar_one_or_none()

            if not review:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Відгук про виробника з ID {manufacturer_review_id} не знайдено"
                }

            # Отримуємо інформацію про customer для автора відповіді
            customer_result = await db.execute(
                select(Customer).where(Customer.customer_id == ReviewResponseService.DEFAULT_CUSTOMER_ID)
            )
            customer = customer_result.scalar_one_or_none()

            if not customer:
                return {
                    "data": None,
                    "status": False,
                    "message": "Автор відповіді не знайдено"
                }

            # Створюємо відповідь
            response_db = ReviewResponse(
                review_type="manufacturer",
                review_id=manufacturer_review_id,
                author_type="customer",
                author_id=customer.customer_id,
                author_name=customer.name,
                response_text=response_text,
                ip=ip,
                status=ReviewStatusEnum.approved,
                date_added=datetime.utcnow(),
                date_modify=datetime.utcnow()
            )

            db.add(response_db)

            # Оновлюємо флаг has_response в відгуці про виробника
            await db.execute(
                update(ManufacturerReview)
                .where(ManufacturerReview.manufacturer_review_id == manufacturer_review_id)
                .values(has_response=True)
            )

            await db.commit()
            await db.refresh(response_db)

            return {
                "data": {
                    "response_id": response_db.response_id,
                    "review_type": response_db.review_type,
                    "review_id": response_db.review_id,
                    "author_type": response_db.author_type,
                    "author_id": response_db.author_id,
                    "author_name": response_db.author_name,
                    "response_text": response_db.response_text,
                    "date_added": response_db.date_added.isoformat() if response_db.date_added else None,
                    "date_modify": response_db.date_modify.isoformat() if response_db.date_modify else None,
                    "status": response_db.status.name
                },
                "status": True,
                "message": "Відповідь успішно створено"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Помилка при створенні відповіді: {str(e)}"
            }

    @staticmethod
    async def create_service_review_response(
            db: AsyncSession,
            service_review_id: int,
            response_text: str,
            ip: str
    ) -> dict:
        """Створити відповідь на відгук про сервіс"""
        try:
            # Перевіряємо існування відгуку про сервіс
            review_result = await db.execute(
                select(ServiceReview)
                .options(selectinload(ServiceReview.customer))
                .where(ServiceReview.service_review_id == service_review_id)
            )
            review = review_result.scalar_one_or_none()

            if not review:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Відгук про сервіс з ID {service_review_id} не знайдено"
                }

            # Отримуємо інформацію про customer для автора відповіді
            customer_result = await db.execute(
                select(Customer).where(Customer.customer_id == ReviewResponseService.DEFAULT_CUSTOMER_ID)
            )
            customer = customer_result.scalar_one_or_none()

            if not customer:
                return {
                    "data": None,
                    "status": False,
                    "message": "Автор відповіді не знайдено"
                }

            # Створюємо відповідь
            response_db = ReviewResponse(
                review_type="service",
                review_id=service_review_id,
                author_type="customer",
                author_id=customer.customer_id,
                author_name=customer.name,
                response_text=response_text,
                ip=ip,
                status=ReviewStatusEnum.approved,
                date_added=datetime.utcnow(),
                date_modify=datetime.utcnow()
            )

            db.add(response_db)

            # Оновлюємо флаг has_response в відгуці про сервіс
            await db.execute(
                update(ServiceReview)
                .where(ServiceReview.service_review_id == service_review_id)
                .values(has_response=True)
            )

            await db.commit()
            await db.refresh(response_db)

            return {
                "data": {
                    "response_id": response_db.response_id,
                    "review_type": response_db.review_type,
                    "review_id": response_db.review_id,
                    "author_type": response_db.author_type,
                    "author_id": response_db.author_id,
                    "author_name": response_db.author_name,
                    "response_text": response_db.response_text,
                    "date_added": response_db.date_added.isoformat() if response_db.date_added else None,
                    "date_modify": response_db.date_modify.isoformat() if response_db.date_modify else None,
                    "status": response_db.status.name
                },
                "status": True,
                "message": "Відповідь успішно створено"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Помилка при створенні відповіді: {str(e)}"
            }

    # Залишаємо існуючі методи get_review_responses та delete_review_response без змін
    @staticmethod
    async def get_review_responses(
            db: AsyncSession,
            review_type: str,
            review_id: int
    ) -> List[dict]:
        """Отримати всі відповіді на конкретний відгук"""
        try:
            result = await db.execute(
                select(ReviewResponse)
                .where(
                    ReviewResponse.review_type == review_type,
                    ReviewResponse.review_id == review_id,
                    ReviewResponse.status == ReviewStatusEnum.approved
                )
                .order_by(ReviewResponse.date_added.asc())
            )
            responses = result.scalars().all()

            return [
                {
                    "response_id": r.response_id,
                    "review_type": r.review_type,
                    "review_id": r.review_id,
                    "author_type": r.author_type,
                    "author_id": r.author_id,
                    "author_name": r.author_name,
                    "response_text": r.response_text,
                    "date_added": r.date_added.isoformat() if r.date_added else None,
                    "date_modify": r.date_modify.isoformat() if r.date_modify else None,
                    "status": r.status.name
                }
                for r in responses
            ]

        except Exception:
            return []

    @staticmethod
    async def delete_review_response(
            db: AsyncSession,
            response_id: int
    ) -> dict:
        """Видалити відповідь на відгук"""
        try:
            result = await db.execute(
                select(ReviewResponse)
                .where(ReviewResponse.response_id == response_id)
            )
            response = result.scalar_one_or_none()

            if not response:
                return {
                    "status": False,
                    "message": "Відповідь не знайдено"
                }

            review_type = response.review_type
            review_id = response.review_id

            # Видаляємо відповідь
            await db.delete(response)

            # Перевіряємо, залишились чи ще відповіді на цей відгук
            remaining_responses = await db.execute(
                select(func.count(ReviewResponse.response_id))
                .where(
                    ReviewResponse.review_type == review_type,
                    ReviewResponse.review_id == review_id,
                    ReviewResponse.status == ReviewStatusEnum.approved
                )
            )
            count = remaining_responses.scalar()

            # Якщо відповідей не залишилось, оновлюємо флаг has_response
            if count == 0:
                await ReviewResponseService._update_review_has_response(
                    db, review_type, review_id, False
                )

            await db.commit()

            return {
                "status": True,
                "message": "Відповідь успішно видалено"
            }

        except Exception as e:
            await db.rollback()
            return {
                "status": False,
                "message": f"Помилка при видаленні відповіді: {str(e)}"
            }

    @staticmethod
    async def _update_review_has_response(
            db: AsyncSession,
            review_type: str,
            review_id: int,
            has_response: bool
    ):
        """Оновити флаг has_response у відгуці"""
        try:
            if review_type == "product":
                await db.execute(
                    update(ProductReview)
                    .where(ProductReview.product_review_id == review_id)
                    .values(has_response=has_response)
                )
            elif review_type == "store":
                await db.execute(
                    update(StoreReview)
                    .where(StoreReview.store_review_id == review_id)
                    .values(has_response=has_response)
                )
            elif review_type == "manufacturer":
                await db.execute(
                    update(ManufacturerReview)
                    .where(ManufacturerReview.manufacturer_review_id == review_id)
                    .values(has_response=has_response)
                )
            elif review_type == "service":
                await db.execute(
                    update(ServiceReview)
                    .where(ServiceReview.service_review_id == review_id)
                    .values(has_response=has_response)
                )
        except Exception:
            pass