"""
SQLAlchemy ORM Models

Defines database schema for AegisLM evaluation framework.
Tables: tenants, users, evaluation_runs, evaluation_results, model_versions, 
dataset_versions, monitoring_metrics, alerts, release_validations, api_keys, 
workers, audit_logs
"""

import uuid
from datetime import datetime
from typing import Any, Dict, Optional

from sqlalchemy import (
    JSON,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    String,
    Text,
    Uuid,
    Boolean,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    """Base class for all SQLAlchemy models."""

    pass


# =============================================================================
# Table 0: tenants (Multi-tenant isolation)
# =============================================================================

class Tenant(Base):
    """
    Represents a tenant organization in the multi-tenant system.
    
    Each tenant is an organization (company/team) that owns its own
    users, API keys, models, evaluation jobs, monitoring data, and reports.
    """

    __tablename__ = "tenants"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the tenant"
    )

    name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        index=True,
        doc="Tenant organization name"
    )

    plan_type: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        default="free",
        doc="Plan type: free, basic, pro, enterprise"
    )

    job_quota: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        default=10,
        doc="Maximum number of concurrent jobs allowed"
    )

    api_rate_limit: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        default=100,
        doc="API requests per minute limit"
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When the tenant was created"
    )

    active: Mapped[bool] = mapped_column(
        default=True,
        index=True,
        doc="Whether this tenant is active"
    )

    # Tenant metadata
    tenant_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional tenant metadata"
    )

    # Relationships
    users: Mapped[list["User"]] = relationship(
        "User",
        back_populates="tenant",
        cascade="all, delete-orphan",
        doc="Users belonging to this tenant"
    )

    api_keys: Mapped[list["APIKey"]] = relationship(
        "APIKey",
        back_populates="tenant",
        cascade="all, delete-orphan",
        doc="API keys belonging to this tenant"
    )

    evaluation_runs: Mapped[list["EvaluationRun"]] = relationship(
        "EvaluationRun",
        back_populates="tenant",
        cascade="all, delete-orphan",
        doc="Evaluation runs belonging to this tenant"
    )

    def __repr__(self) -> str:
        return f"<Tenant(id={self.id}, name={self.name}, plan={self.plan_type})>"


# =============================================================================
# Table 0b: users (User management per tenant)
# =============================================================================

class User(Base):
    """
    Represents a user within a tenant organization.
    
    Each user belongs to a tenant and has a specific role that determines
    their access permissions within the platform.
    """

    __tablename__ = "users"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the user"
    )

    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant"
    )

    email: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        index=True,
        doc="User email address"
    )

    role: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        default="viewer",
        doc="User role: admin, engineer, viewer, api_client"
    )

    password_hash: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Hashed password"
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When the user was created"
    )

    active: Mapped[bool] = mapped_column(
        default=True,
        index=True,
        doc="Whether this user is active"
    )

    last_login: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="Last login timestamp"
    )

    # User metadata
    user_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional user metadata"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        back_populates="users",
        doc="Parent tenant"
    )

    audit_logs: Mapped[list["AuditLog"]] = relationship(
        "AuditLog",
        back_populates="user",
        cascade="all, delete-orphan",
        doc="Audit logs for this user"
    )

    def __repr__(self) -> str:
        return f"<User(id={self.id}, email={self.email}, role={self.role})>"


# =============================================================================
# Table 1: evaluation_runs (updated with tenant_id)
# =============================================================================

class EvaluationRun(Base):
    """
    Represents a single evaluation run.
    
    Tracks the overall lifecycle and results of evaluating a model
    against a specific dataset version.
    """

    __tablename__ = "evaluation_runs"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the evaluation run"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    timestamp: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When the evaluation run was initiated"
    )

    model_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Name of the model being evaluated"
    )

    model_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="Version identifier of the model"
    )

    dataset_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="Version of the dataset used"
    )

    status: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        default="pending",
        doc="Status: pending, running, completed, failed, cancelled"
    )

    config_hash: Mapped[str] = mapped_column(
        String(64),
        nullable=False,
        doc="SHA256 hash of configuration for reproducibility"
    )

    composite_score: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Final composite robustness score (0-1)"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        back_populates="evaluation_runs",
        doc="Parent tenant"
    )

    results: Mapped[list["EvaluationResult"]] = relationship(
        "EvaluationResult",
        back_populates="run",
        cascade="all, delete-orphan",
        doc="Individual evaluation results for each sample"
    )

    def __repr__(self) -> str:
        return f"<EvaluationRun(id={self.id}, model={self.model_name}, status={self.status})>"


# =============================================================================
# Table 2: evaluation_results
# =============================================================================

class EvaluationResult(Base):
    """
    Individual evaluation result for a single sample.
    
    Contains scores for each metric (hallucination, toxicity, bias, confidence)
    and the raw model output.
    """

    __tablename__ = "evaluation_results"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the result"
    )

    run_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("evaluation_runs.id", ondelete="CASCADE"),
        nullable=False,
        doc="Reference to parent evaluation run"
    )

    sample_id: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Identifier of the sample from the dataset"
    )

    attack_type: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        doc="Type of attack applied (if any)"
    )

    mutation_type: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        doc="Type of mutation applied (if any)"
    )

    # Scoring metrics (all in [0, 1] range)
    hallucination: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Hallucination score (0 = no hallucination, 1 = full hallucination)"
    )

    toxicity: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Toxicity score (0 = non-toxic, 1 = toxic)"
    )

    bias: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Bias score (0 = unbiased, 1 = biased)"
    )

    confidence: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Model confidence score (0 = low, 1 = high)"
    )

    # Computed robustness score
    robustness: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Computed robustness score for this sample"
    )

    # Raw outputs
    raw_output: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        doc="Raw model output text"
    )

    processed_prompt: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        doc="Prompt after attack/mutation processing"
    )

    # Metadata
    result_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional result metadata"
    )

    processing_time_ms: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Time taken to process this sample in milliseconds"
    )

    # Relationships
    run: Mapped["EvaluationRun"] = relationship(
        "EvaluationRun",
        back_populates="results",
        doc="Parent evaluation run"
    )

    def __repr__(self) -> str:
        return f"<EvaluationResult(id={self.id}, sample={self.sample_id})>"


# =============================================================================
# Table 3: model_versions
# =============================================================================

class ModelVersion(Base):
    """
    Tracks registered model versions.
    
    Stores metadata about models that have been registered
    for evaluation in the system.
    """

    __tablename__ = "model_versions"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier"
    )

    model_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        index=True,
        doc="Model name (e.g., meta-llama/Llama-2-7b-hf)"
    )

    model_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="Version string (e.g., v1.0, latest)"
    )

    parameters: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Model parameters (num_params, vocab_size, etc.)"
    )

    checksum: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        doc="SHA256 checksum of model weights"
    )

    registered_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When this model version was registered"
    )

    last_used: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="When this model was last used in evaluation"
    )

    is_active: Mapped[bool] = mapped_column(
        default=True,
        doc="Whether this model version is available for evaluation"
    )

    def __repr__(self) -> str:
        return f"<ModelVersion(name={self.model_name}, version={self.model_version})>"


# =============================================================================
# Table 4: dataset_versions
# =============================================================================

class DatasetVersion(Base):
    """
    Tracks registered dataset versions.
    
    Stores metadata about datasets that have been registered
    for evaluation in the system.
    """

    __tablename__ = "dataset_versions"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier"
    )

    dataset_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        index=True,
        doc="Dataset name (e.g., aegislm-harmful-queries)"
    )

    version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="Version string (e.g., v1.0)"
    )

    checksum: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        doc="SHA256 checksum of dataset file"
    )

    dataset_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional dataset metadata (num_samples, categories, etc.)"
    )

    registered_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When this dataset version was registered"
    )

    is_active: Mapped[bool] = mapped_column(
        default=True,
        doc="Whether this dataset version is available for evaluation"
    )

    def __repr__(self) -> str:
        return f"<DatasetVersion(name={self.dataset_name}, version={self.version})>"


# =============================================================================
# Table 5: monitoring_metrics (updated with tenant_id)
# =============================================================================

class MonitoringMetric(Base):
    """
    Stores per-inference metrics for continuous monitoring.
    
    Tracks real-time evaluation results for drift detection
    and longitudinal robustness tracking.
    """

    __tablename__ = "monitoring_metrics"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the metric"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    timestamp: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        index=True,
        doc="When this metric was recorded"
    )

    model_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Name of the model being monitored"
    )

    model_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        index=True,
        doc="Version of the model"
    )

    # Evaluation metrics (all in [0, 1] range)
    hallucination: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Hallucination score (0 = no hallucination, 1 = full hallucination)"
    )

    toxicity: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Toxicity score (0 = non-toxic, 1 = toxic)"
    )

    bias: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Bias score (0 = unbiased, 1 = biased)"
    )

    confidence: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Model confidence score (0 = low, 1 = high)"
    )

    robustness: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Composite robustness score (0-1)"
    )

    # Metadata
    category: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        index=True,
        doc="User category for segmentation"
    )

    processing_time_ms: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Time taken to process this inference in milliseconds"
    )

    request_id: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        index=True,
        doc="Request identifier for tracing"
    )

    metric_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional metric metadata"
    )

    def __repr__(self) -> str:
        return f"<MonitoringMetric(id={self.id}, model={self.model_name}, timestamp={self.timestamp})>"


# =============================================================================
# Table 6: alerts (updated with tenant_id)
# =============================================================================

class Alert(Base):
    """
    Stores alerts generated from drift detection.
    
    Tracks detected drifts and their resolution status.
    """

    __tablename__ = "alerts"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the alert"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    alert_type: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        index=True,
        doc="Type of alert (hallucination_drift, toxicity_drift, etc.)"
    )

    severity: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        doc="Severity level: low, medium, high, critical"
    )

    model_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Model being monitored"
    )

    model_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        index=True,
        doc="Model version"
    )

    metric_name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        doc="Metric that triggered the alert"
    )

    # Drift details
    baseline_value: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Baseline metric value"
    )

    current_value: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Current metric value when alert triggered"
    )

    drift_magnitude: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Magnitude of drift (absolute difference)"
    )

    threshold: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Threshold that was exceeded"
    )

    # Timestamps
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        index=True,
        doc="When alert was created"
    )

    resolved_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="When alert was resolved"
    )

    is_resolved: Mapped[bool] = mapped_column(
        default=False,
        index=True,
        doc="Whether alert has been resolved"
    )

    # Additional info
    alert_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional alert metadata"
    )

    def __repr__(self) -> str:
        return f"<Alert(id={self.id}, type={self.alert_type}, severity={self.severity})>"


# =============================================================================
# Table 7: release_validations (updated with tenant_id)
# =============================================================================

class ReleaseValidation(Base):
    """
    Stores release validation records for audit trail.
    
    Tracks the results of release validation checks when comparing
    new model versions against previous versions.
    """

    __tablename__ = "release_validations"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the validation record"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    # Model version info
    prev_model_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Previous model name"
    )

    prev_model_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="Previous model version"
    )

    new_model_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="New model name"
    )

    new_model_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="New model version"
    )

    # Dataset info
    dataset_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Dataset used for evaluation"
    )

    dataset_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="Dataset version"
    )

    # Metric deltas
    delta_robustness: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Change in robustness (prev - new)"
    )

    delta_hallucination: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Change in hallucination (new - prev)"
    )

    delta_toxicity: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Change in toxicity (new - prev)"
    )

    delta_bias: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Change in bias (new - prev)"
    )

    delta_confidence: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Change in confidence (new - prev)"
    )

    # Previous model metrics
    prev_robustness: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Previous model robustness score"
    )

    prev_hallucination: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Previous model hallucination score"
    )

    prev_toxicity: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Previous model toxicity score"
    )

    prev_bias: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Previous model bias score"
    )

    prev_confidence: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Previous model confidence score"
    )

    # New model metrics
    new_robustness: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="New model robustness score"
    )

    new_hallucination: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="New model hallucination score"
    )

    new_toxicity: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="New model toxicity score"
    )

    new_bias: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="New model bias score"
    )

    new_confidence: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="New model confidence score"
    )

    # Validation result
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        doc="Validation status: PASS, FAIL, CONDITIONAL_PASS, BLOCKED"
    )

    # Sample info
    sample_count: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        doc="Number of samples evaluated"
    )

    # Configuration verification
    config_hash_match: Mapped[bool] = mapped_column(
        default=True,
        doc="Whether configuration hashes matched"
    )

    # Timestamps
    timestamp: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        index=True,
        doc="When validation was performed"
    )

    # References to runs
    prev_run_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        Uuid,
        nullable=True,
        doc="Previous evaluation run ID"
    )

    new_run_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        Uuid,
        nullable=True,
        doc="New evaluation run ID"
    )

    # Additional info
    validation_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional validation metadata"
    )

    # Baseline update
    baseline_updated: Mapped[bool] = mapped_column(
        default=False,
        doc="Whether monitoring baseline was updated after this validation"
    )

    def __repr__(self) -> str:
        return f"<ReleaseValidation(id={self.id}, prev={self.prev_model_name}/{self.prev_model_version}, new={self.new_model_name}/{self.new_model_version}, status={self.status})>"


# =============================================================================
# Table 8: api_keys (updated with tenant_id)
# =============================================================================

class APIKey(Base):
    """
    Stores API keys for public API access.
    
    Tracks API keys for the Evaluation-as-a-Service (EaaS) platform.
    Each API key is scoped to a specific tenant.
    """

    __tablename__ = "api_keys"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the API key"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    key_hash: Mapped[str] = mapped_column(
        String(64),
        nullable=False,
        unique=True,
        index=True,
        doc="SHA256 hash of the API key"
    )

    owner: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        index=True,
        doc="Owner identifier for the API key"
    )

    rate_limit: Mapped[int] = mapped_column(
        Integer,
        default=100,
        doc="Rate limit (requests per minute)"
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When this API key was created"
    )

    last_used: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="When this API key was last used"
    )

    active: Mapped[bool] = mapped_column(
        default=True,
        index=True,
        doc="Whether this API key is active"
    )

    # Governance controls
    evaluation_mode_restriction: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        doc="Restricted evaluation mode: lightweight or full"
    )

    mutation_enabled: Mapped[bool] = mapped_column(
        default=True,
        doc="Whether mutation is enabled for this key"
    )

    monitoring_only: Mapped[bool] = mapped_column(
        default=False,
        doc="Whether monitoring-only mode is enforced"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        back_populates="api_keys",
        doc="Parent tenant"
    )

    def __repr__(self) -> str:
        return f"<APIKey(id={self.id}, owner={self.owner}, tenant_id={self.tenant_id}, active={self.active})>"


# =============================================================================
# Table 9: workers
# =============================================================================

class Worker(Base):
    """
    Tracks registered worker nodes in the distributed evaluation system.
    
    Stores metadata about workers including GPU capacity, health status,
    and active job counts for scheduling decisions.
    """

    __tablename__ = "workers"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the worker"
    )

    worker_id: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        unique=True,
        index=True,
        doc="Unique worker identifier (hostname-pid format)"
    )

    hostname: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        doc="Worker hostname"
    )

    gpu_count: Mapped[int] = mapped_column(
        Integer,
        default=0,
        doc="Number of GPUs available on this worker"
    )

    gpu_memory_total: Mapped[int] = mapped_column(
        Integer,
        default=0,
        doc="Total GPU memory in MB"
    )

    gpu_memory_used: Mapped[int] = mapped_column(
        Integer,
        default=0,
        doc="Currently used GPU memory in MB"
    )

    # Worker status: REGISTERED, ACTIVE, DEGRADED, OFFLINE
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="registered",
        index=True,
        doc="Worker status"
    )

    last_heartbeat: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="Last heartbeat timestamp"
    )

    active_jobs: Mapped[int] = mapped_column(
        Integer,
        default=0,
        doc="Number of currently active jobs"
    )

    max_concurrent_jobs: Mapped[int] = mapped_column(
        Integer,
        default=1,
        doc="Maximum concurrent jobs this worker can handle"
    )

    # GPU load metrics
    gpu_usage_percent: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Current GPU usage percentage (0-100)"
    )

    # Worker capabilities
    capabilities: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Worker capabilities (supported models, etc.)"
    )

    # Metadata
    worker_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional worker metadata"
    )

    # Timestamps
    registered_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When worker was registered"
    )

    # Version tracking for distributed coordination
    version: Mapped[int] = mapped_column(
        Integer,
        default=1,
        doc="Worker version for optimistic locking"
    )

    def __repr__(self) -> str:
        return f"<Worker(id={self.id}, worker_id={self.worker_id}, status={self.status})>"


# =============================================================================
# Table 10: audit_logs (Immutable - append only, no updates allowed)
# =============================================================================

class AuditLog(Base):
    """
    Immutable audit log for tracking user actions.
    
    This table is append-only - no updates or deletes are allowed.
    All entries are immutable to maintain audit integrity.
    """

    __tablename__ = "audit_logs"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the audit log entry"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    # User who performed the action
    user_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="User who performed the action"
    )

    # Action details
    action: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        index=True,
        doc="Action performed (e.g., JOB_CREATED, USER_LOGIN)"
    )

    resource_type: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        index=True,
        doc="Type of resource affected (e.g., job, user, api_key)"
    )

    resource_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        Uuid,
        nullable=True,
        doc="ID of the affected resource"
    )

    # Timestamp (mandatory for audit)
    timestamp: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        index=True,
        doc="When the action occurred"
    )

    # Additional action metadata (renamed from 'metadata' to avoid SQLAlchemy conflict)
    action_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional action metadata"
    )

    # Request tracing
    request_id: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        index=True,
        doc="Request ID for tracing"
    )

    ip_address: Mapped[Optional[str]] = mapped_column(
        String(45),  # IPv6 compatible
        nullable=True,
        doc="Client IP address"
    )

    # Action status
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="success",
        doc="Action status: success, failure, pending"
    )

    # Error details (if any)
    error_message: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        doc="Error message if action failed"
    )

    # Hash chain for tamper detection (Week 6 Day 4 - Enterprise Security Hardening)
    entry_hash: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        index=True,
        doc="SHA256 hash for audit chain integrity verification"
    )

    # Previous entry hash (to enable chain verification)
    previous_hash: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        doc="Hash of previous audit entry for chain linkage"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        doc="Parent tenant"
    )

    user: Mapped["User"] = relationship(
        "User",
        back_populates="audit_logs",
        doc="User who performed the action"
    )

    def __repr__(self) -> str:
        return f"<AuditLog(id={self.id}, action={self.action}, tenant_id={self.tenant_id})>"


# =============================================================================
# Table 11: tenant_usage (Week 9 Day 1 - SaaS Usage Tracking)
# =============================================================================

class TenantUsage(Base):
    """
    Tracks usage metrics per tenant for billing purposes.
    
    Stores monthly usage for GPU hours, API calls, evaluations, and storage.
    """

    __tablename__ = "tenant_usage"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the usage record"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    # Billing period
    year: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        doc="Year of the billing period"
    )

    month: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        doc="Month of the billing period (1-12)"
    )

    # Usage metrics
    gpu_hours: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Total GPU hours used in this period"
    )

    api_calls: Mapped[int] = mapped_column(
        Integer,
        default=0,
        doc="Total API calls in this period"
    )

    evaluations: Mapped[int] = mapped_column(
        Integer,
        default=0,
        doc="Total evaluations performed in this period"
    )

    storage_gb: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Storage used in GB"
    )

    monitoring_events: Mapped[int] = mapped_column(
        Integer,
        default=0,
        doc="Total monitoring events in this period"
    )

    # Computed costs
    base_fee: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Base subscription fee for this period"
    )

    gpu_cost: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="GPU usage cost for this period"
    )

    evaluation_cost: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Evaluation overage cost for this period"
    )

    total_cost: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Total cost for this period"
    )

    # Quota tracking
    quota_utilization_percent: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Overall quota utilization percentage"
    )

    is_over_quota: Mapped[bool] = mapped_column(
        default=False,
        doc="Whether usage exceeded quota this period"
    )

    # Timestamps
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When this record was created"
    )

    updated_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        onupdate=datetime.utcnow,
        doc="When this record was last updated"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        doc="Parent tenant"
    )

    def __repr__(self) -> str:
        return f"<TenantUsage(tenant_id={self.tenant_id}, year={self.year}, month={self.month}, cost=${self.total_cost:.2f})>"


# =============================================================================
# Table 12: subscriptions (Week 9 Day 1 - SaaS Subscriptions)
# =============================================================================

class Subscription(Base):
    """
    Tracks tenant subscription information.
    
    Stores subscription plan, period dates, and status.
    """

    __tablename__ = "subscriptions"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the subscription"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant"
    )

    # Plan information
    plan_type: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        doc="Plan type: free, pro, enterprise, research_partner"
    )

    # Billing period
    current_period_start: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="Start of current billing period"
    )

    current_period_end: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="End of current billing period"
    )

    # Status
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="active",
        doc="Subscription status: active, trialing, past_due, cancelled, paused"
    )

    # Auto-renewal
    auto_renew: Mapped[bool] = mapped_column(
        default=True,
        doc="Whether subscription auto-renews"
    )

    # Feature overrides (JSON for flexibility)
    feature_overrides: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Feature overrides for this subscription"
    )

    # Custom GSS weights (enterprise only)
    custom_gss_weights: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Custom GSS weight configuration"
    )

    # Timestamps
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When subscription was created"
    )

    updated_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        onupdate=datetime.utcnow,
        doc="When subscription was last updated"
    )

    cancelled_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="When subscription was cancelled"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        doc="Parent tenant"
    )

    def __repr__(self) -> str:
        return f"<Subscription(tenant_id={self.tenant_id}, plan={self.plan_type}, status={self.status})>"


# =============================================================================
# Table 13: invoices (Week 9 Day 1 - SaaS Invoices)
# =============================================================================

class Invoice(Base):
    """
    Tracks invoices for billing.
    
    Stores invoice details, line items, and payment status.
    """

    __tablename__ = "invoices"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the invoice"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant"
    )

    invoice_number: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        unique=True,
        doc="Human-readable invoice number"
    )

    # Period
    period_start: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="Start of billing period"
    )

    period_end: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="End of billing period"
    )

    # Amounts
    subtotal: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Subtotal before tax"
    )

    tax: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Tax amount"
    )

    total: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Total amount due"
    )

    currency: Mapped[str] = mapped_column(
        String(3),
        default="USD",
        doc="Currency code"
    )

    # Status
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="draft",
        doc="Invoice status: draft, issued, paid, overdue, cancelled, refunded"
    )

    # Payment
    paid_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="When invoice was paid"
    )

    payment_method: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        doc="Payment method used"
    )

    due_date: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="Payment due date"
    )

    # Line items (stored as JSON)
    line_items: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Invoice line items"
    )

    # Timestamps
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When invoice was created"
    )

    issued_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="When invoice was issued"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        doc="Parent tenant"
    )

    def __repr__(self) -> str:
        return f"<Invoice(invoice_number={self.invoice_number}, tenant_id={self.tenant_id}, total=${self.total:.2f}, status={self.status})>"


# =============================================================================
# Table 14: contracts (Week 9 Day 1 - Enterprise Contracts)
# =============================================================================

class Contract(Base):
    """
    Tracks enterprise contracts with custom terms.
    
    Stores contract metadata, custom pricing, and SLA terms.
    """

    __tablename__ = "contracts"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the contract"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant"
    )

    # Contract info
    contract_number: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        unique=True,
        doc="Human-readable contract number"
    )

    contract_type: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        doc="Contract type: standard, custom, reseller, research, trial"
    )

    # Period
    start_date: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="Contract start date"
    )

    end_date: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="Contract end date"
    )

    # Status
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="draft",
        doc="Contract status: draft, pending, active, expired, terminated, renewed"
    )

    # Custom pricing
    custom_base_fee: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Custom monthly base fee"
    )

    custom_gpu_rate: Mapped[Optional[float]] = mapped_column(
        Float,
        nullable=True,
        doc="Custom GPU rate per hour"
    )

    discount_percent: Mapped[float] = mapped_column(
        Float,
        default=0.0,
        doc="Discount percentage"
    )

    # Custom quotas
    custom_evaluations_quota: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        doc="Custom evaluations quota"
    )

    custom_gpu_hours_quota: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        doc="Custom GPU hours quota"
    )

    custom_concurrent_jobs: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        doc="Custom concurrent jobs limit"
    )

    # SLA terms
    sla_uptime_percent: Mapped[float] = mapped_column(
        Float,
        default=99.9,
        doc="SLA uptime percentage"
    )

    sla_max_job_start_delay_seconds: Mapped[int] = mapped_column(
        Integer,
        default=60,
        doc="Max job start delay in seconds"
    )

    sla_incident_response_hours: Mapped[int] = mapped_column(
        Integer,
        default=4,
        doc="Incident response time in hours"
    )

    sla_support_level: Mapped[str] = mapped_column(
        String(20),
        default="community",
        doc="Support level: community, email, 24x7"
    )

    # Data terms
    data_retention_days: Mapped[int] = mapped_column(
        Integer,
        default=90,
        doc="Data retention days"
    )

    allow_data_export: Mapped[bool] = mapped_column(
        default=True,
        doc="Whether data export is allowed"
    )

    # Dedicated resources
    dedicated_cluster: Mapped[bool] = mapped_column(
        default=False,
        doc="Whether dedicated cluster is provided"
    )

    # Metadata
    notes: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        doc="Contract notes"
    )

    # Timestamps
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When contract was created"
    )

    updated_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        onupdate=datetime.utcnow,
        doc="When contract was last updated"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        doc="Parent tenant"
    )

    def __repr__(self) -> str:
        return f"<Contract(contract_number={self.contract_number}, tenant_id={self.tenant_id}, type={self.contract_type}, status={self.status})>"


# =============================================================================
# Table 15: billing_events (Week 9 Day 2 - Billing Audit Logging)
# =============================================================================

class BillingEvent(Base):
    """
    Immutable billing event log for audit trail.
    
    This table is append-only - no updates or deletes are allowed.
    All entries are immutable to maintain billing audit integrity.
    
    Schema: tenant_id, event_type, amount, currency, timestamp, status, payment_provider_id
    """

    __tablename__ = "billing_events"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the billing event"
    )

    # Multi-tenant isolation
    tenant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        doc="Reference to parent tenant for isolation"
    )

    # Event type
    event_type: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        index=True,
        doc="Event type: payment_succeeded, payment_failed, subscription_created, subscription_cancelled, invoice_issued, invoice_paid, refund_issued, grace_period_started, grace_period_ended, account_suspended, account_reactivated"
    )

    # Amount
    amount: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Event amount"
    )

    currency: Mapped[str] = mapped_column(
        String(3),
        default="USD",
        doc="Currency code (ISO 4217)"
    )

    # Status
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        doc="Event status: pending, completed, failed, cancelled, refunded"
    )

    # Payment provider reference
    payment_provider_id: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        doc="Payment provider's reference ID (e.g., Stripe payment intent ID)"
    )

    # Subscription reference
    subscription_id: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        doc="Subscription ID from payment provider"
    )

    # Invoice reference
    invoice_id: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        doc="Invoice ID if applicable"
    )

    # Customer reference
    customer_id: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        doc="Customer ID from payment provider"
    )

    # Plan information
    plan_type: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        doc="Plan type if applicable"
    )

    # Failure details (if applicable)
    failure_reason: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        doc="Failure reason if payment failed"
    )

    # Event metadata (stored as JSON for flexibility)
    event_metadata: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Additional event metadata"
    )

    # Timestamp (mandatory for audit)
    timestamp: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        index=True,
        doc="When the event occurred"
    )

    # Hash chain for tamper detection
    event_hash: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        doc="SHA256 hash for billing event chain integrity verification"
    )

    # Previous event hash (to enable chain verification)
    previous_hash: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        doc="Hash of previous billing event for chain linkage"
    )

    # Relationships
    tenant: Mapped["Tenant"] = relationship(
        "Tenant",
        doc="Parent tenant"
    )

    def __repr__(self) -> str:
        return f"<BillingEvent(id={self.id}, tenant_id={self.tenant_id}, event_type={self.event_type}, amount={self.amount} {self.currency}, status={self.status})>"


# =============================================================================
# Table 16: certificates (Week 10 Day 5 - Public Certification Registry)
# =============================================================================

class Certificate(Base):
    """
    Stores public certification records for the AegisLM certification registry.
    
    These certificates are publicly verifiable and contain the evaluation
    results for AI models under the GSS-1 standard.
    """

    __tablename__ = "certificates"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
        doc="Unique identifier for the certificate"
    )

    # Certificate identification
    certificate_id: Mapped[str] = mapped_column(
        String(36),
        nullable=False,
        unique=True,
        index=True,
        doc="Public certificate ID (UUID format)"
    )

    # Model information
    model_name: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        index=True,
        doc="Name of the certified model"
    )

    model_version: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        doc="Version of the model"
    )

    organization: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        index=True,
        doc="Organization owning the model"
    )

    # GSS Scores
    gss_score: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Composite Robustness Score (R) from GSS-1"
    )

    RSI: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Robustness Stability Index"
    )

    RiskIndex: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        doc="Risk Index from GSS-1"
    )

    # Tier classifications
    certification_tier: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        index=True,
        doc="GSS certification tier: Tier A, Tier B, Tier C, Tier D"
    )

    risk_tier: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        index=True,
        doc="Risk tier: LOW, MODERATE, HIGH, CRITICAL"
    )

    # Individual metrics (JSON)
    individual_metrics: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Individual component scores (H, T, B, C)"
    )

    # Evaluation metadata
    evaluation_date: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="Date of evaluation"
    )

    valid_until: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        doc="Certificate validity end date"
    )

    # Audit and verification
    audit_hash: Mapped[str] = mapped_column(
        String(64),
        nullable=False,
        doc="SHA256 hash of the evaluation audit trail"
    )

    verification_signature: Mapped[str] = mapped_column(
        String(256),
        nullable=False,
        doc="Digital signature for certificate verification"
    )

    # Version info
    gss_version: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="1.0",
        doc="GSS version used for evaluation"
    )

    dataset_hash: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        doc="SHA256 hash of the dataset used"
    )

    config_hash: Mapped[Optional[str]] = mapped_column(
        String(64),
        nullable=True,
        doc="SHA256 hash of evaluation configuration"
    )

    sample_size: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        doc="Number of samples evaluated"
    )

    # Confidence interval
    confidence_interval: Mapped[Optional[Dict[str, Any]]] = mapped_column(
        JSON,
        nullable=True,
        doc="Statistical confidence interval"
    )

    # Status (for revocation tracking)
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="active",
        index=True,
        doc="Certificate status: active, revoked, expired"
    )

    revocation_reason: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        doc="Reason for revocation if status is revoked"
    )

    revoked_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        doc="Timestamp when certificate was revoked"
    )

    # Additional metadata
    sector: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        index=True,
        doc="Industry sector"
    )

    model_size: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        doc="Model size category: small, medium, large, xl"
    )

    # Timestamps
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        doc="When certificate was created"
    )

    updated_at: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.utcnow,
        onupdate=datetime.utcnow,
        doc="When certificate was last updated"
    )

    def __repr__(self) -> str:
        return f"<Certificate(id={self.id}, model={self.model_name}, tier={self.certification_tier}, status={self.status})>"


# =============================================================================
# Helper functions
# =============================================================================

async def create_tables():
    """Create all tables in the database."""
    from backend.db.session import create_database_engine
    
    engine = create_database_engine()
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    await engine.dispose()