<?php declare(strict_types=1);

/*
 * Подсчёт топа авторов по статьям
 * php version 7.2.0
 *
 * @category Utilities
 * @package	Ifhub
 * @author	 Oreolek <keloero@oreolek.ru>
 * @license	ISC https://opensource.org/licenses/ISC
 * @link		 https://ifhub.club
 */

require_once 'vendor/autoload.php';
$config = \Dotenv\Dotenv::create(__DIR__);
$config->load();
$config->required(['DB_DATABASE', 'DB_USER', 'DB_PASS', 'DB_HOST', 'DB_DRIVER']);
// $dbconfig = new \Doctrine\DBAL\Configuration();
$conn = \Doctrine\DBAL\DriverManager::getConnection(
	[
		'dbname'	 => getenv('DB_DATABASE'),
		'user'		 => getenv('DB_USER'),
		'password' => getenv('DB_PASS'),
		'host'		 => getenv('DB_HOST'),
		'driver'	 => getenv('DB_DRIVER'),
	],
	null
);

$date_start = ($argv[1] ?? date('Y')).'-01-01 00:00:00';
$date_start = new \DateTime($date_start);
$date_start = $date_start->format('c');
$queryBuilder = $conn->createQueryBuilder();

$articles = $queryBuilder
	->select(
		't.topic_title',
		't.topic_rating',
		't.topic_date_publish',
		'u.user_login'
	)
	->from('topic', 't')
	->innerJoin('t', 'user', 'u', 't.user_id = u.user_id')
	->where('t.topic_date_publish >= ?')
	->andWhere('t.topic_date_publish < CURRENT_DATE')
	->andWhere("t.topic_type = 'topic'")
	->andWhere('t.topic_rating >= 7')
	->andWhere('u.user_admin = 0')
	->orderBy('t.topic_rating', 'desc');
$articles = $articles->getSql();
$statement = $conn->prepare($articles);
$statement->bindValue(1, $date_start);
$statement->execute();
while (($row = $statement->fetch()) !== false) {
	$date = new \DateTime($row['topic_date_publish']);
	$rating = (int) $row['topic_rating'];
	echo $date->format('d.m.Y').'| '.$row['user_login'].', '.$row['topic_title'].' | '.$rating.PHP_EOL;
}