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

#pragma warning disable SA1201 // Elements should appear in the correct order

namespace Jellyfin.Database.Providers.Postgres;

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Jellyfin.Database.Implementations;
using Jellyfin.Database.Implementations.DbConfiguration;
using Jellyfin.Database.Implementations.Entities;
using Jellyfin.Database.Implementations.Entities.Security;
using MediaBrowser.Common.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Npgsql;

/// <summary>
/// Configures Jellyfin to use a PostgreSQL database.
/// </summary>
[JellyfinDatabaseProviderKey("Jellyfin-PostgreSQL")]
public sealed class PostgresDatabaseProvider : IJellyfinDatabaseProvider
{
    private readonly IApplicationPaths applicationPaths;
    private readonly ILogger<PostgresDatabaseProvider> logger;

    /// <summary>
    /// Initializes a new instance of the <see cref="PostgresDatabaseProvider"/> class.
    /// </summary>
    /// <param name="applicationPaths">Service to construct the fallback when the old data path configuration is used.</param>
    /// <param name="logger">A logger.</param>
    public PostgresDatabaseProvider(IApplicationPaths applicationPaths, ILogger<PostgresDatabaseProvider> logger)
    {
        this.applicationPaths = applicationPaths;
        this.logger = logger;
    }

    /// <summary>
    /// Schema names mapping to legacy database files.
    /// </summary>
    public static class Schemas
    {
        /// <summary>
        /// Schema for activity log tables (legacy: activitylog.db).
        /// </summary>
        public const string ActivityLog = "activitylog";

        /// <summary>
        /// Schema for authentication tables (legacy: authentication.db).
        /// </summary>
        public const string Authentication = "authentication";

        /// <summary>
        /// Schema for display preferences tables (legacy: displaypreferences.db).
        /// </summary>
        public const string DisplayPreferences = "displaypreferences";

        /// <summary>
        /// Schema for library tables (legacy: library.db).
        /// </summary>
        public const string Library = "library";

        /// <summary>
        /// Schema for user tables (legacy: users.db).
        /// </summary>
        public const string Users = "users";

        /// <summary>
        /// Gets all schema names.
        /// </summary>
        public static string[] All => [ActivityLog, Authentication, DisplayPreferences, Library, Users];
    }

    /// <inheritdoc/>
    public IDbContextFactory<JellyfinDbContext>? DbContextFactory { get; set; }

    /// <inheritdoc/>
    public void Initialise(DbContextOptionsBuilder options, DatabaseConfigurationOptions databaseConfiguration)
    {
        static T? GetOption<T>(ICollection<CustomDatabaseOption>? options, string key, Func<string, T> converter, Func<T>? defaultValue = null)
        {
            if (options is null)
            {
                return defaultValue is not null ? defaultValue() : default;
            }

            var value = options.FirstOrDefault(e => e.Key.Equals(key, StringComparison.OrdinalIgnoreCase));
            if (value is null)
            {
                return defaultValue is not null ? defaultValue() : default;
            }

            return converter(value.Value);
        }

        var customOptions = databaseConfiguration.CustomProviderOptions?.Options;

        var connectionBuilder = new NpgsqlConnectionStringBuilder
        {
            Host = GetOption(customOptions, "host", e => e, () => "localhost")!,
            Port = GetOption(customOptions, "port", int.Parse, () => 5432),
            Database = GetOption(customOptions, "database", e => e, () => "jellyfin")!,
            Username = GetOption(customOptions, "username", e => e, () => "jellyfin")!,
            Password = GetOption(customOptions, "password", e => e, () => string.Empty)!,
            Pooling = GetOption(customOptions, "pooling", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase), () => true),
            CommandTimeout = GetOption(customOptions, "command-timeout", int.Parse, () => 30),
            Timeout = GetOption(customOptions, "connection-timeout", int.Parse, () => 15),
            Multiplexing = GetOption(customOptions, "multiplexing", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase), () => false),
            MaxPoolSize = GetOption(customOptions, "max-pool-size", int.Parse, () => 100),
            MinPoolSize = GetOption(customOptions, "min-pool-size", int.Parse, () => 0)
        };

        var connectionString = connectionBuilder.ToString();

        // Log PostgreSQL connection parameters (without password)
        logger.LogInformation(
            "PostgreSQL connection: Host={Host}, Port={Port}, Database={Database}, Username={Username}, Pooling={Pooling}, MaxPoolSize={MaxPoolSize}, Multiplexing={Multiplexing}",
            connectionBuilder.Host,
            connectionBuilder.Port,
            connectionBuilder.Database,
            connectionBuilder.Username,
            connectionBuilder.Pooling,
            connectionBuilder.MaxPoolSize,
            connectionBuilder.Multiplexing);

        options
            .UseNpgsql(
                connectionString,
                npgsqlOptions => npgsqlOptions.MigrationsAssembly(GetType().Assembly.FullName));

        var enableSensitiveDataLogging = GetOption(customOptions, "EnableSensitiveDataLogging", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase), () => false);
        if (enableSensitiveDataLogging)
        {
            options.EnableSensitiveDataLogging(enableSensitiveDataLogging);
            logger.LogInformation("EnableSensitiveDataLogging is enabled on PostgreSQL connection");
        }
    }

    /// <summary>
    /// Ensures the PostgreSQL database exists, creating it if necessary.
    /// </summary>
    /// <param name="databaseConfiguration">The database configuration.</param>
    /// <param name="cancellationToken">Cancellation token.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    public async Task EnsureDatabaseExistsAsync(DatabaseConfigurationOptions databaseConfiguration, CancellationToken cancellationToken = default)
    {
        static T? GetOption<T>(ICollection<CustomDatabaseOption>? options, string key, Func<string, T> converter, Func<T>? defaultValue = null)
        {
            if (options is null)
            {
                return defaultValue is not null ? defaultValue() : default;
            }

            var value = options.FirstOrDefault(e => e.Key.Equals(key, StringComparison.OrdinalIgnoreCase));
            if (value is null)
            {
                return defaultValue is not null ? defaultValue() : default;
            }

            return converter(value.Value);
        }

        var customOptions = databaseConfiguration.CustomProviderOptions?.Options;

        var host = GetOption(customOptions, "host", e => e, () => "localhost")!;
        var port = GetOption(customOptions, "port", int.Parse, () => 5432);
        var database = GetOption(customOptions, "database", e => e, () => "jellyfin")!;
        var username = GetOption(customOptions, "username", e => e, () => "jellyfin")!;
        var password = GetOption(customOptions, "password", e => e, () => string.Empty)!;
        var timeout = GetOption(customOptions, "connection-timeout", int.Parse, () => 15);

        // Connect to 'postgres' maintenance database to check if target database exists
        var maintenanceConnectionBuilder = new NpgsqlConnectionStringBuilder
        {
            Host = host,
            Port = port,
            Database = "postgres", // Connect to maintenance database
            Username = username,
            Password = password,
            Timeout = timeout,
            Pooling = false // Don't pool maintenance connections
        };

        try
        {
            await using var connection = new NpgsqlConnection(maintenanceConnectionBuilder.ToString());
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);

            // Check if database exists
            var checkQuery = "SELECT 1 FROM pg_database WHERE datname = @databaseName";
            await using var checkCommand = new NpgsqlCommand(checkQuery, connection);
            checkCommand.Parameters.AddWithValue("databaseName", database);

            var exists = await checkCommand.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);

            if (exists is null)
            {
                // Database doesn't exist, create it
                logger.LogInformation("PostgreSQL database '{Database}' does not exist. Creating...", database);

                // Use identifier quoting for safety
                var createQuery = $"CREATE DATABASE \"{database}\" OWNER \"{username}\"";
                await using var createCommand = new NpgsqlCommand(createQuery, connection);
                await createCommand.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);

                logger.LogInformation("PostgreSQL database '{Database}' created successfully", database);

                // Grant privileges
                var grantQuery = $"GRANT ALL PRIVILEGES ON DATABASE \"{database}\" TO \"{username}\"";
                await using var grantCommand = new NpgsqlCommand(grantQuery, connection);
                await grantCommand.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);

                logger.LogInformation("Granted all privileges on database '{Database}' to user '{Username}'", database, username);
            }
            else
            {
                logger.LogInformation("PostgreSQL database '{Database}' already exists", database);
            }

            // Now ensure all schemas exist in the database
            await EnsureSchemasExistAsync(host, port, database, username, password, timeout, cancellationToken).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "Failed to ensure PostgreSQL database '{Database}' exists. Error: {Message}", database, ex.Message);
            throw;
        }
    }

    /// <summary>
    /// Ensures all required schemas exist in the PostgreSQL database.
    /// </summary>
    private async Task EnsureSchemasExistAsync(
        string host,
        int port,
        string database,
        string username,
        string password,
        int timeout,
        CancellationToken cancellationToken)
    {
        var connectionBuilder = new NpgsqlConnectionStringBuilder
        {
            Host = host,
            Port = port,
            Database = database,
            Username = username,
            Password = password,
            Timeout = timeout,
            Pooling = false
        };

        try
        {
            await using var connection = new NpgsqlConnection(connectionBuilder.ToString());
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);

            foreach (var schema in Schemas.All)
            {
                // Check if schema exists
                var checkQuery = "SELECT 1 FROM information_schema.schemata WHERE schema_name = @schemaName";
                await using var checkCommand = new NpgsqlCommand(checkQuery, connection);
                checkCommand.Parameters.AddWithValue("schemaName", schema);

                var exists = await checkCommand.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);

                if (exists is null)
                {
                    // Schema doesn't exist, create it
                    logger.LogInformation("Creating PostgreSQL schema '{Schema}'...", schema);

                    var createQuery = $"CREATE SCHEMA \"{schema}\" AUTHORIZATION \"{username}\"";
                    await using var createCommand = new NpgsqlCommand(createQuery, connection);
                    await createCommand.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);

                    logger.LogInformation("Schema '{Schema}' created successfully", schema);
                }
                else
                {
                    logger.LogDebug("Schema '{Schema}' already exists", schema);
                }
            }

            logger.LogInformation("All required PostgreSQL schemas are ready");
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "Failed to ensure PostgreSQL schemas exist. Error: {Message}", ex.Message);
            throw;
        }
    }

    /// <inheritdoc/>
    public async Task RunScheduledOptimisation(CancellationToken cancellationToken)
    {
        var context = await DbContextFactory!.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
        await using (context.ConfigureAwait(false))
        {
            // Run PostgreSQL optimization commands on all schemas
            // Note: VACUUM cannot be parameterized, but schema names are from const strings, not user input
            foreach (var schema in Schemas.All)
            {
#pragma warning disable EF1002 // Schema names are internal constants, not user input
                await context.Database.ExecuteSqlRawAsync($"VACUUM ANALYZE \"{schema}\"", cancellationToken).ConfigureAwait(false);
#pragma warning restore EF1002
                logger.LogDebug("Optimized PostgreSQL schema '{Schema}'", schema);
            }

            logger.LogInformation("PostgreSQL database optimized successfully!");
        }
    }

    /// <inheritdoc/>
    public void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.SetDefaultDateTimeKind(DateTimeKind.Utc);

        // Assign entities to schemas based on their legacy database origin
        AssignEntitiesToSchemas(modelBuilder);
    }

    /// <summary>
    /// Assigns entities to PostgreSQL schemas based on their legacy database origin.
    /// </summary>
    /// <param name="modelBuilder">The model builder.</param>
    private static void AssignEntitiesToSchemas(ModelBuilder modelBuilder)
    {
        // ActivityLog schema (legacy: activitylog.db)
        modelBuilder.Entity<ActivityLog>().ToTable("ActivityLogs", Schemas.ActivityLog);

        // Authentication schema (legacy: authentication.db)
        modelBuilder.Entity<ApiKey>().ToTable("ApiKeys", Schemas.Authentication);
        modelBuilder.Entity<Device>().ToTable("Devices", Schemas.Authentication);
        modelBuilder.Entity<DeviceOptions>().ToTable("DeviceOptions", Schemas.Authentication);

        // DisplayPreferences schema (legacy: displaypreferences.db)
        modelBuilder.Entity<DisplayPreferences>().ToTable("DisplayPreferences", Schemas.DisplayPreferences);
        modelBuilder.Entity<ItemDisplayPreferences>().ToTable("ItemDisplayPreferences", Schemas.DisplayPreferences);
        modelBuilder.Entity<CustomItemDisplayPreferences>().ToTable("CustomItemDisplayPreferences", Schemas.DisplayPreferences);
        modelBuilder.Entity<HomeSection>().ToTable("HomeSections", Schemas.DisplayPreferences);

        // Users schema (legacy: users.db)
        modelBuilder.Entity<User>().ToTable("Users", Schemas.Users);
        modelBuilder.Entity<Permission>().ToTable("Permissions", Schemas.Users);
        modelBuilder.Entity<Preference>().ToTable("Preferences", Schemas.Users);
        modelBuilder.Entity<AccessSchedule>().ToTable("AccessSchedules", Schemas.Users);

        // Library schema (legacy: library.db) - All remaining entities
        modelBuilder.Entity<BaseItemEntity>().ToTable("BaseItems", Schemas.Library);
        modelBuilder.Entity<Chapter>().ToTable("Chapters", Schemas.Library);
        modelBuilder.Entity<MediaStreamInfo>().ToTable("MediaStreamInfos", Schemas.Library);
        modelBuilder.Entity<AttachmentStreamInfo>().ToTable("AttachmentStreamInfos", Schemas.Library);
        modelBuilder.Entity<ImageInfo>().ToTable("ImageInfos", Schemas.Library);
        modelBuilder.Entity<BaseItemImageInfo>().ToTable("BaseItemImageInfos", Schemas.Library);
        modelBuilder.Entity<BaseItemProvider>().ToTable("BaseItemProviders", Schemas.Library);
        modelBuilder.Entity<BaseItemMetadataField>().ToTable("BaseItemMetadataFields", Schemas.Library);
        modelBuilder.Entity<BaseItemTrailerType>().ToTable("BaseItemTrailerTypes", Schemas.Library);
        modelBuilder.Entity<ItemValue>().ToTable("ItemValues", Schemas.Library);
        modelBuilder.Entity<ItemValueMap>().ToTable("ItemValuesMap", Schemas.Library);
        modelBuilder.Entity<People>().ToTable("Peoples", Schemas.Library);
        modelBuilder.Entity<PeopleBaseItemMap>().ToTable("PeopleBaseItemMap", Schemas.Library);
        modelBuilder.Entity<UserData>().ToTable("UserData", Schemas.Library);
        modelBuilder.Entity<AncestorId>().ToTable("AncestorIds", Schemas.Library);
        modelBuilder.Entity<TrickplayInfo>().ToTable("TrickplayInfos", Schemas.Library);
        modelBuilder.Entity<MediaSegment>().ToTable("MediaSegments", Schemas.Library);
        modelBuilder.Entity<KeyframeData>().ToTable("KeyframeData", Schemas.Library);
    }

    /// <inheritdoc/>
    public Task RunShutdownTask(CancellationToken cancellationToken)
    {
        if (DbContextFactory is null)
        {
            return Task.CompletedTask;
        }

        // Clear connection pools
        NpgsqlConnection.ClearAllPools();
        return Task.CompletedTask;
    }

    /// <inheritdoc/>
    public void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        // PostgreSQL-specific conventions can be added here if needed
    }

    /// <inheritdoc />
    public async Task<string> MigrationBackupFast(CancellationToken cancellationToken)
    {
        var key = DateTime.UtcNow.ToString("yyyyMMddHHmmss", CultureInfo.InvariantCulture);

        logger.LogWarning("Fast backup is not implemented for PostgreSQL. Use pg_dump for proper backups.");

        // Return a key for compatibility, but actual backup should be done externally
        return await Task.FromResult(key).ConfigureAwait(false);
    }

    /// <inheritdoc />
    public Task RestoreBackupFast(string key, CancellationToken cancellationToken)
    {
        logger.LogWarning("Fast restore is not implemented for PostgreSQL. Use pg_restore for proper restoration.");
        return Task.CompletedTask;
    }

    /// <inheritdoc />
    public Task DeleteBackup(string key)
    {
        logger.LogWarning("Backup deletion is not implemented for PostgreSQL. Manage backups externally.");
        return Task.CompletedTask;
    }

    /// <inheritdoc/>
    public async Task PurgeDatabase(JellyfinDbContext dbContext, IEnumerable<string>? tableNames)
    {
        ArgumentNullException.ThrowIfNull(tableNames);

        var deleteQueries = new List<string>();
        foreach (var tableName in tableNames)
        {
            // Find the schema for this table
            var entityType = dbContext.Model.GetEntityTypes()
                .FirstOrDefault(et => et.GetTableName() == tableName);

            if (entityType is not null)
            {
                var schema = entityType.GetSchema() ?? "public";
                deleteQueries.Add($"TRUNCATE TABLE \"{schema}\".\"{tableName}\" CASCADE;");
            }
            else
            {
                // Fallback to public schema if entity not found
                deleteQueries.Add($"TRUNCATE TABLE \"public\".\"{tableName}\" CASCADE;");
            }
        }

        var deleteAllQuery = string.Join('\n', deleteQueries);

        await dbContext.Database.ExecuteSqlRawAsync(deleteAllQuery).ConfigureAwait(false);
    }
}
