<?php namespace Services;

use DB;
use File;
use PDO;

// 数据库管理
class Database {

	// 数据库备份路径
	protected $path = 'data/backup';

	// 备份的文件名
	protected $file = '';

	// 查询分隔符
	protected $delimiter = ';';

	// PDO连接实例 
	protected $pdo;

	// 所有的表
	public $tables = [];

    public function __construct()
    {
		$this->pdo = $this->connect();
    }

    public function setPath($path)
    {
        $this->path = $path;
    }

    public function getPath()
    {
        return $this->path;
    }

	// 数据库备份
	public function backup()
	{	
		$this->makeFile();
		$this->tables = $this->getTables();
		$this->lock();
		$this->dump();
		$this->unlock();
	}

	// 数据库恢复
	public function restore($filename, $path = '')
	{
        $path = $path ?: $this->path;

		$this->open(base_path().'/'.$path.'/'.$filename, 'r');
		$this->import();
		$this->close();
	}

	// 数据库内容转储到一个临时文件
	private function dump()
	{
		$sql = '-- '.date('c').' - ';

		if (!empty($this->tables)) {

		 	$tablePrefix = DB::connection()->getTablePrefix();
		 	DB::connection()->setFetchMode(PDO::FETCH_ASSOC); // 返回数组格式

		 	foreach ($this->tables as $key => $value) {
		 		$table = current($value);

		 	    if (isset($a['Table_type']) && $a['Table_type'] === 'VIEW') {
                	continue;
            	}

            	// 结构
            	$structure = $this->pdo->query('SHOW CREATE TABLE `'.$table.'`')->fetch();

            	$this->append($sql, "\n-- Table structure for table `{$table}`\n", false);
 				$this->append($sql, 'DROP TABLE IF EXISTS `'.$table.'`');
 				$this->append($sql, end($structure));

 				// 数据
 				$this->append($sql, "\n-- Dumping data for table `{$table}`\n", false);

 				$data = DB::table(substr($table, strlen($tablePrefix)))->get();

 				if (!empty($data)) {

 					$this->append($sql, "LOCK TABLES `{$table}` WRITE");

 					$insert = "INSERT INTO `{$table}` VALUES ";

 					$insertDataArray = [];

 					foreach ($data as $item) {
 						$insertDataArray[] = '('.implode(',', array_map(array($this, 'escape'), $item)).')';
 					}

 					$this->append($sql, $insert . implode(',', $insertDataArray));
 					$this->append($sql, 'UNLOCK TABLES');
 				} 				
		 	}

		 	$this->append($sql, "\n-- Completed on: ".date('c'), false);
		}

		File::put($this->fileFullPath, $sql);
	}

    protected function import()
    {
        $query = '';

        while (!feof($this->fileHandle)) {
            $line = fgets($this->fileHandle);
            $trim = trim($line);

            if ($trim === '' || strpos($trim, '--') === 0 || strpos($trim, '/*') === 0) {
                continue;
            }

            $query .= $line;

            if (substr($trim, strlen($this->delimiter) * -1) === $this->delimiter) {
                $this->pdo->exec(substr(trim($query), 0, strlen($this->delimiter) * -1));
                $query = '';
            }
        }
    }

	// 生产备份文件名称
	private function makeFile()
	{
		$this->file = DB::getDatabaseName() . '_' . date("YmdHis") . '_' . mt_rand(1000, 9999) . '.sql';

		$this->fileFullPath = base_path() . '/' . $this->path . '/' . $this->file;

		if (File::exists($this->fileFullPath)) {
			$this->makeFile();
		}
	}

	// 获得所有的表
	public function getTables()
	{
		return $this->pdo->query('SHOW FULL TABLES')->fetchAll();
	}

	private function connect()
	{
		return DB::connection()->getPdo();
	}

	// 追加字符串
    protected function append(&$sql, $string, $format = true)
    {
        if ($format) {
            $string .= $this->delimiter;
        }

        $sql .= $string . "\n";
    }	

    // 锁定所有的表
    protected function lock()
    {
        $table = [];

        if (!empty($this->tables)) {
        	foreach ($this->tables as $key => $value) {
        		$table[] = current($value);
        	}
        }

        return empty($table) || $this->pdo->exec('LOCK TABLES `'.implode('` WRITE, `', $table).'` WRITE');
    }      

    // 解锁所有表
    protected function unlock()
    {
        return DB::unprepared("UNLOCK TABLES");
    }

    protected function escape($value)
    {
        if ($value === null) {
            return 'NULL';
        }

        if (is_numeric($value)) {
            return intval($value);
        }

        return "'" . addslashes($value) . "'";
    }

    // 数据库大小
    public function getSize()
    {
    	$result = $this->connect()->query('SHOW TABLE STATUS')->fetchAll();

    	$size = 0;

    	if (!empty($result)) {
    		foreach ($result as $key => $value) {
    			$size += $value["Data_length"] + $value["Index_length"];
    		}
    	}

    	return $size;
    }

    // 优化数据表
    public function optimize()
    {
    	$tables = $this->getTables();

    	if (!empty($tables)) {
    		foreach ($tables as $key => $value) {
    			$this->connect()->query('OPTIMIZE TABLE '.current($value));
    		}
    	}
    }

    // 数据库备份文件列表
    public function getBackupFiles()
    {
    	$files = File::files(base_path().'/'.$this->path);

    	$data = [];

    	if (!empty($files)) {
    		foreach ($files as $key => $value) {
    			$data[] = [
    				'filename' => pathinfo($value, PATHINFO_BASENAME),
    				'name'     => File::name($value),
    				'size'     => human_filesize(File::size($value)),
    				'time'     => File::lastModified($value) 
    			]; 
    		}
    	}

    	return $data;
    }

    protected function open($file, $mode)
    {
        if (is_file($file) === false || ($this->fileHandle = fopen($file, $mode)) === false) {
            throw new Exception('Unable to open the target file.');
        }
    } 

    protected function close()
    {
        if (is_resource($this->fileHandle)) {
            fclose($this->fileHandle);
        }
    }     

    // 备份文件删除
    public function delete($paths)
    {
    	$paths = array_map(function($value) { return base_path().'/'.$this->path.'/'.$value;  }, $paths);
    	return File::delete($paths);
    }

    // 备份文件下载
    public function down($filename)
    {
        return Response::download($this->fullPath($filename));
    }

    public function fullPath($filename)
    {
        return base_path().'/'.$this->path.'/'.$filename;
    }
}