using Dapper;
using ECPDAL.Correspondence.DTOs;
using ECPDAL.Correspondence.Interfaces;
using ECPDAL.Correspondence.QueryBuilders;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace ECPDAL.Correspondence.Implementations;

public class CorrespondenceDAL(IQueryExecutor _qe) : ICorrespondenceDAL
{
    public async Task<CorrespondenceDTO?> GetCorrespondenceAsync(
        int correspondenceId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<CorrespondenceDTO>(
            login,
            CorrespondenceQB.GET_CORRESPONDENCE,
            new { CorrespondenceId = correspondenceId, login.DatabaseName },
            cancellationToken: ct).ConfigureAwait(false);
        return rows.FirstOrDefault();
    }

    public async Task<List<CorrespondenceDTO>> GetCorrespondenceHistoryAsync(
        int objectTypeId, int objectId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<CorrespondenceDTO>(
            login,
            CorrespondenceQB.GET_CORRESPONDENCE_HISTORY,
            new { ObjectTypeId = objectTypeId, ObjectId = objectId, login.DatabaseName },
            cancellationToken: ct).ConfigureAwait(false);
        return rows.ToList();
    }

    public async Task<PagedResult<CorrespondenceDTO>> GetCorrespondenceListAsync(
        CriteriaDTO? criteriaDTO, int pageNumber, int pageSize, LoginDTO login, CancellationToken ct)
    {
        string dataSql = CorrespondenceQB.GET_CORRESPONDENCE_LIST;
        string countSql = CorrespondenceQB.GET_CORRESPONDENCE_LIST_COUNT;

        int effectivePageNumber = pageNumber < 1 ? 1 : pageNumber;
        int effectivePageSize = pageSize < 1 ? 20 : pageSize;

        var dataParams = new DynamicParameters();
        dataParams.Add("DatabaseName", login.DatabaseName);
        dataParams.Add("Offset", (effectivePageNumber - 1) * effectivePageSize);
        dataParams.Add("PageSize", effectivePageSize);

        var countParams = new DynamicParameters();
        countParams.Add("DatabaseName", login.DatabaseName);

        ApplyCorrespondenceListCriteria(ref dataSql, dataParams, criteriaDTO);
        ApplyCorrespondenceListCriteria(ref countSql, countParams, criteriaDTO);
        dataSql = dataSql.Replace("{CRITERIA}", "");
        countSql = countSql.Replace("{CRITERIA}", "");

        var items = await _qe.QueryAsync<CorrespondenceDTO>(login, dataSql, dataParams, cancellationToken: ct)
            .ConfigureAwait(false);
        var total = await _qe.ExecuteScalarAsync<int>(login, countSql, countParams, cancellationToken: ct)
            .ConfigureAwait(false);

        return new PagedResult<CorrespondenceDTO> { Items = items.ToList(), TotalCount = total };
    }

    // Maps CriteriaDTO FieldName -> TCORRESPONDENCE column, mirroring MeetingDAL's
    // ApplyMeetingListCriteria shape (itself mirroring CRM TaskDAL's ApplySelectListTaskCriteria).
    private static void ApplyCorrespondenceListCriteria(ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO)
    {
        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 = GetCorrespondenceColumnForField(attr.FieldName.ToLower());
                if (column == null) continue;

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

                var (condSql, condVal) = BuildCorrespondenceCondition(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? GetCorrespondenceColumnForField(string fieldName) => fieldName switch
    {
        "direction"            => "C.DIRECTION",
        "mode"                 => "C.MODE",
        "actionstatus"         => "C.ACTIONSTATUS",
        "correspondencetypeid" => "C.CORRESPONDENCETYPEID",
        "objecttypeid"         => "C.OBJECTTYPEID",
        "objectid"             => "C.OBJECTID",
        "createdon"            => "C.CREATEDON",
        "senton"               => "C.SENTON",
        _                      => null
    };

    private static readonly HashSet<string> _dateCorrespondenceColumns =
        new(StringComparer.OrdinalIgnoreCase) { "C.CREATEDON", "C.SENTON" };

    private static (string? Cond, object Val) BuildCorrespondenceCondition(
        string column, string paramName, CriteriaDTO.OperationType opType, string fieldValue)
    {
        object asVal = _dateCorrespondenceColumns.Contains(column) ? (object)DateTime.Parse(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.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> SaveCorrespondenceAsync(
        CorrespondenceDTO dto, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            CorrespondenceQB.SAVE_CORRESPONDENCE,
            dto,
            cancellationToken: ct).ConfigureAwait(false);
        return dto.CorrespondenceId;
    }

    public async Task UpdateActionStatusAsync(
        int correspondenceId, byte actionStatus, DateTime? sentOn, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            CorrespondenceQB.UPDATE_ACTION_STATUS,
            new
            {
                CorrespondenceId = correspondenceId,
                ActionStatus     = actionStatus,
                SentOn           = sentOn,
                ModifiedById     = modifiedById,
                ModifiedOn       = DateTime.UtcNow,
                login.DatabaseName,
            },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<bool> UpdateCorrespondenceAsync(
        UpdateCorrespondenceDTO dto, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            CorrespondenceQB.UPDATE_CORRESPONDENCE,
            new
            {
                dto.CorrespondenceId,
                dto.CorrespondenceTypeId,
                dto.BizTransactionTypeId,
                dto.Mode,
                dto.Subject,
                dto.FromId,
                dto.ToIds,
                dto.CcIds,
                dto.BccIds,
                dto.ScheduledOn,
                dto.BizTransactionId,
                dto.OuId,
                dto.Version,
                ModifiedById = modifiedById,
                ModifiedOn   = DateTime.UtcNow,
                login.DatabaseName,
            },
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0;
    }

    public async Task<bool> DeleteCorrespondenceAsync(
        int correspondenceId, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        int rows = await _qe.ExecuteAsync(
            login,
            CorrespondenceQB.DELETE_CORRESPONDENCE,
            new
            {
                CorrespondenceId = correspondenceId,
                ModifiedById     = modifiedById,
                ModifiedOn       = DateTime.UtcNow,
                login.DatabaseName,
            },
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0;
    }
}