
<?php
$db = new SQLite3('./enpassant.db');
$res = $db->query('SELECT * FROM enpassant ORDER BY RANDOM() LIMIT 1')->fetchArray();
$res2 = $db->query('SELECT * FROM enpassant WHERE NumMoves in (SELECT MIN(CAST( `NumMoves` AS INTEGER)) from enpassant) ORDER BY RANDOM() LIMIT 1')->fetchArray();
$res3 = $db->query('SELECT * FROM enpassant WHERE NumMoves in (SELECT MAX(CAST( `NumMoves` AS INTEGER)) from enpassant) ORDER BY RANDOM() LIMIT 1')->fetchArray();
$res4 = $db->query('SELECT * FROM enpassant WHERE NumEnPassant in (SELECT MAX(CAST( `NumEnPassant` AS INTEGER)) from enpassant) ORDER BY RANDOM() LIMIT 1')->fetchArray();
$res5 = $db->query('SELECT * FROM enpassant WHERE WhiteELO+BlackELO in (SELECT MAX(WhiteELO+BlackELO) from enpassant) ORDER BY RANDOM() LIMIT 1')->fetchArray();
$res6 = $db->query('SELECT * FROM enpassant WHERE Crosscheck="True" ORDER BY RANDOM() LIMIT 1')->fetchArray();
$res7 = $db->query('SELECT COUNT(1) FROM enpassant')->fetchArray();
$res8q = $db->query('SELECT NumMoves,COUNT(NumMoves) FROM enpassant GROUP BY NumMoves ORDER BY NumMoves');
$res8 = [];
while ($row = $res8q->fetchArray(SQLITE3_NUM)){
	$res8[$row[0]]=$row[1];
}
$res9q = $db->query('SELECT ROUND(WhiteELO/100.0-0.5)*100 AS ELO,COUNT(*) AS [ELO Count] FROM enpassant GROUP BY ELO ORDER BY 1');
$res9 = [];
while ($row = $res9q->fetchArray(SQLITE3_NUM)){
	$res9[$row[0]]=$row[1];
}
$res10q = $db->query('SELECT ROUND(BlackELO/100.0-0.5)*100 AS ELO,COUNT(*) AS [ELO Count] FROM enpassant GROUP BY ELO ORDER BY 1');
$res10 = [];
while ($row = $res10q->fetchArray(SQLITE3_NUM)){
	$res10[$row[0]]=$row[1];
}

$res11q = $db->query('SELECT LastMove,COUNT(LastMove) FROM enpassant GROUP BY LastMove ORDER BY LastMove');
$res11 = [];
while ($row = $res11q->fetchArray(SQLITE3_NUM)){
	$res11[$row[0]]=$row[1];
}
$res12 = $db->query('SELECT COUNT(1) FROM enpassant WHERE Result="1-0"')->fetchArray();
$json=array(
	"randgame"=>$res['Moves'], 
	"randlink"=>$res['Link'], 
	"shortgame"=>$res2['Moves'],
	"shortlink"=>$res2['Link'],
	"longgame"=>$res3['Moves'],
	"longlink"=>$res3['Link'],
	"mostgame"=>$res4['Moves'],
	"mostlink"=>$res4['Link'],
	"highgame"=>$res5['Moves'],
	"highlink"=>$res5['Link'],
	"crossgame"=>$res6['Moves'],
	"crosslink"=>$res6['Link'],
	"gamecount"=>$res7[0],
	"movehist"=>$res8,
	"whiteelo"=>$res9,
	"blackelo"=>$res10,
	"lastmove"=>$res11,
	"totalgames"=>file_get_contents("totalgames"),
	"whitewin"=>$res12[0]
);
echo json_encode($json);
?>