harness_zbt/backend/app/infrastructure/mysql/analytics_models.py

from datetime import date, datetime

from sqlalchemy import BigInteger, Date, Index, Numeric, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from app.infrastructure.mysql.types import UTCDateTime


class AnalyticsBase(DeclarativeBase):
    pass


class DimDateRecord(AnalyticsBase):
    __tablename__ = "dim_date"

    date_key: Mapped[int] = mapped_column(primary_key=True)
    full_date: Mapped[date] = mapped_column(Date, unique=True, nullable=False)
    year: Mapped[int] = mapped_column(nullable=False)
    month: Mapped[int] = mapped_column(nullable=False)
    day: Mapped[int] = mapped_column(nullable=False)


class DimProductRecord(AnalyticsBase):
    __tablename__ = "dim_product"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[str] = mapped_column(String(26), nullable=False)
    product_version_id: Mapped[str] = mapped_column(String(26), unique=True, nullable=False)
    product_code: Mapped[str] = mapped_column(String(32), nullable=False)
    product_name: Mapped[str] = mapped_column(String(128), nullable=False)
    version_no: Mapped[str] = mapped_column(String(32), nullable=False)


class DimSalespersonRecord(AnalyticsBase):
    __tablename__ = "dim_salesperson"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    salesperson_id: Mapped[str] = mapped_column(String(26), unique=True, nullable=False)
    salesperson_code: Mapped[str] = mapped_column(String(32), nullable=False)
    display_name: Mapped[str] = mapped_column(String(64), nullable=False)
    organization_code: Mapped[str] = mapped_column(String(64), nullable=False)


class FactOrderRecord(AnalyticsBase):
    __tablename__ = "fact_orders"
    __table_args__ = (Index("ix_fact_orders_date_product", "date_key", "product_key"),)

    order_id: Mapped[str] = mapped_column(String(26), primary_key=True)
    date_key: Mapped[int] = mapped_column(nullable=False)
    product_key: Mapped[int] = mapped_column(BigInteger, nullable=False)
    salesperson_key: Mapped[int | None] = mapped_column(BigInteger)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    premium_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)


class FactPolicyRecord(AnalyticsBase):
    __tablename__ = "fact_policies"

    policy_id: Mapped[str] = mapped_column(String(26), primary_key=True)
    order_id: Mapped[str] = mapped_column(String(26), unique=True, nullable=False)
    date_key: Mapped[int] = mapped_column(nullable=False)
    product_key: Mapped[int] = mapped_column(BigInteger, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    premium_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)


class MetricDefinitionRecord(AnalyticsBase):
    __tablename__ = "metric_definitions"

    metric_code: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    description: Mapped[str] = mapped_column(String(500), nullable=False)
    unit: Mapped[str] = mapped_column(String(16), nullable=False)
    expression: Mapped[str] = mapped_column(String(1000), nullable=False)
    warning_threshold: Mapped[float | None] = mapped_column(Numeric(18, 4))


class AnalyticsSyncOffsetRecord(AnalyticsBase):
    __tablename__ = "analytics_sync_offsets"

    source_name: Mapped[str] = mapped_column(String(64), primary_key=True)
    last_event_id: Mapped[str | None] = mapped_column(String(26))
    last_occurred_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    updated_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)