core/models.py

"""
SQLAlchemy models for the APIGateway application.
"""
from sqlalchemy import Column, Integer, String, Text, DateTime, JSON, Boolean
from sqlalchemy.sql import func
from core.database import Base


class BlinkData(Base):
    """
    Model for storing decrypted blink data.
    
    Attributes:
        id: Primary key
        user_id: User identifier (first 20 chars from URL param)
        refer_url: Referer URL from request header
        json_data: Decrypted JSON data
        created_at: Timestamp of record creation
    """
    __tablename__ = "blink_data"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    user_id = Column(String(20), index=True, nullable=False)
    refer_url = Column(Text, nullable=True)
    ip_address = Column(String(45), nullable=True)  # IPv6 can be up to 45 chars
    ipv4_address = Column(String(15), nullable=True)
    ipv6_address = Column(String(45), nullable=True)
    country = Column(String(100), nullable=True)  # Country from IP geolocation
    region = Column(String(100), nullable=True)   # Region/State from IP geolocation
    json_data = Column(JSON, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    def __repr__(self):
        return f"<BlinkData(id={self.id}, user_id={self.user_id})>"


class User(Base):
    """
    User model for credit system.
    Supports both legacy secret key and Google OAuth authentication.
    """
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    user_id = Column(String(50), unique=True, index=True, nullable=False)  # Backend generated UUID
    temp_user_id = Column(String(50), index=True, nullable=True)  # From frontend
    email = Column(String(255), unique=True, index=True, nullable=False)
    
    # Google OAuth fields
    google_id = Column(String(255), unique=True, index=True, nullable=True)  # Google sub claim
    name = Column(String(255), nullable=True)  # Display name from Google
    profile_picture = Column(Text, nullable=True)  # Google profile picture URL
    
    # Legacy field (kept for migration, nullable now)
    secret_key_hash = Column(String(255), nullable=True)
    
    # Token versioning for JWT invalidation
    # Incrementing this invalidates all existing tokens for this user
    token_version = Column(Integer, default=1, nullable=False)
    
    # Credits and status
    credits = Column(Integer, default=100)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    last_used_at = Column(DateTime(timezone=True), nullable=True)
    is_active = Column(Boolean, default=True)

    def __repr__(self):
        return f"<User(id={self.id}, email={self.email})>"



class RateLimit(Base):
    """
    Rate limit tracking table.
    """
    __tablename__ = "rate_limits"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    identifier = Column(String(255), index=True, nullable=False)  # IP or email
    endpoint = Column(String(255), index=True, nullable=False)
    attempts = Column(Integer, default=0)
    window_start = Column(DateTime(timezone=True), nullable=False)
    expires_at = Column(DateTime(timezone=True), nullable=False)


class AuditLog(Base):
    """
    Audit log for security events.
    """
    __tablename__ = "audit_logs"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    user_id = Column(String(50), nullable=True)
    action = Column(String(50), nullable=False)
    ip_address = Column(String(45), nullable=False)
    user_agent = Column(String(255), nullable=True)
    status = Column(String(20), nullable=False)
    error_message = Column(Text, nullable=True)
    timestamp = Column(DateTime(timezone=True), server_default=func.now())


class GeminiJob(Base):
    """
    Generic job queue for Gemini operations (video, image, text).
    Uses priority-tier system for worker assignment.
    """
    __tablename__ = "gemini_jobs"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    job_id = Column(String(100), unique=True, index=True, nullable=False)  # Our ID for client
    user_id = Column(String(50), index=True, nullable=False)  # User who requested
    job_type = Column(String(20), index=True, nullable=False)  # video, image, text, analyze
    third_party_id = Column(String(255), nullable=True)  # Gemini operation name (for video)
    status = Column(String(20), default="queued", index=True)  # queued, processing, completed, failed, cancelled
    
    # Priority-tier worker system
    priority = Column(String(10), default="fast", index=True)  # fast (5s), medium (30s), slow (60s)
    next_process_at = Column(DateTime(timezone=True), nullable=True, index=True)  # When worker should pick up again
    retry_count = Column(Integer, default=0)  # Number of status check retries
    
    input_data = Column(JSON, nullable=True)  # Request details (prompt, settings, etc.)
    output_data = Column(JSON, nullable=True)  # Result (filename, text, etc.)
    error_message = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    started_at = Column(DateTime(timezone=True), nullable=True)
    completed_at = Column(DateTime(timezone=True), nullable=True)
    
    # Credit tracking for reservation pattern
    credits_reserved = Column(Integer, default=0)  # Credits reserved for this job
    credits_refunded = Column(Boolean, default=False)  # Whether credits were refunded

    def __repr__(self):
        return f"<GeminiJob(job_id={self.job_id}, type={self.job_type}, status={self.status}, priority={self.priority})>"


class ApiKeyUsage(Base):
    """
    Track API key usage for round-robin load balancing.
    Only stores the key index, not the actual key for security.
    """
    __tablename__ = "api_key_usage"

    id = Column(Integer, primary_key=True, autoincrement=True)
    key_index = Column(Integer, unique=True, index=True, nullable=False)  # Index of key in GEMINI_API_KEYS
    total_requests = Column(Integer, default=0)
    success_count = Column(Integer, default=0)
    failure_count = Column(Integer, default=0)
    last_error = Column(Text, nullable=True)  # Stores the last error message for review
    last_used_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    def __repr__(self):
        return f"<ApiKeyUsage(index={self.key_index}, total={self.total_requests}, success={self.success_count}, failed={self.failure_count})>"


class PaymentTransaction(Base):
    """
    Track payment transactions for credit purchases.
    Supports multiple payment gateways (Razorpay now, Stripe later).
    """
    __tablename__ = "payment_transactions"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    transaction_id = Column(String(50), unique=True, index=True, nullable=False)  # Our internal ID
    user_id = Column(String(50), index=True, nullable=False)  # Links to User.user_id
    
    # Order details
    gateway = Column(String(20), nullable=False)  # razorpay, stripe
    gateway_order_id = Column(String(100), index=True, nullable=True)  # Razorpay order_id
    gateway_payment_id = Column(String(100), index=True, nullable=True)  # Razorpay payment_id
    
    # Package details
    package_id = Column(String(50), nullable=False)  # starter, standard, pro
    credits_amount = Column(Integer, nullable=False)  # Credits to add
    amount_paise = Column(Integer, nullable=False)  # Amount in paise (INR * 100)
    currency = Column(String(3), default="INR")
    
    # Status tracking
    status = Column(String(20), default="created", index=True)  # created, paid, failed, refunded
    verified_by = Column(String(20), nullable=True)  # client, webhook, or both
    
    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    paid_at = Column(DateTime(timezone=True), nullable=True)
    
    # Metadata
    razorpay_signature = Column(String(255), nullable=True)  # For verification audit
    error_message = Column(Text, nullable=True)
    extra_data = Column(JSON, nullable=True)  # Additional gateway-specific data (renamed from 'metadata' - reserved)

    def __repr__(self):
        return f"<PaymentTransaction(id={self.transaction_id}, status={self.status}, amount={self.amount_paise})>"


class Contact(Base):
    """
    Contact form submissions from authenticated users.
    For customer support inquiries.
    """
    __tablename__ = "contacts"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    user_id = Column(String(50), index=True, nullable=False)  # Links to User.user_id
    email = Column(String(255), nullable=False, index=True)  # User's email
    subject = Column(String(500), nullable=True)
    message = Column(Text, nullable=False)
    ip_address = Column(String(45), nullable=True)  # IPv6 can be up to 45 chars
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    def __repr__(self):
        return f"<Contact(id={self.id}, user_id={self.user_id}, email={self.email})>"