hs-data/apps/api/alembic/versions/0001_create_group_funnel_tables.py

"""create group-buy funnel tables (daily + rolling)

Revision ID: 0001
Revises:
Create Date: 2026-06-24

Two tables per docs/03 §11 (v3):
* ads_trd_group_funnel_daily   - single day, full history.
* ads_trd_group_funnel_rolling - rolling 7d/30d, single overwritten row.
"""
from __future__ import annotations

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = "0001"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "ads_trd_group_funnel_daily",
        sa.Column("dt", sa.String(length=8), primary_key=True),
        sa.Column("uv_start", sa.BigInteger(), nullable=True),
        sa.Column("uv_show", sa.BigInteger(), nullable=True),
        sa.Column("uv_detail", sa.BigInteger(), nullable=True),
        sa.Column("uv_order", sa.BigInteger(), nullable=True),
        sa.Column("uv_paid", sa.BigInteger(), nullable=True),
        sa.Column(
            "etl_time",
            sa.DateTime(),
            nullable=True,
            server_default=sa.func.now(),
        ),
    )

    op.create_table(
        "ads_trd_group_funnel_rolling",
        sa.Column("dt", sa.String(length=8), primary_key=True),
        sa.Column("uv_start_7d", sa.BigInteger(), nullable=True),
        sa.Column("uv_show_7d", sa.BigInteger(), nullable=True),
        sa.Column("uv_detail_7d", sa.BigInteger(), nullable=True),
        sa.Column("uv_order_7d", sa.BigInteger(), nullable=True),
        sa.Column("uv_paid_7d", sa.BigInteger(), nullable=True),
        sa.Column("uv_start_30d", sa.BigInteger(), nullable=True),
        sa.Column("uv_show_30d", sa.BigInteger(), nullable=True),
        sa.Column("uv_detail_30d", sa.BigInteger(), nullable=True),
        sa.Column("uv_order_30d", sa.BigInteger(), nullable=True),
        sa.Column("uv_paid_30d", sa.BigInteger(), nullable=True),
        sa.Column(
            "etl_time",
            sa.DateTime(),
            nullable=True,
            server_default=sa.func.now(),
        ),
    )


def downgrade() -> None:
    op.drop_table("ads_trd_group_funnel_rolling")
    op.drop_table("ads_trd_group_funnel_daily")