<?php
/**
 * Dashboard data provider.
 *
 * Provides statistics and data for the admin dashboard.
 *
 * @package Waitlytics
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class WTLY_Dashboard
 */
class WTLY_Dashboard {

	/**
	 * Get dashboard statistics.
	 *
	 * @return array {
	 *     @type int   $active_subscribers     Total active subscribers.
	 *     @type int   $products_watched       Distinct products with active subs.
	 *     @type int   $out_of_stock_watched   OOS products with active subs.
	 *     @type int   $emails_sent_this_month Notifications sent this month.
	 *     @type float $conversion_rate        Percentage of notified that purchased.
	 * }
	 */
	public function get_stats() {
		global $wpdb;

		$sub_table = $wpdb->prefix . 'wtly_subscribers';
		$log_table = $wpdb->prefix . 'wtly_notifications_log';

		// Active subscribers.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$active_subscribers = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$sub_table} WHERE status = %s",
				'active'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Products watched (distinct products with active subs).
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$products_watched = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT product_id) FROM {$sub_table} WHERE status = %s",
				'active'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Out-of-stock products with active subscribers.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$out_of_stock_watched = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT s.product_id)
				FROM {$sub_table} s
				INNER JOIN {$wpdb->postmeta} pm ON s.product_id = pm.post_id
				WHERE s.status = %s
				AND pm.meta_key = '_stock_status'
				AND pm.meta_value = 'outofstock'",
				'active'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Emails sent this month.
		$first_of_month         = gmdate( 'Y-m-01 00:00:00' );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$emails_sent_this_month = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$log_table} WHERE sent_at >= %s",
				$first_of_month
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Conversion rate (notified subscribers who purchased).
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_notified = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$log_table}"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_purchased = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$log_table} WHERE purchased = %d",
				1
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$conversion_rate = $total_notified > 0
			? round( ( $total_purchased / $total_notified ) * 100, 1 )
			: 0;

		return array(
			'active_subscribers'     => $active_subscribers,
			'products_watched'       => $products_watched,
			'out_of_stock_watched'   => $out_of_stock_watched,
			'emails_sent_this_month' => $emails_sent_this_month,
			'conversion_rate'        => $conversion_rate,
		);
	}

	/**
	 * Get most wanted products by subscriber count.
	 *
	 * @param int $limit Number of products to return. Default 10.
	 * @return array Array of objects with product_id, sub_count, product_name, stock_status.
	 */
	public function get_most_wanted_products( $limit = 10 ) {
		global $wpdb;

		$sub_table = $wpdb->prefix . 'wtly_subscribers';
		$limit     = absint( $limit );
		if ( 0 === $limit ) {
			$limit = 10;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT product_id, COUNT(*) AS sub_count
				FROM {$sub_table}
				WHERE status = %s
				GROUP BY product_id
				ORDER BY sub_count DESC
				LIMIT %d",
				'active',
				$limit
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $results ) ) {
			return array();
		}

		// Enrich with product data.
		foreach ( $results as $row ) {
			$product = wc_get_product( $row->product_id );
			if ( $product ) {
				$row->product_name  = $product->get_name();
				$row->stock_status  = $product->get_stock_status();
				$row->stock_quantity = $product->get_stock_quantity();
				$row->edit_link     = get_edit_post_link( $row->product_id, 'raw' );
			} else {
				$row->product_name   = __( '(Deleted product)', 'waitlytics' );
				$row->stock_status   = 'unknown';
				$row->stock_quantity = null;
				$row->edit_link      = '';
			}
		}

		return $results;
	}

	/**
	 * Get recent activity (subscriptions and notifications).
	 *
	 * @param int $limit Number of items to return. Default 10.
	 * @return array Array of activity objects with type, message, and time.
	 */
	public function get_recent_activity( $limit = 10 ) {
		global $wpdb;

		$sub_table = $wpdb->prefix . 'wtly_subscribers';
		$log_table = $wpdb->prefix . 'wtly_notifications_log';
		$limit     = absint( $limit );
		if ( 0 === $limit ) {
			$limit = 10;
		}

		$activities = array();

		// Recent subscriptions.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$subscriptions = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, email, product_id, status, subscribed_at AS event_time
				FROM {$sub_table}
				ORDER BY subscribed_at DESC
				LIMIT %d",
				$limit
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		foreach ( $subscriptions as $sub ) {
			$product_name = $this->get_product_name( $sub->product_id );

			if ( 'active' === $sub->status ) {
				$type    = 'subscription';
				$message = sprintf(
					/* translators: 1: email address, 2: product name */
					__( '%1$s subscribed to "%2$s"', 'waitlytics' ),
					$sub->email,
					$product_name
				);
			} elseif ( 'unsubscribed' === $sub->status ) {
				$type    = 'unsubscription';
				$message = sprintf(
					/* translators: 1: email address, 2: product name */
					__( '%1$s unsubscribed from "%2$s"', 'waitlytics' ),
					$sub->email,
					$product_name
				);
			} else {
				continue;
			}

			$activities[] = (object) array(
				'type'    => $type,
				'message' => $message,
				'time'    => $sub->event_time,
			);
		}

		// Recent notification batches.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$notifications = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT product_id, COUNT(*) AS sent_count, MAX(sent_at) AS event_time
				FROM {$log_table}
				GROUP BY product_id, DATE(sent_at)
				ORDER BY event_time DESC
				LIMIT %d",
				$limit
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		foreach ( $notifications as $notif ) {
			$product_name = $this->get_product_name( $notif->product_id );
			$activities[] = (object) array(
				'type'    => 'notification',
				'message' => sprintf(
					/* translators: 1: number of notifications, 2: product name */
					__( '%1$d notifications sent for "%2$s" restock', 'waitlytics' ),
					(int) $notif->sent_count,
					$product_name
				),
				'time'    => $notif->event_time,
			);
		}

		// Sort by time descending.
		usort( $activities, function ( $a, $b ) {
			return strtotime( $b->time ) - strtotime( $a->time );
		} );

		return array_slice( $activities, 0, $limit );
	}

	/**
	 * Get product name by ID, with fallback.
	 *
	 * @param int $product_id Product ID.
	 * @return string Product name or fallback string.
	 */
	private function get_product_name( $product_id ) {
		$product = wc_get_product( $product_id );
		if ( $product ) {
			return $product->get_name();
		}
		return __( '(Deleted product)', 'waitlytics' );
	}
}
