<?php

namespace Dipper\Foundation\Database;

use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Query\Grammars\MySqlGrammar as Grammar;

class MySqlGrammar extends Grammar
{
    /**
     * Compile an replace into statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileReplace(QueryBuilder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (!is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));
        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we will loop through the record and parameterize them all.
        $parameters = [];

        foreach ($values as $record) {
            $parameters[] = '(' . $this->parameterize($record) . ')';
        }

        $parameters = implode(', ', $parameters);

        return "replace into $table ($columns) values $parameters";
    }

    /**
     * Compile an insert ON DUPLICATE KEY UPDATE statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileInsertUpdate(QueryBuilder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (!is_array(reset($values))) {
            $values = [$values];
        }

        $columns = array_keys(reset($values));

        $columnsString = $this->columnize($columns);

        $parameters = collect($values)->map(function ($record) {
            return '('.$this->parameterize($record).')';
        })->implode(', ');

        $sql = "insert into $table ($columnsString) values $parameters ON DUPLICATE KEY UPDATE";

        foreach ($columns as $column) {
            $column = $this->wrap($column);
            $sql .= "$column = VALUES($column),";
        }

        return rtrim($sql, ',');
    }

    /**
     * Compile an insert ignore statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileCreateOrIgnore(QueryBuilder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (!is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we will loop through the record and parameterize them all.
        $parameters = [];

        foreach ($values as $record) {
            $parameters[] = '(' . $this->parameterize($record) . ')';
        }

        $parameters = implode(', ', $parameters);

        return "insert ignore into $table ($columns) values $parameters";
    }

    /**
     * Compile an insert ignore statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileCreateNotExist(QueryBuilder $query, array $values, array $where = [])
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (!is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we will loop through the record and parameterize them all.
        $parameters = [];

        foreach ($values as $record) {
            $parameters[] = '(' . $this->parameterize($record) . ')';
        }

        $parameters = implode(', ', $parameters);


        $conditions = [];

        foreach ($where as $key => $limit) {
            $conditions[] = "$key = '$limit'";
        }

        $conditions = implode('and ', $conditions);

        return "insert into $table ($columns) select $parameters from $table where not exists (select 1 from $table where $conditions) limit 1";
    }

    /**
     * Compile an update batch statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @param  string  $filed
     * @return string
     */
    public function compileUpdateBatch(QueryBuilder $query, array $values, $filed = 'id')
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (!is_array(reset($values))) {
            $values = [$values];
        }

        $row = current($values);

        $columns = array_keys(reset($values));

        // 指定更新字段，默认id字段,不存在时选用第一个字段。
        $reference = isset($row[$filed]) ? $filed : current($columns);
        $key = isset($row[$filed]) ? array_search($filed, $columns) : 0;
        unset($columns[$key]);


        $sql = "UPDATE $table SET";

        $sets = [];

        foreach ($columns as $column) {
            $setSql = "`" . $column . "` = CASE ";
            foreach ($values as $data) {
                $setSql .= "WHEN `" . $reference . "` = '$data[$reference]' THEN '$data[$column]' ";
            }
            $setSql .= "ELSE `" . $column . "` END ";
            $sets[] = $setSql;
        }

        $sql .= implode(', ', $sets);

        $whereIn = collect($values)->pluck($reference)->values()->all();

        return rtrim($sql, ', ') . " WHERE `" . $reference . "` IN (" . implode(', ', $whereIn) . ")";
    }

    /**
     * Compile an upsert statement into SQL.
     *
     * @param Builder $query
     * @param array $values
     * @param array|string $unique
     * @return string
     */
    public function compileUpsert(QueryBuilder $query, array $values, $filed = ['id'], $doNothing = false)
    {
        if ($doNothing) {
            return $this->compileCreateOrIgnore($query, $values);
        }

        return $this->compileInsertUpdate($query, $values);
    }
}
