<?php

namespace App\Services;

use App\AcademyUser;
use App\AcademyCategory;
use App\AcademyLocation;
use App\AcademyPurchase;
use App\AcademySchedule;
use App\AcademyState;
use App\AcademyStateUser;
use App\Address;
use App\DocumentType;
use App\Http\Controllers\AcademyParameterController;
use App\Http\Controllers\UtilController;
use App\PaymentTransaction;
use Carbon\Carbon;
use Exception;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class AcademyService
{

    private $utilController;
    private $academyParameterController;

    public function __construct()
    {
        $this->utilController = new UtilController;
        $this->academyParameterController = new AcademyParameterController;
    }
    public function find($id)
    {
        return AcademyUser::find($id);
    }

    public function validateAcademyStudents()
    {
        $students = $this->countAcademyStudents();
        return $students > 0;
    }

    public function academyStudents()
    {
        $students = AcademyUser::select(
            DB::raw('UPPER(CONCAT(student_name, " ", student_last_name)) AS name'),
            'student_name AS first_name',
            'student_last_name AS last_name',
            'document_types.alias AS document_type',
            'identification AS document',
            'student_name AS first_name',
            'student_last_name AS last_name',
        )->join('document_types', 'document_types.id', '=', 'academy_users.student_document_type_id')
            ->where('user_id', Auth::user()->id)
            ->where('academy_users.active', 1)
            ->orderBy('name', 'ASC')
            ->get()->toArray();

        return $students;
    }

    public function mapAcademyData($data)
    {
        $academyUser = AcademyUser::where('identification', $data['document'])->where('user_id', Auth::user()->id)->first();
        if ($academyUser) {
            $data['first_name']     = $academyUser->student_name;
            $data['last_name']      = $academyUser->student_last_name;
            $data['dob']            = $academyUser->birthdate;
            $data['document_type_id'] = $academyUser->student_document_type_id;
            $data['document']       = $academyUser->identification;
            $data['email']          = $academyUser->mail;
            $data['phone']          = $academyUser->phone;

            $data['fileIdentificationDocument'] = 'pending';
            $data['fileInsuranceDocument']      = 'pending';

            $data['guardianFirstName']  = $academyUser->advisor_name;
            $data['guardianLastName']   = $academyUser->advisor_last_name;
            $data['guardianDocument']   = $academyUser->advisor_identification;
            $data['guardianEmail']      = $academyUser->mail;
            $data['guardianPhone']      = $academyUser->phone;
        }
        return $data;
    }

    private function countAcademyStudents()
    {
        $students = AcademyUser::select(
            'academy_users.id'
        )->where('user_id', Auth::user()->id)
            ->where('academy_users.active', 1)
            ->count();

        return $students;
    }

    public function mapDataAcademy($academyUserId)
    {
        $academyUser = $this->find($academyUserId);

        // Campos del deportista
        $addresses = Address::where('id', $academyUser->address_id)->first();
        $addressesDirection = $addresses ? $addresses->direction : '';
        $addressesDistrict = $addresses ? $addresses->district : '';

        // Campos de la academia
        $academyCategory = AcademyCategory::where('id', $academyUser->academy_category_id)->first();
        $academyLocation = AcademyLocation::where('id', ($academyCategory ? $academyCategory->academy_location_id : $academyUser->academy_location_id))->first();
        $academySchedule = AcademySchedule::where('id', $academyUser->academy_schedule_id)->first();
        $inscriptionValue = $academyCategory ? $academyCategory->inscription_value . '' : '';
        $monthlyPayment = $academyCategory ? $academyCategory->monthly_payment . '' : '';
        $now = Carbon::now();
        $startTerm = $this->academyParameterController->registrationStartDate($academyUser->type_academy);
        if (!$startTerm || $startTerm < $now) {
            $startTerm = $now;
        } else if ($startTerm instanceof string) {
            $startTerm = Carbon::parse($startTerm);
        }
        $endTerm = $this->academyParameterController->registrationEndDate($academyUser->type_academy);
        if (!$endTerm || $endTerm < $now) {
            $endTerm = $now;
        } else if ($endTerm instanceof string) {
            $endTerm = Carbon::parse($endTerm);
        }

        // Campos del responsable
        $advisorDocumentType = DocumentType::where('id', $academyUser->advisor_document_type_id)->first();
        $academyUserDocument = DocumentType::where('id', $academyUser->student_document_type_id)->first();
        $advisorFullName = $academyUser->advisor_name . ' ' . $academyUser->advisor_last_name;
        $advisorNames = $academyUser->advisor_name ?? '';
        $advisorLastNames = $academyUser->advisor_last_name ?? '';
        $advisorDocument = $academyUser->advisor_identification ?? '';
        $advisorPhone = $academyUser->advisor_phone ?? $academyUser->phone ?? '';
        $advisorMail = $academyUser->advisor_mail ?? $academyUser->mail ?? '';
        $advisorOcupation = $academyUser->advisor_ocupation ?? '';
        $advisorRelation = $academyUser->advisor_relation ?? 'Responsable';

        $data = [
            ['de' => 'FECHA_DILIGENCIAMIENTO',                      'para' => date('Y-m-d')],
            ['de' => 'FECHA_DILIGENCIAMIENTO_DIA',                  'para' => date('d')],
            ['de' => 'FECHA_DILIGENCIAMIENTO_MES',                  'para' => date('m')],
            ['de' => 'FECHA_DILIGENCIAMIENTO_MES_NOMBRE',           'para' => $this->utilController->getTextMonth(date('Y-m-d'))],
            ['de' => 'FECHA_DILIGENCIAMIENTO_ANIO',                 'para' => date('Y')],
            ['de' => 'ALUMNO_ANTIGUEDAD',                           'para' => $academyUser->last_inscription_year ?? ' '],
            ['de' => 'ALUMNO_ANTIGUO',                              'para' => $academyUser->last_inscription_year && str_contains(strtolower($academyUser->last_inscription_year), 'antiguo') ? 'X' : ' '],
            ['de' => 'ALUMNO_NUEVO',                                'para' => $academyUser->last_inscription_year && (str_contains(strtolower($academyUser->last_inscription_year), 'nuevo') || str_contains(strtolower($academyUser->last_inscription_year), 'primer')) ? 'X' : ' '],
            ['de' => 'ALUMNO_OTRO_CLUB',                            'para' => $academyUser->last_inscription_year && (str_contains(strtolower($academyUser->last_inscription_year), 'otro')) ? 'X' : ' '],
            ['de' => 'ALUMNO_APELLIDOS',                            'para' => $academyUser->student_last_name ?? ' '],
            ['de' => 'ALUMNO_NOMBRES',                              'para' => $academyUser->student_name ?? ' '],
            ['de' => 'ALUMNO_FECHA_NACIMIENTO',                     'para' => $academyUser->birthdate ?? ' '],
            ['de' => 'ALUMNO_DOCUMENTO_NUMERO',                     'para' => $academyUser->identification ?? ' '],
            ['de' => 'ALUMNO_TIPO_DOCUMENTO',                       'para' => $academyUserDocument->name ?? ' '],
            ['de' => 'ALUMNO_TIPO_DOCUMENTO_ALIAS',                 'para' => $academyUserDocument->alias ?? ' '],
            ['de' => 'ALUMNO_RH',                                   'para' => $academyUser->blood_type ?? ' '],
            ['de' => 'ALUMNO_EPS',                                  'para' => $academyUser->eps ?? ' '],
            ['de' => 'ALUMNO_DIRECCION',                            'para' => $addressesDirection ?? ' '],
            ['de' => 'ALUMNO_BARRIO',                               'para' => $addressesDistrict ?? ' '],
            ['de' => 'ALUMNO_COLEGIO',                              'para' => $academyUser->school_name ?? ' '],
            ['de' => 'ALUMNO_GRADO',                                'para' => $academyUser->school_grade ?? ' '],
            ['de' => 'PLAN_SEDE',                                   'para' => $academyLocation ? $academyLocation->name : ' '],
            ['de' => 'ALUMNO_NOMBRE_COMPLETO',                      'para' => $academyUser->student_name . ' ' . $academyUser->student_last_name],
            ['de' => 'ALUMNO_EMAIL',                                'para' => $academyUser->mail ?? ' '],
            ['de' => 'ALUMNO_EDAD',                                 'para' => ((string) $academyUser->age) ?? ' '],
            ['de' => 'ALUMNO_SEDE',                                 'para' => $academyLocation ? $academyLocation->name : ' '],
            ['de' => 'ALUMNO_CATEGORIA',                            'para' => $academyCategory ? $academyCategory->name : ' '],
            ['de' => 'ALUMNO_HORARIO',                              'para' => $academySchedule ? $academySchedule->name : ' '],
            ['de' => 'ALUMNO_TALLA',                                'para' => $academyUser->tshirt_size ?? ' '],
            ['de' => 'ALUMNO_TIPO_UNIFORME',                        'para' => $academyUser->uniform_type ?? ' '],
            ['de' => 'VALOR_MATRICULA',                             'para' => $inscriptionValue ?? ' '],
            ['de' => 'VALOR_MENSUALIDAD',                           'para' => $monthlyPayment ?? ' '],
            ['de' => 'FECHA_INICIO_MATRICULA',                      'para' => $startTerm->toDateString() ?? ' '],
            ['de' => 'FECHA_FIN_MATRICULA',                         'para' => $endTerm->toDateString() ?? ' '],
            ['de' => 'RESPONSABLE_NOMBRE_COMPLETO',                 'para' => $advisorFullName],
            ['de' => 'RESPONSABLE_NOMBRES',                         'para' => $advisorNames],
            ['de' => 'RESPONSABLE_APELLIDOS',                       'para' => $advisorLastNames],
            ['de' => 'RESPONSABLE_TIPO_DOCUMENTO',                  'para' => $advisorDocumentType->name ?? ' '],
            ['de' => 'RESPONSABLE_TIPO_DOCUMENTO_ALIAS',            'para' => $advisorDocumentType->alias ?? ' '],
            ['de' => 'RESPONSABLE_DOCUMENTO_NUMERO',                'para' => $advisorDocument],
            ['de' => 'RESPONSABLE_TELEFONO',                        'para' => (string) $advisorPhone],
            ['de' => 'RESPONSABLE_EMAIL',                           'para' => $advisorMail],
            ['de' => 'RESPONSABLE_OCUPACION',                       'para' => $advisorOcupation],
            ['de' => 'RESPONSABLE_RELACION',                        'para' => $advisorRelation],
        ];

        switch ($advisorRelation) {
            case 'Padre':
                $data[] = [
                    'de' => 'PADRE_NOMBRE_COMPLETO',
                    'para' => $advisorFullName
                ];
                $data[] = [
                    'de' => 'PADRE_DOCUMENTO_NUMERO',
                    'para' => $advisorDocument
                ];
                $data[] = [
                    'de' => 'PADRE_OCUPACION',
                    'para' => $advisorOcupation
                ];
                break;

            case 'Madre':
                $data[] = [
                    'de' => 'MADRE_NOMBRE_COMPLETO',
                    'para' => $advisorFullName
                ];
                $data[] = [
                    'de' => 'MADRE_DOCUMENTO_NUMERO',
                    'para' => $advisorDocument
                ];
                $data[] = [
                    'de' => 'MADRE_OCUPACION',
                    'para' => $advisorOcupation
                ];
                break;

            case 'Responsable':
                $data[] = [
                    'de' => 'ACUDIENTE_NOMBRE_COMPLETO',
                    'para' => $advisorFullName
                ];
                $data[] = [
                    'de' => 'ACUDIENTE_DOCUMENTO_NUMERO',
                    'para' => $advisorDocument
                ];
                $data[] = [
                    'de' => 'ACUDIENTE_OCUPACION',
                    'para' => $advisorOcupation
                ];
                break;
        }

        if ($academyUser->guardian_information) {
            $guardianInformation = json_decode($academyUser->guardian_information);
            if (!$guardianInformation) {
                return $data;
            }

            if (isset($guardianInformation->name) && $guardianInformation->name) {
                $data = $this->replaceValueElement($data, 'RESPONSABLE_NOMBRE_COMPLETO', $guardianInformation->name . ' ' . $guardianInformation->last_name);
                $data = $this->replaceValueElement($data, 'RESPONSABLE_NOMBRES', $guardianInformation->name);
                $data = $this->replaceValueElement($data, 'RESPONSABLE_APELLIDOS', $guardianInformation->last_name);
            }
            if (isset($guardianInformation->document) && $guardianInformation->document) {
                $data = $this->replaceValueElement($data, 'RESPONSABLE_DOCUMENTO_NUMERO', $guardianInformation->document);
            }

            if (isset($guardianInformation->document_type_id) && $guardianInformation->document_type_id) {
                $documentType = DocumentType::find($guardianInformation->document_type_id);
                $data = $this->replaceValueElement($data, 'RESPONSABLE_TIPO_DOCUMENTO', $documentType->name);
                $data = $this->replaceValueElement($data, 'RESPONSABLE_TIPO_DOCUMENTO_ALIAS', $documentType->alias);
            }

            if (property_exists($guardianInformation, 'phone') && $guardianInformation->phone) {
                $data = $this->replaceValueElement($data, 'RESPONSABLE_TELEFONO', (string)$guardianInformation->phone);
            }

            if (isset($guardianInformation->email) && $guardianInformation->email) {
                $data = $this->replaceValueElement($data, 'RESPONSABLE_EMAIL', $guardianInformation->email);
            }

            if (isset($guardianInformation->ocupation) && $guardianInformation->ocupation) {
                $data = $this->replaceValueElement($data, 'RESPONSABLE_OCUPACION', $guardianInformation->ocupation);
            }

            if (isset($guardianInformation->relation) && $guardianInformation->relation) {
                $data = $this->replaceValueElement($data, 'RESPONSABLE_RELACION', $guardianInformation->relation);
            }

            if (isset($guardianInformation->question_manage_public_resources)) {
                $data = $this->replaceValueElement($data, 'MANEJA_RECURSOS_PUBLICOS_SI', $guardianInformation->question_manage_public_resources ? 'X' : ' ');
                $data = $this->replaceValueElement($data, 'MANEJA_RECURSOS_PUBLICOS_NO', $guardianInformation->question_manage_public_resources ? ' ' : 'X');
            }

            if (isset($guardianInformation->question_public_recognition)) {
                $data = $this->replaceValueElement($data, 'RECONOCIMIENTO_PUBLICO_SI', $guardianInformation->question_public_recognition ? 'X' : ' ');
                $data = $this->replaceValueElement($data, 'RECONOCIMIENTO_PUBLICO_NO', $guardianInformation->question_public_recognition ? ' ' : 'X');
            }

            if (isset($guardianInformation->question_degree_public_authority)) {
                $data = $this->replaceValueElement($data, 'GRADO_PUBLICO_SI', $guardianInformation->question_degree_public_authority ? 'X' : ' ');
                $data = $this->replaceValueElement($data, 'GRADO_PUBLICO_NO', $guardianInformation->question_degree_public_authority ? ' ' : 'X');
            }

            if (isset($guardianInformation->question_familiarity)) {
                $data = $this->replaceValueElement($data, 'FAMILIARIDAD_SI', $guardianInformation->question_familiarity ? 'X' : ' ');
                $data = $this->replaceValueElement($data, 'FAMILIARIDAD_NO', $guardianInformation->question_familiarity ? ' ' : 'X');
            }

            if (isset($guardianInformation->question_is_pep)) {
                $data = $this->replaceValueElement($data, 'PEP_SI', $guardianInformation->question_is_pep ? 'X' : ' ');
                $data = $this->replaceValueElement($data, 'PEP_NO', $guardianInformation->question_is_pep ? ' ' : 'X');
            }

            if (isset($guardianInformation->question_origin_of_funds)) {
                $data = $this->replaceValueElement($data, 'ORIGEN_FONDOS', $guardianInformation->question_origin_of_funds);
            }
        }

        return $this->numberToStringValueElement($data);
    }

    public function getActiveUsers($franchises)
    {
        $states = AcademyState::select('id')->where('active', 1)->get();

        return AcademyUser::whereIn('academy_state_id', $states->pluck('id')->toArray())
            ->when($franchises, function ($query) use ($franchises) {
                $query->whereIn('academy_location_id', $franchises);
            })
            ->get();
    }

    public function getUserBilling($userId, $year)
    {
        return AcademyPurchase::with('payment_transaction')
            ->where('academy_user_id', $userId)
            ->whereYear('academy_purchases.start_term', $year)
            ->get();
    }

    public function getAcademyData($request)
    {
        $academyType = $request->academyType;
        if (!$academyType) {
            throw new Exception('No existe el filtro de tipo academia, es necesario el parametro: academyType', 400);
        }
        $sinceDate = $request->sinceDate;
        if (!$sinceDate) {
            throw new Exception('No existe el filtro de fecha, es necesario el parametro: sinceDate', 400);
        }

        DB::statement("SET sql_mode = ''");
        $query = DB::table('academy_users')
            ->select([
                // Campos deportista
                DB::raw('IFNULL(SHA2(academy_users.id, 256), "") AS id'),
                DB::raw('IFNULL(academy_users.student_name, "") AS studentFirstName'),
                DB::raw('IFNULL(academy_users.student_last_name, "") AS studentLastName'),
                DB::raw('IFNULL(academy_state.name, "") AS studentProcessStatus'),
                DB::raw('IFNULL(document_types.name, "") AS studentDocumentType'),
                DB::raw('IFNULL(academy_users.identification, "") AS studentDocumentNumber'),
                DB::raw('IFNULL(academy_users.student_academy_code, "") AS studentAcademyCode'),
                DB::raw('IFNULL(academy_users.school_name, "") AS studentEducationalInstitution'),
                DB::raw('IFNULL(academy_users.school_grade, "") AS studentGradeLevel'),
                DB::raw('IFNULL(academy_users.birthdate, "") AS studentBirthDate'),
                DB::raw('IFNULL(academy_users.age, "") AS studentAge'),
                DB::raw('IFNULL(academy_users.phone, "") AS studentPhone'),

                // Campos contacto
                DB::raw('IFNULL(appUser.first_name, "") AS contactFirstName'),
                DB::raw('IFNULL(appUser.last_name, "") AS contactLastName'),
                DB::raw('IFNULL(appUser.email, "") AS contactEmail'),
                DB::raw('IFNULL(document_types_contact.name, "") AS contactDocumentType'),
                DB::raw('IFNULL(appUser.document, "") AS contactDocumentNumber'),
                DB::raw('IFNULL(academy_users.advisor_relation, "") AS contactRelationship'),
                DB::raw('IFNULL(academy_users.advisor_ocupation, "") AS contactOccupation'),

                // Campos academia
                DB::raw("
                    CASE academy_users.type_person
                        WHEN 'ALL' THEN '" . __('messages.academy_document.all') . "'
                        WHEN 'JURIDIC_PERSON' THEN '" . __('messages.academy_document.juridic_person') . "'
                        WHEN 'NATURAL_PERSON' THEN '" . __('messages.academy_document.natural_person') . "'
                        ELSE ''
                    END AS personType
                "),
                DB::raw('IFNULL(advisor_business_name, "") AS businessName'),
                DB::raw('IFNULL(advisor_property_name, "") AS establishmentName'),
                DB::raw('IFNULL(academy_categories.name, "") AS category'),
                DB::raw("IFNULL(GROUP_CONCAT(DISTINCT(CONCAT_WS(' ', users1.first_name, users1.last_name, CONCAT('(',users1.email,')'))) ORDER BY users1.id ASC), '') AS coaches"),
                DB::raw('IFNULL(GROUP_CONCAT(DISTINCT(CONCAT(academy_discounts.name, " (", academy_discounts.discount, "%)"))), "") AS specialConditions'),
                DB::raw("DATE_FORMAT(academy_users.created_at, '%Y-%m-%d %r') AS enrollmentDate"),
                DB::raw('IFNULL(academy_users.payment_status, "") AS paymentStatus'),
                DB::raw('IF(academy_users.active, "SI", "NO") AS isActiveStudent'),
            ])
            ->addSelect([
                'lastPaymentDate' => PaymentTransaction::selectRaw('IFNULL(DATE_FORMAT(MAX(payment_transactions.payment_date) , "%Y-%m-%d %r"), "")')
                    ->join('academy_purchases', 'academy_purchases.payment_transaction_id', '=', 'payment_transactions.id')
                    ->whereColumn('academy_purchases.academy_user_id', 'academy_users.id')
            ])
            ->addSelect([
                'lastStatusDate' => AcademyStateUser::selectRaw('IFNULL(DATE_FORMAT(MAX(academy_state_users.updated_at) , "%Y-%m-%d %r"), "")')
                    ->whereColumn('academy_state_users.academy_user_id', 'academy_users.id')
            ])
            ->join('document_types', 'document_types.id', '=', 'academy_users.student_document_type_id')
            ->leftJoin('document_types AS document_types_advisor', 'document_types_advisor.id', '=', 'academy_users.advisor_document_type_id')
            ->join('academy_states AS academy_state', 'academy_state.id', '=', 'academy_users.academy_state_id')
            ->leftJoin('academy_categories', function ($join) {
                $join->on('academy_categories.id', '=', 'academy_users.academy_category_id');
            })
            ->leftJoin('academy_categories_schedules', 'academy_categories_schedules.academy_category_id', '=', 'academy_categories.id')
            ->leftJoin('academy_schedules', function ($join) {
                $join->on('academy_schedules.id', '=', 'academy_users.academy_schedule_id')
                    ->where('academy_schedules.active', true);
            })
            ->leftJoin('academy_schedules_coaches', 'academy_schedules_coaches.academy_schedule_id', '=', 'academy_schedules.id')
            ->leftJoin('users AS users1', function ($join) {
                $join->on('users1.id', '=', 'academy_schedules_coaches.user_id')->whereNull('users1.deleted_at');
            })
            ->leftJoin('academy_locations', function ($join) {
                $join->on('academy_locations.id', '=', 'academy_categories.academy_location_id');
            })
            ->leftJoin('academy_users_discounts', 'academy_users_discounts.academy_user_id', '=', 'academy_users.id')
            ->leftJoin('academy_discounts', function ($join) {
                $join->on('academy_discounts.id', '=', 'academy_users_discounts.academy_discount_id');
            })
            ->leftJoin('academy_periods', function ($join) {
                $join->on('academy_periods.id', '=', 'academy_users.academy_period_id');
            })
            ->leftJoin('users AS appUser', 'appUser.id', '=', 'academy_users.user_id')
            ->leftJoin('document_types AS document_types_contact', 'document_types_contact.id', '=', 'appUser.document_type_id')
            ->leftJoin('user_tags', 'academy_users.id', '=', 'user_tags.academy_user_id')
            ->leftjoin('tags', function ($join) {
                $join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
            })
            ->whereNull('academy_users.deleted_at')
            ->where('academy_users.type_academy', $academyType)
            ->groupBy('academy_users.id');

        $query->when($sinceDate, function ($query) use ($sinceDate) {
            if ($sinceDate != 'null') {
                $query->where(function ($query) use ($sinceDate) {
                    $query->where('academy_users.created_at', '>=', $sinceDate)
                        ->orWhere('academy_users.updated_at', '>=', $sinceDate);
                });
            }
        });

        $query->orderBy('academy_users.id', 'DESC');
        DB::statement("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
        return $query;
    }

    private function replaceValueElement($data, $key, $value)
    {
        $newData = [];
        $replaced = false;
        foreach ($data as $item) {
            if ($item['de'] == $key) {
                $item['para'] = $value;
                $replaced = true;
            }
            $newData[] = $item;
        }
        if (!$replaced) {
            $newData[] = [
                'de'    => $key,
                'para'  => $value
            ];
        }
        return $newData;
    }

    private function numberToStringValueElement($data)
    {
        $newData = [];
        foreach ($data as $item) {
            $item['para'] = (string)$item['para'];
            $newData[] = $item;
        }
        return $newData;
    }
}