<?php
defined( 'ABSPATH' ) || exit;
// This file is the storage layer for the plugin's own custom entries table: direct queries are
// intended here, and values are always bound with wpdb->prepare(). No caching applies to a per-site
// table where reads are always recent anyway, so the caching sniff is not used in this file.
// phpcs:disable WordPress.DB.DirectDatabaseQuery

/**
 * Submissions are stored in a custom table. An entry keeps a snapshot of each field's label and value,
 * so editing a form later never rewrites or loses what people already sent. No IP address or browser
 * details are stored.
 */
function pubforms_entries_table() {
	global $wpdb;
	return $wpdb->prefix . 'pubforms_entries';
}

function pubforms_create_entries_table() {
	global $wpdb;
	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	$table   = pubforms_entries_table();
	$charset = $wpdb->get_charset_collate();

	dbDelta( "CREATE TABLE $table (
		id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		form_id bigint(20) unsigned NOT NULL,
		data longtext NOT NULL,
		status varchar(10) NOT NULL DEFAULT 'unread',
		notified tinyint(1) NOT NULL DEFAULT 0,
		partial tinyint(1) NOT NULL DEFAULT 0,
		notes longtext NOT NULL,
		created_at datetime NOT NULL,
		PRIMARY KEY  (id),
		KEY form_created (form_id,created_at)
	) $charset;" );

	update_option( 'pubforms_db_version', PUBFORMS_VERSION );
}

/**
 * @param int   $form_id
 * @param array $items   Each: id, label, type, value.
 * @param bool  $partial Whether this is an abandonment (partial) capture rather than a real submission.
 * @return int Entry id.
 */
function pubforms_insert_entry( $form_id, $items, $partial = false ) {
	global $wpdb;
	$wpdb->insert( pubforms_entries_table(), array(
		'form_id'    => $form_id,
		'data'       => wp_json_encode( array_values( $items ) ),
		'status'     => 'unread',
		'notified'   => 0,
		'partial'    => $partial ? 1 : 0,
		'created_at' => gmdate( 'Y-m-d H:i:s' ),
	) );
	return (int) $wpdb->insert_id;
}

function pubforms_get_entry( $entry_id ) {
	global $wpdb;
	$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', pubforms_entries_table(), $entry_id ) );
	return $row ? pubforms_decode_entry( $row ) : null;
}

function pubforms_decode_entry( $row ) {
	$items = json_decode( $row->data, true );
	$row->items = is_array( $items ) ? $items : array();
	return $row;
}

/**
 * Value stored for a field id in an entry, or '' if it has none.
 */
function pubforms_entry_value( $entry, $field_id ) {
	foreach ( $entry->items as $item ) {
		if ( ( $item['id'] ?? '' ) === $field_id ) {
			return (string) ( $item['value'] ?? '' );
		}
	}
	return '';
}

/**
 * @param array $args status, partial, search, date_from, date_to, orderby (created_at|id|status), order, per_page, paged.
 * @return array items, total.
 */
function pubforms_query_entries( $form_id, $args = array() ) {
	global $wpdb;
	$args  = wp_parse_args( $args, array( 'status' => '', 'partial' => 0, 'search' => '', 'date_from' => '', 'date_to' => '', 'orderby' => 'created_at', 'order' => 'desc', 'per_page' => 20, 'paged' => 1 ) );
	$table = pubforms_entries_table();

	$where  = 'form_id = %d';
	$params = array( $form_id );
	if ( in_array( $args['status'], array( 'read', 'unread' ), true ) ) {
		$where   .= ' AND status = %s';
		$params[] = $args['status'];
	}
	// Partials are excluded unless explicitly asked for; public submissions are incomplete captures.
	$where   .= ' AND partial = %d';
	$params[] = in_array( (int) $args['partial'], array( 0, 1 ), true ) ? (int) $args['partial'] : 0;
	if ( '' !== trim( $args['search'] ) ) {
		$where   .= ' AND data LIKE %s';
		$params[] = '%' . $wpdb->esc_like( trim( $args['search'] ) ) . '%';
	}
	// Date range filters, matched against the UTC created_at.
	if ( '' !== trim( $args['date_from'] ) ) {
		$where   .= ' AND created_at >= %s';
		$params[] = gmdate( 'Y-m-d 00:00:00', strtotime( sanitize_text_field( $args['date_from'] ) ) );
	}
	if ( '' !== trim( $args['date_to'] ) ) {
		$where   .= ' AND created_at <= %s';
		$params[] = gmdate( 'Y-m-d 23:59:59', strtotime( sanitize_text_field( $args['date_to'] ) ) );
	}

	$orderby  = in_array( $args['orderby'], array( 'created_at', 'id', 'status' ), true ) ? $args['orderby'] : 'created_at';
	$order    = 'asc' === strtolower( $args['order'] ) ? 'ASC' : 'DESC';
	$per_page = max( 1, min( 200, (int) $args['per_page'] ) );
	$offset   = max( 0, ( (int) $args['paged'] - 1 ) * $per_page );

	// $where holds fixed fragments with placeholders; $orderby and $order come from the whitelists above.
	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
	$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE $where", array_merge( array( $table ), $params ) ) );
	$rows  = $wpdb->get_results( $wpdb->prepare(
		"SELECT * FROM %i WHERE $where ORDER BY $orderby $order, id $order LIMIT %d OFFSET %d",
		array_merge( array( $table ), $params, array( $per_page, $offset ) )
	) );
	// phpcs:enable
	return array( 'items' => array_map( 'pubforms_decode_entry', (array) $rows ), 'total' => $total );
}

/**
 * @return int[] total, unread, partial.
 */
function pubforms_entry_counts( $form_id ) {
	global $wpdb;
	$rows  = $wpdb->get_results( $wpdb->prepare( 'SELECT status, partial, COUNT(*) AS n FROM %i WHERE form_id = %d GROUP BY status, partial', pubforms_entries_table(), $form_id ) );
	$count = array( 'total' => 0, 'unread' => 0, 'partial' => 0 );
	foreach ( (array) $rows as $row ) {
		if ( (int) $row->partial ) {
			$count['partial'] += (int) $row->n;
			continue;
		}
		$count['total'] += (int) $row->n;
		if ( 'unread' === $row->status ) {
			$count['unread'] += (int) $row->n;
		}
	}
	return $count;
}

function pubforms_set_entry_status( $entry_id, $status ) {
	global $wpdb;
	return in_array( $status, array( 'read', 'unread' ), true ) && false !== $wpdb->update( pubforms_entries_table(), array( 'status' => $status ), array( 'id' => $entry_id ) );
}

function pubforms_delete_entry( $entry_id ) {
	global $wpdb;
	$entry = pubforms_get_entry( $entry_id );
	if ( $entry ) {
		pubforms_remove_entry_files( $entry );
	}
	return (bool) $wpdb->delete( pubforms_entries_table(), array( 'id' => $entry_id ) );
}

/**
 * The note an admin left on an entry (private, not part of the submission).
 */
function pubforms_get_entry_note( $entry_id ) {
	$entry = pubforms_get_entry( $entry_id );
	return $entry ? trim( (string) ( $entry->notes ?? '' ) ) : '';
}

function pubforms_set_entry_note( $entry_id, $note ) {
	global $wpdb;
	$note = sanitize_textarea_field( $note );
	return false !== $wpdb->update( pubforms_entries_table(), array( 'notes' => $note ), array( 'id' => $entry_id ) );
}

/**
 * Count how often each stored value of a field appears across a form's real (non-partial) entries.
 * Used for survey/poll/quiz results.
 *
 * @return array value => count
 */
function pubforms_field_tally( $form, $field_id ) {
	$tally  = array();
	$paged  = 1;
	$size   = 200;
	do {
		$page = pubforms_query_entries( $form['id'], array( 'per_page' => $size, 'paged' => $paged, 'order' => 'asc' ) );
		$got  = count( $page['items'] );
		foreach ( $page['items'] as $entry ) {
			$value = pubforms_entry_value( $entry, $field_id );
			if ( '' === $value ) {
				continue;
			}
			$tally[ $value ] = ( $tally[ $value ] ?? 0 ) + 1;
		}
		$paged++;
	} while ( $size === $got );
	return $tally;
}

/**
 * Delete the physical files an entry's file uploads point at. Only files inside that form's own
 * upload folder are touched, so we never delete anything outside PubForms' storage.
 */
function pubforms_remove_entry_files( $entry ) {
	if ( ! is_object( $entry ) || empty( $entry->form_id ) ) {
		return;
	}
	$dir = pubforms_uploads_dir( $entry->form_id );
	foreach ( $entry->items as $item ) {
		$path = (string) ( $item['path'] ?? '' );
		if ( in_array( $item['type'] ?? '', array( 'file', 'signature' ), true ) && '' !== $path && strpos( $path, $dir['path'] ) === 0 && file_exists( $path ) ) {
			wp_delete_file( $path );
		}
	}
}

/**
 * Every entry that mentions an email address anywhere in its data (used by the privacy tools).
 *
 * @return object[] Decoded entry rows.
 */
function pubforms_entries_by_email( $email ) {
	global $wpdb;
	$email = trim( (string) $email );
	if ( ! is_email( $email ) ) {
		return array();
	}
	$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE data LIKE %s', pubforms_entries_table(), '%' . $wpdb->esc_like( $email ) . '%' ) );
	return array_map( 'pubforms_decode_entry', (array) $rows );
}

/**
 * Delete entries older than a number of days (also removing their files). Returns how many were removed.
 */
function pubforms_delete_entries_older_than( $days ) {
	global $wpdb;
	$days = (int) $days;
	if ( $days <= 0 ) {
		return 0;
	}
	$cutoff = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
	$rows   = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE created_at < %s', pubforms_entries_table(), $cutoff ) );
	foreach ( $rows as $row ) {
		pubforms_remove_entry_files( pubforms_decode_entry( $row ) );
	}
	return (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', pubforms_entries_table(), $cutoff ) );
}

// Deleting a form deletes its entries and their stored files.
add_action( 'before_delete_post', function ( $post_id ) {
	if ( 'pubforms_form' !== get_post_type( $post_id ) ) {
		return;
	}
	global $wpdb;
	$table = pubforms_entries_table();
	$rows  = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE form_id = %d', $table, $post_id ) );
	foreach ( $rows as $row ) {
		pubforms_remove_entry_files( pubforms_decode_entry( $row ) );
	}
	$wpdb->delete( $table, array( 'form_id' => $post_id ) );
	pubforms_remove_uploads_dir( $post_id );
} );

/**
 * Remove a directory and everything in it. Used only on PubForms' own private upload folders.
 */
function pubforms_remove_tree( $path ) {
	if ( ! is_dir( $path ) ) {
		return;
	}
	foreach ( (array) scandir( $path ) as $name ) {
		if ( '.' === $name || '..' === $name ) {
			continue;
		}
		$child = $path . '/' . $name;
		if ( is_dir( $child ) && ! is_link( $child ) ) {
			pubforms_remove_tree( $child );
		} else {
			wp_delete_file( $child );
		}
	}
	rmdir( $path ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_rmdir -- removing PubForms' own emptied upload folder.
}

/**
 * Remove a form's upload folder along with its guard files.
 */
function pubforms_remove_uploads_dir( $form_id ) {
	$dir = pubforms_uploads_dir( $form_id );
	pubforms_remove_tree( $dir['path'] );
}

/**
 * A spreadsheet cell that cannot be run as a formula when the CSV is opened.
 */
function pubforms_csv_safe( $value ) {
	$value = (string) $value;
	return preg_match( '/^[=+\-@\t\r]/', $value ) ? "'" . $value : $value;
}

/**
 * Rows for a CSV export: a header, then one row per entry using the form's current fields.
 *
 * @param array $form Form.
 * @param array $args Optional filters passed through to the query: status, date_from, date_to.
 * @return array[]
 */
function pubforms_entries_csv_rows( $form, $args = array() ) {
	$header = array( __( 'Date (UTC)', 'pubforms' ), __( 'Status', 'pubforms' ) );
	foreach ( $form['fields'] as $field ) {
		$header[] = $field['label'];
	}
	$rows = array( $header );

	$filter = wp_parse_args( $args, array( 'status' => '', 'date_from' => '', 'date_to' => '' ) );
	$paged     = 1;
	$page_size = 200;
	do {
		$page = pubforms_query_entries( $form['id'], array_merge( $filter, array( 'per_page' => $page_size, 'paged' => $paged, 'order' => 'asc' ) ) );
		$got  = count( $page['items'] );
		foreach ( $page['items'] as $entry ) {
			$row = array( $entry->created_at, $entry->status );
			foreach ( $form['fields'] as $field ) {
				$row[] = pubforms_entry_value( $entry, $field['id'] );
			}
			$rows[] = array_map( 'pubforms_csv_safe', $row );
		}
		$paged++;
	} while ( $got === $page_size );
	return $rows;
}