using System.Data.Common;
using FMDAL.DTO.Device;
using FMDAL.Query.Device;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;

namespace FMDAL.CustomCode.Device
{
    public class FMDeviceDAL : IFMDeviceDAL
    {
        private readonly IQueryExecutor _qe;

        public FMDeviceDAL(IQueryExecutor qe) => _qe = qe;

        public async Task<DeviceSaveDTO?> GetDevice(int deviceId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.GET_DEVICE_SQL,
                DBType.PostGre => FMDeviceQB.GET_DEVICE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            var items = await _qe.QueryAsync<DeviceSaveDTO>(login, sql,
                new { DeviceId = deviceId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            return items.FirstOrDefault();
        }

        public async Task<(IEnumerable<DeviceListItemDTO> Items, int TotalCount)> GetDeviceList(
            int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            var (sql, countSql) = login.DatabaseType switch
            {
                DBType.SQL => (FMDeviceQB.GET_DEVICE_LIST_SQL, FMDeviceQB.GET_DEVICE_LIST_COUNT_SQL),
                DBType.PostGre => (FMDeviceQB.GET_DEVICE_LIST_PG, FMDeviceQB.GET_DEVICE_LIST_COUNT_PG),
                _ => throw new NotSupportedException("Unsupported database type")
            };
            var param = new { TenantId = login.ClientId, FirstNumber = firstNumber, MaxResult = maxResult };
            var items = await _qe.QueryAsync<DeviceListItemDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false);
            var count = await _qe.ExecuteScalarAsync<int>(login, countSql, new { TenantId = login.ClientId }, cancellationToken: ct).ConfigureAwait(false);
            return (items, count);
        }

        public async Task<int> SaveDevice(DeviceSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.SAVE_DEVICE_SQL,
                DBType.PostGre => FMDeviceQB.SAVE_DEVICE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            return await _qe.ExecuteScalarAsync<int>(login, sql, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task UpdateDevice(DeviceSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.UPDATE_DEVICE_SQL,
                DBType.PostGre => FMDeviceQB.UPDATE_DEVICE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task SoftDeleteDevice(int deviceId, int modifiedById, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.SOFT_DELETE_DEVICE_SQL,
                DBType.PostGre => FMDeviceQB.SOFT_DELETE_DEVICE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql,
                new { DeviceId = deviceId, ModifiedById = modifiedById, TenantId = login.ClientId },
                tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task UpdateLastSeen(int deviceId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.UPDATE_LAST_SEEN_SQL,
                DBType.PostGre => FMDeviceQB.UPDATE_LAST_SEEN_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql,
                new { DeviceId = deviceId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<DeviceValidationResultDTO?> ValidateDevice(int deviceId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.VALIDATE_DEVICE_SQL,
                DBType.PostGre => FMDeviceQB.VALIDATE_DEVICE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            var items = await _qe.QueryAsync<DeviceValidationResultDTO>(login, sql,
                new { DeviceId = deviceId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            return items.FirstOrDefault();
        }


        public async Task<string> GetSelectListDeviceType(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = FMDeviceQB.GET_DEVICETYPE_SELECTLIST;

                var Result = await _qe.QueryAsync<DeviceTypePicklistDTO>(
                    LoginDTO,
                    SQL,
                    null!);

                string Json = JsonConvert.SerializeObject(Result);

                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> GetSelectListDevice(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = FMDeviceQB.GET_DEVICE_SELECTLIST;

                var result = await _qe.QueryAsync<DevicePicklistDTO>(
                    LoginDTO,
                    sql,
                    null!);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<DeviceTypeDTO?> GetDeviceType(int deviceTypeId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.GET_DEVICETYPE_SQL,
                DBType.PostGre => FMDeviceQB.GET_DEVICETYPE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            var items = await _qe.QueryAsync<DeviceTypeDTO>(login, sql,
                new { DeviceTypeId = deviceTypeId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            return items.FirstOrDefault();
        }

        public async Task<int> SaveDeviceType(DeviceTypeDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.SAVE_DEVICETYPE_SQL,
                DBType.PostGre => FMDeviceQB.SAVE_DEVICETYPE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            return await _qe.ExecuteScalarAsync<int>(login, sql, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task UpdateDeviceType(DeviceTypeDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.UPDATE_DEVICETYPE_SQL,
                DBType.PostGre => FMDeviceQB.UPDATE_DEVICETYPE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task SoftDeleteDeviceType(int deviceTypeId, int modifiedById, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL => FMDeviceQB.SOFT_DELETE_DEVICETYPE_SQL,
                DBType.PostGre => FMDeviceQB.SOFT_DELETE_DEVICETYPE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql,
                new { DeviceTypeId = deviceTypeId, ModifiedById = modifiedById, TenantId = login.ClientId },
                tx, cancellationToken: ct).ConfigureAwait(false);
        }
    }
}