using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Serialization;
using Dapper;
using FrameworkDAL.DTO.Version;
using FrameworkDAL.Query.DBLevelSetting;
using FrameworkDAL.Query.Module;
using FrameworkDAL.Query.UserDetail;
using GB5Shared.Connection;
using GB5Shared.DBQueryConverter;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.Query.FrameWork.DbConnection;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkDAL.CustomCode.Version
{
    public class VersionDAL : IVersionDAL
    { 
        private readonly IQueryExecutor _queryExecutor;
        private readonly IOptionsSnapshot<Gb5SystemDTO> _DataBaseDTO;
        private readonly IApplicationConnection _applicationConnection;

        public VersionDAL(IQueryExecutor queryExecutor, IApplicationConnection applicationConnection, IOptionsSnapshot<Gb5SystemDTO> DataBaseDTO)
        {
            _queryExecutor = queryExecutor;
            _applicationConnection = applicationConnection;
            _DataBaseDTO = DataBaseDTO;
        }
        public async Task<IEnumerable<VersionDTO>> VersionCheck(string ConnectionName)
        {
            try
            {
                // Resolved once up front — same value regardless of the match/lower/higher outcome below.
                int partnerProductId = await ResolvePartnerProductIdByConnectionName(ConnectionName);

                string sql = DBLevelSettingQB.GET_DBLEVEL_AND_APPLICABLE_APP_VERSIONS;
                var databaseType = _DataBaseDTO.Value.DataBaseType;
                if (databaseType == DBTYPE.POSTGRESQL)
                {
                    sql = ConvertSqlToPostgres.ConvertSqlServerToPostgres(sql);
                }
                IEnumerable<VersionDTO> candidates = await _queryExecutor.QueryAsync<VersionDTO>(ConnectionName, sql, null!);

                if (candidates == null || !candidates.Any())
                {
                    return Enumerable.Empty<VersionDTO>();
                }

                // MAppVersion can legitimately have more than one row with ISAPPLICABLE=0
                // at once (e.g. a dual-version rollout window). Each candidate is compared
                // against DBVersionNumber independently and returned as its own row, rather
                // than collapsing to a single "canonical" row — callers rely on getting one
                // MatchStatus per applicable app version.
                var results = candidates
                    .OrderByDescending(c => c.AppVersionNumber, Comparer<string>.Create(CompareVersionStrings))
                    .ToList();

                foreach (var current in results)
                {
                    int cmp = CompareVersionStrings(current.DBVersionNumber, current.AppVersionNumber);
                    current.MatchStatus = cmp == 0 ? 0 : (cmp < 0 ? 1 : 2);
                    current.Message = cmp == 0
                        ? "Matching Version between Database and Application"
                        : cmp < 0
                            ? "Lower Version in Database compared to Application. Need to synchronize the Database"
                            : "Higher Version in Database compared to Application. Need to upgrade the application";
                    current.GB5BaseUri = current.SecondServiceBaseURL;
                    current.GB5Enabled = current.IsApplicable == "0" ? "Y" : "N";
                    current.IsSecondEnabled = current.IsSecondApplicable == "0" ? "Y" : "N";
                    current.PartnerProductId = partnerProductId;
                }

                return results;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Compares dot-separated version strings (e.g. "4.1.10.48.1" vs "4.1.9.48.1") segment
        // by segment as integers — replaces a plain NVARCHAR </> SQL comparison that sorted
        // lexicographically and would misorder as soon as any segment reached double digits
        // (e.g. "4.1.10" < "4.1.9" as text). Missing/non-numeric trailing segments compare as 0.
        private static int CompareVersionStrings(string? a, string? b)
        {
            var segmentsA = (a ?? string.Empty).Split('.');
            var segmentsB = (b ?? string.Empty).Split('.');
            int length = Math.Max(segmentsA.Length, segmentsB.Length);

            for (int i = 0; i < length; i++)
            {
                int valueA = i < segmentsA.Length && int.TryParse(segmentsA[i], out var pa) ? pa : 0;
                int valueB = i < segmentsB.Length && int.TryParse(segmentsB[i], out var pb) ? pb : 0;
                int result = valueA.CompareTo(valueB);
                if (result != 0) return result;
            }
            return 0;
        }

        // Pre-auth resolution (no ClientId/session available yet) — resolves purely from
        // ConnectionName against GB5System, honoring the MSERVERCONFIG-level override before
        // falling back to MCLIENTDETAILS. Same pattern as PartnerBrandQB.GET_ROUTING_BY_CONNECTION_NAME.
        private async Task<int> ResolvePartnerProductIdByConnectionName(string ConnectionName)
        {
            string sql = ConnectionQB.GET_PARTNERPRODUCTID_BY_CONNECTION_NAME;
            string gb5SystemConnStr = await _applicationConnection.Gb5SystemConnectionString();

            if (_DataBaseDTO.Value.DataBaseType == DBTYPE.POSTGRESQL)
            {
                sql = ConvertSqlToPostgres.ConvertSqlServerToPostgres(sql);
                using var pg = new Npgsql.NpgsqlConnection(gb5SystemConnStr);
                int? pgResult = await pg.QueryFirstOrDefaultAsync<int?>(sql, new { ConnectionName });
                return pgResult ?? -1;
            }

            using var sqlConn = new SqlConnection(gb5SystemConnStr);
            int? result = await sqlConn.QueryFirstOrDefaultAsync<int?>(sql, new { ConnectionName });
            return result ?? -1;
        }

        public async  Task<VersionUrlIdentifierDTO> VersionUrlIdentifier(string ConnectionName)
        {
            VersionUrlIdentifierDTO VersionUrlIdentifierDTO = new VersionUrlIdentifierDTO();
            List<VersionUrlIdentifierServerDTO> VersionUrlIdentifierServerDTOs = new List<VersionUrlIdentifierServerDTO>();
            try
            {
                GB5Trace.Step("resolve-version-url-identifier", new { ConnectionName });

                VersionUrlIdentifierServerDTOs = await GetServerDetailForVersionUrlIdentifier(ConnectionName);
                if (VersionUrlIdentifierServerDTOs.Count == 0)
                {
                    GB5Trace.MarkFailed("no-server-detail-found");
                    throw new MethodNotAllowedException(ErrorResponse.NoServerDetailFoundMessage);
                }
                else if (VersionUrlIdentifierServerDTOs.Count > 1)
                {
                    GB5Trace.MarkFailed("multiple-server-detail-found");
                    throw new MethodNotAllowedException(ErrorResponse.MultipleServerDetailFoundMessage);
                }
                else
                {
                    if (VersionUrlIdentifierServerDTOs[0].IISSERVERID == -1)
                    {
                        GB5Trace.MarkFailed("service-server-not-assigned");
                        throw new MethodNotAllowedException(ErrorResponse.ServiceServerNotAssignedMessage);
                    }

                    if (VersionUrlIdentifierServerDTOs[0].DBSERVERRUNSTATUS != 0)
                    {
                        if (VersionUrlIdentifierServerDTOs[0].DBSERVERRUNSTATUS == 1)
                        {
                            throw new MethodNotAllowedException("Db Server is in maintenance state.Pl try after some time or Contact Administrator..");
                        }
                        if (VersionUrlIdentifierServerDTOs[0].DBSERVERRUNSTATUS == 2)
                        {
                            throw new MethodNotAllowedException("Db Server is in offline state.Pl try after some time or Contact Administrator..");
                        }
                    }
                    if (VersionUrlIdentifierServerDTOs[0].IISSERVERRUNSTATUS != 0)
                    {
                        VersionUrlIdentifierDTO.VersionBaseUrl = VersionUrlIdentifierServerDTOs[0].SECONDORYBASEURL;
                    }
                    else
                    {
                        VersionUrlIdentifierDTO.VersionBaseUrl = VersionUrlIdentifierServerDTOs[0].PRIMAYBASEURL;

                    }
                }

                VersionUrlIdentifierDTO.PartnerProductId = await ResolvePartnerProductIdByConnectionName(ConnectionName);
                return VersionUrlIdentifierDTO;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                VersionUrlIdentifierDTO = null!;
            }
        }
        private async Task<List<VersionUrlIdentifierServerDTO>> GetServerDetailForVersionUrlIdentifier(string ConnectionName)
        {
            List<VersionUrlIdentifierServerDTO> VersionUrlIdentifierServerDTOs = new List<VersionUrlIdentifierServerDTO>();
            try
            {
                string Sql = ConnectionQB.LOAD_VERSION_IDENFIFER_SERVER_BASED_ON_CONNECTION_SQL;
                var databaseConfig = _DataBaseDTO.Value;
                int DatabaseType = databaseConfig.DataBaseType;
                if (DatabaseType == DBTYPE.SQL!)
                {
                    // Parameterized via Dapper — was previously a raw SqlCommand built with
                    // Sql.Replace(":connectionname", ConnectionName), a live SQL-injection
                    // surface since ConnectionName comes directly from the login form.
                    string Connection = await _applicationConnection.Gb5SystemConnectionString();
                    await using var sqlConn = new SqlConnection(Connection);
                    await sqlConn.OpenAsync();
                    var rows = await sqlConn.QueryAsync<VersionUrlIdentifierServerDTO>(Sql, new { ConnectionName });
                    VersionUrlIdentifierServerDTOs.AddRange(rows);
                }
                else if (DatabaseType == DBTYPE.POSTGRESQL)
                {
                    Sql = ConvertSqlToPostgres.ConvertSqlServerToPostgres(Sql);
                    string connection = await _applicationConnection.Gb5SystemConnectionString();

                    using (var pg = new Npgsql.NpgsqlConnection(connection))
                    {
                        await pg.OpenAsync();

                        using (var cmd = new Npgsql.NpgsqlCommand(Sql, pg))
                        {
                            cmd.Parameters.AddWithValue("ConnectionName", ConnectionName);
                            using var reader = await cmd.ExecuteReaderAsync();
                            while (await reader.ReadAsync())
                            {
                                VersionUrlIdentifierServerDTO dto = new()
                                {
                                    DBSERVERRUNSTATUS = reader.GetInt32(reader.GetOrdinal("dbserverrunstatus")),
                                    IISSERVERID = reader.GetInt32(reader.GetOrdinal("iisserverid")),
                                    IISSERVERRUNSTATUS = reader.GetInt32(reader.GetOrdinal("iisserverrunstatus")),
                                    PRIMAYBASEURL = reader.GetString(reader.GetOrdinal("primaybaseurl")),
                                    SECONDORYBASEURL = reader.GetString(reader.GetOrdinal("secondorybaseurl"))
                                };

                                VersionUrlIdentifierServerDTOs.Add(dto);
                            }
                        }
                    }
                }
                else
                {
                    throw new MethodNotAllowedException("Databasetype is not supported..Contact your software vendor..");
                }
                return VersionUrlIdentifierServerDTOs;
            }
            catch (Exception)
            {
                throw;
            } 
        } 
    }
}