using Dapper;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Data.SqlClient;
using Npgsql;
using System.Data;
using TCMSDAL.DTO;
using TCMSDAL.Interfaces;
using TCMSDAL.QueryBuilders;
using static GB5Shared.GB5Constant.Constant;

namespace TCMSDAL.Implementations;

/// <summary>
/// DAL for Test Environment Management.
/// Dual-connection pattern:
///   - gb5system (MSERVERCONFIG, MSERVER): accessed via IApplicationConnection.Gb5SystemConnectionString() + direct Dapper.
///   - TCMS DB (TTESTENVRESETLOG): accessed via IQueryExecutor with LoginDTO.
///   - Target run-DB server (DDL): accessed via caller-supplied connection string.
/// DB type is detected from the connection string prefix (Host= → PostgreSQL, otherwise SQL Server).
/// </summary>
public class TCMSTestEnvDAL : ITCMSTestEnvDAL
{
    private readonly IQueryExecutor         _Qe;
    private readonly IApplicationConnection _AppConn;

    public TCMSTestEnvDAL(
        IQueryExecutor queryExecutor,
        IApplicationConnection appConn)
    {
        _Qe      = queryExecutor;
        _AppConn = appConn;
    }

    // ── gb5system helpers ────────────────────────────────────────────────────

    private async Task<IDbConnection> SystemConnAsync(CancellationToken ct)
    {
        ct.ThrowIfCancellationRequested();
        string cs = await _AppConn.Gb5SystemConnectionString().ConfigureAwait(false);
        return IsPostgres(cs) ? new NpgsqlConnection(cs) : new SqlConnection(cs);
    }

    private static bool IsPostgres(string connectionString)
        => connectionString.Contains("Host=", StringComparison.OrdinalIgnoreCase);

    // ── MSERVERCONFIG list ────────────────────────────────────────────────────

    public async Task<IEnumerable<TCMSTestEnvListDTO>> GetTestEnvironmentsAsync(
        LoginDTO login, CancellationToken ct)
    {
        using var conn = await SystemConnAsync(ct).ConfigureAwait(false);
        return await conn.QueryAsync<TCMSTestEnvListDTO>(
            TCMSTestEnvQB.GET_TEST_ENVIRONMENTS,
            new { ClientId = login.ClientId }).ConfigureAwait(false)
            ?? Enumerable.Empty<TCMSTestEnvListDTO>();
    }

    public async Task<TCMSTestEnvListDTO?> GetTestEnvByIdAsync(
        int serverConfigId, CancellationToken ct)
    {
        using var conn = await SystemConnAsync(ct).ConfigureAwait(false);
        return await conn.QueryFirstOrDefaultAsync<TCMSTestEnvListDTO>(
            TCMSTestEnvQB.GET_TEST_ENV_BY_ID,
            new { ServerConfigId = serverConfigId }).ConfigureAwait(false);
    }

    public async Task<TCMSTestDbConnectionDTO?> GetTestDbConnectionRawAsync(
        int serverConfigId, CancellationToken ct)
    {
        using var conn = await SystemConnAsync(ct).ConfigureAwait(false);
        return await conn.QueryFirstOrDefaultAsync<TCMSTestDbConnectionDTO>(
            TCMSTestEnvQB.GET_TEST_DB_CONNECTION_RAW,
            new { ServerConfigId = serverConfigId }).ConfigureAwait(false);
    }

    public async Task<bool> ConnectionNameExistsAsync(
        string connectionName, CancellationToken ct)
    {
        using var conn = await SystemConnAsync(ct).ConfigureAwait(false);
        var count = await conn.ExecuteScalarAsync<int>(
            TCMSTestEnvQB.CHECK_CONNECTIONNAME_EXISTS,
            new { ConnectionName = connectionName }).ConfigureAwait(false);
        return count > 0;
    }

    public async Task<int> RegisterTestDbAsync(
        TCMSRegisterTestDbDTO dto, LoginDTO login, CancellationToken ct)
    {
        using var conn = await SystemConnAsync(ct).ConfigureAwait(false);
        return await conn.ExecuteScalarAsync<int>(
            TCMSTestEnvQB.INSERT_MSERVERCONFIG,
            new
            {
                dto.ConnectionName, dto.ServerId, DatabaseType = dto.DatabaseType,
                dto.DatabaseName, dto.DatabaseUserName, dto.DatabasePassword,
                Port = dto.Port, dto.ControlSourceDbName,
                dto.ClientId, dto.ClientSiteId,
                CreatedById = login.UserId,
            }).ConfigureAwait(false);
    }

    public async Task<int> DeregisterTestDbAsync(
        int serverConfigId, LoginDTO login, CancellationToken ct)
    {
        using var conn = await SystemConnAsync(ct).ConfigureAwait(false);
        return await conn.ExecuteAsync(
            TCMSTestEnvQB.DEREGISTER_TEST_DB,
            new { ServerConfigId = serverConfigId, ModifiedById = login.UserId }).ConfigureAwait(false);
    }

    public async Task<IEnumerable<TCMSMServerSelectDTO>> GetMServerSelectListAsync(
        CancellationToken ct)
    {
        using var conn = await SystemConnAsync(ct).ConfigureAwait(false);
        return await conn.QueryAsync<TCMSMServerSelectDTO>(
            TCMSTestEnvQB.GET_MSERVER_SELECT_LIST).ConfigureAwait(false)
            ?? Enumerable.Empty<TCMSMServerSelectDTO>();
    }

    // ── TTESTENVRESETLOG (TCMS DB via IQueryExecutor) ────────────────────────

    public async Task<int> InsertResetLogAsync(
        TCMSTestEnvResetLogDTO log, LoginDTO login, CancellationToken ct)
        => await _Qe.ExecuteScalarAsync<int>(
            login,
            TCMSTestEnvQB.INSERT_RESET_LOG,
            new
            {
                log.ServerConfigId, log.ConnectionName, log.ControlSource,
                log.TriggeredBy, log.DurationMs,
                Success      = log.Success ? 1 : 0,
                log.ErrorMessage,
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task UpdateResetLogOutcomeAsync(
        int logId, int durationMs, bool success, string? errorMessage,
        LoginDTO login, CancellationToken ct)
        => await _Qe.ExecuteAsync(
            login,
            TCMSTestEnvQB.UPDATE_RESET_LOG_OUTCOME,
            new
            {
                ResetLogId   = logId,
                DurationMs   = durationMs,
                Success      = success ? 1 : 0,
                ErrorMessage = errorMessage,
            },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<TCMSTestEnvResetLogDTO>> GetResetHistoryAsync(
        int serverConfigId, LoginDTO login, CancellationToken ct)
        => await _Qe.QueryAsync<TCMSTestEnvResetLogDTO>(
            login,
            TCMSTestEnvQB.GET_RESET_HISTORY,
            new { ServerConfigId = serverConfigId },
            cancellationToken: ct).ConfigureAwait(false)
           ?? Enumerable.Empty<TCMSTestEnvResetLogDTO>();

    // ── DDL on target run-DB server ───────────────────────────────────────────

    public async Task ExecuteDdlOnTargetServerAsync(
        string connectionString, string ddlSql, int dbType, CancellationToken ct)
    {
        ct.ThrowIfCancellationRequested();
        using IDbConnection conn = dbType == DBTYPE.POSTGRESQL
            ? new NpgsqlConnection(connectionString)
            : new SqlConnection(connectionString);

        // DDL timeout: 10 minutes — clone/restore operations can be long-running
        await conn.ExecuteAsync(ddlSql, commandTimeout: 600).ConfigureAwait(false);
    }

    // ── Snapshot catalog ──────────────────────────────────────────────────────

    public async Task<IEnumerable<TCMSTestEnvSnapshotListDTO>> GetSnapshotsAsync(
        string adminConnectionString, string runDbName, int dbType, CancellationToken ct)
    {
        ct.ThrowIfCancellationRequested();
        using IDbConnection conn = dbType == DBTYPE.POSTGRESQL
            ? new NpgsqlConnection(adminConnectionString)
            : new SqlConnection(adminConnectionString);

        string sql = dbType == DBTYPE.POSTGRESQL
            ? @"SELECT datname                          AS SnapshotName,
                       @RunDbName                       AS SourceDatabase,
                       (SELECT pg_stat_file('base/'|| oid ||'/PG_VERSION')).modification
                                                        AS CreateDate,
                       pg_database_size(datname)/1048576 AS SizeMb
                FROM   pg_database
                WHERE  datname LIKE @Prefix"
            : @"SELECT d.name                                                    AS SnapshotName,
                       DB_NAME(d.source_database_id)                             AS SourceDatabase,
                       d.create_date                                             AS CreateDate,
                       SUM(mf.size) * 8 / 1024                                  AS SizeMb
                FROM   sys.databases d
                JOIN   sys.master_files mf ON mf.database_id = d.database_id
                WHERE  d.source_database_id = DB_ID(@RunDbName)
                GROUP  BY d.name, d.source_database_id, d.create_date";

        var param = dbType == DBTYPE.POSTGRESQL
            ? (object)new { RunDbName = runDbName, Prefix = $"{runDbName}_snap_%" }
            : new { RunDbName = runDbName };

        return await conn.QueryAsync<TCMSTestEnvSnapshotListDTO>(sql, param)
            .ConfigureAwait(false)
            ?? Enumerable.Empty<TCMSTestEnvSnapshotListDTO>();
    }
}