<?php

/**
 * Define SQL for Mysql database system
 * @author valentin carruesco
 * @category Core
 * @license MIT
 */

class Mysql
{
	const label = 'MySQL';
	const connection = 'mysql:host={{host}};dbname={{name}}';
	const description = 'Base robuste authentifiée necessitant un serveur Mysql (Conseillé)';
	const table_escape = '`';
	const column_escape = '`';

	public static function pdo_attributes(){
		return array(
			PDO::ATTR_PERSISTENT => true,
			PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION
		);
	}

	public static function operators($key=null){
		$operators =  
		array(

				'in'=> array(
						'slug'=> 'in',
						'label'=>'Contient une des valeurs',
						'sql'=>function($column,$values,&$query,&$data){
							$places = array();
							foreach ($values as $value) {
								$places[] = '?';
								$data[] = $value;
							}
						    $query .= $column.' IN ('. implode(',',$places).')';
						}),
				'not in'=> array(
						'slug'=> 'not in',
						'label'=>'Ne contient aucune des valeurs',
						'sql'=>function($column,$values,&$query,&$data){
						$places = array();
						foreach ($values as $value) {
							$places[] = '?';
							$data[] = $value;
						}
					    $query .= $column.' NOT IN ('. implode(',',$places).')';
				}),
				'null'=> array(
						'slug'=> 'null',
						'label'=>'Non renseigné',
						'sql'=>'{{column}} IS NULL'
					),
				'not null'=> array(
						'slug'=> 'not null',
						'label'=>'Renseigné',
						'sql'=>'{{column}} IS NOT NULL'
					),
				'like'=> array(
						'slug'=> 'like',
						'label'=>'Contient',
						'sql'=>function($column,$values,&$query,&$data){
				
							$data[] = '%'.$values[0].'%';
							$query .= ' '.$column.' LIKE ?' ;
						}
					),
				'not like'=> array(
						'slug'=> 'not like',
						'label'=>'Ne contient pas',
						'sql'=>function($column,$values,&$query,&$data){
				
							$data[] = '%'.$values[0].'%';
							$query .= ' '.$column.' NOT LIKE ?' ;
						}
					),
				'='=> array(
						'slug'=> '=',
						'label'=>'Egal',
						'sql'=>'{{column}} = {{value}}'
					),
				'!='=> array(
						'slug'=> '!=',
						'label'=>'Différent',
						'sql'=>'{{column}} != {{value}}'
					),
				'<'=> array(
						'slug'=> '<',
						'label'=>'Inférieur',
						'sql'=>'{{column}} < {{value}}'
					),
				'>'=> array(
						'slug'=> '>',
						'label'=>'Supérieur',
						'sql'=>'{{column}} > {{value}}'
					),
				'between'=> array(
						'slug'=> 'between',
						'label'=>'Entre',
						'values' => 2,
						'sql'=>function($column,$values,&$query,&$data){
							$query .= ' '.$column.' between ? AND ? ' ;
							$data[] = $values[0];
							$data[] = $values[1];
						}
					),
				'inline-or'=> array(
						'slug'=> 'inline-or',
						'label'=>'Contient une des valeurs',
						'sql'=>function($column,$values,&$query,&$data){
					$values = explode(',', $values[0]);
					$query .= ' ( ';
					foreach($values as $i=>$value){
						$query .= ' '.($i!=0?' OR ':'  ').$column.' LIKE ? ';
						$data[] =  '%,'.$value.',%';
					}
					$query .= ' ) ';
				}),
				'inline-and'=> array(
						'slug'=> 'inline-and',
						'label'=>'Contient toutes les valeurs',
						'sql'=>function($column,$values,&$query,&$data){
							$values = explode(',', $values[0]);
							$query .= ' ( ';
							foreach($values as $i=>$value){
								$query .= ' '.($i!=0?' AND ':'  ').$column.' LIKE ? ';
								$data[] =  '%,'.$value.',%';
							}
					$query .= ' ) ';
				})
			
		);
		if(!isset($key)) return $operators;
		return isset($operators[$key]) ?$operators[$key] : array('label'=>'Non définit','sql'=>'');
	}

	public static function fields(){
		return array(
			array('id'=>'host','label'=>'Serveur','default'=>'localhost','comment'=>''),
			array('id'=>'login','label'=>'Identifiant','default'=>'','comment'=>''),
			array('id'=>'password','label'=>'Mot de passe','default'=>'','comment'=>''),
			array('id'=>'name','label'=>'Nom de la base','default'=>'','comment'=>'')
		);
	}

	public static function processField(&$field,&$value,&$values,&$i){
		if($field['operator'] == 'BETWEEN' || $field['operator'] == 'NOT BETWEEN'){
			foreach ($value as $v2) {
                $tag = ':'.$i;
                $field['tag'][]= $tag;
                $values[$tag] = $v2;
                $i++;
            }
            $field['tag'] = implode(' AND ',$field['tag']);
		}else if($field['operator'] == 'IN' || $field['operator'] == 'NOT IN'){
            $field['tag'] = array(); 

            $valueArray = !is_array($value)?explode(',',$value) : $value;
            foreach ($valueArray as $v2) {
                $tag = ':'.$i;
                $field['tag'][]= $tag;
                $values[$tag] = $v2;
                $i++;
            }
            $field['tag'] = implode(',',$field['tag']);
            $field['operator'] = $field['operator'].'(';
            $field['postoperator'] = ')';
        }else if(is_null($value)){
        	$field['operator'] = ' IS'.($field['operator']=='!=' ? ' NOT ':' ').'NULL ';
        	$field['tag'] = '';
        }else{
            $tag = ':'.$i;
            $field['tag'] = $tag;
            $values[$tag] = $value;
            $i++;
        }
	}

	public static function types(){
		$types = array();
		$types['string']  = 'VARCHAR(225) CHARACTER SET utf8 COLLATE utf8_general_ci';
		$types['longstring'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
		$types['key'] = 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY';
		$types['object'] = $types['timestamp'] = $types['datetime'] = $types['date'] = $types['int'] = 'INT(11)';
		$types['boolean'] = 'tinyint(1) NOT NULL DEFAULT \'0\'';
		$types['blob'] = ' BLOB';
		$types['float'] = 'FLOAT';
		$types['decimal'] = 'DECIMAL(20,2)';
		$types['default'] = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
		return $types;
	}

	public static function beforeTransaction($pdo){
		$pdo->exec("set names utf8");
		$pdo->exec("SET time_zone='".TIME_ZONE."'");
	}
	
	public static function select(){
		$sql = 'SELECT {{:selected}}{{value}}{{;}},{{/;}}{{/:selected}} FROM `{{table}}` {{?joins}}{{:joins}}LEFT JOIN `{{jointable2}}` {{jointableAlias}} ON `{{jointable1}}`.{{field1}}= `{{jointableAlias}}`.{{field2}} {{/:joins}}{{/?joins}} {{?filter}} WHERE {{:filter}} `{{table}}`.`{{key}}` {{operator}} {{value}} {{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}} {{?orderby}}ORDER BY {{:orderby}}{{value}}{{;}},{{/;}}{{/:orderby}} {{/?orderby}} {{?limit}}LIMIT {{:limit}}{{value}}{{;}},{{/;}}{{/:limit}}{{/?limit}}';
		return $sql;
	}
	public static function delete(){
		$sql = 'DELETE FROM `{{table}}` {{?filter}}WHERE {{:filter}}`{{key}}`{{operator}}{{value}} {{postoperator}}  {{;}} AND {{/;}} {{/:filter}} {{/?filter}} {{?limit}}LIMIT {{:limit}}{{value}}{{;}},{{/;}}{{/:limit}}{{/?limit}}';
		return $sql;
	}
	public static function count(){
		$sql = 'SELECT COUNT({{selected}}) number FROM `{{table}}` {{?filter}}WHERE {{:filter}}`{{key}}`{{operator}}{{value}}{{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}}';
		return $sql;
	}
	public static function update(){
		$sql = 'UPDATE `{{table}}` SET {{?fields}} {{:fields}}`{{key}}`={{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}} {{?filter}} WHERE {{:filter}} `{{table}}`.`{{key}}` {{operator}} {{value}} {{postoperator}} {{;}} AND {{/;}} {{/:filter}} {{/?filter}}';
		return $sql;
	}
	public static function insert_head(){
		$sql = 'INSERT INTO  `{{table}}` ({{?fields}} {{:fields}}`{{key}}` {{;}} , {{/;}} {{/:fields}} {{/?fields}})VALUES';
		return $sql;
	}
	public static function insert_body(){
		$sql = '({{?fields}} {{:fields}}{{value}} {{;}} , {{/;}} {{/:fields}} {{/?fields}})';
		return $sql;
	}

	public static function insert(){
		$sql = self::insert_head().self::insert_body();
		return $sql;
	}
	public static function create(){
		$sql = 'CREATE TABLE IF NOT EXISTS `{{table}}` ({{?fields}} {{:fields}}`{{key}}` {{value}}{{;}} , {{/;}} {{/:fields}} {{/?fields}}) ENGINE=MyISAM DEFAULT CHARSET=utf8;';
		return $sql;
	}
	public static function drop(){
		$sql = 'DROP TABLE IF EXISTS `{{table}}`;';
		return $sql;
	}

	public static function truncate(){
		$sql = 'TRUNCATE TABLE `{{table}}`;';
		return $sql;
	}

	public static function create_index(){
		$sql = 'CREATE INDEX `{{index_name}}` ON `{{table}}` ({{column}})';
		return $sql;
	}

	public static function drop_index(){
		$sql = 'DROP INDEX `{{index_name}}` ON `{{table}}`';
		return $sql;
	}

	public static function count_index(){
		$sql = "SELECT COUNT(1) `exists` FROM INFORMATION_SCHEMA.STATISTICS
		WHERE table_schema=DATABASE() AND table_name='{{table}}' AND index_name='{{index_name}}'";
		return $sql;
	}

	public static function show_tables(){
		$sql = 'SHOW TABLES';
		return $sql;
	}

	public static function show_columns(){
	  $sql = 'SELECT COLUMN_NAME `column`,DATA_TYPE `type` FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = "{{table}}"';
	  return $sql;
	}
}

?>