<?php
/**
 * Database Model for the background mail queue.
 *
 * @package NimblyMail\SMTP
 */

namespace NimblyMail\SMTP\Queue;

defined( 'ABSPATH' ) || exit;

/**
 * Class Model
 *
 * Handles raw CRUD operations on nimblymail_queue. Mirrors the conventions
 * of Log\Model: static methods, prepared statements, allow-listed orderby,
 * and table names appended via concatenation.
 *
 * @since 1.0.0
 * @since 1.0.3 release_stuck_processing() now returns released rows'
 *              id/priority instead of a count, delete_orphaned_placeholders()
 *              was added to sweep abandoned pending placeholder rows, and
 *              reset_for_retry() was added as an atomic guarded reset for
 *              admin-triggered retries.
 * @since 1.0.5 find() now converts created_at/started_at/failed_at/next_attempt
 *              on returned rows to ISO-8601 strings with the site's UTC offset
 *              baked in.
 */
class Model {

    /**
     * Object cache group used for queue stats.
     *
     * Allows persistent-cache plugins (Redis, Memcached) to share counts
     * across requests; without persistent cache it still de-duplicates
     * count queries within a single request.
     *
     * @since 1.0.0
     *
     * @var string
     */
    private const CACHE_GROUP = 'nimblymail_queue';

    /**
     * Cache key for the count_by_status aggregate.
     *
     * @since 1.0.0
     *
     * @var string
     */
    private const CACHE_KEY_STATS = 'count_by_status';

    /**
     * Object cache TTL in seconds for the stats aggregate.
     *
     * @since 1.0.0
     *
     * @var int
     */
    private const CACHE_TTL_STATS = 30;

    /**
     * Get the table name.
     *
     * @since 1.0.0
     *
     * @return string
     */
    public static function get_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'nimblymail_queue';
    }

    /**
     * Invalidate the cached aggregate counts.
     *
     * Called from every mutation site so the stats endpoint and the drain
     * notice see fresh values immediately after enqueue, send, or delete.
     *
     * @since 1.0.0
     *
     * @return void
     */
    public static function flush_stats_cache(): void {
        wp_cache_delete( self::CACHE_KEY_STATS, self::CACHE_GROUP );
    }

    /**
     * Insert a queue entry.
     *
     * @since 1.0.0
     *
     * @param array $data Row data. Format inferred from value types.
     *
     * @return int Inserted ID, or 0 on failure.
     */
    public static function insert( array $data ): int {
        global $wpdb;

        $format = array_map(
            function ( $value ) {
                if ( is_int( $value ) ) {
                    return '%d';
                }
                if ( is_float( $value ) ) {
                    return '%f';
                }
                return '%s';
            },
            $data
        );

        $wpdb->insert( self::get_table(), $data, $format ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $id = (int) $wpdb->insert_id;
        if ( $id > 0 ) {
            self::flush_stats_cache();
        }
        return $id;
    }

    /**
     * Atomically claim a pending row for processing.
     *
     * Uses a guarded UPDATE so concurrent runners cannot double-claim the
     * same row: only the row whose status is still pending will flip to
     * processing. Callers must check the return value before doing work.
     *
     * @since 1.0.0
     *
     * @param int $id The queue row ID.
     *
     * @return bool True when this caller successfully claimed the row.
     */
    public static function claim( int $id ): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; user input bound via prepare().
        $rows = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$wpdb->prefix}nimblymail_queue SET status = 'processing', started_at = %s WHERE id = %d AND status = 'pending'",
                current_time( 'mysql', true ),
                $id
            )
        );

        $claimed = is_numeric( $rows ) && (int) $rows > 0;
        if ( $claimed ) {
            self::flush_stats_cache();
        }
        return $claimed;
    }

    /**
     * Atomically reset a row to pending so an admin-triggered retry can pick it up.
     *
     * Uses a guarded UPDATE, mirroring claim(): only rows not currently
     * 'processing' are affected, so this is the single source of truth for
     * whether the retry is safe. A caller that instead reads status via
     * find_one() and branches on it separately would leave a window between
     * that read and this write for a concurrent claim() to flip the row to
     * 'processing' — this method closes that window.
     *
     * @since 1.0.3
     *
     * @param int $id The queue row ID.
     *
     * @return bool True when this call reset the row (it was not 'processing').
     */
    public static function reset_for_retry( int $id ): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; user input bound via prepare().
        $rows = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$wpdb->prefix}nimblymail_queue SET status = 'pending', next_attempt = %s WHERE id = %d AND status <> 'processing'",
                current_time( 'mysql', true ),
                $id
            )
        );

        $reset = is_numeric( $rows ) && (int) $rows > 0;
        if ( $reset ) {
            self::flush_stats_cache();
        }
        return $reset;
    }

    /**
     * Update bookkeeping fields after a failed attempt that will be retried.
     *
     * Restores status to pending so a subsequent runner pass can re-claim
     * the row, and stores the next scheduled attempt time for bookkeeping
     * (queue admin UI sorting/display).
     *
     * @since 1.0.0
     * @since 1.0.3 Corrected the docblock: claim() does not filter on
     *              next_attempt, so retry timing is enforced solely by when
     *              Action Scheduler re-fires RUNNER_HOOK for this row — this
     *              column was never a defense-in-depth guard in code.
     *
     * @param int    $id           The queue row ID.
     * @param int    $attempts     The new attempt count.
     * @param string $next_attempt MySQL DATETIME (UTC) for the next retry.
     * @param string $error        Last error message.
     *
     * @return bool True on success.
     */
    public static function mark_failed_retry( int $id, int $attempts, string $next_attempt, string $error ): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            self::get_table(),
            array(
                'status'       => 'pending',
                'attempts'     => $attempts,
                'next_attempt' => $next_attempt,
                'last_error'   => $error,
            ),
            array( 'id' => $id ),
            array( '%s', '%d', '%s', '%s' ),
            array( '%d' )
        );

        if ( false !== $result ) {
            self::flush_stats_cache();
            return true;
        }
        return false;
    }

    /**
     * Mark a row as permanently failed after the final attempt.
     *
     * @since 1.0.0
     *
     * @param int    $id    The queue row ID.
     * @param string $error Final error message.
     *
     * @return bool True on success.
     */
    public static function mark_failed_permanent( int $id, string $error ): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            self::get_table(),
            array(
                'status'     => 'failed',
                'failed_at'  => current_time( 'mysql', true ),
                'last_error' => $error,
            ),
            array( 'id' => $id ),
            array( '%s', '%s', '%s' ),
            array( '%d' )
        );

        if ( false !== $result ) {
            self::flush_stats_cache();
            return true;
        }
        return false;
    }

    /**
     * Find a single queue row by ID.
     *
     * @since 1.0.0
     *
     * @param int $id The queue row ID.
     *
     * @return array|null
     */
    public static function find_one( int $id ): ?array {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $row = $wpdb->get_row(
            $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}nimblymail_queue WHERE id = %d", $id ),
            ARRAY_A
        );

        return $row ? $row : null;
    }

    /**
     * Find queue rows with filters and pagination.
     *
     * Mirrors Log\Model::find(). All user-supplied values pass through
     * $wpdb->prepare(); orderby and order are validated against allow-lists.
     *
     * created_at, started_at, failed_at, and next_attempt are stored as GMT
     * (see Manager::enqueue() and Model::claim()/reset_for_retry()/
     * mark_failed_permanent(), and Runner.php's gmdate() call ahead of
     * mark_failed_retry(), all of which write GMT values), so they are
     * converted to ISO-8601 strings with the site's UTC offset baked in
     * before being returned, mirroring Log\Model::find()'s created_at
     * conversion.
     *
     * @since 1.0.0
     * @since 1.0.5 Converts created_at/started_at/failed_at/next_attempt on returned rows via convert_row_timestamp().
     *
     * @param array $args Query arguments: page, per_page, status, priority, search, orderby, order.
     *
     * @return array { items: array, total: int }
     */
    public static function find( array $args = array() ): array {
        global $wpdb;

        $defaults = array(
            'page'     => 1,
            'per_page' => 20,
            'status'   => '',
            'priority' => '',
            'search'   => '',
            'orderby'  => 'created_at',
            'order'    => 'DESC',
        );

        $parsed = wp_parse_args( $args, $defaults );

        $clauses  = array( '1=1' );
        $bindings = array();

        $allowed_statuses = array( 'pending', 'processing', 'failed' );
        if ( ! empty( $parsed['status'] ) && in_array( $parsed['status'], $allowed_statuses, true ) ) {
            $clauses[]  = 'status = %s';
            $bindings[] = $parsed['status'];
        }

        // Skip when no priority filter requested. Both null (REST param absent)
        // and the empty string (default in $defaults) must be treated as
        // "no filter". Without the null check the clause was always applied
        // with $bindings[] = (int) null = 0, which silently filtered every
        // listing to priority=0 — no rows in any installation use that value.
        if ( null !== $parsed['priority'] && '' !== $parsed['priority'] ) {
            $clauses[]  = 'priority = %d';
            $bindings[] = (int) $parsed['priority'];
        }

        if ( ! empty( $parsed['search'] ) ) {
            $like       = '%' . $wpdb->esc_like( $parsed['search'] ) . '%';
            $clauses[]  = '(last_error LIKE %s OR hash LIKE %s)';
            $bindings[] = $like;
            $bindings[] = $like;
        }

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

        $allowed_orders = array( 'id', 'created_at', 'next_attempt', 'priority', 'attempts', 'status' );
        $orderby        = in_array( $parsed['orderby'], $allowed_orders, true ) ? $parsed['orderby'] : 'created_at';
        $order          = strtoupper( $parsed['order'] ) === 'ASC' ? 'ASC' : 'DESC';

        $per_page = max( 1, (int) $parsed['per_page'] );
        $offset   = ( max( 1, (int) $parsed['page'] ) - 1 ) * $per_page;

        // $where_sql contains only fixed clause fragments with %s/%d placeholders;
        // $orderby and $order are validated against allow-lists above.
        $count_sql  = "SELECT COUNT(id) FROM {$wpdb->prefix}nimblymail_queue WHERE {$where_sql}";
        $select_sql = "SELECT id, hash, priority, status, attempts, next_attempt, last_error, started_at, failed_at, created_at FROM {$wpdb->prefix}nimblymail_queue WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d, %d";

        if ( ! empty( $bindings ) ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause has only literal placeholder fragments; user data is in $bindings.
            $total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $bindings ) );
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input; WHERE is a literal "1=1".
            $total = (int) $wpdb->get_var( $count_sql );
        }

        $select_bindings = array_merge( $bindings, array( $offset, $per_page ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where_sql is literal placeholder fragments; $orderby/$order are allow-list validated; user data is in $select_bindings.
        $items = $wpdb->get_results( $wpdb->prepare( $select_sql, $select_bindings ), ARRAY_A );
        $items = is_array( $items ) ? $items : array();

        $items = array_map(
            static function ( array $item ): array {
                $item['created_at']   = self::convert_row_timestamp( $item['created_at'] ?? null );
                $item['started_at']   = self::convert_row_timestamp( $item['started_at'] ?? null );
                $item['failed_at']    = self::convert_row_timestamp( $item['failed_at'] ?? null );
                $item['next_attempt'] = self::convert_row_timestamp( $item['next_attempt'] ?? null );
                return $item;
            },
            $items
        );

        return array(
            'items' => $items,
            'total' => $total,
        );
    }

    /**
     * Convert a raw GMT DATETIME string to an ISO-8601 string carrying the
     * site's UTC offset.
     *
     * Mirrors Log\Model::convert_row_timestamp(). started_at and failed_at
     * are nullable columns (a row that hasn't started or failed yet has
     * NULL), so null/empty values pass through as null rather than being
     * converted into a garbage date.
     *
     * @since 1.0.5
     *
     * @param string|null $value Raw GMT DATETIME string, or null/empty when the column allows NULL.
     *
     * @return string|null ISO-8601 string with the site's UTC offset, or null when $value was null/empty.
     */
    private static function convert_row_timestamp( ?string $value ): ?string {
        return ( null === $value || '' === $value ) ? null : get_date_from_gmt( $value, 'c' );
    }

    /**
     * Delete a single queue row.
     *
     * @since 1.0.0
     *
     * @param int $id The queue row ID.
     *
     * @return bool True when the row was deleted.
     */
    public static function delete( int $id ): bool {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $deleted = false !== $wpdb->delete( self::get_table(), array( 'id' => $id ), array( '%d' ) );
        if ( $deleted ) {
            self::flush_stats_cache();
        }
        return $deleted;
    }

    /**
     * Delete multiple queue rows by ID.
     *
     * @since 1.0.0
     *
     * @param int[] $ids Row IDs.
     *
     * @return int Number of rows deleted.
     */
    public static function bulk_delete( array $ids ): int {
        global $wpdb;
        $ids = array_map( 'intval', $ids );
        if ( empty( $ids ) ) {
            return 0;
        }

        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders is built from a fixed-format integer count and contains only %d markers; values are bound via prepare().
        $count = (int) $wpdb->query(
            $wpdb->prepare( "DELETE FROM {$wpdb->prefix}nimblymail_queue WHERE id IN ({$placeholders})", $ids )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if ( $count > 0 ) {
            self::flush_stats_cache();
        }
        return $count;
    }

    /**
     * Get aggregate counts grouped by status.
     *
     * @since 1.0.0
     *
     * @return array{pending:int, processing:int, failed:int}
     */
    public static function count_by_status(): array {
        $cached = wp_cache_get( self::CACHE_KEY_STATS, self::CACHE_GROUP );
        if ( is_array( $cached ) ) {
            return $cached;
        }

        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Result cached via wp_cache_set immediately below.
        $result = $wpdb->get_results(
            "SELECT status, COUNT(id) AS count FROM {$wpdb->prefix}nimblymail_queue GROUP BY status",
            ARRAY_A
        );

        $stats = array(
            'pending'    => 0,
            'processing' => 0,
            'failed'     => 0,
        );

        foreach ( (array) $result as $row ) {
            $status = $row['status'];
            if ( isset( $stats[ $status ] ) ) {
                $stats[ $status ] = (int) $row['count'];
            }
        }

        wp_cache_set( self::CACHE_KEY_STATS, $stats, self::CACHE_GROUP, self::CACHE_TTL_STATS );

        return $stats;
    }

    /**
     * Release rows stuck in 'processing' for longer than the configured timeout.
     *
     * Returns them to status='pending' so the runner can pick them up again
     * on the next dispatch. The most common cause is a worker crash mid-send;
     * left alone, those rows would never re-fire.
     *
     * Known low-severity race: the returned list is the pre-UPDATE candidate
     * set, not the rows the UPDATE actually flipped, so an overlapping
     * Rotation run or a row that finalises between the SELECT and the UPDATE
     * can cause the caller to re-enqueue an Action Scheduler action for a row
     * it did not actually release. This is intentionally not tightened
     * further here: claim()'s own atomic guard means such an extra action is
     * a harmless no-op (the row is either already 'pending' with a fresh
     * action from the other run, or no longer 'pending' at all), and closing
     * the window precisely would require restructuring this SELECT-then-UPDATE
     * shape (e.g. per-row locking or a marker column), which is out of
     * proportion to the risk.
     *
     * @since 1.0.0
     * @since 1.0.3 Returns the released rows' id and priority (instead of a
     *              count) so the caller can re-enqueue an Action Scheduler
     *              action for each one — flipping the DB status alone does
     *              not make anything pick the row up again.
     *
     * @param int $minutes Age threshold in minutes. Values <= 0 are treated as no-op.
     *
     * @return array<int, array{id: int, priority: int}> Released rows.
     */
    public static function release_stuck_processing( int $minutes ): array {
        global $wpdb;

        if ( $minutes <= 0 ) {
            return array();
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; user input bound via prepare().
        $candidates = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, priority FROM {$wpdb->prefix}nimblymail_queue WHERE status = 'processing' AND started_at IS NOT NULL AND started_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d MINUTE)",
                $minutes
            ),
            ARRAY_A
        );

        $candidates = is_array( $candidates ) ? $candidates : array();
        if ( empty( $candidates ) ) {
            return array();
        }

        $ids          = array_map( 'intval', array_column( $candidates, 'id' ) );
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

        // Bumping `attempts` here is intentional: a worker that crashed
        // mid-send would otherwise be replayed forever with the same attempt
        // count, since the runner never reached the increment path. Counting
        // each release as a failed attempt lets the configured max-attempts
        // ceiling actually catch poison rows.
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders is composed of fixed %d markers built from an int-cast count; values are bound via prepare().
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$wpdb->prefix}nimblymail_queue SET status = 'pending', started_at = NULL, attempts = attempts + 1 WHERE status = 'processing' AND id IN ({$placeholders})",
                $ids
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

        self::flush_stats_cache();

        $released = array();
        foreach ( $candidates as $row ) {
            $released[] = array(
                'id'       => (int) $row['id'],
                'priority' => (int) $row['priority'],
            );
        }

        return $released;
    }

    /**
     * Delete permanently failed rows older than the retention window.
     *
     * Called by Log\Rotation. Returns the number of rows removed so the
     * caller can clean up matching staging directories.
     *
     * @since 1.0.0
     *
     * @param int $days Retention in days. Values <= 0 are treated as no-op.
     *
     * @return int[] Deleted row IDs (so callers can remove their staging dirs).
     */
    public static function delete_expired_failed( int $days ): array {
        global $wpdb;

        if ( $days <= 0 ) {
            return array();
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; user input bound via prepare().
        $ids = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}nimblymail_queue WHERE status = 'failed' AND failed_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)",
                $days
            )
        );

        $ids = array_map( 'intval', (array) $ids );

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

        self::bulk_delete( $ids );
        return $ids;
    }

    /**
     * Delete pending placeholder rows whose payload was never finalised.
     *
     * Manager::enqueue() inserts a placeholder row (status='pending',
     * payload='') before staging attachments and encrypting the payload; if
     * that later step throws (e.g. Encryption::encrypt() failure), the
     * placeholder is left behind with no payload and no Action Scheduler
     * action ever referencing it — nothing else can claim, retry, or clean
     * it up. An empty payload on a pending row is otherwise impossible once
     * enqueue() finishes normally, so it is a safe, unambiguous signal that
     * the row is abandoned once it is old enough that no in-flight request
     * could still be writing to it.
     *
     * @since 1.0.3
     *
     * @param int $hours Age threshold in hours. Values <= 0 are treated as no-op.
     *
     * @return int[] Deleted row IDs (so callers can remove their staging dirs).
     */
    public static function delete_orphaned_placeholders( int $hours ): array {
        global $wpdb;

        if ( $hours <= 0 ) {
            return array();
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is internal; user input bound via prepare().
        $ids = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}nimblymail_queue WHERE status = 'pending' AND payload = '' AND created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d HOUR)",
                $hours
            )
        );

        $ids = array_map( 'intval', (array) $ids );

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

        self::bulk_delete( $ids );
        return $ids;
    }
}
