<?php
if (!defined('ABSPATH')) exit;
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Every table name is $wpdb->prefix plus a fixed suffix from a private helper (never user input); all runtime values go through $wpdb->prepare() placeholders.

/**
 * Linking health score (0-100), priority tasks, daily history and trends.
 *
 * Components are computed only on scanned content (meta _lbraid_links_scanned),
 * with the same rules as the REST filters in the panel. Without a scan an empty
 * state is returned instead of an understated score.
 */
class Linkbraid_Health {

    /** WHERE clause for published content of the supported types. Returns [sql, params]. */
    private static function types_where($alias = 'p') {
        $types = lbraid_post_types();
        $in = implode(',', array_fill(0, count($types), '%s'));
        return ["$alias.post_status='publish' AND $alias.post_type IN ($in)", $types];
    }

    /** Full report for the Overview panel. */
    public static function report() {
        return [
            'health'  => self::compute(),
            'tasks'   => self::tasks(),
            'history' => self::history(),
            'trends'  => self::trends(),
        ];
    }

    /** SQL tail excluding ignored content — deliberate gaps must not hurt the score. */
    private static function ignored_sql() {
        $ignored = lbraid_ignored_posts();
        if (!$ignored) return ['', []];
        return [' AND p.ID NOT IN (' . implode(',', array_fill(0, count($ignored), '%d')) . ')', $ignored];
    }

    /** Score 0-100 broken down by component. */
    public static function compute() {
        global $wpdb;
        list($where, $params) = self::types_where();
        list($ignored_sql, $ignored_params) = self::ignored_sql();
        $where .= $ignored_sql;
        $params = array_merge($params, $ignored_params);
        $links = $wpdb->prefix . 'lbraid_existing_links';
        $index = $wpdb->prefix . 'lbraid_post_index';

        $scanned = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p
             JOIN {$wpdb->postmeta} m ON m.post_id=p.ID AND m.meta_key='_lbraid_links_scanned'
             WHERE $where", ...$params));
        if (!$scanned) return ['empty' => true];

        $with_incoming = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p
             JOIN {$wpdb->postmeta} m ON m.post_id=p.ID AND m.meta_key='_lbraid_links_scanned'
             JOIN (SELECT DISTINCT target_post_id FROM $links) l ON l.target_post_id=p.ID
             WHERE $where", ...$params));

        $outgoing_ok = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts} p
             JOIN {$wpdb->postmeta} m ON m.post_id=p.ID AND m.meta_key='_lbraid_links_scanned'
             JOIN (SELECT source_post_id, COUNT(*) c FROM $links GROUP BY source_post_id) o ON o.source_post_id=p.ID AND o.c>=3
             WHERE $where", ...$params));

        // Anchor quality: length is not a ranking factor — problematic are
        // generic phrases, raw URLs as anchor text and anchors over 8 words.
        $generic = Linkbraid_Anchors::generic();
        $generic_ph = implode(',', array_fill(0, count($generic), '%s'));
        $wc_expr = "(LENGTH(TRIM(anchor_text)) - LENGTH(REPLACE(TRIM(anchor_text), ' ', '')) + 1)";
        $anchors = $wpdb->get_row($wpdb->prepare(
            "SELECT COUNT(*) total,
                    SUM(CASE WHEN LOWER(TRIM(anchor_text)) IN ($generic_ph)
                              OR $wc_expr > 8
                              OR LOWER(TRIM(anchor_text)) LIKE %s
                              OR LOWER(TRIM(anchor_text)) LIKE %s THEN 1 ELSE 0 END) bad
             FROM $links WHERE TRIM(anchor_text) <> ''", ...array_merge($generic, ['http%', 'www.%'])));

        $total = 0;
        foreach (lbraid_post_types() as $type) $total += (int) wp_count_posts($type)->publish;
        // Ignored content leaves the freshness denominator too — the WHERE above already excludes it from the numerator.
        if ($ignored_params) {
            $ignored_ph = implode(',', array_fill(0, count($ignored_params), '%d'));
            $total = max(0, $total - (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status='publish' AND ID IN ($ignored_ph)", ...$ignored_params)));
        }
        $fresh = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts} p
             JOIN $index i ON i.post_id=p.ID AND i.embedding IS NOT NULL
             WHERE $where AND NOT EXISTS (
                SELECT 1 FROM {$wpdb->postmeta} m2 WHERE m2.post_id=p.ID AND m2.meta_key='_lbraid_index_stale' AND m2.meta_value='1'
             )", ...$params));

        $priority_total = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $index i JOIN {$wpdb->posts} p ON p.ID=i.post_id WHERE i.is_priority=1 AND $where", ...$params));
        $priority_covered = $priority_total ? (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $index i JOIN {$wpdb->posts} p ON p.ID=i.post_id
             JOIN (SELECT DISTINCT target_post_id FROM $links) l ON l.target_post_id=p.ID
             WHERE i.is_priority=1 AND $where", ...$params)) : 0;

        $posts_url = admin_url('admin.php?page=linkbraid-posts');
        $components = [];
        $components[] = self::component('incoming', __( 'Incoming links', 'linkbraid' ), $with_incoming, $scanned, 35,
            __( 'Share of scanned content with at least one detected incoming link.', 'linkbraid' ),
            __( 'Add links to orphaned content.', 'linkbraid' ), $posts_url . '&filter=no_incoming');
        $components[] = self::component('outgoing', __( 'Outgoing links', 'linkbraid' ), $outgoing_ok, $scanned, 25,
            __( 'Share of scanned content with at least three outgoing links.', 'linkbraid' ),
            __( 'Run the analysis for content without outgoing links.', 'linkbraid' ), $posts_url . '&filter=no_outgoing');
        if ($anchors && (int) $anchors->total > 0) {
            $components[] = self::component('anchors', __( 'Anchor quality', 'linkbraid' ), (int) $anchors->total - (int) $anchors->bad, (int) $anchors->total, 15,
                __( 'Share of anchors free of real problems: generic phrases ("click here"), raw URLs as anchor text and anchors over 8 words. Anchor length itself is not a ranking factor.', 'linkbraid' ),
                __( 'Review the anchor report.', 'linkbraid' ), admin_url('admin.php?page=linkbraid-reports&tab=anchors'));
        }
        if (lbraid_has_pro_code()) $components[] = self::component('freshness', __( 'Index freshness', 'linkbraid' ), $fresh, max(1, $total), 15,
            __( 'Share of published content with an up-to-date embedding (not flagged as “needs refresh”).', 'linkbraid' ),
            __( 'Index the content flagged for refresh.', 'linkbraid' ), $posts_url . '&filter=stale');
        if ($priority_total > 0) {
            $components[] = self::component('priority', __( 'Priority coverage', 'linkbraid' ), $priority_covered, $priority_total, 10,
                __( 'Share of priority pages with at least one detected incoming link.', 'linkbraid' ),
                __( 'Look for links to starred pages.', 'linkbraid' ), admin_url('admin.php?page=linkbraid-priority'));
        }

        $weight_sum = 0; $points_sum = 0.0;
        foreach ($components as $c) { $weight_sum += $c['weight']; $points_sum += $c['pct'] * $c['weight']; }
        $score = $weight_sum ? (int) round(100 * $points_sum / $weight_sum) : 0;

        return [
            'empty'      => false,
            'score'      => $score,
            'status'     => $score >= 80 ? 'good' : ($score >= 55 ? 'warn' : 'bad'),
            'scanned'    => $scanned,
            'components' => $components,
        ];
    }

    private static function component($key, $label, $good, $total, $weight, $description, $hint, $url) {
        $pct = $total > 0 ? max(0.0, min(1.0, $good / $total)) : 0.0;
        return [
            'key' => $key, 'label' => $label,
            'good' => (int) $good, 'total' => (int) $total,
            'pct' => round($pct, 4), 'weight' => (int) $weight,
            'points' => round($pct * $weight, 1),
            'status' => $pct >= 0.8 ? 'good' : ($pct >= 0.5 ? 'warn' : 'bad'),
            'description' => $description,
            'hint' => $hint, 'url' => $url,
        ];
    }

    /** Priority tasks with counters and deep links to filtered views. */
    public static function tasks() {
        global $wpdb;
        list($where, $params) = self::types_where();
        $links = $wpdb->prefix . 'lbraid_existing_links';
        $index = $wpdb->prefix . 'lbraid_post_index';
        $posts_url = admin_url('admin.php?page=linkbraid-posts');

        $no_incoming = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p
             JOIN {$wpdb->postmeta} m ON m.post_id=p.ID AND m.meta_key='_lbraid_links_scanned'
             LEFT JOIN (SELECT DISTINCT target_post_id FROM $links) l ON l.target_post_id=p.ID
             WHERE $where AND l.target_post_id IS NULL", ...$params));

        $no_outgoing = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p
             JOIN {$wpdb->postmeta} m ON m.post_id=p.ID AND m.meta_key='_lbraid_links_scanned'
             LEFT JOIN (SELECT source_post_id, COUNT(*) c FROM $links GROUP BY source_post_id) o ON o.source_post_id=p.ID
             WHERE $where AND COALESCE(o.c,0)=0", ...$params));

        $pending = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}lbraid_link_suggestions WHERE status='pending'");

        $stale = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts} p
             LEFT JOIN $index i ON i.post_id=p.ID
             WHERE $where AND (i.embedding IS NULL OR EXISTS (
                SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_lbraid_index_stale' AND m.meta_value='1'
             ))", ...$params));

        $priority_uncovered = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $index i JOIN {$wpdb->posts} p ON p.ID=i.post_id
             LEFT JOIN (SELECT DISTINCT target_post_id FROM $links) l ON l.target_post_id=p.ID
             WHERE i.is_priority=1 AND $where AND l.target_post_id IS NULL", ...$params));

        $tasks = [];
        $broken = class_exists('Linkbraid_Broken') ? Linkbraid_Broken::broken_count() : 0;
        if ($broken) $tasks[] = ['key' => 'broken', 'count' => $broken, 'label' => __( 'Broken external links', 'linkbraid' ), 'action' => __( 'View', 'linkbraid' ), 'url' => admin_url('admin.php?page=linkbraid-reports&tab=broken'), 'severity' => 'bad'];
        if ($pending) $tasks[] = ['key' => 'pending', 'count' => $pending, 'label' => __( 'Suggestions waiting for review', 'linkbraid' ), 'action' => __( 'Review', 'linkbraid' ), 'url' => $posts_url . '&filter=has_suggestions', 'severity' => 'info'];
        if ($priority_uncovered) $tasks[] = ['key' => 'priority', 'count' => $priority_uncovered, 'label' => __( 'Priority pages without incoming links', 'linkbraid' ), 'action' => __( 'View', 'linkbraid' ), 'url' => admin_url('admin.php?page=linkbraid-priority'), 'severity' => 'bad'];
        if ($no_incoming) $tasks[] = ['key' => 'no_incoming', 'count' => $no_incoming, 'label' => __( 'Content without detected incoming links', 'linkbraid' ), 'action' => __( 'Show', 'linkbraid' ), 'url' => $posts_url . '&filter=no_incoming', 'severity' => 'bad'];
        if ($no_outgoing) $tasks[] = ['key' => 'no_outgoing', 'count' => $no_outgoing, 'label' => __( 'Content without outgoing links', 'linkbraid' ), 'action' => __( 'Show', 'linkbraid' ), 'url' => $posts_url . '&filter=no_outgoing', 'severity' => 'warn'];
        if ($stale) $tasks[] = ['key' => 'stale', 'count' => $stale, 'label' => __( 'Content to index or refresh', 'linkbraid' ), 'action' => __( 'Show', 'linkbraid' ), 'url' => $posts_url . '&filter=stale', 'severity' => 'warn'];
        return $tasks;
    }

    /** Stores the daily stats snapshot (once a day, when the Overview is opened). */
    public static function maybe_snapshot() {
        global $wpdb;
        $today = current_time('Y-m-d');
        if (get_option('lbraid_stats_last_snapshot') === $today) return;

        $index = $wpdb->prefix . 'lbraid_post_index';
        $suggestions = $wpdb->prefix . 'lbraid_link_suggestions';
        $total_posts = 0;
        foreach (lbraid_post_types() as $type) $total_posts += (int) wp_count_posts($type)->publish;
        $health = self::compute();

        $result = $wpdb->replace($wpdb->prefix . 'lbraid_stats_history', [
            'snapshot_date' => $today,
            'total_posts'   => $total_posts,
            'indexed_posts' => (int) $wpdb->get_var("SELECT COUNT(*) FROM $index WHERE embedding IS NOT NULL"),
            'total_links'   => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}lbraid_existing_links"),
            'pending'       => (int) $wpdb->get_var("SELECT COUNT(*) FROM $suggestions WHERE status='pending'"),
            'accepted'      => (int) $wpdb->get_var("SELECT COUNT(*) FROM $suggestions WHERE status='accepted'"),
            'priority'      => (int) $wpdb->get_var("SELECT COUNT(*) FROM $index WHERE is_priority=1"),
            'health_score'  => empty($health['empty']) ? (int) $health['score'] : 0,
        ]);
        if ($result !== false) update_option('lbraid_stats_last_snapshot', $today, false);
        if (class_exists('Linkbraid_Clicks')) Linkbraid_Clicks::cleanup(); // retention runs once a day, alongside the snapshot
    }

    /** The last 30 snapshots, ascending by date. */
    public static function history() {
        global $wpdb;
        $rows = $wpdb->get_results(
            "SELECT snapshot_date, total_posts, indexed_posts, total_links, pending, accepted, priority, health_score
             FROM {$wpdb->prefix}lbraid_stats_history ORDER BY snapshot_date DESC LIMIT 30", ARRAY_A);
        if (!$rows) return [];
        foreach ($rows as &$r) foreach ($r as $k => $v) if ($k !== 'snapshot_date') $r[$k] = (int) $v;
        return array_reverse($rows);
    }

    /** Change against a snapshot at least 7 days old (or the oldest one available). */
    public static function trends() {
        global $wpdb;
        $table = $wpdb->prefix . 'lbraid_stats_history';
        $today = current_time('Y-m-d');
        $baseline = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table WHERE snapshot_date <= DATE_SUB(%s, INTERVAL 7 DAY) ORDER BY snapshot_date DESC LIMIT 1", $today), ARRAY_A);
        if (!$baseline) {
            $baseline = $wpdb->get_row($wpdb->prepare(
                "SELECT * FROM $table WHERE snapshot_date < %s ORDER BY snapshot_date ASC LIMIT 1", $today), ARRAY_A);
        }
        $current = $wpdb->get_row("SELECT * FROM $table ORDER BY snapshot_date DESC LIMIT 1", ARRAY_A);
        if (!$baseline || !$current || $baseline['snapshot_date'] === $current['snapshot_date']) return null;

        $out = ['since' => $baseline['snapshot_date'], 'days' => max(1, (int) ((strtotime($current['snapshot_date']) - strtotime($baseline['snapshot_date'])) / DAY_IN_SECONDS))];
        foreach (['total_posts', 'indexed_posts', 'total_links', 'pending', 'accepted', 'priority', 'health_score'] as $key) {
            $out[$key] = (int) $current[$key] - (int) $baseline[$key];
        }
        return $out;
    }
}