<?php
/**
 * File Path: includes/data/class-mbs-activity-log-dao.php
 *
 * Activity Log DAO
 *
 * Dashboard Phase 2 (Phase 1): records book edit events for the Publisher-tier
 * activity feed. Keeps a capped ring-buffer to avoid unbounded growth.
 *
 * INSIGHTS-2 (Phase 3): adds meta column support, filtered query, CSV export,
 * and filterable MAX_ROWS constant.
 *
 * @package MyBookShowroom
 * @since   2.3.0
 */

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

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_Activity_Log_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/** Maximum rows retained in the log (filterable via mbs_activity_log_max_rows). */
	const MAX_ROWS = 500;

	/**
	 * Record an activity log event.
	 *
	 * Inserts a row then prunes old entries so the table never exceeds MAX_ROWS.
	 * Silently no-ops if the table does not yet exist (pre-migration installs).
	 *
	 * @param int    $book_id Book post ID (0 for non-book-specific events: import, export).
	 * @param int    $user_id WP user ID performing the action.
	 * @param string $action  Event type: publish | draft | edit | import | export | arc_send.
	 * @param array  $meta    Optional event metadata to JSON-encode (filename, row_count, etc.).
	 * @return bool True on success.
	 * @since 2.3.0
	 */
	/**
	 * Return all distinct user IDs that appear in the activity log.
	 *
	 * Used by the activity log admin page to populate the user filter dropdown.
	 *
	 * @since  1.2.0
	 * @return int[] Array of user IDs, ordered ascending.
	 */
	public static function get_distinct_user_ids(): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_activity_log';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$ids = $wpdb->get_col( "SELECT DISTINCT user_id FROM `{$table}` ORDER BY user_id ASC" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		return array_map( 'intval', $ids ?: array() );
	}

	public static function log_edit( int $book_id, int $user_id, string $action = 'edit', array $meta = array() ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_activity_log';
		$row   = array(
			'book_id'    => $book_id,
			'user_id'    => $user_id,
			'action'     => sanitize_key( $action ),
			'created_at' => current_time( 'mysql', true ),
		);
		$fmt = array( '%d', '%d', '%s', '%s' );

		// Add meta column if it has data and the column exists (post-migration-4.4 installs).
		if ( ! empty( $meta ) ) {
			$row['meta'] = wp_json_encode( $meta );
			$fmt[]       = '%s';
		}

		$result = $wpdb->insert( $table, $row, $fmt ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $result ) {
			self::prune();
		}
		return (bool) $result;
	}

	/**
	 * Retrieve the most recent activity log entries.
	 *
	 * @param int $limit Number of rows to return (default 20).
	 * @return array Array of objects with book_id, user_id, action, meta, created_at.
	 * @since 2.3.0
	 */
	public static function get_recent( int $limit = 20 ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_activity_log';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$rows  = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT * FROM `{$table}` ORDER BY created_at DESC, id DESC LIMIT %d",
				$limit
			)
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Retrieve filtered, paginated log entries.
	 *
	 * @param array $args {
	 *     Optional filter arguments.
	 *     @type int|null    $user_id   Filter by WP user ID.
	 *     @type string|null $action    Filter by action string.
	 *     @type string|null $date_from ISO date string lower bound (inclusive).
	 *     @type string|null $date_to   ISO date string upper bound (inclusive).
	 *     @type int         $per_page  Rows per page (default 50).
	 *     @type int         $page      1-based page number (default 1).
	 * }
	 * @return array{rows: array, total: int}
	 * @since 4.4.0
	 */
	public static function get_filtered( array $args = array() ): array {
		global $wpdb;
		$table    = $wpdb->prefix . 'mbs_activity_log';
		$per_page = max( 1, (int) ( $args['per_page'] ?? 50 ) );
		$page     = max( 1, (int) ( $args['page'] ?? 1 ) );
		$offset   = ( $page - 1 ) * $per_page;

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $args['user_id'] ) ) {
			$where[]  = 'user_id = %d';
			$params[] = (int) $args['user_id'];
		}
		if ( ! empty( $args['action'] ) ) {
			$where[]  = 'action = %s';
			$params[] = sanitize_key( $args['action'] );
		}
		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'created_at >= %s';
			$params[] = sanitize_text_field( $args['date_from'] ) . ' 00:00:00';
		}
		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'created_at <= %s';
			$params[] = sanitize_text_field( $args['date_to'] ) . ' 23:59:59';
		}

		$where_sql = implode( ' AND ', $where );

		// Total count (no pagination).
		if ( ! empty( $params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery
			$total = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"SELECT COUNT(*) FROM `{$table}` WHERE {$where_sql}", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
					...$params
				)
			);
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params / ...$row_ids correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
					"SELECT * FROM `{$table}` WHERE {$where_sql} ORDER BY created_at DESC LIMIT %d OFFSET %d",
					...array_merge( $params, array( $per_page, $offset ) )
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$rows  = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"SELECT * FROM `{$table}` ORDER BY created_at DESC LIMIT %d OFFSET %d",
					$per_page,
					$offset
				)
			);
		}

		return array(
			'rows'  => is_array( $rows ) ? $rows : array(),
			'total' => $total,
		);
	}

	/**
	 * Export all log entries matching $args as CSV rows.
	 *
	 * Returns an array of associative arrays suitable for fputcsv().
	 * No pagination limit applied -- all matching rows are returned.
	 *
	 * @param array $args Same filter keys as get_filtered() (user_id, action, date_from, date_to).
	 *                    per_page and page are ignored.
	 * @return array<int, array{ID, Book, User, Action, Meta, Date}>
	 * @since 4.4.0
	 */
	public static function export_csv( array $args = array() ): array {
		// Force unlimited results by passing a very high per_page.
		$args['per_page'] = 100000;
		$args['page']     = 1;
		$result           = self::get_filtered( $args );
		$rows_out         = array();

		foreach ( $result['rows'] as $entry ) {
			$book_title  = (int) $entry->book_id > 0 ? get_the_title( (int) $entry->book_id ) : __( 'System', 'my-book-showroom' );
			$user_data   = get_userdata( (int) $entry->user_id );
			$user_name   = $user_data ? $user_data->display_name : __( 'Unknown', 'my-book-showroom' );
			$meta_string = '';
			if ( ! empty( $entry->meta ) ) {
				$meta_arr = json_decode( $entry->meta, true );
				if ( is_array( $meta_arr ) ) {
					$parts = array();
					foreach ( $meta_arr as $k => $v ) {
						$parts[] = $k . ': ' . $v;
					}
					$meta_string = implode( ', ', $parts );
				}
			}
			// Formula injection defense: prefix cells that start with = + - @ (V6-C-009).
			$rows_out[] = array(
				'ID'                                    => (int) $entry->id,
				__( 'Book',   'my-book-showroom' )      => self::safe_csv_cell( $book_title ),
				__( 'User',   'my-book-showroom' )      => self::safe_csv_cell( $user_name ),
				__( 'Action', 'my-book-showroom' )      => self::safe_csv_cell( $entry->action ),
				__( 'Meta',   'my-book-showroom' )      => self::safe_csv_cell( $meta_string ),
				__( 'Date',   'my-book-showroom' )      => $entry->created_at,
			);
		}
		return $rows_out;
	}

	/**
	 * Prune log to MAX_ROWS by deleting the oldest excess entries.
	 *
	 * Called automatically after every insert.
	 * MAX_ROWS is filterable via mbs_activity_log_max_rows.
	 *
	 * @return void
	 * @since 2.3.0
	 */
	public static function prune(): void {
		global $wpdb;
		$table    = $wpdb->prefix . 'mbs_activity_log';
		$max_rows = (int) apply_filters( 'mbs_activity_log_max_rows', self::MAX_ROWS ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $count <= $max_rows ) {
			return;
		}
		$excess = $count - $max_rows;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"DELETE FROM `{$table}` ORDER BY created_at ASC LIMIT %d",
				$excess
			)
		);
	}
	/**
	 * Neutralise formula injection: prepend a tab to any cell value starting
	 * with = + - @ (common spreadsheet formula triggers). V6-C-009.
	 *
	 * @param string $value Raw cell value.
	 * @return string        Safe cell value.
	 */
	private static function safe_csv_cell( string $value ): string {
		if ( '' !== $value && in_array( $value[0], array( '=', '+', '-', '@' ), true ) ) {
			return "	" . $value;
		}
		return $value;
	}

}
