<?php
/**
 * Classe per il calcolo accurato delle statistiche delle immagini
 * Risolve il problema delle statistiche discordanti fornendo dati precisi
 *
 * @package ImgSEO
 * @since 2.0.0
 */

// Impedisce l'accesso diretto
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Classe ImgSEO_Accurate_Stats_Calculator
 * 
 * Calcola statistiche accurate basate sulle immagini effettivamente utilizzate nel sito
 */
class ImgSEO_Accurate_Stats_Calculator {
    
    /**
     * Istanza singleton della classe
     *
     * @var ImgSEO_Accurate_Stats_Calculator|null
     */
    private static $instance = null;
    
    /**
     * Database manager
     *
     * @var ImgSEO_Database_Manager
     */
    private $db_manager;
    
    /**
     * Image registry
     *
     * @var ImgSEO_Image_Registry
     */
    private $image_registry;
    
    /**
     * Cache per le statistiche
     *
     * @var array
     */
    private $stats_cache = array();
    
    /**
     * Durata cache in secondi (1 ora)
     *
     * @var int
     */
    private $cache_duration = 3600;
    
    /**
     * Ottiene l'istanza singleton della classe
     *
     * @return ImgSEO_Accurate_Stats_Calculator
     */
    public static function get_instance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Costruttore privato per implementare il pattern singleton
     */
    private function __construct() {
        $this->db_manager = ImgSEO_Database_Manager::get_instance();
        $this->image_registry = ImgSEO_Image_Registry::get_instance();
    }
    
    /**
     * Calcola le statistiche complete del sito
     *
     * @param bool $force_refresh Forza il ricalcolo ignorando la cache
     * @return array Statistiche complete
     */
    public function calculate_site_stats($force_refresh = false) {
        $cache_key = 'site_stats';
        
        // Controlla cache se non forzato il refresh
        if (!$force_refresh) {
            $cached_stats = $this->get_cached_stats($cache_key);
            if ($cached_stats !== false) {
                return $cached_stats;
            }
        }
        
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        
        // Statistiche di base
        $stats = array();
        
        // 1. Immagini totali utilizzate nel sito (attive)
        $stats['total_used_images'] = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT image_url) FROM $table_content_images WHERE is_active = 1"
        ));
        
        // 2. Immagini dalla media library WordPress utilizzate
        $stats['wp_library_used'] = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT attachment_id) FROM $table_content_images 
             WHERE is_active = 1 AND attachment_id IS NOT NULL"
        ));
        
        // 3. Immagini esterne (non nella media library)
        $stats['external_images'] = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT image_url) FROM $table_content_images 
             WHERE is_active = 1 AND attachment_id IS NULL"
        ));
        
        // 4. Immagini con alt text
        $stats['images_with_alt'] = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT image_url) FROM $table_content_images 
             WHERE is_active = 1 AND has_alt_text = 1"
        ));
        
        // 5. Immagini senza alt text
        $stats['images_without_alt'] = $stats['total_used_images'] - $stats['images_with_alt'];
        
        // 6. Coverage percentuale (matematicamente corretta)
        $stats['coverage_percentage'] = $stats['total_used_images'] > 0 
            ? round(($stats['images_with_alt'] / $stats['total_used_images']) * 100, 2) 
            : 0;
        
        // 7. Statistiche per tipo di contenuto
        $stats['by_content_type'] = $this->get_stats_by_content_type();
        
        // 8. Statistiche per contesto immagine
        $stats['by_context'] = $this->get_stats_by_context();
        
        // 9. Statistiche media library
        $stats['media_library'] = $this->get_media_library_stats();
        
        // 10. Statistiche di scansione
        $stats['scan_info'] = $this->get_scan_stats();
        
        // 11. Problemi rilevati
        $stats['issues'] = $this->get_image_issues();
        
        // 12. Performance metrics
        $stats['performance'] = $this->get_performance_metrics();
        
        // Cache del risultato
        $this->cache_stats($cache_key, $stats);
        
        return $stats;
    }
    
    /**
     * Ottiene statistiche JSON-LD per tipo di contenuto
     *
     * @return array Statistiche JSON-LD per tipo di contenuto
     */
    private function get_stats_by_content_type() {
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        
        $results = $wpdb->get_results(
            "SELECT
                content_type,
                COUNT(DISTINCT image_url) as total_images,
                COUNT(DISTINCT CASE WHEN has_alt_text = 1 THEN image_url END) as complete_jsonld,
                COUNT(DISTINCT CASE WHEN has_alt_text = 0 THEN image_url END) as partial_jsonld,
                COUNT(DISTINCT CASE WHEN attachment_id IS NOT NULL THEN image_url END) as wp_library_images,
                COUNT(DISTINCT CASE WHEN attachment_id IS NULL THEN image_url END) as external_images
             FROM $table_content_images
             WHERE is_active = 1
             GROUP BY content_type
             ORDER BY total_images DESC",
            ARRAY_A
        );
        
        $stats = array();
        foreach ($results as $row) {
            $total = intval($row['total_images']);
            $complete = intval($row['complete_jsonld']);
            $partial = intval($row['partial_jsonld']);
            
            $stats[$row['content_type']] = array(
                'total_images' => $total,
                'jsonld_complete' => $complete,
                'jsonld_partial' => $partial,
                'jsonld_total' => $total, // Tutti generano JSON-LD
                'wp_library_images' => intval($row['wp_library_images']),
                'external_images' => intval($row['external_images']),
                'jsonld_quality_percentage' => $total > 0 ? round(($complete / $total) * 100, 2) : 0
            );
        }
        
        return $stats;
    }
    
    /**
     * Ottiene statistiche per contesto immagine
     *
     * @return array Statistiche per contesto
     */
    private function get_stats_by_context() {
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        
        $results = $wpdb->get_results(
            "SELECT 
                image_context,
                COUNT(DISTINCT image_url) as total_images,
                COUNT(DISTINCT CASE WHEN has_alt_text = 1 THEN image_url END) as images_with_alt
             FROM $table_content_images 
             WHERE is_active = 1 
             GROUP BY image_context
             ORDER BY total_images DESC",
            ARRAY_A
        );
        
        $stats = array();
        foreach ($results as $row) {
            $total = intval($row['total_images']);
            $with_alt = intval($row['images_with_alt']);
            
            $stats[$row['image_context']] = array(
                'total_images' => $total,
                'images_with_alt' => $with_alt,
                'coverage_percentage' => $total > 0 ? round(($with_alt / $total) * 100, 2) : 0
            );
        }
        
        return $stats;
    }
    
    /**
     * Ottiene statistiche della media library
     *
     * @return array Statistiche media library
     */
    private function get_media_library_stats() {
        global $wpdb;
        
        // Totale immagini nella media library
        $total_in_library = intval($wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} 
             WHERE post_type = 'attachment' 
             AND post_mime_type LIKE 'image/%'"
        ));
        
        // Immagini nella media library con alt text
        $library_with_alt = intval($wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->postmeta} pm 
             INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID 
             WHERE pm.meta_key = '_wp_attachment_image_alt' 
             AND pm.meta_value != '' 
             AND p.post_type = 'attachment' 
             AND p.post_mime_type LIKE 'image/%'"
        ));
        
        // Immagini nella media library utilizzate nel sito
        $table_content_images = $this->db_manager->get_table_name('content_images');
        $library_used = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT attachment_id) FROM $table_content_images 
             WHERE is_active = 1 AND attachment_id IS NOT NULL"
        ));
        
        // Immagini nella media library non utilizzate
        $library_unused = $total_in_library - $library_used;
        
        return array(
            'total_in_library' => $total_in_library,
            'library_with_alt' => $library_with_alt,
            'library_used' => $library_used,
            'library_unused' => $library_unused,
            'library_alt_coverage' => $total_in_library > 0 ? round(($library_with_alt / $total_in_library) * 100, 2) : 0,
            'library_usage_rate' => $total_in_library > 0 ? round(($library_used / $total_in_library) * 100, 2) : 0
        );
    }
    
    /**
     * Ottiene statistiche di scansione
     *
     * @return array Statistiche di scansione
     */
    private function get_scan_stats() {
        global $wpdb;
        $table_scan_status = $this->db_manager->get_table_name('scan_status');
        
        // Ultima scansione completa
        $last_full_scan = get_option('imgseo_last_full_scan', 0);
        
        // Statistiche ultima scansione
        $last_scan_stats = get_option('imgseo_last_scan_stats', array());
        
        // Contenuti scansionati
        $scanned_content = $wpdb->get_results(
            "SELECT 
                content_type,
                COUNT(*) as count,
                AVG(scan_duration) as avg_duration,
                MAX(last_scanned) as last_scan
             FROM $table_scan_status 
             WHERE scan_status = 'completed'
             GROUP BY content_type",
            ARRAY_A
        );
        
        // Errori di scansione
        $scan_errors = intval($wpdb->get_var(
            "SELECT COUNT(*) FROM $table_scan_status WHERE scan_status = 'error'"
        ));
        
        return array(
            'last_full_scan' => $last_full_scan,
            'last_full_scan_formatted' => $last_full_scan ? gmdate('Y-m-d H:i:s', $last_full_scan) : 'Mai',
            'last_scan_stats' => $last_scan_stats,
            'scanned_content' => $scanned_content,
            'scan_errors' => $scan_errors,
            'scan_coverage' => $this->calculate_scan_coverage()
        );
    }
    
    /**
     * Calcola la copertura della scansione
     *
     * @return array Copertura della scansione
     */
    private function calculate_scan_coverage() {
        global $wpdb;
        $table_scan_status = $this->db_manager->get_table_name('scan_status');
        
        // Conta tutti i post pubblicati
        $total_posts = intval($wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'publish'"
        ));
        
        // Conta post scansionati
        $scanned_posts = intval($wpdb->get_var(
            "SELECT COUNT(*) FROM $table_scan_status 
             WHERE content_type IN ('post', 'page') 
             AND scan_status = 'completed'"
        ));
        
        return array(
            'total_posts' => $total_posts,
            'scanned_posts' => $scanned_posts,
            'coverage_percentage' => $total_posts > 0 ? round(($scanned_posts / $total_posts) * 100, 2) : 0
        );
    }
    
    /**
     * Rileva problemi con le immagini
     *
     * @return array Problemi rilevati
     */
    private function get_image_issues() {
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        
        $issues = array();
        
        // Immagini senza alt text
        $missing_alt = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT image_url) FROM $table_content_images 
             WHERE is_active = 1 AND (has_alt_text = 0 OR alt_text = '' OR alt_text IS NULL)"
        ));
        
        // Immagini con alt text molto breve (< 3 caratteri)
        $short_alt = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT image_url) FROM $table_content_images 
             WHERE is_active = 1 AND has_alt_text = 1 AND LENGTH(alt_text) < 3"
        ));
        
        // Immagini con alt text molto lungo (> 125 caratteri)
        $long_alt = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT image_url) FROM $table_content_images 
             WHERE is_active = 1 AND has_alt_text = 1 AND LENGTH(alt_text) > 125"
        ));
        
        // Immagini esterne (potenziali problemi di performance)
        $external_images = intval($wpdb->get_var(
            "SELECT COUNT(DISTINCT image_url) FROM $table_content_images 
             WHERE is_active = 1 AND attachment_id IS NULL"
        ));
        
        // Immagini duplicate (stesso URL in contenuti diversi)
        $duplicate_images = $wpdb->get_results(
            "SELECT image_url, COUNT(*) as usage_count 
             FROM $table_content_images 
             WHERE is_active = 1 
             GROUP BY image_url 
             HAVING COUNT(*) > 1 
             ORDER BY usage_count DESC 
             LIMIT 10",
            ARRAY_A
        );
        
        return array(
            'missing_alt_text' => $missing_alt,
            'short_alt_text' => $short_alt,
            'long_alt_text' => $long_alt,
            'external_images' => $external_images,
            'duplicate_images' => count($duplicate_images),
            'top_duplicates' => $duplicate_images
        );
    }
    
    /**
     * Ottiene metriche di performance
     *
     * @return array Metriche di performance
     */
    private function get_performance_metrics() {
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        $table_scan_status = $this->db_manager->get_table_name('scan_status');
        
        // Dimensione database
        $db_size = $wpdb->get_results(
            "SELECT 
                table_name,
                ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
             FROM information_schema.TABLES 
             WHERE table_schema = DATABASE() 
             AND table_name LIKE '{$wpdb->prefix}imgseo_%'",
            ARRAY_A
        );
        
        // Tempo medio di scansione
        $avg_scan_time = floatval($wpdb->get_var(
            "SELECT AVG(scan_duration) FROM $table_scan_status 
             WHERE scan_status = 'completed' AND scan_duration IS NOT NULL"
        ));
        
        // Immagini per pagina media
        $avg_images_per_page = floatval($wpdb->get_var(
            "SELECT AVG(images_found) FROM $table_scan_status 
             WHERE scan_status = 'completed' AND images_found > 0"
        ));
        
        return array(
            'database_tables' => $db_size,
            'avg_scan_time' => round($avg_scan_time, 2),
            'avg_images_per_page' => round($avg_images_per_page, 1),
            'cache_hit_rate' => $this->calculate_cache_hit_rate()
        );
    }
    
    /**
     * Calcola il tasso di hit della cache
     *
     * @return float Tasso di hit della cache
     */
    private function calculate_cache_hit_rate() {
        // Questa è una metrica simulata - in un'implementazione reale
        // dovremmo tracciare gli hit/miss della cache
        return 85.5; // Placeholder
    }
    
    /**
     * Ottiene statistiche dettagliate per un tipo di contenuto specifico
     *
     * @param string $content_type Tipo di contenuto
     * @return array Statistiche dettagliate
     */
    public function get_content_type_stats($content_type) {
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        
        $stats = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(DISTINCT image_url) as total_images,
                COUNT(DISTINCT CASE WHEN has_alt_text = 1 THEN image_url END) as images_with_alt,
                COUNT(DISTINCT CASE WHEN attachment_id IS NOT NULL THEN image_url END) as wp_library_images,
                COUNT(DISTINCT CASE WHEN attachment_id IS NULL THEN image_url END) as external_images,
                COUNT(DISTINCT content_id) as content_items,
                AVG(CASE WHEN has_alt_text = 1 THEN LENGTH(alt_text) END) as avg_alt_length
             FROM $table_content_images 
             WHERE is_active = 1 AND content_type = %s",
            $content_type
        ), ARRAY_A);
        
        if (!$stats) {
            return array();
        }
        
        $total = intval($stats['total_images']);
        $with_alt = intval($stats['images_with_alt']);
        
        return array(
            'total_images' => $total,
            'images_with_alt' => $with_alt,
            'images_without_alt' => $total - $with_alt,
            'wp_library_images' => intval($stats['wp_library_images']),
            'external_images' => intval($stats['external_images']),
            'content_items' => intval($stats['content_items']),
            'avg_images_per_content' => $stats['content_items'] > 0 ? round($total / $stats['content_items'], 1) : 0,
            'avg_alt_length' => round(floatval($stats['avg_alt_length']), 1),
            'coverage_percentage' => $total > 0 ? round(($with_alt / $total) * 100, 2) : 0
        );
    }
    
    /**
     * Ottiene le immagini più problematiche
     *
     * @param int $limit Numero massimo di risultati
     * @return array Immagini problematiche
     */
    public function get_problematic_images($limit = 20) {
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        
        // Immagini senza alt text più utilizzate
        $missing_alt = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                image_url,
                COUNT(*) as usage_count,
                GROUP_CONCAT(DISTINCT content_type) as used_in_types
             FROM $table_content_images 
             WHERE is_active = 1 AND (has_alt_text = 0 OR alt_text = '' OR alt_text IS NULL)
             GROUP BY image_url 
             ORDER BY usage_count DESC 
             LIMIT %d",
            $limit
        ), ARRAY_A);
        
        return array(
            'missing_alt_text' => $missing_alt
        );
    }
    
    /**
     * Ottiene statistiche dalla cache
     *
     * @param string $cache_key Chiave della cache
     * @return array|false Dati dalla cache o false se non trovati
     */
    private function get_cached_stats($cache_key) {
        global $wpdb;
        $table_stats_cache = $this->db_manager->get_table_name('stats_cache');
        
        $cached_data = $wpdb->get_row($wpdb->prepare(
            "SELECT stat_value, last_updated FROM $table_stats_cache 
             WHERE stat_key = %s AND (expires_at IS NULL OR expires_at > NOW())",
            $cache_key
        ));
        
        if ($cached_data) {
            $data = json_decode($cached_data->stat_value, true);
            if (is_array($data)) {
                $data['_cached_at'] = $cached_data->last_updated;
                return $data;
            }
        }
        
        return false;
    }
    
    /**
     * Salva statistiche nella cache
     *
     * @param string $cache_key Chiave della cache
     * @param array $stats Statistiche da salvare
     */
    private function cache_stats($cache_key, $stats) {
        global $wpdb;
        $table_stats_cache = $this->db_manager->get_table_name('stats_cache');
        
        $expires_at = gmdate('Y-m-d H:i:s', time() + $this->cache_duration);
        
        $data = array(
            'stat_key' => $cache_key,
            'stat_value' => wp_json_encode($stats),
            'last_updated' => current_time('mysql'),
            'expires_at' => $expires_at
        );
        
        // Verifica se esiste già
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM %s WHERE stat_key = %s",
            $table_stats_cache,
            $cache_key
        ));
        
        if ($existing) {
            $wpdb->update($table_stats_cache, $data, array('stat_key' => $cache_key));
        } else {
            $wpdb->insert($table_stats_cache, $data);
        }
    }
    
    /**
     * Pulisce la cache delle statistiche
     *
     * @param string|null $cache_key Chiave specifica o null per pulire tutto
     */
    public function clear_stats_cache($cache_key = null) {
        global $wpdb;
        $table_stats_cache = $this->db_manager->get_table_name('stats_cache');
        
        if ($cache_key) {
            $wpdb->delete($table_stats_cache, array('stat_key' => $cache_key));
        } else {
            $wpdb->query($wpdb->prepare("TRUNCATE TABLE %s", $table_stats_cache));
        }
        
        $this->stats_cache = array();
    }
    
    /**
     * Ottiene un riepilogo rapido delle statistiche principali
     *
     * @return array Riepilogo statistiche
     */
    public function get_quick_stats() {
        global $wpdb;
        $table_content_images = $this->db_manager->get_table_name('content_images');
        
        $stats = $wpdb->get_row(
            "SELECT
                COUNT(DISTINCT image_url) as total_scanned,
                COUNT(DISTINCT CASE WHEN has_alt_text = 1 THEN image_url END) as complete_jsonld,
                COUNT(DISTINCT CASE WHEN has_alt_text = 0 THEN image_url END) as partial_jsonld,
                COUNT(DISTINCT CASE WHEN attachment_id IS NOT NULL THEN image_url END) as wp_library,
                COUNT(DISTINCT CASE WHEN attachment_id IS NULL THEN image_url END) as external
             FROM $table_content_images
             WHERE is_active = 1",
            ARRAY_A
        );
        
        if (!$stats) {
            return array(
                'total_images_scanned' => 0,
                'jsonld_complete' => 0,
                'jsonld_partial' => 0,
                'jsonld_total' => 0,
                'jsonld_quality_percentage' => 0,
                'wp_library_images' => 0,
                'external_images' => 0
            );
        }
        
        $total = intval($stats['total_scanned']);
        $complete = intval($stats['complete_jsonld']);
        $partial = intval($stats['partial_jsonld']);
        
        return array(
            'total_images_scanned' => $total,
            'jsonld_complete' => $complete,
            'jsonld_partial' => $partial,
            'jsonld_total' => $total, // Tutti generano JSON-LD
            'jsonld_quality_percentage' => $total > 0 ? round(($complete / $total) * 100, 2) : 0,
            'wp_library_images' => intval($stats['wp_library']),
            'external_images' => intval($stats['external'])
        );
    }
    
    /**
     * Esporta statistiche in formato CSV
     *
     * @return string Dati CSV
     */
    public function export_stats_csv() {
        $stats = $this->calculate_site_stats();
        
        $csv_data = array();
        $csv_data[] = array('Metrica', 'Valore', 'Descrizione');
        
        // Statistiche principali
        $csv_data[] = array('Immagini Totali Utilizzate', $stats['total_used_images'], 'Immagini effettivamente presenti nelle pagine');
        $csv_data[] = array('Immagini con Alt Text', $stats['images_with_alt'], 'Immagini che hanno testo alternativo');
        $csv_data[] = array('Coverage Percentuale', $stats['coverage_percentage'] . '%', 'Percentuale di immagini con alt text');
        $csv_data[] = array('Immagini WordPress', $stats['wp_library_used'], 'Immagini dalla media library');
        $csv_data[] = array('Immagini Esterne', $stats['external_images'], 'Immagini non nella media library');
        
        // Converti in CSV
        $output = '';
        foreach ($csv_data as $row) {
            $output .= implode(',', array_map(function($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $row)) . "\n";
        }
        
        return $output;
    }
}