using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using w4c_workflows.Data;
using w4c_workflows.Models;
using w4c_workflows.Services.Credentials;
using w4c_workflows.Services.Nodes;
using w4c_workflows.Services.Nodes.Executors;
using w4c_workflows.Services.Nodes.Interpolation;
using Xunit;

namespace w4c_workflows.Tests;

/// <summary>
/// Covers the self-hosted Lite-mode (SQLite) schema path. The node columns, the
/// edge table, the credential vault table and the sub-workflow run columns have
/// no EF migration (project house rule), so <see cref="WorkflowsSchema"/> adds
/// them idempotently — with a <c>pragma_table_info</c> probe because SQLite has
/// no <c>ADD COLUMN IF NOT EXISTS</c>. Without this step Lite-mode node
/// workflows fail with "no such column".
/// </summary>
public class LiteModeSchemaTests
{
    private const string Tenant = "lite-tenant";

    private const string BranchYaml = """
        name: branch-demo
        tasks:
          - id: prepare
            node: { type: core.set }
            parameters:
              mode: manual
              fields:
                - name: status
                  value: ready
            next: decide
          - id: decide
            node: { type: core.if }
            parameters:
              left: "={{ $json.status }}"
              operator: equals
              right: ready
            edges:
              - { output: 0, to: ok }
              - { output: 1, to: nope }
          - id: ok
            node: { type: core.noop }
          - id: nope
            node: { type: core.noop }
        """;

    // ------------------------------------------------------------------ fixtures

    private static async Task<(SqliteConnection Connection, WorkflowsDbContext Db)> OpenLiteAsync()
    {
        var connection = new SqliteConnection("DataSource=:memory:");
        await connection.OpenAsync();

        var db = NewContext(connection);
        await WorkflowsSchema.ApplyAsync(db, liteMode: true);
        return (connection, db);
    }

    private static WorkflowsDbContext NewContext(SqliteConnection connection)
        => new(new DbContextOptionsBuilder<WorkflowsDbContext>()
            .UseSqlite(connection)
            .Options);

    private static async Task<HashSet<string>> ColumnNamesAsync(WorkflowsDbContext db, string table)
    {
        var names = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        var connection = db.Database.GetDbConnection();
        var closeAfter = connection.State != System.Data.ConnectionState.Open;
        if (closeAfter)
            await connection.OpenAsync();

        try
        {
            await using var command = connection.CreateCommand();
            command.CommandText = "SELECT name FROM pragma_table_info(@table)";
            AddParameter(command, "@table", table);
            await using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
                names.Add(reader.GetString(0));
        }
        finally
        {
            if (closeAfter)
                await connection.CloseAsync();
        }

        return names;
    }

    private static async Task<HashSet<string>> TableNamesAsync(WorkflowsDbContext db)
    {
        var names = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        var connection = db.Database.GetDbConnection();
        var closeAfter = connection.State != System.Data.ConnectionState.Open;
        if (closeAfter)
            await connection.OpenAsync();

        try
        {
            await using var command = connection.CreateCommand();
            command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'";
            await using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
                names.Add(reader.GetString(0));
        }
        finally
        {
            if (closeAfter)
                await connection.CloseAsync();
        }

        return names;
    }

    private static void AddParameter(System.Data.Common.DbCommand command, string name, object value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        command.Parameters.Add(parameter);
    }

    private static NodeWorkflowRunner NewRunner(NodeBlueprintCatalog catalog)
    {
        var executors = new List<INodeExecutor>
        {
            new NoOpNodeExecutor(),
            new SetNodeExecutor(),
            new IfNodeExecutor(),
        };
        var graphRunner = new NodeGraphRunner(new NodeExecutorRegistry(executors), new NodeParameterInterpolator());
        var vault = new CredentialVault(new ReversibleTestCipher(), new CredentialTypeCatalog());
        return new NodeWorkflowRunner(catalog, graphRunner, vault);
    }

    // -------------------------------------------------------------------- tests

    [Fact]
    public async Task Lite_schema_has_the_node_and_subworkflow_columns()
    {
        var (connection, db) = await OpenLiteAsync();
        await using var _connection = connection;
        await using var _db = db;

        var tasks = await ColumnNamesAsync(db, "Tasks");
        Assert.Subset(tasks, new HashSet<string>
        {
            "ArchivedAt", "Server", "NodeType", "NodeVersion", "ParametersJson",
            "CredentialsJson", "RetryJson", "ContinueOnFail", "RunMode",
        });

        var runs = await ColumnNamesAsync(db, "WorkflowRuns");
        Assert.Subset(runs, new HashSet<string> { "ParentRunId", "ParentTaskId", "Depth" });

        var workflows = await ColumnNamesAsync(db, "Workflows");
        Assert.Contains("Repo", workflows);
    }

    [Fact]
    public async Task Lite_schema_has_the_edge_credential_and_repo_tables()
    {
        var (connection, db) = await OpenLiteAsync();
        await using var _connection = connection;
        await using var _db = db;

        var tables = await TableNamesAsync(db);
        Assert.Subset(tables, new HashSet<string>
        {
            "Tasks", "Workflows", "WorkflowRuns",
            "WorkflowTaskEdges", "Credentials", "WorkflowRepos", "WorkflowRuntimes",
        });
    }

    [Fact]
    public async Task Lite_schema_is_idempotent()
    {
        var (connection, db) = await OpenLiteAsync();
        await using var _connection = connection;
        await using var _db = db;

        // A second apply on the already-created database must not throw (the column
        // probe + IF NOT EXISTS tables make it a no-op).
        await WorkflowsSchema.ApplyAsync(db, liteMode: true);

        var tasks = await ColumnNamesAsync(db, "Tasks");
        Assert.Contains("NodeType", tasks);
    }

    [Fact]
    public async Task Lite_schema_materializes_edges_credentials_and_subworkflow_runs()
    {
        var (connection, db) = await OpenLiteAsync();
        await using var _connection = connection;
        await using var _db = db;

        var now = DateTime.UtcNow;
        var workflowId = Guid.NewGuid();
        var fromId = Guid.NewGuid();
        var toId = Guid.NewGuid();

        db.Workflows.Add(new Workflow
        {
            Id = workflowId,
            TenantId = Tenant,
            Name = "lite",
            Path = "workflows/lite.yaml",
            Status = WorkflowStatus.Compiled,
            Mode = WorkflowMode.Function,
            Target = "default",
            CreatedAt = now,
            UpdatedAt = now,
        });
        db.Tasks.Add(new WorkflowTask
        {
            Id = fromId,
            WorkflowId = workflowId,
            Key = "a",
            Language = "node",
            Mode = WorkflowMode.Function,
            NodeType = "core.noop",
            NodeVersion = 1,
            ParametersJson = "{}",
            ContinueOnFail = true,
            RunMode = "eachItem",
        });
        db.Tasks.Add(new WorkflowTask
        {
            Id = toId,
            WorkflowId = workflowId,
            Key = "b",
            Language = "node",
            Mode = WorkflowMode.Function,
            NodeType = "core.noop",
        });
        db.TaskEdges.Add(new WorkflowTaskEdge
        {
            Id = Guid.NewGuid(),
            WorkflowId = workflowId,
            FromTaskId = fromId,
            FromOutput = 0,
            ToTaskId = toId,
            ToInput = 0,
            IsLoopBack = false,
        });
        db.Credentials.Add(new Credential
        {
            Id = Guid.NewGuid(),
            TenantId = Tenant,
            Name = "my-api",
            Type = "httpHeaderAuth",
            EncryptedData = "ciphertext",
            CreatedAt = now,
            UpdatedAt = now,
        });
        db.WorkflowRepos.Add(new TenantWorkflowRepo { TenantId = Tenant, RepoName = "workflows" });
        db.WorkflowRuns.Add(new WorkflowRun
        {
            Id = Guid.NewGuid(),
            WorkflowId = workflowId,
            TenantId = Tenant,
            Status = RunStatus.Running,
            ParentRunId = Guid.NewGuid(),
            ParentTaskId = fromId,
            Depth = 2,
            StartedAt = now,
        });
        await db.SaveChangesAsync();

        // Read back through a fresh context over the same connection: this selects
        // every mapped column, so a missing node/edge/credential column fails here.
        await using var verify = NewContext(connection);
        var edge = await verify.TaskEdges.SingleAsync();
        Assert.Equal(fromId, edge.FromTaskId);
        Assert.Equal(toId, edge.ToTaskId);

        var credential = await verify.Credentials.SingleAsync();
        Assert.Equal("ciphertext", credential.EncryptedData);

        var run = await verify.WorkflowRuns.SingleAsync();
        Assert.Equal(2, run.Depth);
        Assert.Equal(fromId, run.ParentTaskId);
        Assert.NotNull(run.ParentRunId);

        var task = await verify.Tasks.SingleAsync(t => t.Key == "a");
        Assert.Equal("core.noop", task.NodeType);
        Assert.True(task.ContinueOnFail);

        var workflow = await verify.Workflows.SingleAsync();
        Assert.Equal("workflows", workflow.Repo);
    }

    [Fact]
    public async Task Node_workflow_runs_end_to_end_on_sqlite()
    {
        var (connection, db) = await OpenLiteAsync();
        await using var _connection = connection;
        await using var _db = db;

        var catalog = NodeTestData.CoreCatalog();
        var workflow = WorkflowDataHelpers.CompileAndSave(db, Tenant, BranchYaml, "workflows/branch.yaml");
        var run = WorkflowDataHelpers.AddPendingRun(db, workflow, "[{}]");

        var outcome = await NewRunner(catalog).RunAsync(db, run, workflow, workingDirectory: null, default);

        Assert.True(outcome.Succeeded, outcome.Error);
        Assert.Equal(RunStatus.Succeeded, run.Status);

        // Every reached node records a TaskRun: prepare → decide, then both branch
        // nodes are scheduled (the untaken "nope" branch arrives with no items and
        // is skipped, so it records no output).
        var taskRuns = await db.TaskRuns.Where(t => t.RunId == run.Id).ToListAsync();
        Assert.Equal(4, taskRuns.Count);
        Assert.All(taskRuns, t => Assert.Equal(TaskRunStatus.Succeeded, t.Status));

        var keys = await db.Tasks.ToDictionaryAsync(t => t.Id, t => t.Key);
        var byKey = taskRuns.ToDictionary(r => keys[r.TaskId]);
        Assert.NotNull(byKey["prepare"].OutputJson);
        Assert.NotNull(byKey["decide"].OutputJson);
        Assert.NotNull(byKey["ok"].OutputJson);
        // The false branch never received an item, so it has no output.
        Assert.Null(byKey["nope"].OutputJson);
    }
}