using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.Employee
{
    /// <summary>
    /// Resolves which addon columns are valid for a given entity (e.g. "EMPLOYEEADDON")
    /// on the CURRENT tenant database, and builds a SQL SELECT fragment that can be
    /// spliced into a larger query (e.g. GET_EMPLOYEE) at a token position.
    ///
    /// SQL Server only (no dialect branching) per your requirement.
    ///
    /// Resolution chain:
    ///   MENTITY (ENTITYCODE -> ENTITYID)
    ///     -> MADDONFIELDS (ENTITYID -> configured FIELDNAME list)
    ///     -> INFORMATION_SCHEMA.COLUMNS (validate FIELDNAME actually exists
    ///        physically on this tenant DB's table - the safeguard that prevents
    ///        the "basictest only has EMPLOYEEID" bug)
    /// </summary>
    public class AddonColumnResolver : IAddonColumnResolver
    {
        private readonly IQueryExecutor _queryExecutor;

        // cache key = "{ClientId}|{EntityCode}|{TableName}"
        private static readonly ConcurrentDictionary<string, List<string>> _fieldCache = new();

        // Defense in depth: never splice anything into SQL text that isn't a plain
        // identifier, even though we already intersect against real physical columns.
        private static readonly Regex SafeIdentifier = new("^[A-Za-z0-9_]+$", RegexOptions.Compiled);

        public AddonColumnResolver(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        /// <summary>
        /// Returns the validated, safe field names for the given entity/table on the
        /// tenant database resolved from loginDTO. Cached, since MENTITY/MADDONFIELDS
        /// metadata rarely changes.
        /// </summary>
        public async Task<List<string>> GetSafeFieldNamesAsync(
            LoginDTO loginDTO, string entityCode, string tableName)
        {
            var cacheKey = $"{loginDTO.ClientId}|{entityCode}|{tableName}";

            if (_fieldCache.TryGetValue(cacheKey, out var cached))
                return cached;

            // 1) ENTITYID for this entity code.
            //    TOP 1 guards against QuerySingleAsync throwing if ENTITYCODE
            //    somehow isn't unique - we just want one deterministic match.
            var entityId = await _queryExecutor.QuerySingleAsync<int?>(
                loginDTO,
                "SELECT TOP 1 ENTITYID FROM MENTITY WHERE ENTITYCODE = @EntityCode",
                new { EntityCode = entityCode });

            if (entityId == null)
            {
                _fieldCache[cacheKey] = new List<string>();
                return _fieldCache[cacheKey];
            }

            // 2) Configured fields from MADDONFIELDS (only active ones, in admin-defined order)
            var configuredFields = (await _queryExecutor.QueryAsync<string>(
                loginDTO,
                @"SELECT FIELDNAME FROM MADDONFIELDS
                  WHERE ENTITYID = @EntityId AND STATUS = 1
                  ORDER BY SORTORDER",
                new { EntityId = entityId })).ToList();

            if (configuredFields.Count == 0)
            {
                _fieldCache[cacheKey] = new List<string>();
                return _fieldCache[cacheKey];
            }

            // 3) Physical columns that actually exist on THIS tenant DB's table.
            var physicalColumns = (await _queryExecutor.QueryAsync<string>(
                loginDTO,
                "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName",
                new { TableName = tableName }))
                .ToHashSet(StringComparer.OrdinalIgnoreCase);

            // 4) Intersect configured fields with physical reality + identifier safety check
            var safeFields = configuredFields
                .Where(f => physicalColumns.Contains(f))
                .Where(f => SafeIdentifier.IsMatch(f))
                .Distinct(StringComparer.OrdinalIgnoreCase)
                .ToList();

            _fieldCache[cacheKey] = safeFields;
            return safeFields;
        }

        /// <summary>
        /// Builds the SQL fragment for the addon table, aliased with an "Addon_" prefix
        /// so each field can be embedded into GET_EMPLOYEE without colliding with the
        /// existing splitOn markers (AddonEmployeeId, AddressId, PermAddressId, ContactId).
        /// Each item is comma-terminated so it can be inserted directly after
        /// "empaddon.EMPLOYEEID AS AddonEmployeeId," with no special-casing for the
        /// empty case.
        /// </summary>
        public async Task<(string sqlFragment, List<string> fieldNames)> BuildAddonSelectFragmentAsync(
            LoginDTO loginDTO,
            string entityCode,
            string tableName,
            string tableAlias)
        {
            var fields = await GetSafeFieldNamesAsync(loginDTO, entityCode, tableName);

            if (fields.Count == 0)
                return (string.Empty, fields);

            var fragment = string.Join(
                "\n    ",
                fields.Select(f => $"{tableAlias}.[{f}] AS Addon_{f},"));

            return (fragment, fields);
        }

        /// <summary>
        /// Call if MENTITY/MADDONFIELDS metadata changes at runtime (rare) and you
        /// need to force a re-read instead of serving cached values.
        /// </summary>
        public void ClearCache() => _fieldCache.Clear();
    }

    public interface IAddonColumnResolver
    {
        Task<List<string>> GetSafeFieldNamesAsync(LoginDTO loginDTO, string entityCode, string tableName);

        Task<(string sqlFragment, List<string> fieldNames)> BuildAddonSelectFragmentAsync(
            LoginDTO loginDTO, string entityCode, string tableName, string tableAlias);

        void ClearCache();
    }
}