namespace Terrasoft.Configuration.CreatioToDWHWorklogsIntegrations
{
    using System;
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using Terrasoft.Core;
    using Terrasoft.Core.DB;
    using Terrasoft.Common;
    using Terrasoft.Web.Common;
    using Terrasoft.Core.Entities; 
    using System.Data.SqlClient;
    using System.Collections.Generic;
    using Newtonsoft.Json;

    public class CreatioExportOfWorklogsIntegrations : BaseService
    {
        public string ConnectToDWH(UserConnection userConnection)
		{
			// Define your connection string here (ensure this is secured in actual use)
            string dataSource = Terrasoft.Core.Configuration.SysSettings.GetValue<string>(userConnection, "UsrDWHDataSource",string.Empty);
			string initialCatalog = Terrasoft.Core.Configuration.SysSettings.GetValue<string>(userConnection, "UsrDWHInitialCatalogIntegrations",string.Empty);
			string userId = Terrasoft.Core.Configuration.SysSettings.GetValue<string>(userConnection, "UsrDWHUserID",string.Empty);
			string password = Terrasoft.Core.Configuration.SysSettings.GetValue<string>(userConnection, "UsrDWHPassword",string.Empty);
            string connectionString = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}",dataSource,initialCatalog,userId,password);

            // Dictionary to collect IDs and error messages of unprocessed records
            Dictionary<Guid, string> unprocessedRecords = new Dictionary<Guid, string>();
			
			try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    // Delete existing records from the external table
                    string deleteQuery = "DELETE FROM IntegrationsQA.dbo.Creatio_Stg_UsrWorklogs";
                    using (SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection))
                    {
                        deleteCommand.ExecuteNonQuery();
                    }

                                      // Define the select query
                    var selectQuery = new Select(userConnection)
                                      .Column("UsrWorklogs", "Id")
                                      .Column("UsrWorklogs", "UsrDate")
                                      .Column("UsrWorklogs", "UsrStartTime")
                                      .Column("UsrWorklogs", "UsrDurationHours")
                                      .Column("UsrWorklogs", "UsrDescription")
                                      .Column("UsrWorklocationgroup", "Name").As("WorkLocationGroupName")
                                      .Column("UsrWorklocation", "Name").As("WorkLocationName")
                                      .Column("UsrWorklogCategory", "Name").As("Category")
                                      .Column("UsrWorkcode", "Name").As("WorkCode")
                                      .Column("UsrWorklogStatus", "Name").As("WorklogCPStatus")
                                      .Column("UsrNonBillablereason", "Name").As("NonBillableReason")
                                      .Column("UsrWorklogWorkingHours", "Name").As("WorkingHours")
                                      .Column("Case", "Number").As("Case")
                                      .Column("UsrWorklogs", "UsrRequestTimeId")
                                      .Column("UsrWorklogs", "CreatedOn")
                                      .Column("CreatedBy", "Email").As("CreatedByEmail")
                                      .Column("TimeZone", "Name").As("TimeZoneName")
                                      .From("UsrWorklogs")
                                      .Join(JoinType.LeftOuter, "UsrWorklocationgroup").On("UsrWorklogs", "UsrWorklocationgroupId").IsEqual("UsrWorklocationgroup", "Id")
                                      .Join(JoinType.LeftOuter, "UsrWorklocation").On("UsrWorklogs", "UsrWorklocationId").IsEqual("UsrWorklocation", "Id")
                                      .Join(JoinType.LeftOuter, "UsrWorklogCategory").On("UsrWorklogs", "UsrCategoryId").IsEqual("UsrWorklogCategory", "Id")
                                      .Join(JoinType.LeftOuter, "UsrWorkcode").On("UsrWorklogs", "UsrWorkcodeId").IsEqual("UsrWorkcode", "Id")
                                      .Join(JoinType.LeftOuter, "UsrWorklogStatus").On("UsrWorklogs", "UsrStatusId").IsEqual("UsrWorklogStatus", "Id")
                                      .Join(JoinType.LeftOuter, "UsrNonBillablereason").On("UsrWorklogs", "UsrNonBillablereasonId").IsEqual("UsrNonBillablereason", "Id")
                                      .Join(JoinType.LeftOuter, "UsrWorklogWorkingHours").On("UsrWorklogs", "UsrWorkinghoursId").IsEqual("UsrWorklogWorkingHours", "Id")
                                      .Join(JoinType.LeftOuter, "Case").On("UsrWorklogs", "UsrCaseId").IsEqual("Case", "Id")
                                      .Join(JoinType.LeftOuter, "Contact").As("CreatedBy").On("UsrWorklogs", "CreatedById").IsEqual("CreatedBy", "Id")
                                      .Join(JoinType.LeftOuter, "SysAdminUnit").On("SysAdminUnit", "ContactId").IsEqual("UsrWorklogs", "CreatedById")
                                      .Join(JoinType.LeftOuter, "TimeZone").On("TimeZone", "Code").IsEqual("SysAdminUnit", "TimeZoneId")
                                      as Select;


                    // Execute the query and get the collection of entities
                    using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
                    {
                        using (var dataReader = selectQuery.ExecuteReader(dbExecutor))
                        {
                            while (dataReader.Read())
                            {
                                try
                                {
                                    // Retrieve values from the data reader
                                    var worklogId = dataReader.GetColumnValue<Guid>("Id");
                                    var usrDate = dataReader.GetColumnValue<DateTime>("UsrDate");
                                    var usrStartTime = dataReader.GetColumnValue<DateTime>("UsrStartTime");
                                    var usrDurationHours = dataReader.GetColumnValue<decimal>("UsrDurationHours");
                                    var usrDescription = dataReader.GetColumnValue<string>("UsrDescription");
                                    var workLocationGroupName = dataReader.GetColumnValue<string>("WorkLocationGroupName");
                                    var workLocationName = dataReader.GetColumnValue<string>("WorkLocationName");
                                    var category = dataReader.GetColumnValue<string>("Category");
                                    var workCode = dataReader.GetColumnValue<string>("WorkCode");
                                    var worklogCPStatus = dataReader.GetColumnValue<string>("WorklogCPStatus");
                                    var nonBillableReason = dataReader.GetColumnValue<string>("NonBillableReason");
                                    var workingHours = dataReader.GetColumnValue<string>("WorkingHours");
                                    var caseNumber = dataReader.GetColumnValue<string>("Case");
                                    var usrRequestTimeId = dataReader.GetColumnValue<string>("UsrRequestTimeId");
                                    var createdOn = dataReader.GetColumnValue<DateTime>("CreatedOn");
                                    var createdByEmail = dataReader.GetColumnValue<string>("CreatedByEmail");
                                    var timeZoneName = dataReader.GetColumnValue<string>("TimeZoneName");

                                    // Insert new record
                                    string insertQuery = @"
                                        INSERT INTO IntegrationsQA.dbo.Creatio_Stg_UsrWorklogs (
                                             ID  
                                            , UsrDate  
                                            , UsrStartTime  
                                            , UsrDurationInHours  
                                            , UsrDescription  
                                            , UsrWorkLocationGroup  
                                            , UsrWorkLocation  
                                            , UsrCategory  
                                            , UsrWorkCode  
                                            , UsrNonBillableReason  
                                            , UsrWorkingHours  
                                            , UsrCase  
                                            , UsrRequestTimeId  
                                            , UsrWorklogCPStatus  
                                            , CreatedBy  
                                            , CreatedOn  
                                            , UsrTimeZone
                                        ) VALUES (
                                            @worklogId,
                                            @usrDate,
                                            @usrStartTime,
                                            @usrDurationHours,
                                            @usrDescription,
                                            @workLocationGroupName,
                                            @workLocationName,
                                            @category,
                                            @workCode,
                                            @nonBillableReason,
                                            @workingHours,
                                            @caseNumber,
                                            @usrRequestTimeId,
                                            @worklogCPStatus,
                                            @createdByEmail,
                                            @createdOn,
                                            @timeZoneName
                                        )";
                                    using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection))
                                    {
                                        insertCommand.Parameters.AddWithValue("@worklogId", worklogId);
                                        insertCommand.Parameters.AddWithValue("@usrDate", (object)usrDate ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@usrStartTime", (object)usrStartTime ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@usrDurationHours", (object)usrDurationHours ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@usrDescription", (object)usrDescription ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@workLocationGroupName", (object)workLocationGroupName ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@workLocationName", (object)workLocationName ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@category", (object)category ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@workCode", (object)workCode ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@worklogCPStatus", (object)worklogCPStatus ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@nonBillableReason", (object)nonBillableReason ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@workingHours", (object)workingHours ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@caseNumber", (object)caseNumber ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@usrRequestTimeId", (object)usrRequestTimeId ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@createdOn", (object)createdOn ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@createdByEmail", (object)createdByEmail ?? DBNull.Value);
                                        insertCommand.Parameters.AddWithValue("@timeZoneName", (object)timeZoneName ?? DBNull.Value);
                                        insertCommand.ExecuteNonQuery();
                                    }
                                }
                                catch (Exception ex)
                                {
                                    // Log the error and add the ID to the list of unprocessed records
                                    unprocessedRecords.Add(dataReader.GetColumnValue<Guid>("Id"), ex.Message);
                                }
                            }
                        }
                    }

                    if (unprocessedRecords.Count > 0)
                    {
                        // You can log the errors or return them in the response
                        return $"Worklogs Integrations synchronized with errors: {JsonConvert.SerializeObject(unprocessedRecords)}";
                    }
                    return "Worklogs Integrations synchronized successfully.";
                }
            }
            catch (Exception ex)
            {
                //Log.Error($"Error during synchronization: {ex.Message}");
                return $"Error: {ex.Message}";
            }
			
		}
	}

  }