models_sql.py

# models_sql.py
# ==========================================
# 🗄️ SQL 数据库模型定义
# ==========================================
# 🚀 P1性能优化：添加复合索引加速查询
# ==========================================

from sqlalchemy import Column, Integer, String, DateTime, Boolean, Index, func
from sqlalchemy.orm import declarative_base
import datetime

Base = declarative_base()

class Wallet(Base):
    """用户钱包表"""
    __tablename__ = "wallets"
    
    account = Column(String, primary_key=True, index=True)
    balance = Column(Integer, default=0, nullable=False)         # 用于消费的余额 (充值获得)
    earn_balance = Column(Integer, default=0, nullable=False)    # 创作者销售收益余额 (别人购买获得)
    tip_balance = Column(Integer, default=0, nullable=False)     # 【新增】创作者打赏收益余额 (粉丝赞助获得)
    task_balance = Column(Integer, default=0, nullable=False)    # 【新增】任务相关余额
    frozen_balance = Column(Integer, default=0, nullable=False)  # 提现审核冻结中的余额
    
    # 乐观锁版本号，防止并发扣款被击穿
    version = Column(Integer, default=1)

class Ownership(Base):
    """资源所有权表"""
    __tablename__ = "ownerships"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    account = Column(String, index=True)
    item_id = Column(String, index=True)
    purchased_at = Column(DateTime, default=datetime.datetime.utcnow)
    # 🔄 P7后悔模式增强
    price_paid = Column(Integer, default=0)       # 购买时支付的价格
    is_refunded = Column(Boolean, default=False)  # 是否已退款
    refunded_at = Column(DateTime, nullable=True) # 退款时间
    
    # 🚀 P1性能优化：复合索引
    __table_args__ = (
        Index('ix_ownerships_account_item', 'account', 'item_id'),
        Index('ix_ownerships_account_refunded', 'account', 'is_refunded'),
    )

class Refund(Base):
    """🔄 P7后悔模式：退款记录表"""
    __tablename__ = "refunds"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    account = Column(String, index=True)          # 退款用户
    item_id = Column(String, index=True)          # 退款商品
    amount = Column(Integer)                       # 退款金额
    refunded_at = Column(DateTime, default=datetime.datetime.utcnow)
    ban_until = Column(DateTime)                   # 禁止购买截止时间（30天后）

class Transaction(Base):
    """交易流水表"""
    __tablename__ = "transactions"
    
    tx_id = Column(String, primary_key=True)
    account = Column(String, index=True)
    tx_type = Column(String)                     # 枚举: RECHARGE, PURCHASE, EARN, TIP_SEND, TIP_RECEIVE, WITHDRAW_APPLY, REFUND
    amount = Column(Integer)
    related_account = Column(String, nullable=True)
    item_id = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, index=True)  # 🚀 P1: 添加时间索引
    prev_hash = Column(String)                   # 上一笔订单的哈希
    tx_hash = Column(String)                     # 本笔订单的哈希 (防篡改)
    
    # 💰 提现相关字段 (仅 WITHDRAW_APPLY 类型交易使用)
    alipay_account = Column(String, nullable=True)      # 收款支付宝账号
    real_name = Column(String, nullable=True)            # 收款人姓名
    withdraw_status = Column(String, nullable=True)      # pending / completed
    payment_order_id = Column(String, nullable=True)     # 管理员填写的打款订单号
    net_amount = Column(Integer, nullable=True)          # 实际到账金额（用于解冻时准确扣减）
    
    # 交易详情增强字段
    description = Column(String, nullable=True)          # 交易自描述文本
    item_title = Column(String, nullable=True)           # 资源/任务标题
    item_type = Column(String, nullable=True)            # 资源类型 (tool/app/recommend/post/task)
    related_user_name = Column(String, nullable=True)    # 对方用户名
    
    # 🚀 P1性能优化：复合索引
    __table_args__ = (
        Index('ix_transactions_account_type', 'account', 'tx_type'),
        Index('ix_transactions_account_created', 'account', 'created_at'),
    )