using CMSDAL.DTO.SitePage;
using CMSDAL.Query.SitePage;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System.Data.Common;
using System.Linq;

namespace CMSDAL.CustomCode.SitePage;
public class SitePageDAL : ISitePageDAL
{
    private readonly IQueryExecutor _qe;
    private readonly IValidation _validation;

    public SitePageDAL(IQueryExecutor qe, IValidation validation)
    {
        _qe = qe;
        _validation = validation;
    }

    public async Task<string> GetSitePage(int sitePageId, LoginDTO loginDTO, CancellationToken ct = default)
    {
        try
        {
            var result = await _qe.QueryAsync<SitePageDTO>(loginDTO, SitePageQB.GET_SITEPAGE,
                new { SitePageId = sitePageId, TenantId = loginDTO.ClientId }, cancellationToken: ct);
            return JsonConvert.SerializeObject(result.FirstOrDefault());
        }
        catch { throw; }
    }

    public async Task<SitePageRouteDTO?> GetRouteByUrlPathAsync(int siteId, string urlPath, LoginDTO loginDTO, CancellationToken ct = default)
    {
        try
        {
            var result = await _qe.QueryAsync<SitePageRouteDTO>(loginDTO, SitePageQB.GET_ROUTE_BY_URLPATH,
                new { SiteId = siteId, UrlPath = urlPath }, cancellationToken: ct);
            return result.FirstOrDefault();
        }
        catch { throw; }
    }

    public async Task<(int ContentTypeId, int SpaceId)> GetSitePageContentTypeAsync(LoginDTO loginDTO, CancellationToken ct = default)
    {
        try
        {
            var result = await _qe.QueryAsync<SitePageContentTypeRow>(loginDTO, SitePageQB.GET_SITEPAGE_CONTENTTYPE,
                cancellationToken: ct);
            var row = result.FirstOrDefault()
                ?? throw new InvalidOperationException(
                    "The 'site-page' system ContentType is missing — apply 20260905_CMS_Site_Page_Foundation_SqlServer.sql first.");
            return (row.ContentTypeId, row.SpaceId);
        }
        catch { throw; }
    }

    public async Task<int> SaveSitePage(SitePageDTO dto, LoginDTO loginDTO, DbTransaction? tx = null, CancellationToken ct = default)
    {
        try
        {
            int newSitePageId = await _qe.ExecuteIdentityAsync(loginDTO, SitePageQB.SAVE_SITEPAGE, dto, tx);
            dto.SitePageId = newSitePageId;
            return newSitePageId;
        }
        catch (Exception ex)
        {
            string error = await _validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
            throw new Exception(error);
        }
    }

    public async Task<int> UpdateSitePage(SitePageDTO dto, LoginDTO loginDTO, DbTransaction? tx = null, CancellationToken ct = default)
    {
        try
        {
            await _qe.ExecuteAsync(loginDTO, SitePageQB.UPDATE_SITEPAGE, dto, tx, ct);
            return dto.SitePageId;
        }
        catch (Exception ex)
        {
            string error = await _validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
            throw new Exception(error);
        }
    }

    public async Task<string> DeleteSitePage(int sitePageId, LoginDTO loginDTO, CancellationToken ct = default)
    {
        try
        {
            int rows = await _qe.ExecuteAsync(loginDTO, SitePageQB.DELETE_SITEPAGE,
                new { SitePageId = sitePageId, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                cancellationToken: ct);
            return rows > 0 ? SuccessResponse.DeleteSuccessMessage : ErrorResponse.DeleteNotFoundMessage;
        }
        catch (Exception ex)
        {
            string error = await _validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
            throw new Exception(error);
        }
    }

    private class SitePageContentTypeRow
    {
        public int ContentTypeId { get; set; }
        public int SpaceId { get; set; }
    }
}