ylrz_saas_pharmacy/adminui/scripts/repair-lobster-api-registry-schema.js

/**
 * Repair lobster_api_registry schema for tenant DBs (old api_url -> new base_url/provider/auth columns).
 * Usage: node scripts/repair-lobster-api-registry-schema.js [dbName|all]
 */
const mysql = require('mysql2/promise')

const DB_CFG = {
  host: process.env.DB_HOST || 'cq-cdb-8fjmemkb.sql.tencentcdb.com',
  port: Number(process.env.DB_PORT || 27220),
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'Ylrz_1q2w3e4r5t6y',
  multipleStatements: true,
  charset: 'utf8mb4'
}

const MASTER_DB = process.env.DB_NAME || 'ylrz_saas'

const ALTERS = [
  "ALTER TABLE lobster_api_registry ADD COLUMN provider VARCHAR(64) DEFAULT NULL COMMENT 'provider' AFTER category",
  "ALTER TABLE lobster_api_registry ADD COLUMN base_url VARCHAR(500) DEFAULT NULL COMMENT 'base url' AFTER provider",
  "ALTER TABLE lobster_api_registry ADD COLUMN auth_type VARCHAR(32) DEFAULT 'none' COMMENT 'auth type' AFTER base_url",
  "ALTER TABLE lobster_api_registry ADD COLUMN auth_config TEXT COMMENT 'auth config' AFTER auth_type",
  "ALTER TABLE lobster_api_registry ADD COLUMN timeout INT DEFAULT 5000 COMMENT 'timeout ms' AFTER auth_config",
  "ALTER TABLE lobster_api_registry ADD COLUMN priority INT DEFAULT 0 COMMENT 'priority' AFTER enabled",
  "ALTER TABLE lobster_api_registry ADD COLUMN is_backup TINYINT DEFAULT 0 COMMENT 'backup' AFTER priority",
  "ALTER TABLE lobster_api_registry ADD COLUMN description TEXT COMMENT 'description' AFTER is_backup"
]

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

async function tableExists(conn, table) {
  const [rows] = await conn.query('SHOW TABLES LIKE ?', [table])
  return rows.length > 0
}

async function repairDb(db) {
  const conn = await mysql.createConnection({ ...DB_CFG, database: db })
  try {
    if (!(await tableExists(conn, 'lobster_api_registry'))) {
      console.log(`[skip] ${db}: table missing`)
      return
    }
    let added = 0
    for (const sql of ALTERS) {
      const col = sql.match(/ADD COLUMN (\w+)/)[1]
      if (await columnExists(conn, 'lobster_api_registry', col)) continue
      await conn.query(sql)
      added++
      console.log(`[${db}] added column ${col}`)
    }
    if (await columnExists(conn, 'lobster_api_registry', 'api_url')) {
      await conn.query(
        "UPDATE lobster_api_registry SET base_url = api_url WHERE (base_url IS NULL OR base_url = '') AND api_url IS NOT NULL AND api_url <> ''"
      )
    }
    if (await columnExists(conn, 'lobster_api_registry', 'api_description')) {
      await conn.query(
        "UPDATE lobster_api_registry SET description = api_description WHERE (description IS NULL OR description = '') AND api_description IS NOT NULL AND api_description <> ''"
      )
    }
    await conn.query("UPDATE lobster_api_registry SET auth_type = 'none' WHERE auth_type IS NULL OR auth_type = ''")
    const [cols] = await conn.query('SHOW COLUMNS FROM lobster_api_registry')
    const [cnt] = await conn.query('SELECT COUNT(*) AS c FROM lobster_api_registry')
    console.log(`[ok] ${db}: columns=${cols.length}, rows=${cnt[0].c}, newCols=${added}`)
  } finally {
    await conn.end()
  }
}

async function listTenantDbs() {
  const conn = await mysql.createConnection({ ...DB_CFG, database: MASTER_DB })
  const dbs = new Set()
  try {
    const [rows] = await conn.query("SELECT db_url FROM tenant_info WHERE db_url IS NOT NULL AND db_url <> ''")
    for (const r of rows) {
      const m = String(r.db_url).match(/\/([^/?]+)(?:\?|$)/)
      if (m) dbs.add(m[1])
    }
  } finally {
    await conn.end()
  }
  return [...dbs]
}

async function main() {
  const arg = process.argv[2] || 'all'
  const targets = arg === 'all' ? await listTenantDbs() : [arg]
  for (const db of targets) {
    try {
      await repairDb(db)
    } catch (e) {
      console.error(`[fail] ${db}: ${e.message}`)
    }
  }
  console.log('Done.')
}

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