<?php
/**
 * BaseCloud Boost — Database Optimizer
 *
 * @package BaseCloud\Boost
 */

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

global $wpdb;

$options       = (array) get_option( BCBOOST_OPTIONS_KEY, [] );
$cleanup_sched = (string) ( $options['db_cleanup_schedule'] ?? 'off' );

$revisions   = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'" );
$auto_drafts = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'" );
$trash_posts = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'" );
$spam_cmts   = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'" );
$expired_tr  = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d", '_transient_timeout_%', time() ) );
$orphan_meta = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" );
?>
<div class="bcboost-wrap">
	<?php include __DIR__ . '/partials/header.php'; ?>

	<div class="bcboost-card bcboost-card--accent-purple">
		<h3><span class="bcboost-section-icon">🗄️</span> <?php esc_html_e( 'Database Cleanup', 'basecloud-boost' ); ?></h3>

		<div class="bcboost-notice bcboost-notice--info">
			<span>💡</span>
			<span><?php esc_html_e( 'Regular database cleanup improves query performance and reduces database size. Always backup before running cleanup operations.', 'basecloud-boost' ); ?></span>
		</div>

		<table class="bcboost-table" style="margin-top:16px;">
			<thead>
				<tr>
					<th><?php esc_html_e( 'Item', 'basecloud-boost' ); ?></th>
					<th><?php esc_html_e( 'Count', 'basecloud-boost' ); ?></th>
					<th><?php esc_html_e( 'Action', 'basecloud-boost' ); ?></th>
				</tr>
			</thead>
			<tbody>
				<tr>
					<td>
						<strong><?php esc_html_e( 'Post Revisions', 'basecloud-boost' ); ?></strong>
						<div class="bcboost-muted"><?php esc_html_e( 'Older versions of posts/pages', 'basecloud-boost' ); ?></div>
					</td>
					<td>
						<span class="bcboost-badge <?php echo $revisions > 0 ? 'bcboost-badge--warning' : 'bcboost-badge--success'; ?>">
							<?php echo number_format_i18n( $revisions ); ?>
						</span>
					</td>
					<td>
						<button class="bcboost-btn bcboost-btn--secondary bcboost-btn--small bcboost-cleanup-btn" data-action="revisions">
							<?php esc_html_e( 'Clean', 'basecloud-boost' ); ?>
						</button>
					</td>
				</tr>
				<tr>
					<td>
						<strong><?php esc_html_e( 'Auto-Drafts', 'basecloud-boost' ); ?></strong>
						<div class="bcboost-muted"><?php esc_html_e( 'Temporary unsaved post drafts', 'basecloud-boost' ); ?></div>
					</td>
					<td>
						<span class="bcboost-badge <?php echo $auto_drafts > 0 ? 'bcboost-badge--warning' : 'bcboost-badge--success'; ?>">
							<?php echo number_format_i18n( $auto_drafts ); ?>
						</span>
					</td>
					<td>
						<button class="bcboost-btn bcboost-btn--secondary bcboost-btn--small bcboost-cleanup-btn" data-action="auto_drafts">
							<?php esc_html_e( 'Clean', 'basecloud-boost' ); ?>
						</button>
					</td>
				</tr>
				<tr>
					<td>
						<strong><?php esc_html_e( 'Trashed Posts', 'basecloud-boost' ); ?></strong>
						<div class="bcboost-muted"><?php esc_html_e( 'Posts/pages in the trash', 'basecloud-boost' ); ?></div>
					</td>
					<td>
						<span class="bcboost-badge <?php echo $trash_posts > 0 ? 'bcboost-badge--warning' : 'bcboost-badge--success'; ?>">
							<?php echo number_format_i18n( $trash_posts ); ?>
						</span>
					</td>
					<td>
						<button class="bcboost-btn bcboost-btn--secondary bcboost-btn--small bcboost-cleanup-btn" data-action="trash">
							<?php esc_html_e( 'Clean', 'basecloud-boost' ); ?>
						</button>
					</td>
				</tr>
				<tr>
					<td>
						<strong><?php esc_html_e( 'Spam Comments', 'basecloud-boost' ); ?></strong>
						<div class="bcboost-muted"><?php esc_html_e( 'Comments marked as spam', 'basecloud-boost' ); ?></div>
					</td>
					<td>
						<span class="bcboost-badge <?php echo $spam_cmts > 0 ? 'bcboost-badge--danger' : 'bcboost-badge--success'; ?>">
							<?php echo number_format_i18n( $spam_cmts ); ?>
						</span>
					</td>
					<td>
						<button class="bcboost-btn bcboost-btn--secondary bcboost-btn--small bcboost-cleanup-btn" data-action="spam">
							<?php esc_html_e( 'Clean', 'basecloud-boost' ); ?>
						</button>
					</td>
				</tr>
				<tr>
					<td>
						<strong><?php esc_html_e( 'Expired Transients', 'basecloud-boost' ); ?></strong>
						<div class="bcboost-muted"><?php esc_html_e( 'Temporary data past expiry date', 'basecloud-boost' ); ?></div>
					</td>
					<td>
						<span class="bcboost-badge <?php echo $expired_tr > 0 ? 'bcboost-badge--warning' : 'bcboost-badge--success'; ?>">
							<?php echo number_format_i18n( $expired_tr ); ?>
						</span>
					</td>
					<td>
						<button class="bcboost-btn bcboost-btn--secondary bcboost-btn--small bcboost-cleanup-btn" data-action="transients">
							<?php esc_html_e( 'Clean', 'basecloud-boost' ); ?>
						</button>
					</td>
				</tr>
				<tr>
					<td>
						<strong><?php esc_html_e( 'Orphaned Post Meta', 'basecloud-boost' ); ?></strong>
						<div class="bcboost-muted"><?php esc_html_e( 'Meta rows with no parent post', 'basecloud-boost' ); ?></div>
					</td>
					<td>
						<span class="bcboost-badge <?php echo $orphan_meta > 0 ? 'bcboost-badge--warning' : 'bcboost-badge--success'; ?>">
							<?php echo number_format_i18n( $orphan_meta ); ?>
						</span>
					</td>
					<td>
						<button class="bcboost-btn bcboost-btn--secondary bcboost-btn--small bcboost-cleanup-btn" data-action="postmeta">
							<?php esc_html_e( 'Clean', 'basecloud-boost' ); ?>
						</button>
					</td>
				</tr>
			</tbody>
		</table>

		<div class="bcboost-actions-row" style="margin-top:20px;padding-top:16px;border-top:1px solid var(--bcboost-border);">
			<button class="bcboost-btn bcboost-btn--danger" id="bcboost-run-all-cleanup">
				<span class="dashicons dashicons-trash"></span>
				<?php esc_html_e( 'Clean Everything', 'basecloud-boost' ); ?>
			</button>
			<button class="bcboost-btn bcboost-btn--purple" id="bcboost-optimize-tables">
				<span class="dashicons dashicons-database-export"></span>
				<?php esc_html_e( 'Optimize Tables', 'basecloud-boost' ); ?>
			</button>
		</div>

		<div id="bcboost-db-feedback" class="bcboost-feedback" style="display:none;margin-top:14px;"></div>
	</div>

	<div class="bcboost-card bcboost-card--accent-green">
		<h3><span class="bcboost-section-icon">⏰</span> <?php esc_html_e( 'Automatic Cleanup', 'basecloud-boost' ); ?></h3>

		<form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>">
			<?php wp_nonce_field( 'BCBOOST_save_settings' ); ?>
			<input type="hidden" name="action" value="BCBOOST_save_settings">

			<div class="bcboost-field">
				<label class="bcboost-field-label" for="db_cleanup_schedule"><?php esc_html_e( 'Run Cleanup Automatically', 'basecloud-boost' ); ?></label>
				<span class="bcboost-field-desc"><?php esc_html_e( 'Schedule a safe, unattended cleanup of post revisions, auto-drafts, spam comments, expired transients and orphaned post meta. Trashed posts and table optimization are left out of the automatic run — use the buttons above for those.', 'basecloud-boost' ); ?></span>
				<select id="db_cleanup_schedule" name="db_cleanup_schedule" class="bcboost-select" style="margin-top:10px;">
					<?php
					$bcb_sched_opts = [
						'off'    => __( 'Off (manual only)', 'basecloud-boost' ),
						'daily'  => __( 'Daily', 'basecloud-boost' ),
						'weekly' => __( 'Weekly', 'basecloud-boost' ),
					];
					foreach ( $bcb_sched_opts as $bcb_v => $bcb_label ) :
						?>
						<option value="<?php echo esc_attr( $bcb_v ); ?>" <?php selected( $cleanup_sched, $bcb_v ); ?>><?php echo esc_html( $bcb_label ); ?></option>
					<?php endforeach; ?>
				</select>
			</div>

			<div class="bcboost-form-footer">
				<button type="submit" class="bcboost-btn bcboost-btn--primary">
					<span class="dashicons dashicons-saved"></span>
					<?php esc_html_e( 'Save Schedule', 'basecloud-boost' ); ?>
				</button>
			</div>
		</form>
	</div>
</div>
