from typing import Optional, List

from fastapi import HTTPException
from sqlalchemy import select, asc, desc, func, or_
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import joinedload

from src.address.models import Zone, City
from src.address.schemas import ZoneResponse, ZoneCreate, ZoneUpdateSchema, CityCreateSchema, CityUpdateSchema
from src.address.utils import fetch_new_post_zones, fetch_new_post_cities, fetch_np_cities_for_zone, \
    get_zone_ref_by_name
from src.error import _ERROR
from src.store.models import StoreAddress


class ZoneService:
    @staticmethod
    async def get_zone_list(
        db: AsyncSession,
        needle: Optional[str] = None,
        sort_order: str = "ASC",
        page: int = 1,
        limit: int = 10,
    ) -> dict:
        base_query = select(Zone)
        count_query = select(func.count()).select_from(Zone)

        if needle:
            like = f"%{needle}%"
            base_query = base_query.where(Zone.name.ilike(like))
            count_query = count_query.where(Zone.name.ilike(like))

        order_func = asc if sort_order.upper() == "ASC" else desc
        base_query = base_query.order_by(order_func(Zone.name))
        base_query = base_query.offset((page - 1) * limit).limit(limit)

        result = await db.execute(base_query)
        zones = result.scalars().all()

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

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

    @staticmethod
    async def get_zone_by_id(zone_id: int, db: AsyncSession) -> dict:
        result = await db.execute(select(Zone).where(Zone.zone_id == zone_id))
        zone = result.scalar_one_or_none()

        if not zone:
            _ERROR("ZoneNotFound")

        return {"data": ZoneResponse.model_validate(zone), "status": True}

    @staticmethod
    async def create_zone(data: ZoneCreate, db: AsyncSession) -> dict:
        # Перевіряємо на унікальність назви
        existing = await db.execute(select(Zone).where(Zone.name == data.name))
        if existing.scalar_one_or_none():
            _ERROR("ZoneAlreadyExists")

        zone = Zone(name=data.name)
        db.add(zone)
        await db.commit()
        await db.refresh(zone)

        return {"data": ZoneResponse.model_validate(zone), "status": True}

    @staticmethod
    async def update_zone(zone_id: int, data: ZoneUpdateSchema, db: AsyncSession) -> dict:
        stmt = select(Zone).where(Zone.zone_id == zone_id)
        result = await db.execute(stmt)
        zone = result.scalar_one_or_none()

        if not zone:
            _ERROR("ZoneNotFound")

        update_fields = ["name"]
        for field in update_fields:
            value = getattr(data, field, None)
            if value is not None:
                setattr(zone, field, value)

        try:
            await db.commit()
            await db.refresh(zone)
            return {"data": zone, "status": True}
        except SQLAlchemyError:
            await db.rollback()
            _ERROR("DatabaseError")

    @staticmethod
    async def delete_zone(zone_id: int, db: AsyncSession):
        # Перевіряємо чи є пов'язані міста
        city_stmt = select(City).where(City.zone_id == zone_id)
        city_result = await db.execute(city_stmt)
        if city_result.scalars().first():
            _ERROR("ZoneNotEmpty")

        zone_stmt = select(Zone).where(Zone.zone_id == zone_id)
        zone_result = await db.execute(zone_stmt)
        zone = zone_result.scalar_one_or_none()

        if not zone:
            _ERROR("ZoneNotFound")

        await db.delete(zone)
        await db.commit()
        return {"data": {"zone_id": zone_id}, "status": True}

    @staticmethod
    async def get_zone_cities_list(
            db: AsyncSession,
            zone_id: int,
            needle: str = None,
            page: int = 10,
            limit: int = 1,
    ) -> dict:
        base_query = (
            select(City)
            .options(joinedload(City.zone))
            .where(City.zone_id == zone_id)
        )

        count_query  = select(func.count()).select_from(City).where(City.zone_id == zone_id)

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

        base_query = base_query.order_by(asc(City.name))
        base_query = base_query.offset((page - 1) * limit).limit(limit)

        result = await db.execute(base_query)
        cities = result.scalars().all()

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

        return {
            "items": [
                {
                    "city_id": city.city_id,
                    "name": city.name,
                    "zone_id": city.zone.zone_id,
                    "zone_name": city.zone.name
                }
                for city in cities
            ],
            "total": total
        }

    @staticmethod
    async def get_city_by_id(zone_id: int, city_id: int, db: AsyncSession):
        stmt = (
            select(City.city_id, City.name, City.zone_id, Zone.name.label("zone_name"))
            .join(Zone, City.zone_id == Zone.zone_id)
            .where(City.city_id == city_id, City.zone_id == zone_id)
        )
        result = await db.execute(stmt)
        city = result.mappings().first()

        if not city:
            _ERROR("CityNotFound")

        return {"data": dict(city), "status": True}

    @staticmethod
    async def create_city_service(data: CityCreateSchema, db: AsyncSession):
        # Перевіряємо на існування відповідної області
        zone = await db.scalar(select(Zone).where(Zone.zone_id == data.zone_id))
        if not zone:
            _ERROR("ZoneNotFound")

        new_city = City(name=data.name, zone_id=data.zone_id)
        db.add(new_city)
        await db.commit()
        await db.refresh(new_city)

        return {
            "data": {
                "city_id": new_city.city_id,
                "name": new_city.name,
                "zone_id": new_city.zone_id,
                "zone_name": zone.name
            },
            "status": True
        }

    @staticmethod
    async def update_city(zone_id: int, city_id: int, data: CityUpdateSchema, db: AsyncSession):
        stmt = select(City).where(City.city_id == city_id, Zone.zone_id == zone_id)
        result = await db.execute(stmt)
        city = result.scalar_one_or_none()

        if not city:
            _ERROR("CityNotFound")

        if data.name is not None:
            city.name = data.name

        await db.commit()
        await db.refresh(city)

        zone = await db.get(Zone, zone_id)

        return {
            "city_id": city.city_id,
            "name": city.name,
            "zone_id": city.zone_id,
            "zone_name": zone.name if zone else None
        }


    @staticmethod
    async def delete_city(
            zone_id: int,
            city_id: int,
            db: AsyncSession,
    ):
        # коли будуть таблиці продукт розкоментити бо зараз будуть помилки при створенні таблиці Store тому поки що так
        # Перевірка наявності прив’язок
        # linked_stmt = select(StoreAddress).where(StoreAddress.city_id == city_id)
        # linked_result = await db.execute(linked_stmt)
        # linked_item = linked_result.scalars().first()

        # if linked_item:
        #     raise HTTPException(
        #         status_code=400,
        #         detail="Cannot delete city: there are linked records"
        #     )

        # Якщо залежностей немає, видаляємо місто
        stmt = select(City).where(City.city_id == city_id, City.zone_id == zone_id)
        result = await db.execute(stmt)
        city = result.scalar_one_or_none()

        if not city:
            _ERROR("CityNotFound")

        await db.delete(city)
        await db.commit()

        return {"status": True, "message": "City deleted successfully"}

    @staticmethod
    async def synchronize_zones(db: AsyncSession):
        new_post_zones = await fetch_new_post_zones()

        existing_zones_result = await db.execute(select(Zone))
        existing_zones = existing_zones_result.scalars().all()
        existing_names = {zone.name.lower(): zone for zone in existing_zones}

        for zone_data in new_post_zones:
            zone_name = zone_data["name"].strip()
            zone_name_lower = zone_name.lower()

            if zone_name_lower in existing_names:
                # Область вже є не потрібно змінювати
                continue

            new_zone = Zone(name=zone_name)
            db.add(new_zone)

        await db.commit()

        updated_zones_result = await db.execute(select(Zone))
        updated_zones = updated_zones_result.scalars().all()

        return updated_zones

    @staticmethod
    async def synchronize_cities(db: AsyncSession):
        new_post_cities = await fetch_new_post_cities()

        zones_result = await db.execute(select(Zone))
        zones = zones_result.scalars().all()
        zones_map = {zone.name.strip().lower(): zone for zone in zones}

        cities_result = await db.execute(select(City))
        cities = cities_result.scalars().all()
        cities_map = {(city.name.strip().lower(), city.zone_id): city for city in cities}

        added_count = 0

        for np_city in new_post_cities:
            city_name = np_city["name"].strip()
            zone_name = np_city["zone_name"].strip()
            zone_key = zone_name.lower()

            zone = zones_map.get(zone_key)

            if not zone:
                continue

            key = (city_name.lower(), zone.zone_id)
            if key in cities_map:
                continue

            new_city = City(
                name=city_name,
                zone_id=zone.zone_id,
                area_text=zone_name
            )
            db.add(new_city)
            added_count += 1

        await db.commit()

        return True

    @staticmethod
    async def synch_cities_in_zone(zone_id: int, db: AsyncSession) -> List[City]:
        zone_response = await ZoneService.get_zone_by_id(zone_id, db)
        if not zone_response["status"] or zone_response["data"] is None:
            _ERROR("ZoneNotFound")

        zone_name = zone_response["data"].name.strip()

        try:
            zone_ref = await get_zone_ref_by_name(zone_name)
        except ValueError:
            _ERROR("ZoneNotFound")

        try:
            np_cities = await fetch_np_cities_for_zone(zone_ref)
        except Exception:
            _ERROR("ExternalAPIError")

        existing_cities_result = await db.execute(select(City).where(City.zone_id == zone_id))
        existing_cities = existing_cities_result.scalars().all()
        existing_names = {city.name.strip().lower() for city in existing_cities}

        new_cities = []
        for city_data in np_cities:
            city_name = city_data["name"].strip()
            city_area = city_data.get("area_text", "").strip()

            if city_area.lower() == zone_name.lower():
                if city_name.lower() not in existing_names:
                    new_city = City(
                        name=city_name,
                        area_text=city_area,
                        zone_id=zone_id
                    )
                    db.add(new_city)
                    new_cities.append(new_city)
                    existing_names.add(city_name.lower())

        await db.commit()
        return new_cities