shaguabijia-app-server/app/models/wallet.py

"""钱包相关表:金币账户 + 金币流水。

设计要点:
  - 余额快照(coin_account)用于读取,流水账本(coin_transaction)用于对账,
    每次余额变动都写一笔流水并记 balance_after,出问题能逐笔回溯。
  - 金额一律存整数:金币是个数,现金存"分"(cash_balance_cents),不用浮点。
  - cash_balance_cents 本步先留列、恒为 0,金币兑现金(第 2 步)再启用。
"""
from __future__ import annotations

from datetime import datetime

from sqlalchemy import DateTime, ForeignKey, Integer, String, func
from sqlalchemy.orm import Mapped, mapped_column

from app.db.base import Base


class CoinAccount(Base):
    __tablename__ = "coin_account"

    # 一个用户一行,user_id 既是主键也是外键
    user_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("user.id"), primary_key=True
    )
    coin_balance: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    cash_balance_cents: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    # 累计赚取的金币(只增不减),用于"历史总收益"类展示
    total_coin_earned: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )

    def __repr__(self) -> str:  # pragma: no cover
        return f"<CoinAccount user_id={self.user_id} coin={self.coin_balance}>"


class CoinTransaction(Base):
    __tablename__ = "coin_transaction"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("user.id"), index=True, nullable=False
    )
    # 正数=入账(赚),负数=出账(花/兑换)
    amount: Mapped[int] = mapped_column(Integer, nullable=False)
    # 本笔变动后的金币余额,对账用
    balance_after: Mapped[int] = mapped_column(Integer, nullable=False)
    # 业务类型:signin / task_<key> / exchange_out / ...
    biz_type: Mapped[str] = mapped_column(String(32), nullable=False)
    # 关联业务 id(签到日期、任务 key 等),可空
    ref_id: Mapped[str | None] = mapped_column(String(64), nullable=True)
    remark: Mapped[str | None] = mapped_column(String(128), nullable=True)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), index=True, nullable=False
    )

    def __repr__(self) -> str:  # pragma: no cover
        return f"<CoinTransaction id={self.id} user_id={self.user_id} amount={self.amount}>"


class WithdrawOrder(Base):
    """提现单(现金 → 微信零钱)。状态机:pending → success / failed。

    扣现金 + 写 cash_transaction(withdraw) 与建本单在同一事务;失败/取消时退回现金
    并写 cash_transaction(withdraw_refund)。wechat_state 存微信侧原始状态(WAIT_USER_CONFIRM /
    SUCCESS / FAIL / CANCELLED ...),status 是我们归一化后的三态。
    """

    __tablename__ = "withdraw_order"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("user.id"), index=True, nullable=False
    )
    # 商户单号(我们生成,微信查单的 out_bill_no),唯一
    out_bill_no: Mapped[str] = mapped_column(String(64), unique=True, index=True, nullable=False)
    amount_cents: Mapped[int] = mapped_column(Integer, nullable=False)
    # 归一化状态:pending / success / failed
    status: Mapped[str] = mapped_column(String(16), nullable=False, default="pending")
    # 微信侧原始状态
    wechat_state: Mapped[str | None] = mapped_column(String(32), nullable=True)
    # 微信转账单号
    transfer_bill_no: Mapped[str | None] = mapped_column(String(64), nullable=True)
    # 待用户确认时返回给 App 拉起确认页的 package_info
    package_info: Mapped[str | None] = mapped_column(String(512), nullable=True)
    fail_reason: Mapped[str | None] = mapped_column(String(256), nullable=True)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), index=True, nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
    )

    def __repr__(self) -> str:  # pragma: no cover
        return f"<WithdrawOrder id={self.id} user_id={self.user_id} cents={self.amount_cents} {self.status}>"


class CashTransaction(Base):
    """现金流水(单位:分)。金币兑现金、提现都记在这里。"""

    __tablename__ = "cash_transaction"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("user.id"), index=True, nullable=False
    )
    # 正数=入账(兑入),负数=出账(提现)
    amount_cents: Mapped[int] = mapped_column(Integer, nullable=False)
    balance_after_cents: Mapped[int] = mapped_column(Integer, nullable=False)
    # 业务类型:exchange_in / withdraw
    biz_type: Mapped[str] = mapped_column(String(32), nullable=False)
    ref_id: Mapped[str | None] = mapped_column(String(64), nullable=True)
    remark: Mapped[str | None] = mapped_column(String(128), nullable=True)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), index=True, nullable=False
    )

    def __repr__(self) -> str:  # pragma: no cover
        return f"<CashTransaction id={self.id} user_id={self.user_id} cents={self.amount_cents}>"