models_sql.py

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

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

Base = declarative_base()


class OpenApiOperation(Base):
    __tablename__ = "open_api_operations"
    request_key = Column(String(64), primary_key=True)
    request_hash = Column(String(64), nullable=False)
    transaction_id = Column(String(100), nullable=False, unique=True)
    response_payload = Column(Text, nullable=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)


event.listen(OpenApiOperation.__table__, "after_create",
    DDL("ALTER TABLE %(fullname)s ENABLE ROW LEVEL SECURITY").execute_if(dialect="postgresql"))
event.listen(OpenApiOperation.__table__, "after_create",
    DDL("REVOKE ALL ON TABLE %(fullname)s FROM PUBLIC").execute_if(dialect="postgresql"))
event.listen(OpenApiOperation.__table__, "after_create", DDL("""

DO $$ BEGIN

    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'anon') THEN

        REVOKE ALL ON TABLE %(fullname)s FROM anon;

    END IF;

    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'authenticated') THEN

        REVOKE ALL ON TABLE %(fullname)s FROM authenticated;

    END IF;

END; $$

""").execute_if(dialect="postgresql"))


class ResourcePublication(Base):
    """同次资源发布的持久原始请求和恢复内容；不参与资金变更。"""
    __tablename__ = "resource_publications"
    request_key = Column(String(64), primary_key=True)
    item_id = Column(String(100), nullable=False, unique=True)
    author = Column(String, nullable=False)
    request_payload = Column(Text, nullable=False)
    payload = Column(Text, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)


class TaskPublication(Base):
    """与任务资金冻结同提交的待发布记录；保留原始内容供失败恢复。"""
    __tablename__ = "task_publications"
    task_id = Column(String(64), primary_key=True)
    publisher = Column(String, nullable=False)
    payload = Column(Text, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)


class TaskRevision(Base):
    """任务编辑的持久字段投影，与改价资金同事务；版本按钱包锁串行递增。"""
    __tablename__ = "task_revisions"
    task_id = Column(String(64), primary_key=True)
    revision = Column(Integer, primary_key=True)
    publisher = Column(String, nullable=False)
    payload = Column(Text, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)


class TaskAssignment(Base):
    """指派和订金SQL提交后的状态投影，不能因JSON失败再次扣订金。"""
    __tablename__ = "task_assignments"
    task_id = Column(String(64), primary_key=True)
    publisher = Column(String, nullable=False)
    assignee = Column(String, nullable=False)
    deposit = Column(Integer, nullable=False)
    assigned_at = Column(Integer, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)


class TaskDelivery(Base):
    """交付/退回最新版本先持久保存，JSON失败不能丢失成果或恢复旧验收。"""
    __tablename__ = "task_deliveries"
    task_id = Column(String(64), primary_key=True)
    publisher = Column(String, nullable=False)
    assignee = Column(String, nullable=False)
    revision = Column(Integer, nullable=False)
    payload = Column(Text, nullable=False)
    updated_at = Column(Integer, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)


class TaskSettlement(Base):
    """验收付款和待完成状态同事务；一任务只结算一次。"""
    __tablename__ = "task_settlements"
    task_id = Column(String(64), primary_key=True)
    publisher = Column(String, nullable=False)
    assignee = Column(String, nullable=False)
    amount = Column(Integer, nullable=False)
    deposit = Column(Integer, nullable=False)
    payment_tx_id = Column(String, nullable=False)
    income_tx_id = Column(String, nullable=False)
    completed_at = Column(Integer, nullable=False)
    feedback = Column(Text, nullable=True)
    projected = Column(Boolean, nullable=False, default=False, index=True)


class TaskResolution(Base):
    """仲裁资金和裁决同提交；一任务只允许一次最终分配。"""
    __tablename__ = "task_resolutions"
    task_id = Column(String(64), primary_key=True)
    dispute_id = Column(String(64), nullable=False, unique=True)
    publisher = Column(String, nullable=False)
    assignee = Column(String, nullable=False)
    amount = Column(Integer, nullable=False)
    deposit = Column(Integer, nullable=False)
    publisher_refund = Column(Integer, nullable=False)
    assignee_income = Column(Integer, nullable=False)
    initiator_role = Column(String(16), nullable=False)
    resolution = Column(String(32), nullable=False)
    ratio = Column(Integer, nullable=True)
    note = Column(Text, nullable=True)
    resolved_by = Column(String, nullable=False)
    resolved_at = Column(Integer, nullable=False)
    payment_tx_id = Column(String, nullable=False)
    refund_tx_id = Column(String, nullable=False)
    income_tx_id = Column(String, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)

class TaskDisputeRecord(Base):
    """申诉先持久保存；JSON同步期间也禁止普通验收结算。"""
    __tablename__ = "task_dispute_records"
    task_id = Column(String(64), primary_key=True)
    dispute_id = Column(String(64), nullable=False, unique=True)
    publisher = Column(String, nullable=False)
    assignee = Column(String, nullable=False)
    payload = Column(Text, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)
    created_at = Column(Integer, nullable=False)


class TaskDisputeResponseRecord(Base):
    """首次申诉回应持久记录；一个任务只保存一份原始回应。"""
    __tablename__ = "task_dispute_responses"
    task_id = Column(String(64), primary_key=True)
    dispute_id = Column(String(64), nullable=False, unique=True)
    publisher = Column(String, nullable=False)
    assignee = Column(String, nullable=False)
    respondent = Column(String, nullable=False)
    payload = Column(Text, nullable=False)
    projected = Column(Boolean, nullable=False, default=False, index=True)


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_id_col，不提供 ORM 乐观锁保护
    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 RechargeOrder(Base):
    """💰 充值订单预存表（订单号→账号映射持久化，防止实例重启导致回调找不到订单）"""
    __tablename__ = "recharge_orders"
    
    order_id = Column(String, primary_key=True)    # 商户订单号 PAY_xxx
    account = Column(String, index=True)           # 充值用户
    amount = Column(Integer)                        # 充值金额
    status = Column(String, default="pending")     # pending/paid
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

class Transaction(Base):
    """交易流水表"""
    __tablename__ = "transactions"
    
    tx_id = Column(String, primary_key=True)
    account = Column(String, index=True)
    tx_type = Column(String)                     # 枚举: RECHARGE, PURCHASE, SALE, TIP_OUT, TIP_IN, WITHDRAW, WITHDRAW_FEE, REFUND, SALE_REFUND, TASK_FREEZE, TASK_PRICE_ADJUST, TASK_DEPOSIT, TASK_PAYMENT, TASK_INCOME, TASK_REFUND, TASK_CANCEL_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/skill/app/recommend/post/task/prompt)
    related_user_name = Column(String, nullable=True)    # 对方用户名
    
    # 🌐 开放API：来源插件标识（如 "nodecraft_ai"），用于按工具结构化追踪消费
    source_plugin = Column(String(50), nullable=True, index=True)
    
    # 🚀 P1性能优化：复合索引
    __table_args__ = (
        Index('ix_transactions_account_type', 'account', 'tx_type'),
        Index('ix_transactions_account_created', 'account', 'created_at'),
        Index('ix_transactions_source_plugin_created', 'source_plugin', 'created_at'),
    )

class SystemConfig(Base):
    """系统配置表 - 持久化存储，替代 /tmp/system_config.json"""
    __tablename__ = "system_configs"
    
    config_key = Column(String, primary_key=True)
    config_value = Column(String, nullable=False, default="{}")
    updated_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_by = Column(String, nullable=True)


class RegisteredPlugin(Base):
    """🌐 开放API：第三方插件注册信息表"""
    __tablename__ = "registered_plugins"

    plugin_id          = Column(String(50), primary_key=True)   # 英文标识如 "nodecraft_ai"
    plugin_key         = Column(String(100), unique=True, nullable=False, index=True)
    name               = Column(String(100), nullable=False)     # 显示名
    description        = Column(String(500), nullable=True)
    enabled            = Column(Boolean, default=True, nullable=False)
    rate_limit         = Column(Integer, default=100)            # 每分钟最大请求数
    max_deduct_per_tx  = Column(Integer, default=100)            # 单笔扣款上限
    max_deduct_per_day = Column(Integer, default=1000)           # 日扣款上限
    permissions        = Column(String(200), default='["verify","balance","deduct","refund"]')  # JSON数组
    created_at         = Column(DateTime, default=func.now())
    created_by         = Column(String(50))                      # 创建者管理员账号
    updated_at         = Column(DateTime, onupdate=func.now())
    last_rotated_at    = Column(DateTime, nullable=True)         # 上次轮换key时间


class OpenApiRateLimitBucket(Base):
    """跨 worker 持久化的开放 API 插件分钟窗口计数。"""
    __tablename__ = "open_api_rate_limit_buckets"

    plugin_id = Column(String(50), primary_key=True)
    window_start = Column(Integer, nullable=False)
    request_count = Column(Integer, nullable=False, default=0)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)


class ApiRateLimitBucket(Base):
    """跨 worker 共享的常规 API 限流分钟窗口。"""
    __tablename__ = "api_rate_limit_buckets"

    bucket_key = Column(String(64), primary_key=True)
    window_start = Column(Integer, nullable=False)
    request_count = Column(Integer, nullable=False, default=0)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)


class VerificationContactCooldown(Base):
    """验证码联系方式发送冷却；只持久化脱敏哈希键，不保存原始邮箱/手机号。"""
    __tablename__ = "verification_contact_cooldowns"

    contact_key = Column(String(64), primary_key=True)
    last_sent_at = Column(Integer, nullable=False)
    claim_id = Column(String(32), nullable=False)


class VerificationCode(Base):
    """跨 worker 一次性验证码；仅保存联系方式哈希和带密钥的验证码摘要。"""
    __tablename__ = "verification_codes"

    contact_key = Column(String(64), primary_key=True)
    code_digest = Column(String(64), nullable=False)
    expires_at = Column(Integer, nullable=False, index=True)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)