hs-data/apps/api/app/db/models.py

"""SQLAlchemy ORM models for the 拼团 (group-buy) funnel source tables.

Source of truth: docs/03 §11 (v3). The MVP funnel data is split into two tables
because their sync logic differs:

* ``ads_trd_group_funnel_daily`` — single-day, KEEPS FULL HISTORY (daily
  incremental insert of a new ``dt``). Backs ``period=day``.
* ``ads_trd_group_funnel_rolling`` — rolling 7d/30d, ONLY ONE ROW (daily
  overwrite, no history). Backs ``period=last_7d`` / ``last_30d``.

Fixed funnel order (5 steps): 启动 start -> 曝光 show -> 拼团详情 detail ->
下单 order -> 成功 paid.
"""

from __future__ import annotations

from datetime import datetime

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


class Base(DeclarativeBase):
    """Declarative base for all ORM models."""


class AdsTrdGroupFunnelDaily(Base):
    """Single-day group-buy funnel snapshot, full history (docs/03 §11.1).

    Daily incremental insert of a new ``dt``; any historical single day can be
    read back. Primary key: ``dt`` (varchar(8), yyyyMMdd).
    """

    __tablename__ = "ads_trd_group_funnel_daily"

    dt: Mapped[str] = mapped_column(String(8), primary_key=True)

    uv_start: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_show: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_detail: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_order: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_paid: Mapped[int | None] = mapped_column(BigInteger, nullable=True)

    etl_time: Mapped[datetime | None] = mapped_column(
        DateTime, nullable=True, server_default=func.now()
    )


class AdsTrdGroupFunnelRolling(Base):
    """Rolling 7d/30d group-buy funnel snapshot, single row (docs/03 §11.2).

    Daily overwrite of the latest as-of row — no history. ``dt`` is the as-of
    snapshot day. Only the current rolling 7d/30d windows are available.
    """

    __tablename__ = "ads_trd_group_funnel_rolling"

    dt: Mapped[str] = mapped_column(String(8), primary_key=True)

    uv_start_7d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_show_7d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_detail_7d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_order_7d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_paid_7d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)

    uv_start_30d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_show_30d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_detail_30d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_order_30d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    uv_paid_30d: Mapped[int | None] = mapped_column(BigInteger, nullable=True)

    etl_time: Mapped[datetime | None] = mapped_column(
        DateTime, nullable=True, server_default=func.now()
    )