<?php

require_once("base.php");

$dbfilename = "schools/$schoolYear/votes.sqlite3";

if(realpath($dbfilename) != "")
	$dbfile = realpath($dbfilename);
else $dbfile = $dbfilename;

if(defined('DELETE_DATABASE') && file_exists($dbfile))
    unlink($dbfile);

$dsn = "sqlite:" . ($dbfile);

if(DEBUG) echo "dbfilename is $dbfilename  ; <br>\n dns : $dsn\n<br>\n";

if(!isset($IGNORE_MISSING_DB) && ! (is_readable($dbfilename) && is_writable($dbfilename)) ) {
    $restart = link_to('/restart');
    echo "Try recreating the database: <a href=\"$restart\">restart</a><br />\n";
    die("Could not find a usable database. Aborting.");
} else {
    $dbh = new PDO($dsn);

    if($dbh === NULL) { // database not available; let's try restarting
        die("Could not attach to database. Aborting.");
    }
}

$votes_table      = $dbh->quote("{$election}_votes");
$categories_table = $dbh->quote("{$election}_categories");

//
// Convenient wrapper function to get an array of results from a SQL query
function SQLArrayQuery($query, $vars = false, $fetch_style = PDO::FETCH_BOTH, $column = 0) {
    global $dbh;
    if(!$vars) $vars = array();
    $sth = $dbh->prepare($query);
    if(!$sth) {
        echo "bad query!<br>\n";
        echo "<pre>$query</pre>";
        print_r( $dbh->errorInfo() );
    }
    $rv = $sth->execute($vars);
    if(!$rv) die("could not execute!");
    if($fetch_style == PDO::FETCH_COLUMN)
		$result =	$sth->fetchAll($fetch_style, $column);
    else	$result =	$sth->fetchAll($fetch_style);  // passing column for a style other than _COLUMN results in an error
    if(DEBUG){
        var_dump($query);
        var_dump($vars);
        var_dump($result);
    }
    return $result;
}

//
// Wrapper function for result-less SQL queries
// On success, returns the second param prefixed by # of changed rows (according to db) 
function SQLExec($query, $message = "") {
	global $dbh;
	$result = $dbh->exec($query);
	return ($result !== FALSE) ? "$result $message" : print_r($dbh->errorInfo());
}
//
// Returns a string title
function getCategoryTitle($category_id) {
	global $categories_table;
	$query = "select description from $categories_table where category_id = ?";
	$titles = SQLArrayQuery($query, array($category_id), PDO::FETCH_COLUMN, 0);
	return $titles[0];
}

//
// returns an array of categories
function getCategories() { // default to all categories
	global $categories_table;
	$query = "select description from $categories_table;";
	return SQLArrayQuery($query, null, PDO::FETCH_COLUMN);
}

function authenticate($given_lastname = "", $given_student_id = "") {
	global $dbh;
	// To authenticate, we require (a given lastname or a posted lastname), and (a given student id, or posted (student id or hash)).
	// given lastname OR given stid are empty AND didn't post stid and hash, OR didn't post lastname
	if((	($given_lastname == "" || $given_student_id == "")
	      &&   (!isset($_POST['student_id']) && !isset($_POST['student_hash']))
	      || !isset($_POST['lastname']))
	) {
		if(DEBUG) {
			echo ($given_lastname == "" || $given_student_id == "") ? 't' : 'f';
			echo (!isset($_POST['student_id']) && !isset($_POST['student_hash'])) ? 't' : 'f';
			echo !isset($_POST['lastname'])? 't' : 'f';
			print_r($_POST);
			print_r($_GET);
		}
		die("Could not authenticate -- please go back and provide a valid last name and student ID.\n--" . isset($given_lastname));
	} else {			// htmlentities(urlencode($userinput))
		if($given_lastname == "") 	$given_lastname = $_POST['lastname'];
		if(is_array($given_lastname)) $given_lastname = $given_lastname['null'];
		if($given_student_id == "")	
				$given_student_id = isset($_POST['student_id'])
								? getHash($_POST['student_id'])
								: $_POST['student_hash'];
		unset($_POST['lastname']);
                unset($_POST['student_id']);

		$query = "select lastname, gender from students where student_id = ?;";
		$result = SQLArrayQuery($query, array($given_student_id)); // fetch both
	       // echo $given_student_id;
                if(sizeof($result) == 0) {
                    echo "\n<br><pre>\n";
                    $sid = "Mickey";
                    $sth = $dbh->prepare("select * from students where firstname = ?;",
                         array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
                    $rv = $sth->execute(array($sid));
                    //$sth = $dbh->prepare("select * from students where lastname = ?;");
                    //$rv = $sth->execute(array("Mouse"));
                    var_dump($rv);
                    var_dump($sth->fetchAll());
                    print $dbh->errorCode();
                    echo "\n<br></pre>\n";
                    die("No student with that ID could be found.");
                }
		
		list($lastname, $gender) = $result[0];
		
        // XXX might be too restrictive
		if(!isset($lastname)
		|| ($given_lastname != $lastname)) {
			die("The lastname you provided does not match the student ID. $given_lastname vs $lastname");
		}
	}
	
	return array($given_lastname, $given_student_id, $gender);
}


$studentLists = array();

function getStudentList($vote_id = 'null', $n = 0, $category = '', $weight = '', $gender = -1, $select = 0, $grade = 12, $use_lastname = true, $use_cache = false) {
	global $election;
	global $dbh;
	global $studentLists;
	
	
	@$restrictions = config("$election/restrictions");
	if(empty($restrictions))
		$restrictions = array();
	else $restrictions = array($restrictions);
	
	if($gender != -1) {
		$restrictions[] = "gender = '$gender'";
        }
        $restrictions[] = "grade = $grade";
	
	$restrictions = implode("\nAND ", $restrictions);
	if(!empty($restrictions)) $restrictions = "WHERE $restrictions";
	else $restrictions = '--'; // SQL comment is ignored, but still serves as array key
	
	$sid = "${category}_${weight}_${gender}[$n]";


        $html = "<select name=\"{$sid}[$vote_id]\" id=\"$sid\" onchange=\"return selected(event);\">";
        if($grade == 18) {
            $html .= "\n<option value=\"0\">Please pick a teacher</option>\n";
        } else {
            $html .= "\n<option value=\"0\">Please pick a student</option>\n";
        }

	// This is an optimization for the common case where we aren't restoring votes.
	// We avoid doing votes*2*categories (~120) DB queries which
	// results in an order of magnitude speed increase
	$studentList = "";
	if($select == 0 && array_key_exists($restrictions, $studentLists)) {
		$studentList = strval($studentLists[$restrictions]);
	} else {
		$__selected = ' selected="true"';
		
		// build candidates array, sorted alphabetically by last name
		$query = "
SELECT student_id, firstname, lastname
FROM students
$restrictions
ORDER BY lastname ASC, firstname ASC;";
		if(DEBUG) echo "<pre>$query</pre>";
		if(DEBUG) $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

		foreach($dbh->query($query) as $candidate) {
			$id = $candidate['student_id'];
			$selected = ($id == $select) ? $__selected : '';
			if($use_lastname) $id = $candidate['lastname'];
			$studentList .= "<option value=\"$id\"$selected>{$candidate['lastname']}, {$candidate['firstname']}</option>\n";
		}
		
		if($select == 0 && !array_key_exists($restrictions, $studentLists))
			$studentLists[$restrictions] = $studentList;
	}
	
	return "$html\n$studentList\n</select>\n";
}

?>