<?php

namespace AcademicCrew\Publications;

use AcademicCrew\Sync\DTO\NormalizedPublication;

/**
 * Publication deduplication and manual-merge logic.
 *
 * Lives outside includes/Sync/ (a Pro-only, wordpress.org-excluded
 * namespace — see the "Licensing (Freemius)" section in CLAUDE.md)
 * because merge_publications()/merge_db_records() back the Free-tier
 * manual "merge duplicates" admin tool in Admin\PublicationsPage, not
 * just the Pro automated sync pipeline. The process()/find_existing()
 * family (used only by Pro's SyncOrchestrator) still depends on
 * Sync\DTO\NormalizedPublication — safe, since PHP only resolves a
 * method's type-hints when that method is actually called, and the
 * Free manual-merge path never calls them.
 */
class Deduplicator {

    private const FUZZY_THRESHOLD        = 0.85;
    private const MEMBER_SCOPE_THRESHOLD = 0.70;
    // Two pubs with matching titles but venues this dissimilar are distinct publications.
    private const VENUE_MISMATCH_THRESHOLD = 0.75;
    // …or at least this share of the shorter venue's significant words appears in the other.
    private const VENUE_WORDS_OVERLAP = 0.6;

    public function __construct( private readonly \wpdb $db ) {}

    /**
     * @param  NormalizedPublication[] $publications
     * @param  int                     $member_id    When > 0 enables Level-4 member-scoped dedup.
     * @param  bool                    $allow_insert false = enrich existing rows only; unmatched pubs are ignored.
     * @return array{new: int[], updated: int[], skipped: int[], skip_details: array[], merged: int}
     */
    public function process( array $publications, int $member_id = 0, bool $allow_insert = true ): array {
        $result = [ 'new' => [], 'updated' => [], 'skipped' => [], 'skip_details' => [], 'merged' => 0 ];

        // deduplicate_existing() is intentionally NOT called here.
        // It is called once externally — either at the start of sync_member() for
        // single-member syncs, or once before the loop in sync_all_members() for
        // global syncs — to avoid O(N²) repeated sweeps when syncing N members.

        foreach ( $publications as $pub ) {
            $match = $this->find_existing( $pub, $member_id );

            if ( $match === null ) {
                if ( ! $allow_insert ) {
                    continue;
                }
                if ( $this->is_excluded( $pub ) ) {
                    $result['skipped'][] = 0;
                    $result['skip_details'][] = [
                        'pub_id'      => 0,
                        'source'      => $pub->source,
                        'match_type'  => 'excluded',
                        'match_value' => 'manually excluded',
                        'pub_title'   => $pub->title,
                        'pub_year'    => $pub->year,
                    ];
                } else {
                    $new_id = $this->insert( $pub );
                    if ( $new_id ) {
                        $result['new'][] = $new_id;
                    }
                }
            } else {
                $updated = $this->merge_into_existing( $match['id'], $pub );
                if ( $updated ) {
                    $result['updated'][] = $match['id'];
                } else {
                    $result['skipped'][] = $match['id'];
                    $result['skip_details'][] = [
                        'pub_id'      => $match['id'],
                        'source'      => $pub->source,
                        'match_type'  => $match['match_type'],
                        'match_value' => $match['match_value'],
                        'pub_title'   => $pub->title,
                        'pub_year'    => $pub->year,
                    ];
                }
            }
        }

        return $result;
    }

    // ---------------------------------------------------------------
    // Level 1: DOI exact match
    // Level 2: Source-specific ID match
    // Level 3: Fuzzy title + year + venue guard (≥85%)
    //          Only runs for incoming pubs that have NO doi.
    //          The query no longer filters "doi IS NULL": a no-doi incoming pub
    //          must be able to match an existing doi-bearing record (same paper
    //          indexed by Scholar without the DOI that ORCID already provided).
    //          doi-bearing DB records are returned first so we always prefer them.
    // Level 4: Member-scoped fuzzy title + year (≥70%, all member pubs)
    //          Only runs when $member_id > 0.
    // ---------------------------------------------------------------

    private function find_existing( NormalizedPublication $pub, int $member_id = 0 ): ?array {
        if ( $pub->doi ) {
            $id = $this->find_by_doi( $pub->doi );
            if ( $id ) {
                return [ 'id' => $id, 'match_type' => 'doi', 'match_value' => $pub->doi ];
            }
        }

        $match = $this->find_by_source_id( $pub );
        if ( $match ) return $match;

        // Fuzzy levels only for pubs without a DOI.
        // If a pub has a DOI and Level 1 did not find it, it is a genuinely new entry.
        if ( ! $pub->doi && $pub->title && $pub->year ) {
            $match = $this->find_by_fuzzy( $pub );
            if ( $match ) return $match;

            if ( $member_id > 0 ) {
                $match = $this->find_by_member_scope( $pub, $member_id );
                if ( $match ) return $match;
            }
        }

        return null;
    }

    private function find_by_doi( string $doi ): ?int {
        $table = $this->db->prefix . 'academiccrew_publications';
        $id    = $this->db->get_var( $this->db->prepare(
            "SELECT id FROM {$table} WHERE doi = %s LIMIT 1",
            strtolower( $doi )
        ) );
        return $id ? (int) $id : null;
    }

    private function find_by_source_id( NormalizedPublication $pub ): ?array {
        $table  = $this->db->prefix . 'academiccrew_publications';
        $checks = [
            'orcid_work_id' => $pub->orcid_work_id,
            'scholar_id'    => $pub->scholar_id,
            'iris_id'       => $pub->iris_id,
        ];

        foreach ( $checks as $column => $value ) {
            if ( ! $value ) continue;
            $id = $this->db->get_var( $this->db->prepare(
                "SELECT id FROM {$table} WHERE {$column} = %s LIMIT 1",
                $value
            ) );
            if ( $id ) {
                return [
                    'id'          => (int) $id,
                    'match_type'  => 'source_id',
                    'match_value' => "{$column}: {$value}",
                ];
            }
        }

        // Second pass: PHP-based scan of merged_ids_json.
        // Avoids JSON_CONTAINS which is MySQL 5.7+ only and has case-sensitive string
        // comparisons that can silently miss matches on some server configurations.
        // Also includes doi here (not in the first pass) so a conflict-stored DOI
        // stored during a manual merge is still found on the next sync.
        $merged_checks = array_filter( array_merge( $checks, [ 'doi' => $pub->doi ] ) );
        if ( $merged_checks ) {
            $records = $this->db->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                "SELECT id, merged_ids_json FROM {$table} WHERE merged_ids_json IS NOT NULL AND merged_ids_json != '[]'",
                ARRAY_A
            );
            foreach ( $records as $record ) {
                $merged = json_decode( (string) $record['merged_ids_json'], true );
                if ( ! is_array( $merged ) ) continue;
                foreach ( $merged as $entry ) {
                    $col = $entry['col'] ?? '';
                    $val = $entry['val'] ?? '';
                    if ( ! $col || ! $val ) continue;
                    if ( isset( $merged_checks[ $col ] ) && $merged_checks[ $col ] === $val ) {
                        return [
                            'id'          => (int) $record['id'],
                            'match_type'  => 'source_id',
                            'match_value' => "merged:{$col}: {$val}",
                        ];
                    }
                }
            }
        }

        return null;
    }

    private function find_by_fuzzy( NormalizedPublication $pub ): ?array {
        $table    = $this->db->prefix . 'academiccrew_publications';
        $year_min = ( $pub->known_year() ?? 0 ) - 1;
        $year_max = ( $pub->known_year() ?? NormalizedPublication::IN_PRESS_YEAR ) + 1;

        // doi IS NULL filter removed: a no-doi incoming pub must be able to find
        // a doi-bearing DB record (e.g. same paper in ORCID and Scholar).
        // doi-bearing records ordered first so we always merge into the authoritative one.
        $candidates = $this->db->get_results( $this->db->prepare(
            "SELECT id, title, venue, doi FROM {$table}
             WHERE ( year BETWEEN %d AND %d OR year >= 9000 )
             ORDER BY (doi IS NULL) ASC, id ASC",
            $year_min,
            $year_max
        ), ARRAY_A );

        if ( empty( $candidates ) ) return null;

        $pub_title_n = $this->normalise_title( $pub->title );
        $pub_venue_n = $this->normalise_title( $pub->venue ?? '' );

        foreach ( $candidates as $candidate ) {
            $cand_title_n = $this->normalise_title( $candidate['title'] );
            similar_text( $pub_title_n, $cand_title_n, $title_pct );

            if ( ( $title_pct / 100.0 ) < self::FUZZY_THRESHOLD ) {
                continue;
            }

            // Venue guard: if BOTH sides have a venue and they differ significantly,
            // treat as distinct publications (same title published in different venues).
            $cand_venue_n = $this->normalise_title( $candidate['venue'] ?? '' );
            if ( $pub_venue_n !== '' && $cand_venue_n !== '' ) {
                if ( ! $this->venues_compatible( $pub_venue_n, $cand_venue_n ) ) {
                    continue;
                }
            }

            return [
                'id'          => (int) $candidate['id'],
                'match_type'  => 'fuzzy',
                'match_value' => round( $title_pct, 1 ) . '%',
            ];
        }

        return null;
    }

    private function find_by_member_scope( NormalizedPublication $pub, int $member_id ): ?array {
        $pub_table = $this->db->prefix . 'academiccrew_publications';
        $rel_table = $this->db->prefix . 'academiccrew_member_publications';
        $year_min  = ( $pub->known_year() ?? 0 ) - 1;
        $year_max  = ( $pub->known_year() ?? NormalizedPublication::IN_PRESS_YEAR ) + 1;

        $candidates = $this->db->get_results( $this->db->prepare(
            "SELECT p.id, p.title, p.venue
             FROM {$pub_table} p
             INNER JOIN {$rel_table} mp ON p.id = mp.publication_id
             WHERE mp.member_id = %d
               AND ( p.year BETWEEN %d AND %d OR p.year >= 9000 )
             ORDER BY (p.doi IS NULL) ASC, p.id ASC",
            $member_id,
            $year_min,
            $year_max
        ), ARRAY_A );

        if ( empty( $candidates ) ) return null;

        $pub_title_n = $this->normalise_title( $pub->title );
        $pub_venue_n = $this->normalise_title( $pub->venue ?? '' );
        $best_pct    = 0.0;
        $best_id     = null;

        foreach ( $candidates as $candidate ) {
            $cand_title_n = $this->normalise_title( $candidate['title'] );
            similar_text( $pub_title_n, $cand_title_n, $title_pct );

            if ( ( $title_pct / 100.0 ) < self::MEMBER_SCOPE_THRESHOLD ) {
                continue;
            }

            $cand_venue_n = $this->normalise_title( $candidate['venue'] ?? '' );
            if ( $pub_venue_n !== '' && $cand_venue_n !== '' ) {
                if ( ! $this->venues_compatible( $pub_venue_n, $cand_venue_n ) ) {
                    continue;
                }
            }

            if ( $title_pct > $best_pct ) {
                $best_pct = $title_pct;
                $best_id  = (int) $candidate['id'];
            }
        }

        return $best_id ? [
            'id'          => $best_id,
            'match_type'  => 'member_scope',
            'match_value' => round( $best_pct, 1 ) . '%',
        ] : null;
    }

    // ---------------------------------------------------------------
    // Existing-duplicates sweep
    // Called at the start of process() to clean up duplicates that
    // slipped through earlier syncs (e.g. before venue guard and the
    // doi IS NULL fix were introduced).
    // ---------------------------------------------------------------

    /**
     * Scan the publications table for pairs that are duplicates under the
     * current matching rules (fuzzy title ≥85% + year proximity ±1 + venue guard)
     * and merge the lower-priority record into the higher-priority one.
     *
     * Priority (kept): has DOI > has source IDs > lower id (oldest).
     *
     * IMPORTANT — year handling: years may be NULL in the DB (stored as 0 by wpdb
     * with %d format). We use `continue` (not `break`) so that records with unknown
     * years are always compared against all other records, regardless of ordering.
     *
     * @return int number of records merged and deleted
     */
    public function deduplicate_existing(): int {
        $table = $this->db->prefix . 'academiccrew_publications';

        $all = $this->db->get_results(
            "SELECT id, title, venue, year, doi, orcid_work_id, scholar_id, iris_id
             FROM {$table}
             ORDER BY id ASC",
            ARRAY_A
        );

        $count = count( $all );
        if ( $count < 2 ) return 0;

        $merged  = 0;
        $dropped = []; // tracks ids already deleted in this pass

        for ( $i = 0; $i < $count; $i++ ) {
            $a = $all[ $i ];
            if ( isset( $dropped[ $a['id'] ] ) ) continue;

            $a_title_n = $this->normalise_title( $a['title'] );
            $a_venue_n = $this->normalise_title( $a['venue'] ?? '' );
            $a_year    = NormalizedPublication::is_real_year( $a['year'] ) ? (int) $a['year'] : 0;

            for ( $j = $i + 1; $j < $count; $j++ ) {
                $b = $all[ $j ];
                if ( isset( $dropped[ $b['id'] ] ) ) continue;

                // Year proximity check: skip only when BOTH years are known and differ by >1.
                // If either year is unknown (0), we still compare — same paper may lack a year
                // on one source but not the other.
                $b_year = NormalizedPublication::is_real_year( $b['year'] ) ? (int) $b['year'] : 0;
                if ( $a_year > 0 && $b_year > 0 && abs( $b_year - $a_year ) > 1 ) {
                    continue;
                }

                $b_title_n = $this->normalise_title( $b['title'] );
                similar_text( $a_title_n, $b_title_n, $title_pct );

                if ( ( $title_pct / 100.0 ) < self::FUZZY_THRESHOLD ) continue;

                // Venue guard: different venues → distinct publications.
                $b_venue_n = $this->normalise_title( $b['venue'] ?? '' );
                if ( $a_venue_n !== '' && $b_venue_n !== '' ) {
                    if ( ! $this->venues_compatible( $a_venue_n, $b_venue_n ) ) continue;
                }

                [ $keep_id, $drop_id ] = $this->pick_authoritative( $a, $b );

                if ( $this->merge_db_records( $keep_id, $drop_id ) ) {
                    $dropped[ $drop_id ] = true;
                    $merged++;
                }

                // If $a itself was the one dropped, stop the inner loop — $a no longer exists.
                if ( $drop_id === (int) $a['id'] ) break;
            }
        }

        return $merged;
    }

    /**
     * Determine which of two duplicate records to keep.
     * Score: has DOI (4pts) > has any source ID (2pts each) > lower id (tie-break).
     */
    private function pick_authoritative( array $a, array $b ): array {
        $score = static function ( array $r ): int {
            return (int) (bool) $r['doi']           * 4
                 + (int) (bool) $r['orcid_work_id'] * 2
                 + (int) (bool) $r['scholar_id']    * 2
                 + (int) (bool) $r['iris_id']       * 2;
        };

        $a_score = $score( $a );
        $b_score = $score( $b );

        if ( $a_score !== $b_score ) {
            return $a_score > $b_score
                ? [ (int) $a['id'], (int) $b['id'] ]
                : [ (int) $b['id'], (int) $a['id'] ];
        }

        // Tie-break: keep the oldest record (lower id).
        return (int) $a['id'] < (int) $b['id']
            ? [ (int) $a['id'], (int) $b['id'] ]
            : [ (int) $b['id'], (int) $a['id'] ];
    }

    /**
     * Public entry point for manual merges from the admin UI.
     * Merges $drop_id into $keep_id and returns true on success.
     */
    public function merge_publications( int $keep_id, int $drop_id ): bool {
        if ( $keep_id === $drop_id || $keep_id <= 0 || $drop_id <= 0 ) {
            return false;
        }
        return $this->merge_db_records( $keep_id, $drop_id );
    }

    /**
     * Merge $drop_id into $keep_id: transfer member relationships, fill empty
     * fields on keep from drop, then delete the drop record.
     *
     * @return bool true if the drop record was successfully deleted
     */
    private function merge_db_records( int $keep_id, int $drop_id ): bool {
        $pub_table = $this->db->prefix . 'academiccrew_publications';
        $rel_table = $this->db->prefix . 'academiccrew_member_publications';

        $keep = $this->db->get_row( $this->db->prepare( "SELECT * FROM {$pub_table} WHERE id = %d", $keep_id ), ARRAY_A );
        $drop = $this->db->get_row( $this->db->prepare( "SELECT * FROM {$pub_table} WHERE id = %d", $drop_id ), ARRAY_A );

        if ( ! $keep || ! $drop ) return false;

        // Reassign member relationships from drop → keep (INSERT IGNORE avoids duplicates).
        $this->db->query( $this->db->prepare(
            "INSERT IGNORE INTO {$rel_table} (member_id, publication_id, is_corresponding)
             SELECT member_id, %d, is_corresponding
             FROM {$rel_table}
             WHERE publication_id = %d",
            $keep_id,
            $drop_id
        ) );
        $this->db->delete( $rel_table, [ 'publication_id' => $drop_id ], [ '%d' ] );

        // Fill NULL/empty fields on keep record from drop record.
        //
        // IMPORTANT: merged_ids_json is updated in a SEPARATE query (below) so that a
        // missing column (e.g. migration not yet applied) cannot cause the entire UPDATE
        // to fail and silently leave the source ID transfers (scholar_id, orcid_work_id…)
        // un-applied on the keep record.  Those transfers are the primary mechanism that
        // prevents the sync from re-inserting the deleted drop record on the next run.
        $main_updates = [];
        $main_formats = [];

        $source_id_fields = [ 'doi', 'orcid_work_id', 'scholar_id', 'iris_id' ];
        $string_fields    = [ ...$source_id_fields, 'title', 'abstract', 'venue', 'pub_type', 'source_api', 'pdf_url', 'url', 'keywords' ];

        $extra_ids = []; // source IDs from drop that couldn't overwrite keep's populated field

        foreach ( $string_fields as $field ) {
            $drop_val = $drop[ $field ] ?? null;
            $keep_val = $keep[ $field ] ?? null;

            if ( $drop_val === null || $drop_val === '' ) continue;

            if ( $keep_val === null || $keep_val === '' ) {
                // Keep field is empty — copy drop's value directly.
                $main_updates[ $field ] = $drop_val;
                $main_formats[]         = '%s';
            } elseif ( in_array( $field, $source_id_fields, true ) && $keep_val !== $drop_val ) {
                // Both have different values for a source-ID field — preserve drop's ID
                // in merged_ids_json so the sync can still find this record next time.
                $extra_ids[] = [ 'col' => $field, 'val' => $drop_val ];
            }
        }

        // authors_json — empty array '[]' counts as "no data".
        if ( ( $keep['authors_json'] === null || $keep['authors_json'] === '[]' )
             && $drop['authors_json'] !== null && $drop['authors_json'] !== '[]' ) {
            $main_updates['authors_json'] = $drop['authors_json'];
            $main_formats[]               = '%s';
        }

        // year — keep the known year if keep lacks one.
        if ( ! NormalizedPublication::is_real_year( $keep['year'] ) && (int) $drop['year'] > 0
             && ( (int) $keep['year'] === 0 || NormalizedPublication::is_real_year( $drop['year'] ) ) ) {
            $main_updates['year'] = (int) $drop['year'];
            $main_formats[]       = '%d';
        }

        // citations — same precedence as a sync (manual > Scholar > highest other).
        $to_int = static fn( $v ) => $v === null || $v === '' ? null : (int) $v;
        if ( NormalizedPublication::citations_win( $to_int( $drop['citations'] ?? null ), $drop['citations_source'] ?? null,
                                                   $to_int( $keep['citations'] ?? null ), $keep['citations_source'] ?? null ) ) {
            $main_updates['citations']        = (int) $drop['citations'];
            $main_formats[]                   = '%d';
            $main_updates['citations_source'] = $drop['citations_source'] ?? null;
            $main_formats[]                   = '%s';
        }

        // Query 1: transfer source IDs and metadata — must succeed even if merged_ids_json column is absent.
        if ( $main_updates ) {
            $main_updates['updated_at'] = current_time( 'mysql', true );
            $main_formats[]             = '%s';
            $this->db->update( $pub_table, $main_updates, [ 'id' => $keep_id ], $main_formats, [ '%d' ] );
        }

        // Merge keep's existing merged_ids_json + drop's merged_ids_json + new extra_ids.
        $keep_merged = json_decode( $keep['merged_ids_json'] ?? '[]', true ) ?: [];
        $drop_merged = json_decode( $drop['merged_ids_json'] ?? '[]', true ) ?: [];
        $all_merged  = array_merge( $keep_merged, $drop_merged, $extra_ids );

        // Deduplicate merged_ids_json entries.
        $seen_merged = [];
        $all_merged  = array_values( array_filter( $all_merged, static function ( $e ) use ( &$seen_merged ): bool {
            if ( ! isset( $e['col'], $e['val'] ) ) return false;
            $k = $e['col'] . ':' . $e['val'];
            if ( isset( $seen_merged[ $k ] ) ) return false;
            $seen_merged[ $k ] = true;
            return true;
        } ) );

        // Query 2: update merged_ids_json separately — fails gracefully if column is missing.
        if ( $all_merged ) {
            $this->db->query( $this->db->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                "UPDATE {$pub_table} SET merged_ids_json = %s WHERE id = %d",
                wp_json_encode( $all_merged ),
                $keep_id
            ) );
        }

        $deleted = $this->db->delete( $pub_table, [ 'id' => $drop_id ], [ '%d' ] );
        return $deleted !== false && $deleted > 0;
    }

    // ---------------------------------------------------------------
    // INSERT
    // ---------------------------------------------------------------

    private function insert( NormalizedPublication $pub ): ?int {
        $table  = $this->db->prefix . 'academiccrew_publications';
        $result = $this->db->insert(
            $table,
            [
                'doi'           => $pub->doi,
                'orcid_work_id' => $pub->orcid_work_id,
                'scholar_id'    => $pub->scholar_id,
                'iris_id'       => $pub->iris_id,
                'title'         => $pub->title,
                'abstract'      => $pub->abstract,
                'year'          => $pub->year,
                'venue'         => $pub->venue,
                'pub_type'      => $pub->pub_type,
                'authors_json'  => json_encode( $pub->raw_authors ),
                'pdf_url'       => $pub->pdf_url,
                'url'           => $pub->url,
                'citations'     => $pub->citations,
                'citations_source' => $pub->citations !== null ? $pub->citation_source() : null,
                'source_api'    => $pub->source,
                'raw_data'      => $pub->raw_data_json,
                'keywords'      => $pub->keywords ? json_encode( $pub->keywords ) : null,
                'created_at'    => current_time( 'mysql', true ),
                'updated_at'    => current_time( 'mysql', true ),
            ],
            [ '%s','%s','%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%s' ]
        );

        return $result ? $this->db->insert_id : null;
    }

    // ---------------------------------------------------------------
    // MERGE — fill NULL fields from new data; never overwrite existing
    // ---------------------------------------------------------------

    private function merge_into_existing( int $existing_id, NormalizedPublication $pub ): bool {
        $table    = $this->db->prefix . 'academiccrew_publications';
        $existing = $this->db->get_row( $this->db->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $existing_id
        ), ARRAY_A );

        if ( ! $existing ) return false;

        $updates = [];
        $formats = [];

        $nullable_fields = [
            'doi'           => [ $pub->doi,           '%s' ],
            'orcid_work_id' => [ $pub->orcid_work_id, '%s' ],
            'scholar_id'    => [ $pub->scholar_id,    '%s' ],
            'iris_id'       => [ $pub->iris_id,       '%s' ],
            'abstract'      => [ $pub->abstract,      '%s' ],
            'venue'         => [ $pub->venue,         '%s' ],
            'pub_type'      => [ $pub->pub_type,      '%s' ],
            'source_api'    => [ $pub->source,        '%s' ],
            'pdf_url'       => [ $pub->pdf_url,       '%s' ],
            'url'           => [ $pub->url,           '%s' ],
            'keywords'      => [ $pub->keywords ? json_encode( $pub->keywords ) : null, '%s' ],
        ];

        foreach ( $nullable_fields as $field => [ $new_value, $format ] ) {
            if ( $new_value !== null && ( $existing[ $field ] === null || $existing[ $field ] === '' ) ) {
                $updates[ $field ] = $new_value;
                $formats[]         = $format;
            }
        }

        // Fill a missing year, and replace the "in press" sentinel once the work is out.
        $existing_known = NormalizedPublication::is_real_year( $existing['year'] ?? null );
        if ( $pub->year !== null && ! $existing_known
             && ( $pub->known_year() !== null || $existing['year'] === null || $existing['year'] === '' ) ) {
            $updates['year'] = $pub->year;
            $formats[]       = '%d';
        }

        $new_authors_json = $pub->raw_authors ? json_encode( $pub->raw_authors ) : null;
        $existing_authors = $existing['authors_json'] ?? null;
        if ( $new_authors_json && ( $existing_authors === null || $existing_authors === '[]' ) ) {
            $updates['authors_json'] = $new_authors_json;
            $formats[]               = '%s';
        }

        $old_citations = isset( $existing['citations'] ) && $existing['citations'] !== '' ? (int) $existing['citations'] : null;
        if ( NormalizedPublication::citations_win( $pub->citations, $pub->citation_source(), $old_citations, $existing['citations_source'] ?? null ) ) {
            $updates['citations']        = $pub->citations;
            $formats[]                   = '%d';
            $updates['citations_source'] = $pub->citation_source();
            $formats[]                   = '%s';
        }

        if ( empty( $updates ) ) return false;

        $updates['updated_at'] = current_time( 'mysql', true );
        $formats[]             = '%s';

        $this->db->update(
            $table,
            $updates,
            [ 'id' => $existing_id ],
            $formats,
            [ '%d' ]
        );

        return true;
    }

    // ---------------------------------------------------------------
    // Exclusion list check
    // ---------------------------------------------------------------

    /**
     * Returns true if any source ID on $pub matches an entry in the exclusion table,
     * meaning an admin previously chose to never re-add this publication from sync.
     */
    public function is_excluded( NormalizedPublication $pub ): bool {
        $table  = $this->db->prefix . 'academiccrew_excluded_publications';
        $checks = array_filter( [
            'doi'           => $pub->doi,
            'orcid_work_id' => $pub->orcid_work_id,
            'scholar_id'    => $pub->scholar_id,
            'iris_id'       => $pub->iris_id,
        ] );
        foreach ( $checks as $column => $value ) {
            $found = $this->db->get_var( $this->db->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                "SELECT id FROM {$table} WHERE {$column} = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $value
            ) );
            if ( $found ) return true;
        }
        return false;
    }

    // ---------------------------------------------------------------
    // Helpers
    // ---------------------------------------------------------------

    /**
     * Same venue? Sources spell one venue very differently ("2025 IEEE/CVF Conference on
     * Computer Vision and Pattern Recognition …, 2025" vs "Proceedings of the Computer
     * Vision and Pattern Recognition Conference, 2027-2035, 2025"), so besides the string
     * similarity the significant words are compared: one venue's words mostly contained
     * in the other's count as the same venue.
     */
    private function venues_compatible( string $a, string $b ): bool {
        similar_text( $a, $b, $pct );
        if ( ( $pct / 100.0 ) >= self::VENUE_MISMATCH_THRESHOLD ) {
            return true;
        }
        $wa = $this->venue_words( $a );
        $wb = $this->venue_words( $b );
        if ( count( $wa ) < 2 || count( $wb ) < 2 ) {
            return false;
        }
        return count( array_intersect( $wa, $wb ) ) / min( count( $wa ), count( $wb ) ) >= self::VENUE_WORDS_OVERLAP;
    }

    /** @return string[] distinct significant words of a normalised venue string */
    private function venue_words( string $venue ): array {
        static $noise = [ 'proceedings', 'conference', 'international', 'workshop', 'workshops', 'annual', 'symposium', 'transactions', 'journal', 'with', 'from', 'that', 'this', 'volume', 'pages', 'ieee', 'acm' ];
        $words = array_filter( explode( ' ', $venue ), static fn( string $w ): bool => strlen( $w ) > 3 && ! ctype_digit( $w ) && ! in_array( $w, $noise, true ) );
        return array_values( array_unique( $words ) );
    }

    private function normalise_title( string $title ): string {
        $title = strtolower( $title );
        $title = preg_replace( '/[^a-z0-9\s]/u', '', $title );
        return trim( (string) preg_replace( '/\s+/', ' ', $title ) );
    }
}