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
}

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

const CORE_SQL = `
DELETE FROM sys_role_menu WHERE menu_id IN (3000, 29924, 2621);
DELETE FROM sys_menu WHERE menu_id IN (3000, 29924) OR component = 'lobster/dedup-config/index' OR path IN ('lobster-dedup-config', 'dedup-config');
INSERT IGNORE INTO 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
(2621, '\u804a\u5929\u6d88\u606f\u53bb\u91cd', 2600, 15, 'messageDedup', 'admin/messageDedup/index', 'C', 'el-icon-filter', '0', '0', 0, 0, NULL, 'admin', NOW(), 'Lobster outbound message dedup policy');
UPDATE sys_menu SET perms = NULL WHERE menu_id = 2621;
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT DISTINCT rm.role_id, 2621
FROM sys_role_menu rm
INNER JOIN sys_menu m ON m.menu_id = rm.menu_id
WHERE m.menu_id >= 2000 AND m.menu_id < 3000
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = rm.role_id AND x.menu_id = 2621);
`

const TENANT_TEMPLATE_CLEANUP = [
  ['tenant_sys_role_menu', 'menu_id IN (3000, 29924)'],
  ['tenant_company_role_menu', 'menu_id IN (3000, 29924)'],
  ['tenant_sys_menu', "menu_id IN (3000, 29924) OR component = 'lobster/dedup-config/index'"],
  ['tenant_company_menu', "menu_id IN (3000, 29924) OR component = 'lobster/dedup-config/index' OR path = 'dedup-config'"]
]

const TENANT_SQL = `
DELETE FROM sys_role_menu WHERE menu_id = 29924;
DELETE FROM company_role_menu WHERE menu_id = 29924;
DELETE FROM company_menu WHERE menu_id = 29924 OR path = 'dedup-config' OR component = 'lobster/dedup-config/index';
DELETE FROM sys_menu WHERE menu_id = 29924 OR path = 'dedup-config' OR component = 'lobster/dedup-config/index';
`

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

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

async function main() {
  const masterConn = await mysql.createConnection({ ...DB_CFG, database: MASTER_DB })
  await masterConn.query(CORE_SQL)
  for (const [table, where] of TENANT_TEMPLATE_CLEANUP) {
    if (await tableExists(masterConn, table)) {
      await masterConn.query(`DELETE FROM ${table} WHERE ${where}`)
    }
  }
  const [menuRows] = await masterConn.query(
    'SELECT menu_id, menu_name, parent_id, path, component FROM sys_menu WHERE menu_id = 2621'
  )
  console.log('master menu 2621:', menuRows[0] || 'NOT FOUND')

  const dbs = new Set()
  try {
    const [tenants] = await masterConn.query(
      'SELECT 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 skipped:', e.message)
  }
  await masterConn.end()

  for (const db of dbs) {
    const conn = await mysql.createConnection({ ...DB_CFG, database: db })
    try {
      if (!(await tableExists(conn, 'sys_menu'))) continue
      await conn.query(TENANT_SQL)
      console.log(`${db}: tenant dedup menu removed`)
    } catch (e) {
      console.error(`${db}: ${e.message}`)
    } finally {
      await conn.end()
    }
  }
}

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