<?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.
use PrettyLinks\Pro\Rotation\RotationsRepository;
use PrettyLinks\Repositories\LinkMetas;
use PrettyLinks\Repositories\Links as LinksRepo;
use PrettyLinks\Support\SiteDate;

/**
 * Split-test analytics primitive (Pro).
 *
 * Backs the per-link split-test report — given a link with rotation enabled
 * and split-test on, returns per-variant click + unique counts and an
 * optional per-variant time-series for charting.
 *
 * v3 parity:
 *  - Variants come from `prli_clicks_rotations.url` GROUP BY — the URL
 *    string the redirect engine actually served at click time. That means
 *    rotation rows that have since been deleted (or had their URL changed)
 *    still surface in historical reporting, identified by the URL string
 *    they were served as. Caller compares against `currentVariants()` to
 *    flag the "(removed)" badge.
 *  - Uniques use `COUNT(DISTINCT cl.vuid)`, clicks use `COUNT(cl.id)`.
 *  - No read-time bot filter — v4 blocks bots at write time
 *    (`\PrettyLinks\Redirect\ClickWriter`), so adding `WHERE robot = 0`
 *    here would drop legitimate allow-listed humans. Same rationale as
 *    `\PrettyLinks\Pro\Reports\Conversions`.
 *  - Conversions are intentionally NOT joined here — the controller layer
 *    composes summary + `Conversions::byVariant()` so this class stays
 *    focused on click data.
 *
 * Meta keys are the v3-canonical hyphenated form (`prli-enable-split-test`,
 * `prli-split-test-goal-link`) so a 3.x → 4.0 in-place upgrade reads the
 * existing rows untouched.
 */
final class SplitTest
{
    public const META_ENABLED   = 'prli-enable-split-test';
    public const META_GOAL_LINK = 'prli-split-test-goal-link';

    /**
     * The links repository.
     *
     * @var LinksRepo
     */
    private LinksRepo $links;

    /**
     * The link metas repository.
     *
     * @var LinkMetas
     */
    private LinkMetas $linkMetas;

    /**
     * Store the repository dependencies.
     *
     * @param LinksRepo $links     The links repository.
     * @param LinkMetas $linkMetas The link metas repository.
     */
    public function __construct(LinksRepo $links, LinkMetas $linkMetas)
    {
        $this->links     = $links;
        $this->linkMetas = $linkMetas;
    }

    /**
     * Per-variant click + unique counts for a date range.
     *
     * Returns one row per distinct `prli_clicks_rotations.url` that has at
     * least one click in range. Variants with zero clicks are omitted —
     * the controller layer fills them in from `currentVariants()` so the
     * UI always shows the configured rotations.
     *
     * @param  integer $linkId The link ID to summarize.
     * @param  string  $from   The range start date (Y-m-d).
     * @param  string  $to     The range end date (Y-m-d).
     * @return array<int, array{url: string, clicks: int, uniques: int}>
     */
    public function summaryByVariant(int $linkId, string $from, string $to): array
    {
        if ($linkId <= 0) {
            return [];
        }

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

        // $from/$to are site-local calendar days; created_at is UTC.
        $sdate = SiteDate::dayStartUtc($from);
        $edate = SiteDate::dayEndUtc($to);

        $sql = "SELECT cr.url AS url,
                       COUNT(cl.id) AS clicks,
                       COUNT(DISTINCT cl.vuid) AS uniques
                  FROM {$clicks} cl
                  JOIN {$rotations} cr ON cl.id = cr.click_id
                 WHERE cl.link_id = %d
                   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, $sdate, $edate);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($prepared, ARRAY_A) ?: [];

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

    /**
     * Per-variant time-series for the chart. One series per variant URL,
     * each series is an array of `{date, clicks}` rows ordered ASC.
     *
     * Granularity:
     *  - 'day'  → groups by site-local `DATE(CONVERT_TZ(cl.created_at, …))`
     *  - 'hour' → groups by `DATE_FORMAT(cl.created_at, '%%Y-%%m-%%d %%H:00:00')` (UTC)
     *
     * Like `summaryByVariant()`, only variants with at least one click in
     * range are returned; zero-volume variants are filled in by the
     * controller from the current rotation config.
     *
     * @param  integer $linkId      The link ID to chart.
     * @param  string  $from        The range start date (Y-m-d).
     * @param  string  $to          The range end date (Y-m-d).
     * @param  string  $granularity The bucket granularity, 'day' or 'hour'.
     * @return array<int, array{url: string, series: array<int, array{date: string, clicks: int}>}>
     */
    public function timeseriesByVariant(int $linkId, string $from, string $to, string $granularity = 'day'): array
    {
        if ($linkId <= 0) {
            return [];
        }

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

        // $from/$to are site-local calendar days; created_at is UTC.
        $sdate = SiteDate::dayStartUtc($from);
        $edate = SiteDate::dayEndUtc($to);

        // Literal `%` in DATE_FORMAT doubled for the $wpdb->prepare pass below.
        // Hour buckets stay UTC datetimes (JS converts). Day buckets are
        // date-only, so GROUP BY site-local calendar day.
        $bucket = $granularity === 'hour'
            ? "DATE_FORMAT(cl.created_at, '%%Y-%%m-%%d %%H:00:00')"
            : 'DATE(' . SiteDate::sqlSiteLocalForDay('cl.created_at', $from) . ')';

        $sql = "SELECT cr.url AS url,
                       {$bucket} AS bucket,
                       COUNT(cl.id) AS clicks
                  FROM {$clicks} cl
                  JOIN {$rotations} cr ON cl.id = cr.click_id
                 WHERE cl.link_id = %d
                   AND cl.created_at BETWEEN %s AND %s
              GROUP BY cr.url, bucket
              ORDER BY cr.url ASC, bucket ASC";

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

        $byUrl = [];
        foreach ($rows as $row) {
            $url = (string) $row['url'];
            if (!isset($byUrl[$url])) {
                $byUrl[$url] = [];
            }
            $byUrl[$url][] = [
                'date'   => (string) $row['bucket'],
                'clicks' => (int) $row['clicks'],
            ];
        }

        $out = [];
        foreach ($byUrl as $url => $series) {
            $out[] = [
                'url'    => $url,
                'series' => $series,
            ];
        }
        return $out;
    }

    /**
     * The currently-configured rotation variants for a link: the link's own
     * `url` followed by every `prli_link_rotations.url` row (ordered by
     * position). Empty strings are stripped to match v3
     * `get_split_report_links_array()`.
     *
     * Returns an empty array if the link doesn't exist.
     *
     * @param  integer $linkId The link ID to read variants for.
     * @return array<int, string>
     */
    public function currentVariants(int $linkId): array
    {
        if ($linkId <= 0) {
            return [];
        }

        $link = $this->links->find($linkId);
        if ($link === null) {
            return [];
        }

        $variants = [];
        $baseUrl  = trim((string) ($link['url'] ?? ''));
        if ($baseUrl !== '') {
            $variants[] = $baseUrl;
        }

        $rotations = (new RotationsRepository())->forLink($linkId);
        foreach ($rotations as $row) {
            $url = trim((string) ($row['url'] ?? ''));
            if ($url !== '') {
                $variants[] = $url;
            }
        }
        return $variants;
    }

    /**
     * The configured goal link id for this link's split test, or null when
     * no goal is set. Reads `prli-split-test-goal-link` link meta.
     *
     * @param integer $linkId The link ID to read the goal for.
     *
     * @return integer|null The goal link ID, or null when none is set.
     */
    public function goalLinkId(int $linkId): ?int
    {
        if ($linkId <= 0) {
            return null;
        }
        $raw = $this->linkMetas->get($linkId, self::META_GOAL_LINK, null);
        if ($raw === null || $raw === '' || $raw === '0') {
            return null;
        }
        $id = (int) $raw;
        return $id > 0 ? $id : null;
    }

    /**
     * Whether split testing is enabled for this link
     * (`prli-enable-split-test` meta is truthy).
     *
     * V3 stored the meta value as the literal string `'1'` when the
     * checkbox was on and `''` (or absent) when off. Treat any non-empty
     * non-`0` string as enabled to handle both.
     *
     * @param integer $linkId The link ID to check.
     *
     * @return boolean True when split testing is enabled.
     */
    public function isSplitTestEnabled(int $linkId): bool
    {
        if ($linkId <= 0) {
            return false;
        }
        $raw = $this->linkMetas->get($linkId, self::META_ENABLED, null);
        if ($raw === null || $raw === '' || $raw === '0') {
            return false;
        }
        return true;
    }
}
