<?php
/**
 * AP Family Trees - Database Layer Base
 *
 * Central point for all table name constants and shared $wpdb utilities.
 * Every table class extends or uses this base.
 *
 * Table prefix: {wp_prefix}apft_
 * All table names are constructed at runtime to support multisite.
 *
 * @package    APFT_Family_Trees
 * @subpackage APFT_Family_Trees/includes/db
 * @since      2.0.0
 */

namespace AncestorPress\Core;

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

/**
 * Database base class.
 *
 * Provides table name accessors and shared query utilities for all
 * AP Family Trees data access classes.
 *
 * @since 2.0.0
 */
class Tables {

	/**
	 * Plugin table name prefix (appended to the WordPress prefix).
	 *
	 * @since 2.0.0
	 * @var string
	 */
	const PLUGIN_PREFIX = 'apft_';

	/**
	 * Current schema version.
	 * Increment when the schema changes to trigger dbDelta on upgrade.
	 *
	 * @since 2.0.0
	 * @var string
	 */
	const SCHEMA_VERSION = '0.6.9';

	/**
	 * Option key storing the installed schema version.
	 *
	 * @since 2.0.0
	 * @var string
	 */
	const SCHEMA_VERSION_OPTION = 'apft_ft_db_version';

	/**
	 * Resolve a core table's physical name via the ANCP Core table map.
	 *
	 * The core-table accessors below delegate here so the apft_ -> ancp_ rename
	 * (spec-01 item 3) is a single change in Table_Map. Falls back to the
	 * legacy apft_ construction when the core has not booted yet - an identical
	 * result today - so pre-boot calls stay correct.
	 *
	 * @since  0.6.10
	 * @param  string $key Logical core table key.
	 * @return string      Fully qualified table name.
	 */
	private static function core_name( $key ) {
		if ( class_exists( 'AncestorPress\Core\Table_Map' ) ) {
			return Table_Map::name( $key );
		}
		global $wpdb;
		return $wpdb->prefix . self::PLUGIN_PREFIX . $key;
	}

	// ── Table name accessors ──────────────────────────────────────────────────

	/**
	 * Return the fully qualified trees table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function trees() {
		return self::core_name( 'trees' );
	}

	/**
	 * Return the fully qualified people table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function people() {
		return self::core_name( 'people' );
	}

	/**
	 * Return the fully qualified names table name.
	 *
	 * @since  0.3.0
	 * @return string
	 */
	public static function names() {
		return self::core_name( 'names' );
	}

	/**
	 * Return the fully qualified families table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function families() {
		return self::core_name( 'families' );
	}

	/**
	 * Return the fully qualified children table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function children() {
		return self::core_name( 'children' );
	}

	/**
	 * Return the fully qualified events table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function events() {
		return self::core_name( 'events' );
	}

	/**
	 * Return the fully qualified event types table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function event_types() {
		return self::core_name( 'event_types' );
	}

	/**
	 * Return the fully qualified places table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function places() {
		return self::core_name( 'places' );
	}

	/**
	 * Return the fully qualified notes table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function notes() {
		return self::core_name( 'notes' );
	}

	/**
	 * Return the fully qualified note links table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function note_links() {
		return self::core_name( 'note_links' );
	}

	/**
	 * Return the fully qualified profile images table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function profile_images() {
		return self::core_name( 'profile_images' );
	}

	/**
	 * Return the fully qualified media table name.
	 *
	 * @since  0.3.0
	 * @return string
	 */
	public static function media() {
		return self::core_name( 'media' );
	}

	/**
	 * Return the fully qualified media links table name.
	 *
	 * @since  0.3.0
	 * @return string
	 */
	public static function media_links() {
		return self::core_name( 'medialinks' );
	}

	/**
	 * Return the fully qualified occurrences table name.
	 *
	 * @since  0.3.0
	 * @return string
	 */
	public static function occurrences() {
		return self::core_name( 'occurrences' );
	}

	/**
	 * Return the fully qualified record sets table name.
	 *
	 * @since  0.12.0
	 * @return string
	 */
	public static function record_sets() {
		return self::core_name( 'record_sets' );
	}

	/**
	 * Return the fully qualified candidates table name.
	 *
	 * @since  0.12.0
	 * @return string
	 */
	public static function candidates() {
		return self::core_name( 'candidates' );
	}

	/**
	 * Return the fully qualified judgements table name.
	 *
	 * @since  0.12.0
	 * @return string
	 */
	public static function judgements() {
		return self::core_name( 'judgements' );
	}

	/**
	 * Return the fully qualified interests table name.
	 *
	 * @since  0.12.0
	 * @return string
	 */
	public static function interests() {
		return self::core_name( 'interests' );
	}

	/**
	 * Return the fully qualified import maps table name.
	 *
	 * @since  0.12.0
	 * @return string
	 */
	public static function import_maps() {
		return self::core_name( 'import_maps' );
	}

	/**
	 * Return the fully qualified repositories table name.
	 *
	 * @since  0.7.0
	 * @return string
	 */
	public static function repositories() {
		return self::core_name( 'repositories' );
	}

	/**
	 * Return the fully qualified sources table name.
	 *
	 * @since  0.7.0
	 * @return string
	 */
	public static function sources() {
		return self::core_name( 'sources' );
	}

	/**
	 * Return the fully qualified citations table name.
	 *
	 * @since  0.7.0
	 * @return string
	 */
	public static function citations() {
		return self::core_name( 'citations' );
	}

	/**
	 * Return the fully qualified branches table name.
	 *
	 * @since  0.11.0
	 * @return string
	 */
	public static function branches() {
		return self::core_name( 'branches' );
	}

	/**
	 * Return the fully qualified branch-links table name.
	 *
	 * @since  0.11.0
	 * @return string
	 */
	public static function branch_links() {
		return self::core_name( 'branchlinks' );
	}

	/**
	 * Return the fully qualified charts table name.
	 *
	 * @since  2.0.0
	 * @return string
	 */
	public static function charts() {
		global $wpdb;
		return $wpdb->prefix . self::PLUGIN_PREFIX . 'charts';
	}

	/**
	 * Return the fully qualified custom field definitions table name.
	 *
	 * @since  0.6.9
	 * @return string
	 */
	public static function field_defs() {
		return self::core_name( 'field_defs' );
	}

	/**
	 * Return the fully qualified custom field values table name.
	 *
	 * @since  0.6.9
	 * @return string
	 */
	public static function field_values() {
		return self::core_name( 'field_values' );
	}

	// ── Utility methods ───────────────────────────────────────────────────────

	/**
	 * Check whether the plugin tables have been installed.
	 *
	 * @since  2.0.0
	 * @return bool True if the people table exists (proxy for all tables).
	 */
	public static function tables_exist() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->get_var(
			$wpdb->prepare( 'SHOW TABLES LIKE %s', self::people() )
		);
		return ! empty( $result );
	}

	/**
	 * Check whether the installed schema version matches the current version.
	 *
	 * @since  2.0.0
	 * @return bool
	 */
	public static function schema_is_current() {
		// The DB-version option is stamped with APFT_VERSION (see the activator
		// and the plugins_loaded upgrade check), not SCHEMA_VERSION.
		return get_option( self::SCHEMA_VERSION_OPTION ) === APFT_VERSION;
	}

	/**
	 * Normalise a GEDCOM date string to a MySQL DATE value.
	 *
	 * GEDCOM dates may include qualifiers (ABT, BEF, AFT, BET, EST) and
	 * partial dates (year only, month+year). This method extracts the most
	 * specific date it can find and returns a MySQL-compatible DATE string,
	 * or null if the date cannot be parsed.
	 *
	 * @since  2.0.0
	 * @param  string $gedcom_date Raw GEDCOM date string.
	 * @return string|null         MySQL DATE (YYYY-MM-DD) or null.
	 */
	public static function normalise_date( $gedcom_date ) {
		if ( empty( $gedcom_date ) ) {
			return null;
		}

		// Strip GEDCOM qualifiers.
		$clean = preg_replace( '/\b(ABT|ABOUT|CAL|EST|BEF|BEFORE|AFT|AFTER|FROM|TO|INT)\b/i', '', $gedcom_date );

		// Handle BET ... AND ... - take the first date.
		if ( preg_match( '/BET\s+(.+?)\s+AND\s+/i', $gedcom_date, $m ) ) {
			$clean = $m[1];
		}

		$clean = trim( $clean );

		// Month name map.
		$months = array(
			'JAN' => '01', 'FEB' => '02', 'MAR' => '03', 'APR' => '04',
			'MAY' => '05', 'JUN' => '06', 'JUL' => '07', 'AUG' => '08',
			'SEP' => '09', 'OCT' => '10', 'NOV' => '11', 'DEC' => '12',
		);

		// Full date: DD MON YYYY.
		if ( preg_match( '/^(\d{1,2})\s+([A-Z]{3})\s+(\d{4})$/i', $clean, $m ) ) {
			$month = strtoupper( $m[2] );
			if ( isset( $months[ $month ] ) ) {
				return sprintf( '%04d-%02d-%02d', (int) $m[3], (int) $months[ $month ], (int) $m[1] );
			}
		}

		// Month and year: MON YYYY.
		if ( preg_match( '/^([A-Z]{3})\s+(\d{4})$/i', $clean, $m ) ) {
			$month = strtoupper( $m[1] );
			if ( isset( $months[ $month ] ) ) {
				return sprintf( '%04d-%02d-01', (int) $m[2], (int) $months[ $month ] );
			}
		}

		// Year only: YYYY.
		if ( preg_match( '/^(\d{4})$/', $clean, $m ) ) {
			return sprintf( '%04d-01-01', (int) $m[1] );
		}

		return null;
	}

	/**
	 * Return the current UTC datetime string for created_at / updated_at.
	 *
	 * @since  2.0.0
	 * @return string MySQL DATETIME string.
	 */
	public static function now() {
		return gmdate( 'Y-m-d H:i:s' );
	}
}
