using CareFix.Api.Infrastructure;
using CareFix.Api.Options;
using CareFix.Api.Security;
using CareFix.Core;
using Dapper;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Options;

namespace CareFix.Api.Hospitals;

/// <summary>The only component that talks to hospital databases.</summary>
public interface IHospitalExecutor
{
    Task<QueryResult> RunSelectAsync(int hospitalId, string sql, CancellationToken ct);
    Task<bool> AnyRowsAsync(int hospitalId, string sql, string pkValue, CancellationToken ct);
    Task<ValueRead> GetValueAsync(int hospitalId, string table, string pkColumn, string pkValue, string column, CancellationToken ct);
    Task<Dictionary<string, object?>?> GetRowAsync(int hospitalId, string table, string pkColumn, string pkValue, CancellationToken ct);
    Task<int> ApplyStepsAsync(int hospitalId, IReadOnlyList<StepChange> steps, string ticketRef, CancellationToken ct);
    Task<IReadOnlyList<SchemaColumn>> ReadSchemaAsync(int hospitalId, CancellationToken ct);
    Task<object> TestAsync(int hospitalId, CancellationToken ct);
}

/// <summary>Sends each call to the Direct or Agent channel, per the hospital's setting.</summary>
public sealed class RoutingHospitalExecutor(ControlDb db, IMemoryCache cache, DirectHospitalExecutor direct, AgentHospitalExecutor agent) : IHospitalExecutor
{
    public void Invalidate(int hospitalId) => cache.Remove($"channel:{hospitalId}");

    private async Task<IHospitalExecutor> PickAsync(int hospitalId, CancellationToken ct)
    {
        var channel = await cache.GetOrCreateAsync($"channel:{hospitalId}", async e =>
        {
            e.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(1);
            await using var c = await db.OpenAsync(ct);
            return await c.ExecuteScalarAsync<string?>("SELECT Channel FROM CF_HOSPITAL WHERE HospitalId = @hospitalId AND Status = 'Active'", new { hospitalId });
        });
        return channel switch
        {
            "Direct" => direct,
            "Agent" => agent,
            _ => throw AppException.BadRequest("This hospital is not active in CareFix."),
        };
    }

    public async Task<QueryResult> RunSelectAsync(int h, string sql, CancellationToken ct) => await (await PickAsync(h, ct)).RunSelectAsync(h, sql, ct);
    public async Task<bool> AnyRowsAsync(int h, string sql, string pk, CancellationToken ct) => await (await PickAsync(h, ct)).AnyRowsAsync(h, sql, pk, ct);
    public async Task<ValueRead> GetValueAsync(int h, string t, string pkc, string pk, string col, CancellationToken ct) => await (await PickAsync(h, ct)).GetValueAsync(h, t, pkc, pk, col, ct);
    public async Task<Dictionary<string, object?>?> GetRowAsync(int h, string t, string pkc, string pk, CancellationToken ct) => await (await PickAsync(h, ct)).GetRowAsync(h, t, pkc, pk, ct);
    public async Task<int> ApplyStepsAsync(int h, IReadOnlyList<StepChange> steps, string r, CancellationToken ct) => await (await PickAsync(h, ct)).ApplyStepsAsync(h, steps, r, ct);
    public async Task<IReadOnlyList<SchemaColumn>> ReadSchemaAsync(int h, CancellationToken ct) => await (await PickAsync(h, ct)).ReadSchemaAsync(h, ct);
    public async Task<object> TestAsync(int h, CancellationToken ct) => await (await PickAsync(h, ct)).TestAsync(h, ct);
}

/// <summary>Direct channel: hospital DB reachable from the CareFix server (cloud, VPN or whitelisted IP).</summary>
public sealed class DirectHospitalExecutor(ControlDb db, CredentialVault vault, IOptions<CareFixOptions> options) : IHospitalExecutor
{
    private sealed record ConnRow(string Server, int Port, string DbName, string RoUser, byte[] RoPasswordEnc,
                                  string RwUser, byte[] RwPasswordEnc, bool Encrypt, bool TrustServerCert);

    private async Task<SqlOps> OpsAsync(int hospitalId, CancellationToken ct)
    {
        ConnRow? row;
        await using (var c = await db.OpenAsync(ct))
        {
            row = await c.QuerySingleOrDefaultAsync<ConnRow>(
                "SELECT Server, Port, DbName, RoUser, RoPasswordEnc, RwUser, RwPasswordEnc, Encrypt, TrustServerCert FROM CF_CONNECTION WHERE HospitalId = @hospitalId",
                new { hospitalId });
        }
        if (row is null) throw AppException.BadRequest("Connection details are not set for this hospital. An admin must add them under Hospitals.");
        var s = options.Value.Safety;
        return new SqlOps(
            SqlOps.BuildConnectionString(row.Server, row.Port, row.DbName, row.RoUser, vault.Decrypt(row.RoPasswordEnc), row.Encrypt, row.TrustServerCert, "CareFix-Read"),
            SqlOps.BuildConnectionString(row.Server, row.Port, row.DbName, row.RwUser, vault.Decrypt(row.RwPasswordEnc), row.Encrypt, row.TrustServerCert, "CareFix-Write"),
            new SqlLimits(s.MaxRowsPerSelect, s.SelectTimeoutSeconds, s.LockTimeoutMs));
    }

    private static async Task<T> W<T>(Func<Task<T>> f)
    {
        try { return await f(); }
        catch (OpsException ex) { throw new AppException(ex.Status, ex.Message); }
    }

    public Task<QueryResult> RunSelectAsync(int h, string sql, CancellationToken ct) => W(async () => await (await OpsAsync(h, ct)).RunSelectAsync(sql, ct));
    public Task<bool> AnyRowsAsync(int h, string sql, string pk, CancellationToken ct) => W(async () => await (await OpsAsync(h, ct)).AnyRowsAsync(sql, pk, ct));
    public Task<ValueRead> GetValueAsync(int h, string t, string pkc, string pk, string col, CancellationToken ct) => W(async () => await (await OpsAsync(h, ct)).GetValueAsync(t, pkc, pk, col, ct));
    public Task<Dictionary<string, object?>?> GetRowAsync(int h, string t, string pkc, string pk, CancellationToken ct) => W(async () => await (await OpsAsync(h, ct)).GetRowAsync(t, pkc, pk, ct));
    public Task<int> ApplyStepsAsync(int h, IReadOnlyList<StepChange> steps, string r, CancellationToken ct) => W(async () => await (await OpsAsync(h, ct)).ApplyStepsAsync(steps, r, ct));
    public Task<IReadOnlyList<SchemaColumn>> ReadSchemaAsync(int h, CancellationToken ct) => W(async () => await (await OpsAsync(h, ct)).ReadSchemaAsync(ct));
    public Task<object> TestAsync(int h, CancellationToken ct) => W<object>(async () => await (await OpsAsync(h, ct)).TestAsync(ct));
}