using Microsoft.EntityFrameworkCore;

namespace MealPlan.Api.Data;

/// <summary>
/// Resyncs PostgreSQL identity sequences after bulk imports or manual ID inserts.
/// </summary>
public static class SequenceMaintenance
{
    private static readonly (string Table, string Column)[] IdentityTables =
    [
        ("Recipes", "Id"),
        ("Ingredients", "Id"),
        ("RecipeSteps", "Id"),
        ("RecipeGeneratorSessions", "Id"),
        ("DietGeneratorSessions", "Id"),
        ("DietGeneratorDraftMeals", "Id"),
        ("DietGeneratorDraftMealIngredients", "Id"),
        ("MealPlanEntries", "Id"),
    ];

    public static async Task<IReadOnlyList<string>> ResyncIdentitySequencesAsync(
        AppDbContext db,
        CancellationToken ct = default)
    {
        var messages = new List<string>();

        foreach (var (table, column) in IdentityTables)
        {
            var sql = $"""
                SELECT setval(
                    pg_get_serial_sequence('diet."{table}"', '{column}'),
                    COALESCE((SELECT MAX("{column}") FROM diet."{table}"), 1),
                    (SELECT MAX("{column}") IS NOT NULL FROM diet."{table}"))
                """;

            try
            {
                await db.Database.ExecuteSqlRawAsync(sql, ct);
                messages.Add($"Resynced diet.\"{table}\".\"{column}\" sequence.");
            }
            catch (Exception ex)
            {
                messages.Add($"Skipped diet.\"{table}\": {ex.Message}");
            }
        }

        return messages;
    }
}