<?php
/**
 * ANCP Core - Record sets table data access.
 *
 * A record set is an imported batch/dataset ("1861 census transcription") -
 * the unit of import, reversal and the auto-generated record inventory (OPS
 * spec v0.2 Sections 3.1/3.5). Every occurrence files under exactly one
 * record set; rows that predate the concept (or are entered by hand) belong
 * to the per-dataset "Manual entries" set created by ensure_manual().
 *
 * @package AncestorPress\Core
 * @since   0.12.0
 */

namespace AncestorPress\Core;

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

/**
 * Record sets table CRUD.
 *
 * @since 0.12.0
 */
class Record_Sets_Table {

	/**
	 * The reserved key of the per-dataset manual/legacy record set.
	 *
	 * @var string
	 */
	const MANUAL_REF = 'RS0';

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

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

	/**
	 * All record sets for a tree, newest first.
	 *
	 * @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.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s ORDER BY id DESC', self::table(), $gedcom ) );
	}

	/**
	 * Insert a record set. recordsetID is generated ('RS' + n) when empty.
	 *
	 * @param  array $data Field => value pairs (incl. gedcom).
	 * @return string|false The recordsetID, or false.
	 */
	public static function insert( $data ) {
		global $wpdb;
		if ( empty( $data['gedcom'] ) ) {
			return false;
		}
		if ( empty( $data['recordsetID'] ) ) {
			$data['recordsetID'] = self::next_ref( $data['gedcom'] );
		}

		$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['recordsetID'] : false;
	}

	/**
	 * Update a record set by tree + recordsetID.
	 *
	 * @param  string $gedcom      Tree identifier.
	 * @param  string $recordsetID Record-set key.
	 * @param  array  $data        Field => value pairs.
	 * @return int|false
	 */
	public static function update_by_ref( $gedcom, $recordsetID, $data ) {
		global $wpdb;
		$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,
				'recordsetID' => $recordsetID,
			)
		);
	}

	/**
	 * Delete a record set row (the caller decides what happens to its
	 * occurrences - see Occurrences_Table::delete_by_record_set()).
	 * The manual set is never deleted.
	 *
	 * @param  string $gedcom      Tree identifier.
	 * @param  string $recordsetID Record-set key.
	 * @return int|false
	 */
	public static function delete_by_ref( $gedcom, $recordsetID ) {
		global $wpdb;
		if ( self::MANUAL_REF === $recordsetID ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->delete(
			self::table(),
			array(
				'gedcom'      => $gedcom,
				'recordsetID' => $recordsetID,
			)
		);
	}

	/**
	 * The per-dataset manual/legacy record set, created on first need.
	 *
	 * The name is stored untranslated: core code carries no text domain (a
	 * bundled library must not claim a plugin's), and the value is data the
	 * consuming plugin may present as it sees fit.
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return string The manual recordsetID (self::MANUAL_REF).
	 */
	public static function ensure_manual( $gedcom ) {
		if ( ! self::get_by_ref( $gedcom, self::MANUAL_REF ) ) {
			self::insert(
				array(
					'gedcom'      => $gedcom,
					'recordsetID' => self::MANUAL_REF,
					'name'        => 'Manual entries',
					'coverage'    => 'Hand-entered occurrences and rows predating record sets.',
				)
			);
		}
		return self::MANUAL_REF;
	}

	/**
	 * Recompute and store a record set's row_count snapshot.
	 *
	 * @param  string $gedcom      Tree identifier.
	 * @param  string $recordsetID Record-set key.
	 * @return void
	 */
	public static function refresh_row_count( $gedcom, $recordsetID ) {
		$count = Occurrences_Table::count_by_tree(
			$gedcom,
			array(
				'recordsetID'     => $recordsetID,
				'include_private' => true,
			)
		);
		self::update_by_ref( $gedcom, $recordsetID, array( 'row_count' => (int) $count ) );
	}

	/**
	 * One-time, idempotent backfill (0.12.0): file every occurrence without a
	 * record set under its dataset's manual set. Called from
	 * Schema::install(); a cheap no-op once complete.
	 *
	 * @return void
	 */
	public static function backfill_legacy() {
		global $wpdb;
		$occurrences = Tables::occurrences();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- backfill probe; identifier bound via %i.
		$gedcoms = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT gedcom FROM %i WHERE recordsetID IS NULL OR recordsetID = ''", $occurrences ) );
		foreach ( $gedcoms as $gedcom ) {
			$ref = self::ensure_manual( $gedcom );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-time backfill; identifier bound via %i.
			$wpdb->query( $wpdb->prepare( "UPDATE %i SET recordsetID = %s WHERE gedcom = %s AND ( recordsetID IS NULL OR recordsetID = '' )", $occurrences, $ref, $gedcom ) );
			self::refresh_row_count( $gedcom, $ref );
		}
	}

	/**
	 * Next sequential recordsetID for a tree ('RS' + n; RS0 is reserved for
	 * the manual set).
	 *
	 * @param  string $gedcom Tree identifier.
	 * @return string
	 */
	private static function next_ref( $gedcom ) {
		global $wpdb;
		$table = self::table();
		// 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(recordsetID, 3) AS UNSIGNED)) FROM %i WHERE gedcom = %s AND recordsetID REGEXP '^RS[0-9]+$'", $table, $gedcom ) );
		return 'RS' . ( $max + 1 );
	}
}
