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

namespace AncestorPress\Core;

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

class Families_Table {

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

	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 ) ) );
	}

	public static function get_by_ref( $gedcom, $familyID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND familyID = %s LIMIT 1', self::table(), $gedcom, $familyID ) );
	}

	public static function get_id_by_ref( $gedcom, $familyID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$r = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE gedcom = %s AND familyID = %s LIMIT 1', self::table(), $gedcom, $familyID ) );
		return $r ? (int) $r : null;
	}

	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', self::table(), $gedcom ) );
	}

	public static function get_for_spouse( $gedcom, $personID ) {
		global $wpdb;
  return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE gedcom = %s AND ( husband = %s OR wife = %s )', self::table(), $gedcom, $personID, $personID ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	public static function count_by_tree( $gedcom ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE gedcom = %s', self::table(), $gedcom ) );
	}

	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, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->insert( self::table(), $data );
		return $result ? (int) $wpdb->insert_id : false;
	}

	public static function update( $id, $data ) {
		global $wpdb;
		$data['updated_at'] = Tables::now();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->update( self::table(), $data, array( 'id' => absint( $id ) ) );
	}

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

	/**
	 * Delete a family and its dependent rows: child links, family-level events
	 * and note links. Prevents orphaned rows pointing at a deleted familyID.
	 *
	 * @param  int $id Family row ID.
	 * @return int|false Rows deleted from the families table, or false if not found.
	 */
	public static function delete_cascade( $id ) {
		$fam = self::get( $id );
		if ( ! $fam ) {
			return false;
		}
		Children_Table::delete_by_family( $fam->gedcom, $fam->familyID );
		Events_Table::delete_by_persfam( $fam->gedcom, $fam->familyID );
		Note_Links_Table::delete_by_persfam( $fam->gedcom, $fam->familyID );
		return self::delete( $id );
	}

	/**
	 * Detach a person from every family that records them as a spouse, clearing
	 * the husband/wife reference. Used when a person is deleted so no family row
	 * points at a nonexistent personID.
	 *
	 * @param  string $gedcom   Tree slug.
	 * @param  string $personID Person GEDCOM ID.
	 * @return void
	 */
	public static function detach_person( $gedcom, $personID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update( self::table(), array( 'husband' => '' ), array( 'gedcom' => $gedcom, 'husband' => $personID ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update( self::table(), array( 'wife' => '' ), array( 'gedcom' => $gedcom, 'wife' => $personID ) );
	}

	/**
	 * Generate the next available manual family ID for a tree.
	 * Manual IDs use the prefix MAN- to avoid collision with GEDCOM IDs.
	 *
	 * @param  string $gedcom Tree slug.
	 * @return string         e.g. "MAN-1", "MAN-2"
	 */
	public static function next_manual_id( $gedcom ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- querying own table; table name cannot use %s placeholder; LIKE prefix is a trusted literal.
		$rows = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT familyID FROM %i WHERE gedcom = %s AND familyID LIKE %s', self::table(),
				$gedcom,
				$wpdb->esc_like( 'MAN-' ) . '%'
			)
		);
		// phpcs:enable
		$max = 0;
		foreach ( $rows as $fid ) {
			$n = (int) substr( $fid, 4 );
			if ( $n > $max ) {
				$max = $n;
			}
		}
		return 'MAN-' . ( $max + 1 );
	}
}
