<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
require '../config.php';

@header('Content-Type: text/html; charset=UTF-8');

function random($length, $numeric = 0) {
	$seed = base_convert(md5(microtime().$_SERVER['DOCUMENT_ROOT']), 16, $numeric ? 10 : 35);
	$seed = $numeric ? (str_replace('0', '', $seed).'012340567890') : ($seed.'zZ'.strtoupper($seed));
	$hash = '';
	$max = strlen($seed) - 1;
	for($i = 0; $i < $length; $i++) {
		$hash .= $seed[mt_rand(0, $max)];
	}
	return $hash;
}

try{
	$db = new PDO("mysql:host=".$dbconfig['host'].";dbname=".$dbconfig['dbname'].";port=".$dbconfig['port'], $dbconfig['user'], $dbconfig['pwd']);
}catch(Exception $e){
	exit('链接数据库失败:'.$e->getMessage());
}
date_default_timezone_set("PRC");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$db->exec("set sql_mode = ''");
$db->exec("set names utf8");

$targetVersion = 2021;
$currentVersion = 0;

$row = $db->query("SELECT v FROM pre_config WHERE k='version'");
if($row){
	$val = $row->fetchColumn();
	if($val !== false && $val !== null){
		$currentVersion = (int)$val;
	}
}

if($currentVersion >= $targetVersion){
	exit('你的网站已经升级到最新版本了（当前版本：'.$currentVersion.'，目标版本：'.$targetVersion.'）');
}

echo '<!DOCTYPE html><html><head><meta charset="UTF-8"><title>数据库升级</title>';
echo '<style>
body{font-family:"Microsoft YaHei",sans-serif;background:#f5f5f5;margin:0;padding:20px;}
.container{max-width:800px;margin:0 auto;background:#fff;border-radius:8px;box-shadow:0 2px 10px rgba(0,0,0,0.1);padding:30px;}
h2{color:#333;border-bottom:2px solid #e0e0e0;padding-bottom:10px;margin-top:0;}
h3{color:#555;border-bottom:1px solid #e0e0e0;padding-bottom:8px;margin-top:25px;}
.info{background:#e8f4fd;border-left:4px solid #2196F3;padding:12px 16px;margin:15px 0;border-radius:4px;}
.success{color:#4CAF50;font-weight:bold;}
.error{color:#f44336;font-weight:bold;}
.warning{color:#FF9800;font-weight:bold;}
.table-list{margin:10px 0;padding:0;list-style:none;}
.table-list li{padding:8px 12px;margin:4px 0;background:#f9f9f9;border-radius:4px;border:1px solid #e0e0e0;}
.table-list li.new{background:#e8f5e9;border-color:#4CAF50;}
.sql-block{background:#f4f4f4;border:1px solid #ddd;padding:10px;margin:8px 0;border-radius:4px;font-family:Consolas,monospace;font-size:12px;white-space:pre-wrap;word-break:break-all;max-height:300px;overflow-y:auto;}
.summary{background:#f9f9f9;padding:15px;border-radius:4px;margin:15px 0;}
.btn{display:inline-block;padding:10px 20px;background:#2196F3;color:#fff;text-decoration:none;border-radius:4px;margin-top:15px;}
.btn:hover{background:#1976D2;}
.field-list{margin:5px 0 15px 20px;padding:0;list-style:none;font-size:13px;}
.field-list li{padding:4px 8px;margin:2px 0;background:#f5f5f5;border-radius:3px;border-left:3px solid #ccc;}
.field-list li.new{border-left-color:#4CAF50;background:#e8f5e9;}
.debug{background:#fff3cd;border:1px solid #ffc107;padding:10px;margin:10px 0;border-radius:4px;font-size:12px;}
</style></head><body><div class="container">';

echo '<h2>数据库结构同步升级</h2>';

echo '<div class="info">当前数据库版本: <strong>'.$currentVersion.'</strong> &rarr; 目标版本: <strong>'.$targetVersion.'</strong></div>';

$sqlContent = file_get_contents('update.sql');
if($sqlContent === false || trim($sqlContent) === ''){
	exit('<p class="error">update.sql 文件为空或无法读取，升级终止。</p></div></body></html>');
}

function parseCreateTable($sql) {
	$result = [
		'columns' => [],
		'indexes' => [],
		'primary' => null,
		'sql' => $sql
	];
	
	preg_match('/CREATE\s+TABLE\s+`?(\w+)`?\s*\((.*)\)\s*ENGINE/is', $sql, $matches);
	if(empty($matches[2])) {
		return $result;
	}
	
	$content = $matches[2];
	
	$lines = [];
	$currentLine = '';
	$parenDepth = 0;
	$inQuote = false;
	$quoteChar = '';
	
	for($i = 0; $i < strlen($content); $i++) {
		$char = $content[$i];
		
		if(!$inQuote && ($char == "'" || $char == '"')) {
			$inQuote = true;
			$quoteChar = $char;
		}
		elseif($inQuote && $char == $quoteChar) {
			$inQuote = false;
			$quoteChar = '';
		}
		elseif(!$inQuote) {
			if($char == '(') $parenDepth++;
			elseif($char == ')') $parenDepth--;
			elseif($char == ',' && $parenDepth == 0) {
				$lines[] = trim($currentLine);
				$currentLine = '';
				continue;
			}
		}
		
		$currentLine .= $char;
	}
	
	if(trim($currentLine) != '') {
		$lines[] = trim($currentLine);
	}
	
	foreach($lines as $line) {
		$line = trim($line);
		if(empty($line)) continue;
		
		if(preg_match('/^`(\w+)`\s+(.+)$/i', $line, $colMatch)) {
			$colName = $colMatch[1];
			$colDef = rtrim($colMatch[2], ',');
			$result['columns'][$colName] = $colDef;
		}
		elseif(preg_match('/PRIMARY\s+KEY\s*\(([^)]+)\)/i', $line, $pkMatch)) {
			$result['primary'] = $pkMatch[1];
		}
		elseif(preg_match('/UNIQUE\s+KEY\s*`?(\w+)`?\s*\(([^)]+)\)/i', $line, $ukMatch)) {
			$result['indexes'][$ukMatch[1]] = ['type' => 'UNIQUE', 'columns' => $ukMatch[2]];
		}
		elseif(preg_match('/KEY\s*`?(\w+)`?\s*\(([^)]+)\)/i', $line, $idxMatch)) {
			$result['indexes'][$idxMatch[1]] = ['type' => 'INDEX', 'columns' => $idxMatch[2]];
		}
	}
	
	return $result;
}

function getDbTableStructure($db, $table) {
	$result = [
		'columns' => [],
		'indexes' => []
	];
	
	try {
		$cols = $db->query("SHOW COLUMNS FROM `$table`");
		if($cols) {
			while($row = $cols->fetch(PDO::FETCH_ASSOC)) {
				$result['columns'][$row['Field']] = $row;
			}
		}
		
		$idxs = $db->query("SHOW INDEX FROM `$table`");
		if($idxs) {
			while($row = $idxs->fetch(PDO::FETCH_ASSOC)) {
				if($row['Key_name'] === 'PRIMARY') continue;
				if(!isset($result['indexes'][$row['Key_name']])) {
					$result['indexes'][$row['Key_name']] = [
						'type' => $row['Non_unique'] == 0 ? 'UNIQUE' : 'INDEX',
						'columns' => []
					];
				}
				$result['indexes'][$row['Key_name']]['columns'][] = $row['Column_name'];
			}
		}
	} catch(Exception $e) {
		return null;
	}
	
	return $result;
}

function generateColumnSql($table, $colName, $colDef) {
	return "ALTER TABLE `$table` ADD COLUMN `$colName` $colDef";
}

function generateIndexSql($table, $idxName, $idxInfo) {
	$colStr = is_array($idxInfo['columns']) ? implode(',', $idxInfo['columns']) : $idxInfo['columns'];
	$idxType = $idxInfo['type'] === 'UNIQUE' ? 'UNIQUE INDEX' : 'INDEX';
	return "ALTER TABLE `$table` ADD $idxType `$idxName` ($colStr)";
}

preg_match_all('/DROP\s+TABLE\s+IF\s+EXISTS\s+`(\w+)`;\s*(CREATE\s+TABLE\s+`\w+`\s*\([^;]+?)\s*ENGINE=[^;]+;/is', $sqlContent, $tableBlocks, PREG_SET_ORDER);

$expectedTables = [];
foreach($tableBlocks as $block) {
	$tableName = $block[1];
	$createSql = $block[0];
	$expectedTables[$tableName] = parseCreateTable($createSql);
}

$existingTables = [];
$tablesResult = $db->query("SHOW TABLES");
if($tablesResult){
	while($tableRow = $tablesResult->fetch(PDO::FETCH_NUM)){
		$existingTables[] = $tableRow[0];
	}
}

echo '<p>数据库现有 '.count($existingTables).' 张表，SQL文件定义 '.count($expectedTables).' 张表</p>';

$totalSuccess = 0;
$totalError = 0;
$errorDetail = '';

$newTables = [];
$existingTablesToCheck = [];

foreach($expectedTables as $tableName => $structure) {
	if(!in_array($tableName, $existingTables)) {
		$newTables[$tableName] = $structure;
	} else {
		$existingTablesToCheck[$tableName] = $structure;
	}
}

if(!empty($newTables)) {
	echo '<h3>创建新表</h3>';
	echo '<p>检测到 <strong>'.count($newTables).'</strong> 张缺失的表需要创建：</p>';
	echo '<ul class="table-list">';
	foreach($newTables as $tableName => $structure) {
		echo '<li class="new">+ '.htmlspecialchars($tableName, ENT_QUOTES, 'UTF-8').'</li>';
	}
	echo '</ul>';
	
	foreach($newTables as $tableName => $structure) {
		preg_match('/DROP\s+TABLE\s+IF\s+EXISTS\s+`'.$tableName.'`;\s*(CREATE\s+TABLE\s+`'.$tableName.'`\s*\([^;]+)\s*ENGINE=[^;]+;/is', $sqlContent, $createMatch);
		
		if(!empty($createMatch[0])) {
			$createSql = trim($createMatch[0]);
			if($db->exec($createSql) !== false) {
				$totalSuccess++;
				echo '<p class="success">表 <strong>'.htmlspecialchars($tableName, ENT_QUOTES, 'UTF-8').'</strong> 创建成功</p>';
			} else {
				$totalError++;
				$dberror = $db->errorInfo();
				$errorDetail .= '<p class="error">创建表 <strong>'.htmlspecialchars($tableName, ENT_QUOTES, 'UTF-8').'</strong> 失败 ['.$dberror[0].'/'.$dberror[1].']: '.htmlspecialchars($dberror[2], ENT_QUOTES, 'UTF-8').'</p>';
			}
		}
	}
} else {
	echo '<div class="info">所有表均已存在，无需创建新表。</div>';
}

if(!empty($existingTablesToCheck)) {
	echo '<h3>同步表字段和索引</h3>';
	
	foreach($existingTablesToCheck as $tableName => $expectedStructure) {
		$dbStructure = getDbTableStructure($db, $tableName);
		if(!$dbStructure) {
			echo '<p class="warning">无法获取表 <strong>'.htmlspecialchars($tableName, ENT_QUOTES, 'UTF-8').'</strong> 的结构，跳过</p>';
			continue;
		}
		
		$missingColumns = [];
		foreach($expectedStructure['columns'] as $colName => $colDef) {
			if(!isset($dbStructure['columns'][$colName])) {
				$missingColumns[$colName] = $colDef;
			}
		}
		
		$missingIndexes = [];
		foreach($expectedStructure['indexes'] as $idxName => $idxInfo) {
			if(!isset($dbStructure['indexes'][$idxName])) {
				$missingIndexes[$idxName] = $idxInfo;
			}
		}
		
		if(!empty($missingColumns) || !empty($missingIndexes)) {
			echo '<p><strong>表 '.htmlspecialchars($tableName, ENT_QUOTES, 'UTF-8').'：</strong></p>';
			
			if(!empty($missingColumns)) {
				echo '<ul class="field-list">';
				foreach($missingColumns as $colName => $colDef) {
					$alterSql = generateColumnSql($tableName, $colName, $colDef);
					if($db->exec($alterSql) !== false) {
						$totalSuccess++;
						echo '<li class="new">+ 字段 '.htmlspecialchars($colName, ENT_QUOTES, 'UTF-8').' 添加成功</li>';
					} else {
						$totalError++;
						$dberror = $db->errorInfo();
						echo '<li class="error">+ 字段 '.htmlspecialchars($colName, ENT_QUOTES, 'UTF-8').' 添加失败: '.htmlspecialchars($dberror[2], ENT_QUOTES, 'UTF-8').'</li>';
						$errorDetail .= '<div class="sql-block">'.htmlspecialchars($alterSql, ENT_QUOTES, 'UTF-8').'</div>';
					}
				}
				echo '</ul>';
			}
			
			if(!empty($missingIndexes)) {
				echo '<ul class="field-list">';
				foreach($missingIndexes as $idxName => $idxInfo) {
					$alterSql = generateIndexSql($tableName, $idxName, $idxInfo);
					if($db->exec($alterSql) !== false) {
						$totalSuccess++;
						echo '<li class="new">+ 索引 '.htmlspecialchars($idxName, ENT_QUOTES, 'UTF-8').' 添加成功</li>';
					} else {
						$totalError++;
						$dberror = $db->errorInfo();
						echo '<li class="error">+ 索引 '.htmlspecialchars($idxName, ENT_QUOTES, 'UTF-8').' 添加失败: '.htmlspecialchars($dberror[2], ENT_QUOTES, 'UTF-8').'</li>';
						$errorDetail .= '<div class="sql-block">'.htmlspecialchars($alterSql, ENT_QUOTES, 'UTF-8').'</div>';
					}
				}
				echo '</ul>';
			}
		}
	}
} else {
	echo '<div class="info">没有需要检查的现有表。</div>';
}

$updateVersionSql = "REPLACE INTO `pre_config` VALUES ('version', '$targetVersion')";
if($db->exec($updateVersionSql) !== false){
	$totalSuccess++;
	echo '<p class="success">版本号已更新为 '.$targetVersion.'</p>';
}else{
	$totalError++;
	$dberror = $db->errorInfo();
	$errorDetail .= '<p class="error">更新版本号失败: '.htmlspecialchars($dberror[2], ENT_QUOTES, 'UTF-8').'</p>';
}

$syskeyRow = $db->query("SELECT v FROM pre_config WHERE k='syskey'");
if(!$syskeyRow || $syskeyRow->fetchColumn() === false){
	$syskeySql = "INSERT INTO `pre_config` VALUES ('syskey', '".random(32)."')";
	if($db->exec($syskeySql) !== false){
		$totalSuccess++;
		echo '<p class="success">系统密钥 syskey 已自动生成</p>';
	}else{
		$totalError++;
		$dberror = $db->errorInfo();
		$errorDetail .= '<p class="error">生成 syskey 失败: '.htmlspecialchars($dberror[2], ENT_QUOTES, 'UTF-8').'</p>';
	}
}

echo '<div class="summary">';
echo '<p>执行完毕: 成功 <span class="success">'.$totalSuccess.'</span> 条';
if($totalError > 0){
	echo '，失败 <span class="error">'.$totalError.'</span> 条';
}
echo '</p>';
echo '</div>';

if($errorDetail){
	echo '<div style="margin-top:15px;"><strong class="error">错误详情：</strong>'.$errorDetail.'</div>';
}

if($totalError === 0){
	echo '<script>alert("数据库升级完成！所有操作均已成功执行。");</script>';
}else{
	echo '<script>alert("数据库升级完成，但有 '.$totalError.' 条语句执行失败，请检查页面错误详情。");</script>';
}

echo '<a href="../" class="btn">返回网站首页</a>';
echo '</div></body></html>';
?>
