<?php
/**
 * Read-only SQL guidance for recognized WordPress database findings.
 *
 * @package LocalGuardScanner
 */

namespace LocalGuardScanner\Admin;

/**
 * Builds bounded SELECT statements without executing them.
 */
final class DatabaseReviewQueryBuilder {
	/** @param string $table Posts table. @param int $post_id Post ID. @return string */
	public function content( string $table, int $post_id ): string {
		if ( ! $this->safe_identifier( $table ) || $post_id <= 0 ) {
			return '';
		}
		$query = "SELECT ID, post_author, post_type, post_status, post_date_gmt, post_modified_gmt, post_title,\n"
			. "       CHAR_LENGTH(post_content) AS content_length,\n"
			. "       LOCATE('<?php', LOWER(post_content)) AS php_position,\n"
			. "       LOCATE('shell_exec', LOWER(post_content)) AS shell_position,\n"
			. "       LOCATE('file_put_contents', LOWER(post_content)) AS write_position,\n"
			. "       LOCATE('base64_decode', LOWER(post_content)) AS base64_position,\n"
			. "       post_content\n"
			. 'FROM `' . $table . "`\n"
			. 'WHERE ID = ' . $post_id;
		return $this->validate( $query );
	}

	/** @param string $table Options table. @param string $option_name Exact option name. @return string */
	public function option( string $table, string $option_name ): string {
		if ( ! $this->safe_identifier( $table ) || '' === $option_name || strlen( $option_name ) > 191 ) {
			return '';
		}
		$query = "SELECT option_id, option_name, autoload,\n"
			. "       CHAR_LENGTH(option_value) AS value_length,\n"
			. "       LOCATE('<?php', LOWER(option_value)) AS php_position,\n"
			. "       LOCATE('shell_exec', LOWER(option_value)) AS shell_position,\n"
			. "       LOCATE('file_put_contents', LOWER(option_value)) AS write_position,\n"
			. "       LOCATE('base64_decode', LOWER(option_value)) AS base64_position,\n"
			. "       option_value\n"
			. 'FROM `' . $table . "`\n"
			. 'WHERE option_name = 0x' . bin2hex( $option_name );
		return $this->validate( $query );
	}

	/**
	 * @param string $users_table    Shared users table.
	 * @param string $usermeta_table Shared usermeta table.
	 * @param string $site_prefix    Validated site table prefix.
	 * @param int    $user_id        User ID.
	 * @return string
	 */
	public function user( string $users_table, string $usermeta_table, string $site_prefix, int $user_id ): string {
		if ( ! $this->safe_identifier( $users_table ) || ! $this->safe_identifier( $usermeta_table ) || '' === $site_prefix || ! $this->safe_identifier( $site_prefix . 'options' ) || $user_id <= 0 ) {
			return '';
		}
		$capabilities = bin2hex( $site_prefix . 'capabilities' );
		$user_level   = bin2hex( $site_prefix . 'user_level' );
		$query        = "SELECT u.ID, u.user_login, u.user_email, u.user_registered, u.display_name,\n"
			. "       um.meta_key, um.meta_value\n"
			. 'FROM `' . $users_table . '` AS u' . "\n"
			. 'LEFT JOIN `' . $usermeta_table . '` AS um' . "\n"
			. '       ON um.user_id = u.ID' . "\n"
			. '      AND um.meta_key IN (0x' . $capabilities . ', 0x' . $user_level . ")\n"
			. 'WHERE u.ID = ' . $user_id;
		return $this->validate( $query );
	}

	/** @return bool */
	private function safe_identifier( string $identifier ): bool {
		return '' !== $identifier && strlen( $identifier ) <= 191 && 1 === preg_match( '/^[A-Za-z0-9_]+$/', $identifier );
	}

	/** @return string */
	private function validate( string $query ): string {
		if ( 1 !== preg_match( '/^SELECT\b/i', $query ) || preg_match( '/\b(?:DELETE|UPDATE|INSERT|REPLACE|DROP|ALTER|TRUNCATE|CREATE|CALL|DO)\b/i', $query ) || preg_match( '/\bINTO\s+OUTFILE\b/i', $query ) || false !== strpos( $query, ';' ) || false !== strpos( $query, '--' ) || false !== strpos( $query, '/*' ) ) {
			return '';
		}
		return $query;
	}
}
