using Ease.Core.DataAccess;
using Ease.Core.Model;
using Ease.Core.Utility;
using HRM.BO;
using ICSharpCode.SharpZipLib.Zip;
using iTextSharp.text;
using Microsoft.AspNetCore.Http;
using Microsoft.SemanticKernel;
using Microsoft.VisualBasic;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using OpenAI;
using OpenAI.Chat;
using OpenAI.Models;
using Org.BouncyCastle.Asn1.Crmf;
using System;
using System.ClientModel;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Security.Cryptography.Xml;
using System.Text;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading;
using System.Threading.Tasks;



namespace HRM.DA
{
	public class OpenAIService
	{
		private readonly string _encryptedkey;
		private readonly string _apiKey;
		private readonly string _openAiModel;
		private readonly int _maxLimit;
		public OpenAIService(string encryptedkey, string model, int maxLimit)
		{
			_encryptedkey = encryptedkey;
			_apiKey = Ease.Core.Utility.Global.CipherFunctions.Decrypt("CeLiMiTeD.AdMIn", _encryptedkey);
			_openAiModel = model;
			_maxLimit = maxLimit;
		}

		#region Invalid Query Message
		public string returnMessage = "";
		private const string queryMessage = "I'm sorry, I couldn't process your request. Please try rephrasing it. I'm here to help with anything related to payroll or HR tasks.";
		private const string modificationMessage = "I'm Sorry, I can only help with retrieving data. I am not allowed to update or delete any records.";
		#endregion

		#region Schema Generation (Old Version)
		// Aliases
		//sb.AppendLine("\nAlias Mappings:");
		//foreach (var row in columns.AsEnumerable())
		//{
		//	string caption = row["CAPTION"]?.ToString();
		//	string columnName = row["COLUMNNAME"].ToString();
		//	string tableName = row["TABLENAME"].ToString();
		//	if (!string.IsNullOrWhiteSpace(caption) && caption != columnName)
		//	{
		//		sb.AppendLine($"- {tableName}.{columnName} is also called '{caption}'");
		//	}
		//}
		#endregion

		#region Ask SQL Query

		#region Function Summary
		/*
		 * Function Summary & What it Handles:
		 *
		 * 1. Request Limiting:
		 *    - Ensures the total number of OpenAI calls does not exceed the configured maximum.
		 *    - Throws a polite error message if the request limit is reached.
		 *
		 * 2. Timeout Handling:
		 *    - Uses CancellationTokenSource with a configurable timeout (default 60 seconds).
		 *    - Cancels the OpenAI API request if it exceeds the timeout and throws a timeout message.
		 *
		 * 3. Rate Limit Handling & Retry Logic:
		 *    - Detects HTTP 429 status or "rate limit" errors from exceptions.
		 *    - Retries the API call up to 5 times with exponential backoff.
		 *    - Returns a polite "service busy" message if retries are exhausted.
		 *
		 * 4. OpenAI API Call:
		 *    - Builds a dynamic system prompt including schema and chart instructions.
		 *    - Sends the user’s prompt to OpenAI for SQL and chart generation.
		 *    - Extracts and cleans the AI response.
		 *
		 * 5. Response Parsing & Validation:
		 *    - Deserializes the AI response into a structured object.
		 *    - Ensures only SELECT queries are allowed.
		 *    - Rejects invalid or unsafe queries with descriptive errors.
		 *
		 * 6. Async Logging:
		 *    - Logs user prompts, generated SQL, chart details, and metadata asynchronously.
		 *    - Logging errors do not interrupt the main flow.
		 *
		 * 7. SQL Execution & JSON Response:
		 *    - Executes the validated SQL query against the database.
		 *    - Wraps the dataset with chart metadata and returns JSON.
		 *
		 * 8. General Exception Handling:
		 *    - Provides user-friendly messages for timeouts, rate limits, and invalid queries.
		 *    - Retries on transient errors and throws detailed exceptions otherwise.
		 */
		#endregion

		public async Task<string> AskOpenAISqlQueryWithChartAsync(string prompt, int userID, EnumReportGroupType reportGroupType, DateTime nextPayprocessDate = new DateTime(), int timeoutSeconds = 60)
		{
			#region Initialize OpenAI Call Count For Limitation
			int totalOpenAICall = TotalOpenAICall();
			if (totalOpenAICall >= _maxLimit)
			{
				throw new Exception("You have reached the maximum limit of requests. Please try again later.");
			}
			#endregion

			string returnMessage = "An error occurred while processing your request.";
			int maxRetries = 5;
			int delaySeconds = 1;
			for (int attempt = 0; attempt <= maxRetries; attempt++)
			{
				try
				{
					#region Initialize OpenAI Client and Cancellation Token
					using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(timeoutSeconds));
					var client = new ChatClient(model: _openAiModel, apiKey: _apiKey);
					#endregion

					#region Build System Prompt
					string systemPrompt = BuildInitialDescriptionWithChart(reportGroupType) + BuildDynamicSchemaPrompt(reportGroupType, nextPayprocessDate) + BuildInstructionWithChart(reportGroupType);
					#endregion

					//throw new Exception("You have reached the maximum limit of requests. Please try again later.");

					#region Call OpenAI API
					var completion = await client.CompleteChatAsync(
						new ChatMessage[]
						{
							ChatMessage.CreateSystemMessage(systemPrompt),
							ChatMessage.CreateUserMessage(prompt)
						},
						cancellationToken: cts.Token
					);
					#endregion

					#region Process and Store OpenAI Response
					string aiResponse = completion.Value.Content[0].Text.Trim();
					if (string.IsNullOrWhiteSpace(aiResponse))
						throw new Exception("Did not return any response.");

					aiResponse = aiResponse.Replace("```", "").Trim();
					aiResponse = aiResponse.Replace("\n", "\\n").Replace("\r", "\\r");
					OpenAIResponse aiResponseObject = Newtonsoft.Json.JsonConvert.DeserializeObject<OpenAIResponse>(aiResponse);
					#endregion

					#region Validate AI response
					string sql = aiResponseObject.Sql;
					bool isChart = aiResponseObject.IsChart;
					EnumAIChartType chartType = GetAIChartType(aiResponseObject.ChartType);

					if (!string.IsNullOrWhiteSpace(aiResponseObject.Message))
					{
						throw new Exception(aiResponseObject.Message);
					}
					if (!sql.TrimStart().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
						throw new Exception("Only SELECT queries are allowed.");
					#endregion

					#region Save OpenAI Query Log
					_ = Task.Run(() =>
					{
						try
						{
							var logService = new OpenAIQueryLogService();
							logService.Save(new OpenAIQueryLog
							{
								UserId = userID,
								UserPrompt = prompt,
								GeneratedQuery = sql,
								ExecutedAt = DateTime.Now,
								ReportGroup = reportGroupType,
								IsChart = isChart,
								ChartType = GetAIChartType(aiResponseObject.ChartType),
								Title = aiResponseObject.Title,
								IsCommon = false
							});
						}
						catch (Exception logEx)
						{
							Console.WriteLine($"[LogError] OpenAI log failed: {logEx.Message}");
						}
					});
					#endregion

					#region Execute SQL and Build Data
					DataSet data = GetOpenAIData(sql);
					var chartData = BuildOpenAIResponseWithData(data, aiResponseObject);
					return Newtonsoft.Json.JsonConvert.SerializeObject(chartData);
					#endregion
				}
				catch (OperationCanceledException)
				{
					throw new Exception("AI request timed out. Please try again.");
				}
				catch (HttpRequestException httpEx) when (httpEx.Message.Contains("429") || httpEx.Message.Contains("rate limit", StringComparison.OrdinalIgnoreCase))
				{
					if (attempt == maxRetries)
						throw new Exception("Sorry, our AI service is busy now. Please try again after some time.");

					await Task.Delay(TimeSpan.FromSeconds(delaySeconds));
					delaySeconds *= 2;
				}
				catch (Exception ex)
				{
					if (ex.Message.Contains("You've reached our limits of messages", StringComparison.OrdinalIgnoreCase) ||
						ex.Message.Contains("rate limit", StringComparison.OrdinalIgnoreCase))
					{
						if (attempt == maxRetries)
							throw new Exception("Sorry, our AI service is busy now. Please try again after some time.");

						await Task.Delay(TimeSpan.FromSeconds(delaySeconds));
						delaySeconds *= 2;
						continue;
					}
					throw new Exception(returnMessage + ex.Message, ex);
				}
			}
			throw new Exception("Failed to get a response from AI after multiple attempts. Please try again later.");
		}
		#endregion

		#region Build OpenAI Response with Data
		public OpenAIResponseWithData BuildOpenAIResponseWithData(DataSet ds, OpenAIResponse aiResponseObject)
		{
			var table = ds.Tables[0];
			var labels = new List<string>();
			var values = new List<double>();
			if (aiResponseObject.IsChart)
			{
				labels = table.AsEnumerable().Select(r => r[0].ToString()).ToList();
				values = table.AsEnumerable().Select(r => Convert.ToDouble(r[1])).ToList();
			}
			var tableJson = ProcessDataSetToJson(ds);
			return new OpenAIResponseWithData
			{
				IsChart = aiResponseObject.IsChart,
				ChartType = aiResponseObject.ChartType,
				Title = aiResponseObject.Title,
				Labels = labels,
				Values = values,
				TableData = tableJson
			};
		}
		#endregion

		#region Build Initial Description (System Prompt)
		public string BuildInitialDescriptionWithChart(EnumReportGroupType reportGroupType)
		{
			var sb = new StringBuilder();
			sb.AppendLine("You are an expert SQL assistant.");
			sb.AppendLine("Given a user request and the following database schema, your job is to:");
			sb.AppendLine("1.Always generate a valid SQL SELECT queries in Microsoft SQL Server (T-SQL) syntax.");
			sb.AppendLine("2.Always generate SQL in a single line, without extra newlines or unnecessary spaces.");
			sb.AppendLine("3.Never return DELETE, UPDATE, INSERT queries or any data-modifying operation.You are read-only.");
			sb.AppendLine("4.Detect if the user wants a chart/graph.");
			sb.AppendLine("5.Always return only valid JSON following structure:" +
				"{\"isChart\":true|false,\"chartType\":\"pie|bar|line|scatter|area|histogram|waterfall|gantt|radar|activity\",\"title\":\"<title>\",\"sql\":\"<SQL>\",\"message\":\"\"}");
			sb.AppendLine("6.Title must be short, human-readable, based on the user request.");
			sb.AppendLine("7.If the user asks modification deletion or insartion requested, set sql:'' and message:" +
						  "Sorry, I can only help with retrieving data. I am not allowed to update or delete any records.");

			#region Check 
			if (reportGroupType == EnumReportGroupType.Employee)
			{
				sb.AppendLine("8.If the user request is not related to the payroll management system or does not match the provided schema,set \"sql\": \"\" and " +
								"\"message\": \"I can only help with questions related to Employee Information\"");
			}
			if (reportGroupType == EnumReportGroupType.Performance)
			{
				sb.AppendLine("8.If the user request is not related to the payroll management system or does not match the provided schema,set \"sql\": \"\" and " +
								"\"message\": \"I can only help with questions related to Employee Performance\"");
			}
			if (reportGroupType == EnumReportGroupType.Salary)
			{
				sb.AppendLine("8.If the user request is not related to the payroll management system or does not match the provided schema,set \"sql\": \"\" and " +
								"\"message\": \"I can only help with questions related to Employee Salary\"");
			}
			if (reportGroupType == EnumReportGroupType.Training)
			{
				sb.AppendLine("8.If the user request is not related to the payroll management system or does not match the provided schema,set \"sql\": \"\" and " +
								"\"message\": \"I can only help with questions related to Employee Training\"");
			}
			if (reportGroupType == EnumReportGroupType.Attendance)
			{
				sb.AppendLine("8.If the user request is not related to the payroll management system or does not match the provided schema,set \"sql\": \"\" and " +
								"\"message\": \"I can only help with questions related to Employee Attendance\"");
			}
			if (reportGroupType == EnumReportGroupType.Leave)
			{
				sb.AppendLine("8.If the user request is not related to the payroll management system or does not match the provided schema,set \"sql\": \"\" and " +
								"\"message\": \"I can only help with questions related to Employee Leave\"");
			}
			#endregion

			sb.AppendLine("9.Do not return actual data values or any explanations.");
			return sb.ToString();
		}
		#endregion

		#region Dynamic Schema Generation (System Prompt)
		public string BuildDynamicSchemaPrompt(EnumReportGroupType reportGroupType, DateTime nextPayprocessDate)
		{
			TransactionContext tc = null;
			try
			{
				tc = TransactionContext.Begin();
				DataTable columns = GetColumnDefinitions(tc, reportGroupType);
				DataTable relations = GetColumnRelations(tc,reportGroupType);
				DataTable allData = null;
				if (reportGroupType == EnumReportGroupType.Salary || reportGroupType == EnumReportGroupType.Leave)
				{
					allData = GetAllType(tc, reportGroupType);
				}
				tc.End();

				var sb = new StringBuilder();
				sb.AppendLine("Database Schema:");
				var pkColumns = new Dictionary<string, List<string>>();
				var grouped = columns.AsEnumerable().GroupBy(row => row["TABLENAME"].ToString()).OrderBy(g => g.Key);

				#region Table Definitions
				foreach (var tableGroup in grouped)
				{
					string tableName = tableGroup.Key;
					var columnDefs = new List<string>();
					var pks = new List<string>();

					foreach (var row in tableGroup)
					{
						string columnName = row["COLUMNNAME"].ToString();
						string caption = row["CAPTION"]?.ToString();
						string dataType = ((EnumColumnDataType)Convert.ToInt32(row["DATATYPE"])).ToString();
						bool isPK = row.Table.Columns.Contains("IsPrimaryKey") && Convert.ToBoolean(row["IsPrimaryKey"]);
						bool isFK = row.Table.Columns.Contains("IsForeignKey") && Convert.ToBoolean(row["IsForeignKey"]);

						string label = $"{columnName}";
						if (isPK)
						{
							label += "(PK)";
							pks.Add(columnName);
						}
						else if (isFK)
						{
							label += "(FK)";
						}
						label += $" ({dataType})";
						if (!string.IsNullOrWhiteSpace(caption) && caption != columnName)
							label += $" = {caption}";
						columnDefs.Add(label);
					}

					sb.AppendLine($"-{tableName}({string.Join(", ", columnDefs)})");
					if (pks.Any())
						pkColumns[tableName] = pks;
				}
				#endregion

				#region RelationShip Definitions
				if (relations.Rows.Count > 0)
				{
					sb.AppendLine("Relationships:");
					foreach (DataRow rel in relations.Rows)
					{
						string master = rel["MASTERTABLENAME"].ToString();
						string masterCol = rel["MASTERCOLUMN1NAME"].ToString();
						string link = rel["LINKTABLENAME"].ToString();
						string linkCol = rel["LINKCOLUMN1NAME"].ToString();
						sb.AppendLine($"-{link}.{linkCol} → {master}.{masterCol}");
					}
				}
				#endregion

				sb.AppendLine(BuildInitialNoted(reportGroupType, nextPayprocessDate, allData));
				return sb.ToString();
			}
			catch (Exception e)
			{
				#region Handle Exception
				if (tc != null)
					tc.HandleError();
				ExceptionLog.Write(e);
				throw new ServiceException(e.Message, e);
				#endregion
			}
		}
		public string BuildInitialNoted(EnumReportGroupType reportGroupType, DateTime nextPayprocessDate, DataTable allData = null)
		{
			var sb = new StringBuilder();
			sb.AppendLine("Note:");
			sb.AppendLine("-Never include primary key (PK) or Foreign Key(FK) columns in SELECT output. For reference only.");
			if (reportGroupType == EnumReportGroupType.Employee)
			{
				sb.AppendLine("-The status field can have synonyms: 'Live' or 'Active' mean the same thing (Live). 'Discontinue' or 'Inactive' mean the same thing (Discontinue).");
				sb.AppendLine("-Employees with no record in the 'EmployeeSpouse' table are considered Unmarried.");
			}
			if (allData != null && reportGroupType == EnumReportGroupType.Salary)
			{
				if (allData.Columns.Contains("Head"))
				{
					var heads = allData.AsEnumerable()
									   .Select(r => r["Head"]?.ToString())
									   .Where(h => !string.IsNullOrEmpty(h))
									   .Distinct()
									   .ToList();
					if (heads.Any())
					{
						sb.AppendLine($"-The 'Head' column represents different salary components such as: {string.Join(",", heads)}");
					}
				}
				sb.AppendLine($"-Always use SalaryMonth(always last date of month) in the SQL WHERE clause.");
				sb.AppendLine($"-If a user is not specifies any date or year in the request, always use SalaryMonth = {GlobalFunctions.LastDateOfMonth(nextPayprocessDate).ToString("dd MMM yyyy")} in the SQL WHERE clause.");
				sb.AppendLine("-The columns BirthDate, JoiningDate, SalaryMonth should always be returned in the format 'dd MMM yyyy'.");
			}
			if (allData != null && reportGroupType == EnumReportGroupType.Leave)
			{
				if (allData.Columns.Contains("LeaveType"))
				{
					var heads = allData.AsEnumerable()
									   .Select(r => r["LeaveType"]?.ToString())
									   .Where(h => !string.IsNullOrEmpty(h))
									   .Distinct()
									   .ToList();
					if (heads.Any())
					{
						sb.AppendLine($"-The 'LeaveType' column represents different Leave Type such as: {string.Join(",", heads)}");
					}
				}
				sb.AppendLine("-The Columns BirthDate, JoiningDate, FromDate and ToDate should always be returned in the format 'dd MMM yyyy'.");
			}
			if (reportGroupType == EnumReportGroupType.Attendance)
			{
				sb.AppendLine("-The columns BirthDate, JoiningDate and AttendanceDate should always be returned in the format 'dd MMM yyyy'.");
				sb.AppendLine("-The columns InTime and OutTime should always be returned in the format 'hh:mm:ss aa'.");
				sb.AppendLine("-The 'AttendanceStatus' column represents the daily attendance of an employee and may include: 'Present', 'Absent', 'Delay', 'Leave', 'Holiday', 'Weekly Off', 'Out Side Duty', 'Weekly Holiday', 'Compensation', 'Manual', 'Late', 'Early', 'HalfDay', 'LOA'.");
				sb.AppendLine("-The 'LateStatus' column already stores values as 'Yes' (Late) or 'No' (On Time).");
				sb.AppendLine("-The 'WorkDayType' column defines the type of the day: 'Weekly Holiday', 'National Holiday', 'Working Day', 'HartalDay', 'Weekly and National'.");
			}
			if (reportGroupType == EnumReportGroupType.Employee || reportGroupType == EnumReportGroupType.Training)
			{
				sb.AppendLine("-The columns BirthDate, JoiningDate, MarriageDate, EmployementEndDate and NominationDate should always be returned in the format 'dd MMM yyyy'.");
				sb.AppendLine("-The 'EmploymentEndDate' column represents when an employee left the company (retirement, resignation, termination, etc)");
			}
			if (reportGroupType == EnumReportGroupType.Training)
			{
				sb.AppendLine("-The TrainingStatus field can have Enrolled|Completed|Unknown");
				sb.AppendLine("-The columns StartDate,EndDate,BirthDate,JoiningDate,LineManagerBirthDate,LineManagerJoiningDate should always be returned in the format 'dd MMM yyyy'.");
			}
			return sb.ToString();
		}
		#endregion

		#region Build Instruction (System Prompt)
		public string BuildInstructionWithChart(EnumReportGroupType reportGroupType)
		{
			var sb = new StringBuilder();
			sb.AppendLine("Instructions:");
			sb.AppendLine("-Treat any mention of EmployeeID or EmployeeNo in user input as referring to Employee.EmployeeNo.");
			sb.AppendLine("-Use appropriate JOINs based on foreign keys.");
			sb.AppendLine("-If only certain columns are requested, include only those.");
			sb.AppendLine("-Always map user-friendly aliases (e.g., 'Employee No', 'Department Name') to the correct table and column.");
			sb.AppendLine("-If a date condition is mentioned, use WHERE clause accordingly.");
			sb.AppendLine("-Support ordering, limits (TOP/OFFSET), and filtering.");
			return sb.ToString();
		}
		#endregion

		#region OpenAI Previous Query Execution
		public async Task<string> OpenAIPreviousQueryExecution(OpenAIQueryLog oQueryLog)
		{
			try
			{
				oQueryLog.TotalExecution++;

				#region Update OpenAI Query Log Total Execution
				Task.Run(() =>
				{
					try
					{
						var logService = new OpenAIQueryLogService();
						logService.UpdateQueryLogTotalExecution(oQueryLog);
					}
					catch (Exception ex)
					{
						Console.WriteLine($"[LogError] OpenAI log failed: {ex.Message}");
					}
				});
				#endregion

				string sql = oQueryLog.GeneratedQuery;
				DataSet data = GetOpenAIData(sql);

				OpenAIResponse aiResponseObject = new OpenAIResponse
				{
					IsChart = oQueryLog.IsChart,
					ChartType = GetAIChartType(oQueryLog.ChartType),
					Sql = sql,
					Message = string.Empty,
					Title = oQueryLog.Title
				};
				var chartData = BuildOpenAIResponseWithData(data, aiResponseObject);
				return Newtonsoft.Json.JsonConvert.SerializeObject(chartData);
			}
			catch (Exception ex)
			{
				throw new Exception(returnMessage);
			}
		}
		#endregion

		#region Process DataSet to JSON
		public string ProcessDataSetToJson(DataSet OpenAiData)
		{
			string output;
			DataSet information = new DataSet();
			information = OpenAiData;
			var value = (object)information;
			switch (value)
			{
				case null:
					output = "";
					break;
				case DataSet ds:
					output = JsonSerializer.Serialize(ds.Tables.Cast<DataTable>()
						.Select(tbl => new {
							TableName = tbl.TableName,
							Rows = tbl.Rows.Cast<DataRow>()
								.Select(row => tbl.Columns.Cast<DataColumn>()
									.ToDictionary(col => col.ColumnName, col => row[col]))
						}),
						new JsonSerializerOptions { WriteIndented = true }
					);
					break;
				case DataTable dt:
					output = JsonSerializer.Serialize(
						dt.Rows.Cast<DataRow>()
							.Select(row => dt.Columns.Cast<DataColumn>()
								.ToDictionary(col => col.ColumnName, col => row[col])),
						new JsonSerializerOptions { WriteIndented = true }
					);
					break;
				case IEnumerable<object> list:
					output = JsonSerializer.Serialize(list, new JsonSerializerOptions { WriteIndented = true });
					break;
				case string str:
					output = str;
					break;
				default:
					output = JsonSerializer.Serialize(value, new JsonSerializerOptions
					{
						WriteIndented = true,
						IgnoreReadOnlyProperties = true,
						PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
						DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull
					});
					break;
			}
			return output;
		}
		#endregion

		#region Get OpenAI Data (Generate OpenAI's Provided SQL Query)
		public DataSet GetOpenAIData(string sql)
		{
			TransactionContext tc = null;

			DataSet information = new DataSet();
			try
			{
				tc = TransactionContext.Begin();
				information = OpenAIDA.GetOpenAIData(tc, sql);
				tc.End();
			}
			catch (Exception e)
			{
				#region Handle Exception

				if (tc != null)
					tc.HandleError();
				ExceptionLog.Write(e);

				throw new ServiceException(e.Message, e);

				#endregion
			}

			return information;
		}
		#endregion

		#region Get 'Column Definitions' and 'Relations Between Tables'
		public DataTable GetColumnDefinitions(TransactionContext tc, EnumReportGroupType reportGroupType)
		{
			DataTable oColumnDefinition = new DataTable();
			try
			{
				oColumnDefinition = OpenAIDA.GetColumnDefinitions(tc, reportGroupType);
			}
			catch (Exception e)
			{
				#region Handle Exception

				if (tc != null)
					tc.HandleError();
				ExceptionLog.Write(e);

				throw new ServiceException(e.Message, e);

				#endregion
			}
			return oColumnDefinition;
		}
		public async Task<DataTable> GetColumnDefinitions(EnumReportGroupType reportGroupType)
		{
			DataTable oColumnDefinition = new DataTable();
			TransactionContext tc = null;
			try
			{
				tc = TransactionContext.Begin();
				oColumnDefinition = OpenAIDA.GetColumnDefinitions(tc, reportGroupType);
				tc.End();
			}
			catch (Exception e)
			{
				#region Handle Exception

				if (tc != null)
					tc.HandleError();
				ExceptionLog.Write(e);

				throw new ServiceException(e.Message, e);

				#endregion
			}
			return oColumnDefinition;
		}
		public DataTable GetColumnRelations(TransactionContext tc, EnumReportGroupType reportGroupType)
		{
			DataTable oColumnRelation = new DataTable();
			try
			{
				oColumnRelation = OpenAIDA.GetColumnRelations(tc, reportGroupType);
			}
			catch (Exception e)
			{
				#region Handle Exception

				if (tc != null)
					tc.HandleError();
				ExceptionLog.Write(e);

				throw new ServiceException(e.Message, e);

				#endregion
			}
			return oColumnRelation;
		}
		#endregion

		#region Get All Type for (Leave Type & Salary Head)
		public DataTable GetAllType(TransactionContext tc, EnumReportGroupType reportGroupType)
		{
			DataTable oColumnDefinition = new DataTable();
			try
			{
				oColumnDefinition = OpenAIDA.GetAllType(tc, reportGroupType);
			}
			catch (Exception e)
			{
				#region Handle Exception

				if (tc != null)
					tc.HandleError();
				ExceptionLog.Write(e);

				throw new ServiceException(e.Message, e);

				#endregion
			}
			return oColumnDefinition;
		}
		#endregion

		#region Get AI Chart Type
		public EnumAIChartType GetAIChartType(string type)
		{
			switch (type.ToLower())
			{
				case "pie":
					return EnumAIChartType.PieChart;
				case "bar":
					return EnumAIChartType.BarChart;
				case "line":
					return EnumAIChartType.LineGraph;
				case "scatter":
					return EnumAIChartType.ScatterPlot;
				case "area":
					return EnumAIChartType.AreaChart;
				case "histogram":
					return EnumAIChartType.Histogram;
				case "waterfall":
					return EnumAIChartType.WaterfallChart;
				case "gantt":
					return EnumAIChartType.GantChart;
				case "radar":
					return EnumAIChartType.RadarChart;
				case "activity":
					return EnumAIChartType.ActivityDiagram;
				default:
					return EnumAIChartType.None;
			}
		}
		public string GetAIChartType(EnumAIChartType type)
		{
			switch (type)
			{
				case EnumAIChartType.PieChart:
					return "pie";
				case EnumAIChartType.BarChart:
					return "bar";
				case EnumAIChartType.LineGraph:
					return "line";
				case EnumAIChartType.ScatterPlot:
					return "scatter";
				case EnumAIChartType.AreaChart:
					return "area";
				case EnumAIChartType.Histogram:
					return "histogram";
				case EnumAIChartType.WaterfallChart:
					return "waterfall";
				case EnumAIChartType.GantChart:
					return "gantt";
				case EnumAIChartType.RadarChart:
					return "radar";
				case EnumAIChartType.ActivityDiagram:
					return "activity";
				default:
					return "";
			}
		}
		#endregion

		#region Total OpenAI Call
		public int TotalOpenAICall()
		{
			TransactionContext tc = null;
			int? totalCall = 0;
			try
			{
				tc = TransactionContext.Begin();
				DataReader dr = new DataReader(OpenAIDA.TotalOpenAICall(tc));
				if (dr.Read())
				{
					totalCall = dr.GetInt32(0);
				}
				dr.Close();
				tc.End();
			}
			catch (Exception e)
			{
				#region Handle Exception
				if (tc != null)
					tc.HandleError();
				ExceptionLog.Write(e);
				throw new ServiceException(e.Message, e);
				#endregion
			}
			return totalCall ?? 0;
		}
		#endregion
	}
	public class AIServiceUsageLimitException : Exception
	{
		public AIServiceUsageLimitException(string message) : base(message) { }
	}
}