ylrz_saas_pharmacy/adminui/scripts/apply-sales-corpus-task-migration.js

const mysql = require('mysql2/promise')
const fs = require('fs')
const path = require('path')

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

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

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

async function tableExists(conn, table) {
  const [rows] = await conn.query(
    `SELECT COUNT(*) AS cnt FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?`,
    [table]
  )
  return rows[0].cnt > 0
}

async function main() {
  const conn = await mysql.createConnection({ ...DB_CFG, database: TARGET_DB })
  console.log('Applying sales corpus task migration on', TARGET_DB)

  if (!(await tableExists(conn, 'lobster_sales_corpus_task'))) {
    await conn.query(`
      CREATE TABLE lobster_sales_corpus_task (
        id BIGINT NOT NULL AUTO_INCREMENT,
        company_id BIGINT NOT NULL,
        batch_no VARCHAR(64) DEFAULT NULL,
        task_name VARCHAR(200) DEFAULT NULL,
        salesperson_name VARCHAR(100) DEFAULT NULL,
        scenario VARCHAR(100) DEFAULT NULL,
        total_count INT DEFAULT 0,
        qualified_count INT DEFAULT 0,
        optimizable_count INT DEFAULT 0,
        optimized_count INT DEFAULT 0,
        kb_written_count INT DEFAULT 0,
        overall_score DECIMAL(5,2) DEFAULT NULL,
        ai_summary TEXT,
        status VARCHAR(32) DEFAULT 'imported',
        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,
        PRIMARY KEY (id),
        KEY idx_company_status (company_id, status),
        KEY idx_batch_no (batch_no)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `)
    console.log('  created lobster_sales_corpus_task')
  } else {
    console.log('  lobster_sales_corpus_task already exists')
  }

  const alters = [
    ['customer_question', 'ADD COLUMN customer_question TEXT NULL AFTER dialog_text'],
    ['sales_answer', 'ADD COLUMN sales_answer TEXT NULL AFTER customer_question'],
    ['task_id', 'ADD COLUMN task_id BIGINT NULL AFTER company_id'],
    ['salesperson_name', 'ADD COLUMN salesperson_name VARCHAR(100) NULL AFTER task_id'],
    ['status', 'ADD COLUMN status VARCHAR(32) NULL AFTER tags'],
    ['quality_status', 'ADD COLUMN quality_status VARCHAR(32) NULL AFTER status'],
    ['optimized_answer', 'ADD COLUMN optimized_answer TEXT NULL AFTER quality_status'],
    ['ai_feedback', 'ADD COLUMN ai_feedback TEXT NULL AFTER optimized_answer'],
    ['kb_written', 'ADD COLUMN kb_written TINYINT DEFAULT 0 AFTER ai_feedback']
  ]

  for (const [col, sql] of alters) {
    if (!(await columnExists(conn, 'lobster_sales_corpus', col))) {
      await conn.query(`ALTER TABLE lobster_sales_corpus ${sql}`)
      console.log('  added column', col)
    }
  }

  const [idxRows] = await conn.query(
    `SELECT COUNT(*) AS cnt FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_sales_corpus' AND INDEX_NAME = 'idx_task_id'`
  )
  if (idxRows[0].cnt === 0) {
    await conn.query('ALTER TABLE lobster_sales_corpus ADD INDEX idx_task_id (task_id)')
    console.log('  added idx_task_id')
  }

  await conn.end()
  console.log('Done.')
}

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