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 FlsInstanceDAL : IFlsInstanceDAL
{
    private readonly IQueryExecutor _qe;

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

    public async Task<IEnumerable<FlsInstancePicklistDTO>> GetSelectListFlsInstance(
        int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = FlsInstanceQB.GET_SELECTLIST_FLSINSTANCE;
        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<FlsInstancePicklistDTO>(login, sql, parameters)
               ?? Enumerable.Empty<FlsInstancePicklistDTO>();
    }

    public async Task<IEnumerable<FlsInstanceGroupPicklistDTO>> GetSelectListFlsInstanceGroup(
        int flsInstanceId, int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = FlsInstanceQB.GET_SELECTLIST_FLSINSTANCEGROUP;
        var parameters = new DynamicParameters();
        parameters.Add("FlsInstanceId", flsInstanceId);
        parameters.Add("firstnumber",   firstNumber);
        parameters.Add("maxresult",     maxResult);
        parameters.Add("TenantId",      login.ClientId);

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

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

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

    // Maps CriteriaDTO field names to MFLSINSTANCE/MFLSINSTANCEGROUP columns. Called twice: the caller's
    // CriteriaDTO (suffix="") and LoginDTO.UserCriteriaDTO (suffix="_u") for access scoping.
    private static void ApplySelectListCriteria(
        ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix, bool isGroup = false)
    {
        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 = isGroup
                    ? GetGroupColumnForField(attr.FieldName.ToLower())
                    : 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 "flsinstanceid"      => "i.FLSINSTANCEID",
        "code" or "instancecode"     => "i.INSTANCECODE",
        "name" or "instancename"     => "i.INSTANCENAME",
        "flsregistrationid"          => "i.FLSREGISTRATIONID",
        "instancestatus" or "status" => "i.INSTANCESTATUS",
        _                            => null
    };

    private static string? GetGroupColumnForField(string fieldName) => fieldName switch
    {
        "id" or "groupid"   => "g.GROUPID",
        "code" or "groupcode" => "g.GROUPCODE",
        "name" or "groupname" => "g.GROUPNAME",
        "groupstatus" or "status" => "g.GROUPSTATUS",
        _                    => null
    };

    private static readonly HashSet<string> _textColumns =
        new(StringComparer.OrdinalIgnoreCase)
        {
            "i.INSTANCECODE", "i.INSTANCENAME",
            "g.GROUPCODE", "g.GROUPNAME"
        };

    private static (string? Cond, object Val) BuildCondition(
        string column, string paramName, CriteriaDTO.OperationType opType, string fieldValue)
    {
        bool isText = _textColumns.Contains(column);
        object asVal = isText ? (object)fieldValue : (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.Like      => ($"{column} LIKE @{paramName}", (object)$"%{fieldValue}%"),
            CriteriaDTO.OperationType.StartWith => ($"{column} LIKE @{paramName}", (object)$"{fieldValue}%"),
            CriteriaDTO.OperationType.EndsWith  => ($"{column} LIKE @{paramName}", (object)$"%{fieldValue}"),
            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)
        };
    }

    public async Task InsertInstanceAsync(int flsInstanceId, CreateFlsInstanceRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.INSERT_INSTANCE,
            new
            {
                FlsInstanceId     = flsInstanceId,
                req.FlsRegistrationId,
                req.InstanceCode,
                req.InstanceName,
                req.EntityObjectId,
                req.PeriodId,
                req.MaxSubmissions,
                IsAnonymous       = req.IsAnonymous ? 1 : 0,
                TenantId          = login.ClientId,
                UserId            = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateInstanceAsync(int flsInstanceId, CreateFlsInstanceRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.UPDATE_INSTANCE,
            new
            {
                FlsInstanceId  = flsInstanceId,
                req.InstanceName,
                req.EntityObjectId,
                req.PeriodId,
                req.MaxSubmissions,
                IsAnonymous    = req.IsAnonymous ? 1 : 0,
                TenantId       = login.ClientId,
                UserId         = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<Dictionary<string, int>> InsertGroupsAsync(
        int flsInstanceId, List<FlsGroupRequest> groups, List<int> groupIds, LoginDTO login, CancellationToken ct)
    {
        var groupCodeToId = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);

        for (int i = 0; i < groups.Count; i++)
        {
            var g = groups[i];
            int groupId = groupIds[i];

            await _qe.ExecuteAsync(
                login,
                FlsInstanceQB.INSERT_GROUP,
                new
                {
                    GroupId             = groupId,
                    FlsInstanceId       = flsInstanceId,
                    SlNo                = i + 1,
                    g.GroupCode,
                    g.GroupName,
                    g.ContextObjectTypeId,
                    g.ContextObjectId,
                    g.ContextBizTypeId,
                    g.ContextReference,
                    g.BlockingFlag,
                    g.BlockingSpName,
                    g.OpensFrom,
                    g.ClosesOn,
                    g.DeadlineDt,
                    g.StepCount,
                    TenantId            = login.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false);

            groupCodeToId[g.GroupCode] = groupId;
        }

        return groupCodeToId;
    }

    public async Task InsertInchargesAsync(
        int flsInstanceId, List<FlsInchargeRequest> incharges, List<int> inchargeIds,
        Dictionary<string, int> groupCodeToId, LoginDTO login, CancellationToken ct)
    {
        for (int i = 0; i < incharges.Count; i++)
        {
            var inc = incharges[i];
            int resolvedGroupId = inc.GroupCode is not null && groupCodeToId.TryGetValue(inc.GroupCode, out var gid)
                ? gid
                : -1;

            await _qe.ExecuteAsync(
                login,
                FlsInstanceQB.INSERT_INCHARGE,
                new
                {
                    InstanceInchargeId = inchargeIds[i],
                    FlsInstanceId      = flsInstanceId,
                    inc.RespondentType,
                    inc.InchargeRefId,
                    inc.InchargeRole,
                    GroupId            = resolvedGroupId,
                    TenantId           = login.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false);
        }
    }

    public async Task UpsertScheduleAsync(
        int flsInstanceId, int instanceScheduleId, DateTime opensFrom, DateTime closesOn,
        DateTime? globalDeadlineDt, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.UPSERT_SCHEDULE,
            new
            {
                InstanceScheduleId = instanceScheduleId,
                FlsInstanceId      = flsInstanceId,
                OpensFrom          = opensFrom,
                ClosesOn           = closesOn,
                GlobalDeadlineDt   = globalDeadlineDt,
                TenantId           = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task OpenAsync(int flsInstanceId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.OPEN_INSTANCE,
            new { FlsInstanceId = flsInstanceId, TenantId = login.ClientId, UserId = login.UserId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task ControlAsync(int flsInstanceId, int action, string? actionNote, LoginDTO login, CancellationToken ct)
    {
        // Action: 0=Pause, 1=Resume, 2=Close, 3=Archive → INSTANCESTATUS
        int newStatus = action switch
        {
            0 => 3, // Pause
            1 => 2, // Resume
            2 => 4, // Close
            3 => 5, // Archive
            _ => throw new ArgumentOutOfRangeException(nameof(action), action, "Invalid instance control action.")
        };

        await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.CONTROL_INSTANCE,
            new
            {
                FlsInstanceId = flsInstanceId,
                NewStatus     = newStatus,
                ActionNote    = actionNote,
                TenantId      = login.ClientId,
                UserId        = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<FlsInstanceRow?> GetByIdAsync(int flsInstanceId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FlsInstanceRow>(
            login,
            FlsInstanceQB.GET_INSTANCE_BY_ID,
            new { FlsInstanceId = flsInstanceId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

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

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

    public async Task InsertGroupOnlyAsync(int groupId, SaveFlsInstanceGroupRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.INSERT_GROUP_SINGLE,
            new
            {
                GroupId              = groupId,
                req.FlsInstanceId,
                req.SlNo,
                req.GroupCode,
                req.GroupName,
                req.ContextObjectTypeId,
                req.ContextObjectId,
                req.ContextBizTypeId,
                req.ContextReference,
                req.BlockingFlag,
                req.BlockingSpName,
                req.OpensFrom,
                req.ClosesOn,
                req.DeadlineDt,
                req.StepCount,
                TenantId             = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateGroupOnlyAsync(SaveFlsInstanceGroupRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.UPDATE_GROUP,
            new
            {
                req.GroupId,
                req.SlNo,
                req.GroupCode,
                req.GroupName,
                req.ContextObjectTypeId,
                req.ContextObjectId,
                req.ContextBizTypeId,
                req.ContextReference,
                req.BlockingFlag,
                req.BlockingSpName,
                req.OpensFrom,
                req.ClosesOn,
                req.DeadlineDt,
                req.StepCount,
                TenantId             = login.ClientId,
                UserId               = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<bool> DeleteGroupAsync(int groupId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsInstanceQB.DELETE_GROUP,
            new { GroupId = groupId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<FlsGroupRow?> GetGroupByIdAsync(int groupId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FlsGroupRow>(
            login,
            FlsInstanceQB.GET_GROUP_BY_ID,
            new { GroupId = groupId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

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

    public async Task<bool> CodeExistsAsync(string code, int excludeId, LoginDTO login, CancellationToken ct)
    {
        var count = await _qe.ExecuteScalarAsync<int>(
            login, FlsInstanceQB.CODE_EXISTS,
            new { Code = code, TenantId = login.ClientId, ExcludeId = excludeId },
            cancellationToken: ct).ConfigureAwait(false);
        return count > 0;
    }

    public async Task<IEnumerable<FlsInstanceRow>> GetOpenInstancesForReminderAsync(
        int tenantId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsInstanceRow>(
            login, FlsInstanceQB.GET_OPEN_INSTANCES_FOR_REMINDER, new { TenantId = tenantId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsInstanceRow>();

    public async Task<IEnumerable<FlsInstanceRow>> GetInstancesByRegistrationAsync(
        int flsRegistrationId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<FlsInstanceRow>(
            login,
            FlsInstanceQB.GET_INSTANCES_BY_REGISTRATION,
            new { FlsRegistrationId = flsRegistrationId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<FlsInstanceRow>();
}