<?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).

/**
 * HubClusters (0.98.180) — pages as clusters.
 *
 * Some sites are built from post types that point at each other through
 * custom fields instead of categories: an area names its island
 * (area_island), an attraction names its area (place_area). Each island is
 * then a natural cluster — the island's page, the areas that point to it and
 * the attractions that point to those areas — though no taxonomy says so.
 *
 * relations() finds such fields by themselves: custom fields of the scanned
 * post types whose values are IDs of other published pages (ACF post object
 * and relationship fields, or any field holding a post ID). A field only
 * counts when nearly all its values are pages of one post type, so a number
 * that happens to equal a post ID doesn't make a relation.
 *
 * A post type ticked in Cluster Admin (setting enabled_hubs) makes every one
 * of its pages a cluster: cluster_type "hub_{post type}", cluster_key
 * "hub_{post ID}", term_id = the post ID. Members are worked out on every
 * sync and Metrics run, two steps deep, so new content joins by itself.
 */
final class HubClusters {

    const PREFIX       = 'hub_';
    const CACHE_OPTION = 'clusterlane_hub_relations';

    /** Share of a field's values that must be pages of the same post type. */
    private const MIN_SHARE = 0.8;

    /** Fewest pages that must use a field before it counts. */
    private const MIN_USES = 3;

    /** Steps from the hub page: areas point to the island (1), attractions to the areas (2). */
    private const DEPTH = 2;

    public static function is_hub_type( string $cluster_type ): bool {
        return strpos( $cluster_type, self::PREFIX ) === 0;
    }

    public static function post_type_of( string $cluster_type ): string {
        return substr( $cluster_type, strlen( self::PREFIX ) );
    }

    /** The post types whose pages are clusters (Cluster Admin, onboarding). @return string[] */
    public static function enabled(): array {
        $s = Settings::get_instance();
        if ( ! $s ) return [];
        $types = array_map( 'sanitize_key', (array) $s->get( 'enabled_hubs', [] ) );
        return array_values( array_intersect( $types, array_keys( self::candidates() ) ) );
    }

    /**
     * Fields that point from one scanned post type to another.
     *
     * @return array<int, array{from: string, key: string, to: string, uses: int}>
     */
    public static function relations( bool $fresh = false, ?array $types = null ): array {
        if ( $types !== null ) { // onboarding: the post types being picked, not saved yet
            $types = array_values( array_filter( array_map( 'sanitize_key', $types ) ) );
            return $types ? self::detect( $types ) : [];
        }
        $types = array_values( array_filter( array_map( 'sanitize_key', Scope::post_types() ) ) );
        $sig   = md5( implode( ',', $types ) );
        if ( ! $fresh ) {
            $c = get_option( self::CACHE_OPTION );
            if ( is_array( $c ) && ( $c['sig'] ?? '' ) === $sig && isset( $c['rel'] ) && time() - (int) ( $c['at'] ?? 0 ) < DAY_IN_SECONDS ) return $c['rel'];
        }
        $rel = $types ? self::detect( $types ) : [];
        update_option( self::CACHE_OPTION, [ 'sig' => $sig, 'at' => time(), 'rel' => $rel ], false );
        return $rel;
    }

    /** @param string[] $types @return array<int, array{from: string, key: string, to: string, uses: int}> */
    private static function detect( array $types ): array {
        global $wpdb;
        // Per field and source post type: how many values there are, and where they point.
        $totals = []; // "from|key" => values
        $hits   = []; // "from|key" => [ to => count ]

        // A single ID: "2803".
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT p.post_type AS src, pm.meta_key AS k, t.post_type AS dst, t.post_status AS st, COUNT(*) AS n
             FROM {$wpdb->postmeta} pm
             JOIN {$wpdb->posts} p ON p.ID = pm.post_id AND p.post_status = 'publish' AND p.post_type IN (" . implode( ',', array_fill( 0, count( $types ), '%s' ) ) . ")
             LEFT JOIN {$wpdb->posts} t ON t.ID = CAST( pm.meta_value AS UNSIGNED ) AND t.ID <> p.ID
             WHERE pm.meta_key NOT LIKE %s AND pm.meta_value REGEXP %s
             GROUP BY src, k, dst, st",
            array_merge( $types, [ $wpdb->esc_like( '_' ) . '%', '^[1-9][0-9]{0,18}$' ] )
        ) );
        foreach ( (array) $rows as $r ) {
            $id = $r->src . '|' . $r->k;
            $totals[ $id ] = ( $totals[ $id ] ?? 0 ) + (int) $r->n;
            if ( $r->dst && $r->st === 'publish' && in_array( $r->dst, $types, true ) ) $hits[ $id ][ $r->dst ] = ( $hits[ $id ][ $r->dst ] ?? 0 ) + (int) $r->n;
        }

        // A list of IDs (ACF relationship): a:2:{i:0;s:4:"2803";i:1;s:4:"2808";}
        $lists = $wpdb->get_results( $wpdb->prepare(
            "SELECT p.post_type AS src, pm.meta_key AS k, pm.meta_value AS v
             FROM {$wpdb->postmeta} pm
             JOIN {$wpdb->posts} p ON p.ID = pm.post_id AND p.post_status = 'publish' AND p.post_type IN (" . implode( ',', array_fill( 0, count( $types ), '%s' ) ) . ')
             WHERE pm.meta_key NOT LIKE %s AND pm.meta_value LIKE %s AND CHAR_LENGTH( pm.meta_value ) < 4000
             LIMIT 20000',
            array_merge( $types, [ $wpdb->esc_like( '_' ) . '%', $wpdb->esc_like( 'a:' ) . '%' ] )
        ) );
        $ids = []; $by = [];
        foreach ( (array) $lists as $r ) {
            $v = is_serialized( $r->v ) ? @unserialize( $r->v, [ 'allowed_classes' => false ] ) : null; // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged, WordPress.PHP.DiscouragedPHPFunctions.serialize_unserialize
            if ( ! is_array( $v ) || ! $v ) continue;
            $vals = array_values( array_filter( $v, fn( $x ) => is_scalar( $x ) && preg_match( '/^[1-9][0-9]{0,18}$/', (string) $x ) ) );
            if ( count( $vals ) !== count( $v ) ) continue; // not a list of IDs
            $id = $r->src . '|' . $r->k;
            foreach ( $vals as $x ) { $by[ $id ][] = (int) $x; $ids[ (int) $x ] = true; }
        }
        if ( $ids ) {
            $type_of = [];
            foreach ( array_chunk( array_keys( $ids ), 1000 ) as $chunk ) {
                $chunk = array_map( 'intval', $chunk );
                foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_type FROM {$wpdb->posts} WHERE post_status = 'publish' AND ID IN (" . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ')', $chunk ) ) as $p ) {
                    $type_of[ (int) $p->ID ] = $p->post_type;
                }
            }
            foreach ( $by as $id => $vals ) {
                foreach ( $vals as $x ) {
                    $totals[ $id ] = ( $totals[ $id ] ?? 0 ) + 1;
                    $dst = $type_of[ $x ] ?? '';
                    if ( $dst && in_array( $dst, $types, true ) ) $hits[ $id ][ $dst ] = ( $hits[ $id ][ $dst ] ?? 0 ) + 1;
                }
            }
        }

        $out = [];
        foreach ( $hits as $id => $dsts ) {
            arsort( $dsts );
            $dst = (string) array_key_first( $dsts );
            $n   = (int) $dsts[ $dst ];
            if ( $n < self::MIN_USES || $n < self::MIN_SHARE * (int) $totals[ $id ] ) continue;
            [ $src, $key ] = explode( '|', $id, 2 );
            if ( $src === $dst ) continue; // a page pointing to pages of its own type is no hierarchy
            $out[] = [ 'from' => $src, 'key' => $key, 'to' => $dst, 'uses' => $n ];
        }
        usort( $out, fn( $a, $b ) => $b['uses'] <=> $a['uses'] );
        return $out;
    }

    /**
     * Post types other content points to — what Cluster Admin offers — with
     * the paths into them: Islands ← Areas (area_island) ← Attractions (place_area).
     *
     * @return array<string, array{label: string, singular: string, pages: int, paths: array<int, array{from: string, key: string, via?: string, via_key?: string}>}>
     */
    public static function candidates( ?array $types = null ): array {
        $rel = self::relations( false, $types );
        $out = [];
        foreach ( $rel as $r ) {
            $obj = get_post_type_object( $r['to'] );
            if ( ! $obj ) continue;
            if ( ! isset( $out[ $r['to'] ] ) ) {
                $out[ $r['to'] ] = [
                    'label'    => html_entity_decode( (string) $obj->labels->name, ENT_QUOTES, 'UTF-8' ),
                    'singular' => html_entity_decode( (string) $obj->labels->singular_name, ENT_QUOTES, 'UTF-8' ),
                    'pages'    => (int) ( wp_count_posts( $r['to'] )->publish ?? 0 ),
                    'paths'    => [],
                ];
            }
            $out[ $r['to'] ]['paths'][] = [ 'from' => $r['from'], 'key' => $r['key'] ];
            // One step further: what points at the pages that point here.
            foreach ( $rel as $r2 ) {
                if ( $r2['to'] === $r['from'] && $r2['from'] !== $r['to'] ) {
                    $out[ $r['to'] ]['paths'][] = [ 'from' => $r2['from'], 'key' => $r2['key'], 'via' => $r['from'], 'via_key' => $r['key'] ];
                }
            }
        }
        return array_filter( $out, fn( $c ) => $c['pages'] >= 2 );
    }

    /**
     * The pages of a hub cluster: the hub page, the pages pointing to it, and
     * the pages pointing to those. Published and in scope only.
     *
     * @return int[]
     */
    public static function members( int $hub_id ): array {
        $hub = get_post( $hub_id );
        if ( ! $hub || Scope::excludes( $hub ) ) return [];
        $rel     = self::relations();
        $members = [ $hub_id => true ];
        $level   = [ $hub->post_type => [ $hub_id ] ];
        for ( $d = 0; $d < self::DEPTH && $level; $d++ ) {
            $next = [];
            foreach ( $level as $to_type => $ids ) {
                foreach ( $rel as $r ) {
                    if ( $r['to'] !== $to_type ) continue;
                    foreach ( self::pointing_to( $r['from'], $r['key'], $ids ) as $pid ) {
                        if ( isset( $members[ $pid ] ) ) continue;
                        $members[ $pid ] = true;
                        $next[ $r['from'] ][] = $pid;
                    }
                }
            }
            $level = $next;
        }
        return array_map( 'intval', array_keys( $members ) );
    }

    /** Published, in-scope pages of $type whose field $key holds one of $ids. @param int[] $ids @return int[] */
    private static function pointing_to( string $type, string $key, array $ids ): array {
        global $wpdb;
        $ids = array_values( array_filter( array_map( 'intval', $ids ) ) );
        if ( ! $ids ) return [];
        $out = [];
        foreach ( array_chunk( $ids, 200 ) as $chunk ) {
            // The field as a single ID ("12") or inside a serialized list (s:2:"12"; or i:12;).
            $likes = [];
            foreach ( $chunk as $i ) {
                $likes[] = '%' . $wpdb->esc_like( '"' . $i . '"' ) . '%';
                $likes[] = '%' . $wpdb->esc_like( 'i:' . $i . ';' ) . '%';
            }
            $args = array_merge( [ $key, $type ], array_map( 'strval', $chunk ), [ $wpdb->esc_like( 'a:' ) . '%' ], $likes );
            // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared -- SQL assembled from code-only fragments (no values); every value is a placeholder passed to $wpdb->prepare().
            $rows = $wpdb->get_col( $wpdb->prepare(
                "SELECT DISTINCT p.ID FROM {$wpdb->postmeta} pm
                 JOIN {$wpdb->posts} p ON p.ID = pm.post_id
                 WHERE pm.meta_key = %s AND p.post_type = %s AND " . Scope::sql( 'p' ) . '
                   AND ( pm.meta_value IN (' . implode( ',', array_fill( 0, count( $chunk ), '%s' ) ) . ')
                         OR ( pm.meta_value LIKE %s AND ( ' . implode( ' OR ', array_fill( 0, count( $likes ), 'pm.meta_value LIKE %s' ) ) . ' ) ) )',
                $args
            ) ); // Scope::sql() has no values
            // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
            foreach ( (array) $rows as $id ) $out[] = (int) $id;
        }
        return $out;
    }

    /**
     * Hub clusters an article belongs to: its own page when its type is a hub,
     * and the hubs its fields lead to, up to DEPTH steps.
     *
     * @return string[] cluster keys
     */
    public static function keys_for( int $post_id ): array {
        $hubs = self::enabled();
        if ( ! $hubs ) return [];
        $post = get_post( $post_id );
        if ( ! $post ) return [];
        $rel   = self::relations();
        $keys  = [];
        $level = [ $post_id => $post->post_type ];
        for ( $d = 0; $d <= self::DEPTH && $level; $d++ ) {
            $next = [];
            foreach ( $level as $id => $type ) {
                if ( in_array( $type, $hubs, true ) ) $keys[] = self::PREFIX . $id;
                if ( $d === self::DEPTH ) continue;
                foreach ( $rel as $r ) {
                    if ( $r['from'] !== $type ) continue;
                    $v    = get_post_meta( $id, $r['key'], true );
                    $vals = is_array( $v ) ? $v : [ $v ];
                    foreach ( $vals as $x ) {
                        if ( is_scalar( $x ) && preg_match( '/^[1-9][0-9]*$/', (string) $x ) && get_post_type( (int) $x ) === $r['to'] ) $next[ (int) $x ] = $r['to'];
                    }
                }
            }
            $level = $next;
        }
        return array_values( array_unique( $keys ) );
    }

    /**
     * The page at the top of this page's hierarchy: follow its fields upward
     * (area → its island) until nothing points further. The page itself when
     * it points nowhere. Used to group page-clusters into sections (0.98.181).
     */
    public static function top_of( int $post_id ): int {
        $rel  = self::relations();
        $seen = [ $post_id => true ];
        $cur  = $post_id;
        for ( $i = 0; $i < 6; $i++ ) {
            $type = get_post_type( $cur );
            $up   = 0;
            foreach ( $rel as $r ) {
                if ( $r['from'] !== $type ) continue;
                $v = get_post_meta( $cur, $r['key'], true );
                foreach ( is_array( $v ) ? $v : [ $v ] as $x ) {
                    if ( is_scalar( $x ) && preg_match( '/^[1-9][0-9]*$/', (string) $x ) && get_post_type( (int) $x ) === $r['to'] && get_post_status( (int) $x ) === 'publish' ) { $up = (int) $x; break 2; }
                }
            }
            if ( ! $up || isset( $seen[ $up ] ) ) break;
            $seen[ $up ] = true;
            $cur = $up;
        }
        return $cur;
    }

    /** Pages that must point to a page before it is a cluster: the Cluster Admin minimum, at least 2. */
    public static function min_pointing(): int {
        $s = Settings::get_instance();
        return max( 2, $s ? $s->min_term_count() : 2 );
    }

    /**
     * Create or refresh a cluster for every page of the ticked post types and
     * switch off hub clusters no longer wanted. @return int clusters created
     */
    public static function sync(): int {
        global $wpdb;
        $table   = $wpdb->prefix . 'clusterlane_clusters';
        $types   = self::enabled();
        $keep    = [];
        $created = 0;
        foreach ( $types as $type ) {
            $pages = get_posts( Scope::query_args( [
                'post_type' => $type, 'post_status' => 'publish', 'posts_per_page' => -1,
                'orderby' => 'title', 'order' => 'ASC', 'no_found_rows' => true,
            ] ) );
            foreach ( $pages as $p ) {
                // A page with one page pointing to it is no cluster — a pair says nothing (0.98.181).
                if ( count( self::members( (int) $p->ID ) ) - 1 < self::min_pointing() ) continue;
                $key    = self::PREFIX . (int) $p->ID;
                $keep[] = $key;
                $label  = html_entity_decode( get_the_title( $p ), ENT_QUOTES, 'UTF-8' );
                $exists = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE cluster_key = %s', $table, $key ) );
                if ( $exists ) {
                    $wpdb->update( $table, [ 'cluster_label' => $label, 'cluster_type' => self::PREFIX . $type, 'term_id' => (int) $p->ID, 'is_active' => 1 ], [ 'cluster_key' => $key ] );
                } else {
                    $wpdb->insert( $table, [ 'cluster_key' => $key, 'cluster_label' => $label, 'cluster_type' => self::PREFIX . $type, 'term_id' => (int) $p->ID, 'is_active' => 1 ] );
                    $created++;
                }
            }
        }
        $like = $wpdb->esc_like( self::PREFIX ) . '%';
        if ( $keep ) {
            $wpdb->query( $wpdb->prepare( 'UPDATE %i SET is_active = 0 WHERE cluster_type LIKE %s AND cluster_key NOT IN (' . implode( ',', array_fill( 0, count( $keep ), '%s' ) ) . ')', array_merge( [ $table, $like ], $keep ) ) );
        } else {
            $wpdb->query( $wpdb->prepare( 'UPDATE %i SET is_active = 0 WHERE cluster_type LIKE %s', $table, $like ) );
        }
        return $created;
    }
}