<?php
/**
 * @package Hafenstudios\Kurato
 */

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Tags (hs_tags, hs_tag_rel). Freie Etiketten fuer Kontakte (BAUPLAN 13).
 *
 * Nicht final, damit Tests die DB-Methoden per Test-Double ueberschreiben koennen.
 */
class TagRepository {

	/**
	 * Alle Tags, alphabetisch.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function all(): array {
		global $wpdb;
		$table = Schema::table( 'tags' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( "SELECT * FROM {$table} ORDER BY label ASC", ARRAY_A );
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Stellt einen Tag sicher (per Label) und liefert seine ID.
	 *
	 * @param string $label Anzeigename.
	 * @return int 0, wenn das Label leer ist.
	 */
	public function ensure( string $label ): int {
		$label = trim( sanitize_text_field( $label ) );
		if ( '' === $label ) {
			return 0;
		}
		$slug = sanitize_title( $label );

		global $wpdb;
		$table = Schema::table( 'tags' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$existing = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE slug = %s", $slug ) );
		if ( $existing > 0 ) {
			return $existing;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert(
			$table,
			array(
				'slug'       => $slug,
				'label'      => $label,
				'created_at' => current_time( 'mysql', true ),
			)
		);

		if ( false === $ok ) {
			// Race: ein paralleler Request hat den Slug bereits angelegt (UNIQUE).
			// insert_id waere hier irrefuehrend, daher erneut per Slug selektieren.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE slug = %s", $slug ) );
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Verknuepft einen Tag mit einem Kontakt (idempotent).
	 *
	 * @param int $contact_id Kontakt-ID.
	 * @param int $tag_id     Tag-ID.
	 * @return void
	 */
	public function attach( int $contact_id, int $tag_id ): void {
		if ( $contact_id <= 0 || $tag_id <= 0 ) {
			return;
		}
		global $wpdb;
		// replace() ist idempotent bei zusammengesetztem Primaerschluessel.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->replace(
			Schema::table( 'tag_rel' ),
			array(
				'tag_id'     => $tag_id,
				'contact_id' => $contact_id,
			)
		);
	}

	/**
	 * Loest die Verknuepfung zwischen Tag und Kontakt.
	 *
	 * @param int $contact_id Kontakt-ID.
	 * @param int $tag_id     Tag-ID.
	 * @return void
	 */
	public function detach( int $contact_id, int $tag_id ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			Schema::table( 'tag_rel' ),
			array(
				'tag_id'     => $tag_id,
				'contact_id' => $contact_id,
			)
		);
	}

	/**
	 * Tags eines Kontakts.
	 *
	 * @param int $contact_id Kontakt-ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function tags_for( int $contact_id ): array {
		global $wpdb;
		$tags = Schema::table( 'tags' );
		$rel  = Schema::table( 'tag_rel' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT t.* FROM {$tags} t INNER JOIN {$rel} r ON r.tag_id = t.id WHERE r.contact_id = %d ORDER BY t.label ASC",
				$contact_id
			),
			ARRAY_A
		);
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Normalisiert eine Zeile.
	 *
	 * @param array<string, mixed> $row Rohzeile.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		return array(
			'id'    => (int) $row['id'],
			'slug'  => (string) $row['slug'],
			'label' => (string) $row['label'],
		);
	}
}
