using System;
using System.Collections.Generic;
using System.Linq;
using AnalyticsDAL.DTO.Wizard;
using GB5Shared.DTO.Framework.SchemaIntrospection;

namespace AnalyticsBLL.Wizard
{
    /// <summary>Pure logic, no DI, no DB calls — derives candidate DBJOIN suggestions from live
    /// foreign-key metadata for whichever tables the wizard user has already selected. Never
    /// called automatically by AnalyticsWizardBLL.QuickBuildAsync (which only persists joins the
    /// user has explicitly accepted via AnalyticsWizardBuildRequestDTO.AcceptedJoins) — this is a
    /// standalone helper the wizard FE (or a future BLL step) can invoke once it knows which
    /// tables are selected, since WizardDiscoverRequestDTO itself carries no table selection.</summary>
    public static class JoinSuggestionService
    {
        /// <summary>
        /// Builds one SuggestedJoinDTO per foreign-key constraint where both the referencing and
        /// referenced table are present in <paramref name="tableKeyToDbObjectId"/>. Multi-column
        /// FK constraints are grouped by ConstraintName and combined into a single
        /// " AND "-joined expression.
        /// </summary>
        /// <param name="tableKeyToDbObjectId">
        /// Maps "schema.table" (e.g. "dbo.TCUSTOMER") to the DBObjectId already registered/resolved
        /// for that table in this wizard session. MUST use
        /// <see cref="StringComparer.OrdinalIgnoreCase"/> as its key comparer — lookups here are
        /// case-insensitive to match SQL Server's default identifier collation.
        /// </param>
        /// <param name="foreignKeys">The raw FK list from AnalyticsWizardBLL.DiscoverAsync.</param>
        public static List<SuggestedJoinDTO> SuggestJoins(
            IReadOnlyDictionary<string, int> tableKeyToDbObjectId,
            IEnumerable<SchemaForeignKeyDTO> foreignKeys)
        {
            if (tableKeyToDbObjectId == null) throw new ArgumentNullException(nameof(tableKeyToDbObjectId));
            if (foreignKeys == null) throw new ArgumentNullException(nameof(foreignKeys));

            var suggestions = new List<SuggestedJoinDTO>();

            // Group multi-column FKs by ConstraintName so they become one combined expression.
            // FKs with no constraint name (defensive — shouldn't happen for real SQL Server FKs)
            // each get their own synthetic group so they aren't accidentally merged together.
            var groups = foreignKeys
                .Select((fk, index) => (fk, index))
                .GroupBy(
                    t => string.IsNullOrWhiteSpace(t.fk.ConstraintName) ? $"__unnamed_{t.index}" : t.fk.ConstraintName,
                    StringComparer.OrdinalIgnoreCase);

            foreach (var group in groups)
            {
                var columnPairs = group.Select(t => t.fk).ToList();
                var first = columnPairs[0];

                var fromKey = $"{first.FromSchema}.{first.FromTable}";
                var toKey = $"{first.ToSchema}.{first.ToTable}";

                if (!tableKeyToDbObjectId.TryGetValue(fromKey, out var fromDbObjectId))
                    continue;
                if (!tableKeyToDbObjectId.TryGetValue(toKey, out var toDbObjectId))
                    continue;

                var expression = string.Join(" AND ",
                    columnPairs.Select(c => $"{c.FromTable}.{c.FromColumn} = {c.ToTable}.{c.ToColumn}"));

                var requiresManualAliasing = string.Equals(first.FromTable, first.ToTable, StringComparison.OrdinalIgnoreCase);

                suggestions.Add(new SuggestedJoinDTO
                {
                    FromDBObjectId = fromDbObjectId,
                    ToDBObjectId = toDbObjectId,
                    FromTable = first.FromTable,
                    ToTable = first.ToTable,
                    Expression = expression,
                    SourceConstraintName = first.ConstraintName,
                    RequiresManualAliasing = requiresManualAliasing
                });
            }

            return suggestions;
        }
    }
}