<?php
/**
 * AP Family Trees - Places Table
 *
 * @package    APFT_Family_Trees
 * @subpackage APFT_Family_Trees/includes/db
 * @since      2.0.0
 */

namespace AncestorPress\Core;

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

class Places_Table {

	public static function table() {
		return Tables::places();
	}

	public static function get_by_tree( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s ORDER BY place ASC', self::table(), $gedcom ) );
	}

	/**
	 * Fetch one place by row id.
	 *
	 * @since  0.5.0
	 * @param  int $id Row id.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), absint( $id ) ) );
	}

	/**
	 * Set (or clear) a place's Chapman county code.
	 *
	 * The county assignment is a STANDARDISED value on the place authority
	 * (spec-00 Section 3): user-correctable, never derived onto the verbatim
	 * place strings occurrences carry.
	 *
	 * @since  0.5.0
	 * @param  int    $id      Place row id.
	 * @param  string $chapman Chapman code (e.g. 'GLS'), or '' to clear.
	 * @return int|false
	 */
	public static function set_chapman( $id, $chapman ) {
		global $wpdb;
		$chapman = strtoupper( substr( preg_replace( '/[^A-Za-z]/', '', (string) $chapman ), 0, 10 ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->update(
			self::table(),
			array(
				'chapman'    => ( '' === $chapman ) ? null : $chapman,
				'updated_at' => Tables::now(),
			),
			array( 'id' => absint( $id ) )
		);
	}

	/**
	 * Update a place's descriptive metadata (additive API, core 0.12.0).
	 *
	 * Whitelisted fields only: longitude, latitude, zoom, placelevel,
	 * temple, geoignore, notes, and (0.16.0, the OPS hierarchy fields already
	 * in the 0.12.0 schema) parent_id, place_type, names_observed,
	 * boundary_notes, external_map_url. The place STRING and gedcom are
	 * immutable here (renames belong to the future place-merge utility -
	 * CLAUDE.md backlog), and the county assignment has its own setter
	 * (set_chapman). Values are typed and clamped; invalid
	 * values skip that field rather than failing the call (importer-safe).
	 *
	 * @since  0.12.0
	 * @param  int   $id   Place row id.
	 * @param  array $data Field => value pairs (unknown keys ignored).
	 * @return int|false Rows updated, false on error, 0 when nothing to set.
	 */
	public static function update_meta( $id, $data ) {
		global $wpdb;
		$data   = (array) $data;
		$update = array();
		foreach ( array( 'longitude', 'latitude' ) as $coord ) {
			if ( array_key_exists( $coord, $data ) ) {
				$value = trim( (string) $data[ $coord ] );
				if ( '' === $value ) {
					$update[ $coord ] = null;
				} elseif ( is_numeric( $value ) ) {
					$range = 'latitude' === $coord ? 90 : 180;
					if ( abs( (float) $value ) <= $range ) {
						$update[ $coord ] = (float) $value;
					}
				}
			}
		}
		foreach ( array( 'zoom', 'placelevel' ) as $tiny ) {
			if ( array_key_exists( $tiny, $data ) ) {
				$value = trim( (string) $data[ $tiny ] );
				if ( '' === $value ) {
					$update[ $tiny ] = null;
				} elseif ( is_numeric( $value ) ) {
					$update[ $tiny ] = max( 0, min( 127, (int) $value ) );
				}
			}
		}
		foreach ( array( 'temple', 'geoignore' ) as $flag ) {
			if ( array_key_exists( $flag, $data ) ) {
				$update[ $flag ] = empty( $data[ $flag ] ) ? 0 : 1;
			}
		}
		if ( array_key_exists( 'notes', $data ) ) {
			$update['notes'] = null === $data['notes'] ? null : (string) $data['notes'];
		}
		// Hierarchy + descriptive fields (0.12.0 schema, API-reachable 0.16.0).
		if ( array_key_exists( 'parent_id', $data ) ) {
			// A place cannot parent itself; invalid values skip the field.
			$parent = absint( $data['parent_id'] );
			if ( 0 === $parent ) {
				$update['parent_id'] = null;
			} elseif ( $parent !== absint( $id ) ) {
				$update['parent_id'] = $parent;
			}
		}
		if ( array_key_exists( 'place_type', $data ) ) {
			$type                 = sanitize_key( (string) $data['place_type'] );
			$update['place_type'] = ( '' === $type ) ? null : substr( $type, 0, 30 );
		}
		if ( array_key_exists( 'names_observed', $data ) ) {
			// Stored as a JSON array of recorded spellings; arrays are encoded
			// here, strings must already be valid JSON arrays or the field skips.
			$names = $data['names_observed'];
			if ( null === $names || array() === $names || '' === $names ) {
				$update['names_observed'] = null;
			} elseif ( is_array( $names ) ) {
				$update['names_observed'] = wp_json_encode( array_values( array_map( 'strval', $names ) ) );
			} elseif ( is_string( $names ) && is_array( json_decode( $names, true ) ) ) {
				$update['names_observed'] = $names;
			}
		}
		if ( array_key_exists( 'boundary_notes', $data ) ) {
			$update['boundary_notes'] = null === $data['boundary_notes'] ? null : (string) $data['boundary_notes'];
		}
		if ( array_key_exists( 'external_map_url', $data ) ) {
			$url                        = esc_url_raw( trim( (string) $data['external_map_url'] ) );
			$update['external_map_url'] = ( '' === $url ) ? null : $url;
		}
		if ( empty( $update ) ) {
			return 0;
		}
		$update['updated_at'] = Tables::now();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->update( self::table(), $update, array( 'id' => absint( $id ) ) );
	}

	/**
	 * Direct children of a place in the hierarchy (study place > street/area
	 * > property - OPS spec v0.2 Section 3.2). Pass 0/null for top-level rows
	 * (no parent) in the tree.
	 *
	 * @since  0.16.0
	 * @param  string   $gedcom    Tree identifier.
	 * @param  int|null $parent_id Parent place row id, or 0/null for roots.
	 * @return array
	 */
	public static function get_children( $gedcom, $parent_id ) {
		global $wpdb;
		$parent_id = absint( $parent_id );
		if ( 0 === $parent_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
			return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND parent_id IS NULL ORDER BY place ASC', self::table(), $gedcom ) );
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND parent_id = %d ORDER BY place ASC', self::table(), $gedcom, $parent_id ) );
	}

	/**
	 * All places of one type in a tree (parish, street, property, ...).
	 *
	 * @since  0.16.0
	 * @param  string $gedcom     Tree identifier.
	 * @param  string $place_type Place type key.
	 * @return array
	 */
	public static function get_by_type( $gedcom, $place_type ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND place_type = %s ORDER BY place ASC', self::table(), $gedcom, sanitize_key( $place_type ) ) );
	}

	public static function get_or_insert( $gedcom, $place_string ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$existing = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE gedcom = %s AND place = %s LIMIT 1', self::table(), $gedcom, $place_string ) );
		if ( $existing ) {
			return (int) $existing;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert( self::table(), array(
			'gedcom'             => $gedcom,
			'place'              => $place_string,
			'created_by_user_id' => get_current_user_id(),
			'created_at'         => Tables::now(),
			'updated_at'         => Tables::now(),
		) );
		return (int) $wpdb->insert_id;
	}

	public static function delete_by_gedcom( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->delete( self::table(), array( 'gedcom' => $gedcom ) );
	}
}
