<?php
/**
 * Buchung Repository
 *
 * Sprint 11: Fast query methods using custom database table
 *
 * @package KursBooking
 */

declare(strict_types=1);

// Prevent direct access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class Kurs_Booking_Buchung_Repository
 *
 * Provides optimized query methods for bookings using the custom table.
 * Falls back to post meta queries if custom table is not available.
 */
class Kurs_Booking_Buchung_Repository {

	/**
	 * Cache group for repository queries
	 */
	private const CACHE_GROUP = 'kurs_buchungen';

	/**
	 * Cache expiration in seconds (5 minutes)
	 */
	private const CACHE_EXPIRATION = 300;

	/**
	 * Check if custom table should be used
	 *
	 * @return bool True if custom table is available and has data.
	 */
	public static function use_custom_table(): bool {
		static $use_table = null;

		if ( null === $use_table ) {
			$use_table = Kurs_Booking_Database::table_exists();
		}

		return $use_table;
	}

	/**
	 * Count bookings by status
	 *
	 * @param string      $status Booking status (pending, confirmed, cancelled, anfrage).
	 * @param int|null    $kurs_id Optional course ID filter.
	 * @param string|null $date_from Optional start date (Y-m-d).
	 * @param string|null $date_to Optional end date (Y-m-d).
	 * @return int Count of bookings.
	 */
	public static function count_by_status(
		string $status,
		?int $kurs_id = null,
		?string $date_from = null,
		?string $date_to = null
	): int {
		global $wpdb;

		$cache_key = sprintf( 'count_%s_%d_%s_%s', $status, $kurs_id ?? 0, $date_from ?? '', $date_to ?? '' );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false !== $cached ) {
			return (int) $cached;
		}

		if ( self::use_custom_table() ) {
			$table = Kurs_Booking_Database::get_table_name();
			$where = array( 'status = %s' );
			$args  = array( $status );

			if ( $kurs_id ) {
				$where[] = 'kurs_id = %d';
				$args[]  = $kurs_id;
			}

			if ( $date_from ) {
				$where[] = 'created_at >= %s';
				$args[]  = $date_from . ' 00:00:00';
			}

			if ( $date_to ) {
				$where[] = 'created_at <= %s';
				$args[]  = $date_to . ' 23:59:59';
			}

			$where_sql = implode( ' AND ', $where );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}", ...$args )
			);
		} else {
			// Fallback to post meta query.
			$count = self::count_by_status_legacy( $status, $kurs_id, $date_from, $date_to );
		}

		wp_cache_set( $cache_key, $count, self::CACHE_GROUP, self::CACHE_EXPIRATION );

		return $count;
	}

	/**
	 * Legacy count method using post meta
	 *
	 * @param string      $status Status.
	 * @param int|null    $kurs_id Course ID.
	 * @param string|null $date_from Start date.
	 * @param string|null $date_to End date.
	 * @return int Count.
	 */
	private static function count_by_status_legacy(
		string $status,
		?int $kurs_id,
		?string $date_from,
		?string $date_to
	): int {
		global $wpdb;

		$where = array(
			'p.post_type = %s',
			"p.post_status = 'publish'",
			"pm_status.meta_key = '_buchung_status'",
			'pm_status.meta_value = %s',
		);
		$args = array( 'kurs-buchung', $status );

		$join = "INNER JOIN {$wpdb->postmeta} pm_status ON p.ID = pm_status.post_id";

		if ( $kurs_id ) {
			$join   .= " INNER JOIN {$wpdb->postmeta} pm_kurs ON p.ID = pm_kurs.post_id";
			$where[] = "pm_kurs.meta_key = '_buchung_kurs_id'";
			$where[] = 'pm_kurs.meta_value = %d';
			$args[]  = $kurs_id;
		}

		if ( $date_from ) {
			$where[] = 'p.post_date >= %s';
			$args[]  = $date_from . ' 00:00:00';
		}

		if ( $date_to ) {
			$where[] = 'p.post_date <= %s';
			$args[]  = $date_to . ' 23:59:59';
		}

		$where_sql = implode( ' AND ', $where );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} p {$join} WHERE {$where_sql}", ...$args )
		);
	}

	/**
	 * Get booking IDs for a course
	 *
	 * @param int         $kurs_id Course ID.
	 * @param string|null $status Optional status filter.
	 * @param int         $limit Maximum results (0 = unlimited).
	 * @param int         $offset Offset for pagination.
	 * @return array<int> Array of booking post IDs.
	 */
	public static function get_bookings_for_kurs(
		int $kurs_id,
		?string $status = null,
		int $limit = 0,
		int $offset = 0
	): array {
		global $wpdb;

		if ( self::use_custom_table() ) {
			$table = Kurs_Booking_Database::get_table_name();
			$where = array( 'kurs_id = %d' );
			$args  = array( $kurs_id );

			if ( $status ) {
				$where[] = 'status = %s';
				$args[]  = $status;
			}

			$where_sql = implode( ' AND ', $where );
			$limit_sql = $limit > 0 ? sprintf( 'LIMIT %d OFFSET %d', $limit, $offset ) : '';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$results = $wpdb->get_col(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->prepare( "SELECT post_id FROM {$table} WHERE {$where_sql} ORDER BY created_at DESC {$limit_sql}", ...$args )
			);

			return array_map( 'intval', $results );
		}

		// Fallback to WP_Query.
		$meta_query = array(
			array(
				'key'   => '_buchung_kurs_id',
				'value' => $kurs_id,
				'type'  => 'NUMERIC',
			),
		);

		if ( $status ) {
			$meta_query[] = array(
				'key'   => '_buchung_status',
				'value' => $status,
			);
		}

		$args = array(
			'post_type'      => 'kurs-buchung',
			'post_status'    => 'publish',
			'posts_per_page' => $limit ?: -1,
			'offset'         => $offset,
			'fields'         => 'ids',
			'orderby'        => 'date',
			'order'          => 'DESC',
			'meta_query'     => $meta_query, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
		);

		return get_posts( $args );
	}

	/**
	 * Count confirmed participants for a course
	 *
	 * @param int $kurs_id Course ID.
	 * @return int Total confirmed participant count.
	 */
	public static function count_confirmed_participants( int $kurs_id ): int {
		global $wpdb;

		$cache_key = 'participants_' . $kurs_id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false !== $cached ) {
			return (int) $cached;
		}

		if ( self::use_custom_table() ) {
			$table = Kurs_Booking_Database::get_table_name();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"SELECT COALESCE(SUM(anzahl), 0) FROM {$table} WHERE kurs_id = %d AND status = 'confirmed'",
					$kurs_id
				)
			);
		} else {
			// Fallback: slower but works without custom table.
			$bookings = self::get_bookings_for_kurs( $kurs_id, 'confirmed' );
			$count    = 0;

			foreach ( $bookings as $booking_id ) {
				$count += (int) get_post_meta( $booking_id, '_buchung_anzahl', true ) ?: 1;
			}
		}

		wp_cache_set( $cache_key, $count, self::CACHE_GROUP, self::CACHE_EXPIRATION );

		return $count;
	}

	/**
	 * Calculate total revenue for a period
	 *
	 * @param string|null $date_from Start date (Y-m-d).
	 * @param string|null $date_to End date (Y-m-d).
	 * @param int|null    $kurs_id Optional course filter.
	 * @return array{total: float, confirmed: float, pending: float, count: int}
	 */
	public static function calculate_revenue(
		?string $date_from = null,
		?string $date_to = null,
		?int $kurs_id = null
	): array {
		global $wpdb;

		if ( self::use_custom_table() ) {
			$table = Kurs_Booking_Database::get_table_name();
			$where = array( "status IN ('confirmed', 'pending')" );
			$args  = array();

			if ( $kurs_id ) {
				$where[] = 'kurs_id = %d';
				$args[]  = $kurs_id;
			}

			if ( $date_from ) {
				$where[] = 'created_at >= %s';
				$args[]  = $date_from . ' 00:00:00';
			}

			if ( $date_to ) {
				$where[] = 'created_at <= %s';
				$args[]  = $date_to . ' 23:59:59';
			}

			$where_sql = implode( ' AND ', $where );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->get_row(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->prepare(
					"SELECT
						COALESCE(SUM(total_price), 0) as total,
						COALESCE(SUM(CASE WHEN status = 'confirmed' THEN total_price ELSE 0 END), 0) as confirmed,
						COALESCE(SUM(CASE WHEN status = 'pending' THEN total_price ELSE 0 END), 0) as pending,
						COUNT(*) as count
					FROM {$table}
					WHERE {$where_sql}",
					...$args
				)
			);

			return array(
				'total'     => (float) $result->total,
				'confirmed' => (float) $result->confirmed,
				'pending'   => (float) $result->pending,
				'count'     => (int) $result->count,
			);
		}

		// Fallback - simplified without custom table.
		return array(
			'total'     => 0.0,
			'confirmed' => 0.0,
			'pending'   => 0.0,
			'count'     => 0,
		);
	}

	/**
	 * Search bookings by email or name
	 *
	 * @param string $search Search term.
	 * @param int    $limit Maximum results.
	 * @return array<int> Array of booking post IDs.
	 */
	public static function search_bookings( string $search, int $limit = 50 ): array {
		global $wpdb;

		$search = '%' . $wpdb->esc_like( $search ) . '%';

		if ( self::use_custom_table() ) {
			$table = Kurs_Booking_Database::get_table_name();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$results = $wpdb->get_col(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"SELECT post_id FROM {$table}
					WHERE name LIKE %s OR email LIKE %s
					ORDER BY created_at DESC
					LIMIT %d",
					$search,
					$search,
					$limit
				)
			);

			return array_map( 'intval', $results );
		}

		// Fallback to WP_Query with meta search.
		$args = array(
			'post_type'      => 'kurs-buchung',
			'post_status'    => 'publish',
			'posts_per_page' => $limit,
			'fields'         => 'ids',
			'meta_query'     => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
				'relation' => 'OR',
				array(
					'key'     => '_buchung_name',
					'value'   => trim( $search, '%' ),
					'compare' => 'LIKE',
				),
				array(
					'key'     => '_buchung_email',
					'value'   => trim( $search, '%' ),
					'compare' => 'LIKE',
				),
			),
		);

		return get_posts( $args );
	}

	/**
	 * Get booking by confirmation token
	 *
	 * @param string $token Confirmation token.
	 * @return int|null Booking post ID or null.
	 */
	public static function get_by_token( string $token ): ?int {
		global $wpdb;

		if ( empty( $token ) ) {
			return null;
		}

		if ( self::use_custom_table() ) {
			$table = Kurs_Booking_Database::get_table_name();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$post_id = $wpdb->get_var(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"SELECT post_id FROM {$table} WHERE confirm_token = %s LIMIT 1",
					$token
				)
			);

			return $post_id ? (int) $post_id : null;
		}

		// Fallback.
		$args = array(
			'post_type'      => 'kurs-buchung',
			'post_status'    => 'publish',
			'posts_per_page' => 1,
			'fields'         => 'ids',
			'meta_query'     => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
				array(
					'key'   => '_buchung_confirm_token',
					'value' => $token,
				),
			),
		);

		$posts = get_posts( $args );
		return ! empty( $posts ) ? (int) $posts[0] : null;
	}

	/**
	 * Get statistics overview
	 *
	 * @return array{pending: int, confirmed: int, cancelled: int, anfrage: int, total_revenue: float}
	 */
	public static function get_statistics(): array {
		global $wpdb;

		$cache_key = 'statistics_overview';
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false !== $cached ) {
			return $cached;
		}

		if ( self::use_custom_table() ) {
			$table = Kurs_Booking_Database::get_table_name();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->get_row(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT
					SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
					SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed,
					SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
					SUM(CASE WHEN status = 'anfrage' THEN 1 ELSE 0 END) as anfrage,
					COALESCE(SUM(CASE WHEN status = 'confirmed' THEN total_price ELSE 0 END), 0) as total_revenue
				FROM {$table}"
			);

			$stats = array(
				'pending'       => (int) $result->pending,
				'confirmed'     => (int) $result->confirmed,
				'cancelled'     => (int) $result->cancelled,
				'anfrage'       => (int) $result->anfrage,
				'total_revenue' => (float) $result->total_revenue,
			);
		} else {
			$stats = array(
				'pending'       => self::count_by_status( 'pending' ),
				'confirmed'     => self::count_by_status( 'confirmed' ),
				'cancelled'     => self::count_by_status( 'cancelled' ),
				'anfrage'       => self::count_by_status( 'anfrage' ),
				'total_revenue' => 0.0,
			);
		}

		wp_cache_set( $cache_key, $stats, self::CACHE_GROUP, self::CACHE_EXPIRATION );

		return $stats;
	}

	/**
	 * Invalidate all caches for this repository
	 */
	public static function invalidate_cache(): void {
		wp_cache_delete( 'statistics_overview', self::CACHE_GROUP );
		// Note: Individual caches will expire naturally or be invalidated on write.
	}

	/**
	 * Invalidate cache for a specific course
	 *
	 * @param int $kurs_id Course ID.
	 */
	public static function invalidate_kurs_cache( int $kurs_id ): void {
		wp_cache_delete( 'participants_' . $kurs_id, self::CACHE_GROUP );
	}
}
