namespace VECV_WebApi.Models.PreClosure
{

  #region Namespaces

  using DBHelper;
  using LoggingHelper;
  using Newtonsoft.Json;
  using Newtonsoft.Json.Linq;
  using Npgsql;
  using RestSharp;
  using System;
  using System.Collections.Generic;
  using System.Configuration;
  using System.Data;
  using System.Data.Entity.Infrastructure;
  using System.Diagnostics;
  using System.Globalization;
  using System.IO;
  using System.Linq;
  using System.Net;
  using System.Net.Http;
  using System.Net.Http.Headers;
  using System.Net.Mail;
  using System.Net.Security;
  using System.Security.Cryptography;
  using System.Security.Cryptography.X509Certificates;
  using System.Text;
  using System.Threading.Tasks;
  using System.Web;
  using System.Web.Script.Serialization;
  using System.Xml;
  using System.Xml.Linq;
  using VECV_WebApi.Common;
  using VECV_WebApi.Models.Authorization;
  using VECV_WebApi.Models.Customer;
  using VECV_WebApi.Models.EmailServices;
  using VECV_WebApi.Models.ServiceEngineer;
  using VECV_WebApi.Models.Ticket;
  using VECV_WebApi.Models.Tracking;
  using VECV_WebApi.Models.Util;
  using VECV_WebApi.Models.Vehicle;

  #endregion

  #region Repository Class

  /// <summary>
  /// This class contain method for ticket manipulation
  /// </summary>
  public class PreClosureRepository
  {  
    #region Global Variables

    /// <summary>
    /// making object of LoggingUtility class available to this class
    /// </summary>
    LoggingUtility objLog = new LoggingUtility();

    PushData UdanPushData = new PushData();

    /// <summary>
    /// making the data-log file path available to this class
    /// </summary>
    string path = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["PathLog"]);

    /// <summary>
    /// making data log file path available to this class
    /// </summary>
    string logtf = (ConfigurationManager.AppSettings["Log"]);

    /// <summary>
    /// making error log file path available to this class
    /// </summary>
    string errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]);

    /// <summary>
    /// making the Database connection string available to this class
    /// </summary>
    private string _connStr;


    /// <summary>
    /// making Authentication Repository object available to this class
    /// </summary>
    AuthenticationRepository objAuthorization;

    #endregion

    #region Contructors

    /// <summary>
    /// Default constructor intialize connection string of tracking database 
    /// </summary>
    public PreClosureRepository(string connString)
    {
      this._connStr = connString;
    }

    #endregion

    #region API Methods

    public string InsertCogentComment(CogentDataModel model)
    {
      string strmessage = "";
      try
      {
        objLog.ErrorLogFile("InsertCogentReamrks", model.Remarks, path, errorlogtf);
        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
        nSqlParam[0] = new NpgsqlParameter("inticket_id", model.ticket_number);
        nSqlParam[1] = new NpgsqlParameter("inremarks", model.Remarks);
        DataSet ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_Cogent_Preclosure_remarks"], nSqlParam);
     
          strmessage = "success";//ds.Tables[0].Rows[0]["_ticket_id"].ToString();  // getting ticket id alias
                                 // errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]) + ticketIdAlias;
        objLog.ErrorLogFile("InsertCogentSuccess", model.Remarks, path, errorlogtf);
      }
      catch (Exception Ex)
      {
        objLog.ErrorLogFile("InsertCogentComment", Ex.Message, path, errorlogtf);
      }
      return strmessage;
    }
    public string InserKAMData(List<PreClosureModel> records)
    {
      string strsuccess = "";
      try
      {

        NpgsqlParameter[] nSqlParamdel = new NpgsqlParameter[0];

        DataSet ds1 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_delete_kam_details"], nSqlParamdel);

                //Added by mayuri khandelwal on 25 Jul 2025

                string jsonRecords = JsonConvert.SerializeObject(records);
                //        foreach (var c in records)
                //{

                //  NpgsqlParameter[] nSqlParam = new NpgsqlParameter[6];
                //  nSqlParam[0] = new NpgsqlParameter("inChassis_No", c.Chassis_No);
                //  nSqlParam[1] = new NpgsqlParameter("inFleet_Manager_Name", c.Fleet_Manager_Name);
                //  nSqlParam[2] = new NpgsqlParameter("inFleet_Manager_Number", c.Fleet_Manager_Number);
                //  nSqlParam[3] = new NpgsqlParameter("inReg_No", c.Reg_No);
                //  nSqlParam[4] = new NpgsqlParameter("inVECV_KAM_Manager", c.VECV_KAM_Manager);
                //  nSqlParam[5] = new NpgsqlParameter("inVECV_KAM_Manager_Contact_No", c.VECV_KAM_Manager_Contact_No);

                //  DataSet ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_insert_kam_details"], nSqlParam);

                //}
                NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                nSqlParam[0] = new NpgsqlParameter("inRecordsJson", jsonRecords);

                DataSet ds = NpgSqlHelper.ExecuteDataset(
                    _connStr,
                    CommandType.StoredProcedure,
                    ConfigurationManager.AppSettings["usp_insert_kam_details_bulk"],
                    nSqlParam
                );



                return strsuccess;
      }
      catch (Exception ex)
      {
        return "error";
      }
    }
    private string ConvertToCsv(List<PreClosureModel> records)
    {
      var sb = new StringBuilder();
      foreach (var r in records)
      {
        string chassis = EscapeCsv(r.Chassis_No);
        string reg = EscapeCsv(r.Reg_No);
        string kam = EscapeCsv(r.VECV_KAM_Manager);
        string kamContact = EscapeCsv(r.VECV_KAM_Manager_Contact_No);
        string fleet = EscapeCsv(r.Fleet_Manager_Name);
        string fleetNo = EscapeCsv(r.Fleet_Manager_Number);
        string status = r.Status.ToString();

        sb.AppendLine($"{chassis},{reg},{kam},{kamContact},{fleet},{fleetNo},{status}");
      }
      return sb.ToString();
    }

    private string EscapeCsv(string input)
    {
      if (string.IsNullOrEmpty(input))
        return "";

      if (input.Contains(",") || input.Contains("\"") || input.Contains("\n"))
      {
        input = input.Replace("\"", "\"\"");
        return $"\"{input}\"";
      }

      return input;
    }
    #endregion
  }
  #endregion
}