<?php
/**
 * CacheLoop – Image Optimization Data Layer
 *
 * Dedicated table (not wp_postmeta) for tracking per-attachment
 * optimization state. At large media-library sizes (100k+ attachments),
 * a wp_postmeta meta_query JOIN for listing/counting becomes slow because
 * it competes with every other plugin's meta on that same bloated table.
 * A small table of our own, sized only to attachments we've actually
 * touched, with its own indexes, keeps these queries fast regardless of
 * how big the site's overall media library or postmeta table is.
 *
 * This file ships in both Free and Pro builds (it's schema/data-access
 * only — no compression logic), so the table exists and self-heals on
 * every activation/upgrade even before a license is entered.
 *
 * @package CacheLoop
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class CACHELOOP_Image_Optimize_DB
 */
class CACHELOOP_Image_Optimize_DB {

	/**
	 * Returns the fully-qualified table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'cacheloop_image_optimize';
	}

	/**
	 * Creates (or updates the schema of) the tracking table. Safe to call
	 * on every activation/upgrade — dbDelta() only alters what changed.
	 *
	 * @return void
	 */
	public static function create_table() {
		global $wpdb;

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

		// dbDelta() is picky about formatting: two spaces before PRIMARY KEY,
		// each field/key on its own line, no trailing comma quirks.
		$sql = "CREATE TABLE $table (
			attachment_id BIGINT UNSIGNED NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT '',
			reduction BIGINT UNSIGNED NOT NULL DEFAULT 0,
			original_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
			reason TEXT NULL,
			optimized_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (attachment_id),
			KEY status (status),
			KEY optimized_at (optimized_at)
		) $charset_collate;";

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

	/**
	 * Drops the table entirely (uninstall only).
	 *
	 * @return void
	 */
	public static function drop_table() {
		global $wpdb;
		$table = self::table();
		$wpdb->query( "DROP TABLE IF EXISTS $table" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- table name only, no user input.
	}

	/**
	 * Inserts or updates the tracking row for an attachment.
	 *
	 * @param  int    $attachment_id Attachment post ID.
	 * @param  string $status        'success' | 'error' | 'skipped'.
	 * @param  int    $reduction     Bytes saved.
	 * @param  string $reason        Human-readable reason (empty on success).
	 * @param  int    $original_size Total on-disk size (bytes) of the source files before conversion, summed across every optimized size. 0 when not applicable (error/skipped).
	 * @return void
	 */
	public static function upsert( $attachment_id, $status, $reduction, $reason, $original_size = 0 ) {
		global $wpdb;

		$table = self::table();
		$wpdb->query( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- table name only, no user input.
			$wpdb->prepare(
				"INSERT INTO $table (attachment_id, status, reduction, original_size, reason, optimized_at)
				 VALUES (%d, %s, %d, %d, %s, %s)
				 ON DUPLICATE KEY UPDATE status = VALUES(status), reduction = VALUES(reduction), original_size = VALUES(original_size), reason = VALUES(reason), optimized_at = VALUES(optimized_at)",
				$attachment_id,
				$status,
				max( 0, (int) $reduction ),
				max( 0, (int) $original_size ),
				(string) $reason,
				current_time( 'mysql' )
			)
		);
	}

	/**
	 * Removes the tracking row for an attachment (used by revert).
	 *
	 * @param  int $attachment_id Attachment post ID.
	 * @return void
	 */
	public static function delete( $attachment_id ) {
		global $wpdb;
		$wpdb->delete( self::table(), array( 'attachment_id' => (int) $attachment_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Whether an attachment already has a tracking row.
	 *
	 * @param  int $attachment_id Attachment post ID.
	 * @return bool
	 */
	public static function exists( $attachment_id ) {
		global $wpdb;
		$table = self::table();
		return (bool) $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM $table WHERE attachment_id = %d", $attachment_id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Fetches a single tracking row.
	 *
	 * @param  int $attachment_id Attachment post ID.
	 * @return array|null
	 */
	public static function get( $attachment_id ) {
		global $wpdb;
		$table = self::table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $table WHERE attachment_id = %d", $attachment_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Total tracked rows (= attachments that have been processed at least once).
	 *
	 * @return int
	 */
	public static function count_all() {
		global $wpdb;
		$table = self::table();
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM $table" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Row counts grouped by status.
	 *
	 * @return array{success:int,error:int,skipped:int}
	 */
	public static function count_by_status() {
		global $wpdb;
		$table = self::table();

		$rows = $wpdb->get_results( "SELECT status, COUNT(*) AS cnt FROM $table GROUP BY status", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		$counts = array(
			'success' => 0,
			'error'   => 0,
			'skipped' => 0,
		);
		foreach ( (array) $rows as $row ) {
			if ( isset( $counts[ $row['status'] ] ) ) {
				$counts[ $row['status'] ] = (int) $row['cnt'];
			}
		}
		return $counts;
	}

	/**
	 * Sum of bytes saved across every tracked row.
	 *
	 * @return int
	 */
	public static function sum_reduction() {
		global $wpdb;
		$table = self::table();
		return (int) $wpdb->get_var( "SELECT SUM(reduction) FROM $table" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Paginated tracking rows, newest first. Does not join to wp_posts —
	 * the caller fetches title/thumbnail only for the handful of rows on
	 * the current page.
	 *
	 * @param  int $paged    1-indexed page number.
	 * @param  int $per_page Rows per page.
	 * @return array{rows:array,total:int,pages:int,paged:int}
	 */
	public static function list_paginated( $paged = 1, $per_page = 10 ) {
		global $wpdb;
		$table = self::table();

		$paged    = max( 1, (int) $paged );
		$per_page = max( 1, (int) $per_page );
		$offset   = ( $paged - 1 ) * $per_page;

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare(
				"SELECT * FROM $table ORDER BY optimized_at DESC LIMIT %d OFFSET %d",
				$per_page,
				$offset
			),
			ARRAY_A
		);

		$total = self::count_all();

		return array(
			'rows'  => (array) $rows,
			'total' => $total,
			'pages' => $per_page > 0 ? (int) ceil( $total / $per_page ) : 0,
			'paged' => $paged,
		);
	}

	/**
	 * Image attachment IDs that have NOT been tracked yet (candidates for
	 * "Optimize All" / on-upload). A LEFT JOIN against our own small table
	 * — not against wp_postmeta — so it stays fast at any library size.
	 *
	 * @param  int $limit Max IDs to return, or -1 for unlimited.
	 * @return int[]
	 */
	public static function find_untracked_attachment_ids( $limit = -1 ) {
		global $wpdb;
		$table = self::table();

		$limit_sql = $limit > 0 ? $wpdb->prepare( 'LIMIT %d', (int) $limit ) : '';

		$sql = "SELECT p.ID FROM {$wpdb->posts} p
				LEFT JOIN $table t ON t.attachment_id = p.ID
				WHERE p.post_type = 'attachment'
				AND p.post_mime_type IN ('image/jpeg','image/png')
				AND p.post_status = 'inherit'
				AND t.attachment_id IS NULL
				ORDER BY p.ID ASC
				$limit_sql";

		return array_map( 'intval', (array) $wpdb->get_col( $sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * A random sample of untracked (not-yet-optimized) attachment IDs, used
	 * to estimate average file size across the whole remaining queue without
	 * having to stat every file on disk (expensive at 100k+ attachments).
	 *
	 * @param  int $sample_size Max IDs to return.
	 * @return int[]
	 */
	public static function sample_untracked_attachment_ids( $sample_size = 200 ) {
		global $wpdb;
		$table = self::table();

		return array_map( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			'intval',
			(array) $wpdb->get_col(
				$wpdb->prepare(
					"SELECT p.ID FROM {$wpdb->posts} p
					 LEFT JOIN $table t ON t.attachment_id = p.ID
					 WHERE p.post_type = 'attachment'
					 AND p.post_mime_type IN ('image/jpeg','image/png')
					 AND p.post_status = 'inherit'
					 AND t.attachment_id IS NULL
					 ORDER BY RAND()
					 LIMIT %d",
					(int) $sample_size
				)
			)
		);
	}

	/**
	 * Average compression ratio (optimized_size / original_size) across
	 * successfully-optimized rows that have size data — i.e. how much of
	 * the original size a WebP conversion actually kept, measured from this
	 * site's own real results rather than a generic assumption.
	 *
	 * @return float|null Ratio between 0 and 1, or null if no data yet.
	 */
	public static function avg_compression_ratio() {
		global $wpdb;
		$table = self::table();

		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			"SELECT SUM(original_size) AS total_original, SUM(original_size - reduction) AS total_optimized
			 FROM $table WHERE status = 'success' AND original_size > 0",
			ARRAY_A
		);

		if ( ! $row || empty( $row['total_original'] ) || (float) $row['total_original'] <= 0 ) {
			return null;
		}

		return max( 0, min( 1, (float) $row['total_optimized'] / (float) $row['total_original'] ) );
	}

	/**
	 * All tracked attachment IDs (for "Revert All").
	 *
	 * @return int[]
	 */
	public static function find_tracked_attachment_ids() {
		global $wpdb;
		$table = self::table();
		return array_map( 'intval', (array) $wpdb->get_col( "SELECT attachment_id FROM $table" ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}
}
