using FluentAssertions;
using NUnit.Framework;
using Strata.ContinuousImprovement.Biz.StrategicOpportunities.Queries;
using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;

namespace Strata.ContinuousImprovement.Biz.Test.Unit.StrategicOpportunities
{
    [ExcludeFromCodeCoverage]
    [TestFixture, Category("Unit"), Category("CCI")]
    internal class StrategicOpportunityGLDetailQueryBuilderTest
    {
        private StrategicOpportunityGLDetailQueryBuilder _queryBuilder;

        [SetUp]
        public void Setup()
        {
            _queryBuilder = new StrategicOpportunityGLDetailQueryBuilder();
        }

        [Test]
        public void BuildDeleteQuery_WithOpportunityId_EmbedsIdDirectly()
        {
            var result = _queryBuilder.BuildDeleteQuery(42);

            result.Should().Contain("OpportunityId = 42");
            result.Should().NotContain(":opportunityId");
        }

        [Test]
        public void BuildDeleteQuery_WithoutOpportunityId_UsesParameterWithoutTrailingSemicolon()
        {
            var result = _queryBuilder.BuildDeleteQuery(null);

            result.Should().Contain(":opportunityId");
            result.Should().NotContain(":opportunityId;");
        }

        [Test]
        public void BuildInsertQuery_WithTypedAccountIds_GeneratesAccountTypeMapCte()
        {
            var revenueIds = new List<int> { 100, 101 };
            var expenseIds = new List<int> { 200 };
            var statisticIds = new List<int> { 300 };

            var result = _queryBuilder.BuildInsertQuery(
                opportunityId: 42,
                sourceTable: "fw.factglsampled",
                departmentIds: new List<int> { 1, 2 },
                revenueAccountIds: revenueIds,
                expenseAccountIds: expenseIds,
                statisticAccountIds: statisticIds,
                baselineStart: new DateOnly(2024, 1, 1),
                baselineEnd: new DateOnly(2024, 12, 31));

            result.Should().Contain("(100, 0)");
            result.Should().Contain("(101, 0)");
            result.Should().Contain("(200, 1)");
            result.Should().Contain("(300, 2)");
        }

        [Test]
        public void BuildInsertQuery_WithTypedAccountIds_IncludesAccountTypeInInsertAndSelect()
        {
            var result = _queryBuilder.BuildInsertQuery(
                opportunityId: 42,
                sourceTable: "fw.factglsampled",
                departmentIds: new List<int> { 1 },
                revenueAccountIds: new List<int> { 100 },
                expenseAccountIds: new List<int> { 200 },
                statisticAccountIds: new List<int> { 300 },
                baselineStart: new DateOnly(2024, 1, 1),
                baselineEnd: new DateOnly(2024, 12, 31));

            result.Should().Contain("AccountType,");
            result.Should().Contain("at.AccountType,");
            result.Should().Contain("at.AccountType;");
            result.Should().Contain("JOIN (SELECT AccountId, AccountType FROM (VALUES");
        }

        [Test]
        public void BuildMergeQuery_WithTypedAccountIds_IncludesAccountTypeInMergeConditionAndInsert()
        {
            var result = _queryBuilder.BuildMergeQuery(
                opportunityId: 42,
                sourceTable: "fw.factglsampled",
                departmentIds: new List<int> { 1 },
                revenueAccountIds: new List<int> { 100 },
                expenseAccountIds: new List<int> { 200 },
                statisticAccountIds: new List<int> { 300 },
                trackingStart: new DateOnly(2025, 1, 1),
                trackingEnd: new DateOnly(2025, 12, 31));

            result.Should().Contain("AND t.AccountType = s.AccountType");
            result.Should().Contain("AccountId, AccountType, Amount");
            result.Should().Contain("s.AccountType, s.Amount");
            result.Should().Contain("at.AccountType,");
            result.Should().Contain("JOIN (SELECT AccountId, AccountType FROM (VALUES");
        }
    }
}