<?php

use WHMCS\Session;
use Carbon\Carbon;
use WHMCS\Database\Capsule;

$hook = array(
    'hook' => 'AfterCronJob',
    'function' => 'suiten_SyncDB'
);

if (!function_exists('suiten_SyncDB')) {

	function suiten_SyncDB($WHMCSobject) {
		
		global $rec_gwmsgid, $rec_sendid, $rec_wadate, $rec_wanum, $rec_errmsg, $rec_status;
		$global_module_name = 'SUITEN Whatsapp API';

		$result = WHMCS\Database\Capsule::table('tblconfiguration')->select('value')->where('setting', 'LogErrors')->first();
		$logerror = ($result->value == "on" ? TRUE : FALSE);

		// Ambil Data Config
		foreach (WHMCS\Database\Capsule::table('s10wa_config')->WHERE('suiten_Nama_Modul', $global_module_name)->get() as $modulwa_config) {
		    $gwhost = $modulwa_config->gwhost;
		    $gwdb = $modulwa_config->gwdb;
		    $gwuser = $modulwa_config->gwuser;
		    $gwpass = $modulwa_config->gwpass;
		}
		
		if ($gwhost == "" || $gwdb == "" || $gwuser == "" ||  $gwpass == "") {
			// logactivity( 'suiten_SyncDB: S10WA local only');
			return;
		}

		if (is_numeric($gwhost[0])) {
			$gwsqli = new mysqli($gwhost,$gwuser,$gwpass,$gwdb);
		} else {
			$gwsqli = new mysqli(substr($gwhost,1),$gwuser,$gwpass,$gwdb);
		}

		// Check connection
		if ($gwsqli -> connect_errno) {
			LogActivity("suiten_SyncDB: Failed to connect to MySQL (" . $gwhost . ") " . $gwsqli -> connect_error);
			exit();
		}
		
		//set charset
		if (!$gwsqli->set_charset("utf8")) {
			LogActivity("suiten_SyncDB: Error loading character set utf8: %s\n", $gwsqli->error);
		} else {
			//printf("Current character set: %s\n", $gwsqli->character_set_name());
		}		

		if ($logerror) logactivity( 'suiten_SyncDB: START ('.$gwsqli->character_set_name().')');	
		// logactivity( 'suiten_SyncDB: S10WA opened DB');

		//groupid
		// 0 = still in local
		// 1 = pushed from local to gateway
		// 2 = gateway updated
		// 3 = pushed from gateway to local
		// 4 = new record at gateway, push to local, delete at gateway then set local to 0
		

		// STEP  1: Tarik update dari Gateway ke Local ( 2 -> 3 )
		$processcount  = 0;
		$result = $gwsqli->query('SELECT * FROM s10wa_sendmsg WHERE s10wa_groupid=2');	
		//$numrow =  mysqli_fetch_array($result);

		foreach ( $result as $msgrow ) {

			$rec_gwmsgid = $msgrow['gwmsgid'];
			$rec_sendid = $msgrow['s10wa_sendid'];
			$rec_wadate = $msgrow['s10wa_date'];
			$rec_wanum = $msgrow['s10wa_wanumber'];
			$rec_errmsg = $msgrow['s10wa_errormsg'];
			$rec_status = $msgrow['s10wa_status'];
			//  logactivity( 'suiten_SyncDB: [1] Analyze '  . $rec_gwmsgid . ' = ' .  $rec_sendid . ' @ ' . $rec_wadate . ' # ' . $rec_wanum);
			
			try {

					$updatedUserCount = Capsule::table('s10wa_sendmsg')
						->where('s10wa_sendid', $rec_sendid )
						->where('s10wa_date', $rec_wadate )
						->where('s10wa_wanumber', $rec_wanum )
						->update(
						[
							's10wa_errormsg' => $rec_errmsg,
							's10wa_status' => $rec_status,
							's10wa_groupid' => 3,
						]
					);
					
					if ($updatedUserCount > 0) {
						// report to Gateway that local has been updated
						mysqli_query($gwsqli, 'UPDATE s10wa_sendmsg SET s10wa_groupid=3 WHERE gwmsgid="'.$rec_gwmsgid.'"');

						$processcount += $updatedUserCount;
						// LogActivity("suiten_SyncDB: [1] Local Record updated " . $rec_sendid );
					} else {
						// LogActivity("suiten_SyncDB: Local Record mismatch " . $rec_sendid );
					}

					// LogActivity("suiten_SyncDB: Local Record updated " . $rec_sendid );
				
			} catch (\Exception $e) {
				LogActivity ("Gagal Update. {$e->getMessage()}");
			}
			
		}	// End STEP  1
		If ($processcount>0) {
			if ($logerror) LogActivity("suiten_SyncDB: [1] Gateway to Local Sync = " . $processcount);
		}

		// STEP  2: Insert new record dari Gateway ke Local ( 4 -> 3 )
		$processcount = 0;
		$result = $gwsqli->query('SELECT * FROM s10wa_sendmsg WHERE s10wa_groupid=4');
		foreach ( $result as $msgrow ) {


			// Perform potentially risky queries in a transaction for easy rollback.
			$rec_gwmsgid = $msgrow->gwmsgid;
			$rec_sendid = $msgrow->sendid;
			$rec_wadate = $msgrow->s10wa_date;
			$rec_wanum = $msgrow->s10wa_wanumber;
			$rec_wamsg = $msgrow->s10wa_message;
			$rec_errmsg = $msgrow->s10wa_errormsg;
			$rec_status = $msgrow->s10wa_status;
			if  (is_null($rec_wanum)) {
				$rec_wanum = '00';
			}

			try {
				Capsule::connection()->transaction(
					function ($connectionManager)
					{
						global $rec_gwmsgid, $rec_sendid, $rec_wadate, $rec_wanum, $rec_errmsg, $rec_status;
						$connectionManager->table('s10wa_sendmsg')->insert(
							[
								's10wa_date' => $rec_wadate,
								's10wa_wanumber' => $rec_wanum,
								's10wa_groupid' => (int)0,
								's10wa_interval' => (int)4,
								's10wa_priority' => (int)0,
								's10wa_message' => $rec_wamsg,
								's10wa_msgtype' => (int)1,
								's10wa_errormsg'  => $rec_errmsg,
								's10wa_status' => (int)0,
							]
						);
					}
				);
				$gwsqli->exec('DELETE s10wa_sendmsg WHERE gwmsgid=' .  $rec_gwmsgid);
				$processcount += 1;
				// LogActivity("suiten_SyncDB: Gateway New Record inserted GW" . $rec_gwmsgid );

			} catch (\Exception $e) {
				//echo "Uh oh! Inserting didn't work, but I was able to rollback. {$e->getMessage()}";
				if ($logerror) LogActivity("suiten_SyncDB: [2] Insert Failed = " . $e->getMessage());				
				
			}			

		} //   End STEP 2
		If ($processcount>0) {
			if ($logerror) LogActivity("suiten_SyncDB: [2] Gateway New to Local = " . $processcount);
		}

		// STEP  3: Push message dari local ke Gateway (groupid = 0, msg status=0) 

		$processcount =  0;
		foreach (WHMCS\Database\Capsule::table('s10wa_sendmsg')->WHERE([['s10wa_groupid','=',0],['s10wa_status','=',0]])->get() as $msgrow) {

			$rec_sendid = $msgrow->s10wa_sendid;
			$rec_wadate = $msgrow->s10wa_date;
			$rec_wanum = $msgrow->s10wa_wanumber;
			$rec_wamsg = $msgrow->s10wa_message;
			$rec_attachment = $msgrow->s10wa_attachment;
			$rec_clientid = $msgrow->s10wa_clientid;
			$rec_clientinfo = $msgrow->s10wa_clientinfo;		
			$rec_errmsg = $msgrow->s10wa_errormsg;
			$rec_status = $msgrow->s10wa_status;
			
			$esc_wamsg = mysqli_real_escape_string($gwsqli, $rec_wamsg);
			$esc_errmsg = mysqli_real_escape_string($gwsqli, $rec_errmsg);

			if  (is_null($rec_wanum)) {
				$rec_wanum = '000';
			}
			
			$result = $gwsqli->query('SELECT * FROM s10wa_sendmsg WHERE s10wa_sendid='.$rec_sendid);	
			$numrow =  mysqli_fetch_array($result);
			if ( $numrow>0 ) {
				$sql = "UPDATE s10wa_sendmsg SET ";
				$sql .= "s10wa_groupid = 0, ";
				$sql .= "s10wa_status = ". $rec_status . " ";
				$sql .= "WHERE s10wa_sendid = " . $rec_sendid;
			} else {
				$sql = "INSERT INTO s10wa_sendmsg ";
				$sql .= "(s10wa_sendid,s10wa_date,s10wa_wanumber,s10wa_groupid,s10wa_interval,";
				$sql .= "s10wa_priority,s10wa_message,s10wa_msgtype,s10wa_attachment,s10wa_clientid,s10wa_clientinfo,";
				$sql .= "s10wa_errormsg,s10wa_status) VALUES (";

				$sql .= $rec_sendid . ",'"  . $rec_wadate .  "','" . $rec_wanum . "',0,4,";
				$sql .= "0,'" . $esc_wamsg . "',1,'".$rec_attachment."',".$rec_clientid.",'".$rec_clientinfo."',";
				$sql .= "'" . $esc_errmsg . "'," . $rec_status .  ")";			
			}
			
	// LogActivity($esc_wamsg);			
	// LogActivity($sql);
			
			if (mysqli_query($gwsqli, $sql)) {
				//  LogActivity("suiten_SyncDB: [3] Inserted " . $rec_sendid );
				try {

					$updatedUserCount = Capsule::table('s10wa_sendmsg')
						->where('s10wa_sendid', $rec_sendid )
						->where('s10wa_date', $rec_wadate )
						->where('s10wa_wanumber', $rec_wanum )
						->update(
						[
							's10wa_groupid' => 2,
						]
					);
				
					if ($updatedUserCount > 0) {
						// ok groupid updated to 2
						//  LogActivity("suiten_SyncDB: Local Record GID#2 updated " . $rec_sendid );
						$processcount += $updatedUserCount;
					} else {
						if ($logerror) LogActivity("suiten_SyncDB: Fail set GID#2 for " . $rec_sendid );
					}
			
				} catch (\Exception $e) {
					LogActivity ("Gagal Update. {$e->getMessage()}");
				}
				
			} else {
				LogActivity('Gagal Insert '  .   $rec_sendid . ' ' .  mysqli_error($gwsqli));
				echo "Error: " . $sql . "<br>" . mysqli_error($gwsqli);
			}
			
		} // END  of  STEP 3
		
		If ($processcount>0) {
			if ($logerror) LogActivity("suiten_SyncDB: [3] Local to Gateway = " . $processcount);
		}

		// logactivity( 'suiten_SyncDB: S10WA Finish DBsync');
		
	}

}

return $hook;