<?php
/**
 * AP Family Trees - Field Values Table
 *
 * CRUD for apft_field_values: custom field values stored per person.
 *
 * @package    APFT_Family_Trees
 * @subpackage APFT_Family_Trees/includes/db
 * @since      0.6.9
 */

namespace AncestorPress\Core;

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

/**
 * Data access for the apft_field_values table.
 *
 * @since 0.6.9
 */
class Field_Values_Table {

	/**
	 * Return the fully qualified table name.
	 *
	 * @since  0.6.9
	 * @return string
	 */
	public static function table() {
		return Tables::field_values();
	}

	/**
	 * Return all custom field values for a person as a key => value map.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID Person ID.
	 * @return array            Associative array of field_key => field_value.
	 */
	public static function get_for_person( $gedcom, $personID ) {
		global $wpdb;
		$t = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare( "SELECT field_key, field_value FROM %i WHERE gedcom = %s AND personID = %s", $t, $gedcom, $personID )
		);
		// phpcs:enable
		$map = array();
		foreach ( (array) $rows as $row ) {
			$map[ $row->field_key ] = $row->field_value;
		}
		return $map;
	}

	/**
	 * Upsert (insert or update) a custom field value for a person.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom    Tree identifier.
	 * @param  string $personID  Person ID.
	 * @param  string $field_key Field key slug.
	 * @param  string $value     Field value (may be empty string to clear).
	 * @return bool
	 */
	public static function upsert( $gedcom, $personID, $field_key, $value ) {
		global $wpdb;
		$now    = Tables::now();
		$uid    = get_current_user_id();
		$exists = self::get_for_person( $gedcom, $personID );

		if ( isset( $exists[ $field_key ] ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return (bool) $wpdb->update(
				self::table(),
				array( 'field_value' => $value, 'updated_at' => $now ),
				array( 'gedcom' => $gedcom, 'personID' => $personID, 'field_key' => $field_key )
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		return (bool) $wpdb->insert(
			self::table(),
			array(
				'gedcom'             => $gedcom,
				'personID'           => $personID,
				'field_key'          => $field_key,
				'field_value'        => $value,
				'created_by_user_id' => $uid,
				'created_at'         => $now,
				'updated_at'         => $now,
			)
		);
	}

	/**
	 * Delete all values for a specific field key within a tree.
	 * Called when a field definition is deleted.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom    Tree identifier.
	 * @param  string $field_key Field key slug.
	 * @return void
	 */
	public static function delete_by_field( $gedcom, $field_key ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( self::table(), array( 'gedcom' => $gedcom, 'field_key' => $field_key ) );
	}

	/**
	 * Delete all custom field values for a person.
	 * Called when a person record is deleted.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID Person ID.
	 * @return void
	 */
	public static function delete_by_person( $gedcom, $personID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( self::table(), array( 'gedcom' => $gedcom, 'personID' => $personID ) );
	}

	/**
	 * Delete all custom field values for a tree.
	 * Called when a tree is deleted.
	 *
	 * @since  0.6.9
	 * @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 ) );
	}

	/**
	 * Return all field values for a tree, indexed by personID then field_key.
	 * Used by the GEDCOM exporter for bulk export.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom Tree identifier.
	 * @return array          Nested array: personID => [ field_key => field_value ]
	 */
	public static function get_all_for_tree( $gedcom ) {
		global $wpdb;
		$t = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare( "SELECT personID, field_key, field_value FROM %i WHERE gedcom = %s ORDER BY personID ASC", $t, $gedcom )
		);
		// phpcs:enable
		$map = array();
		foreach ( (array) $rows as $row ) {
			$map[ $row->personID ][ $row->field_key ] = $row->field_value;
		}
		return $map;
	}
}
