111 lines
4.8 KiB
Python
111 lines
4.8 KiB
Python
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)
|