using Dapper;
using FrameworkDAL.Query.DBLevelSetting;
using GB5Shared.Connection;
using GB5Shared.DBQueryConverter;
using GB5Shared.DTO.Framework.DBLevelSetting;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.Enums.ReportOrchestration;
using GB5Shared.QueryExecutor;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using Npgsql;
using System;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkDAL.CustomCode.DBLevelSetting
{
    public class DBLevelSettingDAL : IDBLevelSettingDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IApplicationConnection _AppConnection;

        public DBLevelSettingDAL(IQueryExecutor QueryExecutor, IApplicationConnection AppConnection)
        {
            _QueryExecutor = QueryExecutor;
            _AppConnection = AppConnection;
        }

        public async Task<string> GetDBLevelSetting(LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = DBLevelSettingQB.GET_DBLEVELSETTING;

                if (LoginDTO.DatabaseType == DBType.PostGre)
                    sql = ConvertSqlToPostgres.ConvertSqlServerToPostgres(sql);

                var parameters = new { clientid = LoginDTO.ClientId };
                var result = (await _QueryExecutor.QueryAsync<DBLevelSettingDTO>(LoginDTO, sql, parameters)).ToList();

                // MCLIENTDETAILS lives only in the GB5System database, never in the
                // tenant DB — fetch it as a separate hop (same pattern as
                // PartnerBrandDAL.GetResolvedBrandForClient) instead of joining it
                // into the tenant-DB query above.
                string systemConnStr = await _AppConnection.Gb5SystemConnectionString();
                using var systemConn = new SqlConnection(systemConnStr);
                var licenseInfo = await systemConn.QueryFirstOrDefaultAsync<DBLevelSettingDTO>(
                    DBLevelSettingQB.GET_CLIENT_LICENSE_INFO, new { clientid = LoginDTO.ClientId });

                if (licenseInfo != null)
                {
                    foreach (var row in result)
                    {
                        row.DBLevelSettingWebUserLicenses = licenseInfo.DBLevelSettingWebUserLicenses;
                        row.DBLevelSettingHostingValidTill = licenseInfo.DBLevelSettingHostingValidTill;
                        row.DBLevelSettingLicenceValidTill = licenseInfo.DBLevelSettingLicenceValidTill;
                        row.DBLevelSettingSupportValidTill = licenseInfo.DBLevelSettingSupportValidTill;
                    }
                }

                Json = JsonConvert.SerializeObject(result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<int> UpdateBuildVersionOnConnection(
            string connectionString, byte databaseType, string buildVersion, CancellationToken ct)
        {
            using System.Data.Common.DbConnection conn = (DatabaseProviderType)databaseType switch
            {
                DatabaseProviderType.SqlServer => new SqlConnection(connectionString),
                DatabaseProviderType.PostgreSql => new NpgsqlConnection(connectionString),
                _ => throw new NotSupportedException(
                    $"Provider {(DatabaseProviderType)databaseType} is not supported for MDBLevelSetting writes."),
            };

            var command = new CommandDefinition(
                DBLevelSettingQB.UPDATE_BUILD_VERSION,
                new { BuildVersion = buildVersion },
                cancellationToken: ct);

            return await conn.ExecuteAsync(command).ConfigureAwait(false);
        }
    }
}