<?php
  
  namespace Main\Controllers\Account;
  
  use Main\Core\Controller;
  use Main\Services\CategoryService;
  use Main\Services\SeoService;
  use PDO;
  use Spirit55555\Minecraft\MinecraftColors;
  
  class AccountController extends Controller
  {
	  protected string $route_type = 'profile';
		
    public function profile()
    {
	    SeoService::set('account.profile.index');
	    
			$customFields = db()->prepare("SELECT ACF.name, ACFV.value FROM AccountCustomFieldValues ACFV INNER JOIN AccountCustomFields ACF ON ACF.id = ACFV.customFieldID WHERE ACFV.accountID = ? AND ACFV.value != ''");
	    $customFields->execute(array(auth()->user()->id()));
	    $customFields = $customFields->fetchAll();
	    
	    $tfaStatus = false;
	    if (modules('tfa')->isActive()) {
		    $tfaStatus = db()->prepare("SELECT * FROM AccountTfaKeys WHERE accountID = ?");
		    $tfaStatus->execute(array(auth()->user()->id()));
		    $tfaStatus = $tfaStatus->rowCount() > 0;
	    }
	    
	    $leaderboards = db()->query("SELECT serverName, serverSlug FROM Leaderboards");
	    $leaderboards->execute();
	    $leaderboards = $leaderboards->fetchAll(PDO::FETCH_ASSOC);
	    
	    $leaderboard = null;
	    if (modules("leaderboards")->isActive() && 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(auth()->user()->displayName()));
			    $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(auth()->user()->displayName()));
			    $leaderboard["userData"] = $leaderboardUserData->fetch(PDO::FETCH_ASSOC);
		    }
	    }
	    
	    $tickets = [];
	    if (modules('support')->isActive()) {
		    $tickets = db()->prepare("SELECT S.*, SC.name as categoryName FROM Supports S INNER JOIN SupportCategories SC ON S.categoryID = SC.id WHERE S.accountID = ? ORDER BY S.updateDate DESC LIMIT 5");
		    $tickets->execute(array(auth()->user()->id()));
		    $tickets = $tickets->fetchAll();
	    }
			
			$creditLogs = [];
			if (modules('credit')->isActive()) {
				$creditLogs = db()->prepare("SELECT CH.*, A.username, A.realname as otherAccountDisplayName FROM CreditHistory CH LEFT JOIN Accounts A ON A.id = CH.otherAccountID WHERE CH.accountID = ? ORDER BY CH.id DESC LIMIT 5");
				$creditLogs->execute(array(auth()->user()->id()));
				$creditLogs = $creditLogs->fetchAll();
			}
	    
	    $orders = [];
			if (modules('store')->isActive()) {
				$orders = db()->prepare("SELECT * FROM Orders WHERE accountID = ? ORDER BY id DESC LIMIT 5");
				$orders->execute(array(auth()->user()->id()));
				$orders = $orders->fetchAll();
			}

	    return $this->view('account.profile', compact(
				'customFields',
				'tfaStatus',
				'leaderboards',
				'leaderboard',
				'tickets',
				'creditLogs',
				'orders'
	    ));
    }
	  
	  public function orders()
	  {
		  $this->route_type = 'account-orders';
		  SeoService::set('account.orders');
		  
		  $orders = db()->prepare("SELECT * FROM Orders WHERE accountID = ? ORDER BY id DESC LIMIT 50");
		  $orders->execute(array(auth()->user()->id()));
		  $orders = $orders->fetchAll();
		  
		  return $this->view('account.orders', compact('orders'));
	  }
	  
	  public function subscriptions()
	  {
		  $this->route_type = 'account-subscriptions';
		  SeoService::set('account.subscriptions');
		  
		  $subsProductsTableName = modules('tebex_store')->isActive() ? 'TebexProducts' : 'Products';
		  $subscriptions = db()->prepare("SELECT S.*, P.name as productName FROM Subscriptions S INNER JOIN $subsProductsTableName P ON P.id = S.productID WHERE S.accountID = ? ORDER BY S.id DESC LIMIT 50");
		  $subscriptions->execute(array(auth()->user()->id()));
		  $subscriptions = $subscriptions->fetchAll();
		  
		  return $this->view('account.subscriptions', compact('subscriptions'));
	  }
	  
	  public function creditHistory()
	  {
		  $this->route_type = 'account-credit-history';
		  SeoService::set('account.credit-history');
		  
		  $logs = db()->prepare("SELECT CH.*, A.username, A.realname as otherAccountDisplayName FROM CreditHistory CH LEFT JOIN Accounts A ON A.id = CH.otherAccountID WHERE CH.accountID = ? ORDER BY CH.id DESC LIMIT 50");
		  $logs->execute(array(auth()->user()->id()));
		  $logs = $logs->fetchAll();
		  
		  return $this->view('account.credit-logs', compact('logs'));
	  }
	  
	  public function applications()
	  {
		  $this->route_type = 'account-applications';
		  SeoService::set('account.applications');
		  
		  $applications = db()->prepare("SELECT AP.id, AF.title, AP.reason, AP.status FROM Applications AP INNER JOIN Accounts A ON A.id = AP.accountID INNER JOIN ApplicationForms AF ON AF.id = AP.formID WHERE AP.accountID = ? ORDER BY AP.id DESC LIMIT 50");
		  $applications->execute(array(auth()->user()->id()));
		  $applications = $applications->fetchAll();
		  
		  return $this->view('account.applications', compact('applications'));
	  }
  }