using System.Text.Json;
using Dapper;
using FLSDAL.DTO.QuickRating;
using FLSDAL.Query.QuickRating;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace FLSDAL.CustomCode.QuickRating;

public class QuickRatingDAL : IQuickRatingDAL
{
    private readonly IQueryExecutor _qe;

    public QuickRatingDAL(IQueryExecutor queryExecutor) => _qe = queryExecutor;

    public async Task<QuickRatingConfigRow?> GetConfigAsync(int objectTypeId, int bizTransactionTypeId, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<QuickRatingConfigRow>(
            login,
            QuickRatingQB.GET_CONFIG,
            new { ObjectTypeId = objectTypeId, BizTransactionTypeId = bizTransactionTypeId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<QuickRatingConfigRow?> GetConfigByIdAsync(int quickRatingConfigId, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<QuickRatingConfigRow>(
            login,
            QuickRatingQB.GET_CONFIG_BY_ID,
            new { QuickRatingConfigId = quickRatingConfigId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<QuickRatingConfigPicklistDTO>> GetSelectListQuickRatingConfig(
        int firstNumber, int maxResult, LoginDTO login)
    {
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);
        parameters.Add("TenantId",    login.ClientId);

        return await _qe.QueryAsync<QuickRatingConfigPicklistDTO>(
            login, QuickRatingQB.GET_SELECTLIST_QUICKRATINGCONFIG, parameters)
               ?? Enumerable.Empty<QuickRatingConfigPicklistDTO>();
    }

    public async Task<bool> DeleteConfigAsync(int quickRatingConfigId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            QuickRatingQB.DELETE_CONFIG,
            new { QuickRatingConfigId = quickRatingConfigId, TenantId = login.ClientId, UserId = login.UserId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task InsertConfigAsync(int quickRatingConfigId, SaveQuickRatingConfigRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            QuickRatingQB.INSERT_CONFIG,
            new
            {
                QuickRatingConfigId = quickRatingConfigId,
                req.ObjectTypeId,
                req.BizTransactionTypeId,
                req.ConfigName,
                req.ScaleType,
                req.ScaleMin,
                req.ScaleMax,
                req.RatingLabelLow,
                req.RatingLabelHigh,
                req.IdentityMode,
                req.AllowMultiRating,
                req.AllowComment,
                req.CommentMaxLength,
                req.ThankYouLabel,
                ShowSummaryAfterSubmit = req.ShowSummaryAfterSubmit ? 1 : 0,
                req.DisplayMode,
                TenantId = login.ClientId,
                UserId   = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateConfigAsync(int quickRatingConfigId, SaveQuickRatingConfigRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            QuickRatingQB.UPDATE_CONFIG,
            new
            {
                QuickRatingConfigId = quickRatingConfigId,
                req.ConfigName,
                req.ScaleType,
                req.ScaleMin,
                req.ScaleMax,
                req.RatingLabelLow,
                req.RatingLabelHigh,
                req.IdentityMode,
                req.AllowMultiRating,
                req.AllowComment,
                req.CommentMaxLength,
                req.ThankYouLabel,
                ShowSummaryAfterSubmit = req.ShowSummaryAfterSubmit ? 1 : 0,
                req.DisplayMode,
                TenantId = login.ClientId,
                UserId   = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<bool> ConfigExistsForObjectAsync(int objectTypeId, int bizTransactionTypeId, int excludeId, LoginDTO login, CancellationToken ct)
    {
        int count = await _qe.ExecuteScalarAsync<int>(
            login,
            QuickRatingQB.CONFIG_EXISTS_FOR_OBJECT,
            new { ObjectTypeId = objectTypeId, BizTransactionTypeId = bizTransactionTypeId, ExcludeId = excludeId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return count > 0;
    }

    public async Task<QuickRatingSummaryRow?> GetSummaryAsync(int objectTypeId, int objectId, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<QuickRatingSummaryRow>(
            login,
            QuickRatingQB.GET_SUMMARY,
            new { ObjectTypeId = objectTypeId, ObjectId = objectId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<int> SaveRatingAsync(
        int quickRatingId, int quickRatingConfigId, int objectTypeId, int objectId,
        decimal ratingValue, string? commentText,
        int submitterType, int submitterId,
        LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            QuickRatingQB.INSERT_RATING,
            new
            {
                QuickRatingId       = quickRatingId,
                QuickRatingConfigId = quickRatingConfigId,
                ObjectTypeId        = objectTypeId,
                ObjectId            = objectId,
                RatingValue         = ratingValue,
                CommentText         = commentText,
                RatingBy            = submitterType,
                RatingByRefId       = submitterId,
                TenantId            = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return quickRatingId;
    }

    public async Task AmendRatingAsync(
        int quickRatingId, decimal ratingValue, string? commentText,
        LoginDTO login, CancellationToken ct)
    {
        const string sql = @"
            UPDATE TQUICKRATING
            SET    RATING       = @RatingValue,
                   COMMENTTEXT  = @CommentText,
                   RATEDON      = GETUTCDATE()
            WHERE  QUICKRATINGID = @QuickRatingId
            AND    TENANTID      = @TenantId";
        await _qe.ExecuteAsync(
            login, sql,
            new { QuickRatingId = quickRatingId, RatingValue = ratingValue, CommentText = commentText, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<QuickRatingSummaryRow?> UpdateSummaryAsync(
        int quickRatingConfigId, int objectTypeId, int objectId, int newSummaryIdIfInserting,
        LoginDTO login, CancellationToken ct)
    {
        var stats = await _qe.QuerySingleAsync<SummaryStatsRow>(
            login,
            QuickRatingQB.RECOMPUTE_SUMMARY_STATS,
            new { ObjectTypeId = objectTypeId, ObjectId = objectId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

        var distRows = await _qe.QueryAsync<DistRow>(
            login,
            QuickRatingQB.GET_RATING_DISTRIBUTION,
            new { ObjectTypeId = objectTypeId, ObjectId = objectId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);

        string distJson = JsonSerializer.Serialize(
            (distRows ?? Enumerable.Empty<DistRow>()).ToDictionary(d => d.RatingValue.ToString(), d => d.RatingCount));

        int total = stats?.RatingCount ?? 0;
        decimal? npsScore = total > 0 && stats is not null
            ? Math.Round((decimal)(stats.NpsPromoters - stats.NpsDetractors) / total * 100, 2)
            : null;

        await _qe.ExecuteAsync(
            login,
            QuickRatingQB.UPSERT_SUMMARY,
            new
            {
                QuickRatingSummaryId = newSummaryIdIfInserting,
                QuickRatingConfigId  = quickRatingConfigId,
                ObjectTypeId         = objectTypeId,
                ObjectId             = objectId,
                RatingCount          = total,
                AvgRating            = stats?.AvgRating ?? 0,
                MinRating            = stats?.MinRating,
                MaxRating            = stats?.MaxRating,
                RatingDistJson       = distJson,
                NpsPromoters         = stats?.NpsPromoters,
                NpsPassives          = stats?.NpsPassives,
                NpsDetractors        = stats?.NpsDetractors,
                NpsScore             = npsScore,
                TenantId             = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

        return await GetSummaryAsync(objectTypeId, objectId, login, ct).ConfigureAwait(false);
    }

    private record SummaryStatsRow
    {
        public int      RatingCount   { get; init; }
        public decimal? AvgRating     { get; init; }
        public decimal? MinRating     { get; init; }
        public decimal? MaxRating     { get; init; }
        public int      NpsPromoters  { get; init; }
        public int      NpsPassives   { get; init; }
        public int      NpsDetractors { get; init; }
    }
    private record DistRow(int RatingValue, int RatingCount);

    public async Task<QuickRatingRow?> GetUserRatingAsync(
        int objectTypeId, int objectId, int userId,
        LoginDTO login, CancellationToken ct)
    {
        const string sql = @"
            SELECT TOP 1
                   QUICKRATINGID, QUICKRATINGCONFIGID, OBJECTTYPEID, OBJECTID,
                   RATING, COMMENTTEXT,
                   CAST(RATINGBY AS INT) AS RATINGBY, RATINGBYREFID,
                   CAST(ISLATEST AS INT) AS ISLATEST,
                   CAST(STATUS AS INT) AS STATUS, RATEDON, TENANTID
            FROM   TQUICKRATING
            WHERE  OBJECTTYPEID = @ObjectTypeId
            AND    OBJECTID     = @ObjectId
            AND    RATINGBYREFID = @UserId
            AND    ISLATEST     = 1
            AND    STATUS       <> 2
            AND    TENANTID     = @TenantId
            ORDER BY RATEDON DESC";
        var rows = await _qe.QueryAsync<QuickRatingRow>(
            login, sql,
            new { ObjectTypeId = objectTypeId, ObjectId = objectId, UserId = userId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task<IEnumerable<QuickRatingRow>> GetRatingsPagedAsync(
        int objectTypeId, int objectId, int pageNo, int pageSize,
        LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<QuickRatingRow>(
            login,
            QuickRatingQB.GET_RATINGS_PAGED,
            new
            {
                ObjectTypeId = objectTypeId,
                ObjectId     = objectId,
                TenantId     = login.ClientId,
                Offset       = (pageNo - 1) * pageSize,
                PageSize     = pageSize
            },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<QuickRatingRow>();

    public async Task<int> GetRatingsCountAsync(int objectTypeId, int objectId, LoginDTO login, CancellationToken ct)
    {
        const string sql = @"
            SELECT COUNT(1) FROM TQUICKRATING
            WHERE OBJECTTYPEID = @ObjectTypeId AND OBJECTID = @ObjectId AND TENANTID = @TenantId";
        return await _qe.ExecuteScalarAsync<int>(
            login, sql,
            new { ObjectTypeId = objectTypeId, ObjectId = objectId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<IEnumerable<QuickRatingTrendRow>> GetTrendAsync(
        int objectTypeId, int objectId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<QuickRatingTrendRow>(
            login,
            QuickRatingQB.GET_TREND,
            new { ObjectTypeId = objectTypeId, ObjectId = objectId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<QuickRatingTrendRow>();
}