Finlytic/FinlyticBot/Database/BotDbContext.cs

using System;
using System.Collections.Generic;
using System.Text.Json;
using FinlyticCore.Database;
using FinlyticCore.Dtos.TechnicalAnalysis;
using FinlyticCore.Entities.Settings;
using FinlyticBot.Database.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace FinlyticBot.Database;

public class BotDbContext : DbContext, ISettingsDbContext
{
    private static readonly JsonSerializerOptions JsonOptions = new()
    {
        PropertyNameCaseInsensitive = true,
        PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
        WriteIndented = false
    };

    public BotDbContext(DbContextOptions<BotDbContext> options) : base(options)
    {
    }

    public DbSet<SettingEntity> DynamicSettings => Set<SettingEntity>();
    public DbSet<BotPositionEntity> Positions => Set<BotPositionEntity>();
    public DbSet<BotPortfolioSnapshotEntity> PortfolioSnapshots => Set<BotPortfolioSnapshotEntity>();

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

        // 1. Settings Table
        modelBuilder.Entity<SettingEntity>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => e.Key).IsUnique();
        });

        // 2. ExitPlan JSONB Converter
        var exitPlanConverter = new ValueConverter<ExitPlan, string>(
            v => JsonSerializer.Serialize(v, JsonOptions),
            v => JsonSerializer.Deserialize<ExitPlan>(v, JsonOptions) ?? new ExitPlan(ExitStrategyType.FixedSingleTarget, 0m, new List<TakeProfitStage>(), null, null, null, null)
        );

        // 3. Bot Positions Table
        modelBuilder.Entity<BotPositionEntity>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => new { e.Status, e.Venue });
            entity.HasIndex(e => e.OpenedAtUtc);
            entity.HasIndex(e => e.ProposalId);

            entity.Property(e => e.ExitPlan)
                .HasColumnType("jsonb")
                .HasConversion(exitPlanConverter);
        });

        // 4. Portfolio Snapshots Table
        modelBuilder.Entity<BotPortfolioSnapshotEntity>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => e.SnapshotDateUtc);
        });
    }
}

public class BotDbContextFactory : IDesignTimeDbContextFactory<BotDbContext>
{
    public BotDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<BotDbContext>();
        optionsBuilder.UseNpgsql("Host=localhost;Database=finlytic_bot;Username=postgres;Password=postgres");
        return new BotDbContext(optionsBuilder.Options);
    }
}