using System.Text.Json;
using CareFix.Api.Infrastructure;
using CareFix.Api.Options;
using CareFix.Core;
using Dapper;
using Microsoft.Extensions.Options;

namespace CareFix.Api.Hospitals;

/// <summary>
/// Agent channel for on-prem hospitals. The server writes a job; the hospital's CareFix Agent (outbound HTTPS only)
/// picks it up, runs it locally with its own credentials and posts the result. No inbound port is opened at the hospital,
/// and the hospital's SQL passwords never leave the hospital.
/// </summary>
public sealed class AgentHospitalExecutor(ControlDb db, IOptions<CareFixOptions> options) : IHospitalExecutor
{
    private const int PickupSeconds = 45;
    private static readonly TimeSpan OfflineAfter = TimeSpan.FromMinutes(2);

    private sealed class JobRow
    {
        public string State { get; set; } = "";
        public string? ResultJson { get; set; }
        public int? ErrorStatus { get; set; }
        public string? Error { get; set; }
        public DateTime ExpiresAt { get; set; }
    }

    private async Task<JsonElement> CallAsync(int hospitalId, string kind, object payload, TimeSpan wait, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        var lastSeen = await c.ExecuteScalarAsync<DateTime?>("SELECT LastSeenAt FROM CF_AGENT WHERE HospitalId = @hospitalId", new { hospitalId });
        if (lastSeen is null)
            throw AppException.BadRequest("No CareFix Agent has connected for this hospital yet. Generate an agent key under Hospitals and install the agent on the hospital's server.");
        if (DateTime.UtcNow - lastSeen.Value > OfflineAfter)
            throw new AppException(503, $"The CareFix Agent at this hospital is offline (last seen {lastSeen.Value:dd MMM HH:mm} UTC). Ask the hospital IT team to check the \"CareFix Agent\" Windows service.");

        var jobId = await c.ExecuteScalarAsync<long>("""
            INSERT CF_AGENT_JOB (HospitalId, Kind, PayloadJson, ExpiresAt)
            OUTPUT INSERTED.JobId VALUES (@hospitalId, @kind, @p, DATEADD(SECOND, @PickupSeconds, SYSUTCDATETIME()))
            """, new { hospitalId, kind, p = JsonSerializer.Serialize(payload, AgentProtocol.Json), PickupSeconds });

        var deadline = DateTime.UtcNow + wait;
        while (true)
        {
            await Task.Delay(400, ct);
            var r = await c.QuerySingleAsync<JobRow>("SELECT State, ResultJson, ErrorStatus, Error, ExpiresAt FROM CF_AGENT_JOB WHERE JobId = @jobId", new { jobId });
            if (r.State == "Done")
            {
                // Results can contain unmasked patient data: keep them only as long as needed to hand over.
                await c.ExecuteAsync("UPDATE CF_AGENT_JOB SET ResultJson = NULL WHERE JobId = @jobId", new { jobId });
                using var doc = JsonDocument.Parse(r.ResultJson ?? "null");
                return doc.RootElement.Clone();
            }
            if (r.State == "Failed") throw new AppException(r.ErrorStatus ?? 400, r.Error ?? "The agent reported an error.");

            if (r.State == "Pending" && DateTime.UtcNow > r.ExpiresAt)
            {
                var n = await c.ExecuteAsync("UPDATE CF_AGENT_JOB SET State = 'Expired' WHERE JobId = @jobId AND State = 'Pending'", new { jobId });
                if (n == 1) throw new AppException(504, "The CareFix Agent did not pick up the request in time. It may be busy or losing its connection. Try again.");
                continue; // taken just now
            }
            if (r.State == "Taken" && DateTime.UtcNow > deadline)
            {
                throw new AppException(504, kind == AgentProtocol.Kinds.ApplySteps
                    ? "The agent started the change but did not report back in time, so the result is unknown. Check carefix.CF_CHANGE_LOG at the hospital before doing anything else. Running the fix again is safe: every step checks the old value first."
                    : "The agent did not finish in time. Try again, or narrow the query.");
            }
        }
    }

    private TimeSpan SelectWait => TimeSpan.FromSeconds(options.Value.Safety.SelectTimeoutSeconds + PickupSeconds + 15);

    public async Task<QueryResult> RunSelectAsync(int h, string sql, CancellationToken ct)
    {
        var e = await CallAsync(h, AgentProtocol.Kinds.Select, new SelectPayload(sql), SelectWait, ct);
        var dto = e.Deserialize<SelectResultDto>(AgentProtocol.Json) ?? throw new AppException(502, "The agent returned an empty result.");
        return new QueryResult(dto.Columns, dto.Rows.Select(r => r.Select(AgentProtocol.ToPlain).ToArray()).ToList(), dto.Truncated, dto.DurationMs);
    }

    public async Task<bool> AnyRowsAsync(int h, string sql, string pkValue, CancellationToken ct) =>
        (await CallAsync(h, AgentProtocol.Kinds.AnyRows, new AnyRowsPayload(sql, pkValue), SelectWait, ct)).GetBoolean();

    public async Task<ValueRead> GetValueAsync(int h, string table, string pkColumn, string pkValue, string column, CancellationToken ct) =>
        (await CallAsync(h, AgentProtocol.Kinds.GetValue, new ValuePayload(table, pkColumn, pkValue, column), SelectWait, ct))
            .Deserialize<ValueRead>(AgentProtocol.Json)!;

    public async Task<Dictionary<string, object?>?> GetRowAsync(int h, string table, string pkColumn, string pkValue, CancellationToken ct)
    {
        var e = await CallAsync(h, AgentProtocol.Kinds.GetRow, new RowPayload(table, pkColumn, pkValue), SelectWait, ct);
        if (e.ValueKind == JsonValueKind.Null) return null;
        return e.EnumerateObject().ToDictionary(p => p.Name, p => AgentProtocol.ToPlain(p.Value));
    }

    public async Task<int> ApplyStepsAsync(int h, IReadOnlyList<StepChange> steps, string ticketRef, CancellationToken ct) =>
        (await CallAsync(h, AgentProtocol.Kinds.ApplySteps, new ApplyPayload(steps.ToList(), ticketRef), TimeSpan.FromSeconds(PickupSeconds + 90), ct)).GetInt32();

    public async Task<IReadOnlyList<SchemaColumn>> ReadSchemaAsync(int h, CancellationToken ct) =>
        (await CallAsync(h, AgentProtocol.Kinds.ReadSchema, new { }, TimeSpan.FromSeconds(PickupSeconds + 180), ct))
            .Deserialize<List<SchemaColumn>>(AgentProtocol.Json)!;

    public async Task<object> TestAsync(int h, CancellationToken ct) =>
        await CallAsync(h, AgentProtocol.Kinds.Test, new { }, SelectWait, ct);
}