<?php
/**
 * This file handles the database related calls for the audit logs.
 *
 * @package object-data-sync-for-salesforce\Services
 */

namespace MoSfSyncSalesforce\Services;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use MoSfSyncSalesforce\Helper\Instance;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
/**
 * This class is responsible for handling the database related calls for the audit logs.
 */
class Audit_DB {

	use Instance;

	/**
	 * Name of the audit log WordPress table.
	 *
	 * @var string
	 */
	public $audit_log_table_name;

	/**
	 * Global object of `$wpdb`, the WordPress database.
	 *
	 * @var object
	 */
	public $db;

	/**
	 * Creates instance of the class.
	 *
	 * @return __CLASS__
	 */
	public static function instance() {
		global $wpdb;
		if ( is_null( self::$instance ) ) {
			self::$instance = new self();
		}
		self::$instance->db                   = $wpdb;
		self::$instance->audit_log_table_name = 'mo_sf_sync_audit_log_db';
		return self::$instance;
	}

	/**
	 * Creates the audit log table.
	 *
	 * @return bool
	 */
	public function mo_sf_sync_create_audit_log_table() {

		$current_charset_collate = $this->db->get_charset_collate();
		$create_table_query      = "CREATE TABLE $this->audit_log_table_name (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            direction text COLLATE utf8mb4_unicode_ci NOT NULL,
            salesforce_id text COLLATE utf8mb4_unicode_ci NOT NULL,
            wordpress_object text COLLATE utf8mb4_unicode_ci NOT NULL,
            wordpress_id text COLLATE utf8mb4_unicode_ci NOT NULL,  
            user_action text COLLATE utf8mb4_unicode_ci NOT NULL,
            action_status text COLLATE utf8mb4_unicode_ci NOT NULL,
            response text COLLATE utf8mb4_unicode_ci NOT NULL,
            time_stamp timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
            PRIMARY KEY (id)
          ) ENGINE=InnoDB $current_charset_collate";

		$created_table = dbDelta( $create_table_query, true );

		if ( empty( $created_table ) ) {
			return false;
		}
		return true;
	}

	/**
	 * Adds an entry to the audit log table.
	 *
	 * @param string $direction direction of the sync.
	 * @param string $salesforce_id salesforce record id.
	 * @param string $username WordPress username/post_title.
	 * @param string $user_action sync action either it is create or update.
	 * @param string $action_status status of the sync, either success or fail.
	 * @param string $response response of the sync action.
	 * @param string $wp_object WordPress object name.
	 * @return void
	 */
	public function mo_sf_sync_add_log( $direction = '', $salesforce_id = '', $username = '', $user_action = '', $action_status = '', $response = '', $wp_object = 'user' ) {
		$data   = array(
			'direction'        => $direction,
			'salesforce_id'    => $salesforce_id,
			'wordpress_id'     => $username,
			'user_action'      => $user_action,
			'action_status'    => $action_status,
			'response'         => $response,
			'wordpress_object' => $wp_object,
		);
		$format = array( '%s', '%s', '%s', '%s', '%s', '%s', '%s' );
		$this->db->insert( $this->audit_log_table_name, $data, $format );
	}

	/**
	 * Fetches all audit entries from the audit log table.
	 *
	 * @return array
	 */
	public function mo_sf_sync_get_audit_logs() {
		$query        = $this->db->prepare( 'SELECT * FROM %i', $this->audit_log_table_name );
		$query_result = $this->db->get_results( $query );

		return $query_result;
	}

	/**
	 * Delete all the audit entries from the audit log table.
	 *
	 * @return void
	 */
	public function mo_sf_sync_clear_all_logs() {
		$this->db->query( "TRUNCATE TABLE `$this->audit_log_table_name`" );
	}

	/**
	 * Saves the search settings for the audit logs.
	 *
	 * @return void
	 */
	public function mo_sf_sync_save_advance_search_settings() {
		if ( $this->mo_sf_sync_check_option_admin_referer( 'mo_sf_sync_advanced_reports' ) ) {

			$username    = '';
			$direction   = '';
			$status      = '';
			$user_action = '';
			$from_date   = '';
			$to_date     = '';
			// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
			if ( isset( $_POST['username'] ) ) {
				// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
				$username = sanitize_text_field( wp_unslash( $_POST['username'] ) );
			}
			// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
			if ( isset( $_POST['direction'] ) ) {
				// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
				$direction = sanitize_text_field( wp_unslash( $_POST['direction'] ) );
			}
			// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
			if ( isset( $_POST['status'] ) ) {
				// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
				$status = sanitize_text_field( wp_unslash( $_POST['status'] ) );
			}
			// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
			if ( isset( $_POST['user_action'] ) ) {
				// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
				$user_action = sanitize_text_field( wp_unslash( $_POST['user_action'] ) );
			}
			// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
			if ( isset( $_POST['from_date'] ) ) {
				// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
				$from_date = sanitize_text_field( wp_unslash( $_POST['from_date'] ) );
			}
			// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
			if ( isset( $_POST['to_date'] ) ) {
				// phpcs:ignore WordPress.Security.NonceVerification.Missing -- no need for nonce verification here as its already done on line 131 of this file.
				$to_date = sanitize_text_field( wp_unslash( $_POST['to_date'] ) );
			}

			update_option( 'mo_sf_sync_advanced_search_username', $username );
			update_option( 'mo_sf_sync_advanced_search_direction', $direction );
			update_option( 'mo_sf_sync_advanced_search_status', $status );
			update_option( 'mo_sf_sync_advanced_search_action', $user_action );
			update_option( 'mo_sf_sync_advanced_search_from_date', $from_date );
			update_option( 'mo_sf_sync_advanced_search_to_date', $to_date );
			update_option( 'mo_sf_sync_advanced_reports', true );
		} elseif ( $this->mo_sf_sync_check_option_admin_referer( 'mo_sf_sync_clear_advance_search' ) ) {
			delete_option( 'mo_sf_sync_advanced_search_username' );
			delete_option( 'mo_sf_sync_advanced_search_direction' );
			delete_option( 'mo_sf_sync_advanced_search_status' );
			delete_option( 'mo_sf_sync_advanced_search_action' );
			delete_option( 'mo_sf_sync_advanced_search_from_date' );
			delete_option( 'mo_sf_sync_advanced_search_to_date' );
			delete_option( 'mo_sf_sync_advanced_reports' );
		}
	}

	/**
	 * Used for nonce verification.
	 *
	 * @param string $option_name name of the option for nonce verification.
	 */
	public function mo_sf_sync_check_option_admin_referer( $option_name ) {
		return ( isset( $_POST['option'] ) && $_POST['option'] === $option_name && check_admin_referer( $option_name ) );
	}

	/**
	 * Fetch audit entries from the audit table according to the saved search settings.
	 *
	 * @return array
	 */
	public function mo_sf_sync_get_audit_using_advanced_search() {
		$myrows = '';
		if ( ! get_option( 'mo_sf_sync_advanced_reports' ) ) {
			$query  = $this->db->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d', array( $this->audit_log_table_name, 5000 ) );
			$myrows = $this->db->get_results( $query );
			return $myrows;
		}

		$filter_methods = array(
			'username'    => 'mo_sf_sync_filter_by_username',
			'direction'   => 'mo_sf_sync_filter_by_direction',
			'status'      => 'mo_sf_sync_filter_by_status',
			'user_action' => 'mo_sf_sync_filter_by_user_action',
			'date_range'  => 'mo_sf_sync_filter_by_date_range',
		);

		$filters = array(
			'username'    => get_option( 'mo_sf_sync_advanced_search_username' ),
			'direction'   => get_option( 'mo_sf_sync_advanced_search_direction' ),
			'status'      => get_option( 'mo_sf_sync_advanced_search_status' ),
			'user_action' => get_option( 'mo_sf_sync_advanced_search_action' ),
			'from_date'   => get_option( 'mo_sf_sync_advanced_search_from_date' ),
			'to_date'     => get_option( 'mo_sf_sync_advanced_search_to_date' ),
		);

		$where_conditions = array();
		$query_params     = array();

		foreach ( $filter_methods as $key => $callback ) {
			$where  = array();
			$params = array();
			if ( 'date_range' === $key ) {
				if ( empty( $filters['from_date'] ) || empty( $filters['to_date'] ) ) {
					continue;
				}
				list($where, $params) = $this->$callback( $filters['from_date'], $filters['to_date'] );
			} elseif ( ! empty( $filters[ $key ] ) ) {
				list($where, $params) = $this->$callback( $filters[ $key ] );
			} else {
				continue;
			}
			$where_conditions = array_merge( $where_conditions, (array) $where );
			$query_params     = array_merge( $query_params, (array) $params );
		}

		$base_query = 'SELECT * FROM %i';
		$where_conditions = array_filter( $where_conditions );

		if ( ! empty( $where_conditions ) ) {
			$where_clause = ' WHERE ' . implode( ' AND ', $where_conditions );
			$query        = $base_query . $where_clause . ' ORDER BY id DESC';
		} else {
			$query          = $base_query . ' ORDER BY id DESC LIMIT %d';
			$query_params[] = 5000;
		}

		array_unshift( $query_params, $this->audit_log_table_name );

		$prepared_query = $this->db->prepare( $query, $query_params );
		$myrows         = $this->db->get_results( $prepared_query );

		return $myrows;
	}

	/**
	 * Validates a date range and returns an array with error status and message.
	 *
	 * @param string $from_date The From Date.
	 * @param string $to_date The To Date.
	 * @return array ['error' => bool, 'message' => string]
	 */
	private function mo_sf_sync_validate_date_range( string $from_date, string $to_date ): array {
		$result   = array(
			'error'   => false,
			'message' => '',
		);
		$frm_date = \DateTime::createFromFormat( 'Y-m-d', $from_date );
		$t_date   = \DateTime::createFromFormat( 'Y-m-d', $to_date );

		if ( ! $frm_date || ! $t_date ) {
			$result['error']   = true;
			$result['message'] = 'Invalid date format';
		} elseif ( $frm_date->getTimestamp() > $t_date->getTimestamp() ) {
			$result['error']   = true;
			$result['message'] = 'Invalid selection date interval';
		}
		return $result;
	}

	/**
	 * Filter audit logs by username.
	 *
	 * @param string $username The username to filter by.
	 * @return array The WHERE clause and parameters.
	 */
	private function mo_sf_sync_filter_by_username( $username ) {
		if ( empty( $username ) ) {
			return array( '', array() );
		}
		$where = 'wordpress_id LIKE %s';
		$param = $this->db->esc_like( $username ) . '%';
		return array( $where, array( $param ) );
	}

	/**
	 * Filter audit logs by direction.
	 *
	 * @param string $direction The direction to filter by.
	 * @return array The WHERE clause and parameters.
	 */
	private function mo_sf_sync_filter_by_direction( $direction ) {
		$allowed_directions = array( 'sync_wp_to_sf', 'sync_sf_to_wp' );
		if ( empty( $direction ) || 'default' === $direction || ! in_array( $direction, $allowed_directions, true ) ) {
			return array( '', array() );
		}
		$direction = str_replace( '_', ' ', $direction );
		$where     = 'direction = %s';
		return array( $where, array( $direction ) );
	}

	/**
	 * Filter audit logs by status.
	 *
	 * @param string $status The status to filter by.
	 * @return array The WHERE clause and parameters.
	 */
	private function mo_sf_sync_filter_by_status( $status ) {
		$allowed_statuses = array( 'success', 'failed' );
		if ( empty( $status ) || 'default' === $status || ! in_array( $status, $allowed_statuses, true ) ) {
			return array( '', array() );
		}
		$where = 'action_status = %s';
		return array( $where, array( $status ) );
	}

	/**
	 * Filter audit logs by user action.
	 *
	 * @param string $user_action The user action to filter by.
	 * @return array The WHERE clause and parameters.
	 */
	private function mo_sf_sync_filter_by_user_action( $user_action ) {
		$allowed_actions = array( 'Create', 'Update' );
		if ( empty( $user_action ) || 'default' === $user_action || ! in_array( $user_action, $allowed_actions, true ) ) {
			return array( '', array() );
		}
		$where = 'user_action = %s';
		return array( $where, array( $user_action ) );
	}

	/**
	 * Filter audit logs by date range.
	 *
	 * @param string $from_date The start date.
	 * @param string $to_date   The end date.
	 * @return array The WHERE clause and parameters.
	 */
	private function mo_sf_sync_filter_by_date_range( $from_date, $to_date ) {
		if ( empty( $from_date ) || empty( $to_date ) || $from_date === $to_date ) {
			return array( '', array() );
		}

		$validation = $this->mo_sf_sync_validate_date_range( $from_date, $to_date );

		if ( $validation['error'] ) {
			return array( '', array() );
		}
		$where  = 'time_stamp BETWEEN %s AND %s';
		$params = array( $from_date . ' 00:00:00', $to_date . ' 23:59:59' );
		return array( $where, $params );
	}
}
