using System.Data;
using System.Security;
using Dapper;
using FLSDAL.DTO.Bridge;
using FLSDAL.Query.Bridge;
using FLSDAL.Query.Dispatch;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace FLSDAL.CustomCode.Bridge;

public class FlsBridgeDAL : IFlsBridgeDAL
{
    private readonly IQueryExecutor _qe;

    // Whitelist guards against SP injection via MFLSBRIDGECONFIG.SUMMARYSPNAME (plan §4.4)
    private static readonly HashSet<string> AllowedBridgeSps = new(StringComparer.OrdinalIgnoreCase)
    {
        "SP_TMS_FLS_UPDATESUMMARY",
        "SP_HRMS_APPRAISAL_UPDATESUMMARY",
        "SP_PERM_FLS_UPDATESUMMARY"
    };

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

    public async Task<FlsBridgeConfigRow?> GetBridgeByModuleAsync(int moduleId, LoginDTO login, CancellationToken ct)
        => await _qe.QuerySingleAsync<FlsBridgeConfigRow>(
            login,
            FlsBridgeQB.GET_BRIDGE_BY_MODULE,
            new { ModuleId = moduleId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task CallSummarySpAsync(string spName, int flsInstanceId, int groupId, LoginDTO login, CancellationToken ct)
    {
        if (!AllowedBridgeSps.Contains(spName))
            throw new SecurityException($"Bridge SP '{spName}' is not on the allow-list.");

        using var _ = await _qe.QueryMultipleAsync(
            login,
            spName,
            new { FlsInstanceId = flsInstanceId, GroupId = groupId, TenantId = login.ClientId },
            null,
            CommandType.StoredProcedure).ConfigureAwait(false);
    }

    public async Task LogDispatchAsync(
        int flsInstanceId, int groupId, int flsRespondentId,
        int dispatchType, int channel, string? eipTemplCode, string? eipCommandRef,
        string? recipientMail, int deliveryStatus,
        LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsDispatchQB.INSERT_DISPATCH_LOG,
            new
            {
                FlsInstanceId   = flsInstanceId,
                GroupId         = groupId,
                FlsRespondentId = flsRespondentId,
                DispatchType    = dispatchType,
                Channel         = channel,
                EipTemplCode    = eipTemplCode,
                EipCommandRef   = eipCommandRef,
                RecipientMail   = recipientMail,
                DeliveryStatus  = deliveryStatus,
                TenantId        = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<TrainerFeedbackSummaryRow>> GetTrainerFeedbackSummaryAsync(
        int? trainerId, int? flsInstanceId, LoginDTO login, CancellationToken ct)
    {
        string sql;
        object parameters;

        if (trainerId.HasValue && flsInstanceId.HasValue)
        {
            sql = FlsTrainerFeedbackQB.GET_TRAINER_FEEDBACK_SUMMARY_BY_BOTH;
            parameters = new { TrainerId = trainerId.Value, FlsInstanceId = flsInstanceId.Value, TenantId = login.ClientId };
        }
        else if (trainerId.HasValue)
        {
            sql = FlsTrainerFeedbackQB.GET_TRAINER_FEEDBACK_SUMMARY_BY_TRAINER;
            parameters = new { TrainerId = trainerId.Value, TenantId = login.ClientId };
        }
        else if (flsInstanceId.HasValue)
        {
            sql = FlsTrainerFeedbackQB.GET_TRAINER_FEEDBACK_SUMMARY_BY_INSTANCE;
            parameters = new { FlsInstanceId = flsInstanceId.Value, TenantId = login.ClientId };
        }
        else
        {
            return Enumerable.Empty<TrainerFeedbackSummaryRow>();
        }

        return await _qe.QueryAsync<TrainerFeedbackSummaryRow>(login, sql, parameters, cancellationToken: ct).ConfigureAwait(false)
               ?? Enumerable.Empty<TrainerFeedbackSummaryRow>();
    }
}