JQC_multi_tenant/app/models/issue.py

from app import db
from app.utils.time_utils import now_eastern


class IssueComment(db.Model):
    __tablename__ = 'issue_comments'

    id                  = db.Column(db.Integer, primary_key=True)
    issue_id            = db.Column(db.Integer, db.ForeignKey('issues.id'), nullable=False)
    user_id             = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    status_at_time      = db.Column(db.String(20))   # snapshot of issue status when comment was made
    body                = db.Column(db.Text, nullable=False)
    created_at          = db.Column(db.DateTime, default=now_eastern, nullable=False)
    is_customer_visible = db.Column(db.Boolean, nullable=False, default=False)

    # Relationships
    author = db.relationship('User', foreign_keys=[user_id])

    def __repr__(self):
        return f'<IssueComment {self.id} issue={self.issue_id}>'


# ── Issue Follower ─────────────────────────────────────────────────────────────
# Association table linking users who opt in to receive notifications
# for any updates on a specific issue.

class IssueFollower(db.Model):
    __tablename__ = 'issue_followers'

    id         = db.Column(db.Integer, primary_key=True)
    issue_id   = db.Column(db.Integer, db.ForeignKey('issues.id', ondelete='CASCADE'), nullable=False)
    user_id    = db.Column(db.Integer, db.ForeignKey('users.id',  ondelete='CASCADE'), nullable=False)
    created_at = db.Column(db.DateTime, default=now_eastern, nullable=False)

    __table_args__ = (
        db.UniqueConstraint('issue_id', 'user_id', name='uq_issue_follower'),
    )

    user  = db.relationship('User',  foreign_keys=[user_id])
    issue = db.relationship('Issue', foreign_keys=[issue_id], back_populates='followers')

    def __repr__(self):
        return f'<IssueFollower issue={self.issue_id} user={self.user_id}>'



# ── Issue Link ────────────────────────────────────────────────────────────────
# Connects two issues so staff can jump between a duplicate and the original, or
# between issues that are simply about the same thing.

class IssueLink(db.Model):
    """One directed link between two issues, displayed on BOTH of them.

    Only one row is stored per pair. The stored direction carries meaning for
    'duplicate' — issue_id is a duplicate OF linked_issue_id — so the two issues
    read the same row differently:

        on issue_id        -> "Duplicate of #B"
        on linked_issue_id -> "Duplicated by #A"

    'related' is symmetric and reads "Related to" from either side.

    Storing one row rather than a mirrored pair is what keeps the direction
    unambiguous and makes unlinking a single delete. The cost is that uniqueness
    cannot be expressed by the UniqueConstraint alone: (A,B) and (B,A) are
    distinct rows to the database but the same link to a person, so the
    duplicate check has to look in both directions. exists_between() is that
    check, and it is the only thing callers should use.

    A link is PURELY NAVIGATIONAL. Marking a duplicate does not touch either
    issue's status, SLA, assignee or followers — closing the duplicate stays a
    deliberate, separate action.

    Multi-tenant: nothing here is tenant-aware, and deliberately so. The table
    lives in the tenant database and every query routes through RoutingSession,
    so a link can only ever reach an issue in the same tenant. Scope WITHIN a
    tenant is the caller's job — see _readable_links() in routes/issues.py.
    """
    __tablename__ = 'issue_links'

    TYPE_DUPLICATE = 'duplicate'
    TYPE_RELATED   = 'related'

    # How each link type reads from the two sides, keyed by (type, is_source).
    LABELS = {
        ('duplicate', True):  'Duplicate of',
        ('duplicate', False): 'Duplicated by',
        ('related',   True):  'Related to',
        ('related',   False): 'Related to',
    }

    # Offered in the "Link an issue" picker. The value is what gets stored; the
    # phrasing is from the point of view of the issue being viewed.
    TYPE_CHOICES = [
        ('duplicate', 'Duplicate of'),
        ('related',   'Related to'),
    ]

    id              = db.Column(db.Integer, primary_key=True)
    issue_id        = db.Column(db.Integer,
                                db.ForeignKey('issues.id', ondelete='CASCADE'),
                                nullable=False, index=True)
    linked_issue_id = db.Column(db.Integer,
                                db.ForeignKey('issues.id', ondelete='CASCADE'),
                                nullable=False, index=True)
    link_type       = db.Column(db.Enum('duplicate', 'related'),
                                nullable=False, default='related')
    created_by      = db.Column(db.Integer,
                                db.ForeignKey('users.id', ondelete='SET NULL'),
                                nullable=True)
    created_at      = db.Column(db.DateTime, default=now_eastern, nullable=False)

    __table_args__ = (
        # Catches the exact-duplicate row at the database level. The REVERSE
        # direction is caught by exists_between() — see the class docstring.
        db.UniqueConstraint('issue_id', 'linked_issue_id', name='uq_issue_link'),
    )

    # BOTH relationships must pin foreign_keys: two FKs from this table to
    # issues leave the join condition ambiguous otherwise, and the mapper raises
    # on first ORM USE rather than at import — the app starts cleanly and then
    # every request 500s (the phase56 lesson, CLAUDE.md §17).
    issue        = db.relationship('Issue', foreign_keys=[issue_id],
                                   back_populates='links_from')
    linked_issue = db.relationship('Issue', foreign_keys=[linked_issue_id],
                                   back_populates='links_to')
    creator      = db.relationship('User', foreign_keys=[created_by])

    def label_for(self, viewing_issue_id):
        """How this link reads on the issue currently being viewed."""
        return self.LABELS[(self.link_type, self.issue_id == viewing_issue_id)]

    def other_issue(self, viewing_issue_id):
        """The issue at the far end of this link from the one being viewed."""
        return (self.linked_issue if self.issue_id == viewing_issue_id
                else self.issue)

    @staticmethod
    def exists_between(issue_id, other_id):
        """True when the two issues are already linked, in EITHER direction.

        The UniqueConstraint only covers the stored direction, so this is what
        stops #A being linked to #B and then #B linked back to #A as a second,
        contradictory row.
        """
        return db.session.query(
            IssueLink.query.filter(
                db.or_(
                    db.and_(IssueLink.issue_id == issue_id,
                            IssueLink.linked_issue_id == other_id),
                    db.and_(IssueLink.issue_id == other_id,
                            IssueLink.linked_issue_id == issue_id),
                )
            ).exists()
        ).scalar()

    def __repr__(self):
        return (f'<IssueLink {self.issue_id} {self.link_type} '
                f'{self.linked_issue_id}>')


class Issue(db.Model):
    __tablename__ = 'issues'

    id            = db.Column(db.Integer, primary_key=True)
    inspection_id = db.Column(db.Integer, db.ForeignKey('inspections.id'))
    area_id       = db.Column(db.Integer, db.ForeignKey('areas.id'), nullable=True)
    facility_id   = db.Column(db.Integer, db.ForeignKey('facilities.id'), nullable=True)
    severity      = db.Column(db.Enum('low', 'medium', 'high', 'critical'), nullable=False)
    description   = db.Column(db.Text, nullable=False)
    photo_path    = db.Column(db.String(255))
    status        = db.Column(db.Enum('open', 'in_progress', 'resolved', 'pending_verification'), default='open')
    assigned_to   = db.Column(db.Integer, db.ForeignKey('users.id'))
    # Set at creation time to the user who filed the issue (inspector or admin).
    # Nullable for backward compatibility — pre-phase18 rows will be NULL.
    # Used by the mobile API to return issues the inspector created but hasn't
    # been assigned yet (assigned_to is NULL until a director assigns them).
    reported_by   = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    reported_at   = db.Column(db.DateTime, default=now_eastern)
    resolved_at   = db.Column(db.DateTime)
    result_notes  = db.Column(db.Text)
    result_photos = db.Column(db.JSON)   # list of relative paths e.g. ["uploads/issue_photos/abc.jpg"]
    # Extra evidence photos submitted from the iPad at issue-creation time.
    # Stored separately from result_photos (resolution photos added via web)
    # so they display under "Photo Evidence" rather than "Resolution Details".
    mobile_photo_paths = db.Column(db.JSON, nullable=True)

    # ── Resolution verification ──────────────────────────────────────────
    verified_by        = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    verified_at        = db.Column(db.DateTime, nullable=True)
    verification_note  = db.Column(db.Text, nullable=True)

    # Tracks which SLA alert level has already been notified so cron runs
    # don't fire duplicate notifications. Values: None / 'at_risk' / 'breached'
    sla_notified  = db.Column(db.String(10), nullable=True, default=None)
    mobile_local_id = db.Column(db.String(64), nullable=True, index=True)  # idempotency key for mobile submissions

    # External vendor / contractor assignment (phase26)
    vendor_name    = db.Column(db.String(100), nullable=True)
    vendor_contact = db.Column(db.String(200), nullable=True)  # phone or email
    vendor_notes   = db.Column(db.Text, nullable=True)

    # Handler type — who is responsible for resolving the issue (phase39).
    # 'internal' means janitorial staff (the default); 'facility' unlocks the
    # facility_handler_* sub-fields; 'vendor' points to vendor_*.
    # NOT NULL DEFAULT 'internal' since phase44 — previously nullable, with NULL
    # treated as a synonym for 'internal'. Existing NULLs were backfilled by that
    # migration, so the two representations are now one.
    handler_type             = db.Column(
        db.Enum('internal', 'facility', 'vendor'),
        nullable=False, default='internal',
    )
    facility_handler_name    = db.Column(db.String(100), nullable=True)
    facility_handler_contact = db.Column(db.String(200), nullable=True)
    facility_handler_notes   = db.Column(db.Text, nullable=True)
    # Free-text name of the janitorial staff member who will handle the issue,
    # used when handler_type == 'internal'. Distinct from assigned_to (the JQC
    # User who owns follow-up): the actual crew member may not be a system user.
    # (phase44)
    internal_handler_name    = db.Column(db.String(100), nullable=True)
    internal_handler_contact = db.Column(db.String(200), nullable=True)  # phone or email

    # Relationships
    # NOTE: Issue.area is provided by the backref on Area.issues (facility.py).
    # Do NOT add a second explicit db.relationship('Area') here — it conflicts
    # with that backref at mapper configuration time (CLAUDE.md rule 31 revised).
    facility       = db.relationship('Facility', foreign_keys=[facility_id], backref='direct_issues')
    assigned_user  = db.relationship('User', foreign_keys=[assigned_to], backref='assigned_issues')
    reporter       = db.relationship('User', foreign_keys=[reported_by], backref='reported_issues')
    verifier       = db.relationship('User', foreign_keys=[verified_by], backref='verified_issues')
    comments      = db.relationship('IssueComment', backref='issue', lazy='dynamic',
                                    order_by='IssueComment.created_at',
                                    cascade='all, delete-orphan')
    followers     = db.relationship('IssueFollower', back_populates='issue',
                                    cascade='all, delete-orphan', lazy='dynamic')

    # An issue link is stored once and shown on both issues, so each issue has
    # rows pointing OUT of it and rows pointing AT it. Deleting an issue must
    # take its links with it from BOTH sides, or the surviving issue keeps a row
    # referencing one that no longer exists.
    links_from    = db.relationship('IssueLink', back_populates='issue',
                                    foreign_keys='IssueLink.issue_id',
                                    cascade='all, delete-orphan', lazy='dynamic')
    links_to      = db.relationship('IssueLink', back_populates='linked_issue',
                                    foreign_keys='IssueLink.linked_issue_id',
                                    cascade='all, delete-orphan', lazy='dynamic')

    def is_followed_by(self, user):
        """Return True if the given user is currently following this issue."""
        return self.followers.filter_by(user_id=user.id).first() is not None

    def all_links(self):
        """Every link touching this issue, from both directions, newest first.

        The two relationships are a storage detail — a link is one thing to the
        person reading it, so callers get a single list and ask each row how it
        reads via label_for() / other_issue().

        Nothing here filters by permission. The caller MUST drop links whose far
        end the viewer cannot access, or a link becomes a way to read an issue
        at a facility they hold no assignment to. See _readable_links() in
        routes/issues.py.
        """
        links = list(self.links_from) + list(self.links_to)
        links.sort(key=lambda link: link.created_at, reverse=True)
        return links

    # Display labels for handler_type. The web templates hardcode these inline;
    # this mapping exists so the mobile API can return a human-readable label
    # without the client duplicating the strings. (phase43)
    HANDLER_LABELS = {
        'internal': 'Janitorial Staff',
        'facility': 'Facility Staff',
        'vendor':   'External Vendor',
    }
    # One-line explanation per handler type, shown under the radio options on the
    # issue form so staff pick the right one. (phase44)
    HANDLER_DESCRIPTIONS = {
        'internal': 'Our janitorial crew handles it.',
        'facility': "The facility's own on-site staff handle it.",
        'vendor':   'An outside contractor handles it.',
    }

    @property
    def handler_label(self):
        """Human-readable label for handler_type; defaults to internal."""
        return self.HANDLER_LABELS.get(self.handler_type or 'internal', 'Janitorial Staff')

    @property
    def resolved_facility(self):
        """Returns the Facility for this issue regardless of which path was used to create it.
        Issues created via the standalone form have facility_id set directly.
        Issues created via flag_issue (from an inspection) have area_id set.
        """
        if self.facility:
            return self.facility
        if self.area:
            return self.area.facility
        return None

    def __repr__(self):
        return f'<Issue {self.id} - {self.severity}>'