<?php

/**
 * AP Family Archive - read-only query service over the shared core.
 *
 * Every front-end data need of the archive goes through here: person lookup,
 * family navigation, the surname index, and search (name incl. phonetic,
 * date range, place). READ-ONLY by design - the archive displays conclusions,
 * it does not edit them in 0.1.0. Core CRUD classes (People_Table etc.)
 * are used where they fit; the aggregate/search queries this plugin needs
 * beyond them live here, always against core table names resolved via
 * Tables, always inside the APFAAR_Privacy floor.
 *
 * @package    APFAAR_Family_Archive
 * @subpackage APFAAR_Family_Archive/includes
 * @since      0.1.0
 */
if ( !defined( 'ABSPATH' ) ) {
    exit;
}
/**
 * Core read layer for the archive.
 *
 * @since 0.1.0
 */
class APFAAR_People {
    /**
     * The PRIMARY tree this archive displays (option-configured). The free
     * behaviour: one tree, this one.
     *
     * @return string Gedcom identifier, '' when unconfigured.
     */
    public static function gedcom() {
        return (string) get_option( 'apfaar_gedcom', '' );
    }

    /**
     * Every tree the archive publishes (P5, "multiple trees as one site"):
     * the primary tree plus - when the extension package provides them - the additional trees the
     * owner ticked, each validated against the core (a deleted tree drops
     * out silently). Primary first. Free installs get exactly [primary].
     *
     * @return string[] Gedcom identifiers ([] when unconfigured).
     */
    public static function published_gedcoms() {
        $primary = self::gedcom();
        $out = array();
        if ( '' !== $primary ) {
            $out[] = $primary;
        }
        return $out;
    }

    /**
     * Fetch one person by GEDCOM reference, privacy-gated.
     *
     * @param  string $gedcom   Tree identifier.
     * @param  string $personID Person reference (e.g. I0001).
     * @return object|null      Person row, or null when missing/not viewable.
     */
    public static function get_person( $gedcom, $personID ) {
        if ( '' === $gedcom || '' === $personID || !class_exists( 'AncestorPress\\Core\\People_Table' ) ) {
            return null;
        }
        $person = \AncestorPress\Core\People_Table::get_by_ref( $gedcom, $personID );
        return ( APFAAR_Privacy::can_view( $person ) ? $person : null );
    }

    /**
     * Fetch one person WITHOUT the privacy gate (for placeholder rendering
     * in family listings - callers must go through APFAAR_Privacy before
     * showing anything beyond the placeholder label).
     *
     * @param  string $gedcom   Tree identifier.
     * @param  string $personID Person reference.
     * @return object|null
     */
    public static function get_person_raw( $gedcom, $personID ) {
        if ( '' === $gedcom || '' === $personID || !class_exists( 'AncestorPress\\Core\\People_Table' ) ) {
            return null;
        }
        return \AncestorPress\Core\People_Table::get_by_ref( $gedcom, $personID );
    }

    /**
     * Display name for a person row (no privacy decision here - gate first).
     *
     * @param  object $person Person row.
     * @return string
     */
    public static function display_name( $person ) {
        $parts = array_filter( array(trim( (string) ($person->firstname ?? '') ), trim( (string) ($person->lnprefix ?? '') ), trim( (string) ($person->lastname ?? '') )) );
        $name = implode( ' ', $parts );
        return ( '' !== $name ? $name : __( '(unnamed)', 'ap-family-archive' ) );
    }

    /**
     * Lifespan string "1841-1902" from the translated dates (as-recorded
     * strings stay untouched; this is display convenience only).
     *
     * @param  object $person Person row.
     * @return string
     */
    public static function lifespan( $person ) {
        $birth = self::year_of( $person->birthdatetr ?? null );
        $death = self::year_of( $person->deathdatetr ?? null );
        if ( '' === $birth && '' === $death ) {
            return '';
        }
        return $birth . '&ndash;' . $death;
    }

    /**
     * Extract a display year from a translated date.
     *
     * @param  string|null $datetr MySQL date or null.
     * @return string
     */
    private static function year_of( $datetr ) {
        if ( empty( $datetr ) || '0000-00-00' === $datetr ) {
            return '';
        }
        $year = (int) substr( (string) $datetr, 0, 4 );
        return ( $year > 0 ? (string) $year : '' );
    }

    // ── Surname index ─────────────────────────────────────────────────────────
    /**
     * Initial letters that have surnames, for the A-Z bar.
     *
     * @param  string $gedcom Tree identifier.
     * @param  string $branch Optional branch filter (the extension package; narrowing only).
     * @return string[]       Uppercase initials in order.
     */
    public static function surname_letters( $gedcom, $branch = '' ) {
        global $wpdb;
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table query; fully prepared.
        return $wpdb->get_col( $wpdb->prepare(
            "SELECT DISTINCT UPPER( LEFT( lastname, 1 ) ) AS initial FROM %i\n\t\t\t\t WHERE gedcom = %s AND lastname IS NOT NULL AND lastname != ''\n\t\t\t\t   AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) )\n\t\t\t\t ORDER BY initial ASC",
            \AncestorPress\Core\Tables::people(),
            $gedcom,
            $priv
        ) );
    }

    /**
     * Surnames with bearer counts, optionally restricted to one initial.
     *
     * @param  string $gedcom Tree identifier.
     * @param  string $letter Optional single initial filter.
     * @param  int    $limit  Max rows (0 = all).
     * @param  string $branch Optional branch filter (the extension package; narrowing only).
     * @return array          Rows of (lastname, total).
     */
    public static function surname_counts(
        $gedcom,
        $letter = '',
        $limit = 0,
        $branch = ''
    ) {
        global $wpdb;
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        $initial = ( '' !== $letter ? $wpdb->esc_like( $letter ) . '%' : '%' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table query; fully prepared ( limit 0 = "all rows": the %d then binds the max int sentinel ).
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT lastname, COUNT(*) AS total FROM %i\n\t\t\t\t WHERE gedcom = %s AND lastname IS NOT NULL AND lastname != ''\n\t\t\t\t   AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) )\n\t\t\t\t   AND ( %s = '' OR lastname LIKE %s )\n\t\t\t\t GROUP BY lastname ORDER BY lastname ASC\n\t\t\t\t LIMIT %d",
            \AncestorPress\Core\Tables::people(),
            $gedcom,
            $priv,
            (string) $letter,
            $initial,
            ( $limit > 0 ? (int) $limit : PHP_INT_MAX )
        ) );
    }

    /**
     * People bearing one surname, paginated, privacy-floored.
     *
     * @param  string $gedcom   Tree identifier.
     * @param  string $surname  Exact surname.
     * @param  int    $per_page Page size.
     * @param  int    $page     1-based page number.
     * @param  string $branch   Optional branch filter (the extension package; narrowing only).
     * @return array            { rows: object[], total: int }
     */
    public static function people_by_surname(
        $gedcom,
        $surname,
        $per_page = 50,
        $page = 1,
        $branch = ''
    ) {
        global $wpdb;
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        $offset = max( 0, ($page - 1) * $per_page );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table queries; fully prepared.
        $total = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE gedcom = %s AND lastname = %s\n\t\t\t\t   AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) )",
            \AncestorPress\Core\Tables::people(),
            $gedcom,
            $surname,
            $priv
        ) );
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM %i WHERE gedcom = %s AND lastname = %s\n\t\t\t\t   AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) )\n\t\t\t\t ORDER BY firstname ASC, birthdatetr ASC LIMIT %d OFFSET %d",
            \AncestorPress\Core\Tables::people(),
            $gedcom,
            $surname,
            $priv,
            $per_page,
            $offset
        ) );
        // phpcs:enable
        return array(
            'rows'  => $rows,
            'total' => $total,
        );
    }

    // ── Search ────────────────────────────────────────────────────────────────
    /**
     * Search people by name (optionally phonetic), date range and place.
     *
     * All criteria combine with AND; the privacy floor always applies.
     * Phonetic matching uses the core metaphone column (populated by the
     * core importer/names service) against metaphone() of the query surname.
     *
     * @param  string $gedcom Tree identifier.
     * @param  array  $args   name, phonetic (bool), year_from, year_to, place,
     *                        per_page, page.
     * @return array          { rows: object[], total: int }
     */
    public static function search( $gedcom, $args ) {
        global $wpdb;
        $table = \AncestorPress\Core\Tables::people();
        $defaults = array(
            'name'       => '',
            'phonetic'   => false,
            'name_op'    => 'contains',
            'year_from'  => 0,
            'year_to'    => 0,
            'place'      => '',
            'branch'     => '',
            'sex'        => '',
            'event_type' => '',
            'per_page'   => 50,
            'page'       => 1,
        );
        $args = wp_parse_args( $args, $defaults );
        $where = 'gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) )';
        $where_args = array($gedcom, ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 ));
        $name = trim( (string) $args['name'] );
        if ( '' !== $name ) {
            // Match operator: contains by default; the separate package's
            // helper may widen it. Phonetic overrides the operator either way.
            $op = 'contains';
            $wrap = static function ( $term ) use($wpdb, $op) {
                if ( 'exact' === $op ) {
                    return $wpdb->esc_like( $term );
                    // Compared with LIKE, no wildcards = equality semantics.
                }
                return (( 'starts' === $op ? '' : '%' )) . $wpdb->esc_like( $term ) . '%';
            };
            if ( $args['phonetic'] ) {
                // Sounds-like: metaphone of the query (surname-style single
                // token, or the last token of a full name) against the core
                // phonetic column, with the literal match kept as a fallback
                // so exact hits always surface.
                $tokens = preg_split( '/\\s+/', $name );
                $surname = end( $tokens );
                $phonetic = metaphone( $surname );
                $like_full = '%' . $wpdb->esc_like( $name ) . '%';
                $where .= ' AND ( metaphone = %s OR CONCAT( COALESCE(firstname,""), " ", COALESCE(lastname,"") ) LIKE %s )';
                $where_args[] = $phonetic;
                $where_args[] = $like_full;
            } elseif ( str_contains( $name, ' ' ) ) {
                $parts = preg_split( '/\\s+/', $name, 2 );
                $where .= ' AND ( CONCAT( COALESCE(firstname,""), " ", COALESCE(lastname,"") ) LIKE %s OR ( firstname LIKE %s AND lastname LIKE %s ) )';
                $where_args[] = $wrap( $name );
                $where_args[] = $wrap( $parts[0] );
                $where_args[] = $wrap( $parts[1] );
            } else {
                $where .= ' AND ( lastname LIKE %s OR firstname LIKE %s )';
                $where_args[] = $wrap( $name );
                $where_args[] = $wrap( $name );
            }
        }
        // Date range: a person matches when their lifespan overlaps the range
        // (born before the range ends, and not known dead before it starts).
        $year_from = absint( $args['year_from'] );
        $year_to = absint( $args['year_to'] );
        if ( $year_from > 0 ) {
            $where .= ' AND ( ( birthdatetr IS NOT NULL AND YEAR(birthdatetr) >= %d ) OR ( deathdatetr IS NOT NULL AND YEAR(deathdatetr) >= %d ) )';
            $where_args[] = $year_from;
            $where_args[] = $year_from;
        }
        if ( $year_to > 0 ) {
            $where .= ' AND ( ( birthdatetr IS NOT NULL AND YEAR(birthdatetr) <= %d ) OR ( birthdatetr IS NULL AND deathdatetr IS NOT NULL AND YEAR(deathdatetr) <= %d ) )';
            $where_args[] = $year_to;
            $where_args[] = $year_to;
        }
        $place = trim( (string) $args['place'] );
        if ( '' !== $place ) {
            $like = '%' . $wpdb->esc_like( $place ) . '%';
            $where .= ' AND ( birthplace LIKE %s OR deathplace LIKE %s OR burialplace LIKE %s OR baptplace LIKE %s )';
            $where_args[] = $like;
            $where_args[] = $like;
            $where_args[] = $like;
            $where_args[] = $like;
        }
        $per_page = max( 1, absint( $args['per_page'] ) );
        $offset = max( 0, (absint( $args['page'] ) - 1) * $per_page );
        $q_args = array_merge( array($table), $where_args );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $where is assembled above exclusively from the literal placeholder fragments visible in this method; every value binds through prepare().
        $total = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE ' . $where, $q_args ) );
        $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE ' . $where . ' ORDER BY lastname ASC, firstname ASC LIMIT %d OFFSET %d', array_merge( $q_args, array($per_page, $offset) ) ) );
        // phpcs:enable
        return array(
            'rows'  => $rows,
            'total' => $total,
        );
    }

    // ── Family navigation ─────────────────────────────────────────────────────
    /**
     * The family a person is a child of (their parents), if any.
     *
     * Prefers the denormalised famc pointer, falls back to the children table.
     *
     * @param  string $gedcom Tree identifier.
     * @param  object $person Person row.
     * @return object|null    Family row or null.
     */
    public static function parents_family( $gedcom, $person ) {
        if ( !class_exists( 'AncestorPress\\Core\\Families_Table' ) ) {
            return null;
        }
        if ( !empty( $person->famc ) ) {
            $family = \AncestorPress\Core\Families_Table::get_by_ref( $gedcom, $person->famc );
            if ( $family ) {
                return $family;
            }
        }
        if ( class_exists( 'AncestorPress\\Core\\Children_Table' ) ) {
            $links = \AncestorPress\Core\Children_Table::get_by_person( $gedcom, $person->personID );
            if ( !empty( $links ) ) {
                return \AncestorPress\Core\Families_Table::get_by_ref( $gedcom, $links[0]->familyID );
            }
        }
        return null;
    }

    /**
     * Families where the person is a spouse/partner.
     *
     * @param  string $gedcom   Tree identifier.
     * @param  string $personID Person reference.
     * @return array            Family rows.
     */
    public static function spouse_families( $gedcom, $personID ) {
        if ( !class_exists( 'AncestorPress\\Core\\Families_Table' ) ) {
            return array();
        }
        $families = \AncestorPress\Core\Families_Table::get_for_spouse( $gedcom, $personID );
        return ( is_array( $families ) ? $families : array() );
    }

    /**
     * Fetch one family by GEDCOM reference (no privacy gate here - the family
     * page gates member-by-member so a family with one living member still
     * renders its viewable members).
     *
     * @param  string $gedcom   Tree identifier.
     * @param  string $familyID Family reference (e.g. F001).
     * @return object|null
     */
    public static function get_family( $gedcom, $familyID ) {
        if ( '' === $gedcom || '' === $familyID || !class_exists( 'AncestorPress\\Core\\Families_Table' ) ) {
            return null;
        }
        return \AncestorPress\Core\Families_Table::get_by_ref( $gedcom, $familyID );
    }

    /**
     * Child links for a family, in order.
     *
     * @param  string $gedcom   Tree identifier.
     * @param  string $familyID Family reference.
     * @return array            Children link rows (personID, frel, mrel...).
     */
    public static function children_of( $gedcom, $familyID ) {
        if ( !class_exists( 'AncestorPress\\Core\\Children_Table' ) ) {
            return array();
        }
        $links = \AncestorPress\Core\Children_Table::get_by_family( $gedcom, $familyID );
        return ( is_array( $links ) ? $links : array() );
    }

    // ── Events timeline ───────────────────────────────────────────────────────
    /**
     * Non-vital events for a person or family, with display labels.
     *
     * @param  string $gedcom    Tree identifier.
     * @param  string $persfamID Person or family reference.
     * @return array             Event rows with ->label resolved.
     */
    public static function events_for( $gedcom, $persfamID ) {
        if ( !class_exists( 'AncestorPress\\Core\\Events_Table' ) ) {
            return array();
        }
        $events = \AncestorPress\Core\Events_Table::get_by_person( $gedcom, $persfamID );
        if ( empty( $events ) ) {
            return array();
        }
        foreach ( $events as $event ) {
            $event->label = self::event_label( $event->eventtypeID );
        }
        return $events;
    }

    /**
     * Human label for a GEDCOM event tag via the core event_types registry.
     *
     * @param  string $tag GEDCOM tag (OCCU, RESI, EVEN...).
     * @return string
     */
    private static function event_label( $tag ) {
        static $cache = array();
        if ( isset( $cache[$tag] ) ) {
            return $cache[$tag];
        }
        $label = $tag;
        if ( class_exists( 'AncestorPress\\Core\\Event_Types_Table' ) ) {
            $type = \AncestorPress\Core\Event_Types_Table::get_by_tag( $tag );
            if ( $type && !empty( $type->display ) ) {
                $label = $type->display;
            }
        }
        $cache[$tag] = $label;
        return $label;
    }

    // ── Media (core media/medialinks - spec-00 6.3) ──────────────────────────
    /**
     * Whether the booted core carries the media tables (core 0.3.0+).
     *
     * @return bool
     */
    public static function media_available() {
        return class_exists( 'AncestorPress\\Core\\Media' ) && method_exists( 'AncestorPress\\Core\\Tables', 'media_links' );
    }

    /**
     * Media attached to a person or family, display-ordered.
     *
     * Read-only join over the core media/medialinks tables. The defphoto
     * (profile photo) sorts first. For unprivileged viewers the media
     * private flag is enforced here AND (spec-00 Section 7: a living
     * person's media is never rendered) any item linked to a living/private
     * record ANYWHERE is excluded entirely - a photo shared between a
     * deceased and a living person never surfaces through the deceased
     * person's page either. All IN ADDITION to the person-level floor the
     * caller has already applied.
     *
     * @param  string $gedcom    Tree identifier.
     * @param  string $linktype  person|family.
     * @param  string $persfamID Person or family reference.
     * @return array             Media rows (+ defphoto, ordernum from the link).
     */
    public static function media_for( $gedcom, $linktype, $persfamID ) {
        global $wpdb;
        if ( !self::media_available() || '' === $gedcom || '' === $persfamID ) {
            return array();
        }
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table query; fully prepared (the NOT EXISTS arm hides any item linked to a living/private record).
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT m.*, l.defphoto, l.ordernum\n\t\t\t\t FROM %i l\n\t\t\t\t JOIN %i m ON m.gedcom = l.gedcom AND m.mediaID = l.mediaID\n\t\t\t\t WHERE l.gedcom = %s AND l.linktype = %s AND l.persfamID = %s\n\t\t\t\t   AND ( %d = 1 OR ( m.private = 0 AND NOT EXISTS (\n\t\t\t\t\t\tSELECT 1 FROM %i lx\n\t\t\t\t\t\tLEFT JOIN %i px ON lx.linktype = 'person' AND px.gedcom = lx.gedcom AND px.personID = lx.persfamID\n\t\t\t\t\t\tLEFT JOIN %i fx ON lx.linktype = 'family' AND fx.gedcom = lx.gedcom AND fx.familyID = lx.persfamID\n\t\t\t\t\t\tWHERE lx.gedcom = %s AND lx.mediaID = m.mediaID\n\t\t\t\t\t\t  AND ( ( px.id IS NOT NULL AND ( px.living = 1 OR px.private = 1 ) )\n\t\t\t\t\t\t\t OR ( fx.id IS NOT NULL AND ( fx.living = 1 OR fx.private = 1 ) ) ) ) ) )\n\t\t\t\t ORDER BY l.defphoto DESC, l.ordernum ASC, l.id ASC",
            \AncestorPress\Core\Tables::media_links(),
            \AncestorPress\Core\Tables::media(),
            $gedcom,
            $linktype,
            $persfamID,
            $priv,
            \AncestorPress\Core\Tables::media_links(),
            \AncestorPress\Core\Tables::people(),
            \AncestorPress\Core\Tables::families(),
            $gedcom
        ) );
        return ( is_array( $rows ) ? $rows : array() );
    }

    /**
     * Full-size URL for a media row (WP attachment first, external URL second).
     *
     * @param  object $row Media row.
     * @return string      URL or ''.
     */
    public static function media_url( $row ) {
        if ( !empty( $row->attachment_id ) ) {
            $url = wp_get_attachment_url( (int) $row->attachment_id );
            if ( $url ) {
                return $url;
            }
        }
        return ( !empty( $row->external_url ) ? (string) $row->external_url : '' );
    }

    /**
     * Thumbnail URL for a media row: attachment thumbnail size, else the
     * stored thumbnail path, else the full-size URL.
     *
     * @param  object $row Media row.
     * @return string      URL or ''.
     */
    public static function media_thumb_url( $row ) {
        if ( !empty( $row->attachment_id ) ) {
            $url = wp_get_attachment_image_url( (int) $row->attachment_id, 'thumbnail' );
            if ( $url ) {
                return $url;
            }
        }
        if ( !empty( $row->thumbnail ) ) {
            return (string) $row->thumbnail;
        }
        return self::media_url( $row );
    }

    /**
     * Profile-photo thumbnails for a SET of people in one query (listings).
     *
     * Returns personID => thumbnail URL for every person in the set that has
     * a viewable default photo. Callers pass rows that already passed the
     * privacy floor; private media is still excluded here.
     *
     * @param  string   $gedcom     Tree identifier.
     * @param  string[] $person_ids Person references.
     * @return array                personID => URL.
     */
    public static function photo_thumbs( $gedcom, $person_ids ) {
        global $wpdb;
        if ( !self::media_available() || '' === $gedcom || empty( $person_ids ) ) {
            return array();
        }
        $ids = array_values( array_unique( array_map( 'strval', $person_ids ) ) );
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        // One %s placeholder per id, exactly as the WordPress documentation
        // prescribes for IN() lists; every other token is a fixed placeholder.
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%s' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- fully prepared; the only interpolation is the per-id %s placeholder list built on the line above.
        $rows = $wpdb->get_results( $wpdb->prepare( "SELECT l.persfamID, m.attachment_id, m.thumbnail, m.external_url\n\t\t\t\t FROM %i l\n\t\t\t\t JOIN %i m ON m.gedcom = l.gedcom AND m.mediaID = l.mediaID\n\t\t\t\t WHERE l.gedcom = %s AND l.linktype = 'person' AND l.defphoto = 1\n\t\t\t\t   AND ( %d = 1 OR ( m.private = 0 AND NOT EXISTS (\n\t\t\t\t\t\tSELECT 1 FROM %i lx\n\t\t\t\t\t\tLEFT JOIN %i px ON lx.linktype = 'person' AND px.gedcom = lx.gedcom AND px.personID = lx.persfamID\n\t\t\t\t\t\tLEFT JOIN %i fx ON lx.linktype = 'family' AND fx.gedcom = lx.gedcom AND fx.familyID = lx.persfamID\n\t\t\t\t\t\tWHERE lx.gedcom = %s AND lx.mediaID = m.mediaID\n\t\t\t\t\t\t  AND ( ( px.id IS NOT NULL AND ( px.living = 1 OR px.private = 1 ) )\n\t\t\t\t\t\t\t OR ( fx.id IS NOT NULL AND ( fx.living = 1 OR fx.private = 1 ) ) ) ) ) )\n\t\t\t\t\tAND l.persfamID IN ({$placeholders})\n\t\t\t\t ORDER BY l.ordernum ASC, l.id ASC", array_merge( array(
            \AncestorPress\Core\Tables::media_links(),
            \AncestorPress\Core\Tables::media(),
            $gedcom,
            $priv,
            \AncestorPress\Core\Tables::media_links(),
            \AncestorPress\Core\Tables::people(),
            \AncestorPress\Core\Tables::families(),
            $gedcom
        ), $ids ) ) );
        $map = array();
        foreach ( (array) $rows as $row ) {
            if ( isset( $map[$row->persfamID] ) ) {
                continue;
            }
            $url = self::media_thumb_url( $row );
            if ( '' !== $url ) {
                $map[$row->persfamID] = $url;
            }
        }
        return $map;
    }

    // ── Hub package queries (spec-02 Section 7 free additions) ───────────────
    /**
     * Most frequent surnames with counts, privacy-floored.
     *
     * @param  string $gedcom Tree identifier.
     * @param  int    $limit  How many (TNG convention: 30).
     * @return array          Rows of (lastname, total).
     */
    public static function top_surnames( $gedcom, $limit = 30 ) {
        global $wpdb;
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table query; fully prepared.
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT lastname, COUNT(*) AS total\n\t\t\t\t FROM %i\n\t\t\t\t WHERE gedcom = %s AND lastname IS NOT NULL AND lastname != ''\n\t\t\t\t   AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) )\n\t\t\t\t GROUP BY lastname\n\t\t\t\t ORDER BY total DESC, lastname ASC\n\t\t\t\t LIMIT %d",
            \AncestorPress\Core\Tables::people(),
            $gedcom,
            $priv,
            absint( $limit )
        ) );
    }

    /**
     * Recently added and recently changed people/families ("What's New").
     *
     * @param  string $gedcom Tree identifier.
     * @param  int    $days   Recency window in days.
     * @param  int    $max    Max rows per category.
     * @return array          people_added, people_updated, families_added,
     *                        families_updated (each an array of rows).
     */
    public static function whats_new( $gedcom, $days = 30, $max = 10 ) {
        global $wpdb;
        $people = \AncestorPress\Core\Tables::people();
        $families = \AncestorPress\Core\Tables::families();
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        $cutoff = gmdate( 'Y-m-d H:i:s', time() - absint( $days ) * DAY_IN_SECONDS );
        $max = max( 1, absint( $max ) );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table queries; fully prepared.
        $out = array(
            'people_added'     => $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND created_at >= %s ORDER BY created_at DESC LIMIT %d",
                $people,
                $gedcom,
                $priv,
                $cutoff,
                $max
            ) ),
            'people_updated'   => $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND updated_at >= %s AND updated_at > created_at ORDER BY updated_at DESC LIMIT %d",
                $people,
                $gedcom,
                $priv,
                $cutoff,
                $max
            ) ),
            'families_added'   => $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND created_at >= %s ORDER BY created_at DESC LIMIT %d",
                $families,
                $gedcom,
                $priv,
                $cutoff,
                $max
            ) ),
            'families_updated' => $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND updated_at >= %s AND updated_at > created_at ORDER BY updated_at DESC LIMIT %d",
                $families,
                $gedcom,
                $priv,
                $cutoff,
                $max
            ) ),
        );
        // phpcs:enable
        return $out;
    }

    /**
     * People/families with an event on a given day and/or month
     * ("Dates & Anniversaries" - matches against the translated dates;
     * as-recorded strings are displayed, never matched).
     *
     * @param  string $gedcom Tree identifier.
     * @param  int    $day    Day of month (0 = any).
     * @param  int    $month  Month (0 = any).
     * @return array          births, deaths, marriages (arrays of rows).
     */
    public static function anniversaries( $gedcom, $day = 0, $month = 0 ) {
        global $wpdb;
        if ( !$day && !$month ) {
            return array(
                'births'    => array(),
                'deaths'    => array(),
                'marriages' => array(),
            );
        }
        $people = \AncestorPress\Core\Tables::people();
        $families = \AncestorPress\Core\Tables::families();
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        // One static prepare per query: %i binds the table and date column,
        // %s/%d the values; a day/month of 0 disables that arm.
        $fetch = function ( $table, $col ) use(
            $wpdb,
            $gedcom,
            $day,
            $month,
            $priv
        ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table query; fully prepared.
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM %i WHERE gedcom = %s\n\t\t\t\t\t   AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) )\n\t\t\t\t\t   AND %i IS NOT NULL\n\t\t\t\t\t   AND ( %d = 0 OR DAY(%i) = %d )\n\t\t\t\t\t   AND ( %d = 0 OR MONTH(%i) = %d )\n\t\t\t\t\t ORDER BY MONTH(%i), DAY(%i), YEAR(%i) LIMIT 200",
                $table,
                $gedcom,
                $priv,
                $col,
                $day,
                $col,
                $day,
                $month,
                $col,
                $month,
                $col,
                $col,
                $col
            ) );
        };
        return array(
            'births'    => $fetch( $people, 'birthdatetr' ),
            'deaths'    => $fetch( $people, 'deathdatetr' ),
            'marriages' => $fetch( $families, 'marrdatetr' ),
        );
    }

    /**
     * Place index for the free-lite place browse: distinct places with an
     * optional initial-letter filter. Reads the core place authority; when
     * the authority has no rows for the tree (older imports), falls back to
     * the distinct place strings on people.
     *
     * @param  string $gedcom Tree identifier.
     * @param  string $letter Optional single initial filter.
     * @return array          { letters: string[], places: string[] }
     */
    public static function places_index( $gedcom, $letter = '' ) {
        global $wpdb;
        $places_table = \AncestorPress\Core\Tables::places();
        $initial = ( '' !== $letter ? $wpdb->esc_like( $letter ) . '%' : '%' );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read-only core-table queries; fully prepared.
        $has_authority = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE gedcom = %s", $places_table, $gedcom ) );
        if ( $has_authority ) {
            $letters = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT UPPER( LEFT( place, 1 ) ) FROM %i WHERE gedcom = %s AND place != '' ORDER BY 1", $places_table, $gedcom ) );
            $places = $wpdb->get_col( $wpdb->prepare(
                "SELECT DISTINCT place FROM %i WHERE gedcom = %s AND place != ''\n\t\t\t\t   AND ( %s = '' OR place LIKE %s )\n\t\t\t\t ORDER BY place ASC LIMIT 2000",
                $places_table,
                $gedcom,
                (string) $letter,
                $initial
            ) );
        } else {
            // Fallback: aggregate the four people place columns, written out as
            // explicit UNION arms. The privacy floor applies to every arm - a
            // living person's places must not leak into the index.
            $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
            $people = \AncestorPress\Core\Tables::people();
            $arm_args = array(
                $people,
                $gedcom,
                $priv,
                $people,
                $gedcom,
                $priv,
                $people,
                $gedcom,
                $priv,
                $people,
                $gedcom,
                $priv
            );
            $letters = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT UPPER( LEFT( place, 1 ) ) FROM (\n\t\t\t\t\t SELECT birthplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND birthplace IS NOT NULL AND birthplace != ''\n\t\t\t\t\t UNION SELECT deathplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND deathplace IS NOT NULL AND deathplace != ''\n\t\t\t\t\t UNION SELECT burialplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND burialplace IS NOT NULL AND burialplace != ''\n\t\t\t\t\t UNION SELECT baptplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND baptplace IS NOT NULL AND baptplace != ''\n\t\t\t\t ) AS p ORDER BY 1", $arm_args ) );
            $places = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT place FROM (\n\t\t\t\t\t SELECT birthplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND birthplace IS NOT NULL AND birthplace != ''\n\t\t\t\t\t UNION SELECT deathplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND deathplace IS NOT NULL AND deathplace != ''\n\t\t\t\t\t UNION SELECT burialplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND burialplace IS NOT NULL AND burialplace != ''\n\t\t\t\t\t UNION SELECT baptplace AS place FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND baptplace IS NOT NULL AND baptplace != ''\n\t\t\t\t ) AS p\n\t\t\t\t WHERE ( %s = '' OR place LIKE %s )\n\t\t\t\t ORDER BY place ASC LIMIT 2000", array_merge( $arm_args, array((string) $letter, $initial) ) ) );
        }
        // phpcs:enable
        return array(
            'letters' => ( is_array( $letters ) ? $letters : array() ),
            'places'  => ( is_array( $places ) ? $places : array() ),
        );
    }

    /**
     * Full statistics set for the public statistics page.
     *
     * Counts include living/private people (numbers only, no identities -
     * the TNG convention); the named earliest/latest rows are privacy-gated
     * by the caller through the normal person rendering path.
     *
     * @param  string $gedcom Tree identifier.
     * @return array
     */
    public static function stats_extended( $gedcom ) {
        global $wpdb;
        $people = \AncestorPress\Core\Tables::people();
        $events = \AncestorPress\Core\Tables::events();
        $places = \AncestorPress\Core\Tables::places();
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- core tables via Tables.
        $row = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS total,\n\t\t\t\tSUM( CASE WHEN sex = 'M' THEN 1 ELSE 0 END ) AS males,\n\t\t\t\tSUM( CASE WHEN sex = 'F' THEN 1 ELSE 0 END ) AS females,\n\t\t\t\tSUM( CASE WHEN sex IS NULL OR sex NOT IN ('M','F') THEN 1 ELSE 0 END ) AS unknown_sex,\n\t\t\t\tSUM( CASE WHEN living = 1 THEN 1 ELSE 0 END ) AS living,\n\t\t\t\tCOUNT( DISTINCT CASE WHEN lastname IS NOT NULL AND lastname != '' THEN lastname END ) AS surnames,\n\t\t\t\tAVG( CASE WHEN birthdatetr IS NOT NULL AND deathdatetr IS NOT NULL\n\t\t\t\t\tAND YEAR(deathdatetr) > YEAR(birthdatetr)\n\t\t\t\t\tTHEN YEAR(deathdatetr) - YEAR(birthdatetr) END ) AS avg_lifespan\n\t\t\t FROM %i WHERE gedcom = %s", $people, $gedcom ) );
        $priv = ( APFAAR_Privacy::viewer_is_privileged() ? 1 : 0 );
        $earliest = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND birthdatetr IS NOT NULL AND YEAR(birthdatetr) > 0 ORDER BY birthdatetr ASC LIMIT 1",
            $people,
            $gedcom,
            $priv
        ) );
        $latest = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM %i WHERE gedcom = %s AND ( %d = 1 OR ( ( living = 0 OR living IS NULL ) AND ( private = 0 OR private IS NULL ) ) ) AND birthdatetr IS NOT NULL AND YEAR(birthdatetr) > 0 ORDER BY birthdatetr DESC LIMIT 1",
            $people,
            $gedcom,
            $priv
        ) );
        $event_count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE gedcom = %s", $events, $gedcom ) );
        $place_count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE gedcom = %s", $places, $gedcom ) );
        // phpcs:enable
        $source_count = ( class_exists( 'AncestorPress\\Core\\Sources_Table' ) ? \AncestorPress\Core\Sources_Table::count_by_tree( $gedcom ) : 0 );
        return array(
            'sources'      => $source_count,
            'total'        => (int) ($row->total ?? 0),
            'males'        => (int) ($row->males ?? 0),
            'females'      => (int) ($row->females ?? 0),
            'unknown_sex'  => (int) ($row->unknown_sex ?? 0),
            'living'       => (int) ($row->living ?? 0),
            'surnames'     => (int) ($row->surnames ?? 0),
            'families'     => ( class_exists( 'AncestorPress\\Core\\Families_Table' ) ? \AncestorPress\Core\Families_Table::count_by_tree( $gedcom ) : 0 ),
            'avg_lifespan' => ( null !== ($row->avg_lifespan ?? null) ? round( (float) $row->avg_lifespan, 1 ) : null ),
            'events'       => $event_count,
            'places'       => $place_count,
            'earliest'     => $earliest,
            'latest'       => $latest,
        );
    }

    // ── Statistics (hub page / admin) ─────────────────────────────────────────
    /**
     * Headline counts for the configured tree.
     *
     * @param  string $gedcom Tree identifier.
     * @return array          people, families, surnames.
     */
    public static function stats( $gedcom ) {
        global $wpdb;
        $people = ( class_exists( 'AncestorPress\\Core\\People_Table' ) ? \AncestorPress\Core\People_Table::count_by_tree( $gedcom ) : 0 );
        $families = ( class_exists( 'AncestorPress\\Core\\Families_Table' ) ? \AncestorPress\Core\Families_Table::count_by_tree( $gedcom ) : 0 );
        $table = \AncestorPress\Core\Tables::people();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- core table via Tables.
        $surnames = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT( DISTINCT lastname ) FROM %i WHERE gedcom = %s AND lastname IS NOT NULL AND lastname != ''", $table, $gedcom ) );
        return array(
            'people'   => $people,
            'families' => $families,
            'surnames' => $surnames,
        );
    }

}
