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

namespace FLSDAL.CustomCode.Registration;

public class FlsRegistrationDAL : IFlsRegistrationDAL
{
    private readonly IQueryExecutor _qe;

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

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

    // Maps CriteriaDTO field names to MFLSREGISTRATION/MFLSACCESSRULE 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 isAccessRule = 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 = isAccessRule
                    ? GetAccessRuleColumnForField(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 "flsregistrationid" => "r.FLSREGISTRATIONID",
        "code" or "registrationcode" => "r.REGISTRATIONCODE",
        "name" or "registrationname" => "r.REGISTRATIONNAME",
        "formtemplateid"             => "r.FORMTEMPLATEID",
        "moduleid"                   => "r.MODULEID",
        "status"                     => "r.STATUS",
        _                            => null
    };

    private static string? GetAccessRuleColumnForField(string fieldName) => fieldName switch
    {
        "id" or "accessruleid"       => "AR.ACCESSRULEID",
        "flsregistrationid"          => "AR.FLSREGISTRATIONID",
        "principaltype"              => "AR.PRINCIPALTYPE",
        "principalid"                => "AR.PRINCIPALID",
        "accesstype"                 => "AR.ACCESSTYPE",
        "scope"                      => "AR.SCOPE",
        "scopeobjectid"              => "AR.SCOPEOBJECTID",
        "status"                     => "AR.STATUS",
        _                            => null
    };

    private static readonly HashSet<string> _textColumns =
        new(StringComparer.OrdinalIgnoreCase) { "r.REGISTRATIONCODE", "r.REGISTRATIONNAME" };

    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<int> CreateAsync(
        int flsRegistrationId, string registrationCode, string registrationName,
        int formTemplateId, int moduleId, int completionMethod,
        int allowDraftResponse, int allowMultiAttempt, int isMandatory,
        int tokenExpiryHours, int triggeredBy,
        string? defaultEipTemplCode, string? webhookPath,
        LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.INSERT_REGISTRATION,
            new
            {
                FlsRegistrationId  = flsRegistrationId,
                RegistrationCode    = registrationCode,
                RegistrationName    = registrationName,
                FormTemplateId      = formTemplateId,
                ModuleId            = moduleId,
                CompletionMethod    = completionMethod,
                AllowDraftResponse  = allowDraftResponse,
                AllowMultiAttempt   = allowMultiAttempt,
                IsMandatory         = isMandatory,
                TokenExpiryHours    = tokenExpiryHours,
                TriggeredBy         = triggeredBy,
                DefaultEipTemplCode = defaultEipTemplCode,
                WebhookPath         = webhookPath,
                TenantId            = login.ClientId,
                UserId              = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return flsRegistrationId;
    }

    public async Task<bool> UpdateAsync(
        int flsRegistrationId, string registrationName,
        int formTemplateId, int moduleId, int completionMethod,
        int allowDraftResponse, int allowMultiAttempt, int isMandatory,
        int tokenExpiryHours, int triggeredBy,
        string? defaultEipTemplCode, string? webhookPath,
        LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.UPDATE_REGISTRATION,
            new
            {
                FlsRegistrationId   = flsRegistrationId,
                RegistrationName    = registrationName,
                FormTemplateId      = formTemplateId,
                ModuleId            = moduleId,
                CompletionMethod    = completionMethod,
                AllowDraftResponse  = allowDraftResponse,
                AllowMultiAttempt   = allowMultiAttempt,
                IsMandatory         = isMandatory,
                TokenExpiryHours    = tokenExpiryHours,
                TriggeredBy         = triggeredBy,
                DefaultEipTemplCode = defaultEipTemplCode,
                WebhookPath         = webhookPath,
                TenantId            = login.ClientId,
                UserId              = login.UserId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<FlsRegistrationRow?> GetByIdAsync(int flsRegistrationId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FlsRegistrationRow>(
            login,
            FlsRegistrationQB.GET_REGISTRATION_BY_ID,
            new { FlsRegistrationId = flsRegistrationId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task<FlsRegistrationRow?> GetByCodeAsync(string code, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FlsRegistrationRow>(
            login,
            FlsRegistrationQB.GET_REGISTRATION_BY_CODE,
            new { RegistrationCode = code, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task<IEnumerable<FlsReminderRuleRow>> GetReminderRulesAsync(
        int flsRegistrationId, LoginDTO login, CancellationToken ct)
    {
        return await _qe.QueryAsync<FlsReminderRuleRow>(
            login,
            FlsRegistrationQB.GET_REMINDER_RULES,
            new { FlsRegistrationId = flsRegistrationId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
               ?? Enumerable.Empty<FlsReminderRuleRow>();
    }

    public async Task<FlsReminderRuleRow?> GetReminderRuleByIdAsync(int reminderRuleId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FlsReminderRuleRow>(
            login,
            FlsRegistrationQB.GET_REMINDER_RULE_BY_ID,
            new { ReminderRuleId = reminderRuleId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task InsertReminderRuleAsync(int reminderRuleId, SaveFlsReminderRuleRequest req, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.INSERT_REMINDER_RULE,
            new
            {
                ReminderRuleId = reminderRuleId,
                req.FlsRegistrationId,
                req.RuleName,
                req.ReminderTrigger,
                req.DayOffset,
                req.StatusCondition,
                req.ReminderAction,
                req.EipTemplCode,
                req.SortOrder,
                TenantId       = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<bool> UpdateReminderRuleAsync(SaveFlsReminderRuleRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.UPDATE_REMINDER_RULE,
            new
            {
                req.ReminderRuleId,
                req.RuleName,
                req.ReminderTrigger,
                req.DayOffset,
                req.StatusCondition,
                req.ReminderAction,
                req.EipTemplCode,
                req.SortOrder,
                TenantId       = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<bool> DeleteReminderRuleAsync(int reminderRuleId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.DELETE_REMINDER_RULE,
            new { ReminderRuleId = reminderRuleId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<bool> CodeExistsAsync(string code, int excludeId, LoginDTO login, CancellationToken ct)
    {
        const string sql = @"
            SELECT COUNT(1) FROM MFLSREGISTRATION
            WHERE  REGISTRATIONCODE   = @Code
            AND    TENANTID           = @TenantId
            AND    FLSREGISTRATIONID <> @ExcludeId
            AND    STATUS            <> 2";
        var count = await _qe.ExecuteScalarAsync<int>(
            login, sql, new { Code = code, TenantId = login.ClientId, ExcludeId = excludeId },
            cancellationToken: ct).ConfigureAwait(false);
        return count > 0;
    }

    public async Task<bool> DeleteAsync(int flsRegistrationId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.DELETE_REGISTRATION,
            new { FlsRegistrationId = flsRegistrationId, TenantId = login.ClientId, UserId = login.UserId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

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

    public async Task<FlsRegistrationRow?> GetByWebhookProviderAsync(
        string providerCode, LoginDTO login, CancellationToken ct)
    {
        const string sql = @"
            SELECT FLSREGISTRATIONID, REGISTRATIONCODE, REGISTRATIONNAME,
                   FORMTEMPLATEID, MODULEID,
                   CAST(COMPLETIONMETHOD AS INT) AS COMPLETIONMETHOD,
                   CAST(ALLOWDRAFTRESPONSE AS INT) AS ALLOWDRAFTRESPONSE,
                   CAST(ALLOWMULTIATTEMPT AS INT) AS ALLOWMULTIATTEMPT,
                   CAST(ISMANDATORY AS INT) AS ISMANDATORY,
                   CAST(TOKENEXPIRYHOURS AS INT) AS TOKENEXPIRYHOURS,
                   CAST(TRIGGEREDBY AS INT) AS TRIGGEREDBY,
                   DEFAULTEIPTEMPLCODE,
                   WEBHOOKPATH, WEBHOOKSECRET,
                   CAST(STATUS AS INT) AS STATUS, TENANTID
            FROM   MFLSREGISTRATION
            WHERE  WEBHOOKPATH LIKE '%' + @ProviderCode + '%'
            AND    TENANTID  = @TenantId
            AND    STATUS   <> 2";
        var rows = await _qe.QueryAsync<FlsRegistrationRow>(
            login, sql,
            new { ProviderCode = providerCode, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task InsertAccessRuleAsync(int accessRuleId, SaveAccessRuleRequest req, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.INSERT_ACCESS_RULE,
            new
            {
                AccessRuleId      = accessRuleId,
                req.FlsRegistrationId,
                req.PrincipalType,
                req.PrincipalId,
                req.AccessType,
                req.Scope,
                req.ScopeObjectId,
                TenantId          = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<bool> UpdateAccessRuleAsync(SaveAccessRuleRequest req, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.UPDATE_ACCESS_RULE,
            new
            {
                req.AccessRuleId,
                req.FlsRegistrationId,
                req.PrincipalType,
                req.PrincipalId,
                req.AccessType,
                req.Scope,
                req.ScopeObjectId,
                TenantId          = login.ClientId
            },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<bool> DeleteAccessRuleAsync(int accessRuleId, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            FlsRegistrationQB.DELETE_ACCESS_RULE,
            new { AccessRuleId = accessRuleId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows > 0;
    }

    public async Task<IEnumerable<FlsAccessRuleRow>> GetAccessRulesAsync(
        int flsRegistrationId, LoginDTO login, CancellationToken ct)
    {
        return await _qe.QueryAsync<FlsAccessRuleRow>(
            login,
            FlsRegistrationQB.GET_ACCESS_RULES,
            new { FlsRegistrationId = flsRegistrationId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false)
               ?? Enumerable.Empty<FlsAccessRuleRow>();
    }

    public async Task<FlsAccessRuleRow?> GetAccessRuleByIdAsync(int accessRuleId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<FlsAccessRuleRow>(
            login,
            FlsRegistrationQB.GET_ACCESS_RULE_BY_ID,
            new { AccessRuleId = accessRuleId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows?.FirstOrDefault();
    }

    public async Task<IEnumerable<FlsAccessRulePicklistDTO>> GetSelectListAccessRule(
        int flsRegistrationId, int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login)
    {
        string sql = FlsRegistrationQB.GET_SELECTLIST_ACCESSRULE;
        var parameters = new DynamicParameters();
        parameters.Add("FlsRegistrationId", flsRegistrationId);
        parameters.Add("firstnumber",       firstNumber);
        parameters.Add("maxresult",         maxResult);
        parameters.Add("TenantId",          login.ClientId);

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

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

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