<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Rest;

// 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\Rest\Controllers\BaseController;
use PrettyLinks\Support\SiteDate;
use WP_REST_Request;
use WP_REST_Response;
use WP_REST_Server;

/**
 * Pro analytics endpoints consumed by the `clicks-pro-extras` bundle on
 * the Click History admin page.
 *
 *   GET /pro/reports/summary     — KPI cards (clicks, uniques, avg/day, links)
 *   GET /pro/reports/timeseries  — line-chart series (hour / day / month / year buckets)
 *   GET /pro/reports/top-links   — highest-performing links in range
 *   GET /pro/reports/breakdown   — grouped counts by country/referrer/device/browser
 *
 * All four share a common date-range contract (YYYY-MM-DD `from`/`to` in the
 * site timezone). ClickWriter stamps rows UTC; bounds are converted via
 * SiteDate before querying. Ranges are inclusive of the whole site-local day.
 *
 * Queries hit `prli_clicks` only — Simple/count-only tracking doesn't
 * populate that table, so this whole surface shows zeros under Simple mode.
 * The menu registrar hides the Click History page in that mode so the
 * bundle never loads there in practice.
 */
class ReportsController extends BaseController
{
    private const BREAKDOWN_COLUMNS = [
        'country'  => 'country',
        'referrer' => 'referer',
        'device'   => 'device_type',
        'browser'  => 'btype',
    ];

    /**
     * Register the Pro reports REST routes.
     *
     * @return void
     */
    public function register(): void
    {
        // `summary` and `top-links` live in Lite (`/reports/counters` and
        // `/reports/top-links`) so the Dashboard and Click History surfaces
        // can't disagree about the numbers. Pro only owns the endpoints
        // whose query shape is Pro-exclusive.
        $common = [
            'from' => ['type' => 'string'],
            'to'   => ['type' => 'string'],
        ];

        register_rest_route($this->namespace(), '/pro/reports/timeseries', [
            'methods'             => WP_REST_Server::READABLE,
            'callback'            => [$this, 'timeseries'],
            'permission_callback' => $this->permission(),
            'args'                => array_merge($common, [
                'granularity' => [
                    'type'    => 'string',
                    'enum'    => ['hour', 'day', 'month', 'year'],
                    'default' => 'day',
                ],
            ]),
        ]);

        register_rest_route($this->namespace(), '/pro/reports/breakdown', [
            'methods'             => WP_REST_Server::READABLE,
            'callback'            => [$this, 'breakdown'],
            'permission_callback' => $this->permission(),
            'args'                => array_merge($common, [
                'dimension' => [
                    'type'     => 'string',
                    'enum'     => array_keys(self::BREAKDOWN_COLUMNS),
                    'required' => true,
                ],
                'limit'     => [
                    'type'    => 'integer',
                    'default' => 25,
                ],
            ]),
        ]);
    }

    /**
     * Return a click time series for the requested range.
     *
     * @param WP_REST_Request $request Incoming REST request.
     *
     * @return WP_REST_Response
     */
    public function timeseries(WP_REST_Request $request): WP_REST_Response
    {
        global $wpdb;
        $clicks = $wpdb->prefix . 'prli_clicks';

        list($fromDt, $toDt, $fromYmd) = $this->dateBounds($request);
        $granularity                   = (string) $request->get_param('granularity');

        // Every bucket expression emits a sortable ISO-ish string that
        // round-trips through the frontend LineChart (it reads `bucket`
        // for the x-axis label). Using "first of period" for month/year
        // keeps the result parseable as a date if a consumer wants to
        // reformat it. Literal `%` signs in DATE_FORMAT must be doubled
        // because the expression is interpolated into the $wpdb->prepare()
        // template below, where `%` starts a placeholder.
        //
        // created_at is UTC. Hour buckets stay UTC datetimes so the JS
        // dateI18n path can convert labels. Day/month/year are date-only
        // strings, so GROUP BY the site-local calendar period or the chart
        // attributes clicks to the wrong day near timezone boundaries. The
        // offset comes from the same validated site-local $fromYmd as the SQL
        // bounds (not the raw request param) so CONVERT_TZ can't desync from
        // the filtered range.
        $siteLocal = SiteDate::sqlSiteLocalForDay('created_at', $fromYmd);
        switch ($granularity) {
            case 'hour':
                $bucketExpr = "DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H:00:00')";
                break;
            case 'month':
                $bucketExpr = "DATE_FORMAT({$siteLocal}, '%%Y-%%m-01')";
                break;
            case 'year':
                $bucketExpr = "DATE_FORMAT({$siteLocal}, '%%Y-01-01')";
                break;
            case 'day':
            default:
                $bucketExpr = "DATE({$siteLocal})";
                break;
        }

        $rows = (array) $wpdb->get_results(
            $wpdb->prepare(
                "SELECT {$bucketExpr} AS bucket,
                        COUNT(*) AS clicks,
                        COUNT(DISTINCT vuid) AS uniques
                   FROM {$clicks}
                  WHERE created_at BETWEEN %s AND %s
                  GROUP BY bucket
                  ORDER BY bucket ASC",
                $fromDt,
                $toDt
            ),
            ARRAY_A
        );

        $items = array_map(static function (array $r): array {
            return [
                'bucket'  => (string) ($r['bucket'] ?? ''),
                'clicks'  => (int) ($r['clicks'] ?? 0),
                'uniques' => (int) ($r['uniques'] ?? 0),
            ];
        }, $rows);

        return new WP_REST_Response($items);
    }

    /**
     * Return a dimensional click breakdown for the requested range.
     *
     * @param WP_REST_Request $request Incoming REST request.
     *
     * @return WP_REST_Response
     */
    public function breakdown(WP_REST_Request $request): WP_REST_Response
    {
        global $wpdb;
        $clicks = $wpdb->prefix . 'prli_clicks';

        $dimension = (string) $request->get_param('dimension');
        if (!isset(self::BREAKDOWN_COLUMNS[$dimension])) {
            return new WP_REST_Response([], 200);
        }
        $column = self::BREAKDOWN_COLUMNS[$dimension];

        list($fromDt, $toDt) = $this->dateBounds($request);
        $limit               = max(1, min(500, (int) $request->get_param('limit')));

        // Referrer: reduce to hostname so "https://google.com/search?q=a" and
        // "https://google.com/search?q=b" fold into one bucket. Other
        // dimensions are already categorical (country code, device_type,
        // btype) and go through as-is. "(direct)" label for empty referers
        // matches v3 and UA analytics conventions.
        if ($dimension === 'referrer') {
            $selectExpr = "CASE
                WHEN {$column} = '' OR {$column} IS NULL THEN '(direct)'
                ELSE SUBSTRING_INDEX(SUBSTRING_INDEX({$column}, '://', -1), '/', 1)
            END";
        } else {
            $selectExpr = "CASE
                WHEN {$column} = '' OR {$column} IS NULL THEN '(unknown)'
                ELSE {$column}
            END";
        }

        $rows = (array) $wpdb->get_results(
            $wpdb->prepare(
                "SELECT {$selectExpr} AS value,
                        COUNT(*) AS clicks
                   FROM {$clicks}
                  WHERE created_at BETWEEN %s AND %s
                  GROUP BY value
                  ORDER BY clicks DESC
                  LIMIT %d",
                $fromDt,
                $toDt,
                $limit
            ),
            ARRAY_A
        );

        $items = array_map(static function (array $r): array {
            return [
                'value'  => (string) ($r['value'] ?? ''),
                'clicks' => (int) ($r['clicks'] ?? 0),
            ];
        }, $rows);

        // Grand total for the date range — needed by the client to keep
        // Share % stable regardless of how many rows the UI has paged in.
        // Computing alongside the grouped query avoids the client having
        // to reconcile two endpoints for the same range.
        $total = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$clicks}
                  WHERE created_at BETWEEN %s AND %s",
                $fromDt,
                $toDt
            )
        );

        return new WP_REST_Response([
            'items' => $items,
            'total' => $total,
        ]);
    }

    /**
     * Resolve site-local YYYY-MM-DD `from`/`to` into inclusive UTC MySQL
     * datetimes plus the validated site-local Y-m-d strings. Missing/invalid
     * values fall back to last-30-days so the endpoint degrades gracefully
     * for callers that drop the params.
     *
     * @param WP_REST_Request $request Incoming REST request.
     *
     * @return array{0:string,1:string,2:string,3:string} UTC start, UTC end, site-local from, site-local to.
     */
    private function dateBounds(WP_REST_Request $request): array
    {
        list($from, $to) = SiteDate::resolvePickerRange(
            (string) $request->get_param('from'),
            (string) $request->get_param('to')
        );

        return [SiteDate::dayStartUtc($from), SiteDate::dayEndUtc($to), $from, $to];
    }
}
