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

/**
 * Manages the site-wide entity registry.
 * Mirrors the aggregate-entities Supabase edge function.
 */
class Semantic_Entity_Registry {

    /**
     * After an analysis completes, find/create registry entries and update metrics.
     */
    public static function aggregate_from_analysis( $analysis_id ) {
        global $wpdb;

        $entities = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}semantic_suggested_entities
             WHERE analysis_id = %d AND approval_status = 'approved'",
            $analysis_id
        ), ARRAY_A );

        if ( empty( $entities ) ) return;

        foreach ( $entities as $entity ) {
            $registry_id = self::find_or_create( $entity );
            if ( $registry_id ) {
                // Link the suggested entity back to the registry row
                $wpdb->update(
                    $wpdb->prefix . 'semantic_suggested_entities',
                    [ 'entity_registry_id' => $registry_id ],
                    [ 'id' => $entity['id'] ],
                    [ '%d' ],
                    [ '%d' ]
                );
            }
        }

        // Recompute metrics for every affected registry entry
        self::recompute_all_metrics();
    }

    /**
     * Called when a single entity is approved — update its registry entry and metrics.
     */
    public static function on_entity_approved( $suggested_entity_id ) {
        global $wpdb;

        $entity = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}semantic_suggested_entities WHERE id = %d",
            $suggested_entity_id
        ), ARRAY_A );

        if ( ! $entity ) return;

        $registry_id = self::find_or_create( $entity );
        if ( $registry_id ) {
            $wpdb->update(
                $wpdb->prefix . 'semantic_suggested_entities',
                [ 'entity_registry_id' => $registry_id ],
                [ 'id' => $suggested_entity_id ],
                [ '%d' ],
                [ '%d' ]
            );
            self::recompute_metrics_for( $registry_id );
        }
    }

    /**
     * Find or create a registry entry for the given entity data.
     * Deduplicates by: entity_id → wikidata_qid → synonym match.
     *
     * @param array $entity  Row from semantic_suggested_entities (as array).
     * @return int|null  Registry row ID.
     */
    private static function find_or_create( array $entity ) {
        global $wpdb;
        $t = $wpdb->prefix . 'semantic_entity_registry';

        $canonical_id = $entity['canonical_entity_id']
            ?: 'entity_' . preg_replace( '/[^a-z0-9]+/', '_', strtolower( $entity['name'] ) );

        // 1. Exact entity_id match
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, synonyms FROM {$t} WHERE entity_id = %s LIMIT 1",
            $canonical_id
        ), ARRAY_A );

        if ( $row ) {
            self::maybe_add_synonym( $row['id'], $entity['name'], $row['synonyms'] );
            return (int) $row['id'];
        }

        // 2. Wikidata QID match
        if ( ! empty( $entity['wikidata_qid'] ) ) {
            $row = $wpdb->get_row( $wpdb->prepare(
                "SELECT id, synonyms FROM {$t} WHERE wikidata_qid = %s LIMIT 1",
                $entity['wikidata_qid']
            ), ARRAY_A );

            if ( $row ) {
                self::maybe_add_synonym( $row['id'], $entity['name'], $row['synonyms'] );
                return (int) $row['id'];
            }
        }

        // 3. Synonym match (check if entity name is already a known synonym)
        $name_lower = strtolower( $entity['name'] );
        $all        = $wpdb->get_results( "SELECT id, synonyms FROM {$t}", ARRAY_A );
        foreach ( $all as $candidate ) {
            $syns = json_decode( $candidate['synonyms'] ?? '[]', true ) ?: [];
            if ( in_array( $name_lower, array_map( 'strtolower', $syns ), true ) ) {
                return (int) $candidate['id'];
            }
        }

        // 4. Create new entry
        $aliases = json_decode( $entity['aliases'] ?? '[]', true ) ?: [];
        $synonyms = array_unique( array_map( 'strtolower', array_merge( [ $entity['name'] ], $aliases ) ) );

        $wpdb->insert( $t, [
            'entity_id'       => $canonical_id,
            'label'           => $entity['name'],
            'canonical_label' => $entity['name'],
            'type'            => $entity['type'],
            'wikidata_qid'    => ! empty( $entity['wikidata_verified'] ) ? ( $entity['wikidata_qid'] ?: null ) : null,
            'same_as'         => $entity['same_as'] ?: '[]',
            'synonyms'        => wp_json_encode( array_values( $synonyms ) ),
            'definition'      => $entity['definition'],
            'canonical_roles' => '[]',
            'role_frequency'  => '{}',
            'typical_contexts'=> '[]',
            'occurrence_count'=> 0,
            'mention_count'   => 0,
        ] );

        return $wpdb->insert_id ?: null;
    }

    private static function maybe_add_synonym( $registry_id, $name, $existing_synonyms_json ) {
        global $wpdb;
        $syns     = json_decode( $existing_synonyms_json ?: '[]', true ) ?: [];
        $lower    = strtolower( $name );
        if ( ! in_array( $lower, $syns, true ) ) {
            $syns[] = $lower;
            $wpdb->update(
                $wpdb->prefix . 'semantic_entity_registry',
                [ 'synonyms' => wp_json_encode( array_values( $syns ) ) ],
                [ 'id' => $registry_id ]
            );
        }
    }

    /**
     * Recompute occurrence_count, mention_count, canonical_roles, role_frequency, typical_contexts
     * for every entity in the registry. Full recalculation from approved entities.
     */
    public static function recompute_all_metrics() {
        global $wpdb;

        $registry_ids = $wpdb->get_col( "SELECT id FROM {$wpdb->prefix}semantic_entity_registry" );
        foreach ( $registry_ids as $id ) {
            self::recompute_metrics_for( (int) $id );
        }
    }

    public static function recompute_metrics_for( $registry_id ) {
        global $wpdb;

        // All approved suggested entities linked to this registry entry
        $entities = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}semantic_suggested_entities
             WHERE entity_registry_id = %d AND approval_status = 'approved'",
            $registry_id
        ), ARRAY_A );

        $occurrence_count = count( $entities );
        $mention_count    = 0;
        $all_roles        = [];
        $context_words    = [];

        foreach ( $entities as $e ) {
            $mentions = json_decode( $e['mentions'] ?? '[]', true ) ?: [];
            $mention_count += count( $mentions );

            $roles = json_decode( $e['roles'] ?? '[]', true ) ?: [];
            foreach ( $roles as $r ) {
                $all_roles[] = $r['role'] ?? '';
            }

            // Collect context words from mention sentences
            foreach ( $mentions as $m ) {
                $sentence = $m['full_sentence'] ?? '';
                preg_match_all( '/\b[a-z]{4,}\b/', strtolower( $sentence ), $matches );
                $context_words = array_merge( $context_words, $matches[0] );
            }
        }

        // Role frequency (0-1)
        $role_counts = array_count_values( array_filter( $all_roles ) );
        $role_freq   = [];
        $total_roles = array_sum( $role_counts );
        if ( $total_roles > 0 ) {
            foreach ( $role_counts as $role => $count ) {
                $role_freq[ $role ] = round( $count / $total_roles, 2 );
            }
        }

        // Canonical roles = roles with ≥30% frequency
        $canonical_roles = array_keys( array_filter( $role_freq, fn( $f ) => $f >= 0.3 ) );

        // Top 10 context words
        $word_counts   = array_count_values( $context_words );
        arsort( $word_counts );
        $stop_words    = [ 'this', 'that', 'with', 'from', 'have', 'been', 'they', 'their', 'into' ];
        $context_top   = array_slice( array_keys( array_diff_key( $word_counts,
            array_flip( $stop_words ) ) ), 0, 10 );

        $wpdb->update(
            $wpdb->prefix . 'semantic_entity_registry',
            [
                'occurrence_count' => $occurrence_count,
                'mention_count'    => $mention_count,
                'canonical_roles'  => wp_json_encode( array_values( $canonical_roles ) ),
                'role_frequency'   => wp_json_encode( $role_freq ),
                'typical_contexts' => wp_json_encode( array_values( $context_top ) ),
                'updated_at'       => current_time( 'mysql' ),
            ],
            [ 'id' => $registry_id ]
        );
    }

    /**
     * Return ALL registry data (admin view). Includes entities that may only occur
     * in drafts / private / excluded content — never expose this through a public
     * endpoint; use get_all_public() / is_public() for anything unauthenticated.
     */
    public static function get_all() {
        global $wpdb;
        return $wpdb->get_results(
            "SELECT * FROM {$wpdb->prefix}semantic_entity_registry ORDER BY occurrence_count DESC",
            ARRAY_A
        );
    }

    /**
     * Numeric registry ids that occur in at least one CURRENTLY published, public,
     * password-free, non-excluded post (via an approved entity on a completed
     * analysis). This is the allow-list that keeps the public AI endpoints from
     * leaking entities derived from drafts, private/trashed posts, opted-out posts
     * or password-protected content. Memoised per request — several public
     * surfaces ask for it on a single page load.
     *
     * @return int[]
     */
    public static function public_registry_ids() {
        static $cache = null;
        if ( null !== $cache ) return $cache;

        global $wpdb;
        $types = Semantic_Settings::public_post_types();
        if ( empty( $types ) ) { $cache = []; return $cache; }

        $placeholders = implode( ',', array_fill( 0, count( $types ), '%s' ) );
        $args         = array_merge( $types, [ Semantic_Settings::EXCLUDE_META ] );

        $ids = $wpdb->get_col( $wpdb->prepare(
            "SELECT DISTINCT se.entity_registry_id
               FROM {$wpdb->prefix}semantic_suggested_entities se
               JOIN {$wpdb->prefix}semantic_analyses sa ON sa.id = se.analysis_id
               JOIN {$wpdb->posts} p ON p.ID = sa.post_id
              WHERE se.approval_status = 'approved'
                AND se.entity_registry_id IS NOT NULL
                AND sa.status = 'completed'
                AND p.post_status = 'publish'
                AND p.post_password = ''
                AND p.post_type IN ($placeholders)
                AND p.ID NOT IN (
                    SELECT post_id FROM {$wpdb->postmeta}
                     WHERE meta_key = %s AND meta_value = '1'
                )",
            $args
        ) );

        $cache = array_map( 'intval', $ids );
        return $cache;
    }

    /** Registry rows safe for public output (see public_registry_ids), get_all order. */
    public static function get_all_public() {
        $allowed = array_flip( self::public_registry_ids() );
        if ( empty( $allowed ) ) return [];
        return array_values( array_filter(
            self::get_all(),
            static function ( $row ) use ( $allowed ) { return isset( $allowed[ (int) $row['id'] ] ); }
        ) );
    }

    /** True when a registry row appears in currently public content. */
    public static function is_public( $row ) {
        if ( empty( $row['id'] ) ) return false;
        return in_array( (int) $row['id'], self::public_registry_ids(), true );
    }

    public static function get_by_entity_id( $entity_id ) {
        global $wpdb;
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}semantic_entity_registry WHERE entity_id = %s",
            $entity_id
        ), ARRAY_A );
    }

    /**
     * Full detail for one registry entry by its numeric id, including the
     * approved pages where the entity appears (with relevance + role).
     */
    public static function get_detail( $registry_id, $public_only = false ) {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}semantic_entity_registry WHERE id = %d",
            $registry_id
        ), ARRAY_A );
        if ( ! $row ) return null;

        foreach ( [ 'same_as', 'synonyms', 'canonical_roles', 'role_frequency', 'typical_contexts' ] as $col ) {
            $row[ $col ] = json_decode( $row[ $col ] ?? '[]', true ) ?: [];
        }

        // Linked content — approved suggested-entity rows for this registry id, joined to their post.
        // Scope to the latest completed analysis per post so superseded duplicates don't appear.
        // When $public_only (public MCP path), restrict to currently published, public,
        // password-free, non-excluded posts so linked_content can't leak draft URLs/titles.
        $join  = "JOIN {$wpdb->prefix}semantic_analyses sa ON sa.id = se.analysis_id";
        $where = "se.entity_registry_id = %d
                   AND se.approval_status = 'approved'
                   AND sa.id IN ( SELECT MAX(id) FROM {$wpdb->prefix}semantic_analyses WHERE status = 'completed' GROUP BY post_id )";
        $args  = [ (int) $registry_id ];

        if ( $public_only ) {
            $types = Semantic_Settings::public_post_types();
            if ( empty( $types ) ) { $row['linked_content'] = []; return $row; }
            $ph     = implode( ',', array_fill( 0, count( $types ), '%s' ) );
            $join  .= " JOIN {$wpdb->posts} p ON p.ID = sa.post_id";
            $where .= " AND p.post_status = 'publish' AND p.post_password = '' AND p.post_type IN ($ph)
                        AND p.ID NOT IN ( SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = '1' )";
            $args   = array_merge( $args, $types, [ Semantic_Settings::EXCLUDE_META ] );
        }

        $links = $wpdb->get_results( $wpdb->prepare(
            "SELECT se.relevance_score, se.roles, sa.post_id
             FROM {$wpdb->prefix}semantic_suggested_entities se
             {$join}
             WHERE {$where}
             ORDER BY se.relevance_score DESC",
            $args
        ), ARRAY_A );

        $linked = [];
        foreach ( $links as $l ) {
            $roles = json_decode( $l['roles'] ?? '[]', true ) ?: [];
            $role  = '';
            if ( ! empty( $roles ) && is_array( $roles ) ) {
                $first = $roles[0];
                $role  = is_array( $first ) ? ( $first['role'] ?? '' ) : (string) $first;
            }
            $linked[] = [
                'post_id' => (int) $l['post_id'],
                'title'   => get_the_title( $l['post_id'] ) ?: ( 'Post ' . $l['post_id'] ),
                'url'     => get_permalink( $l['post_id'] ),
                'score'   => round( ( (float) $l['relevance_score'] ) * 100 ),
                'role'    => $role,
            ];
        }
        $row['linked_content'] = $linked;
        return $row;
    }
}