<?php
/**
 * ANCP Core - Citations Table (GEDCOM source citation, spec-00 5.4).
 *
 * A citation attaches a source to a person, family, event or name.
 * persfamID targets a person or family (string-key convention); eventID and
 * nameID are optional refinements.
 *
 * @package    AncestorPress\Core
 * @subpackage AncestorPress\Core\DB
 * @since      0.7.0
 */

namespace AncestorPress\Core;

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

class Citations_Table {


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

	/**
	 * Citations for a person/family, with source details joined for display.
	 *
	 * @param  string $gedcom    Tree identifier.
	 * @param  string $persfamID Person or family reference.
	 * @return array             Citation rows with source_title/author/publisher.
	 */
	public static function get_for_persfam( $gedcom, $persfamID ) {
		global $wpdb;
		$citations = self::table();
		$sources   = Tables::sources();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- core tables via Tables.
		return $wpdb->get_results( $wpdb->prepare(
			"SELECT c.*, s.title AS source_title, s.author AS source_author, s.publisher AS source_publisher
			 FROM %i c
			 LEFT JOIN %i s ON s.gedcom = c.gedcom AND s.sourceID = c.sourceID
			 WHERE c.gedcom = %s AND c.persfamID = %s
			 ORDER BY c.ordernum ASC, c.id ASC", $citations, $sources,
			$gedcom,
			$persfamID
		) );
	}

	/**
	 * Citations referencing a source (for frequency/usage displays).
	 *
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $sourceID Source reference.
	 * @return array
	 */
	public static function get_by_source( $gedcom, $sourceID ) {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND sourceID = %s ORDER BY persfamID ASC', self::table(), $gedcom, $sourceID ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

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

	/**
	 * Insert a citation.
	 *
	 * @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 -- core table write.
		$result = $wpdb->insert( self::table(), $data );
		return $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Update a citation by row ID.
	 *
	 * @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 -- core table write.
		return $wpdb->update( self::table(), $data, array( 'id' => absint( $id ) ) );
	}

	/**
	 * Delete a citation by row ID, optionally scoped to a tree (0.23.0).
	 *
	 * Pass $gedcom whenever the row ID came from a request: the delete then
	 * only fires when the citation belongs to that tree, so a manipulated ID
	 * cannot reach across trees. Omitting $gedcom keeps the old unscoped
	 * behaviour for internal cascade paths that already own their rows.
	 *
	 * @param  int    $id     Row ID.
	 * @param  string $gedcom Optional tree identifier to scope the delete to.
	 * @return int|false Rows deleted (0 when scoped and not in that tree).
	 */
	public static function delete( $id, $gedcom = '' ) {
		global $wpdb;
		$where = array( 'id' => absint( $id ) );
		if ( '' !== (string) $gedcom ) {
			$where['gedcom'] = (string) $gedcom;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- core table write.
		return $wpdb->delete( self::table(), $where );
	}

	/**
	 * Delete all citations attached to a person/family (cascade helper).
	 *
	 * @param  string $gedcom    Tree identifier.
	 * @param  string $persfamID Person or family reference.
	 * @return void
	 */
	public static function delete_by_persfam( $gedcom, $persfamID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- core table write.
		$wpdb->delete(
			self::table(),
			array(
				'gedcom'    => $gedcom,
				'persfamID' => $persfamID,
			)
		);
	}

	/**
	 * Delete all citations referencing a source (source delete cascade).
	 *
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $sourceID Source reference.
	 * @return void
	 */
	public static function delete_by_source( $gedcom, $sourceID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- core table write.
		$wpdb->delete(
			self::table(),
			array(
				'gedcom'   => $gedcom,
				'sourceID' => $sourceID,
			)
		);
	}

	/**
	 * Delete all citations 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 -- core table write.
		$wpdb->delete( self::table(), array( 'gedcom' => $gedcom ) );
	}
}
