using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace GB5Shared.Addon
{
    public class AddonService : IAddonService
    {
        private readonly IQueryExecutor _queryExecutor;

        // Whitelist of allowed addon tables — prevents SQL injection via table/column name parameters.
        // Add new addon tables here as modules are onboarded.
        private static readonly HashSet<string> _allowedTables = new(StringComparer.OrdinalIgnoreCase)
        {
            "TPAYREVISIONADDON",
            "TPAYPROCESSADDON",
            "MEMPLOYEEADDON",
            "TAPPLICANTADDON",
            "TMONTHLYATTENDANCEADDON",
            "TPERIODICADDON",
        };

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

        public async Task<string?> GetAddonJsonAsync(
            string addonTable,
            string fkColumn,
            int fkValue,
            LoginDTO loginDTO,
            CancellationToken ct = default)
        {
            ValidateTableAndColumn(addonTable, fkColumn);

            // Scalar subquery always returns one row; FeAddon is NULL when no addon row exists.
            var sql = $@"SELECT (
                             SELECT TOP 1 *
                             FROM   [{addonTable}]
                             WHERE  [{fkColumn}] = @FkValue
                             FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                         ) AS FeAddon";

            return await _queryExecutor.QuerySingleAsync<string?>(
                loginDTO, sql, new { FkValue = fkValue }, cancellationToken: ct);
        }

        public async Task<IEnumerable<AddonResult>> GetAddonJsonBatchAsync(
            string addonTable,
            string fkColumn,
            IEnumerable<int> fkValues,
            LoginDTO loginDTO,
            CancellationToken ct = default)
        {
            ValidateTableAndColumn(addonTable, fkColumn);

            var ids = fkValues?.ToList() ?? new List<int>();
            if (ids.Count == 0)
                return Enumerable.Empty<AddonResult>();

            // One row per FK value; correlated subquery produces JSON for that FK.
            // GROUP BY ensures deduplication when multiple rows exist per FK.
            var sql = $@"SELECT
                             a.[{fkColumn}] AS Id,
                             (SELECT TOP 1 *
                              FROM   [{addonTable}] t2
                              WHERE  t2.[{fkColumn}] = a.[{fkColumn}]
                              FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS FeAddon
                         FROM  [{addonTable}] a
                         WHERE a.[{fkColumn}] IN @FkValues
                         GROUP BY a.[{fkColumn}]";

            // Dapper expands "IN @FkValues" into one SQL parameter per id — SQL Server caps a
            // request at 2100 parameters total, so a large id list must be batched.
            const int MaxParametersPerBatch = 2000;
            var results = new List<AddonResult>();
            foreach (var chunk in ids.Chunk(MaxParametersPerBatch))
            {
                var chunkResult = await _queryExecutor.QueryAsync<AddonResult>(
                    loginDTO, sql, new { FkValues = chunk }, cancellationToken: ct);
                if (chunkResult != null)
                    results.AddRange(chunkResult);
            }

            return results;
        }

        private static void ValidateTableAndColumn(string table, string column)
        {
            if (!_allowedTables.Contains(table))
                throw new ArgumentException(
                    $"Table '{table}' is not a registered addon table.", nameof(table));

            if (string.IsNullOrEmpty(column) || !column.All(c => char.IsLetterOrDigit(c) || c == '_'))
                throw new ArgumentException(
                    $"Column name '{column}' contains invalid characters.", nameof(column));
        }
    }
}