<?php

namespace Main\Controllers;

use Main\Core\Controller;
use Main\Services\AvatarService;
use PDO;

class HomeController extends Controller
{
		protected string $route_type = 'home';
		
    public function index()
    {
				$newsLimit = 6;
		    if (isset($_GET["page"])) {
			    if (!is_numeric($_GET["page"])) {
				    $_GET["page"] = 1;
			    }
			    $page = intval($_GET["page"]);
		    } else {
			    $page = 1;
		    }
	    
		    if (auth()->check() && auth()->user()->can('MANAGE_BLOG')) {
			    $accessibleBlogStatuses = ['draft', 'published'];
		    } else {
			    $accessibleBlogStatuses = ['published'];
		    }
		    $accessibleBlogStatuses = implode(", ", array_map(function($status) {
			    return "'$status'";
		    }, $accessibleBlogStatuses));
				
		    $itemsCount = db()->prepare("SELECT count(N.id) from News N INNER JOIN NewsCategories NC ON N.categoryID = NC.id INNER JOIN Accounts A ON N.accountID = A.id WHERE N.status IN ($accessibleBlogStatuses)");
		    $itemsCount->execute();
		    $itemsCount = $itemsCount->fetchColumn();
        $pageCount = ceil($itemsCount / $newsLimit);
		    if ($page > $pageCount) {
			    $page = 1;
		    }
        $visibleItemsCount = $page * $newsLimit - $newsLimit;
        $visiblePageCount = 5;

        $sliderItems = db()->prepare("SELECT * FROM Slider");
		    $sliderItems->execute();
				$sliderItems = array_map(function($sliderItem) {
					$sliderItem['image']  = "/assets/core/images/slider/" . $sliderItem['imageID'] . "." . $sliderItem['imageType'];
					
					return $sliderItem;
				}, $sliderItems->fetchAll());
	    
		    $posts = db()->prepare("SELECT N.*, NC.name as categoryName, NC.slug as categorySlug, A.username, A.realname from News N INNER JOIN NewsCategories NC ON N.categoryID = NC.id INNER JOIN Accounts A ON N.accountID = A.id WHERE N.status IN ($accessibleBlogStatuses) ORDER BY N.id DESC LIMIT $visibleItemsCount, $newsLimit");
		    $posts->execute();
		    $posts = $posts->fetchAll(PDO::FETCH_ASSOC);
		    $posts = array_map(function($post) {
			    $newsCount = db()->prepare("SELECT COUNT(id) FROM NewsComments WHERE newsID = ? AND status = ? ORDER BY id DESC");
			    $newsCount->execute(array($post["id"], 1));
			    $post["commentsCount"] = $newsCount->fetchColumn();
			    
			    $post["user"] = [
				    'id' => $post["accountID"],
				    'username' => $post["username"],
				    'displayName' => $post["realname"],
				    'avatar' => AvatarService::get($post["accountID"], $post["realname"]),
			    ];
			    
			    $post["category"] = [
				    'id' => $post["categoryID"],
				    'name' => $post["categoryName"],
				    'slug' => $post["categorySlug"],
			    ];
			    
			    $post['image']  = "/assets/core/images/blog/posts/" . $post['imageID'] . "." . $post['imageType'];
			    
			    return $post;
		    }, $posts);

        $topDonorsAllTime = db()->prepare("SELECT SUM(O.total) as total, O.accountID, A.username, A.realname FROM Orders O INNER JOIN Accounts A ON O.accountID = A.id WHERE O.status = ? AND O.earnings > 0 GROUP BY O.accountID HAVING total > 0 ORDER BY total DESC LIMIT 5");
        $topDonorsAllTime->execute(array(1));
        $topDonorsAllTime = array_map(function ($donor) {
	        $donor["displayName"] = $donor["realname"];
	        $donor['avatar'] = AvatarService::get($donor['accountID'], $donor['realname']);
					
	        return $donor;
        }, $topDonorsAllTime->fetchAll());

        $topDonorsMonth = db()->prepare("SELECT SUM(O.total) as total, O.accountID, A.username, A.realname FROM Orders O INNER JOIN Accounts A ON O.accountID = A.id WHERE O.status = ? AND O.earnings > 0 AND O.creationDate LIKE ? GROUP BY O.accountID HAVING total > 0 ORDER BY total DESC LIMIT 5");
        $topDonorsMonth->execute(array(1, '%' . date("Y-m") . '%'));
        $topDonorsMonth = array_map(function ($donor) {
	        $donor["displayName"] = $donor["realname"];
	        $donor['avatar'] = AvatarService::get($donor['accountID'], $donor['realname']);
					
	        return $donor;
        }, $topDonorsMonth->fetchAll());

        $recentDonations = db()->prepare("SELECT O.total, O.accountID, A.username, A.realname FROM Orders O INNER JOIN Accounts A ON O.accountID = A.id WHERE O.status = ? AND O.earnings > 0 ORDER BY O.id DESC LIMIT 5");
        $recentDonations->execute(array(1));
        $recentDonations = array_map(function ($donor) {
					$donor["displayName"] = $donor["realname"];
	        $donor['avatar'] = AvatarService::get($donor['accountID'], $donor['realname']);
					
	        return $donor;
        }, $recentDonations->fetchAll());

        $recentPurchases = db()->prepare("SELECT P.name as productName, PC.name as categoryName, O.accountID, A.username, A.realname FROM OrderProducts OP INNER JOIN Orders O ON OP.orderID = O.id INNER JOIN Products P ON OP.productID = P.id INNER JOIN ProductCategories PC ON P.categoryID = PC.id INNER JOIN Accounts A ON O.accountID = A.id WHERE O.status = ? ORDER BY O.id DESC LIMIT 5");
        $recentPurchases->execute(array(1));
        $recentPurchases = array_map(function ($donor) {
	        $donor["displayName"] = $donor["realname"];
	        $donor['avatar'] = AvatarService::get($donor['accountID'], $donor['realname']);
					
	        return $donor;
        }, $recentPurchases->fetchAll());

        return $this->view('home', compact('sliderItems', 'posts', 'topDonorsAllTime', 'topDonorsMonth', 'recentDonations', 'recentPurchases', 'page', 'pageCount', 'visiblePageCount'));
    }
}