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

namespace AncestorPress\Core;

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

class Profile_Images_Table {

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

	public static function get_primary( $gedcom, $personID ) {
		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 personID = %s ORDER BY sort_order ASC, id ASC LIMIT 1', self::table(), $gedcom, $personID ) );
	}

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

	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 );
		if ( ! $result ) {
			return false;
		}
		// Capture id before the media dual-write (which INSERTs and would
		// otherwise overwrite $wpdb->insert_id).
		$new_id = (int) $wpdb->insert_id;
		self::sync_media( $data['gedcom'] ?? '', $data['personID'] ?? '' );
		return $new_id;
	}

	/**
	 * Dual-write a person's profile photos into the media/medialinks tables
	 * (spec-01 item 4 transition). No-op if the media layer is unavailable.
	 *
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID GEDCOM person id.
	 * @return void
	 */
	private static function sync_media( $gedcom, $personID ) {
		if ( '' !== (string) $gedcom && '' !== (string) $personID && class_exists( 'AncestorPress\Core\Media' ) ) {
			Media::sync_person( $gedcom, $personID );
		}
	}

	/**
	 * Look up the (gedcom, personID) for a profile image row id.
	 *
	 * @param  int $id Row id.
	 * @return array{0:string,1:string} [gedcom, personID] or ['',''].
	 */
	private static function person_of( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT gedcom, personID FROM %i WHERE id = %d', self::table(), absint( $id ) ) );
		return $row ? array( $row->gedcom, $row->personID ) : array( '', '' );
	}

	/**
	 * Insert or update a profile image URL for a specific person.
	 *
	 * If a row exists for this gedcom + personID combination, the URL is
	 * updated. If no row exists, a new record is inserted.
	 *
	 * @since  2.0.0
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID GEDCOM person reference.
	 * @param  string $url      Full image URL from the WordPress Media Library.
	 * @return int|false        Row ID on insert, rows-affected on update, false on failure.
	 */
	public static function upsert( $gedcom, $personID, $url ) {
		$existing = self::get_primary( $gedcom, $personID );

		if ( $existing ) {
			global $wpdb;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- targeted update by primary key.
			$result = $wpdb->update(
				self::table(),
				array(
					'url'        => esc_url_raw( $url ),
					'updated_at' => Tables::now(),
				),
				array( 'id' => (int) $existing->id )
			);
			self::sync_media( $gedcom, $personID );
			return $result;
		}

		return self::insert(
			array(
				'gedcom'   => $gedcom,
				'personID' => $personID,
				'url'      => esc_url_raw( $url ),
			)
		);
	}

	/**
	 * Update URL and caption for an existing image record.
	 *
	 * @param  int    $id      Row ID.
	 * @param  string $url     Image URL.
	 * @param  string $caption Caption text.
	 * @return int|false
	 */
	public static function update_item( $id, $url, $caption, $sort_order = 0 ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- targeted update by primary key.
		$result = $wpdb->update(
			self::table(),
			array(
				'url'        => esc_url_raw( $url ),
				'caption'    => sanitize_text_field( $caption ),
				'sort_order' => absint( $sort_order ),
				'updated_at' => Tables::now(),
			),
			array( 'id' => absint( $id ) )
		);
		list( $g, $p ) = self::person_of( $id );
		self::sync_media( $g, $p );
		return $result;
	}

	/**
	 * Delete a single image record by its ID.
	 *
	 * @param  int $id Row ID.
	 * @return int|false
	 */
	public static function delete_by_id( $id ) {
		global $wpdb;
		list( $g, $p ) = self::person_of( $id ); // resolve before the row is gone
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- targeted delete by primary key.
		$result = $wpdb->delete( self::table(), array( 'id' => absint( $id ) ) );
		self::sync_media( $g, $p );
		return $result;
	}

	/**
	 * Delete all profile image records for a specific person.
	 *
	 * @since  2.0.0
	 * @param  string $gedcom   Tree identifier.
	 * @param  string $personID GEDCOM person reference.
	 * @return int|false        Number of rows deleted, or false on failure.
	 */
	public static function delete_for_person( $gedcom, $personID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- targeted delete by gedcom + personID.
		$result = $wpdb->delete(
			self::table(),
			array(
				'gedcom'   => $gedcom,
				'personID' => $personID,
			)
		);
		self::sync_media( $gedcom, $personID ); // no images left -> clears media
		return $result;
	}

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