import sys
from datetime import datetime
from typing import Optional, List
from collections import defaultdict
from fastapi import HTTPException, UploadFile
from sqlalchemy import select, desc, asc, func, or_, distinct, and_, text, delete
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload
from starlette import status
from tqdm.asyncio import tqdm

from src.category.models import Category
from src.manufacturer.models import Manufacturer
from src.product.models import Product, ProductCategory, ProductStatus, ProductAttribute, ProductImage, FilterGroup, Filter, ProductFilter
from src.product.schemas import ProductOut, ProductDetailResponse, StoreOut, ImageOut, ManufacturerOut, AttributeOut, \
    CategoryBrief, ProductCreate, ProductUpdate, ProductAttributeCreate, ProductAttributeUpdate, TransferResult
from src.store.models import ProductStore, Store
from src.utils.image_manager import handle_image_upload
from src.error import _ERROR

from sqlalchemy.ext.asyncio import async_sessionmaker
from sqlalchemy import delete
import asyncio

class ProductService:

    @staticmethod
    async def clean_duplicate_attributes(db: AsyncSession):
        async def process_products_batch(product_ids: list[int], session_factory):
            async with session_factory() as db:
                stmt = (
                    select(Product)
                    .where(Product.product_id.in_(product_ids))
                    .options(selectinload(Product.attributes))
                )
                result = await db.execute(stmt)
                products = result.scalars().all()

                ids_to_delete = []

                for product in products:
                    seen = set()
                    for attr in product.attributes:
                        key = (attr.group_name, attr.name, attr.value)
                        if key in seen:
                            ids_to_delete.append(attr.product_attribute_id)
                        else:
                            seen.add(key)

                if ids_to_delete:
                    await db.execute(
                        delete(ProductAttribute)
                        .where(ProductAttribute.product_attribute_id.in_(ids_to_delete))
                    )
                    await db.commit()

                return len(ids_to_delete)

        CONCURRENCY = 10  # реалістично, не 100
        BATCH_SIZE = 100

        print("Starting to clean duplicate attributes")

        session_factory = async_sessionmaker(
            bind=db.bind,
            expire_on_commit=False
        )

        # Беремо ТІЛЬКИ ID — швидко і дешево
        result = await db.execute(select(Product.product_id))
        product_ids = result.scalars().all()

        semaphore = asyncio.Semaphore(CONCURRENCY)

        async def sem_task(batch):
            async with semaphore:
                return await process_products_batch(batch, session_factory)

        tasks = []
        for i in range(0, len(product_ids), BATCH_SIZE):
            batch = product_ids[i:i + BATCH_SIZE]
            tasks.append(asyncio.create_task(sem_task(batch)))

        deleted_total = 0
        for coro in tqdm(asyncio.as_completed(tasks), total=len(tasks), desc="Cleaning"):
            deleted_total += await coro

        print(f"Done. Deleted {deleted_total} duplicate attributes.")

    @staticmethod
    async def get_products(
            db: AsyncSession,
            needle: Optional[str] = None,
            category_id: Optional[int] = None,
            price_min: Optional[float] = None,
            price_max: Optional[float] = None,
            status: Optional[int] = None,
            sort_field: Optional[str] = "name",
            sort_order: Optional[str] = "asc",
            page: int = 1,
            limit: int = 10,
    ) -> dict:
        # 🔹 Базовий запит для отримання унікальних продуктів з полем для сортування
        sort_map = {
            "name": Product.name,
            "price": Product.price,
            "date_added": Product.date_added,
            "product_id": Product.product_id
        }

        # Вибираємо поле для сортування
        sort_col = sort_map.get(sort_field, Product.name)

        # Базовий запит включає product_id та поле для сортування
        if sort_field == "category":
            # Для сортування по категорії потрібно додати category.name
            base_stmt = select(Product.product_id, Category.name.label('category_name')).distinct()
            base_stmt = base_stmt.join(ProductCategory, Product.product_id == ProductCategory.product_id)
            base_stmt = base_stmt.join(Category, ProductCategory.category_id == Category.category_id)
        else:
            # Для інших полей додаємо поле сортування до SELECT
            base_stmt = select(Product.product_id, sort_col.label('sort_field')).distinct()

        # 🔹 Додаємо джоіни тільки якщо потрібні для фільтрації (але не для сортування)
        needs_category_join = category_id is not None
        if needs_category_join and sort_field != "category":
            base_stmt = base_stmt.join(ProductCategory, Product.product_id == ProductCategory.product_id)
            base_stmt = base_stmt.join(Category, ProductCategory.category_id == Category.category_id)

        # 🔹 Фільтри
        filters = []
        if needle:
            pattern = f"%{needle.lower()}%"
            filters.append(func.lower(Product.name).like(pattern))
        if category_id:
            filters.append(ProductCategory.category_id == category_id)
        if price_min is not None:
            filters.append(Product.price >= price_min)
        if price_max is not None:
            filters.append(Product.price <= price_max)
        if status is not None:
            filters.append(Product.status == ProductStatus(status))

        if filters:
            base_stmt = base_stmt.where(and_(*filters))

        # 🔹 Сортування для підзапиту ID продуктів
        if sort_field == "category":
            base_stmt = base_stmt.order_by(desc(Category.name) if sort_order == "desc" else asc(Category.name))
        else:
            # Використовуємо label для сортування
            order_col = text('sort_field')
            if sort_field == "date_added":
                base_stmt = base_stmt.order_by(desc(order_col) if sort_order == "desc" else asc(order_col))
            else:
                base_stmt = base_stmt.order_by(desc(order_col) if sort_order == "desc" else asc(order_col))

        # 🔹 Пагінація на рівні унікальних ID продуктів
        offset = (page - 1) * limit
        base_stmt = base_stmt.offset(offset).limit(limit)

        # 🔹 Отримуємо ID продуктів для поточної сторінки
        result = await db.execute(base_stmt)
        product_ids = [row[0] for row in result.all()]

        if not product_ids:
            # Якщо немає продуктів, повертаємо порожній результат
            return {
                "data": [],
                "dashboard": {
                    "total_quantity": 0,
                    "quantity_on": 0,
                    "quantity_off": 0,
                },
                "page": page,
                "limit": limit,
                "total": 0,
                "status": True,
            }

        # 🔹 Отримуємо повні дані продуктів з їх категоріями
        products_stmt = (
            select(Product, Category)
            .outerjoin(ProductCategory, Product.product_id == ProductCategory.product_id)
            .outerjoin(Category, ProductCategory.category_id == Category.category_id)
            .where(Product.product_id.in_(product_ids))
        )

        # Зберігаємо порядок сортування
        if sort_field == "name":
            products_stmt = products_stmt.order_by(desc(Product.name) if sort_order == "desc" else asc(Product.name))
        elif sort_field == "price":
            products_stmt = products_stmt.order_by(desc(Product.price) if sort_order == "desc" else asc(Product.price))
        elif sort_field == "date_added":
            products_stmt = products_stmt.order_by(
                desc(Product.date_added) if sort_order == "desc" else asc(Product.date_added))
        elif sort_field == "product_id":
            products_stmt = products_stmt.order_by(
                desc(Product.product_id) if sort_order == "desc" else asc(Product.product_id))
        elif sort_field == "category":
            products_stmt = products_stmt.order_by(desc(Category.name) if sort_order == "desc" else asc(Category.name))

        result = await db.execute(products_stmt)
        rows = result.all()

        # 🔹 Total з усіма фільтрами (для пагінації)
        total_stmt = select(func.count(func.distinct(Product.product_id))).select_from(Product)

        if category_id:
            total_stmt = total_stmt.join(ProductCategory).where(ProductCategory.category_id == category_id)

        filter_conditions = []
        if needle:
            pattern = f"%{needle.lower()}%"
            filter_conditions.append(func.lower(Product.name).like(pattern))
        if price_min is not None:
            filter_conditions.append(Product.price >= price_min)
        if price_max is not None:
            filter_conditions.append(Product.price <= price_max)
        if status is not None:
            filter_conditions.append(Product.status == ProductStatus(status))

        if filter_conditions:
            total_stmt = total_stmt.where(and_(*filter_conditions))

        total = (await db.execute(total_stmt)).scalar_one() or 0

        # 🔹 Dashboard: quantity_on/off
        async def count_status(product_status: ProductStatus) -> int:
            if status is not None and product_status != ProductStatus(status):
                return 0
            stmt = select(func.coalesce(func.count(func.distinct(Product.product_id)), 0)).select_from(Product)
            stmt = stmt.where(Product.status == product_status)

            if category_id:
                stmt = stmt.join(ProductCategory).where(ProductCategory.category_id == category_id)

            conditions = []
            if needle:
                pattern = f"%{needle.lower()}%"
                conditions.append(func.lower(Product.name).like(pattern))
            if price_min is not None:
                conditions.append(Product.price >= price_min)
            if price_max is not None:
                conditions.append(Product.price <= price_max)

            if conditions:
                stmt = stmt.where(and_(*conditions))

            return (await db.execute(stmt)).scalar_one() or 0

        quantity_on = await count_status(ProductStatus.ON)
        quantity_off = await count_status(ProductStatus.OFF)

        # 🔹 Категорії для повного шляху
        q_cats = await db.execute(select(Category))
        categories = q_cats.scalars().all()
        all_categories = {c.category_id: c for c in categories}

        def build_full_path(cat: Category) -> str:
            path = [cat.name]
            current = cat
            while current.parent_category_id:
                parent = all_categories.get(current.parent_category_id)
                if not parent:
                    break
                path.insert(0, parent.name)
                current = parent
            return " > ".join(path)

        # 🔹 Формування мапи продуктів з категоріями
        product_map = defaultdict(set)
        product_data = {}

        for row in rows:
            product = row.Product
            category = row.Category

            # Зберігаємо дані продукту
            if product.product_id not in product_data:
                product_data[product.product_id] = product

            # Додаємо категорію до продукту
            if category:
                product_map[product.product_id].add(build_full_path(category))

        # 🔹 Формування фінального списку в правильному порядку
        products = []
        for product_id in product_ids:  # Використовуємо порядок з першого запиту
            if product_id in product_data:
                product = product_data[product_id]
                categories = sorted(product_map.get(product_id, []))
                products.append(
                    ProductOut(
                        product_id=product.product_id,
                        name=product.name,
                        status=product.status,
                        price=product.price,
                        price_old=product.price_old,
                        category=" | ".join(categories) if categories else None,
                        image=product.image,
                    )
                )

        return {
            "data": products,
            "dashboard": {
                "total_quantity": total,
                "quantity_on": quantity_on,
                "quantity_off": quantity_off,
            },
            "page": page,
            "limit": limit,
            "total": total,
            "status": True,
        }

    @staticmethod
    async def get_product_by_id(
            product_id: int,
            db: AsyncSession
    ) -> ProductDetailResponse:
        """
        Отримує детальну інформацію про товар за його ID, включаючи пов'язані об'єкти.
        """
        # Отримання товару з усіма пов'язаними даними
        stmt = (
            select(Product)
            .where(Product.product_id == product_id)
            .options(
                selectinload(Product.categories).joinedload(ProductCategory.category),
                selectinload(Product.attributes),
                selectinload(Product.images),
                selectinload(Product.manufacturer),
                selectinload(Product.product_links).joinedload(ProductStore.store),
            )
        )

        result = await db.execute(stmt)
        product: Product | None = result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        # Отримання всіх категорій для побудови повного шляху
        all_categories_result = await db.execute(select(Category))
        categories_all = {cat.category_id: cat for cat in all_categories_result.scalars().all()}

        def build_category_path(cat: Category) -> str:
            path = [cat.name]
            while cat.parent_category_id:
                parent = categories_all.get(cat.parent_category_id)
                if not parent:
                    break
                path.insert(0, parent.name)
                cat = parent
            return " > ".join(path)

        # Формування списку категорій
        categories_out = [
            CategoryBrief(
                category_id=pc.category.category_id,
                name=build_category_path(pc.category)
            )
            for pc in product.categories if pc.category
        ]

        # Формування списку магазинів
        stores_out = []
        seen_store_ids = set()
        for link in product.product_links:
            if link.store and link.store.store_id not in seen_store_ids:
                seen_store_ids.add(link.store.store_id)
                stores_out.append(
                    StoreOut(
                        store_id=link.store.store_id,
                        name=link.store.name
                    )
                )

        return ProductDetailResponse(
            product_id=product.product_id,
            name=product.name,
            description=product.description,
            seo_keyword=product.seo_keyword,
            meta_title=product.meta_title,
            meta_description=product.meta_description,
            meta_keyword=product.meta_keyword,
            image=product.image,
            status=product.status,
            date_added=product.date_added,
            date_modify=product.date_modify,
            price=product.price,
            price_old=product.price_old,
            model=product.model,
            rating=product.rating,
            viewed=product.viewed,
            categories=categories_out,
            attributes=[
                AttributeOut(
                    group_name=attr.group_name,
                    name=attr.name,
                    value=attr.value,
                    sort_order=attr.sort_order
                )
                for attr in product.attributes
            ],
            images=[
                ImageOut(
                    image=img.image,
                    sort_order=img.sort_order
                )
                for img in product.images
            ],
            stores=stores_out,
            manufacturer=ManufacturerOut(
                manufacturer_id=product.manufacturer.manufacturer_id,
                name=product.manufacturer.name
            ) if product.manufacturer else None
        )

    @staticmethod
    async def create_product(data: dict, db: AsyncSession) -> Product:
        new_product = Product(
            name=data["name"],
            description=data.get("description"),
            price=data["price"],
            price_old=data["price_old"],
            model=data.get("model"),
            manufacturer_id=data.get("manufacturer_id"),
            status=ProductStatus.ON,
            seo_keyword=data.get("seo_keyword"),
            meta_title=data.get("meta_title"),
            meta_description=data.get("meta_description"),
            meta_keyword=data.get("meta_keyword"),
            image=data.get("image_url")
        )
        db.add(new_product)
        await db.commit()
        await db.refresh(new_product)
        return new_product

    @staticmethod
    async def update_product(
            product_id: int,
            data: dict,
            image_file: Optional[UploadFile],
            db: AsyncSession
    ) -> Product:
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()
        if not product:
            _ERROR("ItemNotFound")

        update_data = {k: v for k, v in data.items() if v is not None}

        # Нормалізація manufacturer_id
        manufacturer_id = update_data.get("manufacturer_id")
        if manufacturer_id is not None:
            if manufacturer_id == 0:
                update_data["manufacturer_id"] = None
            else:
                q = await db.execute(select(Manufacturer).filter(Manufacturer.manufacturer_id == manufacturer_id))
                manufacturer = q.scalar_one_or_none()
                if manufacturer is None:
                    _ERROR("ManufacturerNotFound")

        # Ручна валідація статусу
        status = update_data.get("status")
        if status is not None:
            if status not in (0, 1):
                _ERROR("ValidationError")
            update_data["status"] = ProductStatus(status)

        # Обробка картинки
        if image_file is not None:
            image_url = await handle_image_upload(image_file)
            update_data["image"] = image_url

        update_data["date_modify"] = datetime.utcnow()

        for key, value in update_data.items():
            setattr(product, key, value)

        await db.commit()
        await db.refresh(product)
        return product

    @staticmethod
    async def delete_product(
            product_id: int,
            db: AsyncSession
    ) -> None:
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        await db.delete(product)
        await db.commit()

    @staticmethod
    async def get_product_images(
            product_id: int,
            db: AsyncSession,
            page: int,
            limit: int
    ):
        # Перевірка чи існує продукт
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        # Загальна кількість картинок
        total = await db.scalar(
            select(func.count()).select_from(
                select(ProductImage).where(ProductImage.product_id == product_id).subquery()
            )
        )

        # Отримання картинок з пагінацією
        result = await db.execute(
            select(ProductImage)
            .where(ProductImage.product_id == product_id)
            .offset((page - 1) * limit)
            .limit(limit)
        )
        images = result.scalars().all()

        return images, total

    @staticmethod
    async def get_product_image(
            product_id: int,
            product_image_id: int,
            db: AsyncSession
    ) -> ProductImage:
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        result = await db.execute(
            select(ProductImage).where(
                ProductImage.product_id == product_id,
                ProductImage.product_image_id == product_image_id
            )
        )
        image = result.scalar_one_or_none()
        if not image:
            _ERROR("NotFound")

        return image

    @staticmethod
    async def add_product_images(
            product_id: int,
            file: List,
            db: AsyncSession
    ):
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        if not file:
            _ERROR("ValidationError")

        max_sort_order_result = await db.execute(
            select(func.max(ProductImage.sort_order)).where(ProductImage.product_id == product_id)
        )
        max_sort_order = max_sort_order_result.scalar()
        if max_sort_order is None:
            max_sort_order = -1

        added_images = []
        current_sort_order = max_sort_order + 1

        for f in file:
            image_url = await handle_image_upload(f)
            product_image = ProductImage(
                product_id=product_id,
                image=image_url,
                sort_order=current_sort_order
            )
            current_sort_order += 1

            db.add(product_image)
            await db.flush()
            added_images.append(product_image)

        await db.commit()
        return added_images

    @staticmethod
    async def add_product_images_from_urls(
            product_id: int,
            urls: List[str],
            db: AsyncSession
    ):
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        if not urls:
            _ERROR("ValidationError")

        max_sort_order_result = await db.execute(
            select(func.max(ProductImage.sort_order)).where(ProductImage.product_id == product_id)
        )
        max_sort_order = max_sort_order_result.scalar()
        if max_sort_order is None:
            max_sort_order = -1

        added_images = []
        current_sort_order = max_sort_order + 1

        for url in urls:
            product_image = ProductImage(
                product_id=product_id,
                image=url,
                sort_order=current_sort_order
            )
            current_sort_order += 1

            db.add(product_image)
            await db.flush()
            added_images.append(product_image)

        await db.commit()
        return added_images

    @staticmethod
    async def update_product_image_sort_order(
            product_id: int,
            product_image_id: int,
            sort_order: int,
            db: AsyncSession
    ) -> ProductImage:
        result = await db.execute(
            select(ProductImage).where(
                ProductImage.product_id == product_id,
                ProductImage.product_image_id == product_image_id
            )
        )
        product_image = result.scalar_one_or_none()

        if not product_image:
            _ERROR("NotFound")

        product_image.sort_order = sort_order
        await db.commit()
        await db.refresh(product_image)
        return product_image

    @staticmethod
    async def delete_product_image(
            product_id: int,
            product_image_id: int,
            db: AsyncSession
    ) -> None:
        result = await db.execute(
            select(ProductImage).where(
                ProductImage.product_id == product_id,
                ProductImage.product_image_id == product_image_id
            )
        )
        image = result.scalar_one_or_none()

        if not image:
            _ERROR("NotFound")

        await db.delete(image)
        await db.commit()

    @staticmethod
    async def get_product_category_with_path(
            product_id: int,
            db: AsyncSession,
            page: int,
            limit: int,
    ) -> tuple[list[dict], int]:
        result = await db.execute(
            select(ProductCategory)
            .options(
                selectinload(ProductCategory.category).selectinload(Category.parent)
            )
            .where(ProductCategory.product_id == product_id)
            .offset((page - 1) * limit)
            .limit(limit)
        )
        product_categories = result.scalars().all()

        if not product_categories:
            return [], 0

        total = await db.scalar(
            select(func.count())
            .select_from(ProductCategory)
            .where(ProductCategory.product_id == product_id)
        )

        all_categories = {pc.category.category_id: pc.category for pc in product_categories}

        def build_full_path(cat: Category) -> str:
            path = [cat.name]
            current = cat
            while current.parent_category_id:
                parent = all_categories.get(current.parent_category_id)
                if not parent:
                    break
                path.insert(0, parent.name)
                current = parent
            return " > ".join(path)

        enriched = [
            {
                "product_category_id": pc.product_category_id,
                "product_id": pc.product_id,
                "category_id": pc.category_id,
                "full_name": build_full_path(pc.category),
                "status": pc.category.status,
                "date_added": pc.category.date_added.isoformat() if pc.category.date_added else None,
                "date_modify": pc.category.date_modify.isoformat() if pc.category.date_modify else None,
            }
            for pc in product_categories
        ]

        return enriched, total

    @staticmethod
    async def get_product_category(
            product_id: int,
            product_category_id: int,
            db: AsyncSession
    ) -> Category:
        result = await db.execute(
            select(ProductCategory)
            .where(
                ProductCategory.product_id == product_id,
                ProductCategory.product_category_id == product_category_id
            )
            .options(selectinload(ProductCategory.category))
        )
        product_category = result.scalar_one_or_none()
        if not product_category or not product_category.category:
            _ERROR("CategoryNotFound")

        if not product_category.category.name:
            _ERROR("CategoryNotFound")

        return product_category.category

    @staticmethod
    async def add_category_to_product(
            product_id: int,
            category_id: int,
            db: AsyncSession
    ) -> ProductCategory:
        product_result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = product_result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        category_result = await db.execute(select(Category).where(Category.category_id == category_id))
        category = category_result.scalar_one_or_none()

        if not category:
            _ERROR("CategoryNotFound")

        existing_result = await db.execute(
            select(ProductCategory).where(
                ProductCategory.product_id == product_id,
                ProductCategory.category_id == category_id,
            )
        )
        existing_link = existing_result.scalar_one_or_none()
        if existing_link:
            _ERROR("ValidationError")

        # Collect all ancestor category IDs
        category_ids_to_add = [category_id]
        current = category
        while current.parent_category_id is not None:
            parent_result = await db.execute(
                select(Category).where(Category.category_id == current.parent_category_id)
            )
            current = parent_result.scalar_one_or_none()
            if current is None:
                break
            category_ids_to_add.append(current.category_id)

        # Fetch already linked categories for this product
        existing_links_result = await db.execute(
            select(ProductCategory.category_id).where(
                ProductCategory.product_id == product_id,
                ProductCategory.category_id.in_(category_ids_to_add),
            )
        )
        already_linked = {row[0] for row in existing_links_result.fetchall()}

        for cid in category_ids_to_add:
            if cid not in already_linked:
                db.add(ProductCategory(product_id=product_id, category_id=cid))

        await db.commit()

        product_category_result = await db.execute(
            select(ProductCategory).where(
                ProductCategory.product_id == product_id,
                ProductCategory.category_id == category_id,
            )
        )
        return product_category_result.scalar_one()

    @staticmethod
    async def delete_product_category(
            product_id: int,
            product_category_id: int,
            db: AsyncSession
    ) -> None:
        result = await db.execute(
            select(ProductCategory).where(
                ProductCategory.product_id == product_id,
                ProductCategory.product_category_id == product_category_id
            )
        )
        product_category = result.scalar_one_or_none()

        if not product_category:
            _ERROR("CategoryNotFound")

        await db.delete(product_category)
        await db.commit()

    @staticmethod
    async def get_product_attributes(
            product_id: int,
            db: AsyncSession,
            page: int,
            limit: int,
            needle: Optional[str] = None
    ) -> tuple[List[ProductAttribute], int]:
        # перевірка чи існує продукт
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()

        if not product:
            _ERROR("ItemNotFound")

        filters = [ProductAttribute.product_id == product_id]

        if needle:
            like_term = f"%{needle.lower()}%"
            filters.append(
                or_(
                    ProductAttribute.group_name.ilike(like_term),
                    ProductAttribute.name.ilike(like_term),
                    ProductAttribute.value.ilike(like_term),
                )
            )

        # загальна кількість атрибутів (з урахуванням пошуку)
        total = await db.scalar(
            select(func.count()).select_from(
                select(ProductAttribute).where(and_(*filters)).subquery()
            )
        )

        # пагінація (з урахуванням пошуку)
        result = await db.execute(
            select(ProductAttribute)
            .where(and_(*filters))
            .offset((page - 1) * limit)
            .limit(limit)
        )
        attributes = result.scalars().all()

        return attributes, total

    @staticmethod
    async def get_product_attribute(
            product_id: int,
            product_attribute_id: int,
            db: AsyncSession
    ) -> ProductAttribute:
        result = await db.execute(
            select(ProductAttribute).where(
                ProductAttribute.product_id == product_id,
                ProductAttribute.product_attribute_id == product_attribute_id
            )
        )
        attribute = result.scalar_one_or_none()
        if not attribute:
            _ERROR("NotFound")
        return attribute

    @staticmethod
    async def add_product_attribute(
            product_id: int,
            data: ProductAttributeCreate,
            db: AsyncSession
    ) -> ProductAttribute:
        result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = result.scalar_one_or_none()
        if not product:
            _ERROR("ItemNotFound")

        attribute = ProductAttribute(
            product_id=product_id,
            group_name=data.group_name,
            name=data.name,
            value=data.value,
            sort_order=data.sort_order
        )
        db.add(attribute)
        await db.commit()
        await db.refresh(attribute)

        return attribute

    @staticmethod
    async def update_product_attribute(
            product_id: int,
            product_attribute_id: int,
            data: ProductAttributeUpdate,
            db: AsyncSession
    ) -> ProductAttribute:
        result = await db.execute(
            select(ProductAttribute).where(
                ProductAttribute.product_id == product_id,
                ProductAttribute.product_attribute_id == product_attribute_id
            )
        )
        attribute = result.scalar_one_or_none()

        if not attribute:
            _ERROR("NotFound")

        for field, value in data.dict(exclude_unset=True).items():
            setattr(attribute, field, value)

        db.add(attribute)
        await db.commit()
        await db.refresh(attribute)

        return attribute

    @staticmethod
    async def delete_product_attribute(
            product_id: int,
            product_attribute_id: int,
            db: AsyncSession
    ) -> None:

        result = await db.execute(
            select(ProductAttribute).where(
                ProductAttribute.product_id == product_id,
                ProductAttribute.product_attribute_id == product_attribute_id
            )
        )
        attribute = result.scalar_one_or_none()

        if not attribute:
            _ERROR("NotFound")

        await db.delete(attribute)
        await db.commit()

    @staticmethod
    async def get_product_stores(
            product_id: int,
            db: AsyncSession,
            page: int,
            limit: int,
            needle: str | None
    ) -> tuple[list[dict], int]:
        query = (
            select(ProductStore)
            .options(selectinload(ProductStore.store))  # підтягуємо магазин
            .join(Product)
            .join(Store, Store.store_id == ProductStore.store_id)
            .where(Product.product_id == product_id)
        )

        if needle:
            query = query.where(
                or_(
                    Product.name.ilike(f"%{needle}%"),
                    Store.name.ilike(f"%{needle}%")
                )
            )

        # загальна кількість
        total = await db.scalar(
            select(func.count()).select_from(query.subquery())
        )

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

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

        stores_list = []
        for ps in stores:
            store = ps.store
            stores_list.append({
                "product_store_id": ps.product_store_id,
                "store_id": store.store_id if store else None,
                "store_name": store.name if store else None,
                "store_logo": store.logo if store else None,
                "price": ps.price,
                "price_old": ps.price_old,
                "date_added": ps.date_added.isoformat() if ps.date_added else None,
                "date_modify": ps.date_modify.isoformat() if ps.date_modify else None,
            })

        return stores_list, total

    @staticmethod
    async def get_product_store(
            product_id: int,
            product_store_id: int,
            db: AsyncSession
    ) -> dict:
        result = await db.execute(
            select(ProductStore)
            .options(selectinload(ProductStore.store))
            .where(
                ProductStore.product_id == product_id,
                ProductStore.product_store_id == product_store_id
            )
        )

        ps = result.scalar_one_or_none()
        if not ps:
            _ERROR("ItemNotFound")
        store = ps.store
        return {
            "product_store_id": ps.product_store_id,
            "store_id": store.store_id if store else None,
            "store_name": store.name if store else None,
            "store_logo": store.logo if store else None,
            "price": ps.price,
            "price_od": ps.price_old,
            "date_added": ps.date_added.isoformat() if ps.date_added else None,
            "date_modify": ps.date_modify.isoformat() if ps.date_modify else None,
        }

    @staticmethod
    async def add_store_to_product(
            product_id: int,
            store_id: int,
            price: float,
            price_old: float | None,
            db: AsyncSession
    ) -> ProductStore:
        """Додає магазин до товару або оновлює ціну, якщо зв'язок вже існує (UPSERT)"""
        # Перевіряємо чи існує продукт
        product_result = await db.execute(select(Product).where(Product.product_id == product_id))
        product = product_result.scalar_one_or_none()
        if not product:
            _ERROR("ItemNotFound")

        # Перевіряємо чи існує магазин
        store_result = await db.execute(select(Store).where(Store.store_id == store_id))
        store = store_result.scalar_one_or_none()
        if not store:
            _ERROR("NotFound")

        # Нормалізуємо price_old: якщо 0, то None
        normalized_price_old = None if price_old == 0 else price_old

        # Перевіряємо чи вже існує зв'язок
        existing_result = await db.execute(
            select(ProductStore).where(
                ProductStore.product_id == product_id,
                ProductStore.store_id == store_id
            )
        )
        existing_link = existing_result.scalar_one_or_none()

        if existing_link:
            # ОНОВЛЮЄМО існуючий зв'язок
            existing_link.price = price
            existing_link.price_old = normalized_price_old
            existing_link.date_modify = datetime.utcnow()
            await db.commit()
            await db.refresh(existing_link)
            return existing_link

        # Створюємо новий зв'язок
        product_store = ProductStore(
            product_id=product_id,
            store_id=store_id,
            price=price,
            price_old=normalized_price_old
        )
        db.add(product_store)
        await db.commit()
        await db.refresh(product_store)

        return product_store

    @staticmethod
    async def update_product_store(
            product_id: int,
            store_id: int,
            update_data: dict,
            db: AsyncSession
    ) -> ProductStore:
        result = await db.execute(
            select(ProductStore).where(
                ProductStore.product_id == product_id,
                ProductStore.store_id == store_id
            )
        )
        product_store = result.scalar_one_or_none()
        if not product_store:
            _ERROR("ItemNotFound")

        if "price_old" in update_data and update_data["price_old"] == 0:
            update_data["price_old"] = None

        for key, value in update_data.items():
            setattr(product_store, key, value)

        product_store.date_modify = datetime.utcnow()
        await db.commit()
        await db.refresh(product_store)
        return product_store

    @staticmethod
    async def get_products_by_category(
            category_id: int,
            db: AsyncSession
    ) -> List[int]:
        """
        Отримати всі product_id товарів, які належать до певної категорії
        """
        result = await db.execute(
            select(Product.product_id)
            .join(ProductCategory, Product.product_id == ProductCategory.product_id)
            .where(ProductCategory.category_id == category_id)
        )
        return [row[0] for row in result.all()]

    @staticmethod
    async def transfer_products_to_new_category(
            product_ids: List[int],
            old_category_id: Optional[int],
            new_category_id: int,
            db: AsyncSession
    ) -> TransferResult:
        """
        Перенести товари з однієї категорії в іншу
        """
        try:
            # Перевіряємо існування товарів
            existing_products = await db.execute(
                select(Product.product_id).where(Product.product_id.in_(product_ids))
            )
            existing_product_ids = {row[0] for row in existing_products.all()}

            if len(existing_product_ids) != len(product_ids):
                missing_ids = set(product_ids) - existing_product_ids
                raise ValueError(f"Товари не знайдені: {list(missing_ids)}")

            # Перевіряємо існування нової категорії
            new_category = await db.execute(
                select(Category).where(Category.category_id == new_category_id)
            )
            new_category_obj = new_category.scalar_one_or_none()
            if not new_category_obj:
                raise ValueError("Категорію не знайдено")

            # Отримуємо всі категорії для побудови повного шляху
            all_categories_result = await db.execute(select(Category))
            all_categories = {cat.category_id: cat for cat in all_categories_result.scalars().all()}

            def build_category_path(cat: Category) -> str:
                path = [cat.name]
                current = cat
                while current.parent_category_id:
                    parent = all_categories.get(current.parent_category_id)
                    if not parent:
                        break
                    path.insert(0, parent.name)
                    current = parent
                return " > ".join(path)

            def get_category_hierarchy(category_id: int) -> List[int]:
                """Отримати всі категорії в ієрархії від кореня до поточної"""
                hierarchy = []
                current_id = category_id

                while current_id:
                    hierarchy.append(current_id)
                    current_category = all_categories.get(current_id)
                    if not current_category:
                        break
                    current_id = current_category.parent_category_id

                # Повертаємо в порядку від кореня до листа
                return list(reversed(hierarchy))

            # Будуємо повний шлях для нової категорії
            new_category_full_path = build_category_path(new_category_obj)

            # Отримуємо всі категорії в ієрархії нової категорії
            hierarchy_category_ids = get_category_hierarchy(new_category_id)

            # Видаляємо зв'язки зі старою категорією
            if old_category_id:
                await db.execute(
                    delete(ProductCategory).where(
                        ProductCategory.product_id.in_(product_ids),
                        ProductCategory.category_id == old_category_id
                    )
                )

            # Створюємо зв'язки для всіх категорій в ієрархії
            new_links_created = 0
            for product_id in product_ids:
                for category_id in hierarchy_category_ids:
                    # Перевіряємо чи вже існує зв'язок
                    existing_result = await db.execute(
                        select(ProductCategory.product_category_id).where(
                            ProductCategory.product_id == product_id,
                            ProductCategory.category_id == category_id
                        )
                    )

                    if not existing_result.scalar_one_or_none():
                        # Створюємо новий зв'язок
                        new_link = ProductCategory(
                            product_id=product_id,
                            category_id=category_id
                        )
                        db.add(new_link)
                        new_links_created += 1

            await db.commit()

            return {
                "transferred_count": len(product_ids),
                "new_links_created": new_links_created,
                "old_category_id": old_category_id,
                "new_category_id": new_category_id,
                "new_category_path": new_category_full_path,
                "hierarchy_categories": hierarchy_category_ids  # Всі категорії в ієрархії
            }

        except ValueError:
            await db.rollback()
            raise
        except Exception as e:
            await db.rollback()
            raise Exception(f"Помилка при перенесенні товарів: {str(e)}")

    @staticmethod
    async def get_filter_group(db: AsyncSession, filter_group_id: int):
        """Отримує конкретну фільтр-групу за ID"""

        result = await db.execute(
            select(FilterGroup).where(FilterGroup.filter_group_id == filter_group_id)
        )
        filter_group = result.scalar_one_or_none()

        if not filter_group:
            _ERROR("NotFound")

        return {
            "data": {
                "filter_group_id": filter_group.filter_group_id,
                "name": filter_group.name,
                "sort_order": filter_group.sort_order
            },
            "status": True
        }

    @staticmethod
    async def get_filter_group_list(db: AsyncSession, needle, page, limit):
        """Отримує список фільтр-груп з пагинацією та пошуком"""
        
        query = select(FilterGroup)
        
        # Пошук за назвою
        if needle:
            if needle.isdigit():
                query = query.where(FilterGroup.filter_group_id == int(needle))
            else:
                query = query.where(FilterGroup.name.ilike(f"%{needle}%"))
        
        # Сортування за sort_order та назвою
        query = query.order_by(FilterGroup.sort_order.asc(), FilterGroup.name.asc())
        
        # Підрахунок загальної кількості
        count_query = select(func.count(FilterGroup.filter_group_id))
        if needle:
            if needle.isdigit():
                count_query = count_query.where(FilterGroup.filter_group_id == int(needle))
            else:
                count_query = count_query.where(FilterGroup.name.ilike(f"%{needle}%"))
        
        total_result = await db.execute(count_query)
        total = total_result.scalar()
        
        # Пагинація
        offset = (page - 1) * limit
        query = query.offset(offset).limit(limit)
        
        result = await db.execute(query)
        filter_groups = result.scalars().all()
        
        # Форматування результату
        data = [
            {
                "filter_group_id": fg.filter_group_id,
                "name": fg.name,
                "sort_order": fg.sort_order
            }
            for fg in filter_groups
        ]
        
        return {
            "data": data,
            "total": total,
            "page": page,
            "limit": limit,
            "status": True
        }

    @staticmethod
    async def add_filter_group(db: AsyncSession, name: str, sort_order: int = 0):
        """Додає нову фільтр-групу"""
        
        # Перевіряємо чи не існує вже група з такою назвою
        existing_result = await db.execute(
            select(FilterGroup).where(FilterGroup.name == name)
        )
        existing_group = existing_result.scalar_one_or_none()
        
        if existing_group:
            _ERROR("ValidationError")
        
        # Створюємо нову групу
        new_filter_group = FilterGroup(
            name=name,
            sort_order=sort_order
        )
        
        db.add(new_filter_group)
        await db.commit()
        await db.refresh(new_filter_group)
        
        return {
            "data": {
                "filter_group_id": new_filter_group.filter_group_id,
                "name": new_filter_group.name,
                "sort_order": new_filter_group.sort_order
            },
            "status": True
        }

    @staticmethod
    async def update_filter_group(db: AsyncSession, filter_group_id: int, name: str = None, sort_order: int = None):
        """Оновлює існуючу фільтр-групу"""
        
        # Знаходимо групу
        result = await db.execute(
            select(FilterGroup).where(FilterGroup.filter_group_id == filter_group_id)
        )
        filter_group = result.scalar_one_or_none()
        
        if not filter_group:
            _ERROR("NotFound")
        
        # Перевіряємо унікальність назви (якщо назва змінюється)
        if name and name != filter_group.name:
            existing_result = await db.execute(
                select(FilterGroup).where(FilterGroup.name == name)
            )
            existing_group = existing_result.scalar_one_or_none()
            
            if existing_group:
                _ERROR("ValidationError")
        
        # Оновлюємо поля
        if name is not None:
            filter_group.name = name
        if sort_order is not None:
            filter_group.sort_order = sort_order
        
        await db.commit()
        await db.refresh(filter_group)
        
        return {
            "data": {
                "filter_group_id": filter_group.filter_group_id,
                "name": filter_group.name,
                "sort_order": filter_group.sort_order
            },
            "status": True
        }

    @staticmethod
    async def delete_filter_group(db: AsyncSession, filter_group_id: int):
        """Видаляє фільтр-групу"""
        
        # Знаходимо групу
        result = await db.execute(
            select(FilterGroup).where(FilterGroup.filter_group_id == filter_group_id)
        )
        filter_group = result.scalar_one_or_none()
        
        if not filter_group:
            _ERROR("NotFound")
        
        # Видаляємо групу
        await db.delete(filter_group)
        await db.commit()
        
        return {
            "data": "Filter group deleted successfully",
            "status": True
        }

    @staticmethod
    async def get_filter_list(db: AsyncSession, needle, filter_group_id, page, limit):
        """Отримує список фільтр-груп з пагинацією, пошуком та фільтрацією по filter_group_id"""

        query = select(FilterGroup)

        # Пошук за назвою або ID
        if needle:
            if needle.isdigit():
                query = query.where(FilterGroup.filter_group_id == int(needle))
            else:
                query = query.where(FilterGroup.name.ilike(f"%{needle}%"))

        # Фільтрація по filter_group_id
        if filter_group_id:
            query = query.where(FilterGroup.filter_group_id == filter_group_id)

        # Сортування за sort_order та назвою
        query = query.order_by(FilterGroup.sort_order.asc(), FilterGroup.name.asc())

        # Підрахунок загальної кількості
        count_query = select(func.count(FilterGroup.filter_group_id))

        if needle:
            if needle.isdigit():
                count_query = count_query.where(FilterGroup.filter_group_id == int(needle))
            else:
                count_query = count_query.where(FilterGroup.name.ilike(f"%{needle}%"))

        if filter_group_id:
            count_query = count_query.where(FilterGroup.filter_group_id == filter_group_id)

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

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

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

        # Форматування результату
        data = [
            {
                "filter_group_id": fg.filter_group_id,
                "name": fg.name,
                "sort_order": fg.sort_order
            }
            for fg in filter_groups
        ]

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

    @staticmethod
    async def get_filter(db: AsyncSession, filter_id: int):
        result = await db.execute(select(Filter).where(Filter.filter_id == filter_id))
        filter = result.scalar_one_or_none()

        if not filter:
            _ERROR("NotFound")

        return {
            "data": {
                "filter_id": filter.filter_id,
                "filter_group_id": filter.filter_group_id,
                "name": filter.name,
                "sort_order": filter.sort_order
            },
            "status": True
        }

    @staticmethod
    async def add_filter(db: AsyncSession, name, sort_order, filter_group_id):

        # Перевіряємо чи не існує вже група з такою назвою
        existing_result = await db.execute(
            select(Filter).where(Filter.name == name)
        )
        existing_group = existing_result.scalar_one_or_none()

        if existing_group:
            _ERROR("ValidationError")

        # Створюємо нову групу
        new_filter = Filter(
            name=name,
            sort_order=sort_order,
            filter_group_id=filter_group_id
        )

        db.add(new_filter)
        await db.commit()
        await db.refresh(new_filter)

        return {
            "data": {
                "filter_id" : new_filter.filter_id,
                "filter_group_id": new_filter.filter_group_id,
                "name": new_filter.name,
                "sort_order": new_filter.sort_order
            },
            "status": True
        }

    @staticmethod
    async def update_filter(db: AsyncSession, filter_id: int, name, sort_order):
        result = await db.execute(
            select(Filter).where(Filter.filter_id == filter_id)
        )
        filter = result.scalar_one_or_none()

        if not filter:
            _ERROR("NotFound")

        # Перевіряємо унікальність назви (якщо назва змінюється)
        if name and name != filter.name:
            existing_result = await db.execute(select(Filter).where(Filter.name == name))
            existing_group = existing_result.scalar_one_or_none()

            if existing_group:
                _ERROR("ValidationError")

        # Оновлюємо поля
        if name is not None:
            filter.name = name
        if sort_order is not None:
            filter.sort_order = sort_order

        await db.commit()
        await db.refresh(filter)

        return {
            "data": {
                "filter_id"         : filter.filter_id,
                "filter_group_id"   : filter.filter_group_id,
                "name"              : filter.name,
                "sort_order"        : filter.sort_order
            },
            "status": True
        }

    @staticmethod
    async def delete_filter(db: AsyncSession, filter_id: int):
        """Видаляє фільтр"""

        # Знаходимо групу
        result = await db.execute(
            select(Filter).where(Filter.filter_id == filter_id)
        )
        filter = result.scalar_one_or_none()

        if not filter:
            _ERROR("NotFound")

        # Видаляємо групу
        await db.delete(filter)
        await db.commit()

        return {
            "data": "Filter deleted successfully",
            "status": True
        }

    @staticmethod
    async def get_product_filters(db: AsyncSession, product_id: int, needle: Optional[str] = None, page: int = 1, limit: int = 10):
        """
        Получает все фильтры товара с полной информацией о фильтрах и их группах
        Поддерживает поиск по названию фильтра и группы, пагинацию
        """
        from src.product.models import ProductFilter, Filter, FilterGroup
        from sqlalchemy import or_, func

        # Базовый запрос
        base_query = (
            select(
                ProductFilter.product_filter_id,
                ProductFilter.product_id,
                ProductFilter.filter_id,
                Filter.filter_id.label('filter_filter_id'),
                Filter.filter_group_id,
                Filter.name.label('filter_name'),
                Filter.sort_order.label('filter_sort_order'),
                FilterGroup.filter_group_id.label('group_filter_group_id'),
                FilterGroup.name.label('group_name'),
                FilterGroup.sort_order.label('group_sort_order')
            )
            .join(Filter, ProductFilter.filter_id == Filter.filter_id)
            .join(FilterGroup, Filter.filter_group_id == FilterGroup.filter_group_id)
            .where(ProductFilter.product_id == product_id)
        )

        # Добавляем поиск по needle
        if needle:
            needle_filter = or_(
                Filter.name.ilike(f"%{needle}%"),
                FilterGroup.name.ilike(f"%{needle}%")
            )
            base_query = base_query.where(needle_filter)

        # Запрос для подсчета общего количества
        count_query = select(func.count()).select_from(
            base_query.subquery()
        )
        total_result = await db.execute(count_query)
        total = total_result.scalar()

        # Основной запрос с пагинацией
        offset = (page - 1) * limit
        result = await db.execute(
            base_query
            .order_by(FilterGroup.sort_order, Filter.sort_order)
            .offset(offset)
            .limit(limit)
        )

        filters_data = []
        for row in result:
            filter_item = {
                "product_filter_id": row.product_filter_id,
                "product_id": row.product_id,
                "filter_id": row.filter_id,
                "filter_data": {
                    "filter_id": row.filter_filter_id,
                    "filter_group_id": row.filter_group_id,
                    "name": row.filter_name,
                    "sort_order": row.filter_sort_order,
                    "filter_group_data": {
                        "filter_group_id": row.group_filter_group_id,
                        "name": row.group_name,
                        "sort_order": row.group_sort_order
                    }
                }
            }
            filters_data.append(filter_item)

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

    @staticmethod
    async def get_product_filter(db: AsyncSession, product_filter_id: int):
        # Базовый запрос
        base_query = (
            select(
                ProductFilter.product_filter_id,
                ProductFilter.product_id,
                ProductFilter.filter_id,
                Filter.filter_id.label('filter_filter_id'),
                Filter.filter_group_id,
                Filter.name.label('filter_name'),
                Filter.sort_order.label('filter_sort_order'),
                FilterGroup.filter_group_id.label('group_filter_group_id'),
                FilterGroup.name.label('group_name'),
                FilterGroup.sort_order.label('group_sort_order')
            )
            .join(Filter, ProductFilter.filter_id == Filter.filter_id)
            .join(FilterGroup, Filter.filter_group_id == FilterGroup.filter_group_id)
            .where(ProductFilter.product_filter_id == product_filter_id)
        )

        result = await db.execute(
            base_query
        )

        filters_data = {}
        for row in result:
            filters_data = {
                "product_filter_id": row.product_filter_id,
                "product_id": row.product_id,
                "filter_id": row.filter_id,
                "filter_data": {
                    "filter_id": row.filter_filter_id,
                    "filter_group_id": row.filter_group_id,
                    "name": row.filter_name,
                    "sort_order": row.filter_sort_order,
                    "filter_group_data": {
                        "filter_group_id": row.group_filter_group_id,
                        "name": row.group_name,
                        "sort_order": row.group_sort_order
                    }
                }
            }

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

    @staticmethod
    async def add_product_filter(db: AsyncSession, product_id: int, filter_id: int):
        """
        Добавляет фильтр к товару
        """
        try:
            # Проверяем, существует ли товар
            product_result = await db.execute(
                select(Product).where(Product.product_id == product_id)
            )
            product = product_result.scalar_one_or_none()

            if not product:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Товар с ID {product_id} не найден"
                }

            # Проверяем, существует ли фильтр
            filter_result = await db.execute(
                select(Filter).where(Filter.filter_id == filter_id)
            )
            filter_obj = filter_result.scalar_one_or_none()

            if not filter_obj:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Фильтр с ID {filter_id} не найден"
                }

            # Проверяем, не существует ли уже такая связь
            existing_result = await db.execute(
                select(ProductFilter).where(
                    and_(
                        ProductFilter.product_id == product_id,
                        ProductFilter.filter_id == filter_id
                    )
                )
            )
            existing = existing_result.scalar_one_or_none()

            if existing:
                return {
                    "data": None,
                    "status": False,
                    "message": "Этот фильтр уже добавлен к товару"
                }

            # Создаем новую связь
            new_product_filter = ProductFilter(
                product_id=product_id,
                filter_id=filter_id
            )

            db.add(new_product_filter)
            await db.commit()
            await db.refresh(new_product_filter)

            return {
                "data": {
                    "product_filter_id": new_product_filter.product_filter_id,
                    "product_id": new_product_filter.product_id,
                    "filter_id": new_product_filter.filter_id,
                    "message": "Фильтр успешно добавлен к товару"
                },
                "status": True
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Ошибка при добавлении фильтра: {str(e)}"
            }

    @staticmethod
    async def delete_product_filter(db: AsyncSession, product_id: int, filter_id: int):
        """
        Удаляет фильтр из товара
        """
        try:
            # Ищем связь между товаром и фильтром
            existing_result = await db.execute(
                select(ProductFilter).where(
                    and_(
                        ProductFilter.product_id == product_id,
                        ProductFilter.filter_id == filter_id
                    )
                )
            )
            existing = existing_result.scalar_one_or_none()

            if not existing:
                return {
                    "data": None,
                    "status": False,
                    "message": "Связь между товаром и фильтром не найдена"
                }

            # Удаляем связь
            await db.delete(existing)
            await db.commit()

            return {
                "data": {
                    "product_id": product_id,
                    "filter_id": filter_id,
                    "message": "Фильтр успешно удален из товара"
                },
                "status": True
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Ошибка при удалении фильтра: {str(e)}"
            }

    @staticmethod
    async def generate_filters_from_attributes(
        db: AsyncSession,
        attribute_name: str,
        create_missing_groups: bool = True
    ) -> dict:
        """
        Генерирует фильтры из характеристик товаров для указанного атрибута
        attribute_name становится названием фильтра, group_name - названием группы фильтров
        """
        try:
            # Получаем все уникальные значения для указанного атрибута
            attr_query = await db.execute(
                select(
                    ProductAttribute.group_name,
                    ProductAttribute.value,
                    func.count(ProductAttribute.product_id).label('product_count')
                )
                .where(ProductAttribute.name == attribute_name)
                .group_by(ProductAttribute.group_name, ProductAttribute.value)
                .order_by(ProductAttribute.group_name, ProductAttribute.value)
            )
            attributes = attr_query.all()

            if not attributes:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Не найдено атрибутов с именем '{attribute_name}'"
                }

            # Группируем по group_name (каждый group_name становится группой фильтров)
            groups_data = {}
            for attr in attributes:
                group_name = attr.group_name
                if group_name not in groups_data:
                    groups_data[group_name] = []
                groups_data[group_name].append(attr)

            all_results = []
            total_created = 0

            # Обрабатываем каждую группу
            for group_name, group_attributes in groups_data.items():
                # Проверяем/создаем группу фильтров
                filter_group_query = await db.execute(
                    select(FilterGroup).where(FilterGroup.name == group_name)
                )
                filter_group = filter_group_query.scalar_one_or_none()

                if not filter_group:
                    if create_missing_groups:
                        filter_group = FilterGroup(name=group_name, sort_order=0)
                        db.add(filter_group)
                        await db.flush()  # Получаем ID без коммита
                    else:
                        continue

                # Получаем существующие фильтры в этой группе
                existing_filters_query = await db.execute(
                    select(Filter).where(Filter.filter_group_id == filter_group.filter_group_id)
                )
                existing_filters = {f.name: f for f in existing_filters_query.scalars().all()}

                created_filters = []
                existing_filters_list = []

                # Создаем фильтры - где name фильтра = значение атрибута
                for attr in group_attributes:
                    filter_name = attr.value  # Значение атрибута становится названием фильтра

                    if filter_name not in existing_filters:
                        # Создаем новый фильтр
                        new_filter = Filter(
                            filter_group_id=filter_group.filter_group_id,
                            name=filter_name,
                            sort_order=0
                        )
                        db.add(new_filter)
                        created_filters.append({
                            "name": filter_name,
                            "product_count": attr.product_count
                        })
                        total_created += 1
                    else:
                        existing_filters_list.append({
                            "name": filter_name,
                            "product_count": attr.product_count,
                            "filter_id": existing_filters[filter_name].filter_id
                        })

                all_results.append({
                    "group_name": group_name,
                    "filter_group_id": filter_group.filter_group_id,
                    "created_filters": created_filters,
                    "existing_filters": existing_filters_list,
                    "total_created": len(created_filters),
                    "total_existing": len(existing_filters_list)
                })

            await db.commit()

            return {
                "data": {
                    "attribute_name": attribute_name,
                    "groups_processed": len(groups_data),
                    "total_created_filters": total_created,
                    "results": all_results
                },
                "status": True,
                "message": f"Создано {total_created} новых фильтров для атрибута '{attribute_name}'"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Ошибка при генерации фильтров: {str(e)}"
            }

    @staticmethod
    async def generate_all_filters_from_attributes(
        db: AsyncSession,
        create_missing_groups: bool = True
    ) -> dict:
        """
        Генерирует фильтры из всех характеристик товаров
        """
        try:
            # Получаем все уникальные имена атрибутов
            attributes_query = await db.execute(
                select(ProductAttribute.name)
                .distinct()
                .order_by(ProductAttribute.name)
            )
            attribute_names = [row.name for row in attributes_query]

            if not attribute_names:
                return {
                    "data": None,
                    "status": False,
                    "message": "Не найдено атрибутов товаров для генерации фильтров"
                }

            results = []
            total_created = 0

            # Обрабатываем каждый атрибут
            for attribute_name in attribute_names:
                result = await ProductService.generate_filters_from_attributes(
                    db=db,
                    attribute_name=attribute_name,
                    create_missing_groups=create_missing_groups
                )

                if result.get("status"):
                    data = result.get("data", {})
                    total_created += data.get("total_created_filters", 0)
                    results.append(data)
                else:
                    results.append({
                        "attribute_name": attribute_name,
                        "error": result.get("message", "Неизвестная ошибка")
                    })

            return {
                "data": {
                    "processed_attributes": len(attribute_names),
                    "total_created_filters": total_created,
                    "results": results
                },
                "status": True,
                "message": f"Обработано {len(attribute_names)} атрибутов, создано {total_created} новых фильтров"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Ошибка при генерации всех фильтров: {str(e)}"
            }

    @staticmethod
    async def assign_filters_from_attributes(
        db: AsyncSession,
        attribute_name: str
    ) -> dict:
        """
        Привязывает фильтры к товарам на основе их характеристик
        """
        try:
            # Получаем все атрибуты с указанным именем
            attributes_query = await db.execute(
                select(ProductAttribute)
                .where(ProductAttribute.name == attribute_name)
                .order_by(ProductAttribute.product_id)
            )
            attributes = attributes_query.scalars().all()

            if not attributes:
                return {
                    "data": None,
                    "status": False,
                    "message": f"Не найдено атрибутов с именем '{attribute_name}'"
                }

            # Группируем атрибуты по group_name
            groups_data = {}
            for attr in attributes:
                group_name = attr.group_name
                if group_name not in groups_data:
                    groups_data[group_name] = []
                groups_data[group_name].append(attr)

            all_results = []
            total_created_links = 0

            # Обрабатываем каждую группу
            for group_name, group_attributes in groups_data.items():
                # Получаем группу фильтров
                filter_group_query = await db.execute(
                    select(FilterGroup).where(FilterGroup.name == group_name)
                )
                filter_group = filter_group_query.scalar_one_or_none()

                if not filter_group:
                    all_results.append({
                        "group_name": group_name,
                        "error": f"Группа фильтров '{group_name}' не найдена"
                    })
                    continue

                # Получаем все фильтры в этой группе
                filters_query = await db.execute(
                    select(Filter).where(Filter.filter_group_id == filter_group.filter_group_id)
                )
                filters = {f.name: f.filter_id for f in filters_query.scalars().all()}

                if not filters:
                    all_results.append({
                        "group_name": group_name,
                        "error": f"Не найдено фильтров в группе '{group_name}'"
                    })
                    continue

                # Получаем существующие связи product_filter для этой группы фильтров
                existing_links_query = await db.execute(
                    select(ProductFilter.product_id, ProductFilter.filter_id)
                    .join(Filter, ProductFilter.filter_id == Filter.filter_id)
                    .where(Filter.filter_group_id == filter_group.filter_group_id)
                )
                existing_links = set((row.product_id, row.filter_id) for row in existing_links_query)

                created_links = 0
                skipped_links = 0
                not_found_filters = []

                # Создаем связи для каждого атрибута в группе
                for attr in group_attributes:
                    filter_value = attr.value

                    if filter_value in filters:
                        filter_id = filters[filter_value]
                        link_key = (attr.product_id, filter_id)

                        if link_key not in existing_links:
                            # Создаем новую связь
                            new_link = ProductFilter(
                                product_id=attr.product_id,
                                filter_id=filter_id
                            )
                            db.add(new_link)
                            created_links += 1
                            total_created_links += 1
                        else:
                            skipped_links += 1
                    else:
                        # Фильтр для этого значения не найден
                        if filter_value not in not_found_filters:
                            not_found_filters.append(filter_value)

                all_results.append({
                    "group_name": group_name,
                    "filter_group_id": filter_group.filter_group_id,
                    "total_attributes": len(group_attributes),
                    "created_links": created_links,
                    "skipped_links": skipped_links,
                    "not_found_filters": not_found_filters,
                    "available_filters": list(filters.keys())
                })

            await db.commit()

            return {
                "data": {
                    "attribute_name": attribute_name,
                    "groups_processed": len(groups_data),
                    "total_created_links": total_created_links,
                    "results": all_results
                },
                "status": True,
                "message": f"Создано {total_created_links} новых связей для атрибута '{attribute_name}'"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Ошибка при привязке фильтров: {str(e)}"
            }

    @staticmethod
    async def generate_and_assign_filters_from_attributes(db: AsyncSession) -> dict:
        """
        Генерирует фильтры из всех характеристик товаров и автоматически привязывает их к товарам.
        ProductAttribute.name → название группы фильтров
        ProductAttribute.value → название фильтра
        """
        try:
            # Получаем все уникальные комбинации name (группа) и value (фильтр)
            attr_query = await db.execute(
                select(
                    ProductAttribute.name.label('group_name'),
                    ProductAttribute.value.label('filter_name'),
                    func.count(ProductAttribute.product_id).label('product_count')
                )
                .group_by(ProductAttribute.name, ProductAttribute.value)
                .order_by(ProductAttribute.name, ProductAttribute.value)
            )
            attributes = attr_query.all()

            if not attributes:
                return {
                    "data": None,
                    "status": False,
                    "message": "Не найдено атрибутов товаров для генерации фильтров"
                }

            # Группируем по name (группам фильтров)
            groups_data = {}
            for attr in attributes:
                group_name = attr.group_name
                if group_name not in groups_data:
                    groups_data[group_name] = []
                groups_data[group_name].append(attr)

            all_results = []
            total_created_groups = 0
            total_created_filters = 0
            total_created_links = 0

            # Обрабатываем каждую группу
            for group_name, group_attributes in groups_data.items():
                # Проверяем/создаем группу фильтров
                filter_group_query = await db.execute(
                    select(FilterGroup).where(FilterGroup.name == group_name)
                )
                filter_group = filter_group_query.scalar_one_or_none()

                if not filter_group:
                    filter_group = FilterGroup(name=group_name, sort_order=0)
                    db.add(filter_group)
                    await db.flush()  # Получаем ID без коммита
                    total_created_groups += 1

                # Получаем существующие фильтры в этой группе
                existing_filters_query = await db.execute(
                    select(Filter).where(Filter.filter_group_id == filter_group.filter_group_id)
                )
                existing_filters = {f.name: f for f in existing_filters_query.scalars().all()}

                created_filters = []
                filters_to_assign = {}  # {filter_name: filter_id}

                # Создаем фильтры для каждого уникального значения
                for attr in group_attributes:
                    filter_name = attr.filter_name

                    if filter_name not in existing_filters:
                        # Создаем новый фильтр
                        new_filter = Filter(
                            filter_group_id=filter_group.filter_group_id,
                            name=filter_name,
                            sort_order=0
                        )
                        db.add(new_filter)
                        await db.flush()  # Получаем ID

                        created_filters.append({
                            "name": filter_name,
                            "product_count": attr.product_count
                        })
                        filters_to_assign[filter_name] = new_filter.filter_id
                        total_created_filters += 1
                    else:
                        # Используем существующий фильтр
                        filters_to_assign[filter_name] = existing_filters[filter_name].filter_id

                # Получаем все атрибуты для этой группы для привязки
                group_attributes_query = await db.execute(
                    select(ProductAttribute)
                    .where(ProductAttribute.name == group_name)
                    .order_by(ProductAttribute.product_id)
                )
                group_attributes_full = group_attributes_query.scalars().all()

                # Получаем существующие связи product_filter для этой группы фильтров
                existing_links_query = await db.execute(
                    select(ProductFilter.product_id, ProductFilter.filter_id)
                    .join(Filter, ProductFilter.filter_id == Filter.filter_id)
                    .where(Filter.filter_group_id == filter_group.filter_group_id)
                )
                existing_links = set((row.product_id, row.filter_id) for row in existing_links_query)

                created_links = 0
                skipped_links = 0

                # Привязываем фильтры к товарам
                for attr in group_attributes_full:
                    filter_name = attr.value
                    if filter_name in filters_to_assign:
                        filter_id = filters_to_assign[filter_name]
                        link_key = (attr.product_id, filter_id)

                        if link_key not in existing_links:
                            # Создаем новую связь
                            new_link = ProductFilter(
                                product_id=attr.product_id,
                                filter_id=filter_id
                            )
                            db.add(new_link)
                            created_links += 1
                            total_created_links += 1
                        else:
                            skipped_links += 1

                all_results.append({
                    "group_name": group_name,
                    "filter_group_id": filter_group.filter_group_id,
                    "created_filters": created_filters,
                    "existing_filters": len(existing_filters),
                    "created_links": created_links,
                    "skipped_links": skipped_links,
                    "total_products": len(group_attributes_full)
                })

            await db.commit()

            return {
                "data": {
                    "processed_groups": len(groups_data),
                    "total_created_groups": total_created_groups,
                    "total_created_filters": total_created_filters,
                    "total_created_links": total_created_links,
                    "results": all_results
                },
                "status": True,
                "message": f"Создано {total_created_groups} групп, {total_created_filters} фильтров и {total_created_links} связей"
            }

        except Exception as e:
            await db.rollback()
            return {
                "data": None,
                "status": False,
                "message": f"Ошибка при генерации и привязке фильтров: {str(e)}"
            }

    @staticmethod
    async def export_product(category_id: int, db: AsyncSession):
        try:
            # Получаем все товары привязанные к категории
            stmt = (
                select(Product.product_id, Product.name)
                .join(ProductCategory, Product.product_id == ProductCategory.product_id)
                .where(ProductCategory.category_id == category_id)
                .order_by(Product.name)
            )

            result = await db.execute(stmt)
            products = result.all()

            # Формируем список товаров
            product_list = [
                {"id": product.product_id, "name": product.name}
                for product in products
            ]

            return {
                "data": product_list,
                "status": True,
                "message": f"Найдено товаров: {len(product_list)}"
            }

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