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

from datetime import UTC, date, datetime

from sqlalchemy import (
    JSON,
    BigInteger,
    Column,
    Date,
    ForeignKey,
    Index,
    String,
    Table,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

from app.infrastructure.mysql.types import UTCDateTime


def utc_now() -> datetime:
    return datetime.now(UTC)


class CoreBase(DeclarativeBase):
    pass


admin_user_roles = Table(
    "admin_user_roles",
    CoreBase.metadata,
    Column("admin_user_id", String(26), ForeignKey("admin_users.id"), primary_key=True),
    Column("role_id", String(26), ForeignKey("roles.id"), primary_key=True),
)


role_permissions = Table(
    "role_permissions",
    CoreBase.metadata,
    Column("role_id", String(26), ForeignKey("roles.id"), primary_key=True),
    Column(
        "permission_id",
        String(26),
        ForeignKey("permissions.id"),
        primary_key=True,
    ),
)


class H5UserRecord(CoreBase):
    __tablename__ = "h5_users"
    __table_args__ = (
        Index("ix_h5_users_status_created", "status", "created_at"),
        Index("ix_h5_users_source", "source_code"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    mobile: Mapped[str] = mapped_column(String(32), unique=True, nullable=False)
    mobile_masked: Mapped[str] = mapped_column(String(32), nullable=False)
    display_name: Mapped[str] = mapped_column(String(64), nullable=False)
    region_code: Mapped[str | None] = mapped_column(String(12))
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="ACTIVE")
    source_code: Mapped[str] = mapped_column(String(64), nullable=False, default="DIRECT")
    last_login_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False, default=utc_now)
    updated_at: Mapped[datetime] = mapped_column(
        UTCDateTime(), nullable=False, default=utc_now, onupdate=utc_now
    )
    version: Mapped[int] = mapped_column(nullable=False, default=1)


class PermissionRecord(CoreBase):
    __tablename__ = "permissions"

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    code: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    resource: Mapped[str] = mapped_column(String(64), nullable=False)
    action: Mapped[str] = mapped_column(String(32), nullable=False)


class RoleRecord(CoreBase):
    __tablename__ = "roles"

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    code: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    data_scope: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="ACTIVE")
    permissions: Mapped[list[PermissionRecord]] = relationship(
        secondary=role_permissions,
        lazy="selectin",
    )


class AdminUserRecord(CoreBase):
    __tablename__ = "admin_users"

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    username: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
    display_name: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="ACTIVE")
    roles: Mapped[list[RoleRecord]] = relationship(
        secondary=admin_user_roles,
        lazy="selectin",
    )


class AuthSessionRecord(CoreBase):
    __tablename__ = "auth_sessions"
    __table_args__ = (
        Index(
            "ix_auth_sessions_subject",
            "subject_type",
            "subject_id",
            "revoked_at",
        ),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    subject_type: Mapped[str] = mapped_column(String(16), nullable=False)
    subject_id: Mapped[str] = mapped_column(String(26), nullable=False)
    refresh_jti_hash: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    expires_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)
    revoked_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False, default=utc_now)


class SalespersonRecord(CoreBase):
    __tablename__ = "salespersons"

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    admin_user_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("admin_users.id"),
        unique=True,
        nullable=False,
    )
    salesperson_code: Mapped[str] = mapped_column(
        String(32),
        unique=True,
        nullable=False,
    )
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        UTCDateTime(),
        nullable=False,
        default=utc_now,
    )


class PromotionCodeRecord(CoreBase):
    __tablename__ = "promotion_codes"
    __table_args__ = (Index("ix_promotion_codes_salesperson", "salesperson_id", "status"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    code: Mapped[str] = mapped_column(String(32), unique=True, nullable=False)
    salesperson_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("salespersons.id"),
        nullable=False,
    )
    channel: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        UTCDateTime(),
        nullable=False,
        default=utc_now,
    )


class LeadAttributionRecord(CoreBase):
    __tablename__ = "lead_attributions"
    __table_args__ = (Index("ix_lead_attributions_salesperson", "salesperson_id", "last_touch_at"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    h5_user_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("h5_users.id"),
        unique=True,
        nullable=False,
    )
    salesperson_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("salespersons.id"),
        nullable=False,
    )
    promotion_code_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("promotion_codes.id"),
        nullable=False,
    )
    source_code: Mapped[str] = mapped_column(String(32), nullable=False)
    visit_count: Mapped[int] = mapped_column(nullable=False, default=1)
    first_touch_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)
    last_touch_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)


class OrderAttributionRecord(CoreBase):
    __tablename__ = "order_attributions"
    __table_args__ = (
        Index("ix_order_attributions_salesperson", "salesperson_id", "attributed_at"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    order_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("enrollment_orders.id"),
        unique=True,
        nullable=False,
    )
    h5_user_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("h5_users.id"),
        nullable=False,
    )
    salesperson_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("salespersons.id"),
        nullable=False,
    )
    promotion_code_id: Mapped[str] = mapped_column(
        String(26),
        ForeignKey("promotion_codes.id"),
        nullable=False,
    )
    source_code: Mapped[str] = mapped_column(String(32), nullable=False)
    amount_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    attributed_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)


class ProductRecord(CoreBase):
    __tablename__ = "products"

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    product_code: Mapped[str] = mapped_column(String(32), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    category: Mapped[str] = mapped_column(String(32), nullable=False)
    summary: Mapped[str] = mapped_column(String(500), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)


class ProductVersionRecord(CoreBase):
    __tablename__ = "product_versions"
    __table_args__ = (
        UniqueConstraint("product_id", "version_no", name="uq_product_version"),
        Index("ix_product_versions_availability", "status", "effective_from", "effective_to"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    product_id: Mapped[str] = mapped_column(String(26), ForeignKey("products.id"), nullable=False)
    version_no: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    effective_from: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)
    effective_to: Mapped[datetime | None] = mapped_column(UTCDateTime())
    rule_version: Mapped[str] = mapped_column(String(32), nullable=False, default="eligibility-v1")
    rate_version: Mapped[str] = mapped_column(String(32), nullable=False, default="rate-v1")
    terms_summary: Mapped[str] = mapped_column(Text, nullable=False, default="")
    plans: Mapped[list["PlanRecord"]] = relationship(
        back_populates="product_version",
        cascade="all, delete-orphan",
        lazy="selectin",
    )


class PlanRecord(CoreBase):
    __tablename__ = "plans"
    __table_args__ = (
        UniqueConstraint("product_version_id", "plan_code", name="uq_plan_version_code"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    product_version_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("product_versions.id"), nullable=False
    )
    plan_code: Mapped[str] = mapped_column(String(32), nullable=False)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    summary: Mapped[str] = mapped_column(String(500), nullable=False, default="")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="ACTIVE")
    premium_cents: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    coverage_amount_cents: Mapped[int] = mapped_column(
        BigInteger, nullable=False, default=0
    )
    min_age: Mapped[int] = mapped_column(nullable=False, default=0)
    max_age: Mapped[int] = mapped_column(nullable=False, default=100)
    product_version: Mapped[ProductVersionRecord] = relationship(back_populates="plans")


class ProductChangeLogRecord(CoreBase):
    __tablename__ = "product_change_logs"
    __table_args__ = (
        Index("ix_product_change_logs_product_created", "product_id", "created_at"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    product_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("products.id"), nullable=False
    )
    version_id: Mapped[str | None] = mapped_column(String(26))
    action: Mapped[str] = mapped_column(String(32), nullable=False)
    actor_id: Mapped[str] = mapped_column(String(26), nullable=False)
    actor_name: Mapped[str] = mapped_column(String(64), nullable=False)
    detail_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)


class EligibilityRuleRecord(CoreBase):
    __tablename__ = "eligibility_rules"
    __table_args__ = (
        UniqueConstraint("product_version_id", "rule_code", name="uq_eligibility_rule"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    product_version_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("product_versions.id"), nullable=False
    )
    rule_code: Mapped[str] = mapped_column(String(64), nullable=False)
    rule_type: Mapped[str] = mapped_column(String(32), nullable=False)
    operator: Mapped[str] = mapped_column(String(16), nullable=False)
    parameters_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    failure_code: Mapped[str] = mapped_column(String(64), nullable=False)
    failure_message: Mapped[str] = mapped_column(String(255), nullable=False)


class RateTableRecord(CoreBase):
    __tablename__ = "rate_tables"
    __table_args__ = (
        UniqueConstraint("product_version_id", "rate_code", name="uq_rate_code"),
        Index(
            "ix_rate_lookup",
            "product_version_id",
            "plan_id",
            "region_group",
            "min_age",
            "max_age",
            "effective_from",
        ),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    product_version_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("product_versions.id"), nullable=False
    )
    plan_id: Mapped[str] = mapped_column(String(26), ForeignKey("plans.id"), nullable=False)
    rate_code: Mapped[str] = mapped_column(String(64), nullable=False)
    min_age: Mapped[int] = mapped_column(nullable=False)
    max_age: Mapped[int] = mapped_column(nullable=False)
    region_group: Mapped[str] = mapped_column(String(32), nullable=False)
    occupation_group: Mapped[str] = mapped_column(String(32), nullable=False)
    premium_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False, default="CNY")
    effective_from: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)
    effective_to: Mapped[datetime | None] = mapped_column(UTCDateTime())


class QuoteRecord(CoreBase):
    __tablename__ = "quotes"
    __table_args__ = (Index("ix_quotes_user_status", "h5_user_id", "status", "expires_at"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    h5_user_id: Mapped[str] = mapped_column(String(26), ForeignKey("h5_users.id"), nullable=False)
    product_id: Mapped[str] = mapped_column(String(26), ForeignKey("products.id"), nullable=False)
    product_version_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("product_versions.id"), nullable=False
    )
    plan_id: Mapped[str] = mapped_column(String(26), ForeignKey("plans.id"), nullable=False)
    insured_age: Mapped[int] = mapped_column(nullable=False)
    insured_region_code: Mapped[str] = mapped_column(String(12), nullable=False)
    occupation_code: Mapped[str] = mapped_column(String(32), nullable=False)
    relationship: Mapped[str] = mapped_column(String(32), nullable=False)
    premium_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False)
    rule_version: Mapped[str] = mapped_column(String(32), nullable=False)
    rate_version: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    expires_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False, default=utc_now)


class EnrollmentDraftRecord(CoreBase):
    __tablename__ = "enrollment_drafts"
    __table_args__ = (Index("ix_drafts_user_status", "h5_user_id", "status", "created_at"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    h5_user_id: Mapped[str] = mapped_column(String(26), ForeignKey("h5_users.id"), nullable=False)
    quote_id: Mapped[str] = mapped_column(String(26), ForeignKey("quotes.id"), nullable=False)
    applicant_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    insured_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    contact_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    expires_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False, default=utc_now)


class UserConfirmationRecord(CoreBase):
    __tablename__ = "user_confirmations"
    __table_args__ = (Index("ix_confirmations_subject", "draft_id", "status"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    h5_user_id: Mapped[str] = mapped_column(String(26), ForeignKey("h5_users.id"), nullable=False)
    draft_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("enrollment_drafts.id"), nullable=False
    )
    token_hash: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    expires_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False, default=utc_now)


class EnrollmentOrderRecord(CoreBase):
    __tablename__ = "enrollment_orders"
    __table_args__ = (
        UniqueConstraint(
            "h5_user_id",
            "idempotency_key",
            name="uq_orders_user_idempotency",
        ),
        Index("ix_orders_user_status", "h5_user_id", "status", "created_at"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    order_no: Mapped[str] = mapped_column(String(40), unique=True, nullable=False)
    h5_user_id: Mapped[str] = mapped_column(String(26), ForeignKey("h5_users.id"), nullable=False)
    quote_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("quotes.id"), unique=True, nullable=False
    )
    draft_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("enrollment_drafts.id"), nullable=False
    )
    confirmation_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("user_confirmations.id"), unique=True, nullable=False
    )
    idempotency_key: Mapped[str] = mapped_column(String(64), nullable=False)
    product_id: Mapped[str] = mapped_column(String(26), nullable=False)
    product_version_id: Mapped[str] = mapped_column(String(26), nullable=False)
    plan_id: Mapped[str] = mapped_column(String(26), nullable=False)
    applicant_snapshot_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    insured_snapshot_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    amount_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False, default=utc_now)


class PaymentTransactionRecord(CoreBase):
    __tablename__ = "payment_transactions"
    __table_args__ = (
        UniqueConstraint(
            "order_id",
            "idempotency_key",
            name="uq_payments_order_idempotency",
        ),
        Index("ix_payments_order_status", "order_id", "status"),
    )

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    payment_no: Mapped[str] = mapped_column(String(40), unique=True, nullable=False)
    order_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("enrollment_orders.id"), nullable=False
    )
    h5_user_id: Mapped[str] = mapped_column(String(26), nullable=False)
    transaction_type: Mapped[str] = mapped_column(String(16), nullable=False)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    idempotency_key: Mapped[str] = mapped_column(String(64), nullable=False)
    amount_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    provider_transaction_no: Mapped[str | None] = mapped_column(String(64), unique=True)
    succeeded_at: Mapped[datetime | None] = mapped_column(UTCDateTime())
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False, default=utc_now)


class PolicyRecord(CoreBase):
    __tablename__ = "policies"
    __table_args__ = (Index("ix_policies_user_status", "h5_user_id", "status", "coverage_end"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    policy_no: Mapped[str] = mapped_column(String(40), unique=True, nullable=False)
    order_id: Mapped[str] = mapped_column(
        String(26), ForeignKey("enrollment_orders.id"), unique=True, nullable=False
    )
    h5_user_id: Mapped[str] = mapped_column(String(26), nullable=False)
    product_id: Mapped[str] = mapped_column(String(26), nullable=False)
    product_version_id: Mapped[str] = mapped_column(String(26), nullable=False)
    plan_id: Mapped[str] = mapped_column(String(26), nullable=False)
    premium_cents: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(3), nullable=False)
    coverage_start: Mapped[date] = mapped_column(Date, nullable=False)
    coverage_end: Mapped[date] = mapped_column(Date, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    issued_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)


class OutboxEventRecord(CoreBase):
    __tablename__ = "outbox_events"
    __table_args__ = (Index("ix_outbox_dispatch", "status", "created_at"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    aggregate_type: Mapped[str] = mapped_column(String(32), nullable=False)
    aggregate_id: Mapped[str] = mapped_column(String(26), nullable=False)
    event_type: Mapped[str] = mapped_column(String(128), nullable=False)
    payload_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)


class AsyncTaskRecord(CoreBase):
    __tablename__ = "async_tasks"
    __table_args__ = (Index("ix_async_tasks_claim", "status", "created_at"),)

    id: Mapped[str] = mapped_column(String(26), primary_key=True)
    task_type: Mapped[str] = mapped_column(String(128), nullable=False)
    business_key: Mapped[str] = mapped_column(String(128), nullable=False)
    idempotency_key: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
    payload_json: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    attempt_count: Mapped[int] = mapped_column(nullable=False, default=0)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(), nullable=False)