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

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Wiedervorlagen / Nachfass-Aufgaben (hs_tasks, BAUPLAN 13).
 *
 * Adressiert den groessten KMU-Pain-Point: vergessenes Nachfassen. Jede Aufgabe
 * haengt an einem Kontakt, hat einen Titel und ein optionales Faelligkeitsdatum.
 *
 * Nicht final, damit Tests die DB-Methoden per Test-Double ueberschreiben koennen.
 */
class TaskRepository {

	/**
	 * Aufgaben eines Kontakts (offene zuerst, dann nach Faelligkeit).
	 *
	 * @param int  $contact_id   Kontakt-ID.
	 * @param bool $include_done Auch erledigte Aufgaben liefern.
	 * @return array<int, array<string, mixed>>
	 */
	public function for_contact( int $contact_id, bool $include_done = true ): array {
		global $wpdb;
		$table = Schema::table( 'tasks' );
		$where = 'contact_id = %d';
		$args  = array( $contact_id );
		if ( ! $include_done ) {
			$where .= ' AND done = 0';
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE {$where} ORDER BY done ASC, due_date IS NULL, due_date ASC, id DESC", ...$args ),
			ARRAY_A
		);
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Legt eine Aufgabe an.
	 *
	 * @param int         $contact_id Kontakt-ID.
	 * @param string      $title      Titel.
	 * @param string|null $due_date   Faelligkeit (Y-m-d) oder null.
	 * @return int Task-ID (0 bei Fehler).
	 */
	public function create( int $contact_id, string $title, ?string $due_date ): int {
		$title = trim( sanitize_text_field( $title ) );
		if ( $contact_id <= 0 || '' === $title ) {
			return 0;
		}
		$due = $this->normalize_date( $due_date );

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert(
			Schema::table( 'tasks' ),
			array(
				'contact_id' => $contact_id,
				'title'      => $title,
				'due_date'   => $due,
				'done'       => 0,
				'created_at' => current_time( 'mysql', true ),
			)
		);
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Setzt den Erledigt-Status.
	 *
	 * @param int  $id   Task-ID.
	 * @param bool $done Erledigt?
	 * @return bool
	 */
	public function set_done( int $id, bool $done ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return false !== $wpdb->update( Schema::table( 'tasks' ), array( 'done' => $done ? 1 : 0 ), array( 'id' => $id ) );
	}

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

	/**
	 * Offene Aufgaben ueber ALLE Kontakte (globale Wiedervorlagen-Liste fuer die
	 * Cockpit-Bruecke). Optional nur ueberfaellige. Join auf Kontakte, damit die
	 * App Name statt nur ID zeigen kann. Faelligste zuerst.
	 *
	 * @param bool $overdue_only Nur ueberfaellige (due_date < heute).
	 * @param int  $limit        Obergrenze (gekappt auf 1..500).
	 * @return array<int, array<string, mixed>>
	 */
	public function open( bool $overdue_only = false, int $limit = 200 ): array {
		global $wpdb;
		$tasks    = Schema::table( 'tasks' );
		$contacts = Schema::table( 'contacts' );
		$today    = gmdate( 'Y-m-d' );
		$limit    = max( 1, min( 500, $limit ) );

		$where = 't.done = 0';
		$args  = array();
		if ( $overdue_only ) {
			$where  .= ' AND t.due_date IS NOT NULL AND t.due_date < %s';
			$args[]  = $today;
		}
		$args[] = $limit;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT t.id, t.contact_id, t.title, t.due_date, t.done, t.created_at, c.display_name, c.email
				FROM {$tasks} t
				LEFT JOIN {$contacts} c ON c.id = t.contact_id
				WHERE {$where}
				ORDER BY t.due_date IS NULL, t.due_date ASC, t.id ASC
				LIMIT %d",
				...$args
			),
			ARRAY_A
		);
		return array_map( array( $this, 'hydrate_open' ), $rows ?: array() );
	}

	/**
	 * Normalisiert eine offene Aufgabe inkl. Kontakt-Name fuer die Bruecke.
	 *
	 * @param array<string, mixed> $row Rohzeile (Join).
	 * @return array<string, mixed>
	 */
	private function hydrate_open( array $row ): array {
		$name = trim( (string) ( $row['display_name'] ?? '' ) );
		if ( '' === $name ) {
			$name = (string) ( $row['email'] ?? '' );
		}
		$due = null !== $row['due_date'] ? (string) $row['due_date'] : '';
		return array(
			'id'           => (int) $row['id'],
			'contact_id'   => (int) $row['contact_id'],
			'contact_name' => $name,
			'title'        => (string) $row['title'],
			'due_date'     => $due,
			'overdue'      => '' !== $due && $due < gmdate( 'Y-m-d' ),
			'done'         => false,
		);
	}

	/**
	 * Anzahl offener, ueberfaelliger Aufgaben (fuer Hinweise/Dashboard).
	 *
	 * @return int
	 */
	public function count_overdue(): int {
		global $wpdb;
		$table = Schema::table( 'tasks' );
		$today = gmdate( 'Y-m-d' );
		// 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 done = 0 AND due_date IS NOT NULL AND due_date < %s", $today ) );
	}

	/**
	 * Prueft und normalisiert ein Datum auf Y-m-d oder null.
	 *
	 * @param string|null $date Eingabe.
	 * @return string|null
	 */
	private function normalize_date( ?string $date ): ?string {
		$date = trim( (string) $date );
		if ( '' === $date ) {
			return null;
		}
		$parts = date_parse( $date );
		if ( ! empty( $parts['error_count'] ) || ! checkdate( (int) $parts['month'], (int) $parts['day'], (int) $parts['year'] ) ) {
			return null;
		}
		return sprintf( '%04d-%02d-%02d', $parts['year'], $parts['month'], $parts['day'] );
	}

	/**
	 * 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'],
			'contact_id' => (int) $row['contact_id'],
			'title'      => (string) $row['title'],
			'due_date'   => null !== $row['due_date'] ? (string) $row['due_date'] : '',
			'done'       => (bool) $row['done'],
			'created_at' => (string) $row['created_at'],
		);
	}
}
