<?php

namespace App\Http\Controllers\Apoderado;

use App\Model\Apoderado;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Yajra\Datatables\Datatables;

class AsistenciaController extends Controller
{
    public function index(){
        return view('pages.apoderado.asistencia');
    }

    public function getCursos() {
        try {
            $alumnoid = Apoderado::where('id_usuario','=', Auth::user()->id)->first()->id_alumno;

            $cursos = DB::select(DB::raw("
            SELECT DISTINCT c.nombre as Curso, sc.nombre_seccion as Seccion, sc.id,a.id as alumnoID, IF(falta.faltas is not null ,falta.faltas,0) as faltas,IF(asistencia.asistencia is not null ,asistencia.asistencia,0) as asistencia, IF(tardanza.tardanza is not null ,tardanza.tardanza,0) as tardanza 
            from alumno a
            INNER JOIN alumno_seccion sa on a.id = sa.id_alumno
            LEFT JOIN asistencia_alumno aa on sa.id = aa.id_alumno_seccion
            INNER JOIN seccion_curso sc on sa.id_seccion_curso = sc.id
            INNER JOIN curso c on sc.id_curso = c.id
            LEFT JOIN (SELECT DISTINCT COUNT(aa.id) as faltas, aa.id as asistenciaID
                        from alumno a
                        INNER JOIN alumno_seccion sa on a.id = sa.id_alumno
                        INNER JOIN asistencia_alumno aa on sa.id = aa.id_alumno_seccion
                        INNER JOIN seccion_curso sc on sa.id_seccion_curso = sc.id
                        INNER JOIN curso c on sc.id_curso = c.id
                        where a.id = $alumnoid AND aa.estado = 'Falta'
                        GROUP  by c.nombre, sc.nombre_seccion, c.id) as falta on aa.id = falta.asistenciaID
            LEFT JOIN (SELECT DISTINCT COUNT(aa.id) as asistencia , aa.id as asistenciaID
                        from alumno a
                        INNER JOIN alumno_seccion sa on a.id = sa.id_alumno
                        INNER JOIN asistencia_alumno aa on sa.id = aa.id_alumno_seccion
                        INNER JOIN seccion_curso sc on sa.id_seccion_curso = sc.id
                        INNER JOIN curso c on sc.id_curso = c.id
                        where a.id = $alumnoid AND aa.estado = 'Asistio'
                        GROUP  by c.nombre, sc.nombre_seccion, c.id) as asistencia on aa.id = asistencia.asistenciaID
            LEFT JOIN (SELECT DISTINCT COUNT(aa.id) as tardanza , aa.id as asistenciaID
                        from alumno a
                        INNER JOIN alumno_seccion sa on a.id = sa.id_alumno
                        INNER JOIN asistencia_alumno aa on sa.id = aa.id_alumno_seccion
                        INNER JOIN seccion_curso sc on sa.id_seccion_curso = sc.id
                        INNER JOIN curso c on sc.id_curso = c.id
                        where a.id = $alumnoid AND aa.estado = 'Tardanza'
                        GROUP  by c.nombre, sc.nombre_seccion, c.id) as tardanza on aa.id = tardanza.asistenciaID
                        
            where a.id = $alumnoid
            GROUP  by c.nombre, sc.nombre_seccion, c.id
        "));

            return Datatables::of($cursos)->make(true);
        } catch (\Exception $e) {
            return json_encode([
                "sEcho" => 1,
                "iTotalRecords" => 0,
                "iTotalDisplayRecords" => 0,
                "aaData" => []
            ]);
        }
    }
}
