<?php

  class mw_data_sql_crud extends mw_data{

    # ----------------------------------------------------------------------------------------
    #                                                                       parametres du sgbd
    #

    public function set_sgbd_param($key, $value){
      $env = $this->env();
      if(($env->bdd("sgbd") == "pdo_mysql") || ($env->bdd("sgbd") == "mysql")){
        return $this->set_sgbd_mysql_param($key, $value);
      }
      return false;
    }

    public function set_sgbd_mysql_param($key, $value){
      $sgbd = $this->sgbd();
      $sql = "SET ".$key."=\"".$value."\"";
      try{
        $sgbd->query($sql);
      }
      catch(Exception $e){
        return false;
      }
      return true;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                             create table
    #

    public function data_create_table($params){
      $env = $this->env();
      if($env->bdd("sgbd") == "pdo_sqlite"){
        return $this->data_create_sqlite_table($params);
      }
      return $this->data_create_mysql_table($params);
    }

    public function data_create_mysql_table($params){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : "";
      $fields = isset($params["fields"]) ? $params["fields"] : array();
      $keys = isset($params["keys"]) ? $params["keys"] : array();
      $options = isset($params["options"]) ? $params["options"] : array();
      if(!$table_name || !$fields) return false;
      $sql_fields = "";
      $sql_keys = "";
      $sql_options = "";
      foreach($fields as $field_name => $field){
        if(!preg_match("/^[a-z]+[a-z_0-9]*$/", $field_name)) return false;
        if(!isset($field["type"])) return false;
        $sql_field = "";
        switch($field["type"]){
          case "int":
            $value = isset($field["value"]) ? $field["value"] : "11";
            if(!preg_match("/^[0-9]+$/", $value)) return false;
            $sql_field .= "`".$field_name."` int(".$value.")";
            break;
          case "varchar":
            $value = isset($field["value"]) ? $field["value"] : "255";
            if(!preg_match("/^[0-9]+$/", $value)) return false;
            $sql_field .= "`".$field_name."` varchar(".$value.")";
            break;
          case "text":
            $sql_field .= "`".$field_name."` text";
            break;
          case "date":
            $sql_field .= "`".$field_name."` date";
            break;
          case "datetime":
            $sql_field .= "`".$field_name."` datetime";
            break;
        }
        if($sql_field){
          $null = isset($field["null"]) ? $field["null"] : false;
          $sql_field .= ($null ? " DEFAULT" : " NOT")." NULL";
          if($null && isset($field["default"])){
            $sql_field .= " DEFAULT ".$this->eq($field["default"]);
          }
          if(isset($field["autoincrement"]) && $field["autoincrement"]){
            $sql_field .= " AUTO_INCREMENT";
          }
          $sql_fields .= ($sql_fields ? ", " : "").$sql_field;
        }
      }
      $key_index = 0;
      foreach($keys as $key){
        $key_index++;
        $sql_key_fields = "";
        foreach($key["fields"] as $key_field){
          $sql_key_fields .= ($sql_key_fields ? "," : "")."`".$key_field."`";
        }
        $sql_key = "KEY `#--".$table_name."_".$key_index."`(".$sql_key_fields.")";
        if(isset($key["primary"]) && $key["primary"]){
          $sql_key = "PRIMARY ".$sql_key;
        }
        $sql_keys .= ($sql_keys ? ", " : "").$sql_key;
      }
      foreach($options as $option_name => $option_value){
        switch($option_name){
          case "default_charset":
            $sql_options .= " DEFAULT CHARSET=".$option_value;
            break;
        }
      }
      $sql = "CREATE TABLE `#--".$table_name."`(".$sql_fields.", ".$sql_keys.")".$sql_options;
      try{
        $sgbd->query($sql);
      }
      catch(Exception $e){
        return false;
      }
      return true;
    }

    public function data_create_sqlite_table($params){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : "";
      $fields = isset($params["fields"]) ? $params["fields"] : array();
      $keys = isset($params["keys"]) ? $params["keys"] : array();
      $options = isset($params["options"]) ? $params["options"] : array();
      if(!$table_name || !$fields) return false;
      $sql_fields = "";
      $sql_keys = "";
      $sql_options = "";
      foreach($fields as $field_name => $field){
        if(!preg_match("/^[a-z]+[a-z_0-9]*$/", $field_name)) return false;
        if(!isset($field["type"])) return false;
        $sql_field = "";
        switch($field["type"]){
          case "int":
            $sql_field .= "`".$field_name."` INTEGER";
            break;
          case "varchar":
          case "text":
          case "date":
          case "datetime":
            $sql_field .= "`".$field_name."` TEXT";
            break;
        }
        if($sql_field){
          $null = isset($field["null"]) ? $field["null"] : false;
          $sql_field .= ($null ? " DEFAULT" : " NOT")." NULL";
          if($null && isset($field["default"])){
            $sql_field .= " DEFAULT ".$this->eq($field["default"]);
          }
          $sql_fields .= ($sql_fields ? ", " : "").$sql_field;
        }
      }
      $key_index = 0;
      foreach($keys as $key){
        if(isset($key["primary"]) && $key["primary"]){
          $key_index++;
          $sql_key_fields = "";
          foreach($key["fields"] as $key_field){
            $sql_key_fields .= ($sql_key_fields ? "," : "")."`".$key_field."`";
          }
          $sql_key = "PRIMARY KEY (".$sql_key_fields.")";
          $sql_keys .= ($sql_keys ? ", " : "").$sql_key;
        }
      }
      $sql = "CREATE TABLE `#--".$table_name."`(".$sql_fields.", ".$sql_keys.")".$sql_options;
      try{
        $sgbd->query($sql);
      }
      catch(Exception $e){
        return false;
      }
      return true;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                              description
    #

    public function data_desc($params = array()){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : "";
      if(!$table_name) return false;
      try{
        $desc = $sgbd->desc_table("#--".$table_name);
      }
      catch(Exception $e){ $desc = false; }
      return $desc;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                                   insert
    #

    public function data_insert($params = array()){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : "";
      if(!$table_name) return false;
      $values = (isset($params["values"]) && is_array($params["values"])) ? $params["values"] : false;
      if(!$values) return false;
      $return_insert_id = isset($params["return_insert_id"]) ? $params["return_insert_id"] : false;
      $attributs_names = "";
      $attributs_values = "";
      foreach($values as $attribut_name => $attribut_value){
        $attributs_names .= ($attributs_names ? ", " : "")."`".$attribut_name."`";
        $attributs_values .= ($attributs_values ? ", " : "").$this->eq($attribut_value);
      }
      $attributs_names = $attributs_names ? "(".$attributs_names.")" : "";
      $attributs_values = $attributs_values ? " VALUES (".$attributs_values.")" : "";
      try{
        $sgbd->query("INSERT INTO `#--".$table_name."`".$attributs_names.$attributs_values);
        $res = $return_insert_id ? $sgbd->insert_id() : true;
      }
      catch(Exception $e) { $res = false; }
      return $res;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                                     read
    #

    public function data_read($params = array()){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : "";
      $index_name = isset($params["index_name"]) ? $params["index_name"] : "";
      $index_value = isset($params["index_value"]) ? $params["index_value"] : "";
      if(!$table_name || !$index_name || !$index_value) return false;
      $item = array();
      try{
        $sql = "SELECT * from `#--".$table_name."` WHERE `".$index_name."`=".$this->eq($index_value)." LIMIT 1";
        $rst = $sgbd->query($sql);
        if($v_rst = $sgbd->fetch_assoc($rst)) $item = $v_rst;
        $sgbd->free_result($rst);
      }
      catch(Exception $e) { $item = false; }
      return $item;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                                   update
    #

    public function data_update($params = array()){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : false;
      $values = (isset($params["values"]) && is_array($params["values"])) ? $params["values"] : false;
      $index_name = isset($params["index_name"]) ? $params["index_name"] : false;
      $index_value = isset($params["index_value"]) && (strlen($params["index_value"]) > 0) ? $params["index_value"] : false;
      if(!$table_name || !$index_name || !$index_value || !$values) return false;
      try{
        $sql = "UPDATE `#--".$table_name."`";
        $set = "";
        foreach($values as $attribut_name => $attribut_value){
          $set .= ($set ? ", " : "")."`".$attribut_name."`=".$this->eq($attribut_value);
        }
        $set = $set ? " SET ".$set : "";
        $where = " WHERE `".$index_name."`=".$this->eq($index_value);
        $sgbd->query($sql.$set.$where);
      }
      catch(Exception $e) { return false; }
      return true;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                                   delete
    #

    public function data_delete($params = array()){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : false;
      $index_name = isset($params["index_name"]) ? $params["index_name"] : false;
      $index_value = isset($params["index_value"]) ? $params["index_value"] : false;
      if(!$table_name || !$index_name || !$index_value) return false;
      try{
        $sql = "DELETE FROM `#--".$table_name."` WHERE `".$index_name."`=".$this->eq($index_value);
        $sgbd->query($sql);
      }
      catch(Exception $e) { debug($e->getMessage()); return false; }
      return true;
    }

    # ----------------------------------------------------------------------------------------
    #                                                                                     list
    #

    public function data_list($params = array()){
      $sgbd = $this->sgbd();
      $table_name = isset($params["table_name"]) ? $params["table_name"] : "";
      if(!$table_name) return false;
      $index_name = isset($params["index_name"]) ? $params["index_name"] : "";
      $select = "SELECT `#--".$table_name."`.*";
      $from = " FROM `#--".$table_name."`";
      $where = isset($params["where"]) ? " WHERE ".$params["where"] : "";
      if(isset($params["filters"])){
        foreach($params["filters"] as $filter){
          $and_where = "";
          if(isset($filter[0]) && isset($filter[1])){
            switch(strtolower($filter[1])){
              case "eq":
                if(isset($filter[2])){
                  $and_where .= ($where ? " AND " : " WHERE ").$filter[0]."=".$this->eq($filter[2]);
                  break;
                }
              case "lt":
                if(isset($filter[2])){
                  $and_where .= ($where ? " AND " : " WHERE ").$filter[0]."<".$this->eq($filter[2]);
                  break;
                }
              case "lte":
                if(isset($filter[2])){
                  $and_where .= ($where ? " AND " : " WHERE ").$filter[0].">=".$this->eq($filter[2]);
                  break;
                }
              case "gt":
                if(isset($filter[2])){
                  $and_where .= ($where ? " AND " : " WHERE ").$filter[0].">".$this->eq($filter[2]);
                  break;
                }
              case "gte":
                if(isset($filter[2])){
                  $and_where .= ($where ? " AND " : " WHERE ").$filter[0].">=".$this->eq($filter[2]);
                  break;
                }
            }
          }
          $where .= $and_where;
        }
      }
      $order = isset($params["order_by"]) ?
        " ORDER BY ".$params["order_by"].(isset($params["order"]) ? " ".$params["order"] : "")
      : "";
      $limit = isset($params["limit"]) ?
        " LIMIT ".$params["limit"].(isset($params["offset"]) ? " OFFSET ".$params["offset"] : "")
      : "";
      $list = array("list" => array(), "total" => 0);
      try{
        $sql = "SELECT count(*) as n FROM(".$select.$from.$where.") res";
        $rst = $sgbd->query($sql);
        if($v_rst = $sgbd->fetch_assoc($rst)) $list["total"] = $v_rst["n"];
        $sgbd->free_result($rst);
        if($list["total"] > 0){
          $sql = "SELECT * FROM(".$select.$from.$where.$order.$limit.") res";
          $rst = $sgbd->query($sql);
          while($v_rst = $sgbd->fetch_assoc($rst)){
            if($index_name) $list["list"][$v_rst[$index_name]] = $v_rst;
            else $list["list"][] = $v_rst;
          }
          $sgbd->free_result($rst);
        }
      }
      catch(Exception $e) { $list = false; }
      return $list;
    }

  }