<?php
/**
 * 系统升级处理，所有数据库更新，全部放在本文件里。
 * 
 * ============================================================================
 * 版权所有 2017北京素玄科技，并保留所有权利。
 * 
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 * $Author: songdemei<songdemei@suxuantech.cn> 2017-10-17 $
 * 
 */
namespace app\admin\lib;
class Update {
    protected static $instance;
    var $sqlUpdate = [];
    
    function __construct() {
        $this->sqlUpdate = require_once('config.php');
    }
    
   function runUpdate(){
        $dbVersion = $this->getDbVersion();
        $thisVersion = get_version('APP');
        if(version_compare($thisVersion, $dbVersion, '<=')){
            return true;
        }
        $sqlActive = null;
        foreach ($this->sqlUpdate as $k=>$sqlConfig){
            if(version_compare($k, $dbVersion, '>')){
                $sql = $this->getSql($sqlConfig);
                if(!empty($sql)){
                    $sqlActive = $sqlActive?array_merge($sqlActive, $sql):$sql;
                }
            }
        }
        //if(count($sqlActive) > 0){
        $upSql = implode("\r\n", $sqlActive);
        $sqlV = db('app_version')->fetchSql()->strict(false)->insert(['version'=>$thisVersion,'up_time'=>date('Y-m-d H:i:s'),'up_sql'=>$upSql]).';';
        $sqlActive[] = $sqlV;

//*
        \think\Db::startTrans();
        try{
            foreach($sqlActive as $sqlinfo){
                \think\Db::execute($sqlinfo);
            }
            cache('db_version_'.  config('APP_ID'),null);
            \think\Db::commit();
        
        }catch(\Exception $e){
            \think\Db::rollback();
            echo "===========数据库升级失败============<br/>\r\n";
            echo $e->getMessage();
            dump($e->getTraceAsString());
            echo "===========本次升级SQL：============<br/>\r\n";
            dump($sqlActive);
            exit();
        }
        return true;
    }
    
    function getSql($config){
        $sql = [];
        foreach($config as $k=>$info){
            $thisSql = '';
            switch ($info['type']){
                case 'table':
                    if($this->isTableExist($info['name']) === true){
                        continue;
                    }
                    $thisSql .= 'CREATE TABLE `'.trim($info['name']).'`(';
                    $thisSql .= $this->createColumn($info['fields']);
                    $thisSql .= ') COLLATE=\'utf8_unicode_ci\' ';
                    $thisSql .= ' ENGINE=InnoDB ';
                    break;
                case 'column':
                    $column = $this->addColumn($info['fields'], $info['name']);
                    if($column){
                        $thisSql .= ' ALTER TABLE `'.trim($info['name']).'` '.$column;
                    }
                    break;
                case 'data':
                    $column = $this->addData($info['fields'], $info['name']);
                    if($column){
                        $thisSql .= ' INSERT INTO `'.trim($info['name']).'` '.$column;
                    }
                    break;
                case 'sql':
                    $thisSql = $info['sql'];
                    break;
            }
            if($thisSql){
                $sql[] = $thisSql;
            }
            
            
        }
        return $sql;
    }
    /**
     * 添加字段时，组成字段语句。
     * ﻿ALTER TABLE `app_version`
     * ADD COLUMN `test` varchar(50) NULL ,
     * ADD COLUMN `列 5` VARCHAR(50) NULL,
     * ADD INDEX `Index 2` (`version`, `id`);
     * @param type $columnList
     * @param type $table
     * @return string
     */
    function addColumn($columnList,$table){
        $ret = '';
        // static $allColumn;
        // if(!$allColumn){
        
        // }
        $allColumn = $this->getColumns($table);
        if($allColumn === false){
            return false;
        }
        
        foreach ( $columnList as $value){
            if($value['type'] == 'key'){
                $keys[] = ' ADD INDEX `'.$value['name'].'` (`'.implode('`,`', $value['fields']).'`) ';
                continue;
            }
            if(in_array($value['name'], $allColumn)){
                //已经有这个字段了。
                continue;
            }
            $ret .= ' ADD COLUMN `'.$value['name'].'` '.$value['type'];
            $ret .= isset($value['length'])?'('.$value['length'].') ':' ';
            $ret .= $value['is_null']?' NULL ':' NOT NULL ';
            
            switch (true){
                case strtoupper(substr($value['default'],0,14))=='AUTO_INCREMENT':
                    $ret .=' '.$value['default'].' ';
                    break;
                /*
                case strtoupper(substr($value['default'],0,17))=='CURRENT_TIMESTAMP' && in_array(strtoupper($value['type']),['date','datetime'] ):
                    $ret .=' DEFAULT '.$value['default'];
                    break;
                case is_string($value['default']):
                    $ret .= ' DEFAULT '.$value['default'];
                    break;
                 * 
                 */
                case isset($value['default']):
                    $ret .= is_numeric($value['default'])?' DEFAULT '.$value['default'].' ':' DEFAULT \''.$value['default'].'\' ';
                    break;
                
            }
            if(in_array(strtolower($value['type']),['varchar','char','text','tinytext','mediumtext','longtext','json']) ){
                $ret .= ' COLLATE \'utf8_unicode_ci\' ';
            }
            $ret .= ',';
        }
        $ret .= !empty($keys)?implode(',', $keys):'';
        $ret = trim($ret,',');
        
        return $ret;
    }
    /**
     * 创建表时组成字段语句
     * @param type $config
     * @return type
     */
    function createColumn($config){
        $ret = '';
        $primary = '';
        $keys = '';
        foreach ($config as $value){
            if($value['type'] == 'key'){
                $keys[] = ' INDEX `'.$value['name'].'` (`'.implode('`,`', $value['fields']).'`) ';
                continue;
            }
            $ret .= '`'.$value['name'].'` '.$value['type'];
            $ret .= isset($value['length'])?'('.$value['length'].') ':' ';
            $ret .= $value['is_null']?' NULL ':' NOT NULL ';
            switch (true){
                case strtoupper(substr($value['default'],0,14))=='AUTO_INCREMENT':
                    $ret .=' '.$value['default'].' ';
                    $primary = $value['name'];
                    break;
                /*
                case strtoupper(substr($value['default'],0,17))=='CURRENT_TIMESTAMP' && in_array(strtoupper($value['type']),['date','datetime'] ):
                    $ret .=' DEFAULT '.$value['default'];
                    break;
                case is_string($value['default']):
                    $ret .= ' DEFAULT '.$value['default'];
                    break;
                 * 
                 */
                case isset($value['default']):
                    $ret .= is_numeric($value['default'])?' DEFAULT '.$value['default'].' ':' DEFAULT \''.$value['default'].'\' ';
                    break;
                
            }
            if(in_array(strtolower($value['type']),['varchar','char','text','tinytext','mediumtext','longtext','json']) ){
                $ret .= ' COLLATE \'utf8_unicode_ci\' ';
            }
            if($value['primary']){
                $primary = $value['name'];
            }
            
            $ret .= ',';
            
        }
        
        if($primary){
            $ret .= 'PRIMARY KEY (`'.$primary.'`),';
        }
        $ret .= !empty($keys)?implode(',', $keys):'';
        $ret = trim($ret,',');
        return $ret;
    }

    /**
     * 插入数据时，组成字段语句。
     * INSERT INTO USER(name, sex, age)
    SELECT '小红','1','1' UNION
    SELECT '王明','2','14' UNION
    SELECT '赵强','1','64' UNION
    SELECT '孙俪','2,'34'；
     * @param type $dataList
     * @param type $table
     * @return string
     */
    function addData($dataList,$table){
        $ret = '';
        // static $allColumn;
        // if(!$allColumn){
            
        // }
        $allColumn = $this->getColumns($table);
        if($allColumn === false){
            return false;
        }
        $ret .=' ('.implode(',',$dataList['field']).') ';
        foreach($dataList['value'] as $k => $v){
            $dataList['value'][$k]=$arr = array_map(create_function('$item', 'return "\'$item\'";'), $v);
            $ret .=" SELECT ".implode(',',$dataList['value'][$k])." union ";
        }
        $sql=substr($ret,0,strripos($ret,"union"));

        return $sql;
    }


    function getColumns($table){
        if($this->isTableExist($table) === false){
            return false;
        }
        $sql = 'desc '.$table;
        $res = db()->master()->query($sql);
        $column = [];
        foreach($res as $value){
            $column[] = $value['Field'];
        }
        return $column;
    }
    
    function getDbVersion(){
        static $version;
        $version = $version?$version:cache('db_version_'.  config('APP_ID'));
        if(!$version){
            if($this->isTableExist('app_version')){
                $version = db('app_version')->order('id', 'desc')->find();
                $version = $version['version']?$version['version']:'0.0.0';
                cache('db_version_'.  config('APP_ID'),$version);
            }else{
                $sql = <<<EOF
CREATE TABLE `app_version` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `version` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `up_time` DATETIME NULL DEFAULT NULL,
         `up_sql` text NULL ,
    PRIMARY KEY (`id`)
)
COMMENT='应用版本管理'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
EOF;
                $res = db()->execute($sql);
                if($res === false){
                    return false;
                }
                $version = '0.0.0';
            }
            
            
        }
        return $version;
    }
    
    function isTableExist($table){
        $sql = "show tables;";
        $res = db()->master()->query($sql);
        foreach($res as $value){
            
            if(array_search($table, $value)){
                return true;
            }
        }
        return false;
    }

}