app/wallet/models/model.py

"""
SQLAlchemy models for spa wallet management.
Maps to trans.spa_wallets and trans.spa_wallet_transactions tables.
"""
from sqlalchemy import Column, String, LargeBinary, TIMESTAMP, ForeignKey, Numeric, Text
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.core.database import Base
import uuid


class SpaWallet(Base):
    """Spa wallet model with encrypted balance"""
    __tablename__ = "spa_wallets"
    __table_args__ = {"schema": "trans"}
    
    wallet_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    partner_id = Column(String(50), unique=True, nullable=False)
    encrypted_balance = Column(LargeBinary, nullable=False)  # Encrypted balance
    currency = Column(String(3), default="INR")
    status = Column(String(20), default="active")  # active | suspended | closed
    last_transaction_at = Column(TIMESTAMP)
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp())
    
    # Relationship to transactions
    transactions = relationship("SpaWalletTransaction", back_populates="wallet", cascade="all, delete-orphan")


class SpaWalletTransaction(Base):
    """Spa wallet transaction model"""
    __tablename__ = "spa_wallet_transactions"
    __table_args__ = {"schema": "trans"}
    
    transaction_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    wallet_id = Column(UUID(as_uuid=True), ForeignKey("trans.spa_wallets.wallet_id"), nullable=False)
    partner_id = Column(String(50), nullable=False)
    transaction_type = Column(String(20), nullable=False)  # credit | debit
    amount = Column(Numeric(12, 2), nullable=False)
    balance_after = Column(Numeric(12, 2), nullable=False)
    reference_type = Column(String(50))  # order | refund | topup | commission | withdrawal
    reference_id = Column(String(100))
    description = Column(Text)
    meta_data = Column(JSONB)
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp())
    
    # Relationship to wallet
    wallet = relationship("SpaWallet", back_populates="transactions")