<?php

namespace App\Domain\Shopping\Repository;

use PDO;
use Illuminate\Database\Connection;
use Slim\Psr7\UploadedFile;
use Exception;

/**
 * Repository.
 */

class ShoppingRepository {

    /**
     * @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 sede
     *
     * @return int The new ID
     */
    
    public function InsertRecord(array $data):array{

            $row = [
                'nombre' => $data['name'],
                'descripcion' => $data['description'],
                'valor_total' => $data['totalValue'],
                'fecha' => $data['date'],
                'fk_dependencia' => $data['foKeyDependency'],
                'fk_id_tip_compra' => $data['foKeyShopping'],
                'nom_pdf' => $data['pdfName'],
                'pdf' => $data['pdf']
            ];

            try { 

                $lastInsertId = $this->connection->table('compra')->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('compra')
        ->select('nombre')
        ->sharedLock()->get();
        return $payload;  
    }

    public function RecordsWithoutPDF(string $citiesId): string {

        // ids cities for search
        $ids = explode(",", $citiesId);

        // sql laravel
        $payload = $this->connection->table('compra')
            ->Select('id_compra as id', 'compra.nombre as name', 'descripcion as description', 'valor_total as totalValue', 'fecha as date', 
                'fk_dependencia as foKeyDependency', 'fk_id_tip_compra as foKeyShopping', 'nom_pdf as pdfName')
            ->Join('dependencia', 'dependencia.id_dependencia', '=', 'compra.fk_dependencia')
            ->WhereIn('dependencia.fk_id_city', $ids)
            ->sharedLock()
            ->get();
        return $payload;
    }

    public function SearchRecord(string $idCities):string{

        $ids = explode(",", $idCities);

        $payload=$this->connection->table('compra')
        ->select('id_sede as id', 'sede.nombre as name', $this->connection->raw('COUNT(ubicacion.id_ubicacion) as countLocations'), 'fk_id_city as foKeyCity')
        ->whereIn('fk_id_city', $ids)
        ->leftJoin('ubicacion','sede.id_sede','=','ubicacion.fk_id_sede')
        ->groupBy('sede.nombre')->get();
        return $payload;  
    }

    public function DeleteRecord(string $id):array {

      try { 

        $this->connection->table('compra')
        ->where(['id_compra' => $id])
        ->delete();

            $result = [

                'SqlState' => 'Transaccion ejecutada correctamente',
                'ErrNumber' => '0',
                'UniqueViolation' => 'Ok',
                'Description' => 'El registro se elimino 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 eliminar el registro',
                        'FullInfo' => trim($error[4])
                
                    ];
            // Note any method of class PDOException can be called on $ex.
        }

        return $result;
    }

    public function DownloadPDF(int $id): ?string {
        $payload=$this->connection->table('compra')
        ->Select('pdf')
        ->Where(['id_compra' => $id])
        ->first();
        return $payload->pdf;
    }

    public function UpdateRecord(array $data):array{

        $id = $data['id'];

        $row = [
                'nombre' => $data['name'],
                'descripcion' => $data['description'],
                'valor_total' => $data['totalValue'],
                'fecha' => $data['date'],
                'fk_dependencia' => $data['foKeyDependency'],
                'fk_id_tip_cate' => $data['foKeyShopping'],
                'nom_pdf' => $data['pdfName'],
                'pdf' => $data['pdf']
        ];

            try { 
                $this->connection->table('compra')
                ->where(['id_compra' => $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){ 
                //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 UploadFile(UploadedFile $file, int $id): array {    

        $fileContent = file_get_contents($file->getStream()->getMetadata('uri'));
        $row = [
            'pdf' => $fileContent
        ];

        
            try { 

                $this->connection->table('compra')
                ->where(['id_compra' => $id])->update($row);
                $vErr = 0;

                $result = [

                            'SqlState' => 'Transaccion ejecutada correctamente',
                            'ErrNumber' => $vErr,
                            'UniqueViolation' => 'Ok',
                            'Description' => 'El archivo se subio correctamente',
                            'FullInfo' => 'Ok'
                    
                        ];

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

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

                    $result = [
                                'SqlState' => $ex->getCode(),
                                'ErrNumber' => $ex->errorInfo[1],
                                'UniqueViolation' => $ex->errorInfo[2],
                                'Description' => 'Error al subir el archivo: ' . $ex->getMessage(),
                                'FullInfo' => $ex->getMessage()
                    ];
                // Note any method of class PDOException can be called on $ex.
              }
            
       return $result;  
    }

}