<?php

namespace Main\Controllers;

use Main\Core\Controller;
use Main\Services\AvatarService;
use Main\Services\SeoService;
use Main\Services\UserService;
use PDO;
use PDOException;

class MemberController extends Controller
{
		protected string $route_type = 'member';
		
    public function index()
    {
	      SeoService::set('members.index');
				
        return $this->view('members.index');
    }

    public function show($id)
    {
        $member = db()->prepare("SELECT * FROM Accounts WHERE id = ?");
        $member->execute(array($id));
        $member = $member->fetch();

        if (!$member) abort_404();
	    
		    SeoService::set('members.show', [
			    'username' => $member['realname'],
		    ]);
				
        $lastSeen = db()->prepare("SELECT * FROM OnlineAccountsHistory WHERE accountID = ?");
        $lastSeen->execute(array($member["id"]));
        $lastSeen = $lastSeen->fetch(PDO::FETCH_ASSOC);

				$member["displayName"] = $member["realname"];
				$member["avatar"] = AvatarService::get($member["id"], $member["realname"]);
        $member["lastSeen"] = (($lastSeen) ? $lastSeen["creationDate"] : null);
        $member["creationDate"] = (($member["creationDate"] == "1000-01-01 00:00:00") ? null : $member["creationDate"]);
				
				// Member Roles
	      $member["roles"] = UserService::getRoles($member["id"]);
				$member["primaryRole"] = $member["roles"][0];

        $accountCustomFieldValues = db()->prepare("SELECT ACF.name, ACFV.value FROM AccountCustomFieldValues ACFV INNER JOIN AccountCustomFields ACF ON ACF.id = ACFV.customFieldID WHERE ACFV.accountID = ? AND ACF.isPublic = ? AND ACFV.value != ''");
        $accountCustomFieldValues->execute(array($member["id"], 1));
		    $member["customFields"] = $accountCustomFieldValues->fetchAll(PDO::FETCH_ASSOC);
	    
		    $bans = db()->prepare("SELECT * FROM BannedAccounts WHERE accountID = ? AND (expiryDate > ? OR expiryDate = ?)");
		    $bans->execute(array($member["id"], datetime(), '1000-01-01 00:00:00'));
		    $member["bans"] = $bans->fetchAll();
	    
		    $leaderboards = db()->query("SELECT serverName, serverSlug FROM Leaderboards");
		    $leaderboards->execute();
		    $leaderboards = $leaderboards->fetchAll(PDO::FETCH_ASSOC);
				
				$leaderboard = null;
				if (input('leaderboard')) {
					$leaderboard = db()->prepare("SELECT * FROM Leaderboards WHERE serverSlug = ?");
					$leaderboard->execute(array(input('leaderboard')));
					$leaderboard = $leaderboard->fetch(PDO::FETCH_ASSOC);
					
					if (!$leaderboard) abort_404();
					
					$usernameColumn = $leaderboard["usernameColumn"];
					$mysqlTable = $leaderboard["mysqlTable"];
					$sorter = $leaderboard["sorter"];
					$tableTitles = $leaderboard["tableTitles"];
					$tableData = $leaderboard["tableData"];
					
					$leaderboard["tableTitles"] = explode(",", $tableTitles);
					$leaderboard["tableColumns"] = explode(",", $tableData);
					
					if ($leaderboard["mysqlServer"] == '0') {
						$leaderboardUserData = db()->prepare("SELECT $usernameColumn,$tableData FROM $mysqlTable WHERE $usernameColumn = ? ORDER BY $sorter DESC LIMIT 1");
						$leaderboardUserData->execute(array($member["realname"]));
						$leaderboard["userData"] = $leaderboardUserData->fetch(PDO::FETCH_ASSOC);
					}
					else {
						try {
							$newDB = new PDO("mysql:host=".$leaderboard["mysqlServer"]."; port=".$leaderboard["mysqlPort"]."; dbname=".$leaderboard["mysqlDatabase"]."; charset=utf8", $leaderboard["mysqlUsername"], $leaderboard["mysqlPassword"]);
						}
						catch (PDOException $e) {
							die("<strong>MySQL connection error:</strong> ".utf8_encode($e->getMessage()));
						}
						$leaderboardUserData = $newDB->prepare("SELECT $usernameColumn,$tableData FROM $mysqlTable WHERE $usernameColumn = ? ORDER BY $sorter DESC LIMIT 1");
						$leaderboardUserData->execute(array($member["realname"]));
						$leaderboard["userData"] = $leaderboardUserData->fetch(PDO::FETCH_ASSOC);
					}
				}

        return $this->view('members.show', compact('member', 'leaderboards', 'leaderboard'));
    }
}