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

/**
 * Conversion attribution primitive (Pro).
 *
 * Counts unique visitors (VUIDs) who hit a "source" link and then later hit
 * a "goal" link within the same date range. Used by Custom Reports
 * (multi-link goal tracking) and Split Test Reports (per-variant
 * conversion). No UI in this layer — Phase 4 / 5 controllers consume
 * the methods directly.
 *
 * The shape is a drop-in match for v3's `PlpReport::get_conversions_by_links()`
 * and `PlpReport::get_split_conversions()` so 3.x admins see identical
 * numbers post-upgrade.
 *
 * Read-time bot exclusion: v4 blocks bots at write time in
 * `\PrettyLinks\Redirect\ClickWriter::writeRow()` (the row is never inserted
 * unless the UA passes BotDetector or the IP is on the allow list), so unlike
 * v3 we do NOT add a `WHERE robot = 0` clause here. Doing so would silently
 * drop legitimate counts for allow-listed IPs whose UAs happen to look bot-like.
 *
 * v3 SQL we mirror, for traceability.
 *
 * `PlpReport::get_conversions_by_links()` (v3, lines 211-242):
 *
 *   SELECT li.id as id, (
 *     SELECT COUNT(DISTINCT cl.vuid)
 *       FROM {prli_clicks} cl
 *      WHERE cl.link_id = li.id
 *        {exclude_clause}
 *        AND cl.vuid IN (
 *          SELECT DISTINCT cl2.vuid
 *            FROM {prli_clicks} cl2
 *           WHERE cl2.link_id = %d
 *             AND cl.created_at < cl2.created_at
 *             AND cl2.created_at BETWEEN %s AND %s
 *             {exclude_clause}
 *        )
 *        AND cl.created_at BETWEEN %s AND %s
 *   ) as conversions
 *   FROM {prli_links} li
 *  WHERE li.id IN (...)
 *    AND li.link_status = 'enabled'
 *  ORDER BY li.name
 *
 * `PlpReport::get_split_conversions()` (v3, lines 347-379):
 *
 *   SELECT cr.url as url, COUNT(DISTINCT cl.vuid) as conversions
 *     FROM {prli_clicks} cl
 *     JOIN {prli_clicks_rotations} cr ON cl.id = cr.click_id
 *    WHERE cl.link_id = %d
 *      {exclude_clause}
 *      AND cl.vuid IN (
 *        SELECT DISTINCT cl2.vuid
 *          FROM {prli_clicks} cl2
 *         WHERE cl2.link_id = %d
 *           AND cl.created_at < cl2.created_at
 *           {exclude_clause for cl2}
 *      )
 *      AND cl.created_at BETWEEN %s AND %s
 *    GROUP BY cr.url
 *
 * Note the asymmetry vs the custom-report query: v3's split-test inner
 * subquery does NOT bound `cl2.created_at` to the date range. A goal hit
 * outside the report's date range still counts toward a variant's
 * conversion total as long as the source click is in range. We preserve
 * that behavior verbatim — changing it would diverge from 3.x and surprise
 * upgraders comparing reports side-by-side.
 *
 * v4 schema notes:
 *  - v3 filtered source links by `link_status = 'enabled'`. v4 uses
 *    `deleted_at IS NULL` for soft-delete (the canonical "live" predicate
 *    across `Analytics`, `HealthChecker`, `EmailDigest`, etc.) — same
 *    intent: don't surface conversion rows for links the admin has trashed.
 *    `link_status` still exists in the schema (additive compat) but is no
 *    longer the source of truth.
 *  - `bySource()` still returns `0` for input link IDs that exist but have
 *    no conversions — callers (Custom Reports controller) rely on a fully
 *    populated map keyed by every requested ID.
 */
final class Conversions
{
    /**
     * Count, per source link, the unique visitors who hit that link and then
     * later hit the goal link within the date range.
     *
     * Always returns one entry per id in `$sourceLinkIds` (zero when no
     * conversions). Returns `[]` for an empty input — no query issued.
     *
     * @param array<int, int|string> $sourceLinkIds Source link ids to attribute conversions to.
     * @param integer                $goalLinkId    Goal link id (the click that "completes" a conversion).
     * @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, int> Map of source link id => conversion count.
     */
    public function bySource(array $sourceLinkIds, int $goalLinkId, 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 === [] || $goalLinkId <= 0) {
            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'));

        /*
         * Order of bound args matches placeholder order in the SQL string:
         *   1: goal_link_id (inner subquery cl2.link_id)
         *   2: sdate (cl2.created_at lower bound)
         *   3: edate (cl2.created_at upper bound)
         *   4: sdate (cl.created_at lower bound)
         *   5: edate (cl.created_at upper bound)
         *   6..N: source link ids for the outer IN ()
         */
        $args = array_merge([$goalLinkId, $sdate, $edate, $sdate, $edate], $ids);

        $sql = "SELECT li.id AS id, (
                    SELECT COUNT(DISTINCT cl.vuid)
                      FROM {$clicks} cl
                     WHERE cl.link_id = li.id
                       AND cl.vuid IN (
                           SELECT DISTINCT cl2.vuid
                             FROM {$clicks} cl2
                            WHERE cl2.link_id = %d
                              AND cl.created_at < cl2.created_at
                              AND cl2.created_at BETWEEN %s AND %s
                       )
                       AND cl.created_at BETWEEN %s AND %s
                ) AS conversions
                FROM {$links} li
               WHERE li.id IN ({$placeholders})
                 AND li.deleted_at IS NULL
               ORDER BY li.name";

        // 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) ?: [];

        // Seed every requested id with 0 so callers always get the full map.
        $out = array_fill_keys($ids, 0);
        foreach ($rows as $row) {
            $out[(int) $row['id']] = (int) $row['conversions'];
        }

        return $out;
    }

    /**
     * Count, per rotation variant URL of `$linkId`, the unique visitors who
     * hit that variant and then later hit the goal link.
     *
     * Returns `[ rotation_url => conversion_count ]`. Variants with zero
     * conversions are absent from the result (v3 parity — v3's
     * `get_split_conversions()` returns only rows from the GROUP BY and lets
     * the chart code default missing variants to zero).
     *
     * @param integer $linkId     Rotated parent link id whose variant clicks we count.
     * @param integer $goalLinkId Goal link id that completes a conversion.
     * @param string  $from       Start of date range (YYYY-MM-DD, inclusive).
     * @param string  $to         End of date range (YYYY-MM-DD, inclusive).
     *
     * @return array<string, int> Map of variant URL => conversion count.
     */
    public function byVariant(int $linkId, int $goalLinkId, string $from, string $to): array
    {
        if ($linkId <= 0 || $goalLinkId <= 0) {
            return [];
        }

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

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

        // V3 parity: the inner subquery bounds the source range but NOT the
        // goal hit's date. See class docblock for rationale.
        $sql = "SELECT cr.url AS url, COUNT(DISTINCT cl.vuid) AS conversions
                  FROM {$clicks} cl
                  JOIN {$rotations} cr ON cl.id = cr.click_id
                 WHERE cl.link_id = %d
                   AND cl.vuid IN (
                       SELECT DISTINCT cl2.vuid
                         FROM {$clicks} cl2
                        WHERE cl2.link_id = %d
                          AND cl.created_at < cl2.created_at
                   )
                   AND cl.created_at BETWEEN %s AND %s
              GROUP BY cr.url";

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

        $out = [];
        foreach ($rows as $row) {
            $out[(string) $row['url']] = (int) $row['conversions'];
        }

        return $out;
    }
}
