using Dapper;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using PartnerDAL.DTO.PartnerApiKey;
using PartnerDAL.Query.PartnerApiKey;

namespace PartnerDAL.CustomCode.PartnerApiKey
{
    // TPARTNERAPIKEY lives in GB5System ONLY — it must be centrally resolvable with no
    // ConnectionName/tenant context (an API key alone has to self-identify its partner, unlike
    // TPARTNERBRAND/TPARTNERPRODUCT which can rely on an already-known ConnectionName). Every
    // method here — including the admin-facing CRUD, not just the pre-auth lookups — reads and
    // writes GB5System directly via Gb5SystemConnectionString(), never via the caller's
    // LoginDTO/tenant connection. The one exception is GET_PARTNERID_BY_PARTNERPRODUCTID, which
    // deliberately runs against the caller's own tenant DB (where TPARTNERPRODUCT actually
    // lives) to resolve PartnerId once at save time, before the key row itself is written
    // centrally.
    public class PartnerApiKeyDAL : IPartnerApiKeyDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _validation;
        private readonly IApplicationConnection _appConnection;

        public PartnerApiKeyDAL(IQueryExecutor queryExecutor, IValidation validation, IApplicationConnection appConnection)
        {
            _queryExecutor = queryExecutor;
            _validation = validation;
            _appConnection = appConnection;
        }

        public async Task<string> GetPartnerApiKeyList(int partnerProductId, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                var rows = await conn.QueryAsync<PartnerApiKeyListDTO>(
                    PartnerApiKeyQB.GET_PARTNER_API_KEY_LIST,
                    new { PartnerProductId = partnerProductId }).ConfigureAwait(false);
                return JsonConvert.SerializeObject(rows);
            }
            catch (Exception) { throw; }
        }

        public async Task<PartnerApiKeyListDTO?> GetApiKeyByHash(string hashedKey, LoginDTO loginDTO)
            => await GetApiKeyByHashFromSystemDbAsync(hashedKey, CancellationToken.None).ConfigureAwait(false);

        public async Task<int> SavePartnerApiKey(PartnerApiKeyDTO dto, LoginDTO loginDTO)
        {
            try
            {
                // Hop 1 — resolve PartnerId from the tenant DB (where TPARTNERPRODUCT lives),
                // using the caller's own LoginDTO. Only needed if the caller didn't already
                // supply it.
                if (dto.PartnerId is null or 0)
                {
                    dto.PartnerId = await _queryExecutor.QuerySingleAsync<int?>(
                        loginDTO, PartnerApiKeyQB.GET_PARTNERID_BY_PARTNERPRODUCTID,
                        new { dto.PartnerProductId }).ConfigureAwait(false);
                }

                // Hop 2 — the key row itself is written centrally to GB5System.
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteScalarAsync<int>(
                    PartnerApiKeyQB.SAVE_PARTNER_API_KEY, dto).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.SaveErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> DeletePartnerApiKey(int apiKeyId, int modifiedById, LoginDTO loginDTO)
        {
            try
            {
                string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                await using var conn = new SqlConnection(connStr);
                return await conn.ExecuteAsync(
                    PartnerApiKeyQB.DELETE_PARTNER_API_KEY,
                    new { ApiKeyId = apiKeyId, ModifiedById = modifiedById, ModifiedOn = DateTime.UtcNow }).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, ErrorResponse.DeleteErrorMessage).ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task UpdateApiKeyLastUsed(int apiKeyId, LoginDTO loginDTO)
            => await UpdateApiKeyLastUsedInSystemDbAsync(apiKeyId, CancellationToken.None).ConfigureAwait(false);

        public async Task<PartnerApiKeyListDTO?> GetApiKeyByHashFromSystemDbAsync(string hashedKey, CancellationToken ct)
        {
            string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
            await using var conn = new SqlConnection(connStr);
            var rows = await conn.QueryAsync<PartnerApiKeyListDTO>(
                new CommandDefinition(PartnerApiKeyQB.GET_API_KEY_BY_HASH, new { HashedKey = hashedKey }, cancellationToken: ct))
                .ConfigureAwait(false);
            return rows?.FirstOrDefault();
        }

        public async Task UpdateApiKeyLastUsedInSystemDbAsync(int apiKeyId, CancellationToken ct)
        {
            string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
            await using var conn = new SqlConnection(connStr);
            await conn.ExecuteAsync(
                new CommandDefinition(PartnerApiKeyQB.UPDATE_API_KEY_LAST_USED, new { ApiKeyId = apiKeyId }, cancellationToken: ct))
                .ConfigureAwait(false);
        }
    }
}