using Dapper;
using FLSDAL.DTO.Instance;
using FLSDAL.Query.Instance;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace FLSDAL.CustomCode.Instance;

public class FlsStepSequenceDAL : IFlsStepSequenceDAL
{
    private readonly IQueryExecutor _qe;

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

    public async Task InsertStepSequenceAsync(int stepSequenceId, SaveFlsStepSequenceRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsStepSequenceQB.INSERT_STEP_SEQUENCE,
            new
            {
                StepSequenceId = stepSequenceId,
                req.FlsInstanceId,
                req.GroupId,
                req.StepNo,
                req.StepName,
                req.IsUnlocked,
                req.UnlockedAt,
                TenantId       = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateStepSequenceAsync(SaveFlsStepSequenceRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsStepSequenceQB.UPDATE_STEP_SEQUENCE,
            new
            {
                req.StepSequenceId,
                req.FlsInstanceId,
                req.GroupId,
                req.StepNo,
                req.StepName,
                req.IsUnlocked,
                req.UnlockedAt,
                TenantId       = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<bool> DeleteStepSequenceAsync(int stepSequenceId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsStepSequenceQB.DELETE_STEP_SEQUENCE,
            new { StepSequenceId = stepSequenceId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<FlsStepSequenceRow?> GetStepSequenceByIdAsync(int stepSequenceId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FlsStepSequenceRow>(
            login,
            FlsStepSequenceQB.GET_STEP_SEQUENCE_BY_ID,
            new { StepSequenceId = stepSequenceId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task<IEnumerable<FlsStepSequenceRow>> GetStepSequencesByInstanceAsync(int flsInstanceId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsStepSequenceRow>(
            login,
            FlsStepSequenceQB.GET_STEP_SEQUENCES_BY_INSTANCE,
            new { FlsInstanceId = flsInstanceId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsStepSequenceRow>();

    public async Task<IEnumerable<FlsStepSequenceRow>> GetStepSequencesByGroupAsync(int groupId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsStepSequenceRow>(
            login,
            FlsStepSequenceQB.GET_STEP_SEQUENCES_BY_GROUP,
            new { GroupId = groupId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsStepSequenceRow>();

    public async Task<IEnumerable<FlsStepSequencePicklistDTO>> GetSelectListFlsStepSequence(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = FlsStepSequenceQB.GET_SELECTLIST_STEPSEQUENCE;
        var parameters = new DynamicParameters();
        parameters.Add("firstnumber", firstNumber);
        parameters.Add("maxresult",   maxResult);
        parameters.Add("TenantId",    login.ClientId);

        ApplySelectListCriteria(ref sql, parameters, criteriaDTO,           suffix: "");
        ApplySelectListCriteria(ref sql, parameters, login.UserCriteriaDTO, suffix: "_u");

        sql = sql.Replace("{CRITERIA}", "");

        return await _qe.QueryAsync<FlsStepSequencePicklistDTO>(login, sql, parameters)
               ?? Enumerable.Empty<FlsStepSequencePicklistDTO>();
    }

    // Maps CriteriaDTO field names to MFLSSTEPSEQUENCE columns.
    private static void ApplySelectListCriteria(
        ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix)
    {
        if (criteriaDTO?.SectionCriteriaList == null) return;

        var conditions = new List<(string Cond, string Param, object Val, CriteriaDTO.AttributeJoinOperationType Join)>();

        int idx = 0;
        foreach (var section in criteriaDTO.SectionCriteriaList)
        {
            foreach (var attr in section.AttributesCriteriaList)
            {
                string? fieldValue = attr.FieldValue?.ToString();
                if (string.IsNullOrEmpty(fieldValue)) continue;

                string? column = GetColumnForField(attr.FieldName.ToLower());
                if (column == null) continue;

                string paramName = $"{attr.FieldName.ToLower().Replace(".", "_")}{suffix}_{idx++}";

                if ((attr.OperationType == CriteriaDTO.OperationType.In ||
                     attr.OperationType == CriteriaDTO.OperationType.NotIn)
                    && attr.InArray?.Length > 0)
                {
                    string op = attr.OperationType == CriteriaDTO.OperationType.In ? "IN" : "NOT IN";
                    var vals = attr.InArray.Select(v => Convert.ToInt32(v?.ToString())).ToList();
                    conditions.Add(($"{column} {op} @{paramName}", paramName, vals, attr.JoinType));
                    continue;
                }

                var (condSql, condVal) = BuildCondition(column, paramName, attr.OperationType, fieldValue);
                if (condSql == null) continue;

                conditions.Add((condSql, paramName, condVal, attr.JoinType));
            }
        }

        int i = 0;
        while (i < conditions.Count)
        {
            var (cond, pname, pval, joinType) = conditions[i];

            if (joinType == CriteriaDTO.AttributeJoinOperationType.Or)
            {
                var orParts = new List<string> { cond };
                parameters.Add(pname, pval);
                i++;
                while (i < conditions.Count)
                {
                    var (nc, np, nv, nj) = conditions[i];
                    parameters.Add(np, nv);
                    orParts.Add(nc);
                    i++;
                    if (nj != CriteriaDTO.AttributeJoinOperationType.Or) break;
                }
                sql = sql.Replace("{CRITERIA}", $" AND ({string.Join(" OR ", orParts)}) {{CRITERIA}}");
            }
            else
            {
                parameters.Add(pname, pval);
                sql = sql.Replace("{CRITERIA}", $" AND {cond} {{CRITERIA}}");
                i++;
            }
        }
    }

    private static string? GetColumnForField(string fieldName) => fieldName switch
    {
        "id" or "stepsequenceid" => "s.STEPSEQUENCEID",
        "flsinstanceid"          => "s.FLSINSTANCEID",
        "groupid"                => "s.GROUPID",
        "stepno"                 => "s.STEPNO",
        _                        => null
    };

    private static (string? Cond, object Val) BuildCondition(
        string column, string paramName, CriteriaDTO.OperationType opType, string fieldValue)
    {
        object asVal = (object)Convert.ToInt32(fieldValue);

        return opType switch
        {
            CriteriaDTO.OperationType.Equal    or
            CriteriaDTO.OperationType.In       => ($"{column} = @{paramName}",   asVal),
            CriteriaDTO.OperationType.NotEqual or
            CriteriaDTO.OperationType.NotIn    => ($"{column} <> @{paramName}",  asVal),
            CriteriaDTO.OperationType.GreaterThan          => ($"{column} > @{paramName}",  asVal),
            CriteriaDTO.OperationType.LessThan             => ($"{column} < @{paramName}",  asVal),
            CriteriaDTO.OperationType.GreaterThanOrEqualTo => ($"{column} >= @{paramName}", asVal),
            CriteriaDTO.OperationType.LessThanOrEqualTo    => ($"{column} <= @{paramName}", asVal),
            _ => (null, (object)0)
        };
    }
}