<?php

namespace Hilco;

use Arr;
use Str;
use DB;
use Illuminate\Database\Query\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

abstract class VisionwareDatatable {
    /**
     * Use this if you don't want to have to figure out how this stupid class works
     * @var bool
     */
    protected $simpleMode = false;

    /**
     * Make sure you set this to false if you're using simpleMode
     * @var bool
     */
    protected $hasGroupBy = true;

    /**
     * Use this if the column for marking deleted column isn't 'deleted_at' for some reason
     * @var string
     */
    protected $deletedColumn = 'deleted_at';

    /**
     * Use this if for some crazy reason you still need to group by but it's not just on a single column
     * @var boolean
     */
    protected $isGroupByRaw = false;

    protected $isGroupByMultiple = false;

    protected $isSelectColumnRaw = false;

    /**
     * Use this if you're using broke-ass mode but you still gotta group-by raw and it's grouping on multiple columns...
     * @var string
     */
    protected $selectColumn = 'id';

    /**
     * @var Collection
     */
    protected $input;
    /**
     * @var Collection
     */
    protected $fixedFilters;
    /**
     * @var Collection
     */
    protected $filters;
    /**
     * @var array
     */
    protected $expectedFilters = [];
    /**
     * @var string
     */
    protected $primaryTable;
    /**
     * @var bool
     */
    protected $isPrimaryTableDerived = false;
    /**
     * @var string
     */
    protected $primaryTableDerivedName;
    /**
     * @var string
     */
    protected $groupByColumn;
    /**
     * @var Builder
     */
    protected $informationQuery;
    /**
     * @var Builder
     */
    protected $filterQuery;

    protected $filteredIds;
    /**
     * @var Collection
     */
    protected $additionalData;

    protected $excelColumnNames;

    /**
     * Name of the database connection to use (uses project default)
     * @var string
     */
    private $connection;

    protected function __construct(Request $request, $additionalFilters = [], $additionalData = [], $connection = null) {
        $this->input = collect($request->all());
        $this->connection = is_null($connection) ? config('database.default') : $connection;

        foreach ($additionalFilters as $key => $value) {
            $this->input->put($key, $value);
        }
        $this->additionalData = collect();
        foreach ($additionalData as $key => $value) {
            $this->additionalData->put($key, $value);
        }
        $this->filters = new Collection;
        $this->fixedFilters = new Collection;
        $this->filteredIds = [];
        array_push($this->expectedFilters, 'visible');
        array_push($this->expectedFilters, 'deleted');
    }

    public static function handleRequest(Request $request, $additionalFilters = [], $additionalData = [], $connectionToUse = null) {
        $instance = new static($request, $additionalFilters, $additionalData, $connectionToUse);
        return $instance->go();
    }

    public static function setup(Request $request) {
        $instance = new static($request);
        return $instance;
    }

    protected function go() {
        $this->populateFilters();

        $recordsCount = null;
        if ($this->simpleMode) {
            if ($this->primaryTable) {
                $informationQuery = $this->buildInformationQuery(DB::connection($this->connection())->table($this->primaryTable()));
                $informationQuery = $this->addColumns($informationQuery);
                if ($this->hasGroupBy) {
                    $informationQuery = $this->applyGroupBy($informationQuery);
                }
                $informationQuery = $this->applyOrdering($informationQuery);
                $informationQuery = $this->applyFilters($informationQuery);

                $recordsCount = $informationQuery->count();
                $informationQuery = $this->applyLimits($informationQuery);
            } else {
                $informationQuery = $this->buildInformationQuery(DB::connection($this->connection())->query());
                $informationQuery = $this->addColumns($informationQuery);
            }

            $results = $this->getResults($informationQuery);

            $response = [
                'draw' => (int)$this->input->get('draw'),
                'recordsTotal' => $recordsCount ? $recordsCount : count($results),
                'recordsFiltered' => $recordsCount ? $recordsCount : count($results),
                'data' => $results,
            ];

            $response = array_merge($response, $this->additionalData());
            return $response;
        } else {
            // Oh I see you elected to use crazy broke-ass mode
            $filterQuery = $this->buildFilterQuery(DB::connection($this->connection())->table($this->primaryTable()));
            $filterQuery = $this->applyFixedFilters($filterQuery);
            $potentialCount = $this->count($filterQuery);
            $filterQuery = $this->applyFilters($filterQuery);
            $filteredCount = $this->count($filterQuery);
            $filterQuery = $this->applyLimits($filterQuery);
            if ($this->isSelectColumnRaw) {
                $filterQuery->addSelect(DB::raw($this->columnToSelect() . ' AS CUSTOMSELECTBYRAW'));
            } else {
                $filterQuery->addSelect($this->columnToSelect());
            }
            $filterQuery = $this->applyOrdering($filterQuery);

            if ($this->isSelectColumnRaw) {
                $this->filteredIds = $filterQuery->groupBy($this->groupByColumn())->pluck("CUSTOMSELECTBYRAW");
            } else {
                $this->filteredIds = $filterQuery->groupBy($this->groupByColumn())->pluck($this->columnToSelect());
            }

            if ($this->isSelectColumnRaw) {
                $this->filteredIds = $this->filteredIds->map(function ($entry) {
                    return preg_replace("/'/", "\\'", $entry);
                });
                $informationQuery = $this->buildInformationQuery(DB::connection($this->connection())->table($this->primaryTable()))->whereRaw($this->columnToSelect() . " IN " . "('" . implode("','", $this->filteredIds->toArray()) . "')");
            } else {
                $informationQuery = $this->buildInformationQuery(DB::connection($this->connection())->table($this->primaryTable()))->whereIn($this->columnToSelect(), $this->filteredIds);
            }
            $informationQuery = $this->addColumns($informationQuery);
            $informationQuery = $this->applyOrdering($informationQuery);

            $results = $this->getResults($informationQuery);

            $response = [
                'draw' => (int)$this->input->get('draw'),
                'recordsTotal' => $potentialCount,
                'recordsFiltered' => $filteredCount,
                'data' => $results,
            ];

            $response = array_merge($response, $this->additionalData());
            return $response;
        }
    }

    protected function connection(){
        return $this->connection;
    }

    protected function primaryTable() {
        return $this->isPrimaryTableDerived ? DB::raw($this->primaryTable) : $this->primaryTable;
    }

    protected function additionalData() {
        return [];
    }

    protected function getResults(Builder $query) {
        if ($this->hasGroupBy) {
            $results = $query->groupBy($this->groupByColumn())->get();
        } else {
            $results = $query->get();
        }

        if (!is_array($results)) $results = $results->toArray();

        $preparedResults = $this->prepareResults($results);

        $data = [];
        foreach ($preparedResults as $rowObject) {
            $row = (array)$rowObject;
            $preparedRow = $this->prepareRow($row);
            if (!Arr::has($preparedRow, 'isDeleted')) {
                if ($this->deletedColumn !== false) {
                    $preparedRow['isDeleted'] = Arr::get($preparedRow, $this->deletedColumn, 0) > 0;
                } else {
                    $preparedRow['isDeleted'] = false;
                }
            }
            if (!Arr::has($preparedRow, 'DT_RowId') && Arr::has($preparedRow, 'id')) $preparedRow['DT_RowId'] = $preparedRow['id'];
            $data[] = $preparedRow;
        }
        return $data;
    }

    protected function prepareResults(array $results) {
        return $results;
    }

    protected function prepareRow(array $row) {
        return $row;
    }

    protected function addColumns(Builder $query) {
        $columnMap = $this->baseColumnMap();
        foreach ($columnMap as $as => $column) {
            $query->addSelect(DB::raw("$column as $as"));
        }
        return $query;
    }

    protected function applyOrdering(Builder $query, $addColumns = true) {
        $columns = $this->input->get('columns');
        $columnMap = $this->baseColumnMap();

        foreach ($this->input->get('order', []) as $orderBy) {
            $orderByName = $columns[$orderBy['column']]['data'];
            $mappedName = $columnMap[$orderByName];

            if ($addColumns) {
                if (strpos($mappedName, '(') === false) {
                    $orderByName = $mappedName;
                } else {
                    $query->addSelect(DB::raw("$mappedName as $orderByName"));
                }
            }
            $query->orderBy($orderByName, $orderBy['dir']);
        }
        return $query;
    }

    protected function applyLimits(Builder $query) {
        if ($this->input->get('length')) $query->take($this->input->get('length'));
        if ($this->input->get('start')) $query->offset($this->input->get('start'));
        return $query;
    }

    protected function buildFilterQuery(Builder $query) {
        return $this->buildInformationQuery($query);
    }

    protected function count(Builder $query) {
        if($this->hasGroupBy === false || isset($this->isGroupByRaw) && $this->isGroupByRaw ){
            return $query->count();
        }
        return $query->distinct($this->groupByColumn())->count($this->groupByColumn());
    }

    protected function filterSearch(Builder $query) {
        $searchArray = $this->filters['search'];
        $query->where(function ($wheres) use ($searchArray) {
            foreach ($searchArray as $column => $value) {
                if (strpos($column, '(') === false) {
                    $wheres->orWhere($column, 'LIKE', $value);
                } else {
                    $wheres->orWhere(DB::raw($column), 'LIKE', $value);
                }
            }
        });
        return $query;
    }

    protected function applyFilters(Builder $query) {
        foreach ($this->filters as $filterKey => $filterValue) {
            $filterMethod = "filter" . Str::studly($filterKey);
            if (method_exists($this, $filterMethod)) {
                $query = call_user_func_array([$this, $filterMethod], [$query, $filterValue]);
            } else if (strpos($filterKey, '(') === false) {
                $query->where($filterKey, $filterValue);
            } else {
                $query->where(DB::raw($filterKey), $filterValue);
            }
        }
        return $query;
    }

    protected function filterDeleted(Builder $query, $value) {
        if ($value == 'include') {

        } else if ($value == 'only') {
            if ($this->isPrimaryTableDerived && isset($this->primaryTableDerivedName)) {
                $query->whereRaw("{$this->primaryTableDerivedName}.deleted_at != 0");
            } else {
                $query->where("{$this->primaryTable}.deleted_at", '!=', DB::raw(0));
            }
        } else {
            if ($this->isPrimaryTableDerived && isset($this->primaryTableDerivedName)) {
                $query->whereRaw("{$this->primaryTableDerivedName}.deleted_at = 0");
            } else {
                $query->where("{$this->primaryTable}.deleted_at", '=', DB::raw(0));
            }
        }
        return $query;
    }

    protected function filterVisible(Builder $query, $value) {
        if ($value == 'visible') {
            if ($this->isPrimaryTableDerived && isset($this->primaryTableDerivedName)) {
                $query->whereRaw("{$this->primaryTableDerivedName}.is_visible = 1");
            } else {
                $query->where("{$this->primaryTable}.is_visible", '=', 1);
            }
        } else if ($value == 'hidden') {
            if ($this->isPrimaryTableDerived && isset($this->primaryTableDerivedName)) {
                $query->whereRaw("{$this->primaryTableDerivedName}.is_visible = 0");
            } else {
                $query->where("{$this->primaryTable}.is_visible", '=', 0);
            }
        }
        return $query;
    }

    protected function applyFixedFilters(Builder $query) {
        foreach ($this->fixedFilters as $filterKey => $filterValue) {
            $filterMethod = "filter" . Str::studly($filterKey);
            if (method_exists($this, $filterMethod)) {
                $query = call_user_func_array([$this, $filterMethod], [$query, $filterValue]);
            } else {
                $query->where($filterKey, $filterValue);
            }
        }
        return $query;
    }

    protected function buildInformationQuery(Builder $query) {
        return $query;
    }

    protected function populateFilters() {
        if (!(count($this->expectedFilters))) return;
        $columnMap = $this->baseColumnMap();
        $search = $this->input->get('search', false);
        $this->filters = collect($this->input->only($this->expectedFilters))->filter(function ($value) {
            return strlen(trim($value));
        });
        if (!$this->filters->has('deleted')) $this->filters->put('deleted', 'no');

        foreach ($this->input as $key => $value) {
//            if ($key == 'deleted') $key = 'fixed_deleted';
            if (!Str::startsWith($key, 'fixed_')) continue;
            $actualKey = str_replace('fixed_', '', $key);
            if (!in_array($actualKey, $this->expectedFilters)) continue;
            if (is_array($value)) {
                $array = [];
                foreach ($value as $index => $item) {
                    $item = trim($item);
                    if (!strlen($item)) continue;
                    $array[$index] = $item;
                    $this->fixedFilters->put($actualKey, $array);
                }
            } else {
                if (!strlen(trim($value))) continue;
                $this->fixedFilters->put($actualKey, $value);
            }
        }
        if (strlen(Arr::get($search,'value',''))) {
            $searchArray = [];
            foreach ($this->input->get('columns', []) as $column) {
                if ($column['searchable'] === 'true') {
                    if (Arr::has($columnMap, $column['data'])) {
                        $searchArray[$columnMap[$column['data']]] = "%$search[value]%";
                    }
                }
            }
            $this->filters->put('search', $searchArray);
        }
    }

    private function groupByColumn() {
        if ($this->isGroupByRaw) {
            return DB::raw($this->groupByColumn);
        } else {
            if ($this->isPrimaryTableDerived && isset($this->primaryTableDerivedName)) {
                return "{$this->primaryTableDerivedName}.{$this->groupByColumn}";
            } else {
                return $this->primaryTable . '.' . $this->groupByColumn;
            }
        }
    }

    private function columnToSelect() {
        if ($this->isSelectColumnRaw) {
            return $this->selectColumn;
        } else if ($this->isGroupByRaw && $this->isGroupByMultiple) {
            if ($this->isPrimaryTableDerived && isset($this->primaryTableDerivedName)) {
                return "{$this->primaryTableDerivedName}.{$this->selectColumn}";
            } else {
                return $this->primaryTable . '.' . $this->selectColumn;
            }
        } else {
            return $this->groupByColumn();
        }
    }

    private function baseColumnMap() {
        $map = $this->columnMap();
        if ($this->isPrimaryTableDerived && isset($this->primaryTableDerivedName)) {
            $map['deleted_at'] = "{$this->primaryTableDerivedName}.deleted_at";
        } else {
            $map['deleted_at'] = "{$this->primaryTable}.deleted_at";
        }
        return $map;
    }

    protected function columnMap() {
        return [];
    }

    protected function applyGroupBy(Builder $query, $addColumns = true) {
        return $query->groupBy($this->groupByColumn());
    }

    /**
     * @param string $fileName
     * @return bool
     * @throws Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function createExcel (string $fileName = 'Report'): bool {
        $this->simpleMode = true;
        $datatableResponse = $this->go();
        $tableData = $datatableResponse['data'];

        $excelData = [];
        foreach ($tableData as $index => $row) {
            foreach (array_keys($row) as $key) {
                if (!in_array($key, array_keys($this->excelColumnNames))) {
                    unset($row[$key]);
                }
            }
            foreach ($row as $columnName => $cell) {
                $row[$this->excelColumnNames[$columnName]] = $cell;
                unset($row[$columnName]);
            }
            $excelData[] = $row;
        }

        $export = new class($excelData) implements FromArray, WithHeadings, WithStyles {
            private $array;

            public function __construct($array){
                $this->array = $array;
            }

            public function headings(): array {
                if (count($this->array) > 0){
                    return array_keys($this->array[0]);
                }else{
                    return [];
                }
            }

            public function styles(Worksheet $sheet) {
                return [
                    // Style the first row as bold text.
                    1    => ['font' => ['bold' => true]],
                ];
            }

            public function array(): array {
                return $this->array;
            }
        };
        return Excel::store($export, $fileName, 'exports');
    }
}
