<?php
/**
 * ANCP Core - Candidates table data access.
 *
 * A candidate is a machine- or user-raised research suggestion awaiting a
 * human judgement: two occurrences that may be the same person, records that
 * may belong to one family, an existing link that may conflate two people, an
 * anomaly worth a look, or a targeted-search hit. Candidates are WORKING
 * NOTES, never facts: nothing a candidate asserts is applied to a person or
 * occurrence until a judgement says so (spec-00 s3.5 - the one-true-link
 * principle). The schema shipped at DB 0.12.0 ahead of any consumer; this
 * class arrived with its first consumer (OPS Phase 3, core 0.22.0).
 *
 * Core provides the mechanism only. What the types mean, how evidence is
 * built and how confidence bands are labelled is CONSUMER policy - this
 * class never interprets the JSON it stores.
 *
 * @package AncestorPress\Core
 * @since   0.22.0
 */

namespace AncestorPress\Core;

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

/**
 * Candidates table CRUD.
 *
 * @since 0.22.0
 */
class Candidates_Table {

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

	/**
	 * Fetch one candidate by tree + candidateID.
	 *
	 * subject_ids and evidence are returned as decoded arrays.
	 *
	 * @param  string $gedcom      Tree identifier.
	 * @param  string $candidateID Candidate key.
	 * @return object|null
	 */
	public static function get_by_ref( $gedcom, $candidateID ) {
		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 candidateID = %s LIMIT 1', self::table(), $gedcom, $candidateID ) );
		return $row ? self::decode( $row ) : null;
	}

	/**
	 * Append the optional queue filters to a query under construction.
	 *
	 * Each active filter contributes one fixed literal clause containing only
	 * placeholders; its value is pushed to $params. The caller makes the
	 * single wpdb::prepare() call on the finished query.
	 *
	 * @param  array  $args   Filters: status, type, band, origin.
	 * @param  string $sql    Query under construction, appended by reference.
	 * @param  array  $params prepare() parameters, appended by reference.
	 * @return void
	 */
	private static function append_filters( $args, &$sql, &$params ) {
		if ( ! empty( $args['status'] ) ) {
			$sql     .= ' AND status = %s';
			$params[] = $args['status'];
		}
		if ( ! empty( $args['type'] ) ) {
			$sql     .= ' AND type = %s';
			$params[] = $args['type'];
		}
		if ( ! empty( $args['band'] ) ) {
			$sql     .= ' AND confidence_band = %s';
			$params[] = $args['band'];
		}
		if ( ! empty( $args['origin'] ) ) {
			$sql     .= ' AND origin = %s';
			$params[] = $args['origin'];
		}
	}

	/**
	 * Query candidates for a tree.
	 *
	 * Supported $args keys (all optional):
	 * - status  string  e.g. 'open'
	 * - type    string  candidate type
	 * - band    string  confidence band label
	 * - origin  string  'engine' | 'ai' | 'user'
	 * - limit   int     page size (default 50)
	 * - offset  int     page start
	 *
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters, see above.
	 * @return array Decoded row objects, newest first.
	 */
	public static function get_by_tree( $gedcom, $args = array() ) {
		global $wpdb;

		$limit  = isset( $args['limit'] ) ? max( 1, (int) $args['limit'] ) : 50;
		$offset = isset( $args['offset'] ) ? max( 0, (int) $args['offset'] ) : 0;

		$sql    = 'SELECT * FROM %i WHERE gedcom = %s';
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql     .= ' ORDER BY id DESC LIMIT %d OFFSET %d';
		$params[] = $limit;
		$params[] = $offset;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
		return array_map( array( __CLASS__, 'decode' ), (array) $rows );
	}

	/**
	 * Count candidates matching the same filters as get_by_tree().
	 *
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters (limit/offset ignored).
	 * @return int
	 */
	public static function count_by_tree( $gedcom, $args = array() ) {
		global $wpdb;

		$sql    = 'SELECT COUNT(*) FROM %i WHERE gedcom = %s';
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Per-status counts for a tree (queue facet totals), optionally filtered
	 * by the same args as get_by_tree() (status itself is ignored).
	 *
	 * @param  string $gedcom Tree identifier.
	 * @param  array  $args   Filters.
	 * @return array status => count.
	 */
	public static function counts_by_status( $gedcom, $args = array() ) {
		global $wpdb;
		unset( $args['status'] );

		$sql    = 'SELECT status, COUNT(*) AS n FROM %i WHERE gedcom = %s';
		$params = array( self::table(), $gedcom );
		self::append_filters( $args, $sql, $params );
		$sql .= ' GROUP BY status';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the whole query is ONE prepare(): identifier via %i, every value placeholder-bound. NotPrepared is a false positive on a variable first argument.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
		$out  = array();
		foreach ( (array) $rows as $r ) {
			$out[ $r->status ] = (int) $r->n;
		}
		return $out;
	}

	/**
	 * Insert a candidate. candidateID is generated ('C' + n) when empty;
	 * subject_ids and evidence may be passed as arrays (JSON-encoded here).
	 *
	 * @param  array $data Field => value pairs (incl. gedcom).
	 * @return string|false The candidateID, or false.
	 */
	public static function insert( $data ) {
		global $wpdb;
		if ( empty( $data['gedcom'] ) ) {
			return false;
		}
		if ( empty( $data['candidateID'] ) ) {
			$data['candidateID'] = self::next_ref( $data['gedcom'] );
		}
		foreach ( array( 'subject_ids', 'evidence' ) as $json_col ) {
			if ( isset( $data[ $json_col ] ) && is_array( $data[ $json_col ] ) ) {
				$data[ $json_col ] = wp_json_encode( $data[ $json_col ] );
			}
		}

		$now  = Tables::now();
		$data = array_merge(
			array(
				'status'             => 'open',
				'origin'             => 'engine',
				'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['candidateID'] : false;
	}

	/**
	 * Update a candidate by tree + candidateID (array JSON columns encoded).
	 *
	 * @param  string $gedcom      Tree identifier.
	 * @param  string $candidateID Candidate key.
	 * @param  array  $data        Field => value pairs.
	 * @return int|false
	 */
	public static function update_by_ref( $gedcom, $candidateID, $data ) {
		global $wpdb;
		foreach ( array( 'subject_ids', 'evidence' ) as $json_col ) {
			if ( isset( $data[ $json_col ] ) && is_array( $data[ $json_col ] ) ) {
				$data[ $json_col ] = wp_json_encode( $data[ $json_col ] );
			}
		}
		$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,
				'candidateID' => $candidateID,
			)
		);
	}

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

	/**
	 * Delete every candidate 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 JSON columns of a row in place.
	 *
	 * @param  object $row Raw row.
	 * @return object Row with subject_ids/evidence as arrays.
	 */
	private static function decode( $row ) {
		$row->subject_ids = empty( $row->subject_ids ) ? array() : (array) json_decode( $row->subject_ids, true );
		$row->evidence    = empty( $row->evidence ) ? array() : (array) json_decode( $row->evidence, true );
		return $row;
	}

	/**
	 * Next sequential candidateID 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(candidateID, 2) AS UNSIGNED)) FROM %i WHERE gedcom = %s AND candidateID REGEXP '^C[0-9]+$'", self::table(), $gedcom ) );
		return 'C' . ( $max + 1 );
	}
}
