/**
 * 数据库表头分析脚本
 * 从每个表的每个字段提取3个示例值，用于分析字段实际含义
 * 
 * 使用方式:
 *   node analyze-tables.js --host <host> --port <port> --db <database> --user <user> --password <password>
 * 
 * 示例:
 *   node analyze-tables.js --host rm-xxx.sqlserver.rds.aliyuncs.com --port 1433 --db mydb --user admin --password 123456
 */

const sql = require('mssql');
const fs = require('fs');
const path = require('path');

// 解析命令行参数
function parseArgs() {
  const args = process.argv.slice(2);
  const config = {};
  for (let i = 0; i < args.length; i += 2) {
    const key = args[i].replace('--', '');
    const value = args[i + 1];
    config[key] = value;
  }
  return config;
}

// 需要分析的表列表
const TABLES = [
  { name: 'jst_goods_profit_kpi_items_afs', description: '业绩退款相关信息' },
  { name: 'jst_sku', description: '普通商品资料表' },
  { name: 'fys_shopInfo', description: '店铺信息' },
  { name: 'jst_order_express_details', description: '订单快递详情' },
  { name: 'fys_SkuDataLibrary_SpuConfig', description: '产品资料SPU配置' },
  { name: 'op_operManage', description: 'SPU负责人管理' },
  { name: 'op_TargetManagement', description: '商品指标管理' },
  { name: 'fys_Ticketing_install', description: '开票安装单' },
  { name: 'fys_Ticketing_install_item', description: '安装明细表' },
  { name: 'fys_jstFreightCost', description: '聚水潭运费' },
  { name: 'fys_jstFreightCostDetail', description: '聚水潭运费明细' },
  { name: 'pc_kfChatLog', description: '客服聊天记录' }
];

async function getCloudDbConfig() {
  const args = parseArgs();
  
  // 如果提供了命令行参数，使用命令行参数
  if (args.host && args.db && args.user && args.password) {
    return {
      server: args.host,
      port: parseInt(args.port || '1433'),
      database: args.db,
      user: args.user,
      password: args.password,
      options: {
        encrypt: args.ssl === 'true',
        trustServerCertificate: true,
        connectTimeout: 30000,
      }
    };
  }

  // 否则从本地数据库获取配置
  const mysql = require('mysql2/promise');
  const localConfig = {
    host: args.localHost || 'localhost',
    port: parseInt(args.localPort || '3306'),
    user: args.localUser || 'root',
    password: args.localPassword || process.env.LOCAL_DB_PASSWORD || '',
    database: args.localDb || 'shujuku2_system'
  };

  try {
    const connection = await mysql.createConnection(localConfig);
    const [rows] = await connection.execute(
      'SELECT driver, host, port, database_name, username, password, `ssl` FROM database_configs WHERE is_default = 1 LIMIT 1'
    );
    await connection.end();
    
    if (rows.length > 0) {
      const config = rows[0];
      console.log(`   从本地数据库获取到配置: ${config.host}:${config.port}/${config.database_name}`);
      return {
        server: config.host,
        port: config.port || 1433,
        database: config.database_name,
        user: config.username,
        password: config.password,
        options: {
          encrypt: config.ssl === 1,
          trustServerCertificate: true,
          connectTimeout: 30000,
        }
      };
    }
    throw new Error('未找到云数据库配置');
  } catch (error) {
    console.error('从本地数据库获取配置失败:', error.message);
    throw error;
  }
}

async function getTableColumns(pool, tableName) {
  try {
    const result = await pool.request().query(`
      SELECT COLUMN_NAME, DATA_TYPE 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_NAME = '${tableName}'
      ORDER BY ORDINAL_POSITION
    `);
    return result.recordset;
  } catch (error) {
    console.error(`获取表 ${tableName} 的列信息失败:`, error.message);
    return [];
  }
}

async function getSampleData(pool, tableName, columnName) {
  try {
    // 获取3个非空的示例值
    const result = await pool.request().query(`
      SELECT TOP 3 [${columnName}] as value
      FROM [${tableName}]
      WHERE [${columnName}] IS NOT NULL AND CAST([${columnName}] AS NVARCHAR(MAX)) != ''
      ORDER BY NEWID()
    `);
    return result.recordset.map(r => {
      const val = r.value;
      // 处理不同类型的值
      if (val instanceof Date) {
        return val.toISOString().replace('T', ' ').substring(0, 19);
      }
      if (typeof val === 'string' && val.length > 100) {
        return val.substring(0, 100) + '...';
      }
      return String(val);
    });
  } catch (error) {
    return [`[错误: ${error.message.substring(0, 50)}]`];
  }
}

async function analyzeTable(pool, tableInfo) {
  console.log(`\n正在分析表: ${tableInfo.name} (${tableInfo.description})`);
  
  const columns = await getTableColumns(pool, tableInfo.name);
  if (columns.length === 0) {
    return { ...tableInfo, columns: [], error: '无法获取列信息' };
  }

  const analyzedColumns = [];
  for (const col of columns) {
    process.stdout.write(`  - ${col.COLUMN_NAME}...`);
    const samples = await getSampleData(pool, tableInfo.name, col.COLUMN_NAME);
    analyzedColumns.push({
      name: col.COLUMN_NAME,
      type: col.DATA_TYPE,
      samples
    });
    console.log(' 完成');
  }

  return {
    ...tableInfo,
    columns: analyzedColumns
  };
}

function generateMarkdown(results) {
  let md = `# 云数据库表头分析报告

> 生成时间: ${new Date().toLocaleString('zh-CN')}
> 
> 本文档通过提取每个字段的3个示例值来帮助理解字段的实际含义

---

`;

  for (const table of results) {
    md += `## ${table.name}\n\n`;
    md += `**描述**: ${table.description}\n\n`;
    
    if (table.error) {
      md += `**错误**: ${table.error}\n\n`;
      continue;
    }

    md += `| 序号 | 字段名 | 数据类型 | 示例值1 | 示例值2 | 示例值3 | 推测含义 |\n`;
    md += `|------|--------|----------|---------|---------|---------|----------|\n`;

    table.columns.forEach((col, idx) => {
      const s1 = col.samples[0] || '-';
      const s2 = col.samples[1] || '-';
      const s3 = col.samples[2] || '-';
      // 转义管道符号
      const escape = (str) => String(str).replace(/\|/g, '\\|').replace(/\n/g, ' ');
      md += `| ${idx + 1} | ${col.name} | ${col.type} | ${escape(s1)} | ${escape(s2)} | ${escape(s3)} | |\n`;
    });

    md += `\n---\n\n`;
  }

  return md;
}

async function main() {
  console.log('=== 云数据库表头分析工具 ===\n');
  
  let pool;
  try {
    // 获取云数据库配置
    console.log('1. 获取云数据库配置...');
    const config = await getCloudDbConfig();
    console.log(`   连接到: ${config.server}:${config.port}/${config.database}`);
    
    // 连接云数据库
    console.log('\n2. 连接云数据库...');
    pool = await sql.connect(config);
    console.log('   连接成功!');
    
    // 分析每个表
    console.log('\n3. 开始分析表...');
    const results = [];
    for (const tableInfo of TABLES) {
      const result = await analyzeTable(pool, tableInfo);
      results.push(result);
    }
    
    // 生成Markdown报告
    console.log('\n4. 生成分析报告...');
    const markdown = generateMarkdown(results);
    const outputPath = path.join(__dirname, '../../docs/云数据库表头分析.md');
    fs.writeFileSync(outputPath, markdown, 'utf8');
    console.log(`   报告已保存到: ${outputPath}`);
    
    console.log('\n=== 分析完成 ===');
    
  } catch (error) {
    console.error('\n错误:', error.message);
    process.exit(1);
  } finally {
    if (pool) {
      await pool.close();
    }
  }
}

main();