ylrz_saas_pharmacy/adminui/scripts/apply-instance-scope-columns.js

const mysql = require('mysql2/promise')
const DB = process.argv[2] || 'fs_tenant_cs1'
const cfg = {
  host: 'cq-cdb-8fjmemkb.sql.tencentcdb.com', port: 27220,
  user: 'root', password: 'Ylrz_1q2w3e4r5t6y', database: DB, charset: 'utf8mb4', multipleStatements: true
}

async function colExists(c, table, col) {
  const [r] = await c.query(
    'SELECT COUNT(*) cnt FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?',
    [table, col]
  )
  return r[0].cnt > 0
}

async function main() {
  const c = await mysql.createConnection(cfg)
  console.log('Patch instance scope columns on', DB)
  const alters = [
    ['dept_id', "ADD COLUMN dept_id BIGINT NULL AFTER channel_type"],
    ['company_user_id', "ADD COLUMN company_user_id BIGINT NULL AFTER dept_id"],
    ['qw_user_id', "ADD COLUMN qw_user_id BIGINT NULL AFTER company_user_id"],
    ['wx_account_id', "ADD COLUMN wx_account_id BIGINT NULL AFTER qw_user_id"]
  ]
  for (const [col, sql] of alters) {
    if (!(await colExists(c, 'lobster_workflow_instance', col))) {
      await c.query(`ALTER TABLE lobster_workflow_instance ${sql}`)
      console.log('  added', col)
    }
  }
  await c.end()
  console.log('Done')
}
main().catch(e => { console.error(e); process.exit(1) })