import re
import string
import random
from datetime import datetime
from typing import Optional, List, Tuple
from transliterate import translit
from fastapi import UploadFile, HTTPException
from sqlalchemy import select, func, update, delete
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from src.cart.models import *
from src.category.schemas import CategoryCreateForm
from src.utils.image_manager import handle_image_upload
from src.main import PRODUCTS_CACHE


class CartService:
    @staticmethod
    async def get_current_cart_products(db: AsyncSession, customer_id: int):
        query = (
            select(CurrentCart)
            .where(CurrentCart.customer_id == customer_id)
        )

        result = await db.execute(query)
        items = result.scalars().all()
        cart_data = []
        for item in items:
            product_data = PRODUCTS_CACHE.get(item.product_id)
            if product_data:
                # Create a clean copy to avoid circular references
                clean_product_data = {
                    "product_id": product_data.get("product_id"),
                    "name": product_data.get("name"),
                    "description": product_data.get("description"),
                    "seo_keyword": product_data.get("seo_keyword"),
                    "image": product_data.get("image"),
                    "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"),
                    "stores": [
                        {
                            "id": store.get("id"),
                            "name": store.get("name"),
                            "description": store.get("description"),
                            "phone": store.get("phone"),
                            "email": store.get("email"),
                            "status": store.get("status"),
                            "rating": store.get("rating"),
                            "seo_keyword": store.get("seo_keyword"),
                            "logo": store.get("logo"),
                            "price": store.get("price"),
                        }
                        for store in (product_data.get("stores") or [])
                    ],
                    "filters": product_data.get("filters")
                }
            else:
                clean_product_data = None
                
            cart_data.append({
                "current_cart_product_id"   : item.current_cart_product_id,
                "quantity"                  : item.quantity,
                "date_added"                : item.date_added,
                "store_id"                  : item.store_id,
                "product_data"              : clean_product_data
            })

        return cart_data

    @staticmethod
    async def add_current_cart_products(db: AsyncSession, customer_id: int, request):
        for product in request.products:
            # Визначаємо магазин з найменшою ціною
            product_data = PRODUCTS_CACHE.get(product.product_id)
            best_store_id = 0
            if product_data:
                stores = product_data.get("stores") or []
                if stores:
                    best_store_id = stores[0].get("id", 0)  # stores вже відсортовані за ціною (мін. перший)

            # Проверяем, есть ли уже товар в корзине
            result = await db.execute(
                select(CurrentCart).where(
                    CurrentCart.customer_id == customer_id,
                    CurrentCart.product_id == product.product_id
                )
            )
            cart_product = result.scalars().first()

            if cart_product:
                # обновляем количество и обновляем магазин на самый дешёвый
                cart_product.quantity += product.quantity
                cart_product.store_id = best_store_id
            else:
                # создаем новую запись
                cart_product = CurrentCart(
                    customer_id=customer_id,
                    product_id=product.product_id,
                    quantity=product.quantity,
                    store_id=best_store_id,
                )
                db.add(cart_product)

        await db.commit()

    @staticmethod
    async def edit_current_cart_products(db: AsyncSession, current_cart_product_id: int, quantity: Optional[int] = None, store_id: Optional[int] = None):
        # Найдём запись
        result = await db.execute(
            select(CurrentCart).where(CurrentCart.current_cart_product_id == current_cart_product_id)
        )
        cart_product = result.scalar_one_or_none()

        if cart_product:
            # Обновляем только переданные поля
            if quantity is not None:
                cart_product.quantity = quantity
            if store_id is not None:
                cart_product.store_id = store_id

            db.add(cart_product)
            await db.commit()
            await db.refresh(cart_product)
            return cart_product

        return None

    @staticmethod
    async def get_current_cart_product(db: AsyncSession, current_cart_product_id: int):
        # Найдём запись
        result = await db.execute(
            select(CurrentCart).where(CurrentCart.current_cart_product_id == current_cart_product_id)
        )
        cart_product = result.scalar_one_or_none()

        if cart_product:
            return cart_product

        return None

    @staticmethod
    async def delete_current_cart_products(db: AsyncSession, current_cart_product_id: int):
        # Найдём запись
        result = await db.execute(
            select(CurrentCart).where(CurrentCart.current_cart_product_id == current_cart_product_id)
        )
        cart_product = result.scalar_one_or_none()

        if cart_product:
            await db.delete(cart_product)  # удаляем объект
            await db.commit()  # фиксируем изменения
            return True

        return False

    @staticmethod
    async def get_cart_list(db: AsyncSession, customer_id: int):
        query = (
            select(Cart)
            .where(Cart.customer_id == customer_id)
            .options(selectinload(Cart.products))  # подтягиваем связанные товары
        )

        result = await db.execute(query)
        carts = result.scalars().unique().all()

        cart_data = []
        for cart in carts:
            cart_products = []
            for product in cart.products:
                cart_products.append({
                    "cart_product_id"  : product.cart_product_id,
                    "quantity"         : product.quantity,
                    "store_id"         : product.store_id,
                    "product_data"     : PRODUCTS_CACHE.get(product.product_id)
                })

            cart_data.append({
                "cart_id"      : cart.cart_id,
                "name"         : cart.name,
                "date_added"   : cart.date_added,
                "date_updated" : cart.date_updated,
                "products"     : cart_products
            })

        return cart_data

    @staticmethod
    async def get_cart_count(db: AsyncSession, customer_id: int) -> int:
        """Получить количество сохраненных корзин для пользователя"""
        result = await db.execute(
            select(func.count(Cart.cart_id)).where(Cart.customer_id == customer_id)
        )
        return result.scalar() or 0

    @staticmethod
    async def get_cart_details_for_owner(cart_id: int, customer_id: int, db: AsyncSession):
        """Отримати деталі корзини для власника"""

        query = select(Cart).where(
            Cart.cart_id == cart_id,
            Cart.customer_id == customer_id
        )

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

        if not cart:
            return {"data": None, "status": False, "error": "Корзину не знайдено"}

        return {
            "data": {
                "cart_id": cart.cart_id,
                "name": cart.name,
                "is_public": cart.is_public,
                "seo_keyword": cart.seo_keyword,
                "share_url": f"/cart/{cart.seo_keyword}" if cart.is_public and cart.seo_keyword else None,
                "date_added": cart.date_added,
                "date_updated": cart.date_updated
            },
            "status": True
        }

    async def save_current_cart(self, db: AsyncSession, customer_id: int, name: str, product_ids: Optional[List[int]] = None):
        """Збереження поточної корзини.

        product_ids — список current_cart_product_id для часткового збереження.
        Якщо None або порожній — зберігаються всі товари.
        """

        cart_count = await self.get_cart_count(db, customer_id)
        if cart_count >= 3:
            raise HTTPException(
                status_code=400,
                detail="Досягнуто максимальну кількість збережених корзин (3). Видаліть одну з існуючих корзин для створення нової."
            )

        current_products = await self.get_current_cart_products(db=db, customer_id=customer_id)
        if not current_products:
            raise HTTPException(
                status_code=400,
                detail="Неможливо зберегти порожню корзину. Додайте товари перед збереженням."
            )

        # Визначаємо які товари зберігаємо
        if product_ids:
            products_to_save = [p for p in current_products if p["current_cart_product_id"] in product_ids]
            if not products_to_save:
                raise HTTPException(
                    status_code=400,
                    detail="Жоден з переданих товарів не знайдено в поточній корзині."
                )
        else:
            products_to_save = current_products

        # Створюємо корзину
        cart = Cart(
            customer_id=customer_id,
            name=name
        )
        db.add(cart)
        await db.commit()
        await db.refresh(cart)

        cart_id = cart.cart_id

        # Копіюємо товари зі збереженням вибраного магазину
        from src.store.models import ProductStore

        for product_data in products_to_save:
            product_id = product_data["product_data"]["product_id"]
            store_id = product_data["store_id"]

            # Перевіряємо що магазин існує для цього товару
            store_result = await db.execute(
                select(ProductStore.store_id)
                .where(
                    ProductStore.product_id == product_id,
                    ProductStore.store_id == store_id
                )
                .limit(1)
            )
            valid_store_id = store_result.scalar()

            if not valid_store_id:
                # Якщо вибраний магазин більше не доступний — беремо перший доступний
                fallback = await db.execute(
                    select(ProductStore.store_id)
                    .where(ProductStore.product_id == product_id)
                    .limit(1)
                )
                valid_store_id = fallback.scalar()

            if not valid_store_id:
                raise HTTPException(
                    status_code=400,
                    detail=f"Товар з ID {product_id} недоступний в жодному магазині"
                )

            cart_product = CartProduct(
                cart_id=cart_id,
                product_id=product_id,
                quantity=product_data["quantity"],
                store_id=valid_store_id
            )
            db.add(cart_product)

        await db.commit()

        # Видаляємо з поточної корзини тільки збережені товари
        ids_to_delete = [p["current_cart_product_id"] for p in products_to_save]
        stmt = delete(CurrentCart).where(CurrentCart.current_cart_product_id.in_(ids_to_delete))
        await db.execute(stmt)
        await db.commit()

        return {
            "cart_id": cart_id,
            "name": name,
            "date_added": cart.date_added,
            "products": products_to_save
        }

    @staticmethod
    async def clear_current_cart(db: AsyncSession, customer_id: int):
        stmt = delete(CurrentCart).where(CurrentCart.customer_id == customer_id)
        await db.execute(stmt)
        await db.commit()

    @staticmethod
    async def get_cart_products(db: AsyncSession, cart_id: int):
        query = (
            select(CartProduct)
            .where(CartProduct.cart_id == cart_id)
        )

        result = await db.execute(query)
        items = result.scalars().all()
        cart_data = []
        for item in items:
            cart_data.append({
                "cart_product_id"           : item.cart_product_id,
                "quantity"                  : item.quantity,
                "store_id"                  : item.store_id,
                "product_data"              : PRODUCTS_CACHE[item.product_id]
            })

        return cart_data

    @staticmethod
    async def get_cart_product(db: AsyncSession, cart_product_id: int):
        query = (
            select(CartProduct)
            .where(CartProduct.cart_product_id == cart_product_id)
        )

        result = await db.execute(query)
        items = result.scalars().all()
        cart_data = {}
        for item in items:
            cart_data = {
                "cart_product_id"           : item.cart_product_id,
                "quantity"                  : item.quantity,
                "store_id"                  : item.store_id,
                "product_data"              : PRODUCTS_CACHE[item.product_id]
            }

        return cart_data

    @staticmethod
    async def edit_cart_product(db: AsyncSession, cart_product_id: int, quantity: Optional[int] = None, store_id: Optional[int] = None):
        # Формируем словарь только с переданными значениями
        update_values = {}
        if quantity is not None:
            update_values["quantity"] = quantity
        if store_id is not None:
            update_values["store_id"] = store_id

        # Если нет значений для обновления, ничего не делаем
        if not update_values:
            return

        stmt = (
            update(CartProduct)
            .where(CartProduct.cart_product_id == cart_product_id)
            .values(**update_values)
            .execution_options(synchronize_session="fetch")
        )

        await db.execute(stmt)
        await db.commit()

    @staticmethod
    async def delete_cart_product(db: AsyncSession, cart_product_id: int):
        stmt = (
            delete(CartProduct)
            .where(CartProduct.cart_product_id == cart_product_id)
        )

        await db.execute(stmt)
        await db.commit()

    @staticmethod
    async def transfer_cart_product(db: AsyncSession, cart_product_id: int, new_cart_id: int):
        # Получаем товар из корзины
        result = await db.execute(
            select(CartProduct).where(CartProduct.cart_product_id == cart_product_id)
        )
        cart_product = result.scalar_one_or_none()
        
        if not cart_product:
            return None
        
        # Создаем копию товара в новой корзине
        new_cart_product = CartProduct(
            cart_id=new_cart_id,
            product_id=cart_product.product_id,
            quantity=cart_product.quantity,
            store_id=cart_product.store_id
        )
        
        db.add(new_cart_product)
        await db.commit()
        await db.refresh(new_cart_product)
        
        return {
            "cart_product_id": new_cart_product.cart_product_id,
            "quantity": new_cart_product.quantity,
            "store_id": new_cart_product.store_id,
            "product_data": PRODUCTS_CACHE[new_cart_product.product_id]
        }

    @staticmethod
    def generate_seo_keyword():
        """Генерирует уникальный SEO ключ в формате cart-{10 случайных символов}"""
        characters = string.ascii_lowercase + string.digits
        random_part = ''.join(random.choices(characters, k=10))
        return f"cart-{random_part}"

    @staticmethod
    async def share_cart(db: AsyncSession, cart_id: int, customer_id: int):
        """Создает публичную копию корзины для шаринга"""
        
        # Получаем оригинальную корзину
        cart_result = await db.execute(
            select(Cart).where(Cart.cart_id == cart_id, Cart.customer_id == customer_id)
        )
        cart = cart_result.scalar_one_or_none()
        
        if not cart:
            raise HTTPException(
                status_code=404,
                detail="Корзина не найдена"
            )
        
        # Получаем товары корзины
        products_result = await db.execute(
            select(CartProduct).where(CartProduct.cart_id == cart_id)
        )
        products = products_result.scalars().all()
        
        if not products:
            raise HTTPException(
                status_code=400,
                detail="Нельзя поделиться пустой корзиной"
            )
        
        # Генерируем уникальный SEO ключ
        seo_keyword = None
        while True:
            seo_keyword = CartService.generate_seo_keyword()
            existing = await db.execute(
                select(SharedCart).where(SharedCart.seo_keyword == seo_keyword)
            )
            if not existing.scalar_one_or_none():
                break
        
        # Создаем публичную корзину
        shared_cart = SharedCart(
            original_cart_id=cart_id,
            customer_id=customer_id,
            name=cart.name,
            seo_keyword=seo_keyword
        )
        
        db.add(shared_cart)
        await db.commit()
        await db.refresh(shared_cart)
        
        # Копируем товары
        for product in products:
            shared_product = SharedCartProduct(
                shared_cart_id=shared_cart.shared_cart_id,
                product_id=product.product_id,
                quantity=product.quantity,
                store_id=product.store_id
            )
            db.add(shared_product)
        
        await db.commit()
        
        return {
            "shared_cart_id": shared_cart.shared_cart_id,
            "seo_keyword": seo_keyword,
            "share_url": f"/cart/{seo_keyword}",
            "name": cart.name,
            "date_added": shared_cart.date_added
        }

    @staticmethod
    async def get_shared_cart_by_seo(db: AsyncSession, seo_keyword: str):
        """Получает публичную корзину по SEO ключу"""
        
        # Получаем публичную корзину
        shared_cart_result = await db.execute(
            select(SharedCart).where(SharedCart.seo_keyword == seo_keyword)
        )
        shared_cart = shared_cart_result.scalar_one_or_none()
        
        if not shared_cart:
            return {
                "data": None,
                "status": False,
                "error": "Корзина не найдена"
            }
        
        # Получаем товары из публичной корзины
        products_result = await db.execute(
            select(SharedCartProduct).where(SharedCartProduct.shared_cart_id == shared_cart.shared_cart_id)
        )
        products = products_result.scalars().all()
        
        # Формируем данные товаров с информацией из кэша
        cart_products = []
        for product in products:
            product_data = PRODUCTS_CACHE.get(product.product_id)
            if product_data:
                cart_products.append({
                    "shared_cart_product_id": product.shared_cart_product_id,
                    "quantity": product.quantity,
                    "store_id": product.store_id,
                    "product_data": product_data
                })
        
        cart_data = {
            "shared_cart_id": shared_cart.shared_cart_id,
            "original_cart_id": shared_cart.original_cart_id,
            "customer_id": shared_cart.customer_id,
            "name": shared_cart.name,
            "seo_keyword": shared_cart.seo_keyword,
            "date_added": shared_cart.date_added,
            "products": cart_products
        }
        
        return {
            "data": cart_data,
            "status": True
        }

    @staticmethod
    async def get_selected_products(db: AsyncSession, customer_id: int, page: int = 1, limit: int = 10, sort_field: str = "name", sort_order: str = "asc"):
        offset = (page - 1) * limit

        # Получаем product_id из таблицы selected_product для данного пользователя
        result = await db.execute(
            select(SelectedProduct.product_id)
            .where(SelectedProduct.customer_id == customer_id)
        )
        product_ids = [row.product_id for row in result]

        if not product_ids:
            return {
                "data": [],
                "total": 0,
                "page": page,
                "limit": limit,
                "status": True
            }

        # Получаем товары из кеша по найденным ID
        products = []
        for product_id in product_ids:
            if product_id in PRODUCTS_CACHE:
                products.append(PRODUCTS_CACHE[product_id])

        # Сортировка товаров
        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"]:
                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)

        total = len(products)

        # Пагинация
        paginated_products = products[offset:offset + limit]

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

    @staticmethod
    async def toggle_selected_product(db: AsyncSession, customer_id: int, product_id: int):
        """
        Переключает товар в избранном: добавляет если нет, удаляет если есть
        """
        # Проверяем, есть ли товар в избранном у пользователя
        result = await db.execute(
            select(SelectedProduct)
            .where(
                SelectedProduct.customer_id == customer_id,
                SelectedProduct.product_id == product_id
            )
        )
        selected_product = result.scalar_one_or_none()

        if selected_product:
            # Товар есть в избранном - удаляем его
            await db.delete(selected_product)
            await db.commit()

            return {
                "action": "removed",
                "message": "Товар видалено з обраних",
                "product_id": product_id,
                "status": True
            }
        else:
            # Товара нет в избранном - добавляем его
            new_selected_product = SelectedProduct(
                customer_id=customer_id,
                product_id=product_id
            )
            db.add(new_selected_product)
            await db.commit()
            await db.refresh(new_selected_product)

            return {
                "action": "added",
                "message": "Товар додано до обраних",
                "product_id": product_id,
                "selected_product_id": new_selected_product.selected_product_id,
                "status": True
            }

    @staticmethod
    async def delete_cart_products(db: AsyncSession, cart_id: int):
        stmt = (
            delete(Cart)
            .where(Cart.cart_id == cart_id)
        )

        await db.execute(stmt)
        await db.commit()