<?php
/**
 * MySQL ORM Grammar Class
 *
 * @package ZenoChatbot\Framework\Database\ORM\Grammar
 */

namespace ZenoChatbot\Framework\Database\ORM\Grammar;

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * MySQL ORM Grammar Class
 */
class MySqlGrammar extends Grammar
{
    /**
     * Compile a select statement
     *
     * @param array $components
     * @return string
     */
    public function compileSelect(array $components)
    {
        $sql = 'SELECT ';

        // Distinct
        if (isset($components['distinct']) && $components['distinct']) {
            $sql .= 'DISTINCT ';
        }

        // Columns
        if (isset($components['columns']) && !empty($components['columns'])) {
            $columns = array_map([$this, 'wrapIdentifier'], $components['columns']);
            $sql .= implode(', ', $columns);
        } else {
            $sql .= '*';
        }

        // From
        if (isset($components['from'])) {
            $sql .= ' FROM ' . $this->wrapIdentifier($this->getTableName($components['from']));
        }

        // Joins
        if (isset($components['joins']) && !empty($components['joins'])) {
            $sql .= ' ' . $this->compileJoins($components['joins']);
        }

        // Where
        if (isset($components['wheres']) && !empty($components['wheres'])) {
            $sql .= ' WHERE ' . $this->compileWheres($components['wheres']);
        }

        // Group By
        if (isset($components['groups']) && !empty($components['groups'])) {
            $groups = array_map([$this, 'wrapIdentifier'], $components['groups']);
            $sql .= ' GROUP BY ' . implode(', ', $groups);
        }

        // Having
        if (isset($components['havings']) && !empty($components['havings'])) {
            $sql .= ' HAVING ' . $this->compileWheres($components['havings']);
        }

        // Order By
        if (isset($components['orders']) && !empty($components['orders'])) {
            $orders = [];
            foreach ($components['orders'] as $order) {
                $column = $this->wrapIdentifier($order['column']);
                $direction = strtoupper($order['direction']);
                $orders[] = "{$column} {$direction}";
            }
            $sql .= ' ORDER BY ' . implode(', ', $orders);
        }

        // Limit
        if (isset($components['limit'])) {
            $sql .= ' LIMIT ' . (int) $components['limit'];
        }

        // Offset
        if (isset($components['offset'])) {
            $sql .= ' OFFSET ' . (int) $components['offset'];
        }

        return $sql;
    }

    /**
     * Compile an insert statement
     *
     * @param string $table
     * @param array $values
     * @return string
     */
    public function compileInsert($table, array $values)
    {
        $tableName = $this->wrapIdentifier($this->getTableName($table));

        if (empty($values)) {
            return "INSERT INTO {$tableName} () VALUES ()";
        }

        // Single row insert
        if (!is_array(reset($values))) {
            $columns = array_map([$this, 'wrapIdentifier'], array_keys($values));
            $valuesList = array_map([$this, 'wrapValue'], array_values($values));

            return "INSERT INTO {$tableName} (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $valuesList) . ")";
        }

        // Multiple row insert
        $columns = array_map([$this, 'wrapIdentifier'], array_keys(reset($values)));
        $rows = [];

        foreach ($values as $row) {
            $rowValues = array_map([$this, 'wrapValue'], array_values($row));
            $rows[] = "(" . implode(', ', $rowValues) . ")";
        }

        return "INSERT INTO {$tableName} (" . implode(', ', $columns) . ") VALUES " . implode(', ', $rows);
    }

    /**
     * Compile an update statement
     *
     * @param string $table
     * @param array $values
     * @param array $wheres
     * @return string
     */
    public function compileUpdate($table, array $values, array $wheres)
    {
        $tableName = $this->wrapIdentifier($this->getTableName($table));

        $sets = [];
        foreach ($values as $column => $value) {
            $column = $this->wrapIdentifier($column);
            $value = $this->wrapValue($value);
            $sets[] = "{$column} = {$value}";
        }

        $sql = "UPDATE {$tableName} SET " . implode(', ', $sets);

        if (!empty($wheres)) {
            $sql .= ' WHERE ' . $this->compileWheres($wheres);
        }

        return $sql;
    }

    /**
     * Compile a delete statement
     *
     * @param string $table
     * @param array $wheres
     * @return string
     */
    public function compileDelete($table, array $wheres)
    {
        $tableName = $this->wrapIdentifier($this->getTableName($table));
        $sql = "DELETE FROM {$tableName}";

        if (!empty($wheres)) {
            $sql .= ' WHERE ' . $this->compileWheres($wheres);
        }

        return $sql;
    }

    /**
     * Compile where clauses
     *
     * @param array $wheres
     * @return string
     */
    public function compileWheres(array $wheres)
    {
        $conditions = [];

        foreach ($wheres as $where) {
            $boolean = isset($where['boolean']) ? strtoupper($where['boolean']) : 'AND';

            switch ($where['type']) {
                case 'basic':
                    $condition = $this->compileWhereBasic($where);
                    break;
                case 'in':
                    $condition = $this->compileWhereIn($where);
                    break;
                case 'not_in':
                    $condition = $this->compileWhereNotIn($where);
                    break;
                case 'null':
                    $condition = $this->compileWhereNull($where);
                    break;
                case 'not_null':
                    $condition = $this->compileWhereNotNull($where);
                    break;
                case 'between':
                    $condition = $this->compileWhereBetween($where);
                    break;
                case 'not_between':
                    $condition = $this->compileWhereNotBetween($where);
                    break;
                case 'nested':
                    $condition = $this->compileWhereNested($where);
                    break;
                case 'exists':
                    $condition = $this->compileWhereExists($where);
                    break;
                case 'column':
                    $condition = $this->compileWhereColumn($where);
                    break;
                default:
                    $condition = '';
            }

            if (!empty($condition)) {
                if (empty($conditions)) {
                    $conditions[] = $condition;
                } else {
                    $conditions[] = "{$boolean} {$condition}";
                }
            }
        }

        return implode(' ', $conditions);
    }

    /**
     * Compile joins
     *
     * @param array $joins
     * @return string
     */
    public function compileJoins(array $joins)
    {
        $joinClauses = [];

        foreach ($joins as $join) {
            $type = strtoupper($join['type']);
            $table = $this->wrapIdentifier($this->getTableName($join['table']));
            $on = $this->compileWheres($join['clauses']);

            $joinClauses[] = "{$type} JOIN {$table} ON {$on}";
        }

        return implode(' ', $joinClauses);
    }

    /**
     * Compile where basic
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereBasic(array $where)
    {
        $column = $this->wrapIdentifier($where['column']);
        $operator = $where['operator'];
        $value = $this->wrapValue($where['value']);

        return "{$column} {$operator} {$value}";
    }

    /**
     * Compile where in
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereIn(array $where)
    {
        $column = $this->wrapIdentifier($where['column']);
        $values = array_map([$this, 'wrapValue'], $where['values']);
        $valuesList = implode(', ', $values);

        return "{$column} IN ({$valuesList})";
    }

    /**
     * Compile where not in
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereNotIn(array $where)
    {
        $column = $this->wrapIdentifier($where['column']);
        $values = array_map([$this, 'wrapValue'], $where['values']);
        $valuesList = implode(', ', $values);

        return "{$column} NOT IN ({$valuesList})";
    }

    /**
     * Compile where null
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereNull(array $where)
    {
        $column = $this->wrapIdentifier($where['column']);
        return "{$column} IS NULL";
    }

    /**
     * Compile where not null
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereNotNull(array $where)
    {
        $column = $this->wrapIdentifier($where['column']);
        return "{$column} IS NOT NULL";
    }

    /**
     * Compile where between
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereBetween(array $where)
    {
        $column = $this->wrapIdentifier($where['column']);
        $min = $this->wrapValue($where['values'][0]);
        $max = $this->wrapValue($where['values'][1]);

        return "{$column} BETWEEN {$min} AND {$max}";
    }

    /**
     * Compile where not between
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereNotBetween(array $where)
    {
        $column = $this->wrapIdentifier($where['column']);
        $min = $this->wrapValue($where['values'][0]);
        $max = $this->wrapValue($where['values'][1]);

        return "{$column} NOT BETWEEN {$min} AND {$max}";
    }

    /**
     * Compile where nested (closure)
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereNested(array $where)
    {
        $nestedQuery = $where['query'];
        $nestedWheres = $nestedQuery->getWheres();

        if (empty($nestedWheres)) {
            return '';
        }

        $compiled = $this->compileWheres($nestedWheres);
        return "({$compiled})";
    }

    /**
     * Compile where exists (for whereHas)
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereExists(array $where)
    {
        $subQuery = $where['query'];
        $subSql = $subQuery->toSql();

        return "EXISTS ({$subSql})";
    }

    /**
     * Compile where column
     *
     * @param array $where
     * @return string
     */
    protected function compileWhereColumn(array $where)
    {
        $first = $this->wrapIdentifier($where['first']);
        $operator = $where['operator'];
        $second = $this->wrapIdentifier($where['second']);

        return "{$first} {$operator} {$second}";
    }
}
