<?php
/* This file used for bulk sms and email related function-aarti*/
include("../../config/web_mysqlconnect.php");
include("date.php");
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);
// commented the logic related to the quota sending message [vastvikta][28-11-2025]
class BULK{
	function __construct(){
		global $link,$db;
		$this->customer_array = [] ;
		$this->bad_customer_array = [] ;
		// $this->load->library('excel');
		if(isset($_POST['action']) || isset($_GET['action'])){
			if($_POST['action'] == 'import_file'){
				$this->import_file();
			}
			if($_POST['action'] == 'send_sms_index'){
				$this->send_sms_index();
			}
			if($_GET['action'] == 'get_contact_list'){
				$this->get_contact_list();
			}
			if($_POST['action']== 'get_campaign_content'){
				$this->get_campaign_content();
			}
			if($_POST['action']== 'get_template_content'){
				$this->get_template_content();
			}
			if($_GET['action'] == 'ajax_campaign_contact_count'){
				$this->ajax_campaign_contact_count();
			}
			// By Farhan Akhtar on 13-02-2025
			if($_GET['action'] == 'GET-EMAIL-LIST'){
				$this->export_bulk_emails($_GET['startDate'], $_GET['endDate']);
			}

			// for bulk delete record aarti on 31-10-2025
			if($_POST['action']== 'delete_bulk'){
				$this->delete_bulk();
			}
			// for bulk update_bulk record aarti on 31-10-2025
			if($_POST['action']== 'update_bulk_campaign'){
				$this->update_bulk_campaign();
				exit; // stop executing the rest of the page
			}
			if($_POST['action']== 'bulkcampaign_report'){
				$this->bulkcampaign_report();
			}
			if($_POST['action'] == 'add_remark'){
				$this->add_remark(); 
			}
			// Level final approved by backoffice
			if($_POST['action'] == 'level_second_final_approve_bulk'){
				$this->level_second_final_approve_bulk(); 
			}
			if($_POST['action'] == 'get_csv_fields'){
				$this->get_csv_fields();
			}
			if($_POST['action'] == 'generate_csv_from_db'){
				$this->generate_csv_from_db();
			}
			if ($_POST['action'] === 'check_campaign_name') {
				$this->check_campaign_name();
			}
			// resume code
			if($_POST['action'] == 'resume_bulk_campaign'){
				$this->resume_bulk_campaign();
			}
			// pause code
			if($_POST['action'] == 'pause_bulk_campaign'){
				$this->pause_bulk_campaign();
			}
			// run code
			if($_POST['action'] == 'run_bulk_campaign'){
				$this->run_bulk_campaign();
			}
			if($_POST['action'] == 'save_template'){
				$this->save_template();
			}
			if($_POST['action'] == 'get_template_saved'){
				get_template_saved();
			}
			if (isset($_POST['action']) && $_POST['action'] == 'upload_preview_csv') {
		    if (!isset($_FILES['file']) || empty($_FILES['file']['name'])) {
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'No file selected.'
		        ]);
		        exit;
		    }

		    $file = $_FILES['file'];

		    if ($file['error'] != 0) {
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'File upload error.'
		        ]);
		        exit;
		    }

		    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));

		    if ($ext !== 'csv') {
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'Only CSV file allowed.'
		        ]);
		        exit;
		    }

		    // Folder path (adjust to your project)
		    // $uploadDir = "../uploads/bulk_csv/";

		     $baseUploadDir  = "/home/unistorage/2224/omnichannel_config/email_attachments/";
		    $publicBasePath = "unistorage/2224/omnichannel_config/email_attachments/";
		    $currentDate    = date("Y-m-d");

		    $existingAttachment = $_POST['old_attachments'] ?? '';


		    $uploadDir = $baseUploadDir . $currentDate . "/";
		    $publicDir = $publicBasePath . $currentDate . "/";

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


		    $newFileName = 'bulk_preview_' . time() . '_' . rand(1000,9999) . '.csv';
		    $targetPath = $uploadDir . $newFileName;

		    if (!move_uploaded_file($file['tmp_name'], $targetPath)) {
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'Failed to save uploaded file.'
		        ]);
		        exit;
		    }

		    echo json_encode([
		        'status' => 'success',
		        'filepath' => $targetPath,
		        'message' => 'CSV uploaded successfully.'
		    ]);
		    exit;
		}

		if (isset($_POST['action']) && $_POST['action'] == 'preview_template_with_csv') {

		    $templateContent = isset($_POST['template_content']) ? $_POST['template_content'] : '';
		    $csvFile         = isset($_POST['csv_file']) ? $_POST['csv_file'] : '';
		    $bulkId          = isset($_POST['bulk_id']) ? $_POST['bulk_id'] : '';

		    // If file path not passed, fetch from DB using bulk id
		    if (empty($csvFile) && !empty($bulkId)) {
		        // example only - adjust table/column names
		        // $row = mysqli_fetch_assoc(mysqli_query($link, "SELECT upload_file FROM channel_bulk_uploads WHERE id='".mysqli_real_escape_string($link, $bulkId)."'"));
		        // $csvFile = $row['upload_file'] ?? '';
		    }

		    if (empty($templateContent)) {
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'Template content missing.'
		        ]);
		        exit;
		    }

		    if (empty($csvFile) || !file_exists($csvFile)) {
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'CSV file not found.'
		        ]);
		        exit;
		    }

		    $csv = fopen($csvFile, 'r');

		    if (!$csv) {
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'Unable to open CSV file.'
		        ]);
		        exit;
		    }

		    $headers = fgetcsv($csv);

		    if (!$headers || empty($headers)) {
		        fclose($csv);
		        echo json_encode([
		            'status' => 'error',
		            'message' => 'CSV header missing.'
		        ]);
		        exit;
		    }

		    $headers = array_map(function($h){
		        return trim($h);
		    }, $headers);

		    $previewRows = [];
		    $count = 0;
		    $maxPreview = 10000; // only first 10 rows

		    while (($row = fgetcsv($csv)) !== false) {

		        // Skip empty rows
		        if (empty(array_filter($row, function($v){
		            return trim($v) !== '';
		        }))) {
		            continue;
		        }

		        $rowData = [];
		        foreach ($headers as $i => $header) {
		            $rowData[$header] = isset($row[$i]) ? trim($row[$i]) : '';
		        }

		        // $rendered = $this->replace_placeholders_preview($templateContent, $rowData);
		        $previewResult = $this->replace_placeholders_preview($templateContent, $rowData);

		        $previewRows[] = [
				    'rendered_content' => $previewResult['rendered_content'],
				    'missing_placeholders' => $previewResult['missing_placeholders'],
				    'meta' => [
				        'name'   => $this->get_first_available($rowData, ['name', 'full_name', 'customer_name']),
				        'mobile' => $this->get_first_available($rowData, ['mobile', 'phone', 'contact', 'msisdn']),
				        'email'  => $this->get_first_available($rowData, ['email', 'send to', 'send_to'])
				    ],
				    'raw_data' => $rowData
				];

		        $count++;
		        if ($count >= $maxPreview) {
		            break;
		        }
		    }

		    fclose($csv);

		    echo json_encode([
		        'status' => 'success',
		        'preview_rows' => $previewRows
		    ]);
		    exit;
		}
		} 	
	}
	/* 
		* AUTHOR :: FARHAN AKHTAR
		* LAST MODIFIED ON :: 13-02-2025
		* PURPOSE :: TO IMPLEMENT FUNCTIONALLITY FOR GENERATE LIST (EXCEL SHEET) OF EMAILS (DATE WISE). 
	*/

	public function export_bulk_emails($startDate, $endDate) {
		global $db, $link;
	
		// Convert dates to MySQL format (yyyy-mm-dd)
		$startFormatted = DateTime::createFromFormat('d-m-Y H:i:s', $startDate)->format('Y-m-d H:i:s');
		$endFormatted = DateTime::createFromFormat('d-m-Y H:i:s', $endDate)->format('Y-m-d H:i:s');		
	
		// SQL Query to fetch data within the date range
		$sql = "SELECT 
					wa.phone AS Send_To, 
					wa.fname AS first_name, 
					'' AS last_name, 
					wei.v_toemail AS email, 
					'' AS reference_contact_id 
				FROM $db.web_email_information wei 
				LEFT JOIN $db.web_accounts wa ON wei.v_toemail = wa.email 
				WHERE wei.d_email_date BETWEEN '$startFormatted' AND '$endFormatted'
				GROUP BY wei.v_toemail";
		$result = mysqli_query($link, $sql);
	
		// Check if data exists
		if (mysqli_num_rows($result) > 0) {
			// Define the filename with current date
			$fileName = "web_information_" . date("Ymd_His") . ".csv";

			// Set headers for download
			header('Content-Type: text/csv');
			header('Content-Disposition: attachment; filename="' . $fileName . '"');

			// Open the output stream
			$output = fopen('php://output', 'w');

			// CSV Header
			$header = ['Send To', 'first_name', 'last_name', 'email', 'reference_contact_id'];
			fputcsv($output, $header);

			// Fetch and write rows
			while ($row = mysqli_fetch_assoc($result)) {
				fputcsv($output, $row);
			}

			// Close the output stream
			fclose($output);
			exit();
		} 
	}


	// farhan Akhtar :: Function to Get Active Prefix Country Code for SMS number [14-02-2025]
	public function getActivePrefixSMS(){
	    global $db,$link;
		$sql = "SELECT `v_prefix` FROM $db.`tbl_sms_connection` WHERE `status` = 1 ORDER BY `id` DESC";
		$result = mysqli_query($link,$sql);
		$row = mysqli_fetch_assoc($result);
		return $row['v_prefix'];
	}
	


	// Get bulksms templates  : By Aarti on 19-13-2023
	public function get_templates($campaign){
	    global $db,$link;
		$sql = "SELECT * FROM $db.bulksms_templates where type = '$campaign'";
		$result = mysqli_query($link,$sql);
		$template_assign = array();
		while($row = mysqli_fetch_assoc($result)){
			$template_assign[] = $row;
		}
		return $template_assign;
	}
	// Get bulksms templates  : By Aarti on 19-13-2023
	public function get_bulk_upload_list(){
	    global $db,$link;
	    $campaign =$_GET['campaign'];
		$sql = "SELECT * FROM $db.channel_bulk_uploads where channel_type = '$campaign' order by id desc";
		$result = mysqli_query($link,$sql);
		$recent_uploads = array();
		while($row = mysqli_fetch_assoc($result)){
			$recent_uploads[] = $row;
		}
		return $recent_uploads;
	}

	public function getstatus_channel($queue_session,$campaign){
		global $db,$link;
	    // $campaign =$_GET['campaign'];

	    if($campaign == 'SMS'){
	    	$sql = "SELECT * FROM $db.sms_out_queue where queue_session ='$queue_session' group by queue_session";
		   $result = mysqli_query($link,$sql);
		   $row = mysqli_fetch_assoc($result);
		   $status = $row['status'];

	    }else if($campaign == 'Email'){
	    	
	    	$sql = "SELECT * FROM $db.web_email_information_out where queue_session ='$queue_session' group by queue_session";
		   $result = mysqli_query($link,$sql);
		   $row = mysqli_fetch_assoc($result);
		   $status = $row['I_Status'];

	    }else if($campaign == 'WhatsApp'){
	    	$sql = "SELECT * FROM $db.whatsapp_out_queue where queue_session ='$queue_session' group by queue_session";
		   $result = mysqli_query($link,$sql);
		   $row = mysqli_fetch_assoc($result);
		   $status = $row['status'];
	    }
		return $status;
	}
	public function getqueue_bulk_relation($list_id){
		global $db,$link;
		$sql = "SELECT * FROM $db.queue_bulk_relation where list_id ='$list_id'";
	   $result = mysqli_query($link,$sql);
	   $row = mysqli_fetch_assoc($result);
	   return $row;
	}

	/*Start Single sms insert code*/
	// Insert single sms in database
	// STEP-5 function mapping insert_sms
	// corrected the function as the data wasn't being  handled correctly [vastvikta][17-05-2025]
	public function insert_smsmessages($data) {
		global $link, $db;
	
		$todaytime      = date("Y-m-d H:i:s");
		$caller_id      = isset($data['send_to']) ? trim($data['send_to']) : '';
		$message_text   = isset($data['message']) ? mysqli_real_escape_string($link, trim($data['message'])) : '';
		$V_Type         = 'SMS'; // Assuming this is a varchar column
		$v_prefix       = $this->getActivePrefixSMS(); // e.g., "91" for India
		$V_CreatedBY    = isset($data['created_by']) ? $data['created_by'] : '';
		$queue_session  = isset($data['queue_session']) ? $data['queue_session'] : '';
		$schedule_flag  = isset($data['schedule_flag']) ? $data['schedule_flag'] : '0';
		$schedule_time  = isset($data['schedule_time']) ? $data['schedule_time'] : null;
		$send_from      = 'HELB';
	
		
	
		// Fetch user name based on mobile
		$web_accounts = "SELECT name FROM $db.web_accounts WHERE mobile = '$caller_id' LIMIT 1";
		$response     = mysqli_query($link, $web_accounts);
		$value        = mysqli_fetch_array($response);
		$fname        = isset($value['name']) ? mysqli_real_escape_string($link, $value['name']) : 'Unknown';
	
		// Add prefix to mobile number if not there [vastvikta][30-10-2025]
		// Remove all non-digit characters just in case
		$caller_id = preg_replace('/\D/', '', $caller_id);

		// Remove any leading zeros
		$caller_id = ltrim($caller_id, '0');

		// Kenya number validation (mobile numbers are 9 digits)
		if (strlen($caller_id) == 9) {
			// Add country prefix, e.g., '254'
			$full_number = $v_prefix . $caller_id;
		} else {
			// If number length is invalid, keep it as is or handle error
			$full_number = $caller_id;
		}

		$message_type_flag = $data['message_type_flag'];
		// Prepare final SQL query
		$sql_sms_feed = "INSERT INTO $db.sms_out_queue 
			(send_to, send_from, message, message_type_flag, AccountName, created_by, create_date, status, queue_session, schedule_time, schedule_flag, V_Type) 
			VALUES (
				'$full_number', 
				'$send_from', 
				'$message_text', 
				'$message_type_flag', 
				'$fname', 
				'$V_CreatedBY', 
				'$todaytime', 
				'1', 
				'$queue_session', 
				'$schedule_time', 
				'$schedule_flag',
				'NORMAL'
			)";
	
		$result_sms = mysqli_query($link, $sql_sms_feed) or die("Error in insert: " . mysqli_error($link));
	
		return '1';
	}
	
	
	
	/*Start Single whatsapp outgoing insert code*/
	// Insert single sms in database
	// STEP-6 function mapping whatsapp outgoing
	// corrected the function as the data wasn't being  handled correctly [vastvikta][17-05-2025]
	public function insert_WhatsApp_Out($data) {
		global $link, $db;
		
		
		$todaytime     = date("Y-m-d H:i:s");
		$caller_id     = isset($data['send_to']) ? trim($data['send_to']) : '';
		$message_text  = isset($data['message']) ? trim($data['message']) : '';
		$V_CreatedBY   = isset($data['created_by']) ? $data['created_by'] : '';
		$queue_session = isset($data['queue_session']) ? $data['queue_session'] : '';
		$send_from     = isset($data['send_from']) ? trim($data['send_from']) : '';
		$mesg_type     = '1';  // default to '1'
		$status        = isset($data['status']) ? $data['status'] : '0';
		$channel_type  = '1';  // Assuming 1 = WhatsApp
		$msg_flag      = 'OUT'; // Keeping your existing use — if this should be a number, change accordingly
		$bulk_session_id = isset($data['bulk_session_id']) ? $data['bulk_session_id'] : ''; // Optional
		$schedule_flag  = isset($data['schedule_flag']) ? $data['schedule_flag'] : '0';
		$schedule_time  = isset($data['schedule_time']) ? $data['schedule_time'] : null;
			if (!empty($data['schedule_flag']) && $data['schedule_flag'] == '1') {
			$schedule_time = $data['schedule_time'];
			$schedule_flag = '1';
		}
		$message_type_flag = $data['message_type_flag'];
		$sql_sms_feed = "INSERT INTO $db.whatsapp_out_queue 
			(send_to, send_from, message, message_type_flag, status, create_date, bulk_session_id, created_by, channel_type, msg_flag, queue_session,schedule_flag,schedule_time)
			VALUES (
				'$caller_id',
				'$send_from',
				'$message_text',
				'$message_type_flag',
				'$status',
				'$todaytime',
				'$bulk_session_id',
				'$V_CreatedBY',
				'$channel_type',
				'$msg_flag',
				'$queue_session',
				'$schedule_flag',
				'$schedule_time'
			)";
		$result_sms = mysqli_query($link, $sql_sms_feed) or die("Error In whatsapp_out_queue: " . mysqli_error($link));
		return '1';
	}
	// updated the function to handle the data correctly [vastvikta][17-05-2025]
	public function insert_web_email_information_out($data) {
		global $link, $db;
	
		
		$Mail         = isset($data['email']) ? trim($data['email']) : '';
		$from         = "singhkewalsingh1986@gmail.com";
		$V_Subject    = !empty($data['Subject']) ? trim($data['Subject']) : trim($data['description']);
		$V_CreatedBY  = isset($data['created_by']) ? $data['created_by'] : '';
		$queue_session = isset($data['queue_session']) ? $data['queue_session'] : '';
		$todaytime    = date("Y-m-d H:i:s");
		$caller_id    = isset($data['send_to']) ? $data['send_to'] : '';
		$V_Content    = isset($data['message']) ? trim($data['message']) : '';
	
		$message_type_flag = $data['message_type_flag'];

		$schedule_flag  = isset($data['schedule_flag']) ? $data['schedule_flag'] : '0';
		$schedule_time  = isset($data['schedule_time']) ? $data['schedule_time'] : null;

		if (!empty($data['schedule_flag']) && $data['schedule_flag'] == '1') {
			$schedule_time = $data['schedule_time'];
			$schedule_flag = '1';
		}
		$sql_email = "INSERT INTO $db.web_email_information_out 
			(v_toemail, v_fromemail, v_subject, v_body, d_email_date, email_type, module, I_Status, queue_session, ICASEID, scheduling_date, schedule_flag,message_type_flag) 
			VALUES (
				'$Mail', 
				'$from', 
				'$V_Subject', 
				'$V_Content', 
				'$todaytime', 
				'OUT', 
				'Bulk Email', 
				'1', 
				'$queue_session', 
				'$caller_id', 
				'$schedule_time', 
				'$schedule_flag',
				'$message_type_flag'
			)";
	
		
		$result_sms = mysqli_query($link, $sql_email) or die("Error in web_email_information_out: " . mysqli_error($link));
		
		return '1';
	}
	
	
	// Insert single sms in database
	// STEP-5 function mapping send_sms_index
	// updated the code related to insertion in email [vastvikta][17-05-2025]
    public function insert_sms_email_whatsapp(){
        $message_id = $_POST['message_id']; 
        $mobile = $_POST['mobile']; 
        $message = $_POST['message'];
        $scheduletime = $_POST['scheduletime'];
        $channel_type = $_POST['channel_type_direct'];
        if($scheduletime=='on'){
            $scheduledate = date('Y-m-d H:i', strtotime($_POST['date']));
            $scheduleflag='1';
        }else{
            $scheduleflag='0';
            $scheduledate = '';
        }        
       
		
        if($channel_type == 'SMS'){
    	 	foreach($mobile as $mob){
	            $data = array(
	                'send_to'=>$mob,
	                'send_from'=>'ZRA',
	                'message' => $message,
	                'message_type_flag'=>'1',
	                'status'=>'0',
	                'schedule_flag' =>$scheduleflag,
	                'schedule_time' => $scheduledate,
	                'created_by'  =>$_SESSION['userid']
	            );
	          $respose = $this->insert_smsmessages($data);  
	        }	
        }else if($channel_type == 'Email'){
        	$Subject = $_POST['Subject'];
        	$email = $_POST['email']; 
			
	            $data = array(
	                'email'=>$email,
	                'Subject'=>$Subject,
	                'send_from'=>'ZRA',
	                'message' => $message,
	                'message_type_flag'=>'1',
	                'status'=>'0',
	                'schedule_flag' =>$scheduleflag,
	                'schedule_time' => $scheduledate,
	                'created_by'  =>$_SESSION['userid']
	            );
				
	          $respose = $this->insert_web_email_information_out($data); 
			 	        
        }else if ($channel_type == 'WhatsApp') {
			foreach ($mobile as $mob) {
				
				// Make sure these variables are defined; assuming $scheduleflag and $scheduledate are derived from $scheduletime
				$scheduleflag = !empty($scheduletime) ? 1 : 0;
			
	
				$data = array(
					'send_to' => $mob,
					'send_from' => '919220411572',
					'message' => $message,
					'message_type_flag' => '1',
					'status' => '0',
					'schedule_flag' => $scheduleflag,
					'schedule_time' => $scheduledate,
					'created_by' => $_SESSION['userid']
				);
				
				$respose = $this->insert_WhatsApp_Out($data);
			}
		}
        return $respose;
    }
    // get sms quota for insert single sms
    // STEP-3 function mapping sms_quota_update
    public function get_quota_sms($user_id=''){
    	global $db,$link;
        if(empty($user_id)){
            $user_id=$_SESSION['userid'];
        }        
		$web_accounts="select * FROM $db.uniuserprofile where AtxUserID='$user_id'";
		$response=mysqli_query($link,$web_accounts);
		$res=mysqli_fetch_array($response);
		return $res['sms_quota'];
    }
    // STEP-3 function mapping get_quota_whatsapp
    public function get_quota_whatsapp($user_id=''){
    	global $db,$link;
        if(empty($user_id)){
            $user_id=$_SESSION['userid'];
        }        
		$web_accounts="select * FROM $db.uniuserprofile where AtxUserID='$user_id'";
		$response=mysqli_query($link,$web_accounts);
		$res=mysqli_fetch_array($response);
		return $res['whatsapp_quota'];
    }
    // get consumend quota sms for insert single sms
    // STEP-4 function mapping sms_quota_update
    public function get_consumend_quota_sms($user_id =''){
    	global $db,$link;
        if(empty($user_id)){
            $user_id=$_SESSION['userid'];
        }
        $web_accounts="select * FROM $db.uniuserprofile where AtxUserID='$user_id'";
		$response=mysqli_query($link,$web_accounts);
		$res=mysqli_fetch_array($response);
        return $res['sms_quota_consumed'];
    }
    // get consumend quota sms for insert single sms
    // STEP-4 function mapping sms_quota_update
    public function get_consumend_quota_whatsapp($user_id =''){
    	global $db,$link;
        if(empty($user_id)){
            $user_id=$_SESSION['userid'];
        }
        $web_accounts="select * FROM $db.uniuserprofile where AtxUserID='$user_id'";
		$response=mysqli_query($link,$web_accounts);
		$res=mysqli_fetch_array($response);
        return $res['whatsapp_quota_consumed'];
    }

    // update sms quata limit for insert single sms
    // STEP-2 - function mapping send_sms_index
    public function sms_quota_update($mobCnt){  
    	global $db,$link;      
        $quotaCnt = $this->get_quota_sms();
        $consumed_ct = $this->get_consumend_quota_sms();
        $user_id=$_SESSION['userid'];
        $sms_quota_count = $quotaCnt-$mobCnt;
        $sms_quota = $sms_quota_count ;
        $sms_quota_consumed = $consumed_ct+$mobCnt;

        $web_accounts="update $db.uniuserprofile set sms_quota='$sms_quota',sms_quota_consumed='$sms_quota_consumed' where AtxUserID='$user_id'";
		$response=mysqli_query($link,$web_accounts);
    }
    // update sms quata limit for insert single sms
    // STEP-2 - function mapping send_sms_index
    public function whatsapp_quota_update($mobCnt){  
    	global $db,$link;      
        $quotaCnt = $this->get_quota_whatsapp();
        $consumed_ct = $this->get_consumend_quota_whatsapp();
        $user_id=$_SESSION['userid'];
        $whatsapp_quota = $quotaCnt-$mobCnt;
        $whatsapp_quota_consumed = $consumed_ct+$mobCnt;

        $web_accounts="update $db.uniuserprofile set whatsapp_quota='$whatsapp_quota',whatsapp_quota_consumed='$whatsapp_quota_consumed' where AtxUserID='$user_id'";
		$response=mysqli_query($link,$web_accounts);
    }
    // Insert single sms 
    // STEP-1
	public function send_sms_index() {
		$channel_type = $_POST['channel_type_direct'];
        if ($_POST['mobile'] && ($channel_type = 'SMS' || $channel_type = 'WhatsApp')) {  
        	if($channel_type = 'SMS'){
        		$quota = $this->get_quota_sms();
        	}else if($channel_type = 'WhatsApp'){
        		$quota = $this->get_quota_whatsapp();
        	}
			$mobCnt = count($_POST['mobile']);
			$msgLnth = strlen($_POST['message']);
            $finalcnt = (floor($msgLnth/153)+1) * $mobCnt;
			// No Quota limts For Admin 				
			// if($_SESSION['userid'] != '1'){
			// 	if($quota == 0){
			// 		echo json_encode(array('st' => 2, 'msg' => "You Can't Send messages Please contact admin..Quota Exceed it's limit"));die();
			// 	}else{
			// 		if($quota >= $finalcnt){
			// 			$result = insert_sms_email_whatsapp();
			// 			if($result){
			// 				$this->sms_quota_update($finalcnt);
			// 				echo json_encode(array('st' => 0, 'msg' => "Message sent successfully"));die();
			// 			}else{
			// 				echo json_encode(array('st' => 2, 'msg' => "Send Sms Fail, please Try after some time."));die();
			// 			}
			// 		}else{
			// 			echo json_encode(array('st' => 2, 'msg' => "Your available quota limit is ".$quota.""));die();
			// 		}
			// 	}
			// }else{
				$result = $this->insert_sms_email_whatsapp();
				if($result){
					if($channel_type = 'WhatsApp'){
						$this->whatsapp_quota_update($finalcnt); // update whatsapp quota
					}else{
						$this->sms_quota_update($finalcnt); // update sms quota
					}
					echo json_encode(array('st' => 0, 'msg' => "Created successfully"));die();
				}else{
					echo json_encode(array('st' => 2, 'msg' => "Send Whatsapp message Fail, please Try after some time."));die();
				}
			// }
		}else if ($_POST['email'] && $channel_type = 'Email') { 
			$result = $this->insert_sms_email_whatsapp();
			if($result){
				echo json_encode(array('st' => 0, 'msg' => "Created successfully"));die();
			}else{
				echo json_encode(array('st' => 2, 'msg' => "Send Email Fail, please Try after some time."));die();
			}
        } else {
            $data = array(
                'mobile' => 'mobile[]',
                'message' => 'message',
                'date' => 'date',
            );
            echo json_encode(array('st' => 1, 'msg' => $data));die();
        }
    }
    // get list contact when user search mobile
	public function get_contact_list() {
		global $db, $link;
		$q = trim($_GET['q'] ?? '');
		$contact = [];

		if (!empty($q)) {
			// First, try fetching from contact table
			$query = "SELECT * FROM $db.contact WHERE first_name LIKE '%$q%' OR mobile_no LIKE '%$q%' LIMIT 20";
			$response = mysqli_query($link, $query);

			if ($response && mysqli_num_rows($response) > 0) {
				while ($value = mysqli_fetch_array($response)) {
					$ar = array(
						'id' => $value['mobile_no'],
						'text' => $value['first_name'] . ' - ' . $value['mobile_no'],
						'Name' => $value['first_name']
					);
					array_push($contact, $ar);
				}
			} else {
				// Then, try from web_accounts
				$query = "SELECT * FROM $db.web_accounts 
						WHERE fname LIKE '%$q%' 
						OR mobile LIKE '%$q%' 
						OR phone LIKE '%$q%' 
						LIMIT 20";
				$response = mysqli_query($link, $query);

				if ($response && mysqli_num_rows($response) > 0) {
					while ($value = mysqli_fetch_array($response)) {
						$matched_number = '';
						if (strpos($value['mobile'], $q) !== false) {
							$matched_number = $value['mobile'];
						} elseif (strpos($value['phone'], $q) !== false) {
							$matched_number = $value['phone'];
						}

						$text = $value['fname'] . ' - ' . $matched_number;
						$ar = array(
							'id' => $matched_number,
							'text' => $text,
							'Name' => $value['fname']
						);
						array_push($contact, $ar);
					}
				} 
			}

			// ✅ If no match found and input looks like a valid mobile number
			// ✅ If no match found and input looks like a valid mobile number (10–12 digits only)
			if (empty($contact) && preg_match('/^[0-9]\d{8,11}$/', $q)) {
			    if (strlen($q) <= 12) {
			        $ar = array(
			            'id' => $q,
			            'text' => 'New Number - ' . $q,
			            'Name' => 'Unknown'
			        );
			        array_push($contact, $ar);
			    }
			} else if (strlen($q) > 12) {
			    // ✅ Return empty array if number exceeds 12 digits
			    echo json_encode([]);
			    die();
			}

		}

		echo json_encode($contact);
		die();
	}
	//  get list contact when user search mobile
    public function get_email_list(){
    	global $db,$link;
		$web_accounts="select * FROM $db.contact";
		$response=mysqli_query($link,$web_accounts);
		return $response;
	}

	
	//view single sms template
    public function get_template_content(){
    	global $db,$link;	
    	$tempname=$_POST['tempname'];
        $web_accounts="select * FROM $db.bulksms_templates where name='$tempname'";
		$response=mysqli_query($link,$web_accounts);
		$value=mysqli_fetch_array($response);
        print_r(json_encode($value));

    }
	public function get_campaign_content(){
	    global $db, $link;

	    $campaign_id = intval($_POST['campaign_id']);

	    $sql = "SELECT *
	            FROM {$db}.channel_bulk_uploads
	            WHERE id = '{$campaign_id}'";

	    $res = mysqli_query($link, $sql);
	    $row = mysqli_fetch_assoc($res);

	    echo json_encode($row);
	    exit;
	}

	
    ###########################################Code Bulk##############################
    /*Bulk insert sms code start*/
    public function ajax_campaign_contact_count(){
    	global $db,$link;
		$campaign_id = $_GET['campaign_id'];
		if($campaign_id > 0 ){
			$sql = "SELECT * FROM $db.channel_bulk_uploads where id = $campaign_id";
			$result = mysqli_query($link,$sql);
			$results = mysqli_fetch_assoc($result);
			echo 'Total Count In selected Campaign '. $results['total_count'];die(); 
		}
		echo '' ;die();
	}

    // aarti update : 20-12-2023 
    // Save Contact From Bulk Upload list
    // STEP - 4
    public function multi_unique($src){
		$output = array_map("unserialize",array_unique(array_map("serialize", $src)));
	   	return $output;
	}

	// Validation for mobile number
	public function validate_mobile_number($mobile) {
	    // Example: Check if the number has 09-12 digits
	    return preg_match('/^\d{09,12}$/', $mobile);
	}

	// Validation for email
	public function validate_email($email) {
	    return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
	}

	/*Insert Contact From Bulk Upload list*/
	public function insert_batch_contact($filtered){
		global $db,$link;
		$mobile_no = $filtered['mobile_no'];
		$email= $filtered['email'];
		$first_name = $filtered['first_name'];
		$last_name = $filtered['last_name'];
		$sql_qry = "INSERT INTO $db.contact (first_name,mobile_no,email,last_name) VALUES ('{$first_name}','{$mobile_no}','{$email}','{$last_name}')";
	    mysqli_query($link, $sql_qry);
	    // echo $sql_qry;
	}
	/*Insert Bad Contact From Bulk Upload list*/
	public function insert_batch_bad_contact($filtered){
		global $db,$link;
		$mobile_no = $filtered['mobile_no'];
		$email= $filtered['email'];
		$first_name = $filtered['first_name'];
		$last_name = $filtered['last_name'];
		$sql_qry = "INSERT INTO $db.bad_contact (first_name, mobile_no,email,last_name) VALUES ('{$first_name}','{$mobile_no}','{$email}','{$last_name}')";
	    mysqli_query($link, $sql_qry);
	}
	public function save_contact_from_list($value=''){ 
    	if(count($this->customer_array) > 0 ){
    		$customer_data = $this->multi_unique($this->customer_array) ;
    		// $customer_data = $this->customer_array;
    		$filtered = [] ;
    		foreach ($customer_data as $key => $value) {
    			if(!$this->is_customer_exist($value['mobile_no'])){
    				// array_push($filtered, $value);
    				$this->insert_batch_contact($value);
    			}
    		}
    		// insert in batch
    		if(count($filtered) > 0 ){
    			// $this->insert_batch_contact($filtered);
    		}
    	}
    	if(count($this->bad_customer_array) > 0 ){    		
    		// $this->insert_batch_bad_contact($this->bad_customer_array)    		
    	}
    }
	
	// get select file name
	public function get_selected_file_name($file_id){	
		global $db,$link;
		$web_accounts="select file_name FROM $db.channel_bulk_uploads where id='$file_id'";
		$response=mysqli_query($link,$web_accounts);
		$data=mysqli_fetch_array($response);
		return $data['file_name']; // Return only the file name
		
	}
	//  this function filter a list and make simple array
	// STEP - 3  
	public function make_bulk_upload_list($path, $scheduletime, $bulk_session_id = '', $queue_session_id = '', $channel_type) {

		// check for schedule date time
		if ($scheduletime == 'on') {
			$schedule_date_time = date('Y-m-d H:i', strtotime($_POST['date']));
			$schedule_flag = '1';
		} else {
			$schedule_date_time = date('Y-m-d H:i');
			$schedule_flag = '0';
		}

		$bad_customer_data = [];
		$customer_data = [];
		$list_data = [];

		// normalize channel type
		$channel_type = strtolower($channel_type);

		// Open the CSV file for reading
		$csvFile = fopen($path, 'r');
		if ($csvFile !== false) {

			// Read headers
			$headers = fgetcsv($csvFile);
			$i = 1;

			while (($data = fgetcsv($csvFile)) !== false) {

				$bad_records = false;

				$send_to      = $data[0];
				$first_name   = $data[1];
				$last_name    = $data[2];
				$email        = trim($data[3]);
				$reference_id = $data[4];

				// Clean mobile number
				$mobile = trim($send_to, '\'"');
				$mobile = preg_replace("/[^0-9]/", '', $mobile);

				/* ================= CHANNEL BASED VALIDATION ================= */

				// SMS / WhatsApp → mobile required
				if (in_array($channel_type, ['sms', 'whatsapp'])) {
					if (empty($mobile) || !$this->validate_mobile_number($mobile)) {
						$data['file_upload'] = "Invalid / Missing Mobile Number in line no $i. Value is: $mobile";
						echo json_encode(['status' => 'fail', 'msg' => $data]);
						die();
					}
				}

				// Email → email required
				if ($channel_type == 'email') {
					if (empty($email) || !$this->validate_email($email)) {
						$data['file_upload'] = "Invalid / Missing Email Address in line no $i. Value is: $email";
						echo json_encode(['status' => 'fail', 'msg' => $data]);
						die();
					}
				}

				/* ================= END VALIDATION ================= */

				if ($bad_records) {

					$bad_customer_data[] = [
						'first_name'     => $first_name,
						'last_name'      => $last_name,
						'email'          => $email,
						'mobile_no'      => $mobile,
						'reference'      => $reference_id,
						'campaign_name'  => $_POST['file_upload_type'] == 'overwrite'
											? $_FILES["file"]["name"]
											: $bulk_session_id,
						'created_by'     => $_SESSION['userid']
					];

				} else {

					// Good customer records
					$customer_data[] = [
						'first_name' => $first_name,
						'last_name'  => $last_name,
						'email'      => $email,
						'mobile_no'  => $mobile,
						'reference'  => $reference_id,
						'created_by' => $_SESSION['userid']
					];

					// Message list
					$list_data[] = [
						'send_to'           => ($channel_type == 'email') ? $email : $mobile,
						'send_from'         => 'HELB',
						'message'           => $_POST['message'],
						'message_type_flag' => '2',
						'status'            => '0',
						'schedule_flag'     => $schedule_flag,
						'schedule_time'     => $schedule_date_time,
						'queue_session'     => $queue_session_id,
						'created_by'        => $_SESSION['userid'],
						'email'             => $email,
						'description'       => $_POST['description'],
						'bulk_session_id'   => $_POST['file_upload_type'] == 'overwrite'
											? $_FILES["file"]["name"]
											: $bulk_session_id
					];

					$this->customer_array     = $customer_data;
					$this->bad_customer_array = $bad_customer_data;
				}

				$i++;
			}

			fclose($csvFile);

		} else {
			echo "Error opening the CSV file.";
		}

		return $list_data;
	}

	
    // aarti update : 20-12-2023 check customer exist in database
    public function is_customer_exist($mobile){	
    	global $db,$link;
		// if(!empty($filename)){
			$web_accounts="select * FROM $db.contact where mobile_no='$mobile'";
			$response=mysqli_query($link,$web_accounts);		
			$num = mysqli_num_rows($response);
			if($num>0){
				return true;
			}else{
				return false;
			}
		// }
    }
 
	#################################### (Bulk SMS) ################################################
    /*Insert sms bulk uploads data in database*/
    //mapping with import_file function
    public function insert_channel_bulk_uploads($upload_data){
    	global $db,$link;
    	$groupid = $_SESSION['userid'] ?? '';
    	// if($groupid == '070000'){
		// 	$istatus = 4;
		// }else{
		// 	$istatus = 0;
		// }
		$created_by = $_SESSION['userid'];
		$status = '4';
    	$list_name = $upload_data['list_name'];
       	$description = $upload_data['message'];
       	$file_name = $upload_data['file_name'];
		$slug = $upload_data['slug'];
		$total_count = $upload_data['total_count'];
		// $created_by = $upload_data['created_by'];
		$channel_type = $upload_data['channel_type'];
		$created_at = date('Y-m-d H:i');
		$queue_session_id = $upload_data['queue_session_id'];

		$schedule_flag= $upload_data['schedule_flag'];

		$schedule_time = $upload_data['schedule_time'];

		$existingAttachment = $_POST['old_attachments'] ?? '';

		if (!empty($_FILES['attachments']['name'][0]) || !empty($_POST['existing_attachments'])) {
		    $uploadedFiles = $this->upload_attachments_files();
		} else {
		    $uploadedFiles = '';
		}

		$form_id =$_POST['form_link'];

    	$sql_sms_feed="insert into $db.channel_bulk_uploads (list_name,slug,description,total_count,file_name,created_by,created_at,channel_type,queue_session,is_scheduled, scheduled_time,status,attachments,form_id) values ('$list_name','$slug','$description','$total_count','$file_name', '{$created_by}','{$created_at}','$channel_type','{$queue_session_id}','{$schedule_flag}','{$schedule_time}','{$status}','{$uploadedFiles}','{$form_id}')";
		$result_sms= mysqli_query($link,$sql_sms_feed) or die("Error In Query24 ".mysqli_error($link));
		$last_insert_id = mysqli_insert_id($link);

		$userid = $_SESSION['userid'] ?? '';
		$userName = $this->getUserNameByAtxUserID($link, $db, $userid);
        $comments = "Bulk Campaign Created : ". $list_name ." By: ".$userName;
        $this->logUserAction($link, $db, $userid, 'bulk_uploads', $comments);

		return $last_insert_id;

    }
	function upload_attachments_files() {
		    $baseUploadDir  = "/home/unistorage/2224/omnichannel_config/email_attachments/";
		    $publicBasePath = "unistorage/2224/omnichannel_config/email_attachments/";
		    $currentDate    = date("Y-m-d");

		    $existingAttachment = $_POST['old_attachments'] ?? '';


		    $uploadDir = $baseUploadDir . $currentDate . "/";
		    $publicDir = $publicBasePath . $currentDate . "/";

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

		    $tempDir = $uploadDir . "temp_" . time() . "/";
		    mkdir($tempDir, 0777, true);

		    $savedFiles = [];

		    /* ===============================
		       1️⃣ Existing IMAP Attachments
		       =============================== */
		    if (!empty($_POST['existing_attachments'])) {
		        $imapBasePath = "/home/unistorage/2224/imap/";

		        foreach ($_POST['existing_attachments'] as $file) {
		            $src = $imapBasePath . basename($file);
		            $dst = $tempDir . basename($file);

		            if (file_exists($src)) {
		                copy($src, $dst);
		                $savedFiles[] = basename($file);
		            }
		        }
		    }
		    // If user didn't upload new attachments → keep old
			if(empty($savedFiles) && !empty($existingAttachment)){
			    return $existingAttachment;
			}

		    /* ===============================
		       2️⃣ Newly Uploaded Attachments
		       =============================== */
		    if (!empty($_FILES['attachments']['name'][0])) {

		        $allowedExt = ['pdf','doc','docx','xls','xlsx','csv','jpg','jpeg','png','zip'];
		        $maxSize    = 10 * 1024 * 1024; // 10MB

		        foreach ($_FILES['attachments']['name'] as $i => $name) {

		            $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));

		            if (!in_array($ext, $allowedExt)) {
		                continue;
		            }

		            if ($_FILES['attachments']['size'][$i] > $maxSize) {
		                continue;
		            }

		            $safeName = time().'_'.$i.'_'.preg_replace('/[^a-zA-Z0-9._-]/','',$name);
		            move_uploaded_file($_FILES['attachments']['tmp_name'][$i], $tempDir.$safeName);
		            $savedFiles[] = $safeName;
		        }
		    }

		    $savedFiles = array_unique($savedFiles);

		    /* ===============================
		       3️⃣ ZIP or Single File
		       =============================== */
		    if (count($savedFiles) > 1) {

		        $zipName = "attachments_" . date("Ymd_His") . ".zip";
		        $zipPath = $uploadDir . $zipName;

		        $zip = new ZipArchive();
		        $zip->open($zipPath, ZipArchive::CREATE);

		        foreach ($savedFiles as $file) {
		            $zip->addFile($tempDir.$file, $file);
		        }
		        $zip->close();

		        array_map('unlink', glob($tempDir."*"));
		        rmdir($tempDir);

		        return $publicDir . $zipName;

		    } elseif (count($savedFiles) == 1) {

		        $file = $savedFiles[0];
		        rename($tempDir.$file, $uploadDir.$file);
		        rmdir($tempDir);

		        return $publicDir . $file;
		    }

		    rmdir($tempDir);
		    return '';
	}

    public function log_debug($msg) {
	    $file = 'attachment_debug_log.txt';
	    $entry = "[" . date('Y-m-d H:i:s') . "] $msg\n";
	    file_put_contents($file, $entry, FILE_APPEND);
	}

    /*Update sms bulk uploads data in database*/
    //mapping with import_file function
    public function update_channel_bulk_uploads(){
    	global $db,$link;
    	// $total_count = $upload_data['total_count'];
		// $file_name = $upload_data['file_name'];
    	$total_count = '';
		$file_name = '';
		$updated_at = date('Y-m-d H:i');
    	$sql_sms_feed="update $db.channel_bulk_uploads set total_count='$total_count',updated_at='$updated_at' where file_name='$file_name'";
		$result_sms= mysqli_query($link,$sql_sms_feed) or die("Error In Query24 ".mysqli_error($link));
		// Get the last inserted ID
		$last_insert_id = mysqli_insert_id($link);
		return $last_insert_id;
    }
    // aarti update : 20-12-2023 check uploade file exits or not
	// STEP - 2
	//mapping with import_file function
	public function check_upload_file_exist($filename='',$channel_type){
		global $db,$link;
		if(!empty($filename)){
			$web_accounts="select * FROM $db.channel_bulk_uploads where file_name='$filename' and channel_type='$channel_type'";
			$response=mysqli_query($link,$web_accounts);		
			$num = mysqli_num_rows($response);
			if($num>0){
				$data=mysqli_fetch_array($response);
				return $data;
			}else{
				return false;
			}
		}
		return false ;
		// Response error 
	}
	//Insert record in queue_bulk_relation table
	//mapping with import_file function
    public function insert_queue_bulk_relation($relation_data){
		global $db,$link;
		$list_id = $relation_data['list_id'] ;
		$message = $relation_data['message'];
		$queue_session_id = $relation_data['out_queue_session'];
		$total_count = $relation_data['total_count'];
		$schedule_flag= $relation_data['schedule_flag'];
		$schedule_time = $relation_data['schedule_time'];
		$created_at = $relation_data['created_at'];
		$created_by = $relation_data['created_by'];
		$channel_type = $relation_data['channel_type'];

    	$sql_sms_feed="insert into $db.queue_bulk_relation (list_id,message,out_queue_session,total_count,schedule_flag, schedule_time,created_at,created_by,channel_type) values ('$list_id','$message','$queue_session_id','$total_count','$schedule_flag','$schedule_time', '$created_at','$created_by','$channel_type')";
		$result_sms= mysqli_query($link,$sql_sms_feed) or die("Error In Query24 ".mysqli_error($link));
		return '1';
	}
	public function do_upload($bulk_session_id){
    	global $bulkcampaign_path;
	    $new_name = $_POST['file_upload_type'] == 'overwrite' ? $_FILES["file"]["name"] : $bulk_session_id; // Assuming 'uploaded_file' is the name of your file input
	    $target_dir = $bulkcampaign_path;
		$target_file = $target_dir . basename($new_name); 
		$imageFileType = strtolower(pathinfo($_FILES["file"]["name"],PATHINFO_EXTENSION));
		// Check file size
		$uploadOk = 1;
		if ($_FILES["file"]["size"] > 1000000) {
		  $data['file_upload'] = 'Sorry, your file is too large.';
		  echo json_encode(array('status' => 'fail', 'msg' => $data  ));die();
		  $uploadOk = 0;
		}
		// Allow certain file formats
		if($imageFileType != "xls" && $imageFileType != "csv" ) {
		  $data['file_upload'] = 'Sorry, only xls|xlsx|csv & GIF files are allowed.';
		  echo json_encode(array('status' => 'fail', 'msg' => $data  ));die();
		  $uploadOk = 0;
		}
		// Check if $uploadOk is set to 0 by an error
		if ($uploadOk == 0) {
		  $data['file_upload'] = 'Sorry, your file was not uploaded.';
		  echo json_encode(array('status' => 'fail', 'msg' => $data  ));die();
		} else {
			if (!file_exists($target_dir)) {
			    mkdir($target_dir, 0777, true); // Create the directory if it doesn't exist
			}
			$newpath = $target_file.'.'.$imageFileType;
		    if (move_uploaded_file($_FILES["file"]["tmp_name"], $newpath)) {
			    // echo "The file ". htmlspecialchars( basename( $_FILES["file"]["name"])). " has been uploaded.";
			    return true;
			} else {
			    // echo "Sorry, there was an error uploading your file.";
			     return false;

			}
		}
	}
	//Aarti update : 10-06-2021    
    //this function is responsible to upload lsit
	public function import_file(){
		global $bulkcampaign_path;
		$bulk_session_id="Bulk_".uniqid()."_".rand(0,1000000);	// Make Ther Unique BULK Upload ID 
		$queue_session_id = md5(microtime() . mt_rand());	// Make Ther Unique BULK Upload ID 		
		$schedule_time = $_POST['scheduletime'];

		if($schedule_time == 'on'){
            $schedule_date_time = date('Y-m-d H:i:s', strtotime($_POST['date']));
            // $schedule_date_time = datetimeformat($_POST['date']);
            $schedule_flag='1';
        }else{
            $schedule_date_time = '0000-00-00 00:00:00';
            $schedule_flag='0';
        }

		// fetch avialable sms quota for loggedin user
		if($_POST['channel_type'] == 'WhatsApp'){
			$quota = $this->get_quota_whatsapp();
		}else if($_POST['channel_type'] == 'SMS'){
			$quota = $this->get_quota_sms();
		}
		
		// Read and store for new file if User Add New File 
		if(isset($_FILES["file"]["name"]) && !empty($_FILES["file"]["name"]) ){
			$path = $_FILES["file"]["tmp_name"];
			// Check file exist or not 
			if($_POST['file_upload_type'] != 'overwrite'){
				$result = $this->check_upload_file_exist($bulk_session_id.'.csv',$_POST['channel_type']); 
				if($result != false){
					$list_id = $result['id'];
					if($_POST['file_upload_type'] != 'no_new'){
						$data['file_upload'] = "you want to update file";
						echo json_encode(array('status' => 'fail', 'error_type' => '1' , 'msg' => $data  ));
						die();
					}
				}
			}
			
			/* make bulk Upload data from file */
			/* fetch data from excel file and store database*/
		    $data = $this->make_bulk_upload_list($path, $schedule_time, $bulk_session_id, $queue_session_id,$channel_type);
		    // Check for Qouta avialablty
		    $msgLnth = strlen($_POST['message']);
        	$finalcnt = (floor($msgLnth/153)+1) * count($data);
        	
        	// No Quota limts For Admin 	
			// no logic in table that's why comment on code [vastvikta][26-11-2025]			
			// if($_SESSION['userid'] != '1' && ($_POST['channel_type'] == 'WhatsApp' || $_POST['channel_type'] == 'SMS')){
            // 	if($quota < $finalcnt){
            // 		$data['file_upload'] = "Your available quota limit is $quota, less than that required $finalcnt";
			// 		echo json_encode(array('status' => 'fail', 'msg' => $data));die();
            // 	}
			// }; 

			// File Remove first before save it in case of overwrite
			if($_POST['file_upload_type'] == 'overwrite'){
				// remove old file in case or overwrite
				$file_with_path = $bulkcampaign_path.$_FILES["file"]["name"] ;
				// log_message('error', ' file_with_path '.$file_with_path);
				if(file_exists($file_with_path)){
					unlink($file_with_path);
				}

			}          		 
			// uploading file function used here
			$do_uploade = $this->do_upload($bulk_session_id);
			if($do_uploade){
            	// Save Customer Details 
            	$this->save_contact_from_list(); // insert record contact table
            	// if($_SESSION['userid'] != '1'){// update SMS Quota 
	            // 	if($_POST['channel_type'] == 'WhatsApp'){
				// 		$this->whatsapp_quota_update($finalcnt);
				// 	}else if($_POST['channel_type'] == 'SMS'){
				// 		$this->sms_quota_update($finalcnt);
				// 	}	
	            // }
            	// file Upload Successfully
            	foreach ($data as $key_bulk => $value_bulk){
            		if($_POST['channel_type'] == 'WhatsApp'){
            			// $this->insert_WhatsApp_Out($value_bulk);	// insert channel_bulk_uploads table 
            		}else if($_POST['channel_type'] == 'SMS'){
            			// $this->insert_smsmessages($value_bulk);	// insert channel_bulk_uploads table 
            		}else if($_POST['channel_type'] == 'Email'){
            			// $this->insert_web_email_information_out($value_bulk);	// insert channel_bulk_uploads table 
            		}	
            	}
            	$new_name = $_POST['file_upload_type'] == 'overwrite' ? $_FILES["file"]["name"] : $bulk_session_id; // Assuming 'uploaded_file' is the name of your file input
            	$imageFileType = strtolower(pathinfo($_FILES["file"]["name"],PATHINFO_EXTENSION));

            	$new_filename = $new_name.'.'.$imageFileType;
				// Dont make any change in here in case of overwite
				if($_POST['file_upload_type'] != 'overwrite'){
					// save new list record 
					
		           	$upload_data['list_name'] = $_POST['list_name'];
					$upload_data['message'] = $_POST['message'];//added message for campaign [vastvikta][30-10-2025]
		           	$upload_data['description'] = $_POST['message'];
		           	$upload_data['file_name']	= $new_filename;
					$upload_data['slug'] = str_replace(" ", "-", strtolower($_POST['description']))  ;
					$upload_data['total_count'] = count($data);
					$upload_data['created_by']  = $_SESSION['userid'];
					if($_POST['channel_type'] == 'WhatsApp'){
						$upload_data['channel_type'] = 'WhatsApp';
					}else if($_POST['channel_type'] == 'SMS'){
						$upload_data['channel_type'] = 'SMS';
					}else if($_POST['channel_type'] == 'Email'){
						$upload_data['channel_type'] = 'Email';
					}
					$upload_data['queue_session_id'] = $queue_session_id;
					$upload_data['schedule_flag']	= $schedule_flag ;
					$upload_data['schedule_time']	= $schedule_date_time ;

					$list_id = $this->insert_channel_bulk_uploads($upload_data); // insert sms bulk upload table						
				}else{
					// if File Overwrite than update Count In Mail list Table
					// $new_name = basename($_FILES["file"]["name"], '.csv') ;
					// $this->db->where('file_name', $new_name);
					$this->update_channel_bulk_uploads(array('total_count' =>count($data) ,'updated_at' => date('Y-m-d H:i:s', strtotime('now')), 'file_name' =>$new_filename));

					// if file exist get list id
					$result = $this->check_upload_file_exist($new_filename,$_POST['channel_type']); 
					$list_id = $result['id'];

				}
				// save Relation 
				// $relation_data['list_id'] = $list_id ;
				// $relation_data['message'] = $_POST['message'];
				// $relation_data['total_count'] = count($data);
				// $relation_data['out_queue_session'] = $queue_session_id;
				// $relation_data['schedule_flag']	= $schedule_flag ;
				// $relation_data['schedule_time']	= $schedule_date_time ;
				// $relation_data['created_at']  = date('Y-m-d H:i', strtotime('now'));
				// $relation_data['created_by']  = $_SESSION['userid'];
				// if($_POST['channel_type'] == 'WhatsApp'){
				// 	$relation_data['channel_type'] = 'WhatsApp';
				// }else if($_POST['channel_type'] == 'SMS'){
				// 	$relation_data['channel_type'] = 'SMS';
				// }else if($_POST['channel_type'] == 'Email'){
				// 	$relation_data['channel_type'] = 'Email';
				// }
				
				// /* insert queue_bulk_relation data in table*/
				// $this->insert_queue_bulk_relation($relation_data);
		        echo json_encode(array('status' => 'success', "message" => "Data Imported successfully"));die();					
			}else{
				$data['file_upload'] = 'file upload Fial, Please try after some time';
				echo json_encode(array('status' => 'fail', 'msg' => $data));die();
			}			
		}else{// this is responsible for file slected from dropdown
			if($_POST['select_file_name'] > 0){					
				$selected_file = $this->get_selected_file_name($_POST['select_file_name']);
				// file path 
				$path = $bulkcampaign_path.$selected_file ;
				if(file_exists($path)){
					/*Code Remove from here*/
				    $data = $this->make_bulk_upload_list($path, $schedule_time, $selected_file, $queue_session_id,$channel_type);
					
					// Check for Qouta avialablty
				    $msgLnth = strlen($_POST['message']);
	            	$finalcnt = (floor($msgLnth/153)+1) * count($data);
	            	
	            	// No Quota limts For Admin 	
								// no logic in table that's why comment on code [vastvikta][26-11-2025]
					// if($_SESSION['userid'] != '1'){
		            // 	if($quota < $finalcnt){
		            // 		$data['file_upload'] = "Your available quota limit is $quota, less than that required $finalcnt";
					// 		echo json_encode(array('status' => 'fail', 'msg' => $data));die();
		            // 	}
					// }
					// $res = insert_bulk($data);

					// chnaged the handling for the multiple and single data [vastvikta][17-05-2025]
					if($_POST['channel_type'] == 'WhatsApp'){
						foreach ($data as $row) {
            			// $this->insert_WhatsApp_Out($row);	// insert channel_bulk_uploads table 
					    }
            		}else if($_POST['channel_type'] == 'SMS'){
						foreach ($data as $row) {
            			// $this->insert_smsmessages($row);	// insert channel_bulk_uploads table 
						}
            		}
					else if ($_POST['channel_type'] == 'Email') {
						// updated as it was giving error [vastvikta][17-05-2025]
						// foreach ($data as $row) {
							// $this->insert_web_email_information_out($row); // Call one-by-one
						// }
					}

					// if($_SESSION['userid'] != '1'){
					// 	if($_POST['channel_type'] == 'WhatsApp'){
					// 		$this->whatsapp_quota_update($finalcnt);
					// 	}else if($_POST['channel_type'] == 'SMS'){
					// 		$this->sms_quota_update($finalcnt);
					// 	}
					// }

					// save Relation 
					// $relation_data['list_id'] = $_POST['select_file_name'];
					// $relation_data['message'] = $_POST['message'];
					// $relation_data['total_count'] = count($data);
					// $relation_data['out_queue_session'] = $queue_session_id;
					// $relation_data['schedule_flag']	= $schedule_flag ;
					// $relation_data['schedule_time']	= $schedule_date_time ;
					// $relation_data['created_at']  = date('Y-m-d H:i', strtotime('now'));
					// $relation_data['created_by']  = $_SESSION['userid'];
					// if($_POST['channel_type'] == 'WhatsApp'){
					// 	$relation_data['channel_type'] = 'WhatsApp';
					// }else if($_POST['channel_type'] == 'SMS'){
					// 	$relation_data['channel_type'] = 'SMS';
					// }else if($_POST['channel_type'] == 'Email'){
					// 	$relation_data['channel_type'] = 'Email';
					// }
					// $this->insert_queue_bulk_relation($relation_data);

					$upload_data['list_name'] = $_POST['list_name'];
					$upload_data['message'] = $_POST['message'];//added message for campaign [vastvikta][30-10-2025]
		           	$upload_data['description'] = $_POST['message'];
		           	$upload_data['file_name']	= $selected_file;
					$upload_data['slug'] = str_replace(" ", "-", strtolower($_POST['description']))  ;
					$upload_data['total_count'] = count($data);
					$upload_data['created_by']  = $_SESSION['userid'];

					if($_POST['channel_type'] == 'WhatsApp'){
						$upload_data['channel_type'] = 'WhatsApp';
					}else if($_POST['channel_type'] == 'SMS'){
						$upload_data['channel_type'] = 'SMS';
					}else if($_POST['channel_type'] == 'Email'){
						$upload_data['channel_type'] = 'Email';
					}
					$upload_data['queue_session_id'] = $queue_session_id;
					$upload_data['schedule_flag']	= $schedule_flag ;
					$upload_data['schedule_time']	= $schedule_date_time ;

					$this->insert_channel_bulk_uploads($upload_data); // insert sms bulk upload table

					echo json_encode(array('status' => 'success', "message" => "Data Imported successfully, Selected file is $selected_file"));die();
			        /* START TRANS CLOSE HERE */
			    }else{
					$data['file_upload'] = 'file not exist on path';
					echo json_encode(array('status' => 'fail', 'msg' => $data  ));die();
				}
			}
			else{
				$data['file_upload'] = 'Please select file to upload';
				echo json_encode(array('status' => 'fail', 'msg' => $data  ));die();
			}
		}		
		#################################################################################################
	    ###  ..../// Bulk Upload Section Close here 						
	    #################################################################################################			 				
	}

	// bulksms delete by channel  : By Aarti on 31-10-2025
	// this record delete by session id
	public function delete_bulk(){
	    global $db,$link;
	    $id = $_POST['id'];
    	// delete channel_bulk_uploads table
    	$delete1 = "DELETE FROM channel_bulk_uploads WHERE id = '$id'";
	    if (mysqli_query($link, $delete1)) {
	       echo json_encode([
			    'status' => 'success',
			    'message' => 'Deleted successfully'
			]);
			exit;

	    } else {
	        echo json_encode([
			    'status' => 'error',
			    'message' => 'error successfully'
			]);
			exit;

	    }    
	}
	// new code added - [Aarti][15-01-2026]
	public function update_bulk_campaign(){
	    global $db,$link,$bulkcampaign_path;

	    $id = $_POST['edit_id'];
	    $msg = mysqli_real_escape_string($link,$_POST['message']);
	    $subject = mysqli_real_escape_string($link,$_POST['description']);

	    $schedule_flag = isset($_POST['scheduletime']) ? 1 : 0;

	    $schedule_time = $schedule_flag ? date('Y-m-d H:i:s', strtotime($_POST['date'])) : '0000-00-00 00:00:00';

	    // get old file
	    $bulk_session_id="Bulk_".uniqid()."_".rand(0,1000000);	// Make Ther Unique BULK Upload ID 

	    $res = mysqli_query($link,"SELECT file_name FROM $db.channel_bulk_uploads WHERE id='$id'");
	    $row = mysqli_fetch_assoc($res);
	    $oldfile = $row['file_name'];

	    if(!empty($_FILES['file']['name'])){
		    // user selected new file
		    $do_uploade = $this->do_upload($bulk_session_id);
		    if($do_uploade){
			    $new_name = $_POST['file_upload_type'] == 'overwrite' ? $_FILES["file"]["name"] : $bulk_session_id; // Assuming 'uploaded_file' is the name of your file input
	            $imageFileType = strtolower(pathinfo($_FILES["file"]["name"],PATHINFO_EXTENSION));
	            $fileName = $new_name.'.'.$imageFileType;
	        }else{
	        	$data['file_upload'] = 'file upload Fial, Please try after some time';
				echo json_encode(array('status' => 'fail', 'msg' => $data));die();
	        }
		}
		else{
		    // user did NOT change file → keep old
		    $fileName = $_POST['old_file'];
		}

		// ================================
		// ATTACHMENT HANDLING (Bulk Edit)
		// ================================
		if (!empty($_FILES['attachments']['name'][0]) || !empty($_POST['existing_attachments'])) {
		    $attachments = $this->upload_attachments_files();
		} else {
		    // keep old attachments if nothing changed
		    $attachments = $_POST['old_attachments'] ?? '';
		}

	    mysqli_query($link,"
	        UPDATE $db.channel_bulk_uploads SET
	        slug='$subject',
	        description='$msg',
	        is_scheduled='$schedule_flag',
	        scheduled_time='$schedule_time',
	        file_name='$fileName',
	        attachments='$attachments'
	        WHERE id='$id'
	    ");
	    $userid = $_SESSION['userid'] ?? '';
        $userName = $this->getUserNameByAtxUserID($link, $db, $userid);
        $comments = "Bulk Campaign Modify By ".$userName;
        $this->logUserAction($link, $db, $userid, 'bulk_uploads', $comments);

	    echo json_encode(array('status' => 'success', "message" => "Campaign updated"));die();
	}

	// bulksms update by channel  : By Aarti on 31-10-2025
	// this record update by session id
	public function update_bulk(){
	    global $db,$link;

	    $queue_session = $_POST['queue_session'];
    	$message = mysqli_real_escape_string($link, $_POST['message']);
    	$schedule = !empty($_POST['schedule']) ? mysqli_real_escape_string($link, $_POST['schedule']) : '0000-00-00 00:00:00';

	    $channel = $_POST['channel'];

	    if($channel == 'SMS'){
	    	// update queue_bulk_relation table
	    	// $delete2 = "UPDATE  queue_bulk_relation SET message = '$message', schedule_time = '$schedule' ,schedule_flag = '1' WHERE out_queue_session = '$queue_session'";
		    // mysqli_query($link, $delete2);

	    	// update channel_bulk_uploads table
	    	$delete1 = "UPDATE channel_bulk_uploads SET description = '$message' WHERE queue_session = '$queue_session'";
		    mysqli_query($link, $delete1);

	    	// update sms out table
	    	$delete = "UPDATE sms_out_queue 
               SET message = '$message', schedule_time = '$schedule' ,schedule_flag = '1' WHERE queue_session = '$queue_session'";
               // echo $delete; 
		    if (mysqli_query($link, $delete)) {
		        echo "success";
		    } else {
		        echo "error";
		    }
	    }else if($channel == 'Email'){
	    	// update queue_bulk_relation table
	    	// $delete2 = "UPDATE  queue_bulk_relation SET message = '$message', schedule_time = '$schedule' ,schedule_flag = '1' WHERE out_queue_session = '$queue_session'";
		    // mysqli_query($link, $delete2);

	    	// update channel_bulk_uploads table
	    	$delete1 = "UPDATE channel_bulk_uploads SET description = '$message' WHERE queue_session = '$queue_session'";
		    mysqli_query($link, $delete1);

	    	// update sms out table
	    	// $delete = "UPDATE web_email_information_out 
            //    SET v_body = '$message', scheduling_date = '$schedule' ,schedule_flag = '1' WHERE queue_session = '$queue_session'";
		    // if (mysqli_query($link, $delete)) {
		    //     echo "success";
		    // } else {
		    //     echo "error";
		    // }
	    }else if($channel == 'WhatsApp'){
	    	// update queue_bulk_relation table
	    	// $delete2 = "UPDATE  queue_bulk_relation SET message = '$message', schedule_time = '$schedule' ,schedule_flag = '1' WHERE out_queue_session = '$queue_session'";
		    // mysqli_query($link, $delete2);

	    	// update channel_bulk_uploads table
	    	$delete1 = "UPDATE channel_bulk_uploads SET description = '$message' WHERE queue_session = '$queue_session'";
		    mysqli_query($link, $delete1);

	    	// update sms out table
	    	// $delete = "UPDATE whatsapp_out_queue 
            //    SET message = '$message', schedule_time = '$schedule' ,schedule_flag = '1' WHERE queue_session = '$queue_session'";
		    // if (mysqli_query($link, $delete)) {
		    //     echo "success";
		    // } else {
		    //     echo "error";
		    // }
	    }	    
	}
	/**
	 * ============================================================
	 * Function Name   : bulkcampaign_report()
	 * Module          : bulkcampaign Queue Report (Omnichannel)
	 * Description     : Server-side DataTable fetch for Interaction Queue report.
	 *                  Applies filters (date, agent, channel, user email, product,
	 *                  requesttype) and returns JSON response for DataTables.
	 *
	 * Developer       : [Aarti][14-01-2026]
	 * Last Modified   : 13-01-2026
	 *
	 * Notes:
	 *  - Supports DataTables server-side pagination
	 *  - Filters are applied dynamically
	 *  - recordsTotal and recordsFiltered are calculated correctly
	 * ============================================================
	 */
	public function bulkcampaign_report(){
	    global $db, $link;

	    // $groupid = $_SESSION['userid'] ?? '';
	    $groupid = $_SESSION['user_group'];

	    // DataTables params
	    $draw   = intval($_POST['draw'] ?? 1);
	    $start  = intval($_POST['start'] ?? 0);
	    $length = intval($_POST['length'] ?? 25);
	    $search = $_POST['search']['value'] ?? '';
	    $campaign = $_POST['campaign'] ?? '';

	    $columns = [
	        0 => 'list_name',
	        1 => 'channel_type',
	        2 => 'total_count',
	        3 => 'created_at',
	        4 => 'schedule_time',
	        5 => 'status',
	        6 => 'verified_by',
	        7 => 'created_by'
	    ];

	    $orderIndex = $_POST['order'][0]['column'] ?? 3;
	    $orderDir   = $_POST['order'][0]['dir'] ?? 'DESC';
	    $orderCol   = $columns[$orderIndex] ?? 'created_at';

	    // WHERE
	    $where = "WHERE 1=1";

	    if ($campaign != '') {
	        $campaign = mysqli_real_escape_string($link, $campaign);
	        $where .= " AND channel_type = '$campaign'";
	    }

	    if ($search != '') {
	        $search = mysqli_real_escape_string($link, $search);
	        $where .= " AND (
	            list_name LIKE '%$search%'
	            OR created_by LIKE '%$search%'
	            OR verified_by LIKE '%$search%'
	        )";
	    }

	    // Total rows
	    $totalData = mysqli_fetch_row(mysqli_query(
	        $link,
	        "SELECT COUNT(*) FROM $db.channel_bulk_uploads WHERE channel_type='$campaign'"
	    ))[0];

	    // Filtered
	    $totalFiltered = mysqli_fetch_row(mysqli_query(
	        $link,
	        "SELECT COUNT(*) FROM $db.channel_bulk_uploads $where"
	    ))[0];

	    // Data query
	    $sql = "
	        SELECT *
	        FROM $db.channel_bulk_uploads
	        $where
	        ORDER BY $orderCol $orderDir
	        LIMIT $start, $length
	    ";

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

	    $data = [];

	    while ($row = mysqli_fetch_assoc($query)) {

	        // Status from queue table
	        $statuschannel = $row['status'];

	        // if ($statuschannel == '0') {
	        //     $status_text = 'Submit';
	        //     $verify = '';
	        // } elseif ($statuschannel == '1') {
	        //     $status_text = 'Delivered';
	        //     $verify = '';
	        // } elseif ($statuschannel == '2') {
	        //     $status_text = 'Approved';
	        //     $verify = '';
	        // } elseif ($statuschannel == '3') {
	        //     $status_text = 'Deleted';
	        //     $verify = '';
	        // } elseif ($statuschannel == '4') {
	        //     $status_text = 'Not Verified';
	        //     $verify = ' (To be Verified)';
	        // } else {
	        //     $status_text = 'N/A';
	        //     $verify = '';
	        // }
			$statuschannel = $row['status'];
			$runAction     = isset($row['run_action']) ? (int)$row['run_action'] : 0;

			$status_text = 'N/A';
			$verify = '';

			if ($statuschannel == '0') {
			    $status_text = '<span class="badge badge-secondary">Submit</span>';
			    $verify = '';
			} elseif ($statuschannel == '1') {
			    $status_text = '<span class="badge badge-success">Delivered</span>';
			    $verify = '';
			} elseif ($statuschannel == '2') {

			    // First priority: if paused manually, always show Paused
			    if ($runAction == 1) {
			        $status_text = '<span class="badge badge-warning">Paused</span>';
			    } else {

			        // Default
			        $status_text = '<span class="badge badge-info">Approved</span>';

			        // Schedule-based status logic
			        if (
			            isset($row['is_scheduled']) && $row['is_scheduled'] == '1' &&
			            !empty($row['scheduled_time']) &&
			            $row['scheduled_time'] != '0000-00-00 00:00:00'
			        ) {
			            $nowTime      = time();
			            $scheduleTime = strtotime($row['scheduled_time']);
			            $diffMinutes  = ($nowTime - $scheduleTime) / 60;

			            // If time not reached yet => Scheduled
			            if ($nowTime < $scheduleTime) {
			                // $status_text = '<span class="badge badge-dark">Scheduled</span>';
			                $status_text = '<span class="badge badge-dark">Approved</span>';
			            }
			            // If current time matched and within 20 min => Running
			            elseif ($nowTime >= $scheduleTime && $diffMinutes <= 20) {
			                $status_text = '<span class="badge badge-primary">Running</span>';
			            }
			            // After 20 min => keep Approved
			            else {
			                $status_text = '<span class="badge badge-info">Approved</span>';
			            }
			        }
			    }

			    $verify = '';

			} elseif ($statuschannel == '3') {
			    $status_text = '<span class="badge badge-danger">Deleted</span>';
			    $verify = '';
			} elseif ($statuschannel == '4') {
			    $status_text = '<span class="badge badge-warning">Not Verified</span>';
			    $verify = ' (To be Verified)';
			} else {
			    $status_text = '<span class="badge badge-secondary">N/A</span>';
			    $verify = '';
			}
	        // Schedule
	        if($row['is_scheduled'] == '1'){
	        $schedule = (!empty($row['scheduled_time']) && $row['scheduled_time'] != '0000-00-00 00:00:00')
	            ? $row['scheduled_time']
	            : '-';
	            $schedule = date('Y-m-d H:i:s', strtotime($schedule));
	        }else{
	        	$schedule = '-';
	        }

	        // Users
	        $created_by  = $this->getUserName($row['created_by']);
	        $verified_by = $this->getUserName($row['approved_by']);
	        $subject =  htmlspecialchars($row['slug']);
	        $remark = htmlspecialchars($row["remark"]); 
	        $list_name = htmlspecialchars($row["list_name"]);

	        // Download
	        $path = BasePathStorage . '/bulkcampaign/' . $row['file_name'];
	        $download = '<a href="../../'.$path.'" target="_blank" class="dropdown-item">
	                        <i class="fa fa-download"></i> Download File
	                     </a>';

	        $postEscaped   = htmlspecialchars($row['description'], ENT_QUOTES);
			$imagesEscaped = htmlspecialchars(json_encode($attachments_array), ENT_QUOTES);

			// -----------------------------
			// GROUP CHECK
			// -----------------------------
			// $isSupervisor = ($groupid === '060000');
			// $isBackoffice = ($groupid === '080000');

			$isSupervisor = ($groupid === '080000');
			$isBackoffice = ($groupid === '060000');
			
			$isAgent      = (!$isSupervisor && !$isBackoffice);

			// -----------------------------
			// VERIFY / REJECT LOGIC
			// -----------------------------
			$canVerify = false;
			$verifyLevel = '';

			if ($statuschannel == '4') {
			    // Level 2 verification
			    if ($isBackoffice) {
			        $canVerify = true;
			        $verifyLevel = 2;
			    }
			}

			if ($statuschannel == '2') {
			    // Level 3 verification (Backoffice only)
			    if ($isSupervisor) {
			        $canVerify = true;
			        $verifyLevel = 3;
			    }
			}

			// -----------------------------
			// RESET ACTIONS PER ROW
			// -----------------------------
			$actions = [];
			$action_flow = '';
			
			// -----------------------------
			// VIEW (ALWAYS AVAILABLE)
			// -----------------------------
			$actions[] = '
			<a class="dropdown-item viewMsg"
			    href="javascript:void(0);"
			    data-id="'.$row['id'].'"
			    data-subject="'.htmlspecialchars($row['slug'], ENT_QUOTES).'"
			    data-message="'.htmlspecialchars($row['description'], ENT_QUOTES).'"
			    data-attachments="'.htmlspecialchars($row['attachments'], ENT_QUOTES).'"
			    data-channel="'.$row['channel_type'].'"
			    data-status="'.$statuschannel.'"
			    data-can_verify="'.($canVerify ? 1 : 0).'"
			    data-level="'.$verifyLevel.'"
			    data-file="'.$row['file_name'].'"
			    data-groupid="'.$groupid.'">
			    <i class="fa fa-eye"></i> View
			</a>';

			// -----------------------------
			// EDIT (ONLY AGENT + SUPERVISOR)
			// -----------------------------

			$dataSchedule = '';
			if ($row['scheduled_time'] != '0000-00-00 00:00:00' && !empty($row['scheduled_time'])) {
			    $dataSchedule = date('d-m-Y H:i', strtotime($row['scheduled_time']));
			}


			if ($statuschannel != '0' && $statuschannel != '3') {
			    $actions[] = '
			    <a class="dropdown-item editBulk"
			        href="javascript:void(0);"
			        data-id="'.$row['id'].'"
			        data-list_name="'.htmlspecialchars($row['list_name'], ENT_QUOTES).'"
			        data-subject="'.htmlspecialchars($row['slug'], ENT_QUOTES).'"
			        data-message="'.htmlspecialchars($row['description'], ENT_QUOTES).'"
			        data-schedule="'.$dataSchedule.'"
			        data-file="'.$row['file_name'].'"
			        data-sflag="'.$row['is_scheduled'].'"
			        data-channel_type="'.$campaign.'"
			        data-attachments="'.htmlspecialchars($row['attachments'], ENT_QUOTES).'">
			        <i class="fa fa-edit"></i> Edit
			    </a>';
			}

			// -----------------------------
			// VIEW REMARKS (IF EXISTS)
			// -----------------------------
			if (!empty($remark)) {
			    $actions[] = '
			    <a class="dropdown-item view-remarks"
			        href="javascript:void(0);"
			        data-id="'.$row['id'].'"
			        data-remarks="'.$remark.'">
			        <i class="fa fa-comment"></i> View Remarks
			    </a>';
			}
			$nowTime = time();
			$isScheduledRunning = false;
			$isScheduledFuture  = false;

			if ($statuschannel == '2' && $row['is_scheduled'] == '1' && !empty($row['scheduled_time']) && $row['scheduled_time'] != '0000-00-00 00:00:00') {
			    $scheduleTime = strtotime($row['scheduled_time']);
			    $diffMinutes  = ($nowTime - $scheduleTime) / 60;

			    if ($nowTime >= $scheduleTime && $diffMinutes <= 20) {
			        $isScheduledRunning = true;
			    }

			    if ($nowTime < $scheduleTime) {
			        $isScheduledFuture = true;
			    }
			}

			// If paused -> show Resume
			if ($statuschannel == '2' && $runAction == 1) {
			    $actions[] = '
			    <a class="dropdown-item resumeBulkAction"
			        href="javascript:void(0);"
			        data-id="'.$row['id'].'">
			        <i class="fa fa-play text-success"></i> Resume
			    </a>';
			}
			// If currently running -> show Pause
			elseif ($statuschannel == '2' && $runAction == 0 && $isScheduledRunning) {
			    $actions[] = '
			    <a class="dropdown-item pauseBulkAction"
			        href="javascript:void(0);"
			        data-id="'.$row['id'].'">
			        <i class="fa fa-pause text-warning"></i> Pause
			    </a>';
			}
			// If approved but future scheduled -> optionally show Run
			elseif ($statuschannel == '2' && $runAction == 0 && $isScheduledFuture) {
			    $actions[] = '
			    <a class="dropdown-item runBulkAction"
			        href="javascript:void(0);"
			        data-id="'.$row['id'].'">
			        <i class="fa fa-play text-success"></i> Run
			    </a>';
			}
			// -----------------------------
			// DROPDOWN RENDER
			// -----------------------------
			if (!empty($actions)) {
			    $action_flow = '
			    <div class="dropdown" id="bulkdrown">
			        <button class="btn btn-sm btn-light dropdown-toggle"
			            type="button"
			            data-bs-toggle="dropdown" data-bs-display="static" aria-expanded="false">
			            ...
			        </button>
			        <div class="dropdown-menu">
			            '.implode('', $actions).'
			        </div>
			    </div>';
			} else {
			    $action_flow = '-';
			}

	        // DataTables row
	        $data[] = [
	        	"DT_RowId" => "rowbulk_" . $row['id'], 
	            $row['list_name'],
	            $row['channel_type'],
	            $row['total_count'],
	            date('d-m-Y H:i', strtotime($row['created_at'])),
	            $schedule,
	            $status_text,
	            '<span>'.$verified_by.$verify.'</span>',
	            '<span>'.$created_by.'</span>',
	            $action_flow
	        ];
	    }

	    echo json_encode([
	        "draw" => $draw,
	        "recordsTotal" => $totalData,
	        "recordsFiltered" => $totalFiltered,
	        "data" => $data
	    ]);
	    exit;
	}
	public function pause_bulk_campaign(){
	    global $db, $link;

	    $id = intval($_POST['id']);
	    $channel_type = $_POST['channel_type'];

	    // Get queue_session from bulk table
	    $res = mysqli_query($link, "
	        SELECT queue_session
	        FROM $db.channel_bulk_uploads
	        WHERE id='$id'
	        LIMIT 1
	    ");

	    $row = mysqli_fetch_assoc($res);

	    if(!$row || empty($row['queue_session'])){
	        echo "invalid";
	        exit;
	    }

	    $queue_session = mysqli_real_escape_string($link, $row['queue_session']);

	    // Update bulk campaign status
	    mysqli_query($link, "
	        UPDATE $db.channel_bulk_uploads
	        SET run_action='1'
	        WHERE id='$id'
	    ");

	 
	    if($channel_type = 'Email'){
		   // Pause only pending unsent emails of this bulk campaign
		    mysqli_query($link, "
		        UPDATE $db.web_email_information_out
		        SET expiry_flag='1'
		        WHERE queue_session='$queue_session'
		          AND I_Status='1'
		          AND email_type='OUT'
		    ");
		}else if($channel_type == 'SMS'){

			mysqli_query($link, "
		        UPDATE $db.sms_out_queue
		        SET expiry_flag='1'
		        WHERE queue_session='$queue_session' AND status='1'
		    ");

		}else if($channel_type == 'WhatsApp'){

			mysqli_query($link, "
		        UPDATE $db.whatsapp_out_queue
		        SET status='2'
		        WHERE queue_session='$queue_session' AND status='0'
		    ");
		}

	    echo "paused";
	    exit;
	}
	public function resume_bulk_campaign(){
	    global $db, $link;

	    $id = intval($_POST['id']);
	    $channel_type = $_POST['channel_type'];

	    // Get queue_session from bulk table
	    $res = mysqli_query($link, "
	        SELECT queue_session
	        FROM $db.channel_bulk_uploads
	        WHERE id='$id'
	        LIMIT 1
	    ");

	    $row = mysqli_fetch_assoc($res);

	    if(!$row || empty($row['queue_session'])){
	        echo "invalid";
	        exit;
	    }

	    $queue_session = mysqli_real_escape_string($link, $row['queue_session']);

	    // Update bulk campaign status
	    mysqli_query($link, "
	        UPDATE $db.channel_bulk_uploads
	        SET run_action='0'
	        WHERE id='$id'
	    ");

	    if($channel_type = 'Email'){
		    // Resume only pending unsent emails of this bulk campaign
		    mysqli_query($link, "
		        UPDATE $db.web_email_information_out
		        SET expiry_flag='0'
		        WHERE queue_session='$queue_session'
		          AND I_Status='1'
		          AND email_type='OUT'
		    ");
		}else if($channel_type == 'SMS'){

			mysqli_query($link, "
		        UPDATE $db.sms_out_queue
		        SET expiry_flag='0'
		        WHERE queue_session='$queue_session' AND status='2'
		    ");

		}else if($channel_type == 'WhatsApp'){

			mysqli_query($link, "
		        UPDATE $db.whatsapp_out_queue
		        SET status='0'
		        WHERE queue_session='$queue_session' AND status='2'
		    ");
		}

	    echo "resumed";
	    exit;
	}
	public function run_bulk_campaign(){
	    global $db, $link;

	    $id = intval($_POST['id']);
	    $now = date('Y-m-d H:i:s');

	    // Get campaign data
	    $res = mysqli_query($link, "
	        SELECT queue_session, is_scheduled, scheduled_time
	        FROM $db.channel_bulk_uploads
	        WHERE id='$id'
	        LIMIT 1
	    ");

	    $row = mysqli_fetch_assoc($res);

	    if(!$row || empty($row['queue_session'])){
	        echo "invalid";
	        exit;
	    }

	    $queue_session = mysqli_real_escape_string($link, $row['queue_session']);

	    // If scheduled time is future, make it run now
	    if($row['is_scheduled'] == '1' && !empty($row['scheduled_time']) && $row['scheduled_time'] != '0000-00-00 00:00:00' &&
	        strtotime($row['scheduled_time']) > strtotime($now)){
	        mysqli_query($link, "
	            UPDATE $db.channel_bulk_uploads
	            SET 
	                run_action='0',
	                is_scheduled='0',
	                scheduled_time='$now'
	            WHERE id='$id'
	        ");
	    } else {
	        mysqli_query($link, "
	            UPDATE $db.channel_bulk_uploads
	            SET run_action='0'
	            WHERE id='$id'
	        ");
	    }

	    if($channel_type = 'Email'){
		    // run now
		    mysqli_query($link, "
		        UPDATE $db.web_email_information_out
		        SET scheduling_date='$now'
		        WHERE queue_session='$queue_session'
		          AND I_Status='1'
		    ");
		}else if($channel_type == 'SMS'){

			mysqli_query($link, "
		        UPDATE $db.sms_out_queue
		        SET schedule_time='$now'
		        WHERE queue_session='$queue_session' AND status='1'
		    ");

		}else if($channel_type == 'WhatsApp'){

			mysqli_query($link, "
		        UPDATE $db.whatsapp_out_queue
		        SET schedule_time='$now'
		        WHERE queue_session='$queue_session' AND status='0'
		    ");
		}

	    echo "running";
	    exit;
	}
	// Function to get the username based on the provided user ID
	public 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;
	}
	public function add_remark_approve_reject(){
	    global $db,$link;

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

	    if($_POST['action_type'] == 'VERIFY'){
	    	$action_type = 'APPROVED';
	    }else if($_POST['action_type'] == 'REJECT'){
	    	$action_type = 'REJECT';

	    }
	    // else if($_POST['level'] == '2'){ 
	    // 	$action_type = 'APPROVE';
	    // }else if($_POST['level'] == '2'){ 
	    // 	$action_type = 'SUBMIT';
	    // }else{
	    // 	$action_type = mysqli_real_escape_string($link,$_POST['action_type']); // VERIFY / APPROVE / REJECT
	    // }

	    $channel= mysqli_real_escape_string($link,$_POST['channel_type']);
	    $user   = $_SESSION['userid'];

	    // fetch existing JSON
	    $res = mysqli_query($link,"SELECT remark FROM $db.channel_bulk_uploads WHERE id='$id'");
	    $row = mysqli_fetch_assoc($res);

	    $json = [];
	    if(!empty($row['remark'])){
	        $json = json_decode($row['remark'],true);
	        if(!is_array($json)) $json=[];
	    }
	    $username =  $this->getUserName($user);
	    // new audit row
	    $json[] = [
	        "user_id"     => $username,
	        "action"      => $action_type,
	        "remark"      => $remark,
	        "channel"     => $channel,
	        "datetime"    => date("Y-m-d H:i:s"),
	        "ip"          => $_SERVER['REMOTE_ADDR']
	    ];

	    $json_text = mysqli_real_escape_string($link,json_encode($json));
	    mysqli_query($link,"
	        UPDATE $db.channel_bulk_uploads
	        SET remark='$json_text'
	        WHERE id='$id'
	    ");

	    echo "success";
	}
	/* 
	Aauth :Aarti [15-01-2026]
	1.first create bulk insert into bulk table
	2 approved by superviocer then change only status
	3 final approved by backoffice then insert bulk details in channel table
	*/
		public function level_second_final_approve_bulk(){
		    global $db,$link;

		    $bulk_id = mysqli_real_escape_string($link,$_POST['id']);
		    $action  = mysqli_real_escape_string($link,$_POST['action_type']); // VERIFY / REJECT
		    $remark  = mysqli_real_escape_string($link,$_POST['remark']);
		    $user    = $_SESSION['userid'];

		    // fetch bulk row
		    $res = mysqli_query($link,"
		        SELECT * FROM $db.channel_bulk_uploads WHERE id='$bulk_id'
		    ");
		    $bulk = mysqli_fetch_assoc($res);

		    if(!$bulk){
		        echo "Invalid bulk";
		        exit;
		    }

		    // prevent double approval
		    // if($bulk['status']=='2'){
		    //     echo "Already approved";
		    //     exit;
		    // }

		    // save remark JSON
		    $this->add_remark_approve_reject();

		    // REJECT
		    if($action=="REJECT"){
		        mysqli_query($link,"
		            UPDATE $db.channel_bulk_uploads
		            SET status='3', approved_by='$user'
		            WHERE id='$bulk_id'
		        ");

		        $userid = $_SESSION['userid'] ?? '';
		        $comments = "Bulk Campaign Created Rejected by";
		        $this->logUserAction($link, $db, $userid, 'bulk_uploads', $comments);

		        echo "rejected";
		        exit;
		    }
		    if($_POST['level'] == '2'){ //level second to approved by supervisor.
			    // VERIFY / APPROVE
			    if($action=="VERIFY"){
			         mysqli_query($link,"
			            UPDATE $db.channel_bulk_uploads
			            SET status='2', approved_by='$user'
			            WHERE id='$bulk_id'
			        ");
			         $userid = $_SESSION['userid'] ?? '';
			         $userName = $this->getUserNameByAtxUserID($link, $db, $userid);
			        $comments = "Bulk Campaign Aprroved Level One By ".$userName;
			        $this->logUserAction($link, $db, $userid, 'bulk_uploads', $comments);

			        echo "approved";
			        exit;
			    }
			}else{ //level second to approved by backoffice.
			    // VERIFY / APPROVE
			    if($action=="VERIFY"){
			        $this->approve_bulk();   // BOT will now read CSV & push to queues
			        exit;
			    }
			}
		}	
		/*Insert into channel table bulk information*/ 
		public function approve_bulk(){
		    global $db,$link,$bulkcampaign_path;

		    $bulk_id = $_POST['id'];
		    $uid     = $_SESSION['userid'];

		    // fetch bulk upload header
		    $res = mysqli_query($link,"
		        SELECT * FROM $db.channel_bulk_uploads
		        WHERE id='$bulk_id'
		    ");
		    $bulk = mysqli_fetch_assoc($res);

		    if(!$bulk){
		        echo "Invalid bulk ID";
		        exit;
		    }

		    if($bulk['status']=='0'){
		        echo "Already approved";
		        exit;
		    }

		    $file          = $bulk['file_name'];
		    $channel       = $bulk['channel_type'];
		    $schedule_flag = $bulk['is_scheduled'];
		    $schedule_time = $bulk['schedule_time'];

		    $filepath = $bulkcampaign_path.$file;

		    if(!file_exists($filepath)){
		        echo "CSV file missing";
		        exit;
		    }

		    // mark approved
		    mysqli_query($link,"
		        UPDATE $db.channel_bulk_uploads
		        SET approved_by='$uid', status='0'
		        WHERE id='$bulk_id'
		    ");

		    $userid = $_SESSION['userid'] ?? '';
		    $userName = $this->getUserNameByAtxUserID($link, $db, $userid);
	        $comments = "Bulk Campaign Aprroved Level Final By ".$userName;
	        $this->logUserAction($link, $db, $userid, 'bulk_uploads', $comments);

		  // read CSV
		    $csv = fopen($filepath, 'r');

		    // Read header row FIRST (do not skip)
		    $headers = fgetcsv($csv);

		    if (!$headers || empty($headers)) {
		        fclose($csv);
		        echo "CSV header missing";
		        exit;
		    }

		    // Normalize headers
		    $headers = array_map(function($h){
		        return trim($h);
		    }, $headers);


		    while(($row = fgetcsv($csv)) !== false){

			    // Skip fully empty rows
			    if (empty(array_filter($row, function($v){
			        return trim($v) !== '';
			    }))) {
			        continue;
			    }

			     // Create row associative array using header => value
			    $rowData = [];
			    foreach ($headers as $index => $header) {
			        $rowData[$header] = isset($row[$index]) ? trim($row[$index]) : '';
			    }

			    // If CSV has first_name + last_name, auto create customer
				if (!empty($rowData['first_name']) || !empty($rowData['last_name'])) {
				    $rowData['customer'] = trim(($rowData['first_name'] ?? '') . ' ' . ($rowData['last_name'] ?? ''));
				}

			    // Replace <<header>> placeholders in message with row values
			    // this is code for Email
			    $finalMessage = $this->replace_placeholders($bulk['description'], $rowData);
			    
			    // this replace for sms and whatsapp
			    $finalMessageText = $this->preparePlainTextMessage($finalMessage);
			    
			    if($channel=='Email' && !empty($rowData['email'])){

			    	$accountNumber = '';
			    	$source ='Email Campaign';

				    $feedback_data['customerid'] = $accountNumber;
				    $feedback_data['ticketid'] = '';
				    $feedback_data['origin'] = base64_encode($source);
				    $feedback_data['customer_name'] = $rowData['first_name'];
				    $feedback_data['email'] = $rowData['email'];
				    $feedback_link = $this->create_feedbacklink($feedback_data,$bulk['form_id'],$bulk_id);

				     $extra_code = '<br>
					        As part of our commitment to continuous service improvement, 
					        we kindly invite you to share your feedback on the service provided 
					        by clicking <a href="'.$feedback_link.'">HERE</a>.
					        <br><br>';

					    // ✅ IMPORTANT LINE
					    $finalMessage .= $extra_code;
			    }

			    // echo "<pre>";
				// echo "Original:\n" . $bulk['description'] . "\n\n";
				// print_r($rowData);
				// echo "\nFinal:\n" . $finalMessage;
				// echo "</pre>";
				// exit;
				// die;

		        $mobile = preg_replace("/[^0-9]/",'',$row[0]);
		        $email  = trim($row[3]);

		        // SMS / WhatsApp
		        // if(in_array($channel,['SMS','WhatsApp']) && strlen($mobile)<10){
		        //     continue; // skip bad row
		        // }

		        // // Email
		        // if($channel=='Email' && !filter_var($email,FILTER_VALIDATE_EMAIL)){
		        //     continue;
		        // }

		        if($channel=='SMS'){
		            $this->insert_smsmessages([
		                'send_to'=>$mobile,
		                'send_from'=>'HELB',
		                'message'=>$finalMessageText,   // <-- personalized message
		                'message_type_flag'=>'2',
		                'status'=>'0',
		                'schedule_flag'=>$schedule_flag,
		                'schedule_time'=>$schedule_time,
		                'created_by'=>$uid,
		                'queue_session' => $bulk['queue_session']
		            ]);
		        }

		        else if($channel=='WhatsApp'){
		            $this->insert_WhatsApp_Out([
		                'send_to'=>$mobile,
		                'send_from'=>'HELB',
		                'message'=>$finalMessageText,   // <-- personalized message
		                'message_type_flag'=>'2',
		                'status'=>'0',
		                'schedule_flag'=>$schedule_flag,
		                'schedule_time'=>$schedule_time,
		                'created_by'=>$uid,
		                'queue_session' => $bulk['queue_session']
		            ]);
		        }

		        else if($channel=='Email'){
		            $this->insert_web_email_information_out([
						'send_from'         => 'HELB',
						'message'           => $finalMessage,
		                'email'=>$email,
		                'Subject'=>$bulk['list_name'],
		                'description'=>$finalMessage, 
		                'message_type_flag'=>'2',
		                'status'=>'0',
		                'schedule_flag'=>$schedule_flag,
		                'schedule_time'=>$schedule_time,
		                'created_by'=>$uid,
		                'queue_session' => $bulk['queue_session']
		            ]);
		        }
		    }

		    fclose($csv);

		    echo "approved";
		}
		public function preparePlainTextMessage($html)
		{
		    if (empty($html)) {
		        return '';
		    }

		    // Normalize line breaks for block tags before stripping HTML
		    $html = preg_replace('/<\s*br\s*\/?>/i', "\n", $html);
		    $html = preg_replace('/<\/p>/i', "\n", $html);
		    $html = preg_replace('/<\/div>/i', "\n", $html);
		    $html = preg_replace('/<li>/i', "• ", $html);
		    $html = preg_replace('/<\/li>/i', "\n", $html);

		    // Remove all remaining HTML tags
		    $text = strip_tags($html);

		    // Decode HTML entities (&nbsp;, &amp;, etc.)
		    $text = html_entity_decode($text, ENT_QUOTES | ENT_HTML5, 'UTF-8');

		    // Replace non-breaking spaces with normal spaces
		    $text = str_replace("\xC2\xA0", ' ', $text); // UTF-8 NBSP
		    $text = str_replace("\xA0", ' ', $text);     // fallback

		    // Remove weird Â character if still present
		    $text = str_replace("Â", '', $text);

		    // Clean extra spaces around new lines
		    $text = preg_replace("/[ \t]+/", ' ', $text);
		    $text = preg_replace("/ *\n */", "\n", $text);

		    // Remove multiple blank lines
		    $text = preg_replace("/\n{3,}/", "\n\n", $text);

		    return trim($text);
		}
		public function replace_placeholders($message, $rowData){
		    if (empty($message) || empty($rowData)) {
		        return $message;
		    }

		    // Decode HTML entities from CKEditor
		    // &lt;&lt;email&gt;&gt; => <<email>>
		    $message = html_entity_decode($message, ENT_QUOTES | ENT_HTML5, 'UTF-8');

		    // 1) Replace exact header placeholders first
		    foreach ($rowData as $key => $value) {
		        $key = trim($key);
		        $value = trim((string)$value);

		        if ($key === '') continue;

		        // Example: <<email>>, << Email >>, <<EMAIL>>
		        $pattern = '/<<\s*' . preg_quote($key, '/') . '\s*>>/iu';
		        $message = preg_replace($pattern, $value, $message);
		    }

		    // 2) Replace common aliases (important)
		    $aliases = [
		        'email'   => ['email', 'email address', 'e-mail', 'mail'],
		        'mobile'  => ['mobile', 'phone', 'phone number', 'msisdn', 'send to', 'contact number'],
		        'name'    => ['name', 'full name', 'customer name', 'user name'],
		        'subject' => ['subject', 'title']
		    ];

		    foreach ($aliases as $placeholder => $possibleHeaders) {
		        foreach ($possibleHeaders as $headerName) {
		            foreach ($rowData as $csvHeader => $csvValue) {
		                if (strcasecmp(trim($csvHeader), trim($headerName)) === 0) {
		                    $pattern = '/<<\s*' . preg_quote($placeholder, '/') . '\s*>>/iu';
		                    $message = preg_replace($pattern, trim((string)$csvValue), $message);
		                    break 2; // stop once matched
		                }
		            }
		        }
		    }

		    return $message;
		}
		private function get_csv_value($rowData, $possibleKeys = []){
		    foreach ($possibleKeys as $key) {
		        foreach ($rowData as $header => $value) {
		            if (strcasecmp(trim($header), trim($key)) === 0) {
		                return trim($value);
		            }
		        }
		    }
		    return '';
		}
		public function get_csv_fields(){
		    global $link, $db;

		    $module = $_POST['module'];

		    // 🔐 Map allowed modules to tables (SECURITY)
		    $allowedTables = [
		        'customers'    => "$db.web_accounts",
		        'interactions' => "$db.interaction"
		    ];

		    if (!isset($allowedTables[$module])) {
		        echo json_encode([
		            'status' => 'fail',
		            'message' => 'Invalid module'
		        ]);
		        exit;
		    }

		    $table = $allowedTables[$module];
		    // print_r($table);

		    $sql = "SHOW COLUMNS FROM $table";
		    $res = mysqli_query($link, $sql);

		    if (!$res) {
		        echo json_encode([
		            'status' => 'fail',
		            'message' => 'Unable to fetch columns'
		        ]);
		        exit;
		    }

		    $fields = [];

		    while ($row = mysqli_fetch_assoc($res)) {
		        // ❌ Skip sensitive / unwanted columns
		        if (in_array($row['Field'], ['password','token','deleted_at'])) {
		            continue;
		        }
		        $fields[] = $row['Field'];
		    }

		    echo json_encode([
		        'status' => 'success',
		        'fields' => $fields
		    ]);
		    exit;
		}

		public function generate_csv_from_db()
		{
		    global $link, $db, $bulkcampaign_path;

		    $module = $_POST['module'];
		    $fields = $_POST['fields'] ?? [];
		    $start  = $_POST['start'];
		    $end    = $_POST['end'];

		    if (empty($fields)) {
		        echo json_encode(['status'=>'fail','message'=>'No fields selected']);
		        exit;
		    }

		    $dir = rtrim($bulkcampaign_path,'/').'/';
		    if (!is_dir($dir)) mkdir($dir, 0777, true);

		    $filename = $module.'_'.time().'.csv';
		    $path = $dir.$filename;
		    $displaypath = 'unistorage/2224/bulkcampaign/'.$filename;

		    if ($module == 'customers') {
		        $table = "$db.web_accounts";
		        $dateColumn = "createddate";
		    } else {
		        $table = "$db.interaction";
		        $dateColumn = "created_date";
		    }

		    // Protect column names
		    $fieldSql = implode(',', array_map(fn($f)=>"`$f`", $fields));

		    $sql = "
		        SELECT $fieldSql
		        FROM $table
		        WHERE DATE($dateColumn)
		        BETWEEN '$start' AND '$end'
		    ";

		    $res = mysqli_query($link, $sql);
		    if (!$res) {
		        echo json_encode([
		            'status'=>'fail',
		            'message'=>mysqli_error($link)
		        ]);
		        exit;
		    }

		    $fp = fopen($path, 'w');
		    fputcsv($fp, $fields);

		    while ($row = mysqli_fetch_assoc($res)) {
		        fputcsv($fp, $row);
		    }
		    fclose($fp);

		    echo json_encode([
		        'status'   => 'success',
		        'filename' => $filename,
		        'path'     => $displaypath
		    ]);
		    exit;
		}
		public function check_campaign_name(){
		    global $db, $link;

		    $list_name = trim($_POST['list_name']);
		    $edit_id   = intval($_POST['edit_id']); // 0 for create

		    $sql = "SELECT id 
		            FROM {$db}.channel_bulk_uploads 
		            WHERE list_name = ? 
		            AND id != ? 
		            LIMIT 1";

		    $stmt = mysqli_prepare($link, $sql);
		    mysqli_stmt_bind_param($stmt, "si", $list_name, $edit_id);
		    mysqli_stmt_execute($stmt);
		    mysqli_stmt_store_result($stmt);

		    if (mysqli_stmt_num_rows($stmt) > 0) {
		        echo json_encode([
		            'status'  => 'fail',
		            'message' => 'Campaign name already exists. Please use another name.'
		        ]);
		    } else {
		        echo json_encode(['status' => 'success']);
		    }
		    exit;
			

		}
		public function replace_placeholders_preview_old($message, $rowData) {
		    if (empty($message) || empty($rowData)) {
		        return $message;
		    }

		    foreach ($rowData as $key => $value) {
		        $key = trim($key);
		        $pattern = '/<<\s*' . preg_quote($key, '/') . '\s*>>/i';
		        $message = preg_replace($pattern, $value, $message);
		    }

		    return $message;
		}
		public function replace_placeholders_preview($message, $rowData){
		    if (empty($message)) {
		        return [
		            'rendered_content' => $message,
		            'missing_placeholders' => []
		        ];
		    }

		    // Decode CKEditor entities: &lt;&lt;Name&gt;&gt; => <<Name>>
		    $message = html_entity_decode($message, ENT_QUOTES | ENT_HTML5, 'UTF-8');

		    // Normalize CSV headers for case-insensitive matching
		    $normalizedRowData = [];
		    $first_name = '';
		    $last_name='';
		    foreach ($rowData as $key => $value) {
		        $normalizedKey = mb_strtolower(trim($key), 'UTF-8');
		        $normalizedRowData[$normalizedKey] = $value;

		        if($normalizedKey == 'first_name'){
		        	$first_name .= $value;
		        }
		        if($normalizedKey == 'last_name'){
		        	$last_name .= $value;
		        }
		        if(!empty($first_name) && !empty($last_name)){
		        	$normalizedRowData['customer'] = $first_name.' '.$last_name;
		        }
		    }

		    $placeholders = $this->extract_placeholders_from_template($message);
		    $missingPlaceholders = [];

		    foreach ($placeholders as $placeholder) {
		        $normalizedPlaceholder = mb_strtolower(trim($placeholder), 'UTF-8');

		        if (array_key_exists($normalizedPlaceholder, $normalizedRowData)) {
		            $value = $normalizedRowData[$normalizedPlaceholder];

		            // Replace exact placeholder from template (case-insensitive)
		            $pattern = '/<<\s*' . preg_quote($placeholder, '/') . '\s*>>/iu';
		            $message = preg_replace($pattern, $value, $message);
		        } else {
		            $missingPlaceholders[] = $placeholder;
		        }
		    }

		    return [
		        'rendered_content' => $message,
		        'missing_placeholders' => array_values(array_unique($missingPlaceholders))
		    ];
		}

		public function get_first_available($rowData, $possibleKeys) {
		    foreach ($possibleKeys as $key) {
		        foreach ($rowData as $header => $value) {
		            if (strtolower(trim($header)) === strtolower($key)) {
		                return $value;
		            }
		        }
		    }
		    return '';
		}
		public function extract_placeholders_from_template($message){
		    $placeholders = [];

		    if (empty($message)) {
		        return $placeholders;
		    }

		    // Decode CKEditor entities: &lt;&lt;Name&gt;&gt; => <<Name>>
		    $message = html_entity_decode($message, ENT_QUOTES | ENT_HTML5, 'UTF-8');

		    preg_match_all('/<<\s*(.*?)\s*>>/u', $message, $matches);

		    if (!empty($matches[1])) {
		        foreach ($matches[1] as $ph) {
		            $ph = trim($ph);
		            if ($ph !== '') {
		                $placeholders[] = $ph;
		            }
		        }
		    }

		    return array_values(array_unique($placeholders));
		}
		public function logUserAction($link, $db, $user_id, $actionlog, $comments) {
		    $ip = getenv('REMOTE_ADDR') ?: '';
		    $sql = "INSERT INTO {$db}.web_audit_history (user_id, action, created_on, ticket_id, comments, ip_address) VALUES (?, ?, NOW(), NULL, ?, ?)";
		    $stmt = mysqli_prepare($link, $sql);
		    mysqli_stmt_bind_param($stmt, "isss", $user_id, $actionlog, $comments, $ip);
		    return mysqli_stmt_execute($stmt);
		}
		public function getUserNameByAtxUserID($link, $db, $atxUserID) {
	        // $userName = 'Someone'; // Default fallback

	        $query = "SELECT AtxUserName FROM {$db}.uniuserprofile WHERE AtxUserID = ?";
	        $stmt = mysqli_prepare($link, $query);

	        if ($stmt) {
	            mysqli_stmt_bind_param($stmt, 's', $atxUserID);
	            mysqli_stmt_execute($stmt);
	            mysqli_stmt_bind_result($stmt, $fetchedName);
	            if (mysqli_stmt_fetch($stmt)) {
	                $userName = $fetchedName;
	            }
	            mysqli_stmt_close($stmt);
	        }

	        return $userName;
		}

		// save template
		public function save_template(){
			global $db,$link;

			$id = $_POST['id'];
			$name = $_POST['name'];
			$content = $_POST['template_content'];
			$type = $_POST['type'];

			if ($id == '') {
			    $sql = "INSERT INTO $db.bulksms_templates (name, template_content, type, created_by)
			            VALUES ('$name', '$content', '$type', 1)";
			} else {
			    $sql = "UPDATE $db.bulksms_templates 
			            SET name='$name', template_content='$content'
			            WHERE id='$id'";
			}
			mysqli_query($conn, $sql);
			echo "success";
		}
		public function get_template_saved(){
			global $link,$db;

			$type = $_GET['type'];
			$res = mysqli_query($conn, "SELECT * FROM bulksms_templates WHERE type='$type'");
			echo '<option value="0">Select Template</option>';
			while ($row = mysqli_fetch_assoc($res)) {
			    echo '<option value="'.htmlspecialchars($row['template_content']).'">
			            '.$row['name'].'
			          </option>';
			}
		}

		function create_feedbacklink($data = array(),$form_id,$parent_id){
			    global $db, $SiteURL, $link;
			 
			    $Type = '10';
			    $Ticket_id = mysqli_real_escape_string($link, $data['ticketid']);
			    $AgentID = mysqli_real_escape_string($link, $_SESSION['userid']);
			    $customer_email = mysqli_real_escape_string($link, $data['email']);
			    $customer_name = mysqli_real_escape_string($link, $data['customer_name']);
			    $customerid = mysqli_real_escape_string($link, $data['customerid']);
			    $origin = mysqli_real_escape_string($link, $data['origin']);
			 
			    $customeridencoded = base64_encode($customerid);
			    $d_requestTime = date("Y-m-d H:i:s");
			 	$form_path = 'feedback_form_new.php'; // default fallback

				if(!empty($form_id)){
				    $form_id = mysqli_real_escape_string($link, $form_id);

				    $form_res = mysqli_query($link, "
				        SELECT form_path 
				        FROM form_master 
				        WHERE id='$form_id' AND status='1' 
				        LIMIT 1
				    ");

				    if($form_res && mysqli_num_rows($form_res) > 0){
				        $form_row = mysqli_fetch_assoc($form_res);
				        $form_path = $form_row['form_path'];
				    }
				}
			 	if(!empty($Ticket_id)){
				    // ✅ 1. Check if record already exists
				    $check_sql = "SELECT id FROM $db.tbl_survey_request WHERE Ticket_id = '$Ticket_id' LIMIT 1";
				 
				    // echo "Check Query: " . $check_sql . "<br>"; // DEBUG
				 
				    $result = mysqli_query($link, $check_sql);
				 
				    if ($result && mysqli_num_rows($result) > 0) {
				        $row = mysqli_fetch_assoc($result);
				 
				        $existing_id = $row['id'];
				        $id = base64_encode($existing_id);
				 
				        return $SiteURL . "CRM/feedback_form_new.php?id=" . $customeridencoded .
				               "&origin=" . $origin .
				               "&caseid=" . $Ticket_id .
				               "&feedbackid=" . $id;
				    }
				}
			 
			    // ✅ 2. Insert new record
			    $insert_sql = "INSERT INTO $db.tbl_survey_request
			        (Type, Ticket_id, AgentID_Name, d_requestTime, customer_name, customer_email, sent_flag,parent_id)
			        VALUES ('$Type', '$Ticket_id', '$AgentID', '$d_requestTime', '$customer_name', '$customer_email', '0','$parent_id')";
			 
			    // echo "Insert Query: " . $insert_sql . "<br>"; // DEBUG
			 
			    $insert_result = mysqli_query($link, $insert_sql);
			 
			    if ($insert_result) {
			        $last_id = mysqli_insert_id($link);
			 
			        // echo "Inserted ID: " . $last_id . "<br>"; // DEBUG
			 
			        $id = base64_encode($last_id);
			 
			         // If full URL is stored, use directly. Otherwise build CRM path.
				    if (!empty($form_path) && preg_match('/^https?:\/\//i', $form_path)) {
				        $final_form_url = $form_path;
				    } else {
				        $final_form_url = rtrim($SiteURL, '/') . "/CRM/" . ltrim($form_path, '/');
				    }

				    return $final_form_url . "?id=" . $customeridencoded .
				           "&origin=" . $origin .
				           "&caseid=" . $Ticket_id .
				           "&feedbackid=" . $id;

			    } else {
			        // echo "Insert Error: " . mysqli_error($link);
			    }
			 
			    return false;
			}

}
$a = new BULK();
?>
