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'

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

async function runDrop(conn, label) {
  const tables = ['sys_role_menu', 'company_role_menu', 'company_menu', 'sys_menu']
  for (const table of tables) {
    const [exists] = await conn.query('SHOW TABLES LIKE ?', [table])
    if (!exists.length) continue
    if (table.includes('role_menu')) {
      await conn.query(`DELETE FROM ${table} WHERE menu_id = 29947`)
    } else {
      await conn.query(
        `DELETE FROM ${table} WHERE menu_id = 29947 OR path = 'execution-config' OR component = 'lobster/execution-config/index'`
      )
    }
  }
  const count = async (table, where) => {
    const [exists] = await conn.query('SHOW TABLES LIKE ?', [table])
    if (!exists.length) return 'NA'
    const [rows] = await conn.query(`SELECT COUNT(*) AS c FROM ${table} WHERE ${where}`)
    return rows[0].c
  }
  console.log(
    `${label}: sys_menu=${await count('sys_menu', "menu_id = 29947 OR path = 'execution-config'")}, ` +
    `company_menu=${await count('company_menu', "menu_id = 29947 OR path = 'execution-config'")}`
  )
}

async function runMasterTemplateDrop(conn) {
  const tables = ['tenant_sys_role_menu', 'tenant_company_role_menu', 'tenant_company_menu', 'tenant_sys_menu']
  for (const table of tables) {
    const [exists] = await conn.query('SHOW TABLES LIKE ?', [table])
    if (!exists.length) continue
    if (table.includes('role_menu')) {
      await conn.query(`DELETE FROM ${table} WHERE menu_id = 29947`)
    } else {
      await conn.query(
        `DELETE FROM ${table} WHERE menu_id = 29947 OR path = 'execution-config' OR component = 'lobster/execution-config/index'`
      )
    }
  }
  const count = async (table, where) => {
    const [exists] = await conn.query('SHOW TABLES LIKE ?', [table])
    if (!exists.length) return 'NA'
    const [rows] = await conn.query(`SELECT COUNT(*) AS c FROM ${table} WHERE ${where}`)
    return rows[0].c
  }
  console.log(
    `master templates: tenant_sys_menu=${await count('tenant_sys_menu', "menu_id = 29947 OR path = 'execution-config'")}, ` +
    `tenant_company_menu=${await count('tenant_company_menu', "menu_id = 29947 OR path = 'execution-config'")}`
  )
}

async function main() {
  const masterConn = await mysql.createConnection({ ...DB_CFG, database: MASTER_DB })
  const dbs = new Set([MASTER_DB])
  try {
    await runMasterTemplateDrop(masterConn)
  } catch (e) {
    console.warn(`master template drop skipped: ${e.message}`)
  }
  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) {
    const conn = await mysql.createConnection({ ...DB_CFG, database: db })
    try {
      const [hasSys] = await conn.query("SHOW TABLES LIKE 'sys_menu'")
      const [hasCom] = await conn.query("SHOW TABLES LIKE 'company_menu'")
      if (!hasSys.length && !hasCom.length) {
        console.log(`${db}: skip (no tenant menu tables)`)
        continue
      }
      await runDrop(conn, db)
    } catch (e) {
      console.error(`${db}: ERROR ${e.message}`)
    } finally {
      await conn.end()
    }
  }
}

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