CODEX_ADK/BACKEND/Codex.Dal/CodexDbContext.cs

using Codex.Dal.Entities;
using Microsoft.EntityFrameworkCore;

namespace Codex.Dal;

public class CodexDbContext : DbContext
{
    public CodexDbContext(DbContextOptions<CodexDbContext> options) : base(options)
    {
    }

    // DbSets
    public DbSet<Agent> Agents => Set<Agent>();
    public DbSet<AgentTool> AgentTools => Set<AgentTool>();
    public DbSet<AgentExecution> AgentExecutions => Set<AgentExecution>();
    public DbSet<Conversation> Conversations => Set<Conversation>();
    public DbSet<ConversationMessage> ConversationMessages => Set<ConversationMessage>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        ConfigureAgent(modelBuilder);
        ConfigureAgentTool(modelBuilder);
        ConfigureAgentExecution(modelBuilder);
        ConfigureConversation(modelBuilder);
        ConfigureConversationMessage(modelBuilder);
    }

    private static void ConfigureAgent(ModelBuilder modelBuilder)
    {
        var entity = modelBuilder.Entity<Agent>();

        // Primary key
        entity.HasKey(a => a.Id);

        // Required fields
        entity.Property(a => a.Name)
            .IsRequired()
            .HasMaxLength(200);

        entity.Property(a => a.Description)
            .IsRequired()
            .HasMaxLength(1000);

        entity.Property(a => a.ModelProvider)
            .IsRequired()
            .HasMaxLength(100);

        entity.Property(a => a.ModelName)
            .IsRequired()
            .HasMaxLength(100);

        entity.Property(a => a.SystemPrompt)
            .IsRequired();

        entity.Property(a => a.ModelEndpoint)
            .HasMaxLength(500);

        // Indexes
        entity.HasIndex(a => new { a.Status, a.IsDeleted });
        entity.HasIndex(a => a.Type);
        entity.HasIndex(a => a.Name); // Performance: name searches

        // Relationships
        entity.HasMany(a => a.Tools)
            .WithOne(t => t.Agent)
            .HasForeignKey(t => t.AgentId)
            .OnDelete(DeleteBehavior.Cascade);

        entity.HasMany(a => a.Executions)
            .WithOne(e => e.Agent)
            .HasForeignKey(e => e.AgentId)
            .OnDelete(DeleteBehavior.Cascade);
    }

    private static void ConfigureAgentTool(ModelBuilder modelBuilder)
    {
        var entity = modelBuilder.Entity<AgentTool>();

        // Primary key
        entity.HasKey(t => t.Id);

        // Required fields
        entity.Property(t => t.ToolName)
            .IsRequired()
            .HasMaxLength(200);

        entity.Property(t => t.McpServerUrl)
            .HasMaxLength(500);

        entity.Property(t => t.ApiBaseUrl)
            .HasMaxLength(500);

        // PostgreSQL jsonb column for Configuration
        entity.Property(t => t.Configuration)
            .HasColumnType("jsonb");

        // Indexes
        entity.HasIndex(t => new { t.AgentId, t.IsEnabled });
        entity.HasIndex(t => t.Type);
    }

    private static void ConfigureAgentExecution(ModelBuilder modelBuilder)
    {
        var entity = modelBuilder.Entity<AgentExecution>();

        // Primary key
        entity.HasKey(e => e.Id);

        // Required fields
        entity.Property(e => e.UserPrompt)
            .IsRequired();

        entity.Property(e => e.Output)
            .IsRequired()
            .HasDefaultValue(string.Empty);

        // Precision for cost calculation
        entity.Property(e => e.EstimatedCost)
            .HasPrecision(18, 6);

        // Indexes for performance
        entity.HasIndex(e => new { e.AgentId, e.StartedAt })
            .IsDescending(false, true); // AgentId ASC, StartedAt DESC

        entity.HasIndex(e => e.ConversationId);
        entity.HasIndex(e => e.Status);
        entity.HasIndex(e => e.CompletedAt); // Performance: time-based queries

        // Relationships
        entity.HasOne(e => e.Conversation)
            .WithMany(c => c.Executions)
            .HasForeignKey(e => e.ConversationId)
            .OnDelete(DeleteBehavior.SetNull);

        entity.HasMany(e => e.Messages)
            .WithOne(m => m.Execution)
            .HasForeignKey(m => m.ExecutionId)
            .OnDelete(DeleteBehavior.SetNull);
    }

    private static void ConfigureConversation(ModelBuilder modelBuilder)
    {
        var entity = modelBuilder.Entity<Conversation>();

        // Primary key
        entity.HasKey(c => c.Id);

        // Required fields
        entity.Property(c => c.Title)
            .IsRequired()
            .HasMaxLength(500);

        entity.Property(c => c.Summary)
            .HasMaxLength(2000);

        // Indexes
        entity.HasIndex(c => new { c.IsActive, c.LastMessageAt })
            .IsDescending(false, true); // IsActive ASC, LastMessageAt DESC
        entity.HasIndex(c => c.Title); // Performance: title searches

        // Relationships
        entity.HasMany(c => c.Messages)
            .WithOne(m => m.Conversation)
            .HasForeignKey(m => m.ConversationId)
            .OnDelete(DeleteBehavior.Cascade);
    }

    private static void ConfigureConversationMessage(ModelBuilder modelBuilder)
    {
        var entity = modelBuilder.Entity<ConversationMessage>();

        // Primary key
        entity.HasKey(m => m.Id);

        // Required fields
        entity.Property(m => m.Content)
            .IsRequired();

        // Composite index for efficient conversation window queries
        entity.HasIndex(m => new { m.ConversationId, m.IsInActiveWindow, m.MessageIndex });

        // Index for role filtering
        entity.HasIndex(m => m.Role);

        // Performance: time-based queries
        entity.HasIndex(m => m.CreatedAt);
    }
}