// <copyright file="BulkOperationExtensions.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace Jellyfin.Database.Providers.Postgres;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

/// <summary>
/// Extension methods for optimized bulk operations on PostgreSQL.
/// These methods help reduce dead tuple generation during media library scans by batching
/// operations and using more efficient transaction patterns.
/// </summary>
public static class BulkOperationExtensions
{
    /// <summary>
    /// Gets the recommended batch size for bulk operations based on the data size.
    /// PostgreSQL works best with smaller batches to reduce dead tuples during scans.
    /// </summary>
    /// <param name="entityCount">The total number of entities to process.</param>
    /// <returns>The recommended batch size.</returns>
    public static int GetRecommendedBatchSize(int entityCount)
    {
        // For media library scans, smaller batches reduce dead tuples:
        // < 100 entities: batch of 10 (light operations)
        // 100-1000 entities: batch of 50 (medium operations)
        // 1000-10000 entities: batch of 100 (heavy operations)
        // > 10000 entities: batch of 200 (very heavy operations)
        return entityCount switch
        {
            < 100 => 10,
            < 1000 => 50,
            < 10000 => 100,
            _ => 200
        };
    }

    /// <summary>
    /// Saves changes in batches to reduce dead tuple generation during bulk operations.
    /// This is especially useful during media library scans where many items are created/updated/deleted.
    /// </summary>
    /// <param name="context">The database context.</param>
    /// <param name="batchSize">The batch size for SaveChanges calls. If 0 or negative, uses automatic sizing.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>Total number of changes saved.</returns>
    public static async Task<int> SaveChangesInBatchesAsync(
        this DbContext context,
        int batchSize = 0,
        CancellationToken cancellationToken = default)
    {
        var totalChanges = 0;
        var currentBatch = 0;
        var trackingCount = context.ChangeTracker.Entries().Count();

        if (batchSize <= 0)
        {
            batchSize = GetRecommendedBatchSize(trackingCount);
        }

        // If the batch size is larger than what we're tracking, just save once
        if (trackingCount <= batchSize)
        {
            return await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        }

        var entries = context.ChangeTracker.Entries().ToList();
        var changes = new List<object>();

        foreach (var entry in entries)
        {
            changes.Add(entry.Entity);
            currentBatch++;

            if (currentBatch >= batchSize)
            {
                totalChanges += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
                currentBatch = 0;
            }
        }

        // Save any remaining changes
        if (currentBatch > 0)
        {
            totalChanges += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        }

        return totalChanges;
    }

    /// <summary>
    /// Deletes entities in batches using raw SQL for better performance.
    /// This reduces dead tuple generation compared to loading and deleting entities individually.
    /// </summary>
    /// <param name="context">The database context.</param>
    /// <param name="schema">The PostgreSQL schema name.</param>
    /// <param name="tableName">The table name.</param>
    /// <param name="whereClause">Optional WHERE clause (without WHERE keyword). If null, all rows are deleted.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>The number of rows deleted.</returns>
    public static async Task<int> BulkDeleteAsync(
        this DbContext context,
        string schema,
        string tableName,
        string? whereClause = null,
        CancellationToken cancellationToken = default)
    {
        var whereCondition = whereClause is not null ? $"WHERE {whereClause}" : string.Empty;
        var deleteQuery = $"DELETE FROM \"{schema}\".\"{tableName}\" {whereCondition}";

        return await context.Database.ExecuteSqlRawAsync(deleteQuery, cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// Truncates a table completely, which is much faster than DELETE for large tables.
    /// Note: This cannot be used if there are foreign key references.
    /// </summary>
    /// <param name="context">The database context.</param>
    /// <param name="schema">The PostgreSQL schema name.</param>
    /// <param name="tableName">The table name.</param>
    /// <param name="cascadeDelete">If true, will CASCADE delete related rows. Use with caution.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    public static async Task BulkTruncateAsync(
        this DbContext context,
        string schema,
        string tableName,
        bool cascadeDelete = false,
        CancellationToken cancellationToken = default)
    {
        var cascade = cascadeDelete ? "CASCADE" : string.Empty;
        var truncateQuery = $"TRUNCATE TABLE \"{schema}\".\"{tableName}\" {cascade}";

        await context.Database.ExecuteSqlRawAsync(truncateQuery, cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// Adds a large collection of entities to the context in batches to avoid memory issues.
    /// </summary>
    /// <typeparam name="T">The entity type.</typeparam>
    /// <param name="context">The database context.</param>
    /// <param name="entities">The entities to add.</param>
    /// <param name="batchSize">The batch size for adding entities.</param>
    /// <param name="saveAfterEachBatch">Whether to save changes after each batch.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>Total number of entities saved.</returns>
    public static async Task<int> BulkAddAsync<T>(
        this DbContext context,
        IEnumerable<T> entities,
        int batchSize = 1000,
        bool saveAfterEachBatch = true,
        CancellationToken cancellationToken = default) where T : class
    {
        var totalSaved = 0;
        var batch = new List<T>(batchSize);

        foreach (var entity in entities)
        {
            batch.Add(entity);

            if (batch.Count >= batchSize)
            {
                await context.AddRangeAsync(batch, cancellationToken).ConfigureAwait(false);

                if (saveAfterEachBatch)
                {
                    totalSaved += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
                }

                batch.Clear();
            }
        }

        // Add any remaining entities
        if (batch.Count > 0)
        {
            await context.AddRangeAsync(batch, cancellationToken).ConfigureAwait(false);

            if (saveAfterEachBatch)
            {
                totalSaved += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
            }
            else
            {
                totalSaved += batch.Count;
            }
        }

        return totalSaved;
    }

    /// <summary>
    /// Updates entities in batches to reduce dead tuple generation.
    /// </summary>
    /// <typeparam name="T">The entity type.</typeparam>
    /// <param name="context">The database context.</param>
    /// <param name="entities">The entities to update.</param>
    /// <param name="batchSize">The batch size for updates.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>Total number of entities updated.</returns>
    public static async Task<int> BulkUpdateAsync<T>(
        this DbContext context,
        IEnumerable<T> entities,
        int batchSize = 100,
        CancellationToken cancellationToken = default) where T : class
    {
        var totalUpdated = 0;
        var batch = new List<T>(batchSize);

        foreach (var entity in entities)
        {
            batch.Add(entity);

            if (batch.Count >= batchSize)
            {
                context.UpdateRange(batch);
                totalUpdated += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
                batch.Clear();
            }
        }

        // Update any remaining entities
        if (batch.Count > 0)
        {
            context.UpdateRange(batch);
            totalUpdated += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        }

        return totalUpdated;
    }

    /// <summary>
    /// Removes entities in batches to reduce dead tuple generation.
    /// </summary>
    /// <typeparam name="T">The entity type.</typeparam>
    /// <param name="context">The database context.</param>
    /// <param name="entities">The entities to remove.</param>
    /// <param name="batchSize">The batch size for removals.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>Total number of entities removed.</returns>
    public static async Task<int> BulkRemoveAsync<T>(
        this DbContext context,
        IEnumerable<T> entities,
        int batchSize = 100,
        CancellationToken cancellationToken = default) where T : class
    {
        var totalRemoved = 0;
        var batch = new List<T>(batchSize);

        foreach (var entity in entities)
        {
            batch.Add(entity);

            if (batch.Count >= batchSize)
            {
                context.RemoveRange(batch);
                totalRemoved += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
                batch.Clear();
            }
        }

        // Remove any remaining entities
        if (batch.Count > 0)
        {
            context.RemoveRange(batch);
            totalRemoved += await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        }

        return totalRemoved;
    }

    /// <summary>
    /// Executes VACUUM ANALYZE on a specific table to reclaim dead tuples and update statistics.
    /// This is useful to call after bulk deletion operations.
    /// </summary>
    /// <param name="context">The database context.</param>
    /// <param name="schema">The PostgreSQL schema name.</param>
    /// <param name="tableName">The table name.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    public static async Task VacuumTableAsync(
        this DbContext context,
        string schema,
        string tableName,
        CancellationToken cancellationToken = default)
    {
#pragma warning disable EF1002 // Schema and table names are from database metadata
        await context.Database.ExecuteSqlRawAsync($"VACUUM ANALYZE \"{schema}\".\"{tableName}\"", cancellationToken).ConfigureAwait(false);
#pragma warning restore EF1002
    }
}
