<?php
/**
 * ANCP Core - Interests table data access.
 *
 * A standing interest is a saved query definition ("anyone named Tuckfield
 * at Mill Lane") that the consuming plugin re-runs after each import to
 * surface newly relevant material. Core stores the definition verbatim and
 * never interprets it - the query language and its execution engine are
 * consumer policy. Shipped schema-only at DB 0.12.0; class arrived with its
 * first consumer (OPS Phase 3, core 0.22.0).
 *
 * @package AncestorPress\Core
 * @since   0.22.0
 */

namespace AncestorPress\Core;

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

/**
 * Interests table CRUD.
 *
 * @since 0.22.0
 */
class Interests_Table {

	/**
	 * Fully-qualified table name.
	 *
	 * @return string
	 */
	public static function table() {
		return Tables::interests();
	}

	/**
	 * Fetch one interest by tree + interestID (query_def decoded).
	 *
	 * @param  string $gedcom     Tree identifier.
	 * @param  string $interestID Interest key.
	 * @return object|null
	 */
	public static function get_by_ref( $gedcom, $interestID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; identifier bound via %i.
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND interestID = %s LIMIT 1', self::table(), $gedcom, $interestID ) );
		return $row ? self::decode( $row ) : null;
	}

	/**
	 * All interests for a tree, newest first (query_def decoded).
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return array
	 */
	public static function get_by_tree( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; identifier bound via %i.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s ORDER BY id DESC', self::table(), $gedcom ) );
		return array_map( array( __CLASS__, 'decode' ), (array) $rows );
	}

	/**
	 * Insert an interest. interestID is generated ('I' + n) when empty;
	 * query_def may be passed as an array (JSON-encoded here).
	 *
	 * @param  array $data Field => value pairs (incl. gedcom, name).
	 * @return string|false The interestID, or false.
	 */
	public static function insert( $data ) {
		global $wpdb;
		if ( empty( $data['gedcom'] ) || empty( $data['name'] ) ) {
			return false;
		}
		if ( empty( $data['interestID'] ) ) {
			$data['interestID'] = self::next_ref( $data['gedcom'] );
		}
		if ( isset( $data['query_def'] ) && is_array( $data['query_def'] ) ) {
			$data['query_def'] = wp_json_encode( $data['query_def'] );
		}

		$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 -- own table.
		return $wpdb->insert( self::table(), $data ) ? $data['interestID'] : false;
	}

	/**
	 * Update an interest by tree + interestID (query_def array encoded).
	 *
	 * @param  string $gedcom     Tree identifier.
	 * @param  string $interestID Interest key.
	 * @param  array  $data       Field => value pairs.
	 * @return int|false
	 */
	public static function update_by_ref( $gedcom, $interestID, $data ) {
		global $wpdb;
		if ( isset( $data['query_def'] ) && is_array( $data['query_def'] ) ) {
			$data['query_def'] = wp_json_encode( $data['query_def'] );
		}
		$data['updated_at'] = Tables::now();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->update(
			self::table(),
			$data,
			array(
				'gedcom'     => $gedcom,
				'interestID' => $interestID,
			)
		);
	}

	/**
	 * Delete one interest.
	 *
	 * @param  string $gedcom     Tree identifier.
	 * @param  string $interestID Interest key.
	 * @return int|false
	 */
	public static function delete_by_ref( $gedcom, $interestID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->delete(
			self::table(),
			array(
				'gedcom'     => $gedcom,
				'interestID' => $interestID,
			)
		);
	}

	/**
	 * Delete every interest for a tree (dataset teardown).
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return int|false
	 */
	public static function delete_by_gedcom( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->delete( self::table(), array( 'gedcom' => $gedcom ) );
	}

	/**
	 * Decode the query_def JSON column of a row in place.
	 *
	 * @param  object $row Raw row.
	 * @return object Row with query_def as an array.
	 */
	private static function decode( $row ) {
		$row->query_def = empty( $row->query_def ) ? array() : (array) json_decode( $row->query_def, true );
		return $row;
	}

	/**
	 * Next sequential interestID for a tree.
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return string
	 */
	private static function next_ref( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table; identifier bound via %i.
		$max = (int) $wpdb->get_var( $wpdb->prepare( "SELECT MAX(CAST(SUBSTRING(interestID, 2) AS UNSIGNED)) FROM %i WHERE gedcom = %s AND interestID REGEXP '^I[0-9]+$'", self::table(), $gedcom ) );
		return 'I' . ( $max + 1 );
	}
}
