<?php

// +----------------------------------------------------------------------
// | 星数 [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2023~2024 http://xsframe.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: guiHai <786824455@qq.com>
// +----------------------------------------------------------------------

namespace app\admin\controller;

use think\facade\Cache;
use think\facade\Db;
use xsframe\base\AdminBaseController;
use xsframe\util\FileUtil;
use xsframe\util\RandomUtil;
use xsframe\util\StringUtil;

class Ops extends AdminBaseController
{
    /**
     * 备份配置
     * @var array
     */
    private $config = [
        'path'     => '',         // 备份地址
        'part'     => 1024 * 1024,  // 每卷大小
        'compress' => 0,          // 是否压缩
        'level'    => 9,          // 压缩级别
    ];

    /**
     * 数据库链接
     */
    public static function connect()
    {
        return Db::connect();
    }


    public function _admin_initialize()
    {
        parent::_admin_initialize(); // TODO: Change the autogenerated stub
        $this->config['path'] = IA_ROOT . '/backup' . DIRECTORY_SEPARATOR . 'database' . DIRECTORY_SEPARATOR;
        $this->config['compress'] = 1;
        $this->config['level'] = 5;
    }

    // 系统概况
    public function overview(): \think\response\View
    {
        // if ($this->request->isPost()) {
        //
        // }

        $result = [
        ];
        return $this->template('overview', $result);
    }

    // 性能优化
    public function optimize(): \think\response\View
    {
        if ($this->request->isPost()) {

        }

        $database = config('database');

        $result = [
            'database'         => $database,
            'redis_support'    => extension_loaded('redis'),
            'memcache_support' => extension_loaded('memcache'),
            'opcache_support'  => function_exists('opcache_get_configuration'),
        ];
        return $this->template('optimize', $result);
    }

    // 操作日志
    public function oplog(): \think\response\View
    {
        // if ($this->request->isPost()) {
        //
        // }

        $result = [
        ];
        return $this->template('oplog', $result);
    }

    // 数据库优化
    public function database(): \think\response\View
    {
        $table = trim($this->params['table'] ?? '');
        $type = intval($this->params['type'] ?? 1);

        // if ($this->request->isPost()) {
        //
        // }

        if (empty($table)) {
            $list = Db::query("SHOW TABLE STATUS");
        } else {
            if ($type) {
                $list = Db::query("SHOW FULL COLUMNS FROM {$table}");
            } else {
                $list = Db::query("SHOW COLUMNS FROM {$table}");
            }
        }

        $total = $total_size = 0;
        foreach ($list as $k => $v) {
            $list[$k]['Data_length'] = round($v['Data_length'] / 1024 / 1024, 3);  //数据大小
            $list[$k]['Index_length'] = round($v['Index_length'] / 1024 / 1024, 3); //索引大小
            $list[$k]['Data_free'] = round($v['Data_free'] / 1024 / 1024, 3);    //碎片大小
            $list[$k]['Data_total'] = round($list[$k]['Data_length'] + $list[$k]['Index_length'], 3); //合计
            $total_size += $list[$k]['Data_total'];
            $total++;
        }

        $result = [
            'total'     => $total,
            'list'      => $list,
            'totalSize' => round($total_size, 3),
        ];
        return $this->template('database', $result);
    }

    // 数据表字典
    public function dictionary(): \think\response\View
    {
        $table = trim($this->params['table'] ?? '');
        $table = StringUtil::removePrefixIfExists($table, config('database.connections.mysql.prefix'));
        $fields = $this->getFiledList($table);

        $result = [
            'table'  => $table,
            'fields' => $fields,
        ];
        return $this->template('ops/tpl/dictionary', $result);
    }

    // 备份数据表
    public function optimizeTable()
    {
        $tables = $this->params['ids'] ?? '';
        if (!$tables) return $this->errorMsg('请选择需要优化的数据表');

        $table = $tables;
        if (is_array($tables)) {
            $table = implode('`,`', $tables);
        }

        Db::query("OPTIMIZE TABLE `{$table}`");
        return $this->successMsg('优化成功');
    }

    // 备份数据表
    public function doBack()
    {
        $getTables = $this->params['ids'] ?? '';
        if (!$getTables) return $this->errorMsg('请选择需要备份的数据表');

        $tables = [];
        $sizes = [];

        if ($getTables) {
            $totalSize = 0;
            foreach ($getTables as $k => $v) {
                $tables_size = explode(',', $v);
                $tables[$k] = $tables_size[0];
                $sizes[$k] = $tables_size[1];
                $totalSize += $tables_size[1];
            }

            $rs = Db::query("SELECT VERSION() as v");
            $back['version'] = $rs[0]['v']; //获取数据库版本号
            $back['tables'] = $tables;     //数据表集
            $back['sizes'] = $sizes;      //数据表大小集合
            $back['totalTables'] = count($tables);  //数据表总数
            $back['tablesStartKey'] = 0;  //初始数据表集下标
            $back['recordStartNum'] = 0;  //初始数据表记录开始行数
            $back['fileNum'] = 1;  //初始分卷编号
            $back['totalFileNum'] = ceil($totalSize * 1024 * 1024 / $this->config['part']); //总分卷数
            $back['backFolderName'] = date('Y.m.d-H.i.s', time()) . '-' . strtolower(RandomUtil::random(6)); //备份的文件夹名
            session('db_back_info', $back);
        } else {
            $back = session('db_back_info');
        }

        //备份处理
        $sql = '';
        $this->config['part'] = $this->config['compress'] ? $this->config['part'] * 2 : $this->config['part'];
        for ($i = $back['tablesStartKey']; $i < $back['totalTables'] && strlen($sql) < $this->config['part']; $i++) {
            $sql .= $this->dumpSql($back['tables'][$i], $back['recordStartNum'], strlen($sql));
            $back['recordStartNum'] = 0;
        }

        if (trim($sql)) {
            $str = "-- -----------------------------\n";
            $str .= "-- Veitool MySQL Data Transfer \n";
            $str .= "-- \n";
            $str .= "-- Host     : " . config('database.connections.mysql.hostname') . "\n";
            $str .= "-- Port     : " . config('database.connections.mysql.hostport') . "\n";
            $str .= "-- Database : " . config('database.connections.mysql.database') . "\n";
            $str .= "-- \n";
            $str .= "-- Part : #" . $back['fileNum'] . "\n";
            $str .= "-- Date : " . date("Y-m-d H:i:s") . "\n";
            $str .= "-- -----------------------------\n\n";
            $str .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
            $sql = $str . $sql;
            //写入文件
            $fildir = $this->config['path'] . $back['backFolderName'] . '/';
            if (!$this->checkPath($fildir)) return ['code' => 1, 'p' => 100, 'filenum' => 0, 'msg' => '备份目录没有写入权限'];
            $filename = $fildir . $back['fileNum'] . '.sql';
            if ($this->config['compress']) {
                $filename = "{$filename}.gz";
                $fp = @gzopen($filename, "a{$this->config['level']}");
                @gzwrite($fp, $sql);
                @gzclose($fp);
            } else {
                $fp = @fopen($filename, 'a');
                @fwrite($fp, $sql);
                @fclose($fp);
            }
            $p = ($back['totalFileNum'] > 0 && $back['fileNum'] < $back['totalFileNum']) ? $this->dround($back['fileNum'] * 100 / $back['totalFileNum'], 0, true) : 100;
            $p = $p >= 100 ? 100 : ($p < 1 ? 1 : $p);
            session('db_back_info.tablesStartKey', $i - 1);        //记录“数据表集”的键值，便于下次请求时定位数据表
            session('db_back_info.fileNum', $back['fileNum'] + 1); //记录递增的分卷编号
        } else {
            $this->dumpEnd($back);
            session('db_back_info', null);
        }

        return $this->successMsg('备份数据成功');
    }

    // 数据备份列表
    public function imports()
    {
        $list = $this->getBackFile();
        if ($list) $list = array_reverse($list);

        $result = [
            'list' => $list,
        ];
        return $this->template('ops/tpl/imports', $result);
    }

    // 下载sql文件
    public function download()
    {
        $filename = trim($this->params['filename'] ?? '');
        return $this->downFile($filename, 1);
    }

    // 删除备份文件
    public function del()
    {
        $filenames = [];
        $filename = trim($this->params['filename'] ?? '');
        if (empty($filename)) return $this->errorMsg('请选择备份系列');

        $filenames[] = $filename;

        $rs = $this->delBackFile($filenames);
        if (!$rs) {
            return $this->errorMsg('删除备份失败:' . $rs);
        }
        return $this->successMsg('删除备份成功');
    }

    // 导入数据表
    public function doImport()
    {
        $filename = $this->params['filename'] ?? '';

        if ($filename) {
            $path = realpath($this->config['path'] . $filename) . "\\";
            if (is_dir($path)) {
                $ext = $this->config['compress'] ? '.sql.gz' : '.sql';
                $rs = glob($path . '*' . $ext);
                // if (!$rs) return ['code' => 5, 'p' => 0, 'filenum' => 0, 'msg' => '备份源不存在'];
                if (!$rs) $this->errorMsg('备份源不存在');
                $back['files'] = $rs;        //文件集
                $back['total'] = count($rs); //分卷总数
                $back['fileNum'] = 1;          //初始分卷编号
            } else {
                // return ['code' => 2, 'p' => 0, 'filenum' => 0, 'msg' => '参数错误'];
                $this->errorMsg('参数错误');
            }
            session('db_back_imp', $back);
        } else {
            $back = session('db_back_imp');
        }
        //导入数据
        $fid = $back['fileNum'] - 1;
        if (isset($back['files'][$fid])) {
            $sql = '';
            $flag = false;
            $sqlFile = $back['files'][$fid];
            $db = self::connect();
            if ($this->config['compress']) {
                $gz = gzopen($sqlFile, 'r');
                while (!gzeof($gz)) {
                    $sql .= gzgets($gz);

                    $tmp = trim($sql);
                    if ($flag || preg_match('/DELIMITER;;$/', $tmp)) {
                        if (preg_match('/;;DELIMITER;$/', $tmp)) {
                            $flag = false;
                            $sql = str_replace(['DELIMITER;;', 'DELIMITER;', ';;'], ['', '', ''], $sql);
                            $db->execute("set global log_bin_trust_function_creators=1;");
                            if ($db->execute($sql) === false) {
                                // return ['code' => 3, 'p' => 0, 'filenum' => $back['fileNum'], 'msg' => '卷：' . $back['fileNum'] . '导入失败'];
                                $this->errorMsg('卷：' . $back['fileNum'] . '导入失败');
                            }
                            $sql = '';
                        } else {
                            $flag = true;
                        }
                    } else if (preg_match('/.*;$/', $tmp)) {
                        if ($db->execute($sql) === false) {
                            // return ['code' => 3, 'p' => 0, 'filenum' => $back['fileNum'], 'msg' => '卷：' . $back['fileNum'] . '导入失败'];
                            $this->errorMsg('卷：' . $back['fileNum'] . '导入失败');
                        }
                        $sql = '';
                    }
                }
                gzclose($gz);
            } else {
                $gz = fopen($sqlFile, 'r');
                while (!feof($gz)) {
                    $sql .= fgets($gz);
                    $tmp = trim($sql);
                    if ($flag || preg_match('/DELIMITER;;$/', $tmp)) {
                        if (preg_match('/;;DELIMITER;$/', $tmp)) {
                            $flag = false;
                            $sql = str_replace(['DELIMITER;;', 'DELIMITER;', ';;'], ['', '', ''], $sql);
                            $db->execute("set global log_bin_trust_function_creators=1;");
                            if ($db->execute($sql) === false) {
                                // return ['code' => 3, 'p' => 0, 'filenum' => $back['fileNum'], 'msg' => '卷：' . $back['fileNum'] . '导入失败'];
                                $this->errorMsg('卷：' . $back['fileNum'] . '导入失败');
                            }
                            $sql = '';
                        } else {
                            $flag = true;
                        }
                    } else if (preg_match('/.*;$/', $tmp)) {
                        if ($db->execute($sql) === false) {
                            // return ['code' => 3, 'p' => 0, 'filenum' => $back['fileNum'], 'msg' => '卷：' . $back['fileNum'] . '导入失败'];
                            $this->errorMsg('卷：' . $back['fileNum'] . '导入失败');
                        }
                        $sql = '';
                    }
                }
                fclose($gz);
            }

            session('db_back_imp.fileNum', $back['fileNum'] + 1);
            $p = $back['fileNum'] < $back['total'] ? $this->dround($back['fileNum'] * 100 / $back['total'], 0, true) : 100;
            $p = $p >= 100 ? 100 : ($p < 1 ? 1 : $p);
            // return ['code' => 0, 'p' => $p, 'filenum' => $back['fileNum']];
        } else {
            session('db_back_imp', null);
            // return ['code'=>1,'p'=>100,'filenum'=>$back['total'],'msg'=>'恢复数据成功'];
        }

        return $this->successMsg('恢复数据成功');
    }

    // 修复数据表
    public function repairTable()
    {
        $tables = $this->params['ids'] ?? '';
        if (!$tables) return $this->error('请选择需要修复的数据表');

        $table = $tables;
        if (is_array($tables)) {
            $table = implode('`,`', $tables);
        }

        Db::query("REPAIR TABLE `{$table}`");
        return $this->successMsg('修复成功');
    }

    // 更新缓存
    public function cache(): \think\response\View
    {
        if ($this->request->isPost()) {
            $this->success("更新缓存成功！");
        }

        $result = [
        ];
        return $this->template('cache', $result);
    }

    // 检测bom
    public function bom()
    {
        $bomTree = Cache::get('bomTree');

        if ($this->request->isPost()) {
            $path = $this->iaRoot;
            $trees = FileUtil::fileTree($path);
            $bomTree = [];
            foreach ($trees as $tree) {
                $tree = str_replace($path, '', $tree);
                $tree = str_replace('\\', '/', $tree);
                if (strexists($tree, '.php')) {
                    $fname = $path . $tree;
                    $fp = fopen($fname, 'r');
                    if (!empty($fp)) {
                        $bom = fread($fp, 3);
                        fclose($fp);
                        if ($bom == "\xEF\xBB\xBF") {
                            $bomTree[] = $tree;
                        }
                    }
                }
            }
            Cache::set('bomTree', $bomTree);
            show_json(1, ['url' => url('ops/bom')]);
        }

        $result = [
            'bomTree' => $bomTree
        ];
        return $this->template('bom', $result);
    }


    /**
     * 获取数据表数据
     * @param string $table 表名
     * @param int $start 数据记录的开始行数
     * @param int $sizes 累计长度
     * @return  string
     */
    public function dumpSql(string $table, int $start = 0, int $sizes = 0)
    {
        $sql = '';
        if ($start == 0) {
            $rs = Db::query("SHOW CREATE TABLE `$table`");
            $rs = array_map('array_change_key_case', $rs);
            if (isset($rs[0]['create view'])) {
                $sql = "DROP VIEW IF EXISTS `$table`;\n" . trim($rs[0]['create view']) . ";\n\n";
                $back = session('db_back_info');
                $fildir = $this->config['path'] . $back['backFolderName'] . '/';
                if ($this->checkPath($fildir)) {
                    $filename = $fildir . 'z.sql';
                    if (is_file($filename)) {
                        $old = file_get_contents($filename);
                        $fp = @fopen($filename, 'w');
                        @fwrite($fp, $old . $sql);
                        @fclose($fp);
                    } else {
                        $fp = @fopen($filename, 'a');
                        @fwrite($fp, $sql);
                        @fclose($fp);
                    }
                }
                return '';
            } else {
                $sql = "DROP TABLE IF EXISTS `$table`;\n" . trim($rs[0]['create table']) . ";\n\n";
            }
        }
        $rows = $offset = 100;
        while (($sizes + strlen($sql)) < $this->config['part'] && $rows == $offset) {
            $rows = 0;
            /*备份数据记录*/
            $result = Db::query("SELECT * FROM `$table` LIMIT $start, $offset");
            foreach ($result as $row) {
                $str = '';
                foreach ($row as $v) {
                    $str .= (is_null($v) ? 'null,' : "'" . addslashes($v) . "',");
                }
                $sql .= "INSERT INTO `{$table}` VALUES(" . str_replace(["\r", "\n"], ['\\r', '\\n'], rtrim($str, ',')) . ");\n";
                $rows++;
            }/**/
            $start += $offset;
        }
        session('db_back_info.recordStartNum', $start);
        $sql .= "\n";
        return $sql;
    }

    /**
     * 其他信息备份 存储过程/函数/触发器
     * @param array $back 信息集
     * @return  mixd
     */
    public function dumpEnd(array $back)
    {
        $txt = '';
        $dbname = config('database.connections.mysql.database');

        /*版本判断*/
        if (version_compare($back['version'], '5.7.0', '<')) {
            $key = 'name';
            $sql = "select name from mysql.proc where db = '" . $dbname . "' and `type` =";
        } else {
            $key = 'SPECIFIC_NAME';
            $sql = "select * from information_schema.parameters where SPECIFIC_SCHEMA = '" . $dbname . "' and `ROUTINE_TYPE` =";
        }/**/

        /*备份存储过程*/
        $result = Db::query($sql . " 'PROCEDURE' ");
        for ($i = 0; $i < count($result); $i++) {
            $Pname = $result[$i][$key];
            $rs = Db::query("show create procedure {$Pname}");
            $rs = array_map('array_change_key_case', $rs);
            $Pnamez = $rs[0]['create procedure'];
            $txt .= "\r\nDROP PROCEDURE IF EXISTS `{$Pname}`;\r\nDELIMITER;;\r\n{$Pnamez}\r\n;;DELIMITER;\r\n";
        }/**/

        /*备份函数*/
        $result = Db::query($sql . " 'FUNCTION' ");
        for ($i = 0; $i < count($result); $i++) {
            $Pname = $result[$i][$key];
            $rs = Db::query("show create function {$Pname}");
            $rs = array_map('array_change_key_case', $rs);
            $Pnamez = $rs[0]['create function'];
            $txt .= "\r\nDROP FUNCTION IF EXISTS `{$Pname}`;\r\nDELIMITER;;\r\n{$Pnamez}\r\n;;DELIMITER;\r\n";
        }/**/

        /*备份触发器*/
        $sql = "SELECT * FROM information_schema.TRIGGERS where trigger_schema = '" . $dbname . "'";
        $rs = Db::query($sql);
        $rs = array_map('array_change_key_case', $rs);
        for ($i = 0; $i < count($rs); $i++) {
            $trigger_name = $rs[$i]['trigger_name'];
            $action_timing = $rs[$i]['action_timing'];
            $event_manipulation = $rs[$i]['event_manipulation'];
            $event_object_table = $rs[$i]['event_object_table'];
            $action_statement = $rs[$i]['action_statement'];
            $m = "CREATE TRIGGER `{$trigger_name}` {$action_timing} {$event_manipulation} ON `{$event_object_table}` FOR EACH ROW {$action_statement}";
            $txt .= "\r\nDROP TRIGGER IF EXISTS `{$trigger_name}`;\r\nDELIMITER;;\r\n{$m}\r\n;;DELIMITER;\r\n";
        }/**/

        /*写入文件*/
        $fildir = $this->config['path'] . $back['backFolderName'] . '/';
        if ($this->checkPath($fildir)) {
            $filename = $fildir . 'z.sql';
            $newfname = $fildir . $back['fileNum'] . '.sql';
            if (is_file($filename)) {
                $txt = file_get_contents($filename) . $txt;
                @unlink($filename);
            } else if (!$txt) {
                return false;
            }
            if ($this->config['compress']) {
                $newfname = "{$newfname}.gz";
                $fp = @gzopen($newfname, "a{$this->config['level']}");
                @gzwrite($fp, $txt);
                @gzclose($fp);
            } else {
                $fp = @fopen($newfname, 'a');
                @fwrite($fp, $txt);
                @fclose($fp);
            }
        }/**/
    }

    /**
     * 检查目录是否可写
     * @param string $path 文件夹路径
     * @return  bool
     */
    protected function checkPath(string $path = null)
    {
        if (is_dir($path)) {
            return true;
        }
        if (mkdir($path, 0755, true)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 数字格式转换
     * @param float $v 数值
     * @param int $p 小数点后位数
     * @param bool $s 是否格式化为字符串
     * @return   float/string
     */
    private function dround($v, $p = 2, $s = false)
    {
        $v = round(floatval($v), $p);
        if ($s) $v = sprintf('%.' . $p . 'f', $v);
        return $v;
    }

    /**
     * 数据库备份文件列表
     * @return array
     */
    private function getBackFile()
    {
        $dbAks = [];
        if (realpath($this->config['path'])) {
            $dbAk = [];
            $path = realpath($this->config['path']) . DIRECTORY_SEPARATOR;
            $sqlFiles = glob($path . '*');
            if (is_array($sqlFiles)) {
                foreach ($sqlFiles as $id => $sqlfile) {
                    $tmp = basename($sqlfile);
                    if (is_dir($sqlfile)) {
                        $size = $number = 0;
                        $fSql = glob($path . $tmp . '/*.sql*');
                        foreach ($fSql as $f) {
                            $size += filesize($f);
                            $number++;
                        }
                        $dbAk['filename'] = $tmp;
                        $dbAk['number'] = $number;
                        $dbAk['mtime'] = filectime($sqlfile);
                        $dbAk['filesize'] = round($size / (1024 * 1024), 2);
                        $dbAks[] = $dbAk;
                    }
                }
            }
        }
        return $dbAks;
    }

    /**
     * 下载备份文件
     * @param string $folder 备份文件夹
     * @param int $pid 分卷编号
     * @return   file
     */
    private function downFile(string $folder, int $pid = 0)
    {
        $ext = $this->config['compress'] ? '.sql.gz' : '.sql';
        $file = $this->config['path'] . $folder . '/' . $pid . $ext;
        if (file_exists($file)) {
            ob_end_clean();
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Length: ' . filesize($file));
            header('Content-Disposition: attachment; filename=' . basename($file));
            readfile($file);
        } else {
            throw new \Exception("{$folder} File is abnormal");
        }
    }

    /**
     * 删除库数据备份系列
     * @param array $files 要删除的文件夹名
     * @return  string|bool
     */
    private function delBackFile(array $files = [])
    {
        $s = '';
        foreach ($files as $f) {
            $d = $this->config['path'] . $f;
            if (is_dir($d)) {
                $l = glob($d . '/*');
                if ($l) {
                    foreach ($l as $v) {
                        if (is_file($v)) @unlink($v);
                    }
                }
                $r = @rmdir($d);
                if (!$r) $s .= $s ? '|' . $d : $d;
            } else {
                $s .= $s ? '|' . $d : $d;
            }
        }
        return $s ?: true;
    }
}
