TestResultStorageTool.cs

using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Xml.Linq;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

namespace TestResultStorageTool
{

    class TestResultStorageTool
    {
        static async Task<int> Main(string[] args)
        {
            Console.WriteLine("=== Test Results Reporter ===\n");

            // Build configuration
            var configuration = BuildConfiguration();

            // Parse arguments (supports both CI and local usage)
            var config = ParseArguments(args, configuration);
            if (config == null)
            {
                PrintTests.PrintUsage();
                return 1;
            }

            if (!File.Exists(config.XmlPath))
            {
                Console.WriteLine($"ERROR: Test results file not found: {config.XmlPath}");
                return 1;
            }

            //TODO implement as scoped transaction
            try
            {
                Console.WriteLine($"Source: {config.XmlPath}");
                Console.WriteLine($"Pipeline: {config.PipelineId}");
                Console.WriteLine($"Branch: {config.Branch}");
                Console.WriteLine($"Commit: {config.CommitSha}\n");

                var xml = XDocument.Load(config.XmlPath);
                Console.WriteLine("✓ XML parsed successfully");

                Console.WriteLine($"Connecting to database...");
                await using var conn = new SqlConnection(config.ConnectionString);
                await conn.OpenAsync();
                Console.WriteLine("✓ Database connected\n");

                await using var transaction = conn.BeginTransaction();

                try
                {
                    var stats = await ProcessTestAssembly(conn, transaction, config, xml);

                    await transaction.CommitAsync();

                    PrintTests.PrintSummary(stats);
                    Console.WriteLine("\n Test results successfully persisted to database\n");

                    return stats.FailedTests > 0 ? 1 : 0;
                }
                catch
                {
                    await transaction.RollbackAsync();
                    throw;
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"\nERROR: Database error");
                Console.WriteLine($"Message: {ex.Message}");
                Console.WriteLine($"Number: {ex.Number}");
                if (ex.Number == -1 || ex.Number == 53 || ex.Number == 258)
                {
                    Console.WriteLine("\nTroubleshooting:");
                    Console.WriteLine("- Check if SQL Server is running");
                    Console.WriteLine("- Verify the server address");
                }
                else if (ex.Number == 18456)
                {
                    Console.WriteLine("\nTroubleshooting:");
                    Console.WriteLine("- Check username and password");
                    Console.WriteLine("- Verify SQL Server authentication is enabled");
                    Console.WriteLine("- For Windows Authentication, use: Integrated Security=true");
                }
                else if (ex.Number == 2627 || ex.Number == 2601)
                {
                    Console.WriteLine("\nTroubleshooting:");
                    Console.WriteLine("- Duplicate entry detected");
                    Console.WriteLine("- This pipeline may have already been processed");
                }
                return 1;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"\nERROR: {ex.Message}");
                Console.WriteLine($"\nStack trace:\n{ex.StackTrace}");
                return 1;
            }
        }

        static IConfiguration BuildConfiguration()
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddUserSecrets<TestResultStorageTool>();

            return builder.Build();
        }

        static Config? ParseArguments(string[] args, IConfiguration configuration)
        {
            // Support both explicit arguments and environment variables
            if (args.Length >= 5)
            {
                // CI mode: all args provided
                return new Config
                {
                    XmlPath = args[0],
                    ConnectionString = args[1],
                    CommitSha = args[2],
                    Branch = args[3],
                    PipelineId = args[4],
                    BuildNumber = args.Length > 5 ? args[5] : null
                };
            }
            else if (args.Length >= 1)
            {
                // Local mode: XML path required, rest from environment or defaults
                var xmlPath = args[0];

                var connectionString = args.Length > 1 ? args[1] :
                    Environment.GetEnvironmentVariable("DB_CONNECTION_STRING") ??
                    configuration.GetConnectionString("TestResultsDb") ?? 
                    throw new InvalidOperationException(
                        "Connection string not found. Please provide it via:\n" +
                        "dotnet user secrets");

                var commitSha = args.Length > 2 ? args[2] :
                    Environment.GetEnvironmentVariable("CI_COMMIT_SHA") ??
                    MetaData.GetGitCommitSha() ??
                    "local-" + DateTime.Now.ToString("yyyyMMdd-HHmmss");

                var branch = args.Length > 3 ? args[3] :
                    Environment.GetEnvironmentVariable("CI_COMMIT_BRANCH") ??
                    MetaData.GetGitBranch() ??
                    "local";

                var pipelineId = args.Length > 4 ? args[4] :
                    Environment.GetEnvironmentVariable("CI_PIPELINE_ID") ??
                    "local-" + MetaData.GetFileHash(xmlPath);

                var buildNumber = args.Length > 5 ? args[5] : 
                    Environment.GetEnvironmentVariable("CI_BUILD_NUMBER");

                return new Config
                {
                    XmlPath = xmlPath,
                    ConnectionString = connectionString,
                    CommitSha = commitSha,
                    Branch = branch,
                    PipelineId = pipelineId,
                    BuildNumber = buildNumber
                };
            }

            return null;
        }

        static async Task<TestStats> ProcessTestAssembly(
            SqlConnection conn,
            SqlTransaction transaction,
            Config config,
            XDocument xml)
        {
            var stats = new TestStats();
            Console.WriteLine("Processing test assembly...");

            // Get the assemblies element
            var assembliesElement = xml.Root;

            //parse the assembly timestamp
            var timestampStr = assembliesElement?.Attribute("timestamp")?.Value;
            
            DateTime timestamp;
            if (string.IsNullOrEmpty(timestampStr))
            {
                timestamp = DateTime.Now;
            }
            else if (!DateTime.TryParse(timestampStr, System.Globalization.CultureInfo.InvariantCulture, 
                System.Globalization.DateTimeStyles.None, out timestamp))
            {
                // Fallback: try parsing with current culture
                if (!DateTime.TryParse(timestampStr, out timestamp))
                {
                    Console.WriteLine($"Warning: Could not parse timestamp '{timestampStr}', using current time");
                    timestamp = DateTime.Now;
                }
            }
            
            Console.WriteLine($"Assembly timestamp: {timestamp:yyyy-MM-dd HH:mm:ss}");

            // Process each assembly
            var assemblyElements = xml.Descendants("assembly");
            
            foreach (var assembly in assemblyElements)
            {
                // Extract assembly-level stats
                stats.TotalTests = int.Parse(assembly.Attribute("total")?.Value ?? "0");
                stats.PassedTests = int.Parse(assembly.Attribute("passed")?.Value ?? "0");
                stats.FailedTests = int.Parse(assembly.Attribute("failed")?.Value ?? "0");
                stats.SkippedTests = int.Parse(assembly.Attribute("skipped")?.Value ?? "0");
                stats.TotalDuration = double.Parse(assembly.Attribute("time")?.Value ?? "0");

                var runDate = timestamp.Date;
                var runTime = timestamp.TimeOfDay;

                // Insert TestAssembly
                int assemblyId = await InsertTestAssembly(
                    conn, transaction, config, timestamp, runDate, runTime, stats);

                // Process collections
                var collections = assembly.Descendants("collection");
                
                foreach (var collection in collections)
                {
                    await ProcessTestCollection(conn, transaction, assemblyId, collection);
                }
            }

            Console.WriteLine($"\nProcessed {stats.TotalTests} test(s)");
            return stats;
        }

        static async Task<int> InsertTestAssembly(
            SqlConnection conn,
            SqlTransaction transaction,
            Config config,
            DateTime timestamp,
            DateTime runDate,
            TimeSpan runTime,
            TestStats stats)
        {
            // Check if assembly already exists for this pipeline
            var checkCmd = new SqlCommand(
                @"SELECT Id FROM TestAssemblies 
                  WHERE PipelineId = @pipelineId AND CommitSHA = @commitSha",
                conn, transaction);
            
            checkCmd.Parameters.Add("@pipelineId", SqlDbType.NVarChar, 100).Value = config.PipelineId;
            checkCmd.Parameters.Add("@commitSha", SqlDbType.NVarChar, 100).Value = config.CommitSha;

            var existingId = await checkCmd.ExecuteScalarAsync();
            if (existingId != null)
            {
                Console.WriteLine($"Assembly already exists for pipeline {config.PipelineId}");
                return (int)existingId;
            }

            var insertCmd = new SqlCommand(
                @"INSERT INTO TestAssemblies 
                  (AssemblyTimestamp, RunDate, RunTime, TotalTests, PassedTests, FailedTests, 
                   SkippedTests, ErrorCount, TotalDuration, PipelineId, CommitSHA, Branch, BuildNumber)
                  OUTPUT INSERTED.Id
                  VALUES (@timestamp, @runDate, @runTime, @total, @passed, @failed, 
                          @skipped, 0, @duration, @pipelineId, @commitSha, @branch, @buildNumber)",
                conn, transaction);

            // NB inserts changed from addwithvalue (type agnostic, slower) to add (typesafe, effective, more maintainence)
            // insertCmd.Parameters.AddWithValue("@timestamp", timestamp);
            insertCmd.Parameters.Add("@timestamp", SqlDbType.DateTime2).Value = timestamp;
            insertCmd.Parameters.Add("@runDate", SqlDbType.Date).Value = runDate;
            insertCmd.Parameters.Add("@runTime", SqlDbType.Time).Value = runTime;
            insertCmd.Parameters.Add("@total", SqlDbType.Int).Value = stats.TotalTests;
            insertCmd.Parameters.Add("@passed", SqlDbType.Int).Value = stats.PassedTests;
            insertCmd.Parameters.Add("@failed", SqlDbType.Int).Value = stats.FailedTests;
            insertCmd.Parameters.Add("@skipped", SqlDbType.Int).Value = stats.SkippedTests;
            insertCmd.Parameters.Add("@duration", SqlDbType.Int).Value = stats.TotalDuration;
            insertCmd.Parameters.Add("@pipelineId", SqlDbType.NVarChar, 100).Value = config.PipelineId;
            insertCmd.Parameters.Add("@commitSha", SqlDbType.NVarChar, 100).Value = config.CommitSha;
            insertCmd.Parameters.Add("@branch", SqlDbType.NVarChar, 255).Value = config.Branch;
            insertCmd.Parameters.Add("@buildNumber", SqlDbType.NVarChar, 100).Value = config.BuildNumber ?? "n/a";

            var result = await insertCmd.ExecuteScalarAsync();
            
            Console.WriteLine($"Created test assembly record (ID: {result})");
            return result != null ? (int)result : 0;
        }

        static async Task ProcessTestCollection(
            SqlConnection conn,
            SqlTransaction transaction,
            int assemblyId,
            XElement collection)
        {
            var collectionName = collection.Attribute("name")?.Value ?? "Unknown Collection";
            
            // Calculate collection stats
            var tests = collection.Descendants("test").ToList();
            var totalTests = tests.Count;
            var passedTests = tests.Count(t => t.Attribute("result")?.Value == "Pass");
            var failedTests = tests.Count(t => t.Attribute("result")?.Value == "Fail");
            var skippedTests = tests.Count(t => t.Attribute("result")?.Value == "Skip");
            var duration = tests.Sum(t => double.Parse(t.Attribute("time")?.Value ?? "0"));

            // Insert collection
            var collectionId = await InsertTestCollection(
                conn, transaction, assemblyId, collectionName, 
                totalTests, passedTests, failedTests, skippedTests, duration);

            // Process individual tests
            foreach (var test in tests)
            {
                await ProcessTestCase(conn, transaction, collectionId, test);
            }
        }

        static async Task<int> InsertTestCollection(
            SqlConnection conn,
            SqlTransaction transaction,
            int assemblyId,
            string collectionName,
            int totalTests,
            int passedTests,
            int failedTests,
            int skippedTests,
            double duration)
        {
            var cmd = new SqlCommand(
                @"INSERT INTO TestCollections 
                  (TestAssemblyId, CollectionName, TotalTests, PassedTests, FailedTests, SkippedTests, Duration)
                  OUTPUT INSERTED.Id
                  VALUES (@assemblyId, @name, @total, @passed, @failed, @skipped, @duration)",
                conn, transaction);

            cmd.Parameters.Add("@assemblyId", SqlDbType.Int).Value = assemblyId;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 500).Value = collectionName;
            cmd.Parameters.Add("@total", SqlDbType.Int).Value = totalTests;
            cmd.Parameters.Add("@passed", SqlDbType.Int).Value = passedTests;
            cmd.Parameters.Add("@failed", SqlDbType.Int).Value = failedTests;
            cmd.Parameters.Add("@skipped", SqlDbType.Int).Value = skippedTests;
            cmd.Parameters.Add("@duration", SqlDbType.Float).Value = duration;

            var result = await cmd.ExecuteScalarAsync();
            return result != null ? (int)result : 0;
        }

        static async Task ProcessTestCase(
            SqlConnection conn,
            SqlTransaction transaction,
            int collectionId,
            XElement test)
        {
            var className = test.Attribute("type")?.Value ?? "Unknown";
            var methodName = test.Attribute("method")?.Value ?? "Unknown";
            var result = test.Attribute("result")?.Value ?? "Skip";
            var duration = double.Parse(test.Attribute("time")?.Value ?? "0");

            // Insert test case
            var testId = await InsertTestCase(
                conn, transaction, collectionId, className, methodName, result, duration);

            // await InsertTestCaseParameters(conn, transaction, testId, methodParams);

            // Handle failure details
            var failure = test.Element("failure");
            if (failure != null)
            {
                var errorMessage = failure.Element("message")?.Value;
                var stackTrace = failure.Element("stack-trace")?.Value;
                await InsertTestFailure(conn, transaction, testId, errorMessage, stackTrace);
            }

            // Handle skip reasons
            var reason = test.Element("reason");
            if (reason != null && result == "Skip")
            {
                var skipReason = reason?.Value;
                await InsertTestSkipReason(conn, transaction, testId, skipReason);
            }

            // Handle traits
            var traits = test.Element("traits");
            if (traits != null)
            {
                foreach (var trait in traits.Elements("trait"))
                {
                    var traitName = trait.Attribute("name")?.Value ?? "Unknown";
                    var traitValue = trait.Attribute("value")?.Value ?? "Unknown";
                    
                    if (traitName != null && traitValue != null)
                    {
                        await InsertTestTrait(conn, transaction, testId, traitName, traitValue);
                    }
                }
            }
        }

        static async Task<long> InsertTestCase(
            SqlConnection conn,
            SqlTransaction transaction,
            int collectionId,
            string className,
            string methodName,
            string result,
            double duration)
        {
            var cmd = new SqlCommand(
                @"INSERT INTO Testcases 
                  (TestCollectionId, TestClassName, TestMethod, Result, Duration)
                  OUTPUT INSERTED.Id
                  VALUES (@collectionId, @className, @method, @result, @duration)",
                conn, transaction);

            cmd.Parameters.Add("@collectionId", SqlDbType.Int).Value = collectionId;
            cmd.Parameters.Add("@className", SqlDbType.NVarChar, 500).Value = className;
            cmd.Parameters.Add("@method", SqlDbType.NVarChar, 500).Value = methodName;
            cmd.Parameters.Add("@result", SqlDbType.NVarChar, 20).Value = result;
            cmd.Parameters.Add("@duration", SqlDbType.Float).Value = duration;

            var result_id = await cmd.ExecuteScalarAsync();
            return result_id != null ? (long)result_id : 0;
        }

        static async Task InsertTestFailure(
            SqlConnection conn,
            SqlTransaction transaction,
            long testId,
            string? errorMessage,
            string? stackTrace)
        {
            var cmd = new SqlCommand(
                @"INSERT INTO TestFailures (TestId, ErrorMessage, StackTrace)
                  VALUES (@testId, @errorMessage, @stackTrace)",
                conn, transaction);

            cmd.Parameters.Add("@testId", SqlDbType.BigInt).Value = testId;
            cmd.Parameters.Add("@errorMessage", SqlDbType.NVarChar, -1).Value = errorMessage ?? "n/a"; // -1 represents nvarchar(max)
            cmd.Parameters.Add("@stackTrace", SqlDbType.NVarChar, -1).Value = stackTrace ?? "n/a";

            await cmd.ExecuteNonQueryAsync();
        }

        static async Task InsertTestSkipReason(
            SqlConnection conn,
            SqlTransaction transaction,
            long testId,
            string? reason)
        {
            var cmd = new SqlCommand(
                @"INSERT INTO TestSkipReasons (TestId, Reason)
                  VALUES (@testId, @reason)",
                conn, transaction);

            cmd.Parameters.Add("@testId", SqlDbType.BigInt).Value = testId;
            cmd.Parameters.Add("@reason", SqlDbType.NVarChar, 500).Value = reason ?? "n/a";

            await cmd.ExecuteNonQueryAsync();
        }

        static async Task InsertTestTrait(
            SqlConnection conn,
            SqlTransaction transaction,
            long testId,
            string traitName,
            string traitValue)
        {
            var cmd = new SqlCommand(
                @"INSERT INTO TestTraits (TestId, TraitName, TraitValue)
                  VALUES (@testId, @name, @value)",
                conn, transaction);

            cmd.Parameters.Add("@testId", SqlDbType.BigInt).Value = testId;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 200).Value = traitName;
            cmd.Parameters.Add("@value", SqlDbType.NVarChar, 500).Value = traitValue;

            await cmd.ExecuteNonQueryAsync();
        }

    }
    
}