admin / Synapse-Cortex

import enum
import uuid
from datetime import datetime, timezone
from typing import Optional

from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Enum,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
    text,
)
from sqlalchemy.dialects.postgresql import JSON, UUID
from sqlalchemy.orm import relationship

from .database import Base


def _enum_column(enum_cls, name: str):
    """Enum column that persists the lowercase .value instead of the member name."""
    return Enum(enum_cls, name=name, values_callable=lambda e: [member.value for member in e])


class UserRole(str, enum.Enum):
    GLOBAL_ADMIN = "global_admin"
    TENANT_ADMIN = "tenant_admin"
    AGENT = "agent"
    REQUESTER = "requester"


class TicketType(str, enum.Enum):
    INCIDENT = "incident"
    REQUEST = "request"


class TicketPriority(str, enum.Enum):
    CRITICAL = "critical"
    HIGH = "high"
    MEDIUM = "medium"
    LOW = "low"


class TicketStatus(str, enum.Enum):
    NEW = "new"
    IN_PROGRESS = "in_progress"
    AWAITING_USER = "awaiting_user"
    RESOLVED = "resolved"
    CLOSED = "closed"


# Asset types used to be a fixed enum; they're now a tenant-editable table
# (AssetTypeOption below) so admins can rename/add/remove categories. These
# are only the names seeded into that table for every new tenant at setup -
# not a constraint on what values are allowed afterward.
DEFAULT_ASSET_TYPE_NAMES = (
    "Server",
    "Workstation",
    "Laptop",
    "Network Device",
    "Mobile",
    "Printer",
    "Other",
)


class AssetStatus(str, enum.Enum):
    IN_SERVICE = "in_service"
    IN_STOCK = "in_stock"
    IN_REPAIR = "in_repair"
    RETIRED = "retired"


class AssetSource(str, enum.Enum):
    MANUAL = "manual"
    NETSCANXI = "netscanxi"


class TicketSource(str, enum.Enum):
    MANUAL = "manual"
    NETSCANXI = "netscanxi"


class ApprovalLevel(str, enum.Enum):
    """Guardrail on a Playbook: HUMAN_IN_THE_LOOP (default) always blocks
    execution on an explicit Approve/Reject click. AUTO_APPROVE proceeds to
    execution immediately once the AI selects the playbook - it still always
    posts the proposal *and* the outcome to the ticket's Actions Taken log,
    but skips the click. Only global_admin can create/edit an AUTO_APPROVE
    playbook, and it only takes effect at all when ALLOW_AUTO_APPROVE=true
    (see app/ai/guardrails.py) - both are deliberate extra friction on top of
    the highest-privilege role, since this is the one path that lets a
    playbook run unattended."""

    AUTO_APPROVE = "auto_approve"
    HUMAN_IN_THE_LOOP = "human_in_the_loop"


class RemediationRunStatus(str, enum.Enum):
    INVESTIGATING = "investigating"
    PENDING_APPROVAL = "pending_approval"
    EXECUTING = "executing"
    SUCCEEDED = "succeeded"
    FAILED = "failed"
    REJECTED = "rejected"
    BLOCKED = "blocked"


class Tenant(Base):
    __tablename__ = "tenants"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(120), unique=True, nullable=False)
    slug = Column(String(60), unique=True, nullable=False)
    ai_enabled = Column(Boolean, nullable=False, default=False, server_default=text("false"))
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    # Credential vault: a Fernet key generated once via Admin -> Vault,
    # wrapped (encrypted) with the deployment's SECRET_KEY before being
    # stored here so a DB leak alone never exposes it. Deliberately has no
    # rotate/recreate path anywhere in the app - see app/ai/vault.py.
    vault_key_wrapped = Column(Text, nullable=True)
    vault_created_at = Column(DateTime(timezone=True), nullable=True)

    # Claude API key for the AI investigator, encrypted at rest with the
    # tenant's vault key (so it can only be saved once a vault exists).
    # Falls back to the ANTHROPIC_API_KEY environment variable when unset -
    # see app/ai/investigator.py.
    anthropic_api_key_encrypted = Column(Text, nullable=True)
    anthropic_key_last_test_ok = Column(Boolean, nullable=True)
    anthropic_key_last_tested_at = Column(DateTime(timezone=True), nullable=True)

    users = relationship("User", back_populates="tenant", cascade="all, delete-orphan")
    assets = relationship("Asset", back_populates="tenant", cascade="all, delete-orphan")
    tickets = relationship("Ticket", back_populates="tenant", cascade="all, delete-orphan")
    sla_policies = relationship("SLAPolicy", back_populates="tenant", cascade="all, delete-orphan")


class User(Base):
    __tablename__ = "users"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    email = Column(String(255), unique=True, nullable=False, index=True)
    full_name = Column(String(120), nullable=False)
    hashed_password = Column(String(255), nullable=False)
    role = Column(_enum_column(UserRole, "user_role"), nullable=False, default=UserRole.REQUESTER)
    is_active = Column(Boolean, nullable=False, default=True)
    mfa_enabled = Column(Boolean, nullable=False, default=False)
    mfa_secret = Column(String(32), nullable=True)
    mfa_required = Column(Boolean, nullable=False, default=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    tenant = relationship("Tenant", back_populates="users")
    assigned_tickets = relationship(
        "Ticket", foreign_keys="Ticket.assigned_to_id", back_populates="assigned_to"
    )
    requested_tickets = relationship(
        "Ticket", foreign_keys="Ticket.requester_id", back_populates="requester"
    )
    assets_assigned = relationship("Asset", back_populates="assigned_to")


class AssetTypeOption(Base):
    """Admin-managed asset category. Seeded with DEFAULT_ASSET_TYPE_NAMES for
    every new tenant, but fully editable afterward - rename, add, or delete
    (deletion is blocked at the DB level while any Asset still references
    it, via the plain FK on Asset.asset_type_id below)."""

    __tablename__ = "asset_type_options"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    name = Column(String(80), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    tenant = relationship("Tenant")

    __table_args__ = (UniqueConstraint("tenant_id", "name", name="uq_asset_type_tenant_name"),)


class Asset(Base):
    __tablename__ = "assets"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    asset_id = Column(String(32), unique=True, nullable=False, index=True)
    name = Column(String(120), nullable=False)
    asset_type_id = Column(UUID(as_uuid=True), ForeignKey("asset_type_options.id"), nullable=False)
    mac_address = Column(String(17), nullable=True)
    ip_address = Column(String(45), nullable=True)
    status = Column(_enum_column(AssetStatus, "asset_status"), nullable=False, default=AssetStatus.IN_STOCK)
    location = Column(String(120), nullable=True)
    source = Column(
        _enum_column(AssetSource, "asset_source"),
        nullable=False,
        default=AssetSource.MANUAL,
        server_default=text("'manual'"),
    )
    os = Column(String(120), nullable=True)
    software = Column(JSON, nullable=True)
    # Free-text notes about the client/customer this asset belongs to and any
    # operational context the AI investigator should weigh before proposing a
    # fix (e.g. "Production DB - never reboot in business hours", "Client on
    # 24/7 SLA", "Standalone box, safe to take offline"). Purely advisory to a
    # human, but also fed into the AI's ticket-triage prompt when present.
    client_context = Column(Text, nullable=True)
    netscanxi_device_type = Column(String(60), nullable=True)
    assigned_to_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    tenant = relationship("Tenant", back_populates="assets")
    assigned_to = relationship("User", back_populates="assets_assigned")
    tickets = relationship("Ticket", back_populates="asset")
    asset_type_option = relationship("AssetTypeOption")

    @property
    def asset_type_name(self):
        return self.asset_type_option.name if self.asset_type_option else None

    __table_args__ = (
        # Partial unique index: enforce "no two assets share a MAC within a
        # tenant" only when a MAC is actually recorded, since NetscanXi also
        # reports IP-only devices with no MAC.
        Index(
            "uq_asset_tenant_mac",
            "tenant_id",
            "mac_address",
            unique=True,
            postgresql_where=mac_address.isnot(None),
        ),
    )


class SLAPolicy(Base):
    __tablename__ = "sla_policies"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    priority = Column(_enum_column(TicketPriority, "ticket_priority"), nullable=False)
    response_time_minutes = Column(Integer, nullable=False)
    resolution_time_minutes = Column(Integer, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    tenant = relationship("Tenant", back_populates="sla_policies")

    __table_args__ = (
        UniqueConstraint("tenant_id", "priority", name="uq_sla_tenant_priority"),
    )


class Ticket(Base):
    __tablename__ = "tickets"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    ticket_number = Column(String(20), nullable=False, index=True)
    title = Column(String(200), nullable=False)
    description = Column(Text, nullable=True)
    ticket_type = Column(_enum_column(TicketType, "ticket_type"), nullable=False, default=TicketType.INCIDENT)
    priority = Column(_enum_column(TicketPriority, "ticket_priority"), nullable=False, default=TicketPriority.MEDIUM)
    status = Column(_enum_column(TicketStatus, "ticket_status"), nullable=False, default=TicketStatus.NEW)
    requester_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    assigned_to_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    asset_id = Column(UUID(as_uuid=True), ForeignKey("assets.id", ondelete="SET NULL"), nullable=True)
    sla_policy_id = Column(UUID(as_uuid=True), ForeignKey("sla_policies.id", ondelete="SET NULL"), nullable=True)
    response_due_at = Column(DateTime(timezone=True), nullable=True)
    resolution_due_at = Column(DateTime(timezone=True), nullable=True)
    resolved_at = Column(DateTime(timezone=True), nullable=True)
    source = Column(
        _enum_column(TicketSource, "ticket_source"),
        nullable=False,
        default=TicketSource.MANUAL,
        server_default=text("'manual'"),
    )
    external_ref = Column(String(150), nullable=True)
    # Reference only, per the "ticket stores a reference ID, never the
    # secret" requirement - the actual credential is decrypted exclusively
    # inside the remediation execution path (see app/ai/vault.py).
    credential_ref_id = Column(
        UUID(as_uuid=True), ForeignKey("credential_vault_entries.id", ondelete="SET NULL"), nullable=True
    )
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    tenant = relationship("Tenant", back_populates="tickets")
    requester = relationship("User", foreign_keys=[requester_id], back_populates="requested_tickets")
    assigned_to = relationship("User", foreign_keys=[assigned_to_id], back_populates="assigned_tickets")
    asset = relationship("Asset", back_populates="tickets")
    sla_policy = relationship("SLAPolicy")
    credential_ref = relationship("CredentialVaultEntry")

    __table_args__ = (
        UniqueConstraint("tenant_id", "ticket_number", name="uq_ticket_tenant_number"),
        # Partial unique index: dedupe key for auto-imported tickets (e.g. a
        # NetscanXi remediation item id) only enforced when actually set -
        # manually-created tickets never have one. Mirrors Asset.mac_address.
        Index(
            "uq_ticket_tenant_external_ref",
            "tenant_id",
            "external_ref",
            unique=True,
            postgresql_where=external_ref.isnot(None),
        ),
    )

    @property
    def is_sla_breaching(self) -> bool:
        if self.status in (TicketStatus.RESOLVED, TicketStatus.CLOSED):
            return False
        due_at = self.resolution_due_at
        if not due_at:
            return False
        if due_at.tzinfo is None:
            due_at = due_at.replace(tzinfo=timezone.utc)
        return datetime.now(timezone.utc) >= due_at

    # Derived, read-only convenience fields surfaced on TicketOut - always
    # sourced live from the linked Asset/CredentialVaultEntry rather than
    # duplicated onto the ticket itself, so there's one place these can ever
    # go stale. The password itself is never exposed here or anywhere else -
    # only whether one is configured (see app/ai/vault.py's decrypt-only-
    # immediately-before-use discipline).
    @property
    def asset_ip_address(self) -> Optional[str]:
        return self.asset.ip_address if self.asset else None

    @property
    def credential_username(self) -> Optional[str]:
        return self.credential_ref.username if self.credential_ref else None

    @property
    def credential_configured(self) -> bool:
        return self.credential_ref_id is not None


class TicketAction(Base):
    """A free-text action-taken entry an agent logs against a ticket -
    timestamped and attributed to whoever wrote it. Append-only: there is no
    edit/delete API for these, they're a record of what was actually done."""

    __tablename__ = "ticket_actions"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    ticket_id = Column(UUID(as_uuid=True), ForeignKey("tickets.id", ondelete="CASCADE"), nullable=False)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    body = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    ticket = relationship("Ticket")
    user = relationship("User")

    __table_args__ = (Index("ix_ticket_actions_ticket_created", "ticket_id", "created_at"),)

    @property
    def user_name(self):
        return self.user.full_name if self.user else None


class IntegrationKey(Base):
    """A Cortex-issued API key that lets NetscanXi push asset data into the
    tenant's inbound ingest endpoint. One active row per tenant; regenerating
    overwrites/invalidates the previous key immediately."""

    __tablename__ = "integration_keys"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, unique=True)
    key_hash = Column(String(64), nullable=False)
    enabled = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    last_used_at = Column(DateTime(timezone=True), nullable=True)
    created_by_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)

    tenant = relationship("Tenant")
    created_by = relationship("User")


class AuditLog(Base):
    __tablename__ = "audit_logs"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    action = Column(String(60), nullable=False)
    detail = Column(JSON, nullable=True)
    ip_address = Column(String(45), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    tenant = relationship("Tenant")
    user = relationship("User")

    __table_args__ = (
        Index("ix_audit_tenant_created", "tenant_id", "created_at"),
        Index("ix_audit_tenant_action", "tenant_id", "action"),
    )


class Playbook(Base):
    """An admin-authored remediation procedure: a visual node/edge graph
    (built in the React Flow UI, stored as-is) plus guardrails that the
    execution engine enforces server-side and cannot be bypassed by the
    graph content. Guardrails are normalized columns, not nested in
    graph_json, since the engine consumes them as flat fields and this
    keeps "what runs" (the graph) separate from "what is allowed" (policy)."""

    __tablename__ = "playbooks"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    name = Column(String(120), nullable=False)
    enabled = Column(Boolean, nullable=False, default=True)
    graph_json = Column(JSON, nullable=False, default=dict)
    allowed_target_os = Column(JSON, nullable=False, default=list)
    required_approval_level = Column(
        _enum_column(ApprovalLevel, "approval_level"),
        nullable=False,
        default=ApprovalLevel.HUMAN_IN_THE_LOOP,
        server_default=text("'human_in_the_loop'"),
    )
    forbidden_commands = Column(JSON, nullable=False, default=list)
    # Review-eligible baseline categories (e.g. "host_power") that a
    # global_admin has explicitly acknowledged for THIS playbook. A command
    # matching an acknowledged category is not auto-blocked; it is instead
    # routed to mandatory human review (see app/ai/guardrails.py). Empty by
    # default - an ordinary playbook acknowledges nothing.
    acknowledged_dangerous_commands = Column(
        JSON, nullable=False, default=list, server_default=text("'[]'")
    )
    created_by_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    tenant = relationship("Tenant")
    created_by = relationship("User")

    __table_args__ = (UniqueConstraint("tenant_id", "name", name="uq_playbook_tenant_name"),)


class CredentialVaultEntry(Base):
    """A securely-vaulted host credential an agent can link to a ticket by
    reference (Ticket.credential_ref_id). The secret is Fernet-encrypted at
    rest (app/ai/vault.py) and never appears in any API response - only the
    remediation execution path ever decrypts it, in-memory, immediately
    before dispatching a command."""

    __tablename__ = "credential_vault_entries"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    label = Column(String(120), nullable=False)
    username = Column(String(120), nullable=False)
    host = Column(String(255), nullable=False)
    port = Column(Integer, nullable=False, default=22, server_default=text("22"))
    secret_encrypted = Column(Text, nullable=False)
    # Cross-reference key for auto-matching: when set, this credential is
    # "the" credential for this asset (enforced by a partial unique index
    # below) - a ticket linked to the same asset can be auto-linked to it
    # without the agent re-entering it. Optional: credentials not tied to
    # any asset (the original free-form label/host flow) still work as-is.
    asset_id = Column(UUID(as_uuid=True), ForeignKey("assets.id", ondelete="SET NULL"), nullable=True)
    created_by_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    tenant = relationship("Tenant")
    created_by = relationship("User")
    asset = relationship("Asset")

    __table_args__ = (
        Index(
            "uq_credential_tenant_asset",
            "tenant_id",
            "asset_id",
            unique=True,
            postgresql_where=text("asset_id IS NOT NULL"),
            sqlite_where=text("asset_id IS NOT NULL"),
        ),
    )

    __table_args__ = (UniqueConstraint("tenant_id", "label", name="uq_credential_tenant_label"),)

    @property
    def masked_hint(self) -> str:
        return f"{self.username}@{self.host}"


class RemediationRun(Base):
    """One AI-investigation-through-execution cycle for a ticket. Snapshots
    the playbook's name/guardrails/graph at proposal time so a later edit to
    the playbook can never retroactively rewrite what a ticket's permanent
    history says was actually applied."""

    __tablename__ = "remediation_runs"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    ticket_id = Column(UUID(as_uuid=True), ForeignKey("tickets.id", ondelete="CASCADE"), nullable=False)
    playbook_id = Column(UUID(as_uuid=True), ForeignKey("playbooks.id", ondelete="SET NULL"), nullable=True)
    status = Column(
        _enum_column(RemediationRunStatus, "remediation_run_status"),
        nullable=False,
        default=RemediationRunStatus.INVESTIGATING,
    )
    ai_summary = Column(Text, nullable=True)
    proposed_plan = Column(JSON, nullable=True)
    execution_log = Column(JSON, nullable=True)
    outcome_summary = Column(Text, nullable=True)
    approved_by_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    approved_at = Column(DateTime(timezone=True), nullable=True)

    # Immutable snapshot of the playbook as it existed at proposal time.
    playbook_name_snapshot = Column(String(120), nullable=True)
    guardrails_snapshot = Column(JSON, nullable=True)
    graph_snapshot = Column(JSON, nullable=True)

    # For a command-runner playbook only: the free-form command the AI
    # proposed (which the graph's {{command}} token is replaced with). It's
    # the editable default shown on the approval card; the approver may
    # change it, and whatever they approve is re-checked by the guardrails
    # before it runs. Never the real credential secret - see remediation.py.
    suggested_command = Column(Text, nullable=True)

    created_by_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    tenant = relationship("Tenant")
    ticket = relationship("Ticket")
    playbook = relationship("Playbook")
    approved_by = relationship("User", foreign_keys=[approved_by_id])
    created_by = relationship("User", foreign_keys=[created_by_id])

    __table_args__ = (Index("ix_remediation_runs_ticket_created", "ticket_id", "created_at"),)