junction/tests/Junction.Tests/Unit/SqliteMachineRepositoryTests.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Junction.Domain;
using Junction.Domain.Models;
using Junction.Persistence;
using Xunit;

namespace Junction.Tests.Unit
{
    /// <summary>
    /// Integration-style tests for <see cref="SqliteMachineRepository"/> against a REAL temp-file
    /// SQLite database. Proves the full round-trip (config dict, poll interval, snapshots, items)
    /// and the only-latest replace policy on the Linux net8.0 host.
    /// </summary>
    public sealed class SqliteMachineRepositoryTests : IDisposable
    {
        private readonly string _dbPath;
        private readonly SqliteConnectionFactory _factory;
        private readonly SqliteMachineRepository _repo;

        public SqliteMachineRepositoryTests()
        {
            _dbPath = Path.Combine(Path.GetTempPath(), "junction_repo_test_" + Guid.NewGuid().ToString("N") + ".db");
            var connectionString = "Data Source=" + _dbPath;
            _factory = new SqliteConnectionFactory(connectionString);

            Result schema = SqliteSchema.EnsureCreated(_factory);
            Assert.True(schema.IsSuccess, Describe(schema));

            _repo = new SqliteMachineRepository(_factory);
        }

        public void Dispose()
        {
            try
            {
                if (File.Exists(_dbPath))
                {
                    File.Delete(_dbPath);
                }
            }
            catch
            {
                // best-effort cleanup
            }
        }

        [Fact]
        public async Task Upsert_NewMachine_GetById_RoundTripsAllFields()
        {
            var id = Guid.NewGuid();
            var config = new Dictionary<string, string>
            {
                ["url"] = "http://mill:5000",
                ["device"] = "M1",
            };
            var machine = new Machine(id, "Mill 01", "mtconnect", config, TimeSpan.FromSeconds(5));

            Result upsert = await _repo.UpsertAsync(machine, CancellationToken.None);
            Assert.True(upsert.IsSuccess, Describe(upsert));

            Result<Machine> got = await _repo.GetByIdAsync(id, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));

            var loaded = got.Value;
            Assert.Equal(id, loaded.Id);
            Assert.Equal("Mill 01", loaded.Name);
            Assert.Equal("mtconnect", loaded.ProtocolId);
            Assert.Equal(TimeSpan.FromSeconds(5), loaded.PollInterval);
            Assert.Equal(2, loaded.ConnectionConfig.Count);
            Assert.Equal("http://mill:5000", loaded.ConnectionConfig["url"]);
            Assert.Equal("M1", loaded.ConnectionConfig["device"]);
        }

        [Fact]
        public async Task Upsert_MonitoredItemIds_GetById_RoundTripsSelection()
        {
            var id = Guid.NewGuid();
            var selection = new[] { "x1_pos", "c1_load", "dev1_avail" };
            var machine = new Machine(
                id, "Mill 02", "mtconnect", null, TimeSpan.FromSeconds(3), selection);

            Result upsert = await _repo.UpsertAsync(machine, CancellationToken.None);
            Assert.True(upsert.IsSuccess, Describe(upsert));

            Result<Machine> got = await _repo.GetByIdAsync(id, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));

            Assert.Equal(3, got.Value.MonitoredItemIds.Count);
            Assert.Equal(selection.OrderBy(x => x), got.Value.MonitoredItemIds.OrderBy(x => x));
        }

        [Fact]
        public async Task Upsert_DefaultMachine_MonitoredItemIds_IsEmpty_NotNull()
        {
            var id = Guid.NewGuid();
            // Backward-compat ctor (no selection) => empty opt-in set, round-trips as empty.
            var machine = new Machine(id, "Mill 03", "mtconnect", null, TimeSpan.FromSeconds(1));
            await _repo.UpsertAsync(machine, CancellationToken.None);

            Result<Machine> got = await _repo.GetByIdAsync(id, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));
            Assert.NotNull(got.Value.MonitoredItemIds);
            Assert.Empty(got.Value.MonitoredItemIds);
        }

        [Fact]
        public async Task EnsureCreated_OnPreExistingMachinesTable_AddsMonitoredItemIdsColumn_KeepsRows()
        {
            // Simulate a DB created by the OLD schema (no MonitoredItemIdsJson column).
            var oldDbPath = Path.Combine(
                Path.GetTempPath(), "junction_migration_test_" + Guid.NewGuid().ToString("N") + ".db");
            var connectionString = "Data Source=" + oldDbPath;
            var factory = new SqliteConnectionFactory(connectionString);

            try
            {
                var rowId = Guid.NewGuid().ToString("D");
                using (var conn = factory.CreateOpenConnection())
                {
                    Exec(conn,
                        @"CREATE TABLE machines (
                            Id                    TEXT    NOT NULL PRIMARY KEY,
                            Name                  TEXT    NOT NULL,
                            ProtocolId            TEXT    NOT NULL,
                            PollIntervalTicks     INTEGER NOT NULL,
                            ConnectionConfigJson  TEXT    NOT NULL
                        );");
                    Exec(conn,
                        "INSERT INTO machines (Id, Name, ProtocolId, PollIntervalTicks, ConnectionConfigJson) " +
                        "VALUES ('" + rowId + "', 'Legacy', 'mtconnect', 10000000, '{}');");

                    Assert.False(HasColumn(conn, "machines", "MonitoredItemIdsJson"));
                }

                // Migration path: EnsureCreated must ALTER-add the missing column, idempotently.
                Result schema = SqliteSchema.EnsureCreated(factory);
                Assert.True(schema.IsSuccess, Describe(schema));

                using (var conn = factory.CreateOpenConnection())
                {
                    Assert.True(HasColumn(conn, "machines", "MonitoredItemIdsJson"));
                }

                // Existing row survived and reads back (null selection => empty set).
                var repo = new SqliteMachineRepository(factory);
                Result<Machine> got = await repo.GetByIdAsync(Guid.Parse(rowId), CancellationToken.None);
                Assert.True(got.IsSuccess, Describe(got));
                Assert.Equal("Legacy", got.Value.Name);
                Assert.Empty(got.Value.MonitoredItemIds);

                // Idempotent: running again does not fail.
                Assert.True(SqliteSchema.EnsureCreated(factory).IsSuccess);
            }
            finally
            {
                try { if (File.Exists(oldDbPath)) File.Delete(oldDbPath); } catch { /* best-effort */ }
            }
        }

        private static void Exec(System.Data.IDbConnection conn, string sql)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }

        private static bool HasColumn(System.Data.IDbConnection conn, string table, string column)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "PRAGMA table_info(" + table + ");";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (string.Equals(reader.GetValue(1)?.ToString(), column, StringComparison.OrdinalIgnoreCase))
                        {
                            return true;
                        }
                    }
                }
            }

            return false;
        }

        [Fact]
        public async Task Upsert_ExistingId_Updates_GetAllCountStable()
        {
            var id = Guid.NewGuid();
            var original = new Machine(id, "Old Name", "mtconnect", null, TimeSpan.FromSeconds(2));
            await _repo.UpsertAsync(original, CancellationToken.None);

            var updated = new Machine(
                id,
                "New Name",
                "opcua",
                new Dictionary<string, string> { ["k"] = "v" },
                TimeSpan.FromSeconds(10));
            Result upsert = await _repo.UpsertAsync(updated, CancellationToken.None);
            Assert.True(upsert.IsSuccess, Describe(upsert));

            Result<IReadOnlyList<Machine>> all = await _repo.GetAllAsync(CancellationToken.None);
            Assert.True(all.IsSuccess, Describe(all));
            Assert.Single(all.Value);

            Result<Machine> got = await _repo.GetByIdAsync(id, CancellationToken.None);
            Assert.Equal("New Name", got.Value.Name);
            Assert.Equal("opcua", got.Value.ProtocolId);
            Assert.Equal(TimeSpan.FromSeconds(10), got.Value.PollInterval);
            Assert.Equal("v", got.Value.ConnectionConfig["k"]);
        }

        [Fact]
        public async Task GetById_Missing_Fails_NotFound()
        {
            Result<Machine> got = await _repo.GetByIdAsync(Guid.NewGuid(), CancellationToken.None);

            Assert.False(got.IsSuccess);
            Assert.False(got.WasCancelled);
            Assert.NotEmpty(got.Errors);
            Assert.Equal("not_found", got.Errors[0].Code);
        }

        [Fact]
        public async Task GetAll_Empty_ReturnsEmptyList()
        {
            Result<IReadOnlyList<Machine>> all = await _repo.GetAllAsync(CancellationToken.None);

            Assert.True(all.IsSuccess, Describe(all));
            Assert.Empty(all.Value);
        }

        [Fact]
        public async Task SaveSnapshot_GetLatest_RoundTripsItems()
        {
            var machineId = Guid.NewGuid();
            var capturedAt = new DateTimeOffset(2026, 7, 21, 10, 30, 15, TimeSpan.FromHours(2));
            var itemTs = new DateTimeOffset(2026, 7, 21, 10, 30, 14, TimeSpan.FromHours(2));

            var items = new List<DataItem>
            {
                new DataItem("d1", "Spindle Speed", "1200", "Sample", itemTs),
                new DataItem("d2", "Program", "O1000", "Event", itemTs),
            };
            var snapshot = new MachineSnapshot(machineId, capturedAt, ConnectionState.Connected, items);

            Result save = await _repo.SaveSnapshotAsync(snapshot, CancellationToken.None);
            Assert.True(save.IsSuccess, Describe(save));

            Result<MachineSnapshot> got = await _repo.GetLatestSnapshotAsync(machineId, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));

            var loaded = got.Value;
            Assert.Equal(machineId, loaded.MachineId);
            Assert.Equal(capturedAt, loaded.CapturedAt);
            Assert.Equal(ConnectionState.Connected, loaded.ConnectionState);
            Assert.Equal(2, loaded.Items.Count);

            var d1 = loaded.Items.Single(i => i.Id == "d1");
            Assert.Equal("Spindle Speed", d1.Name);
            Assert.Equal("1200", d1.Value);
            Assert.Equal("Sample", d1.Category);
            Assert.Equal(itemTs, d1.Timestamp);
        }

        [Fact]
        public async Task SaveSnapshot_Twice_ReplacesItems_OnlyLatest()
        {
            var machineId = Guid.NewGuid();
            var ts = DateTimeOffset.UtcNow;

            var first = new MachineSnapshot(machineId, ts, ConnectionState.Connected, new List<DataItem>
            {
                new DataItem("d1", "A", "1", "Sample", ts),
                new DataItem("d2", "B", "2", "Sample", ts),
                new DataItem("d3", "C", "3", "Sample", ts),
            });
            await _repo.SaveSnapshotAsync(first, CancellationToken.None);

            var second = new MachineSnapshot(machineId, ts.AddSeconds(1), ConnectionState.Disconnected, new List<DataItem>
            {
                new DataItem("d1", "A", "99", "Sample", ts.AddSeconds(1)),
            });
            Result save = await _repo.SaveSnapshotAsync(second, CancellationToken.None);
            Assert.True(save.IsSuccess, Describe(save));

            Result<MachineSnapshot> got = await _repo.GetLatestSnapshotAsync(machineId, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));

            var loaded = got.Value;
            Assert.Single(loaded.Items);
            Assert.Equal("d1", loaded.Items[0].Id);
            Assert.Equal("99", loaded.Items[0].Value);
            Assert.Equal(ConnectionState.Disconnected, loaded.ConnectionState);
        }

        [Fact]
        public async Task GetLatestSnapshot_None_Fails_NotFound()
        {
            Result<MachineSnapshot> got = await _repo.GetLatestSnapshotAsync(Guid.NewGuid(), CancellationToken.None);

            Assert.False(got.IsSuccess);
            Assert.NotEmpty(got.Errors);
            Assert.Equal("not_found", got.Errors[0].Code);
        }

        [Fact]
        public async Task Delete_RemovesMachine_AndItsSnapshot()
        {
            var id = Guid.NewGuid();
            await _repo.UpsertAsync(new Machine(id, "M", "mtconnect", null, TimeSpan.FromSeconds(1)), CancellationToken.None);
            await _repo.SaveSnapshotAsync(
                new MachineSnapshot(id, DateTimeOffset.UtcNow, ConnectionState.Connected, new List<DataItem>
                {
                    new DataItem("d1", "A", "1", "Sample", DateTimeOffset.UtcNow),
                }),
                CancellationToken.None);

            Result delete = await _repo.DeleteAsync(id, CancellationToken.None);
            Assert.True(delete.IsSuccess, Describe(delete));

            Result<Machine> machine = await _repo.GetByIdAsync(id, CancellationToken.None);
            Assert.False(machine.IsSuccess);
            Assert.Equal("not_found", machine.Errors[0].Code);

            Result<MachineSnapshot> snapshot = await _repo.GetLatestSnapshotAsync(id, CancellationToken.None);
            Assert.False(snapshot.IsSuccess);
            Assert.Equal("not_found", snapshot.Errors[0].Code);
        }

        [Fact]
        public async Task Upsert_Cancelled_ReturnsCancelled()
        {
            using var cts = new CancellationTokenSource();
            cts.Cancel();

            Result result = await _repo.UpsertAsync(
                new Machine(Guid.NewGuid(), "M", "mtconnect", null, TimeSpan.FromSeconds(1)),
                cts.Token);

            Assert.True(result.WasCancelled);
            Assert.False(result.IsSuccess);
        }

        // -- history (append-only time series) ----------------------------------------------

        [Fact]
        public async Task AppendHistory_ThenGetHistory_ReturnsAppendedPoints_Ascending()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);

            // Two snapshots of the same item at different times, appended in reverse order.
            await _repo.AppendHistoryAsync(
                Snap(machineId, t0.AddSeconds(10), new DataItem("d1", "Speed", "20", "Sample", t0.AddSeconds(10))),
                CancellationToken.None);
            Result append = await _repo.AppendHistoryAsync(
                Snap(machineId, t0, new DataItem("d1", "Speed", "10", "Sample", t0)),
                CancellationToken.None);
            Assert.True(append.IsSuccess, Describe(append));

            Result<IReadOnlyList<HistoryPoint>> got =
                await _repo.GetHistoryAsync(machineId, "d1", t0.AddSeconds(-1), 100, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));

            // Append is additive (accumulates, not overwrite) and result is ascending by timestamp.
            Assert.Equal(2, got.Value.Count);
            Assert.Equal("10", got.Value[0].Value);
            Assert.Equal(t0, got.Value[0].Timestamp);
            Assert.Equal("20", got.Value[1].Value);
            Assert.Equal(t0.AddSeconds(10), got.Value[1].Timestamp);
            Assert.Equal("d1", got.Value[0].ItemId);
        }

        [Fact]
        public async Task GetHistory_SinceFilter_ExcludesOlderRows()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);

            for (int i = 0; i < 5; i++)
            {
                await _repo.AppendHistoryAsync(
                    Snap(machineId, t0.AddMinutes(i), new DataItem("d1", "Speed", i.ToString(), "Sample", t0.AddMinutes(i))),
                    CancellationToken.None);
            }

            // since = t0 + 3min => only minute 3 and 4 remain.
            Result<IReadOnlyList<HistoryPoint>> got =
                await _repo.GetHistoryAsync(machineId, "d1", t0.AddMinutes(3), 100, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));

            Assert.Equal(2, got.Value.Count);
            Assert.Equal("3", got.Value[0].Value);
            Assert.Equal("4", got.Value[1].Value);
        }

        [Fact]
        public async Task GetHistory_MaxPoints_KeepsMostRecentN_Ascending()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);

            for (int i = 0; i < 5; i++)
            {
                await _repo.AppendHistoryAsync(
                    Snap(machineId, t0.AddMinutes(i), new DataItem("d1", "Speed", i.ToString(), "Sample", t0.AddMinutes(i))),
                    CancellationToken.None);
            }

            // 5 rows exist, ask for at most 2 => most-recent two (3,4), returned ascending.
            Result<IReadOnlyList<HistoryPoint>> got =
                await _repo.GetHistoryAsync(machineId, "d1", t0.AddMinutes(-1), 2, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));

            Assert.Equal(2, got.Value.Count);
            Assert.Equal("3", got.Value[0].Value);
            Assert.Equal("4", got.Value[1].Value);
        }

        [Fact]
        public async Task GetHistory_UnknownItemOrMachine_ReturnsEmptyOk()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);
            await _repo.AppendHistoryAsync(
                Snap(machineId, t0, new DataItem("d1", "Speed", "10", "Sample", t0)),
                CancellationToken.None);

            Result<IReadOnlyList<HistoryPoint>> unknownItem =
                await _repo.GetHistoryAsync(machineId, "nope", t0.AddSeconds(-1), 100, CancellationToken.None);
            Assert.True(unknownItem.IsSuccess, Describe(unknownItem));
            Assert.Empty(unknownItem.Value);

            Result<IReadOnlyList<HistoryPoint>> unknownMachine =
                await _repo.GetHistoryAsync(Guid.NewGuid(), "d1", t0.AddSeconds(-1), 100, CancellationToken.None);
            Assert.True(unknownMachine.IsSuccess, Describe(unknownMachine));
            Assert.Empty(unknownMachine.Value);
        }

        [Fact]
        public async Task GetHistory_NonPositiveMaxPoints_ReturnsEmptyOk()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);
            await _repo.AppendHistoryAsync(
                Snap(machineId, t0, new DataItem("d1", "Speed", "10", "Sample", t0)),
                CancellationToken.None);

            Result<IReadOnlyList<HistoryPoint>> got =
                await _repo.GetHistoryAsync(machineId, "d1", t0.AddSeconds(-1), 0, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));
            Assert.Empty(got.Value);
        }

        [Fact]
        public async Task AppendHistory_EmptySnapshot_IsNoOpSuccess()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);

            Result append = await _repo.AppendHistoryAsync(
                new MachineSnapshot(machineId, t0, ConnectionState.Connected, null),
                CancellationToken.None);
            Assert.True(append.IsSuccess, Describe(append));

            Result<IReadOnlyList<HistoryPoint>> got =
                await _repo.GetHistoryAsync(machineId, "d1", t0.AddSeconds(-1), 100, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));
            Assert.Empty(got.Value);
        }

        [Fact]
        public async Task PruneHistory_RemovesOlderThanCutoff_KeepsNewer()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);

            for (int i = 0; i < 5; i++)
            {
                await _repo.AppendHistoryAsync(
                    Snap(machineId, t0.AddMinutes(i), new DataItem("d1", "Speed", i.ToString(), "Sample", t0.AddMinutes(i))),
                    CancellationToken.None);
            }

            // Cutoff at minute 3: rows < t0+3min (minutes 0,1,2) removed, 3 and 4 kept.
            Result prune = await _repo.PruneHistoryAsync(t0.AddMinutes(3), CancellationToken.None);
            Assert.True(prune.IsSuccess, Describe(prune));

            Result<IReadOnlyList<HistoryPoint>> got =
                await _repo.GetHistoryAsync(machineId, "d1", DateTimeOffset.MinValue, 100, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));
            Assert.Equal(2, got.Value.Count);
            Assert.Equal("3", got.Value[0].Value);
            Assert.Equal("4", got.Value[1].Value);

            // Idempotent: pruning again with a cutoff below everything removed changes nothing.
            Result prune2 = await _repo.PruneHistoryAsync(t0.AddMinutes(3), CancellationToken.None);
            Assert.True(prune2.IsSuccess, Describe(prune2));
        }

        [Fact]
        public async Task Delete_AlsoClearsMachineHistory()
        {
            var machineId = Guid.NewGuid();
            var t0 = new DateTimeOffset(2026, 7, 22, 8, 0, 0, TimeSpan.Zero);

            await _repo.UpsertAsync(
                new Machine(machineId, "M", "mtconnect", null, TimeSpan.FromSeconds(1)), CancellationToken.None);
            await _repo.AppendHistoryAsync(
                Snap(machineId, t0, new DataItem("d1", "Speed", "10", "Sample", t0)),
                CancellationToken.None);

            Result delete = await _repo.DeleteAsync(machineId, CancellationToken.None);
            Assert.True(delete.IsSuccess, Describe(delete));

            Result<IReadOnlyList<HistoryPoint>> got =
                await _repo.GetHistoryAsync(machineId, "d1", DateTimeOffset.MinValue, 100, CancellationToken.None);
            Assert.True(got.IsSuccess, Describe(got));
            Assert.Empty(got.Value);
        }

        private static MachineSnapshot Snap(Guid machineId, DateTimeOffset capturedAt, params DataItem[] items) =>
            new MachineSnapshot(machineId, capturedAt, ConnectionState.Connected, items);

        private static string Describe<T>(Result<T> result) =>
            result.IsSuccess ? "" : string.Join("; ", result.Errors.Select(e => e.ToString()));

        private static string Describe(Result result) =>
            result.IsSuccess ? "" : string.Join("; ", result.Errors.Select(e => e.ToString()));
    }
}