<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Health;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_value
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// Custom plugin tables (prli_*): table names interpolated from $wpdb->prefix (trusted),
// user values bind through $wpdb->prepare(). No caching: these tables are the source
// of truth for click/redirect data and must read-through. "meta_key"/"meta_value" here
// refer to our own prli_link_metas table, not wp_postmeta.

/**
 * Contributes health-status WHERE fragments to the shared
 * `prli_links_query_clauses` filter. Owns the SQL shape of the
 * "broken" bucket — Bootstrap wires the hook, this class provides the
 * clause.
 *
 * Canonical on `health_status` = `'inactive'` (v3 meta shape). A link
 * with no `health_status` row has never been probed — matching v3, it
 * is not counted as broken.
 */
class HealthStatusFilter
{
    /**
     * Appends the "broken" health-status WHERE fragment to the query clauses.
     *
     * @param  array{0: array<int, string>, 1: array<int, mixed>} $clauses Query clauses as a [where, params] tuple.
     * @param  array<string, mixed>                               $args    Query arguments, may include the 'health' filter.
     * @return array{0: array<int, string>, 1: array<int, mixed>}
     */
    public function apply(array $clauses, array $args): array
    {
        $health = (string) ($args['health'] ?? '');
        if ($health !== 'broken') {
            return $clauses;
        }
        global $wpdb;
        [$where, $params] = $clauses;
        $metas            = $wpdb->prefix . 'prli_link_metas';
        $where[]          = "id IN (
            SELECT link_id FROM {$metas}
            WHERE meta_key = 'health_status' AND meta_value = 'inactive'
        )";
        return [$where, $params];
    }

    /**
     * True when at least one link currently has a broken-or-unreachable
     * health status (code 0 = probe errored / never succeeded, ≥400 =
     * HTTP error). Drives the "Broken" segment visibility in the
     * Links-list filter bar.
     */
    public static function anyBroken(): bool
    {
        global $wpdb;
        $metas = $wpdb->prefix . 'prli_link_metas';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT 1 FROM {$metas}
                WHERE meta_key = 'health_status' AND meta_value = 'inactive'
                LIMIT 1";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->get_var($sql);
    }
}
