From 5eba322cbd24191e05304df08c80af846977d99b Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Tue, 14 Oct 2025 11:05:17 +0200
Subject: New stats overview of dates with most unread articles (#8089)

New view with direct links to dates with most unread articles:

<img width="734" height="581" alt="image" src="https://github.com/user-attachments/assets/159a39b3-3a06-4ae9-9cc0-62ae36d9db9c" />
---
 app/Models/StatsDAO.php       | 49 ++++++++++++++++++++++++++++++++++++++++++-
 app/Models/StatsDAOPGSQL.php  | 14 ++++++++++++-
 app/Models/StatsDAOSQLite.php | 14 ++++++++++++-
 app/Models/ViewStats.php      |  3 +++
 4 files changed, 77 insertions(+), 3 deletions(-)

(limited to 'app/Models')

diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 771a2c7ee..c10567951 100644
--- a/app/Models/StatsDAO.php
+++ b/app/Models/StatsDAO.php
@@ -5,6 +5,30 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo {
 
 	public const ENTRY_COUNT_PERIOD = 30;
 
+	/** Get the number of seconds to add to UTC to get the user's local time */
+	protected function getTimezoneOffset(): int {
+		$timezone = new DateTimeZone(date_default_timezone_get());
+		return $timezone->getOffset(new DateTime('now', new DateTimeZone('UTC')));
+	}
+
+	/**
+	 * @param string $field to use for the date
+	 * @param int $precision to apply to the timestamp (1 for seconds, 1000 for milliseconds, 1000000 for microseconds)
+	 * @param 'day'|'month'|'year' $granularity of the date intervals
+	 */
+	protected function sqlDateToIsoGranularity(string $field, int $precision, string $granularity): string {
+		if (!preg_match('/^[a-zA-Z0-9_]+$/', $field)) {
+			throw new InvalidArgumentException('Invalid date field!');
+		}
+		$offset = $this->getTimezoneOffset();
+		return match ($granularity) {
+			'day' => "FROM_UNIXTIME(($field / $precision) + $offset, '%Y-%m-%d')",
+			'month' => "FROM_UNIXTIME(($field / $precision) + $offset, '%Y-%m')",
+			'year' => "FROM_UNIXTIME(($field / $precision) + $offset, '%Y')",
+			default => throw new InvalidArgumentException('Invalid date granularity!'),
+		};
+	}
+
 	protected function sqlFloor(string $s): string {
 		return "FLOOR($s)";
 	}
@@ -132,8 +156,9 @@ SQL;
 		if ($feed) {
 			$restrict = "WHERE e.id_feed = {$feed}";
 		}
+		$offset = $this->getTimezoneOffset();
 		$sql = <<<SQL
-SELECT DATE_FORMAT(FROM_UNIXTIME(e.date), '{$period}') AS period
+SELECT DATE_FORMAT(FROM_UNIXTIME(e.date + {$offset}), '{$period}') AS period
 , COUNT(1) AS count
 FROM `_entry` AS e
 {$restrict}
@@ -356,4 +381,26 @@ SQL;
 
 		return $translated;
 	}
+
+	/**
+	 * Gets the date intervals with the largest number of unread articles.
+	 * @param 'id'|'date' $field to use for the date
+	 * @param 'day'|'month'|'year' $granularity of the date intervals
+	 * @return list<array{'granularity':string,'unread_count':int}>
+	 */
+	public function getMaxUnreadDates(string $field, string $granularity, int $max = 100): array {
+		$sql = <<<SQL
+SELECT
+	{$this->sqlDateToIsoGranularity($field, precision: $field === 'id' ? 1000000 : 1, granularity: $granularity)} AS granularity,
+	COUNT(*) AS unread_count
+FROM `_entry`
+WHERE is_read = 0
+GROUP BY granularity
+ORDER BY unread_count DESC, granularity DESC
+LIMIT $max;
+SQL;
+		$res = $this->fetchAssoc($sql);
+		/** @var list<array{granularity:string,unread_count:int}>|null $res */
+		return is_array($res) ? $res : [];
+	}
 }
diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php
index 5e3476808..c4a5e5c05 100644
--- a/app/Models/StatsDAOPGSQL.php
+++ b/app/Models/StatsDAOPGSQL.php
@@ -3,6 +3,17 @@ declare(strict_types=1);
 
 class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO {
 
+	#[\Override]
+	protected function sqlDateToIsoGranularity(string $field, int $precision, string $granularity): string {
+		$offset = $this->getTimezoneOffset();
+		return match ($granularity) {
+			'day' => "to_char(to_timestamp(($field / $precision) + $offset), 'YYYY-MM-DD')",
+			'month' => "to_char(to_timestamp(($field / $precision) + $offset), 'YYYY-MM')",
+			'year' => "to_char(to_timestamp(($field / $precision) + $offset), 'YYYY')",
+			default => throw new InvalidArgumentException('Invalid date granularity'),
+		};
+	}
+
 	/**
 	 * Calculates the number of article per hour of the day per feed
 	 *
@@ -43,8 +54,9 @@ class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO {
 		if ($feed) {
 			$restrict = "WHERE e.id_feed = {$feed}";
 		}
+		$offset = $this->getTimezoneOffset();
 		$sql = <<<SQL
-SELECT extract( {$period} from to_timestamp(e.date)) AS period
+SELECT extract( {$period} from to_timestamp(e.date + {$offset})) AS period
 , COUNT(1) AS count
 FROM `_entry` AS e
 {$restrict}
diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php
index 4e51615fc..50b12d2c7 100644
--- a/app/Models/StatsDAOSQLite.php
+++ b/app/Models/StatsDAOSQLite.php
@@ -3,6 +3,17 @@ declare(strict_types=1);
 
 class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO {
 
+	#[\Override]
+	protected function sqlDateToIsoGranularity(string $field, int $precision, string $granularity): string {
+		$offset = $this->getTimezoneOffset();
+		return match ($granularity) {
+			'day' => "strftime('%Y-%m-%d', ($field / $precision) + $offset, 'unixepoch')",
+			'month' => "strftime('%Y-%m', ($field / $precision) + $offset, 'unixepoch')",
+			'year' => "strftime('%Y', ($field / $precision) + $offset, 'unixepoch')",
+			default => throw new InvalidArgumentException('Invalid date granularity'),
+		};
+	}
+
 	#[\Override]
 	protected function sqlFloor(string $s): string {
 		return "CAST(($s) AS INT)";
@@ -18,8 +29,9 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO {
 		} else {
 			$restrict = '';
 		}
+		$offset = $this->getTimezoneOffset();
 		$sql = <<<SQL
-SELECT strftime('{$period}', e.date, 'unixepoch') AS period
+SELECT strftime('{$period}', e.date + {$offset}, 'unixepoch') AS period
 , COUNT(1) AS count
 FROM `_entry` AS e
 {$restrict}
diff --git a/app/Models/ViewStats.php b/app/Models/ViewStats.php
index 89b7d8af4..22b2470f9 100644
--- a/app/Models/ViewStats.php
+++ b/app/Models/ViewStats.php
@@ -44,4 +44,7 @@ final class FreshRSS_ViewStats extends FreshRSS_View {
 	public array $repartitionMonth;
 	/** @var list<array{id:int,name:string,category:string,count:int}> */
 	public array $topFeed;
+
+	/** @var list<array{granularity:string,unread_count:int}> */
+	public array $unreadDates;
 }
-- 
cgit v1.2.3