ylrz_saas_pharmacy/java/scripts/check-tenant-company-relation.js

// 查看 company 表结构和 tenant_info 表，了解租户和公司的关系
const mysql = require('mysql2/promise')

const DB = {
  host: 'cq-cdb-8fjmemkb.sql.tencentcdb.com',
  port: 27220,
  user: 'root',
  password: 'Ylrz_1q2w3e4r5t6y',
  multipleStatements: false
}

async function main() {
  const conn = await mysql.createConnection(DB)
  try {
    // 1. 查看 tenant_info 表结构
    const [tenantCols] = await conn.query("SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ylrz_saas' AND TABLE_NAME='tenant_info' ORDER BY ORDINAL_POSITION")
    console.log('=== tenant_info 表结构 ===')
    tenantCols.forEach(c => console.log(`  ${c.COLUMN_NAME} (${c.DATA_TYPE})`))

    // 2. 查看 company 表结构（主库）
    const [companyCols] = await conn.query("SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ylrz_saas' AND TABLE_NAME='company' ORDER BY ORDINAL_POSITION")
    console.log('\n=== company 表结构 (主库 ylrz_saas) ===')
    companyCols.forEach(c => console.log(`  ${c.COLUMN_NAME} (${c.DATA_TYPE})`))

    // 3. 查看租户列表
    const [tenants] = await conn.query("SELECT id, tenant_code, tenant_name, db_name, status FROM ylrz_saas.tenant_info LIMIT 10")
    console.log('\n=== 租户列表 ===')
    tenants.forEach(t => console.log(`  id=${t.id}, code=${t.tenant_code}, name=${t.tenant_name}, db=${t.db_name}`))

    // 4. 查看租户库里的 company 表
    const [tenantDbs] = await conn.query("SHOW DATABASES LIKE 'fs_tenant_%'")
    const firstTenantDb = tenantDbs[0] ? Object.values(tenantDbs[0])[0] : null
    if (firstTenantDb) {
      const [tenantCompanyCols] = await conn.query(`SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='company' ORDER BY ORDINAL_POSITION`, [firstTenantDb])
      console.log(`\n=== company 表结构 (租户库 ${firstTenantDb}) ===`)
      tenantCompanyCols.forEach(c => console.log(`  ${c.COLUMN_NAME} (${c.DATA_TYPE})`))

      // 查看租户库里的 company 数据
      const [companies] = await conn.query(`SELECT company_id, company_name FROM \`${firstTenantDb}\`.company WHERE is_del = 0 LIMIT 10`)
      console.log(`\n=== ${firstTenantDb} 的 company 列表 ===`)
      companies.forEach(c => console.log(`  company_id=${c.company_id}, name=${c.company_name}`))

      // 查看 lobster_user_profile 的 company_id 分布
      const [profileDist] = await conn.query(`SELECT company_id, COUNT(*) as cnt FROM \`${firstTenantDb}\`.lobster_user_profile WHERE deleted=0 GROUP BY company_id`)
      console.log(`\n=== ${firstTenantDb} 的 lobster_user_profile company_id 分布 ===`)
      profileDist.forEach(p => console.log(`  company_id=${p.company_id}, count=${p.cnt}`))
    }
  } finally {
    await conn.end()
  }
}

main().catch(err => {
  console.error('FAILED:', err.message || err)
  process.exit(1)
})