<?php
if (!defined('ABSPATH')) exit;

class SEOFORGE_Dashboard {
    private static $instance = null;
    public static function instance() { if (!self::$instance) self::$instance = new self(); return self::$instance; }

    private function __construct() {
        add_action('wp_dashboard_setup', [$this, 'add_widget']);
    }

    public function add_widget() {
        wp_add_dashboard_widget('seoforge_dashboard', __('SEO Overview', 'seoforge'), [$this, 'render_widget']);
    }

    public function get_stats() {
        global $wpdb;
        $table = $wpdb->prefix . 'seoforge_analysis';

        // R2-N2/R2-N9 (2026-04-28): table-exists guard. SEOForge can be
        // active without `wp_seoforge_analysis` if activation hook didn't
        // run (manual file copy, drift between hosting + plugin code,
        // table dropped during dev). Without this guard `get_stats`
        // throws two MySQL "Table doesn't exist" errors on every wp-admin
        // load — prod logs filled, MySQL load gets a free hit. Return
        // zeroed defaults instead so the dashboard renders cleanly and
        // the upgrade path (Settings → re-run activation) is the user's
        // surface, not a broken page.
        $defaults = [
            'total_analyzed'      => 0,
            'avg_score'           => 0,
            'good'                => 0,
            'medium'              => 0,
            'bad'                 => 0,
            'no_meta_description' => 0,
            'top_issues'          => [],
        ];
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) !== $table) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table; direct query is intended.
            // Still surface the missing-meta count — that query is on
            // wp_posts/wp_postmeta which always exist.
            $defaults['no_meta_description'] = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->posts} p
                LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_seoforge_meta_description'
                WHERE p.post_status = 'publish' AND p.post_type IN ('post','page')
                AND (pm.meta_value IS NULL OR pm.meta_value = '')"
            );
            return $defaults;
        }

        // Single query for all score stats — only for existing published posts
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- own table from $wpdb->prefix; one-time/admin read
        $stats_row = $wpdb->get_row("SELECT
            COUNT(*) AS total,
            COALESCE(AVG(a.score), 0) AS avg_score,
            SUM(CASE WHEN a.score >= 80 THEN 1 ELSE 0 END) AS good,
            SUM(CASE WHEN a.score >= 50 AND a.score < 80 THEN 1 ELSE 0 END) AS medium,
            SUM(CASE WHEN a.score < 50 THEN 1 ELSE 0 END) AS bad
            FROM $table a
            INNER JOIN {$wpdb->posts} p ON a.post_id = p.ID
            WHERE p.post_status = 'publish'");
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $total = (int) $stats_row->total;
        $avg = (float) $stats_row->avg_score;
        $good = (int) $stats_row->good;
        $medium = (int) $stats_row->medium;
        $bad = (int) $stats_row->bad;

        // Posts without meta description
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- own table from $wpdb->prefix; one-time/admin read
        $no_meta = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_seoforge_meta_description'
            WHERE p.post_status = 'publish' AND p.post_type IN ('post','page')
            AND (pm.meta_value IS NULL OR pm.meta_value = '')"
        );

        // Top issues
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- own table from $wpdb->prefix; one-time/admin read
        $all_issues = $wpdb->get_col("SELECT a.issues FROM $table a INNER JOIN {$wpdb->posts} p ON a.post_id = p.ID WHERE p.post_status = 'publish' AND a.issues != '' AND a.issues != '[]' ORDER BY a.analyzed_at DESC LIMIT 50");
        $issue_counts = [];
        foreach ($all_issues as $json) {
            $issues = json_decode($json, true);
            if (!is_array($issues)) continue;
            foreach ($issues as $issue) {
                $msg = $issue['message'] ?? '';
                if ($msg) {
                    $issue_counts[$msg] = ($issue_counts[$msg] ?? 0) + 1;
                }
            }
        }
        arsort($issue_counts);

        return [
            'total_analyzed' => $total,
            'avg_score' => round($avg),
            'good' => $good,
            'medium' => $medium,
            'bad' => $bad,
            'no_meta_description' => $no_meta,
            'top_issues' => array_slice($issue_counts, 0, 5, true),
        ];
    }

    public function render_widget() {
        $stats = $this->get_stats();
        $color = $stats['avg_score'] >= 80 ? '#059669' : ($stats['avg_score'] >= 50 ? '#D97706' : '#DC2626');
        ?>
        <div style="text-align:center;margin-bottom:12px;">
            <div style="font-size:36px;font-weight:700;color:<?php echo esc_attr( $color ); ?>;"><?php echo esc_html( $stats['avg_score'] ); ?></div>
            <div style="color:#64748b;font-size:14px;"><?php esc_html_e('Average SEO Score', 'seoforge'); ?></div>
        </div>
        <div style="display:flex;gap:8px;text-align:center;margin-bottom:12px;">
            <div style="flex:1;padding:8px;background:#ECFDF5;border-radius:4px;"><strong style="color:#059669;"><?php echo esc_html( $stats['good'] ); ?></strong><br><small><?php esc_html_e('Good', 'seoforge'); ?></small></div>
            <div style="flex:1;padding:8px;background:#FFFBEB;border-radius:4px;"><strong style="color:#D97706;"><?php echo esc_html( $stats['medium'] ); ?></strong><br><small><?php esc_html_e('Needs Work', 'seoforge'); ?></small></div>
            <div style="flex:1;padding:8px;background:#FEF2F2;border-radius:4px;"><strong style="color:#DC2626;"><?php echo esc_html( $stats['bad'] ); ?></strong><br><small><?php esc_html_e('Poor', 'seoforge'); ?></small></div>
        </div>
        <?php if ($stats['no_meta_description'] > 0): ?>
            <?php /* translators: %d: number of posts missing a meta description */ ?>
            <p style="color:#D97706;font-size:14px;"><?php printf( esc_html__('%d posts missing meta description', 'seoforge'), (int) $stats['no_meta_description'] ); ?></p>
        <?php endif; ?>
        <p><a href="<?php echo esc_url( admin_url('admin.php?page=seoforge') ); ?>" class="button"><?php esc_html_e('Full Dashboard', 'seoforge'); ?></a></p>
        <?php
    }

    /**
 * Collect prioritised action items for the "today" dashboard. Only
 * items with a non-zero count are returned, sorted high → low priority.
 */
    public function get_action_items($stats) {
        global $wpdb;

        // No focus keyword on published posts/pages.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- own table from $wpdb->prefix; one-time/admin read
        $no_keyword = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} p
             LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_seoforge_focus_keyword'
             WHERE p.post_status = 'publish' AND p.post_type IN ('post','page')
               AND (pm.meta_value IS NULL OR pm.meta_value = '')"
        );

        // GSC connection now lives on forge-api, not in WP options.
        // SEOFORGE_GSC::is_connected caches the Worker response in a
        // 5-minute transient so the dashboard-render cost stays flat.
        $gsc_connected = class_exists('SEOFORGE_GSC')
            ? SEOFORGE_GSC::instance()->is_connected()
            : false;
        // R2-N2 (2026-04-28): same table-existence guard idea for redirects.
        $redirects_table = $wpdb->prefix . 'seoforge_redirects';
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- own table from $wpdb->prefix; one-time/admin read
        $redirects = ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $redirects_table)) === $redirects_table)
            ? (int) $wpdb->get_var("SELECT COUNT(*) FROM {$redirects_table}")
            : 0;
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

        $items = [];

        if ($stats['total_analyzed'] === 0) {
            $items[] = [
                'tone'   => 'primary',
                'icon'   => 'chart-bar',
                'title'  => __('Analyze your content', 'seoforge'),
                'metric' => __('0 posts analyzed', 'seoforge'),
                'desc'   => __('Run bulk analysis once to surface every opportunity SEO Forge can find.', 'seoforge'),
                'cta'    => __('Run bulk analysis', 'seoforge'),
                'url'    => admin_url('admin.php?page=seoforge-settings#sf-tab-automation'),
            ];
        }
        if ($stats['bad'] > 0) {
            $items[] = [
                'tone'   => 'danger',
                'icon'   => 'warning',
                'title'  => __('Fix under-performing posts', 'seoforge'),
                /* translators: %d: number of posts scoring below 50 */
                'metric' => sprintf(_n('%d post scoring below 50', '%d posts scoring below 50', $stats['bad'], 'seoforge'), $stats['bad']),
                'desc'   => __('These rank poorly in our analyzer. Each has specific issues listed in its editor.', 'seoforge'),
                'cta'    => __('See posts', 'seoforge'),
                'url'    => '#sf-posts-attention',
            ];
        }
        if ($stats['no_meta_description'] > 0) {
            $items[] = [
                'tone'   => 'warn',
                'icon'   => 'text',
                'title'  => __('Add missing meta descriptions', 'seoforge'),
                /* translators: %d: number of posts without a meta description */
                'metric' => sprintf(_n('%d post without description', '%d posts without description', $stats['no_meta_description'], 'seoforge'), $stats['no_meta_description']),
                'desc'   => class_exists('SEOFORGE_License') && !SEOFORGE_License::is_free()
                    ? __('AI can draft descriptions for all of them in one batch.', 'seoforge')
                    : __('Edit each post or upgrade to generate descriptions with AI in bulk.', 'seoforge'),
                'cta'    => __('Open posts list', 'seoforge'),
                'url'    => admin_url('edit.php?post_type=post'),
            ];
        }
        if ($no_keyword > 0) {
            $items[] = [
                'tone'   => 'warn',
                'icon'   => 'tag',
                'title'  => __('Set focus keywords', 'seoforge'),
                /* translators: %d: number of posts without a focus keyword */
                'metric' => sprintf(_n('%d post without keyword', '%d posts without keyword', $no_keyword, 'seoforge'), $no_keyword),
                'desc'   => __('The analyzer scores keyword usage. Without one, we can only judge structure.', 'seoforge'),
                'cta'    => __('Open posts list', 'seoforge'),
                'url'    => admin_url('edit.php?post_type=post'),
            ];
        }
        if (!$gsc_connected) {
            $items[] = [
                'tone'   => 'info',
                'icon'   => 'chart-line',
                'title'  => __('Connect Google Search Console', 'seoforge'),
                'metric' => __('Not connected', 'seoforge'),
                'desc'   => __('See real impressions, clicks and rankings right inside SEO Forge.', 'seoforge'),
                'cta'    => __('Connect', 'seoforge'),
                'url'    => admin_url('admin.php?page=seoforge-settings#sf-tab-integrations'),
            ];
        }

        return $items;
    }

    public function render() {
        $stats        = $this->get_stats();
        $action_items = $this->get_action_items($stats);
        $score_color  = $stats['avg_score'] >= 80 ? '#059669' : ($stats['avg_score'] >= 50 ? '#D97706' : '#DC2626');
        $tone_bg      = [
            'primary' => ['bg' => '#FFF7ED', 'border' => '#FED7AA', 'icon' => '#C2410C'],
            'danger'  => ['bg' => '#FEF2F2', 'border' => '#FCA5A5', 'icon' => '#B91C1C'],
            'warn'    => ['bg' => '#FFFBEB', 'border' => '#FCD34D', 'icon' => '#B45309'],
            'info'    => ['bg' => '#EFF6FF', 'border' => '#BFDBFE', 'icon' => '#1D4ED8'],
        ];
        ?>
        <div class="wrap sf-admin">
            <?php SEOFORGE_Admin_Tabs::render('seoforge'); ?>
            <h1><?php esc_html_e('SEO Forge — Dashboard', 'seoforge'); ?><?php Forge_Docs_Link::render('seoforge', 'understanding-your-seo-score', __('Dashboard overview', 'seoforge'), __('What the numbers mean and which actions to do first.', 'seoforge')); ?></h1>

            <?php if (!empty($action_items)): ?>
            <section class="sf-dash-today">
                <div class="sf-dash-today__head">
                    <h2 style="margin:0;font-size:18px;font-weight:700;"><?php esc_html_e('Today\'s focus', 'seoforge'); ?></h2>
                    <?php /* translators: %d: number of action items */ ?>
                    <span style="color:#64748b;font-size:14px;"><?php printf( esc_html( _n('%d action', '%d actions', count($action_items), 'seoforge') ), count($action_items)); ?></span>
                </div>
                <div class="sf-dash-today__grid">
                    <?php foreach (array_slice($action_items, 0, 5) as $a):
                        $t = $tone_bg[$a['tone']] ?? $tone_bg['info']; ?>
                        <div class="sf-action-card" style="background:<?php echo esc_attr($t['bg']); ?>;border:1px solid <?php echo esc_attr($t['border']); ?>;">
                            <div class="sf-action-card__head">
                                <span class="dashicons dashicons-<?php echo esc_attr($a['icon']); ?>" style="color:<?php echo esc_attr($t['icon']); ?>;"></span>
                                <span class="sf-action-card__metric"><?php echo esc_html($a['metric']); ?></span>
                            </div>
                            <h3 class="sf-action-card__title"><?php echo esc_html($a['title']); ?></h3>
                            <p class="sf-action-card__desc"><?php echo esc_html($a['desc']); ?></p>
                            <a class="button button-primary sf-action-card__cta" href="<?php echo esc_url($a['url']); ?>"><?php echo esc_html($a['cta']); ?></a>
                        </div>
                    <?php endforeach; ?>
                </div>
            </section>
            <?php endif; ?>

            <?php if ($stats['total_analyzed'] > 0): ?>
            <details class="sf-dash-details" open>
                <summary><?php esc_html_e('At a glance', 'seoforge'); ?></summary>
                <div class="sf-dash-stats">
                    <div class="sf-stat-mini">
                        <div class="sf-stat-mini__val" style="color:<?php echo esc_attr($score_color); ?>;"><?php echo (int) $stats['avg_score']; ?></div>
                        <div class="sf-stat-mini__label"><?php esc_html_e('Avg score', 'seoforge'); ?></div>
                    </div>
                    <div class="sf-stat-mini"><div class="sf-stat-mini__val"><?php echo (int) $stats['total_analyzed']; ?></div><div class="sf-stat-mini__label"><?php esc_html_e('Analyzed', 'seoforge'); ?></div></div>
                    <div class="sf-stat-mini"><div class="sf-stat-mini__val" style="color:#059669;"><?php echo (int) $stats['good']; ?></div><div class="sf-stat-mini__label"><?php esc_html_e('Good (80+)', 'seoforge'); ?></div></div>
                    <div class="sf-stat-mini"><div class="sf-stat-mini__val" style="color:#D97706;"><?php echo (int) $stats['medium']; ?></div><div class="sf-stat-mini__label"><?php esc_html_e('Needs work', 'seoforge'); ?></div></div>
                    <div class="sf-stat-mini"><div class="sf-stat-mini__val" style="color:#DC2626;"><?php echo (int) $stats['bad']; ?></div><div class="sf-stat-mini__label"><?php esc_html_e('Poor (<50)', 'seoforge'); ?></div></div>
                    <div class="sf-stat-mini"><div class="sf-stat-mini__val" style="color:#64748B;"><?php echo (int) $stats['no_meta_description']; ?></div><div class="sf-stat-mini__label"><?php esc_html_e('Missing meta', 'seoforge'); ?></div></div>
                </div>
            </details>
            <?php endif; ?>

            <?php if (!empty($stats['top_issues'])): ?>
            <details class="sf-dash-details">
                <summary><?php esc_html_e('Most common issues', 'seoforge'); ?></summary>
                <table class="widefat striped" style="max-width:680px;">
                    <thead><tr><th><?php esc_html_e('Issue', 'seoforge'); ?></th><th style="width:80px;text-align:right;"><?php esc_html_e('Count', 'seoforge'); ?></th></tr></thead>
                    <tbody>
                    <?php foreach ($stats['top_issues'] as $msg => $cnt): ?>
                        <tr><td><?php echo esc_html($msg); ?></td><td style="text-align:right;"><strong><?php echo (int) $cnt; ?></strong></td></tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </details>
            <?php endif; ?>

            <?php
            global $wpdb;
            // group by post_id to avoid duplicate rows from multiple
            // analysis runs; skip unpublished + empty-title rows.
            // R2-N2 (2026-04-28): table-exists guard so a missing
            // wp_seoforge_analysis (fresh activation drift, manual install
            // without hook) doesn't leak a SQL error to PHP error log.
            $analysis_table = $wpdb->prefix . 'seoforge_analysis';
            // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- own table from $wpdb->prefix; one-time/admin read
            $low = ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $analysis_table)) === $analysis_table)
                ? $wpdb->get_results("
                    SELECT a.post_id, MIN(a.score) AS score, p.post_title
                    FROM {$analysis_table} a
                    INNER JOIN {$wpdb->posts} p ON a.post_id = p.ID
                    WHERE a.score < 50
                      AND p.post_status = 'publish'
                      AND TRIM(COALESCE(p.post_title, '')) <> ''
                    GROUP BY a.post_id, p.post_title
                    ORDER BY score ASC
                    LIMIT 20
                ")
                : [];
            // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
            if ($low): ?>
            <details id="sf-posts-attention" class="sf-dash-details">
                <?php /* translators: %d: number of posts needing attention */ ?>
                <summary><?php printf( esc_html__('Posts needing attention (%d)', 'seoforge'), count($low)); ?></summary>
                <table class="widefat striped">
                    <thead><tr><th><?php esc_html_e('Post', 'seoforge'); ?></th><th style="width:80px;text-align:right;"><?php esc_html_e('Score', 'seoforge'); ?></th><th style="width:80px;"></th></tr></thead>
                    <tbody>
                    <?php foreach ($low as $r): ?>
                        <tr>
                            <td><a href="<?php echo esc_url( get_edit_post_link($r->post_id) ); ?>"><?php echo esc_html($r->post_title); ?></a></td>
                            <td style="text-align:right;"><strong style="color:#DC2626;"><?php echo (int) $r->score; ?></strong></td>
                            <td><a href="<?php echo esc_url( get_edit_post_link($r->post_id) ); ?>" class="button button-small"><?php esc_html_e('Fix', 'seoforge'); ?></a></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </details>
            <?php endif; ?>

            <style>
                .sf-dash-today { margin: 20px 0 28px; }
                .sf-dash-today__head { display:flex; align-items:baseline; justify-content:space-between; margin-bottom: 12px; }
                .sf-dash-today__grid { display:grid; grid-template-columns:repeat(auto-fill,minmax(270px,1fr)); gap:14px; }
                .sf-action-card { border-radius: 12px; padding: 16px 18px; display:flex; flex-direction:column; gap:8px; }
                .sf-action-card__head { display:flex; align-items:center; justify-content:space-between; }
                .sf-action-card__head .dashicons { font-size: 22px; width:22px; height:22px; }
                .sf-action-card__metric { font-size: 14px; font-weight: 600; color: #1f2937; background:rgba(255,255,255,0.65); padding:2px 8px; border-radius:999px; }
                .sf-action-card__title { margin: 2px 0 0; font-size: 15px; font-weight: 700; color: #0f172a; line-height:1.35; }
                .sf-action-card__desc  { margin: 0; font-size: 14px; line-height: 1.5; color: #475569; }
                .sf-action-card__cta   { align-self: flex-start; margin-top: 4px; }
                .sf-dash-details { margin: 18px 0; border:1px solid #e5e7eb; border-radius: 10px; background:#fff; }
                .sf-dash-details > summary { list-style:none; cursor:pointer; padding: 12px 18px; font-weight: 600; font-size: 14px; color:#111827; display:flex; align-items:center; gap:8px; }
                .sf-dash-details > summary::-webkit-details-marker { display:none; }
                .sf-dash-details > summary::before { content:'▸'; color:#94a3b8; font-size:14px; transition: transform .15s ease; display:inline-block; }
                .sf-dash-details[open] > summary::before { transform: rotate(90deg); }
                .sf-dash-details > summary + * { padding: 4px 18px 18px; }
                .sf-dash-details table.widefat { margin: 0; }
                .sf-dash-stats { display:grid; grid-template-columns:repeat(auto-fill,minmax(130px,1fr)); gap:10px; }
                .sf-stat-mini { text-align:center; padding: 10px 12px; border:1px solid #e5e7eb; border-radius: 8px; background: #fafbfc; }
                .sf-stat-mini__val { font-size: 24px; font-weight: 800; line-height: 1.1; }
                .sf-stat-mini__label { font-size: 14px; color: #64748b; margin-top: 4px; text-transform: uppercase; letter-spacing: .04em; }
            </style>
        </div>
        <?php
    }
}
