<?php

namespace App\Models;

use DateTimeInterface;
use Exception;
use App\Models\Log;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\SoftDeletes;

class Base extends Model
{
    /**
     * base默认连接mysql数据库实例
     * 查询排序默认按照主键升序,各model继承base建议指定表的主键
     * base默认使用deleted_at作为软删除字段
     */
    use SoftDeletes;

    protected $connection = 'mysql';
    protected $dates = ['deleted_at'];

    //表名声明
    protected $table = '';

    /**
     * 查询单条数据,带查询字段field
     * @param  [type] $where [查询条件]
     * @param  [type] $field [按需取出的字段]
     * @param  string $order [排序字段]
     * @param  string $sort  [排序方式]
     * @return [type]        [array]
     */
    public function findDataWithField($where = [], $field = [], $order = '', $sort = 'ASC')
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

        if (empty($field)) {
            $field = ["*"];
        }

        $data = $this->where($where)
            ->select($field)
            ->orderBy($order, $sort)
            ->first();

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    /**查询多条数据,带查询字段field
     * [selectDataWithField description]
     * @param  [type] $where [查询条件]
     * @param  [type] $field [按需取出的字段]
     * @param  string $order [排序字段]
     * @param  string $sort  [排序方式]
     * @return [type]        [array]
     */
    public function selectDataWithField($where = [], $field = [], $order = '', $sort = 'ASC')
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

//        DB::enableQueryLog();

        if (empty($field)) {
            $field = ["*"];
        }

        $data = $this->where($where)
            ->select($field)
            ->orderBy($order, $sort)
            ->get();

//        var_dump(DB::getQueryLog());die;

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    public function selectDataByIds($ids = [], $field = [])
    {
        if (empty($field)) {
            $field = ['*'];
        }

//        DB::enableQueryLog();

        $data = $this->whereIn($this->primaryKey, $ids)
            ->select($field)
            ->get();

//        var_dump(DB::getQueryLog());die;

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    public function selectDataByIdsTrashed($ids = [], $field = [])
    {
        $data = $this->withTrashed()->whereIn($this->primaryKey, $ids)
            ->select($field)
            ->get();

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    /**
     * 根据条件更新数据
     * @param  [type] $where [更新的条件]
     * @param  [type] $data  [更新的数据]
     * @return [type]        [更新的数据条数]
     */
    public function updateData($where = [], $data = [])
    {
        return $this->where($where)
            ->update($data);
    }

    /**
     * 插入单条数据
     * @param  [type] $data [需要插入的数据,必须为一维数组]
     * @return [type]       [插入数据的主键]
     */
    public function insertData($data = [])
    {
        return $this->insertGetId($data);
    }

    /**
     * 批量插入多条数据
     * @param  [type] $data [一维数组与二维数组均可]
     * @return [type]       [true]
     */
    public function insertMultiData($data = [])
    {
        return $this->insert($data);
    }

    /**
     * (软)删除数据
     * @param  [type] $where [删除的条件]
     * @return [type]        [删除的数据条数]
     */
    public function deleteData($where)
    {
        return $this->where($where)->delete();
    }

    /**
     * (软)删除数据
     * @param  [type] $where [删除的条件]
     * @return [type]        [删除的数据条数]
     */
    public function deleteDataByField($field, $values)
    {
        return $this->whereIn($field, $values)->delete();
    }


    /**
     * 查询单条数据,不带查询字段field,默认返回所有的字段
     * @param  [type] $where [查询条件]
     * @param  string $order [排序字段]
     * @param  string $sort  [排序方式]
     * @return [type]        [array]
     */
    public function findData($where = [], $order = '', $sort = 'asc')
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

        $data = $this->where($where)
            ->orderBy($order, $sort)
            ->first();

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    /**
     * 查询多条数据,不带查询字段field,默认返回所有的字段
     * @param  [type] $where [查询条件]
     * @param  string $order [排序字段]
     * @param  string $sort  [排序方式]
     * @return [type]        [array]
     */
    public function selectData($where = [], $order = '', $sort = 'asc')
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

        $data = $this->where($where)
            ->orderBy($order, $sort)
            ->get();

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    /**
     * 查询多条数据,带查询字段,带分页(默认每页10条数据)
     * @param  [type]  $where    [description]
     * @param  [type]  $field    [description]
     * @param  integer $pagesize [description]
     * @param  string  $order    [description]
     * @param  string  $sort     [description]
     * @return [type]            [description]
     */
    public function selectDataWithPage($where = [], $field = [], $pagesize = 10, $order = 'id', $sort = 'DESC')
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

//        DB::enableQueryLog();

        $data = $this->where($where)
            ->select($field)
            ->orderBy($order, $sort)
            ->paginate($pagesize);

//        var_dump(DB::getQueryLog());die;

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    /**
     * 查询多条数据,带查询字段,带分页(默认每页10条数据),带排序
     * @param  [type]  $where    [description]
     * @param  [type]  $field    [description]
     * @param  integer $pagesize [description]
     * @param  string  $order    [description]
     * @param  string  $sort     [description]
     * @return [type]            [description]
     */
    public function selectDataWithPageSort($where = [], $field = [], $pagesize = 10)
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

//        DB::enableQueryLog();

        $data = $this->where($where)
            ->select($field)
            ->orderBy('is_top', 'desc')
            ->orderBy('set_top_time', 'desc')
            ->orderBy('sort', 'desc')
            ->orderBy('id', 'desc')
            ->paginate($pagesize);

//        var_dump(DB::getQueryLog());die;

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    /**
     * 获取上一条执行的sql
     * @return [type] [description]
     */
    public function getlastSql() {

        $sql = DB::getQueryLog();

        $query = end($sql);

        return $sql;
    }

    /**
     * 批量更新数据
     * 默认找id作为更新的条件,如果没有id则以第一个字段作为条件
     * @param  [type] $multipleData [description]
     * @return [type]               [description]
     */
    public function updateBatch($multipleData = []) {
        try {
            //非空校验
            if (empty($multipleData)) {
                throw new Exception("批量更新的数据不能为空");
            }

            //获取第一行
            $firstRow  = current($multipleData);
            $updateColumn = array_keys($firstRow);

            //默认以id为条件更新,如果没有id则以第一个字段作为条件
            $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
            unset($updateColumn[0]);

            $sets = [];
            $bindings = [];
            //拼接sql语句
            $updateSql = "UPDATE " . $this->table . " SET ";
            foreach ($updateColumn as $column) {
                $setSql = "`" . $column . "` = CASE ";
                foreach ($multipleData as $data) {
                    $setSql .= "WHEN `" . $referenceColumn . "` = ? THEN ? ";
                    $bindings[] = $data[$referenceColumn];
                    $bindings[] = $data[$column];
                }
                $setSql .= "ELSE `" . $column . "` END ";
                $sets[] = $setSql;
            }

            $updateSql .= implode(', ', $sets);
            $whereIn = collect($multipleData)->pluck($referenceColumn)->values()->all();
            $bindings = array_merge($bindings, $whereIn);
            $whereIn = rtrim(str_repeat('?,', count($whereIn)), ',');
            $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";

            //传入预处理sql语句和对应绑定数据
            return DB::update($updateSql, $bindings);
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * 根据条件获取符合条件的记录数
     * @param  [type] $where [description]
     * @return [type]        [description]
     */
    public function getCountByWhere($where = []) {
        return DB::table($this->table)->where($where)->count();
    }

    public function getGroupCountByWhere($where = [], $field = '', $groupBy = '') {
        return DB::table($this->table)->where($where)->select($field)->groupBy($groupBy)->get()->toArray();
    }

    protected function serializeDate(DateTimeInterface $date) {
        return $date->format($this->dateFormat ?: 'Y-m-d H:i:s');
    }

    public function selectDataByWhereFieldAndIds($where, $whereInField, $whereInValues, $field) {
        if (empty($whereInValues)) {
            return [];
        }

//        DB::enableQueryLog();

        $data = $this->where($where)->whereIn($whereInField, $whereInValues)
            ->select($field)
            ->get();

//        var_dump(DB::getQueryLog());

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    public function selectDataByWhereFieldAndIdsOrder($where, $whereInField, $whereInValues, $field, $order = '', $sort = 'ASC') {
        if (empty($whereInValues)) {
            return [];
        }

        if (empty($order)) {
            $order = $this->primaryKey;
        }

//        DB::enableQueryLog();

        $data = $this->where($where)->whereIn($whereInField, $whereInValues)
            ->select($field)
            ->orderBy($order, $sort)
            ->get();

//        var_dump(DB::getQueryLog());die;

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    public function updateDataByIds($whereField = '', $values = [], $data = [])
    {
        if (empty($whereField)) {
            $whereField = $this->primaryKey;
        }

        return $this->whereIn($whereField, $values)
            ->update($data);
    }

    /**
     * incr
     */
    public function incrementByWhere($where, $field, $incr, $extra = [])
    {
        return $this->where($where)->increment($field, $incr, $extra);
    }

    /**
     * decr
     */
    public function decrementByWhere($where, $field, $decr, $extra)
    {
        return $this->where($where)->decrement($field, $decr, $extra);
    }

    /**
     * 查询单条数据,带查询字段field
     * @param  [type] $where [查询条件]
     * @param  [type] $field [按需取出的字段]
     * @param  string $order [排序字段]
     * @param  string $sort  [排序方式]
     * @return [type]        [array]
     */
    public function findDataWithFieldTrashed($where = [], $field = [], $order = '', $sort = 'ASC')
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

        if (empty($field)) {
            $field = ["*"];
        }

        $data = $this->withTrashed()
            ->where($where)
            ->select($field)
            ->orderBy($order, $sort)
            ->first();

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }

    /**查询多条数据,带查询字段field
     * [selectDataWithFieldTrashed description]
     * @param  [type] $where [查询条件]
     * @param  [type] $field [按需取出的字段]
     * @param  string $order [排序字段]
     * @param  string $sort  [排序方式]
     * @return [type]        [array]
     */
    public function selectDataWithFieldTrashed($where = [], $field = [], $order = '', $sort = 'ASC')
    {
        if (empty($order)) {
            $order = $this->primaryKey;
        }

        if (empty($field)) {
            $field = ["*"];
        }

        $data = $this->withTrashed()->where($where)
            ->select($field)
            ->orderBy($order, $sort)
            ->get();

        if (empty($data)) {
            return [];
        } else {
            return $data->toArray();
        }
    }
}
