<?php
/**
 * Creator.php
 * 
 * A pure PHP SQL creator, which generates SQL from the output of PHPSQLParser.
 * 
 * Copyright (c) 2012, André Rothe <arothe@phosco.info, phosco@gmx.de>
 * with contributions by Dan Vande More <bigdan@gmail.com>
 *
 * 
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without modification, 
 * are permitted provided that the following conditions are met:
 *
 *   * Redistributions of source code must retain the above copyright notice, 
 *     this list of conditions and the following disclaimer.
 *   * Redistributions in binary form must reproduce the above copyright notice, 
 *     this list of conditions and the following disclaimer in the documentation 
 *     andgo/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY 
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT 
 * SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED 
 * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR 
 * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 
 * ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH 
 * DAMAGE.
 */

namespace PHPSQL;

class Creator {

	public $created;

    public function __construct($parsed = false) {
        if ($parsed) {
            $this->create($parsed);
        }
    }

    public function create($parsed) {
        $k = key($parsed);
        switch ($k) {
	        case "UNION":
	        case "UNION ALL":
	            throw new \PHPSQL\Exception\UnsupportedFeature($k);
	            break;
	        case "SELECT":
	            $this->created = $this->processSelectStatement($parsed);
	            break;
	        case "INSERT":
	            $this->created = $this->processInsertStatement($parsed);
	            break;
	        case "DELETE":
	            $this->created = $this->processDeleteStatement($parsed);
	            break;
	        case "UPDATE":
	            $this->created = $this->processUpdateStatement($parsed);
	            break;
	        default:
	            throw new \PHPSQL\Exception\UnsupportedFeature($k);
	            break;
        }
        return $this->created;
    }

    protected function processSelectStatement($parsed) {
        $sql = $this->processSELECT($parsed['SELECT']) . " " . $this->processFROM($parsed['FROM']);
        if (isset($parsed['WHERE'])) {
            $sql .= " " . $this->processWHERE($parsed['WHERE']);
        }
        if (isset($parsed['GROUP'])) {
            $sql .= " " . $this->processGROUP($parsed['GROUP']);
        }
        if (isset($parsed['ORDER'])) {
            $sql .= " " . $this->processORDER($parsed['ORDER']);
        }
        if (isset($parsed['LIMIT'])) {
            $sql .= " " . $this->processLIMIT($parsed['LIMIT']);
        }
        return $sql;
    }

    protected function processInsertStatement($parsed) {
        return $this->processINSERT($parsed['INSERT']) . " " . $this->processVALUES($parsed['VALUES']);
        # TODO: subquery?
    }

    protected function processDeleteStatement($parsed) {
        return $this->processDELETE($parsed['DELETE']) . " " . $this->processFROM($parsed['FROM']) . " "
                . $this->processWHERE($parsed['WHERE']);
    }

    protected function processUpdateStatement($parsed) {
        $sql = $this->processUPDATE($parsed['UPDATE']) . " " . $this->processSET($parsed['SET']);
        if (isset($parsed['WHERE'])) {
            $sql .= " " . $this->processWHERE($parsed['WHERE']);
        }
        return $sql;
    }

    protected function processDELETE($parsed) {
        $sql = "DELETE";
        foreach ($parsed['TABLES'] as $k => $v) {
            $sql .= $v . ",";
        }
        return substr($sql, 0, -1);
    }

    protected function processSELECT($parsed) {
        $sql = "";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processColRef($v);
            $sql .= $this->processSelectExpression($v);
            $sql .= $this->processFunction($v);
            $sql .= $this->processConstant($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('SELECT', $k, $v, 'expr_type');
            }

            $sql .= ",";
        }
        $sql = substr($sql, 0, -1);
        return "SELECT " . $sql;
    }

    protected function processFROM($parsed) {
        $sql = "";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processTable($v, $k);
            $sql .= $this->processTableExpression($v, $k);
            $sql .= $this->processSubquery($v, $k);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('FROM', $k, $v, 'expr_type');
            }

            $sql .= " ";
        }
        return "FROM " . substr($sql, 0, -1);
    }

    protected function processORDER($parsed) {
        $sql = "";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processOrderByAlias($v);
            $sql .= $this->processColRef($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('ORDER', $k, $v, 'expr_type');
            }

            $sql .= ",";
        }
        $sql = substr($sql, 0, -1);
        return "ORDER BY " . $sql;
    }

    protected function processLIMIT($parsed) {
        $sql = ($parsed['offset'] ? $parsed['offset'] . ", " : "") . $parsed['rowcount'];
        if ($sql === "") {
            throw new \PHPSQL\Exception\UnableToCreateSQL('LIMIT', 'rowcount', $parsed, 'rowcount');
        }
        return "LIMIT " . $sql;
    }

    protected function processGROUP($parsed) {
        $sql = "";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processColRef($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('GROUP', $k, $v, 'expr_type');
            }

            $sql .= ",";
        }
        $sql = substr($sql, 0, -1);
        return "GROUP BY " . $sql;
    }

    protected function processRecord($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::RECORD) {
            return "";
        }
        $sql = "";
        foreach ($parsed['data'] as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processConstant($v);
            $sql .= $this->processFunction($v);
            $sql .= $this->processOperator($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL(\PHPSQL\Expression\Type::RECORD, $k, $v, 'expr_type');
            }

            $sql .= ",";
        }
        $sql = substr($sql, 0, -1);
        return "(" . $sql . ")";

    }

    protected function processVALUES($parsed) {
        $sql = "";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processRecord($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('VALUES', $k, $v, 'expr_type');
            }

            $sql .= ",";
        }
        $sql = substr($sql, 0, -1);
        return "VALUES " . $sql;
    }

    protected function processINSERT($parsed) {
        $sql = "INSERT INTO " . $parsed['table'];

        if ($parsed['columns'] === false) {
            return $sql;
        }

        $columns = "";
        foreach ($parsed['columns'] as $k => $v) {
            $len = strlen($columns);
            $columns .= $this->processColRef($v);

            if ($len == strlen($columns)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('INSERT[columns]', $k, $v, 'expr_type');
            }

            $columns .= ",";
        }

        if ($columns !== "") {
            $columns = " (" . substr($columns, 0, -1) . ")";
        }

        $sql .= $columns;
        return $sql;
    }

    protected function processUPDATE($parsed) {
        return "UPDATE " . $parsed[0]['table'];
    }

    protected function processSetExpression($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::EXPRESSION) {
            return "";
        }
        $sql = "";
        foreach ($parsed['sub_tree'] as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processColRef($v);
            $sql .= $this->processConstant($v);
            $sql .= $this->processOperator($v);
            $sql .= $this->processFunction($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('SET expression subtree', $k, $v, 'expr_type');
            }

            $sql .= " ";
        }

        $sql = substr($sql, 0, -1);
        return $sql;
    }

    protected function processSET($parsed) {
        $sql = "";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processSetExpression($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('SET', $k, $v, 'expr_type');
            }

            $sql .= ",";
        }
        return "SET " . substr($sql, 0, -1);
    }

    protected function processWHERE($parsed) {
        $sql = "WHERE ";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);

            $sql .= $this->processOperator($v);
            $sql .= $this->processConstant($v);
            $sql .= $this->processColRef($v);
            $sql .= $this->processSubquery($v);
            $sql .= $this->processInList($v);
            $sql .= $this->processFunction($v);
            $sql .= $this->processWhereExpression($v);
            $sql .= $this->processWhereBracketExpression($v);

            if (strlen($sql) == $len) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('WHERE', $k, $v, 'expr_type');
            }

            $sql .= " ";
        }
        return substr($sql, 0, -1);
    }

    protected function processWhereExpression($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::EXPRESSION) {
            return "";
        }
        $sql = "";
        foreach ($parsed['sub_tree'] as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processColRef($v);
            $sql .= $this->processConstant($v);
            $sql .= $this->processOperator($v);
            $sql .= $this->processInList($v);
            $sql .= $this->processFunction($v);
            $sql .= $this->processWhereExpression($v);
            $sql .= $this->processWhereBracketExpression($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('WHERE expression subtree', $k, $v, 'expr_type');
            }

            $sql .= " ";
        }

        $sql = substr($sql, 0, -1);
        return $sql;
    }

    protected function processWhereBracketExpression($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::BRACKET_EXPRESSION) {
            return "";
        }
        $sql = "";
        foreach ($parsed['sub_tree'] as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processColRef($v);
            $sql .= $this->processConstant($v);
            $sql .= $this->processOperator($v);
            $sql .= $this->processInList($v);
            $sql .= $this->processFunction($v);
            $sql .= $this->processWhereExpression($v);
            $sql .= $this->processWhereBracketExpression($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('WHERE expression subtree', $k, $v, 'expr_type');
            }

            $sql .= " ";
        }

        $sql = "(" . substr($sql, 0, -1) . ")";
        return $sql;
    }

    protected function processOrderByAlias($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::ALIAS) {
            return "";
        }
        return $parsed['base_expr'] . $this->processDirection($parsed['direction']);
    }

    protected function processLimitRowCount($key, $value) {
        if ($key != 'rowcount') {
            return "";
        }
        return $value;
    }

    protected function processLimitOffset($key, $value) {
        if ($key !== 'offset') {
            return "";
        }
        return $value;
    }

    protected function processFunction($parsed) {
        if (($parsed['expr_type'] !== \PHPSQL\Expression\Type::AGGREGATE_FUNCTION)
                && ($parsed['expr_type'] !== \PHPSQL\Expression\Type::SIMPLE_FUNCTION)) {
            return "";
        }

        if ($parsed['sub_tree'] === false) {
            return $parsed['base_expr'] . "()";
        }

        $sql = "";
        foreach ($parsed['sub_tree'] as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processFunction($v);
            $sql .= $this->processConstant($v);
            $sql .= $this->processColRef($v);
            $sql .= $this->processReserved($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('function subtree', $k, $v, 'expr_type');
            }

            $sql .= ($this->isReserved($v) ? " " : ",");
        }
        return $parsed['base_expr'] . "(" . substr($sql, 0, -1) . ")";
    }

    protected function processSelectExpression($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::EXPRESSION) {
            return "";
        }
        $sql = $this->processSubTree($parsed, " ");
        $sql .= $this->processAlias($parsed['alias']);
        return $sql;
    }

    protected function processSelectBracketExpression($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::BRACKET_EXPRESSION) {
            return "";
        }
        $sql = $this->processSubTree($parsed, " ");
        $sql = "(" . $sql . ")";
        return $sql;
    }

    protected function processSubTree($parsed, $delim = " ") {
        if ($parsed['sub_tree'] === '') {
            return "";
        }
        $sql = "";
        foreach ($parsed['sub_tree'] as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processFunction($v);
            $sql .= $this->processOperator($v);
            $sql .= $this->processConstant($v);
            $sql .= $this->processSubQuery($v);
            $sql .= $this->processSelectBracketExpression($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('expression subtree', $k, $v, 'expr_type');
            }

            $sql .= $delim;
        }
        return substr($sql, 0, -1);
    }

    protected function processRefClause($parsed) {
        if ($parsed === false) {
            return "";
        }

        $sql = "";
        foreach ($parsed as $k => $v) {
            $len = strlen($sql);
            $sql .= $this->processColRef($v);
            $sql .= $this->processOperator($v);
            $sql .= $this->processConstant($v);

            if ($len == strlen($sql)) {
                throw new \PHPSQL\Exception\UnableToCreateSQL('expression ref_clause', $k, $v, 'expr_type');
            }

            $sql .= " ";
        }
        return "(" . substr($sql, 0, -1) . ")";
    }

    protected function processAlias($parsed) {
        if ($parsed === false) {
            return "";
        }
        $sql = "";
        if ($parsed['as']) {
            $sql .= " as";
        }
        $sql .= " " . $parsed['name'];
        return $sql;
    }

    protected function processJoin($parsed) {
        if ($parsed === 'CROSS') {
            return ",";
        }
        if ($parsed === 'JOIN') {
            return "INNER JOIN";
        }
        if ($parsed === 'LEFT') {
            return "LEFT JOIN";
        }
        if ($parsed === 'RIGHT') {
            return "RIGHT JOIN";
        }
        // TODO: add more
        throw new \PHPSQL\Exception\UnsupportedFeature($parsed);
    }

    protected function processRefType($parsed) {
        if ($parsed === false) {
            return "";
        }
        if ($parsed === 'ON') {
            return " ON ";
        }
        if ($parsed === 'USING') {
            return " USING ";
        }
        // TODO: add more
        throw new \PHPSQL\Exception\UnsupportedFeature($parsed);
    }

    protected function processTable($parsed, $index) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::TABLE) {
            return "";
        }

        $sql = $parsed['table'];
        $sql .= $this->processAlias($parsed['alias']);

        if ($index !== 0) {
            $sql = $this->processJoin($parsed['join_type']) . " " . $sql;
            $sql .= $this->processRefType($parsed['ref_type']);
            $sql .= $this->processRefClause($parsed['ref_clause']);
        }
        return $sql;
    }

    protected function processTableExpression($parsed, $index) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::TABLE_EXPRESSION) {
            return "";
        }
        $sql = substr($this->processFROM($parsed['sub_tree']), 5); // remove FROM keyword
        $sql = "(" . $sql . ")";
        $sql .= $this->processAlias($parsed['alias']);

        if ($index !== 0) {
            $sql = $this->processJoin($parsed['join_type']) . " " . $sql;
            $sql .= $this->processRefType($parsed['ref_type']);
            $sql .= $this->processRefClause($parsed['ref_clause']);
        }
        return $sql;
    }

    protected function processSubQuery($parsed, $index = 0) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::SUBQUERY) {
            return "";
        }

        $sql = $this->processSelectStatement($parsed['sub_tree']);
        $sql = "(" . $sql . ")";

        if (isset($parsed['alias'])) {
            $sql .= $this->processAlias($parsed['alias']);
        }

        if ($index !== 0) {
            $sql = $this->processJoin($parsed['join_type']) . " " . $sql;
            $sql .= $this->processRefType($parsed['ref_type']);
            $sql .= $this->processRefClause($parsed['ref_clause']);
        }
        return $sql;
    }

    protected function processOperator($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::OPERATOR) {
            return "";
        }
        return $parsed['base_expr'];
    }

    protected function processColRef($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::COLREF) {
            return "";
        }
        $sql = $parsed['base_expr'];
        if (isset($parsed['alias'])) {
            $sql .= $this->processAlias($parsed['alias']);
        }
        if (isset($parsed['direction'])) {
            $sql .= $this->processDirection($parsed['direction']);
        }
        return $sql;
    }

    protected function processDirection($parsed) {
        $sql = ($parsed ? " " . $parsed : "");
        return $sql;
    }

    protected function processReserved($parsed) {
        if (!$this->isReserved($parsed)) {
            return "";
        }
        return $parsed['base_expr'];
    }

    protected function isReserved($parsed) {
        return ($parsed['expr_type'] === \PHPSQL\Expression\Type::RESERVED);
    }

    protected function processConstant($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::CONSTANT) {
            return "";
        }
        return $parsed['base_expr'];
    }

    protected function processInList($parsed) {
        if ($parsed['expr_type'] !== \PHPSQL\Expression\Type::IN_LIST) {
            return "";
        }
        $sql = $this->processSubTree($parsed, ",");
        return "(" . $sql . ")";
    }

}
