LT_Janitorial_Quality_Control/app/utils/sla.py

"""
sla.py
------
Issue SLA (Service Level Agreement) helpers.

SLA thresholds define the maximum number of hours an issue of a given
severity may remain unresolved before it is considered breached.

Statuses
--------
ok        — within the allowed window
at_risk   — past 75% of the allowed window but not yet breached
breached  — past the deadline
None      — issue is already resolved; SLA no longer applies
"""

from datetime import timedelta
from app.utils.time_utils import now_eastern

# ── Configurable thresholds (hours) ──────────────────────────────────────────
SLA_HOURS = {
    'critical': 4,
    'high':     24,
    'medium':   72,
    'low':      120,   # 5 days
}

# Fraction of the window at which an issue becomes "at risk"
AT_RISK_THRESHOLD = 0.75


def sla_deadline(issue):
    """
    Return the datetime by which the issue must be resolved,
    or None if the severity is not recognized.
    """
    hours = SLA_HOURS.get(issue.severity)
    if hours is None:
        return None
    return issue.reported_at + timedelta(hours=hours)


def sla_status(issue):
    """
    Return one of: 'ok', 'at_risk', 'breached', or None.

    None is returned when the issue is already resolved — SLA no longer
    applies.  None is also returned for unrecognized severity values.
    """
    if issue.status == 'resolved':
        return None

    hours = SLA_HOURS.get(issue.severity)
    if hours is None:
        return None

    deadline   = issue.reported_at + timedelta(hours=hours)
    at_risk_at = issue.reported_at + timedelta(hours=hours * AT_RISK_THRESHOLD)
    now        = now_eastern()

    if now >= deadline:
        return 'breached'
    if now >= at_risk_at:
        return 'at_risk'
    return 'ok'


def sla_hours_remaining(issue):
    """
    Return the number of hours remaining before the SLA deadline.
    Negative values indicate the deadline has already passed.
    Returns None for resolved issues or unrecognized severities.
    """
    if issue.status == 'resolved':
        return None
    deadline = sla_deadline(issue)
    if deadline is None:
        return None
    delta = deadline - now_eastern()
    return round(delta.total_seconds() / 3600, 1)


# ── SLA alert dispatcher ──────────────────────────────────────────────────────

def send_sla_alerts():
    """
    Check all open/in-progress issues for SLA breaches or at-risk status and
    dispatch in-app + email notifications to the appropriate recipients.

    Recipient rules:
      - Admins always receive alerts
      - If the issue is assigned, the assignee also receives an alert
      - All followers of the issue also receive an alert
      - Deduplication ensures each user gets at most one notification per call

    Deduplication across cron runs:
      - Issue.sla_notified tracks the highest alert level already sent
        ('at_risk' or 'breached'). A notification is only sent once per level.
      - 'breached' supersedes 'at_risk': if a user was already notified
        at-risk, they will receive a second notification when it breaches.

    Returns the number of notifications created.
    """
    from flask import current_app, url_for
    from app import db
    from app.models.issue import Issue
    from app.models.user import User
    from app.utils.notifications import notify, notify_by_matrix
    import logging

    logger = logging.getLogger(__name__)

    # Narrow to actual CANDIDATES in SQL rather than reading every open issue
    # and deciding in Python. This runs every 30 minutes forever, so the old
    # form's cost grew with the whole open-issue backlog even on a quiet night
    # where nothing was due. Three filters, each mirroring a `continue` below:
    #
    #   1. reported_at IS NOT NULL — the column is nullable, and sla_status()
    #      raises TypeError on a NULL (datetime + timedelta). One such row
    #      would abort the entire cron run, so exclude it in SQL.
    #   2. sla_notified <> 'breached' — the highest level is already sent; the
    #      loop skips these unconditionally.
    #   3. old enough to be at least at-risk for its OWN severity, i.e.
    #      reported_at <= now - (window * 0.75). A critical issue qualifies
    #      after 3h, a low one after 90h.
    #
    # Anything this excludes would have hit a `continue` anyway, so the set of
    # notifications sent is unchanged — only the rows read are.
    now = now_eastern()
    age_clauses = [
        db.and_(
            Issue.severity == severity,
            Issue.reported_at <= now - timedelta(hours=hours * AT_RISK_THRESHOLD),
        )
        for severity, hours in SLA_HOURS.items()
    ]

    # yield_per streams the survivors in batches rather than materialising them
    # all at once.
    open_issues = Issue.query.filter(
        Issue.status.in_(['open', 'in_progress', 'pending_verification']),
        Issue.reported_at.isnot(None),
        db.or_(Issue.sla_notified.is_(None), Issue.sla_notified != 'breached'),
        db.or_(*age_clauses),
    ).yield_per(100)

    total_sent = 0

    for issue in open_issues:
        status = sla_status(issue)

        # Only act on at_risk or breached
        if status not in ('at_risk', 'breached'):
            continue

        # Skip if this level (or higher) was already notified
        already = issue.sla_notified
        if already == 'breached':
            continue   # highest level already sent
        if already == 'at_risk' and status == 'at_risk':
            continue   # at_risk already sent, not yet breached

        # Compose message
        hrs      = sla_hours_remaining(issue)
        deadline = sla_deadline(issue)

        facility_name = issue.resolved_facility.name if issue.resolved_facility else "\u2014"

        if status == 'breached':
            title = f'🚨 SLA Breached — Issue #{issue.id} ({issue.severity.title()})'
            body  = (
                f'Issue #{issue.id} at {facility_name} '
                f'has breached its SLA deadline. '
                f'Severity: {issue.severity.title()}. '
                f'Deadline was {deadline.strftime("%Y-%m-%d %H:%M") if deadline else "N/A"}. '
                f'Current status: {issue.status.replace("_", " ").title()}.'
            )
        else:  # at_risk
            title = f'⚠️ SLA At Risk — Issue #{issue.id} ({issue.severity.title()})'
            body  = (
                f'Issue #{issue.id} at {facility_name} '
                f'is approaching its SLA deadline with approximately '
                f'{abs(hrs):.1f}h remaining. '
                f'Severity: {issue.severity.title()}. '
                f'Deadline: {deadline.strftime("%Y-%m-%d %H:%M") if deadline else "N/A"}. '
                f'Current status: {issue.status.replace("_", " ").title()}.'
            )

        try:
            link = url_for('issues.view', issue_id=issue.id)
        except RuntimeError:
            link = f'/issues/{issue.id}'

        # Always notify the assignee and followers (implicit, not matrix-controlled)
        implicit_notified = set()
        if issue.assigned_to and issue.assigned_user:
            notify(
                recipient  = issue.assigned_user,
                title      = title,
                body       = body,
                link       = link,
                issue_id   = issue.id,
                event_type = 'sla_alert',
                send_email = True,
            )
            implicit_notified.add(issue.assigned_user.id)
            total_sent += 1

        for follower_link in issue.followers.all():
            if follower_link.user_id not in implicit_notified:
                notify(
                    recipient  = follower_link.user,
                    title      = title,
                    body       = body,
                    link       = link,
                    issue_id   = issue.id,
                    event_type = 'sla_alert',
                    send_email = True,
                )
                implicit_notified.add(follower_link.user_id)
                total_sent += 1

        # Matrix-controlled broadcast (admin, supervisor, etc.)
        notify_by_matrix(
            event_type       = 'sla_alert',
            title            = title,
            body             = body,
            link             = link,
            issue_id         = issue.id,
            exclude_user_ids = implicit_notified,
        )
        total_sent += 1  # approximate — matrix count not returned

        # Mark this issue as notified at the current level
        issue.sla_notified = status
        logger.info(
            'SLA ALERT SENT | issue_id=%s | status=%s',
            issue.id, status,
        )

    if total_sent:
        db.session.commit()

    return total_sent


# ── Score trend alert dispatcher ──────────────────────────────────────────────

# Default drop threshold in percentage points that triggers an alert.
SCORE_DROP_THRESHOLD = 5.0


def send_score_alerts(threshold=SCORE_DROP_THRESHOLD):
    """
    Compare each active facility's avg inspection score for the last 30 days
    against the prior 30-day period.  When the score has dropped by more than
    *threshold* points, dispatch an in-app + email alert via notify_by_matrix
    and record the alert in facility_score_alerts for deduplication.

    A facility is skipped if it already received an alert within the last 24
    hours (prevents repeat storms on persistent low scores).

    Returns the number of alert notifications dispatched.
    """
    from datetime import timedelta
    from flask import current_app, url_for
    from sqlalchemy import func
    from app import db
    from app.models.facility import Facility
    from app.models.inspection import Inspection
    from app.models.score_alert import FacilityScoreAlert
    from app.utils.notifications import notify_by_matrix
    import logging

    logger = logging.getLogger(__name__)

    now       = now_eastern()
    cur_start = now - timedelta(days=30)
    pri_start = now - timedelta(days=60)
    pri_end   = cur_start

    # Current-period avg score per facility
    cur_rows = db.session.query(
        Facility.id,
        Facility.name,
        func.avg(Inspection.overall_score).label('avg'),
    ).join(Inspection, Facility.id == Inspection.facility_id)\
     .filter(
        Facility.active == True,
        Inspection.inspection_date >= cur_start,
        Inspection.inspection_date <= now,
        Inspection.status == 'completed',
        Inspection.overall_score.isnot(None),
     ).group_by(Facility.id, Facility.name).all()

    # Prior-period avg score per facility
    pri_rows = db.session.query(
        Facility.id,
        func.avg(Inspection.overall_score).label('avg'),
    ).join(Inspection, Facility.id == Inspection.facility_id)\
     .filter(
        Facility.active == True,
        Inspection.inspection_date >= pri_start,
        Inspection.inspection_date <= pri_end,
        Inspection.status == 'completed',
        Inspection.overall_score.isnot(None),
     ).group_by(Facility.id).all()

    prior_map = {r.id: float(r.avg) for r in pri_rows}

    # Facilities that already received an alert in the last 24 hours
    cutoff = now - timedelta(hours=24)
    recent_alerts = db.session.query(FacilityScoreAlert.facility_id)\
        .filter(FacilityScoreAlert.sent_at >= cutoff).all()
    already_alerted = {r.facility_id for r in recent_alerts}

    total_sent = 0

    for row in cur_rows:
        fid      = row.id
        cur_avg  = float(row.avg)
        pri_avg  = prior_map.get(fid)

        if pri_avg is None:
            continue  # no prior period data — nothing to compare

        delta = cur_avg - pri_avg   # negative = score dropped

        if delta >= -threshold:
            continue  # drop is within acceptable range

        if fid in already_alerted:
            logger.debug('SCORE ALERT SKIPPED (already alerted) | facility_id=%s', fid)
            continue

        title = f'📉 Score Drop Alert — {row.name}'
        body  = (
            f'{row.name} avg score has dropped {abs(delta):.1f} points '
            f'(from {pri_avg:.1f}% to {cur_avg:.1f}%) over the last 30 days '
            f'vs. the prior 30-day period.'
        )

        try:
            link = url_for('reports.facility_scorecard', facility_id=fid)
        except RuntimeError:
            link = f'/reports/facility/{fid}/scorecard'

        notify_by_matrix(
            event_type = 'score_alert',
            title      = title,
            body       = body,
            link       = link,
        )
        total_sent += 1

        db.session.add(FacilityScoreAlert(
            facility_id = fid,
            sent_at     = now,
            current_avg = round(cur_avg, 2),
            prior_avg   = round(pri_avg, 2),
            delta       = round(delta, 2),
        ))

        logger.info(
            'SCORE ALERT SENT | facility_id=%s | facility=%s | cur=%.1f | prior=%.1f | delta=%.1f',
            fid, row.name, cur_avg, pri_avg, delta,
        )

    if total_sent:
        db.session.commit()

    return total_sent