<?php
/**
 * ANCP Core - Names table data access.
 *
 * The names table holds every name for a person (GEDCOM NAME 1..n). The
 * person's PRIMARY name is also denormalised on the people row as a display
 * cache; this table mirrors it (is_primary = 1) and will hold alternate names.
 *
 * Dual-write contract (spec-00 Section 5.1, spec-01 item 4): whenever a person's
 * name is written, sync_primary() upserts the mirrored is_primary row. It is
 * called centrally from People_Table::insert()/update() so every writer
 * updates both tables.
 *
 * @package AncestorPress\Core
 * @since   0.3.0
 */

namespace AncestorPress\Core;

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

/**
 * Names table CRUD.
 *
 * @since 0.3.0
 */
class Names_Table {


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

	/**
	 * The person's primary name row, or null.
	 *
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID GEDCOM person id.
	 * @return object|null
	 */
	public static function get_primary( $gedcom, $personID ) {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; name cannot be a placeholder.
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM %i WHERE gedcom = %s AND personID = %s AND is_primary = 1 LIMIT 1", $table, $gedcom, $personID )
		);
	}

	/**
	 * All names for a person, ordered.
	 *
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID GEDCOM person id.
	 * @return array
	 */
	public static function get_by_person( $gedcom, $personID ) {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; name cannot be a placeholder.
		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM %i WHERE gedcom = %s AND personID = %s ORDER BY is_primary DESC, ordernum ASC, id ASC", $table, $gedcom, $personID )
		);
	}

	/**
	 * Insert a name row.
	 *
	 * @param  array    $data    Field => value pairs (incl. gedcom, personID).
	 * @param  int|null $user_id Author id (defaults to current user).
	 * @return int|false
	 */
	public static function insert( $data, $user_id = null ) {
		global $wpdb;
		$now  = Tables::now();
		$data = array_merge(
			array(
				'created_by_user_id' => null === $user_id ? get_current_user_id() : (int) $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 a name row.
	 *
	 * @param  int   $id   Row id.
	 * @param  array $data Field => value pairs.
	 * @return int|false
	 */
	public static function update( $id, $data ) {
		global $wpdb;
		$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 ) ) );
	}

	/**
	 * Upsert the person's primary name row from person name fields.
	 *
	 * The full current name must be passed (the person row after write), not a
	 * partial diff, so no field is nulled out.
	 *
	 * @param  string   $gedcom   Tree identifier.
	 * @param  string   $personID GEDCOM person id.
	 * @param  array    $fields   Name fields (firstname, lastname, lnprefix,
	 *                            nickname, title, prefix, suffix, nameorder).
	 * @param  int|null $user_id  Author id.
	 * @return int|false
	 */
	public static function sync_primary( $gedcom, $personID, $fields, $user_id = null ) {
		if ( '' === (string) $gedcom || '' === (string) $personID ) {
			return false;
		}
		$lastname = isset( $fields['lastname'] ) ? (string) $fields['lastname'] : '';
		$data     = array(
			'nametype'   => 'birth',
			'lnprefix'   => isset( $fields['lnprefix'] ) ? $fields['lnprefix'] : null,
			'lastname'   => $lastname,
			'firstname'  => isset( $fields['firstname'] ) ? $fields['firstname'] : null,
			'nickname'   => isset( $fields['nickname'] ) ? $fields['nickname'] : null,
			'title'      => isset( $fields['title'] ) ? $fields['title'] : null,
			'prefix'     => isset( $fields['prefix'] ) ? $fields['prefix'] : null,
			'suffix'     => isset( $fields['suffix'] ) ? $fields['suffix'] : null,
			'nameorder'  => isset( $fields['nameorder'] ) ? (int) $fields['nameorder'] : 1,
			'metaphone'  => $lastname ? metaphone( $lastname ) : '',
			'is_primary' => 1,
			'ordernum'   => 0,
		);

		$existing = self::get_primary( $gedcom, $personID );
		if ( $existing ) {
			return self::update( (int) $existing->id, $data );
		}
		$data['gedcom']   = $gedcom;
		$data['personID'] = $personID;
		return self::insert( $data, $user_id );
	}

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

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

	/**
	 * One-time backfill: create a primary name row for every person that lacks
	 * one, mirroring the people cache. Idempotent - safe to run repeatedly.
	 *
	 * Runs as a single INSERT..SELECT so it is fast even on large trees.
	 *
	 * @return int|false Rows inserted, or false on error.
	 */
	public static function backfill_from_people() {
		global $wpdb;
		$names  = self::table();
		$people = Tables::people();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-time backfill; identifiers bound via %i.
		$result = $wpdb->query(
			$wpdb->prepare(
				"INSERT INTO %i
					(gedcom, personID, nametype, lnprefix, lastname, firstname, nickname, title, prefix, suffix, nameorder, metaphone, is_primary, ordernum, created_by_user_id, created_at, updated_at)
				SELECT p.gedcom, p.personID, 'birth', p.lnprefix, p.lastname, p.firstname, p.nickname, p.title, p.prefix, p.suffix,
					COALESCE(p.nameorder, 1), p.metaphone, 1, 0, p.created_by_user_id, p.created_at, p.updated_at
				FROM %i p
				WHERE NOT EXISTS (
					SELECT 1 FROM %i n
					WHERE n.gedcom = p.gedcom AND n.personID = p.personID AND n.is_primary = 1
				)",
				$names,
				$people,
				$names
			)
		);
		// phpcs:enable

		return false === $result ? false : (int) $result;
	}
}
