<?php

require_once __DIR__.'/../constant.php';
require_once(__ROOT__.'class'.SLASH.'Database.class.php');
require_once(__ROOT__.'connector'.SLASH.'Oracle.class.php');
require_once(__ROOT__.'connector'.SLASH.'Mysql.class.php');
require_once(__ROOT__.'connector'.SLASH.'Sqlite.class.php');
require_once(__ROOT__.'connector'.SLASH.'SqlServer.class.php');

/**
* Classe de gestion ORM
* @version 2
* @category sql
* @author Valentin CARRUESCO
**/

class Entity {
    public $pdo = null,$baseUid= 'local';
    public $created,$updated,$creator,$updater,$joins,$entityLabel = '';
    public $links = array();
    public $foreignColumns = array();
    public $fieldMapping = array();

    const ACTIVE = 'published';
    const INACTIVE = 'deleted';

    public function __construct() {
        if (!isset($this->TABLE_NAME)) $this->TABLE_NAME = strtolower(get_called_class());
        $this->connect();
        $this->fields['created'] = array('type'=>'date', 'label' => 'Date de création');
        $this->fields['updated'] = array('type'=>'date', 'label' => 'Date de modification');
        $this->fields['updater'] = array('type'=>'text', 'label' => 'Modificateur');
        $this->fields['creator'] = array('type'=>'text', 'label' => 'Créateur');
        $this->joins = array();
        $this->created = time();

        $this->fieldMapping = $this->field_mapping($this->fields);

        global $myUser;
        if(is_object($myUser) && $myUser->login!='') $this->creator = $myUser->login;
    }

    public function field_mapping($fields = array()){
        $fieldMapping = array();

        $fieldTypes = FieldType::available();
        foreach($fields as $field => $type) {
            if(!is_array($type)) $type = array('type'=>$type,'column'=>$field);
            if(!isset($type['column'])) $type['column'] = $field;
            if(isset($type['link'])) $this->links[$type['column']] = $type['link'];
            
            $type['fieldtype'] = $type['type'];
            //conversion des field type en sql type
            if(isset($fieldTypes[$type['type']]))
                $type['type'] = $fieldTypes[$type['type']]->sqlType;
            
            $fieldMapping[$field] = $type;
        } 

        return $fieldMapping;
    }

    //Connexion à la base
    public function connect() {
        $this->pdo = Database::instance($this->baseUid);
        global $databases_credentials;
        $this->connector = $databases_credentials[$this->baseUid]['connector'];
    }

    public function __toString() {
        foreach ($this->toArray() as $key => $value) {
            echo $key.' : '.$value.','.PHP_EOL;
        }
    }

  
    public function __sleep() {
        return array_keys($this->toArray());
    }

    public function __wakeup() {
        $this->connect();
    }

    //Comparaison de deux instances d'une même entité, retourne les champs ayant changés uniquement
    public static function compare($obj1,$obj2,$ignore=array()){
        $class = get_called_class();
      
        $compare = array();
        foreach ($obj1->fields as $field => $type) {
            if($field == 'updated' || $field == 'updater' || in_array($field, $ignore)) continue;
  
            if($obj1->$field != $obj2->$field){
                if($type=='int' && (($obj1->$field==0 && $obj2->$field =='') || ($obj2->$field=='' && $obj1->$field ==0)) ) continue;
                $compare[] = array('field'=>$field,'value1'=>$obj1->$field,'value2'=>$obj2->$field);
            }
        }
        return $compare;
    }

    public static function fields($onlyKeys = true){
        $class = get_called_class();
        $instance = new $class();
        if($onlyKeys) return array_keys($instance->fields);
        return $instance->fieldMapping;
    }

    public function toArray($decoded=false) {
        $fields = array();
        foreach ($this->fields as $field => $type) {
            $fields[$field] = $decoded ? html_entity_decode($this->$field) : $this->$field;
        }
        return $fields;
    }

    public function toText() {
        $text = array();
        foreach ($this->fields as $field => $type) {
            $value = is_object($this->$field) ? '[object]' : $this->$field;
            $text[]= $field.' = '.$value;
        }
        return implode(', ',$text);
    }

    public function fromArray($array) {
        foreach ($array as $field => $value) {
            $this->$field = $value;
        }
    }

    public function closeDatabase() {
        // $this->close();
    }

    
    //Libellé human readable de l'entité
    public static function entityLabel() {
        $class = get_called_class();
        $instance = new $class();
        return !empty($instance->entityLabel) ? $instance->entityLabel : $class;
    }

    public static function tableName($escapeName = false,$instance = null) {
        global $databases_credentials;
        $class = get_called_class();
        if(!isset($instance)) $instance = new $class();
        $prefix = isset($databases_credentials[$instance->baseUid]['prefix']) ? $databases_credentials[$instance->baseUid]['prefix'] : '';
        $connector = $instance->connector;
        return $escapeName ? $connector::table_escape.$prefix.$instance->TABLE_NAME.$connector::table_escape : $prefix.$instance->TABLE_NAME;
    }

    // GESTION SQL
    /**
     * Verifie l'existence de la table en base de donnée.
     * @category manipulation SQL
     * @param <String> créé la table si elle n'existe pas
     * @return true si la table existe, false dans le cas contraire
     */
    public static function checkTable($autocreate = false) {
        $class = get_called_class();
        $instance = new $class();
        $query = 'SELECT count(*) as numRows FROM sqlite_master WHERE type="table" AND name=?';
        $statement = $instance->customQuery($query, array($class::tableName(false,$instance)));

        if ($statement != false) {
            $statement = $statement->fetchArray();
            if ($statement['numRows'] == 1) {
                $return = true;
            }
        }
        if ($autocreate && !$return) self::create();
        return $return;
    }

    public static function install($classDirectory) {
        foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
            $infos = explode('.', basename($file));
            $class = array_shift($infos);
            require_once($classDirectory.SLASH.$class.'.class.php');
            $reflection = new ReflectionClass($class);
            if (!class_exists($class) || !method_exists($class, 'create') || $class == get_class() || $reflection->isAbstract()) {
                continue;
            }
            
            $class::create();
        }
    }
    
    public static function uninstall($classDirectory) {
        foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
            $infos = explode('.', basename($file));
            $class = array_shift($infos);
            require_once($classDirectory.SLASH.$class.'.class.php');
            $reflection = new ReflectionClass($class);
            if (!class_exists($class) || !method_exists($class, 'drop') || $class == get_class() || $reflection->isAbstract()) continue;
            $class::drop();
        }
    }

     /**
     * Methode de vidage de l'entité.
     * @category manipulation SQL
     * @return Aucun retour
     */
    public static function truncate() {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        $sql = $connector::truncate();
        $query = Entity::render($sql,array(
           'table' => $class::tableName(false,$instance),
           'fieldMapping' => $instance->fieldMapping
           ));
        
        $instance->customQuery($query);
    }


    /**
     * Methode de creation de l'entité.
     * @category manipulation SQL
     * @return Aucun retour
     */
    public static function create() {
        $class = get_called_class();
        $instance = new $class();
        $fields = array();

        $connector = $instance->connector;
        $types = $connector::types();

        $fieldMapping = $instance->field_mapping($instance->fields);

        foreach ($instance->fields(false) as $slug => $field) {
            $fields[$slug] =  isset($types[$field['type']]) ? $types[$field['type']] : $types['default'];
        }
        
        
        $sql = $connector::create();
        $query = Entity::render($sql,array(
           'table' => $class::tableName(false,$instance),
           'fields' => $fields,
           'fieldMapping' => $instance->fieldMapping
           ));

        $instance->customQuery($query);
        if(isset($instance->indexes)) $instance->index($instance->indexes);
    }

    public static function drop() {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        $sql = $connector::drop();
        $query = Entity::render($sql,array(
           'table' => $class::tableName(false,$instance),
           'fieldMapping' => $instance->fieldMapping
           ));
        
        $instance->customQuery($query);
        if(isset($instance->indexes)) $instance->index($instance->indexes,false);
    }

    /**
     * Methode d'insertion ou de modifications d'elements de l'entité.
     * @category manipulation SQL
     * @param  Aucun
     * @return Aucun retour
     */
    public function save() {
        global $myUser;
        $this->updated = time();
        
        if(is_object($myUser) && $myUser->login!='') $this->updater = $myUser->login;
        //update
        if (isset($this->id) && $this->id > 0) {
            $fields = array();
            foreach ($this->fields as $field => $type) 
                $fields[$field] = $this->{$field};
            self::change($fields,array('id'=>$this->id));
        //insert
        } else {
            $connector = $this->connector;
            $data = array();
            $fields = array();
            $i = 0;
            foreach ($this->fields as $field => $type) {
                if((is_array($type) && $type['type'] == 'key') || $type == 'key') continue;
                $data[':'.$i] = $this->{$field};
                if((is_array($type) && $type['type'] == 'boolean') || $type == 'boolean') $data[':'.$i] = $data[':'.$i] ? 1 : 0;
                $fields[$field] = ':'.$i;
                $i++;
            }
            $sql = $connector::insert();
            $query = self::render($sql,array(
               'table' => $this->tableName(false,$this),
               'fields' => $fields,
               'fieldMapping' => $this->fieldMapping
            ));
            $this->customQuery($query, $data);
        }
        $this->id = !isset($this->id) || !is_numeric($this->id) ? $this->pdo->lastInsertId() : $this->id;
    }

    
     /**
     * Methode d'insertion massive de l'entité.
     * @category manipulation SQL
     * @param  $entities tableau des entité a inserer (update non géré)
     * @param $maxLines grouper par requete de $maxLines maximum
     * @return Aucun retour
     */
    public static function saveAll($entities,$maxLines = 300) {
        global $myUser;

        if(empty($entities)) return;

        $reference = $entities[0];
        $connector = $reference->connector;
        $sql_head = $connector::insert_head();
        $sql_body = $connector::insert_body();
        $time = time();
        $login = is_object($myUser) && $myUser->login!='' ? $myUser->login : '';

        //sépare en requetes groupées de $maxLines lignes max
        $entities_groups = array_chunk($entities, $maxLines);
        $tableName = $reference->tableName(false,$reference);
        foreach ($entities_groups as $entities_group) {
            $end = count($entities_group)-1;
            $data = array();
            

            foreach($entities_group as $u=>$entity){
                $entity->updated = $time;
                $entity->updater = $login;
               
                $fields = array();
                $i = 0;
                foreach ($entity->fields as $field => $type) {
                    if ($type == 'key') continue;
                    $data[':'.$u.'a'.$i] = $type!='boolean' ?  $entity->{$field} : ($entity->{$field} ? 1:0);
                    $fields[$field] = ':'.$u.'a'.$i;
                    $i++;
                }

                if($u==0){
                    $query = self::render($sql_head,array(
                       'table' => $tableName,
                       'fields' => $fields,
                       'fieldMapping' => $reference->fieldMapping
                    ));
                }
                
                $query .= self::render($sql_body,array(
                   'table' => $entity->tableName(false,$entity),
                   'fields' => $fields,
                   'fieldMapping' => $entity->fieldMapping
                ));
                if($u!=$end) $query .= ',';
            }
            $reference->customQuery($query, $data);
        }
    }

    /**
     * Méthode de modification d'éléments de l'entité.
     * @category manipulation SQL
     * @param <Array>  $colonnes=>$valeurs
     * @param <Array>  $colonnes           (WHERE) =>$valeurs (WHERE)
     * @return Aucun retour
     */
    public static function change($columns, $columns2 = array()) {
        $class = get_called_class();
       
        $instance = new $class();
        $connector = $instance->connector;

        $fields = array();
        $i = 0;
        $values = array();
        foreach ($columns as $field => $value) {
            $values[':'.$i] = $value;
            $fields[$field] = ':'.$i;
            $i++;
        }
     
        $filters = array();
        foreach($columns2 as $key=>$value){
            $filter = array(
                'operator' => '=',
                'field' => $key,
                'postoperator' => ''
            );
            if(strpos($key,':')!==false){
                $infos = explode(':',$key);
                $filter['operator'] = $infos[1];
                $filter['field'] = $infos[0];
            }
            $fieldInfos = $instance->fieldMapping[$filter['field']];
            $filter['type'] = $fieldInfos['type'];
            $filter['column'] = $fieldInfos['column'];
            $connector::processField($filter,$value,$values,$i);
            $filters[] = $filter;
         }
         $data = array(
            'table' => $class::tableName(false,$instance),
            'fields' => $fields,
            'filter' => !isset($filters) ||  count($filters) == 0 ? null: $filters,
            'fieldMapping' => $instance->fieldMapping
        );
        $sql = $connector::update();
        $sql = Entity::render($sql,$data);
        $instance->customQuery($sql, $values);
    }

    /**
     * Méthode de selection de tous les elements de l'entité.
     * @category manipulation SQL
     * @param <String> $ordre=null
     * @param <String> $limite=null
     * @return <Array<Entity>> $Entity
     */
    public static function populate($order = null, $limit = null,$selColumn = array('*'),$joins = 0) {
        $results = self::loadAll(array(), $order, $limit,$selColumn,$joins);
        return $results;
    }

    /**
     * Méthode de selection multiple d'elements de l'entité.
     * @category manipulation SQL
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $ordre=null
     * @param <String> $limite=null
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     * @return <Array<Entity>> $Entity
     */
   
    public static function loadAll($columns = array(), $order = null, $limit = null, $selColumn = array('*'), $joins = 0) {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;

        $values = array();
        $i=0;
        $filters = array();
        foreach($columns as $key=>$value){
            $filter = array(
                'operator' => '=',
                'field' => $key,
                'postoperator' => ''
            );
            if(strpos($key,':')!==false){
                $infos = explode(':',$key);
                $filter['operator'] = $infos[1];
                $filter['field'] = $infos[0];
            }

            $fieldInfos = $instance->fieldMapping[$filter['field']];
            $filter['type'] = $fieldInfos['type'];
            $filter['column'] = $fieldInfos['column'];

            $connector::processField($filter,$value,$values,$i);
            $filters[] = $filter;
         }
         
         if(!empty($order)){
             foreach ($order as $key=>$clause) {
                foreach ($instance->fieldMapping as $attribute => $infos) {
                    $order[$key] = str_replace( $attribute,$infos['column'],$order[$key]);
                }
             }
         }
         $tableName = $class::tableName(false,$instance);
         $data = array(
            'table' => $tableName,
            'selected' => $selColumn,
            'limit' =>  !isset($limit) || count($limit) == 0 ? null: $limit,
            'orderby'  => !isset($order) || count($order) == 0 ? null: $order,
            'filter' => !isset($filters) ||  count($filters) == 0 ? null: $filters,
            'fieldMapping' => $instance->fieldMapping
        );
        $data['joins']  = array();
        if($joins!=0){
            foreach ($data['selected'] as $k=>$column) {
               $data['selected'][$k] = $tableName.'.'.$column;
            }
            $data = self::recursiveJoining($instance,$data,$joins);
        }
        
            
        $sql = $connector::select();
        $sql = Entity::render($sql,$data);

        return $instance->customQuery($sql, $values, true, $joins);
    }

    public static function get($options=array()) {
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;

        $values = array();
        $i=0;
        $filters = array();


        if(!empty($options['where'])){
            foreach($options['where'] as $key=>$value){
                $filter = array(
                    'operator' => '=',
                    'field' => $key,
                    'postoperator' => ''
                );
                if(strpos($key,':')!==false){
                    $infos = explode(':',$key);
                    $filter['operator'] = $infos[1];
                    $filter['field'] = $infos[0];
                }

                $fieldInfos = $instance->fieldMapping[$filter['field']];
                $filter['type'] = $fieldInfos['type'];
                $filter['column'] = $fieldInfos['column'];

                $connector::processField($filter,$value,$values,$i);
                $filters[] = $filter;
             }
         }

         if(!empty($order)){
             foreach ($order as $key=>$clause) {
                foreach ($instance->fieldMapping as $attribute => $infos) {
                    $order[$key] = str_replace( $attribute,$infos['column'],$order[$key]);
                }
             }
         }
         $tableName = $class::tableName(false,$instance);
         $data = array(
            'table' => $tableName,
            'selected' => $selColumn,
            'limit' =>  !isset($limit) || count($limit) == 0 ? null: $limit,
            'orderby'  => !isset($order) || count($order) == 0 ? null: $order,
            'filter' => !isset($filters) ||  count($filters) == 0 ? null: $filters,
            'fieldMapping' => $instance->fieldMapping
        );
        $data['joins']  = array();
        if($joins!=0){
            foreach ($data['selected'] as $k=>$column)
               $data['selected'][$k] = $tableName.'.'.$column;

            $data = self::recursiveJoining($instance,$data,$joins);
        }
        $sql = $connector::select();
        $sql = Entity::render($sql,$data);

        return $instance->customQuery($sql, $values, true, $joins,$alterator);
    }

    /**
     * Méthode privée de gestion du join récursif sur les objets liés
     * @category manipulation SQL
     * @param <Object>  $instance   $instance de départ
     * @param <Array> $data    Tableau de construction de la requete via render()
     * @param <Int> $iterations Nombre d'iteration réecurive maximum
     * @return <Array> $data
     */
    private static function recursiveJoining($instance,$data,$iterations,$joinInstanceAlias=''){
        if($iterations==0) return $data;
        $iterations--;

        if(isset($instance->links)){
            $instanceTable = $instance::tableName();
            foreach ($instance->links as $field => $className) {

                $className = str_replace('.class.php','',basename($className));
                $linkTable = $className::tableName();
                $field2 = 'id';
                $classField = explode('.',$className);

                if(isset($classField[1]))
                    list($className,$field2) = $classField;

                $alias = substr($linkTable,0,3).'_'.$field;
                
                $joinInstance = new $className();
                foreach ($joinInstance->fields as $key=>$type) {
                    $data['selected'][] = $alias.'.'.$key.' as '.$linkTable.'_join_'.$key;
                }
             
                $joinTable1 = $instanceTable;

                if(!empty($joinInstanceAlias)) $joinTable1 = $joinInstanceAlias;
                
                $data['joins'][] = array(
                    'jointable1' => $joinTable1,
                    'jointable2' => $linkTable,
                    'jointableAlias' => $alias,
                    'field1' => $field,
                    'field2' =>  $field2
                );
                $data = self::recursiveJoining($joinInstance,$data,$iterations,$alias);
            }
        }
        return $data;
    }


    /**
     * Methode de comptage des éléments de l'entité.
     * @category manipulation SQL
     * @return<Integer> nombre de ligne dans l'entité'
     */
    public static function rowCount($columns = array()) {
        $values = array();
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;

        $i=0;
        $values = array();
        $filters = array();
        foreach($columns as $key=>$value){
            $filter = array(
                'operator' => '=',
                'field' => $key,
                'postoperator' => ''
            );
            if(strpos($key,':')!==false){
                $infos = explode(':',$key);
                $filter['operator'] = $infos[1];
                $filter['field'] = $infos[0];
            }

            $fieldInfos = $instance->fieldMapping[$filter['field']];
            $filter['type'] = $fieldInfos['type'];
            $filter['column'] = $fieldInfos['column'];

            $connector::processField($filter,$value,$values,$i);
            $filters[] = $filter;
        }

        $data = array(
            'table' => $class::tableName(false,$instance),
            'selected' => 'id' ,
            'filter' =>  count($filters) == 0 ? null: $filters,
            'fieldMapping' => $instance->fieldMapping
        );
        
        $sql = $connector::count();
        $execQuery = $instance->customQuery(Entity::render($sql,$data), $values);
        $row = $execQuery->fetch();

        return $row['number'];
    }

    public static function loadAllOnlyColumn($selColumn, $columns, $order = null, $limit = null) {  
        $objects = self::loadAll($columns, $order, $limit, $selColumn); 
        if (count($objects) == 0) $objects = array();
        return $objects;
    }

    /**
     * Méthode de selection unique d'élements de l'entité.
     *

     *
     * @category manipulation SQL
     *
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     *
     * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
     */
    public static function load($columns = array(),$joins =0) {
        $objects = self::loadAll($columns, null, array('1'),array('*'),$joins);
        if (!isset($objects[0])) $objects[0] = false;
        return $objects[0];
    }

    /**
     * Méthode de selection unique d'élements de l'entité.
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     * @deprecated use byId
     * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
     */
    public static function getById($id,$joins =0 ) {
        return self::byId($id,$joins =0);
    }

    /**
     * Méthode de selection unique d'élements de l'entité.
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
     */
    public static function byId($id,$joins =0 ) {
        return self::load(array('id' => $id),$joins);
    }

    //parsing des templates sql de cnnecteurs avec les filtres/columns/data...
    public static function render($sql,$data=array()) {
       //loop
        $sql = preg_replace_callback('/{{\:([^\/\:\?}]*)}}(.*?){{\/\:[^\/\:\?}]*}}/',function($matches) use ($data) {
            $tag = $matches[1];
            $sqlTpl = $matches[2];

            $sql = '';
            if(isset($data[$tag])){
                $i = 0;
                $values = $data[$tag];

                if($tag =='joins'){
                    //joins
                    foreach($values as $join){
                        $occurence = $sqlTpl;
                        foreach($join as $key=>$value){
                            $occurence = str_replace(array('{{'.$key.'}}'),array($value),$occurence); 
                        }
                        $sql.= $occurence;
                    }
                }else if($tag =='filter'){
                    //filters

                    foreach($values as $key=>$value){
                        $i++;

                        $last = $i == count($values);
                        $operator = $value['operator'];
                        $postoperator = $value['postoperator'];
                        $key = $value['column'];
                        $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,
                            $value['tag'],
                            $operator,
                            $postoperator),
                        $sqlTpl); 
                        $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
                            return $last? '': $matches[1];
                        },$occurence);

                        $sql.= $occurence;
                    }
                } else {
                    //Autre boucles
                    foreach($values as $key=>$value){
                        $i++;
                        $last = $i == count($values);
                        $operator = isset($data['operator']) ? $data['operator'][0] : '=';
                        $postoperator = isset($data['postoperator']) ? $data['postoperator'][0] : '';
                        if(strpos($key,':')!==false){
                            $infos = explode(':',$key);
                            $key = $infos[0];
                            $operator = $infos[1];
                            
                            if($operator=='IN' || $operator=='NOT IN'){
                                $operator = $operator.'(';
                                $postoperator = ')';
                            }
                        } 
                        $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,$value,$operator,$postoperator),$sqlTpl); 
                        $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
                            return $last? '': $matches[1];
                        },$occurence);

                        $sql.= $occurence;
                    }
                }
                return $sql;
            }
            return '';
        },$sql);

        //conditions
        $sql = preg_replace_callback('/{{\?([^\/\:\?}]*)}}(.*?){{\/\?[^\/\:\?}]*}}/',function($matches) use ($data) {
            $key = $matches[1];
            $sql = $matches[2];
            return !isset($data[$key]) || (is_array($data[$key]) && count($data[$key])==0) ?'':$sql;
        },$sql); 

        //simple vars
        $sql = preg_replace_callback('/{{([^\/\:\;\?}]*)}}/',function($matches) use ($data) {
            $key = $matches[1];
            return isset($data[$key])?$data[$key]:'';
        },$sql); 
      
        return $sql;
    }


    /**
     * Methode de définition de l'éxistence d'un moins un des éléments spécifiés en base.
     *

     * @category manipulation SQL
     * @return<boolean> existe (true) ou non (false)
     */
    public static function exist($columns = array()) {
        $result = self::rowCount($columns);
        return $result != 0;
    }

    public static function deleteById($id) {
        self::delete(array('id' => $id));
    }
    
    /**
     * Méthode de Suppression d'elements de l'entité.

     * @category manipulation SQL
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     * @return Aucun retour
     */
    public static function delete($columns, $limit = array()) {
        $values = array();
        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        
        $i=0;
        $values = array();
        $filters = array();
        foreach($columns as $key=>$value){
            $filter = array(
                'operator' => '=',
                'field' => $key,
                'postoperator' => ''
            );
            if(strpos($key,':')!==false){
                $infos = explode(':',$key);
                $filter['operator'] = $infos[1];
                $filter['field'] = $infos[0];
            }

            $fieldInfos = $instance->fieldMapping[$filter['field']];
            $filter['type'] = $fieldInfos['type'];
            $filter['column'] = $fieldInfos['column'];

            $connector::processField($filter,$value,$values,$i);
            $filters[] = $filter;
        }
        
        $data = array(
           'table' => $class::tableName(false,$instance),
           'limit' =>  count($limit) == 0 ? null: $limit,
           'filter' =>  count($filters) == 0 ? null: $filters,
           'fieldMapping' => $instance->fieldMapping
           );
        
        $sql = $connector::delete();
        return $instance->customQuery(Entity::render($sql,$data), $values);
    }

    /**
     * Méthode d'indexation de la ou les colonnes ciblées
     * nb : il est possible d'appeller automatiquement cette méthode sur les classes entity lors du create
     * si la classe contient l'attribut $this->indexes = array(...);

     * @category manipulation SQL
     * @param <Array> | <String>  $colonne(s)  
     * @param <Boolean>  Mode (true : ajout, false : suppression)
     * @return Aucun retour
     */
    public static function index($columns,$mode = true){
        if(!is_array($columns)) $columns = array($columns);
        $columns = array_filter($columns);

        $class = get_called_class();
        $instance = new $class();
        $connector = $instance->connector;
        $tableName = $class::tableName(false,$instance);

        foreach($columns as $column){
            if(!is_array($column)) $column = array($column);
            $data = array(
                'table' => $tableName,
                'column' =>  '`'.implode('`,`',$column).'`',
                'index_name' =>  $tableName.'_'.implode('_',$column),
                'fieldMapping' => $instance->fieldMapping
            );

            $results = $class::staticQuery(Entity::render($connector::count_index(),$data));
            $exists = $results->fetch();
            
            if($mode){
                if($exists['exists'] != 1) $class::staticQuery(Entity::render($connector::create_index(),$data));
            }else{
                if($exists['exists'] > 0) $class::staticQuery(Entity::render($connector::drop_index(),$data));
            }
        }
    }
    
    //Génération d'une pagination
    public static function paginate($itemPerPage,$currentPage,&$query,$data,$alias=''){
        
        $class = get_called_class();
        $instance = new $class();
        $keys = array_keys($instance->fields, 'key');
        $key = count($keys) == 1 ? $keys[0] : 'id';
        $tableName = $class::tableName(false,$instance);
        $queryNumber = $query;

        $queryNumber = preg_replace("/(?<!\([^(\)])(SELECT.+[\n|\t]*FROM[\s\t\r\n])({{table}}|`?".$tableName."`?)(?![^(\)]*\))/iU", 'SELECT DISTINCT '.(!empty($alias) ? $alias : $tableName).'.'.$key.' FROM $2',$queryNumber);

        $queryNumber = $class::staticQuery('SELECT COUNT(*) FROM ('.$queryNumber.') number',$data)->fetch();

        $number = $queryNumber[0];
        $pageNumber = $number / $itemPerPage;
        if($currentPage >= $pageNumber) $currentPage = 0;
        
        $limit = ' LIMIT '.($currentPage*$itemPerPage).','.$itemPerPage;
        $query .= $limit;
        return array(
            'pages' => $pageNumber,
            'current' => $currentPage,
            'total' => $number
        );
    }

    public static function provide($parameter = 'id',$join=0){
        global $_;
        $class = get_called_class();
        return !empty($_[$parameter]) ? $class::getById($_[$parameter],$join) : new $class();
    }

    // L'alias utilisé pour la colonne du join doit avoir la syntaxe [table]_join_[field]
    // Ex : address_join_street
    public static function staticQuery($query, $data = array(), $fill = false,$joins = 0) {
        $class = get_called_class();
        $instance = new $class();
        return $instance->customQuery($query, $data, $fill,$joins);
    }

    public function customQuery($query, $data = array(), $fill = false,$joins = 0) {
        $query = str_replace('{{table}}', $this->tableName(true,$this), $query);
        $mapping = $this->fieldMapping;
        $query = preg_replace_callback('/{{([^}]*)}}/si', function($match) use ($mapping){
            return isset($mapping[$match[1]]) && isset($mapping[$match[1]]['column']) ? $mapping[$match[1]]['column'] : $match[0];
        }, $query);
      
        try{
            if(BASE_DEBUG) self::logFile($query.' :: '.json_encode($data, JSON_UNESCAPED_UNICODE),debug_backtrace());
            $results = $this->pdo->prepare($query);
            $results->execute($data);
            if (!$results) throw new Exception(json_encode($this->pdo->errorInfo()));
        }catch(Exception $e){
            
            Log::put("[SQL ERROR] - Erreur : ".$e->getMessage().' - Requete : '.$query.' - Données : '.json_encode($data, JSON_UNESCAPED_UNICODE));
            if(BASE_DEBUG) self::logFile( "Erreur : ".$e->getMessage());
            throw $e;
        }
        if (!$fill) return $results;
        $class = get_class($this);
        $objects = array();
        $results = $results->fetchAll(PDO::FETCH_ASSOC);

        foreach ($results as $queryReturn) {
            $object = new $class();
            foreach ($this->fields as $field => $type) {
                $dbField = $field;
                if(is_array($type)){

                    if(isset($type['column'])) $dbField = $type['column'];
                    $type = $type['type'];

                }
                if (isset($queryReturn[$dbField])) {
                    $object->{$field} = $queryReturn[$dbField];
                    unset($queryReturn[$dbField]);
                }
            }   
            if($joins>0) $object = self::recursiveJoiningFill($object,$queryReturn,$joins);
            foreach ($queryReturn as $key => $value) {
                if(!is_numeric($key)) $object->foreignColumns[$key] = $value;
            }  
            $objects[] = $object;
            unset($object);
        }
        return $objects == null ? array()  : $objects;
    }

    //Récuperation d'une/plusieurs colonne non référencée dans l'objet mais récuperée dans une requete static query
    public function foreign($key=null){
        if(!isset($key))  return $this->foreignColumns;
        return isset($this->foreignColumns[$key]) ? $this->foreignColumns[$key]  : '';
    }

    //Renvois une chaine de selecteur sql devant être join 
    //ex : Client::joinString('cli') --> cli.id client_join_id,cli.label client_join_label ...
    public static function joinString($prefix = ''){
        $class = get_called_class();
        $instance = new $class();
        $tableName = $class::tableName(false,$instance);
        $columns = array();
        foreach($instance->fields() as $field)
             $columns[] = $prefix.'.'.$field.' '.$tableName.'_join_'.$field.' ';
        return implode(', ',$columns);
    }

    private static function recursiveJoiningFill($object,$queryReturn,$iterations){
        if($iterations == 0) return $object;
        $iterations--;
        if(isset($object->links)){
            foreach ($object->links as $link=>$classLink) {
                $classLink = str_replace('.class.php','',basename($classLink));
                $classField = explode('.',$classLink);
                if(isset($classField[1]))
                    $classLink = $classField[0];

                $instanceLink = new $classLink();
                $tableName =  $classLink::tableName(false,$instanceLink);
                foreach ($instanceLink->fields as $field => $type) {
                    if (isset($queryReturn[$tableName.'_join_'.$field]))
                         $instanceLink->{$field} = $queryReturn[$tableName.'_join_'.$field];
                }
                $instanceLink = self::recursiveJoiningFill($instanceLink,$queryReturn,$iterations);
                $object->joins[$link] =  $instanceLink;
            }
        }
        return  $object;
    }

    /**
     * Récupere l'objet join ex : $contact->join("adress")->street; --> récupere l'attribut street de la class Adress dont l'id est spécifié dans la colonne adress de la class Contact
     * Nb : cette méthode ne fonctionne que si vous avez placé le parametre joins > 0 dans la méthode LoadALl
     * Nb : cette méthode ne fonctionne que si vous avez précisé le lien entre Contact et Adress dans la classe Contact via :
        
         protected $links = array(
        'address' => 'Address'
        );
     *

     * @category manipulation SQL
     *
     * @param <Array>  $colonnes      (WHERE)
     * @param <Array>  $valeurs       (WHERE)
     * @param <String> $operation="=" definis le type d'operateur pour la requete select
     *
     * @return Aucun retour
     */
    public function join($field){
        return isset($this->joins[$field])?$this->joins[$field]:'';
    }

    public static function logFile($msg,$backtrace=null){
        if(strpos(ROOT_URL, '127.0.0.1') === false && strpos(ROOT_URL, 'dev.local') === false && strpos(ROOT_URL, 'localhost') === false ) return;
        file_put_contents(__DIR__.SLASH.'..'.SLASH.'sql.debug.sql', date('H:i:s').' | '.$msg.PHP_EOL,FILE_APPEND);
        //A décommenter pour obtenir la stacktrace d'un appel sql dans le fichier sql.debug.sql
        //if(isset($backtrace)) file_put_contents(__DIR__.SLASH.'..'.SLASH.'sql.debug.sql', json_encode($backtrace,JSON_PRETTY_PRINT).PHP_EOL,FILE_APPEND);
        
    }

    public static function log_executed_query($string, $data) {
        $indexed=$data==array_values($data);
        foreach($data as $k=>$v) {
            if(is_string($v)) $v="'$v'";
            if($indexed) $string=preg_replace('/\?/',$v,$string,1);
            else $string=str_replace(":$k",$v,$string);
        }
        self::logFile($string);
    }
}