using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Query.FrameWork.User;
using FrameworkDAL.DTO.User;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkDAL.CustomCode.Authentication
{
    public class AuthenticationDAL : IAuthenticationDAL
    {
        private readonly IApplicationConnection _AppConnection;
        private readonly IQueryExecutor _queryExecutor;

        public AuthenticationDAL(IApplicationConnection appConnection, IQueryExecutor queryExecutor)
        {
            _AppConnection = appConnection;
            _queryExecutor = queryExecutor;
        }

        public async Task<UserDTO> GetUser(string UserCode, LoginDTO LoginDTO)
        {
            try
            {
                string QUERY = "";
                if (LoginDTO.DatabaseType == DBType.SQL)
                {
                    QUERY = UserQB.USER_DETAIL;
                }
                else
                {
                    QUERY = UserQB.PG_USER_DETAIL;
                }
                var parameters = new
                {
                    usercode = UserCode,
                    clientid = LoginDTO.ClientId,
                    developerid = LoginDTO.DeveloperId
                };

                var user = await _queryExecutor.QuerySingleAsync<UserDTO>(
           LoginDTO,
           QUERY,
           parameters
       );

                if (user == null)
                    throw new Exception($"No user found with code: {UserCode}");

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


        public async Task<UserDTO> GetUserByGMail(string PrimaryMail, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = UserQB.GET_USER_DETAIL_BY_GMAIL;
                var parameters = new
                {
                    primarymail = PrimaryMail,
                    clientid = LoginDTO.ClientId,
                    developerid = LoginDTO.DeveloperId
                };
                var user = await _queryExecutor.QuerySingleAsync<UserDTO>(
                           LoginDTO,
                           Sql,
                           parameters
                       );

                if (user == null)
                    throw new Exception($"No user found with email: {PrimaryMail}");

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

        public async Task<int> DatabaseOffset(LoginDTO LoginDTO, byte DatabaseType)
        {
            try
            {
                int dbOffset = 0;
                string Sql = "";

                if (DatabaseType == DBType.SQL)
                {
                    Sql = "SELECT DATEPART(TZ, SYSDATETIMEOFFSET()) AS Dboffset";
                    dbOffset = await _queryExecutor.ExecuteScalarAsync<int>(LoginDTO, Sql);
                }
                else if (DatabaseType == DBType.Oracle)
                {
                    Sql = "SELECT TZ_OFFSET(sessiontimezone) FROM DUAL";
                    string TempOffset = (await _queryExecutor.ExecuteScalarAsync<string>(LoginDTO, Sql) ?? "").Trim();
                    int Final = 0;
                    string[] Split1 = TempOffset.Split(':');
                    if (Convert.ToInt32(Split1[0]) * 60 > 0)
                        Final = Convert.ToInt32(Split1[0]) * 60 + Convert.ToInt32(Split1[1]);
                    else if (Convert.ToInt32(Split1[0]) * 60 < 0)
                        Final = Convert.ToInt32(Split1[0]) * 60 - Convert.ToInt32(Split1[1]);
                    else
                        Final = Convert.ToInt32(Split1[1]);
                    dbOffset = Final;
                }
                else if (DatabaseType == DBType.PostGre)
                {
                    Sql = "SELECT EXTRACT(TIMEZONE FROM now())";
                    int Offset = await _queryExecutor.ExecuteScalarAsync<int>(LoginDTO, Sql);
                    dbOffset = Offset / 60;
                }

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