using Hangfire;
using Microsoft.AspNetCore.SignalR;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Moq;
using NUnit.Framework;
using Strata.ContinuousImprovement.Biz.DbContexts;
using Strata.ContinuousImprovement.Biz.DbContexts.Snowflake;
using Strata.ContinuousImprovement.Biz.Exploration;
using Strata.ContinuousImprovement.Biz.Exploration.Filters;
using Strata.ContinuousImprovement.Biz.Notification;
using Strata.ContinuousImprovement.Biz.Population;
using Strata.ContinuousImprovement.Biz.Shared;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities.DistributionProcess;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities.Enums;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities.Queries;
using Strata.ContinuousImprovement.Biz.Test.Unit.DbContexts;
using Strata.ContinuousImprovement.Biz.Test.Unit.Utilities;
using Strata.DataSchema.Client;
using Strata.DataSchema.Models.Query;
using Strata.DataSchema.Models.Schema;
using Strata.Schema.Client;
using Strata.Schema.Client.Dtos;
using Strata.Schema.Client.Dtos.Info;
using Strata.SqlTools.Configuration.Common.AsyncFactory;
using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using VerifyNUnit;
using static Strata.ContinuousImprovement.Biz.Exploration.ExplorationService;

namespace Strata.ContinuousImprovement.Biz.Test.Unit.StrategicOpportunities
{
    [ExcludeFromCodeCoverage]
    [TestFixture, Category("Unit"), Category("CCI")]
    public class StrategicChargeCodeOpportunityDetailServiceTests : StrategicOpportunityTestsBase
    {
        private IStrategicChargeCodeOpportunityDetailService _strategicOpportunityDetailService;
        private Mock<IDataSchemaService> _mockDataSchemaService;
        private Mock<ISchemaServiceClient> _mockSchemaServiceClient;
        private Mock<ISnowflakeDatabaseContext> _mockSnowflakeContext;
        private Mock<IAsyncDbContextFactory<JazzDbContext>> _mockJazzDbContext;
        private Mock<IExplorationFilterService> _mockExplorationService;
        private Mock<IDistributionProcessFactory> _mockDistributionProcessFactory;
        private Mock<IStrategicOpportunityDetailQueryBuilder> _mockStrategicOpportunityDetailQueryBuilder;
        private Mock<IHubContext<NotificationHub, INotificationHub>> _mockNotificationHubClient;
        private Mock<ILogger<StrategicChargeCodeOpportunityDetailService>> _mockLogger;
        private Mock<IStrategicItemDimensionSyncService> _mockDimensionSyncService;
        private CentralDbContext _centralDbContext;

        [OneTimeSetUp]
        public void Setup()
        {
            // Create in-memory database
            var options = new DbContextOptionsBuilder<CentralDbContext>()
                .UseInMemoryDatabase(databaseName: "TestCentralDb_" + Guid.NewGuid())
                .Options;

            _centralDbContext = new CentralDbContext(options);

            // Arrange mocks
            _mockSchemaServiceClient = new Mock<ISchemaServiceClient>();
            _mockJazzDbContext = new Mock<IAsyncDbContextFactory<JazzDbContext>>();
            _mockSnowflakeContext = new Mock<ISnowflakeDatabaseContext>();
            _mockDataSchemaService = new Mock<IDataSchemaService>();
            _mockExplorationService = new Mock<IExplorationFilterService>();            
            _mockNotificationHubClient = new Mock<IHubContext<NotificationHub, INotificationHub>>();
            _mockLogger = new Mock<ILogger<StrategicChargeCodeOpportunityDetailService>>();
            _mockDistributionProcessFactory = new Mock<IDistributionProcessFactory>();
            _mockStrategicOpportunityDetailQueryBuilder = new Mock<IStrategicOpportunityDetailQueryBuilder>();
            _mockDimensionSyncService = new Mock<IStrategicItemDimensionSyncService>();
            _mockDimensionSyncService.Setup(x => x.UpsertAsync(It.IsAny<StrategicOpportunity>(), It.IsAny<CancellationToken>())).Returns(Task.CompletedTask);
            _mockDimensionSyncService.Setup(x => x.DeleteAsync(It.IsAny<long>())).Returns(Task.CompletedTask);

            // Seed ExplorationPopulation data for exploration test cases
            _centralDbContext.ExplorationPopulations.Add(new ExplorationPopulation
            {
                ExplorationPopulationId = 100,
                Name = "Test Exploration Population",
                CaseTypeFamilyId = 123,
                StrataId = 1,
                FiltersJSON = @"[{""ChipType"":4,""Key"":1,""DateRange"":[""2024-01-01T00:00:00"",""2024-12-31T00:00:00""]}]"
            });
            _centralDbContext.SaveChanges();

            // Create in-memory JazzDbContext options that will be reused
            var jazzOptions = new DbContextOptionsBuilder<JazzDbContext>()
                .UseInMemoryDatabase(databaseName: "TestJazzDb_" + Guid.NewGuid())
                .Options;

            // Seed the JazzDb with initial data
            using (var jazzDbContext = new JazzDbContext(jazzOptions))
            {
                jazzDbContext.SystemSettings.Add(new SystemSetting
                {
                    SystemSettingId = 1,
                    Name = "Flexible Exploration Inpatient Case Type Family",
                    Value = "0"
                });
                jazzDbContext.SaveChanges();
            }

            // Mock JazzDbContext factory to return a NEW context instance each time
            _mockJazzDbContext.Setup(x => x.CreateDbContextAsync(It.IsAny<CancellationToken>()))
                .ReturnsAsync(() => new JazzDbContext(jazzOptions));

            // Mock ExplorationFilterService
            _mockExplorationService.Setup(x =>
                x.GetFilterStringsAsync(It.IsAny<IEnumerable<FilterChipItem>>(), It.IsAny<IQueryParamBase>(), It.IsAny<CancellationToken>()))
                .ReturnsAsync(("WHERE DSSPES.dischargedatetime >= :DischargeDateStart AND DSSPES.dischargedatetime < :DischargeDateEnd\r\n", 
                               "AND fpes.EntityId = 1\r\n"));

            // Mock DataSchemaService
            var dataTableId = 12345;
            var dataColumnId = 67890;
            var testDataSourceId = 999;

            _mockDataSchemaService.Setup(x =>
                x.GetDataSourcesAsync(It.IsAny<CancellationToken>()))
                .ReturnsAsync(new List<Strata.DataSchema.Models.DataSourceManagement.DataSource>
                {
                    new Strata.DataSchema.Models.DataSourceManagement.DataSource
                    {
                        DataSourceId = testDataSourceId,
                        Name = "DS Strata Encounter Cost Detail"
                    }
                });

            _mockDataSchemaService.Setup(x =>
                x.GetDataTablesByDataSourceId(It.Is<int>(dataSourceId => dataSourceId == testDataSourceId), It.IsAny<bool>(), It.IsAny<DataTableType?>(), It.IsAny<CancellationToken>()))
                .ReturnsAsync((int dsId, bool includeChildren, DataTableType? tableType, CancellationToken ct) =>
                {
                    var tableAliases = new[] { "dsspes", "pblid", "costdetail", "cc", "PATIENTTYPE", "physPrimary", "dsspeci", "explorationPopulationEncounter", "msdrg" };
                    return tableAliases.Select(alias => new DataTable
                    {
                        DataTableId = dataTableId,
                        SqlAlias = alias
                    }).ToList();
                });

            _mockDataSchemaService.Setup(x =>
                x.GetDataColumnsBySqlColumnNameAsync(It.IsAny<string>(), It.IsAny<CancellationToken>()))
                .ReturnsAsync((string columnName, CancellationToken ct) => new List<DataColumn>
                {
                    new DataColumn
                    {
                        DataColumnId = dataColumnId,
                        DataTableId = dataTableId
                    }
                });

            _mockDataSchemaService.Setup(x =>
                x.BuildSqlQuery(It.IsAny<QueryConfig>(), It.IsAny<CancellationToken>()))
                .ReturnsAsync(new SqlResponse
                {
                    SqlQuery = _unitsOfServicePatPopQuery,
                    Parameters = new List<KeyValuePair<string, object>>
                    {
                        new(":dischargedatetime_Start_0", new DateTime(2024, 1, 1)),
                        new(":dischargedatetime_End_1", new DateTime(2024, 12, 31)),
                        new(":chargecodeid_2", 3541)
                    }
                });

            // Mock SchemaServiceClient
            _mockSchemaServiceClient.Setup(x =>
                x.GetDimensionByGlobalIdAsync(It.IsAny<string>(), It.IsAny<CancellationToken>()))
                .ReturnsAsync(new ScoreDimensionInfoDto { DimensionGuid = Guid.NewGuid() });

            _mockSchemaServiceClient.Setup(x =>
                x.GetDimensionMembersFromHierarchyPathsAsync(It.IsAny<Guid>(), It.IsAny<IEnumerable<string>>(), It.IsAny<CancellationToken>()))
                .ReturnsAsync(new List<MemberDto>
                {
                    new MemberDto
                    {
                        Id = "777",
                        Name = "Charge Code Stub"
                    }
                });

            _mockSchemaServiceClient.Setup(x =>
                x.GetHierarchyNodesFromHierarchyPathsAsync(It.IsAny<Guid>(), It.IsAny<IEnumerable<string>>(), It.IsAny<CancellationToken>()))
                .ReturnsAsync(new List<HierarchyNodeDto>());

            // Mock Snowflake context
            _mockSnowflakeContext.Setup(x =>
                x.QueryAsync<StrategicOpportunityDetail>(It.IsAny<string>(), It.IsAny<string>(), It.IsAny<object>(), It.IsAny<TimeSpan>()))
                .ReturnsAsync(new List<StrategicOpportunityDetail>());

            _mockSnowflakeContext.Setup(x =>
                x.ExecuteScalarAsync<double>(It.IsAny<string>(), It.IsAny<object>()))
                .ReturnsAsync(123.45);

            _mockSnowflakeContext.Setup(x =>
                x.ExecuteCommandAsync(It.IsAny<string>(), It.IsAny<object>(), It.IsAny<TimeSpan>()))
                .ReturnsAsync(1);

            var claimsPrincipalAccessor = TestUtilities.GetClaimsPrincipalAccessor();

            _strategicOpportunityDetailService = new StrategicChargeCodeOpportunityDetailService(
                _mockJazzDbContext.Object,
                _centralDbContext,
                claimsPrincipalAccessor,
                _mockDataSchemaService.Object,
                _mockSchemaServiceClient.Object,
                _mockSnowflakeContext.Object,
                _mockExplorationService.Object,
                _mockDistributionProcessFactory.Object,
                _mockStrategicOpportunityDetailQueryBuilder.Object,
                _mockNotificationHubClient.Object,
                _mockDimensionSyncService.Object,
                _mockLogger.Object);
        }

        [SetUp]
        public void TestSetup()
        {
            _centralDbContext.StrategicOpportunityMeasures.RemoveRange(_centralDbContext.StrategicOpportunityMeasures);
            _centralDbContext.StrategicOpportunityChargeCodes.RemoveRange(_centralDbContext.StrategicOpportunityChargeCodes);
            _centralDbContext.SaveChanges();
        }

        private const string _unitsOfServicePatPopQuery = @"SELECT
""DischargeDateTime"",
ZEROIFNULL(""Value"") AS ""Value""

FROM (
SELECT
""DischargeDateTime"",
SUM(""Value"") AS ""Value""

FROM (
SELECT
DSSPES.dischargedatetime AS ""DischargeDateTime"",
SUM(PBLID.unitsofservice) AS ""Value""

FROM dss.FactPatientBillingLineItemDetail AS PBLID
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid

WHERE
(((DSSPES.dischargedatetime >= :dischargedatetime_Start_0 AND DSSPES.dischargedatetime <= :dischargedatetime_End_1) AND PBLID.departmentid = :departmentid_2) AND DSSPES.primaryphysicianid = :primaryphysicianid_3)

GROUP BY
	""DischargeDateTime""


)

GROUP BY
	""DischargeDateTime""


)

ORDER BY
	""DischargeDateTime"" ASC NULLS FIRST";

        private const string _unitsOfServiceFlexPopQuery = @"SELECT
""DischargeDateTime"",
ZEROIFNULL(""Value"") AS ""Value""

FROM (
SELECT
""DischargeDateTime"",
SUM(""Value"") AS ""Value""

FROM (
SELECT
DSSPES.dischargedatetime AS ""DischargeDateTime"",
SUM(PBLID.unitsofservice) AS ""Value""

FROM dss.FactPatientBillingLineItemDetail AS PBLID
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid
LEFT JOIN CCI.FACTEXPLORATIONPOPULATIONENCOUNTER AS EXPLORATIONPOPULATIONENCOUNTER ON EXPLORATIONPOPULATIONENCOUNTER.encounterid = PBLID.encounterid

WHERE
((DSSPES.dischargedatetime >= :dischargedatetime_Start_0 AND DSSPES.dischargedatetime <= :dischargedatetime_End_1) AND EXPLORATIONPOPULATIONENCOUNTER.explorationpopulationid = :explorationpopulationid_2)

GROUP BY
	""DischargeDateTime""


)

GROUP BY
	""DischargeDateTime""


)

ORDER BY
	""DischargeDateTime"" ASC NULLS FIRST";

        private const string _contributionMarginPatPopQuery = @"SELECT
""DischargeDateTime"",
""dsspesNetRevenue"",
ZEROIFNULL(""Value"") AS ""Value""

FROM (
SELECT
""DischargeDateTime"",
""dsspesNetRevenue"",
SUM(""Value"") AS ""Value""

FROM (
SELECT
DSSPES.dischargedatetime AS ""DischargeDateTime"",
DSSPES.netrevenue AS ""dsspesNetRevenue"",
SUM(COSTDETAIL.variabledirectcost) AS ""Value""

FROM dss.FactPatientCostDetail AS COSTDETAIL
RIGHT JOIN dss.FactPatientBillingLineItemDetail AS PBLID ON PBLID.rowid = COSTDETAIL.pblidrowid
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid

WHERE
((DSSPES.dischargedatetime >= :dischargedatetime_Start_0 AND DSSPES.dischargedatetime <= :dischargedatetime_End_1))

GROUP BY
	""DischargeDateTime"",
	""dsspesNetRevenue""


)

GROUP BY
	""DischargeDateTime"",
	""dsspesNetRevenue""


)

ORDER BY
	""DischargeDateTime"" ASC NULLS FIRST,
	""dsspesNetRevenue"" ASC NULLS FIRST";

        private const string _contributionMarginFlexPopQuery = @"SELECT
""DischargeDateTime"",
""dsspesNetRevenue"",
ZEROIFNULL(""Value"") AS ""Value""

FROM (
SELECT
""DischargeDateTime"",
""dsspesNetRevenue"",
SUM(""Value"") AS ""Value""

FROM (
SELECT
DSSPES.dischargedatetime AS ""DischargeDateTime"",
DSSPES.netrevenue AS ""dsspesNetRevenue"",
SUM(COSTDETAIL.variabledirectcost) AS ""Value""

FROM dss.FactPatientCostDetail AS COSTDETAIL
RIGHT JOIN dss.FactPatientBillingLineItemDetail AS PBLID ON PBLID.rowid = COSTDETAIL.pblidrowid
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid
LEFT JOIN CCI.FACTEXPLORATIONPOPULATIONENCOUNTER AS EXPLORATIONPOPULATIONENCOUNTER ON EXPLORATIONPOPULATIONENCOUNTER.encounterid = PBLID.encounterid

WHERE
((DSSPES.dischargedatetime >= :dischargedatetime_Start_0 AND DSSPES.dischargedatetime <= :dischargedatetime_End_1) AND EXPLORATIONPOPULATIONENCOUNTER.explorationpopulationid = :explorationpopulationid_2)

GROUP BY
	""DischargeDateTime"",
	""dsspesNetRevenue""


)

GROUP BY
	""DischargeDateTime"",
	""dsspesNetRevenue""


)

ORDER BY
	""DischargeDateTime"" ASC NULLS FIRST,
	""dsspesNetRevenue"" ASC NULLS FIRST";

        // SQL returned by BuildSqlQuery for the tracking merge path with clinical indicator population
        private const string _trackingPatPopQuery = @"SELECT
""dsspesEncounterId"",
""dsspesDischargeDateTime"",
""dsspesEntityID"",
""dsspesPhysicianId"",
""dsspesPrimaryServiceLineId"",
""dsspesCPTCodeCaseTypeFamilyId"",
""dsspesMSDRGCaseTypeFamilyId"",
""dsspesAPRDRGCaseTypeFamilyId"",
""pblidDepartmentId"",
""physPrimaryPhysicianSpecialtyId"",
""ccChargeCodeID"",
""ccCostDriver"",
""encounterPatientTypeRollupPatientTypeRollup"",
ZEROIFNULL(""Units"") AS ""Units"",
ZEROIFNULL(""Cost"") AS ""Cost""

FROM (
SELECT
""dsspesEncounterId"",
""dsspesDischargeDateTime"",
""dsspesEntityID"",
""dsspesPhysicianId"",
""dsspesPrimaryServiceLineId"",
""dsspesCPTCodeCaseTypeFamilyId"",
""dsspesMSDRGCaseTypeFamilyId"",
""dsspesAPRDRGCaseTypeFamilyId"",
""pblidDepartmentId"",
""physPrimaryPhysicianSpecialtyId"",
""ccChargeCodeID"",
""ccCostDriver"",
""encounterPatientTypeRollupPatientTypeRollup"",
SUM(""Units"") AS ""Units"",
SUM(""Cost"") AS ""Cost""

FROM (
SELECT
DSSPES.encounterid AS ""dsspesEncounterId"",
DSSPES.dischargedatetime AS ""dsspesDischargeDateTime"",
DSSPES.entityid AS ""dsspesEntityID"",
DSSPES.primaryphysicianid AS ""dsspesPhysicianId"",
DSSPES.servicelineid AS ""dsspesPrimaryServiceLineId"",
DSSPES.cptcodecasetypefamilyid AS ""dsspesCPTCodeCaseTypeFamilyId"",
DSSPES.msdrgcasetypefamilyid AS ""dsspesMSDRGCaseTypeFamilyId"",
DSSPES.aprdrgcasetypefamilyid AS ""dsspesAPRDRGCaseTypeFamilyId"",
PBLID.departmentid AS ""pblidDepartmentId"",
PHYSPRIMARY.physicianspecialtyid AS ""physPrimaryPhysicianSpecialtyId"",
CC.chargecodeid AS ""ccChargeCodeID"",
IFNULL(CC.costdriver::TEXT, '') AS ""ccCostDriver"",
IFNULL(ENCOUNTERPATIENTTYPEROLLUP.patienttyperollup::TEXT, '') AS ""encounterPatientTypeRollupPatientTypeRollup"",
NULL AS ""Cost"",
SUM(PBLID.unitsofservice) AS ""Units""

FROM dss.FactPatientBillingLineItemDetail AS PBLID
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid
LEFT JOIN dss.FactPatientEncounterClinicalIndicator AS DSSPECI ON DSSPECI.encounterid = PBLID.encounterid
LEFT JOIN CCI.VIEWENCOUNTERPATIENTTYPEROLLUP AS ENCOUNTERPATIENTTYPEROLLUP ON ENCOUNTERPATIENTTYPEROLLUP.encounterid = PBLID.encounterid
INNER JOIN FW.DIMCHARGECODE AS CC ON CC.chargecodeid = PBLID.chargecodeid
INNER JOIN DSS.DIMPHYSICIAN AS PHYSPRIMARY ON PHYSPRIMARY.physicianid = DSSPES.primaryphysicianid

WHERE
((DSSPES.dischargedatetime >= :dischargedatetime_Start_0 AND DSSPES.dischargedatetime <= :dischargedatetime_End_1) AND DSSPECI.clinicalindicatorid = :clinicalindicatorid_2)

GROUP BY
	""dsspesEncounterId"",
	""dsspesDischargeDateTime"",
	""dsspesEntityID"",
	""dsspesPhysicianId"",
	""dsspesPrimaryServiceLineId"",
	""dsspesCPTCodeCaseTypeFamilyId"",
	""dsspesMSDRGCaseTypeFamilyId"",
	""dsspesAPRDRGCaseTypeFamilyId"",
	""pblidDepartmentId"",
	""physPrimaryPhysicianSpecialtyId"",
	""ccChargeCodeID"",
	""ccCostDriver"",
	""encounterPatientTypeRollupPatientTypeRollup""

UNION ALL

SELECT
DSSPES.encounterid AS ""dsspesEncounterId"",
DSSPES.dischargedatetime AS ""dsspesDischargeDateTime"",
DSSPES.entityid AS ""dsspesEntityID"",
DSSPES.primaryphysicianid AS ""dsspesPhysicianId"",
DSSPES.servicelineid AS ""dsspesPrimaryServiceLineId"",
DSSPES.cptcodecasetypefamilyid AS ""dsspesCPTCodeCaseTypeFamilyId"",
DSSPES.msdrgcasetypefamilyid AS ""dsspesMSDRGCaseTypeFamilyId"",
DSSPES.aprdrgcasetypefamilyid AS ""dsspesAPRDRGCaseTypeFamilyId"",
PBLID.departmentid AS ""pblidDepartmentId"",
PHYSPRIMARY.physicianspecialtyid AS ""physPrimaryPhysicianSpecialtyId"",
CC.chargecodeid AS ""ccChargeCodeID"",
IFNULL(CC.costdriver::TEXT, '') AS ""ccCostDriver"",
IFNULL(ENCOUNTERPATIENTTYPEROLLUP.patienttyperollup::TEXT, '') AS ""encounterPatientTypeRollupPatientTypeRollup"",
SUM(COSTDETAIL.variabledirectcost) AS ""Cost"",
NULL AS ""Units""

FROM dss.FactPatientCostDetail AS COSTDETAIL
RIGHT JOIN dss.FactPatientBillingLineItemDetail AS PBLID ON PBLID.rowid = COSTDETAIL.pblidrowid
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid
LEFT JOIN dss.FactPatientEncounterClinicalIndicator AS DSSPECI ON DSSPECI.encounterid = PBLID.encounterid
LEFT JOIN CCI.VIEWENCOUNTERPATIENTTYPEROLLUP AS ENCOUNTERPATIENTTYPEROLLUP ON ENCOUNTERPATIENTTYPEROLLUP.encounterid = PBLID.encounterid
INNER JOIN FW.DIMCHARGECODE AS CC ON CC.chargecodeid = PBLID.chargecodeid
INNER JOIN DSS.DIMPHYSICIAN AS PHYSPRIMARY ON PHYSPRIMARY.physicianid = DSSPES.primaryphysicianid

WHERE
((DSSPES.dischargedatetime >= :dischargedatetime_Start_3 AND DSSPES.dischargedatetime <= :dischargedatetime_End_4) AND DSSPECI.clinicalindicatorid = :clinicalindicatorid_5)

GROUP BY
	""dsspesEncounterId"",
	""dsspesDischargeDateTime"",
	""dsspesEntityID"",
	""dsspesPhysicianId"",
	""dsspesPrimaryServiceLineId"",
	""dsspesCPTCodeCaseTypeFamilyId"",
	""dsspesMSDRGCaseTypeFamilyId"",
	""dsspesAPRDRGCaseTypeFamilyId"",
	""pblidDepartmentId"",
	""physPrimaryPhysicianSpecialtyId"",
	""ccChargeCodeID"",
	""ccCostDriver"",
	""encounterPatientTypeRollupPatientTypeRollup""


)

GROUP BY
	""dsspesEncounterId"",
	""dsspesDischargeDateTime"",
	""dsspesEntityID"",
	""dsspesPhysicianId"",
	""dsspesPrimaryServiceLineId"",
	""dsspesCPTCodeCaseTypeFamilyId"",
	""dsspesMSDRGCaseTypeFamilyId"",
	""dsspesAPRDRGCaseTypeFamilyId"",
	""pblidDepartmentId"",
	""physPrimaryPhysicianSpecialtyId"",
	""ccChargeCodeID"",
	""ccCostDriver"",
	""encounterPatientTypeRollupPatientTypeRollup""


)

ORDER BY
	""dsspesEncounterId"" ASC NULLS FIRST,
	""dsspesDischargeDateTime"" ASC NULLS FIRST,
	""dsspesEntityID"" ASC NULLS FIRST,
	""dsspesPhysicianId"" ASC NULLS FIRST,
	""dsspesPrimaryServiceLineId"" ASC NULLS FIRST,
	""dsspesCPTCodeCaseTypeFamilyId"" ASC NULLS FIRST,
	""dsspesMSDRGCaseTypeFamilyId"" ASC NULLS FIRST,
	""dsspesAPRDRGCaseTypeFamilyId"" ASC NULLS FIRST,
	""pblidDepartmentId"" ASC NULLS FIRST,
	""physPrimaryPhysicianSpecialtyId"" ASC NULLS FIRST,
	""ccChargeCodeID"" ASC NULLS FIRST,
	""ccCostDriver"" ASC NULLS FIRST,
	""encounterPatientTypeRollupPatientTypeRollup"" ASC NULLS FIRST";

        // SQL returned by BuildSqlQuery for the tracking merge path with exploration population
        private const string _trackingFlexPopQuery = @"SELECT
""dsspesEncounterId"",
""dsspesDischargeDateTime"",
""dsspesEntityID"",
""dsspesPhysicianId"",
""dsspesPrimaryServiceLineId"",
""dsspesCPTCodeCaseTypeFamilyId"",
""dsspesMSDRGCaseTypeFamilyId"",
""dsspesAPRDRGCaseTypeFamilyId"",
""pblidDepartmentId"",
""physPrimaryPhysicianSpecialtyId"",
""ccChargeCodeID"",
""ccCostDriver"",
""encounterPatientTypeRollupPatientTypeRollup"",
ZEROIFNULL(""Units"") AS ""Units"",
ZEROIFNULL(""Cost"") AS ""Cost""

FROM (
SELECT
""dsspesEncounterId"",
""dsspesDischargeDateTime"",
""dsspesEntityID"",
""dsspesPhysicianId"",
""dsspesPrimaryServiceLineId"",
""dsspesCPTCodeCaseTypeFamilyId"",
""dsspesMSDRGCaseTypeFamilyId"",
""dsspesAPRDRGCaseTypeFamilyId"",
""pblidDepartmentId"",
""physPrimaryPhysicianSpecialtyId"",
""ccChargeCodeID"",
""ccCostDriver"",
""encounterPatientTypeRollupPatientTypeRollup"",
SUM(""Units"") AS ""Units"",
SUM(""Cost"") AS ""Cost""

FROM (
SELECT
DSSPES.encounterid AS ""dsspesEncounterId"",
DSSPES.dischargedatetime AS ""dsspesDischargeDateTime"",
DSSPES.entityid AS ""dsspesEntityID"",
DSSPES.primaryphysicianid AS ""dsspesPhysicianId"",
DSSPES.servicelineid AS ""dsspesPrimaryServiceLineId"",
DSSPES.cptcodecasetypefamilyid AS ""dsspesCPTCodeCaseTypeFamilyId"",
DSSPES.msdrgcasetypefamilyid AS ""dsspesMSDRGCaseTypeFamilyId"",
DSSPES.aprdrgcasetypefamilyid AS ""dsspesAPRDRGCaseTypeFamilyId"",
PBLID.departmentid AS ""pblidDepartmentId"",
PHYSPRIMARY.physicianspecialtyid AS ""physPrimaryPhysicianSpecialtyId"",
CC.chargecodeid AS ""ccChargeCodeID"",
IFNULL(CC.costdriver::TEXT, '') AS ""ccCostDriver"",
IFNULL(ENCOUNTERPATIENTTYPEROLLUP.patienttyperollup::TEXT, '') AS ""encounterPatientTypeRollupPatientTypeRollup"",
NULL AS ""Cost"",
SUM(PBLID.unitsofservice) AS ""Units""

FROM dss.FactPatientBillingLineItemDetail AS PBLID
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid
LEFT JOIN CCI.FACTEXPLORATIONPOPULATIONENCOUNTER AS EXPLORATIONPOPULATIONENCOUNTER ON EXPLORATIONPOPULATIONENCOUNTER.encounterid = PBLID.encounterid
LEFT JOIN CCI.VIEWENCOUNTERPATIENTTYPEROLLUP AS ENCOUNTERPATIENTTYPEROLLUP ON ENCOUNTERPATIENTTYPEROLLUP.encounterid = PBLID.encounterid
INNER JOIN FW.DIMCHARGECODE AS CC ON CC.chargecodeid = PBLID.chargecodeid
INNER JOIN DSS.DIMPHYSICIAN AS PHYSPRIMARY ON PHYSPRIMARY.physicianid = DSSPES.primaryphysicianid

WHERE
((DSSPES.dischargedatetime >= :dischargedatetime_Start_0 AND DSSPES.dischargedatetime <= :dischargedatetime_End_1) AND EXPLORATIONPOPULATIONENCOUNTER.explorationpopulationid = :explorationpopulationid_2)

GROUP BY
	""dsspesEncounterId"",
	""dsspesDischargeDateTime"",
	""dsspesEntityID"",
	""dsspesPhysicianId"",
	""dsspesPrimaryServiceLineId"",
	""dsspesCPTCodeCaseTypeFamilyId"",
	""dsspesMSDRGCaseTypeFamilyId"",
	""dsspesAPRDRGCaseTypeFamilyId"",
	""pblidDepartmentId"",
	""physPrimaryPhysicianSpecialtyId"",
	""ccChargeCodeID"",
	""ccCostDriver"",
	""encounterPatientTypeRollupPatientTypeRollup""

UNION ALL

SELECT
DSSPES.encounterid AS ""dsspesEncounterId"",
DSSPES.dischargedatetime AS ""dsspesDischargeDateTime"",
DSSPES.entityid AS ""dsspesEntityID"",
DSSPES.primaryphysicianid AS ""dsspesPhysicianId"",
DSSPES.servicelineid AS ""dsspesPrimaryServiceLineId"",
DSSPES.cptcodecasetypefamilyid AS ""dsspesCPTCodeCaseTypeFamilyId"",
DSSPES.msdrgcasetypefamilyid AS ""dsspesMSDRGCaseTypeFamilyId"",
DSSPES.aprdrgcasetypefamilyid AS ""dsspesAPRDRGCaseTypeFamilyId"",
PBLID.departmentid AS ""pblidDepartmentId"",
PHYSPRIMARY.physicianspecialtyid AS ""physPrimaryPhysicianSpecialtyId"",
CC.chargecodeid AS ""ccChargeCodeID"",
IFNULL(CC.costdriver::TEXT, '') AS ""ccCostDriver"",
IFNULL(ENCOUNTERPATIENTTYPEROLLUP.patienttyperollup::TEXT, '') AS ""encounterPatientTypeRollupPatientTypeRollup"",
SUM(COSTDETAIL.variabledirectcost) AS ""Cost"",
NULL AS ""Units""

FROM dss.FactPatientCostDetail AS COSTDETAIL
RIGHT JOIN dss.FactPatientBillingLineItemDetail AS PBLID ON PBLID.rowid = COSTDETAIL.pblidrowid
RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid
LEFT JOIN CCI.FACTEXPLORATIONPOPULATIONENCOUNTER AS EXPLORATIONPOPULATIONENCOUNTER ON EXPLORATIONPOPULATIONENCOUNTER.encounterid = PBLID.encounterid
LEFT JOIN CCI.VIEWENCOUNTERPATIENTTYPEROLLUP AS ENCOUNTERPATIENTTYPEROLLUP ON ENCOUNTERPATIENTTYPEROLLUP.encounterid = PBLID.encounterid
INNER JOIN FW.DIMCHARGECODE AS CC ON CC.chargecodeid = PBLID.chargecodeid
INNER JOIN DSS.DIMPHYSICIAN AS PHYSPRIMARY ON PHYSPRIMARY.physicianid = DSSPES.primaryphysicianid

WHERE
((DSSPES.dischargedatetime >= :dischargedatetime_Start_3 AND DSSPES.dischargedatetime <= :dischargedatetime_End_4) AND EXPLORATIONPOPULATIONENCOUNTER.explorationpopulationid = :explorationpopulationid_5)

GROUP BY
	""dsspesEncounterId"",
	""dsspesDischargeDateTime"",
	""dsspesEntityID"",
	""dsspesPhysicianId"",
	""dsspesPrimaryServiceLineId"",
	""dsspesCPTCodeCaseTypeFamilyId"",
	""dsspesMSDRGCaseTypeFamilyId"",
	""dsspesAPRDRGCaseTypeFamilyId"",
	""pblidDepartmentId"",
	""physPrimaryPhysicianSpecialtyId"",
	""ccChargeCodeID"",
	""ccCostDriver"",
	""encounterPatientTypeRollupPatientTypeRollup""


)

GROUP BY
	""dsspesEncounterId"",
	""dsspesDischargeDateTime"",
	""dsspesEntityID"",
	""dsspesPhysicianId"",
	""dsspesPrimaryServiceLineId"",
	""dsspesCPTCodeCaseTypeFamilyId"",
	""dsspesMSDRGCaseTypeFamilyId"",
	""dsspesAPRDRGCaseTypeFamilyId"",
	""pblidDepartmentId"",
	""physPrimaryPhysicianSpecialtyId"",
	""ccChargeCodeID"",
	""ccCostDriver"",
	""encounterPatientTypeRollupPatientTypeRollup""


)

ORDER BY
	""dsspesEncounterId"" ASC NULLS FIRST,
	""dsspesDischargeDateTime"" ASC NULLS FIRST,
	""dsspesEntityID"" ASC NULLS FIRST,
	""dsspesPhysicianId"" ASC NULLS FIRST,
	""dsspesPrimaryServiceLineId"" ASC NULLS FIRST,
	""dsspesCPTCodeCaseTypeFamilyId"" ASC NULLS FIRST,
	""dsspesMSDRGCaseTypeFamilyId"" ASC NULLS FIRST,
	""dsspesAPRDRGCaseTypeFamilyId"" ASC NULLS FIRST,
	""pblidDepartmentId"" ASC NULLS FIRST,
	""physPrimaryPhysicianSpecialtyId"" ASC NULLS FIRST,
	""ccChargeCodeID"" ASC NULLS FIRST,
	""ccCostDriver"" ASC NULLS FIRST,
	""encounterPatientTypeRollupPatientTypeRollup"" ASC NULLS FIRST";
    }
}