from __future__ import annotations

from datetime import datetime
from enum import Enum
from typing import Optional

from sqlalchemy import Boolean, DateTime, Enum as SAEnum, Float, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db import Base


class DealType(str, Enum):
    SALE = "sale"
    RENT = "rent"


class Source(str, Enum):
    PROPERTYFINDER = "propertyfinder"
    BAYUT = "bayut"


class ListingStatus(str, Enum):
    ACTIVE = "active"
    REMOVED = "removed"


class Employee(Base):
    __tablename__ = "employees"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(200))
    portal_user_id: Mapped[Optional[str]] = mapped_column(String(100), unique=True, index=True, nullable=True)
    tg_chat_id: Mapped[Optional[str]] = mapped_column(String(64), unique=True, nullable=True)
    tg_username: Mapped[Optional[str]] = mapped_column(String(200), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    projects: Mapped[list["Project"]] = relationship(back_populates="owner")


class Project(Base):
    """Наш проект — квартира, которую агентство рекламирует."""

    __tablename__ = "projects"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(300))
    deal_type: Mapped[DealType] = mapped_column(SAEnum(DealType))
    our_price: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    # Опциональные параметры — используются для подсказок похожих объявлений
    dld_permit: Mapped[Optional[str]] = mapped_column(String(100), index=True, nullable=True)
    building: Mapped[Optional[str]] = mapped_column(String(300), nullable=True)
    bedrooms: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    size_sqft: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    our_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    owner_id: Mapped[int] = mapped_column(ForeignKey("employees.id"))
    owner: Mapped[Employee] = relationship(back_populates="projects")

    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    last_checked_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    listings: Mapped[list["CompetitorListing"]] = relationship(
        back_populates="project", cascade="all, delete-orphan"
    )


class CompetitorListing(Base):
    """Объявление конкурента, найденное на PF/Bayut по DLD permit нашего проекта."""

    __tablename__ = "competitor_listings"
    __table_args__ = (UniqueConstraint("project_id", "source", "external_id", name="uq_listing"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    project_id: Mapped[int] = mapped_column(ForeignKey("projects.id"))
    project: Mapped[Project] = relationship(back_populates="listings")

    source: Mapped[Source] = mapped_column(SAEnum(Source))
    external_id: Mapped[str] = mapped_column(String(100))  # ID на стороне PF/Bayut
    url: Mapped[str] = mapped_column(Text)
    title: Mapped[Optional[str]] = mapped_column(String(500), nullable=True)
    agent_name: Mapped[Optional[str]] = mapped_column(String(300), nullable=True)
    agency_name: Mapped[Optional[str]] = mapped_column(String(300), nullable=True)
    permit_number: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    auto_discovered: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    permit_missing_checks: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

    current_price: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    currency: Mapped[Optional[str]] = mapped_column(String(10), nullable=True, default="AED")
    status: Mapped[ListingStatus] = mapped_column(SAEnum(ListingStatus), default=ListingStatus.ACTIVE)

    first_seen_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    price_history: Mapped[list["PriceHistory"]] = relationship(
        back_populates="listing", cascade="all, delete-orphan", order_by="PriceHistory.recorded_at.desc()"
    )


class PriceHistory(Base):
    __tablename__ = "price_history"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    listing_id: Mapped[int] = mapped_column(ForeignKey("competitor_listings.id"))
    listing: Mapped[CompetitorListing] = relationship(back_populates="price_history")

    price: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    recorded_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)