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

namespace AnalyticsDAL.CustomCode.Warehouse
{
    // Dimension catalog for warehouse facts.
    //
    // The COLUMN allowlists (DimOuColumns, DimDateColumns) remain as static readonly fields —
    // WarehouseDAL.ResolveDimensionColumn() uses them for SQL-injection-safe column validation,
    // and they represent schema facts about the dimension tables that don't change at runtime.
    //
    // The FACT→DIMENSION TABLE registry is now DB-backed (MWAREHOUSEFACTDIMENSION, seeded by
    // migration 20260827_Warehouse_Redesign_SqlServer.sql). GetAvailableDimensionsAsync queries
    // that table to know which dimension tables are wired to a given FactId, then returns only
    // the columns for those tables — enabling new dimensions to be registered without code changes.
    //
    // The legacy static GetAvailableDimensions() is kept for callers that don't yet have a FactId
    // context; it returns the union of all known dimension columns (safe fallback behaviour).
    public class WarehouseDimensionCatalog : IWarehouseDimensionCatalog
    {
        public static readonly HashSet<string> DimOuColumns = new(StringComparer.OrdinalIgnoreCase)
        {
            "OUCODE", "OUNAME", "COMPANYID", "COMPANYNAME", "BRANCHID", "BRANCHNAME",
            "CITYID", "CITYNAME", "REGIONID", "REGIONNAME", "COUNTRYID", "COUNTRYNAME"
        };

        public static readonly HashSet<string> DimDateColumns = new(StringComparer.OrdinalIgnoreCase)
        {
            "Date", "FullDateUK", "FullDateUSA", "DayOfMonth", "DayName",
            "DayOfWeekUSA", "DayOfWeekUK", "WeekOfMonth", "WeekOfQuarter", "WeekOfYear",
            "Month", "MonthName", "MonthOfQuarter", "Quarter", "QuarterName",
            "Year", "YearName", "MonthYear", "MMYYYY",
            "FirstDayOfMonth", "LastDayOfMonth", "FirstDayOfQuarter", "LastDayOfQuarter",
            "FirstDayOfYear", "LastDayOfYear", "IsWeekday",
            "FINANCIALQUARTER", "FINANCIALQUARTERNAME", "FINANCIALYEAR", "FINANCIALYEARNAME"
        };

        private const byte Int = 0, StringType = 1, Date = 3, Bool = 4;

        private static readonly Dictionary<string, (string Label, byte DataType)> OuLabels =
            new(StringComparer.OrdinalIgnoreCase)
            {
                ["OUCODE"] = ("OU Code", StringType),
                ["OUNAME"] = ("OU Name", StringType),
                ["COMPANYID"] = ("Company", Int),
                ["COMPANYNAME"] = ("Company Name", StringType),
                ["BRANCHID"] = ("Branch", Int),
                ["BRANCHNAME"] = ("Branch Name", StringType),
                ["CITYID"] = ("City", Int),
                ["CITYNAME"] = ("City Name", StringType),
                ["REGIONID"] = ("Region", Int),
                ["REGIONNAME"] = ("Region Name", StringType),
                ["COUNTRYID"] = ("Country", Int),
                ["COUNTRYNAME"] = ("Country Name", StringType),
            };

        private static readonly Dictionary<string, (string Label, byte DataType)> DateLabels =
            new(StringComparer.OrdinalIgnoreCase)
            {
                ["Date"] = ("Date", Date),
                ["FullDateUK"] = ("Full Date (UK)", StringType),
                ["FullDateUSA"] = ("Full Date (US)", StringType),
                ["DayOfMonth"] = ("Day of Month", Int),
                ["DayName"] = ("Day Name", StringType),
                ["DayOfWeekUSA"] = ("Day of Week (US)", Int),
                ["DayOfWeekUK"] = ("Day of Week (UK)", Int),
                ["WeekOfMonth"] = ("Week of Month", Int),
                ["WeekOfQuarter"] = ("Week of Quarter", Int),
                ["WeekOfYear"] = ("Week of Year", Int),
                ["Month"] = ("Month", StringType),
                ["MonthName"] = ("Month Name", StringType),
                ["MonthOfQuarter"] = ("Month of Quarter", Int),
                ["Quarter"] = ("Quarter", StringType),
                ["QuarterName"] = ("Quarter Name", StringType),
                ["Year"] = ("Year", StringType),
                ["YearName"] = ("Year Name", StringType),
                ["MonthYear"] = ("Month-Year", StringType),
                ["MMYYYY"] = ("MM/YYYY", StringType),
                ["FirstDayOfMonth"] = ("First Day of Month", Date),
                ["LastDayOfMonth"] = ("Last Day of Month", Date),
                ["FirstDayOfQuarter"] = ("First Day of Quarter", Date),
                ["LastDayOfQuarter"] = ("Last Day of Quarter", Date),
                ["FirstDayOfYear"] = ("First Day of Year", Date),
                ["LastDayOfYear"] = ("Last Day of Year", Date),
                ["IsWeekday"] = ("Is Weekday", Bool),
                ["FINANCIALQUARTER"] = ("Financial Quarter", StringType),
                ["FINANCIALQUARTERNAME"] = ("Financial Quarter Name", StringType),
                ["FINANCIALYEAR"] = ("Financial Year", StringType),
                ["FINANCIALYEARNAME"] = ("Financial Year Name", StringType),
            };

        private readonly IQueryExecutor _queryExecutor;

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

        // DB-backed: returns only the dimension columns registered for the given FactId
        // in MWAREHOUSEFACTDIMENSION.  Falls back gracefully to all known columns if the
        // table has no rows for this factId (e.g., pre-seed or test environment).
        public async Task<List<WarehouseDimensionDTO>> GetAvailableDimensionsAsync(
            int factId, LoginDTO login, CancellationToken ct)
        {
            const string sql = @"
SELECT DIMENSIONTABLE AS DimensionTable
FROM   MWAREHOUSEFACTDIMENSION
WHERE  FACTID = @FactId
ORDER  BY SORTORDER";

            var dimTableNames = (await _queryExecutor
                .QueryAsync<string>(login, sql, new { FactId = factId }, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

            if (dimTableNames.Count == 0)
                return GetAvailableDimensions();

            var result = new List<WarehouseDimensionDTO>
            {
                new() { Field = "OUID",   Label = "Organization Unit", DataType = Int },
                new() { Field = "DATEID", Label = "Date",              DataType = Int },
            };

            foreach (var dimensionTable in dimTableNames)
            {
                if (dimensionTable.Equals("DIMOU", StringComparison.OrdinalIgnoreCase))
                {
                    foreach (var column in DimOuColumns)
                    {
                        var (label, dataType) = OuLabels.TryGetValue(column, out var v) ? v : (column, StringType);
                        result.Add(new WarehouseDimensionDTO { Field = $"DIMOU.{column}", Label = label, DataType = dataType });
                    }
                }
                else if (dimensionTable.Equals("DimDate", StringComparison.OrdinalIgnoreCase))
                {
                    foreach (var column in DimDateColumns)
                    {
                        var (label, dataType) = DateLabels.TryGetValue(column, out var v) ? v : (column, StringType);
                        result.Add(new WarehouseDimensionDTO { Field = $"DIMDATE.{column}", Label = label, DataType = dataType });
                    }
                }
                // Future dimension tables (e.g., DimProduct) are registered here by name.
                // Each new table needs its own column allowlist added above — this is the only
                // code change required when a new dimension table is introduced.
            }

            return result;
        }

        // Static fallback: returns the union of all known dimension columns.
        // Used by BICatalogBLL when no FactId context is available.
        public static List<WarehouseDimensionDTO> GetAvailableDimensions()
        {
            var result = new List<WarehouseDimensionDTO>
            {
                new() { Field = "OUID",   Label = "Organization Unit", DataType = Int },
                new() { Field = "DATEID", Label = "Date",              DataType = Int },
            };

            foreach (var column in DimOuColumns)
            {
                var (label, dataType) = OuLabels.TryGetValue(column, out var v) ? v : (column, StringType);
                result.Add(new WarehouseDimensionDTO { Field = $"DIMOU.{column}", Label = label, DataType = dataType });
            }

            foreach (var column in DimDateColumns)
            {
                var (label, dataType) = DateLabels.TryGetValue(column, out var v) ? v : (column, StringType);
                result.Add(new WarehouseDimensionDTO { Field = $"DIMDATE.{column}", Label = label, DataType = dataType });
            }

            return result;
        }
    }
}