<?php

namespace App\Domain\Fields\Repository;

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

/**
 * Repository.
 */
class FieldsRepository
{
    /**
     * @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_categoria' => $data['foKeyCategory']
            ];

            try { 

                $lastInsertId =  $this->connection->table('campos')->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;
    }

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


    public function SearchWithStatusByFoKey(int $categoryId): string {

        $payload = $this->connection->table('campos as c')
        ->Select('c.id_campo as id', 'c.nombre as name', 'c.fk_id_categoria as foKeyCategory', $this->connection->raw('CASE WHEN ca.id_camp_act IS NOT NULL THEN 1 ELSE 0 END as used'))
        ->LeftJoin('campo_activo as ca', 'ca.fk_id_campo', '=', 'c.id_campo')
        ->groupBy('c.nombre')
        ->orderby('c.nombre')
        ->where('c.fk_id_categoria', '=', $categoryId)
        ->get();

        return $payload;
    }

    public function FindCatId(int $categoryId):string {
        $payload=$this->connection->table('campos')
        ->Select('id_campo as id', 'nombre as name', 'fk_id_categoria as foKeyCategory')
        ->where('fk_id_categoria','=', $categoryId)->get();
        return $payload;  
    }

    public function DeleteRecord(int $fieldId):array {

          try { 

              $this->connection->table('campos')
              ->where(['id_campo' => $fieldId])
              ->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('campos')
                ->where(['id_campo' => $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]),
                ];
                // Note any method of class PDOException can be called on $ex.
              }

            
       return $result;
    }

}