vpn-btcpay-provisioner/app/utils/db/models.py

from sqlalchemy import (
    create_engine, Column, Integer, String, DateTime, 
    ForeignKey, Enum, Text, JSON, Boolean, Float
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
import enum
import datetime

Base = declarative_base()

class SubscriptionStatus(enum.Enum):
    ACTIVE = "active"
    EXPIRED = "expired"
    PENDING = "pending"
    CANCELLED = "cancelled"
    FAILED = "failed"  # New status for failed provisions
    SUSPENDED = "suspended"  # New status for temp suspension

class LogLevel(enum.Enum):
    DEBUG = "debug"
    INFO = "info"
    WARNING = "warning"
    ERROR = "error"

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(String, unique=True, nullable=False)  # UUID generated in frontend
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    last_login = Column(DateTime, nullable=True)  # New: Track last login time
    is_active = Column(Boolean, default=True, nullable=True)  # New: User account status
    user_data = Column(JSON, nullable=True)  # New: Optional user metadata
    
    subscriptions = relationship("Subscription", back_populates="user")
    payments = relationship("Payment", back_populates="user")
    provision_logs = relationship("ProvisionLog", back_populates="user")  # New relationship

class Subscription(Base):
    __tablename__ = 'subscriptions'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    invoice_id = Column(String, unique=True)
    public_key = Column(Text, nullable=False)  # WireGuard public key
    start_time = Column(DateTime, nullable=False)
    expiry_time = Column(DateTime, nullable=False)
    status = Column(Enum(SubscriptionStatus), default=SubscriptionStatus.PENDING)
    warning_sent = Column(Integer, default=0)
    assigned_ip = Column(String)  # WireGuard IP address assigned to this subscription
    
    # New fields for monitoring
    last_connection = Column(DateTime, nullable=True)  # Track last connection time
    data_usage = Column(Float, default=0.0)  # Track data usage in MB
    is_test = Column(Boolean, default=False)  # Flag for test subscriptions
    provision_attempts = Column(Integer, default=0)  # Count provision attempts
    cleanup_attempts = Column(Integer, default=0)  # Count cleanup attempts
    config_data = Column(JSON, nullable=True)  # Store additional config data
    
    user = relationship("User", back_populates="subscriptions")
    payments = relationship("Payment", back_populates="subscription")
    provision_logs = relationship("ProvisionLog", back_populates="subscription")

class Payment(Base):
    __tablename__ = 'payments'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    subscription_id = Column(Integer, ForeignKey('subscriptions.id'))
    invoice_id = Column(String, unique=True)
    amount = Column(Integer, nullable=False)  # Amount in sats
    timestamp = Column(DateTime, default=datetime.datetime.utcnow)
    
    # New payment tracking fields
    payment_method = Column(String, nullable=True)  # Payment method used
    payment_status = Column(String, nullable=True)  # Payment status
    confirmations = Column(Integer, default=0)  # Number of confirmations
    payment_data = Column(JSON, nullable=True)  # Additional payment data
    
    user = relationship("User", back_populates="payments")
    subscription = relationship("Subscription", back_populates="payments")

class ProvisionLog(Base):
    __tablename__ = 'provision_logs'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    subscription_id = Column(Integer, ForeignKey('subscriptions.id'))
    timestamp = Column(DateTime, default=datetime.datetime.utcnow)
    action = Column(String, nullable=False)  # 'provision' or 'cleanup'
    status = Column(String, nullable=False)  # 'success' or 'failure'
    ansible_output = Column(Text, nullable=True)  # Store Ansible output
    error_message = Column(Text, nullable=True)  # Store error messages
    
    user = relationship("User", back_populates="provision_logs")
    subscription = relationship("Subscription", back_populates="provision_logs")