using Dapper;
using FrameworkDAL.CustomCode.Server;
using FrameworkDAL.DTO.Notification;
using FrameworkDAL.DTO.Server;
using FrameworkDAL.Query.Notification;
using FrameworkDAL.Query.Server;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EntityHandler;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.ListQuery;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkDAL.CustomCode.Notification
{
    public class NotificationDAL : INotificationDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly int _dbType;

        public NotificationDAL(IQueryExecutor queryExecutor, IOptionsSnapshot<Gb5SystemDTO> dataBaseConfig)
        {
            _QueryExecutor = queryExecutor;
            _dbType = dataBaseConfig.Value.DataBaseType;
        }

        public async Task<string> GetNotificationUser(
            int first,
            int max,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO)
        {
            var sql = NotificationQB.GET_NOTIFICATION_USER;

            var parameters = new DynamicParameters();
            parameters.Add("FirstNumber", first);
            parameters.Add("MaxResult", max);

            sql = ApplyCriteria(sql, criteriaDTO, parameters);

            var result = await _QueryExecutor.QueryAsync<NotificationListDTO>(
                loginDTO,
                sql,
                parameters
            ).ConfigureAwait(false);

            return JsonConvert.SerializeObject(result);
        }

        public async Task<int> UpdateNotificationTo(int notificationToId, LoginDTO loginDTO)
            => await _QueryExecutor.ExecuteAsync(loginDTO, NotificationQB.UPDATE_NOTIFICATION_TO,
                new { NotificationToId = notificationToId }).ConfigureAwait(false);

        public async Task<int> UpdateAllNotification(int userId, LoginDTO loginDTO)
            => await _QueryExecutor.ExecuteAsync(loginDTO, NotificationQB.UPDATE_ALL_NOTIFICATION,
                new { UserId = userId }).ConfigureAwait(false);

        public async Task<int> DeleteAllNotification(int userId, LoginDTO loginDTO)
            => await _QueryExecutor.ExecuteAsync(loginDTO, NotificationQB.DELETE_ALL_NOTIFICATION,
                new { UserId = userId }).ConfigureAwait(false);

        public async Task<int> DeleteOneNotification(int notificationToId, LoginDTO loginDTO)
            => await _QueryExecutor.ExecuteAsync(loginDTO, NotificationQB.DELETE_ONE_NOTIFICATION,
                new { NotificationToId = notificationToId }).ConfigureAwait(false);

        public async Task<int> SaveNotification(
            string message,
            int fromUserId,
            int dataId,
            List<int> toUserIds,
            LoginDTO loginDTO,
            CancellationToken cancellationToken = default)
        {
            bool isPg = _dbType == DBTYPE.POSTGRESQL;
            string insertSql = isPg ? NotificationQB.INSERT_NOTIFICATION_POSTGRESQL : NotificationQB.INSERT_NOTIFICATION;

            int notificationId = await _QueryExecutor.ExecuteScalarAsync<int>(
                loginDTO, insertSql,
                new { FromUserId = fromUserId, Message = message, DataId = dataId },
                cancellationToken: cancellationToken).ConfigureAwait(false);

            foreach (var toUserId in toUserIds.Distinct())
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, NotificationQB.INSERT_NOTIFICATION_TO,
                    new { NotificationId = notificationId, ToUserId = toUserId },
                    cancellationToken: cancellationToken).ConfigureAwait(false);
            }

            return notificationId;
        }

        private object ConvertValue(object value)
        {
            if (value == null)
                return null;

            if (value is JsonElement json)
            {
                return json.ValueKind switch
                {
                    JsonValueKind.Number => json.TryGetInt64(out var l) ? l : json.GetDouble(),
                    JsonValueKind.String => json.GetString(),
                    JsonValueKind.True => true,
                    JsonValueKind.False => false,
                    _ => json.ToString()
                };
            }

            return value;
        }

        // =========================================================
        // ✅ CRITERIA BUILDER
        // =========================================================
        private string ApplyCriteria(
            string sql,
            CriteriaDTO criteriaDTO,
            DynamicParameters parameters)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return sql.Replace("/**where**/", "");

            var conditions = new List<string>();

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null)
                    continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    var value = ConvertValue(attr.FieldValue);

                    switch (attr.FieldName?.ToLower())
                    {
                        case "touserid":
                            conditions.Add("b.TOUSERID = @ToUserId");
                            parameters.Add("ToUserId", value);
                            break;

                        case "notificationtostatus":
                            conditions.Add("b.NOTIFICATIONSTATUS = @NotificationToStatus");
                            parameters.Add("NotificationToStatus", value);
                            break;

                        case "fromuserid":
                            conditions.Add("a.FROMUSERID = @FromUserId");
                            parameters.Add("FromUserId", value);
                            break;

                        case "notificationid":
                            conditions.Add("a.NOTIFICATIONID = @NotificationId");
                            parameters.Add("NotificationId", value);
                            break;

                        case "searchtext":
                            conditions.Add("a.MESSAGE LIKE @SearchText");
                            parameters.Add("SearchText", $"%{value}%");
                            break;
                    }
                }
            }

            return sql.Replace("/**where**/",
                conditions.Count > 0 ? " AND " + string.Join(" AND ", conditions) : "");
        }
    }
}