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)         # 用于消费的余额 (充值获得)
    earn_balance = Column(Integer, default=0)    # 创作者销售收益余额 (别人购买获得)
    tip_balance = Column(Integer, default=0)     # 【新增】创作者打赏收益余额 (粉丝赞助获得)
    frozen_balance = Column(Integer, default=0)  # 提现审核冻结中的余额
    
    # 乐观锁版本号，防止并发扣款被击穿
    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)                     # 本笔订单的哈希 (防篡改)
    
    # 🚀 P1性能优化：复合索引
    __table_args__ = (
        Index('ix_transactions_account_type', 'account', 'tx_type'),
        Index('ix_transactions_account_created', 'account', 'created_at'),
    )