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

namespace AncestorPress\Core;

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

class Events_Table {


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

	public static function get_by_person( $gedcom, $personID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND persfamID = %s ORDER BY eventdatetr ASC', self::table(), $gedcom, $personID ) );
	}

	public static function get_by_family( $gedcom, $familyID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND persfamID = %s ORDER BY eventdatetr ASC', self::table(), $gedcom, $familyID ) );
	}

	public static function get_first_by_type( $gedcom, $persfamID, $type ) {
		global $wpdb;
  return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND persfamID = %s AND eventtypeID = %s ORDER BY id ASC LIMIT 1', self::table(), $gedcom, $persfamID, $type ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	public static function get_by_tree( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s', self::table(), $gedcom ) );
	}

	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;
	}

	/**
	 * Fetch an event by tree + eventID (the string key minted at import).
	 *
	 * @since  0.8.0
	 * @param  string $gedcom  Tree identifier.
	 * @param  string $eventID Event reference (e.g. E1).
	 * @return object|null
	 */
	public static function get_by_ref( $gedcom, $eventID ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND eventID = %s LIMIT 1', self::table(), $gedcom, $eventID ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Update an event by row ID.
	 *
	 * @since  0.8.0
	 * @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 ) ) );
	}

	/**
	 * Mint eventIDs (E1, E2... unique per tree) for rows imported before core
	 * 0.8.0, which left eventID NULL. Idempotent: only NULL/empty rows are
	 * touched, numbering continues after each tree's highest existing E<n>,
	 * and once every row carries an eventID this is a cheap no-op. Runs from
	 * Schema::install().
	 *
	 * @since  0.8.0
	 * @return void
	 */
	public static function backfill_event_ids() {
		global $wpdb;
		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- core table via Tables.
		$trees = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT gedcom FROM %i WHERE eventID IS NULL OR eventID = ''", $table ) );
		if ( empty( $trees ) ) {
			return;
		}

		foreach ( $trees as $gedcom ) {
			// Continue after the tree's highest existing E<n> so minted IDs
			// never collide with importer-assigned ones.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$seq = (int) $wpdb->get_var( $wpdb->prepare( "SELECT MAX(CAST(SUBSTRING(eventID, 2) AS UNSIGNED)) FROM %i WHERE gedcom = %s AND eventID REGEXP '^E[0-9]+$'", $table, $gedcom ) );

			do {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM %i WHERE gedcom = %s AND (eventID IS NULL OR eventID = '') ORDER BY id ASC LIMIT 500", $table, $gedcom ) );
				if ( empty( $ids ) ) {
					break;
				}
				// Simple prepared per-row updates: this is a one-time backfill
				// over legacy rows, so plain single-row statements beat any
				// dynamically shaped batch query.
				foreach ( $ids as $id ) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-time backfill write.
					$wpdb->query( $wpdb->prepare( 'UPDATE %i SET eventID = %s WHERE id = %d', $table, 'E' . ( ++$seq ), (int) $id ) );
				}
				$batch = count( $ids );
			} while ( 500 === $batch );
		}
	}

	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 events 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 ) );
	}
}
