<?php
/**
 * Query Optimizer
 *
 * Optimizes database queries.
 *
 * @package DonorCore\Performance
 */

namespace DonorCore\Performance;

defined( 'ABSPATH' ) || exit;

/**
 * Query Optimizer Class
 */
class Query_Optimizer {

	/**
	 * Initialize hooks.
	 */
	public function init() {
		add_filter( 'posts_clauses', array( $this, 'optimize_campaign_query' ), 10, 2 );
	}

	/**
	 * Optimize campaign queries.
	 *
	 * @param array     $clauses Query clauses.
	 * @param \WP_Query $query   Query object.
	 * @return array
	 */
	public function optimize_campaign_query( $clauses, $query ) {
		// Only optimize donorcore_campaign queries.
		if ( ! isset( $query->query['post_type'] ) || 'donorcore_campaign' !== $query->query['post_type'] ) {
			return $clauses;
		}

		// Add DISTINCT to prevent duplicates.
		if ( ! isset( $clauses['distinct'] ) || '' === $clauses['distinct'] ) {
			$clauses['distinct'] = 'DISTINCT';
		}

		return $clauses;
	}

	/**
	 * Add database indexes (should be run on activation).
	 */
	public static function add_indexes() {
		global $wpdb;

		// Add index on donation logs for faster campaign totals (only if table exists).
		$logs_table = $wpdb->prefix . 'donorcore_donation_logs';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Index check and creation on custom table
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $logs_table ) ) === $logs_table ) {
			// phpcs:ignore PluginCheck.Security.DirectDB.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query(
				// phpcs:ignore PluginCheck.Security.DirectDB.SchemaChange, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$wpdb->prepare(
					// phpcs:ignore PluginCheck.Security.DirectDB.SchemaChange, WordPress.DB.DirectDatabaseQuery.SchemaChange
					'CREATE INDEX IF NOT EXISTS idx_campaign_amount ON %i (campaign_id, amount)',
					$logs_table
				)
			);
		}

		// Add index on security log only if that table exists.
		$security_table = $wpdb->prefix . 'donorcore_security_log';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Index check and creation on custom table
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $security_table ) ) === $security_table ) {
			// phpcs:ignore PluginCheck.Security.DirectDB.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query(
				// phpcs:ignore PluginCheck.Security.DirectDB.SchemaChange, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$wpdb->prepare(
					// phpcs:ignore PluginCheck.Security.DirectDB.SchemaChange, WordPress.DB.DirectDatabaseQuery.SchemaChange
					'CREATE INDEX IF NOT EXISTS idx_created_at ON %i (created_at)',
					$security_table
				)
			);
		}
	}

	/**
	 * Cleanup old security logs (run via cron).
	 */
	public static function cleanup_old_logs() {
		global $wpdb;

		// Delete logs older than 90 days.
		// Using hardcoded table suffix with $wpdb->prefix for security
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Bulk cleanup
		$wpdb->query(
			"DELETE FROM `{$wpdb->prefix}donorcore_security_log` 
			WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)"
		);
	}
}
