from datetime import date, datetime, timezone

from sqlalchemy import func, or_
from sqlalchemy.orm import Query, Session, joinedload

from ..models import Event, MapObject


def _effective_date_column():
    return func.coalesce(Event.event_date, Event.ingested_at, MapObject.created_at)


def build_map_objects_query(
    db: Session,
    *,
    date_from: datetime | None = None,
    date_to: datetime | None = None,
    region: str | None = None,
    topic: str | None = None,
    source_type: str | None = None,
    search: str | None = None,
    event_id: int | None = None,
) -> Query:
    query = (
        db.query(MapObject)
        .outerjoin(Event, MapObject.event_id == Event.id)
        .options(joinedload(MapObject.event))
    )

    if event_id is not None:
        query = query.filter(MapObject.event_id == event_id)

    effective_date = _effective_date_column()

    if date_from is not None:
        query = query.filter(effective_date >= date_from)
    if date_to is not None:
        query = query.filter(effective_date <= date_to)

    if region:
        query = query.filter(Event.region == region)
    if topic:
        query = query.filter(Event.topic == topic)
    if source_type:
        query = query.filter(Event.source_type == source_type)

    if search:
        pattern = f"%{search}%"
        query = query.filter(
            or_(
                MapObject.name.ilike(pattern),
                MapObject.description.ilike(pattern),
                Event.title.ilike(pattern),
                Event.locality.ilike(pattern),
                Event.description.ilike(pattern),
            )
        )

    return query.order_by(effective_date.desc(), MapObject.id.desc())


def map_object_to_dict(obj: MapObject) -> dict:
    event = obj.event
    return {
        "id": obj.id,
        "name": obj.name,
        "description": obj.description,
        "type": obj.type,
        "latitude": obj.latitude,
        "longitude": obj.longitude,
        "event_id": obj.event_id,
        "created_at": obj.created_at,
        "event_date": event.event_date if event else obj.created_at,
        "locality": event.locality if event else None,
        "region": event.region if event else None,
        "topic": event.topic if event else None,
        "source_type": event.source_type if event else None,
        "source_url": event.source_url if event else None,
        "title": event.title if event else obj.name,
    }


def _date_to_iso(value: date | datetime | None) -> str | None:
    if value is None:
        return None
    if isinstance(value, datetime):
        return value.date().isoformat()
    return value.isoformat()


def get_map_filters(db: Session) -> dict:
    effective_date = _effective_date_column()

    date_bounds = (
        db.query(
            func.min(effective_date).label("min_date"),
            func.max(effective_date).label("max_date"),
        )
        .select_from(MapObject)
        .outerjoin(Event, MapObject.event_id == Event.id)
        .first()
    )

    regions = [
        row[0]
        for row in db.query(Event.region)
        .filter(Event.region.isnot(None), Event.region != "")
        .distinct()
        .order_by(Event.region)
        .all()
    ]

    topics = [
        row[0]
        for row in db.query(Event.topic)
        .filter(Event.topic.isnot(None), Event.topic != "")
        .distinct()
        .order_by(Event.topic)
        .all()
    ]

    source_types = [
        row[0]
        for row in db.query(Event.source_type)
        .filter(Event.source_type.isnot(None), Event.source_type != "")
        .distinct()
        .order_by(Event.source_type)
        .all()
    ]

    available_dates = [
        _date_to_iso(row[0])
        for row in (
            db.query(func.date(effective_date))
            .select_from(MapObject)
            .outerjoin(Event, MapObject.event_id == Event.id)
            .filter(effective_date.isnot(None))
            .distinct()
            .order_by(func.date(effective_date))
            .all()
        )
        if row[0] is not None
    ]

    min_date = date_bounds.min_date if date_bounds else None
    max_date = date_bounds.max_date if date_bounds else None

    return {
        "regions": regions,
        "topics": topics,
        "source_types": source_types,
        "available_dates": available_dates,
        "date_bounds": {
            "min": min_date.isoformat() if min_date else None,
            "max": max_date.isoformat() if max_date else None,
        },
    }


def utc_day_start(dt: datetime) -> datetime:
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.replace(hour=0, minute=0, second=0, microsecond=0)


def utc_day_end(dt: datetime) -> datetime:
    start = utc_day_start(dt)
    return start.replace(hour=23, minute=59, second=59, microsecond=999999)