<?php

namespace App\Domain\Activos\Repository;

use PDO;
use Illuminate\Database\Connection;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Exception;

/**
 * Repository.
 */
class ActivosRepository
{
    /**
     * @var PDO The database connection
     */
    private $connection;
    /**
     * Constructor.
     *
     * @param PDO $connection The database connection
     */
    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    /**
     * Insert user row.
     *
     * @param array $datos de los usuarios
     *
     * @return int The new ID
     */
    
    public function InsertRecord(array $data):array{

            $row = [
                'id_activo' => $data['id'],
                'fk_id_categoria' => $data['foKeyCategory'],
                'descripcion' => $data['description'],
                'estado' => $data['state'],
                'fecha_compra' => $data['date'],
                'valor' => $data['value'],
                'fk_id_func_respon' => $data['foKeyResponsible'],
                'fk_id_ubicacion' => $data['foKeyLocation'],
                'fk_id_compra' => $data['foKeyPurchase'],
                'registration_date' => date('Y-m-d H:i:s'),
                'act_assigned' => $data['actAssigned']
            ];

            try { 

                $this->connection->table('activos')->insert($row);
                $vErr=0;
                $result = [
                    
                        'SqlState' => 'Transaccion ejecutada correctamente',
                        'ErrNumber' => $vErr,
                        'UniqueViolation' => 'Ok',
                        'Description' => 'El registro se creo correctamente',
                        'FullInfo' => $data['id']
                    
                    ];

              } catch(\Illuminate\Database\QueryException $ex){ 

                $error=explode(":",$ex->getMessage());
                  //$descripcion=
                $err=substr($error[0],9,-1);
                  $result = [
                        'SqlState' => $error[0],
                        'ErrNumber' => $err,
                        'UniqueViolation' => $error[1],
                        'Description' => 'Ocurrio un error al momento de crear el registro',
                        'FullInfo' => trim($error[4]),
                ];
                // Note any method of class PDOException can be called on $ex.
                
              }
            
       return $result;
    }

    public function SearchRecords():string {
        $payload = $this->connection->table('activos')->sharedLock()->get();
        return $payload;  
    }

    public function SearchIdOnly():string {
        $payload = $this->connection->table('activos')
        ->Select('id_activo as id')
        ->sharedLock()->get();
        return $payload;  
    } 

    public function SearchByIdRes(int $responsibleId):string {
        $payload = $this->connection->table('activos as a')
        ->Select('a.id_activo as id', 'a.descripcion as description', 'categorias.nombre as state')
        ->Join('categorias', 'a.fk_id_categoria', '=', 'categorias.id_categoria')
        ->Where(['fk_id_func_respon' => $responsibleId])
        ->where('act_assigned', '!=', 0)
        ->sharedLock()->get();
        return $payload;  
    }

    public function SearchRecord(string $cities, int $offset, int $limit):string {

        $query = $this->QueryAssets($cities)->offset($offset)->limit($limit);
        $payload = $query->get();

        return $payload;
    }

    public function SearchById(string $assetId, string $cities): string {

        $query = $this->QueryAssets($cities)->where('id', '=', $assetId);
        $payload = $query->get();

        return $payload;
    }

    public function SearchByResponsible(string $cities, int $responsibleId): string {

        $query = $this->QueryAssets($cities)->where('responsibleId', '=', $responsibleId);
        $payload = $query->get();

        return $payload;
    }

    public function SearchByDescription(string $cities, string $data): string {

        $query = $this->QueryAssets($cities)->where('description', 'LIKE', '%' . $data . '%');
        $payload = $query->get();

        return $payload;
    }

    public function SearchByAssetId(string $cities, string $id): string {

        $query = $this->QueryAsset($cities)->where('a.id_activo', '=', $id);
        $payload = $query->get();

        return $payload;
    }

    public function ReportFormat(string $cities, int $locationId, int $responsibleId) {

        $query = $this->QueryAssets($cities);
        
        if($locationId != -1 && $responsibleId != -1) {
          $query->where(['locationId' => $locationId, 'responsibleId' => $responsibleId]);
        } else {

            if($locationId != -1) {
              $query->where(['locationId' => $locationId]);
            }

            if($responsibleId != -1) {
              $query->where(['responsibleId' => $responsibleId]);
            }

        }

        $payload = $query->sharedLock()->get();
        return $payload;
    }
    
    public function SearchPreviewRecord(string $id):array{

      $payload=$this->connection->table('activos as a')
      ->select('a.id_activo AS ID','categorias.nombre AS CATEGORY','a.descripcion AS DESCRIPTION','funci_responsable.nom_func_respon AS RESPONSIBLE','ubicacion.nombre AS LOCATION')
      ->Join('categorias','categorias.id_categoria','=','a.fk_id_categoria')
      ->Join('funci_responsable','funci_responsable.id_func_respon','=','a.fk_id_func_respon')
      ->Join('ubicacion','ubicacion.id_ubicacion','=','a.fk_id_ubicacion')
      ->where('id_activo','=',$id)
      ->get();

      if(sizeof($payload) != 0){

        $json = json_decode($payload, true);
          
        $ID = $json[0]['ID'];
        $CATEGORY = $json[0]['CATEGORY'];
        $DESCRIPTION = $json[0]['DESCRIPTION'];
        $RESPONSIBLE = $json[0]['RESPONSIBLE'];
        $LOCATION = $json[0]['LOCATION'];
 
        $result = [

          [

            'SqlState' => 'Transaccion ejecutada correctamente',
            'ErrNumber' => '0',
            'UniqueViolation' => 'Ok',
            'Description' => 'El id ingresado es correcto',
            'Id' => $ID,
            'Category' => $CATEGORY,
            'DesData' => $DESCRIPTION,
            'Responsible' => $RESPONSIBLE,
            'Location' => $LOCATION,
          ]

        ];

      }else{

        $result = [

          [
            'SqlState' => 'Ocurrio un error',
            'ErrNumber' => '01',
            'UniqueViolation' => 'Failed',
            'Description' => 'El id ingresado no existe',

          ]

        ];

      }

      return $result;  
    }

    public function DeleteRecord(string $assetId):array {

            try { 

                $this->connection->table('activos')
                ->where(['id_activo' => $assetId])
                ->delete();

                    $result = [
                                  
                      'SqlState' => 'Transaccion ejecutada correctamente',
                      'ErrNumber' => '0',
                      'UniqueViolation' => 'Ok',
                      'Description' => 'El registro se elimino correctamente',
                  
                    ];

            }catch(\Illuminate\Database\QueryException $ex){ 

              $error=explode(":",$ex->getMessage());
              $err=substr($error[0],9,-1);
              
                  $result = [
                        'SqlState' => $error[0],
                        'ErrNumber' => $err,
                        'UniqueViolation' => $error[1],
                        'Description' => 'Ocurrio un error al momento de eliminar el registro',
                        'FullInfo' => trim($error[4]),
                  ];
            }

          return $result;
    }

    public function UpdateGlobalOfficial(string $idOld, string $idNew):array{

        $row = [
            'fk_id_ubicacion' => $idNew,
        ];

        try { 

          $this->connection->table('activos')
            ->where(['fk_id_ubicacion' => $idOld])
            ->update($row);

          $result = [

              [
            
                  'SqlState' => 'Transaccion ejecutada correctamente',
                  'ErrNumber' => '0',
                  'UniqueViolation' => 'Ok',
                  'Description' => 'El registro se actualizo correctamente',
                  'FullInfo' => 'Todo en orden brou',
          
              ]

           ]; 

        } catch(\Illuminate\Database\QueryException $ex){ 

            //var_dump($ex->getMessage());  
            $error=explode(":",$ex->getMessage());
            //$descripcion=
            $err=substr($error[0],9,-1);

            $result = [

                [

                   'SqlState' => $error[0],
                   'ErrNumber' => $err,
                   'UniqueViolation' => $error[1],
                   'Description' => 'Ocurrio un error al momento de actualizar el registro',
                   'FullInfo' => trim($error[4]),

                ]
                
            ];
            
        // Note any method of class PDOException can be called on $ex.
      }

      return $result;
    }

    public function UpdateRecord(array $data):array {

        $row = [
            
            'descripcion' => $data['description'],
            'valor' => $data['value'],
            'fecha_compra' => $data['date'],
            'estado' => $data['state'],
            'fk_id_compra' => $data['foKeyPurchase']
        ];

        if($data['foKeyCategory'] != 0) {

            $row = [

                'fk_id_categoria' => $data['foKeyCategory']
            ];
        }

            try { 

                $this->connection->table('activos')
                ->where(['id_activo' => $data['id']])
                ->update($row);

                $result = [
                    
                        'SqlState' => 'Transaccion ejecutada correctamente',
                        'ErrNumber' => '0',
                        'UniqueViolation' => 'Ok',
                        'Description' => 'El registro se actualizo correctamente',
                        'FullInfo' => 'Ok'
                    
                    ];

              } catch(\Illuminate\Database\QueryException $ex){ 

                $error=explode(":",$ex->getMessage());
                $err=substr($error[0],9,-1);

                  $result = [

                        'SqlState' => $error[0],
                        'ErrNumber' => $err,
                        'UniqueViolation' => $error[1],
                        'Description' => 'Ocurrio un error al momento de actualizar el registro',
                        'FullInfo' => trim($error[4])
                ];
                
              }
            
       return $result;
    }

    public function UpdateAssetLocation(array $data):array {

        $row = [
            'fk_id_func_respon' => $data['foKeyResponsible'],
            'fk_id_ubicacion' => $data['foKeyLocation']
        ];

            try { 

                $this->connection->table('activos')
                ->where(['id_activo' => $data['id']])
                ->update($row);

                $result = [
                    
                        'SqlState' => 'El traslado fue exitoso',
                        'ErrNumber' => '0',
                        'UniqueViolation' => 'Ok',
                        'Description' => 'El registro se actualizo correctamente',
                        'FullInfo' => $data['id']
                    
                    ];

              } catch(\Illuminate\Database\QueryException $ex){ 

                $error=explode(":",$ex->getMessage());
                $err=substr($error[0],9,-1);

                  $result = [
                        'SqlState' => $error[0],
                        'ErrNumber' => $err,
                        'UniqueViolation' => $error[1],
                        'Description' => 'Ocurrio un error al traslador el activo',
                        'FullInfo' => trim($error[4])
                ];
                
              }
            
       return $result;
    }

    public function CountAssets(String $cities): string {

      $payload = $this->connection->table('Activos as a')
      ->select($this->connection->raw('count(a.id_activo) as count'))
      ->whereRaw("FIND_IN_SET(a.fk_id_ubicacion, get_ubications_cities(\"$cities\"))")
      ->where('act_assigned', '!=', 0)
      ->sharedLock()->get();

      return $payload;
    }

    public function TransferResponsible(int $responsibleId, int $newResponsibleId): array {

      $row = [
        'fk_id_func_respon' => $newResponsibleId
      ];

      try {

          $result = $this->connection->table('Activos')
          ->Where('fk_id_func_respon', '=', $responsibleId)
          ->Update($row);

                  $result = [
                            
                    'SqlState' => 'El traslado fue exitoso',
                    'ErrNumber' => '0',
                    'UniqueViolation' => 'Ok',
                    'Description' => 'El registro se actualizo correctamente',
                    'FullInfo' => 'ok'
          
                ];

      } catch(\Illuminate\Database\QueryException $ex) {

        $error=explode(":",$ex->getMessage());
                $err=substr($error[0],9,-1);

                  $result = [
                        'SqlState' => $error[0],
                        'ErrNumber' => $err,
                        'UniqueViolation' => $error[1],
                        'Description' => 'Ocurrio un error al traslador el activo',
                        'FullInfo' => trim($error[4])
                ];
      }

      return $result;
    }


    public function UpdateFoKeyResponsible(string $assetId, int $responsibleId): array {

        $row = [
          'fk_id_func_respon' => $responsibleId
        ];

        try {

          $result = $this->connection->table('activos')
          ->where('id_activo', '=', $assetId)
          ->update($row);

           $result = [
                              
                      'SqlState' => 'El traslado fue exitoso',
                      'ErrNumber' => '0',
                      'UniqueViolation' => 'Ok',
                      'Description' => 'El registro se actualizo correctamente',
                      'FullInfo' => 'ok'
            
                  ];

        } catch(\Illuminate\Database\QueryException $ex) {

          $error=explode(":",$ex->getMessage());
                  $err=substr($error[0],9,-1);

                    $result = [
                          'SqlState' => $error[0],
                          'ErrNumber' => $err,
                          'UniqueViolation' => $error[1],
                          'Description' => 'Ocurrio un error al traslador el activo',
                          'FullInfo' => trim($error[4])
                  ];
        }

        return $result;
    }

    public function TransferAsset(string $assetId, int $responsibleId, int $locationId): array {

        $row = [

            'fk_id_func_respon' => $responsibleId,
            'fk_id_ubicacion' => $locationId
        ];

        try {

            $result = $this->connection->table('activos')
            ->where('id_activo', '=', $assetId)
            ->update($row);

            $result = [
                              
                      'SqlState' => 'El traslado fue exitoso',
                      'ErrNumber' => '0',
                      'UniqueViolation' => 'Ok',
                      'Description' => 'Se realizo el traslado con exito',
                      'FullInfo' => 'ok'
            
                  ];

        } catch(\Illuminate\Database\QueryException $ex) {

            $error=explode(":",$ex->getMessage());
                $err=substr($error[0],9,-1);

                  $result = [
                        'SqlState' => $error[0],
                        'ErrNumber' => $err,
                        'UniqueViolation' => $error[1],
                        'Description' => 'Ocurrio un error al traslador el activo',
                        'FullInfo' => trim($error[4])
                ];
        }

        return $result;
    }

    public function assetCategoryWithFields(string $id): string {

        $payload = $this->connection->table('Activos as a')
        ->select('a.id_activo as id', 'ca.nombre as category', 
                'c.nombre as field', 'cam.descripcion as description', 'ca.id_categoria as categoryId',
                'c.id_campo as fieldId', 'cam.id_camp_act as descriptionId')
        ->join('categorias as ca', 'ca.id_categoria', '=', 'a.fk_id_categoria')
        ->join('campos as c', 'c.fk_id_categoria', '=', 'ca.id_categoria')
        ->join('campo_activo as cam', 'cam.fk_id_activo', '=', 'a.id_activo')
        ->whereRaw("a.id_activo = \"$id\" AND cam.fk_id_campo = c.id_campo")->get();

        return $payload;
    }

    public function ChangeAssetStatus(string $assetId, int $status): array {


        $row = [
            'act_assigned' => $status
        ];


        try {

            $result = $this->connection->table('Activos as a')
            ->where('a.id_activo', '=', $assetId)
            ->update($row);

            $result = [
                              
                      'SqlState' => 'El cambio de estado fue exitoso',
                      'ErrNumber' => '0',
                      'UniqueViolation' => 'Ok',
                      'Description' => 'El cambio de estado se hizo con exito',
                      'FullInfo' => 'ok'
            
                  ];


        } catch(\Illuminate\Database\QueryException $ex) {


            $error=explode(":",$ex->getMessage());
                $err=substr($error[0],9,-1);

                  $result = [

                        'SqlState' => $error[0],
                        'ErrNumber' => $err,
                        'UniqueViolation' => $error[1],
                        'Description' => 'Ocurrio un error al cambiar el estado del activo',
                        'FullInfo' => trim($error[4])
                ];
        }

        return $result;
    }

    private function QueryAssets(string $cities): QueryBuilder {

        $queryAssets = $this->connection->table('ViewAssets as a')
        ->whereRaw($this->connection->raw("FIND_IN_SET(locationId, get_ubications_cities(\"$cities\"))"));

        return $queryAssets;
    }

    private function QueryAsset(string $cities): QueryBuilder {

        $query = $this->connection->table('vwAsset as a')
        ->whereRaw($this->connection->raw("FIND_IN_SET(locationId, get_ubications_cities(\"$cities\"))"));

        return $query;
    }
}