using System.Data;
using Dapper;
using SwDAL.DTO.ServerConfig;
using SwDAL.Query.ServerConfig;
using GB5Shared.Connection;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Npgsql;

namespace SwDAL.CustomCode.ServerConfig;

public interface IServerConfigDAL
{
    Task<int?> FindServerIdByMachineNameAsync(string serverMachineName, CancellationToken ct);
    Task<int>  InsertServerAsync(MServerDTO dto, CancellationToken ct);
    Task<int>  InsertServerConfigAsync(MServerConfigWriteDTO dto, CancellationToken ct);
    Task UpdateReportServerConfigIdAsync(int mainServerConfigId, int linkedServerConfigId, CancellationToken ct);
    Task UpdateArchiveServerConfigIdAsync(int mainServerConfigId, int linkedServerConfigId, CancellationToken ct);
}

/// <summary>SQL Server dialect only for this release (see ServerConfigWriteQB's doc comment) —
/// the dbType switch below mirrors ServerConfigCache's exact pattern so a Postgres branch can be
/// added the same way later without touching callers.</summary>
public class ServerConfigDAL : IServerConfigDAL
{
    private readonly IApplicationConnection _appConnection;
    private readonly IConfiguration         _configuration;

    public ServerConfigDAL(IApplicationConnection appConnection, IConfiguration configuration)
    {
        _appConnection = appConnection;
        _configuration = configuration;
    }

    private async Task<IDbConnection> OpenSystemConnectionAsync(CancellationToken ct)
    {
        string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
        int dbType = _configuration.GetValue<int>("Gb5SystemDTO:DataBaseType", 0);

        IDbConnection conn = dbType switch
        {
            1 => new NpgsqlConnection(connStr),
            _ => new SqlConnection(connStr)
        };

        if (conn is SqlConnection sqlConn)
            await sqlConn.OpenAsync(ct).ConfigureAwait(false);
        else if (conn is NpgsqlConnection npgsqlConn)
            await npgsqlConn.OpenAsync(ct).ConfigureAwait(false);

        return conn;
    }

    public async Task<int?> FindServerIdByMachineNameAsync(string serverMachineName, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        return await conn.QueryFirstOrDefaultAsync<int?>(
            new CommandDefinition(ServerConfigWriteQB.FIND_MSERVER_BY_MACHINE_NAME,
                new { ServerMachineName = serverMachineName }, cancellationToken: ct))
            .ConfigureAwait(false);
    }

    public async Task<int> InsertServerAsync(MServerDTO dto, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        return await conn.QuerySingleAsync<int>(
            new CommandDefinition(ServerConfigWriteQB.INSERT_MSERVER, dto, cancellationToken: ct))
            .ConfigureAwait(false);
    }

    public async Task<int> InsertServerConfigAsync(MServerConfigWriteDTO dto, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        return await conn.QuerySingleAsync<int>(
            new CommandDefinition(ServerConfigWriteQB.INSERT_MSERVERCONFIG, dto, cancellationToken: ct))
            .ConfigureAwait(false);
    }

    public async Task UpdateReportServerConfigIdAsync(int mainServerConfigId, int linkedServerConfigId, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        await conn.ExecuteAsync(
            new CommandDefinition(ServerConfigWriteQB.UPDATE_REPORT_SERVER_CONFIG_ID,
                new { MainServerConfigId = mainServerConfigId, LinkedServerConfigId = linkedServerConfigId },
                cancellationToken: ct))
            .ConfigureAwait(false);
    }

    public async Task UpdateArchiveServerConfigIdAsync(int mainServerConfigId, int linkedServerConfigId, CancellationToken ct)
    {
        using var conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
        await conn.ExecuteAsync(
            new CommandDefinition(ServerConfigWriteQB.UPDATE_ARCHIVE_SERVER_CONFIG_ID,
                new { MainServerConfigId = mainServerConfigId, LinkedServerConfigId = linkedServerConfigId },
                cancellationToken: ct))
            .ConfigureAwait(false);
    }
}