<?php

namespace App\Http\Controllers;

use App\User;
use App\Report;
use App\Filetype;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Box\Spout\Common\Entity\Row;
use Box\Spout\Common\Entity\Style\Color;
use Illuminate\Database\Eloquent\Builder;
use Box\Spout\Common\Entity\Style\CellAlignment;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use App\Http\Controllers\NotificationsController;

class ReportsController extends Controller
{
    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index()
    {
        return view('reports.list');
    }

    public function reportInstitution(Request $request, $method_call = 'web')
    {
        try {
            $writer = WriterEntityFactory::createXLSXWriter();

            if($method_call == 'web'){
                $writer->openToBrowser("Requisitos Documentales por Institución.xlsx");
            }

            if($method_call == 'job'){
                $name_file = $this->getCode().'.xlsx';
                $url_file = base_path('')."/storage/app/public/".$name_file;
                $writer->openToFile($url_file);
                $url_ex = asset('storage/'.$name_file);
            }

            $sheet = $writer->getCurrentSheet();
            $sheet->setName(__('users'));
            $style = (new StyleBuilder())
               ->setFontBold()
               ->setFontSize(10)
               ->setFontColor(Color::BLACK)
               ->setShouldWrapText()
               ->setCellAlignment(CellAlignment::CENTER)
               ->setBackgroundColor(Color::rgb(245, 245, 245))
               ->build();
            $cells = [
                WriterEntityFactory::createCell(__('institution')),
                WriterEntityFactory::createCell(__('program')),
                WriterEntityFactory::createCell(__('rotation')),
                WriterEntityFactory::createCell(__('document')),
                WriterEntityFactory::createCell(__('name')),
                WriterEntityFactory::createCell(__('last_name')),
                WriterEntityFactory::createCell(__('email')),
                WriterEntityFactory::createCell(__('phone')),
            ];

            $file_types = Filetype::select('id','name')->orderBy('name', 'asc')->get();
            foreach ($file_types as $key => $type) {
                $cells[] = WriterEntityFactory::createCell($type->name);
            }
            $singleRow = WriterEntityFactory::createRow($cells,$style);
            $writer->addRow($singleRow);

            $users = User::with('roles','userdocuments','userprograms')
                            ->whereHas('userprograms', function (Builder $query) use($request){
                                $query->whereDate('rotation_end_date', '>=', Carbon::now()->format('Y-m-d'));
                                    if($request->institution_id != 0){
                                        $query->where('institution_id', $request->institution_id);
                                    }
                                    if($request->program_id != 0){
                                        $query->where('program_id', $request->program_id);
                                    }
                                    if($request->rotation_id != 0){
                                        $query->where('rotation_id', $request->rotation_id);
                                    }
                                    if($request->rotation_start_range_start != "" && $request->rotation_start_range_end != ""){
                                        $query->whereBetween('rotation_start_date', [$request->rotation_start_range_start, $request->rotation_start_range_end]);
                                    }
                                    if($request->rotation_end_range_start != "" && $request->rotation_end_range_end != ""){
                                        $query->whereBetween('rotation_end_date', [$request->rotation_end_range_start, $request->rotation_end_range_end]);
                                    }
                            })->get();

            foreach ($users as $user) {
                $list_by_role = DB::table('filetype_role')
                                ->where('role_id', $user->roles[0]->id)
                                ->get();
                $value = [$user->userprograms[0]->institution->name,$user->userprograms[0]->program->name,$user->userprograms[0]->rotation->name,$user->document,$user->name,$user->last_name,$user->email,$user->phone];
                foreach ($file_types as $j => $type) {
                    $state = "Sin cargar";
                    foreach ($user->userdocuments as $k => $document) {
                        if($type->id == $document->filetype_id){
                            $state = $document->filestatus->name . " " .$document->expedition_date;
                        }
                    }
                    if(!$list_by_role->pluck('filetype_id')->contains($type->id)){
                        $state = "No aplica";
                    }
                    $value[] = $state;
                }
                $row = WriterEntityFactory::createRowFromArray($value);
                $writer->addRow($row);
            }
            $writer->close();

            if($method_call == 'job'){
                return $url_ex;
            }
        } catch (\Throwable $th) {
            return;
        }
    }

    public function reportStudent(Request $request,$method_call = 'web')
    {
        try {
            $writer = WriterEntityFactory::createXLSXWriter();
            if($method_call == 'web'){
                $writer->openToBrowser("Requisitos Documentales por Estudiante.xlsx");
            }
            if($method_call == 'job'){
                $name_file = $this->getCode().'.xlsx';
                $url_file = base_path('')."/storage/app/public/".$name_file;
                $writer->openToFile($url_file);
                $url_ex = asset('storage/'.$name_file);
            }
            $sheet = $writer->getCurrentSheet();
            $sheet->setName(__('users'));
            $style = (new StyleBuilder())
               ->setFontBold()
               ->setFontSize(10)
               ->setFontColor(Color::BLACK)
               ->setShouldWrapText()
               ->setCellAlignment(CellAlignment::CENTER)
               ->setBackgroundColor(Color::rgb(245, 245, 245))
               ->build();
            $cells = [
                WriterEntityFactory::createCell(__('institution')),
                WriterEntityFactory::createCell(__('program')),
                WriterEntityFactory::createCell(__('document')),
                WriterEntityFactory::createCell(__('name')),
                WriterEntityFactory::createCell(__('last_name')),
                WriterEntityFactory::createCell(__('email')),
                WriterEntityFactory::createCell(__('phone')),
            ];

            $singleRow = WriterEntityFactory::createRow($cells,$style);
            $writer->addRow($singleRow);

            if($request->document_student != "" && $user = User::where('document', $request->document_student)->with('roles','userdocuments','userprograms')->first()){
                $institution = isset($user->userprograms[0]) ? $user->userprograms[0]->institution->name : '';
                $program = isset($user->userprograms[0]) ? $user->userprograms[0]->program->name : '';
                $cell = [$institution,$program,$user->document,$user->name,$user->last_name,$user->email,$user->phone];
                $row = WriterEntityFactory::createRowFromArray($cell);
                $writer->addRow($row);

                $separator = WriterEntityFactory::createRowFromArray([]);
                $writer->addRow($separator);

                $styleDocuments = (new StyleBuilder())
                ->setFontBold()
                ->setFontSize(12)
                ->setFontColor(Color::BLACK)
                ->setCellAlignment(CellAlignment::CENTER)
                ->setBackgroundColor(Color::rgb(255, 204, 102))
                ->build();
                $cellsDocuments = [
                    WriterEntityFactory::createCell(__('requirement')),
                    WriterEntityFactory::createCell(__('state')),
                    WriterEntityFactory::createCell(__('expiration_date')),
                ];

                $singleRow2 = WriterEntityFactory::createRow($cellsDocuments,$styleDocuments);
                $writer->addRow($singleRow2);

                $list_by_role = DB::table('filetype_role')
                                ->join('filetypes', 'filetype_role.filetype_id', '=', 'filetypes.id')
                                ->where('role_id', $user->roles[0]->id)
                                ->get();

                foreach ($list_by_role as $j => $type) {
                    $value = [];
                    $name_document = $type->name;
                    $state = "Sin cargar";
                    $date = "";
                    foreach ($user->userdocuments as $document) {
                        if($type->filetype_id == $document->filetype_id){
                            $state = $document->filestatus->name;
                            $date  = $document->expiration_date;
                        }
                    }
                    $value[] = $name_document;
                    $value[] = $state;
                    $value[] = $date;
                    $row = WriterEntityFactory::createRowFromArray($value);
                    $writer->addRow($row);
                }

                $writer->addRow($separator);

                $styleDocuments = (new StyleBuilder())
                ->setFontBold()
                ->setFontSize(12)
                ->setFontColor(Color::BLACK)
                ->setCellAlignment(CellAlignment::CENTER)
                ->setBackgroundColor(Color::rgb(255, 204, 102))
                ->build();
                $cellsDocuments = [
                    WriterEntityFactory::createCell(__('rotation')),
                    WriterEntityFactory::createCell(__('rotation_start_date')),
                    WriterEntityFactory::createCell(__('rotation_end_date')),
                ];

                $singleRow2 = WriterEntityFactory::createRow($cellsDocuments,$styleDocuments);
                $writer->addRow($singleRow2);

                foreach ($user->userprograms as $f => $program) {
                    $rotation = [$program->rotation->name,$program->rotation_start_date,$program->rotation_end_date];
                    $row = WriterEntityFactory::createRowFromArray($rotation);
                    $writer->addRow($row);
                }
            }
            $writer->close();

            if($method_call == 'job'){
                return $url_ex;
            }
        } catch (\Throwable $th) {
            return;
        }
    }

    public function getCode()
    {
        return time() . rand(1, 9) . rand(1, 5);
    }

    public function sendReport($report_id,$notification_id,$params)
    {
        $params = json_decode($params, true);
        $request = new Request($params);
        $report = Report::select('name')->where('id', $report_id)->first();
        $attached_name = $report->name.".xlsx";
        $noti = new NotificationsController();
        switch ($report_id) {
            case '1':
                $attached = $this->reportInstitution($request, 'job');
                return $noti->sendNotification($notification_id, $extra_body = '', $attached, $attached_name);
                break;
            case '2':
                $attached = $this->reportStudent($request, 'job');
                return $noti->sendNotification($notification_id, $extra_body = '', $attached, $attached_name);
                break;
            default:
                break;
        }
    }
}