using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using Dapper;
using FrameworkDAL.DTO.Period;
using FrameworkDAL.DTO.ServerConfig;
using FrameworkDAL.DTO.User;
using FrameworkDAL.DTO.UserLogin;
using FrameworkDAL.Query.Port;
using FrameworkDAL.Query.User;
using FrameworkDAL.Query.UserLogin;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.ServerConfig;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.DateConverter;
using GB5Shared.Validation;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkDAL.CustomCode.User
{
    public class UserDAL : IUserDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;
        private readonly IApplicationConnection _AppConnection;

        public UserDAL(IQueryExecutor IQueryExecutor, IValidation IValidation, IApplicationConnection AppConnection)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = IValidation;
            _AppConnection = AppConnection;
        }

        public async Task<string> GetUser(int UserId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = "";
                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = UserQB.GET_USER;
                        break;
                    case DBType.PostGre:
                        sql = UserQB.GET_USERID_PG;
                        break;
                    case DBType.Oracle:
                        sql = UserQB.GET_USER_ORACLE;
                        break;
                    case DBType.MySQL:
                        sql = UserQB.GET_USER_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }
                // clientid tenant filter added 2026-09-09 — GET_USER/GET_USERID_PG previously had
                // none at all (Authentication_Analysis.md CRITICAL-8), letting an authenticated
                // user from tenant A read any tenant B's full MUSER row (password hash,
                // security-question answer, MFA secret) just by guessing a UserId.
                var parameters = new { userid = UserId, clientid = LoginDTO.ClientId };
                UserDTO UserDTOs = await _QueryExecutor.QuerySingleAsync<UserDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(UserDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveUser(UserDTO UserDTO, LoginDTO LoginDTO, DbTransaction? tx = null)
        {
            try
            {
                string sql = "";
                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = UserQB.SAVE_USER;
                        break;
                    case DBType.PostGre:
                        sql = UserQB.SAVE_USER_PG;
                        break;
                    case DBType.Oracle:
                        sql = UserQB.SAVE_USER_ORACLE;
                        break;
                    case DBType.MySQL:
                        sql = UserQB.SAVE_USER_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, UserDTO, tx);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateUser(UserDTO UserDTO, LoginDTO LoginDTO, DbTransaction? tx = null)
        {
            try
            {
                string sql = "";
                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = UserQB.UPDATE_USER;
                        break;
                    case DBType.PostGre:
                        sql = UserQB.UPDATE_USER_PG;
                        break;
                    case DBType.Oracle:
                        sql = UserQB.UPDATE_USER_ORACLE;
                        break;
                    case DBType.MySQL:
                        sql = UserQB.UPDATE_USER_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, UserDTO, tx);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteUser(int UserId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = "";
                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = UserQB.DELETE_USER;
                        break;
                    case DBType.PostGre:
                        sql = UserQB.DELETE_USER_PG;
                        break;
                    case DBType.Oracle:
                        sql = UserQB.DELETE_USER_ORACLE;
                        break;
                    case DBType.MySQL:
                        sql = UserQB.DELETE_USER_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }
                var Parameters = new { userid = UserId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, Parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> CheckAdminRights(LoginDTO LoginDTO, ServerConfigDTO ServerConfigDTO)
        {
            try
            {
                string query = UserQB.CHECK_ADMIN_RIGHTS;

                var parameters = new
                {
                    code = LoginDTO.UserCode,
                    clientid = ServerConfigDTO.ClientId
                };

                IEnumerable<int> result = await _QueryExecutor.QueryAsync<int>(LoginDTO, query, parameters);

                return result.FirstOrDefault(); // Returns 0 if no result
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> GetUserCountByCode(string UserCode, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new { code = UserCode, clientid = LoginDTO.ClientId };
                IEnumerable<int> result = await _QueryExecutor.QueryAsync<int>(LoginDTO, UserQB.CHECK_USER_EXISTS_BY_CODE, parameters);
                return result.FirstOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
        }


        public async Task<string> GetUserLogin(LoginDTO LoginDTO)
        {
            try
            {
                if (LoginDTO.UserId == 0)
                    throw new Exception("Invalid input: UserId Not Selected!!");

                string userQuery = null!;
                string getUserPeriodQuery = null!;
                string allPeriodQuery = null!;

                object userQueryParam = null!;
                object getUserPeriodParam = null!;

                // 1️⃣ Set queries based on database type
                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        userQuery = UserLoginQB.GET_USERLOGINID_SQL;
                        getUserPeriodQuery = UserLoginQB.GET_USER_NO_OF_READ_PERIOD_SQL;
                        allPeriodQuery = UserLoginQB.GET_ALL_PERIOD_SQL;
                        break;
                    case DBType.PostGre:
                        userQuery = UserLoginQB.GET_USERLOGINID_PG;
                        getUserPeriodQuery = UserLoginQB.GET_USER_NO_OF_READ_PERIOD_PG;
                        allPeriodQuery = UserLoginQB.GET_ALL_PERIOD_PG;
                        break;
                    case DBType.Oracle:
                        userQuery = UserLoginQB.GET_USERLOGINID_ORACLE;
                        getUserPeriodQuery = UserLoginQB.GET_USER_NO_OF_READ_PERIOD_ORACLE;
                        allPeriodQuery = UserLoginQB.GET_ALL_PERIOD_ORACLE;
                        break;
                    case DBType.MySQL:
                        userQuery = UserLoginQB.GET_USERLOGINID_MYSQL;
                        getUserPeriodQuery = UserLoginQB.GET_USER_NO_OF_READ_PERIOD_MYSQL;
                        allPeriodQuery = UserLoginQB.GET_ALL_PERIOD_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }

                // 2️⃣ Resolve PartnerProductId from GB5System (MCLIENTDETAILS/MSERVERCONFIG
                // live only there, never in the tenant DB) before building the tenant-DB
                // parameters. LoginDTO.DatabaseName carries the CONNECTIONNAME the user
                // logged in through (set from the "ConnectionName" header at login time —
                // see AuthenticationBLL.GetLoginDetail), so an MSERVERCONFIG-level override
                // for that connection wins over the ClientId-level MCLIENTDETAILS default.
                string systemConnStr = await _AppConnection.Gb5SystemConnectionString();
                using var systemConn = new SqlConnection(systemConnStr);
                var routing = await systemConn.QueryFirstOrDefaultAsync(
                    UserLoginQB.GET_PARTNERPRODUCTID_BY_CLIENT_ID,
                    new { ClientId = LoginDTO.ClientId, ConnectionName = LoginDTO.DatabaseName });
                int partnerProductId = routing != null ? (int)routing.PartnerProductId : -1;

                userQueryParam = new
                {
                    userid = LoginDTO.UserId,
                    baseuri = LoginDTO.BaseUri,
                    clientid = LoginDTO.ClientId,
                    partnerproductid = partnerProductId
                };

                getUserPeriodParam = new
                {
                    userId = LoginDTO.UserId,
                    OuId = LoginDTO.WorkOUId
                };

                // 3️⃣ Fetch single user
                UserLoginDTO user = await _QueryExecutor.QuerySingleAsync<UserLoginDTO>(LoginDTO, userQuery, userQueryParam);

                if (user == null)
                    throw new MethodNotAllowedException("No user found for the given UserId.");

                // 4️⃣ Fetch NoOfReadPeriod
                IList<int> res = (await _QueryExecutor.QueryAsync<int>(LoginDTO, getUserPeriodQuery, getUserPeriodParam)).ToList();
                if (res.Count == 0)
                    throw new MethodNotAllowedException("There is no record found for user in user access right.");

                int noOfReadPeriod = res[0];

                // 5️⃣ Set ApplicablePeriodFrom
                if (noOfReadPeriod == 0)
                {
                    user.ApplicablePeriodFrom = "01-Jan-1899";
                }
                else
                {
                    IList<PeriodDTO> periodDTOs = (await _QueryExecutor.QueryAsync<PeriodDTO>(LoginDTO, allPeriodQuery, new { clientid = LoginDTO.ClientId })).ToList();

                    if (periodDTOs.Count == 0)
                        throw new MethodNotAllowedException("No period created.");

                    if (periodDTOs.Count >= noOfReadPeriod)
                    {
                        user.ApplicablePeriodFrom = periodDTOs[noOfReadPeriod - 1].PeriodFromDate.ToString("dd-MMM-yyyy");
                    }
                    else
                    {
                        user.ApplicablePeriodFrom = "01-Jan-1899";
                    }
                }

                // 6️⃣ Serialize single object to JSON
                var options = new JsonSerializerOptions
                {
                    PropertyNamingPolicy = null
                };
                options.AddGB5Converters();

                // Serialize to JSON string
                string json = System.Text.Json.JsonSerializer.Serialize(user, options);
                //string jsonResult = JsonConvert.SerializeObject(user, Formatting.None);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListUser(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = UserQB.GET_SELECTLIST_USER;
                var Result = await _QueryExecutor.QueryAsync<UserPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetUserSettingDetail(int UserId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoginDTO.DatabaseType switch
                {
                    DBType.SQL     => UserQB.GET_USER_SETTING_DETAIL,
                    DBType.PostGre => UserQB.GET_USER_SETTING_DETAIL_PG,
                    _              => throw new Exception("Unsupported database type")
                };
                var parameters = new { userid = UserId };
                // GB4's UserSettingDetail returns IList<UserSettingDTO> (array-wrapped even for one row) —
                // mirrored here so GB5 clients built against the GB4 shape don't break.
                IEnumerable<UserSettingDTO> result = await _QueryExecutor.QueryAsync<UserSettingDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> UpdateUserSettings(UpdateUserSettingsDTO UpdateUserSettingsDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoginDTO.DatabaseType switch
                {
                    DBType.SQL     => UserQB.UPDATE_USER_SETTINGS,
                    DBType.PostGre => UserQB.UPDATE_USER_SETTINGS_PG,
                    _              => throw new Exception("Unsupported database type")
                };
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, UpdateUserSettingsDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateUserTheme(int ThemeOption, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoginDTO.DatabaseType switch
                {
                    DBType.SQL     => UserQB.UPDATE_USER_THEME,
                    DBType.PostGre => UserQB.UPDATE_USER_THEME_PG,
                    _              => throw new Exception("Unsupported database type")
                };
                var parameters = new { ThemeOption, UserId = LoginDTO.UserId };
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameters);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateUserThemeAndMode(int ThemeOption, int ThemeMode, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoginDTO.DatabaseType switch
                {
                    DBType.SQL     => UserQB.UPDATE_USER_THEME_AND_MODE,
                    DBType.PostGre => UserQB.UPDATE_USER_THEME_AND_MODE_PG,
                    _              => throw new Exception("Unsupported database type")
                };
                var parameters = new { ThemeOption, ThemeMode, UserId = LoginDTO.UserId };
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameters);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }
    }
}