hs-data/apps/api/scripts/seed.py

"""Seed realistic rows into the group-buy funnel tables for local dev.

Inserts:
* ~10 historical daily rows (distinct ``dt``) into ``ads_trd_group_funnel_daily``,
  ending yesterday, so the single-day date picker has history;
* one rolling row as-of yesterday into ``ads_trd_group_funnel_rolling`` for the
  7d/30d windows.

Each row carries a clean descending group-buy funnel
(start > show > detail > order > paid).

Run from apps/api (needs a live database; set USE_FAKE_DATA=false to read it):

    python -m scripts.seed
    # or
    python scripts/seed.py
"""

from __future__ import annotations

import asyncio
import sys
from datetime import date, datetime, timedelta
from pathlib import Path

# Allow running as a bare script.
sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from sqlalchemy import delete  # noqa: E402

from app.db.models import (  # noqa: E402
    AdsTrdGroupFunnelDaily,
    AdsTrdGroupFunnelRolling,
)
from app.db.session import get_engine, get_sessionmaker  # noqa: E402

# Number of historical daily rows to insert (ending yesterday).
HISTORY_DAYS = 10

# Top-step (start) UV for the most recent daily row and the rolling windows.
DAILY_TOP = 12000
ROLLING_TOP_7D = 74000
ROLLING_TOP_30D = 295000

# Cumulative keep-rate down the fixed funnel: start, show, detail, order, paid.
FUNNEL_KEEP = [1.0, 0.82, 0.51, 0.22, 0.18]
STEP_KEYS = ["start", "show", "detail", "order", "paid"]


def _funnel_for(top: int) -> list[int]:
    """Return descending UVs for the five steps given the top-step UV."""
    return [max(0, int(top * keep)) for keep in FUNNEL_KEEP]


async def seed() -> None:
    today = date.today()
    yesterday = today - timedelta(days=1)
    now = datetime.utcnow()

    sessionmaker = get_sessionmaker()
    async with sessionmaker() as session:
        # Idempotent: clear previous seed data in both tables.
        await session.execute(delete(AdsTrdGroupFunnelDaily))
        await session.execute(delete(AdsTrdGroupFunnelRolling))

        # Daily history rows, ending yesterday; older days a touch smaller.
        for day_offset in range(HISTORY_DAYS):
            snapshot_day = yesterday - timedelta(days=day_offset)
            dt = snapshot_day.strftime("%Y%m%d")
            scale = 1.0 - 0.04 * day_offset
            uvs = _funnel_for(int(DAILY_TOP * scale))
            session.add(
                AdsTrdGroupFunnelDaily(
                    dt=dt,
                    etl_time=now,
                    **{f"uv_{key}": uv for key, uv in zip(STEP_KEYS, uvs)},
                )
            )

        # Single rolling row, as-of yesterday.
        uvs_7d = _funnel_for(ROLLING_TOP_7D)
        uvs_30d = _funnel_for(ROLLING_TOP_30D)
        rolling_values: dict[str, int] = {}
        for key, uv in zip(STEP_KEYS, uvs_7d):
            rolling_values[f"uv_{key}_7d"] = uv
        for key, uv in zip(STEP_KEYS, uvs_30d):
            rolling_values[f"uv_{key}_30d"] = uv
        session.add(
            AdsTrdGroupFunnelRolling(
                dt=yesterday.strftime("%Y%m%d"),
                etl_time=now,
                **rolling_values,
            )
        )

        await session.commit()

    await get_engine().dispose()
    print(
        f"seed complete: inserted {HISTORY_DAYS} daily rows + 1 rolling row "
        f"(as-of {yesterday.strftime('%Y%m%d')})"
    )


def main() -> None:
    asyncio.run(seed())


if __name__ == "__main__":
    main()