<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Expiration;

// 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 the "date-expired" WHERE fragment to the shared
 * `prli_links_query_clauses` filter. Activated when the REST caller
 * passes `expired=1` — backs the "Expired" segment in the Lite Links
 * list.
 *
 * Scope: date-based expiration only (`expire_date` meta gated by
 * `enable_expire='1'`, stored as a strtotime-parseable datetime).
 * Click-count expiration (`expire_clicks`) isn't surfaced here because
 * its expired state depends on a live click aggregate — it changes
 * per-click, not per-query, and wiring it into a list filter would
 * require joining the aggregated clicks counter on every query.
 */
class ExpiredLinksFilter
{
    /**
     * Add a date-expired filter clause to the link list query.
     *
     * @param  array{0: array<int, string>, 1: array<int, mixed>} $clauses The accumulated WHERE clause parts and params.
     * @param  array<string, mixed>                               $args    The link list query arguments.
     * @return array{0: array<int, string>, 1: array<int, mixed>}
     */
    public function apply(array $clauses, array $args): array
    {
        $flag = (string) ($args['expired'] ?? '');
        if ($flag !== '1') {
            return $clauses;
        }
        global $wpdb;
        [$where, $params] = $clauses;
        $metas            = $wpdb->prefix . 'prli_link_metas';
        $now              = current_time('mysql');
        $where[]          = $wpdb->prepare('id IN (' . self::expiredSubquery($metas) . ')', $now);
        return [$where, $params];
    }

    /**
     * True when at least one link is currently date-expired. Drives the
     * "Expired" segment visibility in the Links-list filter bar.
     */
    public static function anyExpired(): bool
    {
        global $wpdb;
        $metas = $wpdb->prefix . 'prli_link_metas';
        $now   = current_time('mysql');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = $wpdb->prepare('SELECT 1 FROM (' . self::expiredSubquery($metas) . ') AS expired_links LIMIT 1', $now);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->get_var($sql);
    }

    /**
     * Selects link_ids that are currently date-expired. Matches
     * ExpirationChecker: `expire_date` in the past, gated by
     * `enable_expire='1'` and a non-clicks `expire_type`.
     *
     * @param string $metas The fully-qualified link metas table name.
     *
     * @return string The SQL subquery selecting expired link IDs.
     */
    private static function expiredSubquery(string $metas): string
    {
        return "
            SELECT m.link_id FROM {$metas} m
            WHERE m.meta_key = 'expire_date'
              AND m.meta_value <> ''
              AND m.meta_value < %s
              AND EXISTS (
                  SELECT 1 FROM {$metas} g
                  WHERE g.link_id = m.link_id
                    AND g.meta_key = 'enable_expire'
                    AND g.meta_value = '1'
              )
              AND COALESCE((
                  SELECT t.meta_value FROM {$metas} t
                  WHERE t.link_id = m.link_id AND t.meta_key = 'expire_type' LIMIT 1
              ), 'date') = 'date'
        ";
    }
}
