<?php
/**
 * Attachment path index for fast uploads folder scanning.
 *
 * @package WUClean
 */

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

/**
 * Get attachment index table name.
 *
 * @return string
 */
function pt_wuclean_attachment_index_table_name() {
	global $wpdb;
	return $wpdb->prefix . 'wuclean_attachment_files';
}

/**
 * Whether the attachment index table exists.
 *
 * @return bool
 */
function pt_wuclean_attachment_index_table_exists() {
	global $wpdb;
	$table = pt_wuclean_attachment_index_table_name();
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
	return ! empty( $found );
}

/**
 * Hash a relative uploads path for indexed lookups.
 *
 * @param string $relpath Relative path (stored in _wp_attached_file).
 * @return string 32-char md5 hash.
 */
function pt_wuclean_attachment_index_hash_relpath( $relpath ) {
	$relpath = is_string( $relpath ) ? $relpath : '';
	$relpath = str_replace( '\\', '/', $relpath );
	return md5( $relpath );
}

/**
 * Bulk lookup: return set of relpaths that exist in Media Library (via index table).
 *
 * @param string[] $relpaths Relative paths.
 * @return array<string,bool> Map of relpath => true.
 */
function pt_wuclean_attachment_index_lookup_relpaths( array $relpaths ) {
	global $wpdb;

	$found = array();
	if ( empty( $relpaths ) ) {
		return $found;
	}
	if ( ! pt_wuclean_attachment_index_table_exists() ) {
		return $found;
	}

	$table = pt_wuclean_attachment_index_table_name();
	// Table name is derived from $wpdb->prefix + a fixed suffix, but escape defensively before interpolation.
	$table = esc_sql( $table );

	$normalized = array();
	foreach ( $relpaths as $relpath ) {
		if ( ! is_string( $relpath ) || '' === $relpath ) {
			continue;
		}
		$norm                = str_replace( '\\', '/', $relpath );
		$normalized[ $norm ] = true;
	}
	if ( empty( $normalized ) ) {
		return $found;
	}

	$hash_to_relpaths = array();
	foreach ( array_keys( $normalized ) as $relpath ) {
		$hash = pt_wuclean_attachment_index_hash_relpath( $relpath );
		if ( ! isset( $hash_to_relpaths[ $hash ] ) ) {
			$hash_to_relpaths[ $hash ] = array();
		}
		$hash_to_relpaths[ $hash ][] = $relpath;
	}

	$hashes     = array_keys( $hash_to_relpaths );
	$chunk_size = 500;
	foreach ( array_chunk( $hashes, $chunk_size ) as $chunk ) {
		$placeholders = implode( ', ', array_fill( 0, count( $chunk ), '%s' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $placeholders from fixed count; values bound via prepare.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT relpath_hash, relpath FROM {$table} WHERE relpath_hash IN ({$placeholders})",
				...$chunk
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

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

		foreach ( $rows as $row ) {
			$hash    = isset( $row['relpath_hash'] ) ? (string) $row['relpath_hash'] : '';
			$relpath = isset( $row['relpath'] ) ? (string) $row['relpath'] : '';
			if ( '' === $hash || '' === $relpath ) {
				continue;
			}

			$relpath = str_replace( '\\', '/', $relpath );
			if ( isset( $hash_to_relpaths[ $hash ] ) && in_array( $relpath, $hash_to_relpaths[ $hash ], true ) ) {
				$found[ $relpath ] = true;
			}
		}
	}

	return $found;
}

/**
 * Upsert a single attachment into the index table.
 *
 * @param int $attachment_id Attachment ID.
 * @return void
 */
function pt_wuclean_attachment_index_upsert( $attachment_id ) {
	global $wpdb;

	$attachment_id = absint( $attachment_id );
	if ( $attachment_id <= 0 ) {
		return;
	}
	if ( ! pt_wuclean_attachment_index_table_exists() ) {
		return;
	}

	$relpath = get_post_meta( $attachment_id, '_wp_attached_file', true );
	if ( ! is_string( $relpath ) || '' === $relpath ) {
		return;
	}
	$relpath = str_replace( '\\', '/', $relpath );
	$hash    = pt_wuclean_attachment_index_hash_relpath( $relpath );

	$table = pt_wuclean_attachment_index_table_name();

	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$wpdb->replace(
		$table,
		array(
			'attachment_id' => $attachment_id,
			'relpath_hash'  => $hash,
			'relpath'       => $relpath,
			'updated_at'    => gmdate( 'Y-m-d H:i:s' ),
		),
		array( '%d', '%s', '%s', '%s' )
	);
}

/**
 * Delete a single attachment from the index table.
 *
 * @param int $attachment_id Attachment ID.
 * @return void
 */
function pt_wuclean_attachment_index_delete( $attachment_id ) {
	global $wpdb;
	$attachment_id = absint( $attachment_id );
	if ( $attachment_id <= 0 ) {
		return;
	}
	if ( ! pt_wuclean_attachment_index_table_exists() ) {
		return;
	}

	$table = pt_wuclean_attachment_index_table_name();
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$wpdb->delete( $table, array( 'attachment_id' => $attachment_id ), array( '%d' ) );
}

/**
 * Backfill the index table in small batches.
 *
 * @param int $limit Max rows.
 * @return int Number of rows processed this batch.
 */
function pt_wuclean_attachment_index_backfill_batch( $limit = 500 ) {
	global $wpdb;

	$limit = max( 50, min( 2000, absint( $limit ) ) );
	if ( ! pt_wuclean_attachment_index_table_exists() ) {
		return 0;
	}

	$last_id = absint( get_option( 'pt_wuclean_attachment_index_last_id', 0 ) );
	$table   = pt_wuclean_attachment_index_table_name();

	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$rows = $wpdb->get_results(
		$wpdb->prepare(
			"SELECT post_id, meta_value
			 FROM {$wpdb->postmeta}
			 WHERE meta_key = '_wp_attached_file'
			 AND post_id > %d
			 ORDER BY post_id ASC
			 LIMIT %d",
			$last_id,
			$limit
		),
		ARRAY_A
	);

	if ( ! is_array( $rows ) || empty( $rows ) ) {
		update_option( 'pt_wuclean_attachment_index_complete', 'yes', false );
		return 0;
	}

	$processed = 0;
	foreach ( $rows as $row ) {
		$post_id    = isset( $row['post_id'] ) ? absint( $row['post_id'] ) : 0;
		$meta_value = isset( $row['meta_value'] ) ? (string) $row['meta_value'] : '';
		if ( $post_id <= 0 || '' === $meta_value ) {
			continue;
		}

		$meta_value = str_replace( '\\', '/', $meta_value );
		$hash       = pt_wuclean_attachment_index_hash_relpath( $meta_value );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->replace(
			$table,
			array(
				'attachment_id' => $post_id,
				'relpath_hash'  => $hash,
				'relpath'       => $meta_value,
				'updated_at'    => gmdate( 'Y-m-d H:i:s' ),
			),
			array( '%d', '%s', '%s', '%s' )
		);

		++$processed;
		$last_id = max( $last_id, $post_id );
	}

	update_option( 'pt_wuclean_attachment_index_last_id', $last_id, false );
	return $processed;
}

/**
 * Cron handler to backfill attachment index table.
 *
 * @return void
 */
function pt_wuclean_attachment_index_backfill_cron() {
	if ( get_option( 'pt_wuclean_attachment_index_complete', '' ) === 'yes' ) {
		return;
	}

	$processed = pt_wuclean_attachment_index_backfill_batch( 500 );
	if ( $processed > 0 ) {
		if ( ! wp_next_scheduled( 'pt_wuclean_attachment_index_backfill' ) ) {
			wp_schedule_single_event( time() + 10, 'pt_wuclean_attachment_index_backfill' );
		}
	}
}

add_action( 'pt_wuclean_attachment_index_backfill', 'pt_wuclean_attachment_index_backfill_cron' );

// Keep the index warm for new/updated attachments.
add_action( 'add_attachment', 'pt_wuclean_attachment_index_upsert' );
add_action( 'edit_attachment', 'pt_wuclean_attachment_index_upsert' );
add_action( 'delete_attachment', 'pt_wuclean_attachment_index_delete' );
