<?php

namespace FleekDash\API\Performance;

/**
 * Database Optimizer
 *
 * Handles database optimization tasks
 * Extracted from PerformanceController for better organization
 *
 * @package FleekDash\API\Performance
 * @since 2.2.0
 */
class DatabaseOptimizer
{
    private $wpdb;

    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
    }

    /**
     * Run database optimization tasks
     *
     * @param array $tasks Array of tasks to run
     * @param int $batch_size Batch size for cleanup operations
     * @return array Summary of operations performed
     */
    public function runOptimization($tasks, $batch_size = 500)
    {
        $summary = [
            'optimized_tables' => 0,
            'repaired_tables' => 0,
            'deleted_transients' => 0,
            'deleted_revisions' => 0,
            'deleted_auto_drafts' => 0,
            'deleted_spam_comments' => 0,
            'deleted_trash_posts' => 0,
            'deleted_trash_comments' => 0,
            'deleted_orphan_postmeta' => 0,
            'deleted_orphan_commentmeta' => 0,
            'deleted_orphan_term_relationships' => 0,
        ];

        // Optimize/Repair tables
        if (in_array('optimize_tables', $tasks, true) || in_array('repair_tables', $tasks, true)) {
            $table_summary = $this->optimizeAndRepairTables($tasks);
            $summary['optimized_tables'] = $table_summary['optimized'];
            $summary['repaired_tables'] = $table_summary['repaired'];
        }

        // Clean expired transients
        if (in_array('clean_expired_transients', $tasks, true)) {
            $summary['deleted_transients'] = $this->cleanExpiredTransients($batch_size);
        }

        // Post revisions
        if (in_array('delete_post_revisions', $tasks, true)) {
            $summary['deleted_revisions'] = $this->deletePostRevisions($batch_size);
        }

        // Auto drafts
        if (in_array('delete_auto_drafts', $tasks, true)) {
            $summary['deleted_auto_drafts'] = $this->deleteAutoDrafts($batch_size);
        }

        // Trash cleanup
        if (in_array('empty_trash', $tasks, true)) {
            $trash_summary = $this->emptyTrash($batch_size);
            $summary['deleted_trash_posts'] = $trash_summary['posts'];
            $summary['deleted_trash_comments'] = $trash_summary['comments'];
        }

        // Spam comments
        if (in_array('delete_spam_comments', $tasks, true)) {
            $summary['deleted_spam_comments'] = $this->deleteSpamComments($batch_size);
        }

        // Orphans cleanup
        if (in_array('cleanup_orphans', $tasks, true)) {
            $orphan_summary = $this->cleanupOrphans($batch_size);
            $summary['deleted_orphan_postmeta'] = $orphan_summary['postmeta'];
            $summary['deleted_orphan_commentmeta'] = $orphan_summary['commentmeta'];
            $summary['deleted_orphan_term_relationships'] = $orphan_summary['term_relationships'];
        }

        // Store status
        $this->setOptimizationStatus([
            'status' => 'completed',
            'ran_at' => \current_time('mysql'),
            'summary' => $summary,
        ]);

        return $summary;
    }

    /**
     * Optimize and repair database tables
     *
     * @param array $tasks Tasks array
     * @return array Counts of optimized and repaired tables
     */
    private function optimizeAndRepairTables($tasks)
    {
        $optimized = 0;
        $repaired = 0;

        $db_name = DB_NAME;
        $tables = $this->wpdb->get_col($this->wpdb->prepare(
            "SELECT table_name FROM information_schema.tables WHERE table_schema = %s",
            $db_name
        ));

        foreach ($tables as $table) {
            // Only WordPress tables
            if (strpos($table, $this->wpdb->prefix) !== 0) {
                continue;
            }

            if (in_array('repair_tables', $tasks, true)) {
                @$this->wpdb->query("REPAIR TABLE `{$table}`");
                $repaired++;
            }

            if (in_array('optimize_tables', $tasks, true)) {
                @$this->wpdb->query("OPTIMIZE TABLE `{$table}`");
                $optimized++;
            }
        }

        return ['optimized' => $optimized, 'repaired' => $repaired];
    }

    /**
     * Clean expired transients
     *
     * @param int $batch_size Batch size
     * @return int Number of transients deleted
     */
    private function cleanExpiredTransients($batch_size)
    {
        $options_table = $this->wpdb->prefix . 'options';
        $now = time();

        $expired_keys = $this->wpdb->get_col($this->wpdb->prepare(
            "SELECT REPLACE(option_name, '_transient_timeout_', '') as k 
             FROM {$options_table} 
             WHERE option_name LIKE '_transient_timeout_%' 
             AND option_value < %d 
             LIMIT %d",
            $now,
            $batch_size
        ));

        if (empty($expired_keys)) {
            return 0;
        }

        // Delete transient values
        $placeholders = implode(',', array_fill(0, count($expired_keys), '%s'));
        $transient_names = array_map(function($k) { return '_transient_' . $k; }, $expired_keys);
        
        $this->wpdb->query($this->wpdb->prepare(
            "DELETE FROM {$options_table} WHERE option_name IN ({$placeholders})",
            ...$transient_names
        ));

        // Delete timeout keys
        $timeout_names = array_map(function($k) { return '_transient_timeout_' . $k; }, $expired_keys);
        
        $this->wpdb->query($this->wpdb->prepare(
            "DELETE FROM {$options_table} WHERE option_name IN ({$placeholders})",
            ...$timeout_names
        ));

        return count($expired_keys);
    }

    /**
     * Delete post revisions
     *
     * @param int $batch_size Batch size
     * @return int Number of revisions deleted
     */
    private function deletePostRevisions($batch_size)
    {
        $posts_table = $this->wpdb->prefix . 'posts';
        
        return (int) $this->wpdb->query(
            "DELETE FROM {$posts_table} WHERE post_type='revision' LIMIT {$batch_size}"
        );
    }

    /**
     * Delete auto drafts
     *
     * @param int $batch_size Batch size
     * @return int Number of auto drafts deleted
     */
    private function deleteAutoDrafts($batch_size)
    {
        $posts_table = $this->wpdb->prefix . 'posts';
        
        return (int) $this->wpdb->query(
            "DELETE FROM {$posts_table} WHERE post_status='auto-draft' LIMIT {$batch_size}"
        );
    }

    /**
     * Empty trash (posts and comments older than 30 days)
     *
     * @param int $batch_size Batch size
     * @return array Posts and comments deleted counts
     */
    private function emptyTrash($batch_size)
    {
        $posts_table = $this->wpdb->prefix . 'posts';
        $comments_table = $this->wpdb->prefix . 'comments';

        $deleted_posts = (int) $this->wpdb->query(
            "DELETE FROM {$posts_table} 
             WHERE post_status='trash' 
             AND post_date < (NOW() - INTERVAL 30 DAY) 
             LIMIT {$batch_size}"
        );

        $deleted_comments = (int) $this->wpdb->query(
            "DELETE FROM {$comments_table} 
             WHERE comment_approved='trash' 
             AND comment_date < (NOW() - INTERVAL 30 DAY) 
             LIMIT {$batch_size}"
        );

        return [
            'posts' => $deleted_posts,
            'comments' => $deleted_comments,
        ];
    }

    /**
     * Delete spam comments
     *
     * @param int $batch_size Batch size
     * @return int Number of spam comments deleted
     */
    private function deleteSpamComments($batch_size)
    {
        $comments_table = $this->wpdb->prefix . 'comments';
        
        return (int) $this->wpdb->query(
            "DELETE FROM {$comments_table} WHERE comment_approved='spam' LIMIT {$batch_size}"
        );
    }

    /**
     * Cleanup orphaned meta and relationships
     *
     * @param int $batch_size Batch size
     * @return array Counts of orphans deleted
     */
    private function cleanupOrphans($batch_size)
    {
        $posts = $this->wpdb->prefix . 'posts';
        $postmeta = $this->wpdb->prefix . 'postmeta';
        $comments = $this->wpdb->prefix . 'comments';
        $commentmeta = $this->wpdb->prefix . 'commentmeta';
        $term_rel = $this->wpdb->prefix . 'term_relationships';

        $deleted_postmeta = (int) $this->wpdb->query(
            "DELETE pm FROM {$postmeta} pm 
             LEFT JOIN {$posts} p ON p.ID = pm.post_id 
             WHERE p.ID IS NULL 
             LIMIT {$batch_size}"
        );

        $deleted_commentmeta = (int) $this->wpdb->query(
            "DELETE cm FROM {$commentmeta} cm 
             LEFT JOIN {$comments} c ON c.comment_ID = cm.comment_id 
             WHERE c.comment_ID IS NULL 
             LIMIT {$batch_size}"
        );

        $deleted_term_rel = (int) $this->wpdb->query(
            "DELETE tr FROM {$term_rel} tr 
             LEFT JOIN {$posts} p ON p.ID = tr.object_id 
             WHERE p.ID IS NULL 
             LIMIT {$batch_size}"
        );

        return [
            'postmeta' => $deleted_postmeta,
            'commentmeta' => $deleted_commentmeta,
            'term_relationships' => $deleted_term_rel,
        ];
    }

    /**
     * Get optimization status
     *
     * @return array Status data
     */
    public function getOptimizationStatus()
    {
        $status = \get_transient('fleekdash_db_optimization_status');
        
        if (!$status || !is_array($status)) {
            return ['status' => 'idle'];
        }

        return $status;
    }

    /**
     * Set optimization status
     *
     * @param array $status Status data
     * @return bool Success status
     */
    private function setOptimizationStatus($status)
    {
        return \set_transient('fleekdash_db_optimization_status', $status, HOUR_IN_SECONDS);
    }

    /**
     * Format bytes to human-readable format (public version)
     *
     * @param int $bytes Bytes to format
     * @return string Formatted string
     */
    public function formatBytes($bytes)
    {
        $bytes = (int) $bytes;
        
        if ($bytes < 1024) {
            return $bytes . ' B';
        } elseif ($bytes < 1048576) {
            return round($bytes / 1024, 2) . ' KB';
        } elseif ($bytes < 1073741824) {
            return round($bytes / 1048576, 2) . ' MB';
        } else {
            return round($bytes / 1073741824, 2) . ' GB';
        }
    }
}

