using System.Net;
using System.Text;
using System.Text.Json;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Npgsql;
using NpgsqlRest;

namespace NpgsqlRestClient;

/// <summary>
/// Provides endpoint handlers for PostgreSQL statistics endpoints.
/// Each method executes a query against pg_stat_* views and returns results as JSON, TSV, or HTML.
/// </summary>
public static class StatsEndpoints
{
    private const string RoutinesQuery = """
        select
            funcid as oid,
            schemaname as schema,
            b.proname as name,
            case
                when b.prokind = 'f' then 'function'
                when b.prokind = 'p' then 'procedure'
                when b.prokind = 'a' then 'aggregate'
                when b.prokind = 'w' then 'window'
                else b.prokind::text
            end || ' ' || funcid::regprocedure as signature,
            calls,
            ltrim(justify_interval(total_time * interval '1 ms')::text, '@ ') as total_time,
            ltrim(justify_interval(self_time * interval '1 ms')::text, '@ ') as self_time
        from pg_stat_user_functions a join pg_proc b on a.funcid = b.oid
        where $1 is null or schemaname similar to $1
        order by
            a.schemaname,
            b.proname
        """;

    private const string TablesQuery = """
        select
            schemaname as schema,
            relname as name,
            n_live_tup as live_tuples,
            n_dead_tup as dead_tuples,
            round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_tuple_percent,
            pg_size_pretty(pg_total_relation_size(relid)) as total_size,
            pg_size_pretty(pg_relation_size(relid)) as table_size,
            pg_size_pretty(pg_indexes_size(relid)) as index_size,
            seq_scan as sequential_scans,
            idx_scan as index_scans,
            round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 2) as index_scan_percent,
            n_tup_ins as inserts_count,
            n_tup_upd as updates_count,
            n_tup_del as deletes_count,
            last_vacuum,
            last_autovacuum,
            last_analyze,
            last_autoanalyze,
            vacuum_count + autovacuum_count as total_vacuums,
            analyze_count + autoanalyze_count as total_analyzes
        from pg_stat_user_tables
        where $1 is null or schemaname similar to $1
        order by schemaname, relname
        """;

    private const string IndexesQuery = """
        select
            schemaname as schema,
            relname as table,
            indexrelname AS index,
            indisunique as is_unique,
            indisprimary as is_primary,
            idx_scan AS scans,
            last_idx_scan as last_scan,
            idx_tup_fetch AS tuples_fetched,
            pg_get_indexdef(indexrelid) as definition
        from pg_stat_user_indexes s
        join pg_index i using (indexrelid)
        where $1 is null or schemaname similar to $1
        order by schemaname, relname
        """;

    private const string ActivityQuery = """
        select
            pid,
            usename as user,
            application_name as app,
            client_addr as client_ip,
            datname as database,
            state,
            wait_event_type,
            wait_event,
            ltrim(justify_interval(now() - state_change)::text, '@ ') as state_duration,
            ltrim(justify_interval(now() - backend_start)::text, '@ ') as connection_age,
            ltrim(justify_interval(now() - xact_start)::text, '@ ') as transaction_duration,
            case 
                when state = 'active' then ltrim(justify_interval(now() - query_start)::text, '@ ')
                when state = 'idle' then 'finished ' || ltrim(justify_interval(now() - state_change)::text, '@ ') || ' ago'
                else state
            end as query_duration,
            left(query, 100) as query_preview,
            query
        from pg_stat_activity
        where pid != pg_backend_pid()
        order by state_duration desc nulls last
        """;

    /// <summary>
    /// Returns statistics for user-defined functions and procedures.
    /// </summary>
    public static async Task HandleRoutinesStats(HttpContext context, string connectionString, string outputFormat, string? schemaSimilarTo, bool requireAuthorization, string[]? authorizedRoles, string? roleClaimType, ILogger? logger)
    {
        if (await CheckAuthorization(context, requireAuthorization, authorizedRoles, roleClaimType) is false)
        {
            return;
        }
        await ExecuteQueryAndRespond(context, connectionString, RoutinesQuery, outputFormat, schemaSimilarTo, logger, "Stats.Routines", setupCommands: ["set local intervalstyle = 'postgres_verbose'"]);
    }

    /// <summary>
    /// Returns statistics for user tables including size, tuple counts, and vacuum info.
    /// </summary>
    public static async Task HandleTablesStats(HttpContext context, string connectionString, string outputFormat, string? schemaSimilarTo, bool requireAuthorization, string[]? authorizedRoles, string? roleClaimType, ILogger? logger)
    {
        if (await CheckAuthorization(context, requireAuthorization, authorizedRoles, roleClaimType) is false)
        {
            return;
        }
        await ExecuteQueryAndRespond(context, connectionString, TablesQuery, outputFormat, schemaSimilarTo, logger, "Stats.Tables");
    }

    /// <summary>
    /// Returns statistics for user indexes including scan counts and definitions.
    /// </summary>
    public static async Task HandleIndexesStats(HttpContext context, string connectionString, string outputFormat, string? schemaSimilarTo, bool requireAuthorization, string[]? authorizedRoles, string? roleClaimType, ILogger? logger)
    {
        if (await CheckAuthorization(context, requireAuthorization, authorizedRoles, roleClaimType) is false)
        {
            return;
        }
        await ExecuteQueryAndRespond(context, connectionString, IndexesQuery, outputFormat, schemaSimilarTo, logger, "Stats.Indexes");
    }

    /// <summary>
    /// Returns current database activity from pg_stat_activity.
    /// </summary>
    public static async Task HandleActivityStats(HttpContext context, string connectionString, string outputFormat, bool requireAuthorization, string[]? authorizedRoles, string? roleClaimType, ILogger? logger)
    {
        if (await CheckAuthorization(context, requireAuthorization, authorizedRoles, roleClaimType) is false)
        {
            return;
        }
        await ExecuteQueryAndRespond(context, connectionString, ActivityQuery, outputFormat, null, logger, "Stats.Activity", setupCommands: ["set local intervalstyle = 'postgres_verbose'"]);
    }

    private static async Task<bool> CheckAuthorization(HttpContext context, bool requireAuthorization, string[]? authorizedRoles, string? roleClaimType)
    {
        if (requireAuthorization is false && authorizedRoles is null)
        {
            return true;
        }

        if (context.User?.Identity?.IsAuthenticated is false)
        {
            await Results.Problem(
                type: null,
                statusCode: (int)HttpStatusCode.Unauthorized,
                title: "Unauthorized",
                detail: null).ExecuteAsync(context);
            return false;
        }

        if (authorizedRoles is not null)
        {
            bool ok = false;
            foreach (var claim in context.User?.Claims ?? [])
            {
                if (string.Equals(claim.Type, roleClaimType, StringComparison.Ordinal))
                {
                    if (authorizedRoles.Contains(claim.Value) is true)
                    {
                        ok = true;
                        break;
                    }
                }
            }
            if (ok is false)
            {
                await Results.Problem(
                    type: null,
                    statusCode: (int)HttpStatusCode.Forbidden,
                    title: "Forbidden",
                    detail: null).ExecuteAsync(context);
                return false;
            }
        }

        return true;
    }

    private static async Task ExecuteQueryAndRespond(
        HttpContext context,
        string connectionString,
        string query, string outputFormat,
        string? schemaSimilarTo,
        ILogger? logger,
        string logName,
        string[]? setupCommands = null)
    {
        try
        {
            await using var connection = new NpgsqlConnection(connectionString);
            await connection.OpenAsync(context.RequestAborted);

            // If setup commands are provided, wrap in a transaction with rollback
            if (setupCommands is { Length: > 0 })
            {
                await using var beginCmd = new NpgsqlCommand("begin", connection);
                CommandLogger.LogCommand(beginCmd, logger, logName);
                await beginCmd.ExecuteNonQueryAsync(context.RequestAborted);

                foreach (var setupSql in setupCommands)
                {
                    await using var setupCmd = new NpgsqlCommand(setupSql, connection);
                    CommandLogger.LogCommand(setupCmd, logger, logName);
                    await setupCmd.ExecuteNonQueryAsync(context.RequestAborted);
                }
            }

            try
            {
                await using var command = new NpgsqlCommand(query, connection);
                if (query.Contains("$1"))
                {
                    command.Parameters.Add(new NpgsqlParameter { Value = schemaSimilarTo ?? (object)DBNull.Value, NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text });
                }
                CommandLogger.LogCommand(command, logger, logName);
                await using var reader = await command.ExecuteReaderAsync(context.RequestAborted);

                var format = context.Request.Query.ContainsKey("format") ? context.Request.Query["format"].ToString() : outputFormat;
                if (string.Equals(format, "html", StringComparison.OrdinalIgnoreCase))
                {
                    await WriteAsHtml(context, reader);
                }
                else
                {
                    await WriteAsJson(context, reader);
                }
            }
            finally
            {
                if (setupCommands is { Length: > 0 })
                {
                    await using var rollbackCmd = new NpgsqlCommand("rollback", connection);
                    CommandLogger.LogCommand(rollbackCmd, logger, logName);
                    await rollbackCmd.ExecuteNonQueryAsync(CancellationToken.None);
                }
            }
        }
        catch (OperationCanceledException)
        {
            // Request was cancelled, don't log as error
        }
        catch (Exception ex)
        {
            logger?.LogError(ex, "Error executing stats query");
            context.Response.StatusCode = StatusCodes.Status500InternalServerError;
            await context.Response.WriteAsync("Error retrieving statistics", context.RequestAborted);
        }
    }

    private static async Task WriteAsJson(HttpContext context, NpgsqlDataReader reader)
    {
        context.Response.ContentType = "application/json";

        var columnNames = new string[reader.FieldCount];
        for (int i = 0; i < reader.FieldCount; i++)
        {
            columnNames[i] = ToCamelCase(reader.GetName(i));
        }

        using var stream = new MemoryStream();
        await using var writer = new Utf8JsonWriter(stream);

        writer.WriteStartArray();

        while (await reader.ReadAsync(context.RequestAborted))
        {
            writer.WriteStartObject();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                var value = reader.GetValue(i);
                writer.WritePropertyName(columnNames[i]);
                WriteJsonValue(writer, value);
            }
            writer.WriteEndObject();
        }

        writer.WriteEndArray();
        await writer.FlushAsync(context.RequestAborted);

        stream.Position = 0;
        await stream.CopyToAsync(context.Response.Body, context.RequestAborted);
    }

    private static void WriteJsonValue(Utf8JsonWriter writer, object value)
    {
        if (value == DBNull.Value || value is null)
        {
            writer.WriteNullValue();
            return;
        }

        switch (value)
        {
            case bool b:
                writer.WriteBooleanValue(b);
                break;
            case int i:
                writer.WriteNumberValue(i);
                break;
            case long l:
                writer.WriteNumberValue(l);
                break;
            case decimal d:
                writer.WriteNumberValue(d);
                break;
            case double dbl:
                writer.WriteNumberValue(dbl);
                break;
            case float f:
                writer.WriteNumberValue(f);
                break;
            case DateTime dt:
                writer.WriteStringValue(dt.ToString("o"));
                break;
            case DateTimeOffset dto:
                writer.WriteStringValue(dto.ToString("o"));
                break;
            case TimeSpan ts:
                writer.WriteStringValue(ts.ToString());
                break;
            case System.Net.IPAddress ip:
                writer.WriteStringValue(ip.ToString());
                break;
            case uint ui:
                writer.WriteNumberValue(ui);
                break;
            default:
                writer.WriteStringValue(value.ToString());
                break;
        }
    }

    private static string ToCamelCase(string name)
    {
        if (string.IsNullOrEmpty(name))
            return name;

        // Handle snake_case to camelCase conversion
        var parts = name.Split('_');
        if (parts.Length == 1)
        {
            // Simple case: just lowercase first char
            return char.ToLowerInvariant(name[0]) + name[1..];
        }

        // Convert snake_case to camelCase
        var sb = new StringBuilder();
        for (int i = 0; i < parts.Length; i++)
        {
            if (string.IsNullOrEmpty(parts[i]))
                continue;

            if (sb.Length == 0)
            {
                sb.Append(parts[i].ToLowerInvariant());
            }
            else
            {
                sb.Append(char.ToUpperInvariant(parts[i][0]));
                if (parts[i].Length > 1)
                    sb.Append(parts[i][1..].ToLowerInvariant());
            }
        }
        return sb.ToString();
    }

    private static async Task WriteAsHtml(HttpContext context, NpgsqlDataReader reader)
    {
        context.Response.ContentType = "text/html; charset=utf-8";

        var sb = new StringBuilder();
        sb.AppendLine(
            "<style>table{font-family:Calibri,Arial,sans-serif;font-size:11pt;border-collapse:collapse}th,td{border:1px solid #d4d4d4;padding:4px 8px}th{background-color:#f5f5f5;font-weight:600}</style>");
        sb.AppendLine("<table>");

        // Write header row
        sb.Append("<tr>");
        for (int i = 0; i < reader.FieldCount; i++)
        {
            sb.Append("<th>");
            sb.Append(HtmlEncode(reader.GetName(i)));
            sb.Append("</th>");
        }
        sb.AppendLine("</tr>");

        // Write data rows
        while (await reader.ReadAsync(context.RequestAborted))
        {
            sb.Append("<tr>");
            for (int i = 0; i < reader.FieldCount; i++)
            {
                sb.Append("<td>");
                var value = reader.GetValue(i);
                if (value != DBNull.Value)
                {
                    sb.Append(HtmlEncode(ConvertValueToString(value) ?? ""));
                }
                sb.Append("</td>");
            }
            sb.AppendLine("</tr>");
        }

        sb.AppendLine("</table>");

        await context.Response.WriteAsync(sb.ToString(), context.RequestAborted);
    }

    private static string HtmlEncode(string value)
    {
        return value
            .Replace("&", "&amp;")
            .Replace("<", "&lt;")
            .Replace(">", "&gt;")
            .Replace("\"", "&quot;");
    }

    private static string? ConvertValueToString(object value)
    {
        if (value == DBNull.Value || value is null)
            return null;

        return value switch
        {
            TimeSpan ts => ts.ToString(),
            System.Net.IPAddress ip => ip.ToString(),
            DateTime dt => dt.ToString("o"),
            DateTimeOffset dto => dto.ToString("o"),
            _ => value.ToString()
        };
    }
}