ylrz_saas_pharmacy/adminui/scripts/seed-lobster-channel-data.js

const mysql = require('mysql2/promise')

const BUILTIN = ['QW', 'WX', 'IM']

/** Keep in sync with ChannelTypeRegistry.registerDefaults() */
const ALL_CHANNELS = [
  'QW', 'WX', 'IM',
  'WHATSAPP', 'LINE', 'TELEGRAM', 'APP_IM',
  'TMALL', 'JD', 'DOUYIN_DM', 'KUAISHOU_DM', 'XIAOHONGSHU_DM', 'DOUYIN_EC', 'OTHER'
]

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 seedMasterGrants(conn) {
  const [result] = await conn.query(`
    INSERT IGNORE INTO lobster_tenant_channel_grant (tenant_id, channel_type, granted, create_by)
    SELECT t.id, ch.channel_type, 1, 'seed'
    FROM tenant_info t
    CROSS JOIN (
      SELECT 'QW' AS channel_type
      UNION ALL SELECT 'WX'
      UNION ALL SELECT 'IM'
    ) ch
    WHERE t.id IS NOT NULL
  `)
  return result.affectedRows || 0
}

async function seedTenantPluginConfig(conn) {
  let inserted = 0
  for (const channelType of ALL_CHANNELS) {
    const enabled = BUILTIN.includes(channelType) ? 1 : 0
    const [result] = await conn.query(
      `INSERT IGNORE INTO lobster_channel_plugin_config (company_id, channel_type, enabled, config_json)
       VALUES (0, ?, ?, '{}')`,
      [channelType, enabled]
    )
    inserted += result.affectedRows || 0
  }
  return inserted
}

async function ensureChannelAccountTable(conn) {
  await conn.query(`
    CREATE TABLE IF NOT EXISTS lobster_channel_account (
      id            BIGINT AUTO_INCREMENT PRIMARY KEY,
      company_id    BIGINT       NOT NULL COMMENT 'tenant-level uses 0',
      channel_type  VARCHAR(30)  NOT NULL COMMENT 'WHATSAPP/TMALL/DOUYIN_DM/...',
      account_code  VARCHAR(64)  NOT NULL COMMENT 'unique code within channel',
      account_name  VARCHAR(128) DEFAULT NULL,
      config_json   TEXT,
      enabled       TINYINT      DEFAULT 1,
      is_default    TINYINT      DEFAULT 0,
      sort_order    INT          DEFAULT 0,
      create_time   DATETIME     DEFAULT CURRENT_TIMESTAMP,
      update_time   DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uk_company_channel_account (company_id, channel_type, account_code),
      KEY idx_company_channel (company_id, channel_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `)
  const [result] = await conn.query(`
    INSERT IGNORE INTO lobster_channel_account (
      company_id, channel_type, account_code, account_name, config_json, enabled, is_default, sort_order
    )
    SELECT
      p.company_id,
      p.channel_type,
      'default',
      CONCAT(p.channel_type, '-default'),
      COALESCE(NULLIF(p.config_json, ''), '{}'),
      COALESCE(p.enabled, 0),
      1,
      0
    FROM lobster_channel_plugin_config p
    WHERE p.channel_type NOT IN ('QW', 'WX', 'IM')
  `)
  return result.affectedRows || 0
}

async function main() {
  const masterConn = await mysql.createConnection({ ...DB_CFG, database: 'ylrz_saas' })
  const tenantDbs = new Set()

  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) tenantDbs.add(name)
    }
  } catch (e) {
    console.warn('tenant_info query skipped:', e.message)
  }

  const grantInserted = await seedMasterGrants(masterConn)
  const [grantCount] = await masterConn.query(
    'SELECT COUNT(*) AS c FROM lobster_tenant_channel_grant WHERE granted = 1'
  )
  console.log(`master(ylrz_saas): grant inserted=${grantInserted}, total granted rows=${grantCount[0].c}`)
  await masterConn.end()

  for (const db of tenantDbs) {
    try {
      const conn = await mysql.createConnection({ ...DB_CFG, database: db })
      const n = await seedTenantPluginConfig(conn)
      const migrated = await ensureChannelAccountTable(conn)
      const [rows] = await conn.query(
        'SELECT COUNT(*) AS c FROM lobster_channel_plugin_config WHERE company_id = 0'
      )
      const [accRows] = await conn.query(
        'SELECT COUNT(*) AS c FROM lobster_channel_account WHERE company_id = 0'
      )
      await conn.end()
      console.log(`${db}: plugin inserted=${n}, account migrated=${migrated}, plugin rows=${rows[0].c}, account rows=${accRows[0].c}`)
    } catch (e) {
      console.error(`${db}: ERROR ${e.message}`)
    }
  }
}

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