app/database/models_intents.py

"""
Database models for the ARF API Control Plane.

This module defines the SQLAlchemy ORM models for:
    - Intents (InfrastructureIntent evaluations)
    - Outcomes (recorded results of executed intents)
    - Beta state (conjugate Bayesian posteriors per tenant and category)
    - Audit logs (immutable decision records for compliance)
    - Tenants (multi‑tenant isolation)

All tables include a `tenant_id` column to enforce data partitioning.
The BetaStateDB now stores parameters per (tenant, category) pair.
"""

from sqlalchemy import (
    Column, Integer, String, DateTime, Boolean, Text, JSON,
    Float, ForeignKey, UniqueConstraint, Index
)
from sqlalchemy.orm import relationship
import datetime
from .base import Base


# ============================================================================
# Tenant table – root of multi‑tenancy
# ============================================================================

class TenantDB(Base):
    """
    Represents a customer tenant (organisation). All other tables
    reference this table via a foreign key `tenant_id`.

    Attributes:
        id (str): UUID of the tenant (primary key).
        name (str): Human‑readable organisation name.
        created_at (datetime): UTC timestamp of creation.
        created_by (str, optional): Email or user ID of the creator.
    """
    __tablename__ = "tenants"

    id = Column(String(64), primary_key=True, index=True)
    name = Column(String(256), nullable=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
    created_by = Column(String(128), nullable=True)

    # Relationships
    api_keys = relationship("APIKeyDB", back_populates="tenant", cascade="all, delete-orphan")
    intents = relationship("IntentDB", back_populates="tenant")
    beta_states = relationship("BetaStateDB", back_populates="tenant")
    audit_logs = relationship("DecisionAuditLogDB", back_populates="tenant")


# ============================================================================
# API keys (extended with tenant_id)
# ============================================================================

class APIKeyDB(Base):
    """
    Stores API keys for authentication and tiered quota. Each key belongs
    to exactly one tenant. The `tier` determines monthly evaluation limits.

    Attributes:
        key (str): The hashed API key (primary key).
        tenant_id (str): Foreign key to `tenants.id`.
        tier (str): Tier enumeration value (free, pro, premium, enterprise).
        created_at (datetime): UTC creation time.
        last_used_at (datetime, optional): Timestamp of last successful request.
        is_active (bool): Soft‑delete flag.
    """
    __tablename__ = "api_keys"

    key = Column(String(256), primary_key=True, index=True)
    tenant_id = Column(String(64), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    tier = Column(String(32), nullable=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
    last_used_at = Column(DateTime, nullable=True)
    is_active = Column(Boolean, default=True, nullable=False)

    # Relationships
    tenant = relationship("TenantDB", back_populates="api_keys")
    usage_logs = relationship("UsageLogDB", back_populates="api_key")


# ============================================================================
# Intents (evaluations) – now tenant‑scoped
# ============================================================================

class IntentDB(Base):
    """
    Stores each InfrastructureIntent evaluation request and its resulting
    risk score. One‑to‑many with OutcomeDB.

    Attributes:
        id (int): Auto‑increment primary key.
        deterministic_id (str): Client‑provided idempotency identifier (unique).
        tenant_id (str): Tenant that owns this intent.
        intent_type (str): Type of intent (e.g., "provision_resource").
        payload (JSON): Original API request payload.
        oss_payload (JSON): Canonical OSS intent representation.
        environment (str, optional): Environment label (prod, staging, etc.).
        created_at (datetime): UTC timestamp of evaluation.
        evaluated_at (datetime, optional): When the risk engine processed it.
        risk_score (str, optional): String representation of the risk score.
    """
    __tablename__ = "intents"

    id = Column(Integer, primary_key=True, index=True)
    deterministic_id = Column(String(64), unique=True, index=True, nullable=False)
    tenant_id = Column(String(64), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    intent_type = Column(String(64), nullable=False)
    payload = Column(JSON, nullable=False)
    oss_payload = Column(JSON, nullable=True)
    environment = Column(String(32), nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
    evaluated_at = Column(DateTime, nullable=True)
    risk_score = Column(String(32), nullable=True)

    # Relationships
    tenant = relationship("TenantDB", back_populates="intents")
    outcomes = relationship("OutcomeDB", back_populates="intent", cascade="all, delete-orphan")


class OutcomeDB(Base):
    """
    Records the outcome (success/failure) of a previously evaluated intent.
    Only one outcome per intent is allowed (unique constraint on intent_id).

    Attributes:
        id (int): Primary key.
        intent_id (int): Foreign key to `intents.id`.
        success (bool): Whether the executed action succeeded.
        recorded_by (str, optional): Identity of the caller (e.g., API key owner).
        notes (str, optional): Free‑text notes.
        recorded_at (datetime): UTC timestamp.
        idempotency_key (str, optional): Unique idempotency key for this outcome.
    """
    __tablename__ = "intent_outcomes"

    id = Column(Integer, primary_key=True, index=True)
    intent_id = Column(Integer, ForeignKey("intents.id", ondelete="CASCADE"), nullable=False)
    success = Column(Boolean, nullable=False)
    recorded_by = Column(String(128), nullable=True)
    notes = Column(Text, nullable=True)
    recorded_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
    idempotency_key = Column(String(128), unique=True, nullable=True)

    intent = relationship("IntentDB", back_populates="outcomes")

    __table_args__ = (
        UniqueConstraint("intent_id", name="uq_outcome_intentid"),
    )


# ============================================================================
# Bayesian conjugate state – now per tenant and per category
# ============================================================================

class BetaStateDB(Base):
    """
    Stores the posterior parameters (α, β) of the conjugate Beta model
    for each (tenant, category) pair. This allows online learning to be
    isolated per customer.

    Attributes:
        id (int): Primary key.
        tenant_id (str): Tenant that owns this state.
        category (str): ActionCategory value (e.g., "database", "compute").
        alpha (float): α parameter of the Beta distribution.
        beta (float): β parameter of the Beta distribution.
        updated_at (datetime): Last update timestamp (auto‑set).
    """
    __tablename__ = "beta_state"

    id = Column(Integer, primary_key=True, index=True)
    tenant_id = Column(String(64), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    category = Column(String(32), nullable=False, index=True)
    alpha = Column(Float, nullable=False)
    beta = Column(Float, nullable=False)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    # Composite unique constraint: (tenant_id, category)
    __table_args__ = (
        UniqueConstraint("tenant_id", "category", name="uq_beta_state_tenant_category"),
    )

    # Relationships
    tenant = relationship("TenantDB", back_populates="beta_states")


# ============================================================================
# NEW: Audit log for compliance (immutable decision records)
# ============================================================================

class DecisionAuditLogDB(Base):
    """
    Immutable, tamper‑evident record of every governance decision.
    Designed for compliance (SOC2, ISO) and forensic analysis.

    Attributes:
        id (str): UUID primary key.
        tenant_id (str): Tenant that owns the decision.
        deterministic_id (str): Intent identifier (idempotency key).
        timestamp (datetime): UTC decision time.
        risk_score (float): Fused Bayesian risk score (0‑1).
        action (str): Selected action (approve, deny, escalate).
        justification (str): Human‑readable explanation.
        memory_success_rate (float, optional): Memory‑based correction value.
        memory_weight (float, optional): Weight assigned to memory.
        counterfactual (JSON, optional): Structured counterfactual explanation.
        trace_id (str, optional): OpenTelemetry trace ID for debugging.
        signature (str, optional): Ed25519 signature for tamper‑proofing.
    """
    __tablename__ = "decision_audit_log"

    id = Column(String(64), primary_key=True, default=lambda: str(uuid.uuid4()))
    tenant_id = Column(String(64), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    deterministic_id = Column(String(64), nullable=False, index=True)
    timestamp = Column(DateTime, default=datetime.datetime.utcnow, nullable=False, index=True)
    risk_score = Column(Float, nullable=False)
    action = Column(String(32), nullable=False)
    justification = Column(Text, nullable=False)
    memory_success_rate = Column(Float, nullable=True)
    memory_weight = Column(Float, nullable=True)
    counterfactual = Column(JSON, nullable=True)
    trace_id = Column(String(128), nullable=True)
    signature = Column(String(256), nullable=True)

    # Composite index for fast filtered queries
    __table_args__ = (
        Index("idx_audit_tenant_time", "tenant_id", "timestamp"),
    )

    tenant = relationship("TenantDB", back_populates="audit_logs")