<?php
/**
 * 数据库表检查与修复
 * 验证数据库表与安装目录SQL文件的差异
 */

error_reporting(E_ALL);
ini_set('display_errors', 0);

try {
    require_once __DIR__ . '/../MPHX/common.php';
} catch (Exception $e) {
    header('Content-Type: application/json; charset=UTF-8');
    exit(json_encode(array('code' => 500, 'message' => '加载失败: ' . $e->getMessage())));
}

header('Content-Type: application/json; charset=UTF-8');
header('Access-Control-Allow-Credentials: true');

$action = isset($_POST['action']) ? $_POST['action'] : '';

// 检查登录状态
$admin_user = is_admin_login();
if (!$admin_user) {
    $debug_info = array(
        'session_exists' => isset($_SESSION),
        'admin_user_in_session' => isset($_SESSION['admin_user']),
        'admin_token_cookie' => isset($_COOKIE['admin_token']),
        'session_id' => session_id()
    );
    exit(json_encode(array(
        'code' => 401,
        'message' => '请先登录',
        'debug' => $debug_info
    )));
}

// SQL文件目录
$sql_dir = ROOT . 'install/sql/';

switch($action) {
    case 'check':
        // 检查差异
        logjl($admin_user, '数据库检查', '检查数据库表与SQL文件差异', true);
        
        try {
            // 获取SQL文件列表
            $sql_files = array();
            if (is_dir($sql_dir)) {
                $files = glob($sql_dir . '*.sql');
                foreach ($files as $file) {
                    $table_name = basename($file, '.sql');
                    $sql_files[] = $table_name;
                }
            }
            sort($sql_files);
            
            // 获取数据库中的表
            $db_tables = array();
            $result = $DB->query("SHOW TABLES");
            while ($row = $DB->fetch($result)) {
                $db_tables[] = array_values($row)[0];
            }
            sort($db_tables);
            
            // 计算差异
            $missing_in_db = array_diff($sql_files, $db_tables);  // SQL有但数据库没有
            $extra_in_db = array_diff($db_tables, $sql_files);    // 数据库有但SQL没有
            
            // 检查每个表的字段差异
            $table_differences = array();
            foreach ($sql_files as $table) {
                if (in_array($table, $db_tables)) {
                    // 获取SQL文件中的字段
                    $sql_fields = getSqlTableFields($sql_dir . $table . '.sql');
                    // 获取数据库中的字段
                    $db_fields = getDbTableFields($table, $DB);
                    
                    if ($sql_fields && $db_fields) {
                        $field_diff = array_diff_key($sql_fields, $db_fields);
                        if (!empty($field_diff)) {
                            $table_differences[$table] = array_keys($field_diff);
                        }
                    }
                }
            }
            
            $has_differences = !empty($missing_in_db) || !empty($extra_in_db) || !empty($table_differences);
            
            exit(json_encode(array(
                'code' => 200,
                'has_differences' => $has_differences,
                'sql_count' => count($sql_files),
                'db_count' => count($db_tables),
                'missing_in_db' => array_values($missing_in_db),
                'extra_in_db' => array_values($extra_in_db),
                'table_differences' => $table_differences,
                'message' => $has_differences ? '发现差异' : '数据库表与SQL文件一致'
            )));
            
        } catch(Exception $e) {
            exit(json_encode(array('code' => 500, 'message' => '检查失败: ' . $e->getMessage())));
        }
        break;
        
    case 'repair':
        // 修复数据库（从SQL文件创建缺失的表）
        logjl($admin_user, '数据库修复', '从SQL文件修复数据库表', true);
        
        try {
            $repaired = array();
            $errors = array();
            
            // 获取SQL文件列表
            $sql_files = glob($sql_dir . '*.sql');
            
            foreach ($sql_files as $sql_file) {
                $table_name = basename($sql_file, '.sql');
                
                // 检查表是否已存在
                $check = $DB->get_row("SHOW TABLES LIKE '{$table_name}'");
                
                if (!$check) {
                    // 表不存在，创建它
                    $sql_content = file_get_contents($sql_file);
                    
                    // 执行SQL
                    if ($DB->query($sql_content)) {
                        $repaired[] = $table_name;
                    } else {
                        $errors[] = $table_name . ': ' . $DB->error();
                    }
                } else {
                    // 表存在，检查是否需要添加字段
                    $sql_fields = getSqlTableFields($sql_file);
                    $db_fields = getDbTableFields($table_name, $DB);

                    if ($sql_fields && $db_fields) {
                        // 1. 添加缺失的字段
                        $missing_fields = array_diff_key($sql_fields, $db_fields);
                        foreach ($missing_fields as $field_name => $field_def) {
                            $alter_sql = "ALTER TABLE `{$table_name}` ADD COLUMN `{$field_name}` {$field_def}";
                            if ($DB->query($alter_sql)) {
                                $repaired[] = $table_name . '.' . $field_name . ' (新增)';
                            } else {
                                $errors[] = $table_name . '.' . $field_name . ' (新增): ' . $DB->error();
                            }
                        }

                        // 2. 检查并更新现有字段的默认值
                        foreach ($sql_fields as $field_name => $sql_def) {
                            if (isset($db_fields[$field_name])) {
                                $sql_default = getFieldDefault($sql_def);
                                $db_default = getDbFieldDefault($table_name, $field_name, $DB);

                                // 如果默认值不同，则修改
                                if ($sql_default !== null && $sql_default !== $db_default) {
                                    $alter_sql = "ALTER TABLE `{$table_name}` MODIFY COLUMN `{$field_name}` {$sql_def}";
                                    if ($DB->query($alter_sql)) {
                                        $repaired[] = $table_name . '.' . $field_name . ' (修改默认值: ' . ($db_default ?: '空') . ' -> ' . $sql_default . ')';
                                    } else {
                                        $errors[] = $table_name . '.' . $field_name . ' (修改默认值): ' . $DB->error();
                                    }
                                }
                            }
                        }
                    }
                }
            }
            
            exit(json_encode(array(
                'code' => 200,
                'repaired' => $repaired,
                'errors' => $errors,
                'message' => '修复完成，共修复 ' . count($repaired) . ' 项'
            )));
            
        } catch(Exception $e) {
            exit(json_encode(array('code' => 500, 'message' => '修复失败: ' . $e->getMessage())));
        }
        break;
        
    case 'delete_extra':
        // 删除多余的表
        logjl($admin_user, '数据库清理', '删除多余的数据库表', true);
        
        try {
            $tables_to_delete = isset($_POST['tables']) ? json_decode($_POST['tables'], true) : array();
            
            if (empty($tables_to_delete)) {
                exit(json_encode(array('code' => 400, 'message' => '未指定要删除的表')));
            }
            
            $deleted = array();
            $errors = array();
            
            foreach ($tables_to_delete as $table_name) {
                // 安全检查：表名只能包含字母、数字、下划线
                if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_name)) {
                    $errors[] = $table_name . ': 表名包含非法字符';
                    continue;
                }
                
                // 执行删除
                $drop_sql = "DROP TABLE IF EXISTS `{$table_name}`";
                if ($DB->query($drop_sql)) {
                    $deleted[] = $table_name;
                } else {
                    $errors[] = $table_name . ': ' . $DB->error();
                }
            }
            
            exit(json_encode(array(
                'code' => 200,
                'deleted' => $deleted,
                'errors' => $errors,
                'message' => '删除完成，共删除 ' . count($deleted) . ' 个表'
            )));
            
        } catch(Exception $e) {
            exit(json_encode(array('code' => 500, 'message' => '删除失败: ' . $e->getMessage())));
        }
        break;
        
    default:
        exit(json_encode(array('code' => 400, 'message' => '无效的操作')));
}

/**
 * 从SQL文件获取表字段
 */
function getSqlTableFields($sql_file) {
    $content = file_get_contents($sql_file);
    if (!$content) return false;
    
    $fields = array();
    
    // 匹配 CREATE TABLE 语句中的字段定义
    if (preg_match('/CREATE TABLE.*?\((.*)\).*?ENGINE/si', $content, $matches)) {
        $field_lines = explode("\n", $matches[1]);
        
        foreach ($field_lines as $line) {
            $line = trim($line);
            // 匹配字段定义行（以反引号开头）
            if (preg_match('/^`(\w+)`\s+(.+?)[,\s]*$/', $line, $field_match)) {
                $field_name = $field_match[1];
                $field_def = rtrim($field_match[2], ',');
                $fields[$field_name] = $field_def;
            }
        }
    }
    
    return $fields;
}

/**
 * 从数据库获取表字段
 */
function getDbTableFields($table_name, $db) {
    $fields = array();
    $result = $db->query("DESCRIBE `{$table_name}`");
    
    if (!$result) return false;
    
    while ($row = $db->fetch($result)) {
        $row = array_values($row);
        $field_name = $row[0];
        $field_type = $row[1];
        $field_null = $row[2] === 'NO' ? 'NOT NULL' : 'NULL';
        $field_default = $row[4] !== null ? "DEFAULT '{$row[4]}'" : '';
        $field_extra = $row[5] ?? '';

        $fields[$field_name] = trim("{$field_type} {$field_null} {$field_default} {$field_extra}");
    }

    return $fields;
}

/**
 * 从SQL字段定义中提取默认值
 */
function getFieldDefault($field_def) {
    // 匹配 DEFAULT 'value' 或 DEFAULT value
    if (preg_match("/DEFAULT\s+'([^']+)'/i", $field_def, $matches)) {
        return $matches[1];
    }
    if (preg_match('/DEFAULT\s+(\d+)/i', $field_def, $matches)) {
        return $matches[1];
    }
    return null;
}

/**
 * 从数据库获取字段默认值
 */
function getDbFieldDefault($table_name, $field_name, $db) {
    $result = $db->query("SHOW COLUMNS FROM `{$table_name}` WHERE `Field` = '{$field_name}'");
    if ($result && $row = $db->fetch($result)) {
        return isset($row['Default']) ? $row['Default'] : null;
    }
    return null;
}
