<style>
	#milestones-page .nav-link { padding: 8px; font-size: 1.6rem; }
	#milestones-page .tab-pane { margin-top: 0 !important; }
	.referrals td { text-transform: capitalize; }
</style>
<?php

// REMOVE THIS FOR PROD
//ini_set('display_errors', '1');
//ini_set('display_startup_errors', '1');
//error_reporting(E_ALL);
// REMOVE THIS

function getCrystalVar($varname) {
  global $dbconn;
  
  $strSQL_start = 'SELECT chars.charid, charname, nation, value FROM char_vars INNER JOIN chars ON char_vars.charid = chars.charid WHERE gmlevel = 0 AND char_vars.varname = "';
  $strSQL_finish = '" > 0 ORDER BY value DESC LIMIT 25';
  $statement = $dbconn->prepare($strSQL_start . $varname . $strSQL_finish);
  
  if (!$statement->execute()) {
    watchdog($statement->errorInfo(),'SQL');
    return 'error';
  }
  else {
    $arrReturn = $statement->fetchAll();
    
    if (empty($arrReturn)) {
      return NULL;
    }
    else {
      return $arrReturn;
    }
  }
}

function getArrList($query) {
	global $dbconn;

  $statement = $dbconn->prepare($query);
  
  if (!$statement->execute()) {
    watchdog($statement->errorInfo(),'SQL');
    return 'error';
  }
  else {
    $arrReturn = $statement->fetchAll();
    
    if (empty($arrReturn)) {
      return NULL;
    }
    else {
      return $arrReturn;
    }
  }
}

function echoReferrals() {
    $result = getArrList('SELECT referral AS charname, COUNT(referral) AS referrals FROM accounts GROUP BY referral ORDER BY referrals DESC LIMIT 26');

    for ($i = 1; $i < 26; $i++) {
        echo '<tr>';

        positionMedals($i - 1);

        echo '<td>' . ($i) . '</td>';
		if ($result != NULL && isset($result[$i]) && !empty($result[$i][0])) {
			echo '<td></td>';
			echo '<td>' . $result[$i][0] . '</td>';
			echo '<td>' . $result[$i][1] . '</td>';
		}
		else {
			echo '<td></td>';
			echo '<td><a href="#"> - </a></td>';
			echo '<td> - </td>';
		}

		echo '</tr>';
	}
}

function echoMobKills() {
	$result = getArrList('SELECT mobname AS mob, mobfamily AS family, COUNT(mobname) AS kills, SUM(charlost) AS exp FROM char_deaths GROUP BY mobname ORDER BY kills DESC, exp DESC LIMIT 25');

	for ($i = 0; $i < 25; $i++) {
		echo '<tr>';

		positionMedals($i);

		echo '<td>' . ($i + 1) . '</td>';

		if ($result != NULL && isset($result[$i]) && !empty($result[$i][0])) {
			echo '<td><img src="images/mob-family/' . $result[$i][1] . '.jpg" class="board-icon" /></td>';
			echo '<td>' . str_replace('_', ' ', $result[$i][0]) . '</td>';
			echo '<td>' . $result[$i][2] . '</td>';
			echo '<td>' . number_format($result[$i][3]) . '</td>';
		}
		else {
			echo '<td></td>';
			echo '<td></td>';
			echo '<td><a href="#"> - </a></td>';
			echo '<td> - </td>';
		}

		echo '</tr>';
	}
}

function echoRecentDeaths() {
	$result = getArrList('SELECT charid, charname, charjob, charlevel, charlost, mobname, `time` FROM char_deaths ORDER BY time DESC LIMIT 25');

	$jobName = [
	    0 => "Unknown",
	    1 => "Warrior",
	    2 => "Monk",
	    3 => "White Mage",
	    4 => "Black Mage",
	    5 => "Red Mage",
	    6 => "Thief",
	    7 => "Paladin",
	    8 => "Dark Knight",
	    9 => "Beastmaster",
	    10 => "Bard",
	    11 => "Ranger",
	    12 => "Samurai",
	    13 => "Ninja",
	    14 => "Dragoon",
	    15 => "Summoner",
	    16 => "Blue Mage",
	    17 => "Corsair",
	    18 => "Puppetmaster",
	    19 => "Dancer",
	    20 => "Scholar",
	    21 => "Geomancer",
	    22 => "Runefencer",
	];

	for ($i = 0; $i < 25; $i++) {
		echo '<tr>';

		if ($result != NULL && isset($result[$i]) && !empty($result[$i][0])) {
			echo '<td><a href="/index.php?p=characters&id=' . $result[$i][0] . '">' . $result[$i][1] . '</a></td>';            // Name
			echo '<td><img src="images/job-icon/' . $result[$i][2] . '.png" title="' . $jobName[$result[$i][2]] . '" /></td>'; // Job
			echo '<td>' . $result[$i][3] . '</td>';                                                                            // Level
			echo '<td>' . number_format($result[$i][4]) . '</td>';                                                             // EXP lost
			echo '<td>' . str_replace('_', ' ', $result[$i][5]) . '</td>';                                                     // Mob
			echo '<td>' . $result[$i][6] . '</td>';                                                                            // Date
		}
		else {
			echo '<td> - </td>';
			echo '<td> - </td>';
			echo '<td> - </td>';
			echo '<td> - </td>';
			echo '<td> - </td>';
			echo '<td> - </td>';
		}

		echo '</tr>';
	}
}

function positionMedals($position) {
	echo '<td class="medal">';
	switch($position) {
		case 0: echo '<img src="images/gold.png" />'; break;
		case 1: echo '<img src="images/silver.png" />'; break;
		case 2: echo '<img src="images/bronze.png" />'; break;
		default: echo '&nbsp;'; break;
	}
	echo '</td>';
}

function echoListVar($varname, $format) {
	$result = getCrystalVar($varname);
	
	for ($i = 0; $i < 25; $i++) {
		echo '<tr>';
		
		positionMedals($i);

		echo '<td>' . ($i + 1) . '</td>';

		if ($result != NULL && isset($result[$i])) {
			echo '<td><img src="theme/v1/images/' . $result[$i][2] . '.jpg" class="board-icon" /></td>';
			echo '<td><a href="/index.php?p=characters&id=' . $result[$i][0] . '">' . $result[$i][1] . '</a></td>';

			switch ($format) {
				case "time":
					echo '<td>' . round($result[$i][3] / 3600, 2) . '</td>';
					break;
				case "craft":
					echo '<td>' . round($result[$i][3] / 10, 1) . '</td>';
					break;
				default:
					echo '<td>' . $result[$i][3] . '</td>';
					break;
			}
		}
		else {
			echo '<td><img src="theme/v1/images/3.jpg" class="board-icon" /></td>';
			echo '<td><a href="#"> - </a></td>';
			echo '<td> - </td>';
		}

		echo '</tr>';
	}
}

function charVarTable($id, $name, $heading, $varname, $format) {
	echo '<div class="leaderboard-content ' . $id . '">
				<!-- ' . $name . ' -->
				<table class="table">
				<thead>
					<tr>
					<th scope="col" class="medal"></th>
					<th scope="col" class="place">#</th>
					<th scope="col" class="flag"></th>
					<th scope="col" class="cname">Character</th>
					<th scope="col">' . $heading . '</th>
					</tr>
				</thead>
				<tbody>';
	echoListVar($varname, $format);
	echo '</tbody>
				</table>
				<!-- /' . $name . ' -->
			</div>';
}

function referralTable() {
	echo '<div class="leaderboard-content referrals">
				<!-- referrals -->
				<table class="table">
				<thead>
					<tr>
					<th scope="col" class="medal"></th>
					<th scope="col" class="place">#</th>
					<th scope="col" class="flag"></th>
					<th scope="col" class="cname">Character</th>
					<th scope="col">Referrals</th>
					</tr>
				</thead>
				<tbody>';
	echoReferrals();
	echo '</tbody>
				</table>
				<!-- /referrals -->
			</div>';
}

function mobKillTable() {
	echo '<div class="leaderboard-content mob-kills">
				<!-- mob-kills -->
				<table class="table">
				<thead>
					<tr>
					<th scope="col" class="medal"></th>
					<th scope="col" class="place">#</th>
					<th scope="col" class="flag"></th>
					<th scope="col">Mob Name</th>
					<th scope="col">Total Kills</th>
					<th scope="col">EXP Earned</th>
					</tr>
				</thead>
				<tbody>';
	echoMobKills();
  echo '</tbody>
				</table>
				<!-- /mob-kills -->
				</div>';
}

function recentDeathTable() {
	echo '<div class="leaderboard-content recent-deaths">
					<!-- recent-deaths -->
					<table class="table">
					<thead>
					<tr>
						<th scope="col">Player</th>
						<th scope="col">Job</th>
						<th scope="col">Level</th>
						<th scope="col">EXP</th>
						<th scope="col">Killer</th>
						<th scope="col">When</th>
					</tr>
					</thead>
					<tbody>';
	echoRecentDeaths();
	echo '  </tbody>
					</table>
					<!-- /recent-deaths -->
				</div>';
}
?>
<style>
.board-icon { width: 32px; height: 32px; }
#milestones, #explost, #spicytravels, #fastest75, #mobmostwanted { margin-top: 0 !important; }
th.medal { width: 32px; }
td.medal { text-align: center; }
th.flag, td.flag { width: 48px; }
th.cname, td.cname { width: 240px; }
th.place, td.place { width: 128px; }
.leaderboard-content {
	margin-top: 0; padding: 4px 16px 16px 16px;
	display: none;
	background-color: rgb(237, 237, 237);
	border-radius: 0 0 16px 16px;
}
.leaderboard-content { text-align: center; }
.leaderboard-content tbody tr { background-color: #fff; }
.leaderboard-content.milestones { display: block; }
#select-leaderboard {
	cursor: pointer;
	background-color: rgba(0, 0, 0, 0.03);
	border-width: 1px 1px 0 1px;
	border-radius: 8px 8px 0 0;
	margin-bottom: 0 !important;
	text-align: center;
}
#select-leaderboard:focus { box-shadow: none; border-color: rgb(206, 212, 218); }
#select-leaderboard .heading {
	text-align-last: center;
	font-weight: bold;
	background-color: #cfb8cc;
}
#select-leaderboard .heading:hover { background-color: #cfb8cc; }

/*
This doesn't work for some reason?

option.ucw {
	background-image: transparent url('theme/v1/images/cw/ucw.png');
  background-position: left center;
  background-repeat: no-repeat;
  padding-left: 20px;
}

option.ucw {
	background-image: transparent url('theme/v1/images/cw/cw.png');
  background-position: left center;
  background-repeat: no-repeat;
  padding-left: 20px;
}

option.combined {
	background-image: transparent url('theme/v1/images/cw/combined.png');
  background-position: left center;
  background-repeat: no-repeat;
  padding-left: 20px;
}
*/
#cw-header { text-align: center; }
</style>
<div class="container rounded" id="content">
	<div class="row">
		<div class="card mb-3 about-card">
			<div class="row g-0">
				<div class="col-md-4">
					<center><img src="images/NewLogo.png" width="65%" class="img-fluid start" alt="..."></center><br>
					<img src="images/NewByakko.png" class="img-fluid start" alt="...">
					<div class="row">
						<h3 class="text-center">Crystal Warrior</h3>
						<p>The CatsEyeXI private server is designed to provide players with a unique Final Fantasy XI experience that blends modern quality of life features with the nostalgic feel of the original game. The Crystal Warrior game mode is a new addition to the server that aims to provide a level of challenge for players who don’t align with all of our modern conveniences. </p>
						<p>Crystal Warrior is designed as a completely fresh experience, combining Hardcore, Self-Found and Classic 75-era style gameplay. This separate mode runs alongside existing CatsEyeXI players. </p>
					</div>
				</div>

				<div class="col-md-8">
					<div class="row p-3">
						<div class="card-body about-card" id="milestones-page" style="margin-left:1rem;">
							<select class="form-select form-select-lg mb-3" aria-label=".form-select-lg example" id="select-leaderboard">
								<option value="-" class="heading">Crystal Warrior</option>
								<option value="0" selected>Leaderboard - Milestones</option>
								<option value="1">Leaderboard - Fastest to 75</option>
								<option value="2">Leaderboard - Wealthiest</option>
								<option value="3">Leaderboard - Most Helpful</option>
								<option value="4">Leaderboard - Total Job Level</option>
								<option value="5">Leaderboard - Total Crafting</option>
								<option value="-" class="heading">Unbreakable Crystal Warrior</option>
								<option value="6" class="ucw">Leaderboard - Recent Deaths</option>
								<option value="7" class="ucw">Leaderboard - EXP Lost</option>
								<option value="8">Leaderboard - Mob: Most Wanted!</option>
								<option value="-" class="heading">Miscellaneous</option>
								<option value="9">Leaderboard - Spicy Travels</option>
								<option value="10">Leaderboard - Player Referrals</option>
							</select>
							<?php
								charVarTable("milestones",   "Milestones",      "Points",            "[CW]MILESTONES",       NULL);
								charVarTable("fastest75",    "Fastest to 75",   "Hours",             "[CW]TIME_TO_75",      "time");
								charVarTable("wealthiest",   "Wealthiest CW",   "Gil",               "[CW]GIL",              NULL);
								charVarTable("mosthelpful",  "Most Helpful",    "Missions Assisted", "[CW]HELPER",           NULL);
								charVarTable("totallevel",   "Total Job Level", "Levels",            "[CW]TOTAL_LEVEL",      NULL);
								charVarTable("totalcraft",   "Total Crafting",  "Levels",            "[CW]TOTAL_CRAFT",    "craft");
								// -
								recentDeathTable();
								charVarTable("explost",      "EXP Lost",        "EXP Lost",          "[CW]EXP_LOST",         NULL);
								mobKillTable();
								// -
								charVarTable("spicytravels", "Spicy Travels",   "Travels",           "[Domain]SpicyTravels", NULL);					
								referralTable();
							?>
						</div> <!-- /card-body -->
					</div>
				</div>
			</div>
		</div>
	</div>
</div> <!-- /#content -->
<script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.js"></script>
<script type="text/javascript">
$(document).ready(function () {
	$('#select-leaderboard').on('change', function() {
		if ($(this).val() == '-') {
			return
		}

		const content = $('.leaderboard-content')

		content.hide()
		content.eq($(this).val()).fadeIn()
	})
})
</script>
