using AnalyticsBLL.DatasetResolver;
using AnalyticsBLL.Workspace;
using AnalyticsDAL.CustomCode.AnalysisQuery;
using AnalyticsDAL.CustomCode.BICatalog;
using AnalyticsDAL.CustomCode.BIFieldMapping;
using AnalyticsDAL.CustomCode.DataSource;
using AnalyticsDAL.CustomCode.Warehouse;
using AnalyticsDAL.DTO.AnalysisQuery;
using AnalyticsDAL.DTO.BICatalog;
using AnalyticsDAL.DTO.BIFieldMapping;
using AnalyticsDAL.DTO.Warehouse;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using static GB5Shared.GB5Constant.Constant;

namespace AnalyticsBLL.BICatalog
{
    public class BICatalogBLL : IBICatalogBLL
    {
        // The shared, generic "this GB5 host, reachable for any of its own reports" data
        // source — see 20260806_BIGeneralize_SelfHostDataSource_SqlServer.sql. Resolved by
        // code, not a hardcoded id, since the seeded id can differ per environment.
        private const string SelfHostDataSourceCode = "GB5_SELF_HOST";

        private readonly IBICatalogDAL _catalogDAL;
        private readonly IWorkspaceBLL _workspaceBLL;
        private readonly IDatasetResolver _datasetResolver;
        private readonly IBIFieldMappingDAL _fieldMappingDAL;
        private readonly IAnalysisQueryDAL _analysisQueryDAL;
        private readonly IDataSourceDAL _dataSourceDAL;
        private readonly AutoNumber _autoNumber;
        private readonly IValidation _validation;
        private readonly KeyInvalidate _keyInvalidate;
        private readonly ILogger<BICatalogBLL> _logger;

        public BICatalogBLL(
            IBICatalogDAL catalogDAL,
            IWorkspaceBLL workspaceBLL,
            IDatasetResolver datasetResolver,
            IBIFieldMappingDAL fieldMappingDAL,
            IAnalysisQueryDAL analysisQueryDAL,
            IDataSourceDAL dataSourceDAL,
            AutoNumber autoNumber,
            IValidation validation,
            KeyInvalidate keyInvalidate,
            ILogger<BICatalogBLL> logger)
        {
            _catalogDAL = catalogDAL;
            _workspaceBLL = workspaceBLL;
            _datasetResolver = datasetResolver;
            _fieldMappingDAL = fieldMappingDAL;
            _analysisQueryDAL = analysisQueryDAL;
            _dataSourceDAL = dataSourceDAL;
            _autoNumber = autoNumber;
            _validation = validation;
            _keyInvalidate = keyInvalidate;
            _logger = logger;
        }

        public async Task<List<BICatalogDTO>> GetCatalogList(LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-catalog-list", new { login.ClientId });

            var rows = await _catalogDAL.GetCatalogList(login, ct).ConfigureAwait(false);
            var visible = new List<BICatalogDTO>(rows.Count);

            foreach (var row in rows)
            {
                if (row.WorkspaceId != -1)
                {
                    try
                    {
                        await _workspaceBLL.ValidateWorkspaceVisibility(row.WorkspaceId, login, ct).ConfigureAwait(false);
                    }
                    catch (ArgumentException)
                    {
                        // Workspace exists but isn't visible to this tenant — exclude, don't fail the whole list.
                        continue;
                    }
                }

                visible.Add(row);
            }

            return visible;
        }

        public async Task<List<WarehouseDimensionDTO>> GetDimensionsForDataset(int biCatalogId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-dimensions-for-dataset", new { biCatalogId });

            var resolution = await _datasetResolver.ResolveAsync(biCatalogId, login, ct).ConfigureAwait(false);

            if (resolution.Kind == DatasetKind.Warehouse)
                return WarehouseDimensionCatalog.GetAvailableDimensions();

            if (resolution.Kind == DatasetKind.ApiService)
            {
                var mappings = await _fieldMappingDAL.GetDimensionsByCatalogId(biCatalogId, login, ct).ConfigureAwait(false);
                return mappings.Select(ToWarehouseDimensionDTO).ToList();
            }

            if (resolution.Kind == DatasetKind.AnalysisQuery)
            {
                // resolution.AnalysisQueryId is guaranteed set here — DatasetResolver already
                // rejected DIRECT-type queries (InvalidOperationException) before this branch
                // could ever be reached, so no QUERYTYPE re-check is needed on this side.
                var fields = await _analysisQueryDAL.GetBIDimensionsForQuery(resolution.AnalysisQueryId!.Value, login, ct).ConfigureAwait(false);
                return fields.Select(ToWarehouseDimensionDTO).ToList();
            }

            _logger.LogInformation(
                "GetDimensionsForDataset: dataset {BICatalogId} is {Kind}-kind — returning empty (picker not wired for this kind yet)",
                biCatalogId, resolution.Kind);
            return new List<WarehouseDimensionDTO>();
        }

        public async Task<List<WarehouseMeasureDTO>> GetMeasuresForDataset(int biCatalogId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-measures-for-dataset", new { biCatalogId });

            var resolution = await _datasetResolver.ResolveAsync(biCatalogId, login, ct).ConfigureAwait(false);

            if (resolution.Kind == DatasetKind.Warehouse)
            {
                return (resolution.Measures ?? new List<WarehouseMeasureDTO>())
                    .Where(m => m.IsVisible)
                    .ToList();
            }

            if (resolution.Kind == DatasetKind.ApiService)
            {
                var mappings = await _fieldMappingDAL.GetMeasuresByCatalogId(biCatalogId, login, ct).ConfigureAwait(false);
                return mappings.Where(m => m.IsVisible).Select(ToWarehouseMeasureDTO).ToList();
            }

            if (resolution.Kind == DatasetKind.AnalysisQuery)
            {
                var fields = await _analysisQueryDAL.GetBIMeasuresForQuery(resolution.AnalysisQueryId!.Value, login, ct).ConfigureAwait(false);
                return fields.Select(ToWarehouseMeasureDTO).ToList();
            }

            _logger.LogInformation(
                "GetMeasuresForDataset: dataset {BICatalogId} is {Kind}-kind — returning empty (picker not wired for this kind yet)",
                biCatalogId, resolution.Kind);
            return new List<WarehouseMeasureDTO>();
        }

        // Computed fields (MBIFIELDMAPPING.MAPPEDAS=2) are resolved identically for all 3
        // DatasetKinds — DatasetResolver.ResolveAsync already populates resolution.ComputedFields
        // unconditionally, before its own kind switch — so unlike GetDimensionsForDataset/
        // GetMeasuresForDataset above, no per-kind branching is needed here at all.
        public async Task<List<BIFieldMappingDTO>> GetComputedFieldsForDataset(int biCatalogId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-computed-fields-for-dataset", new { biCatalogId });
            var resolution = await _datasetResolver.ResolveAsync(biCatalogId, login, ct).ConfigureAwait(false);
            return (resolution.ComputedFields ?? new List<BIFieldMappingDTO>()).Where(f => f.IsVisible).ToList();
        }

        public async Task<string> CreateFromReportView(CreateBICatalogFromReportViewDTO request, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("validate-create-bicatalog-from-reportview", new { request.ReportViewId });

            await _validation.NotEmpty(request.CatalogCode, nameof(request.CatalogCode));
            await _validation.NotEmpty(request.CatalogName, nameof(request.CatalogName));

            var reportView = await _catalogDAL.GetReportViewForCatalogCreation(request.ReportViewId, login, ct).ConfigureAwait(false)
                ?? throw new ArgumentException($"ReportView {request.ReportViewId} does not exist.", nameof(request.ReportViewId));

            // The one hard exclusion this generalization cannot work around: a DAL-side
            // pivoted report's output columns are computed at run time from whatever distinct
            // values exist in the data (DataPivotEngine.TransformAsync) — there is no stable
            // field list to ever register a MBIFIELDMAPPING row against. Reject here, at
            // catalog-creation time, rather than let it fail later when someone tries to define
            // field mappings against a field list that doesn't exist yet.
            if (reportView.IsDataPivot == 1)
                throw new ArgumentException(
                    $"ReportView {request.ReportViewId} ('{reportView.ReportViewName}') is a data-pivot view — its output columns are computed at run time and cannot back a BI catalog.",
                    nameof(request.ReportViewId));

            var apiResourcePath = ResolveApiResourcePath(reportView, request.ReportViewId);

            var selfHostDataSourceId = await _dataSourceDAL.GetDataSourceIdByCode(SelfHostDataSourceCode, login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException(
                    $"No MDATASOURCE row with code '{SelfHostDataSourceCode}' exists yet — run 20260806_BIGeneralize_SelfHostDataSource_SqlServer.sql (or its Postgres mirror) first.");

            var autoNumberDTO = await _autoNumber.GetAutoNumber(1, "BICATALOG", login).ConfigureAwait(false);

            var dto = new BICatalogDTO
            {
                BICatalogId = autoNumberDTO.StartNumber,
                CatalogCode = request.CatalogCode,
                CatalogName = request.CatalogName,
                DatasetKind = DatasetKind.ApiService,
                SourceRefId = selfHostDataSourceId,
                ReportViewId = request.ReportViewId,
                ApiResourcePath = apiResourcePath,
                WorkspaceId = -1,
                TenantId = login.ClientId,
                SourceType = 4, // Admin — this is an admin-initiated creation, not a Framework/DevAdmin/ImpAdmin seed
                CreatedById = login.UserId,
                ModifiedById = login.UserId
            };

            try
            {
                GB5Trace.Step("save-bicatalog-from-reportview", new { dto.BICatalogId, request.ReportViewId });
                await _catalogDAL.Insert(dto, login, ct).ConfigureAwait(false);

                // Matches GetSelectListBICatalog.cs's own GetCacheKey() exactly (objectId=0) —
                // without this, the dataset picker keeps serving its pre-creation cached list.
                var listKey = new CacheKeyGeneration().KeyGeneration(
                    0, EntityConstant.OBJECTBICATALOG, CacheKeyLevel.CLIENT_LEVEL, login);
                await _keyInvalidate.AllInvalidateCache(listKey).ConfigureAwait(false);

                return $"{SuccessResponse.SaveSuccessMessage} {dto.BICatalogId}";
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-bicatalog-from-reportview-failed", ex);
                _logger.LogError(ex, "CreateFromReportView failed for ReportViewId {ReportViewId}", request.ReportViewId);
                await _autoNumber.RollbackAutoNumber("BICATALOG", dto.BICatalogId, login).ConfigureAwait(false);
                throw;
            }
        }

        // MREPORT.REPORTURI is almost always the literal sentinel 'NONE' on real report rows
        // (confirmed live on GB5DEMO: 1080/1083) — the actual backing-service address lives on
        // MREPORT.WEBSERVICEID -> MWEBSERVICE.URITEMPLATE for the overwhelming majority of real
        // reports instead (1055/1085). But that fallback is only usable when it's a flat,
        // parameter-free address IExcelExport.ReportURICalling can actually call (it does an
        // unconditional POST to the literal string with zero placeholder resolution) — on
        // GB5DEMO today essentially every MWEBSERVICE row is a legacy GB4 WCF endpoint
        // (`http://{BaseURI}/gb4/<Name>.svc/...`), needing base-URI + path-parameter
        // substitution ReportURICalling doesn't do. Reject those explicitly, naming the web
        // service, rather than let a legacy URL fail confusingly deep inside the HTTP call.
        private static string ResolveApiResourcePath(ReportViewForCatalogCreationDTO reportView, int reportViewId)
        {
            if (IsRealUri(reportView.ReportUri))
                return reportView.ReportUri!;

            // Modern (GB5, non-templated) URI — same precedence MenuDAL.cs's own
            // WebServiceUriTemplateFrom logic already gives SecondURITemplate over URITEMPLATE
            // (MenuDAL.cs:471-475). Never subject to the legacy-pattern rejection below — it's
            // the escape hatch FROM the legacy templated column, not another instance of it.
            // Finance Analytics Dashboards, Phase 1: without this, every report already repointed
            // at a real modern route (AccountRatioAnalysisReport et al., see the 2026-08-25
            // OU-Group filter migration) was rejected here anyway, because this method only ever
            // looked at the legacy URITEMPLATE column sitting right next to it.
            if (IsRealUri(reportView.WebServiceSecondUriTemplate))
                return reportView.WebServiceSecondUriTemplate!;

            if (!IsRealUri(reportView.WebServiceUriTemplate))
                throw new ArgumentException(
                    $"Report '{reportView.ReportName}' (ReportId {reportView.ReportId}) has no ReportUri and no WebServiceId-backed URI configured — nothing for the BI engine to call.",
                    nameof(reportViewId));

            var uri = reportView.WebServiceUriTemplate!;
            if (uri.Contains('{') || uri.Contains(".svc", StringComparison.OrdinalIgnoreCase) || uri.Contains("/gb4/", StringComparison.OrdinalIgnoreCase))
                throw new ArgumentException(
                    $"Report '{reportView.ReportName}' (ReportId {reportView.ReportId}) is backed by web service '{reportView.WebServiceName}' " +
                    $"(WebServiceId {reportView.WebServiceId}), which targets a legacy/templated endpoint ('{uri}') the BI engine's ApiService " +
                    "bridge doesn't support yet — only a flat, parameter-free GB5 report URI can back a BI catalog.",
                    nameof(reportViewId));

            return uri;
        }

        private static bool IsRealUri(string? uri)
            => !string.IsNullOrWhiteSpace(uri) && !string.Equals(uri.Trim(), "NONE", StringComparison.OrdinalIgnoreCase);

        // Reuses the existing WarehouseDimensionDTO/WarehouseMeasureDTO wire shapes for
        // ApiService-kind field mappings too — the config-UI picker endpoints and the FE
        // already speak these shapes (Field/Label/DataType, ColumnName/MeasureName/DataType/
        // AdditivityType/DefaultAggregation), so mapping here means zero FE/endpoint changes
        // for a genuinely new DatasetKind. FactId/MeasureCode/FormatString/BusinessDescription/
        // Synonyms have no ApiService equivalent — left at their type defaults; nothing reads
        // them for this kind (confirmed: the FE's MeasureOption interface only reads
        // ColumnName/MeasureName/DataType/AdditivityType/DefaultAggregation).
        private static WarehouseDimensionDTO ToWarehouseDimensionDTO(BIFieldMappingDTO m) => new()
        {
            Field = m.SourceFieldName,
            Label = m.Label,
            DataType = m.DataType
        };

        private static WarehouseMeasureDTO ToWarehouseMeasureDTO(BIFieldMappingDTO m) => new()
        {
            MeasureId = m.BIFieldMappingId,
            MeasureCode = m.SourceFieldName,
            MeasureName = m.Label,
            ColumnName = m.SourceFieldName,
            DataType = m.DataType,
            AdditivityType = m.AdditivityType ?? AdditivityType.Additive,
            SemiAdditiveDimension = m.SemiAdditiveDimension,
            DefaultAggregation = m.DefaultAggregation ?? 1,
            IsVisible = m.IsVisible
        };

        // AnalysisQuery-kind mapping — same wire shapes again, this time from
        // AnalysisQueryFieldPickerDTO (MANALYSISQUERYFIELDS, joined to DBOBJECTFIELDS for a raw
        // field name/type). No AnalysisFieldMappingId-equivalent exists for this kind, so
        // MeasureId is left at 0 — nothing currently reads MeasureId for display purposes
        // (confirmed: same FE MeasureOption interface as the ApiService mapping above).
        private static WarehouseDimensionDTO ToWarehouseDimensionDTO(AnalysisQueryFieldPickerDTO f) => new()
        {
            Field = f.Field,
            Label = f.Label,
            DataType = TranslateDbFieldDataType(f.RawDataType)
        };

        private static WarehouseMeasureDTO ToWarehouseMeasureDTO(AnalysisQueryFieldPickerDTO f) => new()
        {
            MeasureCode = f.Field,
            MeasureName = f.Label,
            ColumnName = f.Field,
            DataType = TranslateDbFieldDataType(f.RawDataType),
            // AnalysisQuery-kind has no additivity registry (a known, documented v1 gap —
            // AnalysisAggregationService.ValidateAdditivity trusts the caller for this kind
            // specifically, unchanged by this pass). AVG hints NonAdditive for picker display
            // only; it is not enforced.
            AdditivityType = f.Aggregation == 3 ? AdditivityType.NonAdditive : AdditivityType.Additive,
            DefaultAggregation = f.Aggregation ?? 1,
            IsVisible = true
        };

        // DBOBJECTFIELDS.DBFIELDATATYPE has its own numbering (0 numeric,1 varchar,2 nvarchar,
        // 3 datetime,6 int,7 tinyint,8 smallint — confirmed via EntityCreationEngine.cs's own
        // CASE mapping), NOT the same convention as WarehouseDimensionDTO.DataType (0 Int,
        // 1 String,2 Long,3 Date,4 Bool,6 Double per MREPORTVSFIELDS.FIELDTYPE) — a real
        // translation, never a passthrough, despite some values coincidentally lining up.
        private static byte TranslateDbFieldDataType(byte rawDataType) => rawDataType switch
        {
            0 => 6, // numeric -> Double
            1 => 1, // varchar -> String
            2 => 1, // nvarchar -> String
            3 => 3, // datetime -> Date
            6 => 0, // int -> Int
            7 => 0, // tinyint -> Int
            8 => 0, // smallint -> Int
            _ => 1  // unknown -> String (safe fallback)
        };
    }
}