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

namespace ECPDAL.Meeting.Implementations;

public class MeetingDAL(IQueryExecutor _qe) : IMeetingDAL
{
    public async Task<MeetingDTO?> GetMeetingAsync(int meetingId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<MeetingDTO>(
            login,
            MeetingQB.GET_MEETING,
            new { MeetingId = meetingId, login.DatabaseName },
            cancellationToken: ct).ConfigureAwait(false);
        return rows.FirstOrDefault();
    }

    public async Task<List<MeetingTypePicklistDTO>> GetSelectListMeetingTypeAsync(int gcmTypeId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<MeetingTypePicklistDTO>(
            login,
            MeetingQB.GET_SELECTLIST_MEETINGTYPE,
            new { GcmTypeId = gcmTypeId },
            cancellationToken: ct).ConfigureAwait(false);
        return rows.ToList();
    }

    public async Task<int> EnsureMeetingTypeGcmTypeIdAsync(LoginDTO login, CancellationToken ct)
    {
        var existing = await _qe.QueryAsync<int>(
            login,
            MeetingQB.GET_GCMTYPE_BY_CODE,
            new { GcmTypeCode = MeetingQB.GCMTYPECODE_MEETINGTYPE },
            cancellationToken: ct).ConfigureAwait(false);
        var existingId = existing.FirstOrDefault();
        if (existingId != 0)
            return existingId;

        // First use in this environment — seed MGCMTYPE + its 4 MGCM rows inside a transaction so
        // a concurrent first call can't create duplicates (re-checked after acquiring the
        // transaction, same TOCTOU guard as CollaborationSpaceBLL's "already a member" check).
        var transaction = await _qe.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            var recheck = await _qe.QueryAsync<int>(
                login,
                MeetingQB.GET_GCMTYPE_BY_CODE,
                new { GcmTypeCode = MeetingQB.GCMTYPECODE_MEETINGTYPE },
                transaction: transaction,
                cancellationToken: ct).ConfigureAwait(false);
            var recheckId = recheck.FirstOrDefault();
            if (recheckId != 0)
            {
                await _qe.CommitAsync(transaction).ConfigureAwait(false);
                return recheckId;
            }

            var minGcmTypeId = await _qe.ExecuteScalarAsync<int>(
                login, MeetingQB.GET_MIN_GCMTYPEID, transaction: transaction, cancellationToken: ct)
                .ConfigureAwait(false);
            var newGcmTypeId = minGcmTypeId - 1;

            await _qe.ExecuteAsync(
                login,
                MeetingQB.INSERT_GCMTYPE,
                new
                {
                    GcmTypeId          = newGcmTypeId,
                    GcmTypeCode        = MeetingQB.GCMTYPECODE_MEETINGTYPE,
                    GcmTypeDescription = "Meeting Type",
                    ModuleId           = -1,
                    CreatedById  = login.UserId,
                    CreatedOn    = DateTime.UtcNow,
                    ModifiedById = login.UserId,
                    ModifiedOn   = DateTime.UtcNow,
                },
                transaction: transaction,
                cancellationToken: ct).ConfigureAwait(false);

            foreach (var name in new[] { "Planning", "Review", "Standup", "Retrospective" })
            {
                var minGcmId = await _qe.ExecuteScalarAsync<int>(
                    login, MeetingQB.GET_MIN_GCMID, transaction: transaction, cancellationToken: ct)
                    .ConfigureAwait(false);
                var newGcmId = minGcmId - 1;

                await _qe.ExecuteAsync(
                    login,
                    MeetingQB.INSERT_GCM,
                    new
                    {
                        GcmId     = newGcmId,
                        GcmTypeId = newGcmTypeId,
                        GcmCode   = name.ToUpperInvariant(),
                        GcmName   = name,
                    },
                    transaction: transaction,
                    cancellationToken: ct).ConfigureAwait(false);
            }

            await _qe.CommitAsync(transaction).ConfigureAwait(false);
            return newGcmTypeId;
        }
        catch
        {
            await _qe.RollbackAsync(transaction).ConfigureAwait(false);
            throw;
        }
    }

    public async Task<List<MeetingDTO>> GetMeetingListAsync(CriteriaDTO? criteriaDTO, LoginDTO login, CancellationToken ct)
    {
        string sql = MeetingQB.GET_MEETING_LIST;
        var parameters = new DynamicParameters();
        parameters.Add("DatabaseName", login.DatabaseName);

        ApplyMeetingListCriteria(ref sql, parameters, criteriaDTO);
        sql = sql.Replace("{CRITERIA}", "");

        var rows = await _qe.QueryAsync<MeetingDTO>(login, sql, parameters, cancellationToken: ct).ConfigureAwait(false);
        return rows.ToList();
    }

    // Maps CriteriaDTO FieldName -> TMEETING column/condition, mirroring CRM TaskDAL's
    // ApplySelectListTaskCriteria shape. ParticipantUserId is special-cased since it filters via
    // an EXISTS subquery against TMEETINGPARTICIPANT rather than a direct TMEETING column.
    private static void ApplyMeetingListCriteria(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 paramName = $"{attr.FieldName.ToLower().Replace(".", "_")}_{idx++}";
                string fieldNameLower = attr.FieldName.ToLower();

                if (fieldNameLower is "participantuserid" or "participant.id")
                {
                    conditions.Add((
                        $@"EXISTS (SELECT 1 FROM TMEETINGPARTICIPANT P
                                    WHERE P.MEETINGID = M.MEETINGID AND P.USERID = @{paramName} AND P.STATUS = 1)",
                        paramName, Convert.ToInt32(fieldValue), attr.JoinType));
                    continue;
                }

                string? column = GetMeetingColumnForField(fieldNameLower);
                if (column == null) continue;

                var (condSql, condVal) = BuildMeetingCondition(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? GetMeetingColumnForField(string fieldName) => fieldName switch
    {
        "organizeruserid" or "organizeruser.id" => "M.ORGANIZERUSERID",
        "meetingtypeid"                         => "M.MEETINGTYPEID",
        "status"                                => "M.STATUS",
        "objecttypeid"                          => "M.OBJECTTYPEID",
        "objectid"                              => "M.OBJECTID",
        "starton"                               => "M.STARTON",
        "endon"                                 => "M.ENDON",
        "title"                                 => "M.TITLE",
        "location"                              => "M.LOCATION",
        _                                        => null
    };

    private static readonly HashSet<string> _dateMeetingColumns =
        new(StringComparer.OrdinalIgnoreCase) { "M.STARTON", "M.ENDON" };
    private static readonly HashSet<string> _textMeetingColumns =
        new(StringComparer.OrdinalIgnoreCase) { "M.TITLE", "M.LOCATION" };

    private static (string? Cond, object Val) BuildMeetingCondition(
        string column, string paramName, CriteriaDTO.OperationType opType, string fieldValue)
    {
        object asVal = _dateMeetingColumns.Contains(column) ? (object)DateTime.Parse(fieldValue)
                     : _textMeetingColumns.Contains(column) ? (object)fieldValue
                     : (object)Convert.ToInt32(fieldValue);
        bool isText = _textMeetingColumns.Contains(column);

        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}", isText ? (object)$"%{fieldValue}%" : asVal),
            CriteriaDTO.OperationType.StartWith => ($"{column} LIKE @{paramName}", isText ? (object)$"{fieldValue}%" : asVal),
            CriteriaDTO.OperationType.EndsWith  => ($"{column} LIKE @{paramName}", isText ? (object)$"%{fieldValue}" : 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> SaveMeetingAsync(MeetingDTO dto, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            MeetingQB.SAVE_MEETING,
            dto,
            cancellationToken: ct).ConfigureAwait(false);
        return dto.MeetingId;
    }

    public async Task UpdateMeetingAsync(MeetingDTO dto, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            MeetingQB.UPDATE_MEETING,
            dto,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task UpdateMeetingStatusAsync(int meetingId, byte newStatus, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            MeetingQB.UPDATE_MEETING_STATUS,
            new
            {
                MeetingId    = meetingId,
                NewStatus    = newStatus,
                ModifiedById = modifiedById,
                ModifiedOn   = DateTime.UtcNow,
                login.DatabaseName,
            },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task CancelMeetingAsync(int meetingId, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            MeetingQB.CANCEL_MEETING,
            new
            {
                MeetingId    = meetingId,
                ModifiedById = modifiedById,
                ModifiedOn   = DateTime.UtcNow,
                login.DatabaseName,
            },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<List<MeetingParticipantDTO>> GetParticipantsForMeetingAsync(int meetingId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<MeetingParticipantDTO>(
            login,
            MeetingQB.GET_PARTICIPANTS_FOR_MEETING,
            new { MeetingId = meetingId, login.DatabaseName },
            cancellationToken: ct).ConfigureAwait(false);
        return rows.ToList();
    }

    public async Task<MeetingParticipantDTO?> GetParticipantAsync(int meetingId, int userId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<MeetingParticipantDTO>(
            login,
            MeetingQB.GET_PARTICIPANT,
            new { MeetingId = meetingId, UserId = userId, login.DatabaseName },
            cancellationToken: ct).ConfigureAwait(false);
        return rows.FirstOrDefault();
    }

    public async Task AddParticipantAsync(MeetingParticipantDTO dto, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            MeetingQB.ADD_PARTICIPANT,
            dto,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task UpdateParticipantAttendanceAsync(int meetingId, int userId, byte attendanceStatus, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            MeetingQB.UPDATE_PARTICIPANT_ATTENDANCE,
            new
            {
                MeetingId        = meetingId,
                UserId           = userId,
                AttendanceStatus = attendanceStatus,
                ModifiedById     = modifiedById,
                ModifiedOn       = DateTime.UtcNow,
                login.DatabaseName,
            },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task RemoveParticipantAsync(int meetingId, int userId, LoginDTO login, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            login,
            MeetingQB.REMOVE_PARTICIPANT,
            new { MeetingId = meetingId, UserId = userId, login.DatabaseName },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<List<MeetingParticipantPicklistDTO>> GetSelectListParticipantAsync(int meetingId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<MeetingParticipantPicklistDTO>(
            login,
            MeetingQB.GET_SELECTLIST_PARTICIPANT,
            new { MeetingId = meetingId, login.DatabaseName },
            cancellationToken: ct).ConfigureAwait(false);
        return rows.ToList();
    }
}