<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use PDOException;
use Illuminate\Support\Carbon;

class MCOMORController extends Controller
{
    public function create(Request $request){
        $validator = Validator::make($request->all(), [
            'id_usuario' => 'required|string|max:50',
            'id_jefe_directo' => 'required|string|max:50',
            'id_usuario_registra' => 'required|string|max:50',
            'rfc_empresa' => 'required|string',
        ]);

        if ($validator->fails()) {
            return $this->makeResponse(
                true,
                "ERROR_USU(MCOMOR000): Se encontraron uno o más errores",
                $this->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $organigrama = $request->all();

        if ($organigrama['id_usuario'] == $organigrama['id_jefe_directo'] || $organigrama['id_usuario'] == $organigrama['id_jefe_sustituto']) {
            //return response()->json(['error' => 'error_id_user_duplicated'], 400);
            return $this->makeResponse(true, "ERROR_USU(MCOMOR001): El id_usuario está duplicado en el formulario", [], 400);
        }

        $pdo = DB::connection()->getPdo();

        //id_user
        $qryUsr1 = 'SELECT * FROM S001V01TUSUA WHERE USUA_IDUS = :id';
        $gstUsr1 = $pdo->prepare($qryUsr1);
        $gstUsr1->bindParam(":id", $organigrama['id_usuario']);
                
        if (!$gstUsr1->execute()) {
            //return response()->json(['error' => 'error_id_user_not_found'], 500);
            return $this->makeResponse(true, "ERROR_SER(MCOMOR000): No se pudo consultar el registro en la base", [], 500);
        }

        $usr1 = $gstUsr1->fetchAll($pdo::FETCH_ASSOC);

        if (count($usr1) < 1) {
            //return response()->json(['error' => 'error_id_user_not_found'], 404);
            return $this->makeResponse(true, "ERROR_USU(MCOMOR002): El Usuario no existe", [], 404);
        }

        //id_jefe_directo
        $qryUsr2 = 'SELECT * FROM S001V01TUSUA WHERE USUA_IDUS = :id';
        $gstUsr2 = $pdo->prepare($qryUsr2);
        $gstUsr2->bindParam(":id", $organigrama['id_jefe_directo']);

        if (!$gstUsr2->execute()) {
            return $this->makeResponse(true, "ERROR_SER(MCOMOR001): No se pudo consultar el registro en la base", [], 500);
        }

        $usr2 = $gstUsr2->fetchAll($pdo::FETCH_ASSOC);

        if (count($usr2) < 1) {
            return $this->makeResponse(true, "ERROR_USU(MCOMOR003): El Jefe Directo no existe", [], 404);
        }

        //id_jefe_sustituto
        $qryUsr3 = 'SELECT * FROM S001V01TUSUA WHERE USUA_IDUS = :id';
        $gstUsr3 = $pdo->prepare($qryUsr3);
        $gstUsr3->bindParam(":id", $organigrama['id_jefe_sustituto']);

        if (!$gstUsr3->execute()) {
            return $this->makeResponse(true, "ERROR_SER(MCOMOR002): No se pudo consultar el registro en la base", [], 500);
        }

        $usr3 = $gstUsr3->fetchAll($pdo::FETCH_ASSOC);

        if (count($usr3) < 1) {
            return $this->makeResponse(true, "ERROR_USU(MCOMOR004): El Jefe Sustituto no existe", [], 404);
        }

        $qry = 'INSERT INTO S001V01TORGA (ORGA_IDUS, ORGA_RFCE, ORGA_JEDI, ORGA_JESU, ORGA_USRE, ORGA_FERE, ORGA_FEAR) 
            VALUES (:idus, :rfce, :jedi, :jesu, :usre, :fere, CURRENT_TIMESTAMP)';
        $gst = $pdo->prepare($qry);

        $encriptacion = new EncController();
        $rfce = $encriptacion->desencriptar($organigrama['rfc_empresa']);
        $fere = Carbon::now()->timezone('America/Mexico_City')->toDateTimeString();

        $gst->bindParam(":idus", $organigrama['id_usuario']);
        $gst->bindParam(":rfce", $rfce);
        $gst->bindParam(":jedi", $organigrama['id_jefe_directo']);
        $gst->bindParam(":jesu", $organigrama['id_jefe_sustituto']);
        $gst->bindParam(":usre", $organigrama['id_usuario_registra']);
        $gst->bindParam(":fere", $fere);

        try {
            if (!$gst->execute()) {
                //return response()->json(['error' => 'error_insert_organigrama'], 500);
                return $this->makeResponse(true, "ERROR_SER(MCOMOR003): No se pudo insertar el registro en la base", [], 500);
            }
        } catch (PDOException $th) {
            //return response()->json(['error' => 'organigrama_definido'], 400);
            return $this->makeResponse(true, "ERROR_USU(MCOMOR005): El Usuario ya cuenta con un organigrama definido", [], 400);
        }

        //return response()->json(['exito' => 'Organigrama insertado'], 200);
        return $this->makeResponse(false, "EXITO: Organigrama insertado");
    }

    public function update(Request $request){
        $validator = Validator::make($request->all(), [
            'id_usuario' => 'required|string|max:50',
            'id_jefe_directo' => 'required|string|max:50',
            'id_usuario_modifica' => 'required|string|max:50',
        ]);

        if ($validator->fails()) {
            return $this->makeResponse(
                true,
                "ERR_MCOMOR_USU_UPD000: Se encontraron uno o más errores.",
                $this->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $organigrama = $request->all();
        $pdo = DB::connection()->getPdo();

        $qryRFCE = "SELECT USUA_RFCE FROM S001V01TUSUA WHERE USUA_IDUS = :idus";
        $gstRFCE = $pdo->prepare($qryRFCE);
        $gstRFCE->bindParam(":idus", $organigrama['id_usuario_modifica']);

        $gstRFCE->execute();
        $rfce = $gstRFCE->fetchObject()->USUA_RFCE;

        //id_jefe_directo
        $qryUsr2 = 'SELECT * FROM S001V01TUSUA WHERE USUA_IDUS = :id AND USUA_RFCE = :rfce';
        $gstUsr2 = $pdo->prepare($qryUsr2);

        $gstUsr2->bindParam(":id", $organigrama['id_jefe_directo']);
        $gstUsr2->bindParam(":rfce", $rfce);

        if (!$gstUsr2->execute()) {
            return $this->makeResponse(true, "ERR_MCOMOR_SER_UPD001: No se pudo consultar el registro en la base.", [], 500);
        }

        $usr2 = $gstUsr2->fetchAll($pdo::FETCH_ASSOC);

        if (count($usr2) < 1) {
            return $this->makeResponse(true, "ERR_MCOMOR_USU_UPD002: El Jefe Directo no existe.", [], 404);
        }

        //id_jefe_sustituto
        if(array_key_exists('id_jefe_sustituto', $organigrama)){
            $qryUsr3 = 'SELECT * FROM S001V01TUSUA WHERE USUA_IDUS = :id AND USUA_RFCE = :rfce';
            $gstUsr3 = $pdo->prepare($qryUsr3);

            $gstUsr3->bindParam(":id", $organigrama['id_jefe_sustituto']);
            $gstUsr3->bindParam(":rfce", $rfce);
    
            if (!$gstUsr3->execute()) {
                return $this->makeResponse(true, "ERR_MCOMOR_SER_UPD003: No se pudo consultar el registro en la base.", [], 500);
            }
    
            $usr3 = $gstUsr3->fetchAll($pdo::FETCH_ASSOC);
    
            if (count($usr3) < 1) {
                return $this->makeResponse(true, "ERR_MCOMOR_USU_UPD004: El Jefe Sustituto no existe.", [], 404);
            }
        }

        //id_organigrama
        $qryOrg = 'SELECT * FROM S001V01TORGA WHERE ORGA_IDUS = :id AND ORGA_RFCE = :rfce';
        $gstOrg = $pdo->prepare($qryOrg);

        $gstOrg->bindParam(":id", $organigrama['id_usuario']);
        $gstOrg->bindParam(":rfce", $rfce);

        if (!$gstOrg->execute()) {
            return $this->makeResponse(true, "ERR_MCOMOR_SER_UPD005: No se pudo consultar el registro en la base.", [], 500);
        }

        $org = $gstOrg->fetchAll($pdo::FETCH_ASSOC);

        if (count($org) < 1) {
            return $this->makeResponse(true, "ERR_MCOMOR_USU_UPD006: El Organigrama no existe.", [], 404);
        }

        if(array_key_exists('id_jefe_sustituto', $organigrama)){
            if ($org[0]['ORGA_IDUS'] == $organigrama['id_jefe_directo'] || $org[0]['ORGA_IDUS'] == $organigrama['id_jefe_sustituto']) {
                return $this->makeResponse(true, "ERR_MCOMOR_USU_UPD007_A: El IDUSUARIO está duplicado en el formulario.", [], 400);
            }
        }else{
            if ($org[0]['ORGA_IDUS'] == $organigrama['id_jefe_directo']) {
                return $this->makeResponse(true, "ERR_MCOMOR_USU_UPD007_B: El IDUSUARIO está duplicado en el formulario.", [], 400);
            }
        }

        $qry = 'UPDATE S001V01TORGA SET ORGA_JEDI = :jedi, ORGA_JESU = :jesu, ORGA_USMO = :usmo, 
            ORGA_FEMO = :femo, ORGA_FEAR = CURRENT_TIMESTAMP WHERE ORGA_IDUS = :idus AND ORGA_RFCE = :rfce';
        $gst = $pdo->prepare($qry);
        
        $femo = Carbon::now()->timezone('America/Mexico_City')->toDateTimeString();

        $gst->bindParam(":jedi", $organigrama['id_jefe_directo']);
        $gst->bindParam(":jesu", $organigrama['id_jefe_sustituto']);
        $gst->bindParam(":usmo", $organigrama['id_usuario_modifica']);
        $gst->bindParam(":femo", $femo);
        $gst->bindParam(":idus", $organigrama['id_usuario']);
        $gst->bindParam(":rfce", $rfce);

        if (!$gst->execute()) {
            return $this->makeResponse(true, "ERR_MCOMOR_SER_UPD008: Hubo un error al actualizar el registro en la base.", [], 500);
        }

        return $this->makeResponse(false, "EXITO: Organigrama modificado");
    }

    public function delete(Request $request){
        $validator = Validator::make($request->all(), [
            'id' => 'required|integer',
        ]);

        if ($validator->fails()) {
            return $this->makeResponse(
                true,
                "Se encontraron uno o más errores",
                $this->makeErrors($validator->errors()->messages()),
                400
            );
        }

        $pdo = DB::connection()->getPdo();
        $qry = 'UPDATE mcomor SET estatus = "E" WHERE id = :id';
        $gst = $pdo->prepare($qry);

        $id = $request->all()['id'];
        $gst->bindParam(":id", $id);
        
        if (!$gst->execute()) {
            //return response()->json(['error' => 'error_delete_organigrama'], 500);
            return $this->makeResponse(true, "No se pudo eliminar el registro de la base", [], 500);
        }
        //return response()->json(['error' => 'Organigrama eliminado'], 200);
        return $this->makeResponse(false, "Organigrama eliminado");
    }

    public function getOrganigramas($rfce, $nonce){
        $pdo = DB::connection()->getPdo();
        $encController = new EncController();
        $rfce = $encController->desencriptar(base64_encode($rfce . "|" . $nonce));

        $qry = 'SELECT * FROM S001V01TORGA INNER JOIN S001V01TUSUA ON USUA_IDUS = ORGA_IDUS  WHERE ORGA_RFCE = :rfce AND USUA_ESTA != "Eliminado"';
        $gst = $pdo->prepare($qry);
        $gst->bindParam(":rfce", $rfce);
        
        if (!$gst->execute()) {
            return $this->makeResponse(true, "ERR_MCOMOR_SER_GOR000: No se pudieron recuperar los organigramas de la base.", [], 500);
        }

        $organigramas = $gst->fetchAll($pdo::FETCH_ASSOC);
        $organigramasF = array();

        foreach($organigramas as $organigrama){
            $organigramaF = array();
            foreach($organigrama as $key=>$val){
                if($key == "ORGA_IDUS" || $key == "ORGA_JEDI" || $key == "ORGA_JESU"){
                    $qryUsr = "SELECT USUA_NOMB, USUA_APPA, USUA_APMA, USUA_NUEM FROM S001V01TUSUA WHERE USUA_IDUS = :idus AND USUA_RFCE = :rfce ";
                    $gstUsr = $pdo->prepare($qryUsr);

                    $gstUsr->bindParam(":idus", $val);
                    $gstUsr->bindParam(":rfce", $rfce);

                    if(!$gstUsr->execute()){
                        return $this->makeResponse(true, "ERR_MCOMOR_SER_GOR001: No se pudo consultar la información del usuario $val", [], 500);
                    }

                    $usr = $gstUsr->fetchObject();

                    if(!$usr){
                        continue ;
                    }

                    $nombre = $usr->USUA_NOMB . " " . $usr->USUA_APPA . " " . $usr->USUA_APMA;
                   /*  $nombre = str_replace("-", "", $nombre);
                    $nombre = trim($nombre);

                    $nombre .= " - " . $usr->USUA_NUEM;*/

                    $nombre = $nombre . " (" . $val . ")";
                    $organigramaF[$key] = $nombre; 
                }
            }
            $organigramaF = (object) $organigramaF;
            $organigramasF[] = $organigramaF;
        }

        return $this->makeResponse(false, "EXITO", $organigramasF);
    }

    public function getOrganigrama($id){
        $pdo = DB::connection()->getPdo();
        $qry = 'SELECT * FROM mcomor WHERE id = :id';
        $gst = $pdo->prepare($qry);

        $gst->bindParam(":id", $id);

        if (!$gst->execute()) {
            //return response()->json(['error' => 'error_get_organigrama'], 500);
            return $this->makeResponse(true, "No se pudo recuperar la información de la base", [], 500);
        }

        $organigrama = $gst->fetchObject();

        if(!$organigrama){
            return $this->makeResponse(true, "El organigrama solicitado no existe", [], 404);
        }

        if ($organigrama->estatus == 'E') {
            //return response()->json(['error' => 'Organigrama eliminado'], 404);
            return $this->makeResponse(true, "Organigrama eliminado", [], 404);
        }

        //return response()->json(['organigrama' => $organigrama], 200);
        return $this->makeResponse(false, "Consulta exitosa", $organigrama);
    }

    private function makeResponse($error, $msg, $response = [], $code = 200){
        $respuesta = json_encode([
            "error" => $error,
            "msg" => $msg,
            "response" => $response
        ]);

        return response($respuesta, $code)->header('Content-Type', 'application/json');
    }

    private function makeErrors($erroresObj){
        $erroresArr = array();

        foreach ($erroresObj as $key => $value) {
            foreach ($value as $key0 => $value0) {
                if(array_key_exists($key, $erroresArr)){
                    $val = $erroresArr[$key] . "|" . $value0;
                    $erroresArr[$key] = $val;
                }else{
                    $erroresArr[$key] = $value0;
                }
            }
        }

        return $erroresArr;
    }
}
