<?php
/**
 * Image Boost — Database Manager
 */

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

class ImgBoost_DB {

    /**
     * Initialize database checks.
     */
    public static function init() {
        if (get_option('imgboost_db_version') !== IMGBOOST_VERSION) {
            self::create_tables();
        }
    }

    /**
     * Create custom table on activation.
     * 
     * @param bool $network_wide
     */
    public static function activate($network_wide = false) {
        if (is_multisite() && $network_wide) {
            $sites = get_sites();
            foreach ($sites as $site) {
                switch_to_blog($site->blog_id);
                self::create_tables();
                self::set_defaults();
                self::create_directories();
                restore_current_blog();
            }
        } else {
            self::create_tables();
            self::set_defaults();
            self::create_directories();
        }
    }

    /**
     * Helper to create needed directories.
     */
    private static function create_directories() {
        if (!file_exists(IMGBOOST_BACKUP_DIR)) {
            wp_mkdir_p(IMGBOOST_BACKUP_DIR);
            
            $htaccess = IMGBOOST_BACKUP_DIR . '.htaccess';
            if (!file_exists($htaccess)) {
                $htaccess_content  = "<IfModule mod_authz_core.c>\n";
                $htaccess_content .= "Require all denied\n";
                $htaccess_content .= "</IfModule>\n";
                $htaccess_content .= "<IfModule !mod_authz_core.c>\n";
                $htaccess_content .= "Order allow,deny\n";
                $htaccess_content .= "Deny from all\n";
                $htaccess_content .= "</IfModule>\n";
                $htaccess_content .= "Options -Indexes\n";
                // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
                global $wp_filesystem;
                if (empty($wp_filesystem)) {
                    require_once ABSPATH . 'wp-admin/includes/file.php';
                    WP_Filesystem();
                }
                $wp_filesystem->put_contents($htaccess, $htaccess_content, FS_CHMOD_FILE);
            }
            
            $index = IMGBOOST_BACKUP_DIR . 'index.php';
            if (!file_exists($index)) {
                    $wp_filesystem->put_contents($index, "<?php\n// Silence is golden.\n", FS_CHMOD_FILE);
            }
        }
    }

    /**
     * Deactivation cleanup (non-destructive).
     */
    public static function deactivate() {
        wp_clear_scheduled_hook('imgboost_process_queue');
        wp_clear_scheduled_hook('imgboost_daily_cleanup');
    }

    /**
     * Create the optimization log table.
     */
    private static function create_tables() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'imgboost_log';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            attachment_id BIGINT(20) UNSIGNED NOT NULL,
            file_path VARCHAR(500) NOT NULL,
            file_size_name VARCHAR(50) NOT NULL DEFAULT 'full',
            original_size BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            optimized_size BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            savings_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
            compression_level VARCHAR(20) NOT NULL DEFAULT 'pro',
            format_original VARCHAR(10) NOT NULL DEFAULT '',
            format_converted VARCHAR(10) DEFAULT NULL,
            webp_size BIGINT(20) UNSIGNED DEFAULT NULL,
            backup_path VARCHAR(500) DEFAULT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            error_message TEXT DEFAULT NULL,
            optimized_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_attachment (attachment_id),
            KEY idx_status (status),
            KEY idx_level (compression_level)
        ) $charset_collate;";

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

        update_option('imgboost_db_version', IMGBOOST_VERSION);
    }

    /**
     * Set default plugin options.
     */
    private static function set_defaults() {
        $defaults = [
            'imgboost_compression_level' => 'pro',
            'imgboost_auto_optimize'     => 1,
            'imgboost_webp_enabled'      => 1,
            'imgboost_max_width'         => 2560,
            'imgboost_strip_metadata'    => 1,
            'imgboost_backup_enabled'    => 1,
            'imgboost_backup_days'       => 30,
            'imgboost_lazy_loading'      => 1,
            'imgboost_convert_png'       => 1,
            'imgboost_resize_enabled'    => 1,
            'imgboost_batch_size'        => 5,
            'imgboost_total_savings'     => 0,
            'imgboost_total_optimized'   => 0,
        ];

        foreach ($defaults as $key => $value) {
            if (get_option($key) === false) {
                add_option($key, $value);
            }
        }
    }

    /**
     * Log an optimization result.
     *
     * @param array $data
     * @return int|false
     */
    public static function log_optimization($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'imgboost_log';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert($table, [
            'attachment_id'     => $data['attachment_id'],
            'file_path'         => $data['file_path'],
            'file_size_name'    => isset($data['size_name']) ? $data['size_name'] : 'full',
            'original_size'     => $data['original_size'],
            'optimized_size'    => $data['optimized_size'],
            'savings_percent'   => $data['savings_percent'],
            'compression_level' => $data['compression_level'],
            'format_original'   => $data['format_original'],
            'format_converted'  => isset($data['format_converted']) ? $data['format_converted'] : null,
            'webp_size'         => isset($data['webp_size']) ? $data['webp_size'] : null,
            'backup_path'       => isset($data['backup_path']) ? $data['backup_path'] : null,
            'status'            => 'optimized',
            'optimized_at'      => current_time('mysql'),
        ], [
            '%d', '%s', '%s', '%d', '%d', '%f', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s'
        ]);
        
        if ($result && isset($data['attachment_id'])) {
            wp_cache_delete('imgboost_stats_' . $data['attachment_id'], 'imgboost');
            wp_cache_delete('imgboost_total_stats', 'imgboost');
        }

        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Get optimization stats for an attachment.
     *
     * @param int $attachment_id
     * @return array|null
     */
    public static function get_attachment_stats($attachment_id) {
        global $wpdb;

        $cache_key = 'imgboost_stats_' . $attachment_id;
        $cached = wp_cache_get($cache_key, 'imgboost');
        if (false !== $cached) {
            return $cached;
        }

        $table = $wpdb->prefix . 'imgboost_log';
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $query = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}imgboost_log WHERE attachment_id = %d ORDER BY file_size_name ASC", $attachment_id);
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results($query, ARRAY_A);
        
        wp_cache_set($cache_key, $results, 'imgboost', 3600);
        
        return $results;
    }

    /**
     * Get total plugin statistics.
     *
     * @return array
     */
    public static function get_total_stats() {
        global $wpdb;
        
        $cache_key = 'imgboost_total_stats';
        $cached = wp_cache_get($cache_key, 'imgboost');
        if (false !== $cached) {
            return $cached;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $stats = $wpdb->get_row(
            "SELECT 
                COUNT(DISTINCT attachment_id) as total_images,
                SUM(original_size) as total_original,
                SUM(optimized_size) as total_optimized,
                SUM(original_size - optimized_size) as total_saved,
                AVG(savings_percent) as avg_savings
             FROM {$wpdb->prefix}imgboost_log 
             WHERE status = 'optimized'",
            ARRAY_A
        );

        if (!$stats || !$stats['total_images']) {
            $stats = [
                'total_images'    => 0,
                'total_original'  => 0,
                'total_optimized' => 0,
                'total_saved'     => 0,
                'avg_savings'     => 0,
            ];
        }
        
        wp_cache_set($cache_key, $stats, 'imgboost', 3600);

        return $stats;
    }

    /**
     * Delete optimization records for an attachment.
     *
     * @param int $attachment_id
     */
    public static function delete_attachment_records($attachment_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'imgboost_log';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete($table, ['attachment_id' => $attachment_id], ['%d']);
        
        wp_cache_delete('imgboost_stats_' . $attachment_id, 'imgboost');
        wp_cache_delete('imgboost_total_stats', 'imgboost');
    }
}
