<?php
/**
 * AP Family Trees - People Table
 *
 * @package    APFT_Family_Trees
 * @subpackage APFT_Family_Trees/includes/db
 * @since      2.0.0
 */

namespace AncestorPress\Core;

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

class People_Table {

	public static function table() {
		return Tables::people();
	}

	/**
	 * Fetch a single person by database 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
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), absint( $id ) ) );
	}

	/**
	 * Fetch a single person by gedcom slug + GEDCOM personID string.
	 *
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID GEDCOM person reference (e.g. I0001).
	 * @return object|null
	 */
	public static function get_by_ref( $gedcom, $personID ) {
		global $wpdb;
  return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND personID = %s LIMIT 1', self::table(), $gedcom, $personID ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Return the database row ID for a GEDCOM person reference.
	 *
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID GEDCOM person reference.
	 * @return int|null
	 */
	public static function get_id_by_ref( $gedcom, $personID ) {
		global $wpdb;
  $r = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE gedcom = %s AND personID = %s LIMIT 1', self::table(), $gedcom, $personID ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $r ? (int) $r : null;
	}

	/**
	 * Fetch people for a tree with optional filtering.
	 *
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Optional: include_living, include_private, orderby, search, limit, offset.
	 * @return array
	 */
	public static function get_by_tree( $gedcom, $args = array() ) {
		global $wpdb;

		$defaults = array(
			'include_living'  => false,
			'include_private' => false,
			'orderby'         => 'lastname',
			'search'          => '',
			'limit'           => 0,
			'offset'          => 0,
		);
		$args     = wp_parse_args( $args, $defaults );

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

		if ( ! $args['include_living'] ) {
			$sql .= ' AND ( living = 0 OR living IS NULL )';
		}
		if ( ! $args['include_private'] ) {
			$sql .= ' AND ( private = 0 OR private IS NULL )';
		}
		if ( ! empty( $args['search'] ) ) {
			$search = trim( $args['search'] );
			if ( str_contains( $search, ' ' ) ) {
				// Multi-word query: match against concatenated first+last name
				// AND keep individual field matching so "Jo Sm" finds "John Smith".
				$parts    = preg_split( '/\s+/', $search, 2 );
				$sql     .= ' AND ( CONCAT( COALESCE(firstname,""), " ", COALESCE(lastname,"") ) LIKE %s OR ( firstname LIKE %s AND lastname LIKE %s ) )';
				$params[] = '%' . $wpdb->esc_like( $search ) . '%';
				$params[] = '%' . $wpdb->esc_like( $parts[0] ) . '%';
				$params[] = '%' . $wpdb->esc_like( $parts[1] ) . '%';
			} else {
				// Single word: match either field.
				$like     = '%' . $wpdb->esc_like( $search ) . '%';
				$sql     .= ' AND ( lastname LIKE %s OR firstname LIKE %s )';
				$params[] = $like;
				$params[] = $like;
			}
		}

		// ORDER BY: one fixed literal per whitelisted key.
		if ( 'firstname' === $args['orderby'] ) {
			$sql .= ' ORDER BY firstname ASC';
		} elseif ( 'birthdatetr' === $args['orderby'] ) {
			$sql .= ' ORDER BY birthdatetr ASC';
		} elseif ( 'id' === $args['orderby'] ) {
			$sql .= ' ORDER BY id ASC';
		} else {
			$sql .= ' ORDER BY lastname ASC';
		}

		if ( $args['limit'] > 0 ) {
			$sql     .= ' LIMIT %d OFFSET %d';
			$params[] = absint( $args['limit'] );
			$params[] = absint( $args['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 ) );
	}

	/**
	 * Fetch all people for a tree with their profile image URLs joined.
	 *
	 * @param  string $gedcom         Tree identifier.
	 * @param  bool   $include_living Include living people.
	 * @return array
	 */
	public static function get_tree_nodes( $gedcom, $include_living = false ) {
		global $wpdb;

		$t_people = self::table();
		$t_images = Tables::profile_images();

		$show_living = $include_living ? 1 : 0;

		// Use a correlated subquery for the primary image (lowest sort_order,
		// matching Profile_Images_Table::get_primary()). The old
		// "SELECT pi.url ... GROUP BY p.id" errored under MySQL's default
		// ONLY_FULL_GROUP_BY and otherwise returned an arbitrary image.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- identifiers via %i, values via placeholders; fully literal statement.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT p.*, ( SELECT pi.url FROM %i pi WHERE pi.personID = p.personID AND pi.gedcom = p.gedcom ORDER BY pi.sort_order ASC, pi.id ASC LIMIT 1 ) AS thumb_url FROM %i p WHERE p.gedcom = %s AND ( %d = 1 OR p.living = 0 OR p.living IS NULL ) ORDER BY p.lastname ASC', $t_images, $t_people, $gedcom, $show_living ) );
	}

	/**
	 * Count people in a tree.
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return int
	 */
	public static function count_by_tree( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE gedcom = %s', self::table(), $gedcom ) );
	}

	/**
	 * Insert a new person record.
	 *
	 * @param  array $data Field => value pairs.
	 * @return int|false   New row ID or false.
	 */
	public static function insert( $data ) {
		global $wpdb;
		$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
		$result = $wpdb->insert( self::table(), $data );
		if ( ! $result ) {
			return false;
		}
		// Capture the person's insert id BEFORE the dual-write below, which does
		// its own INSERT and would otherwise overwrite $wpdb->insert_id.
		$new_id = (int) $wpdb->insert_id;

		// Dual-write: mirror the primary name into the names table (spec-01 item 4).
		if ( class_exists( 'AncestorPress\Core\Names_Table' ) && ! empty( $data['gedcom'] ) && ! empty( $data['personID'] ) ) {
			Names_Table::sync_primary( $data['gedcom'], $data['personID'], $data, $data['created_by_user_id'] ?? null );
		}
		return $new_id;
	}

	/**
	 * Person columns that make up the primary name (trigger a names dual-write).
	 *
	 * @var string[]
	 */
	private static $name_fields = array( 'firstname', 'lastname', 'lnprefix', 'nickname', 'title', 'prefix', 'suffix', 'nameorder' );

	/**
	 * Update a person record.
	 *
	 * @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
		$result             = $wpdb->update( self::table(), $data, array( 'id' => absint( $id ) ) );

		// Dual-write: if any name field changed, re-sync the primary name row
		// from the full (post-update) person record so no field is nulled out.
		if ( false !== $result
			&& class_exists( 'AncestorPress\Core\Names_Table' )
			&& array_intersect_key( $data, array_flip( self::$name_fields ) )
		) {
			$person = self::get( absint( $id ) );
			if ( $person ) {
				Names_Table::sync_primary( $person->gedcom, $person->personID, (array) $person, $person->created_by_user_id ?? null );
			}
		}
		return $result;
	}

	/**
	 * Delete a person record.
	 *
	 * @param  int $id Row ID.
	 * @return int|false
	 */
	/**
	 * Delete a person and every row that references them: custom field values,
	 * profile images, child links, person-level events and note links, and
	 * clear their spouse references from any family. Prevents the orphaned rows
	 * that a bare delete() leaves behind.
	 *
	 * @param  int $id Person row ID.
	 * @return int|false Rows deleted from the people table, or false if not found.
	 */
	public static function delete_cascade( $id ) {
		$id     = absint( $id );
		$person = self::get( $id );
		if ( ! $person ) {
			return false;
		}
		$gedcom   = $person->gedcom;
		$personID = $person->personID;

		Field_Values_Table::delete_by_person( $gedcom, $personID );
		Profile_Images_Table::delete_for_person( $gedcom, $personID );
		Children_Table::delete_by_person( $gedcom, $personID );
		Events_Table::delete_by_persfam( $gedcom, $personID );
		Note_Links_Table::delete_by_persfam( $gedcom, $personID );
		Families_Table::detach_person( $gedcom, $personID );
		if ( class_exists( 'AncestorPress\Core\Names_Table' ) ) {
			Names_Table::delete_for_person( $gedcom, $personID );
		}

		return self::delete( $id );
	}

	public static function delete( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->delete( self::table(), array( 'id' => absint( $id ) ) );
	}

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

	/**
	 * Search people by partial name within a tree.
	 *
	 * @param  string $gedcom Tree slug.
	 * @param  string $query  Name fragment.
	 * @param  int    $limit  Max results.
	 * @return array
	 */
	public static function search_by_name( $gedcom, $query, $limit = 20 ) {
		global $wpdb;
		$like = '%' . $wpdb->esc_like( $query ) . '%';
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- querying own table; table name cannot use %s placeholder.
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE gedcom = %s AND CONCAT( COALESCE(firstname,""), " ", COALESCE(lastname,"") ) LIKE %s ORDER BY lastname ASC, firstname ASC LIMIT %d', self::table(),
				$gedcom,
				$like,
				$limit
			)
		);
		// phpcs:enable
	}

	/**
	 * Generate the next available manual person ID for a tree.
	 * Manual IDs use the prefix P- to avoid collision with GEDCOM I-series IDs.
	 *
	 * @param  string $gedcom Tree slug.
	 * @return string         e.g. "P-1", "P-2"
	 */
	public static function next_manual_person_id( $gedcom ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- querying own table; table name cannot use %s placeholder; LIKE prefix is a trusted literal.
		$rows = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT personID FROM %i WHERE gedcom = %s AND personID LIKE %s', self::table(),
				$gedcom,
				$wpdb->esc_like( 'P-' ) . '%'
			)
		);
		// phpcs:enable
		$max = 0;
		foreach ( $rows as $pid ) {
			$n = (int) substr( $pid, 2 );
			if ( $n > $max ) { $max = $n; }
		}
		return 'P-' . ( $max + 1 );
	}
}
