<?php
/**
 * SureForms Database Entires Table Class.
 *
 * @link       https://sureforms.com
 * @since      0.0.10
 * @package    SureForms
 * @author     SureForms <https://sureforms.com/>
 */

namespace SRFM\Inc\Database\Tables;

use SRFM\Inc\Database\Base;
use SRFM\Inc\Helper;
use SRFM\Inc\Traits\Get_Instance;

// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

/**
 * SureForms Database Entires Table Class.
 *
 * @since 0.0.10
 */
class Entries extends Base {
	use Get_Instance;

	/**
	 * {@inheritDoc}
	 *
	 * @var string
	 */
	protected $table_suffix = 'entries';

	/**
	 * {@inheritDoc}
	 *
	 * @var int
	 */
	protected $table_version = 2;

	/**
	 * Current logs.
	 *
	 * @var array<array<string,mixed>> $logs
	 * The structure of each log entry is:
	 * [
	 *     'title'    => string,
	 *     'messages' => array<string>,
	 *     'timestamp' => int
	 * ]
	 *
	 * @since 0.0.10
	 */
	private $logs = [];

	/**
	 * {@inheritDoc}
	 */
	public function get_schema() {
		return [
			// Entry ID.
			'ID'              => [
				'type' => 'number',
			],
			// Submitted form ID.
			'form_id'         => [
				'type' => 'number',
			],
			// User ID.
			'user_id'         => [
				'type'    => 'number',
				'default' => 0,
			],
			// Current entry status: 'read', 'unread' and 'trash'.
			'status'          => [
				'type'    => 'string',
				'default' => 'unread',
			],
			// Entry's form type eg quiz, standard etc. Default empty or null means standard.
			'type'            => [
				'type' => 'string',
			],
			// Submitted form data by user.
			'form_data'       => [
				'type'    => 'array',
				'default' => [],
			],
			// Additional information about the current submitted data: i.e: Browser type, device etc.
			'submission_info' => [
				'type'    => 'array',
				'default' => [],
			],
			// Any additional notes that is added by the admin.
			'notes'           => [
				'type'    => 'array',
				'default' => [],
			],
			// Entry activities logs.
			'logs'            => [
				'type'    => 'array',
				'default' => [],
			],
			// Entry submitted date and time.
			'created_at'      => [
				'type' => 'datetime',
			],
			// Any misc extra data that needs to be saved.
			'extras'          => [
				'type'    => 'array',
				'default' => [],
			],
			// Submission language code (e.g. 'en', 'de'). Empty when no multilingual provider is active.
			'language'        => [
				'type'    => 'string',
				'default' => '',
			],
		];
	}

	/**
	 * {@inheritDoc}
	 */
	public function get_columns_definition() {
		return [
			'ID BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
			'form_id BIGINT(20) UNSIGNED',
			'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
			'form_data LONGTEXT', // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
			'logs LONGTEXT',
			'notes LONGTEXT',
			'submission_info LONGTEXT',
			'status VARCHAR(10)',
			'type VARCHAR(20)', // Note: @since 0.0.13 -- We have added type column, it will have entry's form type eg quiz, standard etc.
			'extras LONGTEXT',
			'language VARCHAR(20)', // Note: @since 2.11.0 -- Submission language code, captured from the active multilingual provider. Nullable to match status/type column convention; INSERT path always supplies a value or empty string. Width matches the `type` column precedent and covers extended BCP-47 codes (e.g. `ca-valencia`).
			'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
			'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			'INDEX idx_form_id (form_id)', // Indexing for the performance improvements.
			'INDEX idx_user_id (user_id)',
			'INDEX idx_form_id_created_at_status (form_id, created_at, status)', // Composite index for performance improvements.
			'INDEX idx_form_id_language (form_id, language)', // Composite index for per-language entry filtering.
		];
	}

	/**
	 * {@inheritDoc}
	 */
	public function get_new_columns_definition() {
		return [
			// Note: @since 0.0.13 -- We have added new columns `type`, `extras` and `user_id`.
			'type VARCHAR(20) AFTER status',
			'extras LONGTEXT AFTER status',
			'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER form_id',
			'INDEX idx_user_id (user_id)',
			// Note: @since 2.11.0 -- Added language column for multilingual submission tracking.
			// No `AFTER` clause: on a pre-0.0.13 install upgrading straight to this version,
			// `extras` is added in the SAME combined ALTER, and MySQL resolves `AFTER extras`
			// against the pre-ALTER schema, throwing "Unknown column 'extras'" and failing the
			// whole atomic ALTER. Column ordinal position is cosmetic and addressed by name everywhere.
			'language VARCHAR(20)',
			'INDEX idx_form_id_language (form_id, language)',
		];
	}

	/**
	 * {@inheritDoc}
	 */
	public function get_columns_to_rename() {
		return [
			// Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
			[
				'from' => 'user_data',
				'to'   => 'form_data',
			],
		];
	}

	/**
	 * Retrieve the key of the last log entry.
	 *
	 * @since 0.0.10
	 * @return int|null The key of the last log entry if logs exist, or null if no logs are present.
	 */
	public function get_last_log_key() {
		$key = array_key_last( $this->logs );
		return is_int( $key ) ? $key : null;
	}

	/**
	 * Add a new log entry.
	 *
	 * @param string        $title The title of the log entry.
	 * @param array<string> $messages Optional. An array of messages to include in the log entry. Default is an empty array.
	 * @since 0.0.10
	 * @return int|null The key of the newly added log entry, or null if the log could not be added.
	 */
	public function add_log( $title, $messages = [] ) {
		$log = [
			'title'     => Helper::get_string_value( trim( $title ) ),
			'messages'  => Helper::get_array_value( $messages ),
			'timestamp' => current_time( 'mysql' ), //phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp -- Using current_time() to match the WordPress timezone.
		];

		$this->logs = array_merge( [ $log ], $this->logs );

		return $this->get_last_log_key();
	}

	/**
	 * Update an existing log entry.
	 *
	 * @param int|null      $log_key The key of the log entry to update.
	 * @param string|null   $title Optional. The new title for the log entry. If null, the title will not be changed.
	 * @param array<string> $messages Optional. An array of new messages to add to the log entry.
	 * @since 0.0.10
	 * @return int|null The key of the updated log entry, or null if the log entry does not exist.
	 */
	public function update_log( $log_key, $title = null, $messages = [] ) {
		if ( empty( $this->logs[ $log_key ] ) ) {
			return null;
		}

		$logs = $this->logs;

		$logs[ $log_key ]['title']    = ! is_null( $title ) ? Helper::get_string_value( trim( $title ) ) : $logs[ $log_key ]['title'];
		$logs[ $log_key ]['messages'] = array_merge( Helper::get_array_value( $logs[ $log_key ]['messages'] ), Helper::get_array_value( $messages ) );

		$this->logs = $logs;
		return $log_key;
	}

	/**
	 * Resets logs to zero.
	 *
	 * @since 1.3.0
	 * @return void
	 */
	public function reset_logs() {
		$this->logs = [];
	}

	/**
	 * Retrieve all log entries.
	 *
	 * @since 0.0.10
	 * @return array<array<string,mixed>>
	 */
	public function get_logs() {
		return $this->logs;
	}

	/**
	 * Add a new entry to the database.
	 *
	 * @param array<mixed> $data An associative array of data for the new entry. Must include 'form_id'.
	 *                    If 'ID' is set, it will be removed before inserting.
	 * @since 0.0.10
	 * @return int|false The number of rows inserted, or false if the insertion fails.
	 */
	public static function add( $data ) {
		if ( empty( $data['form_id'] ) ) {
			return false;
		}

		if ( isset( $data['ID'] ) ) {
			// Unset ID if exists because we are creating a new entry, not updating.
			unset( $data['ID'] );
		}

		$instance = self::get_instance();

		if ( ! isset( $data['logs'] ) ) {
			// Add default logs if no logs provided.
			$data['logs'] = $instance->get_logs();
		}

		return $instance->use_insert( $data );
	}

	/**
	 * Update an entry by entry id.
	 *
	 * @param int                 $entry_id Entry ID.
	 * @param array<string,mixed> $data     Data to update.
	 * @since 0.0.13
	 * @return int|false The number of rows updated, or false on error.
	 */
	public static function update( $entry_id, $data = [] ) {
		if ( empty( $entry_id ) ) {
			return false;
		}

		if ( isset( $data['logs'] ) ) {
			// Add logs from the current cache at the very last moment so that we don't tax the performance.
			$data['logs'] = array_merge( Helper::get_array_value( $data['logs'] ), Helper::get_array_value( self::get( $entry_id )['logs'] ) );
		}

		return self::get_instance()->use_update( $data, [ 'ID' => absint( $entry_id ) ] );
	}

	/**
	 * Delete an entry by entry id.
	 *
	 * @param int $entry_id Entry ID to delete.
	 * @since 0.0.13
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public static function delete( $entry_id ) {
		// Add action before deleting the entry.
		do_action( 'srfm_before_delete_entry', $entry_id );

		// Delete the entry.
		return self::get_instance()->use_delete( [ 'ID' => absint( $entry_id ) ], [ '%d' ] );
	}

	/**
	 * Retrieve a specific entry from the database.
	 *
	 * @param int $entry_id The ID of the entry to retrieve.
	 * @since 0.0.10
	 * @return array<mixed> An associative array representing the entry, or an empty array if no entry is found.
	 */
	public static function get( $entry_id ) {
		$results = self::get_instance()->get_results(
			[
				'ID' => $entry_id,
			]
		);

		return isset( $results[0] ) ? Helper::get_array_value( $results[0] ) : [];
	}

	/**
	 * Retrieves a list of records based on the provided arguments.
	 *
	 * This method fetches results from the database, allowing for various
	 * customization options such as filtering, pagination, and sorting.
	 *
	 * @param array<string,mixed> $args {
	 *     Optional. An array of arguments to customize the query.
	 *
	 *     @type array  $where   An associative array of conditions to filter the results.
	 *     @type int    $limit   The maximum number of results to return. Default is 10.
	 *     @type int    $offset  The number of records to skip before starting to collect results. Default is 0.
	 *     @type string $orderby  The column by which to order the results. Default is 'created_at'.
	 *     @type string $order    The direction of the order (ASC or DESC). Default is 'DESC'.
	 * }
	 * @param bool                $set_limit Whether to set the limit on the query. Default is true.
	 *
	 * @since 0.0.13
	 * @return array<mixed> The results of the query, typically an array of objects or associative arrays.
	 */
	public static function get_all( $args = [], $set_limit = true ) {
		/**
		 * Refactored the get_all method to use the get_records_by_args method from the Base class.
		 * Moved the common logic inside the Base class so it can be reused by other tables as well.
		 *
		 * @since 1.13.0
		 */
		return self::get_instance()->get_records_by_args( $args, $set_limit );
	}

	/**
	 * Get the total count of entries by status.
	 *
	 * @param string       $status The status of the entries to count.
	 * @param int|null     $form_id The ID of the form to count entries for.
	 * @param array<mixed> $where_clause Additional where clause to add to the query.
	 * @since 0.0.13
	 * @return int The total number of entries with the specified status.
	 */
	public static function get_total_entries_by_status( $status = 'all', $form_id = 0, $where_clause = [] ) {
		switch ( $status ) {
			case 'all':
				$where_clause[] =
					[
						[
							'key'     => 'status',
							'compare' => '!=',
							'value'   => 'trash',
						],
					];
				if ( 0 < $form_id ) {
					$where_clause[] = [
						[
							'key'     => 'form_id',
							'compare' => '=',
							'value'   => $form_id,
						],
					];
				}
				return self::get_instance()->get_total_count( $where_clause );
			case 'unread':
			case 'trash':
				$where_clause[] = [
					[
						'key'     => 'status',
						'compare' => '=',
						'value'   => $status,
					],
				];
				return self::get_instance()->get_total_count( $where_clause );
			default:
				return self::get_instance()->get_total_count();
		}
	}

	/**
	 * Get the total number of entries created after the given timestamp.
	 *
	 * @param int $timestamp Timestamp in seconds.
	 * @param int $form_id   Optional. The ID of the form to count entries for. Default 0 for all forms.
	 * @since 1.7.3
	 * @return int Total number of entries created after the timestamp.
	 */
	public static function get_entries_count_after( $timestamp, $form_id = 0 ) {
		$timestamp = absint( $timestamp );

		if ( ! $timestamp ) {
			return self::get_total_entries_by_status( 'all', $form_id );
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct DB access is required here to get the most accurate server time for menu badge logic. Caching is not suitable as this is used for real-time admin notifications.
		$mysql_time = $wpdb->get_var( 'SELECT NOW()' );

		// Convert to timestamps.
		$mysql_timestamp = strtotime( $mysql_time );
		$php_timestamp   = time();

		// Offset between MySQL and PHP.
		$offset_seconds = $mysql_timestamp - $php_timestamp;

		$adjusted_timestamp = $timestamp + $offset_seconds;

		$where_clause = [
			[
				[
					'key'     => 'created_at',
					'compare' => '>',
					'value'   => gmdate( 'Y-m-d H:i:s', $adjusted_timestamp ),
				],
			],
		];

		return self::get_total_entries_by_status( 'all', $form_id, $where_clause );
	}

	/**
	 * Get the available months for entries.
	 *
	 * @param array<string,mixed> $where_clause Additional where clause to add to the query.
	 * @since 0.0.13
	 * @return array<int|string, mixed>
	 */
	public static function get_available_months( $where_clause = [] ) {
		$results = self::get_instance()->get_results(
			$where_clause,
			'DISTINCT DATE_FORMAT(created_at, "%Y%m") as month_value, DATE_FORMAT(created_at, "%M %Y") as month_label',
			[
				'ORDER BY month_value ASC',
			],
			false
		);

		$months = [];
		foreach ( $results as $result ) {
			if ( is_array( $result ) && isset( $result['month_value'], $result['month_label'] ) ) {
				$months[ $result['month_value'] ] = $result['month_label'];
			}
		}
		return $months;
	}

	/**
	 * Get all the entry ID's for a form.
	 * The data is used for checking unique field validation.
	 *
	 * @param int $form_id The ID of the form to fetch entry IDs for.
	 * @since 1.0.0
	 * @return array<mixed> An array of entry IDs.
	 */
	public static function get_all_entry_ids_for_form( $form_id ) {
		return self::get_instance()->get_results(
			[ 'form_id' => $form_id ],
			'ID',
			[
				'ORDER BY ID DESC',
			]
		);
	}

	/**
	 * Check if any non-trashed entry for a given form contains a specific field value.
	 *
	 * Uses a single SQL query with JSON_EXTRACT on the form_data column
	 * instead of loading all entries into PHP. Stops at the first match.
	 *
	 * @param int    $form_id     The form ID to search within.
	 * @param string $field_key   The form_data JSON key to match against.
	 * @param string $field_value The value to check for uniqueness.
	 * @since 2.7.0
	 * @return bool True if a duplicate exists, false otherwise.
	 */
	public static function has_duplicate_field_value( $form_id, $field_key, $field_value ) {
		if ( empty( $form_id ) || empty( $field_key ) || '' === $field_value ) {
			return false;
		}

		global $wpdb;
		$table_name = self::get_instance()->get_tablename();
		$json_path  = '$."' . $field_key . '"';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-off existence check; caching not beneficial for uniqueness validation.
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT 1 FROM {$table_name} WHERE form_id = %d AND status != 'trash' AND JSON_UNQUOTE(JSON_EXTRACT(form_data, %s)) = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally generated.
				$form_id,
				$json_path,
				$field_value
			)
		);

		return null !== $exists;
	}

	/**
	 * Get form IDs associated with a list of entry IDs.
	 * This method retrieves the distinct form IDs that are linked to the provided entry IDs.
	 *
	 * @param array<int> $entry_ids An array of entry IDs to fetch associated form IDs for.
	 * @since 1.1.1
	 * @return array<int> An array of form IDs.
	 */
	public static function get_form_ids_by_entries( $entry_ids ) {
		if ( empty( $entry_ids ) && ! is_array( $entry_ids ) ) {
			return [];
		}

		$results = self::get_instance()->get_results(
			[
				[
					[
						'key'     => 'ID',
						'compare' => 'IN',
						'value'   => $entry_ids,
					],
				],
			],
			'DISTINCT form_id'
		);

		return array_map( 'absint', array_column( $results, 'form_id' ) );  // Flatten the array.
	}

	/**
	 * Get the form data for a specific entry.
	 *
	 * @param int $entry_id The ID of the entry to get the form data for.
	 * @since 1.0.0
	 * @return array<string,mixed> An associative array representing the entry's form data.
	 */
	public static function get_form_data( $entry_id ) {
		$result = self::get_instance()->get_results(
			[ 'ID' => $entry_id ],
			'form_data'
		);
		return isset( $result[0] ) && is_array( $result[0] ) ? Helper::get_array_value( $result[0]['form_data'] ) : [];
	}

	/**
	 * Get the entry data for a specific entry.
	 *
	 * @param int $entry_id The ID of the entry to get the entry data for.
	 * @since 1.8.0
	 * @return array<string,mixed> An associative array representing the entry's data.
	 */
	public static function get_entry_data( $entry_id ) {
		$result = self::get_instance()->get_results(
			[ 'ID' => $entry_id ],
			'form_data, extras'
		);
		return isset( $result[0] ) && is_array( $result[0] ) ? $result[0] : [];
	}

	/**
	 * {@inheritDoc}
	 *
	 * Restricts orderable columns to indexed, semantically meaningful fields.
	 * Excludes LONGTEXT blob columns (form_data, submission_info, notes, logs, extras)
	 * to prevent full-table sorts on un-indexed columns.
	 *
	 * @since 2.6.0
	 * @return array<string>
	 */
	protected function get_allowed_orderby_columns() {
		return [ 'ID', 'id', 'form_id', 'user_id', 'status', 'type', 'language', 'created_at', 'updated_at' ];
	}
}
