<?php

namespace App\Http\Controllers\RequestBuilder;

use App\Http\Controllers\Controller;
use Base\Tables\RequestFields;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\View\View;

class BladeController extends Controller
{
    private array $columns;
    private array $params;
    private array $fieldsToUpdate;
    protected ?array $fieldColumn;
    private ?string $table;
    private ?string $type;
    private ?bool $insertResponse = true;
    public function requestBuilder() : View {
        $namespaces = DB::connection('sys_base')->table('namespace')
            ->select('id','namespace')->get()->toArray();
        $allTables = DB::select('SHOW TABLES');
        $tables = array();
        foreach($allTables as $table){
            if (str_contains($table->Tables_in_ftlp_acctg, 'tbl_')) {
                $tbl['name'] = $table->Tables_in_ftlp_acctg;
                $tbl['columns'] = Schema::getColumnListing($table->Tables_in_ftlp_acctg);
                $query = DB::connection('sys_base')->table('request_fields')->where('table', $tbl['name'])->get();
                $tbl['request_fields'] = $this->setTableFields($query);
                $tables[$table->Tables_in_ftlp_acctg] = $tbl;
            }
        }
        $request_fields = DB::connection('sys_base')->table('request_fields')->get();
        $request_services = DB::connection('sys_base')->table('request_services')->get();
        $req_fields = $this->setTableFields($request_fields);
        return view('RequestBuilder/index', compact('tables','namespaces', 'req_fields', 'request_services'));
    }

    public function saveRequest(Request $request){
        try {
            $this->params = $request->params;
            $this->table = $this->params['table'];
            $this->type = $this->params['type'];
            $service = DB::connection('sys_base')->table('request_services')->where('request', $this->params['request'])
                ->where('type', $this->params['type'])->get();
            if($service->isEmpty()) {
                $this->insertResponse = $this->insertToDB('request_services', $this->params);
            } else {
                $values =([
                    'table' => $this->params['table'],
                    'namespace' => $this->params['namespace'],
                    'controller' => $this->params['controller'],
                    'function' => $this->params['function'],
                    'conditions' => $this->params['conditions']
                ]);
                $this->updateRow('request_services', 'type', $this->type, $values);
            }
            if($this->insertResponse) {
                if(!is_null($request->columns)) {
                    $this->columns = $request->columns;
                    $this->saveToReqsFields();
                } else {
                    $this->fieldsToUpdate = [];
                }
                $this->updateQueryField();
                return successResponse("Success", $this->insertResponse);
            }
        } catch(\Exception $e) {
            return failedResponse("ERROR", ["msg" => $e->getMessage()], 422);
        }
        return $request->all();
    }

    public function saveToReqsFields(){
        // Checks if table and field is already existing. NO => create a new row; YES => continue...
        // loop through all queries and check if exists. YES => return; NO => append
        try {
            foreach ($this->columns as $column) {
                $field_column = explode(':', $column);
                $this->fieldsToUpdate[$field_column[0]] = $field_column[0];
                $reqsFields = RequestFields::query()->where('table', $this->table)
                    ->where('field', $field_column[0])->get();
                if ($reqsFields->isEmpty()) {
                    $value = [null, $this->table, $this->type, $field_column[0], $field_column[1], $field_column[2]];
                    $arrayOfValues = $this->makeKeyPair($value, 'add');
                    RequestFields::query()->insert($arrayOfValues);
                    continue;
                }

                // on instances where type and property of fields must be updated
                $fields = ([
                    'type' => $field_column[1],
                    'property' => $field_column[2]
                ]);
                $this->updateRow('request_fields', 'field', $field_column[0], $fields);

                // once the result of query is not empty, check if the query field contains the type (for comma separated)
                if(str_contains($reqsFields->value('query'), $this->type)) continue;
                else {
                    DB::connection('sys_base')->table('request_fields')->where('table', $this->table)
                        ->where('field', $field_column[0])->update(['query' => DB::raw("CONCAT(query, ',$this->type')")]);
                }
            }
        } catch (\Exception $e) {
            return failedResponse("ERROR", ["msg" => $e->getMessage()], 422);
        }
        return 0;
    }

    /** This function performs sanitation to remove a query **/
    public function updateQueryField() {
        // if there are fields selected to update, go to nonselected fields and check if the query exists
        $queryRows = DB::connection('sys_base')->table('request_fields')->where('table', $this->table);
        foreach ($queryRows->get() as $rows) {
            if(!empty($this->fieldsToUpdate)){
                $selectedFields = implode(',',$this->fieldsToUpdate); // get the fields selected to compare
                if(!str_contains($selectedFields, $rows->field)) {
                    $db = DB::connection('sys_base')->table('request_fields')->where('table', $this->table)->where('field', $rows->field)->first();
                    $this->update($db);
                }
            } else {
                // if no fields are selected within the Request Builder, redirect to this function
                $this->update($rows);
            }
        }
    }

    /** Performs sanitation to remove a query within the nonselected fields **/
    public function update($row) {
        if(str_contains($row->query, $this->type)){
            $initialQuery = explode(',',$row->query);
            foreach ($initialQuery as $key=>$query){
                if($query === $this->type) {
                    unset($initialQuery[$key]);
                }
            }
            $updatedQuery = implode(',',$initialQuery);
            DB::connection('sys_base')->table('request_fields')->where('table', $this->table)->where('field', $row->field)->update(['query' => $updatedQuery]);

            $currentRowSelected = DB::connection('sys_base')->table('request_fields')->where('table', $this->table)->where('field', $row->field)->first();
            if(empty($currentRowSelected->query)){
                $this->deleteEmptyQueryRow($this->table, $row->field);
            }
        }
    }

    public function insertToDB($tbl_name, $values) : bool {
        return DB::connection('sys_base')->table($tbl_name)->insert($values);
    }

    public function updateRow($table, $field, $fieldValue, $values) : bool {
        return DB::connection('sys_base')->table($table)->where('table', $this->table)->where($field, $fieldValue)->update($values);
    }

    public function deleteEmptyQueryRow($table, $field) : bool {
        return DB::connection('sys_base')->table('request_fields')->where('table', $table)->where('field', $field)->delete();
    }

    /** Get all rows from request_fields to pass to blade for updating type and property */
    public function setTableFields($request_fields) {
        //return $request_fields;
        $fields = array();

        $field = array();
        foreach ($request_fields as $data){
            $field['table'] = $data->table;
            $field['query'] = $data->query;
            $field['field'] = $data->field;
            $field['type'] = $data->type;
            $field['property'] = $data->property;
            // $fields[$data->table] = $field;
            array_push($fields, $field);
        }
        return $fields;
    }

    /** Iterates columns of request_fields and filter unwanted columns **/
    public function filterTableColumn($mode) {
        $reqFields_columns = Schema::connection('sys_base')->getColumnListing('request_fields');
        $this->fieldColumn = $reqFields_columns;

        if($mode == 'add') $columnToRemove = ['created_at', 'updated_at'];
        else if($mode == 'update') $columnToRemove = ['id', 'created_at', 'updated_at'];

        foreach($this->fieldColumn as $key => $v) {
            if(in_array($this->fieldColumn[$key], $columnToRemove)){
                unset($this->fieldColumn[$key]);
            }
            $reqFields_columns = $this->fieldColumn;
        }
        return $reqFields_columns;
    }

    /** create associative array to set as values parameter for INSERT **/
    // output: ["code" => "value", "type" => "add", ...]
    public function makeKeyPair($values, $mode) {
        $fillableColumns = $this->filterTableColumn($mode);
        $valuePairs = array();
        foreach($fillableColumns as $key=>$column) {
            $valuePairs[$column] = $values[$key];
        }
        return $valuePairs;
    }
}
