using Dapper;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.Resource.Response;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using System.Diagnostics;
using TCMSBLL.Interfaces;
using TCMSDAL.DTO;
using TCMSDAL.Interfaces;
using static GB5Shared.GB5Constant.Constant;

namespace TCMSBLL.Implementations;

/// <summary>
/// Business logic for Test Environment Management.
/// Clone/Sync: dual-engine — SQL Server (BACKUP+RESTORE) and PostgreSQL (CREATE DATABASE TEMPLATE).
/// Snapshot: SQL Server (COW sparse file) and PostgreSQL (full-copy template with naming convention).
/// All mutating operations write to TTESTENVRESETLOG for audit trail.
/// </summary>
public class TCMSTestEnvBLL : ITCMSTestEnvBLL
{
    private readonly ITCMSTestEnvDAL         _Dal;
    private readonly IApplicationConnection  _AppConn;
    private readonly ILogger<TCMSTestEnvBLL> _Logger;

    private const string CryptoKey = "GB5";

    public TCMSTestEnvBLL(
        ITCMSTestEnvDAL dal,
        IApplicationConnection appConn,
        ILogger<TCMSTestEnvBLL> logger)
    {
        _Dal    = dal;
        _AppConn = appConn;
        _Logger  = logger;
    }

    // ── Read ──────────────────────────────────────────────────────────────────

    public async Task<IEnumerable<TCMSTestEnvListDTO>> GetTestEnvironmentsAsync(
        LoginDTO login, CancellationToken ct)
        => await _Dal.GetTestEnvironmentsAsync(login, ct).ConfigureAwait(false);

    public async Task<IEnumerable<TCMSMServerSelectDTO>> GetMServerSelectListAsync(
        LoginDTO login, CancellationToken ct)
        => await _Dal.GetMServerSelectListAsync(ct).ConfigureAwait(false);

    /// <summary>
    /// Returns decrypted credentials for a TEST instance (DBINSTANCETYPE=1).
    /// Returns null for LIVE configs — callers treat null as validation error.
    /// </summary>
    public async Task<TCMSTestDbConnectionDTO?> GetTestDbConnectionAsync(
        int serverConfigId, LoginDTO login, CancellationToken ct)
    {
        var raw = await _Dal.GetTestDbConnectionRawAsync(serverConfigId, ct).ConfigureAwait(false);
        if (raw is null || raw.DbInstanceType != 1)
            return null;

        raw.UserName = _AppConn.Decrypt(raw.UserName, CryptoKey);
        raw.Password = _AppConn.Decrypt(raw.Password, CryptoKey);
        return raw;
    }

    public async Task<IEnumerable<TCMSTestEnvResetLogDTO>> GetResetHistoryAsync(
        int serverConfigId, LoginDTO login, CancellationToken ct)
        => await _Dal.GetResetHistoryAsync(serverConfigId, login, ct).ConfigureAwait(false);

    public async Task<IEnumerable<TCMSTestEnvSnapshotListDTO>> GetSnapshotsAsync(
        int serverConfigId, LoginDTO login, CancellationToken ct)
    {
        var conn = await GetDecryptedConnectionAsync(serverConfigId, ct).ConfigureAwait(false);
        if (conn is null) return Enumerable.Empty<TCMSTestEnvSnapshotListDTO>();

        string adminCs = BuildAdminCs(conn);
        return await _Dal.GetSnapshotsAsync(adminCs, conn.DatabaseName, conn.DatabaseType, ct)
            .ConfigureAwait(false);
    }

    // ── Register / Deregister ─────────────────────────────────────────────────

    public async Task<string> RegisterTestDbAsync(
        TCMSRegisterTestDbDTO dto, LoginDTO login, CancellationToken ct)
    {
        try
        {
            if (string.IsNullOrWhiteSpace(dto.ConnectionName)) return "Connection Name is required.";
            if (string.IsNullOrWhiteSpace(dto.DatabaseName))   return "Database Name is required.";
            if (dto.ServerId == 0)                             return "Server is required.";

            if (await _Dal.ConnectionNameExistsAsync(dto.ConnectionName, ct).ConfigureAwait(false))
                return $"Connection name '{dto.ConnectionName}' is already registered.";

            // Encrypt credentials before persisting to MSERVERCONFIG
            dto.DatabaseUserName = _AppConn.Encrypt(dto.DatabaseUserName, CryptoKey);
            dto.DatabasePassword = _AppConn.Encrypt(dto.DatabasePassword, CryptoKey);
            dto.ClientId         = login.ClientId;
            dto.ClientSiteId     = 1;   // LoginDTO doesn't expose ClientSiteId; default to 1

            int newId = await _Dal.RegisterTestDbAsync(dto, login, ct).ConfigureAwait(false);
            _Logger.LogInformation("Test DB registered: {Name} (ConfigId={Id}) by {User}",
                dto.ConnectionName, newId, login.UserId);

            return $"{SuccessResponse.SaveSuccessMessage} {newId}";
        }
        catch (Exception ex)
        {
            _Logger.LogError(ex, "RegisterTestDb failed for {ConnectionName}", dto.ConnectionName);
            throw;
        }
    }

    public async Task<string> DeregisterTestDbAsync(
        TCMSDeregisterTestDbDTO dto, LoginDTO login, CancellationToken ct)
    {
        try
        {
            if (dto.ServerConfigId == 0) return "ServerConfigId is required.";

            if (dto.DropPhysicalDb)
            {
                var conn = await GetDecryptedConnectionAsync(dto.ServerConfigId, ct).ConfigureAwait(false);
                if (conn is not null)
                {
                    string adminCs = BuildAdminCs(conn);
                    string dropSql = conn.DatabaseType == DBTYPE.POSTGRESQL
                        ? $"DROP DATABASE IF EXISTS \"{conn.DatabaseName}\""
                        : BuildKillAndDropDdl(conn.DatabaseName);

                    await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, dropSql, conn.DatabaseType, ct)
                        .ConfigureAwait(false);

                    _Logger.LogInformation("Physical DB dropped: {Db} (ConfigId={Id}) by {User}",
                        conn.DatabaseName, dto.ServerConfigId, login.UserId);
                }
            }

            await _Dal.DeregisterTestDbAsync(dto.ServerConfigId, login, ct).ConfigureAwait(false);
            _Logger.LogInformation("Test DB deregistered: ConfigId={Id} by {User}",
                dto.ServerConfigId, login.UserId);

            return SuccessResponse.DeleteSuccessMessage;
        }
        catch (Exception ex)
        {
            _Logger.LogError(ex, "DeregisterTestDb failed for ConfigId={Id}", dto.ServerConfigId);
            throw;
        }
    }

    // ── Clone ─────────────────────────────────────────────────────────────────

    public async Task<string> CloneFromControlAsync(
        TCMSCloneDbDTO dto, LoginDTO login, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(dto.ControlDbName))  return "Control DB name is required.";
        if (string.IsNullOrWhiteSpace(dto.RunDbName))      return "Run DB name is required.";
        if (string.IsNullOrWhiteSpace(dto.ConnectionName)) return "Connection name is required.";

        if (await _Dal.ConnectionNameExistsAsync(dto.ConnectionName, ct).ConfigureAwait(false))
            return $"Connection name '{dto.ConnectionName}' is already registered.";

        var controlConfig = await _AppConn.DatabaseConnectionObject(dto.ControlDbName)
            .ConfigureAwait(false);

        string adminUser = _AppConn.Decrypt(controlConfig.DatabaseUserName, CryptoKey);
        string adminPass = _AppConn.Decrypt(controlConfig.DatabasePassword, CryptoKey);
        int    dbType    = controlConfig.DbType;

        string adminCs = dbType == DBTYPE.POSTGRESQL
            ? BuildPgAdminCs(controlConfig.ServerIP, adminUser, adminPass, controlConfig.DatabasePort)
            : BuildSqlAdminCs(controlConfig.ServerIP, adminUser, adminPass, controlConfig.DatabasePort);

        int logId = await _Dal.InsertResetLogAsync(new TCMSTestEnvResetLogDTO
        {
            ServerConfigId = 0,
            ConnectionName = dto.ConnectionName,
            ControlSource  = dto.ControlDbName,
            TriggeredBy    = dto.TriggeredBy,
            Success        = false,
        }, login, ct).ConfigureAwait(false);

        var sw = Stopwatch.StartNew();
        try
        {
            await CloneDatabaseAsync(adminCs, dbType, dto.ControlDbName, dto.RunDbName, ct)
                .ConfigureAwait(false);

            var register = new TCMSRegisterTestDbDTO
            {
                ConnectionName      = dto.ConnectionName,
                ServerId            = controlConfig.ServerId,
                DatabaseName        = dto.RunDbName,
                DatabaseType        = dbType,
                ControlSourceDbName = dto.ControlDbName,
                DatabaseUserName    = controlConfig.DatabaseUserName,   // already encrypted in MSERVERCONFIG
                DatabasePassword    = controlConfig.DatabasePassword,
                Port                = controlConfig.DatabasePort,
                ClientId            = login.ClientId,
                ClientSiteId        = 1,   // LoginDTO doesn't expose ClientSiteId; default to 1
            };
            int newConfigId = await _Dal.RegisterTestDbAsync(register, login, ct).ConfigureAwait(false);

            sw.Stop();
            await _Dal.UpdateResetLogOutcomeAsync(logId, (int)sw.ElapsedMilliseconds, true, null, login, ct)
                .ConfigureAwait(false);

            _Logger.LogInformation("CloneFromControl: {Ctrl}→{Run} ({Ms}ms) by {User}",
                dto.ControlDbName, dto.RunDbName, sw.ElapsedMilliseconds, dto.TriggeredBy);

            return $"{SuccessResponse.SaveSuccessMessage} {newConfigId}";
        }
        catch (Exception ex)
        {
            sw.Stop();
            await _Dal.UpdateResetLogOutcomeAsync(logId, (int)sw.ElapsedMilliseconds, false, ex.Message, login, ct)
                .ConfigureAwait(false);
            _Logger.LogError(ex, "CloneFromControl failed: {Ctrl}→{Run}", dto.ControlDbName, dto.RunDbName);
            throw;
        }
    }

    // ── Sync (re-clone) ───────────────────────────────────────────────────────

    public async Task<string> SyncFromControlAsync(
        TCMSSyncFromControlDTO dto, LoginDTO login, CancellationToken ct)
    {
        if (dto.ServerConfigId == 0) return "ServerConfigId is required.";

        var env = await _Dal.GetTestEnvByIdAsync(dto.ServerConfigId, ct).ConfigureAwait(false);
        if (env is null) return "Test environment not found.";
        if (string.IsNullOrWhiteSpace(env.ControlSourceDb))
            return "No control source DB recorded for this environment. Use CloneFromControl first.";

        var conn = await GetDecryptedConnectionAsync(dto.ServerConfigId, ct).ConfigureAwait(false);
        if (conn is null) return "Could not resolve connection details for this environment.";

        string adminCs = BuildAdminCs(conn);

        int logId = await _Dal.InsertResetLogAsync(new TCMSTestEnvResetLogDTO
        {
            ServerConfigId = dto.ServerConfigId,
            ConnectionName = env.ConnectionName,
            ControlSource  = env.ControlSourceDb,
            TriggeredBy    = dto.TriggeredBy,
            Success        = false,
        }, login, ct).ConfigureAwait(false);

        var sw = Stopwatch.StartNew();
        try
        {
            string dropSql = conn.DatabaseType == DBTYPE.POSTGRESQL
                ? $"DROP DATABASE IF EXISTS \"{conn.DatabaseName}\""
                : BuildKillAndDropDdl(conn.DatabaseName);

            await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, dropSql, conn.DatabaseType, ct)
                .ConfigureAwait(false);

            await CloneDatabaseAsync(adminCs, conn.DatabaseType, env.ControlSourceDb, conn.DatabaseName, ct)
                .ConfigureAwait(false);

            sw.Stop();
            await _Dal.UpdateResetLogOutcomeAsync(logId, (int)sw.ElapsedMilliseconds, true, null, login, ct)
                .ConfigureAwait(false);

            _Logger.LogInformation("SyncFromControl: {Run} reset from {Ctrl} ({Ms}ms) by {User}",
                conn.DatabaseName, env.ControlSourceDb, sw.ElapsedMilliseconds, dto.TriggeredBy);

            return SuccessResponse.UpdateSuccess;
        }
        catch (Exception ex)
        {
            sw.Stop();
            await _Dal.UpdateResetLogOutcomeAsync(logId, (int)sw.ElapsedMilliseconds, false, ex.Message, login, ct)
                .ConfigureAwait(false);
            _Logger.LogError(ex, "SyncFromControl failed for ConfigId={Id}", dto.ServerConfigId);
            throw;
        }
    }

    // ── Snapshots ─────────────────────────────────────────────────────────────

    public async Task<string> CreateSnapshotAsync(
        TCMSCreateSnapshotDTO dto, LoginDTO login, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(dto.SnapshotLabel)) return "Snapshot label is required.";

        var conn = await GetDecryptedConnectionAsync(dto.ServerConfigId, ct).ConfigureAwait(false);
        if (conn is null) return "Test environment not found.";

        string adminCs      = BuildAdminCs(conn);
        string snapshotName = $"{conn.DatabaseName}_snap_{Sanitize(dto.SnapshotLabel)}";

        string ddl = conn.DatabaseType == DBTYPE.POSTGRESQL
            ? $"CREATE DATABASE \"{snapshotName}\" TEMPLATE \"{conn.DatabaseName}\""
            : await BuildSqlSnapshotDdlAsync(adminCs, conn.DatabaseName, snapshotName)
                .ConfigureAwait(false);

        await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, ddl, conn.DatabaseType, ct)
            .ConfigureAwait(false);

        _Logger.LogInformation("Snapshot created: {Snap} from {Db} by {User}",
            snapshotName, conn.DatabaseName, login.UserId);

        return $"{SuccessResponse.SaveSuccessMessage} {snapshotName}";
    }

    public async Task<string> RestoreToSnapshotAsync(
        TCMSRestoreToSnapshotDTO dto, LoginDTO login, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(dto.SnapshotLabel)) return "Snapshot label is required.";

        var conn = await GetDecryptedConnectionAsync(dto.ServerConfigId, ct).ConfigureAwait(false);
        if (conn is null) return "Test environment not found.";

        string adminCs      = BuildAdminCs(conn);
        string snapshotName = $"{conn.DatabaseName}_snap_{Sanitize(dto.SnapshotLabel)}";

        string ddl = conn.DatabaseType == DBTYPE.POSTGRESQL
            // PostgreSQL: drop run DB, recreate from snapshot template
            ? $"DROP DATABASE IF EXISTS \"{conn.DatabaseName}\";" +
              $"CREATE DATABASE \"{conn.DatabaseName}\" TEMPLATE \"{snapshotName}\""
            // SQL Server: native snapshot restore (requires SINGLE_USER mode)
            : $"ALTER DATABASE [{conn.DatabaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;" +
              $"RESTORE DATABASE [{conn.DatabaseName}] FROM DATABASE_SNAPSHOT = N'{snapshotName}';" +
              $"ALTER DATABASE [{conn.DatabaseName}] SET MULTI_USER;";

        await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, ddl, conn.DatabaseType, ct)
            .ConfigureAwait(false);

        _Logger.LogInformation("Restored {Db} to snapshot {Snap} by {User}",
            conn.DatabaseName, snapshotName, login.UserId);

        return SuccessResponse.UpdateSuccess;
    }

    public async Task<string> DeleteSnapshotAsync(
        TCMSDeleteSnapshotDTO dto, LoginDTO login, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(dto.SnapshotLabel)) return "Snapshot label is required.";

        var conn = await GetDecryptedConnectionAsync(dto.ServerConfigId, ct).ConfigureAwait(false);
        if (conn is null) return "Test environment not found.";

        string adminCs      = BuildAdminCs(conn);
        string snapshotName = $"{conn.DatabaseName}_snap_{Sanitize(dto.SnapshotLabel)}";

        string ddl = conn.DatabaseType == DBTYPE.POSTGRESQL
            ? $"DROP DATABASE IF EXISTS \"{snapshotName}\""
            : $"DROP DATABASE [{snapshotName}]";

        await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, ddl, conn.DatabaseType, ct)
            .ConfigureAwait(false);

        _Logger.LogInformation("Snapshot dropped: {Snap} by {User}", snapshotName, login.UserId);
        return SuccessResponse.DeleteSuccessMessage;
    }

    // ── Private helpers ───────────────────────────────────────────────────────

    private async Task<TCMSTestDbConnectionDTO?> GetDecryptedConnectionAsync(
        int serverConfigId, CancellationToken ct)
    {
        var raw = await _Dal.GetTestDbConnectionRawAsync(serverConfigId, ct).ConfigureAwait(false);
        if (raw is null || raw.DbInstanceType != 1) return null;

        raw.UserName = _AppConn.Decrypt(raw.UserName, CryptoKey);
        raw.Password = _AppConn.Decrypt(raw.Password, CryptoKey);
        return raw;
    }

    private static string BuildAdminCs(TCMSTestDbConnectionDTO conn)
        => conn.DatabaseType == DBTYPE.POSTGRESQL
            ? BuildPgAdminCs(conn.ServerIP, conn.UserName, conn.Password, conn.Port)
            : BuildSqlAdminCs(conn.ServerIP, conn.UserName, conn.Password, conn.Port);

    private static string BuildSqlAdminCs(string ip, string user, string pass, string port)
    {
        string src = int.TryParse(port, out int p) && p > 0 ? $"{ip},{p}" : ip;
        return new SqlConnectionStringBuilder
        {
            DataSource             = src,
            UserID                 = user,
            Password               = pass,
            TrustServerCertificate = true,
            Encrypt                = false,
        }.ConnectionString;
    }

    private static string BuildPgAdminCs(string ip, string user, string pass, string port)
    {
        int pg = int.TryParse(port, out int p) && p > 0 ? p : 5432;
        return $"Host={ip};Port={pg};Username={user};Password={pass};Database=postgres";
    }

    private async Task CloneDatabaseAsync(
        string adminCs, int dbType, string sourceDb, string targetDb, CancellationToken ct)
    {
        if (dbType == DBTYPE.POSTGRESQL)
        {
            string ddl = $"CREATE DATABASE \"{targetDb}\" TEMPLATE \"{sourceDb}\"";
            await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, ddl, dbType, ct).ConfigureAwait(false);
        }
        else
        {
            // SQL Server: backup → restore (works across instances and volumes)
            string timestamp  = DateTime.UtcNow.ToString("yyyyMMddHHmmss");
            string serverIp   = new SqlConnectionStringBuilder(adminCs).DataSource.Split(',')[0];
            string backupPath = $@"\\{serverIp}\SQLBackup\{targetDb}_{timestamp}.bak";

            string backupDdl = $@"
                BACKUP DATABASE [{sourceDb}]
                TO  DISK = N'{backupPath}'
                WITH INIT, COMPRESSION, STATS = 10";

            await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, backupDdl, dbType, ct).ConfigureAwait(false);

            string restoreDdl = await BuildRestoreWithMoveAsync(adminCs, sourceDb, targetDb, backupPath)
                .ConfigureAwait(false);

            await _Dal.ExecuteDdlOnTargetServerAsync(adminCs, restoreDdl, dbType, ct).ConfigureAwait(false);
        }
    }

    // Queries sys.master_files to build per-file MOVE clauses for RESTORE
    private static async Task<string> BuildRestoreWithMoveAsync(
        string adminCs, string sourceDb, string targetDb, string backupPath)
    {
        using var conn = new SqlConnection(adminCs);
        var files = (await conn.QueryAsync<(string LogicalName, string PhysicalName, string Type)>(
            "SELECT name, physical_name, type_desc " +
            "FROM sys.master_files WHERE database_id = DB_ID(@Db)",
            new { Db = sourceDb })).ToList();

        var moves = string.Join(",\n", files.Select(f =>
        {
            string ext  = f.Type == "LOG" ? "_log.ldf" : ".mdf";
            string dir  = System.IO.Path.GetDirectoryName(f.PhysicalName) ?? @"C:\Data";
            string dest = System.IO.Path.Combine(dir, $"{targetDb}{ext}");
            return $"MOVE N'{f.LogicalName}' TO N'{dest}'";
        }));

        return $@"
            RESTORE DATABASE [{targetDb}]
            FROM DISK = N'{backupPath}'
            WITH REPLACE, RECOVERY,
            {moves}";
    }

    // Queries sys.master_files (data files only) to build snapshot ON clause
    private static async Task<string> BuildSqlSnapshotDdlAsync(
        string adminCs, string runDbName, string snapshotName)
    {
        using var conn = new SqlConnection(adminCs);
        var dataFiles = (await conn.QueryAsync<(string LogicalName, string PhysicalName)>(
            "SELECT name, physical_name FROM sys.master_files " +
            "WHERE database_id = DB_ID(@Db) AND type = 0",   // type=0 = data
            new { Db = runDbName })).ToList();

        var onClause = string.Join(",\n", dataFiles.Select(f =>
        {
            string dir  = System.IO.Path.GetDirectoryName(f.PhysicalName) ?? @"C:\Data";
            string snap = System.IO.Path.Combine(dir, $"{snapshotName}_{f.LogicalName}.ss");
            return $"(NAME = N'{f.LogicalName}', FILENAME = N'{snap}')";
        }));

        return $@"
            CREATE DATABASE [{snapshotName}]
            ON {onClause}
            AS SNAPSHOT OF [{runDbName}]";
    }

    private static string BuildKillAndDropDdl(string dbName) => $@"
        IF DB_ID(N'{dbName}') IS NOT NULL
        BEGIN
            ALTER DATABASE [{dbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            DROP DATABASE [{dbName}];
        END";

    private static string Sanitize(string label)
        => System.Text.RegularExpressions.Regex.Replace(label, @"[^a-zA-Z0-9_]", "_");
}