<?php
/**
 * ANCP Core - schema owner.
 *
 * Single owner of CREATE/ALTER for the core tables (spec-00 Section 6.1).
 * Schema definitions moved here out of AP Family Trees' class-apft-activator.php
 * (spec-01 item 2); the plugin activator now delegates to this class.
 *
 * Table names resolve through Table_Map so the apft_ -> ancp_ rename is a
 * single-constant change. Migrations are forward-only and additive (new tables,
 * new columns with defaults); the installed state is tracked in the single
 * option ancp_db_version.
 *
 * @package AncestorPress\Core
 * @since   0.1.0
 */

namespace AncestorPress\Core;

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

/**
 * Core schema installer.
 *
 * @since 0.1.0
 */
class Schema {

	/**
	 * Core schema version. Bump on any core table/column change.
	 *
	 * @since 0.1.0
	 * @var string
	 */
	const DB_VERSION = '0.12.0';

	/**
	 * Option storing the installed core schema version (spec-00 Section 2).
	 *
	 * @since 0.1.0
	 * @var string
	 */
	const DB_VERSION_OPTION = 'ancestor_press_core_db_version';

	/**
	 * Earlier generations of the option name (newest first). The value is
	 * copied forward once by is_current(); the old rows are deliberately left
	 * in place so an older bundled core still finds its value if a site is
	 * ever downgraded.
	 *
	 * @since 0.21.0
	 * @var string[]
	 */
	const LEGACY_DB_VERSION_OPTIONS = array( 'ancestorpress_core_db_version', 'ancp_db_version' );

	/**
	 * Create/upgrade all core tables via dbDelta, then stamp the DB version.
	 *
	 * Safe on both fresh installs and upgrades (dbDelta is additive and
	 * idempotent). Does NOT touch plugin-owned tables (e.g. apft_charts).
	 *
	 * @since  0.1.0
	 * @return void
	 */
	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$cc = $wpdb->get_charset_collate();

		// Guarded column rename BEFORE dbDelta: occurrences.variant_group ->
		// name_group (0.12.0). Running first means dbDelta never sees a table
		// that has variant_group but lacks name_group, which would otherwise
		// ADD name_group as a new empty column and strand the data.
		self::migrate_occurrences_name_group();

		foreach ( self::table_definitions( $cc ) as $sql ) {
			dbDelta( $sql );
		}

		// One-time, idempotent backfill (0.12.0): occurrences that predate the
		// record-set concept are filed under a per-dataset "Manual entries"
		// record set, so the record inventory and reversal-by-set semantics
		// hold for every row. Cheap no-op once complete.
		if ( class_exists( 'AncestorPress\Core\Record_Sets_Table' ) ) {
			Record_Sets_Table::backfill_legacy();
		}

		// One-time, idempotent backfill of the names table from existing people
		// (spec-01 item 4). Only people without a primary name row are inserted,
		// so this is a cheap no-op once complete.
		if ( class_exists( 'AncestorPress\Core\Names_Table' ) ) {
			Names_Table::backfill_from_people();
		}

		// Copy-forward profile_images -> media/medialinks (spec-01 item 4).
		if ( class_exists( 'AncestorPress\Core\Media' ) ) {
			Media::backfill_from_profile_images();
		}

		// One-time, idempotent mint of eventID on events rows imported before
		// core 0.8.0 (the importer now mints E1, E2... itself). No table change;
		// the 0.8.0 DB_VERSION bump exists to trigger this data backfill.
		if ( class_exists( 'AncestorPress\Core\Events_Table' ) ) {
			Events_Table::backfill_event_ids();
		}

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	}

	/**
	 * Whether the installed core schema matches this bundle's version.
	 *
	 * @since  0.1.0
	 * @return bool
	 */
	public static function is_current() {
		$stored = get_option( self::DB_VERSION_OPTION );
		if ( false === $stored ) {
			// One-time carry-forward from an earlier generation of the name.
			foreach ( self::LEGACY_DB_VERSION_OPTIONS as $legacy_name ) {
				$legacy = get_option( $legacy_name );
				if ( false !== $legacy ) {
					update_option( self::DB_VERSION_OPTION, $legacy );
					$stored = $legacy;
					break;
				}
			}
		}
		return self::DB_VERSION === $stored;
	}

	/**
	 * The core table CREATE statements, keyed by logical table name.
	 *
	 * Names resolve through Table_Map. Bodies are the shipped AP Family
	 * Trees v1.0.2 definitions, unchanged (spec-00 Section 4: adopted as-is).
	 *
	 * @since  0.1.0
	 * @param  string $cc Charset/collate clause from $wpdb.
	 * @return array<string,string> Logical key => CREATE TABLE SQL.
	 */
	private static function table_definitions( $cc ) {
		$defs = array();

		$defs['trees'] = 'CREATE TABLE ' . Table_Map::name( 'trees' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			treename varchar(255) DEFAULT NULL,
			description text DEFAULT NULL,
			owner varchar(191) DEFAULT NULL,
			email varchar(191) DEFAULT NULL,
			address varchar(255) DEFAULT NULL,
			city varchar(100) DEFAULT NULL,
			state varchar(100) DEFAULT NULL,
			country varchar(100) DEFAULT NULL,
			zip varchar(20) DEFAULT NULL,
			phone varchar(50) DEFAULT NULL,
			secret tinyint(1) DEFAULT 0,
			disallowgedcreate tinyint(1) DEFAULT 0,
			disallowpdf tinyint(1) DEFAULT 0,
			lastimportdate varchar(50) DEFAULT NULL,
			importfilename varchar(255) DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY gedcom (gedcom)
		) $cc;";

		$defs['people'] = 'CREATE TABLE ' . Table_Map::name( 'people' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			personID varchar(50) NOT NULL DEFAULT '',
			lnprefix varchar(50) DEFAULT NULL,
			lastname varchar(191) DEFAULT NULL,
			firstname varchar(191) DEFAULT NULL,
			nickname varchar(100) DEFAULT NULL,
			title varchar(100) DEFAULT NULL,
			prefix varchar(50) DEFAULT NULL,
			suffix varchar(50) DEFAULT NULL,
			nameorder tinyint(1) DEFAULT 1,
			sex varchar(1) DEFAULT NULL,
			birthdate varchar(100) DEFAULT NULL,
			birthdatetr date DEFAULT NULL,
			birthplace varchar(255) DEFAULT NULL,
			altbirthdate varchar(100) DEFAULT NULL,
			altbirthdatetr date DEFAULT NULL,
			altbirthplace varchar(255) DEFAULT NULL,
			deathdate varchar(100) DEFAULT NULL,
			deathdatetr date DEFAULT NULL,
			deathplace varchar(255) DEFAULT NULL,
			burialdate varchar(100) DEFAULT NULL,
			burialdatetr date DEFAULT NULL,
			burialplace varchar(255) DEFAULT NULL,
			burialtype tinyint(1) DEFAULT NULL,
			baptdate varchar(100) DEFAULT NULL,
			baptdatetr date DEFAULT NULL,
			baptplace varchar(255) DEFAULT NULL,
			confdate varchar(100) DEFAULT NULL,
			confdatetr date DEFAULT NULL,
			confplace varchar(255) DEFAULT NULL,
			initdate varchar(100) DEFAULT NULL,
			initdatetr date DEFAULT NULL,
			initplace varchar(255) DEFAULT NULL,
			endldate varchar(100) DEFAULT NULL,
			endldatetr date DEFAULT NULL,
			endlplace varchar(255) DEFAULT NULL,
			famc varchar(50) DEFAULT NULL,
			metaphone varchar(191) DEFAULT NULL,
			living tinyint(1) NOT NULL DEFAULT 0,
			private tinyint(1) NOT NULL DEFAULT 0,
			branch varchar(191) DEFAULT NULL,
			changedate datetime DEFAULT NULL,
			changedby varchar(100) DEFAULT NULL,
			edituser bigint(20) UNSIGNED DEFAULT NULL,
			edittime int(11) UNSIGNED DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_personID (gedcom,personID(50)),
			KEY gedcom_lastname (gedcom,lastname(50)),
			KEY gedcom_living (gedcom,living),
			KEY metaphone (metaphone(50))
		) $cc;";

		$defs['names'] = 'CREATE TABLE ' . Table_Map::name( 'names' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			personID varchar(50) NOT NULL DEFAULT '',
			nametype varchar(20) NOT NULL DEFAULT 'birth',
			lnprefix varchar(50) DEFAULT NULL,
			lastname varchar(191) DEFAULT NULL,
			firstname varchar(191) DEFAULT NULL,
			nickname varchar(100) DEFAULT NULL,
			title varchar(100) DEFAULT NULL,
			prefix varchar(50) DEFAULT NULL,
			suffix varchar(50) DEFAULT NULL,
			nameorder tinyint(1) DEFAULT 1,
			metaphone varchar(191) DEFAULT NULL,
			is_primary tinyint(1) NOT NULL DEFAULT 0,
			ordernum smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_personID (gedcom,personID(50)),
			KEY gedcom_primary (gedcom,is_primary),
			KEY gedcom_lastname (gedcom,lastname(50)),
			KEY metaphone (metaphone(50))
		) $cc;";

		$defs['families'] = 'CREATE TABLE ' . Table_Map::name( 'families' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			familyID varchar(50) NOT NULL DEFAULT '',
			husband varchar(50) DEFAULT NULL,
			wife varchar(50) DEFAULT NULL,
			marrdate varchar(100) DEFAULT NULL,
			marrdatetr date DEFAULT NULL,
			marrplace text DEFAULT NULL,
			marrtype varchar(50) DEFAULT NULL,
			divdate varchar(100) DEFAULT NULL,
			divdatetr date DEFAULT NULL,
			divplace text DEFAULT NULL,
			status varchar(50) DEFAULT NULL,
			sealdate varchar(100) DEFAULT NULL,
			sealdatetr date DEFAULT NULL,
			sealplace text DEFAULT NULL,
			husborder tinyint(1) DEFAULT NULL,
			wifeorder tinyint(1) DEFAULT NULL,
			living tinyint(1) NOT NULL DEFAULT 0,
			private tinyint(1) NOT NULL DEFAULT 0,
			branch varchar(191) DEFAULT NULL,
			changedate datetime DEFAULT NULL,
			changedby varchar(100) DEFAULT NULL,
			edituser bigint(20) UNSIGNED DEFAULT NULL,
			edittime int(11) UNSIGNED DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_familyID (gedcom,familyID(50)),
			KEY gedcom_husband (gedcom,husband(50)),
			KEY gedcom_wife (gedcom,wife(50))
		) $cc;";

		$defs['children'] = 'CREATE TABLE ' . Table_Map::name( 'children' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			familyID varchar(50) NOT NULL DEFAULT '',
			personID varchar(50) NOT NULL DEFAULT '',
			frel varchar(25) DEFAULT NULL,
			mrel varchar(25) DEFAULT NULL,
			sealdate varchar(100) DEFAULT NULL,
			sealdatetr date DEFAULT NULL,
			sealplace text DEFAULT NULL,
			haskids tinyint(1) DEFAULT 0,
			ordernum smallint(5) DEFAULT NULL,
			parentorder tinyint(1) DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_familyID (gedcom,familyID(50)),
			KEY gedcom_personID (gedcom,personID(50))
		) $cc;";

		$defs['events'] = 'CREATE TABLE ' . Table_Map::name( 'events' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			eventID varchar(50) DEFAULT NULL,
			persfamID varchar(50) NOT NULL DEFAULT '',
			eventtypeID varchar(50) NOT NULL DEFAULT '',
			eventdate varchar(100) DEFAULT NULL,
			eventdatetr text DEFAULT NULL,
			eventplace text DEFAULT NULL,
			age varchar(20) DEFAULT NULL,
			agency varchar(191) DEFAULT NULL,
			cause varchar(191) DEFAULT NULL,
			addressID varchar(50) DEFAULT NULL,
			parenttag varchar(50) DEFAULT NULL,
			info text DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_persfamID (gedcom,persfamID(50)),
			KEY gedcom_eventtypeID (gedcom,eventtypeID(50))
		) $cc;";

		$defs['event_types'] = 'CREATE TABLE ' . Table_Map::name( 'event_types' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			tag varchar(50) NOT NULL DEFAULT '',
			display text DEFAULT NULL,
			description varchar(255) DEFAULT NULL,
			type char(1) NOT NULL DEFAULT 'I',
			keep tinyint(1) DEFAULT NULL,
			collapse tinyint(1) DEFAULT NULL,
			ordernum smallint(5) DEFAULT NULL,
			ldsevent tinyint(1) DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY tag (tag(50)),
			KEY type (type)
		) $cc;";

		// 0.12.0 (OPS spec v0.2 Section 3.2): hierarchy (parent_id + place_type:
		// parish, township, townland, street, property, institution,
		// cemetery...), names_observed (JSON array of recorded spellings seen),
		// boundary_notes (free text - boundaries are fluid and studies openly
		// say so) and an optional external map URL.
		$defs['places'] = 'CREATE TABLE ' . Table_Map::name( 'places' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			place varchar(255) NOT NULL DEFAULT '',
			parent_id bigint(20) UNSIGNED DEFAULT NULL,
			place_type varchar(30) DEFAULT NULL,
			longitude decimal(10,7) DEFAULT NULL,
			latitude decimal(10,7) DEFAULT NULL,
			zoom tinyint(3) DEFAULT NULL,
			placelevel tinyint(3) DEFAULT NULL,
			chapman varchar(10) DEFAULT NULL,
			country varchar(3) DEFAULT NULL,
			temple tinyint(1) DEFAULT 0,
			geoignore tinyint(1) DEFAULT 0,
			names_observed longtext DEFAULT NULL,
			boundary_notes text DEFAULT NULL,
			external_map_url varchar(255) DEFAULT NULL,
			notes text DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_place (gedcom,place(100)),
			KEY gedcom_parent (gedcom,parent_id)
		) $cc;";

		$defs['notes'] = 'CREATE TABLE ' . Table_Map::name( 'notes' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			noteID varchar(50) NOT NULL DEFAULT '',
			note text DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_noteID (gedcom,noteID(50))
		) $cc;";

		$defs['note_links'] = 'CREATE TABLE ' . Table_Map::name( 'note_links' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			persfamID varchar(50) NOT NULL DEFAULT '',
			noteID varchar(50) NOT NULL DEFAULT '',
			eventID varchar(50) DEFAULT NULL,
			ordernum float DEFAULT NULL,
			secret tinyint(1) DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_persfamID (gedcom,persfamID(50)),
			KEY gedcom_noteID (gedcom,noteID(50))
		) $cc;";

		$defs['profile_images'] = 'CREATE TABLE ' . Table_Map::name( 'profile_images' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			personID varchar(50) NOT NULL DEFAULT '',
			url varchar(255) DEFAULT NULL,
			file varchar(255) DEFAULT NULL,
			caption varchar(500) DEFAULT NULL,
			sort_order smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_personID (gedcom,personID(50))
		) $cc;";

		$defs['media'] = 'CREATE TABLE ' . Table_Map::name( 'media' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			mediaID varchar(50) NOT NULL DEFAULT '',
			mediatype varchar(30) NOT NULL DEFAULT 'photo',
			attachment_id bigint(20) UNSIGNED DEFAULT NULL,
			external_url varchar(500) DEFAULT NULL,
			thumbnail varchar(500) DEFAULT NULL,
			description varchar(500) DEFAULT NULL,
			mediadate varchar(100) DEFAULT NULL,
			mediadatetr date DEFAULT NULL,
			placeID varchar(50) DEFAULT NULL,
			transcription longtext DEFAULT NULL,
			private tinyint(1) NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_mediaID (gedcom,mediaID(50)),
			KEY gedcom_mediatype (gedcom,mediatype(20))
		) $cc;";

		$defs['medialinks'] = 'CREATE TABLE ' . Table_Map::name( 'medialinks' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			mediaID varchar(50) NOT NULL DEFAULT '',
			linktype varchar(20) NOT NULL DEFAULT 'person',
			persfamID varchar(50) NOT NULL DEFAULT '',
			eventID varchar(50) DEFAULT NULL,
			defphoto tinyint(1) NOT NULL DEFAULT 0,
			ordernum smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_persfam (gedcom,linktype,persfamID(50)),
			KEY gedcom_mediaID (gedcom,mediaID(50)),
			KEY gedcom_defphoto (gedcom,persfamID(50),defphoto)
		) $cc;";

		// 0.12.0 (OPS spec v0.2 Section 3.1): variant_group renamed name_group
		// (semantically neutral name-spelling group; meaning belongs to the
		// consuming plugin). recordsetID files each row under a record set
		// (the unit of import, reversal and inventory). *_precision states
		// what a normalised DATE value actually asserts (day|month|quarter|
		// year|range; NULL when no date); *_confidence is per-normalised-value
		// (high|low; distinct from quay = source reliability). The structured
		// *_recorded columns replace the census note-squash; extra is JSON for
		// record-type-specific overflow fields.
		$defs['occurrences'] = 'CREATE TABLE ' . Table_Map::name( 'occurrences' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			occurrenceID varchar(50) NOT NULL DEFAULT '',
			recordsetID varchar(50) DEFAULT NULL,
			name_recorded varchar(191) NOT NULL DEFAULT '',
			name_std varchar(191) DEFAULT NULL,
			surname_recorded varchar(191) DEFAULT NULL,
			forenames varchar(191) DEFAULT NULL,
			name_group varchar(50) DEFAULT NULL,
			eventdate varchar(100) DEFAULT NULL,
			eventdatetr date DEFAULT NULL,
			eventdate_precision varchar(10) DEFAULT NULL,
			eventdate_confidence varchar(10) DEFAULT NULL,
			recorddate varchar(100) DEFAULT NULL,
			recorddatetr date DEFAULT NULL,
			recorddate_precision varchar(10) DEFAULT NULL,
			place_recorded varchar(255) DEFAULT NULL,
			placeID varchar(50) DEFAULT NULL,
			place_confidence varchar(10) DEFAULT NULL,
			sourceID varchar(50) DEFAULT NULL,
			citetext text DEFAULT NULL,
			recordtype varchar(50) DEFAULT NULL,
			personID varchar(50) DEFAULT NULL,
			familyID varchar(50) DEFAULT NULL,
			eventID varchar(50) DEFAULT NULL,
			role varchar(30) NOT NULL DEFAULT 'subject',
			household_ref varchar(50) DEFAULT NULL,
			age_recorded varchar(50) DEFAULT NULL,
			occupation_recorded varchar(191) DEFAULT NULL,
			relation_recorded varchar(100) DEFAULT NULL,
			condition_recorded varchar(50) DEFAULT NULL,
			birthplace_recorded varchar(255) DEFAULT NULL,
			quay tinyint(1) DEFAULT NULL,
			metaphone varchar(191) DEFAULT NULL,
			private tinyint(1) NOT NULL DEFAULT 0,
			extra longtext DEFAULT NULL,
			note text DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_occurrenceID (gedcom,occurrenceID(50)),
			KEY gedcom_name_std (gedcom,name_std(50)),
			KEY gedcom_name_group (gedcom,name_group(50)),
			KEY gedcom_placeID (gedcom,placeID(50)),
			KEY gedcom_personID (gedcom,personID(50)),
			KEY gedcom_recordsetID (gedcom,recordsetID(50)),
			KEY gedcom_household (gedcom,household_ref(50)),
			KEY metaphone (metaphone(50))
		) $cc;";

		$defs['field_defs'] = 'CREATE TABLE ' . Table_Map::name( 'field_defs' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			field_key varchar(100) NOT NULL DEFAULT '',
			field_label varchar(255) NOT NULL DEFAULT '',
			field_type varchar(50) NOT NULL DEFAULT 'text',
			field_options text DEFAULT NULL,
			ordernum smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY gedcom_field_key (gedcom,field_key(100)),
			KEY gedcom_ordernum (gedcom,ordernum)
		) $cc;";

		$defs['field_values'] = 'CREATE TABLE ' . Table_Map::name( 'field_values' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			personID varchar(50) NOT NULL DEFAULT '',
			field_key varchar(100) NOT NULL DEFAULT '',
			field_value text DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY gedcom_person_field (gedcom,personID(50),field_key(100)),
			KEY gedcom_personID (gedcom,personID(50))
		) $cc;";

		// Sources extension (spec-00 5.2-5.4), added in core 0.7.0. Born ancp_.
		$defs['repositories'] = 'CREATE TABLE ' . Table_Map::name( 'repositories' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			repoID varchar(50) NOT NULL DEFAULT '',
			reponame varchar(255) DEFAULT NULL,
			address varchar(255) DEFAULT NULL,
			city varchar(100) DEFAULT NULL,
			state varchar(100) DEFAULT NULL,
			country varchar(100) DEFAULT NULL,
			zip varchar(20) DEFAULT NULL,
			phone varchar(50) DEFAULT NULL,
			email varchar(191) DEFAULT NULL,
			url varchar(255) DEFAULT NULL,
			notes text DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_repoID (gedcom,repoID(50))
		) $cc;";

		$defs['sources'] = 'CREATE TABLE ' . Table_Map::name( 'sources' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			sourceID varchar(50) NOT NULL DEFAULT '',
			title text DEFAULT NULL,
			author varchar(255) DEFAULT NULL,
			publisher varchar(255) DEFAULT NULL,
			pubdate varchar(100) DEFAULT NULL,
			callnum varchar(100) DEFAULT NULL,
			repoID varchar(50) DEFAULT NULL,
			actualtext text DEFAULT NULL,
			comments text DEFAULT NULL,
			mediatype varchar(50) DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_sourceID (gedcom,sourceID(50)),
			KEY gedcom_repoID (gedcom,repoID(50))
		) $cc;";

		$defs['citations'] = 'CREATE TABLE ' . Table_Map::name( 'citations' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			citationID varchar(50) NOT NULL DEFAULT '',
			persfamID varchar(50) NOT NULL DEFAULT '',
			eventID varchar(50) DEFAULT NULL,
			nameID bigint(20) UNSIGNED DEFAULT NULL,
			sourceID varchar(50) NOT NULL DEFAULT '',
			page varchar(255) DEFAULT NULL,
			quay tinyint(1) DEFAULT NULL,
			citedate varchar(100) DEFAULT NULL,
			citedatetr date DEFAULT NULL,
			citetext text DEFAULT NULL,
			note text DEFAULT NULL,
			ordernum smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_persfamID (gedcom,persfamID(50)),
			KEY gedcom_sourceID (gedcom,sourceID(50))
		) $cc;";

		// Branches (0.11.0; Model C DECISION - Paul, 11 July 2026, recorded in
		// 06-Docs-and-Specs/decision-branches-schema-model-C.md and spec-00
		// 5.9): a branch ENTITY plus person/family-branch LINKS, many-to-many
		// from day one - consumers may ship single-select UIs, the schema
		// never needs widening. The branch also stores a TNG-familiar RULE
		// (start person + generations up/down + cousin depth + spouses) the
		// labeling service processes; is_primary marks the one membership a
		// person displays by default (0.11.1); colour is an optional display
		// accent. The comma-separated people/families `branch` columns are a
		// TNG-compatible CACHE rebuilt from branchlinks - branchlinks is the
		// source of truth. Branches are beyond-GEDCOM (organizing data): no
		// importer/exporter carry, by design.
		$defs['branches'] = 'CREATE TABLE ' . Table_Map::name( 'branches' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			branch varchar(100) NOT NULL DEFAULT '',
			description varchar(255) DEFAULT NULL,
			colour varchar(20) DEFAULT NULL,
			personID varchar(50) NOT NULL DEFAULT '',
			agens smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			dgens smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			dagens smallint(5) UNSIGNED NOT NULL DEFAULT 0,
			inclspouses tinyint(1) NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_branch (gedcom,branch(50))
		) $cc;";

		$defs['branchlinks'] = 'CREATE TABLE ' . Table_Map::name( 'branchlinks' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			branch varchar(100) NOT NULL DEFAULT '',
			linktype varchar(20) NOT NULL DEFAULT 'person',
			persfamID varchar(50) NOT NULL DEFAULT '',
			is_primary tinyint(1) NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_branch (gedcom,branch(50)),
			KEY gedcom_persfam (gedcom,linktype,persfamID(50))
		) $cc;";

		// Record sets (0.12.0, OPS spec v0.2 Section 3.5): an imported batch or
		// dataset ("1861 census transcription") - the unit of import, reversal
		// and the auto-generated record inventory. row_count is a stored
		// snapshot maintained via refresh_row_count(), not a live count.
		$defs['record_sets'] = 'CREATE TABLE ' . Table_Map::name( 'record_sets' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			recordsetID varchar(50) NOT NULL DEFAULT '',
			name varchar(255) DEFAULT NULL,
			recordtype varchar(50) DEFAULT NULL,
			coverage text DEFAULT NULL,
			sourceID varchar(50) DEFAULT NULL,
			import_date datetime DEFAULT NULL,
			row_count int(11) UNSIGNED NOT NULL DEFAULT 0,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_recordsetID (gedcom,recordsetID(50))
		) $cc;";

		// Candidates (0.12.0, OPS spec v0.2 Section 3.5): suggested links and
		// observations awaiting judgement. Schema ships ahead of its consumers
		// (the suite's research and matching features) so they arrive without
		// a migration. The engine only ever WRITES candidate rows; nothing is
		// applied to persons/occurrences without an explicit judgement.
		$defs['candidates'] = 'CREATE TABLE ' . Table_Map::name( 'candidates' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			candidateID varchar(50) NOT NULL DEFAULT '',
			type varchar(30) NOT NULL DEFAULT '',
			subject_ids longtext DEFAULT NULL,
			evidence longtext DEFAULT NULL,
			confidence_band varchar(20) DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'open',
			origin varchar(20) NOT NULL DEFAULT 'engine',
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_candidateID (gedcom,candidateID(50)),
			KEY gedcom_status (gedcom,status)
		) $cc;";

		// Judgements (0.12.0, OPS spec v0.2 Section 3.5): the verify/dismiss
		// reasoning log. Dismissed candidates never resurface; the log exports
		// with the study. created_by_user_id records who judged (the standard
		// core column serves the spec's user_id).
		$defs['judgements'] = 'CREATE TABLE ' . Table_Map::name( 'judgements' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			candidateID varchar(50) NOT NULL DEFAULT '',
			decision varchar(10) NOT NULL DEFAULT '',
			canned_reason varchar(30) DEFAULT NULL,
			reason_text text DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_candidateID (gedcom,candidateID(50))
		) $cc;";

		// Interests (0.12.0, OPS spec v0.2 Section 3.5): saved standing
		// queries that surface newly relevant material after each import.
		// query_def is the inspectable query definition (JSON).
		$defs['interests'] = 'CREATE TABLE ' . Table_Map::name( 'interests' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			interestID varchar(50) NOT NULL DEFAULT '',
			name varchar(191) NOT NULL DEFAULT '',
			query_def longtext DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_interestID (gedcom,interestID(50))
		) $cc;";

		// Import maps (0.12.0, OPS spec v0.2 Section 3.5): saved column-mapping
		// templates, free suite-wide. gedcom '' = site-wide template (the
		// common case - a workbook layout is not dataset-specific). mapping is
		// JSON keyed by normalised column heading, so column order can change.
		$defs['import_maps'] = 'CREATE TABLE ' . Table_Map::name( 'import_maps' ) . " (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			gedcom varchar(191) NOT NULL DEFAULT '',
			name varchar(191) NOT NULL DEFAULT '',
			mapping longtext DEFAULT NULL,
			defaults longtext DEFAULT NULL,
			created_by_user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY gedcom_name (gedcom,name(50))
		) $cc;";

		return $defs;
	}

	/**
	 * Guarded, idempotent rename: occurrences.variant_group -> name_group
	 * (0.12.0, OPS spec v0.2 Section 3.1 decision). Runs before dbDelta.
	 *
	 * States handled:
	 * - Table absent (fresh install): no-op; CREATE carries name_group.
	 * - Only variant_group: CHANGE the column, swap the composite index.
	 * - Both columns (a dbDelta ran against the new definition before this
	 *   migration - should not happen, handled defensively): copy values into
	 *   empty name_group cells, then drop variant_group.
	 * - Only name_group: already migrated; ensure the old index is gone.
	 *
	 * @since  0.12.0
	 * @return void
	 */
	private static function migrate_occurrences_name_group() {
		global $wpdb;
		$table = Table_Map::name( 'occurrences' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- schema migration probe.
		if ( $table !== $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; name from the internal table map.
		$columns = $wpdb->get_col( $wpdb->prepare( "SHOW COLUMNS FROM %i", $table ), 0 );
		$has_old = in_array( 'variant_group', $columns, true );
		$has_new = in_array( 'name_group', $columns, true );

		if ( $has_old && ! $has_new ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- guarded one-time column rename on own table.
			$wpdb->query( $wpdb->prepare( "ALTER TABLE %i CHANGE `variant_group` `name_group` varchar(50) DEFAULT NULL", $table ) );
		} elseif ( $has_old && $has_new ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- defensive merge before dropping the superseded column.
			$wpdb->query( $wpdb->prepare( "UPDATE %i SET name_group = variant_group WHERE ( name_group IS NULL OR name_group = '' ) AND variant_group IS NOT NULL AND variant_group <> ''", $table ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- guarded drop of the superseded column.
			$wpdb->query( $wpdb->prepare( "ALTER TABLE %i DROP COLUMN `variant_group`", $table ) );
		}

		// Index swap: drop the old composite index; dbDelta adds the new one
		// from the CREATE definition.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- index probe on own table.
		$old_index = $wpdb->get_results( $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'gedcom_variant_group'", $table ) );
		if ( ! empty( $old_index ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- guarded one-time index swap on own table.
			$wpdb->query( $wpdb->prepare( "ALTER TABLE %i DROP INDEX `gedcom_variant_group`", $table ) );
		}
	}
}
