<?php
/**
 * ANCP Core - Occurrences table data access.
 *
 * The sourced occurrence (spec-00 Section 5.8): a single appearance of a name
 * in a source, optionally unlinked to a person. The one shared evidence
 * primitive in core - AP One-Name Studies holds tens of thousands (the
 * unlinked occurrence IS the deliverable) and AP Family Research holds the
 * hybrid researcher's candidate pile. Same object, different volume.
 *
 * As-recorded discipline (spec-00 Section 3): name_recorded and place_recorded
 * are VERBATIM and this class never derives, normalises or overwrites them.
 * Standardised values (name_std, placeID, *datetr) are stored separately and
 * remain user-correctable. Callers must never "helpfully" clean the originals.
 *
 * @package AncestorPress\Core
 * @since   0.3.0
 */

namespace AncestorPress\Core;

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

/**
 * Occurrences table CRUD.
 *
 * @since 0.3.0
 */
class Occurrences_Table {

	/**
	 * Fully-qualified table name.
	 *
	 * @return string
	 */
	public static function table() {
		return Tables::occurrences();
	}

	/**
	 * Append the optional occurrence filters to a query under construction.
	 *
	 * One builder shared by get_by_tree(), count_by_tree() and the
	 * aggregations, so paging, counting and analysis always agree on which
	 * rows are in scope. Each active filter appends one fixed literal clause
	 * containing only placeholders and pushes its value to $params; the
	 * caller makes the single wpdb::prepare() call on the finished query.
	 *
	 * @since  0.4.0
	 * @param  array  $args   {
	 *     Optional filters.
	 *     @type string $search        Free-text LIKE across name_recorded,
	 *                                 name_std and forenames.
	 *     @type string $name_std      Standardised-name LIKE match.
	 *     @type string $surname       Exact standardised-surname match
	 *                                 (surname_recorded - the filing spelling).
	 *     @type string $name_group    Exact name-group tag.
	 *     @type string $recordsetID   Exact record-set key.
	 *     @type string $placeID       Exact place key.
	 *     @type string $place_search  place_recorded LIKE match.
	 *     @type string $personID      Exact linked person.
	 *     @type string $household_ref Exact household reference (census
	 *                                 household grouping within a record set).
	 *     @type string $recordtype    Exact record type.
	 *     @type string $role          Exact role.
	 *     @type int    $year_from     Minimum event year (eventdatetr).
	 *     @type int    $year_to       Maximum event year (eventdatetr).
	 *     @type bool   $unlinked_only Only occurrences with no person link.
	 *     @type bool   $include_private Include private rows (default false).
	 * }
	 * @param  string $sql    Query under construction, appended by reference.
	 * @param  array  $params prepare() parameters, appended by reference in
	 *                        placeholder order.
	 * @return void Every filter column exists only on the occurrences table,
	 *              so the same clauses are unambiguous inside the joined
	 *              county query too.
	 */
	private static function append_filters( $args, &$sql, &$params ) {
		global $wpdb;

		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$sql     .= ' AND ( name_recorded LIKE %s OR name_std LIKE %s OR forenames LIKE %s )';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}
		if ( ! empty( $args['name_std'] ) ) {
			$sql     .= ' AND name_std LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $args['name_std'] ) . '%';
		}
		if ( ! empty( $args['surname'] ) ) {
			$sql     .= ' AND surname_recorded = %s';
			$params[] = $args['surname'];
		}
		if ( ! empty( $args['name_group'] ) ) {
			$sql     .= ' AND name_group = %s';
			$params[] = $args['name_group'];
		}
		if ( ! empty( $args['recordsetID'] ) ) {
			$sql     .= ' AND recordsetID = %s';
			$params[] = $args['recordsetID'];
		}
		if ( ! empty( $args['placeID'] ) ) {
			$sql     .= ' AND placeID = %s';
			$params[] = $args['placeID'];
		}
		if ( ! empty( $args['place_search'] ) ) {
			$sql     .= ' AND place_recorded LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $args['place_search'] ) . '%';
		}
		if ( ! empty( $args['personID'] ) ) {
			$sql     .= ' AND personID = %s';
			$params[] = $args['personID'];
		}
		if ( ! empty( $args['household_ref'] ) ) {
			$sql     .= ' AND household_ref = %s';
			$params[] = $args['household_ref'];
		}
		if ( ! empty( $args['recordtype'] ) ) {
			$sql     .= ' AND recordtype = %s';
			$params[] = $args['recordtype'];
		}
		if ( ! empty( $args['role'] ) ) {
			$sql     .= ' AND role = %s';
			$params[] = $args['role'];
		}
		if ( ! empty( $args['year_from'] ) ) {
			$sql     .= ' AND eventdatetr >= %s';
			$params[] = sprintf( '%04d-01-01', (int) $args['year_from'] );
		}
		if ( ! empty( $args['year_to'] ) ) {
			$sql     .= ' AND eventdatetr <= %s';
			$params[] = sprintf( '%04d-12-31', (int) $args['year_to'] );
		}
		if ( ! empty( $args['unlinked_only'] ) ) {
			$sql .= " AND ( personID IS NULL OR personID = '' )";
		}
		if ( empty( $args['include_private'] ) ) {
			$sql .= ' AND private = 0';
		}
	}

	/**
	 * Fetch one occurrence by row id.
	 *
	 * @param  int $id Row id.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; identifier bound via %i.
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), absint( $id ) ) );
	}

	/**
	 * Fetch one occurrence by tree + occurrenceID.
	 *
	 * @param  string $gedcom       Tree identifier.
	 * @param  string $occurrenceID Occurrence string key.
	 * @return object|null
	 */
	public static function get_by_ref( $gedcom, $occurrenceID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; identifier bound via %i.
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND occurrenceID = %s LIMIT 1', self::table(), $gedcom, $occurrenceID ) );
	}

	/**
	 * Query occurrences for a tree with optional filters.
	 *
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (see append_filters()) plus:
	 *     @type string $orderby 'name'|'name_recorded'|'eventdate'|'recorddate'|'place'|'id' (default 'name').
	 *     @type int    $limit   Page size (default 100).
	 *     @type int    $offset  Offset (default 0).
	 * @return array
	 */
	public static function get_by_tree( $gedcom, $args = array() ) {
		global $wpdb;

		$limit  = isset( $args['limit'] ) ? max( 1, (int) $args['limit'] ) : 100;
		$offset = isset( $args['offset'] ) ? max( 0, (int) $args['offset'] ) : 0;
		$key    = isset( $args['orderby'] ) ? $args['orderby'] : 'name';
		$desc   = ! empty( $args['order'] ) && 'DESC' === strtoupper( $args['order'] );

		// Whole query built as one placeholder string + params for a single
		// wpdb::prepare() call: the table binds via %i, every value via its
		// own placeholder, and the ORDER BY is one fixed literal per branch
		// (a whitelist, never user input).
		$sql    = 'SELECT * FROM %i WHERE gedcom = %s';
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );

		if ( 'eventdate' === $key ) {
			$sql .= $desc ? ' ORDER BY eventdatetr DESC, id DESC' : ' ORDER BY eventdatetr ASC, id ASC';
		} elseif ( 'recorddate' === $key ) {
			$sql .= $desc ? ' ORDER BY recorddatetr DESC, id DESC' : ' ORDER BY recorddatetr ASC, id ASC';
		} elseif ( 'name_recorded' === $key ) {
			$sql .= $desc ? ' ORDER BY name_recorded DESC, id DESC' : ' ORDER BY name_recorded ASC, id ASC';
		} elseif ( 'place' === $key ) {
			$sql .= $desc ? ' ORDER BY place_recorded DESC, eventdatetr DESC, id DESC' : ' ORDER BY place_recorded ASC, eventdatetr ASC, id ASC';
		} elseif ( 'id' === $key ) {
			$sql .= $desc ? ' ORDER BY id DESC' : ' ORDER BY id ASC';
		} else {
			$sql .= $desc ? ' ORDER BY name_std DESC, eventdatetr DESC, id DESC' : ' ORDER BY name_std ASC, eventdatetr ASC, id ASC';
		}
		$sql     .= ' LIMIT %d OFFSET %d';
		$params[] = $limit;
		$params[] = $offset;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Count occurrences for a tree.
	 *
	 * Honours the SAME filter set as get_by_tree() (shared append_filters(), so
	 * a paginated listing and its total always agree).
	 *
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (see append_filters()).
	 * @return int
	 */
	public static function count_by_tree( $gedcom, $args = array() ) {
		global $wpdb;

		$sql    = 'SELECT COUNT(*) FROM %i WHERE gedcom = %s';
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Occurrence counts grouped by place (distribution analysis).
	 *
	 * Rows group by the resolved placeID when set, otherwise by the verbatim
	 * place_recorded string, so unresolved places still appear (as themselves)
	 * rather than vanishing from the analysis. Returned rows:
	 * { placeID: string ('' when unresolved), place_recorded: string,
	 *   total: int }.
	 *
	 * @since  0.4.0
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (see append_filters()) plus:
	 *     @type int $limit Max groups returned (default 500, by count desc).
	 * @return array
	 */
	public static function counts_by_place( $gedcom, $args = array() ) {
		global $wpdb;
		$limit = isset( $args['limit'] ) ? max( 1, (int) $args['limit'] ) : 500;

		// Group key: the resolved placeID when set, otherwise the verbatim
		// string ('~raw~' prefix prevents key/string collisions). Both output
		// columns are constant within a group, so MIN() satisfies
		// ONLY_FULL_GROUP_BY while returning the exact value.
		$sql    = "SELECT
				MIN( COALESCE( NULLIF( placeID, '' ), '' ) ) AS placeID,
				MIN( COALESCE( place_recorded, '' ) ) AS place_recorded,
				COUNT(*) AS total
			FROM %i
			WHERE gedcom = %s";
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql     .= " GROUP BY COALESCE( NULLIF( placeID, '' ), CONCAT( '~raw~', COALESCE( place_recorded, '' ) ) )
			ORDER BY total DESC
			LIMIT %d";
		$params[] = $limit;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Occurrence counts grouped by event-date period (distribution analysis).
	 *
	 * Buckets on YEAR(eventdatetr) in spans of $args['span'] years (default
	 * 10). Undated occurrences return one row with period_start = null so
	 * callers can show an "undated" bucket instead of silently dropping rows.
	 * Returned rows: { period_start: int|null, total: int }.
	 *
	 * @since  0.4.0
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (see append_filters()) plus:
	 *     @type int $span Period width in years (default 10).
	 * @return array
	 */
	public static function counts_by_period( $gedcom, $args = array() ) {
		global $wpdb;
		$span = isset( $args['span'] ) ? max( 1, (int) $args['span'] ) : 10;

		$sql    = 'SELECT
				IF( eventdatetr IS NULL, NULL, FLOOR( YEAR( eventdatetr ) / %d ) * %d ) AS period_start,
				COUNT(*) AS total
			FROM %i
			WHERE gedcom = %s';
		$params = array( $span, $span, self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql .= ' GROUP BY period_start ORDER BY period_start IS NULL, period_start ASC';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Occurrence counts grouped by place AND period in one pass (the
	 * distribution matrix). Same grouping semantics as counts_by_place() and
	 * counts_by_period(). Returned rows: { placeID, place_recorded,
	 * period_start: int|null, total: int }.
	 *
	 * @since  0.4.0
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (see append_filters()) plus:
	 *     @type int $span  Period width in years (default 10).
	 *     @type int $limit Max rows returned (default 2000).
	 * @return array
	 */
	public static function counts_by_place_period( $gedcom, $args = array() ) {
		global $wpdb;
		$span  = isset( $args['span'] ) ? max( 1, (int) $args['span'] ) : 10;
		$limit = isset( $args['limit'] ) ? max( 1, (int) $args['limit'] ) : 2000;

		// Same grouping semantics as counts_by_place(), with the period bucket
		// as a second dimension (alias in GROUP BY is valid MySQL).
		$sql    = "SELECT
				MIN( COALESCE( NULLIF( placeID, '' ), '' ) ) AS placeID,
				MIN( COALESCE( place_recorded, '' ) ) AS place_recorded,
				IF( eventdatetr IS NULL, NULL, FLOOR( YEAR( eventdatetr ) / %d ) * %d ) AS period_start,
				COUNT(*) AS total
			FROM %i
			WHERE gedcom = %s";
		$params = array( $span, $span, self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql     .= " GROUP BY COALESCE( NULLIF( placeID, '' ), CONCAT( '~raw~', COALESCE( place_recorded, '' ) ) ),
				period_start
			ORDER BY total DESC
			LIMIT %d";
		$params[] = $limit;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Occurrence counts grouped by Chapman county code (choropleth data).
	 *
	 * Joins resolved occurrences (placeID set) to the place authority and
	 * groups by its chapman code. Only assigned counties return rows; the
	 * caller derives the unassigned remainder as
	 * count_by_tree() - sum(returned totals) so nothing is silently lost.
	 * Returned rows: { chapman: string, total: int }.
	 *
	 * @since  0.5.0
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (see append_filters()).
	 * @return array
	 */
	public static function counts_by_county( $gedcom, $args = array() ) {
		global $wpdb;

		// The filter fragments reference occurrences-only columns, so they
		// are unambiguous inside this join without an alias prefix.
		$sql    = "SELECT pl.chapman AS chapman, COUNT(*) AS total
			FROM %i o
			INNER JOIN %i pl
				ON pl.gedcom = o.gedcom AND pl.id = CAST(o.placeID AS UNSIGNED)
			WHERE o.placeID REGEXP '^[0-9]+\$'
				AND pl.chapman IS NOT NULL AND pl.chapman <> ''
				AND o.gedcom = %s";
		$params = array( self::table(), Tables::places(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql .= ' GROUP BY pl.chapman ORDER BY total DESC';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Households present in one record set (the census household inventory).
	 *
	 * Groups occurrences by household_ref within a record set. Rows without a
	 * household_ref return one group with household_ref = '' so callers can
	 * show an "ungrouped" bucket instead of silently dropping rows. The
	 * householder name is surfaced when a row in the group carries the
	 * 'householder' role (constant within a real household, so MAX() returns
	 * it under ONLY_FULL_GROUP_BY). Returned rows:
	 * { household_ref: string, total: int, householder: string|null }.
	 *
	 * @since  0.16.0
	 * @param  string $gedcom      Tree identifier.
	 * @param  string $recordsetID Record-set key.
	 * @param  array  $args        Filters (see append_filters()) plus:
	 *     @type int $limit Max groups returned (default 1000).
	 * @return array
	 */
	public static function households_by_record_set( $gedcom, $recordsetID, $args = array() ) {
		global $wpdb;
		$args['recordsetID'] = $recordsetID;
		$limit               = isset( $args['limit'] ) ? max( 1, (int) $args['limit'] ) : 1000;

		$sql    = "SELECT
				COALESCE( household_ref, '' ) AS household_ref,
				COUNT(*) AS total,
				MAX( CASE WHEN role = 'householder' THEN name_recorded END ) AS householder
			FROM %i
			WHERE gedcom = %s";
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql     .= " GROUP BY COALESCE( household_ref, '' )
			ORDER BY CAST( COALESCE( household_ref, '' ) AS UNSIGNED ) ASC, household_ref ASC
			LIMIT %d";
		$params[] = $limit;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Occurrence counts grouped by surname (the surname index).
	 *
	 * Rows group by the name_group tag when set, otherwise by the
	 * standardised surname (surname_recorded - the filing spelling; rows
	 * without one fall back to name_std), so ungrouped spellings still appear
	 * (as themselves) rather than vanishing from the index - the same
	 * grouping semantics as counts_by_place(). What belongs in a name_group
	 * is defined by the consuming plugin (the tag is semantically neutral in
	 * core). Returned rows: { name_group: string ('' when ungrouped),
	 * name: string (one representative surname spelling), spellings: string
	 * (distinct surname spellings in the group, '|'-separated), total: int }.
	 *
	 * @since  0.16.0
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (see append_filters()) plus:
	 *     @type int $limit Max groups returned (default 500, by count desc).
	 * @return array
	 */
	public static function counts_by_name( $gedcom, $args = array() ) {
		global $wpdb;
		$limit = isset( $args['limit'] ) ? max( 1, (int) $args['limit'] ) : 500;

		// Group key: the name_group tag when set, otherwise the standardised
		// surname ('~s~' prefix prevents tag/name collisions). name_group is
		// constant within a group so MIN() returns the exact value; the
		// surname spelling varies inside a multi-spelling group, so MIN() is
		// one representative spelling and the full distinct list travels in
		// `spellings`.
		$sql    = "SELECT
				MIN( COALESCE( NULLIF( name_group, '' ), '' ) ) AS name_group,
				MIN( COALESCE( NULLIF( surname_recorded, '' ), COALESCE( NULLIF( name_std, '' ), name_recorded ) ) ) AS name,
				GROUP_CONCAT( DISTINCT NULLIF( surname_recorded, '' ) ORDER BY surname_recorded SEPARATOR '|' ) AS spellings,
				COUNT(*) AS total
			FROM %i
			WHERE gedcom = %s";
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql     .= " GROUP BY COALESCE( NULLIF( name_group, '' ), CONCAT( '~s~', COALESCE( NULLIF( surname_recorded, '' ), COALESCE( name_std, '' ) ) ) )
			ORDER BY total DESC
			LIMIT %d";
		$params[] = $limit;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Point every occurrence bearing an exact verbatim place string at a
	 * place-authority row (the resolution step).
	 *
	 * Sets ONLY placeID; place_recorded is never touched (spec-00 Section 3).
	 * place_confidence records how the link was made: a user assignment is
	 * 'high'; a future suggestion engine passes its own band.
	 *
	 * @since  0.5.0
	 * @param  string $gedcom         Tree identifier.
	 * @param  string $place_recorded Exact verbatim string to match.
	 * @param  int    $place_id       Place authority row id.
	 * @param  string $confidence     Confidence band for the link ('high').
	 * @return int|false              Rows updated.
	 */
	public static function assign_place( $gedcom, $place_recorded, $place_id, $confidence = 'high' ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->update(
			self::table(),
			array(
				'placeID'          => (string) absint( $place_id ),
				'place_confidence' => $confidence,
				'updated_at'       => Tables::now(),
			),
			array(
				'gedcom'         => $gedcom,
				'place_recorded' => $place_recorded,
			)
		);
	}

	/**
	 * Distinct record types present in a tree (for filter dropdowns).
	 *
	 * @since  0.4.0
	 * @param  string $gedcom Tree identifier.
	 * @return string[]
	 */
	public static function record_types( $gedcom ) {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; identifier bound via %i.
		return $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT recordtype FROM %i WHERE gedcom = %s AND recordtype IS NOT NULL AND recordtype <> '' ORDER BY recordtype ASC", $table, $gedcom ) );
	}

	/**
	 * Insert an occurrence.
	 *
	 * name_recorded is required and stored verbatim (sanitised for storage
	 * safety only - no normalisation). If occurrenceID is empty a sequential
	 * one is generated ('O1', 'O2', ... scoped to the tree). metaphone derives
	 * from name_std (never from the recorded original).
	 *
	 * @param  array $data Field => value pairs (incl. gedcom, name_recorded).
	 * @return int|false   New row id, or false.
	 */
	public static function insert( $data ) {
		global $wpdb;

		if ( empty( $data['gedcom'] ) || ! isset( $data['name_recorded'] ) || '' === trim( (string) $data['name_recorded'] ) ) {
			return false;
		}
		if ( empty( $data['occurrenceID'] ) ) {
			$data['occurrenceID'] = self::next_ref( $data['gedcom'] );
		}
		if ( ! isset( $data['metaphone'] ) && ! empty( $data['name_std'] ) ) {
			$data['metaphone'] = metaphone( $data['name_std'] );
		}

		$now  = Tables::now();
		$data = array_merge(
			array(
				'created_by_user_id' => get_current_user_id(),
				'created_at'         => $now,
				'updated_at'         => $now,
			),
			$data
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		$result = $wpdb->insert( self::table(), $data );
		return $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Update an occurrence.
	 *
	 * Callers must pass name_recorded/place_recorded only when the USER edited
	 * the verbatim value - never as a normalisation side effect. Passing an
	 * empty name_recorded is rejected (the verbatim original is required).
	 *
	 * @param  int   $id   Row id.
	 * @param  array $data Field => value pairs.
	 * @return int|false
	 */
	public static function update( $id, $data ) {
		global $wpdb;
		if ( isset( $data['name_recorded'] ) && '' === trim( (string) $data['name_recorded'] ) ) {
			return false;
		}
		if ( isset( $data['name_std'] ) && ! isset( $data['metaphone'] ) ) {
			$data['metaphone'] = $data['name_std'] ? metaphone( $data['name_std'] ) : '';
		}
		$data['updated_at'] = Tables::now();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->update( self::table(), $data, array( 'id' => absint( $id ) ) );
	}

	/**
	 * Link / unlink an occurrence to a core person (the promotion trail).
	 *
	 * @param  int         $id       Row id.
	 * @param  string|null $personID Person key, or null/'' to unlink.
	 * @return int|false
	 */
	public static function set_person( $id, $personID ) {
		return self::update( $id, array( 'personID' => $personID ? (string) $personID : null ) );
	}

	/**
	 * Delete an occurrence.
	 *
	 * @param  int $id Row id.
	 * @return int|false
	 */
	public static function delete( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->delete( self::table(), array( 'id' => absint( $id ) ) );
	}

	/**
	 * Delete all occurrences for a tree.
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return int|false
	 */
	public static function delete_by_gedcom( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->delete( self::table(), array( 'gedcom' => $gedcom ) );
	}

	/**
	 * Delete all occurrences in one record set (import reversal - a record set
	 * is the unit of import and reversal, OPS spec v0.2 Section 3.1).
	 *
	 * @since  0.12.0
	 * @param  string $gedcom      Tree identifier.
	 * @param  string $recordsetID Record-set key.
	 * @return int|false           Rows deleted.
	 */
	public static function delete_by_record_set( $gedcom, $recordsetID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->delete(
			self::table(),
			array(
				'gedcom'      => $gedcom,
				'recordsetID' => $recordsetID,
			)
		);
	}

	/**
	 * Next sequential occurrenceID for a tree ('O' + n).
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return string
	 */
	private static function next_ref( $gedcom ) {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		$max = (int) $wpdb->get_var( $wpdb->prepare( "SELECT MAX(CAST(SUBSTRING(occurrenceID, 2) AS UNSIGNED)) FROM %i WHERE gedcom = %s AND occurrenceID REGEXP '^O[0-9]+$'", $table, $gedcom ) );
		return 'O' . ( $max + 1 );
	}
}
