<?php

namespace App\Http\Controllers;

use App\AcademyLocation;
use \Excel;
use DB;
use DataTables;
use Illuminate\Http\Request;
use App\Http\Controllers\UtilController;
use App\Http\Controllers\Exports\AcademyAttendancesExport;
use App\Services\AcademyLocationsService;

class AcademyAttendanceController extends Controller
{

    private $util;

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

    public function index($type_academy = 'children')
    {
        $academyLocationsService = new AcademyLocationsService;
        if (!$academyLocationsService->validateAuthorizedLocations()) {
            $authorizedLocations = $academyLocationsService->getAuthorizedLocations();
            $places = AcademyLocation::select('id', 'name')->where('active', 1)->whereIn('id', $authorizedLocations)->orderBy('name', 'ASC')->get();
        } else {
            $places = AcademyLocation::select('id', 'name')->where('active', 1)->orderBy('name', 'ASC')->get();
        }
        $status = array();
        $status[] = 'Asistió';
        $status[] = 'No Asistió';
        return view('academy.attendances.list', compact('type_academy'))
            ->with('status', $status);
    }

    public function tableFilter($from_date = null, $to_date = null, $status = null, $type_academy, $categories = null, $locations = null)
    {
        DB::statement("SET sql_mode = ''");
        $obj = $obj = DB::table('academy_attendances')
            ->select(
                'academy_attendances.id',
                'academy_users.student_name',
                'academy_users.student_last_name',
                'academy_users.identification',
                'academy_users.academy_place',
                'academy_categories.name AS categoryName',
                'academy_attendances.attendance_date',
                'academy_attendances.attended',
                DB::raw('DATE(academy_attendances.created_at) AS dateAttendance'),
                DB::raw('TIME(academy_attendances.created_at) AS timeAttendance'),
                'academy_attendances.comment',
                'users.first_name',
                'users.last_name'
            )
            ->join('academy_users', 'academy_attendances.academy_user_id', '=', 'academy_users.id')
            ->join('users', 'academy_attendances.user_id', '=', 'users.id')
            ->leftjoin('academy_categories', 'academy_users.academy_category_id', '=', 'academy_categories.id')
            ->leftjoin('academy_locations', 'academy_users.academy_location_id', '=', 'academy_locations.id')
            ->where('academy_users.type_academy', $type_academy)
            ->groupBy('academy_attendances.id');

        $academyLocationsService = new AcademyLocationsService;
        if (!$academyLocationsService->validateAuthorizedLocations()) {
            $authorizedLocations = $academyLocationsService->getAuthorizedLocations();
            $obj->whereIn('academy_locations.id', $authorizedLocations);
        }

        $categories = (!empty($categories) && !is_array($categories)) ? explode(',', $categories) : $categories;
        $locations = (!empty($locations) && !is_array($locations)) ? explode(',', $locations) : $locations;


        $categories = array_filter($categories, fn($v) => $v !== null && $v !== '' && $v !== 'null');
        $locations = array_filter($locations, fn($v) => $v !== null && $v !== '' && $v !== 'null');

        if (!empty($categories)) {
            $obj->whereIn('academy_users.academy_category_id', $categories);
        }

        if (!empty($locations)) {
            $obj->whereIn('academy_users.academy_location_id', $locations);
        }

        if ($from_date && $from_date != 'null') {
            $obj->whereDate('academy_attendances.attendance_date', '>=', $from_date);
        }

        if ($to_date && $to_date != 'null') {
            $obj->whereDate('academy_attendances.attendance_date', '<=', $to_date);
        }

        if ($status == 'Asistió') {
            $obj->where('academy_attendances.attended', '=', 1);
        } else if ($status == 'No Asistió') {
            $obj->where('academy_attendances.attended', '=', 0);
        }

        DB::statement("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
        \DB::enableQueryLog();
        $dataTable = Datatables::of($obj)
            ->addColumn('actions', function ($obj) {
                $actions = '';
                return $actions;
            })
            ->editColumn('timeAttendance', function ($obj) {
                return \Carbon\Carbon::parse($obj->timeAttendance)->format('h:i:s A');
            })
            ->rawColumns(['actions', 'timeAttendance']);

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

    public function validateExport(Request $request)
    {
        if ($request['query']) {
            $results = $this->util->getGenericData($request["query"], $request["bindings"]);
            if (count($results) > 0) {
                $name = 'ReporteAcademiaAsistencias' . time() . '.xlsx';
                Excel::store(new AcademyAttendancesExport($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);
    }
}