from datetime import datetime
from typing import Optional

from fastapi import HTTPException, UploadFile
from sqlalchemy import asc, desc, select, func, or_, insert
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import aliased
from starlette import status

from src.address.models import City, Zone
from src.product.models import Product
from src.store.models import Store, StorePhoto, StoreAddress, ProductStore
from src.store.schemas import StoreCreate, StoreUpdate, StorePhotoResponse, StoreAddressCreate, StoreAddressUpdate, \
    StoreAddressFilter, StoreOutSchema
from src.utils.image_manager import handle_image_upload

ALLOWED_SORT_FIELDS = {"name", "date_added", "date_modify", "rating"}


class StoreService:
    @staticmethod
    async def get_stores_list(
            db: AsyncSession,
            needle: Optional[str] = None,
            sort_field: str = "date_added",
            sort_order: str = "desc",
            page: int = 1,
            limit: int = 10,
    ) -> dict:

        # Підбираємо правильний атрибут моделі
        if sort_field == "id":
            sort_attr = Store.store_id
        elif sort_field == "rating":
            sort_attr = Store.rating
        else:
            sort_attr = getattr(Store, sort_field)

        order_func = asc if sort_order.upper() == "ASC" else desc

        base_query = (
            select(
                Store,
                func.count(ProductStore.product_store_id).label("product_count")
            )
            .join(ProductStore, ProductStore.store_id == Store.store_id, isouter=True)
            .group_by(Store.store_id)
        )

        count_query = select(func.count()).select_from(Store)

        if needle:
            like_term = f"%{needle.lower()}%"
            filters = or_(
                Store.name.ilike(like_term),
            )
            base_query = base_query.where(filters)
            count_query = count_query.where(filters)

        base_query = base_query.order_by(order_func(sort_attr))
        base_query = base_query.offset((page - 1) * limit).limit(limit)

        result = await db.execute(base_query)
        items_with_counts = result.all()  # список кортежів (Store, product_count)

        items = []
        for store, product_count in items_with_counts:
            store_dict = store.__dict__.copy()
            store_dict['product_count'] = product_count
            items.append(store_dict)

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

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

    @staticmethod
    async def get_store_by_id(
            db: AsyncSession,
            store_id: int
    ) -> Store:
        query = select(Store).where(Store.store_id == store_id)
        result = await db.execute(query)
        store = result.scalars().first()
        if not store:
            raise HTTPException(status_code=404, detail="Store not found")
        return store

    @staticmethod
    async def create_store(
            db: AsyncSession,
            data: StoreCreate,
            logo_url: Optional[str] = None,
    ) -> Store:
        new_store = Store(
            name=data.name,
            description=data.description,
            address=data.address,
            phone=data.phone,
            email=data.email,
            website=data.website,
            url_youtube=data.url_youtube,
            url_viber=data.url_viber,
            url_facebook=data.url_facebook,
            url_telegram=data.url_telegram,
            status=data.status,
            seo_keyword=data.seo_keyword,
            meta_title=data.meta_title,
            meta_description=data.meta_description,
            meta_keyword=data.meta_keyword,
            rating=data.rating or 0,
            logo=logo_url or "/assets/placeholder.webp",
            date_added=datetime.utcnow(),
            date_modify=datetime.utcnow(),
        )
        db.add(new_store)
        await db.commit()
        await db.refresh(new_store)
        return new_store

    @staticmethod
    async def update_store(
            db: AsyncSession,
            store_id: int,
            update_data: dict,
            logo_url: Optional[str] = None,
    ) -> Store:
        stmt = select(Store).where(Store.store_id == store_id)
        result = await db.execute(stmt)
        store = result.scalar_one_or_none()

        if not store:
            raise HTTPException(status_code=404, detail=f"Store with ID {store_id} not found")

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

        if logo_url:
            store.logo = logo_url

        store.date_modify = datetime.utcnow()

        db.add(store)
        await db.commit()
        await db.refresh(store)
        return store
    @staticmethod
    async def delete_store(db: AsyncSession, store_id: int):
        # Перевірка, чи є товари в магазині
        product_link_exists = await db.execute(
            select(ProductStore).where(ProductStore.store_id == store_id).limit(1)
        )
        product_link = product_link_exists.scalar_one_or_none()
        if product_link:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Cannot delete store: products linked to this store exist."
            )

        # Знаходимо магазин
        store_res = await db.execute(select(Store).where(Store.store_id == store_id))
        store = store_res.scalar_one_or_none()
        if not store:
            raise HTTPException(status_code=404, detail="Store not found")

        # Видаляємо магазин
        await db.delete(store)
        await db.commit()

    @staticmethod
    async def get_store_photos(
            store_id: int,
            db: AsyncSession,
            page: int,
            limit: int
    ) -> tuple[list[StorePhoto], int]:
        try:
            # Загальна кількість фото
            total = await db.scalar(
                select(func.count()).select_from(
                    select(StorePhoto).where(StorePhoto.store_id == store_id).subquery()
                )
            )

            # Отримання фото з пагінацією
            result = await db.execute(
                select(StorePhoto)
                .where(StorePhoto.store_id == store_id)
                .order_by(desc(StorePhoto.store_photo_id))
                .offset((page - 1) * limit)
                .limit(limit)
            )
            photos = result.scalars().all()
            return photos, total
        except Exception as e:
            return [], 0

    @staticmethod
    async def add_store_photo(
            db: AsyncSession,
            store_id: int,
            image_url: str
    ) -> StorePhoto:
        # Перевіряємо, чи існує магазин
        store = await db.execute(select(Store).where(Store.store_id == store_id))
        if not store.scalar_one_or_none():
            raise HTTPException(status_code=404, detail="Store not found")

        new_photo = StorePhoto(
            store_id=store_id,
            image=image_url
        )

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

        return new_photo


    @staticmethod
    async def delete_store_photo(
            db: AsyncSession,
            store_id: int,
            store_photo_id: int
    ):
        result = await db.execute(
            select(StorePhoto).where(
                StorePhoto.store_photo_id == store_photo_id,
                StorePhoto.store_id == store_id
            )
        )
        photo = result.scalar_one_or_none()

        if not photo:
            raise HTTPException(status_code=404, detail="Store photo not found")

        await db.delete(photo)
        await db.commit()

    @staticmethod
    async def add_store_address(
            db: AsyncSession,
            store_id: int,
            data: StoreAddressCreate,
    ) -> StoreAddress:
        store_result = await db.execute(
            select(Store).where(Store.store_id == store_id)
        )
        store = store_result.scalar_one_or_none()
        if not store:
            raise HTTPException(status_code=404, detail="Store not found")

        new_address = StoreAddress(
            store_id=store_id,
            address=data.address,
            phone_number=data.phone_number,
            schedule=data.schedule,
            geolocation=data.geolocation,
            city_id=data.city_id,
            zone_id=data.zone_id
        )
        db.add(new_address)
        await db.commit()
        await db.refresh(new_address)

        # Отримуємо назви міста та зони
        result = await db.execute(
            select(
                StoreAddress.store_address_id,
                StoreAddress.store_id,
                StoreAddress.address,
                StoreAddress.city_id,
                City.name.label("city_name"),
                StoreAddress.zone_id,
                Zone.name.label("zone_name"),
                StoreAddress.phone_number,
                StoreAddress.schedule,
                StoreAddress.geolocation,
                StoreAddress.date_added,
                StoreAddress.date_modify
            )
            .join(City, StoreAddress.city_id == City.city_id)
            .join(Zone, StoreAddress.zone_id == Zone.zone_id)
            .where(StoreAddress.store_address_id == new_address.store_address_id)
        )
        address_with_names = result.mappings().first()

        return address_with_names

    @staticmethod
    async def update_store_address(
            db: AsyncSession,
            store_id: int,
            store_address_id: int,
            update_data: StoreAddressUpdate
    ):
        # Шукаємо адресу
        result = await db.execute(
            select(StoreAddress).where(
                StoreAddress.store_address_id == store_address_id,
                StoreAddress.store_id == store_id
            )
        )
        address = result.scalar_one_or_none()

        if not address:
            raise HTTPException(status_code=404, detail="Store address not found")

        # Оновлюємо дані
        update_dict = update_data.dict(exclude_unset=True)
        for key, value in update_dict.items():
            setattr(address, key, value)

        address.date_modify = datetime.utcnow()

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

        # Повертаємо з JOIN, щоб були city_name і zone_name
        result_with_names = await db.execute(
            select(
                StoreAddress.store_address_id,
                StoreAddress.store_id,
                StoreAddress.address,
                StoreAddress.city_id,
                City.name.label("city_name"),
                StoreAddress.zone_id,
                Zone.name.label("zone_name"),
                StoreAddress.phone_number,
                StoreAddress.schedule,
                StoreAddress.geolocation,
                StoreAddress.date_added,
                StoreAddress.date_modify
            )
            .join(City, StoreAddress.city_id == City.city_id)
            .join(Zone, StoreAddress.zone_id == Zone.zone_id)
            .where(StoreAddress.store_address_id == store_address_id)
        )
        return result_with_names.mappings().first()

    @staticmethod
    async def get_store_addresses(
            db: AsyncSession,
            store_id: int,
            filters: StoreAddressFilter,
            page: int = 1,
            limit: int = 10
    ):
        offset = (page - 1) * limit

        # Створюємо запит з JOIN до City і Zone
        stmt = (
            select(StoreAddress, City.name.label("city_name"), Zone.name.label("zone_name"))
            .join(City, StoreAddress.city_id == City.city_id)
            .join(Zone, StoreAddress.zone_id == Zone.zone_id)
            .where(StoreAddress.store_id == store_id)
        )

        # Фільтрація
        if filters.city_id:
            stmt = stmt.where(StoreAddress.city_id == filters.city_id)
        if filters.zone_id:
            stmt = stmt.where(StoreAddress.zone_id == filters.zone_id)

        if filters.search:
            search = f"%{filters.search.lower()}%"
            stmt = stmt.where(
                or_(
                    func.lower(StoreAddress.address).like(search),
                    func.lower(StoreAddress.phone_number).like(search),
                    func.lower(StoreAddress.schedule).like(search)
                )
            )

        # Загальна кількість
        total_stmt = (
            select(func.count())
            .select_from(StoreAddress)
            .where(StoreAddress.store_id == store_id)
        )
        if filters.city_id:
            total_stmt = total_stmt.where(StoreAddress.city_id == filters.city_id)
        if filters.zone_id:
            total_stmt = total_stmt.where(StoreAddress.zone_id == filters.zone_id)
        if filters.search:
            total_stmt = total_stmt.where(
                or_(
                    func.lower(StoreAddress.address).like(search),
                    func.lower(StoreAddress.phone_number).like(search),
                    func.lower(StoreAddress.schedule).like(search)
                )
            )

        total_result = await db.execute(total_stmt)
        total_count = total_result.scalar()

        # Основний запит з пагінацією і сортуванням
        stmt = stmt.order_by(desc(StoreAddress.date_added)).offset(offset).limit(limit)
        result = await db.execute(stmt)
        rows = result.all()  # список кортежів (StoreAddress, city_name, zone_name)

        # Повертаємо список кортежів
        return rows, total_count

    @staticmethod
    async def delete_store_address(
            db: AsyncSession,
            store_id: int,
            store_address_id: int,
    ):
        result = await db.execute(
            select(StoreAddress).where(
                StoreAddress.store_address_id == store_address_id,
                StoreAddress.store_id == store_id
            )
        )
        address = result.scalar_one_or_none()

        if not address:
            raise HTTPException(status_code=404, detail="Store address not found")

        await db.delete(address)
        await db.commit()