<?php
/**
 * AP Family Trees - Field Definitions Table
 *
 * CRUD for apft_field_defs: custom field schemas defined per tree.
 *
 * @package    APFT_Family_Trees
 * @subpackage APFT_Family_Trees/includes/db
 * @since      0.6.9
 */

namespace AncestorPress\Core;

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

/**
 * Data access for the apft_field_defs table.
 *
 * @since 0.6.9
 */
class Field_Defs_Table {

	/**
	 * Allowed field types.
	 *
	 * @since 0.6.9
	 * @var array
	 */
	const FIELD_TYPES = array( 'text', 'textarea', 'date', 'url', 'select' );

	/**
	 * Return the fully qualified table name.
	 *
	 * @since  0.6.9
	 * @return string
	 */
	public static function table() {
		return Tables::field_defs();
	}

	/**
	 * Return all field definitions for a tree, ordered by ordernum.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom Tree identifier.
	 * @return array
	 */
	public static function get_by_tree( $gedcom ) {
		global $wpdb;
		$t = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM %i WHERE gedcom = %s ORDER BY ordernum ASC, id ASC", $t, $gedcom )
		);
		// phpcs:enable
	}

	/**
	 * Return a single field definition by tree + key.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom    Tree identifier.
	 * @param  string $field_key Field key slug.
	 * @return object|null
	 */
	public static function get_by_key( $gedcom, $field_key ) {
		global $wpdb;
		$t = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM %i WHERE gedcom = %s AND field_key = %s", $t, $gedcom, $field_key )
		);
		// phpcs:enable
	}

	/**
	 * Insert a new field definition.
	 *
	 * @since  0.6.9
	 * @param  array $data Associative array: gedcom, field_key, field_label, field_type, field_options, ordernum.
	 * @return int|false   Inserted row ID or false on failure.
	 */
	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
		$result = $wpdb->insert( self::table(), $data );
		return $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Update an existing field definition by id.
	 *
	 * @since  0.6.9
	 * @param  int   $id   Row ID.
	 * @param  array $data Fields to update.
	 * @return bool
	 */
	public static function update( $id, $data ) {
		global $wpdb;
		$data['updated_at'] = Tables::now();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->update( self::table(), $data, array( 'id' => (int) $id ) );
	}

	/**
	 * Delete a field definition by id.
	 * Also deletes all stored values for this field across the tree.
	 *
	 * @since  0.6.9
	 * @param  int    $id     Row ID.
	 * @param  string $gedcom Tree identifier (for cascading value deletion).
	 * @param  string $field_key Field key (for cascading value deletion).
	 * @return bool
	 */
	public static function delete( $id, $gedcom, $field_key ) {
		global $wpdb;
		// Cascade-delete all stored values for this field.
		Field_Values_Table::delete_by_field( $gedcom, $field_key );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( self::table(), array( 'id' => (int) $id ) );
	}

	/**
	 * Return the highest ordernum for a given tree (used when appending a new field).
	 *
	 * @since  0.6.9
	 * @param  string $gedcom Tree identifier.
	 * @return int
	 */
	public static function max_ordernum( $gedcom ) {
		global $wpdb;
		$t = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$max = $wpdb->get_var(
			$wpdb->prepare( "SELECT MAX(ordernum) FROM %i WHERE gedcom = %s", $t, $gedcom )
		);
		// phpcs:enable
		return (int) $max;
	}

	/**
	 * Delete all field definitions for a tree (called when a tree is deleted).
	 *
	 * @since  0.6.9
	 * @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
		$wpdb->delete( self::table(), array( 'gedcom' => $gedcom ) );
	}

	/**
	 * Generate a unique field_key slug from a label within a tree.
	 *
	 * @since  0.6.9
	 * @param  string $gedcom Tree identifier.
	 * @param  string $label  Human-readable label.
	 * @return string         Unique slug (e.g. 'military_service', 'military_service_2').
	 */
	public static function make_key( $gedcom, $label ) {
		$base = strtolower( preg_replace( '/[^a-zA-Z0-9]+/', '_', trim( $label ) ) );
		$base = trim( $base, '_' );
		$base = $base ?: 'field';
		$base = substr( $base, 0, 80 );

		$key      = $base;
		$existing = self::get_by_key( $gedcom, $key );
		$i        = 2;
		while ( $existing ) {
			$key      = $base . '_' . $i++;
			$existing = self::get_by_key( $gedcom, $key );
		}
		return $key;
	}
}
