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

namespace AncestorPress\Core;

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

class Note_Links_Table {

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

	public static function get_by_person( $gedcom, $personID ) {
		global $wpdb;
		$t_note_links = self::table();
		$t_notes      = Tables::notes();
  return $wpdb->get_results( $wpdb->prepare( "SELECT nl.*, n.note FROM %i nl LEFT JOIN %i n ON n.noteID = nl.noteID AND n.gedcom = nl.gedcom WHERE nl.gedcom = %s AND nl.persfamID = %s ORDER BY nl.ordernum ASC", $t_note_links, $t_notes, $gedcom, $personID ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Fetch all note links with note text for a tree, indexed by persfamID.
	 *
	 * Returns rows with persfamID and note columns for use by the exporter.
	 *
	 * @since  0.0.1
	 * @param  string $gedcom Tree identifier.
	 * @return array
	 */
	public static function get_by_tree( $gedcom ) {
		global $wpdb;
		$t_note_links = self::table();
		$t_notes      = Tables::notes();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- querying own tables; table names cannot use %s placeholder in prepare().
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT nl.persfamID, n.note FROM %i nl LEFT JOIN %i n ON n.noteID = nl.noteID AND n.gedcom = nl.gedcom WHERE nl.gedcom = %s ORDER BY nl.persfamID ASC, nl.ordernum ASC", $t_note_links, $t_notes,
				$gedcom
			)
		);
		// phpcs:enable
	}

	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 );
		return $result ? (int) $wpdb->insert_id : false;
	}

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

	/**
	 * Delete all note links attached to a person or family reference.
	 *
	 * @param  string $gedcom    Tree slug.
	 * @param  string $persfamID Person (I..) or family (F..) GEDCOM ID.
	 * @return int|false
	 */
	public static function delete_by_persfam( $gedcom, $persfamID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->delete( self::table(), array( 'gedcom' => $gedcom, 'persfamID' => $persfamID ) );
	}
}
