ylrz_saas_pharmacy/adminui/scripts/repair-lobster-profile-schema.js

/**
 * Add lobster_user_profile.channel_type (and related index) if missing.
 * Usage: node scripts/repair-lobster-profile-schema.js [dbName]
 */
const mysql = require('mysql2/promise')

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

const TARGET_DB = process.argv[2] || 'fs_tenant_cs1'

async function columnExists(conn, table, column) {
  const [rows] = await conn.query(
    `SELECT COUNT(*) c FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`,
    [table, column]
  )
  return rows[0].c > 0
}

async function indexExists(conn, table, indexName) {
  const [rows] = await conn.query(
    `SELECT COUNT(*) c FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?`,
    [table, indexName]
  )
  return rows[0].c > 0
}

async function repair(conn) {
  const [tables] = await conn.query(`SHOW TABLES LIKE 'lobster_user_profile'`)
  if (!tables.length) {
    console.log('skip: lobster_user_profile table not found')
    return
  }

  const hasChannel = await columnExists(conn, 'lobster_user_profile', 'channel_type')
  if (!hasChannel) {
    await conn.query(`
      ALTER TABLE lobster_user_profile
      ADD COLUMN channel_type varchar(20) NOT NULL DEFAULT 'QW' COMMENT 'channel type QW/WX/IM'
      AFTER external_user_id
    `)
    console.log('added column channel_type')
  } else {
    console.log('column channel_type already exists')
  }

  if (await indexExists(conn, 'lobster_user_profile', 'uk_user')) {
    const [idx] = await conn.query(`SHOW INDEX FROM lobster_user_profile WHERE Key_name = 'uk_user'`)
    const cols = idx.map(r => r.Column_name).join(',')
    if (cols === 'company_id,external_user_id' || cols === 'company_id,external_user_id,') {
      await conn.query('ALTER TABLE lobster_user_profile DROP INDEX uk_user')
      console.log('dropped old uk_user')
    }
  }

  if (!(await indexExists(conn, 'lobster_user_profile', 'uk_user'))) {
    await conn.query(`
      ALTER TABLE lobster_user_profile
      ADD UNIQUE KEY uk_user (company_id, channel_type, external_user_id)
    `)
    console.log('added uk_user (company_id, channel_type, external_user_id)')
  } else {
    console.log('index uk_user already ok')
  }
}

async function main() {
  const conn = await mysql.createConnection({ ...DB_CFG, database: TARGET_DB })
  console.log('DB:', TARGET_DB)
  await repair(conn)
  const [cols] = await conn.query(
    `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_user_profile'
     ORDER BY ORDINAL_POSITION`
  )
  console.log('columns:', cols.map(r => r.COLUMN_NAME).join(', '))
  await conn.end()
}

main().catch(e => { console.error(e); process.exit(1) })