<?php

namespace App\Http\Controllers\Ajax;

use App\AuctionObject;
use App\AuctionObjectStage;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\Facades\DataTables;

class AuctionObjectsStageOverviewController extends Controller
{
    public function getData(Request $request) {

        $filterStatus = $request->status;
        $filterLanguage = $request->lang;
        $filterAuctionName = urldecode($request->an);
        $filterSearchColumn = $request->sc;
        $filterSearch = stripslashes($request->s);

        $arrFilterAuctionName = explode(" : ", $filterAuctionName);

        if (!empty($arrFilterAuctionName[0])) {
            $auctionNumber = $arrFilterAuctionName[0];
        } else {
            $auctionNumber = '';
        }

        if (!empty($arrFilterAuctionName[1])) {
            $auctionHouseName = $arrFilterAuctionName[1];
        } else {
            $auctionHouseName = '';
        }

        // if all filters are not selected provoke an empty result
        if(( is_null($filterStatus) && is_null($filterLanguage) && (is_null($auctionNumber) || $auctionNumber == "") ) && strlen($filterSearch) == 0 ) {

            return json_encode(array('data'=>''));

        } else {

            $data = DB::connection('mysql_stage')
                ->table('t_auction_object as ao')
                ->leftJoin('t_language as l', 'l.id', '=', 'ao.t_language_id')
                //->leftJoin('t_medium_category as m', 'm.id', '=', 'ao.t_medium_category_id')
                //->leftJoin('t_source_type as st', 'st.id', '=', 'ao.t_source_type_id')
                //->leftJoin('t_artist as a', 'a.id', '=', 'ao.t_artist_id')
                ->select(
                    'ao.stage_id',
                    'ao.id',
                    'ao.status',
                    'ao.object_id',
                    'ao.source_type',
                    'ao.t_language_id',
                    'ao.lot_no',
                    'ao.auction_no',
                    'ao.auction_name',
                    'ao.auction_loc',
                    'ao.auction_date',
                    'ao.artist_firstname',
                    'ao.artist_lastname',
                    'ao.artist_born_loc',
                    DB::raw("DATE_FORMAT(ao.artist_born_date, '%Y') as artist_born_date"),
                    DB::raw("DATE_FORMAT(ao.artist_died_date, '%Y') as artist_died_date"),
                    'ao.artist_died_loc',
                    'ao.object_title',
                    'ao.object_medium',
                    'ao.object_date_pre',
                    'ao.object_date',
                    'ao.object_date_post',
                    'ao.object_height',
                    'ao.object_width',
                    'ao.object_depth',
                    'ao.object_size_unit',
                    'ao.object_est_from',
                    'ao.object_est_to',
                    'ao.object_est_curr',
                    'ao.object_sold',
                    'ao.object_result_curr',
                    'ao.object_result',
                    'ao.object_url',
                    'ao.object_image_url',
                    'ao.object_indicators',
                    DB::raw("DATE_FORMAT(ao.crawled_at, '%d.%m.%Y %H:%i') as crawled_at"),
                    DB::raw("DATE_FORMAT(ao.created_at, '%d.%m.%Y %H:%i') as created_at"),
                    'l.language as language_name',
                    'l.lang_code as lang_code',
                    'ao.tbd_preselected'
                );

            $data->whereNotIn('ao.status', array(3, 4));

            if(!is_null($filterStatus)) {
                $data->where('ao.status', $filterStatus);
            }

            if(!is_null($filterLanguage)) {
                $data->where('ao.t_language_id', $filterLanguage);
            }

            if(!empty($auctionNumber)) {
                $data->where('ao.auction_no', $auctionNumber);
            }

            if(!empty($auctionHouseName)) {
                $data->where('ao.source_type', $auctionHouseName);
            }

            if(!is_null($filterSearch)) {
                if ($filterSearchColumn == 'all') {

                    $data->where(function($q) use ($filterSearch)
                    {
                        $q->from('t_auction_object')
                            ->where('ao.auction_name', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.artist_firstname', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.artist_lastname', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.artist_born_loc', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.artist_born_date', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.artist_died_date', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.artist_died_loc', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_medium', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_url', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.auction_no', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.lot_no', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_title', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_date_pre', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_date', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_date_post', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_est_from', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_est_to', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_est_curr', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_result', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_result_curr', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_indicators', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_height', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_width', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_depth', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_size_unit', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_image_url', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.auction_loc', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.auction_date', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.source_type', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.object_id', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.id', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.created_at', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('ao.crawled_at', 'LIKE', '%'.$filterSearch.'%')
                            /*->orWhere('a.firstname_de', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('a.firstname_en', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('a.firstname_fr', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('a.lastname_de', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('a.lastname_en', 'LIKE', '%'.$filterSearch.'%')
                            ->orWhere('a.lastname_fr', 'LIKE', '%'.$filterSearch.'%')*/;
                    });
                } else {

                    if ($filterSearchColumn == 'artist_fullname') {
                        $data->where(DB::raw("CONCAT(a.firstname_de,' ', a.lastname_de)"), 'LIKE', '%'.$filterSearch.'%');
                        $data->orWhere(DB::raw("CONCAT(a.firstname_en,' ', a.lastname_en)"), 'LIKE', '%'.$filterSearch.'%');
                        $data->orWhere(DB::raw("CONCAT(a.firstname_fr,' ', a.lastname_fr)"), 'LIKE', '%'.$filterSearch.'%');
                    }  elseif ($filterSearchColumn == 'medium_category') {
                        $data->where('m.name', 'LIKE', '%'.$filterSearch.'%');
                    } elseif ($filterSearchColumn == 'tbd_preselected') {
                        if ($filterSearch == 'N') {
                            $data->where('ao.tbd_preselected', '=', 0);
                        } elseif ($filterSearch == 'Ü') {
                            $data->where('ao.tbd_preselected', '=', 1);
                        } elseif ($filterSearch == 'V') {
                            $data->where('ao.tbd_preselected', '=', 2);
                        } else {
                            // prov. empty result for this search
                            $data->where('ao.tbd_preselected', '=', -1);
                        }
                    }  else {
                        $data->where('ao.'.$filterSearchColumn, 'LIKE', '%'.$filterSearch.'%');
                    }
                }
            }

            return Datatables::of($data)->make(true);

        }

    }

    /**
     * @param Request $request
     * @return string
     */
    public function bulkEditStatus(Request $request) {

        $bulkAction = $request->bulk_action;

        $arrSelectedRows = $request->dt_checkbox;

        $userId = Auth::user()->id;

        $data = array();

        foreach($arrSelectedRows as $selectedRowId) {

            // if user selected Uebernehmen und uebertragen
            if($bulkAction == 3) {
                array_push($data, ['id'=>$selectedRowId, 'status'=>1, 't_editor_id'=>$userId]);
            } else {
                array_push($data, ['id'=>$selectedRowId, 'status'=>$bulkAction, 't_editor_id'=>$userId]);
            }

            /*$auctionObjectStage = new AuctionObjectStage();
            $auctionObjectStage = $auctionObjectStage->find($selectedRowId);
            $auctionObjectStage->status = $bulkAction;
            $auctionObjectStage->t_editor_id = Auth::user()->id;
            $auctionObjectStage->save();*/
        }

        $this->custom_batch_update('t_auction_object', 'id', $data);

        return '200';
    }

    /**
     * @param string $table_name Table name
     * @param string $key Which column is the selective column
     * @param array $update_arr Data array
     * @return int Affected Rows
     */
    private function custom_batch_update(string $table_name = '', string $key = '', Array $update_arr = array()) {

        if(!$table_name || !$key || !$update_arr){
            return false;
        }

        $update_keys = array_keys($update_arr[0]);
        $update_keys_count = count($update_keys);

        for ($i = 0; $i < $update_keys_count; $i++) {
            $key_name = $update_keys[$i];
            if($key === $key_name){
                continue;
            }
            $when_{$key_name} = $key_name . ' = CASE';
        }

        $length = count($update_arr);
        $index = 0;
        $query_str = 'UPDATE ' . $table_name . ' SET ';
        $when_str = '';
        $where_str = ' WHERE ' . $key . ' IN(';

        while ($index < $length) {
            $when_str = " WHEN $key = '{$update_arr[$index][$key]}' THEN";
            $where_str .= "'{$update_arr[$index][$key]}',";
            for ($i = 0; $i < $update_keys_count; $i++) {
                $key_name = $update_keys[$i];
                if($key === $key_name){
                    continue;
                }
                $when_{$key_name} .= $when_str . " '{$update_arr[$index][$key_name]}'";
            }
            $index++;
        }

        for ($i = 0; $i < $update_keys_count; $i++) {
            $key_name = $update_keys[$i];
            if($key === $key_name){
                continue;
            }
            $when_{$key_name} .= ' ELSE ' . $key_name . ' END, ';
            $query_str .= $when_{$key_name};
        }
        $query_str = rtrim($query_str, ', ');
        $where_str = rtrim($where_str, ',') . ')';
        $query_str .= $where_str;
        $affected = DB::connection('mysql_stage')->update($query_str);

        return $affected;
    }
}
