<?php
/**
 * A class for assembling sql queries (sql92 standard).
 *
 * This class provides a way to assemble a sql query, segment by segment, using
 * a fluent interface.
 */
class db_query
{
    /**
     * @var db_connection The database connection object.
     */
    protected $connection;
    
    /**
     * @var array The sql segments.
     */
    protected $action, $table, $distinct, $using, $column, $join, $where, $group, $having, $order, $limit, $offset;
    
    ////////////////////////////////////////////////////////////////////////////
    // Construct and destruct functions.

    /**
     * @param db_connection The db connection.
     */
    public function __construct(db_connection $connection)
    {
        $this->connection = $connection;
    }
    
    ////////////////////////////////////////////////////////////////////////////
    // Overload functions.

    ////////////////////////////////////////////////////////////////////////////
    // Interface functions.
    
    /**
     * Executes the query.
     *
     * @param array (optional) $data The parameters.
     * @return db_statement The query result.
     */
    public function execute(array $data = array())
    {
        return $this->connection->query($this->render(), $data);
    }

    /**
     * Renders the query as sql.
     *
     * @throws logicexception If the action segment is not defined.
     * @return string The rendered sql.
     */
    public function render()
    {
        if (!$this->action)
        {
            throw new logicexception('Action segment is not defined.');
        }
        
        if ($this->action === 'select' && !$this->column)
        {
            $this->column('*');
        }
        
        $segment = array();
        
        foreach (array('action', 'column', 'join', 'where', 'group', 'having', 'order', 'limit', 'offset') as $s)
        {
            if ($this->$s !== null)
            {
                $segment[] = $this->{'render_' . $s}();
            }
        }
        
        return implode(' ', $segment);
    }

    /**
     * SELECT column1 FROM $table
     * 
     * @param string/stdclass $table[, ...] The table name (identifier).
     * @throws logicexception If the query already has an action segment.
     * @return db_query A fluent interface.
     */
    public function select($table)
    {
        if ($this->action && $this->action !== 'select')
        {
            throw new logicexception('Query already has an action segment.');
        }
        
        $this->action = 'select';
        $table = func_get_args();
        self::merge_distinct($this->table, $table);
        return $this;
    }

    /**
     * INSERT INTO $table (column1) VALUE (value1)
     * 
     * @param string/stdclass $table The table name (identifier).
     * @throws logicexception If the query already has an action segment.
     * @return db_query A fluent interface.
     */
    public function insert($table)
    {
        if ($this->action && $this->action !== 'insert')
        {
            throw new logicexception('Query already has an action segment.');
        }
        
        $this->action = 'insert';
        $this->table = $table;
        return $this;
    }
    
    /**
     * UPDATE $table SET column1 = value1
     * 
     * @param string/stdclass $table[, ...] The table name (identifier).
     * @throws logicexception If the query already has an action segment.
     * @return db_query A fluent interface.
     */
    public function update($table)
    {
        if ($this->action && $this->action !== 'update')
        {
            throw new logicexception('Query already has an action segment.');
        }
        
        $this->action = 'update';
        $table = func_get_args();
        self::merge_distinct($this->table, $table);
        return $this;
    }
    
    /**
     * DELETE FROM $table
     * 
     * @param string/stdclass $table[, ...] The table name (identifier).
     * @throws logicexception If the query already has an action segment.
     * @return db_query A fluent interface.
     */
    public function delete($table)
    {
        if ($this->action && $this->action !== 'delete')
        {
            throw new logicexception('Query already has an action segment.');
        }
        
        $this->action = 'delete';
        $table = func_get_args();
        self::merge_distinct($this->table, $table);
        return $this;
    }
    
    /**
     * SELECT [DISTINCT] column1 FROM table1
     * 
     * @param boolean (optional) $distinct Whether to select distinct or not.
     * @return db_query A fluent interface.
     */
    public function distinct($distinct = true)
    {
        $this->distinct = $distinct;
        return $this;
    }
    
    /**
     * DELETE FROM table1 USING $table
     * 
     * @param string/stdclass $table The table name (identifier).
     * @return db_query A fluent interface.
     */
    public function using($table)
    {
        $this->using = $table;
        return $this;
    }
    
    /**
     * SELECT $column FROM table1
     * INSERT INTO table1 ($column) VALUE ($value)
     * UPDATE table1 SET $column = $value
     * 
     * @param string/stdclass $column[, ...] The column name (identifier).
     * -
     * @param string $column The column name (identifier).
     * @param mixed $value The value (literal).
     * -
     * @param array $column The columns (identifier => literal).
     * @throws logicexception If the action segment is not defined.
     * @throws logicexception If the action does not support columns.
     * @return db_query A fluent interface.
     */
    public function column($column, $value = null)
    {
        if (!$this->action)
        {
            throw new logicexception('Action segment is not defined.');
        }
        
        switch ($this->action)
        {
            case 'select' :
                
                $column = func_get_args();
                self::merge_distinct($this->column, $column);
                break;
                
            case 'insert' :
            case 'update' :
                
                if (!$this->column)
                {
                    $this->column = array();
                }
                
                if (is_array($column))
                {
                    $this->column = array_merge($this->column, $column);
                }
                else
                {
                    $this->column[$column] = $value;
                }
                
                break;
                
            default :
                
                throw new logicexception('Action ' . $this->action . ' does not support a column segment.');
        }
        
        return $this;
    }
    
    /**
     * SELECT column1 FROM table1 [$type] JOIN $table ON $condition
     * 
     * @param string/stdclass $table The table name (identifier).
     * @param array $condition The condition ([operator], identifier, identifier).
     * @param string (optional) $type The type.
     * @throws lengthexception If the condition is not valid.
     * @return db_query A fluent interface.
     */
    public function join($table, array $condition, $type = null)
    {
        $count = count($condition);
        
        if ($count < 2)
        {
            throw new lengthexception('Join condition is not valid.');
        }
        
        if ($count === 2)
        {
            if (is_array($condition[1]))
            {
                array_unshift($condition, 'in');
            }
            else
            {
                array_unshift($condition, '=');
            }
        }
        
        if (!$condition[2] instanceof stdclass && !$condition[2] instanceof db_query && !is_array($condition[2]))
        {
            $condition[2] = (object) array('type' => 'identifier', 'value' => $condition[2]);
        }
        
        $this->join[] = array('table' => $table, 'condition' => $condition, 'type' => $type);
        return $this;
    }

    /**
     * SELECT column1 FROM table1 WHERE ($left $condition $right)
     *
     * @param string $condition The operator.
     * @param string/stdclass $left The left operand (identifier).
     * @param mixed $right The right operand (literal).
     * -
     * @param string/stdclass $condition The left operand (identifier).
     * @param mixed $left The right operand (literal).
     * @throws lengthexception If the condition is not valid.
     * @return db_query A fluent interface.
     */
    public function where($condition, $left, $right = null)
    {
        $condition = func_get_args();
        $count = count($condition);
        
        if ($count < 2)
        {
            throw new lengthexception('Where condition is not valid.');
        }
        
        if ($count === 2)
        {
            if (is_array($condition[1]))
            {
                array_unshift($condition, 'in');
            }
            else
            {
                array_unshift($condition, '=');
            }
        }
        
        if ($this->where)
        {
            if ($this->where[0] === 'and')
            {
                $this->where[] = $condition;
            }
            else
            {
                $this->where = array('and', $this->where, $condition);
            }
        }
        else
        {
            $this->where = $condition;
        }
        
        return $this;
    }
    
    /**
     * SELECT COUNT(column1) FROM table1 GROUP BY $column
     * 
     * @param string/stdclass $column The column name (identifier).
     * @return db_query A fluent interface.
     */
    public function group($column)
    {
        $column = func_get_args();
        self::merge_distinct($this->group, $column);
        return $this;
    }
    
    /**
     * SELECT column1 FROM table1 HAVING ($left $condition $right)
     *
     * @param string $condition The operator.
     * @param string/stdclass $left The left operand (identifier).
     * @param mixed $right The right operand (literal).
     * -
     * @param string/stdclass $condition The left operand (identifier).
     * @param mixed $left The right operand (literal).
     * @throws lengthexception If the condition is not valid.
     * @return db_query A fluent interface.
     */
    public function having($condition, $left, $right = null)
    {
        $condition = func_get_args();
        $count = count($condition);
        
        if ($count < 2)
        {
            throw new lengthexception('Having condition is not valid.');
        }
        
        if ($count === 2)
        {
            if (is_array($condition[1]))
            {
                array_unshift($condition, 'in');
            }
            else
            {
                array_unshift($condition, '=');
            }
        }
        
        if ($this->having)
        {
            if ($this->having[0] === 'and')
            {
                $this->having[] = $condition;
            }
            else
            {
                $this->having = array('and', $this->having, $condition);
            }
        }
        else
        {
            $this->having = $condition;
        }
        
        return $this;
    }
    
    /**
     * SELECT column1 FROM table1 ORDER BY $column [$order]
     * 
     * @param string/stdclass $column The column name (identifier).
     * @param string $order The order type.
     * @return db_query A fluent interface.
     */
    public function order($column, $order = null)
    {
        if (!$this->order)
        {
            $this->order = array(array(), array());
        }
        
        $key = array_search($column, $this->order[0]);
        
        if ($key !== false)
        {
            $this->order[0][$key] = $column;
            $this->order[1][$key] = $order;
        }
        else
        {
            $this->order[0][] = $column;
            $this->order[1][] = $order;
        }
        
        return $this;
    }
    
    /**
     * SELECT column1 FROM table1 LIMIT $limit
     * 
     * @param integer/stdclass $limit The limit (raw).
     * @return db_query A fluent interface.
     */
    public function limit($limit)
    {
        $this->limit = $limit;
        return $this;
    }
    
    /**
     * SELECT column1 FROM table1 OFFSET $offset
     * 
     * @param integer/stdclass $offset The offset (raw).
     * @return db_query A fluent interface.
     */
    public function offset($offset)
    {
        $this->offset = $offset;
        return $this;
    }
    
    /**
     * Clears one or all sql segments.
     * 
     * @param string (optional) $segment[, ...] The segment name.
     * @throws outofrangeexception If the segment does not exist.
     * @return db_query A fluent interface.
     */
    public function clear($segment = null)
    {
        if ($segment === null)
        {
            $segment = array_keys(get_object_vars($this));
        }
        else
        {
            $segment = func_get_args();
            
            if (in_array('action', $segment) && !in_array('table', $segment))
            {
                $segment[] = 'table';
            }
        }
        
        while (($key = array_search('connection', $segment)) !== false)
        {
            unset($segment[$key]);
        }
        
        foreach ($segment as $s)
        {
            if (!property_exists($this, $s))
            {
                throw new outofrangeexception('Segment' . $s . '  does not exist.');
            }
            
            $this->$s = null;
        }
        
        return $this;
    }

    ////////////////////////////////////////////////////////////////////////////
    // Internal functions.
    
    protected function render_action()
    {
        return $this->{'render_action_' . $this->action}();
    }

    protected function render_action_select()
    {
        return 'SELECT' . (($this->distinct) ? ' DISTINCT' : '');
    }
    
    protected function render_action_insert()
    {
        return 'INSERT INTO ' . $this->render_sql($this->table, 'identifier');
    }

    protected function render_action_update()
    {
        $table = array();
        
        foreach ($this->table as $t)
        {
            $table[] = $this->render_sql($t, 'identifier');
        }
        
        return 'UPDATE ' . implode(', ', $table);
    }

    protected function render_action_delete()
    {
        $table = array();
        
        foreach ($this->table as $t)
        {
            $table[] = $this->render_sql($t, 'identifier');
        }
        
        return 'DELETE FROM ' . implode(', ', $table) .
               (($this->using !== null) ? ' USING ' . $this->render_sql($this->using, 'identifier') : '');
    }

    protected function render_column()
    {
        return $this->{'render_column_' . $this->action}();
    }
    
    protected function render_column_select()
    {
        $column = array();
        $table = array();
        
        foreach ($this->column as $c)
        {
            $column[] = $this->render_sql($c, 'identifier');
        }
        
        foreach ($this->table as $t)
        {
            $table[] = $this->render_sql($t, 'identifier');
        }
        
        return implode(', ', $column) . ' FROM ' . implode(', ', $table);
    }

    protected function render_column_insert()
    {
        $column = array();
        $value = array();
        
        foreach (array_keys($this->column) as $c)
        {
            if (is_numeric($c))
            {
                continue;
            }
            
            $column[] = $this->render_identifier($c);
        }
        
        foreach ($this->column as $v)
        {
            $value[] = $this->render_sql($v, 'literal');
        }
        
        return '(' . implode(', ', $column) . ') VALUE (' . implode(', ', $value) . ')';
    }

    protected function render_column_update()
    {
        $column = array();
        
        foreach ($this->column as $c => $v)
        {
            $column[] = $this->render_identifier($c) . ' = ' . $this->render_sql($v, 'literal');
        }
        
        return 'SET ' . implode(', ', $column);
    }
    
    protected function render_column_delete()
    {
        return '';
    }

    protected function render_join()
    {
        $join = array();
        
        foreach ($this->join as $j)
        {
            $join[] = ltrim(strtoupper($j['type']) . ' JOIN ') . $this->render_sql($j['table'], 'identifier') . ' ON ' . $this->render_condition($j['condition']);
        }
        
        return implode(' ', $join);
    }

    protected function render_where()
    {
        return 'WHERE ' . $this->render_condition($this->where);
    }

    protected function render_group()
    {
        $column = array();
        
        foreach ($this->group as $c)
        {
            $column[] = $this->render_sql($c, 'identifier');
        }
        
        return 'GROUP BY ' . implode(', ', $column);
    }

    protected function render_having()
    {
        return 'HAVING ' . $this->render_condition($this->having);
    }

    protected function render_order()
    {
        $column = array();
        
        foreach ($this->order[0] as $k => $c)
        {
            $column[] = $this->render_sql($c, 'identifier') . (($this->order[1][$k] !== null) ? ' ' . strtoupper($this->order[1][$k]) : '');
        }
        
        return 'ORDER BY ' . implode(', ', $column);
    }

    protected function render_limit()
    {
        return 'LIMIT ' . $this->render_sql($this->limit, 'raw');
    }

    protected function render_offset()
    {
        return 'OFFSET ' . $this->render_sql($this->offset, 'raw');
    }

    protected function render_sql($value, $default)
    {
        if ($value instanceof stdclass)
        {
            return $this->{'render_' . $value->type}($value->value);
        }
        elseif ($value instanceof db_query)
        {
            return '(' . $value->render() . ')';
        }
        else
        {
            return $this->{'render_' . $default}($value);
        }
    }

    protected function render_raw($value)
    {
        return $value;
    }
    
    protected function render_identifier($value)
    {
        $table = null;
        $alias = null;
        
        if (strpos($value, '.') !== false)
        {
            list($table, $value) = explode('.', $value, 2);
        }
        
        if (strpos($value, ' ') !== false)
        {
            list($value, $alias) = explode(' ', $value, 2);
        }
        
        return (($table !== null) ? '`' . $table . '`.' : '') . (($value === '*') ? '*' : '`' . $value . '`') . (($alias !== null) ? ' AS `' . $alias . '`' : '');
    }

    protected function render_literal($value)
    {
        return ($value === null) ? 'NULL' : $this->connection->quote($value);
    }
    
    protected function render_func(array $value)
    {
        $function = array_shift($value);
        $alias = null;
        $parameter = array();
        
        if (strpos($function, ' ') !== false)
        {
            list($function, $alias) = explode(' ', $function, 2);
        }
        
        foreach ($value as $v)
        {
            $parameter[] = $this->render_sql($v, 'identifier');
        }
        
        return strtoupper($function) . '(' . implode(', ', $parameter) . ')' . (($alias !== null) ? ' AS `' . $alias . '`' : '');
    }
    
    protected function render_condition(array $condition)
    {
        $operator = array_shift($condition);
        $function = 'render_condition_' . $this->translate($operator);
        
        if (!method_exists($this, $function))
        {
            throw new outofrangeexception('Condition operator ' . $operator . ' does not exist.');
        }
        
        return '(' . $this->$function($condition) . ')';
    }
    
    protected function render_condition_and(array $condition)
    {
        return $this->render_condition_logic('AND', $condition);
    }
    
    protected function render_condition_or(array $condition)
    {
        return $this->render_condition_logic('OR', $condition);
    }
    
    protected function render_condition_xor(array $condition)
    {
        return $this->render_condition_logic('XOR', $condition);
    }
    
    protected function render_condition_logic($operator, array $condition)
    {
        $list = array();
        
        foreach ($condition as $c)
        {
            $list[] = $this->render_condition($c);
        }
        
        return implode(' ' . $operator . ' ', $list);
    }
    
    protected function render_condition_equal(array $operand)
    {
        return $this->render_condition_comparison(($operand[1] === null || ($operand[1] instanceof stdclass && $operand[1]->type === 'literal' && $operand[1]->value === null)) ? 'IS' : '=', $operand);
    }
    
    protected function render_condition_not_equal(array $operand)
    {
        return $this->render_condition_comparison(($operand[1] === null || ($operand[1] instanceof stdclass && $operand[1]->type === 'literal' && $operand[1]->value === null)) ? 'IS NOT' : '!=', $operand);
    }
    
    protected function render_condition_like(array $operand)
    {
        return $this->render_condition_comparison('LIKE', $operand);
    }
    
    protected function render_condition_not_like(array $operand)
    {
        return $this->render_condition_comparison('NOT LIKE', $operand);
    }
    
    protected function render_condition_greater(array $operand)
    {
        return $this->render_condition_comparison('>', $operand);
    }
    
    protected function render_condition_greater_or_equal(array $operand)
    {
        return $this->render_condition_comparison('>=', $operand);
    }
    
    protected function render_condition_less(array $operand)
    {
        return $this->render_condition_comparison('<', $operand);
    }
    
    protected function render_condition_less_or_equal(array $operand)
    {
        return $this->render_condition_comparison('<=', $operand);
    }
    
    protected function render_condition_comparison($operator, array $operand)
    {
        list($left, $right) = $operand;
        return $this->render_sql($left, 'identifier') . ' ' . $operator . ' ' . $this->render_sql($right, 'literal');
    }
    
    protected function render_condition_in(array $operand)
    {
        return $this->render_condition_search('IN', $operand);
    }
    
    protected function render_condition_not_in(array $operand)
    {
        return $this->render_condition_search('NOT IN', $operand);
    }

    protected function render_condition_search($operator, array $operand)
    {
        list($left, $right) = $operand;
        $value = array();
        
        if (!is_array($right))
        {
            $right = array($right);
        }
        
        foreach ($right as $v)
        {
            $value[] = $this->render_sql($v, 'literal');
        }
        
        return $this->render_sql($left, 'identifier') . ' ' . $operator . ' ' . '(' . implode(', ', $value) . ')';
    }
    
    /**
     * Translates an abbreviated operator.
     *
     * @param string $operator The operator.
     * @return string The translated operator.
     */
    protected function translate($operator)
    {
        return str_replace(array('>=', '<=', '=', '>', '<', '!'), array('greater_or_equal', 'less_or_equal', 'equal', 'greater', 'less', 'not_'), $operator);
    }
    
    /**
     * Pushes values onto an array only if they are not present.
     *
     * @param array $array The array.
     * @param string/array $value The value.
     */
    protected static function merge_distinct(&$array, $value)
    {
        if (!$array)
        {
            $array = array();
        }
        
        if (!is_array($value))
        {
            $value = array($value);
        }
        
        foreach ($value as $v)
        {
            if (array_search($v, $array, true) === false)
            {
                $array[] = $v;
            }
        }
    }
}
