AutoModerate/app/models/api_user.py

import uuid
from datetime import datetime

from sqlalchemy import func

from app import db


class APIUser(db.Model):
    __tablename__ = 'api_users'
    # An external user is uniquely identified by (project_id, external_user_id).
    # Without this constraint two concurrent /api/moderate requests for the
    # same external user could both miss the SELECT and both INSERT, producing
    # duplicate rows that split stats across them.
    __table_args__ = (
        db.UniqueConstraint('project_id', 'external_user_id',
                            name='uq_api_users_project_external'),
    )

    id = db.Column(db.String(36), primary_key=True,
                   default=lambda: str(uuid.uuid4()))
    # The user ID passed in metadata
    external_user_id = db.Column(db.String(255), nullable=False)
    project_id = db.Column(db.String(36), db.ForeignKey(
        'projects.id'), nullable=False)
    first_seen = db.Column(db.DateTime, default=datetime.utcnow)
    last_seen = db.Column(db.DateTime, default=datetime.utcnow)
    total_requests = db.Column(db.Integer, default=0)
    approved_count = db.Column(db.Integer, default=0)
    rejected_count = db.Column(db.Integer, default=0)
    flagged_count = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(
        db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    project = db.relationship('Project', backref=db.backref('api_users', cascade='all, delete-orphan'))
    content_items = db.relationship('Content', backref='api_user', lazy=True)

    def to_dict(self):
        return {
            'id': self.id,
            'external_user_id': self.external_user_id,
            'project_id': self.project_id,
            'first_seen': self.first_seen.isoformat(),
            'last_seen': self.last_seen.isoformat(),
            'total_requests': self.total_requests,
            'approved_count': self.approved_count,
            'rejected_count': self.rejected_count,
            'flagged_count': self.flagged_count,
            'approval_rate': (self.approved_count / self.total_requests * 100) if self.total_requests > 0 else 0,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }

    def update_stats(self, status):
        """Update user statistics based on moderation result"""
        self.total_requests += 1
        self.last_seen = datetime.utcnow()

        if status == 'approved':
            self.approved_count += 1
        elif status == 'rejected':
            self.rejected_count += 1
        elif status == 'flagged':
            self.flagged_count += 1

    def get_current_stats(self):
        """Get current stats by counting from database with caching"""
        from app.models.content import Content

        # Use cached stats if available in this request
        if hasattr(self, '_cached_stats'):
            return self._cached_stats

        # Use a single efficient query to get all counts at once
        result = db.session.query(
            func.count(Content.id).label('total'),
            func.sum(db.case((Content.status == 'approved', 1), else_=0)).label('approved'),
            func.sum(db.case((Content.status == 'rejected', 1), else_=0)).label('rejected'),
            func.sum(db.case((Content.status == 'flagged', 1), else_=0)).label('flagged')
        ).filter(Content.api_user_id == self.id).first()

        total_content = result.total or 0
        approved = result.approved or 0
        rejected = result.rejected or 0
        flagged = result.flagged or 0

        stats = {
            'total_requests': total_content,
            'approved_count': approved,
            'rejected_count': rejected,
            'flagged_count': flagged,
            'approval_rate': (approved / total_content * 100) if total_content > 0 else 0
        }

        # Cache the stats for this instance
        self._cached_stats = stats
        return stats

    @property
    def approval_rate(self):
        stats = self.get_current_stats()
        return stats['approval_rate']

    @property
    def current_total_requests(self):
        stats = self.get_current_stats()
        return stats['total_requests']

    @property
    def current_approved_count(self):
        stats = self.get_current_stats()
        return stats['approved_count']

    @property
    def current_rejected_count(self):
        stats = self.get_current_stats()
        return stats['rejected_count']

    @property
    def current_flagged_count(self):
        stats = self.get_current_stats()
        return stats['flagged_count']

    @property
    def days_active(self):
        """Calculate days active more accurately"""
        if not self.last_seen or not self.first_seen:
            return 0

        # Get date components only (ignore time)
        first_date = self.first_seen.date()
        last_date = self.last_seen.date()

        # Calculate difference in days and add 1 (inclusive)
        delta = (last_date - first_date).days + 1
        return max(1, delta)  # Minimum 1 day

    def __repr__(self):
        return f'<APIUser {self.external_user_id}>'