<?php
/**
 * Database manager — creates and manages the scan results table.
 *
 * Handles table installation via dbDelta and scan metadata stored
 * in a WordPress option.
 *
 * @package CoffeeWP_Media_Cleanup
 */

namespace CWPM\Core;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders -- plugin SQL builder — operates on wp_cwpm_* tables; values bound via placeholders; placeholder counts dynamic.

class Db {

	/**
	 * Schema version — bump when the table structure changes.
	 */
	const DB_VERSION = '1.2';

	/**
	 * Return the full table name including the WP prefix.
	 *
	 * @return string
	 */
	public static function get_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'cwpm_scan_results';
	}

	/**
	 * Return the full trash table name including the WP prefix.
	 *
	 * @return string
	 */
	public static function get_trash_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'cwpm_trash';
	}

	/**
	 * Return the full file-hashes table name including the WP prefix.
	 *
	 * @return string
	 */
	public static function get_hashes_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'cwpm_file_hashes';
	}

	/**
	 * Validate that a table name belongs to this plugin before destructive ops.
	 *
	 * @param string $table Fully-qualified table name (with prefix).
	 * @return bool
	 */
	private static function is_plugin_table( $table ) {
		$allowed = array(
			self::get_table_name(),
			self::get_trash_table_name(),
			self::get_hashes_table_name(),
		);
		return in_array( $table, $allowed, true );
	}

	/**
	 * Create or update the scan results table.
	 *
	 * Compares DB_VERSION against the stored option and only runs
	 * dbDelta when there is a mismatch.
	 */
	public static function install() {
		$installed_version = get_option( 'cwpm_db_version', '' );

		if ( self::DB_VERSION === $installed_version ) {
			return;
		}

		global $wpdb;

		$table_name      = self::get_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			scan_id CHAR(36) NOT NULL,
			rel_path VARCHAR(512) NOT NULL,
			year VARCHAR(4) NOT NULL,
			month VARCHAR(20) NOT NULL,
			file_name VARCHAR(255) NOT NULL,
			attachment_id BIGINT UNSIGNED DEFAULT 0,
			in_media TINYINT(1) DEFAULT 0,
			used TINYINT(1) DEFAULT 0,
			broken TINYINT(1) DEFAULT 0,
			file_size BIGINT UNSIGNED DEFAULT 0,
			savings_raw BIGINT UNSIGNED DEFAULT 0,
			status VARCHAR(30) DEFAULT 'scanned',
			action_applied VARCHAR(50) DEFAULT '',
			thumb_url TEXT,
			edit_url TEXT,
			sizes_json LONGTEXT,
			orphan_thumbs_json LONGTEXT,
			used_in_json LONGTEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY scan_id (scan_id),
			KEY rel_path (rel_path(191)),
			KEY status (status),
			KEY in_media (in_media),
			KEY broken (broken),
			KEY used (used)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		$trash_table_name = self::get_trash_table_name();

		$trash_sql = "CREATE TABLE {$trash_table_name} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			rel_path VARCHAR(512) NOT NULL,
			original_path VARCHAR(512) NOT NULL DEFAULT '',
			file_name VARCHAR(255) NOT NULL,
			file_size BIGINT UNSIGNED DEFAULT 0,
			trashed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY rel_path (rel_path(191)),
			KEY file_name (file_name(191)),
			KEY trashed_at (trashed_at)
		) {$charset_collate};";

		dbDelta( $trash_sql );

		$hashes_table = self::get_hashes_table_name();
		$hashes_sql   = "CREATE TABLE {$hashes_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			scan_id CHAR(36) NOT NULL,
			rel_path VARCHAR(512) NOT NULL,
			file_hash CHAR(32) NOT NULL,
			file_size BIGINT UNSIGNED DEFAULT 0,
			attachment_id BIGINT UNSIGNED DEFAULT 0,
			hashed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY scan_id (scan_id),
			KEY file_hash (file_hash),
			KEY rel_path (rel_path(191))
		) {$charset_collate};";

		dbDelta( $hashes_sql );

		update_option( 'cwpm_db_version', self::DB_VERSION );
	}

	/**
	 * Save scan metadata.
	 *
	 * @param array $data Scan meta fields.
	 */
	public static function save_scan_meta( $data ) {
		update_option( 'cwpm_scan_meta', $data );
	}

	/**
	 * Retrieve scan metadata.
	 *
	 * @return array|null The stored meta array, or null if not set.
	 */
	public static function get_scan_meta() {
		$meta = get_option( 'cwpm_scan_meta', null );
		return is_array( $meta ) ? $meta : null;
	}

	/**
	 * Delete scan metadata.
	 */
	public static function clear_scan_meta() {
		delete_option( 'cwpm_scan_meta' );
	}

	/**
	 * Start a new scan — generate an ID, truncate old results, and save initial meta.
	 *
	 * @param int $total_files Total number of files to scan.
	 * @return string The generated scan_id (UUID v4).
	 */
	public static function start_scan( $total_files ) {
		global $wpdb;

		$scan_id = self::generate_uuid();
		$table   = self::get_table_name();

		if ( ! self::is_plugin_table( $table ) ) {
			return '';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( "TRUNCATE TABLE `{$table}`" );

		self::save_scan_meta(
			array(
				'scan_id'         => $scan_id,
				'status'          => 'in_progress',
				'started_at'      => current_time( 'mysql' ),
				'completed_at'    => null,
				'total_files'     => (int) $total_files,
				'scanned_files'   => 0,
				'last_file_index' => 0,
			)
		);

		return $scan_id;
	}

	/**
	 * Mark the current scan as complete.
	 */
	public static function complete_scan() {
		$meta = self::get_scan_meta();

		if ( ! $meta ) {
			return;
		}

		$meta['status']       = 'complete';
		$meta['completed_at'] = current_time( 'mysql' );

		self::save_scan_meta( $meta );
	}

	/**
	 * Update scan progress counters.
	 *
	 * @param int $scanned_count   Number of files scanned so far.
	 * @param int $last_file_index Index of the last file processed.
	 */
	public static function update_scan_progress( $scanned_count, $last_file_index ) {
		$meta = self::get_scan_meta();

		if ( ! $meta ) {
			return;
		}

		$meta['scanned_files']   = (int) $scanned_count;
		$meta['last_file_index'] = (int) $last_file_index;

		self::save_scan_meta( $meta );
	}

	/**
	 * Bulk-insert scan result rows into the results table.
	 *
	 * @param string $scan_id The scan UUID.
	 * @param array  $results Array of result arrays from Media\Scanner.
	 */
	public static function insert_results( $scan_id, $results ) {
		if ( empty( $results ) ) {
			return;
		}

		global $wpdb;

		$table = self::get_table_name();

		foreach ( $results as $result ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->insert(
				$table,
				array(
					'scan_id'            => $scan_id,
					'rel_path'           => $result['rel_path'] ?? '',
					'year'               => $result['year'] ?? '',
					'month'              => $result['month'] ?? '',
					'file_name'          => $result['name'] ?? '',
					'attachment_id'      => isset( $result['attachment_id'] ) ? (int) $result['attachment_id'] : 0,
					'in_media'           => ! empty( $result['in_media'] ) ? 1 : 0,
					'used'               => ! empty( $result['used'] ) ? 1 : 0,
					'broken'             => ! empty( $result['broken'] ) ? 1 : 0,
					'file_size'          => isset( $result['size'] ) ? (int) $result['size'] : 0,
					'savings_raw'        => isset( $result['savings_raw'] ) ? (int) $result['savings_raw'] : 0,
					'status'             => 'scanned',
					'action_applied'     => '',
					'thumb_url'          => $result['thumb_url'] ?? '',
					'edit_url'           => $result['edit_url'] ?? '',
					'sizes_json'         => wp_json_encode( $result['sizes'] ?? array() ),
					'orphan_thumbs_json' => wp_json_encode( $result['orphan_thumbs'] ?? array() ),
					'used_in_json'       => wp_json_encode( $result['used_in'] ?? array() ),
				),
				array(
					'%s',
					'%s',
					'%s',
					'%s',
					'%s',
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					'%s',
					'%s',
					'%s',
					'%s',
					'%s',
					'%s',
					'%s',
				)
			);
		}
	}

	/**
	 * Query scan results with filtering, searching, and pagination.
	 *
	 * @param array $args {
	 *     Optional. Query arguments.
	 *
	 *     @type int    $page          Page number (default 1).
	 *     @type int    $per_page      Results per page; 0 for all (default 200).
	 *     @type string $status_filter One of '', 'unused', 'used', 'missing', 'broken',
	 *                                 'missing-thumbs', 'orphan-thumbs', 'duplicates' (default '').
	 *     @type string $search        Search term matched against file_name (default '').
	 *     @type string $order_by      SQL ORDER BY clause (default 'year DESC, month ASC, file_name ASC').
	 * }
	 * @return array {
	 *     @type array $results     Transformed result rows.
	 *     @type int   $total       Total matching rows.
	 *     @type int   $page        Current page.
	 *     @type int   $per_page    Results per page.
	 *     @type int   $total_pages Total pages.
	 * }
	 */
	public static function query_results( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'page'          => 1,
			'per_page'      => 200,
			'status_filter' => '',
			'search'        => '',
			'order_by'      => 'year DESC, month ASC, file_name ASC',
		);
		$args     = wp_parse_args( $args, $defaults );

		$table  = self::get_table_name();
		$where  = array( '1=1' );
		$values = array();

		switch ( $args['status_filter'] ) {
			case 'unused':
				$where[] = 'in_media = 1 AND used = 0 AND broken = 0';
				break;
			case 'used':
				$where[] = 'in_media = 1 AND used = 1 AND broken = 0';
				break;
			case 'missing':
				$where[] = 'in_media = 0 AND broken = 0';
				break;
			case 'broken':
				$where[] = 'broken = 1';
				break;
			case 'missing-thumbs':
				$where[]  = 'sizes_json LIKE %s';
				$values[] = '%"status":"missing"%';
				break;
			case 'orphan-thumbs':
				$where[] = "orphan_thumbs_json != '[]' AND orphan_thumbs_json != '' AND orphan_thumbs_json IS NOT NULL";
				break;
			case 'duplicates':
				$hashes_table = self::get_hashes_table_name();
				$meta         = self::get_scan_meta();
				$scan_id      = is_array( $meta ) && ! empty( $meta['scan_id'] ) ? $meta['scan_id'] : '';
				if ( '' === $scan_id ) {
					$where[] = '1=0';
					break;
				}
				$where[] = $wpdb->prepare(
					"rel_path IN (
						SELECT h.rel_path FROM {$hashes_table} h
						INNER JOIN (
							SELECT file_hash FROM {$hashes_table}
							WHERE scan_id = %s GROUP BY file_hash HAVING COUNT(*) >= 2
						) d ON d.file_hash = h.file_hash
						WHERE h.scan_id = %s
					)",
					$scan_id,
					$scan_id
				);
				break;
		}

		if ( '' !== $args['search'] ) {
			$where[]  = 'file_name LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

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

		$allowed_order_cols = array( 'year', 'month', 'file_name', 'file_size', 'in_media', 'used', 'broken', 'created_at' );
		$order_by           = self::sanitize_order_by( $args['order_by'], $allowed_order_cols );

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$count_sql = $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}", ...$values );
		} else {
			$count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}";
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var( $count_sql );

		$page      = max( 1, (int) $args['page'] );
		$per_page  = (int) $args['per_page'];
		$limit_sql = '';
		if ( $per_page > 0 ) {
			$offset    = ( $page - 1 ) * $per_page;
			$limit_sql = $wpdb->prepare( ' LIMIT %d OFFSET %d', $per_page, $offset );
		}
		$total_pages = $per_page > 0 ? max( 1, (int) ceil( $total / $per_page ) ) : 1;

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$query = $wpdb->prepare( "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$order_by}{$limit_sql}", ...$values );
		} else {
			$query = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$order_by}{$limit_sql}";
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $query, ARRAY_A );

		$scan_meta       = self::get_scan_meta();
		$scan_id_for_map = is_array( $scan_meta ) && ! empty( $scan_meta['scan_id'] ) ? $scan_meta['scan_id'] : '';
		$duplicate_map   = '' !== $scan_id_for_map ? self::build_duplicate_map( $scan_id_for_map ) : array();

		$results = array_map(
			function ( $row ) use ( $duplicate_map ) {
				return self::transform_row( $row, $duplicate_map );
			},
			$rows ?: array()
		);

		return array(
			'results'     => $results,
			'total'       => $total,
			'page'        => $page,
			'per_page'    => $per_page,
			'total_pages' => $total_pages,
		);
	}

	/**
	 * Transform a raw database row into the shape the frontend expects.
	 *
	 * @param array $row           Raw associative row from the scan results table.
	 * @param array $duplicate_map Optional map from build_duplicate_map().
	 * @return array Transformed result.
	 */
	private static function transform_row( $row, $duplicate_map = array() ) {
		return array(
			'id'               => (int) $row['id'],
			'year'             => $row['year'],
			'month'            => $row['month'],
			'name'             => $row['file_name'],
			'rel_path'         => $row['rel_path'],
			'attachment_id'    => (int) $row['attachment_id'],
			'in_media'         => (bool) $row['in_media'],
			'used'             => (bool) $row['used'],
			'broken'           => (bool) $row['broken'],
			'size'             => (int) $row['file_size'],
			'size_formatted'   => size_format( (int) $row['file_size'], 1 ),
			'savings_raw'      => (int) $row['savings_raw'],
			'potential_saving' => size_format( (int) $row['savings_raw'], 1 ),
			'thumb_url'        => $row['thumb_url'] ?: '',
			'edit_url'         => $row['edit_url'] ?: '',
			'used_in'          => json_decode( $row['used_in_json'] ?: '[]', true ) ?: array(),
			'sizes'            => json_decode( $row['sizes_json'] ?: '[]', true ) ?: array(),
			'orphan_thumbs'    => json_decode( $row['orphan_thumbs_json'] ?: '[]', true ) ?: array(),
			'status'           => $row['status'] ?: 'scanned',
			'action_applied'   => $row['action_applied'] ?: '',
			'duplicate_of'     => isset( $duplicate_map[ $row['rel_path'] ] ) ? $duplicate_map[ $row['rel_path'] ] : null,
			'is_duplicate'     => isset( $duplicate_map[ $row['rel_path'] ] ),
		);
	}

	/**
	 * Update the action_applied and status columns for a scan result row.
	 *
	 * @param string $rel_path The file's relative path (uploads-relative).
	 * @param string $action   The action that was applied (e.g. 'delete-unused').
	 * @param string $status   The new status (e.g. 'trashed').
	 * @return int|false The number of rows updated, or false on error.
	 */
	public static function update_result_action( $rel_path, $action, $status ) {
		global $wpdb;
		$table = self::get_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->update(
			$table,
			array(
				'action_applied' => sanitize_text_field( $action ),
				'status'         => sanitize_text_field( $status ),
			),
			array( 'rel_path' => $rel_path ),
			array( '%s', '%s' ),
			array( '%s' )
		);
	}

	/**
	 * Update scan result row fields after an action changes the file's metadata.
	 *
	 * @param string $rel_path      The file's relative path (uploads-relative).
	 * @param array  $data          Associative array of columns to update.
	 * @return int|false Rows updated or false on error.
	 */
	public static function update_result_data( $rel_path, $data ) {
		global $wpdb;
		$table = self::get_table_name();

		$allowed = array( 'attachment_id', 'in_media', 'thumb_url', 'edit_url', 'sizes_json', 'orphan_thumbs_json' );
		$update  = array();
		$formats = array();

		foreach ( $allowed as $col ) {
			if ( ! array_key_exists( $col, $data ) ) {
				continue;
			}
			$update[ $col ] = $data[ $col ];
			$formats[]      = in_array( $col, array( 'attachment_id', 'in_media' ), true ) ? '%d' : '%s';
		}

		if ( empty( $update ) ) {
			return 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->update(
			$table,
			$update,
			array( 'rel_path' => $rel_path ),
			$formats,
			array( '%s' )
		);
	}

	/**
	 * Lightweight query returning minimal columns for clean target building.
	 *
	 * Uses the same filter/search logic as query_results() but returns ALL
	 * matching rows (no pagination) with only the fields needed to build
	 * clean targets on the frontend.  Only rows with status = 'scanned'
	 * (i.e. not yet cleaned) are included.
	 *
	 * @param array $args {
	 *     Optional. Query arguments.
	 *
	 *     @type string $status_filter Filter key (default '').
	 *     @type string $search        Search term matched against file_name (default '').
	 * }
	 * @return array Array of lightweight result arrays.
	 */
	public static function query_results_ids( $args = array() ) {
		global $wpdb;
		$table = self::get_table_name();

		$defaults = array(
			'status_filter' => '',
			'search'        => '',
		);
		$args     = wp_parse_args( $args, $defaults );

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

		switch ( $args['status_filter'] ) {
			case 'unused':
				$where[] = 'in_media = 1 AND used = 0 AND broken = 0';
				break;
			case 'used':
				$where[] = 'in_media = 1 AND used = 1 AND broken = 0';
				break;
			case 'missing':
				$where[] = 'in_media = 0 AND broken = 0';
				break;
			case 'broken':
				$where[] = 'broken = 1';
				break;
			case 'missing-thumbs':
				$where[]  = 'sizes_json LIKE %s';
				$values[] = '%"status":"missing"%';
				break;
			case 'orphan-thumbs':
				$where[] = "orphan_thumbs_json != '[]' AND orphan_thumbs_json != '' AND orphan_thumbs_json IS NOT NULL";
				break;
			case 'duplicates':
				$hashes_table = self::get_hashes_table_name();
				$meta         = self::get_scan_meta();
				$scan_id      = is_array( $meta ) && ! empty( $meta['scan_id'] ) ? $meta['scan_id'] : '';
				if ( '' === $scan_id ) {
					$where[] = '1=0';
					break;
				}
				$where[] = $wpdb->prepare(
					"rel_path IN (
						SELECT h.rel_path FROM {$hashes_table} h
						INNER JOIN (
							SELECT file_hash FROM {$hashes_table}
							WHERE scan_id = %s GROUP BY file_hash HAVING COUNT(*) >= 2
						) d ON d.file_hash = h.file_hash
						WHERE h.scan_id = %s
					)",
					$scan_id,
					$scan_id
				);
				break;
		}

		if ( '' !== $args['search'] ) {
			$where[]  = 'file_name LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		$where[] = "status = 'scanned'";

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

		$columns = 'rel_path, attachment_id, file_name, in_media, used, broken, file_size, savings_raw, sizes_json, orphan_thumbs_json';

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$query = $wpdb->prepare( "SELECT {$columns} FROM {$table} WHERE {$where_sql}", ...$values );
		} else {
			$query = "SELECT {$columns} FROM {$table} WHERE {$where_sql}";
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $query, ARRAY_A );

		$scan_meta       = self::get_scan_meta();
		$scan_id_for_map = is_array( $scan_meta ) && ! empty( $scan_meta['scan_id'] ) ? $scan_meta['scan_id'] : '';
		$duplicate_map   = '' !== $scan_id_for_map ? self::build_duplicate_map( $scan_id_for_map ) : array();

		return array_map(
			function ( $row ) use ( $duplicate_map ) {
				return array(
					'rel_path'      => $row['rel_path'],
					'attachment_id' => (int) $row['attachment_id'],
					'name'          => $row['file_name'],
					'in_media'      => (bool) $row['in_media'],
					'used'          => (bool) $row['used'],
					'broken'        => (bool) $row['broken'],
					'size'          => (int) $row['file_size'],
					'savings_raw'   => (int) $row['savings_raw'],
					'sizes'         => json_decode( $row['sizes_json'] ?: '[]', true ) ?: array(),
					'orphan_thumbs' => json_decode( $row['orphan_thumbs_json'] ?: '[]', true ) ?: array(),
					'duplicate_of'  => isset( $duplicate_map[ $row['rel_path'] ] ) ? $duplicate_map[ $row['rel_path'] ] : null,
					'is_duplicate'  => isset( $duplicate_map[ $row['rel_path'] ] ),
				);
			},
			$rows ?: array()
		);
	}

	/**
	 * Insert a trash record.
	 *
	 * @param array $data {
	 *     @type string $rel_path      Relative path of the file.
	 *     @type string $original_path Original path before trashing.
	 *     @type string $file_name     File name.
	 *     @type int    $file_size     File size in bytes.
	 * }
	 * @return int|false Insert ID on success, or false on error.
	 */
	public static function insert_trash_record( $data ) {
		global $wpdb;
		$table = self::get_trash_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->insert(
			$table,
			array(
				'rel_path'      => $data['rel_path'],
				'original_path' => $data['original_path'] ?? '',
				'file_name'     => $data['file_name'],
				'file_size'     => (int) ( $data['file_size'] ?? 0 ),
			),
			array(
				'%s',
				'%s',
				'%s',
				'%d',
			)
		);

		return false === $result ? false : (int) $wpdb->insert_id;
	}

	/**
	 * Delete a trash record by primary key ID.
	 *
	 * Used for transactional rollback when the on-disk move fails after
	 * the DB row has been pre-inserted (race-safe trash flow).
	 *
	 * @param int $id Primary key.
	 * @return int|false Number of rows deleted.
	 */
	public static function delete_trash_record_by_id( $id ) {
		global $wpdb;
		$table = self::get_trash_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->delete( $table, array( 'id' => (int) $id ), array( '%d' ) );
	}

	/**
	 * Delete a trash record by rel_path.
	 *
	 * @param string $rel_path The relative path to match.
	 * @return int|false Number of rows deleted, or false on error.
	 */
	public static function delete_trash_record( $rel_path ) {
		global $wpdb;
		$table = self::get_trash_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->delete(
			$table,
			array( 'rel_path' => $rel_path ),
			array( '%s' )
		);
	}

	/**
	 * Check whether a trash record exists for a given relative path.
	 *
	 * Used by the trash-folder sync routine to skip files already tracked.
	 *
	 * @param string $rel_path Uploads-relative path.
	 * @return bool
	 */
	public static function trash_record_exists( $rel_path ) {
		global $wpdb;
		$table = self::get_trash_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$found = $wpdb->get_var(
			$wpdb->prepare( "SELECT 1 FROM `{$table}` WHERE rel_path = %s LIMIT 1", $rel_path )
		);
		return null !== $found;
	}

	/**
	 * Truncate the file-hashes table. Used when a new scan starts.
	 *
	 * @return bool
	 */
	public static function truncate_hashes() {
		global $wpdb;
		$table = self::get_hashes_table_name();
		if ( ! self::is_plugin_table( $table ) ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->query( "TRUNCATE TABLE `{$table}`" ) !== false;
	}

	/**
	 * Truncate the trash table.
	 *
	 * @return bool True on success, false on error.
	 */
	public static function truncate_trash() {
		global $wpdb;
		$table = self::get_trash_table_name();
		if ( ! self::is_plugin_table( $table ) ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->query( "TRUNCATE TABLE `{$table}`" ) !== false;
	}

	/**
	 * Query trash records with pagination and search.
	 *
	 * @param array $args {
	 *     Optional. Query arguments.
	 *
	 *     @type int    $page    Page number (default 1).
	 *     @type int    $per_page Results per page (default 50).
	 *     @type string $search  Search term matched against file_name (default '').
	 * }
	 * @return array {
	 *     @type array $results     Trash rows.
	 *     @type int   $total       Total matching rows.
	 *     @type int   $page        Current page.
	 *     @type int   $per_page    Results per page.
	 *     @type int   $total_pages Total pages.
	 * }
	 */
	public static function query_trash( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'page'     => 1,
			'per_page' => 50,
			'search'   => '',
		);
		$args     = wp_parse_args( $args, $defaults );
		$table    = self::get_trash_table_name();

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

		if ( '' !== $args['search'] ) {
			$where[]  = 'file_name LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

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

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$count_sql = $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}", ...$values );
			$size_sql  = $wpdb->prepare( "SELECT COALESCE(SUM(file_size), 0) FROM {$table} WHERE {$where_sql}", ...$values );
		} else {
			$count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}";
			$size_sql  = "SELECT COALESCE(SUM(file_size), 0) FROM {$table} WHERE {$where_sql}";
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var( $count_sql );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_size = (int) $wpdb->get_var( $size_sql );

		$page      = max( 1, (int) $args['page'] );
		$per_page  = (int) $args['per_page'];
		$limit_sql = '';
		if ( $per_page > 0 ) {
			$offset    = ( $page - 1 ) * $per_page;
			$limit_sql = $wpdb->prepare( ' LIMIT %d OFFSET %d', $per_page, $offset );
		}
		$total_pages = $per_page > 0 ? max( 1, (int) ceil( $total / $per_page ) ) : 1;

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$query = $wpdb->prepare(
				"SELECT * FROM {$table} WHERE {$where_sql} ORDER BY trashed_at DESC{$limit_sql}",
				...$values
			);
		} else {
			$query = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY trashed_at DESC{$limit_sql}";
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $query, ARRAY_A );

		$results = array_map(
			function ( $row ) {
				$thumb_url     = '';
				$original_path = $row['original_path'];
				if ( $original_path ) {
						$attached_id = attachment_url_to_postid( $original_path );
					if ( $attached_id ) {
						$thumb_url = wp_get_attachment_image_url( $attached_id, 'thumbnail', false );
					}
				}

				return array(
					'id'             => (int) $row['id'],
					'rel_path'       => $row['rel_path'],
					'original_path'  => $original_path,
					'file_name'      => $row['file_name'],
					'file_size'      => (int) $row['file_size'],
					'size_formatted' => size_format( (int) $row['file_size'], 1 ),
					'trashed_at'     => $row['trashed_at'],
					'thumb_url'      => $thumb_url,
				);
			},
			$rows ?: array()
		);

		return array(
			'results'     => $results,
			'total'       => $total,
			'total_size'  => $total_size,
			'page'        => $page,
			'per_page'    => $per_page,
			'total_pages' => $total_pages,
		);
	}

	/**
	 * Lightweight query for trash IDs (used by "Select All" in the UI).
	 *
	 * Returns ALL matching rows with only rel_path, file_name, file_size.
	 * No pagination.
	 *
	 * @param array $args {
	 *     Optional. Query arguments.
	 *
	 *     @type string $search Search term matched against file_name (default '').
	 * }
	 * @return array Array of lightweight trash rows.
	 */
	public static function query_trash_ids( $args = array() ) {
		global $wpdb;
		$table = self::get_trash_table_name();

		$defaults = array(
			'search' => '',
		);
		$args     = wp_parse_args( $args, $defaults );

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

		if ( '' !== $args['search'] ) {
			$where[]  = 'file_name LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

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

		$columns = 'rel_path, file_name, file_size';

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$query = $wpdb->prepare( "SELECT {$columns} FROM {$table} WHERE {$where_sql}", ...$values );
		} else {
			$query = "SELECT {$columns} FROM {$table} WHERE {$where_sql}";
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $query, ARRAY_A );

		return array_map(
			function ( $row ) {
				return array(
					'rel_path'  => $row['rel_path'],
					'file_name' => $row['file_name'],
					'file_size' => (int) $row['file_size'],
				);
			},
			$rows ?: array()
		);
	}

	/**
	 * Get expired trash records (for file-level deletion).
	 *
	 * @param int $days Retention period in days.
	 * @return array[] Array of [ 'rel_path' => '...' ] records.
	 */
	public static function get_expired_trash( $days ) {
		global $wpdb;
		$table  = self::get_trash_table_name();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT rel_path FROM {$table} WHERE trashed_at < %s",
				$cutoff
			),
			ARRAY_A
		);
	}

	/**
	 * Delete trash records older than a given number of days.
	 *
	 * @param int $days Retention period in days.
	 * @return int Number of records deleted.
	 */
	public static function delete_expired_trash( $days ) {
		global $wpdb;
		$table   = self::get_trash_table_name();
		$cutoff  = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );
		$expired = self::get_expired_trash( $days );

		if ( empty( $expired ) ) {
			return 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$table} WHERE trashed_at < %s",
				$cutoff
			)
		);

		return count( $expired );
	}

	/**
	 * Insert a single file-hash row.
	 *
	 * @param string $scan_id       Current scan UUID.
	 * @param string $rel_path      Uploads-relative path.
	 * @param string $file_hash     32-char md5.
	 * @param int    $file_size     File size in bytes.
	 * @param int    $attachment_id Attachment ID (0 if orphan).
	 * @return int|false
	 */
	public static function insert_hash( $scan_id, $rel_path, $file_hash, $file_size, $attachment_id = 0 ) {
		global $wpdb;
		$table = self::get_hashes_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->insert(
			$table,
			array(
				'scan_id'       => $scan_id,
				'rel_path'      => $rel_path,
				'file_hash'     => $file_hash,
				'file_size'     => (int) $file_size,
				'attachment_id' => (int) $attachment_id,
			),
			array( '%s', '%s', '%s', '%d', '%d' )
		);
	}

	/**
	 * Return duplicate groups — hashes that appear >= 2 times in the current scan.
	 *
	 * @param string|null $scan_id  Optional scan UUID. Defaults to the latest.
	 * @param int         $min_size Minimum bytes per file (default 0 = no floor).
	 * @return array {
	 *     @type array $groups          Array of groups.
	 *     @type int   $total_groups    Number of duplicate groups.
	 *     @type int   $total_dup_bytes Bytes wasted.
	 * }
	 */
	public static function query_duplicate_groups( $scan_id = null, $min_size = 0 ) {
		global $wpdb;
		$table = self::get_hashes_table_name();

		if ( null === $scan_id ) {
			$meta    = self::get_scan_meta();
			$scan_id = is_array( $meta ) && ! empty( $meta['scan_id'] ) ? $meta['scan_id'] : '';
		}
		if ( '' === $scan_id ) {
			return array(
				'groups'          => array(),
				'total_groups'    => 0,
				'total_dup_bytes' => 0,
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$hashes = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT file_hash, COUNT(*) AS cnt, SUM(file_size) AS total
				 FROM {$table}
				 WHERE scan_id = %s AND file_size >= %d
				 GROUP BY file_hash
				 HAVING cnt >= 2
				 ORDER BY total DESC",
				$scan_id,
				(int) $min_size
			)
		);

		$groups          = array();
		$total_dup_bytes = 0;

		foreach ( $hashes as $row ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$files            = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT rel_path, file_size, attachment_id
					 FROM {$table}
					 WHERE scan_id = %s AND file_hash = %s
					 ORDER BY attachment_id = 0 ASC, rel_path ASC",
					$scan_id,
					$row->file_hash
				),
				ARRAY_A
			);
			$count            = (int) $row->cnt;
			$per_file_size    = $files && isset( $files[0]['file_size'] ) ? (int) $files[0]['file_size'] : 0;
			$dup_bytes        = ( $count - 1 ) * $per_file_size;
			$total_dup_bytes += $dup_bytes;

			$groups[] = array(
				'hash'       => $row->file_hash,
				'count'      => $count,
				'total_size' => (int) $row->total,
				'dup_bytes'  => $dup_bytes,
				'files'      => array_map(
					function ( $f ) {
						return array(
							'rel_path'      => $f['rel_path'],
							'file_size'     => (int) $f['file_size'],
							'attachment_id' => (int) $f['attachment_id'],
						);
					},
					$files ?: array()
				),
			);
		}

		return array(
			'groups'          => $groups,
			'total_groups'    => count( $groups ),
			'total_dup_bytes' => $total_dup_bytes,
		);
	}

	/**
	 * Compare two file rows for duplicate-group ordering.
	 *
	 * Lowest non-zero attachment_id first, then earliest hashed_at, then lowest id.
	 */
	public static function compare_duplicate_files( $a, $b ) {
		$a_id = (int) $a['attachment_id'];
		$b_id = (int) $b['attachment_id'];

		if ( $a_id > 0 && $b_id > 0 ) {
			if ( $a_id !== $b_id ) {
				return $a_id - $b_id;
			}
		} elseif ( $a_id > 0 ) {
			return -1;
		} elseif ( $b_id > 0 ) {
			return 1;
		}

		$a_hashed = $a['hashed_at'] ?? '';
		$b_hashed = $b['hashed_at'] ?? '';

		if ( $a_hashed !== $b_hashed ) {
			return strcmp( $a_hashed, $b_hashed );
		}

		return (int) ( $a['id'] ?? 0 ) - (int) ( $b['id'] ?? 0 );
	}

	private static function build_duplicate_map( $scan_id ) {
		global $wpdb;
		$table = self::get_hashes_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, rel_path, file_hash, file_size, attachment_id, hashed_at
				 FROM {$table}
				 WHERE scan_id = %s AND file_hash IN (
				     SELECT file_hash FROM {$table}
				     WHERE scan_id = %s
				     GROUP BY file_hash HAVING COUNT(*) >= 2
				 )",
				$scan_id,
				$scan_id
			),
			ARRAY_A
		);

		if ( empty( $rows ) ) {
			return array();
		}

		$groups = array();
		foreach ( $rows as $row ) {
			$hash = $row['file_hash'];
			if ( ! isset( $groups[ $hash ] ) ) {
				$groups[ $hash ] = array();
			}
			$groups[ $hash ][] = $row;
		}

		$map = array();

		foreach ( $groups as $files ) {
			$sorted = $files;
			usort( $sorted, array( __CLASS__, 'compare_duplicate_files' ) );

			$original    = $sorted[0];
			$group_count = count( $sorted );
			$orig_id     = (int) $original['attachment_id'];
			$edit_url    = $orig_id > 0 ? ( get_edit_post_link( $orig_id, 'raw' ) ?: '' ) : '';

			$base_info = array(
				'rel_path'      => $original['rel_path'],
				'attachment_id' => $orig_id,
				'file_name'     => basename( $original['rel_path'] ),
				'edit_url'      => $edit_url,
				'group_count'   => $group_count,
			);

			$dup_files = array();
			for ( $i = 1; $i < $group_count; $i++ ) {
				$copy_id       = (int) $sorted[ $i ]['attachment_id'];
				$copy_edit_url = $copy_id > 0 ? ( get_edit_post_link( $copy_id, 'raw' ) ?: '' ) : '';
				$dup_files[]   = array(
					'file_name' => basename( $sorted[ $i ]['rel_path'] ),
					'edit_url'  => $copy_edit_url,
				);
			}

			$map[ $original['rel_path'] ] = array_merge(
				$base_info,
				array(
					'is_original' => true,
					'dup_index'   => null,
					'dup_names'   => $dup_files,
				)
			);

			for ( $i = 1; $i < $group_count; $i++ ) {
				$map[ $sorted[ $i ]['rel_path'] ] = array_merge(
					$base_info,
					array(
						'is_original' => false,
						'dup_index'   => $i,
						'dup_names'   => array(),
					)
				);
			}
		}

		return $map;
	}

	/**
	 * Return summary statistics about duplicate files in a scan.
	 *
	 * @param string|null $scan_id Optional scan UUID. Defaults to the latest scan.
	 * @return array {
	 *     @type int $total_groups    Number of hash groups with >= 2 files.
	 *     @type int $total_dup_bytes Wasted bytes (sum of (count-1) * avg_size per group).
	 *     @type int $has_hashes      1 if any hash rows exist for this scan, 0 otherwise.
	 * }
	 */
	public static function query_duplicate_stats( $scan_id = null ) {
		global $wpdb;
		$table = self::get_hashes_table_name();

		if ( null === $scan_id ) {
			$meta    = self::get_scan_meta();
			$scan_id = is_array( $meta ) && ! empty( $meta['scan_id'] ) ? $meta['scan_id'] : '';
		}

		if ( '' === $scan_id ) {
			return array(
				'total_groups'    => 0,
				'total_dup_bytes' => 0,
				'has_hashes'      => 0,
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$has_hashes = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE scan_id = %s LIMIT 1",
				$scan_id
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$groups = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COUNT(*) AS cnt, AVG(file_size) AS avg_size
				 FROM {$table}
				 WHERE scan_id = %s
				 GROUP BY file_hash
				 HAVING cnt >= 2",
				$scan_id
			)
		);

		$total_groups    = count( $groups );
		$total_dup_bytes = 0;

		foreach ( $groups as $g ) {
			$total_dup_bytes += (int) round( ( (int) $g->cnt - 1 ) * (float) $g->avg_size );
		}

		return array(
			'total_groups'    => $total_groups,
			'total_dup_bytes' => $total_dup_bytes,
			'has_hashes'      => min( 1, $has_hashes ),
		);
	}

	/**
	 * Sanitize an ORDER BY string to prevent SQL injection.
	 *
	 * Parses comma-separated "column direction" pairs and only keeps
	 * those where the column name is in the allow-list and the direction
	 * is ASC or DESC.
	 *
	 * @param string $order_by     Raw ORDER BY string.
	 * @param array  $allowed_cols List of allowed column names.
	 * @return string Safe ORDER BY clause.
	 */
	private static function sanitize_order_by( $order_by, $allowed_cols ) {
		$parts = array_map( 'trim', explode( ',', $order_by ) );
		$safe  = array();

		foreach ( $parts as $part ) {
			$tokens = preg_split( '/\s+/', $part );
			$col    = $tokens[0] ?? '';
			$dir    = strtoupper( $tokens[1] ?? 'ASC' );

			if ( in_array( $col, $allowed_cols, true ) && in_array( $dir, array( 'ASC', 'DESC' ), true ) ) {
				$safe[] = "{$col} {$dir}";
			}
		}

		return ! empty( $safe ) ? implode( ', ', $safe ) : 'year DESC, month ASC, file_name ASC';
	}

	/**
	 * Generate a UUID v4 string.
	 *
	 * @return string
	 */
	private static function generate_uuid() {
		return wp_generate_uuid4();
	}
}
