ylrz_saas_pharmacy/adminui/scripts/ensure-lobster-channel-table.js

const mysql = require('mysql2/promise')

const PLUGIN_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS lobster_channel_plugin_config (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  company_id    BIGINT       NOT NULL COMMENT 'tenant-level config uses 0',
  channel_type  VARCHAR(30)  NOT NULL,
  enabled       TINYINT      DEFAULT 0,
  config_json   TEXT,
  create_time   DATETIME     DEFAULT CURRENT_TIMESTAMP,
  update_time   DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_company_channel (company_id, channel_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Lobster channel plugin config'
`

const GRANT_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS lobster_tenant_channel_grant (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id     BIGINT       NOT NULL,
  channel_type  VARCHAR(30)  NOT NULL,
  granted       TINYINT      DEFAULT 1,
  create_by     VARCHAR(64)  DEFAULT '',
  create_time   DATETIME     DEFAULT CURRENT_TIMESTAMP,
  update_by     VARCHAR(64)  DEFAULT '',
  update_time   DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_tenant_channel (tenant_id, channel_type),
  KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Lobster channel grants by platform admin'
`

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

function dbNameFromUrl(url) {
  if (!url) return null
  const m = String(url).match(/\/([^/?]+)(?:\?|$)/)
  return m ? m[1] : null
}

async function ensureTables(database) {
  const conn = await mysql.createConnection({ ...DB_CFG, database })
  await conn.query(PLUGIN_TABLE_SQL)
  await conn.query(GRANT_TABLE_SQL)
  const [p] = await conn.query("SHOW TABLES LIKE 'lobster_channel_plugin_config'")
  const [g] = await conn.query("SHOW TABLES LIKE 'lobster_tenant_channel_grant'")
  await conn.end()
  return { database, plugin: p.length > 0, grant: g.length > 0 }
}

async function main() {
  const masterConn = await mysql.createConnection({ ...DB_CFG, database: 'ylrz_saas' })
  const dbs = new Set(['ylrz_saas'])
  try {
    const [tenants] = await masterConn.query(
      'SELECT id, tenant_code, db_url FROM tenant_info WHERE db_url IS NOT NULL AND db_url != \'\''
    )
    for (const t of tenants) {
      const name = dbNameFromUrl(t.db_url)
      if (name) dbs.add(name)
    }
  } catch (e) {
    console.warn('tenant_info query skipped:', e.message)
  }
  await masterConn.end()

  for (const db of dbs) {
    try {
      const r = await ensureTables(db)
      console.log(`${db}: plugin=${r.plugin ? 'OK' : 'MISSING'} grant=${r.grant ? 'OK' : 'MISSING'}`)
    } catch (e) {
      console.error(`${db}: ERROR ${e.message}`)
    }
  }
}

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