<?php

namespace App\Http\Controllers;

use DB;
use DataTables;
use App\AppValidationSection;
use App\Core\Redemption\Domain\ValueObjects\UserQRTypesEnum;
use App\Http\Controllers\Exports\ReporRedemptionExport;
use App\Services\ExperienceService;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class ReportRedemptionController extends Controller
{
    private $util;

    public function __construct()
    {
        $this->util = new UtilController();
    }

    public function index()
    {
        DB::statement("SET sql_mode = ''");
        $appValidationSections = AppValidationSection::select('app_validation_sections.id', 'app_validation_sections.name')
            ->join('redemptions', 'redemptions.section_id', '=', 'app_validation_sections.id')
            ->where('app_validation_sections.active', true)
            ->groupBy('app_validation_sections.id')
            ->orderBy('app_validation_sections.name', 'ASC')
            ->get();
        DB::statement("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
        return view('report_redemptions.list', compact('appValidationSections'));
    }

    public function tableFilter($appValidationSections = null, $from_date = null, $to_date = null)
    {
        $obj = $obj = DB::table('redemptions')
            ->select(
                'redemptions.id',
                'redemptions.model',
                'redemptions.model_id',
                'redemptions.options',
                'redemptions.created_at',
                'app_validation_sections.name',
                'users.first_name',
                'users.last_name',
                'users.email',
                'loggedUsers.first_name AS logged_first_name',
                'loggedUsers.last_name AS logged_last_name',
                'loggedUsers.email AS logged_email',
                'academy_users.student_name',
                'academy_users.student_last_name',
                'academy_users.mail',
                DB::raw("GROUP_CONCAT(DISTINCT(tags.name)) AS segmentation"),
            )
            ->leftjoin('users', 'users.id', '=', 'redemptions.user_id')
            ->leftjoin('academy_users', 'academy_users.id', '=', 'redemptions.academy_user_id')
            ->join('users as loggedUsers', 'loggedUsers.id', '=', 'redemptions.logged_user_id')
            ->join('app_validation_sections', 'app_validation_sections.id', '=', 'redemptions.section_id')
            ->leftjoin('user_tags', 'user_tags.user_id', '=', 'users.id')
            ->leftjoin('tags', function ($join) {
                $join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
            })
            ->groupBy('redemptions.id');

        if ($appValidationSections && $appValidationSections != 'null') {
            $appValidationSections = explode(',', $appValidationSections);
            $obj->whereIn('redemptions.section_id', $appValidationSections);
        }
        if ($from_date && $from_date != 'null') {
            $obj->whereDate('redemptions.created_at', '>=', $from_date);
        }
        if ($to_date && $to_date != 'null') {
            $obj->whereDate('redemptions.created_at', '<=', $to_date);
        }

        \DB::enableQueryLog();
        $dataTable = DataTables::of($obj)
            ->editColumn('first_name', function ($obj) {
                if (!$obj->first_name && !$obj->student_name) {
                    $user = $this->getUser($obj->model, $obj->model_id);
                    return $user ? $user->first_name ?? $user->student_name : '';
                }
                return $obj->first_name ?? $obj->student_name;
            })
            ->editColumn('last_name', function ($obj) {
                if (!$obj->last_name && !$obj->student_last_name) {
                    $user = $this->getUser($obj->model, $obj->model_id);
                    return $user ? $user->last_name ?? $user->student_last_name : '';
                }
                return $obj->last_name ?? $obj->student_last_name;
            })
            ->editColumn('email', function ($obj) {
                if (!$obj->email && !$obj->mail) {
                    $user = $this->getUser($obj->model, $obj->model_id);
                    return $user ? $user->email ?? $user->mail : '';
                }
                return $obj->email ?? $obj->mail;
            })
            ->editColumn('created_at', function ($obj) {
                return \Carbon\Carbon::parse($obj->created_at)->format('Y-m-d h:i:s A');
            });

        $response = $dataTable->make(true);
        $data = $response->getData();
        $data = json_decode(json_encode($data), true);
        $queries = \DB::getQueryLog();
        $newQueries = [];
        foreach ($queries as $query) {
            if (isset($query['query']) && str_contains($query['query'], 'group by')) {
                $newQueries[] = $query;
            }
        }
        $data['queries'] = $newQueries;
        return $data;
    }

    public function getUser($model, $model_id)
    {
        $user = null;
        switch ($model) {
            case UserQRTypesEnum::EXPERIENCE:
                $experienceService = new ExperienceService();
                $user = $experienceService->getUser($model_id);
                break;
        }
        return $user;
    }

    public function validateExport(Request $request)
    {
        if ($request['query']) {
            $results = $this->util->getGenericData($request["query"], $request["bindings"]);
            if (count($results) > 0) {
                $name = 'ReporteRedenciones' . time() . '.xlsx';
                Excel::store(new ReporRedemptionExport($results), $name, 'public');
                return response()->json(['success' => true, 'message' => 'Validación OK', 'data' => $name]);
            }
        }
        return response()->json(['success' => false, 'message' => 'No existen datos a exportar']);
    }

    public function export($name)
    {
        return $this->util->export($name);
    }
}