<?php
/**
 * Reports — KPI aggregation + printable HTML report + archive + auto-cron.
 *
 * Builds a self-contained, print-to-PDF HTML report with 34 KPIs for any date
 * range (today, week, month, year, custom). Stored under
 * uploads/seoforge-reports/. Cron jobs: weekly report (Sun 06:00) + monthly
 * report (1st of month 06:00).
 *
 * Dependency-free: the report is plain HTML the browser prints to PDF, so the
 * plugin bundles no PDF library and runs on every install.
 *
 * @package SEOForge
 */

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

class Seoforge_Reports {

    /** Optionen-Schlüssel. */
    const OPT_AUTO_WEEKLY    = 'reports_auto_weekly';
    const OPT_AUTO_MONTHLY   = 'reports_auto_monthly';
    const OPT_AUTO_RECIPIENT = 'reports_auto_recipient';

    /** Cron-Hook-Namen. */
    const CRON_WEEKLY  = 'seoforge_report_weekly';
    const CRON_MONTHLY = 'seoforge_report_monthly';

    public static function register( Seoforge_Loader $loader ) {
        $loader->add_action( self::CRON_WEEKLY,  __CLASS__, 'cron_weekly' );
        $loader->add_action( self::CRON_MONTHLY, __CLASS__, 'cron_monthly' );
        $loader->add_action( 'init',             __CLASS__, 'maybe_schedule_cron' );
        $loader->add_action( 'admin_post_seoforge_generate_report', __CLASS__, 'handle_generate' );
        $loader->add_action( 'admin_post_seoforge_resend_report',   __CLASS__, 'handle_resend' );
        $loader->add_action( 'admin_post_seoforge_delete_report',   __CLASS__, 'handle_delete' );
        $loader->add_action( 'admin_post_seoforge_download_report', __CLASS__, 'handle_download' );
        $loader->add_action( 'admin_init', __CLASS__, 'maybe_migrate_filenames' );
    }

    /** Schedule weekly + monthly crons if missing. */
    public static function maybe_schedule_cron() {
        if ( ! wp_next_scheduled( self::CRON_WEEKLY ) ) {
            // Sonntag 06:00 UTC = Sonntag 08:00 Europe/Vienna im Sommer (CEST, UTC+2)
            // bzw. 07:00 im Winter (CET, UTC+1) — bleibt in beiden Fällen Sonntag.
            $next = strtotime( 'next sunday 06:00 UTC' );
            wp_schedule_event( $next, 'weekly', self::CRON_WEEKLY );
        }
        if ( ! wp_next_scheduled( self::CRON_MONTHLY ) ) {
            $next = strtotime( 'first day of next month 06:00 UTC' );
            wp_schedule_event( $next, 'monthly_seoforge', self::CRON_MONTHLY );
        }
    }

    /** Cron Sonntag: Wochenreport für KW-1. */
    public static function cron_weekly() {
        if ( ! Seoforge_Settings::get( self::OPT_AUTO_WEEKLY, true ) ) return;
        $end   = strtotime( 'last sunday 23:59:59 UTC' );
        $start = strtotime( '-6 days', strtotime( gmdate( 'Y-m-d 00:00:00', $end ) . ' UTC' ) );
        $file = self::generate_pdf( $start, $end, 'weekly', true );
        if ( $file ) self::maybe_email( $file, __( 'Weekly report', 'brainwerk-seo-suite' ), $start, $end );
    }

    /** Cron 1. des Monats: Vormonats-Report. */
    public static function cron_monthly() {
        if ( ! Seoforge_Settings::get( self::OPT_AUTO_MONTHLY, true ) ) return;
        $start = strtotime( 'first day of previous month 00:00:00 UTC' );
        $end   = strtotime( 'last day of previous month 23:59:59 UTC' );
        $file = self::generate_pdf( $start, $end, 'monthly', true );
        if ( $file ) self::maybe_email( $file, __( 'Monthly report', 'brainwerk-seo-suite' ), $start, $end );
    }

    /** Send a generated PDF to the configured recipient (optional). */
    private static function maybe_email( $file, $kind, $start, $end ) {
        $to = (string) Seoforge_Settings::get( self::OPT_AUTO_RECIPIENT, '' );
        if ( ! is_email( $to ) ) return;
        $subject = sprintf( '[Brainwerk SEO Suite] %s · %s – %s', $kind, wp_date( 'd.m.Y', $start ), wp_date( 'd.m.Y', $end ) );
        if ( class_exists( 'Seoforge_Notifications' ) ) {
            $body = Seoforge_Notifications::render_notice_html( array(
                'severity'  => 'success',
                'kicker'    => __( 'Report ready', 'brainwerk-seo-suite' ),
                'headline'  => sprintf( '%s · %s – %s', $kind, wp_date( 'd.m.Y', $start ), wp_date( 'd.m.Y', $end ) ),
                'message'   => __( 'Your scheduled report is ready. The full report is attached to this e-mail — open it and use your browser to print or save as PDF.', 'brainwerk-seo-suite' ),
                'cta_url'   => admin_url( 'admin.php?page=brainwerk-seo-suite&tab=reports' ),
                'cta_label' => __( 'Open reports', 'brainwerk-seo-suite' ),
            ) );
        } else {
            $body = sprintf( __( '<p>Anbei der %1$s vom <strong>%2$s</strong> bis <strong>%3$s</strong>.</p>', 'brainwerk-seo-suite' ), esc_html( $kind ), wp_date( 'd.m.Y', $start ), wp_date( 'd.m.Y', $end ) );
        }
        wp_mail( $to, $subject, $body, array( 'Content-Type: text/html; charset=UTF-8' ), array( $file ) );
    }

    /* ===================================================================
     * KPI-AGGREGATION
     * =================================================================== */

    /**
     * Aggregiert ALL 34 KPIs für einen Datumsbereich + Vergleichsperiode.
     */
    public static function aggregate( $start, $end ) {
        global $wpdb;
        $blog_id = Seoforge_Utils::current_blog_id();
        $tbl_v   = Seoforge_Utils::table( 'visits' );
        $tbl_s   = Seoforge_Utils::table( 'sessions' );
        $tbl_404 = Seoforge_Utils::table( '404' );

        $start_sql = gmdate( 'Y-m-d H:i:s', $start );
        $end_sql   = gmdate( 'Y-m-d H:i:s', $end );

        // Pre-Perioden-Bereich (gleiche Länge davor) für Δ-Vergleich
        $duration_sec = max( 1, $end - $start );
        $prev_end     = $start - 1;
        $prev_start   = $prev_end - $duration_sec;
        $prev_start_sql = gmdate( 'Y-m-d H:i:s', $prev_start );
        $prev_end_sql   = gmdate( 'Y-m-d H:i:s', $prev_end );

        // The %i identifiers below are plugin table names from Seoforge_Utils::table() (whitelist resolver, plugin-owned tables only);
        // they are bound through the %i identifier placeholder. Every user value below is bound through $wpdb->prepare() placeholders.
        // 1-7: Grund-Aggregate
        $views        = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s", $tbl_v, $blog_id, $start_sql, $end_sql ) );
        $views_prev   = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s", $tbl_v, $blog_id, $prev_start_sql, $prev_end_sql ) );
        $uniq         = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s", $tbl_v, $blog_id, $start_sql, $end_sql ) );
        $uniq_prev    = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s", $tbl_v, $blog_id, $prev_start_sql, $prev_end_sql ) );
        $sessions     = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT session_id) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s", $tbl_v, $blog_id, $start_sql, $end_sql ) );
        $sessions_prev= (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT session_id) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s", $tbl_v, $blog_id, $prev_start_sql, $prev_end_sql ) );
        $pps          = $sessions > 0 ? round( $views / $sessions, 2 ) : 0;
        $pps_prev     = $sessions_prev > 0 ? round( $views_prev / $sessions_prev, 2 ) : 0;
        $duration_avg = (int) $wpdb->get_var( $wpdb->prepare( "SELECT ROUND(AVG(duration_sec)) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND duration_sec > 0", $tbl_v, $blog_id, $start_sql, $end_sql ) );
        $duration_avg_prev = (int) $wpdb->get_var( $wpdb->prepare( "SELECT ROUND(AVG(duration_sec)) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND duration_sec > 0", $tbl_v, $blog_id, $prev_start_sql, $prev_end_sql ) );
        // Bounce: Sessions mit nur 1 PV
        $bounce_pct = 0;
        if ( $sessions > 0 ) {
            $bounce_sessions = (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM (SELECT session_id FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s GROUP BY session_id HAVING COUNT(*) = 1) t",
                $tbl_v, $blog_id, $start_sql, $end_sql
            ) );
            $bounce_pct = round( ( $bounce_sessions / $sessions ) * 100, 1 );
        }
        $bounce_pct_prev = 0;
        if ( $sessions_prev > 0 ) {
            $bs_prev = (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM (SELECT session_id FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s GROUP BY session_id HAVING COUNT(*) = 1) t",
                $tbl_v, $blog_id, $prev_start_sql, $prev_end_sql
            ) );
            $bounce_pct_prev = round( ( $bs_prev / $sessions_prev ) * 100, 1 );
        }
        $new_pct = $views > 0 ? round( ( (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND is_new_visitor = 1", $tbl_v, $blog_id, $start_sql, $end_sql ) ) / $views ) * 100, 1 ) : 0;

        // Human vs. bot: every other figure in this report filters is_bot = 0;
        // here we surface the bot side so the report can show data quality.
        $bot_hits  = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND is_bot = 1 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s", $tbl_v, $blog_id, $start_sql, $end_sql ) );
        $bot_total = $views + $bot_hits;
        $bot_pct   = $bot_total > 0 ? round( ( $bot_hits / $bot_total ) * 100, 1 ) : 0;

        // 8-12: Top 5 Pages
        $top_pages = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT url_path, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s GROUP BY url_path ORDER BY hits DESC LIMIT 10",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );

        // 13-16: Source-Mix
        $src_mix = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT source_type, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s GROUP BY source_type",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );
        $src_pct = array( 'direct' => 0, 'search' => 0, 'social' => 0, 'referral' => 0, 'internal_search' => 0, 'self' => 0, 'campaign' => 0 );
        foreach ( $src_mix as $s ) {
            if ( $views > 0 ) $src_pct[ $s['source_type'] ] = round( ( (int) $s['hits'] / $views ) * 100, 1 );
        }

        // 17-19: Device-Mix
        $dev_mix = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT device_type, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND device_type != '' GROUP BY device_type",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );
        $dev_pct = array( 'mobile' => 0, 'desktop' => 0, 'tablet' => 0 );
        $dev_total = array_sum( array_column( $dev_mix, 'hits' ) );
        foreach ( $dev_mix as $d ) {
            if ( $dev_total > 0 ) $dev_pct[ $d['device_type'] ] = round( ( (int) $d['hits'] / $dev_total ) * 100, 1 );
        }

        // 20-21: Top Browser + OS
        $top_browser = (string) $wpdb->get_var( $wpdb->prepare(
            "SELECT browser FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND browser != '' GROUP BY browser ORDER BY COUNT(*) DESC LIMIT 1",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ) );
        $top_os = (string) $wpdb->get_var( $wpdb->prepare(
            "SELECT os FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND os != '' GROUP BY os ORDER BY COUNT(*) DESC LIMIT 1",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ) );

        // 22-23: Peak Hour + Weekday
        $peak_hour = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT HOUR(created_at) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s GROUP BY HOUR(created_at) ORDER BY COUNT(*) DESC LIMIT 1",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ) );
        $peak_weekday = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT DAYOFWEEK(created_at) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s GROUP BY DAYOFWEEK(created_at) ORDER BY COUNT(*) DESC LIMIT 1",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ) );
        // DAYOFWEEK() returns 1=Sunday..7=Saturday; WP_Locale::get_weekday()
        // takes 0=Sunday..6=Saturday and returns the name in the site locale
        // (not hardcoded German).
        global $wp_locale;
        $weekday_names = array();
        for ( $i = 0; $i <= 6; $i++ ) {
            $weekday_names[ $i + 1 ] = ( isset( $wp_locale ) && is_object( $wp_locale ) && method_exists( $wp_locale, 'get_weekday' ) )
                ? $wp_locale->get_weekday( $i )
                : gmdate( 'l', strtotime( 'Sunday' ) + $i * DAY_IN_SECONDS );
        }

        // 24-25: 404-Hits, Scroll-Depth Median
        $hits_404 = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND status_code = 404 AND created_at BETWEEN %s AND %s",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ) );
        $hits_404_prev = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND status_code = 404 AND created_at BETWEEN %s AND %s",
            $tbl_v, $blog_id, $prev_start_sql, $prev_end_sql
        ) );
        $scroll_avg = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT ROUND(AVG(scroll_depth)) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND scroll_depth > 0",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ) );

        // 26-29: GA4-Style (Engagement, Conversions, CWV)
        $engaged_pct = $views > 0 ? round( ( (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s AND is_engaged = 1", $tbl_v, $blog_id, $start_sql, $end_sql ) ) / $views ) * 100, 1 ) : 0;
        $conversions = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND source_type = 'conversion' AND created_at BETWEEN %s AND %s",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ) );
        $conversions_prev = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND source_type = 'conversion' AND created_at BETWEEN %s AND %s",
            $tbl_v, $blog_id, $prev_start_sql, $prev_end_sql
        ) );
        $conv_rate = $views > 0 ? round( ( $conversions / $views ) * 100, 2 ) : 0;
        // Core Web Vitals — P75 (Google-Standard für „passing")
        $lcp_p75 = self::percentile( $tbl_v, 'lcp_ms', $blog_id, $start_sql, $end_sql, 75 );
        $cls_p75 = self::percentile( $tbl_v, 'cls_x1000', $blog_id, $start_sql, $end_sql, 75 );
        $inp_p75 = self::percentile( $tbl_v, 'inp_ms', $blog_id, $start_sql, $end_sql, 75 );
        // Core Web Vitals split by device — mobile is usually the bottleneck.
        $cwv_device = array();
        foreach ( array( 'mobile', 'desktop' ) as $dev ) {
            $cls_dev = self::percentile( $tbl_v, 'cls_x1000', $blog_id, $start_sql, $end_sql, 75, $dev );
            $cwv_device[ $dev ] = array(
                'lcp' => self::percentile( $tbl_v, 'lcp_ms', $blog_id, $start_sql, $end_sql, 75, $dev ),
                'inp' => self::percentile( $tbl_v, 'inp_ms', $blog_id, $start_sql, $end_sql, 75, $dev ),
                'cls' => $cls_dev ? round( $cls_dev / 1000, 3 ) : 0,
            );
        }

        // Top countries (geographic reach).
        $top_countries = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT country, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND country != '' AND created_at BETWEEN %s AND %s GROUP BY country ORDER BY hits DESC LIMIT 8",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );

        // 30-31: Top Landing + Exit Pages
        $top_landing = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT entry_url, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND started_at BETWEEN %s AND %s GROUP BY entry_url ORDER BY hits DESC LIMIT 5",
            $tbl_s, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );
        $top_exit = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT exit_url, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND started_at BETWEEN %s AND %s GROUP BY exit_url ORDER BY hits DESC LIMIT 5",
            $tbl_s, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );

        // 32-34: Outbound, Downloads, Internal-Search
        $outbound_top = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT referrer_host AS host, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND source_type = 'outbound' AND created_at BETWEEN %s AND %s GROUP BY referrer_host ORDER BY hits DESC LIMIT 5",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );
        $download_top = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT title, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND source_type = 'download' AND created_at BETWEEN %s AND %s GROUP BY title ORDER BY hits DESC LIMIT 5",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );
        $isearch_top = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT search_term, COUNT(*) AS hits FROM %i WHERE blog_id = %d AND source_type = 'internal_search' AND search_term != '' AND created_at BETWEEN %s AND %s GROUP BY search_term ORDER BY hits DESC LIMIT 5",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );

        // Daily Trend (für Chart auf Seite 2)
        $trend = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT DATE(created_at) AS day, COUNT(*) AS views FROM %i WHERE blog_id = %d AND is_bot = 0 AND source_type NOT IN ('outbound','download','conversion') AND created_at BETWEEN %s AND %s GROUP BY DATE(created_at) ORDER BY day",
            $tbl_v, $blog_id, $start_sql, $end_sql
        ), ARRAY_A );

        return array(
            'period'   => array( 'start' => $start, 'end' => $end, 'prev_start' => $prev_start, 'prev_end' => $prev_end ),
            'kpis' => array(
                'views'             => $views,             'views_prev'        => $views_prev,
                'uniq'              => $uniq,              'uniq_prev'         => $uniq_prev,
                'sessions'          => $sessions,          'sessions_prev'     => $sessions_prev,
                'pps'               => $pps,               'pps_prev'          => $pps_prev,
                'duration_avg'      => $duration_avg,      'duration_avg_prev' => $duration_avg_prev,
                'bounce_pct'        => $bounce_pct,        'bounce_pct_prev'   => $bounce_pct_prev,
                'new_pct'           => $new_pct,
                'engaged_pct'       => $engaged_pct,
                'conversions'       => $conversions,       'conversions_prev'  => $conversions_prev,
                'conv_rate'         => $conv_rate,
                'lcp_p75'           => $lcp_p75,
                'cls_p75'           => $cls_p75 ? round( $cls_p75 / 1000, 3 ) : 0,
                'inp_p75'           => $inp_p75,
                'hits_404'          => $hits_404,          'hits_404_prev'     => $hits_404_prev,
                'scroll_avg'        => $scroll_avg,
                'bot_hits'          => $bot_hits,
                'bot_pct'           => $bot_pct,
                'top_browser'       => $top_browser ?: '—',
                'top_os'            => $top_os ?: '—',
                'peak_hour'         => $peak_hour,
                'peak_weekday'      => $weekday_names[ $peak_weekday ] ?? '—',
            ),
            'src_pct'      => $src_pct,
            'dev_pct'      => $dev_pct,
            'top_pages'    => $top_pages,
            'top_landing'  => $top_landing,
            'top_exit'     => $top_exit,
            'outbound_top' => $outbound_top,
            'download_top' => $download_top,
            'isearch_top'  => $isearch_top,
            'trend'        => $trend,
            'top_countries'=> $top_countries,
            'cwv_device'   => $cwv_device,
        );
    }

    private static function percentile( $tbl, $col, $blog_id, $start_sql, $end_sql, $p = 75, $device = '' ) {
        global $wpdb;
        // The metric column is NEVER taken from a variable: each branch hard-codes
        // its column name as a literal. Unknown columns abort. Values (and the
        // table identifier via %i) are always bound through prepare(). An optional
        // device filter is bound via %s; each with/without-device path keeps its
        // own literal query string. $tbl comes from Seoforge_Utils::table()'s
        // fixed table whitelist.
        $has_dev = ( '' !== $device );
        switch ( $col ) {
            case 'lcp_ms':
                if ( $has_dev ) {
                    $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND device_type = %s AND lcp_ms IS NOT NULL AND lcp_ms > 0 AND created_at BETWEEN %s AND %s", $tbl, $blog_id, $device, $start_sql, $end_sql ) );
                    if ( $count === 0 ) return 0;
                    $offset = max( 0, (int) floor( $count * ( $p / 100 ) ) - 1 );
                    return (int) $wpdb->get_var( $wpdb->prepare( "SELECT lcp_ms FROM %i WHERE blog_id = %d AND device_type = %s AND lcp_ms IS NOT NULL AND lcp_ms > 0 AND created_at BETWEEN %s AND %s ORDER BY lcp_ms ASC LIMIT 1 OFFSET %d", $tbl, $blog_id, $device, $start_sql, $end_sql, $offset ) );
                }
                $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND lcp_ms IS NOT NULL AND lcp_ms > 0 AND created_at BETWEEN %s AND %s", $tbl, $blog_id, $start_sql, $end_sql ) );
                if ( $count === 0 ) return 0;
                $offset = max( 0, (int) floor( $count * ( $p / 100 ) ) - 1 );
                return (int) $wpdb->get_var( $wpdb->prepare( "SELECT lcp_ms FROM %i WHERE blog_id = %d AND lcp_ms IS NOT NULL AND lcp_ms > 0 AND created_at BETWEEN %s AND %s ORDER BY lcp_ms ASC LIMIT 1 OFFSET %d", $tbl, $blog_id, $start_sql, $end_sql, $offset ) );
            case 'cls_x1000':
                if ( $has_dev ) {
                    $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND device_type = %s AND cls_x1000 IS NOT NULL AND cls_x1000 > 0 AND created_at BETWEEN %s AND %s", $tbl, $blog_id, $device, $start_sql, $end_sql ) );
                    if ( $count === 0 ) return 0;
                    $offset = max( 0, (int) floor( $count * ( $p / 100 ) ) - 1 );
                    return (int) $wpdb->get_var( $wpdb->prepare( "SELECT cls_x1000 FROM %i WHERE blog_id = %d AND device_type = %s AND cls_x1000 IS NOT NULL AND cls_x1000 > 0 AND created_at BETWEEN %s AND %s ORDER BY cls_x1000 ASC LIMIT 1 OFFSET %d", $tbl, $blog_id, $device, $start_sql, $end_sql, $offset ) );
                }
                $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND cls_x1000 IS NOT NULL AND cls_x1000 > 0 AND created_at BETWEEN %s AND %s", $tbl, $blog_id, $start_sql, $end_sql ) );
                if ( $count === 0 ) return 0;
                $offset = max( 0, (int) floor( $count * ( $p / 100 ) ) - 1 );
                return (int) $wpdb->get_var( $wpdb->prepare( "SELECT cls_x1000 FROM %i WHERE blog_id = %d AND cls_x1000 IS NOT NULL AND cls_x1000 > 0 AND created_at BETWEEN %s AND %s ORDER BY cls_x1000 ASC LIMIT 1 OFFSET %d", $tbl, $blog_id, $start_sql, $end_sql, $offset ) );
            case 'inp_ms':
                if ( $has_dev ) {
                    $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND device_type = %s AND inp_ms IS NOT NULL AND inp_ms > 0 AND created_at BETWEEN %s AND %s", $tbl, $blog_id, $device, $start_sql, $end_sql ) );
                    if ( $count === 0 ) return 0;
                    $offset = max( 0, (int) floor( $count * ( $p / 100 ) ) - 1 );
                    return (int) $wpdb->get_var( $wpdb->prepare( "SELECT inp_ms FROM %i WHERE blog_id = %d AND device_type = %s AND inp_ms IS NOT NULL AND inp_ms > 0 AND created_at BETWEEN %s AND %s ORDER BY inp_ms ASC LIMIT 1 OFFSET %d", $tbl, $blog_id, $device, $start_sql, $end_sql, $offset ) );
                }
                $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE blog_id = %d AND inp_ms IS NOT NULL AND inp_ms > 0 AND created_at BETWEEN %s AND %s", $tbl, $blog_id, $start_sql, $end_sql ) );
                if ( $count === 0 ) return 0;
                $offset = max( 0, (int) floor( $count * ( $p / 100 ) ) - 1 );
                return (int) $wpdb->get_var( $wpdb->prepare( "SELECT inp_ms FROM %i WHERE blog_id = %d AND inp_ms IS NOT NULL AND inp_ms > 0 AND created_at BETWEEN %s AND %s ORDER BY inp_ms ASC LIMIT 1 OFFSET %d", $tbl, $blog_id, $start_sql, $end_sql, $offset ) );
            default:
                return 0;
        }
    }

    /* ===================================================================
     * HTML REPORT (dependency-free, print-to-PDF)
     * =================================================================== */

    /**
     * Build a self-contained, printable HTML report for a date range.
     *
     * Replaces the former tFPDF generator: the report is now a standalone
     * HTML document that any browser prints to PDF ("Save as PDF"), so the
     * plugin bundles no PDF library and works on every install. When $store
     * is true the file is written to uploads/seoforge-reports/ and its path
     * returned; with $store false the raw HTML string is returned.
     */
    public static function generate_report( $start, $end, $tag = 'custom', $store = true, $variant = 'screen' ) {
        $data = self::aggregate( $start, $end );
        $cfg  = array(
            'site_name' => get_bloginfo( 'name' ),
            'site_url'  => home_url( '/' ),
        );
        $html = self::render_html( $data, $cfg, $start, $end, $tag, $variant );

        if ( ! $store ) {
            return $html;
        }
        return self::store_report_file( $html, $start, $end, $tag, 'html' );
    }

    /**
     * Persist a generated report (an HTML string or raw PDF bytes) to the
     * uploads/seoforge-reports/ directory and return its absolute path.
     *
     * The .htaccess/index.php pair below only stops Apache and directory
     * listings — nginx (and IIS) serve any file under uploads/ directly,
     * with no involvement from WordPress or the admin-post download handler
     * at all. The only protection that holds on every stack is making the
     * filename itself unguessable, so it carries a random token that a path
     * can't be enumerated from (tag + date range alone used to be the whole
     * filename — trivially guessable for weekly/monthly reports).
     */
    private static function store_report_file( $contents, $start, $end, $tag, $ext = 'html' ) {
        $wp_filesystem = self::filesystem();
        $up  = wp_upload_dir();
        $dir = $up['basedir'] . '/seoforge-reports';
        if ( ! is_dir( $dir ) ) {
            wp_mkdir_p( $dir );
        }

        // .htaccess only protects Apache (and only with AllowOverride enabled);
        // nginx/IIS ignore it entirely. A blank index.php is a minimal
        // second layer everywhere: it stops directory listings and serves
        // nothing on its own, but does NOT block direct requests to report
        // files on non-Apache stacks — hosts without Apache-style per-directory
        // access rules should point the uploads dir at a deny rule in their
        // server config, or restrict PHP execution there.
        $ht = $dir . '/.htaccess';
        if ( ! file_exists( $ht ) ) {
            $wp_filesystem->put_contents( $ht, "Require all denied\n<IfModule !mod_authz_core.c>\nOrder deny,allow\nDeny from all\n</IfModule>\n", 0644 );
        }
        $index = $dir . '/index.php';
        if ( ! file_exists( $index ) ) {
            $wp_filesystem->put_contents( $index, "<?php\n// Silence is golden.\n", 0644 );
        }

        $fname = self::report_filename( $start, $end, $tag, $ext );
        $fpath = $dir . '/' . $fname;
        $wp_filesystem->put_contents( $fpath, $contents, 0640 );
        return $fpath;
    }

    /**
     * Build a report filename with an unguessable random component, so a
     * report can't be located by enumerating tag/date-range combinations
     * (see the note on store_report_file() above).
     */
    private static function report_filename( $start, $end, $tag, $ext ) {
        $token = bin2hex( random_bytes( 16 ) );
        return sprintf( 'report-%s-%s-%s-%s.%s', $tag, wp_date( 'Y-m-d', $start ), wp_date( 'Y-m-d', $end ), $token, $ext );
    }

    /**
     * Guarded one-shot (per site): renames any pre-1.8.1 report file still
     * sitting on disk under the old guessable name to carry a random token,
     * the same protection new reports already get. Runs once per site — the
     * option flag is a plain (non-network) option, so on Multisite each site
     * migrates its own uploads/seoforge-reports/ the first time an admin of
     * that site hits wp-admin, matching how the directory itself is already
     * per-site via wp_upload_dir().
     */
    public static function maybe_migrate_filenames() {
        if ( get_option( 'seoforge_reports_filename_migrated' ) ) {
            return;
        }
        self::migrate_report_filenames();
        update_option( 'seoforge_reports_filename_migrated', 1 );
    }

    /**
     * Renames old-scheme report files (report-{tag}-{start}-{end}.{ext}, no
     * random token) to the new report_filename() scheme. Idempotent: a file
     * whose name already ends in a 32-hex-char token is left alone, and
     * .htaccess/index.php never match the report-*.{html,pdf} glob below. A
     * rename that fails (e.g. filesystem permissions) is skipped silently —
     * the old file stays exactly where it was rather than being lost.
     */
    public static function migrate_report_filenames() {
        $up  = wp_upload_dir();
        $dir = $up['basedir'] . '/seoforge-reports';
        if ( ! is_dir( $dir ) ) {
            return;
        }
        $files = array_merge( (array) glob( $dir . '/report-*.html' ), (array) glob( $dir . '/report-*.pdf' ) );
        foreach ( array_filter( $files ) as $path ) {
            $name = basename( $path );
            $ext  = pathinfo( $name, PATHINFO_EXTENSION );
            $base = substr( $name, 0, - ( strlen( $ext ) + 1 ) );
            if ( preg_match( '/-[0-9a-f]{32}$/i', $base ) ) {
                continue; // already carries a token
            }
            $new_path = $dir . '/' . $base . '-' . bin2hex( random_bytes( 16 ) ) . '.' . $ext;
            if ( file_exists( $new_path ) ) {
                continue; // never overwrite an existing file
            }
            @rename( $path, $new_path );
        }
    }

    /**
     * Direct-write WP_Filesystem instance for the (server-local, non-user-
     * facing) reports directory. No credentials prompt: reports are written
     * from cron/admin-post context by the same user WordPress itself runs
     * as, same as core's own upload handling.
     */
    private static function filesystem() {
        global $wp_filesystem;
        if ( ! $wp_filesystem ) {
            if ( ! function_exists( 'WP_Filesystem' ) ) {
                require_once ABSPATH . 'wp-admin/includes/file.php';
            }
            WP_Filesystem();
        }
        return $wp_filesystem;
    }

    /**
     * Real-PDF entry point (and the backwards-compatible alias for the former
     * tFPDF generator). Renders a true PDF through the bundled Dompdf engine
     * when it is present (Pro build); otherwise — Free build, or on any render
     * failure — returns the self-contained printable HTML report, so cron
     * e-mails, downloads and third-party callers keep working everywhere.
     */
    public static function generate_pdf( $start, $end, $tag = 'custom', $store = true ) {
        if ( $store && class_exists( 'Seoforge_Pro_Pdf' ) && Seoforge_Pro_Pdf::available() ) {
            $html = self::generate_report( $start, $end, $tag, false, 'pdf' );
            $pdf  = Seoforge_Pro_Pdf::html_to_pdf( $html );
            if ( false !== $pdf ) {
                return self::store_report_file( $pdf, $start, $end, $tag, 'pdf' );
            }
        }
        return self::generate_report( $start, $end, $tag, $store );
    }

    private static function audit_text( $start, $end, $tag ) {
        return sprintf(
            /* translators: 1: report type/tag, 2: period start date, 3: period end date, 4: creation date/time, 5: site name, 6: plugin version */
            __( 'SEOForge Report · Type: %1$s · Period: %2$s – %3$s · Created: %4$s · Site: %5$s · v%6$s', 'brainwerk-seo-suite' ),
            $tag, wp_date( 'd.m.Y', $start ), wp_date( 'd.m.Y', $end ),
            wp_date( 'd.m.Y H:i', current_time( 'timestamp' ) ),
            get_bloginfo( 'name' ), SEOFORGE_VERSION
        );
    }

    /**
     * Assemble the full standalone HTML document. Returns an escaped, fully
     * self-contained string (embedded CSS, no external assets) so it renders
     * identically as a saved file, an e-mail attachment, or a print preview.
     */
    private static function render_html( $data, $cfg, $start, $end, $tag, $variant = 'screen' ) {
        $is_pdf  = ( 'pdf' === $variant );
        $k       = $data['kpis'];
        $heading = sprintf(
            '%s · %s – %s',
            $cfg['site_name'],
            wp_date( 'd.m.Y', $start ),
            wp_date( 'd.m.Y', $end )
        );
        $audit = self::audit_text( $start, $end, $tag );

        // Traffic sources (only non-zero shares).
        $src_labels = array(
            'direct'          => __( 'Direct', 'brainwerk-seo-suite' ),
            'search'          => __( 'Search engines', 'brainwerk-seo-suite' ),
            'social'          => __( 'Social media', 'brainwerk-seo-suite' ),
            'referral'        => __( 'Referral', 'brainwerk-seo-suite' ),
            'internal_search' => __( 'Site search', 'brainwerk-seo-suite' ),
            'self'            => __( 'Internal', 'brainwerk-seo-suite' ),
            'campaign'        => __( 'Campaign', 'brainwerk-seo-suite' ),
        );
        $src_items = array();
        foreach ( $src_labels as $key => $lbl ) {
            $pct = (float) ( $data['src_pct'][ $key ] ?? 0 );
            if ( $pct <= 0 ) {
                continue;
            }
            $src_items[] = array( 'label' => esc_html( $lbl ), 'pct' => $pct, 'extra' => $pct . ' %' );
        }

        // Device split.
        $dev_labels = array(
            'mobile'  => __( 'Mobile', 'brainwerk-seo-suite' ),
            'desktop' => __( 'Desktop', 'brainwerk-seo-suite' ),
            'tablet'  => __( 'Tablet', 'brainwerk-seo-suite' ),
        );
        $dev_items = array();
        foreach ( $dev_labels as $key => $lbl ) {
            $pct         = (float) ( $data['dev_pct'][ $key ] ?? 0 );
            $dev_items[] = array( 'label' => esc_html( $lbl ), 'pct' => $pct, 'extra' => $pct . ' %' );
        }

        $css = '
            :root{--navy:#1e3c64;--ink:#2b2f36;--mut:#6b7280;--line:#e5e7eb;--bg:#f7f9fc;--gold:#c8a24a}
            *{box-sizing:border-box}
            body{margin:0;background:#fff;color:var(--ink);font:14px/1.5 -apple-system,Segoe UI,Roboto,Helvetica,Arial,sans-serif}
            .sf-wrap{max-width:880px;margin:0 auto;padding:28px}
            .sf-top{border-bottom:3px solid var(--navy);padding-bottom:14px;margin-bottom:22px;display:flex;justify-content:space-between;align-items:flex-end;gap:16px}
            .sf-brand{font-size:22px;font-weight:800;color:var(--navy);letter-spacing:.2px}
            .sf-brand small{display:block;font-size:13px;font-weight:600;color:var(--gold);letter-spacing:.18em;text-transform:uppercase}
            .sf-sub{color:var(--mut);font-size:13px;text-align:right}
            .sf-print{background:var(--navy);color:#fff;border:0;border-radius:8px;padding:9px 16px;font-size:13px;font-weight:600;cursor:pointer}
            h2{color:var(--navy);font-size:16px;margin:26px 0 12px;border-left:4px solid var(--gold);padding-left:9px}
            h3{color:var(--navy);font-size:13px;margin:16px 0 6px}
            .sf-kpis{display:grid;grid-template-columns:repeat(4,1fr);gap:10px}
            .sf-kpi{border:1px solid var(--line);background:var(--bg);border-radius:10px;padding:11px 12px}
            .sf-kpi-l{font-size:11px;color:var(--mut);text-transform:uppercase;letter-spacing:.03em}
            .sf-kpi-v{font-size:20px;font-weight:700;color:var(--navy);margin-top:3px}
            .sf-kpi-d{font-size:11px;margin-top:2px}
            .sf-d-up{color:#1f8b4c}.sf-d-down{color:#cf3a2e}.sf-d-flat,.sf-d-new{color:var(--mut)}
            .sf-cols{display:grid;grid-template-columns:1fr 1fr;gap:26px}
            .sf-bars{margin:2px 0}
            .sf-bar{display:flex;align-items:center;gap:9px;margin:5px 0}
            .sf-bar-l{flex:0 0 130px;font-size:12px;color:var(--ink)}
            .sf-bar-t{flex:1;height:8px;background:#eef1f5;border-radius:5px;overflow:hidden}
            .sf-bar-f{display:block;height:8px;border-radius:5px}
            .sf-bar-v{flex:0 0 64px;text-align:right;font-size:12px;color:var(--mut)}
            .sf-flag{font-size:14px}
            .sf-trend{display:flex;align-items:flex-end;gap:2px;height:90px;border:1px solid var(--line);border-radius:8px;padding:8px;background:var(--bg)}
            .sf-tb{flex:1;min-width:2px;background:#5ba8fd;border-radius:2px 2px 0 0}
            .sf-trend-cap{font-size:11px;color:var(--mut);margin-top:5px}
            table.sf-tbl{width:100%;border-collapse:collapse;font-size:12px}
            table.sf-tbl th{text-align:left;color:var(--navy);border-bottom:2px solid var(--line);padding:5px 6px;font-size:11px;text-transform:uppercase}
            table.sf-tbl td{padding:5px 6px;border-bottom:1px solid #f0f2f5;color:var(--ink)}
            .sf-r{text-align:right}
            .sf-empty{color:var(--mut);font-style:italic;font-size:12px}
            .sf-foot{margin-top:28px;border-top:1px solid var(--line);padding-top:10px;color:#9aa1ab;font-size:10px}
            @media print{.sf-print{display:none}.sf-wrap{max-width:none;padding:0}body{font-size:12px}h2{margin-top:18px}}
        ';
        if ( $is_pdf ) {
            // Dompdf renders CSS2.1 (no grid/flex/vars) — swap in a table-based
            // stylesheet. The data and section content are identical; only the
            // layout containers differ (see the $is_pdf branches below).
            $css = self::pdf_css();
        }

        $html  = '<!DOCTYPE html><html lang="' . esc_attr( substr( get_locale(), 0, 2 ) ) . '"><head><meta charset="utf-8">';
        $html .= '<meta name="viewport" content="width=device-width,initial-scale=1">';
        $html .= '<title>' . esc_html( __( 'SEO report', 'brainwerk-seo-suite' ) . ' · ' . $heading ) . '</title>';
        $html .= '<style>' . $css . '</style></head><body><div class="sf-wrap">';

        // Header. Flex on screen; a two-cell table in the PDF (no print button).
        $brand = '<div class="sf-brand"><small>' . esc_html__( 'Brainwerk SEO Suite', 'brainwerk-seo-suite' ) . '</small>'
            . esc_html__( 'SEO report', 'brainwerk-seo-suite' ) . '</div>';
        $sub_text = esc_html( $heading ) . '<br>'
            . esc_html( sprintf( /* translators: %s: report type tag */ __( 'Type: %s', 'brainwerk-seo-suite' ), $tag ) );
        if ( $is_pdf ) {
            $html .= '<table class="sf-top"><tr><td>' . $brand . '</td>'
                . '<td class="sf-sub">' . $sub_text . '</td></tr></table>';
        } else {
            $html .= '<div class="sf-top">' . $brand
                . '<div class="sf-sub">' . $sub_text . ' · '
                . '<button class="sf-print" onclick="window.print()">' . esc_html__( 'Print / Save as PDF', 'brainwerk-seo-suite' ) . '</button>'
                . '</div></div>';
        }

        // KPI grid.
        $html .= '<h2>' . esc_html__( 'Key performance indicators', 'brainwerk-seo-suite' ) . '</h2>';
        $html .= self::kpi_cards( $k, $is_pdf );

        // Trend.
        $html .= '<h2>' . esc_html__( 'Pageview trend', 'brainwerk-seo-suite' ) . '</h2>';
        $html .= self::trend_block( $data['trend'], $is_pdf );

        // Sources + devices side by side.
        $html .= self::cols_open( $is_pdf ) . '<h2>' . esc_html__( 'Traffic sources', 'brainwerk-seo-suite' ) . '</h2>'
            . ( $src_items ? self::bar_list( $src_items, '#5ba8fd', $is_pdf ) : '<p class="sf-empty">' . esc_html__( 'No data.', 'brainwerk-seo-suite' ) . '</p>' )
            . self::cols_sep( $is_pdf ) . '<h2>' . esc_html__( 'Devices', 'brainwerk-seo-suite' ) . '</h2>'
            . self::bar_list( $dev_items, '#2e8b3e', $is_pdf ) . self::cols_close( $is_pdf );

        // Geographic reach (the #9 Geo block).
        $html .= '<h2>' . esc_html__( 'Top countries', 'brainwerk-seo-suite' ) . '</h2>';
        $html .= self::countries_block( $data['top_countries'], $is_pdf );

        // Detail tables.
        $html .= '<h2>' . esc_html__( 'Pages & engagement', 'brainwerk-seo-suite' ) . '</h2>';
        $html .= self::cols_open( $is_pdf );
        $html .= self::data_table( __( 'Top 10 pages', 'brainwerk-seo-suite' ), __( 'Path', 'brainwerk-seo-suite' ), $data['top_pages'], 'url_path', 'hits' );
        $html .= self::data_table( __( 'Top landing pages', 'brainwerk-seo-suite' ), __( 'Path', 'brainwerk-seo-suite' ), $data['top_landing'], 'entry_url', 'hits' );
        $html .= self::data_table( __( 'Top exit pages', 'brainwerk-seo-suite' ), __( 'Path', 'brainwerk-seo-suite' ), $data['top_exit'], 'exit_url', 'hits' );
        $html .= self::cols_sep( $is_pdf );
        $html .= self::data_table( __( 'Outbound clicks', 'brainwerk-seo-suite' ), __( 'Host', 'brainwerk-seo-suite' ), $data['outbound_top'], 'host', 'hits' );
        $html .= self::data_table( __( 'File downloads', 'brainwerk-seo-suite' ), __( 'File', 'brainwerk-seo-suite' ), $data['download_top'], 'title', 'hits' );
        $html .= self::data_table( __( 'Internal search terms', 'brainwerk-seo-suite' ), __( 'Term', 'brainwerk-seo-suite' ), $data['isearch_top'], 'search_term', 'hits' );
        $html .= self::cols_close( $is_pdf );

        // Footer.
        $html .= '<div class="sf-foot">' . esc_html( $audit ) . '</div>';
        $html .= '</div></body></html>';
        return $html;
    }

    /** Two-column layout wrappers: CSS grid on screen, a table for the PDF. */
    private static function cols_open( $is_pdf ) {
        return $is_pdf ? '<table class="sf-cols"><tr><td>' : '<div class="sf-cols"><div>';
    }
    private static function cols_sep( $is_pdf ) {
        return $is_pdf ? '</td><td>' : '</div><div>';
    }
    private static function cols_close( $is_pdf ) {
        return $is_pdf ? '</td></tr></table>' : '</div></div>';
    }

    /** KPI tile grid (19 indicators with period-over-period deltas). */
    private static function kpi_cards( $k, $is_pdf = false ) {
        $cards = array(
            array( __( 'Pageviews', 'brainwerk-seo-suite' ),        number_format_i18n( (int) $k['views'] ),      self::delta_html( $k['views'], $k['views_prev'] ) ),
            array( __( 'Unique visitors', 'brainwerk-seo-suite' ),  number_format_i18n( (int) $k['uniq'] ),       self::delta_html( $k['uniq'], $k['uniq_prev'] ) ),
            array( __( 'Sessions', 'brainwerk-seo-suite' ),         number_format_i18n( (int) $k['sessions'] ),   self::delta_html( $k['sessions'], $k['sessions_prev'] ) ),
            array( __( 'Pages / session', 'brainwerk-seo-suite' ),  esc_html( $k['pps'] ),                        self::delta_html( $k['pps'], $k['pps_prev'] ) ),
            array( __( 'Avg. time on site', 'brainwerk-seo-suite' ), esc_html( $k['duration_avg'] ) . '&nbsp;s',  self::delta_html( $k['duration_avg'], $k['duration_avg_prev'] ) ),
            array( __( 'Bounce rate', 'brainwerk-seo-suite' ),      esc_html( $k['bounce_pct'] ) . '&nbsp;%',     self::delta_html( $k['bounce_pct'], $k['bounce_pct_prev'], true ) ),
            array( __( 'New visitors', 'brainwerk-seo-suite' ),     esc_html( $k['new_pct'] ) . '&nbsp;%',        '' ),
            array( __( 'Engagement rate', 'brainwerk-seo-suite' ),  esc_html( $k['engaged_pct'] ) . '&nbsp;%',    '' ),
            array( __( 'Conversions', 'brainwerk-seo-suite' ),      number_format_i18n( (int) $k['conversions'] ), self::delta_html( $k['conversions'], $k['conversions_prev'] ) ),
            array( __( 'Conversion rate', 'brainwerk-seo-suite' ),  esc_html( $k['conv_rate'] ) . '&nbsp;%',      '' ),
            array( __( 'LCP (p75)', 'brainwerk-seo-suite' ),        $k['lcp_p75'] ? esc_html( $k['lcp_p75'] ) . '&nbsp;ms' : '—', '' ),
            array( __( 'CLS (p75)', 'brainwerk-seo-suite' ),        0 !== $k['cls_p75'] ? esc_html( $k['cls_p75'] ) : '—', '' ),
            array( __( 'INP (p75)', 'brainwerk-seo-suite' ),        $k['inp_p75'] ? esc_html( $k['inp_p75'] ) . '&nbsp;ms' : '—', '' ),
            array( __( '404 hits', 'brainwerk-seo-suite' ),         number_format_i18n( (int) $k['hits_404'] ),   self::delta_html( $k['hits_404'], $k['hits_404_prev'], true ) ),
            array( __( 'Avg. scroll depth', 'brainwerk-seo-suite' ), esc_html( $k['scroll_avg'] ) . '&nbsp;%',    '' ),
            array( __( 'Top browser', 'brainwerk-seo-suite' ),      esc_html( $k['top_browser'] ),                '' ),
            array( __( 'Top OS', 'brainwerk-seo-suite' ),           esc_html( $k['top_os'] ),                     '' ),
            array( __( 'Peak hour', 'brainwerk-seo-suite' ),        esc_html( $k['peak_hour'] ) . ':00',          '' ),
            array( __( 'Peak weekday', 'brainwerk-seo-suite' ),     esc_html( $k['peak_weekday'] ),               '' ),
        );
        $cell = function ( $c ) {
            return '<div class="sf-kpi-l">' . esc_html( $c[0] ) . '</div>'
                . '<div class="sf-kpi-v">' . $c[1] . '</div>'
                . ( '' !== $c[2] ? '<div class="sf-kpi-d">' . $c[2] . '</div>' : '' );
        };

        if ( $is_pdf ) {
            // Dompdf has no grid: lay the tiles out as a fixed 4-column table,
            // padding the final row so every row has four cells.
            $per_row = 4;
            $out     = '<table class="sf-kpi-tbl">';
            $total   = count( $cards );
            for ( $i = 0; $i < $total; $i += $per_row ) {
                $out .= '<tr>';
                for ( $j = 0; $j < $per_row; $j++ ) {
                    $idx  = $i + $j;
                    $out .= isset( $cards[ $idx ] ) ? '<td>' . $cell( $cards[ $idx ] ) . '</td>' : '<td class="sf-kpi-empty"></td>';
                }
                $out .= '</tr>';
            }
            return $out . '</table>';
        }

        $out = '<div class="sf-kpis">';
        foreach ( $cards as $c ) {
            $out .= '<div class="sf-kpi">' . $cell( $c ) . '</div>';
        }
        return $out . '</div>';
    }

    /** Coloured period-over-period delta badge. $invert: lower is better. */
    private static function delta_html( $cur, $prev, $invert = false ) {
        if ( 0 == $prev ) {
            return 0 != $cur
                ? '<span class="sf-d-new">— ' . esc_html__( 'new', 'brainwerk-seo-suite' ) . '</span>'
                : '';
        }
        $pct = round( ( ( $cur - $prev ) / $prev ) * 100, 1 );
        if ( 0.0 === (float) $pct ) {
            return '<span class="sf-d-flat">±0&nbsp;%</span>';
        }
        $up   = $pct > 0;
        $good = $invert ? ! $up : $up;
        $cls  = $good ? 'sf-d-up' : 'sf-d-down';
        return '<span class="' . $cls . '">' . ( $up ? '▲ +' : '▼ ' ) . esc_html( $pct ) . '&nbsp;%</span>';
    }

    /**
     * Horizontal labelled bar list. Labels in $items are expected pre-escaped
     * (so a country row may carry a flag span); the numeric extra is escaped
     * here.
     */
    private static function bar_list( $items, $color, $is_pdf = false ) {
        if ( $is_pdf ) {
            // Flex row → table row: label | track | value. The fill keeps its
            // percentage width inside the track cell (Dompdf handles width%).
            $out = '<table class="sf-bar-tbl">';
            foreach ( $items as $it ) {
                $pct  = max( 0, min( 100, (float) $it['pct'] ) );
                $out .= '<tr><td class="sf-bar-l">' . $it['label'] . '</td>'
                    . '<td class="sf-bar-t"><span class="sf-bar-f" style="width:' . esc_attr( $pct ) . '%;background:' . esc_attr( $color ) . '"></span></td>'
                    . '<td class="sf-bar-v">' . esc_html( $it['extra'] ) . '</td></tr>';
            }
            return $out . '</table>';
        }

        $out = '<div class="sf-bars">';
        foreach ( $items as $it ) {
            $pct  = max( 0, min( 100, (float) $it['pct'] ) );
            $out .= '<div class="sf-bar"><span class="sf-bar-l">' . $it['label'] . '</span>'
                . '<span class="sf-bar-t"><span class="sf-bar-f" style="width:' . esc_attr( $pct ) . '%;background:' . esc_attr( $color ) . '"></span></span>'
                . '<span class="sf-bar-v">' . esc_html( $it['extra'] ) . '</span></div>';
        }
        return $out . '</div>';
    }

    /** Country reach bars (the Geo block), ranked by hits. */
    private static function countries_block( $rows, $is_pdf = false ) {
        if ( empty( $rows ) ) {
            return '<p class="sf-empty">' . esc_html__( 'No country data in this period — enable visitor geolocation to populate this block.', 'brainwerk-seo-suite' ) . '</p>';
        }
        $max = 1;
        foreach ( $rows as $r ) {
            $max = max( $max, (int) $r['hits'] );
        }
        $items = array();
        foreach ( $rows as $r ) {
            $cc    = (string) $r['country'];
            $hits  = (int) $r['hits'];
            // Regional-indicator flag emoji don't render in the bundled PDF font;
            // show just the country code there.
            $flag  = ( ! $is_pdf && class_exists( 'Seoforge_Geo' ) ) ? Seoforge_Geo::flag( $cc ) : '';
            $label = ( '' !== $flag ? '<span class="sf-flag">' . esc_html( $flag ) . '</span> ' : '' ) . esc_html( $cc );
            $items[] = array( 'label' => $label, 'pct' => $hits / $max * 100, 'extra' => number_format_i18n( $hits ) );
        }
        return self::bar_list( $items, '#1e3c64', $is_pdf );
    }

    /** Mini column chart of daily pageviews. */
    private static function trend_block( $trend, $is_pdf = false ) {
        if ( empty( $trend ) ) {
            return '<p class="sf-empty">' . esc_html__( 'No data in this period.', 'brainwerk-seo-suite' ) . '</p>';
        }
        $max = 1;
        foreach ( $trend as $t ) {
            $max = max( $max, (int) $t['views'] );
        }

        if ( $is_pdf ) {
            // Flex column chart → one-row table; each day is a bottom-aligned
            // cell whose bar height is given in px (Dompdf ignores % heights).
            $cells = '';
            foreach ( $trend as $t ) {
                $px     = max( 2, (int) round( (int) $t['views'] / $max * 66 ) ); // fit the ~72px cell
                $cells .= '<td><span class="sf-tb" style="height:' . esc_attr( $px ) . 'px"></span></td>';
            }
            $chart = '<table class="sf-trend-tbl"><tr>' . $cells . '</tr></table>';
            return $chart . '<div class="sf-trend-cap">'
                . esc_html( sprintf( /* translators: 1: number of days, 2: peak daily views */ __( '%1$d days · peak %2$s views/day', 'brainwerk-seo-suite' ), count( $trend ), number_format_i18n( $max ) ) )
                . '</div>';
        }

        $bars = '';
        foreach ( $trend as $t ) {
            $h     = max( 2, (int) round( (int) $t['views'] / $max * 100 ) );
            $bars .= '<span class="sf-tb" style="height:' . esc_attr( $h ) . '%" title="' . esc_attr( $t['day'] . ': ' . $t['views'] ) . '"></span>';
        }
        return '<div class="sf-trend">' . $bars . '</div><div class="sf-trend-cap">'
            . esc_html( sprintf( /* translators: 1: number of days, 2: peak daily views */ __( '%1$d days · peak %2$s views/day', 'brainwerk-seo-suite' ), count( $trend ), number_format_i18n( $max ) ) )
            . '</div>';
    }

    /** Two-column "label / hits" detail table. */
    private static function data_table( $title, $label_head, $rows, $key, $valkey ) {
        $out = '<h3>' . esc_html( $title ) . '</h3>';
        if ( empty( $rows ) ) {
            return $out . '<p class="sf-empty">' . esc_html__( 'No data.', 'brainwerk-seo-suite' ) . '</p>';
        }
        $out .= '<table class="sf-tbl"><thead><tr><th>' . esc_html( $label_head )
            . '</th><th class="sf-r">' . esc_html__( 'Hits', 'brainwerk-seo-suite' ) . '</th></tr></thead><tbody>';
        foreach ( $rows as $row ) {
            $label = (string) ( $row[ $key ] ?? '' );
            if ( '' === $label ) {
                $label = '—';
            }
            $val  = (string) ( $row[ $valkey ] ?? '' );
            $out .= '<tr><td>' . esc_html( mb_substr( $label, 0, 80 ) ) . '</td><td class="sf-r">' . esc_html( $val ) . '</td></tr>';
        }
        return $out . '</tbody></table>';
    }

    /**
     * Print-oriented stylesheet for the PDF variant. CSS2.1 only (no grid,
     * flex or custom properties) so Dompdf renders it faithfully; layout is
     * carried by tables emitted in the $is_pdf branches above. Uses the
     * bundled DejaVu Sans so German umlauts render correctly.
     */
    private static function pdf_css() {
        return '
            body{margin:0;color:#2b2f36;font-family:"DejaVu Sans",sans-serif;font-size:11px;line-height:1.45}
            .sf-wrap{padding:0}
            table.sf-top{width:100%;border-collapse:collapse;border-bottom:3px solid #1e3c64;margin-bottom:16px}
            table.sf-top td{vertical-align:bottom;padding-bottom:10px}
            .sf-brand{font-size:18px;font-weight:bold;color:#1e3c64}
            .sf-brand small{display:block;font-size:10px;font-weight:bold;color:#c8a24a;letter-spacing:2px;text-transform:uppercase}
            .sf-sub{color:#6b7280;font-size:11px;text-align:right}
            h2{color:#1e3c64;font-size:14px;margin:18px 0 8px;border-left:4px solid #c8a24a;padding-left:8px}
            h3{color:#1e3c64;font-size:12px;margin:12px 0 5px}
            table.sf-kpi-tbl{width:100%;border-collapse:separate;border-spacing:5px}
            table.sf-kpi-tbl td{width:25%;border:1px solid #e5e7eb;background:#f7f9fc;border-radius:8px;padding:7px 8px;vertical-align:top}
            table.sf-kpi-tbl td.sf-kpi-empty{border:0;background:transparent}
            .sf-kpi-l{font-size:9px;color:#6b7280;text-transform:uppercase}
            .sf-kpi-v{font-size:16px;font-weight:bold;color:#1e3c64}
            .sf-kpi-d{font-size:9px}
            .sf-d-up{color:#1f8b4c}.sf-d-down{color:#cf3a2e}.sf-d-flat,.sf-d-new{color:#6b7280}
            table.sf-cols{width:100%;border-collapse:separate;border-spacing:18px 0}
            table.sf-cols td{vertical-align:top;width:50%}
            table.sf-bar-tbl{width:100%;border-collapse:collapse}
            table.sf-bar-tbl td{padding:3px 0;vertical-align:middle}
            td.sf-bar-l{width:130px;font-size:11px;color:#2b2f36}
            td.sf-bar-v{width:60px;text-align:right;font-size:11px;color:#6b7280}
            td.sf-bar-t{background:#eef1f5;border-radius:4px}
            .sf-bar-f{display:block;height:8px;border-radius:4px}
            table.sf-trend-tbl{width:100%;height:72px;border:1px solid #e5e7eb;background:#f7f9fc;border-collapse:collapse}
            table.sf-trend-tbl td{vertical-align:bottom;padding:0 1px}
            .sf-tb{display:block;background:#5ba8fd}
            .sf-trend-cap{font-size:10px;color:#6b7280;margin-top:4px}
            table.sf-tbl{width:100%;border-collapse:collapse;font-size:11px}
            table.sf-tbl th{text-align:left;color:#1e3c64;border-bottom:2px solid #e5e7eb;padding:4px 5px;font-size:10px;text-transform:uppercase}
            table.sf-tbl td{padding:4px 5px;border-bottom:1px solid #f0f2f5;color:#2b2f36}
            .sf-r{text-align:right}
            .sf-empty{color:#6b7280;font-style:italic;font-size:11px}
            .sf-flag{font-size:11px}
            .sf-foot{margin-top:20px;border-top:1px solid #e5e7eb;padding-top:8px;color:#9aa1ab;font-size:9px}
        ';
    }

    /* ===================================================================
     * ADMIN-POST HANDLERS (Generate / Download / Resend / Delete)
     * =================================================================== */

    public static function handle_generate() {
        if ( ! current_user_can( 'manage_options' ) && ! current_user_can( 'manage_network_options' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        if ( ! isset( $_POST['_wpnonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['_wpnonce'] ) ), 'seoforge_generate_report' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        $start = isset( $_POST['range_start'] ) ? strtotime( sanitize_text_field( wp_unslash( $_POST['range_start'] ) ) . ' 00:00:00 UTC' ) : 0;
        $end   = isset( $_POST['range_end'] )   ? strtotime( sanitize_text_field( wp_unslash( $_POST['range_end'] ) )   . ' 23:59:59 UTC' ) : 0;
        $tag   = isset( $_POST['tag'] ) ? sanitize_key( wp_unslash( $_POST['tag'] ) ) : 'custom';
        if ( ! $start || ! $end || $end < $start ) wp_die( esc_html__( 'Invalid date range.', 'brainwerk-seo-suite' ), 400 );
        $file = self::generate_pdf( $start, $end, $tag, true );
        $redirect = admin_url( 'admin.php?page=brainwerk-seo-suite&tab=reports' );
        if ( $file ) $redirect = add_query_arg( 'seoforge_report_ok', basename( $file ), $redirect );
        wp_safe_redirect( $redirect ); exit;
    }

    public static function handle_download() {
        if ( ! current_user_can( 'manage_options' ) && ! current_user_can( 'manage_network_options' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        if ( ! isset( $_GET['_wpnonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) ), 'seoforge_dl_report' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        $file = sanitize_file_name( wp_unslash( $_GET['file'] ?? '' ) );
        $up = wp_upload_dir();
        $path = $up['basedir'] . '/seoforge-reports/' . $file;
        if ( ! file_exists( $path ) || strpos( $file, '..' ) !== false ) wp_die( esc_html__( 'Not found', 'brainwerk-seo-suite' ), 404 );
        $is_html = ( strtolower( (string) pathinfo( $file, PATHINFO_EXTENSION ) ) === 'html' );
        // HTML reports open inline so the browser can print them to PDF; legacy
        // PDFs (if any remain) download as attachments.
        if ( $is_html ) {
            header( 'Content-Type: text/html; charset=UTF-8' );
            header( 'Content-Disposition: inline; filename="' . $file . '"' );
        } else {
            header( 'Content-Type: application/pdf' );
            header( 'Content-Disposition: attachment; filename="' . $file . '"' );
        }
        header( 'X-Content-Type-Options: nosniff' );
        header( 'Content-Length: ' . filesize( $path ) );
        readfile( $path );
        exit;
    }

    public static function handle_resend() {
        if ( ! current_user_can( 'manage_options' ) && ! current_user_can( 'manage_network_options' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        if ( ! isset( $_GET['_wpnonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) ), 'seoforge_resend_report' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        $file = sanitize_file_name( wp_unslash( $_GET['file'] ?? '' ) );
        $up = wp_upload_dir();
        $path = $up['basedir'] . '/seoforge-reports/' . $file;
        if ( ! file_exists( $path ) ) wp_die( esc_html__( 'Not found', 'brainwerk-seo-suite' ), 404 );
        $to = (string) Seoforge_Settings::get( self::OPT_AUTO_RECIPIENT, '' );
        if ( ! is_email( $to ) ) wp_die( esc_html__( 'No recipient configured.', 'brainwerk-seo-suite' ), 400 );
        $resend_body = class_exists( 'Seoforge_Notifications' )
            ? Seoforge_Notifications::render_notice_html( array(
                'severity'  => 'info',
                'kicker'    => __( 'Report re-sent', 'brainwerk-seo-suite' ),
                'headline'  => __( 'Here is your report again', 'brainwerk-seo-suite' ),
                'message'   => __( 'You requested another copy — the report is attached to this e-mail.', 'brainwerk-seo-suite' ),
                'facts'     => array( __( 'File', 'brainwerk-seo-suite' ) => $file ),
                'cta_url'   => admin_url( 'admin.php?page=brainwerk-seo-suite&tab=reports' ),
                'cta_label' => __( 'Open reports', 'brainwerk-seo-suite' ),
            ) )
            : __( '<p>Re-Versand des Reports im Anhang.</p>', 'brainwerk-seo-suite' );
        wp_mail( $to, '[Brainwerk SEO Suite] ' . __( 'Report (resend)', 'brainwerk-seo-suite' ) . ' · ' . $file, $resend_body, array( 'Content-Type: text/html; charset=UTF-8' ), array( $path ) );
        wp_safe_redirect( add_query_arg( 'seoforge_resent', '1', admin_url( 'admin.php?page=brainwerk-seo-suite&tab=reports' ) ) ); exit;
    }

    public static function handle_delete() {
        if ( ! current_user_can( 'manage_options' ) && ! current_user_can( 'manage_network_options' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        if ( ! isset( $_GET['_wpnonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) ), 'seoforge_delete_report' ) ) wp_die( esc_html__( 'Forbidden.', 'brainwerk-seo-suite' ), 403 );
        $file = sanitize_file_name( wp_unslash( $_GET['file'] ?? '' ) );
        $up = wp_upload_dir();
        $path = $up['basedir'] . '/seoforge-reports/' . $file;
        if ( file_exists( $path ) && strpos( $file, '..' ) === false ) @unlink( $path );
        wp_safe_redirect( add_query_arg( 'seoforge_deleted', '1', admin_url( 'admin.php?page=brainwerk-seo-suite&tab=reports' ) ) ); exit;
    }

    /** Lists all stored reports (HTML + any legacy PDF) — name + size + mtime. */
    public static function list_archive() {
        $up = wp_upload_dir();
        $dir = $up['basedir'] . '/seoforge-reports';
        if ( ! is_dir( $dir ) ) return array();
        $files = array_merge(
            (array) glob( $dir . '/report-*.html' ),
            (array) glob( $dir . '/report-*.pdf' )
        );
        $files = array_filter( $files );
        if ( ! $files ) return array();
        $out = array();
        foreach ( $files as $f ) {
            $out[] = array( 'name' => basename( $f ), 'size' => filesize( $f ), 'mtime' => filemtime( $f ) );
        }
        usort( $out, function ( $a, $b ) { return $b['mtime'] - $a['mtime']; } );
        return $out;
    }
}

// Monatliches Cron-Intervall registrieren
add_filter( 'cron_schedules', function ( $s ) {
    if ( ! isset( $s['monthly_seoforge'] ) ) {
        $s['monthly_seoforge'] = array( 'interval' => 30 * DAY_IN_SECONDS, 'display' => __( 'Once a month (Brainwerk SEO Suite)', 'brainwerk-seo-suite' ) );
    }
    return $s;
} );
