<?php

declare(strict_types=1);

namespace SecurWP\Modules\Scanner;

use SecurWP\Modules\Cloud\Service\CloudSettings;
use SecurWP\Modules\Scanner\Repository\IssueRepository;

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

/**
 * Database Content Scanner
 *
 * Scans WordPress database tables for malicious content using SQL-condition-based
 * signatures fetched from the SecurWP Cloud. Signatures are pure SQL WHERE clauses
 * executed directly by MySQL for performance.
 */
class ContentScanner {

	private const TIME_LIMIT      = 15;
	private const CACHE_TTL       = 24 * HOUR_IN_SECONDS;
	private const TRANSIENT_KEY   = 'securwp_db_signatures';

	private const TABLE_MAP = [
		'options'  => [ 'prop' => 'options',  'pk' => 'option_id',  'type' => 'option' ],
		'posts'    => [ 'prop' => 'posts',    'pk' => 'ID',         'type' => 'post' ],
		'comments' => [ 'prop' => 'comments', 'pk' => 'comment_ID', 'type' => 'comment' ],
		'postmeta' => [ 'prop' => 'postmeta', 'pk' => 'meta_id',   'type' => 'postmeta' ],
		'usermeta' => [ 'prop' => 'usermeta', 'pk' => 'umeta_id',  'type' => 'usermeta' ],
		'termmeta' => [ 'prop' => 'termmeta', 'pk' => 'meta_id',   'type' => 'termmeta' ],
	];

	private const ALLOWED_COLUMNS = [
		'options'  => [ 'option_name', 'option_value' ],
		'posts'    => [ 'post_content', 'post_title', 'post_excerpt', 'post_author', 'post_name', 'post_type', 'post_status', 'post_date' ],
		'comments' => [ 'comment_content', 'comment_author', 'comment_author_url', 'comment_author_email' ],
		'postmeta' => [ 'meta_key', 'meta_value' ],
		'usermeta' => [ 'meta_key', 'meta_value' ],
		'termmeta' => [ 'meta_key', 'meta_value' ],
	];

	/** @var array|null Cached signatures for this scan run. */
	private $signatures = null;

	public function get_title(): string {
		return 'Database Security';
	}

	public function run(): array {
		if ( ! class_exists( 'SecurWP\\Modules\\Scanner\\Repository\\IssueRepository' ) ) {
			require_once __DIR__ . '/Repository/IssueRepository.php';
		}

		return [ 'issues' => $this->scan_database() ];
	}

	/**
	 * Execute all signatures grouped by target table.
	 */
	private function scan_database(): array {
		$signatures = $this->get_signatures();

		if ( is_wp_error( $signatures ) ) {
			securwp_log( 'SecurWP: Database intelligence sync failed: ' . $signatures->get_error_message() );
			return [];
		}
		if ( empty( $signatures ) ) {
			return [];
		}

		$by_table = [];
		foreach ( $signatures as $sig ) {
			$tables = $sig['tables'] ?? [ 'options' ];
			if ( ! is_array( $tables ) ) {
				$tables = [ $tables ];
			}
			foreach ( $tables as $t ) {
				$t = (string) $t;
				if ( isset( self::TABLE_MAP[ $t ] ) ) {
					$by_table[ $t ][] = $sig;
				}
			}
		}

		global $wpdb;
		$issues     = [];
		$start_time = microtime( true );

		foreach ( $by_table as $table_key => $table_sigs ) {
			if ( ( microtime( true ) - $start_time ) > self::TIME_LIMIT ) {
				break;
			}

			$config         = self::TABLE_MAP[ $table_key ];
			$prefixed_table = $wpdb->{$config['prop']};
			$pk             = $config['pk'];

			foreach ( $table_sigs as $sig ) {
				if ( ( microtime( true ) - $start_time ) > self::TIME_LIMIT ) {
					break 2;
				}

				$condition = $sig['condition'] ?? '';
				if ( ! $this->validate_condition( $condition, $table_key ) ) {
					continue;
				}

				$select           = $this->build_select_list( $table_key, $pk );
				$scope            = $this->get_table_scope( $table_key );
				$table_identifier = $this->table_identifier( $prefixed_table );

				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name, column list, and condition fragments are constrained to static allowlists before execution.
				$rows = $wpdb->get_results(
					"SELECT {$select} FROM {$table_identifier} WHERE ({$condition}){$scope}",
					ARRAY_A
				);
				// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter

				if ( empty( $rows ) || ! is_array( $rows ) ) {
					continue;
				}

				foreach ( $rows as $row ) {
					$row_id     = $row[ $pk ] ?? 'unknown';
					$stable_key = "{$table_key}_{$row_id}_{$sig['id']}";
					$label      = $this->build_location_label( $table_key, $row, $row_id );
					$issue_data = $this->build_issue_data( $table_key, $row, $row_id, $sig );

					$issues[] = [
						'id'       => $stable_key,
						'hash'     => IssueRepository::generateHash( 'content', $stable_key ),
						'type'     => 'content',
						'status'   => 'infected',
						'severity' => $sig['severity'] ?? 'high',
						'file'     => $label,
						'message'  => $sig['description'] ?? 'Suspicious database content detected',
						'data'     => $issue_data,
					];
				}
			}
		}

		return $issues;
	}

	/**
	 * Validate a SQL WHERE condition fragment.
	 *
	 * Rejects anything that could be used for SQL injection. Only allows
	 * LIKE comparisons, equality checks, boolean operators, LOWER(), and
	 * string/numeric literals referencing known columns.
	 */
	private function validate_condition( string $condition, string $table_key ): bool {
		if ( empty( $condition ) || strlen( $condition ) > 5000 ) {
			return false;
		}

		// Strip string literals before all checks so quoted content
		// (e.g. '%display:none;%') does not trigger false rejections.
		$stripped = preg_replace( "/'[^']*'/", "''", $condition );

		// Block dangerous SQL keywords (checked on stripped content).
		$forbidden = '/\b(SELECT|INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE|CREATE|UNION|INTO|EXEC|EXECUTE|CALL|GRANT|REVOKE|LOAD|OUTFILE|DUMPFILE|INFORMATION_SCHEMA|SLEEP|BENCHMARK)\b/i';
		if ( preg_match( $forbidden, $stripped ) ) {
			return false;
		}

		// Block semicolons, comments, and stacked queries.
		if ( preg_match( '/[;]|--|\\/\\*|#/', $stripped ) ) {
			return false;
		}

		// Block subqueries and parenthesized SELECT.
		if ( preg_match( '/\(\s*SELECT\b/i', $stripped ) ) {
			return false;
		}

		// Block assignment operators.
		if ( preg_match( '/:=|SET\s/i', $stripped ) ) {
			return false;
		}

		// Verify all column references are from the allowed set for this table.
		$allowed_cols = self::ALLOWED_COLUMNS[ $table_key ] ?? [];
		if ( empty( $allowed_cols ) ) {
			return false;
		}

		// Find bare identifiers (word chars, possibly with backticks).
		preg_match_all( '/`?([a-zA-Z_][a-zA-Z0-9_]*)`?/', $stripped, $matches );
		$identifiers = array_unique( $matches[1] );

		// Known SQL keywords and functions that are not column names.
		$sql_keywords = [
			'like', 'and', 'or', 'not', 'in', 'is', 'null', 'lower', 'upper',
			'between', 'true', 'false',
		];

		$col_refs = 0;
		foreach ( $identifiers as $id ) {
			if ( in_array( strtolower( $id ), $sql_keywords, true ) ) {
				continue;
			}
			if ( ! in_array( $id, $allowed_cols, true ) ) {
				return false;
			}
			$col_refs++;
		}

		// Require at least one column reference to prevent tautology conditions
		// (e.g. 'a' OR 'b'='b') that match every row.
		return $col_refs > 0;
	}

	/**
	 * Additional WHERE scope to exclude internal/non-content rows.
	 */
	private function get_table_scope( string $table_key ): string {
		switch ( $table_key ) {
			// Exclude transients, securwp internals, and cron.
			case 'options':
				return " AND option_name NOT LIKE '_transient_%' AND option_name NOT LIKE '_site_transient_%' AND option_name NOT LIKE 'securwp_%' AND option_name != 'cron'";
			// Only scan real content types, not nav menus, revisions, etc.
			case 'posts':
				return " AND post_type IN ('post', 'page', 'wp_block') AND post_status != 'trash'";
			// Only approved comments.
			case 'comments':
				return " AND comment_approved = '1'";
			default:
				return '';
		}
	}

	/**
	 * Extra SELECT columns for context in issue labels.
	 */
	private function get_context_columns( string $table_key ): array {
		switch ( $table_key ) {
			case 'options':
				return [ 'option_name', 'option_value' ];
			case 'posts':
				return [ 'post_title', 'post_content' ];
			case 'comments':
				return [ 'comment_author', 'comment_content' ];
			case 'postmeta':
				return [ 'meta_key', 'meta_value', 'post_id' ];
			case 'usermeta':
				return [ 'meta_key', 'meta_value', 'user_id' ];
			case 'termmeta':
				return [ 'meta_key', 'meta_value', 'term_id' ];
			default:
				return [];
		}
	}

	/**
	 * Build a SELECT list from allowed column names only.
	 */
	private function build_select_list( string $table_key, string $primary_key ): string {
		$allowed_columns = self::ALLOWED_COLUMNS[ $table_key ] ?? [];
		$columns         = [ $primary_key ];

		foreach ( $this->get_context_columns( $table_key ) as $column ) {
			if ( in_array( $column, $allowed_columns, true ) ) {
				$columns[] = $column;
			}
		}

		$columns = array_values( array_unique( $columns ) );
		return implode( ', ', array_map( [ $this, 'quote_identifier' ], $columns ) );
	}

	/**
	 * Quote an identifier after validating its shape.
	 */
	private function quote_identifier( string $identifier ): string {
		if ( $identifier === '' || strlen( $identifier ) > 64 || ! preg_match( '/^[A-Za-z0-9_]+$/', $identifier ) ) {
			throw new \InvalidArgumentException( 'Invalid SQL identifier.' );
		}

		return '`' . $identifier . '`';
	}

	/**
	 * Quote a validated table name.
	 */
	private function table_identifier( string $table ): string {
		return $this->quote_identifier( $table );
	}

	/**
	 * Primary content column per table (used for match snippet extraction).
	 */
	private function get_content_column( string $table_key ): string {
		switch ( $table_key ) {
			case 'options':
				return 'option_value';
			case 'posts':
				return 'post_content';
			case 'comments':
				return 'comment_content';
			case 'postmeta':
			case 'usermeta':
			case 'termmeta':
				return 'meta_value';
			default:
				return '';
		}
	}

	/**
	 * Extract search terms from a SQL condition and find first matching snippet.
	 *
	 * Returns a ~200 char window around the first matched term, or empty string.
	 */
	private function extract_match_snippet( string $content, string $condition ): string {
		if ( empty( $content ) || empty( $condition ) ) {
			return '';
		}

		// Pull LIKE '%term%' values from the condition.
		if ( ! preg_match_all( "/LIKE\s+'%([^%']+)%'/i", $condition, $matches ) ) {
			return '';
		}

		$lower = strtolower( $content );
		foreach ( $matches[1] as $term ) {
			$pos = strpos( $lower, strtolower( $term ) );
			if ( $pos !== false ) {
				$start  = max( 0, $pos - 80 );
				$snippet = substr( $content, $start, 200 );
				if ( $start > 0 ) {
					$snippet = '...' . $snippet;
				}
				if ( $start + 200 < strlen( $content ) ) {
					$snippet .= '...';
				}
				return $snippet;
			}
		}

		return '';
	}

	/**
	 * Build a human-readable location label for the issue.
	 */
	private function build_location_label( string $table_key, array $row, $row_id ): string {
		switch ( $table_key ) {
			case 'options':
				return 'Option: ' . ( $row['option_name'] ?? $row_id );
			case 'posts':
				return 'Post: ' . ( $row['post_title'] ?? '' ) . ' (ID: ' . $row_id . ')';
			case 'comments':
				return 'Comment by ' . ( $row['comment_author'] ?? 'unknown' ) . ' (ID: ' . $row_id . ')';
			case 'postmeta':
				return 'Post meta: ' . ( $row['meta_key'] ?? '' ) . ' (post ' . ( $row['post_id'] ?? '?' ) . ')';
			case 'usermeta':
				return 'User meta: ' . ( $row['meta_key'] ?? '' ) . ' (user ' . ( $row['user_id'] ?? '?' ) . ')';
			case 'termmeta':
				return 'Term meta: ' . ( $row['meta_key'] ?? '' ) . ' (term ' . ( $row['term_id'] ?? '?' ) . ')';
			default:
				return "DB: {$table_key} (ID: {$row_id})";
		}
	}

	/**
	 * Build enriched data payload for an issue.
	 */
	private function build_issue_data( string $table_key, array $row, $row_id, array $sig ): array {
		$content_col = $this->get_content_column( $table_key );
		$content_raw = $content_col ? ( $row[ $content_col ] ?? '' ) : '';
		$snippet     = $this->extract_match_snippet( $content_raw, $sig['condition'] ?? '' );

		$data = [
			'table'          => $table_key,
			'row_id'         => $row_id,
			'signature_id'   => $sig['id'] ?? '',
			'signature_desc' => $sig['description'] ?? '',
			'match_snippet'  => $snippet,
		];

		switch ( $table_key ) {
			case 'posts':
				$data['post_id']    = (int) $row_id;
				$data['post_title'] = $row['post_title'] ?? '';
				$data['edit_url']   = admin_url( "post.php?post={$row_id}&action=edit" );
				if ( function_exists( 'get_permalink' ) ) {
					$post_url = get_permalink( (int) $row_id );
					if ( is_string( $post_url ) && $post_url !== '' ) {
						$data['post_url'] = $post_url;
					}
				}
				break;

			case 'comments':
				$data['comment_id']     = (int) $row_id;
				$data['comment_author'] = $row['comment_author'] ?? '';
				$data['edit_url']       = admin_url( "comment.php?action=editcomment&c={$row_id}" );
				break;

			case 'options':
				$data['option_name'] = $row['option_name'] ?? '';
				break;

				case 'postmeta':
					// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- This is a result payload field, not a database WHERE/ORDER clause.
					$data['meta_key'] = $row['meta_key'] ?? '';
					$data['post_id']  = (int) ( $row['post_id'] ?? 0 );
				if ( $data['post_id'] > 0 ) {
					$data['edit_url'] = admin_url( "post.php?post={$data['post_id']}&action=edit" );
					if ( function_exists( 'get_permalink' ) ) {
						$post_url = get_permalink( (int) $data['post_id'] );
						if ( is_string( $post_url ) && $post_url !== '' ) {
							$data['post_url'] = $post_url;
						}
					}
				}
				break;

				case 'usermeta':
					// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- This is a result payload field, not a database WHERE/ORDER clause.
					$data['meta_key'] = $row['meta_key'] ?? '';
					$data['user_id']  = (int) ( $row['user_id'] ?? 0 );
				if ( $data['user_id'] > 0 ) {
					$data['edit_url'] = admin_url( "user-edit.php?user_id={$data['user_id']}" );
				}
				break;

				case 'termmeta':
					// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- This is a result payload field, not a database WHERE/ORDER clause.
					$data['meta_key'] = $row['meta_key'] ?? '';
					$data['term_id']  = (int) ( $row['term_id'] ?? 0 );
				break;
		}

		return $data;
	}

	/**
	 * Fetch SQL-condition signatures from cloud (cached 24h).
	 *
	 * @return array|\\WP_Error
	 */
	private function get_signatures() {
		if ( $this->signatures !== null ) {
			return $this->signatures;
		}

		$cached = get_transient( self::TRANSIENT_KEY );
		if ( is_array( $cached ) && ! empty( $cached ) ) {
			$this->signatures = $cached;
			return $cached;
		}

		if ( ! class_exists( 'SecurWP\\Modules\\Cloud\\SecureApi' ) ) {
			require_once dirname( __DIR__, 2 ) . '/Cloud/SecureApi.php';
		}

		$settings = CloudSettings::get_all();
		$api      = new \SecurWP\Modules\Cloud\SecureApi(
			$settings['license_key'] ?? '',
			$settings['site_token'] ?? '',
			$settings['site_secret'] ?? ''
		);

		$response = $api->get( 'services/intelligence/db-patterns' );

		if ( is_wp_error( $response ) ) {
			return $response;
		}

		$sigs = $response['signatures'] ?? null;

		if ( ! is_array( $sigs ) || empty( $sigs ) ) {
			return new \WP_Error(
				'db_signatures_unavailable',
				'No database scan signatures returned from Cloud.'
			);
		}

		set_transient( self::TRANSIENT_KEY, $sigs, self::CACHE_TTL );

		$this->signatures = $sigs;
		return $sigs;
	}
}
