<?php
if ( ! defined( 'ABSPATH' ) ) { exit; }

/**
 * Search analytics for Glance Live Search.
 *
 * One row per typing session, identified by a client-generated search_id.
 * The click is a column on that row rather than a separate table, which is
 * what makes the success rate exact: numerator and denominator come from the
 * same population by construction.
 *
 * Recording searches on an idle timer while recording clicks immediately
 * breaks that guarantee: a visitor who types and clicks straight away - the
 * successful search - produces a click with no matching search row, and the
 * rate is then computed across disjoint populations, biased against exactly
 * the searches that worked.
 *
 * @since 1.6.1
 */
final class GLSE_Analytics {

    /** Table name without WordPress prefix. */
    const TABLE = 'glse_searches';

    /** Legacy tables, dropped on upgrade. */
    const LEGACY_TABLES = [ 'glse_analytics', 'glse_clicks', 'glse_index' ];

    /**
     * Ou une recherche peut mener, et ce que chaque destination veut dire.
     *
     * `product` et `category` sont deux facons d'aboutir : le client a trouve
     * une destination. `all` en est une troisieme, et elle ne dit pas la meme
     * chose - demander la page complete signifie souvent que le panneau n'a pas
     * montre ce qu'il fallait. Elle est donc comptee a part, jamais fondue dans
     * le taux de reussite.
     */
    const TARGETS = [ 'product', 'category', 'all' ];

    /** Les destinations qui valent une reussite. */
    const TARGETS_SUCCESS = [ 'product', 'category' ];

    /* ─── Schema ─────────────────────────────── */

    /**
     * Creates (or updates) the table via dbDelta.
     */
    public static function create_table(): void {
        global $wpdb;

        $t  = $wpdb->prefix . self::TABLE;
        $cc = $wpdb->get_charset_collate();

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        /*
         * search_id is UNIQUE so recording is a plain upsert: every AJAX call
         * of the same typing session refines one row instead of appending.
         * idx_zero serves the no-result panel without a filesort.
         */
        dbDelta( "CREATE TABLE {$t} (
            id            BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
            search_id     CHAR(32)          NOT NULL DEFAULT '',
            query         VARCHAR(255)      NOT NULL DEFAULT '',
            results_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            searched_at   DATETIME          NOT NULL,
            clicked_at    DATETIME          NULL DEFAULT NULL,
            product_id    BIGINT UNSIGNED   NULL DEFAULT NULL,
            position      TINYINT UNSIGNED  NULL DEFAULT NULL,
            target        VARCHAR(12)       NOT NULL DEFAULT 'product',
            PRIMARY KEY  (id),
            UNIQUE KEY uniq_search (search_id),
            KEY idx_date  (searched_at),
            KEY idx_query (query(100)),
            KEY idx_zero  (results_count, searched_at)
        ) {$cc};" );
    }

    /**
     * Drops the table. Called on uninstall.
     */
    public static function drop_table(): void {
        global $wpdb;
        $wpdb->query( 'DROP TABLE IF EXISTS ' . $wpdb->prefix . self::TABLE ); // phpcs:ignore WordPress.DB
    }

    /**
     * Drops the pre-1.7 tables.
     *
     * Their data cannot be migrated meaningfully: rows recorded under the old
     * scheme have no search_id, so their click correlation - and therefore
     * every rate derived from it - was wrong. Carrying them forward would only
     * preserve misleading numbers.
     */
    public static function drop_legacy_tables(): void {
        global $wpdb;
        foreach ( self::LEGACY_TABLES as $legacy ) {
            $wpdb->query( 'DROP TABLE IF EXISTS ' . $wpdb->prefix . $legacy ); // phpcs:ignore WordPress.DB
        }
    }

    /* ─── Write ──────────────────────────────── */

    /**
     * Records (or refines) a search.
     *
     * Called from the search request itself, so no extra round trip and no
     * timer: the row exists before the visitor can possibly click. Repeated
     * calls within one typing session share a search_id and update the same
     * row, so "shi" then "shirt" is one search whose final term is "shirt".
     *
     * @param string $search_id     Client-generated id, unique per typing session.
     * @param string $query         Search term.
     * @param int    $results_count Number of results shown.
     */
    public static function record( string $search_id, string $query, int $results_count ): void {
        if ( '' === trim( $query ) || ! self::is_valid_search_id( $search_id ) ) {
            return;
        }

        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = $wpdb->prepare(
            "INSERT INTO {$t} (search_id, query, results_count, searched_at)
             VALUES (%s, %s, %d, %s)
             ON DUPLICATE KEY UPDATE query = VALUES(query), results_count = VALUES(results_count)",
            $search_id,
            mb_substr( $query, 0, 255 ),
            max( 0, min( 65535, $results_count ) ),
            current_time( 'mysql', true )
        );

        $hidden = $wpdb->hide_errors();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $ok = $wpdb->query( $sql );
        $wpdb->show_errors( $hidden );

        if ( false !== $ok ) {
            return;
        }

        if ( ! str_contains( strtolower( (string) $wpdb->last_error ), "doesn't exist" ) ) {
            return;
        }

        self::create_table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query( $sql );
    }

    

    /**
     * A search id is client-generated, so treat it as untrusted input and
     * accept only the exact shape we emit.
     */
    private static function is_valid_search_id( string $search_id ): bool {
        return 1 === preg_match( '/^[a-f0-9]{32}$/', $search_id );
    }

    /**
     * Deletes every recorded search.
     *
     * DELETE rather than TRUNCATE, for the reason already written down beside
     * the suggestion table: TRUNCATE needs the DROP privilege, which plenty of
     * shared hosts withhold from the WordPress user. This method used it anyway
     * and returned nothing, so on such a host the merchant was shown
     * "statistics cleared" while every row was still there.
     *
     * @return bool False when the database refused, so the caller can say so.
     */
    public static function clear(): bool {
        global $wpdb;

        $hidden = $wpdb->hide_errors();
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name comes from a class constant.
        $ok = $wpdb->query( 'DELETE FROM ' . $wpdb->prefix . self::TABLE );
        $wpdb->show_errors( $hidden );

        return false !== $ok;
    }

    /**
     * Deletes records older than $days days.
     *
     * @return int Number of deleted rows.
     */
    public static function purge_before( int $days ): int {
        if ( $days <= 0 ) {
            return 0;
        }

        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return (int) $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$t} WHERE searched_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)",
            $days
        ) );
    }

    /* ─── Read ───────────────────────────────── */

    /**
     * Headline figures for a period, plus the same figures for the period
     * immediately before it so every number can be shown with a trend.
     *
     * A single scan produces both, which is why they are returned together
     * rather than through one getter per metric.
     *
     * @param int $days Length of the period in days.
     * @return array{searches:int, clicked:int, zero:int, prev_searches:int, prev_clicked:int, prev_zero:int}
     */
    public static function get_summary( int $days ): array {
        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY))                       AS searches,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND clicked_at IS NOT NULL AND target <> 'all') AS clicked,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND clicked_at IS NOT NULL AND target = 'product')  AS opened_product,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND clicked_at IS NOT NULL AND target = 'category') AS opened_category,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND clicked_at IS NOT NULL AND target = 'all')      AS viewed_all,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND results_count = 0) AS zero,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND searched_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)) AS prev_searches,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND searched_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND clicked_at IS NOT NULL) AS prev_clicked,
                SUM(searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND searched_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) AND results_count = 0) AS prev_zero
             FROM {$t}
             WHERE searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)",
            // Six colonnes sur la periode courante : recherches, abouties,
            // produit, categorie, page complete, sans resultat.
            $days, $days, $days, $days, $days, $days,
            $days * 2, $days,
            $days * 2, $days,
            $days * 2, $days,
            $days * 2
        ), ARRAY_A );

        /*
         * `clicked` compte les recherches abouties : produit ou categorie. La
         * demande de page complete est renvoyee a part - elle dit souvent que
         * le panneau n'a pas montre le bon produit, et la fondre dans le taux
         * flatterait le chiffre en masquant ce signal.
         */
        return [
            'searches'      => (int) ( $row['searches']      ?? 0 ),
            'clicked'         => (int) ( $row['clicked']         ?? 0 ),
            'opened_product'  => (int) ( $row['opened_product']  ?? 0 ),
            'opened_category' => (int) ( $row['opened_category'] ?? 0 ),
            'viewed_all'      => (int) ( $row['viewed_all']      ?? 0 ),
            'zero'          => (int) ( $row['zero']          ?? 0 ),
            'prev_searches' => (int) ( $row['prev_searches'] ?? 0 ),
            'prev_clicked'  => (int) ( $row['prev_clicked']  ?? 0 ),
            'prev_zero'     => (int) ( $row['prev_zero']     ?? 0 ),
        ];
    }

    /**
     * Daily search volume, zero-filled so the chart has no gaps.
     *
     * @return array<string, int> Y-m-d (site timezone) => count.
     */
    public static function get_daily_volume( int $days ): array {
        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        /*
         * searched_at is UTC; bucket by local day so "today" on the chart is
         * the merchant's today. Seconds rather than hours keeps half-hour and
         * quarter-hour zones correct.
         */
        $offset = (int) wp_timezone()->getOffset( new DateTimeImmutable( 'now', new DateTimeZone( 'UTC' ) ) );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT DATE(DATE_ADD(searched_at, INTERVAL %d SECOND)) AS day, COUNT(*) AS total
             FROM {$t}
             WHERE searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)
             GROUP BY 1",
            $offset,
            $days
        ), ARRAY_A );

        $counts = [];
        foreach ( $rows as $row ) {
            $counts[ (string) $row['day'] ] = (int) $row['total'];
        }

        $out = [];
        $tz  = wp_timezone();
        for ( $i = $days - 1; $i >= 0; $i-- ) {
            $day         = ( new DateTimeImmutable( "-{$i} days", $tz ) )->format( 'Y-m-d' );
            $out[ $day ] = $counts[ $day ] ?? 0;
        }

        return $out;
    }

    /**
     * Most searched terms with the success rate of each.
     *
     * Volume alone says what people want; volume paired with success says
     * which of those wants the catalogue is failing to answer - that is the
     * line the merchant can actually act on.
     *
     * @return array<int, array{query:string, searches:int, clicked:int}>
     */
    public static function get_top_keywords( int $limit, int $days, int $offset = 0 ): array {
        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return array_map( static function ( $row ) {
            return [
                'query'    => (string) $row['query'],
                'searches' => (int) $row['searches'],
                'clicked'  => (int) $row['clicked'],
            ];
        }, (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT query,
                    COUNT(*) AS searches,
                    SUM(clicked_at IS NOT NULL) AS clicked
             FROM {$t}
             WHERE searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)
             GROUP BY query
             ORDER BY searches DESC, query ASC
             LIMIT %d OFFSET %d",
            $days,
            $limit,
            $offset
        ), ARRAY_A ) );
    }

    /**
     * Number of distinct terms searched in the period.
     */
    public static function count_keywords( int $days ): int {
        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(DISTINCT query) FROM {$t} WHERE searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)",
            $days
        ) );
    }

    /**
     * Terms that returned nothing - the catalogue's to-do list.
     *
     * @return array<int, array{query:string, searches:int}>
     */
    public static function get_zero_results( int $limit, int $days, int $offset = 0 ): array {
        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return array_map( static function ( $row ) {
            return [
                'query'    => (string) $row['query'],
                'searches' => (int) $row['searches'],
            ];
        }, (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT query, COUNT(*) AS searches
             FROM {$t}
             WHERE results_count = 0 AND searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)
             GROUP BY query
             ORDER BY searches DESC, query ASC
             LIMIT %d OFFSET %d",
            $days,
            $limit,
            $offset
        ), ARRAY_A ) );
    }

    /**
     * Number of distinct terms that returned nothing.
     */
    public static function count_zero_results( int $days ): int {
        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(DISTINCT query) FROM {$t} WHERE results_count = 0 AND searched_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)",
            $days
        ) );
    }

    /**
     * Total rows currently stored, all periods.
     */
    public static function get_total_count(): int {
        global $wpdb;
        $t = $wpdb->prefix . self::TABLE;
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$t}" ); // phpcs:ignore WordPress.DB
    }

    /* ─── Export ─────────────────────────────── */

    
}
