using System.CommandLine;
using System.Text.Json;
using JdeScoping.ConfigManager.Cli.Commands;
using JdeScoping.ConfigManager.Core.Services;
using JdeScoping.DataSync.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace JdeScoping.ConfigManager.Cli.Tests.Commands;

[Collection("Console Tests")]
public class PipelineElementCommandsTests
{
    private readonly IServiceProvider _serviceProvider;
    private readonly IConfigFileService _configFileService;
    private readonly IAutoDiscoveryService _autoDiscoveryService;
    private readonly Option<string?> _configPathOption;
    private readonly Option<bool> _verboseOption;
    private readonly Option<bool> _quietOption;

    public PipelineElementCommandsTests()
    {
        _configFileService = Substitute.For<IConfigFileService>();
        _autoDiscoveryService = Substitute.For<IAutoDiscoveryService>();

        var services = new ServiceCollection();
        services.AddSingleton(_configFileService);
        services.AddSingleton(_autoDiscoveryService);
        services.AddTestLogging();
        _serviceProvider = services.BuildServiceProvider();

        _configPathOption = new Option<string?>(["--config-path", "-c"]);
        _verboseOption = new Option<bool>(["--verbose", "-v"]);
        _quietOption = new Option<bool>(["--quiet", "-q"]);
    }

    #region Command Structure Tests

    [Fact]
    public void CreatePreScriptCommand_ReturnsCommandWithSubcommands()
    {
        // Act
        var command = PipelineElementCommands.CreatePreScriptCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);

        // Assert
        command.ShouldNotBeNull();
        command.Name.ShouldBe("prescript");
        command.Subcommands.ShouldContain(c => c.Name == "add");
        command.Subcommands.ShouldContain(c => c.Name == "remove");
        command.Subcommands.ShouldContain(c => c.Name == "edit");
        command.Subcommands.ShouldContain(c => c.Name == "move-up");
        command.Subcommands.ShouldContain(c => c.Name == "move-down");
    }

    [Fact]
    public void CreatePostScriptCommand_ReturnsCommandWithSubcommands()
    {
        // Act
        var command = PipelineElementCommands.CreatePostScriptCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);

        // Assert
        command.ShouldNotBeNull();
        command.Name.ShouldBe("postscript");
        command.Subcommands.ShouldContain(c => c.Name == "add");
        command.Subcommands.ShouldContain(c => c.Name == "remove");
        command.Subcommands.ShouldContain(c => c.Name == "edit");
        command.Subcommands.ShouldContain(c => c.Name == "move-up");
        command.Subcommands.ShouldContain(c => c.Name == "move-down");
    }

    [Fact]
    public void CreateTransformCommand_ReturnsCommandWithSubcommands()
    {
        // Act
        var command = PipelineElementCommands.CreateTransformCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);

        // Assert
        command.ShouldNotBeNull();
        command.Name.ShouldBe("transform");
        command.Subcommands.ShouldContain(c => c.Name == "add");
        command.Subcommands.ShouldContain(c => c.Name == "remove");
        command.Subcommands.ShouldContain(c => c.Name == "edit");
        command.Subcommands.ShouldContain(c => c.Name == "move-up");
        command.Subcommands.ShouldContain(c => c.Name == "move-down");
    }

    [Fact]
    public void CreateSourceCommand_ReturnsCommandWithEditSubcommand()
    {
        // Act
        var command = PipelineElementCommands.CreateSourceCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);

        // Assert
        command.ShouldNotBeNull();
        command.Name.ShouldBe("source");
        command.Subcommands.ShouldContain(c => c.Name == "edit");
    }

    [Fact]
    public void CreateDestinationCommand_ReturnsCommandWithEditSubcommand()
    {
        // Act
        var command = PipelineElementCommands.CreateDestinationCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);

        // Assert
        command.ShouldNotBeNull();
        command.Name.ShouldBe("destination");
        command.Subcommands.ShouldContain(c => c.Name == "edit");
    }

    [Fact]
    public void PreScriptAddCommand_HasRequiredOptions()
    {
        // Act
        var prescript = PipelineElementCommands.CreatePreScriptCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);
        var addCommand = prescript.Subcommands.First(c => c.Name == "add");

        // Assert
        addCommand.Arguments.ShouldContain(a => a.Name == "pipeline-name");
        addCommand.Options.ShouldContain(o => o.Name == "connection");
        addCommand.Options.ShouldContain(o => o.Name == "script");
        addCommand.Options.ShouldContain(o => o.Name == "script-file");
        addCommand.Options.ShouldContain(o => o.Name == "at-index");
    }

    [Fact]
    public void TransformAddCommand_HasRequiredOptions()
    {
        // Act
        var transform = PipelineElementCommands.CreateTransformCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);
        var addCommand = transform.Subcommands.First(c => c.Name == "add");

        // Assert
        addCommand.Arguments.ShouldContain(a => a.Name == "pipeline-name");
        addCommand.Options.ShouldContain(o => o.Name == "type");
        addCommand.Options.ShouldContain(o => o.Name == "config");
        addCommand.Options.ShouldContain(o => o.Name == "columns");
        addCommand.Options.ShouldContain(o => o.Name == "mappings");
        addCommand.Options.ShouldContain(o => o.Name == "date-column");
        addCommand.Options.ShouldContain(o => o.Name == "time-column");
        addCommand.Options.ShouldContain(o => o.Name == "output-column");
        addCommand.Options.ShouldContain(o => o.Name == "column");
        addCommand.Options.ShouldContain(o => o.Name == "pattern");
        addCommand.Options.ShouldContain(o => o.Name == "replacement");
        addCommand.Options.ShouldContain(o => o.Name == "ignore-case");
    }

    [Fact]
    public void SourceEditCommand_HasRequiredOptions()
    {
        // Act
        var source = PipelineElementCommands.CreateSourceCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);
        var editCommand = source.Subcommands.First(c => c.Name == "edit");

        // Assert
        editCommand.Arguments.ShouldContain(a => a.Name == "pipeline-name");
        editCommand.Options.ShouldContain(o => o.Name == "connection");
        editCommand.Options.ShouldContain(o => o.Name == "query");
        editCommand.Options.ShouldContain(o => o.Name == "query-file");
        editCommand.Options.ShouldContain(o => o.Name == "mass-query");
        editCommand.Options.ShouldContain(o => o.Name == "mass-query-file");
        editCommand.Options.ShouldContain(o => o.Name == "dry-run");
    }

    [Fact]
    public void DestinationEditCommand_HasRequiredOptions()
    {
        // Act
        var destination = PipelineElementCommands.CreateDestinationCommand(
            _serviceProvider, _configPathOption, _verboseOption, _quietOption);
        var editCommand = destination.Subcommands.First(c => c.Name == "edit");

        // Assert
        editCommand.Arguments.ShouldContain(a => a.Name == "pipeline-name");
        editCommand.Options.ShouldContain(o => o.Name == "table");
        editCommand.Options.ShouldContain(o => o.Name == "match-columns");
        editCommand.Options.ShouldContain(o => o.Name == "add-match-column");
        editCommand.Options.ShouldContain(o => o.Name == "remove-match-column");
        editCommand.Options.ShouldContain(o => o.Name == "exclude-from-update");
        editCommand.Options.ShouldContain(o => o.Name == "add-exclude");
        editCommand.Options.ShouldContain(o => o.Name == "remove-exclude");
        editCommand.Options.ShouldContain(o => o.Name == "dry-run");
    }

    #endregion

    #region PreScript Add Tests

    [Fact]
    public async Task PreScriptAddCommand_AddsScriptToPipeline()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}"); // File must exist for the command

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts = []
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act
            await rootCommand.InvokeAsync(["prescript", "add", "TestPipeline", "--script", "SELECT 1;"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.PreScripts.Count.ShouldBe(1);
            savedPipeline.PreScripts[0].Script.ShouldBe("SELECT 1;");
            savedPipeline.PreScripts[0].Connection.ShouldBe("lotfinder");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    [Fact]
    public async Task PreScriptAddCommand_InsertsAtSpecifiedIndex()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts =
                [
                    new ScriptElement { Script = "Script1" },
                    new ScriptElement { Script = "Script2" }
                ]
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act - insert at position 2 (between Script1 and Script2)
            await rootCommand.InvokeAsync(["prescript", "add", "TestPipeline", "--script", "NewScript", "--at-index", "2"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.PreScripts.Count.ShouldBe(3);
            savedPipeline.PreScripts[0].Script.ShouldBe("Script1");
            savedPipeline.PreScripts[1].Script.ShouldBe("NewScript");
            savedPipeline.PreScripts[2].Script.ShouldBe("Script2");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    #endregion

    #region PreScript Remove Tests

    [Fact]
    public async Task PreScriptRemoveCommand_RemovesScriptWithForce()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts =
                [
                    new ScriptElement { Script = "Script1" },
                    new ScriptElement { Script = "Script2" },
                    new ScriptElement { Script = "Script3" }
                ]
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act - remove script at index 2 (Script2)
            await rootCommand.InvokeAsync(["prescript", "remove", "TestPipeline", "2", "--force"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.PreScripts.Count.ShouldBe(2);
            savedPipeline.PreScripts[0].Script.ShouldBe("Script1");
            savedPipeline.PreScripts[1].Script.ShouldBe("Script3");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    [Fact]
    public async Task PreScriptRemoveCommand_DryRunDoesNotSave()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts = [new ScriptElement { Script = "Script1" }]
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act
            await rootCommand.InvokeAsync(["prescript", "remove", "TestPipeline", "1", "--dry-run"]);

            // Assert - SavePipelineAsync should not be called
            await _configFileService.DidNotReceive()
                .SavePipelineAsync(Arg.Any<string>(), Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>());
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    #endregion

    #region PreScript Move Tests

    [Fact]
    public async Task PreScriptMoveUpCommand_SwapsWithPreviousElement()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts =
                [
                    new ScriptElement { Script = "Script1" },
                    new ScriptElement { Script = "Script2" },
                    new ScriptElement { Script = "Script3" }
                ]
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act - move Script2 (index 2) up
            await rootCommand.InvokeAsync(["prescript", "move-up", "TestPipeline", "2"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.PreScripts[0].Script.ShouldBe("Script2");
            savedPipeline.PreScripts[1].Script.ShouldBe("Script1");
            savedPipeline.PreScripts[2].Script.ShouldBe("Script3");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    [Fact]
    public async Task PreScriptMoveDownCommand_SwapsWithNextElement()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts =
                [
                    new ScriptElement { Script = "Script1" },
                    new ScriptElement { Script = "Script2" },
                    new ScriptElement { Script = "Script3" }
                ]
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act - move Script2 (index 2) down
            await rootCommand.InvokeAsync(["prescript", "move-down", "TestPipeline", "2"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.PreScripts[0].Script.ShouldBe("Script1");
            savedPipeline.PreScripts[1].Script.ShouldBe("Script3");
            savedPipeline.PreScripts[2].Script.ShouldBe("Script2");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    #endregion

    #region Transform Add Tests

    [Fact]
    public async Task TransformAddCommand_AddsColumnDropTransform()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                Transforms = []
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var transform = PipelineElementCommands.CreateTransformCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { transform };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act
            await rootCommand.InvokeAsync(["transform", "add", "TestPipeline", "--type", "ColumnDrop", "--columns", "Col1,Col2,Col3"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.Transforms.Count.ShouldBe(1);
            savedPipeline.Transforms[0].TransformType.ShouldBe("ColumnDrop");
            savedPipeline.Transforms[0].Config.ShouldNotBeNull();
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    [Fact]
    public async Task TransformAddCommand_AddsJdeDateTransform()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                Transforms = []
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var transform = PipelineElementCommands.CreateTransformCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { transform };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act
            await rootCommand.InvokeAsync(["transform", "add", "TestPipeline",
                "--type", "JdeDate",
                "--date-column", "WADOCO",
                "--time-column", "WATIME",
                "--output-column", "UpdatedDT"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.Transforms.Count.ShouldBe(1);
            savedPipeline.Transforms[0].TransformType.ShouldBe("JdeDate");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    #endregion

    #region Source Edit Tests

    [Fact]
    public async Task SourceEditCommand_UpdatesConnection()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                Source = new SourceElement { Connection = "jde", Query = "SELECT 1" }
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var source = PipelineElementCommands.CreateSourceCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { source };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act
            await rootCommand.InvokeAsync(["source", "edit", "TestPipeline", "--connection", "cms"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.Source.Connection.ShouldBe("cms");
            savedPipeline.Source.Query.ShouldBe("SELECT 1"); // Unchanged
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    #endregion

    #region Destination Edit Tests

    [Fact]
    public async Task DestinationEditCommand_UpdatesMatchColumns()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                Destination = new DestinationElement
                {
                    Table = "dbo.TestTable",
                    MatchColumns = ["OldCol1", "OldCol2"]
                }
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var destination = PipelineElementCommands.CreateDestinationCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { destination };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act
            await rootCommand.InvokeAsync(["destination", "edit", "TestPipeline", "--match-columns", "NewCol1,NewCol2,NewCol3"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.Destination.MatchColumns.Count.ShouldBe(3);
            savedPipeline.Destination.MatchColumns.ShouldContain("NewCol1");
            savedPipeline.Destination.MatchColumns.ShouldContain("NewCol2");
            savedPipeline.Destination.MatchColumns.ShouldContain("NewCol3");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    [Fact]
    public async Task DestinationEditCommand_AddsMatchColumn()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        EtlPipelineConfig? savedPipeline = null;

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                Destination = new DestinationElement
                {
                    Table = "dbo.TestTable",
                    MatchColumns = ["Col1", "Col2"]
                }
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            _configFileService.SavePipelineAsync(pipelinePath, Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>())
                .Returns(ci =>
                {
                    savedPipeline = ci.ArgAt<EtlPipelineConfig>(1);
                    return Task.CompletedTask;
                });

            var destination = PipelineElementCommands.CreateDestinationCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { destination };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act
            await rootCommand.InvokeAsync(["destination", "edit", "TestPipeline", "--add-match-column", "Col3"]);

            // Assert
            savedPipeline.ShouldNotBeNull();
            savedPipeline.Destination.MatchColumns.Count.ShouldBe(3);
            savedPipeline.Destination.MatchColumns.ShouldContain("Col1");
            savedPipeline.Destination.MatchColumns.ShouldContain("Col2");
            savedPipeline.Destination.MatchColumns.ShouldContain("Col3");
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    #endregion

    #region Error Handling Tests

    [Fact]
    public async Task PreScriptRemoveCommand_InvalidIndex_ReturnsError()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts = [new ScriptElement { Script = "Script1" }]
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act - try to remove index 5 when only 1 exists
            await rootCommand.InvokeAsync(["prescript", "remove", "TestPipeline", "5", "--force"]);

            // Assert - SavePipelineAsync should not be called
            await _configFileService.DidNotReceive()
                .SavePipelineAsync(Arg.Any<string>(), Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>());
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    [Fact]
    public async Task TransformAddCommand_InvalidType_ReturnsError()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                Transforms = []
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            var transform = PipelineElementCommands.CreateTransformCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { transform };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act - try to add invalid transform type
            await rootCommand.InvokeAsync(["transform", "add", "TestPipeline", "--type", "InvalidType"]);

            // Assert - SavePipelineAsync should not be called
            await _configFileService.DidNotReceive()
                .SavePipelineAsync(Arg.Any<string>(), Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>());
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    [Fact]
    public async Task PreScriptMoveUpCommand_AtFirstPosition_DoesNotSave()
    {
        // Arrange
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        var pipelinesDir = Path.Combine(tempDir, "Pipelines");
        Directory.CreateDirectory(pipelinesDir);
        var pipelinePath = Path.Combine(pipelinesDir, "pipeline.TestPipeline.json");
        File.WriteAllText(pipelinePath, "{}");

        try
        {
            _autoDiscoveryService.FindConfigFolderAsync(Arg.Any<CancellationToken>())
                .Returns(Task.FromResult<string?>(tempDir));

            var pipeline = new EtlPipelineConfig
            {
                Name = "TestPipeline",
                PreScripts =
                [
                    new ScriptElement { Script = "Script1" },
                    new ScriptElement { Script = "Script2" }
                ]
            };

            _configFileService.LoadPipelineAsync(pipelinePath, Arg.Any<CancellationToken>())
                .Returns(Task.FromResult(pipeline));

            var prescript = PipelineElementCommands.CreatePreScriptCommand(
                _serviceProvider, _configPathOption, _verboseOption, _quietOption);
            var rootCommand = new RootCommand { prescript };
            rootCommand.AddGlobalOption(_configPathOption);
            rootCommand.AddGlobalOption(_verboseOption);
            rootCommand.AddGlobalOption(_quietOption);

            // Act - try to move first element up (should be no-op)
            await rootCommand.InvokeAsync(["prescript", "move-up", "TestPipeline", "1"]);

            // Assert - SavePipelineAsync should not be called (already at first position)
            await _configFileService.DidNotReceive()
                .SavePipelineAsync(Arg.Any<string>(), Arg.Any<EtlPipelineConfig>(), Arg.Any<CancellationToken>());
        }
        finally
        {
            Directory.Delete(tempDir, true);
        }
    }

    #endregion
}