<?php
/**
 * Admin_Stats — catalog summary and 24-hour activity counts, read by templates.
 */

declare(strict_types=1);

defined( 'ABSPATH' ) || exit;

// ORL-112 -- PCP suppressions with rationale (WordPress.org reviewer audit trail).
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom-table queries: prweb_metadata / prweb_events / prweb_readers have no WP API equivalent.

class Prweb_Admin_Stats {

    private const TABLE        = 'metadata';
    private const EVENTS_TABLE = 'events';
    private const PENDING_KINDS = [
        'token.requested',
        'oauth.reader_register_started',
        'license.requested',
        'reader.register.start',
    ];

    public function catalog_summary(): array {
        global $wpdb;
        $table = Prweb_Brand::table( self::TABLE );
        $rows  = $wpdb->get_results( $wpdb->prepare( 'SELECT status, pds_dab_id FROM %i', $table ) );
        $reg = $rev = 0;
        foreach ( $rows as $row ) {
            if ( 'revoked' === $row->status ) {
                $rev++;
            } elseif ( $row->pds_dab_id ) {
                $reg++;
            }
        }
        $total = (int) wp_count_posts( 'post' )->publish;
        $unreg = max( 0, $total - $reg - $rev );
        return [
            'total'        => $total,
            'registered'   => $reg,
            'revoked'      => $rev,
            'unregistered' => $unreg,
        ];
    }

    public function activity_24h(): array {
        $deals      = $this->count_recent_kinds( Prweb_License_Stats_Aggregator::grant_kinds(), 1 );
        $denied     = $this->count_recent_kinds( Prweb_License_Stats_Aggregator::deny_kinds(), 1 );
        $registered = $this->count_recent_kinds( [ 'asset.delivered', 'asset.register.ok' ], 1 );
        return [
            'deals'      => $deals,
            'denied'     => $denied,
            'registered' => $registered,
        ];
    }

    public function money_kpis_30d(): array {
        $days  = 30;
        $rows  = $this->event_rows_for_kinds(
            Prweb_License_Stats_Aggregator::event_kinds(),
            $days,
            5000
        );
        $daily = [];
        for ( $i = $days - 1; $i >= 0; $i-- ) {
            $date = gmdate( 'Y-m-d', time() - ( $i * DAY_IN_SECONDS ) );
            $daily[ $date ] = [
                'date'           => $date,
                'earnings_cents' => 0,
                'crawls'         => 0,
                'has_data'       => false,
            ];
        }

        $summary = Prweb_License_Stats_Aggregator::summarize(
            $rows,
            [ 'global' ],
            static fn( array $payload ): string => 'global'
        )['global'];
        foreach ( $summary['daily'] as $date => $day ) {
            if ( isset( $daily[ $date ] ) ) {
                $daily[ $date ] = array_merge( $daily[ $date ], $day );
            }
        }

        return [
            'earnings_cents_30d' => $summary['earnings_cents'],
            'licensed_crawls_30d'=> $summary['licensed_crawls'],
            'active_buyers_30d'  => $summary['buyer_count'],
            'refused_402_30d'    => $summary['refused_402'],
            'sparkline_daily'    => array_values( $daily ),
        ];
    }

    public function asset_stats_30d( array $assets ): array {
        $stats = [];
        $lookup = [
            'post'     => [],
            'dab'      => [],
            'offer'    => [],
            'resource' => [],
            'path'     => [],
        ];
        foreach ( $assets as $asset ) {
            $post_id = (int) ( $asset['post_id'] ?? 0 );
            if ( $post_id <= 0 ) {
                continue;
            }
            $stats[ $post_id ] = [
                'earnings_cents_30d' => 0,
                'crawls_30d'         => 0,
                'refused_30d'        => 0,
                'buyers_30d'         => 0,
                'buyer_ids'          => [],
            ];
            $lookup['post'][ $post_id ] = $post_id;
            $dab_id = (int) ( $asset['dab_id'] ?? 0 );
            if ( $dab_id > 0 ) {
                $lookup['dab'][ $dab_id ] = $post_id;
            }
            $offer_id = (string) ( $asset['offer_id'] ?? '' );
            if ( $offer_id !== '' ) {
                $lookup['offer'][ $offer_id ] = $post_id;
            }
            $resource = (string) ( $asset['url'] ?? '' );
            if ( $resource !== '' ) {
                $lookup['resource'][ $resource ] = $post_id;
                $path = $this->resource_path( $resource );
                if ( $path !== '' ) {
                    $lookup['path'][ $path ] = $post_id;
                }
            }
        }

        if ( empty( $stats ) ) {
            return [];
        }

        $rows = $this->event_rows_for_kinds(
            Prweb_License_Stats_Aggregator::event_kinds(),
            30,
            5000
        );
        $summaries = Prweb_License_Stats_Aggregator::summarize(
            $rows,
            array_keys( $stats ),
            fn( array $payload ): int => $this->match_asset_stat_post_id( $payload, $lookup )
        );
        foreach ( $stats as $post_id => $row ) {
            $summary = $summaries[ (string) $post_id ];
            $stats[ $post_id ]['earnings_cents_30d'] = $summary['earnings_cents'];
            $stats[ $post_id ]['crawls_30d'] = $summary['licensed_crawls'];
            $stats[ $post_id ]['refused_30d'] = $summary['denied_total'];
            $stats[ $post_id ]['buyers_30d'] = $summary['buyer_count'];
            unset( $stats[ $post_id ]['buyer_ids'] );
        }
        return $stats;
    }

    public function daily_driver_activation_30d(): array {
        global $wpdb;
        $events  = Prweb_Brand::table( self::EVENTS_TABLE );
        $columns = (array) $wpdb->get_col( $wpdb->prepare( 'DESC %i', $events ), 0 );
        // Fixed column lists (no interpolated fragment) so the query carries no
        // parameter the analyzer cannot prove safe; table quoted via %i.
        $has_subject_column = in_array( 'subject_user', $columns, true );
        if ( $has_subject_column ) {
            $rows = (array) $wpdb->get_results( $wpdb->prepare(
                "SELECT kind, payload, occurred_at, subject_user
                 FROM %i
                 WHERE kind IN ('oauth.consent_granted','offer.published','offer.updated','bundle.created','bundle.updated','buyer.status_changed')
                   AND occurred_at >= UTC_TIMESTAMP() - INTERVAL 30 DAY
                 ORDER BY occurred_at ASC
                 LIMIT 5000",
                $events
            ) );
        } else {
            $rows = (array) $wpdb->get_results( $wpdb->prepare(
                "SELECT kind, payload, occurred_at
                 FROM %i
                 WHERE kind IN ('oauth.consent_granted','offer.published','offer.updated','bundle.created','bundle.updated','buyer.status_changed')
                   AND occurred_at >= UTC_TIMESTAMP() - INTERVAL 30 DAY
                 ORDER BY occurred_at ASC
                 LIMIT 5000",
                $events
            ) );
        }

        $cohort = [];
        $first_offer_publish = [];
        $rsl_publish = [];
        $daily_action = [];
        foreach ( $rows as $row ) {
            $payload = $this->decode_payload( $row->payload ?? '' );
            $subject = '';
            if ( $has_subject_column && isset( $row->subject_user ) ) {
                $subject = (string) $row->subject_user;
            }
            if ( $subject === '' && isset( $payload['subject_user'] ) ) {
                $subject = (string) $payload['subject_user'];
            }
            if ( $subject === '' ) {
                $subject = 'site';
            }

            $ts = strtotime( (string) $row->occurred_at );
            if ( $ts === false ) {
                continue;
            }
            if ( (string) $row->kind === 'oauth.consent_granted' ) {
                $cohort[ $subject ] = $cohort[ $subject ] ?? $ts;
                continue;
            }
            if ( ! isset( $cohort[ $subject ] ) ) {
                continue;
            }
            $days_since_consent = ( $ts - $cohort[ $subject ] ) / DAY_IN_SECONDS;
            if ( (string) $row->kind === 'offer.published' && $days_since_consent <= 7 ) {
                if ( (string) ( $payload['offer_id'] ?? '' ) === 'rsl-document' ) {
                    $rsl_publish[ $subject ] = true;
                } else {
                    $first_offer_publish[ $subject ] = true;
                }
            }
            if ( in_array( (string) $row->kind, [ 'offer.updated', 'bundle.created', 'bundle.updated', 'buyer.status_changed' ], true ) && $days_since_consent <= 14 ) {
                $daily_action[ $subject ] = true;
            }
        }

        $cohort_count = count( $cohort );
        $first_publish = array_intersect_key( $first_offer_publish, $rsl_publish );
        return [
            'cohort_size' => $cohort_count,
            'first_publish_size' => count( $first_publish ),
            'daily_driver_size' => count( $daily_action ),
            'first_publish_rate' => $cohort_count > 0 ? round( count( $first_publish ) / $cohort_count * 100, 1 ) : null,
            'daily_driver_rate' => $cohort_count > 0 ? round( count( $daily_action ) / $cohort_count * 100, 1 ) : null,
        ];
    }

    private function count_recent_kinds( array $kinds, int $window_days ): int {
        global $wpdb;
        if ( empty( $kinds ) ) {
            return 0;
        }
        $events = Prweb_Brand::table( self::EVENTS_TABLE );
        $placeholders = implode( ',', array_fill( 0, count( $kinds ), '%s' ) );
        $params = array_merge( [ $events ], array_values( $kinds ) );
        $params[] = max( 1, $window_days );
        // The replacement count is right at runtime -- %i + one %s per kind + %d,
        // matching $params exactly -- but the sniff counts only the literal
        // placeholders and cannot expand {$placeholders}, so it sees 2 and one arg.
        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- see above.
        return (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE kind IN ({$placeholders}) AND occurred_at > UTC_TIMESTAMP() - INTERVAL %d DAY",
            $params
        ) );
    }

    private function event_rows_for_kinds( array $kinds, int $window_days, int $limit ): array {
        global $wpdb;
        if ( empty( $kinds ) ) {
            return [];
        }
        $placeholders = implode( ',', array_fill( 0, count( $kinds ), '%s' ) );
        $params       = array_merge( [ Prweb_Brand::table( self::EVENTS_TABLE ) ], array_values( $kinds ) );
        $params[]     = max( 1, min( 365, $window_days ) );
        $params[]     = max( 1, min( 10000, $limit ) );
        // Select the newest capped window so a high-volume publisher does not
        // lose recent licenses and settlements, then restore chronological
        // order for deterministic correlation and replay de-duplication.
        $sql = "SELECT kind, request_id, payload, occurred_at
                FROM (
                    SELECT kind, request_id, payload, occurred_at
                    FROM %i
                    WHERE kind IN ({$placeholders})
                      AND occurred_at >= UTC_TIMESTAMP() - INTERVAL %d DAY
                    ORDER BY occurred_at DESC
                    LIMIT %d
                ) AS recent_events
                ORDER BY occurred_at ASC";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is static SQL plus a %s-only IN() placeholder list; the table is a %i placeholder and every value is bound via prepare(). No interpolated user data.
        return (array) $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
    }

    private function decode_payload( mixed $payload ): array {
        $decoded = json_decode( (string) $payload, true );
        return is_array( $decoded ) ? $decoded : [];
    }

    private function resource_path( string $resource ): string {
        $path = (string) ( wp_parse_url( $resource, PHP_URL_PATH ) ?: '' );
        return $path !== '' ? $path : $resource;
    }

    private function match_asset_stat_post_id( array $payload, array $lookup ): int {
        $post_id = (int) ( $payload['post_id'] ?? $payload['postId'] ?? 0 );
        if ( $post_id > 0 && isset( $lookup['post'][ $post_id ] ) ) {
            return $post_id;
        }
        $dab_id = (int) ( $payload['dab_id'] ?? $payload['dabId'] ?? 0 );
        if ( $dab_id > 0 && isset( $lookup['dab'][ $dab_id ] ) ) {
            return (int) $lookup['dab'][ $dab_id ];
        }
        $offer_id = (string) ( $payload['offer_id'] ?? $payload['offerId'] ?? '' );
        if ( $offer_id !== '' && isset( $lookup['offer'][ $offer_id ] ) ) {
            return (int) $lookup['offer'][ $offer_id ];
        }
        $resource = (string) ( $payload['resource'] ?? $payload['url'] ?? '' );
        if ( $resource !== '' ) {
            if ( isset( $lookup['resource'][ $resource ] ) ) {
                return (int) $lookup['resource'][ $resource ];
            }
            $path = $this->resource_path( $resource );
            if ( $path !== '' && isset( $lookup['path'][ $path ] ) ) {
                return (int) $lookup['path'][ $path ];
            }
        }
        return 0;
    }

    public function ttfl_proxy_summary( int $window_days = 7 ): array {
        global $wpdb;
        $events = Prweb_Brand::table( self::EVENTS_TABLE );
        $window_days = max( 1, min( 90, $window_days ) );
        $cutoff = time() - ( $window_days * DAY_IN_SECONDS );

        $columns = (array) $wpdb->get_col( $wpdb->prepare( 'DESC %i', $events ), 0 );

        // Fixed column lists (no interpolated fragment); table quoted via %i.
        $has_subject_column = in_array( 'subject_user', $columns, true );
        if ( $has_subject_column ) {
            $rows = $wpdb->get_results( $wpdb->prepare(
                "SELECT kind, payload, occurred_at, subject_user
                 FROM %i
                 WHERE kind IN ('oauth.consent_granted', 'wizard.step_started', 'wizard.step_completed', 'offer.published')
                 ORDER BY occurred_at ASC
                 LIMIT 2000",
                $events
            ) );
        } else {
            $rows = $wpdb->get_results( $wpdb->prepare(
                "SELECT kind, payload, occurred_at
                 FROM %i
                 WHERE kind IN ('oauth.consent_granted', 'wizard.step_started', 'wizard.step_completed', 'offer.published')
                 ORDER BY occurred_at ASC
                 LIMIT 2000",
                $events
            ) );
        }

        $connected = [];
        $published = [];

        foreach ( (array) $rows as $row ) {
            $ts = strtotime( (string) $row->occurred_at );
            if ( $ts === false || $ts < $cutoff ) {
                continue;
            }
            $payload = json_decode( (string) $row->payload, true );
            if ( ! is_array( $payload ) ) {
                $payload = [];
            }
            $subject = '';
            if ( $has_subject_column && isset( $row->subject_user ) ) {
                $subject = (string) $row->subject_user;
            }
            if ( $subject === '' && isset( $payload['subject_user'] ) ) {
                $subject = (string) $payload['subject_user'];
            }
            if ( $subject === '' ) {
                $subject = 'site';
            }

            if ( 'oauth.consent_granted' === $row->kind ) {
                $connected[ $subject ] = $connected[ $subject ] ?? $ts;
            }

            if ( 'wizard.step_started' === $row->kind && ( $payload['step_id'] ?? '' ) === 'wallet_ready' ) {
                $connected[ $subject ] = $connected[ $subject ] ?? $ts;
            }

            if ( 'offer.published' === $row->kind && (string) ( $payload['offer_id'] ?? '' ) !== 'rsl-document' ) {
                $published[ $subject ] = $published[ $subject ] ?? $ts;
            }
        }

        $durations = [];
        foreach ( $connected as $subject => $start ) {
            if ( isset( $published[ $subject ] ) && $published[ $subject ] >= $start ) {
                $durations[] = $published[ $subject ] - $start;
            }
        }
        sort( $durations );

        return [
            'window_days'    => $window_days,
            'cohort_size'    => count( $connected ),
            'completed_size' => count( $durations ),
            'median_minutes' => $this->percentile_minutes( $durations, 50 ),
            'p90_minutes'    => $this->percentile_minutes( $durations, 90 ),
            'target_minutes' => 15,
            'target_ready'   => count( $connected ) >= 10,
        ];
    }

    private function percentile_minutes( array $seconds, int $percentile ): ?float {
        $count = count( $seconds );
        if ( $count === 0 ) {
            return null;
        }
        $rank = ( $percentile / 100 ) * ( $count - 1 );
        $lo = (int) floor( $rank );
        $hi = (int) ceil( $rank );
        if ( $lo === $hi ) {
            return round( $seconds[ $lo ] / 60, 1 );
        }
        $weight = $rank - $lo;
        $value = $seconds[ $lo ] + ( ( $seconds[ $hi ] - $seconds[ $lo ] ) * $weight );
        return round( $value / 60, 1 );
    }
}