using NUnit.Framework;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities.Dtos;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities.Enums;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities.Queries;
using Strata.ContinuousImprovement.Biz.Test.Unit.Utilities;
using Strata.DataSchema.Models.Query;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using VerifyNUnit;

namespace Strata.ContinuousImprovement.Biz.Test.Unit.StrategicOpportunities.Queries
{
    [TestFixture]
    public class StrategicOpportunityDetailQueryBuilderTests : StrategicOpportunityTestsBase
    {
        private IStrategicOpportunityDetailQueryBuilder _builder;
        private const long TestOpportunityId = 123;
        private const long TestMeasureId = 456;
        private const string TestStartDate = "2024-01-01";
        private const int TestTrackingMonths = 12;
        private const string SelectQuery = "SELECT * FROM TestTable";
        private const string InvalidSelectQuery = "UPDATE TestTable SET Column1 = 'Value'";
        private const string ComplexSelectQuery = @"
            SELECT 
                *
            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
        ";

        [SetUp]
        public void Setup()
        {
            _builder = new StrategicOpportunityDetailQueryBuilder();
        }

        #region BuildInsertQuery Tests

        [TestCase(true, TestName = "BuildInsertQuery with APRDRG")]
        [TestCase(false, TestName = "BuildInsertQuery without APRDRG")]
        public async Task BuildInsertQuery_ShouldGenerateValidSql(bool isUsingAprdrg)
        {
            // Arrange
            var opportunity = new StrategicOpportunityChargeCode { OpportunityId = TestOpportunityId };

            // Act
            var result = _builder.BuildInsertQuery(opportunity, SelectQuery, isUsingAprdrg);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        [Test]
        public void BuildInsertQuery_WithInvalidSelectQuery_ShouldThrowException()
        {
            // Arrange
            var opportunity = new StrategicOpportunityChargeCode { OpportunityId = TestOpportunityId };

            // Act & Assert
            var ex = Assert.Throws<InvalidOperationException>(() => 
                _builder.BuildInsertQuery(opportunity, InvalidSelectQuery, true));
            Assert.That(ex.Message, Does.Contain("Unexpected SQL syntax. SELECT should be first word."));
        }

        #endregion

        #region BuildDeleteQuery Tests

        [Test]
        public async Task BuildDeleteQuery_WithOpportunityId_ShouldGenerateValidSql()
        {
            // Act
            var result = _builder.BuildDeleteQuery(TestOpportunityId);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        [Test]
        public async Task BuildDeleteQuery_WithoutOpportunityId_ShouldGenerateParameterizedSql()
        {
            // Act
            var result = _builder.BuildDeleteQuery();

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        #endregion

        #region BuildMergeQuery Tests

        [TestCase(true, TestName = "BuildMergeQuery with APRDRG")]
        [TestCase(false, TestName = "BuildMergeQuery without APRDRG")]
        public async Task BuildMergeQuery_ShouldGenerateValidSql(bool isUsingAprdrg)
        {
            // Act
            var result = _builder.BuildMergeQuery(TestOpportunityId, SelectQuery, isUsingAprdrg);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        [Test]
        public void BuildMergeQuery_WithInvalidSelectQuery_ShouldThrowException()
        {
            // Act & Assert
            var ex = Assert.Throws<InvalidOperationException>(() => 
                _builder.BuildMergeQuery(TestOpportunityId, InvalidSelectQuery, true));
            Assert.That(ex.Message, Does.Contain("Unexpected SQL syntax. SELECT should be first word."));
        }

        #endregion

        #region BuildGetDistinctEncounterCountQuery Tests

        [TestCase(true, TestName = "BuildGetDistinctEncounterCountQuery with APRDRG")]
        [TestCase(false, TestName = "BuildGetDistinctEncounterCountQuery without APRDRG")]
        public async Task BuildGetDistinctEncounterCountQuery_ShouldGenerateValidSql(bool isUsingAprdrg)
        {
            // Arrange
            var getData = new StrategicOpportunityGetDetailData
            {
                Dimensions = new[] { "entity", "chargeCode", "costDriver" }
            };

            // Act
            var result = _builder.BuildGetDistinctEncounterCountQuery(TestOpportunityId, SelectQuery, isUsingAprdrg, getData);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        [Test]
        public void BuildGetDistinctEncounterCountQuery_ShouldThrowException()
        {
            // Arrange
            var getData = new StrategicOpportunityGetDetailData
            {
                Dimensions = new[] { "entity", "chargeCode", "costDriver" }
            };

            // Act & Assert
            var ex = Assert.Throws<InvalidOperationException>(() => 
                _builder.BuildGetDistinctEncounterCountQuery(TestOpportunityId, InvalidSelectQuery, true, getData));
            Assert.That(ex.Message, Does.Contain("Unexpected SQL syntax. SELECT should be first word."));
        }

        #endregion

        #region BuildMergeSystemMeasureMergeQuery Tests

        [TestCaseSource(nameof(GetMeasureTypeTestCases))]
        public async Task BuildMergeSystemMeasureMergeQuery_WithDifferentMeasureTypes_ShouldGenerateValidSql(MeasureType measureType)
        {
            // Act
            var result = _builder.BuildMergeSystemMeasureMergeQuery(TestOpportunityId, TestMeasureId, measureType, TestStartDate, TestTrackingMonths);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName($"{TestContext.CurrentContext.Test.Name}_{measureType}");
        }

        private static IEnumerable<TestCaseData> GetMeasureTypeTestCases()
        {
            yield return new TestCaseData(MeasureType.CommittedCost).SetName("BuildMergeSystemMeasureMergeQuery with CommittedCost");
            yield return new TestCaseData(MeasureType.ActualCost).SetName("BuildMergeSystemMeasureMergeQuery with ActualCost");
        }

        #endregion

        #region GetSelectMeasureSql Tests
                
        [TestCaseSource(nameof(GetSelectMeasureSql_TestCases))]
        public async Task GetSelectMeasureSql_ShouldGenerateValidSql(MeasureType measureType)
        {
            // Arrange
            var marginFilters = new Dictionary<string, FilterCondition>
            {
                { "DepartmentId", new FilterCondition { Operator = FilterOperator.In, Values = new List<string> { "1", "2", "3", "4", "5" } } },
                { "ChargeCodeId", new FilterCondition { Operator = FilterOperator.NotIn, Values = new List<string> { "100", "200", "300" } } },
                { "EntityId", new FilterCondition { Operator = FilterOperator.In, Values = new List<string> { "999" } } }
            };

            // Act
            var result = _builder.GetSelectMeasureSql(TestOpportunityId, measureType, ComplexSelectQuery, marginFilters);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }
        
        [Test]
        public async Task GetSelectMeasureSql_EncountersCases()
        {
            // Arrange
            var marginFilters = new Dictionary<string, FilterCondition>();
            var complexSelectQuery = ComplexSelectQuery.Replace(
                "RIGHT JOIN clientdss.FactPatientEncounterSummary AS DSSPES ON DSSPES.encounterid = PBLID.encounterid",
                "INNER JOIN (SELECT * FROM clientdss.FactPatientEncounterSummary AS DSSPES) AS DSSPES ON DSSPES.encounterid = PBLID.encounterid"
            );

            // Act
            var result = _builder.GetSelectMeasureSql(TestOpportunityId, MeasureType.EncountersCases, complexSelectQuery, marginFilters);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        private static IEnumerable<TestCaseData> GetSelectMeasureSql_TestCases()
        {
            foreach (var x in Enum.GetValues<MeasureType>().Where(x => x < MeasureType.CommittedCost))
            {
                yield return new TestCaseData(x)
                    .SetName($"GetSelectMeasureSql with {x}");
            }            
        }

        #endregion

        #region WrapIntoMeasureMergeQuery Tests

        [Test]
        public async Task WrapIntoMeasureMergeQuery_ShouldWrapSelectQueryCorrectly()
        {
            // Act
            var result = _builder.WrapIntoMeasureMergeQuery(SelectQuery);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        #endregion

        #region WrapIntoMeasureAverageQuery Tests

        [Test]
        public async Task WrapIntoMeasureAverageQuery_ShouldWrapSelectQueryCorrectly()
        {
            // Act
            var result = _builder.WrapIntoMeasureAverageQuery(SelectQuery);

            // Assert
            await Verifier.Verify(result, VerifySettings)
                .UseFileName(TestContext.CurrentContext.TestName());
        }

        #endregion

        #region GetDeleteAllMeasuresSql Tests

        [TestCaseSource(nameof(GetDeleteAllMeasuresSql_TestCases))]
        public async Task GetDeleteAllMeasuresSql_ShouldGenerateValidSql(long[] measureIds)
        {
            // Act
            var result = _builder.GetDeleteAllMeasuresSql(measureIds);

            // Assert
            if (measureIds == null || measureIds.Length == 0)
            {
                Assert.That(result, Is.Empty);
            }            
            else
            {
                await Verifier.Verify(result, VerifySettings)
                    .UseFileName(TestContext.CurrentContext.TestName());
            }
            
        }
        private static IEnumerable<TestCaseData> GetDeleteAllMeasuresSql_TestCases()
        {
            yield return new TestCaseData(new long[] { 1, 2, 3, 4, 5 })
                .SetName("GetDeleteAllMeasuresSql with MeasureIds");
            yield return new TestCaseData(new long[] { })
                .SetName("GetDeleteAllMeasuresSql with empty MeasureIds");
            yield return new TestCaseData(null)
                .SetName("GetDeleteAllMeasuresSql with null MeasureIds");
        }
        #endregion
    }
}