<?php
/*
 * Ez-DB (Class version) client for MariaDB
 * 
 * Connect to DB:
 *  $db=new DB($conf);
 *  where $conf is an associative array containig:
 *   ["db-host"] The MariaDB host.
 *   ["db-user"] Connection user.
 *   ["db-pass"] Connection password.
 *   ["db-name"] Name of database to use.
 *   ["db-charset"] Connecion charset.
 *   ["db-prefix"]  prefix on tables used by this connection.
 *  
 * See public methods for actions on DB.
 */
class DB{
    private $mysqli;
    private $prefix;

    //Evolved from ezDB

    function __construct($configuration) {
        $this->dbConnect($configuration["db-host"],$configuration["db-user"],$configuration["db-pass"],$configuration["db-name"],$configuration["db-charset"]);
        $this->prefix=$configuration["db-prefix"];
    }

    private function dbConnect($host,$user,$pass,$db,$charset=null){
        $this->mysqli = new mysqli($host, $user, $pass, $db);
        if ($this->mysqli->connect_errno) 
            die("Connection failed: (" . $this->mysqli->connect_errno . ") " . $this->mysqli->connect_error);
        if ($charset)
        $this->mysqli->set_charset($charset);
    }
    
    private function dbPrepare($sql,$types,$data){
        if ($this->mysqli == null)
            throw new Exception("No DB connection.");
        if (!($q = $this->mysqli->prepare($sql)))
            throw new Exception("Prepare: SQL Error: (" . $this->mysqli->errno . ") " . $this->mysqli->error);
        if ($types != null && !$q->bind_param($types, ...$data)) 
            throw new Exception("Bind: SQL Error: (" . $q->errno . ") " . $q->error);
        return $q;
    }
    
    private function dbExec($sql,$types = null,...$data){
        $q = $this->dbPrepare($sql,$types,$data);
        if (!$q->execute()) {
            throw new Exception("Execute: SQL Error: (" . $q->errno . ") " . $q->error);
        }
        $q->close();
    }
    
    private function dbQuery($sql,$types = null,...$data){
        $q = $this->dbPrepare($sql,$types,$data);
        if (!$q->execute()) {
            throw new Exception("Execute: SQL Error: (" . $q->errno . ") " . $q->error);
        }
        if (!($res = $q->get_result())) {
            throw new Exception("Result: SQL Error: (" . $q->errno . ") " . $q->error);
        }
        return [ "q"=>$q, "r"=>$res ];
    }
    
    private function dbFetch($res){
        return $res["r"]->fetch_assoc();
    }
    
    private function dbFree($res){
        $res["r"]->free();
        $res["q"]->close();
    }

    private function tn($table){
        return '`'.$this->prefix.$table.'`';
    }

    /**
     * Exec command.
     */
    public function exec($command,$table,$options='',$types=null,...$data){
        $sql=$command.' '.$this->tn($table).' '.$options;
        $this->dbExec($sql,$types,...$data);
    }

    public function info($command,$table,$options='',$types=null,...$data){
        $sql=$command.' '.$this->tn($table).' '.$options;
        $q=$this->dbQuery($sql,$types,...$data);
        $data=[];
        while($row = $this->dbFetch($q))
            $data[]=$row;
        $this->dbFree($q);
        return $data;
    }

    public function insert($table,$fields,$types,...$data){
        if ($spos=strpos($fields,'|')!==false){// Para que?
            $vals=substr($fields,$spos+1);
            $fields=substr($fields,0,$spos);
        }else{
            $vals='';
            if ($types){
                for($i=0;$i<strlen($types);$i++){
                    if ($i>0)
                        $vals.=',';
                    $vals.='?';
                }
            }
        }
        $sql='insert into '.$this->tn($table)." ($fields) values ($vals)";
        $this->dbExec($sql,$types,...$data);
    }

    /**
     * Select on table
     * Structure:
     *  select(<table name>,<fiels on string sepparated by comma or empty>,<sql options, like "where...">,<Type definition string (like parametrized SQL)>,...<data parameters>)
     * Usage sample:
     *  $db->select("table",,"where name=? and age>?","si",$name,$age);
     * Returns: An array of associative arrays per row.
     */
    public function select($table,$fields = null,$opt='',$types = null,...$data){
        if ($fields===null)
            $fields='*';
        $sql="select $fields from ".$this->tn($table);
        if ($opt)
            $sql.=" $opt";
        $q=$this->dbQuery($sql,$types,...$data);
        $data=[];
        while($row = $this->dbFetch($q))
            $data[]=$row;
        $this->dbFree($q);
        return $data;
    }

    /**
     * As select but only fetch one associative array with the row.
     * Structure:
     *  selectOne(<table name>,<fiels on string sepparated by comma or empty>,<sql options, like "where...">,<Type definition string (like parametrized SQL)>,...<data parameters>)
     * Usage sample:
     *  $db->selectOne("table",,"where name=? and age>?","si",$name,$age);
     */
    public function selectOne($table,$fields = null,$opt='',$types = null,...$data){
        if ($fields===null)
            $fields='*';
        $sql="select $fields from ".$this->tn($table);
        if ($opt)
            $sql.=" $opt";
        $q=$this->dbQuery($sql,$types,...$data);
        $data=$this->dbFetch($q);
        $this->dbFree($q);
        return $data;
    }

    /**
     * Update data on database.
     * Structure:
     *  update(<table name>,<string with set sql part using ? for data>,<sql options, like "where...">,<Type definition string (like parametrized SQL)>,...<data parameters>)
     * Usage sample:
     *  $db->update("table","name=?","where id=?","si",$newName,$id);
     */
    public function update($table,$set,$opt,$types = null,...$data){
        $sql='update '.$this->tn($table)." set $set $opt";
        $this->dbExec($sql,$types,...$data);
    }

    /**
     * Delete data on database.
     * Structure:
     *  delete(<table name>,<sql options, like "where...">,<Type definition string (like parametrized SQL)>,...<data parameters>)
     * Usage sample:
     *  $db->delete("table","where id=?","s",$id);
     */
    public function delete($table,$opt,$types,...$data){
        $sql='delete from '.$this->tn($table)." $opt";
        $this->dbExec($sql,$types,...$data);
    }
}

?>