<?php

/**
 *
 * @About:      Gestión de EDAS
 * @File:       DbEdas.php
 * @Date:       $Date:$ ADec. 2022
 * @Version:    $Rev:$ 1.0
 * @Developer:  fernando humanes
 **/

    use Psr\Http\Message\ResponseInterface as Response;
    use Psr\Http\Message\ServerRequestInterface as Request;
    use Selective\BasePath\BasePathMiddleware;
    use Slim\Factory\AppFactory;

class DbEdas
{

    private $connDB;

    function __construct()
    {
        global $conn; // Connection data base of PHPRunner
        $this->connDB = $conn;
    }

    public function createSession($param, Request $request, Response $response)
    {   
        global $conn; // Connection data base of PHPRunner
        $login = $param['login'];
        $password = $param['password'];
        // $password = MD5($password );
        // Delete session expired
        $now = now();
        DB::Delete("edas_session_api", "dateOfExpiry < '$now'" );
        // Check User and password
        $sql = "SELECT * FROM edas_user WHERE login = '$login' AND password= '$password' AND active = 1";
        $rs = DB::Query($sql);
        $data = $rs->fetchAssoc();
        if (!is_array($data)) {
            return false;
        }
        // Delete field "password"
        unset($data['password']);
        // Create enviroment of Session
        // session_regenerate_id(true);  // Init SESSION.
        // $session_id = session_id();
        $USER_PRIV = array();
        $USER_PRIV['dataUser']=$data; // Save info of User identified
        $uuid = $this->guidv4();
        // Save session in Data Base
        $data2 = array();
        $data2["uuid"] = $uuid;
        $data2["session"]  = session_id();
        $data2["creationDate"]  = now();
        $data2["dateOfExpiry"]  = date('Y-m-d H:i:s', strtotime('3 hour'));
        $data2['sessionVariables'] = json_encode($USER_PRIV,true);
        DB::Insert("edas_session_api", $data2 );
        
        return array("uuid"=>$uuid);
    }

    /**
     * Analiza el path de Directorios solicitado y crea estructura en variables de entorno
     */
    public function pathDirectory( array $param, Request $request, Response $response)
    {
        global $errorMessages;

        $document = $param['path'];  // New pathdirectory
        $arr_document = explode('/',$document);

        $arr_new_path = array();
        // Initial array
        $arr_new_path[0]['code'] =  '.' ;
        $arr_new_path[0]['url'] =  "edas_nav_directory_list.php?path=0";
        $arr_new_path[0]['id'] =  NULL ;

        $USER_PRIV = $this->restoreUser($request, $response);

        if ($arr_document[1] <> '' ) { // para contemplar el directorio raíz

            $parent = NULL; 
            for ($i=1 ;$i< count($arr_document) ;$i++){ // First element ignore
                $code = $arr_document[$i];
                if ($parent == NULL) {
                    $sql = "SELECT id_edas_directory, edas_parent_directory_id, code, owners, reading_groups, writing_groups FROM edas_directory where code = '$code' and edas_parent_directory_id is null";
                } else {
                    $sql = "SELECT id_edas_directory, edas_parent_directory_id, code, owners, reading_groups, writing_groups FROM edas_directory where code = '$code' and edas_parent_directory_id = $parent";
                }
                $rs = DB::Query($sql);
                if ($rs->value("id_edas_directory") == NULL ) {
                    // No existe el Directorio
                    $responseBody["error"] = true;
                    $responseBody["message_num"] = '008';
                    $responseBody["message"] =str_replace("{1}",$code,$errorMessages['008']);
                    // Error 401
                    return $responseBody;
                }
                
                $parent = $rs->value('id_edas_directory'); 

                // Function "IsInSet()" this in APPSETTING
                // Access control
                $isOwner = IsInSet($rs->value('owners'), $USER_PRIV['dataUser']['id_edas_user']); // Owner?
                $isRead =  IsInSet($rs->value('reading_groups') , $USER_PRIV['dataUser']['belongingGroups']); // Read?
                $isWrite =  IsInSet($rs->value('writing_groups'), $USER_PRIV['dataUser']['belongingGroups']); // write?
                if ( !$isOwner && !$isRead  ) {  
                    //  No access the Directory
                    $responseBody["error"] = true;
                    $responseBody["message_num"] = '009';
                    $responseBody["message"] =str_replace("{1}",$code,$errorMessages['009']);
                    // Error 401
                    return $responseBody;
                }
                $x = $i;
                $arr_new_path[$x]['code'] = $rs->value("code");
                $arr_new_path[$x]['url'] = "edas_nav_directory_list.php?path=".$x;
                $arr_new_path[$x]['id'] = $rs->value("id_edas_directory");
            }
        }

        $responseBody["error"] = false;
        $responseBody["message_num"] = '000';
        $responseBody["message"] = '';
        $responseBody['pathDirectory'] = $arr_new_path;
        $responseBody['selectedDirectory'] = $responseBody['pathDirectory'][count($arr_new_path)-1]['id']; // Last Directory
        // $responseBody['searchDocument'] = $arr_document[count($arr_document)-1]; // Document
        return  $responseBody;

    }
    /*
    ** Listado de los directorios y ficheros del direcotrio posicionado (sólo los que el usuario tiene acceso)
    */
    public function listDirectory($verify,Request $request, Response $response)
    {   
        global $conn; // Connection data base of PHPRunner

        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $verify['selectedDirectory'];
        if ($selectDirectory <> NULL) {
            $sql = <<<EOT
            select * from (
            SELECT 'dir' type, `id_edas_directory` id, `edas_parent_directory_id` edas_parent, `code`, `name`, `locked`, `owners`, `reading_groups`, `writing_groups` FROM edas_directory
                where edas_parent_directory_id = $selectDirectory
            union
            SELECT 'file' type, `id_edas_file`, `edas_directory_id`, `code`, `name`, `locked`, `owners`, `reading_groups`, `writing_groups` FROM edas_file
                where edas_directory_id = $selectDirectory)
            T1  order by 1,4
EOT;
        } else {
            $sql = <<<EOT
            select * from (
            SELECT 'dir' type, `id_edas_directory` id, `edas_parent_directory_id` edas_parent, `code`, `name`, `locked`, `owners`, `reading_groups`, `writing_groups` FROM edas_directory
                    where edas_parent_directory_id is NULL
            union
            SELECT 'file' type, `id_edas_file`, `edas_directory_id`, `code`, `name`, `locked`, `owners`, `reading_groups`, `writing_groups` FROM edas_file
                    where edas_directory_id is NULL)
            T1  order by 1,4
EOT;       
        }
        $arr_row = array();
        $rs = DB::Query($sql);
        $USER_PRIV = $this->restoreUser($request, $response);
        $USER_PRIV['pathDirectory'] = $verify['pathDirectory'];
        $USER_PRIV['selectedDirectory'] = $verify['selectedDirectory'];
        $status = $this->saveUser($USER_PRIV,$request, $response); // Save SESSION User, Path directory and ID of last directory

        while( $data = $rs->fetchAssoc() )
        {
            // Function "IsInSet()" this in APPSETTING
            // Access control
            $isOwner = IsInSet($data['owners'], $USER_PRIV['dataUser']['id_edas_user']); // Owner?
            $isRead =  IsInSet($data['reading_groups'] ,$USER_PRIV['dataUser']['belongingGroups']); // Read?
            $isWrite =  IsInSet($data['writing_groups'],$USER_PRIV['dataUser']['belongingGroups']); // write?
            if ( $isOwner || $isRead  ) {  
                //  Ok access the Directory or file
                $arr_row[] = $data;
            }
        }
        return  $arr_row;
    }
    /*
    ** Verificación de si está posicionado en el directorio adecuado y dispone de acceso al documento solicitado
    */
    public function accessDocument($param ,Request $request, Response $response)
    {
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory
                            
        $id_document = $param['id_document'];     // CODE of File

        if(  $USER_PRIV ['selectedDirectory'] == NULL ) { // No se ha fijado el directorio por defecto
            return '012';
        }
        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];
        if ($selectDirectory <> NULL) {
            $sql = "SELECT * FROM edas_file WHERE  edas_directory_id  = $selectDirectory AND id_edas_file = $id_document";
        } else {
            $sql = "SELECT * FROM edas_file WHERE  edas_directory_id  is NULL AND id_edas_file = $id_document";
        }

        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data == NULL) { // No recuperado datos, Documento no existe
            return '010';
        }
    
        // Function "IsInSet()" this in APPSETTING
        // Access control
        $isOwner = IsInSet($data['owners'], $USER_PRIV['dataUser']['id_edas_user']); // Owner?
        $isRead =  IsInSet($data['reading_groups'] ,$USER_PRIV['dataUser']['belongingGroups']); // Read?
        $isWrite =  IsInSet($data['writing_groups'],$USER_PRIV['dataUser']['belongingGroups']); // write?
        if ($isOwner || $isRead) { //  Ok access
            $data['files'] = str_replace('\\','',$data['files']);

        } else {
                return '011';
        }
        return  $data;

    }
    /*
    ** Verificación de si está posicionado en el directorio adecuado y dispone de acceso para crear un documento
    */
    public function accessDocumentWrite($param ,Request $request, Response $response)
    {
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory
                            

        if(  $USER_PRIV ['selectedDirectory'] == NULL ) { // No se ha fijado el directorio por defecto
            return '012';
        }
        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];
        if ($selectDirectory <> NULL) {
            $sql = "SELECT * FROM edas_directory WHERE  id_edas_directory  = $selectDirectory";
        }

        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data == NULL) { // No recuperado datos, Documento no existe
            return '018';
        }
    
        // Function "IsInSet()" this in APPSETTING
        // Access control
        $isOwner = IsInSet($data['owners'], $USER_PRIV['dataUser']['id_edas_user']); // Owner?
        $isRead =  IsInSet($data['reading_groups'] ,$USER_PRIV['dataUser']['belongingGroups']); // Read?
        $isWrite =  IsInSet($data['writing_groups'],$USER_PRIV['dataUser']['belongingGroups']); // write?
        if (!($isOwner || $isWrite)) { //  KO access
                return '009';
        }
        return  '000';

    }
    /*
    ** DELETE del documento
    */
    public function deleteDocument($param)
    {
        $id_document = $param['id_document'];     // CODE of File
        $sql = "delete from edas_file where id_edas_file = $id_document";
        DB::Exec($sql);                                 // Delete record in database

        $directory_rm = FILES_DIR."/$id_document";
        $files = glob($directory_rm .'/*');             // get all file names
        foreach($files as $file){                       // iterate files
          if(is_file($file)) {
            unlink($file);                              // delete file
          }
        }
        $error_rm = rmdir($directory_rm);               // Delete files of Document
        return $error_rm;

    }
    /*
    ** UPDATE del documento
    */
    public function updateDocument($param, Request $request, Response $response)
    {
        $id_document = $param['id_document'];     // CODE of File
        $code = $param['code'];
        $name = $param['name'];
        $locked = $param['locked'];
        if ( !is_string($code) || !is_string($name) || !( $locked == 0 || $locked == 1)) { // Type of variables
            return '015'; // Error 015
        }
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory

        if(  $USER_PRIV ['selectedDirectory'] == NULL ) { // No se ha fijado el directorio por defecto
            return '012';
        }
        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];
        $code = RemoveSpecialChar($code);   // Function in APPSETTING . Delecte charaters specials
        $sql = "SELECT code, name, locked,  lastModificationDate, userLastModification FROM edas_file where edas_directory_id = $selectDirectory and id_edas_file <> $id_document and code = '$code'"; 
        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data <> NULL) { // Recuperado datos, Duplicado "code"
            return '016';
        }  
        $data = array();
        $keyvalues = array();
        $data["code"] = $code;
        $data["name"]  = $name;
        $data["locked"] = $locked;
        $keyvalues["id_edas_file"] = $id_document;
        DB::Update("edas_file", $data, $keyvalues );  // UPDATE Database
        return '000';
    }  
 
    /*
    ** Add del documento
    */
    public function addDocument($param, Request $request, Response $response)
    {
        $code = $param['code'];
        $name = $param['name'];
        $locked = $param['locked'];
        if ( !is_string($code) || !is_string($name) || !( $locked == 0 || $locked == 1)) { // Type of variables
            return '015'; // Error 015
        }
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory

        if(  $USER_PRIV ['selectedDirectory'] == NULL ) { // No se ha fijado el directorio por defecto
            return '012';
        }
        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];
        $code = RemoveSpecialChar($code);   // Function in APPSETTING . Delecte charaters specials
        $sql = "SELECT code, name, locked,  lastModificationDate, userLastModification FROM edas_file where edas_directory_id = $selectDirectory and code = '$code'"; 
        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data <> NULL) { // Recuperado datos, Duplicado "code"
            return '016';
        } 
        $sql = "SELECT * FROM edas_directory  WHERE id_edas_directory =  $selectDirectory"; 
        $rs = DB::Query($sql);

        $dataDir = $rs->fetchAssoc(); 
        $data = array();
        // edas_directory_id , code , name , locked , files , owners , reading_groups , writing_groups , 
        // creationDate , lastModificationDate , userCreation , userLastModification
        $data["code"] = $code;
        $data["name"]  = $name;
        $data["locked"] = $locked;
        $data["edas_directory_id"] = $selectDirectory  ;
        $data["creationDate"] = now()  ;
        $data["lastModificationDate"] = now()  ;
        $data["userCreation"] = $USER_PRIV['dataUser']['login']   ;
        $data["userLastModification"] = $USER_PRIV['dataUser']['login']    ;
        $data["owners"] = $dataDir['owners']    ;
        $data["reading_groups"] = $dataDir['reading_groups']     ;
        $data["writing_groups"] = $dataDir['writing_groups']     ;

        DB::Insert("edas_file", $data );  // INSERT Database

        $lastID = DB::LastId();
        return array('error'=>'000','lastID'=>$lastID);
    } 

    /*
    ** LIST File del documento
    */
    public function listFile($param, Request $request, Response $response, array $args)
    {
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory
        $action = $args['action'];
        $id_document = $args['id_document'];

        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];

        $sql = "SELECT files FROM edas_file where id_edas_file = $id_document "; 
        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data == NULL) { // No existe el documento
            return '010';
        }
        $files = array();
        $files_arr = json_decode($data['files'], true);

        foreach ($files_arr as &$file) {
               $files[] = array("usrName"=>$file['usrName'],"size"=>$file["size"],"type"=>$file["type"]);
            }

        return $files;
    }
    /*
    ** View File del documento
    */
    public function viewFile($param, Request $request, Response $response, array $args)
    {
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory
        $action = $args['action'];
        $id_document = $args['id_document'];
        $usrName = $param["usrName"];

        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];

        $sql = "SELECT files FROM edas_file where id_edas_file = $id_document "; 
        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data == NULL) { // No existe el documento
            return '010';
        }
        $files = array();
        $files_arr = json_decode($data['files'], true);

        foreach ($files_arr as &$file) {
            if ($file['usrName'] == $usrName){
                $pathFile = FILES_ROOT."/".$file['name'];
                $contentFile = file_get_contents($pathFile);   
                // Encode the image string data into base64
                $dataFile = base64_encode($contentFile);
                $files[] = array("usrName"=>$file['usrName'],"size"=>$file["size"],"type"=>$file["type"],"base64File"=>$dataFile);
            }
        }
        iF (count($files) == 0) { // no ha encontrado el archivo
            return '019';
        }
        return $files;
    }
    /*
    ** Delete File del documento
    */
    public function deleteFile($param, Request $request, Response $response, array $args)
    {
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory
        $action = $args['action'];
        $id_document = $args['id_document'];
        $usrName = $param["usrName"];

        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];

        $sql = "SELECT files FROM edas_file where id_edas_file = $id_document "; 
        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data == NULL) { // No existe el documento
            return '010';
        }
        $files = array();
        $files_arr = json_decode($data['files'], true);

        foreach ($files_arr as &$file) {
            if ($file['usrName'] <> $usrName){
                $files[] = $file;
            }
        }
        if (count($files) == count($files_arr)) { // no ha encontrado el archivo
            return '019';
        }

        $files = json_encode($files, true);

        $data = array();
        $data["files"] = $files;
        $keyvalues["id_edas_file"] = $id_document;
        DB::Update("edas_file", $data, $keyvalues );

        return array("id_document"=>$id_document,"usrName"=>$usrName);
    }
    /*
    ** Add File del documento
    */
    public function addFile($param, Request $request, Response $response, array $args)
    {
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory
        $action = $args['action'];
        $id_document = $args['id_document'];
        $usrName = $param["usrName"];

        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];

        $sql = "SELECT files FROM edas_file where id_edas_file = $id_document "; 
        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data == NULL) { // No existe el documento
            return '010';
        }
        $files = array();
        $files_arr = json_decode($data['files'], true);

        foreach ($files_arr as &$file) {
            if ($file['usrName'] == $usrName){
                $files[] = array("usrName"=>$file['usrName']);
            }
        }
        iF (count($files) <> 0) { // Se ha encontrado un fichero igual
            return '020';
        }
        $pathDir = FILES_DIR."/".$id_document;
        $pathFile = FILES_DIR."/".$id_document."/".$this->RemoveSpecialCharFile($usrName);
        if (!file_exists($pathDir)) { // Directorio existe?
            mkdir($pathDir, 0755);
        }

        $fp = fopen($pathFile, "wb");
        fwrite($fp,base64_decode($param['base64File'])); // Escribe fichero en FileSystem
        fclose($fp);
        $size = filesize($pathFile);
        $nameFile_arr = explode('/',$pathFile); 
        $x = count($nameFile_arr)-3;  // son 3 partículas
        $name = $nameFile_arr[$x].'/'.$nameFile_arr[$x+1].'/'.$nameFile_arr[$x+2];
        
        $files_arr[] = array("name"=>$name,"usrName"=>$usrName,"size"=>$size,"type"=>$param["type"]);
     
        $files = json_encode($files_arr, true);

        $data["files"] = $files;
        $keyvalues["id_edas_file"] = $id_document;
        DB::Update("edas_file", $data, $keyvalues );

        return array("id_document"=>$id_document,"usrName"=>$usrName);
    }
    /*
    ** Update File del documento
    */
    public function updateFile($param, Request $request, Response $response, array $args)
    {
        $USER_PRIV = $this->restoreUser($request, $response);  // Restauramos los campos del USER and data of Directory
        $action = $args['action'];
        $id_document = $args['id_document'];
        $usrName = $param["usrName"];

        // Recuperar directorio y ficheros del último directorio
        $selectDirectory = $USER_PRIV['selectedDirectory'];

        $sql = "SELECT files FROM edas_file where id_edas_file = $id_document "; 
        $rs = DB::Query($sql);

        $data = $rs->fetchAssoc();
        if(  $data == NULL) { // No existe el documento
            return '010';
        }
        $files = array();
        $files_arr = json_decode($data['files'], true);
        $files_arr2 = array();

        foreach ($files_arr as &$file) {
            if ($file['usrName'] == $usrName){
                $files[] = array("usrName"=>$file['usrName']);
            } else {
                $files_arr2[]=$file;  // Save all files diferent
            }
        }
        iF (count($files) == 0) { // No se ha encontrado un fichero igual
            return '019';
        }
        $pathDir = FILES_DIR."/".$id_document;
        $pathFile = FILES_DIR."/".$id_document."/".$this->RemoveSpecialCharFile($usrName);
        if (!file_exists($pathDir)) { // Directorio existe?
            mkdir($pathDir, 0755);
        }
        unlink($pathFile);          // Delete file old
        $fp = fopen($pathFile, "wb");
        fwrite($fp,base64_decode($param['base64File'])); // Escribe fichero en FileSystem
        fclose($fp);
        $size = filesize($pathFile);
        $nameFile_arr = explode('/',$pathFile); 
        $x = count($nameFile_arr)-3;  // son 3 partículas
        $name = $nameFile_arr[$x].'/'.$nameFile_arr[$x+1].'/'.$nameFile_arr[$x+2];
        
        $files_arr2[] = array("name"=>$name,"usrName"=>$usrName,"size"=>$size,"type"=>$param["type"]);
     
        $files = json_encode($files_arr2, true);

        $data["files"] = $files;
        $keyvalues["id_edas_file"] = $id_document;
        DB::Update("edas_file", $data, $keyvalues );

        return array("id_document"=>$id_document,"usrName"=>$usrName);
    }

    /**
     * Recuperar los datos del usuario de la sesion
     */
    public function restoreUser( Request $request, Response $response)
    {
        global $errorMessages;
        // Getting request headers
        $headers = $request->getHeaders();    
        // get the api key
        $token = $headers['token-user'];
        $token = $token[0];
        $rs = DB::Query("SELECT * FROM edas_session_api WHERE uuid = '$token'");
        $data = $rs->fetchAssoc();
        return json_decode($data['sessionVariables'],true);
    }

    /**
     * Save los datos del usuario de la sesion y data Directory of Path
     */
    public function saveUser( array $USER_PRIV,Request $request, Response $response)
    {
        global $errorMessages;
        // Getting request headers
        $headers = $request->getHeaders();    
        // get the api key
        $token = $headers['token-user'];
        $token = $token[0];
        $data = array();
        $keyvalues = array();
        $data["sessionVariables"] =json_encode($USER_PRIV,true);
        $keyvalues["uuid"] = $token;
        DB::Update("edas_session_api", $data, $keyvalues );

        return true;
    }

    private function guidv4($data = null) {
        // Generate 16 bytes (128 bits) of random data or use the data passed into the function.
        $data = $data ?? random_bytes(16);
        assert(strlen($data) == 16);
    
        // Set version to 0100
        $data[6] = chr(ord($data[6]) & 0x0f | 0x40);
        // Set bits 6-7 to 10
        $data[8] = chr(ord($data[8]) & 0x3f | 0x80);
    
        // Output the 36 character UUID.
        return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
    } 

    private function RemoveSpecialCharFile($str) // To normalize filer name
{
    $res = preg_replace('([^A-Za-z0-9_. ])', ' ', $str);
		$res = str_replace(' ','_',$res);
    return $res;
}

}
