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

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Verteilerlisten (hs_lists). Eine Liste buendelt Abonnenten mit Einwilligung
 * (Double-Opt-in per Default). Marketing-Mails gehen nur an bestaetigte Abos.
 *
 * Nicht final, damit Tests die DB-Methoden per Test-Double ueberschreiben koennen.
 */
class ListRepository {

	public const DEFAULT_SLUG = 'newsletter';

	/**
	 * Alle Listen, alphabetisch.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function all(): array {
		global $wpdb;
		$table = Schema::table( 'lists' );
		// 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() );
	}

	/**
	 * Eine Liste.
	 *
	 * @param int $id Listen-ID.
	 * @return array<string, mixed>|null
	 */
	public function get( int $id ): ?array {
		global $wpdb;
		$table = Schema::table( 'lists' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ), ARRAY_A );
		return $row ? $this->hydrate( $row ) : null;
	}

	/**
	 * Legt eine Liste an.
	 *
	 * @param string $label        Anzeigename.
	 * @param string $description   Beschreibung.
	 * @param bool   $doi_required  Double-Opt-in verpflichtend?
	 * @return int Listen-ID (0 bei Fehler).
	 */
	public function create( string $label, string $description = '', bool $doi_required = true ): int {
		$label = trim( sanitize_text_field( $label ) );
		if ( '' === $label ) {
			return 0;
		}
		$slug = $this->unique_slug( sanitize_title( $label ) );

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert(
			Schema::table( 'lists' ),
			array(
				'slug'         => $slug,
				'label'        => $label,
				'description'  => sanitize_textarea_field( $description ),
				'doi_required' => $doi_required ? 1 : 0,
				'created_at'   => current_time( 'mysql', true ),
			)
		);
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Aktualisiert Label/Beschreibung/DOI-Pflicht einer Liste.
	 *
	 * @param int                  $id   Listen-ID.
	 * @param array<string, mixed> $data Zu aendernde Felder.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		$row = array();
		if ( array_key_exists( 'label', $data ) ) {
			$row['label'] = sanitize_text_field( (string) $data['label'] );
		}
		if ( array_key_exists( 'description', $data ) ) {
			$row['description'] = sanitize_textarea_field( (string) $data['description'] );
		}
		if ( array_key_exists( 'doi_required', $data ) ) {
			$row['doi_required'] = ! empty( $data['doi_required'] ) ? 1 : 0;
		}
		if ( array() === $row ) {
			return false;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return false !== $wpdb->update( Schema::table( 'lists' ), $row, array( 'id' => $id ) );
	}

	/**
	 * Loescht eine Liste samt ihrer Abos.
	 *
	 * @param int $id Listen-ID.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( Schema::table( 'subscriptions' ), array( 'list_id' => $id ) );
		$deleted = $wpdb->delete( Schema::table( 'lists' ), array( 'id' => $id ) );
		// phpcs:enable
		return (bool) $deleted;
	}

	/**
	 * Liefert die Default-Newsletter-Liste und legt sie bei Bedarf an.
	 *
	 * @return int Listen-ID.
	 */
	public function ensure_default(): int {
		global $wpdb;
		$table = Schema::table( 'lists' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE slug = %s", self::DEFAULT_SLUG ) );
		if ( $id > 0 ) {
			return $id;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert(
			$table,
			array(
				'slug'         => self::DEFAULT_SLUG,
				'label'        => __( 'Newsletter', 'kurato' ),
				'description'  => '',
				'doi_required' => 1,
				'created_at'   => current_time( 'mysql', true ),
			)
		);
		return (int) $wpdb->insert_id;
	}

	/**
	 * Zahl der Abonnenten einer Liste in einem Status.
	 *
	 * @param int    $list_id Listen-ID.
	 * @param string $status  Status (Default confirmed).
	 * @return int
	 */
	public function subscriber_count( int $list_id, string $status = 'confirmed' ): int {
		global $wpdb;
		$table = Schema::table( 'subscriptions' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE list_id = %d AND status = %s", $list_id, $status ) );
	}

	/**
	 * Erzeugt einen eindeutigen Slug (haengt bei Kollision -2, -3 an).
	 *
	 * @param string $slug Basis-Slug.
	 * @return string
	 */
	private function unique_slug( string $slug ): string {
		global $wpdb;
		$table = Schema::table( 'lists' );
		$base  = '' !== $slug ? $slug : 'liste';
		$try   = $base;
		$n     = 1;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		while ( (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE slug = %s", $try ) ) > 0 ) {
			$n++;
			$try = $base . '-' . $n;
		}
		// phpcs:enable
		return $try;
	}

	/**
	 * 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'],
			'description'  => (string) ( $row['description'] ?? '' ),
			'doi_required' => (bool) $row['doi_required'],
		);
	}
}
