const mysql = require('mysql2/promise');

async function checkPhoneInDatabase() {
    const connection = await mysql.createConnection({
        host: '139.155.104.10',
        port: 3306,
        user: 'ryc',
        password: 'KiYap3E8X8RLcM6T',
        database: 'ryc',
        connectTimeout: 60000
    });

    try {
        console.log('=== 检查数据库表结构和手机号 13981970816 ===\n');

        // 1. 查看所有表
        console.log('1. 查看数据库中的所有表:');
        const [tables] = await connection.execute('SHOW TABLES');
        console.log('数据库表:', tables.map(t => Object.values(t)[0]));
        console.log('');

        // 2. 查看用户相关表的结构
        const userTables = tables.filter(t => Object.values(t)[0].includes('user')).map(t => Object.values(t)[0]);
        
        for (const tableName of userTables) {
            console.log(`${tableName} 表结构:`);
            try {
                const [structure] = await connection.execute(`DESCRIBE ${tableName}`);
                structure.forEach(col => {
                    console.log(`  ${col.Field}: ${col.Type}, Null: ${col.Null}, Key: ${col.Key}, Default: ${col.Default}`);
                });
                console.log('');
            } catch (err) {
                console.log(`  无法查看 ${tableName} 表结构:`, err.message);
            }
        }

        // 3. 查找包含phone字段的表
        console.log('3. 查找包含phone字段的表:');
        for (const table of tables.map(t => Object.values(t)[0])) {
            try {
                const [columns] = await connection.execute(`SHOW COLUMNS FROM ${table} LIKE '%phone%'`);
                if (columns.length > 0) {
                    console.log(`${table} 表包含phone字段:`, columns);
                }
            } catch (err) {
                // 忽略错误，继续下一个表
            }
        }
        console.log('');

        // 4. 如果找到了用户表，查询手机号
        if (userTables.length > 0) {
            const mainUserTable = userTables[0]; // 使用第一个用户表
            console.log(`4. 在 ${mainUserTable} 表中查找手机号 13981970816:`);
            
            try {
                // 先查看表结构确定字段名
                const [structure] = await connection.execute(`DESCRIBE ${mainUserTable}`);
                const phoneField = structure.find(col => col.Field.toLowerCase().includes('phone'));
                const idField = structure.find(col => col.Field.toLowerCase().includes('id') && col.Key === 'PRI');
                
                if (phoneField) {
                    const query = `SELECT * FROM ${mainUserTable} WHERE ${phoneField.Field} = ?`;
                    const [users] = await connection.execute(query, ['13981970816']);
                    console.log('查询结果:', users);
                    
                    // 也查询包含该手机号的记录
                    const likeQuery = `SELECT * FROM ${mainUserTable} WHERE ${phoneField.Field} LIKE ?`;
                    const [likeUsers] = await connection.execute(likeQuery, ['%13981970816%']);
                    console.log('模糊查询结果:', likeUsers);
                } else {
                    console.log('未找到phone字段');
                }
            } catch (err) {
                console.log('查询用户表出错:', err.message);
            }
        }

    } catch (error) {
        console.error('数据库查询错误:', error);
    } finally {
        await connection.end();
    }
}

checkPhoneInDatabase();