models_sql.py

# models_sql.py
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Float
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)    # 创作者收益余额 (别人购买获得)
    frozen_balance = Column(Integer, default=0)  # 提现审核冻结中的余额
    
    # 乐观锁版本号，防止并发扣款被击穿
    version = Column(Integer, default=1)

class Ownership(Base):
    """资源所有权表 (解决Req 4：一次购买，终身免费)"""
    __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)

class Transaction(Base):
    """交易流水表 (复式记账，解决Req 6, 7：税务对账与哈希防篡改)"""
    __tablename__ = "transactions"
    
    tx_id = Column(String, primary_key=True)  # 全局唯一流水号 (如 ORDER_uuid)
    account = Column(String, index=True)
    tx_type = Column(String)                  # RECHARGE, CONSUME, EARN, WITHDRAW
    amount = Column(Integer)                  # 交易额 (正负值)
    target_id = Column(String, nullable=True) # 关联的 item_id 或第三方支付宝流水号
    
    # 区块链级安全：哈希防篡改链
    prev_hash = Column(String, nullable=True) # 上一笔交易的哈希值
    tx_hash = Column(String)                  # 当前交易哈希值
    
    created_at = Column(DateTime, default=datetime.datetime.utcnow)