<?php
/**
 * Mail Manager for WPForms DB Trait
 *
 * This trait used to ensure a class has only one instance
 * and provides a global access point to it.
 *
 * @link       https://mmw.diviaccessories.com/
 * @since      1.0.0
 *
 * @package    mail-manager-wpforms
 * @subpackage mail-manager-wpforms/trait
 */

namespace MMWPF\TRT;

/**
 * Mail Manager for WPForms DB Trait
 *
 * @return object
 */
trait Trait_DB {
	/**
	 * Instance
	 *
	 * @since    1.0.0
	 * @access   private
	 * @var      object $db_instance Instance of this class.
	 */
	private static $db_instance = null;

	/**
	 * Static method to get the instance of DB.
	 *
	 * @return object
	 */
	private static function get_instance() {

		if ( null === self::$db_instance ) {
			global $wpdb;

			$db    = apply_filters( 'mmwpf_database', $wpdb );
			$table = $db->prefix . 'mmwpf_database';

			self::$db_instance = (object) array(
				'db'              => $db,
				'table'           => $table,
				'entry_data'      => self::get_entry_data( $db, $table ),
				'single_data'     => self::get_single_entry_data( $db, $table ),
				'filter'          => self::get_entry_filter( $db, $table ),
				'count_entries'   => self::get_count_form_entry( $db, $table ),
				'csv_data'        => self::get_entry_array_for_csv( $db, $table ),
				'saved_by_ajax'   => self::save_entry_ajax( $db, $table ),
				'saved'           => self::save_entry( $db, $table ),
				'unreaded_single' => self::unreaded_entry( $db, $table ),
				'unreaded'        => self::unreaded_entry_bulk( $db, $table ),
				'trashed'         => self::move_trash( $db, $table ),
				'deleted'         => self::delete_permanent( $db, $table ),
				'restored'        => self::restore_entry( $db, $table ),
			);
		}

		return self::$db_instance;
	}

	/**
	 * Form entry table data
	 *
	 * @since      1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 *
	 * @return array Entry Data
	 */
	private static function get_entry_data( object $db, string $table ) {

		$form_id = isset( $_GET['formId'] ) ? absint( $_GET['formId'] ) : null;

		if ( null === $form_id ) {
			return array();
		}

		$query      = '';
		$search_key = isset( $_GET['s'] ) ? sanitize_text_field( wp_unslash( $_GET['s'] ) ) : '';
		$filter     = isset( $_GET['mmwpf_filter'] ) ? sanitize_key( wp_unslash( $_GET['mmwpf_filter'] ) ) : 'all';

		if ( $search_key && '' !== $search_key ) {
			$query = $db->prepare(
				'SELECT * from %i WHERE form_id=' . $form_id . " AND JSON_UNQUOTE(JSON_EXTRACT(form_value, '$.*.value')) Like '%{$search_key}%'",
				$table
			);
		} else {
			$is_filter = ' AND filter != 0';
			// subsubsub filter.
			if ( isset( $filter ) && null !== $filter ) {
				switch ( $filter ) {
					case 'trashed':
						$is_filter = ' AND filter = 0';
						break;
					case 'unread':
						$is_filter = ' AND status = 0 AND filter != 0';
						break;
					case 'starred':
						$is_filter = ' AND save = 1 AND filter != 0';
						break;
				}
			}

			$query = $db->prepare(
				'SELECT * FROM %i WHERE form_id=' . $form_id . $is_filter . '',
				$table
			);
		}

		return $db->get_results( $query, ARRAY_A );
	}

	/**
	 * Form entry table data
	 *
	 * @since      1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 *
	 * @return array Entry Data
	 */
	private static function get_single_entry_data( object $db, string $table ) {

		$form_id = isset( $_GET['formId'] ) ? absint( $_GET['formId'] ) : null;
		$id      = isset( $_GET['eId'] ) ? absint( $_GET['eId'] ) : null;

		if ( ! $id ) {
			return;
		}

		$query = $db->prepare(
			'SELECT form_value FROM %i 
				WHERE form_id = ' . $form_id . ' 
				AND id = ' . $id,
			$table
		);

		if ( ! $db->get_results( $query )
			&& empty( $db->get_results( $query )[0] ) ) {
			return;
		}

		return json_decode( $db->get_results( $query )[0]->form_value, true );
	}

	/**
	 * Get form entry data for CSV.
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback array Entry data for csv
	 */
	private static function get_entry_array_for_csv( object $db, string $table ) {

		return function ( $id ) use ( $db, $table ) {

			if ( ! $id ) {
				return array();
			}

			$query = '';

			$from_date    = isset( $_GET['from_date'] ) ? sanitize_text_field( wp_unslash( $_GET['from_date'] ) ) : '';
			$to_date      = isset( $_GET['to_date'] ) ? sanitize_text_field( wp_unslash( $_GET['to_date'] ) ) : '';
			$page_entries = get_user_meta( get_current_user_id(), 'entries_table_per_page_' . $id, true );
			$order        = isset( $_GET['order'] ) ? sanitize_text_field( wp_unslash( $_GET['order'] ) ) : 'desc';

			if ( '' !== $from_date && '' !== $to_date ) {
				$query = $db->prepare(
					'SELECT form_value FROM %i 
					WHERE form_id = ' . $id . ' 
					AND date BETWEEN \'' . $from_date . ' 00:00:00\' 
					AND \'' . $to_date . ' 23:59:59\' 
					ORDER BY date ' . $order,
					$table
				);

			} else {
				$query = $db->prepare(
					'SELECT form_value FROM %i 
					WHERE form_id = ' . $id . ' 
					ORDER BY date ' . $order . ' 
					LIMIT ' . $page_entries . '',
					$table
				);
			}

			return $db->get_results( $query, ARRAY_A );
		};
	}

	/**
	 * Get total form entries.
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback
	 */
	private static function get_count_form_entry( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {
			if ( ! $id ) {
				return 0;
			}

			return $db->get_var(
				$db->prepare(
					'SELECT COUNT(*) from %i WHERE form_id =' . $id,
					$table
				)
			);
		};
	}

	/**
	 * Get entry status from database.
	 *
	 * Filter key all:1, starred:2, unread:3, trash:0
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return object
	 */
	private static function get_entry_filter( object $db, string $table ) {
		$db_vars = array();
		$form_id = isset( $_GET['formId'] ) ? absint( $_GET['formId'] ) : null;
		$query   = 'SELECT COUNT(*) FROM %i WHERE form_id=' . $form_id . '';

		// entries status.
		if ( null !== $form_id ) {

			// for 'all' entry data.
			$db_vars['all'] = $db->get_var(
				$db->prepare( $query . ' AND filter != 0', $table )
			);

			// for 'starred' entry data.
			$db_vars['starred'] = $db->get_var(
				$db->prepare( $query . ' AND save = 1 AND filter != 0', $table )
			);

			// for 'unread' - entry data from status.
			$db_vars['unread'] = $db->get_var(
				$db->prepare( $query . ' AND status = 0 AND filter != 0', $table )
			);

			// for 'all' entry data.
			$db_vars['trashed'] = $db->get_var(
				$db->prepare( $query . ' AND filter = 0', $table )
			);
		}

		return (object) array_filter(
			$db_vars,
			fn( $val )=> ! is_null( $val ) && '' !== $val
		);
	}

	/**
	 * Get total form entries.
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return mixed
	 */
	private static function unreaded_entry( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {

			if ( ! isset( $id ) && null === $id ) {
				return;
			}

			// check if need update.
			$is_read = (int) $db->get_var(
				$db->prepare(
					'SELECT status FROM %i WHERE id=' . $id,
					$table
				)
			);

			if ( $is_read ) {
				return;
			}

			return $db->update(
				$table,
				array( 'status' => 1 ),
				array( 'id' => $id ),
				array( '%d' ),
				array( '%d' )
			);
		};
	}

	/**
	 * Entry move to trash
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback
	 */
	private static function unreaded_entry_bulk( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {
			return $db->update(
				$table,
				array( 'status' => 0 ),
				array( 'id' => $id ),
				array( '%d' ),
				array( '%d' )
			);
		};
	}

	/**
	 * Save Entry (Ajax)
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback
	 */
	private static function save_entry_ajax( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {

			// toggle update.
			$is_saved = (int) $db->get_var(
				$db->prepare(
					'SELECT save FROM %i WHERE id=' . $id,
					$table
				)
			);

			return $db->update(
				$table,
				array( 'save' => ! $is_saved ),
				array( 'id' => $id ),
				array( '%d' ),
				array( '%d' )
			);
		};
	}

	/**
	 * Save Entry
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback
	 */
	private static function save_entry( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {
			return $db->update(
				$table,
				array( 'save' => 1 ),
				array( 'id' => $id ),
				array( '%d' ),
				array( '%d' )
			);
		};
	}

	/**
	 * Entry move to trash
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback
	 */
	private static function move_trash( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {
			return $db->update(
				$table,
				array( 'filter' => 0 ),
				array( 'id' => $id ),
				array( '%d' ),
				array( '%d' )
			);
		};
	}

	/**
	 * Entry delete permanently
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback
	 */
	private static function delete_permanent( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {
			return $db->delete(
				$table,
				array( 'id' => $id ),
				array( '%d' ),
			);
		};
	}

	/**
	 * Entry restore
	 *
	 * @since  1.0.0
	 *
	 * @param object $db Database object.
	 * @param string $table Table name.
	 * @return callback
	 */
	private static function restore_entry( object $db, string $table ) {
		return function ( $id ) use ( $db, $table ) {
			return $db->update(
				$table,
				array( 'filter' => 1 ),
				array( 'id' => $id ),
				array( '%d' ),
				array( '%d' )
			);
		};
	}
}