<?php
class QuoteDatabase{
    var $dbAddress;
    var $dbUsername;
    var $dbPassword;
    var $dbDatabase;
    var $dbPort;
    var $sqlDb;
    var $debug;

    function QuoteDatabase($address, $user, $pw, $db, $port){
        $this->dbAddress  = $address;
        $this->dbUsername = $user;
        $this->dbPassword = $pw;
        $this->dbDatabase = $db;
        $this->dbPort     = $port;
        $this->debug      = true;
    }

    private function open(){
        $this->sqlDb = new mysqli(
            $this->dbAddress,
            $this->dbUsername,
            $this->dbPassword,
            $this->dbDatabase,
            $this->dbPort
        );

        // On error print Error Numer and Message
        if ($this->sqlDb->connect_errno) {
            echo 'Failed to connect to MySQL: (' . $this->sqlDb->connect_errno . ') ' . $this->sqlDb->connect_error;
        }
    }

    function getQuoteById($id) {
        // TODO: Validate input because SQLI
        $sqlQuery = $this->buildQuoteByIdQuery($id);

        $this->open(); // TODO: have to close?
        $result = $this->sqlDb->query($sqlQuery);

        if ($result === false) {
            echo "The Database returned an Error!\n";
            if(debug) die(mysql_error());

            die('An error occured, please try again later');
        }
        $quote = $this->parseResultsToQuote($result);

        return $quote;
    }

    function getRandomQuote(){
        $min = 1;
        $max = $this->getHighestId();
        $id = rand($min, $max);
        return $this->getQuoteById($id);
    }

    function customQuery($query) {
        $this->open();
        $result = $this->sqlDb->query($query);

        if ($result === false) {
            echo "The Database returned an Error!\n";
            if(debug) die(mysql_error());

            die('An error occured, please try again later');
        }

        return $result;
    }

    private function parseResultsToQuote($results) {
        while ($row = $results->fetch_array(MYSQLI_ASSOC)) $rows[] = $row;
        if(!isset($rows))
            return NULL;

        foreach ($rows as $row) {
            $quoteId        = $row['q_id'];
            $quoteYear      = $row['year'];
            $quoteSubmitter = $row['submitter'];
            $quoteGame      = $row['game'];
            $gameStyle      = $row['style'];

            $lineName       = $row['name'];
            $lineText       = $row['line'];
            $lineTeam       = $row['team'];
            $lineIndex      = $row['l_index'];

            $line = new QuoteLine ($lineName, $lineText, $lineTeam, $lineIndex);
            $lines[] = $line;
        }

        $q = new Quote($quoteId, $lines, $quoteYear, $quoteSubmitter, $quoteGame, $gameStyle);
        return $q;
    }

    function getHighestId() {
        $sqlStr = 'SELECT MAX(id) as id FROM quotes';
        $result = $this->customQuery($sqlStr);
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) $rows[] = $row;
        foreach($rows as $row) $id = $row['id'];
        return $id;
    }

    private function buildQuoteByIdQuery($id) {
        $sqlQuery = 'SELECT' .
            ' quotes.id              AS q_id        ,' .
            ' quotes.year            AS year        ,' .
            ' quotes.submitted_by    AS submitter   ,' .
            ' quote_lines.name       AS name        ,' .
            ' quote_lines.line       AS line        ,' .
            ' quote_lines.line_index AS l_index     ,' .
            ' teams.name             AS team        ,' .
            ' games.name             AS game        ,' .
            ' games.cssfile          AS style        ' .

            ' FROM' .
            ' quotes, quote_lines, games, teams      ' .

            ' WHERE quotes.id            = ' . $id .
            ' AND   quote_lines.quote_id = quotes.id ' .
            ' AND   quotes.game_id       = games.id  ' .
            ' AND   quote_lines.team_id  = teams.id  ' .

            ' ORDER BY quote_lines.line_index ASC    ' .
            ';';

        return $sqlQuery;
    }
}
?>