<?php
/**
 * functions file 
 * Auth:AARTI  & VASTVIKTA NISHAD
 * DATE: 16-01-24 
 *  This file is used to fetch the  data
 * Insert Update  or delete the data
 * 
 **/
// Added code for adding audit log on update insert and delete [vastvikta][13-02-2025]
// Add code for logging code
include_once("../../logs/config.php");
include_once("../../logs/logs.php");

include_once __DIR__ . "/../../config/web_mysqlconnect.php";
if($_POST['action'] == 'Submit_category'){
    insert_category();
}
if($_POST['action']=='category_delete'){
    category_delete();
}
if($_POST['action']=='subcategory_delete'){
    subcategory_delete();
}
if($_POST['action']=='Submit_subcategory'){
    insert_subcategory();
}
if($_POST['action']=='province_delete'){
    province_delete();
}
if($_POST['action']=='mail_delete'){
    mail_delete();
}
if($_POST['action']=='sms_delete'){
    sms_delete();
}
if($_POST['action']=='delete_bulletin'){
delete_bulletin();
}
if($_POST['action'] == 'Submit_emailadhoc'){
    $email1 = $_POST['emailadhoc1'] ?? '';
    $email2 = $_POST['emailadhoc2'] ?? '';

    adhoc_mail_update($email1, $email2);
}

if ($_POST['action'] == 'project_delete') {
    $id = isset($_POST['id']) ? $_POST['id'] : 0;
    if (!empty($id)) {
        project_delete($id);
    }
}
if ($_POST['action'] == 'submit_province' || $_POST['action'] == 'update_province') {
    insert_or_update_province();
}
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'])) {
    if ($_POST['action'] === 'village_delete') {
        village_delete();
    }
}
if ($_POST['action'] == 'submit_village' || $_POST['action'] == 'update_village') {
    insert_or_update_village($link);
}
if ($_POST['action'] == 'submit_mail' || $_POST['action'] == 'update_mail') {
    insert_or_update_mail($link);
}
if ($_POST['action'] == 'submit_sms' || $_POST['action'] == 'update_sms') {
    insert_or_update_sms($link);
}
if (isset($_POST['action']) && ($_POST['action'] == 'submit_project' || $_POST['action'] == 'update_project')) {
    insert_or_update_project($link);
}  
if (isset($_POST['action']) && $_POST['action'] == 'assign_user') {
    assign_user($link, $_POST['user'], $_POST['assignto'], $db);
} elseif (isset($_POST['action']) && $_POST['action'] == 'unassign_user') {
    unassign_user($link, $_POST['user'], $_POST['assignto'], $db);
}
if($_POST['action']=='status_delete'){
    status_delete();
}
if ($_POST['action'] == 'submit_status' || $_POST['action'] == 'update_status') {
    insert_or_update_status();
}
if($_POST['action']=='base_delete'){
    base_delete();
}
if ($_POST['action'] == 'submit_base' || $_POST['action'] == 'update_base') {
    handleBaseSubmit();
}
if($_POST['action']=='Submit_escalation'){
    update_escalation();
}
if($_POST['action']=='update_smtp'){
    update_smtp();
}
if($_POST['action']=='update_imap'){
    update_imap();
}
if($_POST['action']=='disposition_delete'){
    disposition_delete();
}
if ($_POST['action'] == 'submit_disposition' || $_POST['action'] == 'update_disposition') {
    insert_or_update_disposition();
}
if($_POST['action'] == 'submit_callbacks'){
    updateCallbacks();
}
if($_POST['action']=='bulk_delete'){
    bulk_delete();
}
if($_POST['action']=='update_bulk_data'){
    update_bulk_data();
}
if($_POST['action'] == 'update_sent_mail'){
    update_sent_mail();
}
if ($_POST['action'] == 'submit_webchat' || $_POST['action'] == 'update_webchat') {
    insert_or_update_webchat($link);
}
if($_POST['action']=='webchat_delete'){
    webchat_delete();
}
if ($_POST['action'] == 'submit_whatsapp' || $_POST['action'] == 'update_whatsapp') {
    insert_or_update_whatsapp($link);
}
if($_POST['action']=='whatsapp_delete'){
    whatsapp_delete();
}
// if($_POST['action']=='spam_mail_delete'){
//     spam_mail_delete();
// }
if($_POST['action']=='fetch_email_rules'){
    echo json_encode(fetchEmailRules($link));
    exit;
}
if ($_POST['action']== 'get_email_rule') {
    echo json_encode(getEmailRule($link));
    exit;
}


if ($_POST['action']== 'save_email_rule') {
    $result = saveEmailRule($link, $_POST);
    echo json_encode($result);  //  This must return valid JSON
    exit;
}
if($_POST['action']=='update_sign'){
    update_mail_sign();
}

// Call Knowledge Base Upload API for Document Upload :: Farhan Akhtar [04-02-2025]
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'Upload_DocumentAPI') {

    header('Content-Type: application/json');

    // Define log file
    $logFile = "UPLOADAPIdebug.log";

	try {
        // Log the received request data for debugging
        // file_put_contents($logFile, "[" . date("Y-m-d H:i:s") . "] Received request: " . print_r($_REQUEST, true) . PHP_EOL, FILE_APPEND);

        // Get description from request :: Farhan Akhtar [04-02-2025]
        $description = isset($_POST['descDoc']) ? trim($_POST['descDoc']) : "";

        // (userid in session) :: Farhan Akhtar [04-02-2025]
        $created_by = $_SESSION['userid']; 
        $category = $_POST['kb_classificationDoc'] ?? "";
        // Sanitize category to prevent injection
        $category = preg_replace('/[^a-zA-Z0-9_-]/', '', $category); // allow only safe characters
        $url = "{$APIUrl}{$category}/documents";

		// Call the API function
		$response = uploadFilesToAPI($url, $_FILES, $description, $created_by, $logFile);
		
		// Ensure valid JSON output
		if (!is_array($response) || empty($response)) {
            $errorMsg = "[" . date("Y-m-d H:i:s") . "] Invalid or empty API response: " . print_r($response, true) . PHP_EOL;
            file_put_contents($logFile, $errorMsg, FILE_APPEND);
            throw new Exception("Empty or invalid API response.");
		}

    
        // Encode response as JSON
        $jsonResponse = json_encode($response, JSON_PRETTY_PRINT);
        if ($jsonResponse === false) {
            throw new Exception("JSON encoding error: " . json_last_error_msg());
        }

        echo $jsonResponse;

	} catch (Exception $e) {
        http_response_code(400);
        
        // Log the exception
        $errorMsg = "[" . date("Y-m-d H:i:s") . "] Exception: " . $e->getMessage() . PHP_EOL;
        file_put_contents($logFile, $errorMsg, FILE_APPEND);
        
        // Return error as JSON response
		echo json_encode(["error" => $e->getMessage()]);
	}

	exit();
}
// function to update tbl connection sent mail details [vastvitka][25-04-2025]
function update_sent_mail() {
    global $db, $link;

    $sent_mail = mysqli_real_escape_string($link, $_POST["sent_mail"]);
    $case_count = (int)$_POST["case_count"];
    $status_active = (int)$_POST['status_active'];

    $sql = "UPDATE $db.tbl_connection SET sent_mail='$sent_mail', case_count='$case_count' , status_active = '$status_active' WHERE I_ID = 2";
    $result = mysqli_query($link, $sql);

    if ($result) {
        echo "Updated Successfully!";
    } else {
        echo "Failed to Update!";
    }

    exit; // stop further execution so only this message is returned to AJAX
}

// Function to upload files to the API :: Farhan Akhtar [04-02-2025]
function uploadFilesToAPI($url, $files, $description, $created_by, $logFile) {
    global $db;
    // Initialize an array to store the API responses
    $responses = [];

    // Loop through each file in the $files array
    foreach ($files['file']['name'] as $index => $name) {
        // Prepare the file data for the API request
        $fileData = [
            'file' => new CURLFile($files['file']['tmp_name'][$index], $files['file']['type'][$index], $name),
            'description' => $description,
            'created_by' => $created_by
        ];

        // Initialize cURL session
        $ch = curl_init();

        // Set the cURL options
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $fileData);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // Adjust based on your SSL requirements
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false); // Adjust based on your SSL requirements

        // Execute the cURL request
        $response = curl_exec($ch);

        // Check for cURL errors
        if (curl_errno($ch)) {
            $errorMessage = "[" . date("Y-m-d H:i:s") . "] cURL Error: " . curl_error($ch) . PHP_EOL;
            file_put_contents($logFile, $errorMessage, FILE_APPEND);
            throw new Exception($url."cURL error: " . curl_error($ch));
        }

        // Close the cURL session
        curl_close($ch);

        // Decode the JSON response
        $response_data = json_decode($response, true);

        // Check if the response is valid
        if (json_last_error() !== JSON_ERROR_NONE) {
            $errorMessage = "[" . date("Y-m-d H:i:s") . "] JSON Error: " . json_last_error_msg() . " | Raw Response: " . $response . PHP_EOL;
            file_put_contents($logFile, $errorMessage, FILE_APPEND);
            throw new Exception("Invalid JSON response from API.");
        }
            // Log audit only on success
            add_audit_log($created_by, 'document_upload', '', "New Document added: $name", $db);
      
        // Add the response to the responses array
        $responses[] = $response_data;
    }
    
    // Return the decoded responses
    return $responses;
}

// Function to format size of uploaded files :: Farhan Akhtar [11-03-2025]
function formatSize($bytes) {
    if ($bytes >= 1048576) {
        return number_format($bytes / 1048576, 2) . ' MB'; // Convert to MB
    } elseif ($bytes >= 1024) {
        return number_format($bytes / 1024, 2) . ' KB'; // Convert to KB
    } else {
        return "$bytes bytes"; // Keep in bytes if small
    }
}

// code for view uploaded files :: Farhan Akhtar [11-03-2025]
if (isset($_POST['action']) && $_POST['action'] === "view_document") {

    header('Content-Type: application/json'); // Ensure JSON response

    // Retrieve action from POST request
    $category = "general"; // Replace with the actual category or get it from the request
    $url = "{$APIUrl}{$category}/documents";
    $fileList = getFilesFromApi($url);

    if (!empty($fileList)) {
        echo json_encode([
            "status" => "success",
            "files" => $fileList
        ]);
    } else {
        echo json_encode([
            "status" => "error",
            "message" => "No records found"
        ]);
    }
}

// Function to get uploaded files :: Farhan Akhtar [11-03-2025]
function getFilesFromApi($url) {
    
    $files = [];

    // Initialize cURL session
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    // Execute cURL request
    $response = curl_exec($ch);

    // Check for errors
    if (curl_errno($ch)) {
        // Handle cURL error
        return $files;
    }

    // Close cURL session
    curl_close($ch);

    // Decode the JSON response
    $data = json_decode($response, true);

    $delete_icon = '<svg xmlns="http://www.w3.org/2000/svg" width="17" height="17" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="feather feather-trash-2 text-danger"><polyline points="3 6 5 6 21 6"></polyline><path d="M19 6v14a2 2 0 0 1-2 2H7a2 2 0 0 1-2-2V6m3 0V4a2 2 0 0 1 2-2h4a2 2 0 0 1 2 2v2"></path><line x1="10" y1="11" x2="10" y2="17"></line><line x1="14" y1="11" x2="14" y2="17"></line></svg>';

    $view_icon = '<svg xmlns="http://www.w3.org/2000/svg" width="17" height="17" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="feather feather-eye text-primary"><path d="M1 12s4-8 11-8 11 8 11 8-4 8-11 8-11-8-11-8z"></path><circle cx="12" cy="12" r="3"></circle></svg>';


    // Process the API response
    if (isset($data['documents']) && is_array($data['documents'])) {
        foreach ($data['documents'] as $document) {
            $files[] = [
                "name" => $document['filename'], // Filename
                "size" => formatSize($document['file_size_mb'] * 1024 * 1024), // Convert MB to bytes for formatting
                "description" => $document['description'], // Description
                "created_by" => getUserName($document['created_by']), // Created by
                "date_uploaded" => date("d-m-Y H:i:s", strtotime($document['date_uploaded'])), // Convert date_uploaded to timestamp
                "action" =>'
                             <a href="javascript:void(0)" class="view-doc" id="'.$document['filename'].'" data-category="'.$document['category'].'">'.$view_icon.'</a>
                             <a href="javascript:void(0)" class="delete-doc" id="'.$document['filename'].'" data-category="'.$document['category'].'">'.      $delete_icon.'</a>
    
                           '
            ];
        }

        // Sort files by 'modified' field in descending order (latest first)
        usort($files, function($a, $b) {
            return strtotime($b['date_uploaded']) - strtotime($a['date_uploaded']);
        });
        
    }

    return $files;
}


if (isset($_POST['action']) && $_POST['action'] === "delete_document") {
    // Ensure the category is provided
    if (isset($_POST['category'])) {
        $category = $_POST['category'];
        $filename = $_POST['filename'];
        $url = "{$APIUrl}{$category}/documents";

        // Call the delete function
        $result = deleteDocument($url, $filename);

        // Return the result as JSON
        header('Content-Type: application/json');
        echo json_encode($result);
    } else {
        // Handle missing category
        header('Content-Type: application/json');
        echo json_encode([
            "status" => "error",
            "message" => "Category is required."
        ]);
    }
}

function deleteDocument($url, $filename) {
    global $db;

    $vuserid = $_SESSION['userid'];
    // Prepare the raw JSON data
    $rawData = json_encode([
        "filename" => $filename
    ]);

    // Initialize cURL session
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "DELETE"); // Use DELETE method
    curl_setopt($ch, CURLOPT_POSTFIELDS, $rawData); // Send raw JSON data
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json', // Set content type to JSON
        'Content-Length: ' . strlen($rawData) // Set content length
    ]);

    // Execute cURL request
    $response = curl_exec($ch);

    // Check for errors
    if (curl_errno($ch)) {
        // Handle cURL error
        return [
            "status" => "error",
            "message" => "Failed to connect to the API: " . curl_error($ch)
        ];
    }

    // Close cURL session
    curl_close($ch);

    // Decode the JSON response
    $data = json_decode($response, true);

    // Log audit only on success
    if (isset($data['message']) && $data['message'] === "Document deleted successfully") {
    add_audit_log($vuserid, 'document_deleted', '', "Document Deleted: $filename", $db);
    }
    // Check if the deletion was successful
    if (isset($data['message']) && $data['message'] === "Document deleted successfully") {
        return [
            "status" => "success",
            "message" => $data['message']
        ];
    } else {
        return [
            "status" => "error",
            "message" => "Failed to delete the document"
        ];
    }
}


function fetchBackOfficer($link){
    global $db,$link;
    // changed query  to fetch only backofficer [vastvikta][11-04-2025]
    $sql = "SELECT c.AtxUserID, c.AtxUserName, a.DisplayName, a.atxGid, b.ugdContactID, b.atxGid 
    FROM $db.unigroupid a, $db.unigroupdetails b, $db.uniuserprofile c 
    WHERE a.atxGid = b.atxGid AND b.ugdContactID = c.AtxUserID AND a.atxGid='060000' AND c.AtxUserStatus = '1'
    GROUP BY b.ugdContactID ORDER BY AtxUserName ASC";

    $user_query = mysqli_query($link, $sql);
    if (!$user_query) {
        echo "Error fetching back officers: " . mysqli_error($link);
    }
    return $user_query;
}
/* Fetch customer type list */
function categorytype($id = ''){
    // Access global variables $db and $link for database connection
    global $db, $link;
    // Initialize an empty WHERE clause
    $where = '';
    // Check if $id is not empty, and construct the WHERE clause accordingly
    if (!empty($id)){
        $where = 'WHERE id IN (' . $id . ')';
    }
    // SQL query to select all columns from the 'complaint_type' table with optional WHERE clause
    $sql_type = "SELECT * FROM $db.complaint_type " . $where;
    // Execute the SQL query and store the result in $result_type
    $result_type = mysqli_query($link, $sql_type);
    // Return the result set containing customer types
    return $result_type;
}
/* Insert or update data in the 'web_category' table */
function insert_category(){
    // Access global variables $db and $link for database connection
    global $db, $link;
    // Get user ID from the session
    $vuserid = $_SESSION['userid'];
    // Convert the 'type' array into a comma-separated string
    $type = $_REQUEST['complaint_type'];
    // Sanitize and retrieve values from the request
    $category = test_input($_REQUEST['category']);
    $VDescription = test_input($_REQUEST['V_Description']);
    // Get the current date
    $datedk = date("Y-m-d");
    // Get the category ID from the request
    $id = $_REQUEST['id'];
    // Check if the ID is empty (indicating a new category insertion)
    if(empty($id)){
        // Check for duplicate entries with the same category name
        $sql_check_document = "SELECT * FROM $db.web_category WHERE status='1' AND category='$category'";
        $result_query = mysqli_query($link, $sql_check_document);
        $num_document = mysqli_num_rows($result_query);
        // If no duplicate entries found, insert the new category
        if ($num_document == 0){
            $sql_document = "INSERT INTO $db.web_category( category,V_Description, createdby, createdon, type) VALUES ('$category', '$VDescription', '$vuserid', NOW(), '$type')";
            $res = mysqli_query($link, $sql_document);
            if (!$res){
                if (_DBGLOG_){
                           DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB insert error category: $sql_document". mysqli_error($link));
                }
                $response['error'] = TRUE;
                $response['error_msg'] = "Category Database error";
                echo json_encode($response);
                exit();
            }
            $msg = "Success: $category Category added!";
        } else {
            $msg = "Error: Duplicate Entry Of Category name";
        }
        // inserting category [vastvikta][13-02-2025]
			add_audit_log($vuserid, 'insert_category', '', 'New category added '.$category, $db);
		
    } else {
        // Check for duplicate entries with the same category name (excluding the current category ID)
        $sql_check_document = "SELECT * FROM $db.web_category WHERE status='1' AND category='$category' AND id != '" . $id . "'";
        $result_query = mysqli_query($link, $sql_check_document);
        $num_document = mysqli_num_rows($result_query);
        // If no duplicate entries found, update the existing category
        if ($num_document <= 0){
            $sql3 = "UPDATE $db.web_category SET category = '" . $category . "', V_Description = '" . $VDescription . "', type = '" . $type . "' WHERE id = '" . $id . "'";
            $res1=mysqli_query($link, $sql3);
            if (!$res1){
                if (_DBGLOG_){
                           DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB Update error category: $sql3". mysqli_error($link));
                }
                $response['error'] = TRUE;
                $response['error_msg'] = "Category Database error";
                echo json_encode($response);
                exit();
            }
            $msg = "Success: Category Name Updated!";
        } else {
            $msg = "Error: Duplicate Entry Of Category name";
        }
        //updating category [vastvikta][13-02-2025]
			add_audit_log($vuserid, 'update_category', '', 'Category updated '.$category, $db);
		
    }
    // Output the result message
    echo $msg;
}

/* Fetch data for a specific category from the 'web_category' table */
function category_data($id){
    // Access global variables $db and $link for database connection
    global $db, $link;

    // SQL query to select all columns from the 'web_category' table for the specified category id
    $sql2 = "SELECT * FROM $db.web_category WHERE id = '" . $id . "'";

    // Execute the SQL query and store the result in $fetch2
    $fetch2 = mysqli_query($link, $sql2);

    // Fetch the associative array representing a row of data from the result set
    $row2 = mysqli_fetch_assoc($fetch2);

    // Create an array to store the fetched category data
    $categorydata = array();

    // Store category-related data in the $categorydata array
    $categorydata['category'] = $row2['category'];
    $categorydata['type'] = $row2['type'];
    $categorydata['VDescription'] = $row2['V_Description'];

    // Return the array containing the fetched category data
    return $categorydata;
}

function test_input($data) {
    $data = trim($data);
    $data = htmlspecialchars($data);
    return $data;
}
// Function to delete a web category by updating its status to inactive (status=0)
function category_delete(){
    // Access global variables $db and $link for database connection
    global $db, $link;
    // Get user ID from the session
    $vuserid = $_SESSION['userid'];
    // Get the 'id' parameter from the request
    $id = $_REQUEST['id'];
    // SQL query to update the 'status' column to 0 (inactive) for the specified category id
    $sql_delete = "UPDATE $db.web_category SET status=0 WHERE id='$id'";
    // Execute the SQL query to perform the category deletion (setting status to 0)
    $res = mysqli_query($link, $sql_delete);
    if (!$res){
        if (_DBGLOG_){
                   DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete error category: $sql_delete". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Category Database error";
        echo json_encode($response);
        exit();
    }
     // Checking if the query execution was successful
     if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error'; // Return an error message to the client
    }

    //fetching  category [vastvikta][14-02-2025]
    $sql1 = "SELECT category FROM $db.web_category WHERE id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $category = $row['category'];

    //updating category [vastvikta][13-02-2025]
	add_audit_log($vuserid, 'delete_category', '', 'Category deleted '.$category, $db);
		
}
/*Fetch category data for view page*/
function category_view(){
    // Access global variables $db and $link for database connection
    global $db, $link;
    // SQL query to select all rows from the 'web_category' table where status is 1 (active), ordered by category ascending
    $sql_document = "SELECT * FROM $db.web_category WHERE status=1 ORDER BY category ASC";
    // Execute the SQL query and store the result in $res
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result set containing active web categories
    return $res;
}

/* functions for view subcategory  Auth: VASTVIKTA NISHAD */
// Function to fetch subcategory data for viewing
function subcategory_view(){
    //connection to the database 
    global $db, $link;
    // SQL query to select data from the web_subcategory table
    $sql_document = "SELECT * FROM $db.web_subcategory WHERE status=1 ORDER BY subcategory ASC";
    // store the result in $res
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result set
    return $res;
}
// fetch category name
function category($cat)
{
    global $db,$link;
    $res=mysqli_fetch_assoc(mysqli_query($link,"select category from $db.web_category where id='$cat' ; "));
    return $res['category'];
}
//function to delete the  subcategory 
function subcategory_delete()
{
    global $db, $link;
    // Retrieving the id from the form 
    $id = $_POST['id'];
    
    // Updating status of the subcategory on the basis of the id  
    $sql_delete = "UPDATE $db.web_subcategory SET status=0 WHERE id='$id'";
    // Executing the SQL DELETE query
    $res = mysqli_query($link, $sql_delete);
    if (!$res){
        if (_DBGLOG_){
                   DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete error subcategory: $sql_delete". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Sub Category Database error";
        echo json_encode($response);
        exit();
    }
    // Checking if the query execution was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error'; // Return an error message to the client
    }
    // Get user ID from the session
    $vuserid = $_SESSION['userid'];
    //fetching  category [vastvikta][14-02-2025]
    $sql1 = "SELECT subcategory FROM $db.web_subcategory WHERE id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $subcategory = $row['subcategory'];
 
    //updating category [vastvikta][13-02-2025]
    add_audit_log($vuserid, 'delete_subcategory', '', 'Subcategory deleted '.$subcategory, $db);
         
}
//function to insert data in subcategory
function insert_subcategory()
{
    global $db, $link;
    // Getting user ID from the session
    $vuserid = $_SESSION['userid'];
    // Retrieving data from the request
    $category = $_REQUEST['category'];
    $subcategory = $_REQUEST['subcategory'];
    $time_hours = $_REQUEST['time_hours'];
    $VDescription = $_REQUEST['V_Description'];
    $second_resolution = $_REQUEST['second_resolution'];
    $third_resolution = $_REQUEST['third_resolution'];
    // Concatenating selected users for each level
    $level1_users = implode(",", $_POST['level1_users']);
    $level2_users = implode(",", $_POST['level2_users']);
    $level3_users = implode(",", $_POST['level3_users']);
    // Getting current date
    $datedk = date("Y-m-d");
    // Retrieving additional data from the request
    $id = $_REQUEST['id'];
    // Checking if it's a new entry or an update
    if (empty($id)) {
        // Checking for duplicate entry
        $sql_check_document = "SELECT * FROM $db.web_subcategory WHERE subcategory='$subcategory' AND category='$category' AND status='1'";
        $result_query = mysqli_query($link, $sql_check_document);
        $num_document = mysqli_num_rows($result_query);
        if ($num_document == 0) {
            // Inserting new subcategory if not a duplicate
            $sql_document = "INSERT INTO $db.web_subcategory(subcategory,V_Description,createdby,createdon,category, resolution_time_in_hours, second_resolution_time,third_resolution_time,level1_users,level2_users,level3_users) VALUES('$subcategory','$VDescription','$vuserid','$datedk','$category', '$time_hours', '$second_resolution','$third_resolution','$level1_users','$level2_users','$level3_users')";
            $res = mysqli_query($link, $sql_document);
            //inserting subcategory [vastvikta][13-02-2025]
	    add_audit_log($vuserid, 'insert_subcategory', '', 'New Subcategory added '.$subcategory, $db);
	   
            if (!$res){
                if (_DBGUp_){
                           DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB insert error subcategory: $sql_document". mysqli_error($link));
                }
                $response['error'] = TRUE;
                $response['error_msg'] = "Sub Category Database error";
                echo json_encode($response);
                exit();
            }
            $msg = "Success: Sub Category name added successfully!";
            echo $msg;
            exit;
        } else {
            // Duplicate entry error
            $msg = "Error: Duplicate Entry Of Sub Category name";
            echo $msg;
            exit;
        }
    } else {
        // Checking for duplicate entry during update
        $sql_check_document = "SELECT * FROM $db.web_subcategory WHERE subcategory='$subcategory' AND category='$category' AND status='1' AND id != '$id'";
        $result_query = mysqli_query($link, $sql_check_document);
        $num_document = mysqli_num_rows($result_query);
        if ($num_document == 0) {
            // Updating subcategory if not a duplicate
            $sql_update = "UPDATE $db.web_subcategory SET 
                                subcategory = '$subcategory', 
                                V_Description = '$VDescription', 
                                category = '$category', 
                                resolution_time_in_hours = '$time_hours', 
                                second_resolution_time ='$second_resolution', 
                                third_resolution_time ='$third_resolution', 
                                level1_users ='$level1_users', 
                                level2_users ='$level2_users', 
                                level3_users ='$level3_users', 
                                createdon='" . date('Y-m-d H:i:s') . "' 
                                WHERE id = '$id'";
            mysqli_query($link, $sql_update);
            //update subcategory [vastvikta][13-02-2025]
	        add_audit_log($vuserid, 'insert_subcategory', '', 'Subcategory updated '.$subcategory, $db);
	   
            $msg = "Success: Sub Category updated successfully!";
            echo $msg;
            exit;
        } else {
            // Duplicate entry error during update
            $msg = "Error: Duplicate Entry Of Sub Category name";
            echo $msg;
            exit;
        }
       
    }
}

//function to fetch data in subcategory table 
function getSubCategoryData($link, $id) {
    global $db;
    $subcategoryData = array();
    // Query to retrieve user data for three levels
    $sqlagent = "SELECT AtxUserID, AtxUserName FROM $db.uniuserprofile WHERE AtxUserStatus='1' ORDER BY AtxUserName ASC";
    // Execute the query for each level and store the results in the array
    $subcategoryData['result1'] = mysqli_query($link, $sqlagent);
    $subcategoryData['result2'] = mysqli_query($link, $sqlagent);
    $subcategoryData['result3'] = mysqli_query($link, $sqlagent);
    // If an id is provided, fetch data for a specific subcategory
    if ($id) {
        // Query to retrieve subcategory data based on the provided id
        $sql = "SELECT * FROM $db.web_subcategory WHERE id = '$id'";
        $result = mysqli_query($link, $sql);
        // Process the result and populate the subcategoryData array
        while ($row = mysqli_fetch_assoc($result)) {
            $subcategoryData['category'] = $row['category'];
            $subcategoryData['subcategory'] = $row['subcategory'];
            $subcategoryData['time_hours'] = $row['resolution_time_in_hours'];
            $subcategoryData['second_resolution'] = $row['second_resolution_time'];
            $subcategoryData['third_resolution'] = $row['third_resolution_time'];
            $subcategoryData['level1_users'] = $row['level1_users'];
            $subcategoryData['level2_users'] = $row['level2_users'];
            $subcategoryData['level3_users'] = $row['level3_users'];
            $subcategoryData['VDescription'] = $row['V_Description'];
        }
    }
    // Return the array containing subcategory data
    return $subcategoryData;
}

/* functions for view county */
//function for viewing data from web_city for view county page
function view_county(){
    global $db, $link;
    // SQL query to select all records from the web_city table where status is '1'
    $sql_document = "SELECT * FROM $db.web_city WHERE status='1'";
    // Execute the query
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result set
    return $res;
}
//function to fetch city data from the database 
function getCityData($link, $id) {
    $cityData = array();
    // Check if $id is not empty and is a numeric value
    if ($id !== '' && is_numeric($id)) {
        // SQL query to select the city name based on the provided id
        $sql = "SELECT `city` FROM `web_city` WHERE `id` = $id";
        // Execute the query
        $result = mysqli_query($link, $sql);
        // Check if the query was successful and if there are rows returned
        if ($result && mysqli_num_rows($result) > 0) {
            // Fetch the associative array representing the result row
            $row = mysqli_fetch_assoc($result);
            // Add id for editing and city data to the cityData array
            $cityData['id'] = $id;
            $cityData['city'] = $row['city'];
        }
    }
    // Return the cityData array
    return $cityData;
}

//function to delete  province
function province_delete()
{
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
    // SQL query to update the status of the province to '0' (soft delete)
    $sql_delete = "UPDATE $db.web_city SET status='0' WHERE id='$id'";
    // Execute the SQL query
    
    $res = mysqli_query($link, $sql_delete);
    if (!$res){
        if (_DBGLOG_){
                   DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB Delete error Province: $sql_delete". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Province Database error";
        echo json_encode($response);
        exit();
    }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error'; // Return an error message to the client
    }
    // Get user ID from the session
    $vuserid = $_SESSION['userid'];
    //fetching  category [vastvikta][14-02-2025]
    $sql1 = "SELECT city FROM $db.web_city WHERE id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $city = $row['city'];
 
    //deleting province [vastvikta][14-02-2025]
    add_audit_log($vuserid, 'delete_province', '', 'Province deleted '.$city, $db);
   
}
//function to handle the update and insert  request for the county / province
function insert_or_update_province(){
    global $db, $link;
    // Retrieve data from the POST request
    $id = $_POST['id'];
    $city = $_POST['city'];
    $vuserid = $_SESSION['userid'];
    // Check if the ID is empty to determine whether it's an insert or an update
    if (empty($id)) {
        // SQL query for insertion if ID is empty
        $sql = "INSERT INTO `$db`.`web_city` (`id`, `city`, `status`) VALUES (NULL, '$city', '1')";
       
        //insert province [vastvikta][13-02-2025]
        add_audit_log($vuserid, 'insert_province', '', 'New Province added '.$city, $db);
      
    } else {
        // SQL query for update if ID is not empty
        $sql = "UPDATE `$db`.`web_city` SET `city`='$city', `status`='1' WHERE `id`='$id'";
       
        //update province [vastvikta][13-02-2025]
        add_audit_log($vuserid, 'update_province', '', 'Province updated '.$city, $db);
      
    }
    // Execute the SQL query
    $result = mysqli_query($link, $sql);

    if (!$result){
        if (_DBGLOG_){
                   DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB insert or update error Province: $sql". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Province Database error";
        echo json_encode($response);
        exit();
    }
    // Check if the query was successful
    if ($result) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'Error: ' . mysqli_error($link); // Return an error message with details to the client
    }
}

/*funciton  for view sub county */
//function to load the data from web_Village table 
function view_village() {
    global $db, $link;
    // SQL query to select all records from the web_Village table where status is '1'
    $sql_document = "SELECT * FROM $db.web_Village WHERE status='1'";
    // Execute the query or die with an error message if it fails
    $res = mysqli_query($link, $sql_document) or die("Could not select: " . mysqli_error($link));
    // Return the result set
    return $res;
}

//function to delete the village data
function village_delete(){
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
    // SQL query to update the status of the village to '0' (soft delete)
    $sql_delete = "UPDATE $db.web_Village SET status=0 WHERE id='$id'";
    // Execute the SQL query
    $res = mysqli_query($link, $sql_delete);

    if (!$res){
        if (_DBGLOG_){
                   DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete error District: $sql_delete". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "District Database error";
        echo json_encode($response);
        exit();
    }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error'; // Return an error message to the client
    }
}
//function to get province data on the basis of id  of the village  form web_city for web_new_village 
function getDistrict($id){
    global $db, $link;
    // SQL query to select id and city from web_city where status is '1'
    $sqldistrict = "SELECT id, city FROM web_city WHERE status=1";
    // Execute the SQL query
    $result =  mysqli_query($link, $sqldistrict);
    // Return the result set
    return $result;
}

//function to get district data from the web_village table on the basis of id 
function getVillageData($link, $id){
    global $db;
    $villageData = array();
    // Check if $id is not empty
    if ($id != '') {
        // SQL query to select all columns from web_Village based on the provided id
        $sql = "SELECT * FROM $db.web_Village WHERE id = '$id'";
        // Execute the SQL query
        $result = mysqli_query($link, $sql);
        // Check if the query was successful and if there are rows returned
        if ($result && mysqli_num_rows($result) > 0) {
            // Fetch the associative array representing the result row
            $villageData = mysqli_fetch_assoc($result);
        }
    }
    // Return the villageData array
    return $villageData;
}

//function to  handle insert or update of the   district in the database 
function insert_or_update_village($link){
    global $db, $link;
    // Retrieve data from the POST request and perform proper escaping
    $id = isset($_POST['id']) ? mysqli_real_escape_string($link, $_POST['id']) : '';
    $district = isset($_POST['district']) ? mysqli_real_escape_string($link, $_POST['district']) : '';
    $village = isset($_POST['village']) ? mysqli_real_escape_string($link, $_POST['village']) : '';
    $description = isset($_POST['V_Description']) ? mysqli_real_escape_string($link, $_POST['V_Description']) : '';
    
    // Check if all required fields are available
    if (!$district || !$village) {
        $response = array('error' => TRUE, 'error_msg' => "District or Village cannot be empty.");
        echo json_encode($response);
        exit();
    }
    
    // Determine the SQL query based on whether $id is empty
    if (empty($id)) {
        // SQL query for insertion if ID is empty
        $sql = "INSERT INTO `$db`.`web_Village` (`vVillage`, `iDistrictID`, `status`, `createdby`, `createdon`, `V_Description`)
                VALUES ('$village', '$district', '1', '1', CURRENT_TIMESTAMP, '$description')";
    } else {
        // SQL query for update if ID is not empty
        $sql = "UPDATE `$db`.`web_Village` SET `vVillage`='$village', `iDistrictID`='$district', `V_Description`='$description' WHERE `id`='$id'";
    }
    
    // Log the SQL query for debugging
    error_log("Executing SQL: $sql");

    // Execute the SQL query
    $result = mysqli_query($link, $sql);
    
    // Log any SQL errors
    if (!$result) {
        error_log("SQL Error: " . mysqli_error($link));
        if (_DBGLOG_){
            DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB Update or insert error: $sql - " . mysqli_error($link));
        }
        $response = array('error' => TRUE, 'error_msg' => "District Database error: " . mysqli_error($link));
        echo json_encode($response);
        exit();
    }
    
    // Check if the query was successful
    if ($result) {
        $response = array('error' => FALSE, 'message' => 'success');
    } else {
        $response = array('error' => TRUE, 'error_msg' => "Unexpected error: " . mysqli_error($link));
    }
    
    echo json_encode($response);
}

/* functions for view mail format*/
// Function to retrieve all mail formats with MailStatus set to 1
function view_mail_data() {
    global $db, $link;
    // SQL query to select all records from tbl_mailformats where MailStatus is 1
    $sql_document = "SELECT * FROM $db.tbl_mailformats WHERE MailStatus = 1";
    // Execute the query or die with an error message if it fails
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result set
    return $res;
}
function tbl_mailformats(){
    global $db, $link;
    // SQL query to select all records from the web_city table where status is '1'
    $sql_mail = "SELECT DISTINCT(MailSignature),MailSignImage,MailTypeID FROM tbl_mailformats ORDER BY MailStatus Asc LIMIT 1; ";
    // Execute the query
    $res = mysqli_query($link, $sql_mail) or die("Could not select");
    // Return the result set
    return $res;
}
// Function to soft delete a mail format by updating MailStatus to 0
function mail_delete() {
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
    // SQL query to update MailStatus to 0 for the specified MailTypeID
    $sql_update = "UPDATE $db.tbl_mailformats SET MailStatus = '0' WHERE MailTypeID = '$id'";
    // Execute the SQL query
    $res = mysqli_query($link, $sql_update);
    if (!$res){
        if (_DBGLOG_){
                   DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB Delete error Mail status : $sql_update". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Mail status  Database error";
        echo json_encode($response);
        exit();
    }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
    }
     // Get user ID from the session
     $vuserid = $_SESSION['userid'];
     //fetching  mailformat [vastvikta][14-02-2025]
     $sql1 = "SELECT MailTemplateName FROM $db.tbl_mailformats WHERE MailTypeID = '$id'";
     $res1 = mysqli_query($link, $sql1);
     $row = mysqli_fetch_assoc($res1);
     $MailTemplateName = $row['MailTemplateName'];
  
     //deleting mail format  [vastvikta][14-02-2025]
     add_audit_log($vuserid, 'delete_mailformat', '', 'Mail format deleted '.$MailTemplateName, $db);
    
}
// Function to retrieve mail format data based on the provided MailTypeID
function getMailFormatData($link, $id) {
    global $db;
    $mailFormatData = array();
    // Check if $id is not empty
    if ($id != '') {
        // SQL query to select all columns from tbl_mailformats based on the provided MailTypeID
        $sql = "SELECT * FROM $db.tbl_mailformats WHERE MailTypeID = '$id'";
        // Execute the SQL query
        $result = mysqli_query($link, $sql);
        // Check if the query was successful and if there are rows returned
        if ($result && mysqli_num_rows($result) > 0) {
            // Fetch the associative array representing the result row
            $mailFormatData = mysqli_fetch_assoc($result);
        }
    }
    // Return the mailFormatData array
    return $mailFormatData;
}
// Function to handle insert and update requests for mail formats
function insert_or_update_mail($link) {
    global $db;
    $vuserid = $_SESSION['userid'];
    // Retrieve and clean POST data
    $MailTypeID = isset($_POST['id']) ? (int)$_POST['id'] : 0;
    $MailTemplateName = isset($_POST['template_name']) ? mysqli_real_escape_string($link, $_POST['template_name']) : '';
    $MailType = isset($_POST['type']) ? mysqli_real_escape_string($link, $_POST['type']) : '';
    $MailSubject = isset($_POST['subject']) ? mysqli_real_escape_string($link, $_POST['subject']) : '';
    $MailGreeting = isset($_POST['greeting']) ? mysqli_real_escape_string($link, $_POST['greeting']) : '';
    
    // Use stripslashes to remove extra slashes [vastvikta][06-02-2025]
    $MailBody = isset($_POST['body']) ? stripslashes(mysqli_real_escape_string($link, $_POST['body'])) : '';
    $MailSignature = isset($_POST['signature']) ? stripslashes(mysqli_real_escape_string($link, $_POST['signature'])) : '';
    $MailDescription = isset($_POST['description']) ? mysqli_real_escape_string($link, $_POST['description']) : '';
    $MailStatus = 1;
    $MailExpiry = isset($_POST['expiry']) ? mysqli_real_escape_string($link, $_POST['expiry']) : 0;

    // Check if the action is to insert or update
    if ($_POST['action'] == 'submit_mail') {
        $sql = "INSERT INTO `$db`.`tbl_mailformats` 
                (`MailTemplateName`, `MailType`, `MailSubject`, `MailGreeting`, `MailBody`,`MailDescription`, `MailStatus`, `MailExpiry`)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt_insert = mysqli_prepare($link, $sql);
        
        if ($stmt_insert) {
            mysqli_stmt_bind_param($stmt_insert, 'ssssssii', $MailTemplateName, $MailType, $MailSubject, $MailGreeting, $MailBody, $MailDescription, $MailStatus, $MailExpiry);
            
            $insert_success = mysqli_stmt_execute($stmt_insert);
            
            if ($insert_success) {
                $MailTypeID = mysqli_insert_id($link); // Get the auto-generated MailTypeID
                echo 'success'; // Return success message to the client
            } else {
                $response['error'] = TRUE;
                $response['error_msg'] = 'Database error: ' . mysqli_stmt_error($stmt_insert);
                echo json_encode($response);
            }
            
            mysqli_stmt_close($stmt_insert);
        } else {
            $response['error'] = TRUE;
            $response['error_msg'] = 'Prepare statement error: ' . mysqli_error($link);
            echo json_encode($response);
        }
         // inserting mailformat [vastvikta][13-02-2025]
			add_audit_log($vuserid, 'insert_mailformat', '', 'New Mail format added '.$MailTemplateName, $db);
		
    }

    if ($_POST['action'] == 'update_mail') {
        // Update the mail format
        $sql = "UPDATE `$db`.`tbl_mailformats` 
                SET `MailTemplateName`='$MailTemplateName', 
                    `MailType`='$MailType', 
                    `MailSubject`='$MailSubject', 
                    `MailGreeting`='$MailGreeting', 
                    `MailBody`='$MailBody', 
                    `MailDescription`='$MailDescription', 
                    `MailStatus`='$MailStatus', 
                    `MailExpiry`='$MailExpiry'
                WHERE `MailTypeID`='$MailTypeID'";

        // Execute the update query
        $update_success = mysqli_query($link, $sql);

        if ($update_success) {
            echo 'success'; // Return success message to the client
        } else {
            $response['error'] = TRUE;
            $response['error_msg'] = 'Database error: ' . mysqli_error($link);
            echo json_encode($response);
        }
        // updating mailformat [vastvikta][13-02-2025]
			add_audit_log($vuserid, 'update_mailformat', '', 'Mail format updated '.$MailTemplateName, $db);
		
    }
}

/*FUNCTION  FOR VIEW SMS FORMAT*/
// Function to retrieve all SMS formats with smsstatus set to 1
function view_sms_data(){
    global $db, $link;
    // SQL query to select all records from tbl_smsformat where smsstatus is 1
    $sql_document = "SELECT * FROM $db.tbl_smsformat WHERE smsstatus = 1";
    // Execute the query or die with an error message if it fails
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result set
    return $res;
}
// Function to soft delete an SMS format by updating smsstatus to 0
function sms_delete() {
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
    // SQL query to update smsstatus to 0 for the specified id
    $sql_update = "UPDATE $db.tbl_smsformat SET smsstatus = '0' WHERE id = '$id'";
    // Execute the SQL query
    $res = mysqli_query($link, $sql_update);
        if (!$res){
            if (_DBGLOG_){
                    DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete  error SMS format : $sql_update". mysqli_error($link));
            }
            $response['error'] = TRUE;
            $response['error_msg'] = "SMS format Database error";
            echo json_encode($response);
            exit();
        }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
    }
    // Get user ID from the session
    $vuserid = $_SESSION['userid'];
    //fetching  smsformat [vastvikta][14-02-2025]
    $sql1 = "SELECT smstemplatename FROM $db.tbl_smsformat WHERE id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $smstemplatename = $row['smstemplatename'];
 
    //deleting SMS format  [vastvikta][14-02-2025]
    add_audit_log($vuserid, 'delete_smsformat', '', 'SMS format deleted '.$smstemplatename, $db);
   
}

function delete_bulletin() {
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
    // SQL query to delete_bulletin for the specified id
    $sql_update = "delete from $db.tbl_bulletinboard where id = $id";
     // Execute the SQL query
    $res = mysqli_query($link, $sql_update);
        if (!$res){
            if (_DBGLOG_){
                    DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete error bulletin board : $sql_update". mysqli_error($link));
            }
            $response['error'] = TRUE;
            $response['error_msg'] = "Bulletin board  Database error";
            echo json_encode($response);
            exit();
        }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
    }
}

// Function to get SMS template data from tbl_smsformat based on the provided id
function getSmsData($link, $id) {
    global $db;
    $smsData = array();
    // Check if $id is not empty
    if ($id) {
        // Use prepared statements to prevent SQL injection
        $sql = "SELECT * FROM $db.tbl_smsformat WHERE id = ?";
        $stmt = mysqli_prepare($link, $sql);
        // Bind the parameters and execute the statement
        mysqli_stmt_bind_param($stmt, "s", $id);
        mysqli_stmt_execute($stmt);
        // Get the result set
        $result = mysqli_stmt_get_result($stmt);
        // Iterate through the result set and populate the $smsData array
        while ($row = mysqli_fetch_assoc($result)) {
            $smsData['template_name'] = $row['smstemplatename'];
            $smsData['type'] = $row['smstype'];
            $smsData['header'] = $row['smsheader'];
            $smsData['body'] = $row['smsbody'];
            $smsData['footer'] = $row['smsfooter'];
            $smsData['description'] = $row['smsdescription'];
            $smsData['expiry'] = $row['smsexpiry'];
        }
    }
    // Return the $smsData array
    return $smsData;
}
// Function for updating and inserting data in tbl_smsformat
function insert_or_update_sms($link){
    global $db,$link;
    $vuserid = $_SESSION['userid'];
    // Retrieve data from the POST request and perform proper escaping
    $SmsID = isset($_POST['id']) ? (int)$_POST['id'] : 0;
    $SmsTemplateName = isset($_POST['template_name']) ? mysqli_real_escape_string($link, $_POST['template_name']) : '';
    $SmsType = isset($_POST['type']) ? mysqli_real_escape_string($link, $_POST['type']) : '';
    $SmsHeader = isset($_POST['header']) ? mysqli_real_escape_string($link, $_POST['header']) : '';
    $SmsBody = isset($_POST['body']) ? mysqli_real_escape_string($link, $_POST['body']) : '';
    $SmsFooter = isset($_POST['footer']) ? mysqli_real_escape_string($link, $_POST['footer']) : '';
    $SmsDescription = isset($_POST['description']) ? mysqli_real_escape_string($link, $_POST['description']) : '';
    $SmsStatus = 1; // Assuming '1' is the default status
    $SmsExpiry = isset($_POST['expiry']) ? mysqli_real_escape_string($link, $_POST['expiry']) : 0;
    // Check the action type (submit or update)
    if ($_POST['action'] == 'submit_sms') {
        $sql = "INSERT INTO `$db`.`tbl_smsformat` 
                (`smstemplatename`, `smstype`, `smsheader`, `smsbody`, `smsfooter`, `smsdescription`, `smsstatus`, `smsexpiry`)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt_insert = mysqli_prepare($link, $sql);
        
        if ($stmt_insert) {
            mysqli_stmt_bind_param($stmt_insert, 'ssssssii', 
                $SmsTemplateName, $SmsType, $SmsHeader, $SmsBody, $SmsFooter, $SmsDescription, $SmsStatus, $SmsExpiry);
            
            $SmsTemplateName = $_POST['template_name'];
            $SmsType = $_POST['type'];
            $SmsHeader = $_POST['header'];
            $SmsBody = $_POST['body'];
            $SmsFooter = $_POST['footer'];
            $SmsDescription = $_POST['description'];
            $SmsStatus = 1; // Assuming '1' is the default status
            $SmsExpiry = $_POST['expiry'];
            
            $insert_success = mysqli_stmt_execute($stmt_insert);
            
            if ($insert_success) {
                $SmsID = mysqli_insert_id($link); // Get the auto-generated SmsID
                echo 'success'; // Return success message to the client
            } else {
                $response['error'] = TRUE;
                $response['error_msg'] = 'Database error: ' . mysqli_stmt_error($stmt_insert);
                echo json_encode($response);
            }
            
            mysqli_stmt_close($stmt_insert);
        } else {
            $response['error'] = TRUE;
            $response['error_msg'] = 'Prepare statement error: ' . mysqli_error($link);
            echo json_encode($response);
        }
          
			add_audit_log($vuserid, 'insert_smsformat', '', 'New SMS format added '.$SmsTemplateName, $db);
		
    }  elseif ($_POST['action'] == 'update_sms') {
        $sql = "UPDATE `$db`.`tbl_smsformat` 
                SET `smstemplatename`=?, 
                    `smstype`=?, 
                    `smsheader`=?, 
                    `smsbody`=?, 
                    `smsfooter`=?, 
                    `smsdescription`=?, 
                    `smsstatus`=?, 
                    `smsexpiry`=?
                WHERE `id`=?";
        
        $stmt_update = mysqli_prepare($link, $sql);
        
        if ($stmt_update) {
            mysqli_stmt_bind_param($stmt_update, 'ssssssiii', 
                $SmsTemplateName, $SmsType, $SmsHeader, $SmsBody, $SmsFooter, $SmsDescription, $SmsStatus, $SmsExpiry, $SmsID);
            
            $SmsTemplateName = $_POST['template_name'];
            $SmsType = $_POST['type'];
            $SmsHeader = $_POST['header'];
            $SmsBody = $_POST['body'];
            $SmsFooter = $_POST['footer'];
            $SmsDescription = $_POST['description'];
            $SmsStatus = 1; // Assuming '1' is the default status
            $SmsExpiry = $_POST['expiry'];
            $SmsID = $_POST['id'];
            
            $update_success = mysqli_stmt_execute($stmt_update);
            
            if ($update_success) {
                echo 'success'; // Return success message to the client
            } else {
                $response['error'] = TRUE;
                $response['error_msg'] = 'Database error: ' . mysqli_stmt_error($stmt_update);
                echo json_encode($response);
            }
            
            mysqli_stmt_close($stmt_update);
        } else {
            $response['error'] = TRUE;
            $response['error_msg'] = 'Prepare statement error: ' . mysqli_error($link);
            echo json_encode($response);
        }

			add_audit_log($vuserid, 'update_smsformat', '', 'SMS format updated '.$SmsTemplateName, $db);
		
    }
    
}
// Function to retrieve active projects from web_projects table
function view_project() {
    global $db, $link;
    // SQL query to select all records from web_projects where i_Status is '1'
    $sql_document = "SELECT * FROM $db.web_projects WHERE i_Status='1'";
    // Execute the query or die with an error message if it fails
    $res = mysqli_query($link, $sql_document) or die("Could not select: " . mysqli_error($link));
    // Return the result set
    return $res;
}
// Function to delete a department detail from the web_projects table
function project_delete($id) {
    global $db, $link;
    // SQL query to update i_Status to '0' for the specified pId
    $query = "UPDATE `web_projects` SET `i_Status` = '0' WHERE `pId` = " . $id;
    // Execute the SQL query
    $result = mysqli_query($link, $query);
    if (!$result){
        if (_DBGLOG_){
                DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete error Department: $query". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Department Database error";
        echo json_encode($response);
        exit();
    }
    // Check if the query was successful
    if ($result) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
    }
   
    $vuserid = $_SESSION['userid'];
   
    $sql1 = "SELECT vProjectName FROM $db.web_projects WHERE pId = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $vProjectName = $row['vProjectName'];
 
    add_audit_log($vuserid, 'delete_department', '', 'Department deleted '.$vProjectName, $db);
   
    exit(); // Terminate the script
}
// Function to retrieve project data based on the provided id in the new department form
function getProjectData($link, $id, $db) {
    $data = array();
    // SQL query to select data from web_projects where pId is the provided id
    $sql = "SELECT * FROM $db.web_projects WHERE pId = '" . mysqli_real_escape_string($link, $id) . "'";
    // Execute the query
    $result = mysqli_query($link, $sql);
    // Check if there is a row in the result set
    if ($row = mysqli_fetch_assoc($result)) {
        $data['vProjectName'] = $row['vProjectName'];
        $data['category'] = $row['Type'];
    }
    // Return the data array
    return $data;
}
// Function to insert or update data in the web_projects table
function insert_or_update_project($link) {
    global $db;
    $vuserid = $_SESSION['userid'];
    // Check if the request method is POST
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        // Retrieve session data and current date
        $createdby = $_SESSION['logged'];
        $createdon = date("Y-m-d H:i:s");

        // Retrieve and validate category information
        $category_id = isset($_POST['category']) ? $_POST['category'] : null;
        if ($category_id !== null) {
            $categoryString = $category_id;
        } else {
            echo json_encode(['error' => true, 'message' => 'Error: Category not selected.']);
            return;
        }

        // Retrieve project data from the POST request
        $vProjectName = $_POST['vProjectName'];
        $id = isset($_POST['id']) ? $_POST['id'] : '';
        // Generate a project code based on the project name
        $project_code = substr(preg_replace('/[^a-z]/', '', strtolower(str_replace(' ', '', $vProjectName))), 0, 10);

        // Check if it's a new project or an update
        if (empty($id)) {
            // INSERT logic
            $sql = "INSERT INTO $db.web_projects (vProjectName, v_CreateBY, d_datetime, Type, project_code) VALUES (?, ?, ?, ?, ?)";
            $stmt = mysqli_prepare($link, $sql);
            mysqli_stmt_bind_param($stmt, 'sssss', $vProjectName, $createdby, $createdon, $categoryString, $project_code);
            add_audit_log($vuserid, 'insert_department', '', 'New Department added  '.$vProjectName, $db);
        } else {
            // UPDATE logic
            $sql = "UPDATE $db.web_projects SET vProjectName = ?, Type = ?, project_code = ? WHERE pId = ?";
            $stmt = mysqli_prepare($link, $sql);
            mysqli_stmt_bind_param($stmt, 'sssi', $vProjectName, $categoryString, $project_code, $id);
            add_audit_log($vuserid, 'update_department', '', 'Department updated '.$vProjectName, $db);
        }

        // Execute the prepared statement
        if (mysqli_stmt_execute($stmt)) {
            echo 'success'; // Return success message to the client
        } else {
            echo 'Error: ' . mysqli_stmt_error($stmt); // Return an error message with details to the client
        }

        // Close the prepared statement
        mysqli_stmt_close($stmt);
    }
}

//FUNCTION TO SLEECT USER 
function select_user(){
    global $db,$link;
    $sql = "SELECT c.AtxUserID, c.AtxUserName, a.DisplayName, a.atxGid, b.ugdContactID, b.atxGid FROM $db.unigroupid a, $db.unigroupdetails b, $db.uniuserprofile c WHERE a.atxGid=b.atxGid AND b.ugdContactID = c.AtxUserID AND a.DisplayName='Backoffice Officer' GROUP BY b.ugdContactID ORDER BY AtxUserName ASC";
    return $sql;    
}
//functions  used in  web_user_proj_assign.php file Auth: VASTVIKTA NISHAD 06-02-2024
// Function to get the username based on the provided user ID
function getUserName($id) {
    global $db, $link;
    // SQL query to select AtxUserName from uniuserprofile where AtxUserID is the provided ID
    $q = "SELECT AtxUserName FROM $db.uniuserprofile WHERE AtxUserID='$id'";
    // Execute the query
    $res = mysqli_query($link, $q);
    
    // Check if there are rows in the result set
    if ($res && mysqli_num_rows($res) > 0) {
        // Fetch the result as an associative array
        $rs = mysqli_fetch_assoc($res);
        // Retrieve the AtxUserName from the result set
        $output = $rs['AtxUserName'];
    } else {
        // Set a default value if no result is found
        $output = " ";
    }
    // Return the username
    return $output;
}

// Get Active Users list {farhan akhtar:: 28-10-2025}
function getActiveAgents($link) {
    $agents = [];
    $query = "SELECT AtxUserID, AtxUserName 
              FROM uniuserprofile 
              WHERE AtxDesignation NOT IN ('Master Admin','CRM Admin (Supervisor)','Non Login User')
              AND AtxUserStatus = 1
              ORDER BY AtxUserName ASC";

    $result = mysqli_query($link, $query);

    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $agents[] = $row;
        }
    }

    return $agents;
}


// Function to get the username based on the provided user ID and return it
function assignto($assignto) {
    global $db, $link;
    // SQL query to select AtxUserName from uniuserprofile where AtxUserID is the provided assignto value
    $result = mysqli_query($link, "SELECT AtxUserName FROM $db.uniuserprofile WHERE AtxUserID='$assignto';");
    // Use mysqli_fetch_assoc to fetch an associative array from the result set
    $row = mysqli_fetch_assoc($result);
    // Return the AtxUserName from the result set
    return $row['AtxUserName'];
}
// Function to add an entry to the web_audit_history table for audit logging
function add_audit_log($user_id, $actionlog, $ticketid, $comments, $db, $types = ''){
    global $db, $link;
    // Get the IP address of the user
    $ip = getenv('REMOTE_ADDR');
    // SQL query to insert a new record into the web_audit_history table
    $sql_ins1 = "INSERT INTO $db.web_audit_history(user_id, action, created_on, ticket_id, comments, ip_address, case_process_type) VALUES('$user_id', '$actionlog', NOW(), '$ticketid', '$comments', '$ip', '$types')";
    // Execute the SQL query
    $result = mysqli_query($link, $sql_ins1);
    if (!$result){
        if (_DBGLOG_){
                DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB insert  error Audit: $sql_ins1". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Audit Database error";
        echo json_encode($response);
        exit();
    }
}
// Function to retrieve the project name based on the provided project ID
function project($projectid) {
    global $db, $link;
    // SQL query to select vProjectName from web_projects where pId is the provided projectid value
    $result = mysqli_query($link, "SELECT vProjectName FROM $db.web_projects WHERE pId='$projectid';");
    // Use mysqli_fetch_assoc to fetch an array from the result set
    $row = mysqli_fetch_assoc($result);
    // Return the vProjectName from the array
    return $row['vProjectName'];
}

/*FUNCTIONS USED IN  ASSIGNING DEPARTMENT*/
//functios to assign the department to the user Auth: VASTVIKTA NISHAD 06-02-2024
function assignProjectsToUser($link, $user, $assignto_arr, $db) {
    foreach ($assignto_arr as $val) {
         $vuserid = $_SESSION['userid'];
        // Check if the assignment already exists in the web_project_assigne table
        $query = "SELECT project_id FROM $db.web_project_assigne WHERE user_id = ? AND project_id = ?";
        $stmt = mysqli_prepare($link, $query);
        if ($stmt) {
            // Bind parameters and execute the statement
            mysqli_stmt_bind_param($stmt, "ii", $user, $val);
            mysqli_stmt_execute($stmt);
            // Store the result for further processing
            mysqli_stmt_store_result($stmt);
            // If the assignment does not exist, insert it into the web_project_assigne table
            if (mysqli_stmt_num_rows($stmt) === 0) {
                mysqli_stmt_close($stmt);
                // Prepare the insertion query
                $queryInsert = "INSERT INTO $db.web_project_assigne (project_id, user_id) VALUES (?, ?)";
               
                $stmtInsert = mysqli_prepare($link, $queryInsert);
                if ($stmtInsert) {
                    // Bind parameters and execute the insertion statement
                    mysqli_stmt_bind_param($stmtInsert, "ii", $val, $user);
                    mysqli_stmt_execute($stmtInsert);
                    // Close the insertion statement
                    mysqli_stmt_close($stmtInsert);
                }
                
                $sql1 = "SELECT vProjectName FROM $db.web_projects WHERE pId = '$val'";
                $res1 = mysqli_query($link, $sql1);
                $row = mysqli_fetch_assoc($res1);
                $vProjectName = $row['vProjectName'];
             
                $sql1 = "SELECT c.AtxUserName 
                FROM $db.unigroupid a
                JOIN $db.unigroupdetails b ON a.atxGid = b.atxGid
                JOIN $db.uniuserprofile c ON b.ugdContactID = c.AtxUserID
                WHERE c.AtxUserID = '$user'";
                $res1 = mysqli_query($link, $sql1);
                $row = mysqli_fetch_assoc($res1);
                $name = $row['AtxUserName'];
             
                add_audit_log($vuserid, 'department_assign', '', $vProjectName.' Department assigned to '.$name, $db);
               
            }
        }
    }
}
// function to handle  unassigning the department to the user  Auth: VASTVIKTA NISHAD 06-02-2024
function unassignProjectsFromUser($link, $user, $assignto_arr, $db) {
    $vuserid = $_SESSION['userid'];
    // Loop through each project ID in the $assignto_arr array
    for ($i = 0; $i < count($assignto_arr); $i++) {
        $val = $assignto_arr[$i];
        // Prepare and execute the DELETE query
        $queryDelete = "DELETE FROM $db.web_project_assigne WHERE project_id = ? AND user_id = ?";
        $stmtDelete = mysqli_prepare($link, $queryDelete);
        

        $sql1 = "SELECT vProjectName FROM $db.web_projects WHERE pId = '$val'";
        $res1 = mysqli_query($link, $sql1);
        $row = mysqli_fetch_assoc($res1);
        $vProjectName = $row['vProjectName'];
     
        $sql1 = "SELECT c.AtxUserName 
        FROM $db.unigroupid a
        JOIN $db.unigroupdetails b ON a.atxGid = b.atxGid
        JOIN $db.uniuserprofile c ON b.ugdContactID = c.AtxUserID
        WHERE c.AtxUserID = '$user'";
        $res1 = mysqli_query($link, $sql1);
        $row = mysqli_fetch_assoc($res1);
        $name = $row['AtxUserName'];
     
        add_audit_log($vuserid, 'department_unassign', '', $name.' unassigned from department '.$vProjectName, $db);
       
        // Check if the statement was prepared successfully
        if ($stmtDelete) {
            // Bind parameters and execute the statement
            mysqli_stmt_bind_param($stmtDelete, "ii", $val, $user);
            mysqli_stmt_execute($stmtDelete);
            // Close the statement
            mysqli_stmt_close($stmtDelete);
        }
    }
}
/*FUNCTION FOR VIEW STATUS */
// Function to retrieve and view ticket statuses from the database  Auth: VASTVIKTA NISHAD 07-02-2024
function view_ticketstatus(){
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select all rows from the 'web_ticketstatus' table where status is '1'
    $sql_document = "SELECT * FROM $db.web_ticketstatus WHERE status='1'";  
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql_document) or die("Could not select: " . mysqli_error($link));
    // Return the result, which contains the retrieved ticket statuses
    return $result;
}
// Function to display agent name based on the provided user ID Auth: VASTVIKTA NISHAD 07-02-2024
function displayagentname($userid){
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select the agent's display name from the 'uniuserprofile' table based on the provided user ID
    $sql_user = "SELECT AtxDisplayName FROM $db.uniuserprofile WHERE AtxUserID='$userid'";
    // Execute the SQL query and store the result in the $res_user variable
    $res_user = mysqli_query($link, $sql_user) or die(mysqli_error($link));
    // Fetch the result as an associative array
    $row_user = mysqli_fetch_assoc($res_user);
    // Extract the 'AtxDisplayName' from the fetched associative array
    $AtxDisplayName = $row_user['AtxDisplayName'];
    // Return the agent's display name
    return $AtxDisplayName;
}
// Function to delete a status from the 'web_ticketstatus' table Auth: VASTVIKTA NISHAD 07-02-2024
function status_delete(){
    // Global variables for the database connection
    global $db, $link;
    // Get the 'id' parameter from the POST request
    $id = $_POST['id'];
    // SQL query to update the 'status' column to '0' for the specified 'id' in the 'web_ticketstatus' table
    $sql_update = "UPDATE $db.web_ticketstatus SET status = '0' WHERE id = '$id'";
    // Execute the SQL query and store the result in the $res variable
    $res = mysqli_query($link, $sql_update);
    if (!$res){
        if (_DBGLOG_){
                DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB status delete  error: $sql_update". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "status Delete Database error";
        echo json_encode($response);
        exit();
    }
    // Check if the query was successful
    if ($res) {
        // If successful, echo 'success'
        echo 'success';
    } else {
        // If there's an error, echo 'error' along with the specific error message
        echo 'error: ' . mysqli_error($link);
    }
    $vuserid = $_SESSION['userid'];
   
    $sql1 = "SELECT ticketstatus FROM $db.web_ticketstatus WHERE id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $ticketstatus = $row['ticketstatus'];
 
    add_audit_log($vuserid, 'delete_ticketstatus', '', 'Ticket status deleted '.$ticketstatus, $db);
   
    
}

//function to  get status on the basis of id in the edit form  Auth: VASTVIKTA NISHAD 07-02-2024
// Function to retrieve status data based on the provided ID
function getStatusData($link, $id) {
    // Initialize an empty array to store status data
    $statusData = array();
    // Check if the provided ID is not empty
    if (!empty($id)) {
        // SQL query to select the 'ticketstatus' column from 'web_ticketstatus' where the 'id' matches the provided ID
        $query = "SELECT `ticketstatus` FROM `web_ticketstatus` WHERE `id` = $id";
        // Execute the SQL query and store the result in the $result variable
        $result = mysqli_query($link, $query);
        // Check if the query was successful
        if ($result) {
            // Fetch the result as an associative array and store it in $statusData
            $statusData = mysqli_fetch_assoc($result);
            // Free up the memory associated with the result set
            mysqli_free_result($result);
        } 
    }
    // Return the status data, which may be an empty array or contain the fetched status information
    return $statusData;
}

//function to update or insert the data in the web_ticketstatus table  Auth: VASTVIKTA NISHAD 07-02-2024
// Function to insert or update status records in the 'web_ticketstatus' table
function insert_or_update_status(){
    // Global variable for the database connection
    global $link,$db;
    // Get the 'id' and 'status' parameters from the POST request
    $id = $_POST['id'];
    $status = $_POST['status'];
    $vuserid = $_SESSION['userid'];
    // Check if 'id' is empty, indicating a new record should be added
    if (empty($id)) {
        // Insert new record with provided 'status', setting default values for other columns
        $sql = "INSERT INTO `web_ticketstatus` (`ticketstatus`, `status`, `createdby`, `createdon`, `bgcolor`) VALUES ('$status', 1, 1, NOW(), NULL)";
        add_audit_log($vuserid, 'insert_ticketstatus', '', 'New Ticket status added '.$status, $db);
   
    } else {
        // Update existing record with the provided 'status' for the specified 'id'
        $sql = "UPDATE `web_ticketstatus` SET `status` = 1, `ticketstatus` = '$status' WHERE `id` = $id";
        add_audit_log($vuserid, 'update_ticketstatus', '', 'Ticket status updated '.$status, $db);
   
    }
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql);
    if (!$result){
        if (_DBGLOG_){
                DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB Status update or insert  error  : $sql". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Database error";
        echo json_encode($response);
        exit();
    }
    // Check if the query was successful
    if ($result) {
        // If successful, echo 'success'
        echo 'success';
    } else {
        // If there's an error, echo 'Error' along with the specific error message
        echo 'Error: ' . mysqli_error($link);
    }
}

//function to fetch the data  for view knowledge base Auth: VASTVIKTA NISHAD 08-02-2024
// Function to retrieve base data from the 'tbl_mst_faq' table
function getBaseData() {
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select all columns from 'tbl_mst_faq' where 'i_status' is '1'
    $query = "SELECT * FROM $db.tbl_mst_faq WHERE i_status = '1'";
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $query);
    // Return the result, which contains the retrieved base data
    return $result;
}

//function to delete  the data for view knowledge base Auth: VASTVIKTA NISHAD 08-02-2024
// hello  Function to delete a record from the 'tbl_mst_faq' table by updating the 'i_status' column to '0'
function base_delete() {
    // Global variables for the database connection
    global $db, $link;
    
    // Get the 'i_id' parameter from the POST request
    $id = $_POST['i_id'];
    
    // SQL query to update the 'i_status' column to '0' for the specified 'i_id' in the 'tbl_mst_faq' table
    $sql_update = "UPDATE $db.tbl_mst_faq SET i_status = '0' WHERE i_id = ?";
    
    // Prepare the SQL statement and store it in the $stmt variable
    $stmt = mysqli_prepare($link, $sql_update);
    if ($stmt === false) {
        // Log error if preparation fails
        if (_DBGLOG_) {
            DbgLog(_LOG_ERROR, __LINE__, __FILE__, "Base delete error in Knowledge Base (prepare): " . mysqli_error($link));
        }
        echo json_encode(['error' => true, 'error_msg' => 'Knowledge Base Database error (prepare)']);
        exit();
    }
    
    // Bind the 'i_id' parameter to the prepared statement
    mysqli_stmt_bind_param($stmt, 'i', $id);
    
    // Execute the prepared statement and store the result in the $res variable
    $res = mysqli_stmt_execute($stmt);
    if ($res === false) {
        // Log error if execution fails
        if (_DBGLOG_) {
            DbgLog(_LOG_ERROR, __LINE__, __FILE__, "Base delete error in Knowledge Base (execute): " . mysqli_error($link));
        }
        echo json_encode(['error' => true, 'error_msg' => 'Knowledge Base Database error (execute)']);
        exit();
    }
    
    // Check if the query was successful
    if (mysqli_stmt_affected_rows($stmt) > 0) {
        // If successful, echo 'success'
        echo 'success';
    } else {
        // If no rows were affected, echo an error message
        echo json_encode(['error' => true, 'error_msg' => 'No rows affected']);
    }
    
    $vuserid = $_SESSION['userid'];
   
    $sql1 = "SELECT v_qus FROM $db.tbl_mst_faq WHERE i_id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $v_qus = $row['v_qus'];
 
    add_audit_log($vuserid, 'delete_knowledgebase', '', 'Knowledge Base deleted '.$v_qus, $db);
   

    // Close the prepared statement
    mysqli_stmt_close($stmt);
    
    // Return the result of the deletion operation (true or false)
    return $res;
}
// Function to retrieve a specific record from the 'tbl_mst_faq' table based on the provided 'i_id'
function getBaseDataset($link, $id) {
    // Initialize an empty array to store base data
    $baseData = array();
    // Check if the provided 'i_id' is not empty
    if (!empty($id)) {
        // SQL query to select all columns from 'tbl_mst_faq' where 'i_id' matches the provided 'id'
        $query = "SELECT * FROM `tbl_mst_faq` WHERE `i_id` = $id";
        // Execute the SQL query and store the result in the $result variable
        $result = mysqli_query($link, $query);
        // Check if the query was successful
        if ($result) {
            // Fetch the result as an associative array and store it in $baseData
            $baseData = mysqli_fetch_assoc($result);
            // Free up the memory associated with the result set
            mysqli_free_result($result);
        } 
    }
    // Return the base data, which may be an empty array or contain the fetched record information
    return $baseData;
}

//function to insert or update data in the tbl_mst_faq table Auth: VASTVIKTA NISHAD 08-02-2024
// Function to handle the submission of base data (insert or update)
function handleBaseSubmit()
{
    global $db, $link;
    $vuserid = $_SESSION['userid'];
    extract($_POST);
    
    $qus = addslashes($v_qus);
    $ans = addslashes($v_ans);

    if ($_POST['action'] == 'submit_base') {
        $query = "INSERT INTO $db.tbl_mst_faq (v_qus, v_ans) VALUES ('$qus', '$ans')";

    add_audit_log($vuserid, 'insert_knowledgebase', '', 'New Knowledge Base added '.$qus, $db);
   
    } elseif ($_POST['action'] == 'update_base') {
        $id = (int)$i_id;
        $query = "UPDATE $db.tbl_mst_faq SET v_qus='$qus', v_ans='$ans' WHERE i_id=$id";

    add_audit_log($vuserid, 'update_knowledgebase', '', 'Knowledge Base updated '.$qus, $db);

    }

    if (mysqli_query($link, $query)) {
        echo json_encode(['status' => 'success']);
    } else {
        if (_DBGLOG_) {
            DbgLog(_LOG_ERROR, __LINE__, __FILE__, "DB Insert or Update error in Knowledge Base: $query " . mysqli_error($link));
        }
        echo json_encode(['status' => 'error', 'message' => mysqli_error($link)]);
    }
}
// Function to generate a date string based on key ('from' or 'to') and value (1 to 8)
function getFromDate($key, $val)
{
    // Get the current date and extract the year and month
    $cdt = date('m-Y');
    $dt = explode("-", $cdt);
    $current_year = $dt[1];
    $current_month = $dt[0];
    // Initialize the output variable
    $output = "";
    // Check if the key is 'from'
    if ($key == 'from') {
        // Handle different cases based on the provided value
        if ($val == 1) {
            $output = "01-" . $cdt;
        }if ($val == 2) {
            $cdt = date("m-Y", strtotime("-1 months"));
            $output = "01-" . $cdt;
        }if($val==3){
            $output="01-04-".($current_year);
        }if($val==4) {
            $output="01-04-".($current_year-1);
        }if($val==5){
            $time = strtotime(date("Y-m-d H:i:s"));
            $output = date('Y-m-d H:i:s',strtotime("last Monday", $time));
        } if($val==6) {
            $output=date("d-m-Y", strtotime("-1 day"));
        }if($val==7){
            $monday = strtotime("last monday");
            $monday = date('W', $monday)==date('W') ? $monday-7*86400 : $monday;
            $output = date("Y-m-d",$monday);
        }if($val==8){
            $output=date("d-m-Y", strtotime("-2 day"));
        }
    }
    else if ($key == 'to') {
        // Handle different cases based on the provided value for 'to'
        if ($val == 1) {
            $output = date('d') . "-" . $cdt;
        }if ($val == 2) {
            $cdt = date("m-Y", strtotime("-1 months"));
            $output = date('d', strtotime('last day of previous month')) . "-" . $cdt;
        }if($val==3){
            $output="31-03-".($current_year+1);
        }if($val==4){
            $output="31-03-".($current_year);
        }if($val==5) {
            $output = date("Y-m-d H:i:s");
        }if($val==6) {
            $output=date("d-m-Y", strtotime("-1 day"));
        }if($val==7){
            //$output=date("d-m-Y", strtotime("-1 day"));
            $monday = strtotime("last monday");
            $monday = date('W', $monday)==date('W') ? $monday-7*86400 : $monday;
            $sunday = strtotime(date("Y-m-d",$monday)." +6 days");
            $output = date("Y-m-d",$sunday);
        }if($val==8){
            $output = date("Y-m-d H:i:s");
        }
    }
// Return the generated date string
return $output;
}
// Function to retrieve email information for IN emails (changed email_type from OUT to IN)
function getToEmailInformation() {
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select all columns from 'web_email_information' where email_type is 'IN' and v_toemail is not empty
    $sql = "SELECT * FROM $db.web_email_information_out WHERE email_type='OUT' AND v_toemail!='' GROUP BY v_toemail";

    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql);
    // Return the result, which contains the retrieved email information
    return $result;
}
// Function to retrieve email information for IN emails as the sender (changed email_type from OUT to IN)
function getFromEmailInformation() {
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select all columns from 'web_email_information' where email_type is 'IN' and v_fromemail is not empty
    $sql = "SELECT * FROM $db.web_email_information_out WHERE email_type='OUT' AND v_fromemail!='' GROUP BY v_fromemail";
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql);
    // Return the result, which contains the retrieved email information
    return $result;
}
// Function to retrieve all email information for IN emails with optional date filtering (changed email_type from OUT to IN)
function getAllEmailInformation($datefilter) {
    // Global variables for the database connection
    global $link, $db;
    // SQL query to select all columns from 'web_email_information' where email_type is 'IN', v_toemail is not empty, and optional date filtering
    $qq = "SELECT * FROM $db.web_email_information_out WHERE email_type='OUT' AND v_toemail!='' $datefilter";
    // Execute the SQL query with optional date filtering and order the results by email date and ID
    $result  = mysqli_query($link, "$qq ORDER BY d_email_date DESC, EMAIL_ID DESC;");
    // Return the result, which contains the retrieved email information
    return $result;
}

// Function to fetch user login data from the database for the 'web_live_ip_log' table
// Auth: VASTVIKTA NISHAD 12-02-2024
function getUserLogin($datefilter){
    // Global variables for the database connection
    global $db, $link;
    $qq = "SELECT * FROM $db.logip AS l 
    LEFT JOIN $db.uniuserprofile AS u ON u.AtxUserName=l.UserName 
    LEFT JOIN $db.unigroupdetails AS ug ON u.AtxUserID=ug.ugdContactID
    WHERE l.Reason='login' AND atxGid != '0000'  $datefilter GROUP BY AtxUserName, DATE( AccessedAt )   ORDER BY AccessedAt DESC";
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $qq);
    return $result;
}
// Function to fetch data for viewing escalations from the 'escalationdays' table
// Auth: VASTVIKTA NISHAD 12-02-2024
function view_escalations()
{
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select all columns from the 'escalationdays' table
    $sql = "SELECT * FROM $db.escalationdays";
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql) or die(mysqli_error($link));
    // Return the result, which contains the retrieved data for viewing escalations
    return $result;
}
// Function used in web_view_escalation.php to convert Escalation To
// Auth: VASTVIKTA NISHAD 12-02-2024
function escalation_to($id)
{
    // Check the value of $id and assign the corresponding label to $res
    if ($id == 1) {
        $res = 'Customer';
    } else if ($id == 2) {
        $res = 'Department';
    } else {
        $res = 'Both';
    }
    // Return the result, which represents the converted Escalation To value
    return $res;
}
// Function to convert Escalation Media ID into a corresponding label
function escalation_media($id)
{
    // Check the value of $id and assign the corresponding label to $res
    if ($id == 1) {
        $res = 'Email';
    } else if ($id == 2) {
        $res = 'SMS';
    } else {
        $res = 'Both';
    }
    // Return the result, which represents the converted Escalation Media value
    return $res;
}
// Function to fetch data of escalation page based on the provided ID
function getEscalationData($link, $id)
{
    // Global variable for the database connection
    global $db;
    // SQL query to select all columns from 'escalationdays' where 'eid' matches the provided 'id'
    $sql_check_document = "SELECT * FROM $db.escalationdays WHERE eid='$id'";
    // Execute the SQL query and store the result in the $result_query variable
    $result_query = mysqli_query($link, $sql_check_document);
    // Fetch the result as an associative array and store it in $res
    $res = mysqli_fetch_assoc($result_query);
    // Return the result, which contains the retrieved escalation data
    return $res;
}
// Function to update data in the 'escalationdays' table
function update_escalation() {
    // Global variables for the database connection
    global $db, $link;
    
    $vuserid = $_SESSION['userid'];
    // Retrieve and sanitize the ID from the POST request
    $id = $_POST['id'];
    if (!isset($id)) {
        echo 'Error: ID is not set.';
        return;
    }
    
    // Log received ID
    error_log('Received ID: ' . $id);
    
    // Convert POST variables to appropriate data types
    $escalation_to = (int)$_POST['escalation_to'];
    $escalation_media = (int)$_POST['escalation_media'];
    $escalation_list = implode(",", $_POST['escalation_list']);

    // Use prepared statements to prevent SQL injection
    $sql_document = "UPDATE $db.escalationdays SET escalation_to=?, escalation_media=?, escalation_list=? WHERE eid=?";
    
    $stmt = mysqli_prepare($link, $sql_document);
    mysqli_stmt_bind_param($stmt, "iisi", $escalation_to, $escalation_media, $escalation_list, $id);
    
    // Execute the prepared statement and check for success
    $update_success = mysqli_stmt_execute($stmt);
    
    // Provide appropriate response based on the update result
    if ($update_success) {
        echo 'success';
    } else {
        // If there's an error, display the error message and log it to the server error log
        $error_message = 'Error updating escalation: ' . mysqli_stmt_error($stmt);
        echo $error_message;
        error_log($error_message);
    }

    $sql1 = "SELECT `level` FROM $db.escalationdays WHERE eid = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $level = $row['level'];
 
    add_audit_log($vuserid, 'escalation_update', '', 'Escalation Level ' .$level .' updated', $db);
          
    
    // Close the prepared statement
    mysqli_stmt_close($stmt);
}

// Function to fetch data for the 'imap' table in 'web_view_imap_smtp'
function getIMAPData()
{
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select all columns from 'tbl_connection' with a limit of 10 rows
    $sql = "SELECT * FROM $db.tbl_connection LIMIT 10";
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql) or die(mysqli_error($link));
    // Return the result, which contains the retrieved data for the 'imap' table
    return $result;
}
// Function to fetch data for the 'smtp' table in 'web_view_imap_smtp'
function getSMTPData()
{
    // Global variables for the database connection
    global $db, $link;
    // SQL query to select all columns from 'tbl_smtp_connection' with a limit of 10 rows
    $sql = "SELECT * FROM $db.tbl_smtp_connection LIMIT 10";
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql) or die(mysqli_error($link));
    // Return the result, which contains the retrieved data for the 'smtp' table
    return $result;
}
// Function to fetch data for the 'smtp' table based on the provided ID
function SMTPData($link, $id)
{
    // Global variable for the database connection
    global $db;
    // SQL query to select all columns from 'tbl_smtp_connection' where 'id' matches the provided 'id'
    $sql_check_document = "SELECT * FROM $db.tbl_smtp_connection WHERE id='$id'";
    // Execute the SQL query and store the result in the $result_query variable
    $result_query = mysqli_query($link, $sql_check_document);
    // Fetch the result as an associative array and store it in $res
    $res = mysqli_fetch_assoc($result_query);
    // Free the result set
    mysqli_free_result($result_query);
    // Return the result, which contains the retrieved data for the 'smtp' table
    return $res;
}

// Function to fetch data for the 'imap' table based on the provided ID
function IMAPData($link, $id)
{
    // Global variable for the database connection
    global $db;
    // SQL query to select all columns from 'tbl_connection' where 'I_ID' matches the provided 'id'
    $sql_check_document = "SELECT * FROM $db.tbl_connection WHERE I_ID='$id'";
    // Execute the SQL query and store the result in the $result_query variable
    $result_query = mysqli_query($link, $sql_check_document);
    // Fetch the result as an associative array and store it in $res using mysqli_fetch_assoc
    $res = mysqli_fetch_assoc($result_query);
    // Return the result, which contains the retrieved data for the 'imap' table
    return $res;
}

// Function to update data in the 'imap' table
function update_imap() {
    global $db, $link;
    $response = array();

    $vuserid = $_SESSION['userid'];
    // Retrieve and sanitize input data from the POST request
    $id = $_POST['id'];
    $v_connectionname = mysqli_real_escape_string($link, $_POST['v_connectionname']);
    $v_ipaddress = mysqli_real_escape_string($link, $_POST['v_ipaddress']);
    $v_username = mysqli_real_escape_string($link, $_POST['v_username']);
    $v_pasowrd = $_POST['v_pasowrd'];
    $v_type = mysqli_real_escape_string($link, $_POST['v_type']);
    $v_client_id = mysqli_real_escape_string($link, $_POST['v_client_id']);
    $v_client_secret = mysqli_real_escape_string($link, $_POST['v_client_secret']);
    $v_tenant = mysqli_real_escape_string($link, $_POST['v_tenant']);

    // Use prepared statements to prevent SQL injection
    $sql_document = "UPDATE `$db`.`tbl_connection` 
                     SET 
                        `v_connectionname`=?, 
                        `v_ipaddress`=?, 
                        `v_username`=?, 
                        `v_pasowrd`=?, 
                        `v_type`=?, 
                        `v_client_id`=?, 
                        `v_client_secret`=?, 
                        `v_tenant`=?
                     WHERE `I_ID`=?";

    $stmt = mysqli_prepare($link, $sql_document);

    if ($stmt) {
        mysqli_stmt_bind_param($stmt, "ssssssssi", 
                               $v_connectionname, 
                               $v_ipaddress, 
                               $v_username, 
                               $v_pasowrd, 
                               $v_type, 
                               $v_client_id, 
                               $v_client_secret, 
                               $v_tenant, 
                               $id);

        if (mysqli_stmt_execute($stmt)) {
            $response['status'] = 'success';
            $response['message'] = 'Update successful';

    
            add_audit_log($vuserid, 'imap_settings_update', '', 'IMAP settings updated', $db);
           
        } else {
            $response['status'] = 'error';
            $response['message'] = 'Execute error: ' . mysqli_error($link);
        }

        mysqli_stmt_close($stmt);
    } else {
        $response['status'] = 'error';
        $response['message'] = 'Prepare statement error: ' . mysqli_error($link);
    }

    header('Content-Type: application/json');
    echo json_encode($response);
    exit();
}

// Function to update data in the 'smtp' table
function update_smtp()
{
    // Global variables for the database connection
    global $db, $link;
    $vuserid = $_SESSION['userid'];
    // Initialize response array
    $response = array();

    // Check if required POST parameters are set
    if (isset($_POST['id'], $_POST['i_port'], $_POST['v_username'], $_POST['v_password'], $_POST['v_server'], $_POST['i_tls'], $_POST['i_debug'])) {
        // Retrieve and sanitize input data from the POST request
        $id = $_POST['id'];
        $i_port = $_POST['i_port'];
        $v_username = mysqli_real_escape_string($link, $_POST['v_username']); // Sanitize input
        $v_password = $_POST['v_password'];
        $v_server = $_POST['v_server'];
        $i_tls = $_POST['i_tls'];
        $i_debug = $_POST['i_debug'];

        // Use prepared statements to prevent SQL injection
        $sql_document = "UPDATE $db.tbl_smtp_connection SET i_port=?, v_username=?, v_password=?, v_server=?, i_tls=?, i_debug=? WHERE id=?";

        // Prepare the SQL statement
        $stmt = mysqli_prepare($link, $sql_document);

        // Check if the prepared statement was successfully created
        if ($stmt) {
            // Bind parameters to the prepared statement
            mysqli_stmt_bind_param($stmt, 'isssiii', $i_port, $v_username, $v_password, $v_server, $i_tls, $i_debug, $id);

            // Execute the prepared statement and check for success
            if (mysqli_stmt_execute($stmt)) {
                $response['status'] = 'success';
                $response['message'] = 'SMTP Settings Updated Successfully';
            } else {
                $response['status'] = 'error';
                $response['message'] = 'Execute error: ' . mysqli_error($link);
            }

            add_audit_log($vuserid, 'smtp_settings_update', '', 'SMTP settings updated', $db);
          
            // Close the prepared statement
            mysqli_stmt_close($stmt);
        } else {
            $response['status'] = 'error';
            $response['message'] = 'Prepare statement error: ' . mysqli_error($link);
        }
    
   
    } else {
        $response['status'] = 'error';
        $response['message'] = 'Missing required parameters';
    }

    // Send JSON response
    header('Content-Type: application/json');
    echo json_encode($response);
    exit();
}

// Function to fetch data for the 'tbl_disposition' table in 'web_view_dispostion'
// Auth: VASTVIKTA NISHAD 15-02-2024
function getDisposition() {
    // Global variable for the database connection
    global $link;
    // SQL query to select all columns from 'asterisk.tbl_disposition' where 'I_Status' is 1
    $sql_document = "SELECT * FROM asterisk.tbl_disposition WHERE I_Status=1 ORDER BY `V_DISPO` ASC";
    // Execute the SQL query and store the result in the $res variable
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result, which contains the retrieved data for the 'tbl_disposition' table
    return $res;
}
// Function to fetch data for the 'tbl_disposition' table based on the provided ID
// Auth: VASTVIKTA NISHAD 15-02-2024
function getDispositionID($id) {
    // Global variable for the database connection
    global $link;
    // SQL query to select all columns from 'asterisk.tbl_disposition' where 'I_ID' matches the provided 'id'
    $sql = "SELECT * FROM asterisk.tbl_disposition WHERE I_ID = '" . mysqli_real_escape_string($link, $id) . "'";
    // Execute the SQL query and store the result in the $fetch variable
    $fetch = mysqli_query($link, $sql);
    // Check if the query was successful
    if ($fetch) {
        // Fetch the result as an associative array and store it in $row
        $row = mysqli_fetch_assoc($fetch);
        // Return the result, which contains the retrieved data for the 'tbl_disposition' table based on the provided ID
        return $row;
    } else {
        // Return false if the query was not successful
        return false;
    }
}
// Function to delete a record in the 'tbl_disposition' table
// Auth: VASTVIKTA NISHAD 15-02-2024
function disposition_delete() {
    // Global variable for the database connection
    global $link,$db;
    // Retrieve the 'id' from the POST request
    $id = $_POST['id'];
    $vuserid = $_SESSION['userid'];
    // SQL query to update the 'I_Status' and 'I_Type' columns to 0 in 'asterisk.tbl_disposition' where 'I_ID' matches the provided 'id'
    $sql_delete = "UPDATE asterisk.tbl_disposition SET I_Status=0, I_Type=0 WHERE I_ID='" . mysqli_real_escape_string($link, $id) . "'";
    // Execute the SQL query and store the result in the $res variable
    $res = mysqli_query($link, $sql_delete);
    if (!$res){
        if (_DBGLOG_){
                DbgLog(_LOG_ERROR,__LINE__, __FILE__,"Disposition Delete error : $sql_delete". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Disposition Database error";
        echo json_encode($response);
        exit();
    }
    // Provide appropriate response based on the deletion result
    if ($res) {
        echo 'success';
    } else {
        echo 'error: ' . mysqli_error($link);
    }
   
    add_audit_log($vuserid, 'disposition_deleted', '', 'Disposition deleted', $db);
     
}
// Function to update or insert data in the 'tbl_disposition' table
// Auth: Roshni Gupta 02 Aug 2025
function insert_or_update_disposition() {
    // Global variable for the database connection
    global $link,$db;
    $vuserid = $_SESSION['userid'];
    // Retrieve and sanitize input data from the POST request
    $id = isset($_POST['id']) ? mysqli_real_escape_string($link, $_POST['id']) : '';
    $disposition = isset($_POST['disposition']) ? mysqli_real_escape_string($link, $_POST['disposition']) : '';
    // Check if 'id' is empty to determine whether to insert or update
    if (empty($id)) {
        // SQL query to insert a new record in 'asterisk.tbl_disposition'
        $sql_document = "INSERT INTO asterisk.tbl_disposition(V_DISPO, V_DISPOSITION, I_Status, I_Type) VALUES ('$disposition', '$disposition', 1, 1)";
        add_audit_log($vuserid, 'dispposition_insert', '', 'New Disposition added '. $disposition, $db);
   
    } else {
        // SQL query to update an existing record in 'asterisk.tbl_disposition'
        $sql_document = "UPDATE asterisk.tbl_disposition SET V_DISPO = '$disposition', V_DISPOSITION = '$disposition' WHERE I_ID = '$id'";
        add_audit_log($vuserid, 'disposition_updated', '', 'Dispostion updated '. $disposition, $db);
   
    }
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $sql_document);
    if (!$result){
        if (_DBGLOG_){
                DbgLog(_LOG_ERROR,__LINE__, __FILE__,"Insert or Update Disposition error : $sql_document". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Disposition Database error";
        echo json_encode($response);
        exit();
    }
    // Provide appropriate response based on the execution result
    if ($result) {
        echo 'success';
    } else {
        echo 'Error: ' . mysqli_error($link);
    }
}
// function to update the mail signtaure 
function update_mail_sign() {
    global $link, $db;

    $sign = mysqli_real_escape_string($link, $_POST['sign']);

    $uploadDir = $_SERVER['DOCUMENT_ROOT'] . "/ensembler/CRM/admin/SignatureImage/";
    $allowedTypes = ['image/jpeg', 'image/png','image/jpg'];

    if (!is_dir($uploadDir)) {
        mkdir($uploadDir, 0777, true);
    }

    if (!empty($_FILES['signature_image']['name'])) {

        if (!in_array($_FILES['signature_image']['type'], $allowedTypes)) {
            echo "Invalid image format";
            exit;
        }

        // Fetch old image (single row)
        $fetchSql = "SELECT MailSignImage FROM {$db}.tbl_mailformats 
                     WHERE MailSignImage IS NOT NULL LIMIT 1";
        $fetchRes = mysqli_query($link, $fetchSql);

        if ($fetchRes && ($row = mysqli_fetch_assoc($fetchRes))) {
            if (!empty($row['MailSignImage'])) {
                $oldPath = $_SERVER['DOCUMENT_ROOT'] . '/' . $row['MailSignImage'];
                if (file_exists($oldPath)) {
                    unlink($oldPath);
                }
            }
        }

        $ext = pathinfo($_FILES['signature_image']['name'], PATHINFO_EXTENSION);
        $newFileName = "signature_" . time() . "." . $ext;
        $targetPath = $uploadDir . $newFileName;

        if (!move_uploaded_file($_FILES['signature_image']['tmp_name'], $targetPath)) {
            echo "File upload failed";
            exit;
        }

        $imageDbPath = "admin/SignatureImage/" . $newFileName;

        $sql = "
            UPDATE {$db}.tbl_mailformats
            SET MailSignature = '$sign',
                MailSignImage = '$imageDbPath'
        ";
    } else {
        $sql = "
            UPDATE {$db}.tbl_mailformats
            SET MailSignature = '$sign'
        ";
    }

    if (!mysqli_query($link, $sql)) {
        if (defined('_DBGLOG_') && _DBGLOG_) {
            DbgLog(
                _LOG_ERROR,
                __LINE__,
                __FILE__,
                "DB update mail: $sql - " . mysqli_error($link)
            );
        }
        echo "Database error";
        exit();
    }

    echo "success";
}




// Function to fetch data for callbacks from the 'autodial_callbacks' table
function getCallbacks() {
    // Global variable for the database connection
    global $link;
    // SQL query to select all columns from 'asterisk.autodial_callbacks' where 'comments' is 'CALL BACK'
    $sql_document = "SELECT * FROM asterisk.autodial_callbacks WHERE comments='CALL BACK' ORDER BY `callback_id` DESC";
    // Execute the SQL query and store the result in the $res variable
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result, which contains the retrieved data for callbacks
    return $res;
}
// Function to fetch callback data from the 'autodial_callbacks' table based on the provided id
function get_callbacks($id) {
    // Global variable for the database connection
    global $link;
    // SQL query to select all columns from 'asterisk.autodial_callbacks' where 'callback_id' matches the provided 'id'
    $sql_document = "SELECT * FROM asterisk.autodial_callbacks WHERE callback_id='" . mysqli_real_escape_string($link, $id) . "'";
    // Execute the SQL query and store the result in the $fetch variable
    $fetch = mysqli_query($link, $sql_document) or die("Could not select");
    // Check if the query was successful
    if ($fetch) {
        // Fetch the result as an associative array and store it in $row
        $row = mysqli_fetch_assoc($fetch);
        // Return the result, which contains the retrieved data for callbacks based on the provided id
        return $row;
    } else {
        // Return false if the query was not successful
        return false;
    }
}
// Function to update callback values in the 'autodial_callbacks' table
function updateCallbacks() {
    // Global variable for the database connection
    global $db,$link;
    $vuserid = $_SESSION['userid'];
    // Retrieve 'id' and 'callback_time' from the POST request
    $id = $_POST['id'];
    $callback_time = $_POST['callback_time'];
    $callback_alert_hour = $_POST['callback_alert_hour'];
    $callback_alert_minute = $_POST['callback_alert_minute'];
    $reassignCallback = $_POST['reassignCallback'];
    $callback_remark = mysqli_real_escape_string($link, $_POST['callback_remark']); // Sanitize input

    // Combine values to create the time in hh:mm:ss format
    $callback_alert_time = "$callback_alert_hour:$callback_alert_minute:00";

    // Format the callback time to match the database date-time format
    $formatted_callback_time = date('Y-m-d H:i:s', strtotime($_POST['callback_time']));

   $str = '';

    if (!empty($reassignCallback)) {
        $str = ", recipient = 'USERONLY', user = '" . mysqli_real_escape_string($link, $reassignCallback) . "'";
    } else {
        $str = ", recipient = 'ANYONE', user = NULL";
    }

    // SQL query
    $sql_document = "UPDATE asterisk.autodial_callbacks 
                    SET callback_time = '" . mysqli_real_escape_string($link, $formatted_callback_time) . "', 
                        callback_alert_time = '" . mysqli_real_escape_string($link, $callback_alert_time) . "',
                        remark = '" . mysqli_real_escape_string($link, $callback_remark) . "'
                        $str
                    WHERE callback_id='" . mysqli_real_escape_string($link, $id) . "'";

    $result = mysqli_query($link, $sql_document);
    if (!$result){
        if (_DBGLOG_){
                DbgLog(_LOG_ERROR,__LINE__, __FILE__,"Update callback time error : $sql_document". mysqli_error($link));
        }
        $response['error'] = TRUE;
        $response['error_msg'] = "Callback Database error";
        echo json_encode($response);
        exit();
    }
   
    add_audit_log($vuserid, 'callback_update', '', 'Callback time updated', $db);
     
    // Provide appropriate response based on the execution result
    if ($result) {
        echo 'success';
    } else {
        echo 'Error: ' . mysqli_error($link);
    }
}
// [vastvikta nishad][22-12-2025]
// this function is for teching data of  bulk email sms template [vastvikta][22-01-2025]
function view_bulk_data(){
     // Global variables for the database connection
    global $db,$link;
    $query = "SELECT * FROM $db.bulksms_templates";
     // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link,$query);
     // Return the result, which contains the retrieved base data
    return $result;
}
function getbulkData($link, $id) {
    // Initialize an empty array to store status data
    $bulkData = array();
    // Check if the provided ID is not empty
    if (!empty($id)) {
        // SQL query to select the 'ticketstatus' column from 'web_ticketstatus' where the 'id' matches the provided ID
        $query = "SELECT * FROM bulksms_templates WHERE `id` = $id";
        // Execute the SQL query and store the result in the $result variable
        $result = mysqli_query($link, $query);
        // Check if the query was successful
        if ($result) {
            // Fetch the result as an associative array and store it in $statusData
            $bulkData = mysqli_fetch_assoc($result);
            // Free up the memory associated with the result set
            mysqli_free_result($result);
        } 
    }
    // Return the status data, which may be an empty array or contain the fetched status information
    return $bulkData;
}
function bulk_delete() {
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
  
    $sql_update = "DELETE From $db.bulksms_templates WHERE id = '$id'";
   
    // Execute the SQL query
    $res = mysqli_query($link, $sql_update);
        if (!$res){
            if (_DBGLOG_){
                    DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete  error BULK format : $sql_update". mysqli_error($link));
            }
            $response['error'] = TRUE;
            $response['error_msg'] = "BULK TEMPLATE DELETE format Database error";
            echo json_encode($response);
            exit();
        }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
    }
   
     $vuserid = $_SESSION['userid'];
    
     $sql1 = "SELECT `name`  FROM $db.bulksms_templates WHERE id = '$id'";
     $res1 = mysqli_query($link, $sql1);
     $row = mysqli_fetch_assoc($res1);
     $name = $row['name'];
  
     add_audit_log($vuserid, 'delete_bulk_sms_email_template', '', 'Bulk SMS EMAIL template deleted '.$name, $db);
    
}
function update_bulk_data() {
    global $db, $link;

    $vuserid = $_SESSION['userid'];
    // Sanitize inputs to prevent SQL injection
    $name = mysqli_real_escape_string($link, $_REQUEST['template_name'] ?? '');
    $template_content = mysqli_real_escape_string($link, $_REQUEST['template_content'] ?? '');
    $type = mysqli_real_escape_string($link, $_REQUEST['type'] ?? '');
    $slug = mysqli_real_escape_string($link, $_REQUEST['slug'] ?? '');
    $id = isset($_REQUEST['id']) ? (int)$_REQUEST['id'] : 0;
    // Initialize the response array
    $response = [
        'status' => 'error',
        'message' => ''
    ];

    // Check if the ID is empty (indicating a new category insertion)
    if ($id == 0) {
        // New entry - insert query
        $sql_document = "INSERT INTO $db.bulksms_templates (name, template_content, type, slug) VALUES ('$name', '$template_content', '$type', '$slug')";
        
        // Execute the query
        $res = mysqli_query($link, $sql_document);

        if (!$res) {
            if (_DBGLOG_) {
                DbgLog(_LOG_ERROR, __LINE__, __FILE__, "DB insert error bulksms templates: $sql_document" . mysqli_error($link));
            }
            $response['message'] = "Database insert error";
            echo json_encode($response); // Return the response as JSON
            exit();
        }
        $response['status'] = 'success';
        $response['message'] = "Successfully added!";
        add_audit_log($vuserid, 'insert_bulk_sms_email_template', '', 'New Bulk SMS EMAIL template added '.$name, $db);
    
    } else {
        // Update entry
        $sql3 = "UPDATE $db.bulksms_templates SET name = '$name', template_content = '$template_content', type = '$type', slug = '$slug' WHERE id = $id";
      
        // Execute the query
        $res1 = mysqli_query($link, $sql3);

        if (!$res1) {
            if (_DBGLOG_) {
                DbgLog(_LOG_ERROR, __LINE__, __FILE__, "DB Update error template: $sql3" . mysqli_error($link));
            }
            $response['message'] = "Database update error";
            echo json_encode($response); // Return the response as JSON
            exit();
        }
        $response['status'] = 'success';
        $response['message'] = "Successfully updated!";
        add_audit_log($vuserid, 'update_bulk_sms_email_template', '', 'Bulk SMS EMAIL template updated '.$name, $db);
    
    }

    // Output the result message as JSON
    echo json_encode($response);
}



/******************** This code for Bullitin Module *************************/ 
function edit_bullitin() {
    // Global variables for the database connection
    global $link,$db;
    // SQL query to select all columns from 'tbl_mst_faq' where 'i_status' is '1'
    // $query = "SELECT city FROM $db.web_city WHERE id='" . $res['iDistrictID'] . "'";
    $query = "SELECT city FROM $db.web_city";
    // Execute the SQL query and store the result in the $result variable
    $result = mysqli_query($link, $query);
    // Return the result, which contains the retrieved base data
    return $result;
}
/************************ END ********************************/ 
/*********** This code for Licence Modules ***************/ 
function View_licence_info(){
    global $link, $db;
    $sql="SELECT DisplayName, UserLicence,user_type, atxGid FROM $db.unigroupid WHERE atxGid != '0000' AND status ='1' ORDER BY DisplayName ";
    $res= mysqli_query($link, $sql);
    return $res;
}
function View_licence_company_detail(){
    global $dbname, $link;
    $sql="SELECT V_CompanyName, V_CompanyAddress, D_DateofRegistration, Website, V_PhoneNo FROM $dbname.tbl_mst_company";
    $rs = mysqli_query($link, $sql);
    return $rs;
}
function F_Count_User($companyid,$db,$usertype){
   global $link,$dbname;
    $sql_user_ID = "SELECT count(u.AtxDesignation) as ID FROM  $dbname.tbl_mst_user_company as tmuc, $db.uniuserprofile as u where 
    tmuc.V_EmailID = u.AtxEmail AND u.AtxDesignation = '$usertype' AND tmuc.I_CompanyID='$companyid' AND u.AtxUserStatus = '1' ";
    $Fetch_USERID=mysqli_query($link,$sql_user_ID);
    $row=mysqli_fetch_array($Fetch_USERID);
    return $USERID=$row['ID'];
}
// This code for check which type of licence and display used licence
function F_Count_User_Licence($companyid,$db,$usertype,$licence_type){
   global $link,$dbname,$licence_Concurrent,$db_asterisk;
   if($licence_type == $licence_Concurrent && $usertype == 'Agent'){
        $sql_user_ID = "SELECT count(*) as total FROM $db_asterisk.`autodial_live_agents`";
        $Fetch_USERID=mysqli_query($link,$sql_user_ID);
        $row=mysqli_fetch_array($Fetch_USERID);
        return $USERID=$row['total'];
   }else{
        $sql_user_ID = "SELECT count(u.AtxDesignation) as ID FROM  $dbname.tbl_mst_user_company as tmuc, $db.uniuserprofile as u where 
        tmuc.V_EmailID = u.AtxEmail AND u.AtxDesignation = '$usertype' AND tmuc.I_CompanyID='$companyid' AND u.AtxUserStatus = '1' ";
        $Fetch_USERID=mysqli_query($link,$sql_user_ID);
        $row=mysqli_fetch_array($Fetch_USERID);
        return $USERID=$row['ID'];
   }
    
}
// get email for sending crm daily report(adhoc)
function get_adhoc_mails() 
{
    global $link, $dbname;

    $query = $link->query("
        SELECT adhoc_mail, adhoc_mail_2 
        FROM $dbname.tbl_connection 
        WHERE I_ID = 1
        LIMIT 1
    ");

    return $query ? $query->fetch_assoc() : [];
}
/******************** This code for Adhoc Email Update *************************/ 
function adhoc_mail_update($email1, $email2) {
    global $link, $dbname;

    // Basic sanitization
    $email1 = mysqli_real_escape_string($link, $email1);
    $email2 = mysqli_real_escape_string($link, $email2);

    $result = $link->query("
        UPDATE $dbname.tbl_connection 
        SET 
            adhoc_mail = '$email1',
            adhoc_mail_2 = '$email2'
        WHERE I_ID = 1
    ");

    if ($result) {
        echo 'success';
    } else {
        echo 'Error: ' . mysqli_error($link);
    }
}
function view_webchat_template(){
    global $db, $link;
    // SQL query to select all records 
    $sql_document = "SELECT * FROM $db.webchat_template WHERE status = '1'";
    // Execute the query or die with an error message if it fails
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result set
    return $res;
}
function view_whatsapp_template(){
    global $db, $link;
    // SQL query to select all records 
    $sql_document = "SELECT * FROM $db.whatsapp_template WHERE status = '1'";
    // Execute the query or die with an error message if it fails
    $res = mysqli_query($link, $sql_document) or die("Could not select");
    // Return the result set
    return $res;
}
// Function to get SMS template data from tbl_smsformat based on the provided id
function getwebchatData($link, $id) {
    global $db;
    $Data = array();

    // Check if $id is not empty
    if ($id) {
        // Escape the input to prevent SQL injection
        $safe_id = mysqli_real_escape_string($link, $id);

        // Construct the query
        $sql = "SELECT temp_name, temp_type, temp_content FROM {$db}.webchat_template WHERE id = '$safe_id'";

        // Execute the query
        $result = mysqli_query($link, $sql);

        // Check if query was successful
        if ($result && mysqli_num_rows($result) > 0) {
            $row = mysqli_fetch_assoc($result);
            $Data['temp_name'] = $row['temp_name'];
            $Data['temp_type'] = $row['temp_type'];
            $Data['temp_content'] = $row['temp_content'];
        }

        // Free result set
        if ($result) {
            mysqli_free_result($result);
        }
    }

    return $Data;
}
function webchat_delete() {
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
    $sql_update = "UPDATE $db.webchat_template SET status = '0' WHERE id = '$id'";
    // Execute the SQL query
    $res = mysqli_query($link, $sql_update);
        if (!$res){
            if (_DBGLOG_){
                    DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete  error webchat template: $sql_update". mysqli_error($link));
            }
            $response['error'] = TRUE;
            $response['error_msg'] = "webchat template Database error";
            echo json_encode($response);
            exit();
        }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
    }
    // Get user ID from the session
    $vuserid = $_SESSION['userid'];
    $sql1 = "SELECT temp_content FROM $db.webchat_template WHERE id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $smstemplatename = $row['temp_content'];
 
    add_audit_log($vuserid, 'delete_webchattemplate', '', 'WebChat Template deleted '.$smstemplatename, $db);
   
}
function insert_or_update_webchat($link){
    global $db;

    $vuserid = $_SESSION['userid'];
    $SmsID = isset($_POST['id']) ? (int)$_POST['id'] : 0;
    $SmsTemplateName = isset($_POST['template_name']) ? mysqli_real_escape_string($link, $_POST['template_name']) : '';
    $SmsType = isset($_POST['type']) ? mysqli_real_escape_string($link, $_POST['type']) : '';
    $SmsBody = isset($_POST['body']) ? mysqli_real_escape_string($link, $_POST['body']) : '';
    $SmsStatus = 1;

    if ($_POST['action'] == 'submit_webchat') {
        $create_date = date('Y-m-d H:i:s');

        $sql = "INSERT INTO `{$db}`.`webchat_template` 
                (`temp_name`, `temp_type`, `temp_content`, `status`, `created_by`, `create_date`) 
                VALUES ('$SmsTemplateName', '$SmsType', '$SmsBody', '$SmsStatus', $vuserid, '$create_date')";

        if (mysqli_query($link, $sql)) {
            $SmsID = mysqli_insert_id($link);
            echo 'success';
            add_audit_log($vuserid, 'insert_webchat_template', '', 'New WebChat template added: '.$SmsTemplateName, $db);
        } else {
            echo json_encode([
                'error' => true,
                'error_msg' => 'Database error: ' . mysqli_error($link)
            ]);
        }

    } elseif ($_POST['action'] == 'update_webchat') {
        $SmsStatus = isset($_POST['status']) ? (int)$_POST['status'] : 1;

        $sql = "UPDATE `{$db}`.`webchat_template` 
                SET `temp_name` = '$SmsTemplateName',
                    `temp_type` = '$SmsType',
                    `temp_content` = '$SmsBody',
                    `status` = '$SmsStatus'
                WHERE `id` = $SmsID";

        if (mysqli_query($link, $sql)) {
            echo 'success';
            add_audit_log($vuserid, 'update_webchat_template', '', 'WebChat template updated: '.$SmsTemplateName, $db);
        } else {
            echo json_encode([
                'error' => true,
                'error_msg' => 'Database error: ' . mysqli_error($link)
            ]);
        }
    }
}
// Function to get SMS template data from tbl_smsformat based on the provided id
function getwhatsappData($link, $id) {
    global $db;
    $Data = array();

    // Check if $id is not empty
    if ($id) {
        // Escape the input to prevent SQL injection
        $safe_id = mysqli_real_escape_string($link, $id);

        // Construct the query
        $sql = "SELECT temp_name, temp_type, temp_content FROM {$db}.whatsapp_template WHERE id = '$safe_id'";

        // Execute the query
        $result = mysqli_query($link, $sql);

        // Check if query was successful
        if ($result && mysqli_num_rows($result) > 0) {
            $row = mysqli_fetch_assoc($result);
            $Data['temp_name'] = $row['temp_name'];
            $Data['temp_type'] = $row['temp_type'];
            $Data['temp_content'] = $row['temp_content'];
        }

        // Free result set
        if ($result) {
            mysqli_free_result($result);
        }
    }

    return $Data;
}
function whatsapp_delete() {
    global $db, $link;
    // Retrieve the ID from the POST request
    $id = $_POST['id'];
    $sql_update = "UPDATE $db.whatsapp_template SET status = '0' WHERE id = '$id'";
    // Execute the SQL query
    $res = mysqli_query($link, $sql_update);
        if (!$res){
            if (_DBGLOG_){
                    DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete  error whatsapp template: $sql_update". mysqli_error($link));
            }
            $response['error'] = TRUE;
            $response['error_msg'] = "whatsapp template Database error";
            echo json_encode($response);
            exit();
        }
    // Check if the query was successful
    if ($res) {
        echo 'success'; // Return success message to the client
    } else {
        echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
    }
    // Get user ID from the session
    $vuserid = $_SESSION['userid'];
    $sql1 = "SELECT temp_content FROM $db.whatsapp_template WHERE id = '$id'";
    $res1 = mysqli_query($link, $sql1);
    $row = mysqli_fetch_assoc($res1);
    $smstemplatename = $row['temp_content'];
 
    add_audit_log($vuserid, 'delete_whatsapptemplate', '', 'WhatsaApp Template deleted '.$smstemplatename, $db);
   
}
function insert_or_update_whatsapp($link){
    global $db;

    $vuserid = $_SESSION['userid'];
    $SmsID = isset($_POST['id']) ? (int)$_POST['id'] : 0;
    $SmsTemplateName = isset($_POST['template_name']) ? mysqli_real_escape_string($link, $_POST['template_name']) : '';
    $SmsType = isset($_POST['type']) ? mysqli_real_escape_string($link, $_POST['type']) : '';
    $SmsBody = isset($_POST['body']) ? mysqli_real_escape_string($link, $_POST['body']) : '';
    $SmsStatus = 1;

    if ($_POST['action'] == 'submit_whatsapp') {
        $create_date = date('Y-m-d H:i:s');

        $sql = "INSERT INTO `{$db}`.`whatsapp_template` 
                (`temp_name`, `temp_type`, `temp_content`, `status`, `created_by`, `create_date`) 
                VALUES ('$SmsTemplateName', '$SmsType', '$SmsBody', '$SmsStatus', $vuserid, '$create_date')";

        if (mysqli_query($link, $sql)) {
            $SmsID = mysqli_insert_id($link);
            echo 'success';
            add_audit_log($vuserid, 'insert_whatsapp_template', '', 'New WhatsApp template added: '.$SmsTemplateName, $db);
        } else {
            echo json_encode([
                'error' => true,
                'error_msg' => 'Database error: ' . mysqli_error($link)
            ]);
        }

    } elseif ($_POST['action'] == 'update_whatsapp') {
        $SmsStatus = isset($_POST['status']) ? (int)$_POST['status'] : 1;

        $sql = "UPDATE `{$db}`.`whatsapp_template` 
                SET `temp_name` = '$SmsTemplateName',
                    `temp_type` = '$SmsType',
                    `temp_content` = '$SmsBody',
                    `status` = '$SmsStatus'
                WHERE `id` = $SmsID";

        if (mysqli_query($link, $sql)) {
            echo 'success';
            add_audit_log($vuserid, 'update_whatsapp_template', '', 'WhatsApp template updated: '.$SmsTemplateName, $db);
        } else {
            echo json_encode([
                'error' => true,
                'error_msg' => 'Database error: ' . mysqli_error($link)
            ]);
        }
    }
}

function saveEmailRule($conn, $postData) {
    global $db;
    $stickyAgent = intval($postData['stickyAgent']);
    $priorityEscalationHour = $postData['priorityEscalationHour'] ?? '';
    $nonpriorityEscalationHour = $postData['nonpriorityEscalationHour'] ?? '';
    $escalationTime = $postData['escalationTime'] ?? '';
    $escalationEmailIDs = trim($postData['escalationEmailIDs'] ?? '');
    $blockedMails = trim($postData['blockedMails'] ?? '');
    $microsoftDynamics = intval($postData['microsoftDynamics']);
    $enableLogging = intval($postData['enableLogging']);
    $isActive = 1;

    $vuserid = $_SESSION['user_id'] ?? 0;

    $check = $conn->query("SELECT * FROM EmailAssignmentRules LIMIT 1");
    if ($check && $check->num_rows > 0) {
        $row = $check->fetch_assoc();
        $id = $row['id'];

        $update = "UPDATE EmailAssignmentRules SET 
            stickyAgent = $stickyAgent,
            priorityEscalationHour = '$priorityEscalationHour',
            nonpriorityEscalationHour = '$nonpriorityEscalationHour',
            escalationTime = '$escalationTime',
            escalationEmailIDs = '$escalationEmailIDs',
            blockedMails = '$blockedMails',
            microsoftDynamics = $microsoftDynamics,
            enableLogging = $enableLogging,
            isActive = $isActive,
            timestamp = CURRENT_TIMESTAMP
            WHERE id = $id";

        if ($conn->query($update)) {
            add_audit_log($vuserid, 'update_email_rule', json_encode($row), "Updated ID $id", $db);
            return ['status' => 'success', 'message' => 'Record updated successfully.'];
        } else {
            return ['status' => 'error', 'message' => "Update failed: {$conn->error}"];
        }
    } else {
        $insert = "INSERT INTO EmailAssignmentRules (
            stickyAgent, priorityEscalationHour, nonpriorityEscalationHour, escalationTime,
            escalationEmailIDs, blockedMails, microsoftDynamics, enableLogging, isActive
        ) VALUES (
            $stickyAgent, '$priorityEscalationHour', '$nonpriorityEscalationHour', '$escalationTime',
            '$escalationEmailIDs', '$blockedMails', $microsoftDynamics, $enableLogging, $isActive
        )";

        if ($conn->query($insert)) {
            $newId = $conn->insert_id;
            add_audit_log($vuserid, 'insert_email_rule', '', "Inserted ID $newId", $db);
            return ['status' => 'success', 'message' => 'Record inserted successfully.'];
        } else {
            return ['status' => 'error', 'message' => 'Insert failed: ' . $conn->error];
        }
    }
}



// function to delete blocked emailid from the table [vastvikta]
// function spam_mail_delete(){
//     global $db,$link;
//     $mail = $_POST['id'];
//     $sql = "DELETE FROM $db.tbl_blocked_mailid  WHERE emailid = '$mail'";
//     $res = mysqli_query($link, $sql);
//     if (!$res){
//         if (_DBGLOG_){
//                 DbgLog(_LOG_ERROR,__LINE__, __FILE__,"DB delete  error : $sql". mysqli_error($link));
//         }
//         $response['error'] = TRUE;
//         $response['error_msg'] = "Database error";
//         echo json_encode($response);
//         exit();
//     }
//     if ($res) {
//         echo 'success'; // Return success message to the client
//     } else {
//         echo 'error: ' . mysqli_error($link); // Return an error message with details to the client
//     }

//     $vuserid = $_SESSION['userid'];
//     add_audit_log($vuserid, 'spam_mail_delete', '', 'Spam Mail Deleted '.$mail, $db);
   
// }
// function to add blocked email id [vastvikta]
// function spam_mail(){
//     global $db, $link;

//     if (!isset($_POST['mail'], $_SESSION['userid'])) {
//         echo json_encode(['error' => true, 'error_msg' => 'Missing required fields']);
//         exit();
//     }

//     $mail = mysqli_real_escape_string($link, $_POST['mail']);
//     $vuserid = intval($_SESSION['userid']);

//     $sql = "INSERT INTO $db.tbl_blocked_mailid (`emailid`, `created_by`, `datetime`) VALUES ('$mail', $vuserid, NOW())";

//     $res = mysqli_query($link, $sql);

//     if (!$res){
//         if (defined('_DBGLOG_') && _DBGLOG_){
//             DbgLog(_LOG_ERROR, __LINE__, __FILE__, "DB insert error: $sql | " . mysqli_error($link));
//         }
//         echo json_encode(['error' => true, 'error_msg' => 'Database error']);
//         exit();
//     }

//     echo 'success';

//         add_audit_log($vuserid, 'spam_mail_add', '', 'Spam Mail Added ' . $mail, $db);
   
// }

// function view_spam(){
//     global $db,$link;
//     $sql = "SELECT * FROM $db.tbl_blocked_mailid  ORDER BY emailid";
//     $res = mysqli_query($link, $sql) or die("Could not select");
//     // Return the result set
//     return $res;
// }
function getEmailRule($conn) {
    $query = "SELECT * FROM EmailAssignmentRules LIMIT 1";
    $result = $conn->query($query);

    if ($result && $result->num_rows > 0) {
        $row = $result->fetch_assoc();

        return [
            'status' => 'success',
            'data' => $row
        ];
    } else {
        return [
            'status' => 'empty',
            'message' => 'No record found.'
        ];
    }
}

function fetchEmailRules($conn) {
    $rows = [];
    $result = $conn->query("SELECT * FROM EmailAssignmentRules ORDER BY id DESC");

    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    }

    return $rows;
}
/************************ END ********************************/ 

?>
