<?php
namespace Clusterlane\Core;

if ( ! defined( 'ABSPATH' ) ) exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery
// -- Clusterlane's own tables and set-based reads of posts/terms that no WordPress API covers.
//    Every query goes through $wpdb->prepare(): values and ID lists as placeholders, table and
//    column names as %i. Fragments added to a query are code (Scope::sql()) or are prepared
//    where they are built (Scope::type_sql(), Lang::sql(), SeoMeta joins).

/**
 * Activity — what was done through Clusterlane (0.98.124).
 *
 * One row per write that an editor approved: a link added (Text Match,
 * Link Suggestions), a pillar chosen or cleared, an SEO field written, a
 * draft created from Gap Analysis. Undo doesn't delete the row but sets
 * undone_at, so reports count net ("38 links added") and can still show
 * what was tried.
 *
 * The rows hold the WordPress user ID of the editor. When a user is
 * deleted, their rows are kept for the totals but the ID is cleared.
 */
class Activity {

    const LINK_ADDED     = 'link_added';
    const PILLAR_CHOSEN  = 'pillar_chosen';
    const PILLAR_CLEARED = 'pillar_cleared';
    const META_WRITTEN   = 'meta_written';
    const DRAFT_CREATED  = 'draft_created';

    public static function register_hooks(): void {
        add_action( 'deleted_user', [ self::class, 'forget_user' ] );
        add_action( 'wpmu_delete_user', [ self::class, 'forget_user_network' ] );
        add_action( 'admin_init', [ self::class, 'privacy_policy' ] );
    }

    /** Suggested text for Settings → Privacy (the log holds editors' user IDs). */
    public static function privacy_policy(): void {
        if ( ! function_exists( 'wp_add_privacy_policy_content' ) ) return;
        wp_add_privacy_policy_content( Brand::name(), wp_kses_post( wpautop(
            __( 'This site uses Clusterlane to review internal links. Clusterlane keeps a log of the changes made through it — links added, SEO descriptions written, pillar articles chosen and drafts created — together with the WordPress user ID of the editor who approved each change. The log stays in this site\'s database and is not sent anywhere. When a user account is deleted, its user ID is removed from the log.', 'clusterlane' )
        ) ) );
    }

    private static function table(): string {
        global $wpdb;
        return $wpdb->prefix . 'clusterlane_activity';
    }

    /**
     * Record one write. $cluster_key defaults to the primary cluster of the
     * target (links) or of the post (other actions). Returns the row ID, 0 on failure.
     */
    public static function log( string $action, string $tool, int $post_id, int $target_id = 0, string $detail = '', string $cluster_key = '' ): int {
        global $wpdb;
        if ( $cluster_key === '' ) {
            $of          = $target_id ?: $post_id;
            $cluster_key = (string) $wpdb->get_var( $wpdb->prepare(
                "SELECT primary_cluster FROM `{$wpdb->prefix}clusterlane_post_metrics` WHERE post_id = %d", $of
            ) );
            if ( $cluster_key === '' ) { // not computed yet: any cluster the article belongs to
                $cluster_key = (string) $wpdb->get_var( $wpdb->prepare(
                    "SELECT cluster_key FROM `{$wpdb->prefix}clusterlane_cluster_post_class` WHERE post_id = %d ORDER BY id LIMIT 1", $of
                ) );
            }
        }
        $ok = $wpdb->insert( self::table(), [
            'action'      => substr( sanitize_key( $action ), 0, 20 ),
            'tool'        => substr( sanitize_key( $tool ), 0, 20 ),
            'post_id'     => max( 0, $post_id ),
            'target_id'   => max( 0, $target_id ),
            'cluster_key' => substr( sanitize_key( $cluster_key ), 0, 100 ),
            'detail'      => mb_substr( sanitize_text_field( $detail ), 0, 255 ),
            'user_id'     => get_current_user_id(),
            'created_at'  => current_time( 'mysql', true ),
        ], [ '%s', '%s', '%d', '%d', '%s', '%s', '%d', '%s' ] );
        return $ok ? (int) $wpdb->insert_id : 0;
    }

    /**
     * Mark the latest matching, not yet undone row as undone.
     * $target_id 0 matches any target (SEO fields have none).
     */
    public static function undo_last( string $action, int $post_id, int $target_id = 0, string $tool = '' ): bool {
        global $wpdb;
        $t     = self::table();
        // target 0 = any target, tool '' = any tool.
        $id = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM %i WHERE action = %s AND post_id = %d AND undone_at IS NULL
               AND ( %d = 0 OR target_id = %d ) AND ( %s = '' OR tool = %s )
             ORDER BY id DESC LIMIT 1",
            $t, $action, $post_id, $target_id, $target_id, $tool, $tool
        ) );
        if ( ! $id ) return false;
        return (bool) $wpdb->update( $t, [ 'undone_at' => current_time( 'mysql', true ) ], [ 'id' => $id ], [ '%s' ], [ '%d' ] );
    }

    /**
     * Net counts per action and tool between two UTC datetimes ('Y-m-d H:i:s').
     * @return array{total:array<string,int>, by_tool:array<string,array<string,int>>, undone:int}
     */
    public static function summary( string $from, string $to ): array {
        global $wpdb;
        $rows = $wpdb->get_results( $wpdb->prepare(
            'SELECT action, tool, SUM(undone_at IS NULL) AS kept, SUM(undone_at IS NOT NULL) AS undone
             FROM %i WHERE created_at >= %s AND created_at < %s GROUP BY action, tool',
            self::table(), $from, $to
        ) );
        $out = [ 'total' => [], 'by_tool' => [], 'undone' => 0 ];
        foreach ( (array) $rows as $r ) {
            $out['total'][ $r->action ]              = ( $out['total'][ $r->action ] ?? 0 ) + (int) $r->kept;
            $out['by_tool'][ $r->action ][ $r->tool ] = (int) $r->kept;
            $out['undone']                           += (int) $r->undone;
        }

        // Pillars: clusters that got a new pillar, not medal clicks (0.98.144).
        // A cluster whose pillar changed three times counts once; one whose
        // choice was cleared again by the end of the period doesn't count.
        $last = [];
        foreach ( (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT cluster_key, action, tool FROM %i
             WHERE action IN (%s, %s) AND undone_at IS NULL AND cluster_key <> '' AND created_at >= %s AND created_at < %s ORDER BY id",
            self::table(), self::PILLAR_CHOSEN, self::PILLAR_CLEARED, $from, $to
        ) ) as $r ) {
            $last[ $r->cluster_key ] = $r;
        }
        $chosen = array_filter( $last, static fn( $r ) => $r->action === self::PILLAR_CHOSEN );
        $out['total'][ self::PILLAR_CHOSEN ]   = count( $chosen );
        $out['by_tool'][ self::PILLAR_CHOSEN ] = [];
        foreach ( $chosen as $r ) $out['by_tool'][ self::PILLAR_CHOSEN ][ $r->tool ] = ( $out['by_tool'][ self::PILLAR_CHOSEN ][ $r->tool ] ?? 0 ) + 1;
        return $out;
    }

    /**
     * Net count of one action per bin of $bin_days, from $from (UTC datetime)
     * for $bins bins — for the "per week" bars.
     * @return int[]
     */
    public static function binned( string $action, string $from, int $bins, int $bin_days = 7 ): array {
        global $wpdb;
        $out   = array_fill( 0, max( 1, $bins ), 0 );
        $start = strtotime( $from . ' UTC' );
        $to    = gmdate( 'Y-m-d H:i:s', $start + $bins * $bin_days * DAY_IN_SECONDS );
        $times = $wpdb->get_col( $wpdb->prepare(
            'SELECT created_at FROM %i WHERE action = %s AND undone_at IS NULL AND created_at >= %s AND created_at < %s',
            self::table(), $action, $from, $to
        ) );
        foreach ( (array) $times as $t ) {
            $i = (int) floor( ( strtotime( $t . ' UTC' ) - $start ) / ( $bin_days * DAY_IN_SECONDS ) );
            if ( isset( $out[ $i ] ) ) $out[ $i ]++;
        }
        return $out;
    }

    /** @return array{count:int, first:?string} For the support report. */
    public static function stats(): array {
        global $wpdb;
        $r = $wpdb->get_row( $wpdb->prepare( 'SELECT COUNT(*) AS n, MIN(created_at) AS first FROM %i', self::table() ) );
        return [ 'count' => (int) ( $r->n ?? 0 ), 'first' => $r->first ?? null ];
    }

    /** A deleted user's rows stay for the totals, without their ID. */
    public static function forget_user( int $user_id ): void {
        global $wpdb;
        $wpdb->update( self::table(), [ 'user_id' => 0 ], [ 'user_id' => $user_id ], [ '%d' ], [ '%d' ] );
    }

    /** Multisite: a user deleted from the network — every site they belonged to. */
    public static function forget_user_network( int $user_id ): void {
        foreach ( array_keys( get_blogs_of_user( $user_id ) ) as $blog_id ) {
            switch_to_blog( (int) $blog_id );
            global $wpdb;
            if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', self::table() ) ) === self::table() ) {
                self::forget_user( $user_id );
            }
            restore_current_blog();
        }
    }
}