<?php
/**
 * Stats: aggregate queries for the admin dashboard.
 */

defined( 'ABSPATH' ) || exit;

class BW_Update_Server_Stats {

	const STALE_DAYS = 30;

	public static function overview() {
		global $wpdb;
		$table = BW_Update_Server_Installer::table_name();

		$total_sites = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT site_url) FROM {$table} WHERE site_url != ''" );
		if ( 0 === $total_sites ) {
			$total_sites = (int) $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT DISTINCT ip_address FROM {$table}) s" );
		}

		$total_plugins = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT plugin_slug) FROM {$table}" );
		$checks_today  = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE checked_at >= %s",
				gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS )
			)
		);

		$stale_cutoff = gmdate( 'Y-m-d H:i:s', time() - ( self::STALE_DAYS * DAY_IN_SECONDS ) );
		$stale_sites  = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM (
					SELECT site_url, MAX(checked_at) AS last_check
					FROM {$table}
					WHERE site_url != ''
					GROUP BY site_url
					HAVING last_check < %s
				) s",
				$stale_cutoff
			)
		);

		return array(
			'total_sites'   => $total_sites,
			'total_plugins' => $total_plugins,
			'checks_today'  => $checks_today,
			'stale_sites'   => $stale_sites,
		);
	}

	public static function installs_by_plugin() {
		global $wpdb;
		$table = BW_Update_Server_Installer::table_name();

		$slugs = $wpdb->get_col( "SELECT DISTINCT plugin_slug FROM {$table} ORDER BY plugin_slug ASC" );
		$out   = array();

		foreach ( $slugs as $slug ) {
			$sites = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						COALESCE(NULLIF(site_url, ''), CONCAT('ip:', ip_address)) AS site_url,
						MAX(installed_version) AS installed_version,
						MAX(wp_version) AS wp_version,
						MAX(checked_at) AS last_check,
						COUNT(*) AS check_count
					FROM {$table}
					WHERE plugin_slug = %s
					GROUP BY COALESCE(NULLIF(site_url, ''), CONCAT('ip:', ip_address))
					ORDER BY last_check DESC",
					$slug
				)
			);

			$versions = array();
			foreach ( $sites as $s ) {
				$v = $s->installed_version ?: '?';
				$versions[ $v ] = ( $versions[ $v ] ?? 0 ) + 1;
			}
			arsort( $versions );

			$out[ $slug ] = array(
				'sites_count'          => count( $sites ),
				'version_distribution' => $versions,
				'sites'                => $sites,
			);
		}

		return $out;
	}
}