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

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Pipeline-Stufen (BAUPLAN Abschnitt 13).
 *
 * Die Pipeline bildet nur den aktiven Verkaufsprozess ab. "Newsletter-Kontakt"
 * und "Kunde" sind bewusst KEINE Stufen, sondern Tags/Listen, sonst wird das
 * Board zur Muellhalde. Die Default-Stufen sind frei konfigurierbar
 * (umbenennen, ergaenzen); zwei sind als Abschluss markiert (gewonnen/verloren).
 *
 * Nicht final, damit Tests die DB-Methoden per Test-Double ueberschreiben koennen.
 */
class StageRepository {

	/**
	 * Default-Stufen bei der Erstinstallation.
	 *
	 * @return array<int, array{slug:string,label:string,is_won:int,is_lost:int}>
	 */
	private static function defaults(): array {
		return array(
			array( 'slug' => 'new', 'label' => __( 'Neuer Lead', 'kurato' ), 'is_won' => 0, 'is_lost' => 0 ),
			array( 'slug' => 'contacted', 'label' => __( 'Kontaktiert', 'kurato' ), 'is_won' => 0, 'is_lost' => 0 ),
			array( 'slug' => 'qualified', 'label' => __( 'Qualifiziert', 'kurato' ), 'is_won' => 0, 'is_lost' => 0 ),
			array( 'slug' => 'offer_sent', 'label' => __( 'Angebot gesendet', 'kurato' ), 'is_won' => 0, 'is_lost' => 0 ),
			array( 'slug' => 'follow_up', 'label' => __( 'Nachfassen', 'kurato' ), 'is_won' => 0, 'is_lost' => 0 ),
			array( 'slug' => 'won', 'label' => __( 'Gewonnen', 'kurato' ), 'is_won' => 1, 'is_lost' => 0 ),
			array( 'slug' => 'lost', 'label' => __( 'Verloren', 'kurato' ), 'is_won' => 0, 'is_lost' => 1 ),
		);
	}

	/**
	 * Legt die Default-Stufen an, falls noch keine existieren.
	 *
	 * @return void
	 */
	public function install_defaults(): void {
		global $wpdb;
		$table = Schema::table( 'stages' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		if ( $count > 0 ) {
			return;
		}

		$order = 0;
		foreach ( self::defaults() as $stage ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->insert(
				$table,
				array(
					'slug'       => $stage['slug'],
					'label'      => $stage['label'],
					'sort_order' => $order,
					'is_won'     => $stage['is_won'],
					'is_lost'    => $stage['is_lost'],
				)
			);
			$order++;
		}
	}

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

	/**
	 * ID der ersten Stufe (Eingang neuer Kontakte).
	 *
	 * @return int 0, wenn keine Stufe existiert.
	 */
	public function first_stage_id(): int {
		global $wpdb;
		$table = Schema::table( 'stages' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT id FROM {$table} ORDER BY sort_order ASC, id ASC LIMIT 1" );
	}

	/**
	 * Eine Stufe.
	 *
	 * @param int $id Stufen-ID.
	 * @return array<string, mixed>|null
	 */
	public function get( int $id ): ?array {
		global $wpdb;
		$table = Schema::table( 'stages' );
		// 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;
	}

	/**
	 * Anzahl Kontakte je Stufe (fuer die Board-Kopfzeilen).
	 *
	 * @return array<int, int> stage_id => Anzahl.
	 */
	public function contact_counts(): array {
		global $wpdb;
		$contacts = Schema::table( 'contacts' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( "SELECT stage_id, COUNT(*) AS total FROM {$contacts} WHERE stage_id IS NOT NULL GROUP BY stage_id", ARRAY_A );
		$out  = array();
		foreach ( (array) $rows as $row ) {
			$out[ (int) $row['stage_id'] ] = (int) $row['total'];
		}
		return $out;
	}

	/**
	 * 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'],
			'sort_order' => (int) $row['sort_order'],
			'is_won'     => (bool) $row['is_won'],
			'is_lost'    => (bool) $row['is_lost'],
		);
	}
}
