using System.Linq;
using System.Text.Json;
using AnalyticsDAL.DTO.DataSource;
using AnalyticsDAL.Query.DataSource;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AnalyticsDAL.CustomCode.DataSource
{
    public class DataSourceDAL : IDataSourceDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public DataSourceDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<string> GetDataSource(int dataSourceId, LoginDTO login, CancellationToken ct)
        {
            var dto = await _queryExecutor.QuerySingleAsync<DataSourceDTO>(
                login,
                DataSourceQB.GET_DATASOURCE,
                new { DataSourceId = dataSourceId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            return JsonSerializer.Serialize(dto);
        }

        public async Task<string> GetSelectListDataSource(LoginDTO login, CancellationToken ct)
        {
            var list = await _queryExecutor.QueryAsync<DataSourcePicklistDTO>(
                login,
                DataSourceQB.GET_SELECTLIST_DATASOURCE,
                new { TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            return JsonSerializer.Serialize(list);
        }

        public async Task<int> SaveDataSource(DataSourceDTO dto, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login,
                DataSourceQB.SAVE_DATASOURCE,
                new
                {
                    dto.DataSourceId, dto.DataSourceCode, dto.DataSourceName,
                    dto.DataSourceType, dto.ConnectionType, dto.HostName, dto.Port,
                    dto.DatabaseName, dto.SchemaName, dto.CredentialRef,
                    dto.RefreshType, dto.AccessMode, dto.WorkspaceId, dto.Remarks,
                    dto.SwClientDatabaseId,
                    dto.SortOrder, dto.SourceType,
                    TenantId     = login.ClientId,
                    CreatedById  = login.UserId,
                    ModifiedById = login.UserId
                },
                cancellationToken: ct).ConfigureAwait(false);

            return dto.DataSourceId;
        }

        public async Task UpdateDataSource(DataSourceDTO dto, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login,
                DataSourceQB.UPDATE_DATASOURCE,
                new
                {
                    dto.DataSourceId, dto.DataSourceCode, dto.DataSourceName,
                    dto.DataSourceType, dto.ConnectionType, dto.HostName, dto.Port,
                    dto.DatabaseName, dto.SchemaName, dto.CredentialRef,
                    dto.RefreshType, dto.AccessMode, dto.WorkspaceId, dto.Remarks,
                    dto.SwClientDatabaseId,
                    dto.SortOrder, dto.Status,
                    TenantId     = login.ClientId,
                    ModifiedById = login.UserId
                },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task UpdateValidationStatus(int dataSourceId, byte isValidated, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login,
                DataSourceQB.UPDATE_VALIDATION_STATUS,
                new { DataSourceId = dataSourceId, IsValidated = isValidated, TenantId = login.ClientId, ModifiedById = login.UserId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task DeleteDataSource(int dataSourceId, LoginDTO login, CancellationToken ct)
        {
            await _queryExecutor.ExecuteAsync(
                login,
                DataSourceQB.DELETE_DATASOURCE,
                new { DataSourceId = dataSourceId, TenantId = login.ClientId, ModifiedById = login.UserId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<bool> IsDuplicateCode(string code, int excludeId, LoginDTO login, CancellationToken ct)
        {
            var count = await _queryExecutor.ExecuteScalarAsync<int>(
                login,
                DataSourceQB.CHECK_DUPLICATE_CODE,
                new { DataSourceCode = code, TenantId = login.ClientId, DataSourceId = excludeId },
                cancellationToken: ct).ConfigureAwait(false);

            return count > 0;
        }

        public async Task<int?> GetDataSourceTenantId(int dataSourceId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<int>(
                login,
                DataSourceQB.GET_DATASOURCE_TENANTID,
                new { DataSourceId = dataSourceId },
                cancellationToken: ct).ConfigureAwait(false);

            return rows.Select(r => (int?)r).FirstOrDefault();
        }

        public async Task<int?> GetDataSourceIdByCode(string dataSourceCode, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<int>(
                login,
                DataSourceQB.GET_DATASOURCE_ID_BY_CODE,
                new { DataSourceCode = dataSourceCode },
                cancellationToken: ct).ConfigureAwait(false);

            return rows.Select(r => (int?)r).FirstOrDefault();
        }
    }
}