<?php

namespace App\Domain\Dependency\Repository;

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

/**
 * Repository.
 */
class DependencyRepository
{
    /**
     * @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_city' => $data['foKeyCity']
            ];

            try { 

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

    public function SearchRecord(string $id):string {

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

        $payload=$this->connection->table('dependencia')
        ->select('id_dependencia as id', 'nombre as name', 'fk_id_city as foKeyCity')
        ->whereIn('fk_id_city', $ids)->get();
        return $payload;  
    }

    public function SearchWithParentStatus(string $citiesId): string {

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

        $payload = $this->connection->table('dependencia as d')
        ->select('d.id_dependencia as id', 'd.nombre as name', 'fk_id_city as foKeyCity', 
                $this->connection->raw('CASE WHEN c.id_compra IS NOT NULL THEN 1 ELSE 0 END as used'))
        ->LeftJoin('compra as c', 'c.fk_dependencia', '=', 'd.id_dependencia')
        ->GroupBy('d.nombre')
        ->WhereIn('fk_id_city', $ids)->get();

        return $payload;
    }

    public function SearchWithStatusCity(string $citiesId): string {

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

        $payload = $this->connection->table('dependencia as d')
        ->select('d.id_dependencia as id', 'd.nombre as name', 'fk_id_city as foKeyCity', 
                $this->connection->raw('CASE WHEN c.id_compra IS NOT NULL THEN 1 ELSE 0 END as used'), 'city.city_name as city')
        ->Join('city', 'city.id', '=', 'd.fk_id_city')
        ->LeftJoin('compra as c', 'c.fk_dependencia', '=', 'd.id_dependencia')
        ->GroupBy('d.nombre')
        ->WhereIn('fk_id_city', $ids)->get();

        return $payload;
    }

    public function DeleteRecord(int $id):array {

        try { 

            $this->connection->table('dependencia')
            ->where(['id_dependencia' => $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])
                ];

      }

        return $result;
    }

    public function UpdateRecord(array $data):array {

            $row = [
                'nombre' => $data['name'],
                'fk_id_city' => $data['foKeyCity']
            ];

             try { 

                $this->connection->table('dependencia')
                ->where(['id_dependencia' => $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){ 
                //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])
                        ];

              }

       return $result;
    }
    
    public function UpdateFoKeyCity(array $data):array {

            $row = [
                'fk_id_city' => $data['foKeyCity']
            ];

            try { 

                $this->connection->table('dependencia')
                ->where(['id_dependencia' => $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){ 
                //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])
                        ];

            }

        return $result;
    }
}