"""Hourly source-location rollup for monitoring analytics."""

from __future__ import annotations

import uuid
from datetime import datetime

from sqlalchemy import DateTime, Float, Index, Integer, String, UniqueConstraint
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func

from app.db.base_class import Base


class SourceLocationSnapshot(Base):
    __tablename__ = "source_location_snapshots"
    __table_args__ = (
        UniqueConstraint("bucket_time", "ip_hash", "user_hash", name="uq_source_location_bucket_identity"),
        Index("ix_source_location_bucket", "bucket_time"),
        Index("ix_source_location_country_bucket", "country_code", "bucket_time"),
        Index("ix_source_location_risk_bucket", "high_risk_count", "bucket_time"),
    )

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    bucket_time: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    ip_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    user_hash: Mapped[str] = mapped_column(String(64), nullable=False, default="")
    country: Mapped[str] = mapped_column(String(100), nullable=False, default="")
    country_code: Mapped[str] = mapped_column(String(16), nullable=False, default="")
    province: Mapped[str] = mapped_column(String(100), nullable=False, default="")
    region_code: Mapped[str] = mapped_column(String(24), nullable=False, default="")
    city: Mapped[str] = mapped_column(String(100), nullable=False, default="")
    isp: Mapped[str] = mapped_column(String(160), nullable=False, default="")
    location: Mapped[str] = mapped_column(String(320), nullable=False, default="")
    longitude: Mapped[float | None] = mapped_column(Float, nullable=True)
    latitude: Mapped[float | None] = mapped_column(Float, nullable=True)
    accuracy_level: Mapped[str] = mapped_column(String(16), nullable=False, default="unknown")
    allowed_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    denied_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    security_event_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    high_risk_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    auth_failure_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    first_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )