const { Pool } = require('pg');
const pool = new Pool({
  host: '47.116.196.11',
  port: 15432,
  database: 'postgresql',
  user: 'postgresql',
  password: 'Jchl1528'
});

async function findMissingIds() {
  try {
    // 1. 查找 op_schedule 中存在的 apply_id，但在 cli_surgery 中不存在的
    console.log('=== 查找不匹配的 apply_id ===\n');
    
    const result = await pool.query(`
      SELECT DISTINCT os.apply_id
      FROM hisdev.op_schedule os
      LEFT JOIN hisdev.cli_surgery cs ON os.apply_id = cs.id
      WHERE os.delete_flag = '0'
      AND cs.id IS NULL
      ORDER BY os.apply_id DESC
      LIMIT 10
    `);
    
    console.log('在 op_schedule 中存在，但在 cli_surgery 中不存在的 apply_id:');
    console.table(result.rows);
    
    // 2. 尝试用这些 ID 直接查询 cli_surgery
    if (result.rows.length > 0) {
      console.log('\n=== 尝试直接查询这些 ID ===');
      for (const row of result.rows) {
        const id = row.apply_id;
        const checkResult = await pool.query(`
          SELECT id, surgery_no, patient_name, status_enum, delete_flag
          FROM hisdev.cli_surgery
          WHERE id = $1
        `, [id]);
        
        if (checkResult.rows.length === 0) {
          console.log(`ID ${id}: 在 cli_surgery 表中不存在`);
        } else {
          console.log(`ID ${id}: 找到记录`, checkResult.rows[0]);
        }
      }
    }
    
    // 3. 检查是否有可能是手术单号匹配而不是 ID 匹配
    console.log('\n=== 检查手术单号关联 ===');
    const operCodeResult = await pool.query(`
      SELECT 
        os.schedule_id,
        os.oper_code,
        os.apply_id,
        cs.id as surgery_id_by_apply_id,
        cs2.id as surgery_id_by_oper_code,
        cs2.surgery_no,
        cs2.apply_dept_name,
        cs2.main_surgeon_name
      FROM hisdev.op_schedule os
      LEFT JOIN hisdev.cli_surgery cs ON os.apply_id = cs.id
      LEFT JOIN hisdev.cli_surgery cs2 ON os.oper_code = cs2.surgery_no
      WHERE os.delete_flag = '0'
      ORDER BY os.create_time DESC
      LIMIT 5
    `);
    
    console.table(operCodeResult.rows);
    
  } catch (err) {
    console.error('查询失败:', err.message);
  } finally {
    pool.end();
  }
}

findMissingIds();