<?php

namespace Dipper\Foundation\Database;

use DateTime;
use DateTimeInterface;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Query\Grammars\PostgresGrammar as Grammar;

class PostgresGrammar extends Grammar
{
    /**
       * 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')
    {
        $as = 'as_table';

        $table = $this->wrapTable($query->from);

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

        $row = current($values);

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

        $referenceType =  strpos($filed, '::') ? '::'.explode('::', $filed)[1] : '';
        $filed = strpos($filed, '::') ? explode('::', $filed)[0] : $filed;
        $reference = isset($row[$filed]) ? $filed : current($columns);

        $updates = collect($columns)->filter(function ($column) use ($reference) {
            return $column !== $reference;
        })->map(function ($column, $index) use ($as, $values) {
            $columnType = '';

            if ($values[0][$column] instanceof \DateTime) {
                $columnType = '::timestamp';
            }

            return "{$column} = {$as}.{$column}{$columnType}";
        })->implode(', ');

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

        $columns = '('.$this->columnize($columns).')';

        $from = "FROM (VALUES $parameters) AS {$as}{$columns}";

        if (empty($query->wheres)) {
            $where = "WHERE {$table}.{$reference}$referenceType = {$as}.{$reference}$referenceType";
        } else {
            $where = $this->compileUpdateWheres($query) . " AND  {$table}.{$reference}$referenceType = {$as}.{$reference}$referenceType";
        }

        return trim("UPDATE {$table} SET {$updates} {$from} {$where}");
    }

    /**
     * Compile an upsert statement into SQL.
     *
     * @param Builder $query
     * @param array $values
     * @param array|string $fields
     * @param array|null $only
     * @return string
     */
    public function compileUpsert(QueryBuilder $query, array $values, $fields = ['id'], $only = null)
    {
        $insert = $this->compileInsert($query, $values);

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

        $row = current($values);

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

        if (!is_array($fields)) {
            $fields = explode(',', $fields);
        }

        foreach ($fields as &$field) {
            if ($field === 'deleted_at') {
                $field = "COALESCE($field, '1970-01-01 08:00:00'::timestamp)";
            }
        }

        $reference = implode(',', $fields);

        // excluded fields are all fields except $unique one that will be updated
        // also created_at should be excluded since record already exists
        $excluded = array_filter($keys, function ($e) use ($fields) {
            return !in_array($e, $fields) && $e != 'created_at';
        });

        if (empty($excluded) || (!is_null($only) && empty($only))) {
            return "$insert on conflict ($reference) do nothing";
        }

        $only = is_null($only) ? $excluded : $only;

        $update = join(', ', array_map(function ($e) {
            return "\"$e\" = \"excluded\".\"$e\"";
        }, $only));

        return "$insert on conflict ($reference) do update set $update";
    }
}
