using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using FrameworkDAL.Query.GOP;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.GOP;
using GB5Shared.QueryExecutor;

namespace FrameworkDAL.CustomCode.GOP
{
    public class MappingDAL : IMappingDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public MappingDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // Mirrors QualifierDAL's own private helper exactly — CriteriaDTO's FieldValue can
        // arrive as a raw JsonElement (from System.Text.Json deserialization) rather than a
        // plain string, and defaults to PICKLIST (List<int>) when the caller omits
        // CriteriaAttributeType, silently turning search text into "System.Collections.Generic.List`1[...]".
        private static string? GetRawFieldValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            var attr = criteriaDTO?.SectionCriteriaList?
                .Where(s => s?.AttributesCriteriaList != null)
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

            if (attr?.FieldValue == null) return null;

            string raw = attr.FieldValue is System.Text.Json.JsonElement je
                ? je.ToString()
                : attr.FieldValue.ToString();
            return string.IsNullOrWhiteSpace(raw) ? null : raw;
        }

        public async Task<IReadOnlyList<MappingDefinitionDTO>> GetSelectListMapping(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            string? mapperCode = GetRawFieldValue(criteriaDTO, "MapperCode");
            string? mapperName = GetRawFieldValue(criteriaDTO, "MapperName");

            var result = await _QueryExecutor.QueryAsync<MappingDefinitionDTO>(
                loginDTO, MappingQB.GET_SELECTLIST_MAPPING,
                new
                {
                    TenantId   = loginDTO.ClientId,
                    MapperCode = string.IsNullOrWhiteSpace(mapperCode) ? null : $"%{mapperCode}%",
                    MapperName = string.IsNullOrWhiteSpace(mapperName) ? null : $"%{mapperName}%"
                },
                cancellationToken: ct);
            return (result ?? []).ToList();
        }

        // ── Definition ────────────────────────────────────────────────────────────

        public async Task SaveMappingDefinition(
            MappingDefinitionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            dto.CreatedById  = loginDTO.UserId;
            dto.CreatedOn    = DateTime.UtcNow;
            dto.ModifiedById = loginDTO.UserId;
            dto.ModifiedOn   = DateTime.UtcNow;
            dto.TenantId     = loginDTO.ClientId;

            await _QueryExecutor.ExecuteAsync(loginDTO, MappingQB.INSERT_DEFINITION, dto, tx, ct);
        }

        public async Task UpdateMappingDefinition(
            MappingDefinitionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            dto.ModifiedById = loginDTO.UserId;
            dto.ModifiedOn   = DateTime.UtcNow;

            await _QueryExecutor.ExecuteAsync(loginDTO, MappingQB.UPDATE_DEFINITION, dto, tx, ct);
        }

        public async Task<MappingDefinitionDTO?> GetMappingDefinitionById(
            int mappingDefinitionId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            return await _QueryExecutor.QuerySingleAsync<MappingDefinitionDTO>(
                loginDTO,
                MappingQB.GET_DEFINITION_BY_ID,
                new { TenantId = loginDTO.ClientId, MappingDefinitionId = mappingDefinitionId },
                cancellationToken: ct);
        }

        public async Task<IReadOnlyList<MappingDefinitionDTO>> GetMappingDefinitionList(
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            var list = await _QueryExecutor.QueryAsync<MappingDefinitionDTO>(
                loginDTO,
                MappingQB.GET_DEFINITION_LIST,
                new { TenantId = loginDTO.ClientId },
                cancellationToken: ct);

            return list.ToList();
        }

        public async Task DeleteMappingDefinition(
            int mappingDefinitionId, int modifiedById, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                MappingQB.DELETE_DEFINITION,
                new { TenantId = loginDTO.ClientId, MappingDefinitionId = mappingDefinitionId, ModifiedById = modifiedById },
                tx,
                ct);
        }

        // ── Version ───────────────────────────────────────────────────────────────

        public async Task SaveMappingVersion(
            MappingVersionDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            dto.CreatedById  = loginDTO.UserId;
            dto.CreatedOn    = DateTime.UtcNow;
            dto.ModifiedById = loginDTO.UserId;
            dto.ModifiedOn   = DateTime.UtcNow;
            dto.TenantId     = loginDTO.ClientId;

            await _QueryExecutor.ExecuteAsync(loginDTO, MappingQB.INSERT_VERSION, dto, tx, ct);
        }

        public async Task ArchiveActiveVersion(
            int mappingDefinitionId, int modifiedById, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                MappingQB.ARCHIVE_ACTIVE_VERSION,
                new { TenantId = loginDTO.ClientId, MappingDefinitionId = mappingDefinitionId, ModifiedById = modifiedById },
                tx,
                ct);
        }

        public async Task ActivateVersion(
            int mappingVersionId, int modifiedById, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                MappingQB.ACTIVATE_VERSION,
                new { TenantId = loginDTO.ClientId, MappingVersionId = mappingVersionId, ModifiedById = modifiedById },
                tx,
                ct);
        }

        public async Task<MappingVersionDTO?> GetActiveVersionByDefinition(
            int mappingDefinitionId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            return await _QueryExecutor.QuerySingleAsync<MappingVersionDTO>(
                loginDTO,
                MappingQB.GET_ACTIVE_VERSION_BY_DEFINITION,
                new { TenantId = loginDTO.ClientId, MappingDefinitionId = mappingDefinitionId },
                cancellationToken: ct);
        }

        public async Task<IReadOnlyList<MappingVersionDTO>> GetVersionsByDefinition(
            int mappingDefinitionId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var list = await _QueryExecutor.QueryAsync<MappingVersionDTO>(
                loginDTO,
                MappingQB.GET_VERSIONS_BY_DEFINITION,
                new { TenantId = loginDTO.ClientId, MappingDefinitionId = mappingDefinitionId },
                cancellationToken: ct);

            return list.ToList();
        }

        public async Task<byte?> GetMappingVersionStatus(
            int mappingVersionId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            return await _QueryExecutor.QuerySingleAsync<byte?>(
                loginDTO,
                MappingQB.GET_VERSION_STATUS,
                new { TenantId = loginDTO.ClientId, MappingVersionId = mappingVersionId },
                cancellationToken: ct);
        }

        public async Task DeleteMappingVersion(
            int mappingVersionId, int modifiedById, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                MappingQB.DELETE_VERSION,
                new { TenantId = loginDTO.ClientId, MappingVersionId = mappingVersionId, ModifiedById = modifiedById },
                tx,
                ct);
        }

        // ── Node ──────────────────────────────────────────────────────────────────

        public async Task SaveMappingNode(
            MappingNodeDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            dto.TenantId = loginDTO.ClientId;

            await _QueryExecutor.ExecuteAsync(loginDTO, MappingQB.INSERT_NODE, dto, tx, ct);
        }

        public async Task DeleteNodesByVersion(
            int mappingVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                MappingQB.DELETE_NODES_BY_VERSION,
                new { TenantId = loginDTO.ClientId, MappingVersionId = mappingVersionId },
                tx,
                ct);
        }

        public async Task<IReadOnlyList<MappingNodeDTO>> GetNodesByVersion(
            int mappingVersionId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var list = await _QueryExecutor.QueryAsync<MappingNodeDTO>(
                loginDTO,
                MappingQB.GET_NODES_BY_VERSION,
                new { TenantId = loginDTO.ClientId, MappingVersionId = mappingVersionId },
                cancellationToken: ct);

            return list.ToList();
        }

        public async Task UpdateMappingNode(
            MappingNodeDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            dto.TenantId = loginDTO.ClientId;

            await _QueryExecutor.ExecuteAsync(loginDTO, MappingQB.UPDATE_NODE, dto, tx, ct);
        }

        public async Task DeleteMappingNode(
            int mappingNodeId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                MappingQB.DELETE_NODE,
                new { TenantId = loginDTO.ClientId, MappingNodeId = mappingNodeId },
                tx,
                ct);
        }

        public async Task<IReadOnlyList<MappingNodeDTO>> GetActiveNodesByMapperCode(
            string mapperCode, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var list = await _QueryExecutor.QueryAsync<MappingNodeDTO>(
                loginDTO,
                MappingQB.GET_ACTIVE_NODES_BY_MAPPER_CODE,
                new { TenantId = loginDTO.ClientId, MapperCode = mapperCode },
                cancellationToken: ct);

            return list?.ToList() ?? [];
        }
    }
}