<?php

namespace App\Domain\Locations\Repository;

use PDO;
use Illuminate\Database\Connection;
use Exception;

/**
 * Repository.
 */
class LocationsRepository
{
    /**
     * @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 = [
                'nombre' => $data['name'],
                'fk_id_sede' => $data['foKeyCampus']
            ];

            try { 

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

              } 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;
       // return (int)$this->connection->lastInsertId();
    }

    public function SearchWithStatusByFoKey(int $foKeyHeadquarters):string {
        $payload = $this->connection->table('ubicacion as u')
        ->select('u.id_ubicacion as id','u.nombre as name','u.fk_id_sede as foKeyCampus', 's.nombre as nameCampus', $this->connection->raw('CASE WHEN a.id_activo IS NOT NULL THEN 1 ELSE 0 END as used'))
        ->leftJoin('activos as a', 'a.fk_id_ubicacion', '=', 'u.id_ubicacion')
        ->join('sede as s', 's.id_sede', '=', 'u.fk_id_sede')
        ->where('u.fk_id_sede', '=', $foKeyHeadquarters)
        ->groupBy('u.nombre')
        ->orderby('u.nombre')
        ->sharedLock()->get();
        return $payload;  
    }
    
    public function GetItemsForLocat(string $fk_id):string {

      $payload=$this->connection->table('ubicacion')
      ->select('ubicacion.id_ubicacion as ID','ubicacion.nombre as location','ubicacion.fk_id_sede as fk_id',$this->connection->raw('count(activos.id_activo) as items'))
      ->leftJoin('activos', 'activos.fk_id_ubicacion', '=' ,'ubicacion.id_ubicacion')
      ->where('ubicacion.fk_id_sede','=',$fk_id)
      ->groupBy('ubicacion.nombre')
      ->orderby('ubicacion.nombre')
      ->sharedLock()
      ->get();

      return $payload;  
    }

    public function SearchRecord(int $id):string{
        $payload=$this->connection->table('ubicacion')
        ->where('id_ubicacion','=', $id)->get();
        return $payload;  
    }

    public function RecordsInCity(String $idCities) {
         $ids = explode(",", $idCities);
        return $ids; 
    }

    public function DeleteRecord(int $locationId):array {

          try { 

            $this->connection->table('ubicacion')
            ->where(['id_ubicacion' => $locationId])
            ->delete();

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

          }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 eliminar 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 = [
            'nombre' => $data['name']
        ];

            try { 
                $this->connection->table('ubicacion')
                ->where(['id_ubicacion' => $data['id']])
                ->update($row);
                $result = [
                    
                        'SqlState' => 'Transaccion ejecutada correctamente',
                        'ErrNumber' => '0',
                        'UniqueViolation' => 'Ok',
                        'Description' => 'El registro se actualizo correctamente',
                    
                    ];

              } 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;
    }

}