<?php
/**
 * agent-toolbelt/cleanup-database (v1.1 spec §4, high risk, disabled by default).
 *
 * Deletes accumulated database clutter in well-defined categories. Content
 * categories go through core APIs (wp_delete_post / wp_delete_comment /
 * delete_expired_transients) so hooks fire and caches invalidate; only the
 * orphaned-meta categories use direct SQL — no core API exists for them — with
 * the derived-table wrapper MySQL requires for self-referencing DELETEs
 * (08 §M1 finding).
 */

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

if ( ! class_exists( 'ATB_Operation_Cleanup_Database' ) ) {
    class ATB_Operation_Cleanup_Database extends ATB_Operation
    {
        /**
         * Cleanup categories in processing order (v1.1 spec §4).
         */
        const CATEGORIES = array(
            'revisions',
            'auto_drafts',
            'trashed_posts',
            'spam_comments',
            'trashed_comments',
            'expired_transients',
            'orphaned_postmeta',
            'orphaned_commentmeta',
            'orphaned_termmeta',
            'orphaned_usermeta',
        );

        /**
         * Max post/comment deletions per category per call — these go through
         * core APIs (hooks, cache invalidation), so each one costs real work.
         */
        const POSTS_BATCH = 500;

        /**
         * Max direct-SQL rows per orphan category per call.
         */
        const ROWS_BATCH = 2000;

        /**
         * Auto-drafts younger than this stay: a fresh auto-draft can be an
         * editor's open "Add New" tab (mirrors core's 7-day auto-draft purge).
         */
        const AUTO_DRAFT_DAYS = 7;

        /**
         * Wall-time budget for a real run, in seconds. Deleting through core
         * APIs is unbounded work; agents re-call while `remaining` is non-zero.
         */
        const TIME_BUDGET = 20;

        public function slug()
        {
            return 'cleanup-database';
        }

        public function describe()
        {
            return array(
                'slug'        => $this->slug(),
                'risk'        => 'high',
                'summary'     => 'Deletes database clutter by category: post revisions, abandoned auto-drafts '
                    . '(older than 7 days), trashed posts, spam and trashed comments, expired transients, and '
                    . 'orphaned meta rows whose parent object is gone. Dry-run first to see per-category counts '
                    . 'and get the confirm token; pass the SAME categories input when executing. Deletions are '
                    . 'batched — repeat the dry-run/execute cycle while `remaining` is non-zero.',
                'annotations' => array(
                    'destructive' => true,
                    'idempotent'  => false,
                ),
                // Prefill for the admin Run panel (v1.4); agents may copy it too.
                'example_input' => array( 'categories' => array( 'revisions', 'expired_transients' ) ),
                'input_schema' => array(
                    'type'       => 'object',
                    'properties' => array(
                        'categories' => array(
                            'type'        => 'array',
                            'items'       => array(
                                'type' => 'string',
                                'enum' => self::CATEGORIES,
                            ),
                            'description' => 'Cleanup categories to run; omit for all of: '
                                . implode( ', ', self::CATEGORIES ) . '.',
                        ),
                    ),
                ),
            );
        }

        public function dry_run( $atb_input )
        {
            $atb_categories = $this->resolve_categories( $atb_input );
            if ( isset( $atb_categories['refused'] ) ) {
                return $this->output( false, true, $atb_categories );
            }

            $atb_counts = array();
            $atb_total  = 0;
            foreach ( $atb_categories as $atb_category ) {
                $atb_counts[ $atb_category ] = $this->count_category( $atb_category );
                $atb_total                  += $atb_counts[ $atb_category ];
            }

            return $this->output( false, true, array(
                'counts' => $atb_counts,
                'total'  => $atb_total,
            ) );
        }

        public function run( $atb_input )
        {
            $atb_categories = $this->resolve_categories( $atb_input );
            if ( isset( $atb_categories['refused'] ) ) {
                return $this->output( false, false, $atb_categories );
            }

            $atb_started       = microtime( true );
            $atb_deleted       = array();
            $atb_remaining     = array();
            $atb_failed        = 0;
            $atb_stopped_early = false;
            $atb_warnings      = array();

            foreach ( $atb_categories as $atb_category ) {
                if ( ( microtime( true ) - $atb_started ) > $this->time_budget() ) {
                    $atb_stopped_early = true;
                }

                if ( $atb_stopped_early ) {
                    // Out of time: stop deleting but keep reporting what's left.
                    $atb_deleted[ $atb_category ]   = 0;
                    $atb_remaining[ $atb_category ] = $this->count_category( $atb_category );
                    continue;
                }

                $atb_result = $this->delete_category( $atb_category, $atb_started );

                $atb_deleted[ $atb_category ]   = $atb_result['deleted'];
                $atb_remaining[ $atb_category ] = $this->count_category( $atb_category );
                $atb_failed                    += $atb_result['failed'];

                if ( $atb_result['stopped_early'] ) {
                    $atb_stopped_early = true;
                }
            }

            $atb_total_deleted   = array_sum( $atb_deleted );
            $atb_total_remaining = array_sum( $atb_remaining );

            if ( $atb_failed > 0 ) {
                $atb_warnings[] = $atb_failed . ' deletion(s) failed and were skipped';
            }
            if ( $atb_total_remaining > 0 ) {
                $atb_warnings[] = $atb_total_remaining . ' object(s) still remain; run the same cleanup again (dry-run first for a fresh confirm token) to continue';
            }

            return $this->output( $atb_total_deleted > 0, false, array(
                'deleted'       => $atb_deleted,
                'remaining'     => $atb_remaining,
                'total_deleted' => $atb_total_deleted,
                'stopped_early' => $atb_stopped_early,
            ), $atb_warnings );
        }

        /**
         * Validates and normalizes the requested categories. Omitted or empty
         * means all. Order is normalized to the canonical processing order.
         *
         * @param array $atb_input
         * @return array Category list, or { refused: true, reason: 'unknown_category', unknown: [...] }.
         */
        protected function resolve_categories( $atb_input )
        {
            if ( ! is_array( $atb_input ) || empty( $atb_input['categories'] ) ) {
                return self::CATEGORIES;
            }

            $atb_requested = $atb_input['categories'];
            if ( ! is_array( $atb_requested ) ) {
                return array(
                    'refused' => true,
                    'reason'  => 'unknown_category',
                    'error'   => 'categories must be an array of category names',
                );
            }

            $atb_requested = array_values( array_unique( array_map( 'strval', $atb_requested ) ) );
            $atb_unknown   = array_values( array_diff( $atb_requested, self::CATEGORIES ) );
            if ( ! empty( $atb_unknown ) ) {
                return array(
                    'refused' => true,
                    'reason'  => 'unknown_category',
                    'unknown' => $atb_unknown,
                );
            }

            return array_values( array_intersect( self::CATEGORIES, $atb_requested ) );
        }

        /**
         * Counts the rows/objects a category would delete. Counting queries
         * only — dry-run must never mutate.
         *
         * @param string $atb_category
         * @return int
         */
        protected function count_category( $atb_category )
        {
            global $wpdb;

            // The audit table lives in the plugin's own schema; every query below
            // is either constant SQL over core tables or fully prepared.
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            switch ( $atb_category ) {
                case 'revisions':
                    return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'" );

                case 'auto_drafts':
                    return (int) $wpdb->get_var( $wpdb->prepare(
                        "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft' AND post_date < DATE_SUB( NOW(), INTERVAL %d DAY )",
                        self::AUTO_DRAFT_DAYS
                    ) );

                case 'trashed_posts':
                    return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'" );

                case 'spam_comments':
                    return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'" );

                case 'trashed_comments':
                    return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'trash'" );

                case 'expired_transients':
                    return $this->count_expired_transients();

                default:
                    $atb_orphan = $this->orphan_map();
                    if ( ! isset( $atb_orphan[ $atb_category ] ) ) {
                        return 0;
                    }
                    $atb_map = $atb_orphan[ $atb_category ];
                    return (int) $wpdb->get_var(
                        "SELECT COUNT(*) FROM {$atb_map['table']} m LEFT JOIN {$atb_map['parent']} p ON p.{$atb_map['parent_pk']} = m.{$atb_map['fk']} WHERE p.{$atb_map['parent_pk']} IS NULL"
                    );
            }
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        /**
         * Counts expired transients (pairs, not rows): timeout options whose
         * value is in the past. Site transients live in wp_options only on
         * single site — on multisite they are in sitemeta, which core's
         * delete_expired_transients() handles on the main site only; the count
         * mirrors exactly what the delete below will touch on this site.
         *
         * @return int
         */
        protected function count_expired_transients()
        {
            global $wpdb;

            $atb_like      = $wpdb->esc_like( '_transient_timeout_' ) . '%';
            $atb_site_like = $wpdb->esc_like( '_site_transient_timeout_' ) . '%';

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            if ( is_multisite() ) {
                return (int) $wpdb->get_var( $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d",
                    $atb_like,
                    time()
                ) );
            }

            return (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->options} WHERE ( option_name LIKE %s OR option_name LIKE %s ) AND option_value < %d",
                $atb_like,
                $atb_site_like,
                time()
            ) );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        /**
         * Deletes one category's batch.
         *
         * @param string $atb_category
         * @param float  $atb_started  microtime(true) when the run began.
         * @return array { deleted: int, failed: int, stopped_early: bool }
         */
        protected function delete_category( $atb_category, $atb_started )
        {
            switch ( $atb_category ) {
                case 'revisions':
                case 'auto_drafts':
                case 'trashed_posts':
                    return $this->delete_posts( $atb_category, $atb_started );

                case 'spam_comments':
                case 'trashed_comments':
                    return $this->delete_comments( $atb_category, $atb_started );

                case 'expired_transients':
                    return $this->delete_expired_transients_forced();

                default:
                    return $this->delete_orphans( $atb_category );
            }
        }

        /**
         * Deletes a batch of posts through wp_delete_post( $id, true ) — hooks
         * fire, meta/term relationships and child objects are cleaned by core.
         *
         * @param string $atb_category
         * @param float  $atb_started
         * @return array { deleted: int, failed: int, stopped_early: bool }
         */
        protected function delete_posts( $atb_category, $atb_started )
        {
            global $wpdb;

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            switch ( $atb_category ) {
                case 'revisions':
                    $atb_ids = $wpdb->get_col( $wpdb->prepare(
                        "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'revision' ORDER BY ID ASC LIMIT %d",
                        self::POSTS_BATCH
                    ) );
                    break;
                case 'auto_drafts':
                    $atb_ids = $wpdb->get_col( $wpdb->prepare(
                        "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'auto-draft' AND post_date < DATE_SUB( NOW(), INTERVAL %d DAY ) ORDER BY ID ASC LIMIT %d",
                        self::AUTO_DRAFT_DAYS,
                        self::POSTS_BATCH
                    ) );
                    break;
                default: // trashed_posts
                    $atb_ids = $wpdb->get_col( $wpdb->prepare(
                        "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'trash' ORDER BY ID ASC LIMIT %d",
                        self::POSTS_BATCH
                    ) );
            }
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

            $atb_deleted = 0;
            $atb_failed  = 0;

            foreach ( array_map( 'intval', (array) $atb_ids ) as $atb_id ) {
                if ( ( microtime( true ) - $atb_started ) > $this->time_budget() ) {
                    return array( 'deleted' => $atb_deleted, 'failed' => $atb_failed, 'stopped_early' => true );
                }
                // A post already removed by a cascade (e.g. a revision whose
                // trashed parent was just deleted) counts as neither.
                if ( ! get_post( $atb_id ) ) {
                    continue;
                }
                if ( wp_delete_post( $atb_id, true ) ) {
                    $atb_deleted++;
                } else {
                    $atb_failed++;
                }
            }

            return array( 'deleted' => $atb_deleted, 'failed' => $atb_failed, 'stopped_early' => false );
        }

        /**
         * Deletes a batch of comments through wp_delete_comment( $id, true ).
         *
         * @param string $atb_category
         * @param float  $atb_started
         * @return array { deleted: int, failed: int, stopped_early: bool }
         */
        protected function delete_comments( $atb_category, $atb_started )
        {
            global $wpdb;

            $atb_status = ( 'spam_comments' === $atb_category ) ? 'spam' : 'trash';

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $atb_ids = $wpdb->get_col( $wpdb->prepare(
                "SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved = %s ORDER BY comment_ID ASC LIMIT %d",
                $atb_status,
                self::POSTS_BATCH
            ) );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

            $atb_deleted = 0;
            $atb_failed  = 0;

            foreach ( array_map( 'intval', (array) $atb_ids ) as $atb_id ) {
                if ( ( microtime( true ) - $atb_started ) > $this->time_budget() ) {
                    return array( 'deleted' => $atb_deleted, 'failed' => $atb_failed, 'stopped_early' => true );
                }
                if ( wp_delete_comment( $atb_id, true ) ) {
                    $atb_deleted++;
                } else {
                    $atb_failed++;
                }
            }

            return array( 'deleted' => $atb_deleted, 'failed' => $atb_failed, 'stopped_early' => false );
        }

        /**
         * Deletes expired transients via core's own bulk cleanup, forcing the
         * DB pass so rows stranded by a since-enabled object cache go too.
         * The deleted count is measured (before minus after) because core does
         * not report one.
         *
         * @return array { deleted: int, failed: int, stopped_early: bool }
         */
        protected function delete_expired_transients_forced()
        {
            $atb_before = $this->count_expired_transients();

            if ( function_exists( 'delete_expired_transients' ) ) {
                delete_expired_transients( true );
            }

            $atb_after = $this->count_expired_transients();

            return array(
                'deleted'       => max( 0, $atb_before - $atb_after ),
                'failed'        => 0,
                'stopped_early' => false,
            );
        }

        /**
         * Deletes a batch of orphaned meta rows. No core API exists for these;
         * the DELETE uses the derived-table wrapper (required by MySQL for
         * self-referencing deletes — 08 §M1) which also makes LIMIT possible.
         *
         * @param string $atb_category
         * @return array { deleted: int, failed: int, stopped_early: bool }
         */
        protected function delete_orphans( $atb_category )
        {
            global $wpdb;

            $atb_orphan = $this->orphan_map();
            if ( ! isset( $atb_orphan[ $atb_category ] ) ) {
                return array( 'deleted' => 0, 'failed' => 0, 'stopped_early' => false );
            }
            $atb_map = $atb_orphan[ $atb_category ];

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $atb_result = $wpdb->query( $wpdb->prepare(
                "DELETE FROM {$atb_map['table']} WHERE {$atb_map['pk']} IN (
                    SELECT {$atb_map['pk']} FROM (
                        SELECT m.{$atb_map['pk']} FROM {$atb_map['table']} m
                        LEFT JOIN {$atb_map['parent']} p ON p.{$atb_map['parent_pk']} = m.{$atb_map['fk']}
                        WHERE p.{$atb_map['parent_pk']} IS NULL
                        ORDER BY m.{$atb_map['pk']} ASC
                        LIMIT %d
                    ) AS atb_delete
                )",
                self::ROWS_BATCH
            ) );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

            return array(
                'deleted'       => is_numeric( $atb_result ) ? (int) $atb_result : 0,
                'failed'        => false === $atb_result ? 1 : 0,
                'stopped_early' => false,
            );
        }

        /**
         * Orphaned-meta table map: meta table, its primary key, the foreign key
         * pointing at the parent, and the parent table/key. Note usermeta's
         * primary key is umeta_id, not meta_id.
         *
         * @return array[]
         */
        protected function orphan_map()
        {
            global $wpdb;

            return array(
                'orphaned_postmeta'    => array(
                    'table'     => $wpdb->postmeta,
                    'pk'        => 'meta_id',
                    'fk'        => 'post_id',
                    'parent'    => $wpdb->posts,
                    'parent_pk' => 'ID',
                ),
                'orphaned_commentmeta' => array(
                    'table'     => $wpdb->commentmeta,
                    'pk'        => 'meta_id',
                    'fk'        => 'comment_id',
                    'parent'    => $wpdb->comments,
                    'parent_pk' => 'comment_ID',
                ),
                'orphaned_termmeta'    => array(
                    'table'     => $wpdb->termmeta,
                    'pk'        => 'meta_id',
                    'fk'        => 'term_id',
                    'parent'    => $wpdb->terms,
                    'parent_pk' => 'term_id',
                ),
                'orphaned_usermeta'    => array(
                    'table'     => $wpdb->usermeta,
                    'pk'        => 'umeta_id',
                    'fk'        => 'user_id',
                    'parent'    => $wpdb->users,
                    'parent_pk' => 'ID',
                ),
            );
        }

        /**
         * Wall-time budget in seconds; overridable in tests.
         *
         * @return int
         */
        protected function time_budget()
        {
            return self::TIME_BUDGET;
        }
    }
}