acquire-lead/db/models.py

"""SQLAlchemy 模型：字段名对齐当前数据库实际列名。"""

from datetime import datetime

from sqlalchemy import BigInteger, DateTime, Integer, SmallInteger, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from config.settings import TABLE_PREFIX


class Base(DeclarativeBase):
    pass


def _now() -> datetime:
    return datetime.now()


class Video(Base):
    __tablename__ = f"{TABLE_PREFIX}videos"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    video_id: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    keyword: Mapped[str | None] = mapped_column(String(255))
    title: Mapped[str | None] = mapped_column(String(255))
    author_nickname: Mapped[str | None] = mapped_column(String(128))
    author_douyin_id: Mapped[str | None] = mapped_column(String(255))
    author_link: Mapped[str | None] = mapped_column(String(512))
    video_url: Mapped[str | None] = mapped_column(String(512))
    summary: Mapped[str | None] = mapped_column(String(512))
    create_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now)
    update_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now, onupdate=_now)


class Lead(Base):
    __tablename__ = f"{TABLE_PREFIX}leads"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    douyin_id: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    nick_name: Mapped[str | None] = mapped_column(String(255))
    user_link: Mapped[str | None] = mapped_column(String(512))
    status: Mapped[int | None] = mapped_column(SmallInteger, default=1)
    ai_score: Mapped[int | None] = mapped_column(Integer)
    ai_reason: Mapped[str | None] = mapped_column(String(255))
    score_create: Mapped[datetime | None] = mapped_column(DateTime)
    create_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now)
    update_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now, onupdate=_now)


class Comment(Base):
    __tablename__ = f"{TABLE_PREFIX}comments"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    # 关联 videos.id（表主键），不是平台 video_id 字符串
    video_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    lead_id: Mapped[int | None] = mapped_column(BigInteger, index=True)
    douyin_id: Mapped[str | None] = mapped_column(String(255), index=True)
    nick_name: Mapped[str | None] = mapped_column(String(255))
    content: Mapped[str | None] = mapped_column(String(1024))
    comment_time: Mapped[str | None] = mapped_column(String(64))
    # 1=一级评论，0=回复
    type: Mapped[int | None] = mapped_column(SmallInteger, default=1)
    create_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now)


class Conversation(Base):
    __tablename__ = f"{TABLE_PREFIX}conversations"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    lead_id: Mapped[int] = mapped_column(BigInteger, unique=True, nullable=False)
    douyin_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    nickname: Mapped[str | None] = mapped_column(String(128))
    last_message_time: Mapped[datetime | None] = mapped_column(DateTime)
    create_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now)
    update_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now, onupdate=_now)


class Message(Base):
    __tablename__ = f"{TABLE_PREFIX}messages"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    conversation_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    lead_id: Mapped[int | None] = mapped_column(BigInteger, index=True)
    douyin_id: Mapped[str | None] = mapped_column(String(255))
    direction: Mapped[str | None] = mapped_column(String(16))
    content: Mapped[str | None] = mapped_column(String(1024))
    create_at: Mapped[datetime | None] = mapped_column(DateTime, default=_now)