using CMSDAL.DTO.ContentBlock;
using CMSDAL.Query.ContentBlock;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Newtonsoft.Json;
using System.Linq;

namespace CMSDAL.CustomCode.ContentBlock;
public class ContentBlockDAL : IContentBlockDAL
{
    private readonly IQueryExecutor _qe;
    public ContentBlockDAL(IQueryExecutor qe) => _qe = qe;

    public async Task<string> GetByContainerAsync(int containerId, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            var result = await _qe.QueryAsync<ContentBlockDTO>(loginDTO, ContentBlockQB.GET_BY_CONTAINER,
                new { ContainerId = containerId, TenantId = loginDTO.ClientId }, cancellationToken: ct);
            return JsonConvert.SerializeObject(result);
        }
        catch { throw; }
    }

    public async Task<string> GetFullDocumentAsync(int contentId, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            var rows = await _qe.QueryAsync<FullDocumentFlatRow>(loginDTO, ContentBlockQB.GET_FULL_DOCUMENT,
                new { ContentId = contentId, TenantId = loginDTO.ClientId }, cancellationToken: ct);

            var flat = rows.ToList();
            if (!flat.Any()) return JsonConvert.SerializeObject((object?)null);

            var first = flat.First();
            var doc = new ContentFullDocumentDTO
            {
                ContentId = first.ContentId,
                ContentTitle = first.ContentTitle,
                ContentSlug = first.ContentSlug,
                ContentStatus = first.ContentStatus
            };

            var containerMap = new Dictionary<int, ContentContainerFullDTO>();
            foreach (var row in flat)
            {
                if (!containerMap.TryGetValue(row.ContentContainerId, out var container))
                {
                    container = new ContentContainerFullDTO
                    {
                        ContentContainerId = row.ContentContainerId,
                        ContainerTypeCode = row.ContainerTypeCode,
                        ContainerLabel = row.ContainerLabel,
                        PositionNo = row.PositionNo,
                        ZoneDefinitionsJson = row.ZoneDefinitionsJson
                    };
                    containerMap[row.ContentContainerId] = container;
                    doc.Containers.Add(container);
                }

                if (row.ContentBlockId.HasValue && row.ContentBlockId.Value > 0)
                {
                    container.Blocks.Add(new ContentBlockDTO
                    {
                        ContentBlockId = row.ContentBlockId.Value,
                        ContentContainerId = row.ContentContainerId,
                        BlockCode = row.BlockCode,
                        BlockName = row.BlockName,
                        SchemaJson = row.SchemaJson,
                        ZoneName = row.ZoneName ?? "main",
                        PositionNo = row.BlockPositionNo ?? 0,
                        PropsJson = row.PropsJson ?? "{}"
                    });
                }
            }

            return JsonConvert.SerializeObject(doc);
        }
        catch { throw; }
    }

    public async Task<string> SaveAsync(ContentBlockDTO dto, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            await _qe.ExecuteAsync(loginDTO, ContentBlockQB.INSERT,
                new { dto.ContentContainerId, dto.BlockId, dto.ZoneName, dto.PositionNo, dto.PropsJson,
                      TenantId = loginDTO.ClientId, CreatedById = loginDTO.UserId, ModifiedById = loginDTO.UserId },
                cancellationToken: ct);
            return SuccessResponse.SaveSuccessMessage;
        }
        catch { throw; }
    }

    public async Task<string> UpdatePropsAsync(ContentBlockDTO dto, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            await _qe.ExecuteAsync(loginDTO, ContentBlockQB.UPDATE_PROPS,
                new { dto.ContentBlockId, dto.PropsJson, dto.PositionNo, dto.ZoneName,
                      TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                cancellationToken: ct);
            return SuccessResponse.UpdateSuccessMessage;
        }
        catch { throw; }
    }

    public async Task<string> ReorderAsync(int contentBlockId, short positionNo, string zoneName, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            await _qe.ExecuteAsync(loginDTO, ContentBlockQB.UPDATE_PROPS,
                new { ContentBlockId = contentBlockId, PropsJson = "{}", PositionNo = positionNo,
                      ZoneName = zoneName, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                cancellationToken: ct);
            return SuccessResponse.UpdateSuccessMessage;
        }
        catch { throw; }
    }

    public async Task<string> DeleteAsync(int contentBlockId, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            await _qe.ExecuteAsync(loginDTO, ContentBlockQB.SOFT_DELETE,
                new { ContentBlockId = contentBlockId, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                cancellationToken: ct);
            return SuccessResponse.DeleteSuccessMessage;
        }
        catch { throw; }
    }

    public async Task<int?> GetContainerTenantIdAsync(int contentContainerId, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            var result = await _qe.QueryAsync<int?>(loginDTO, ContentBlockQB.GET_CONTAINER_TENANT,
                new { ContentContainerId = contentContainerId }, cancellationToken: ct);
            return result.FirstOrDefault();
        }
        catch { throw; }
    }

    public async Task<int?> GetContentBlockTenantIdAsync(int contentBlockId, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            var result = await _qe.QueryAsync<int?>(loginDTO, ContentBlockQB.GET_CONTENTBLOCK_TENANT,
                new { ContentBlockId = contentBlockId }, cancellationToken: ct);
            return result.FirstOrDefault();
        }
        catch { throw; }
    }

    public async Task<int?> GetBlockIdByCodeAsync(string blockCode, LoginDTO loginDTO, CancellationToken ct)
    {
        try
        {
            var result = await _qe.QueryAsync<int?>(loginDTO, ContentBlockQB.GET_BLOCK_BY_CODE,
                new { BlockCode = blockCode, TenantId = loginDTO.ClientId }, cancellationToken: ct);
            return result.FirstOrDefault();
        }
        catch { throw; }
    }

    // Flat projection for full document query
    private class FullDocumentFlatRow
    {
        public int ContentId { get; set; }
        public string ContentTitle { get; set; } = string.Empty;
        public string ContentSlug { get; set; } = string.Empty;
        public byte ContentStatus { get; set; }
        public int ContentContainerId { get; set; }
        public short PositionNo { get; set; }
        public string? ContainerLabel { get; set; }
        public string ContainerTypeCode { get; set; } = string.Empty;
        public string ZoneDefinitionsJson { get; set; } = string.Empty;
        public int? ContentBlockId { get; set; }
        public string? ZoneName { get; set; }
        public short? BlockPositionNo { get; set; }
        public string? BlockCode { get; set; }
        public string? BlockName { get; set; }
        public string? SchemaJson { get; set; }
        public string? PropsJson { get; set; }
    }
}