<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Reports;

// 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.

/**
 * Custom Reports analytics primitive (Pro).
 *
 * Per-source-link click + unique counts for the date range, plus the
 * "first click ever for this set of sources" anchor used to default the
 * date range when the user opens a report.
 *
 * v3 parity:
 *  - One result row per source link, even when the link has zero clicks
 *    in range. v3's `PlpReport::get_clicks_by_links()` did the same via
 *    a per-link subquery; we use a single LEFT JOIN to cover the same
 *    case in one query.
 *  - Uniques use `COUNT(DISTINCT cl.vuid)`; clicks use `COUNT(cl.id)`.
 *  - No read-time bot filter — same rationale as
 *    `\PrettyLinks\Pro\Reports\Conversions` and
 *    `\PrettyLinks\Pro\Reports\SplitTest`. v4 blocks bots at write time.
 *  - Source links restricted to `deleted_at IS NULL` (the v4 canonical
 *    "live" predicate; v3 used `link_status = 'enabled'`).
 *
 * Conversions are NOT joined here. The controller layer composes
 * `summaryBySource()` + `Conversions::bySource()` so this class stays
 * single-purpose.
 */
final class CustomReports
{
    /**
     * Per-source-link click + unique counts within the date range.
     *
     * Always returns one row per id in `$sourceLinkIds` that points at a
     * non-deleted link; ids that have been hard-deleted are dropped from
     * the result so the UI can detect missing links by comparing the
     * returned link_ids against the configured set.
     *
     * @param array<int, int|string> $sourceLinkIds Source link ids to summarize.
     * @param string                 $from          Start of date range (YYYY-MM-DD, inclusive).
     * @param string                 $to            End of date range (YYYY-MM-DD, inclusive).
     *
     * @return array<int, array{link_id: int, name: string, clicks: int, uniques: int}>
     */
    public function summaryBySource(array $sourceLinkIds, string $from, string $to): array
    {
        if ($sourceLinkIds === []) {
            return [];
        }

        $ids = array_values(array_unique(array_map('intval', $sourceLinkIds)));
        $ids = array_values(array_filter($ids, static function (int $id): bool {
            return $id > 0;
        }));
        if ($ids === []) {
            return [];
        }

        global $wpdb;
        $clicks = $wpdb->prefix . 'prli_clicks';
        $links  = $wpdb->prefix . 'prli_links';

        $sdate = $from . ' 00:00:00';
        $edate = $to . ' 23:59:59';

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        // LEFT JOIN keeps zero-click sources in the result. Date-bounding
        // lives in the JOIN condition so excluded clicks don't kill the
        // row entirely (a WHERE clause would silently drop sources with
        // only out-of-range clicks).
        $sql = "SELECT li.id AS link_id,
                       li.name AS name,
                       li.slug AS slug,
                       li.url  AS url,
                       COUNT(cl.id) AS clicks,
                       COUNT(DISTINCT cl.vuid) AS uniques
                  FROM {$links} li
             LEFT JOIN {$clicks} cl
                    ON cl.link_id = li.id
                   AND cl.created_at BETWEEN %s AND %s
                 WHERE li.id IN ({$placeholders})
                   AND li.deleted_at IS NULL
              GROUP BY li.id
              ORDER BY li.name ASC";

        $args = array_merge([$sdate, $edate], $ids);

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare($sql, $args);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($prepared, ARRAY_A) ?: [];

        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'link_id' => (int) $row['link_id'],
                'name'    => (string) $row['name'],
                'slug'    => (string) ($row['slug'] ?? ''),
                'url'     => (string) ($row['url'] ?? ''),
                'clicks'  => (int) $row['clicks'],
                'uniques' => (int) $row['uniques'],
            ];
        }
        return $out;
    }

    /**
     * Earliest `prli_clicks.created_at` across the given source links,
     * formatted as `YYYY-MM-DD`. Used to default the report's date range
     * to "from the very first click ever recorded for these links".
     * Returns null if none of the sources have any clicks.
     *
     * Mirrors v3's `PrliUtils::getFirstClickDate()` / `PrliClick::get_first_date()`.
     *
     * @param array<int, int|string> $sourceLinkIds Source link ids to scan.
     */
    public function firstClickDate(array $sourceLinkIds): ?string
    {
        if ($sourceLinkIds === []) {
            return null;
        }

        $ids = array_values(array_unique(array_map('intval', $sourceLinkIds)));
        $ids = array_values(array_filter($ids, static function (int $id): bool {
            return $id > 0;
        }));
        if ($ids === []) {
            return null;
        }

        global $wpdb;
        $clicks = $wpdb->prefix . 'prli_clicks';

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        $sql = "SELECT MIN(created_at) AS first_at
                  FROM {$clicks}
                 WHERE link_id IN ({$placeholders})";

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare($sql, $ids);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $value = $wpdb->get_var($prepared);

        if ($value === null || $value === '') {
            return null;
        }

        if (preg_match('/^(\d{4}-\d{2}-\d{2})/', (string) $value, $m) === 1) {
            return $m[1];
        }
        return null;
    }
}
