/**
 * Sync lobster menus on master DB (ylrz_saas):
 *   tenant_sys_menu  = tenant admin template (includes semantic-keywords 29933)
 *   tenant_company_menu = sales template (excludes 29933)
 * Usage: node scripts/sync-lobster-menus-master.js [audit|sync]
 */
const mysql = require('mysql2/promise')
const { LOBSTER_MENUS, TENANT_COMPANY_MENU_EXCLUDE } = require('./lobster-menu-definitions')

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

const TENANT_COMPANY_MENU_IDS = LOBSTER_MENUS.filter(m => !TENANT_COMPANY_MENU_EXCLUDE.has(m.id)).map(m => m.id)

async function upsertTenantSysMenu(conn, m) {
  const [ex] = await conn.query('SELECT menu_id FROM tenant_sys_menu WHERE menu_id = ?', [m.id])
  if (!ex.length) {
    await conn.query(
      `INSERT INTO tenant_sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, '0', 0, 0, ?, 'admin', NOW(), 'lobster master sync')`,
      [m.id, m.name, m.parent, m.order, m.path, m.component, m.type, m.icon, m.visible, m.perms]
    )
    return 'insert'
  }
  await conn.query(
    `UPDATE tenant_sys_menu SET menu_name=?, parent_id=?, order_num=?, path=?, component=?, menu_type=?, icon=?, visible=?, status='0', perms=?, update_time=NOW() WHERE menu_id=?`,
    [m.name, m.parent, m.order, m.path, m.component, m.type, m.icon, m.visible, m.perms, m.id]
  )
  return 'update'
}

async function upsertTenantCompanyMenu(conn, m) {
  const [ex] = await conn.query('SELECT menu_id FROM tenant_company_menu WHERE menu_id = ? LIMIT 1', [m.id])
  if (!ex.length) {
    await conn.query(
      `INSERT INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark)
       VALUES (?, ?, ?, ?, ?, ?, 0, 0, ?, ?, '0', ?, ?, 'admin', NOW(), NULL, 'lobster master sync')`,
      [m.id, m.name, m.parent, m.order, m.path, m.component, m.type, m.visible, m.perms, m.icon]
    )
    return 'insert'
  }
  await conn.query(
    `UPDATE tenant_company_menu SET menu_name=?, parent_id=?, order_num=?, path=?, component=?, menu_type=?, icon=?, visible=?, status='0', perms=?, update_time=NOW() WHERE menu_id=?`,
    [m.name, m.parent, m.order, m.path, m.component, m.type, m.icon, m.visible, m.perms, m.id]
  )
  return 'update'
}

async function audit(conn) {
  const ids = LOBSTER_MENUS.map(m => m.id)
  const [sysRows] = await conn.query(
    `SELECT menu_id, menu_name, path, component, visible FROM tenant_sys_menu WHERE menu_id IN (${ids.join(',')}) ORDER BY order_num, menu_id`
  )
  const [cmRows] = await conn.query(
    `SELECT menu_id, menu_name, path FROM tenant_company_menu WHERE menu_id IN (${TENANT_COMPANY_MENU_IDS.join(',')}) ORDER BY order_num, menu_id`
  )
  console.log('\n=== tenant_sys_menu === count', sysRows.length, '/', LOBSTER_MENUS.length)
  sysRows.forEach(r => console.log(`  ${r.menu_id}\t${r.menu_name}\t${r.path}`))
  const missSys = LOBSTER_MENUS.filter(m => !sysRows.find(r => r.menu_id === m.id))
  if (missSys.length) console.log('missing tenant_sys_menu:', missSys.map(m => m.id + ':' + m.name).join(', '))

  const [badCm] = await conn.query(`SELECT menu_id, menu_name FROM tenant_company_menu WHERE menu_id = 29933`)
  if (badCm.length) console.log('WARN tenant_company_menu still has 29933:', badCm)

  console.log('\n=== tenant_company_menu === count', cmRows.length, '/', TENANT_COMPANY_MENU_IDS.length)
  const missCm = TENANT_COMPANY_MENU_IDS.filter(id => !cmRows.find(r => r.menu_id === id))
  if (missCm.length) console.log('missing tenant_company_menu:', missCm.join(', '))
}

async function sync(conn) {
  await conn.query('DELETE FROM tenant_company_menu WHERE menu_id = 29933 OR path = \'semantic-keywords\' OR component = \'lobster/semantic-keywords/index\'')

  let sysIns = 0
  let sysUpd = 0
  let cmIns = 0
  let cmUpd = 0
  for (const m of LOBSTER_MENUS) {
    const r = await upsertTenantSysMenu(conn, m)
    if (r === 'insert') sysIns++
    else sysUpd++
  }
  for (const id of TENANT_COMPANY_MENU_IDS) {
    const m = LOBSTER_MENUS.find(x => x.id === id)
    const companyMenu = id === 29934 ? { ...m, perms: 'workflow:lobster:query' } : m
    const r = await upsertTenantCompanyMenu(conn, companyMenu)
    if (r === 'insert') cmIns++
    else cmUpd++
  }
  await conn.query(`UPDATE tenant_sys_menu SET visible='1' WHERE menu_id IN (29936,29922,29948,29926)`)
  await conn.query(`UPDATE tenant_company_menu SET visible='1' WHERE menu_id IN (29922,29948,29926)`)
  console.log(`\nSync: tenant_sys_menu +${sysIns}/~${sysUpd}, tenant_company_menu +${cmIns}/~${cmUpd}`)
}

async function main() {
  const mode = process.argv[2] || 'sync'
  const conn = await mysql.createConnection(DB_CFG)
  console.log('DB: ylrz_saas mode:', mode)
  await audit(conn)
  if (mode === 'sync') {
    await sync(conn)
    await audit(conn)
  }
  await conn.end()
}

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