/**
 * Drop unused lobster summary config menus + company_lobster_summary_config table.
 * Usage:
 *   node scripts/drop-lobster-summary-config.js              # all tenants + master
 *   node scripts/drop-lobster-summary-config.js fs_tenant_cs1  # single tenant only
 */
const fs = require('fs')
const path = require('path')
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,
  charset: 'utf8mb4'
}

const MASTER_DB = process.env.DB_NAME || 'ylrz_saas'
const ROOT = path.join(__dirname, '../../java')
const MASTER_SQL = fs.readFileSync(
  path.join(ROOT, 'sql/master/V20260626_01__drop_lobster_summary_config.sql'),
  'utf8'
)
const TENANT_SQL = fs.readFileSync(
  path.join(ROOT, 'fs-agent/src/main/resources/db/migration/tenant/V20260626_01__drop_lobster_summary_config.sql'),
  'utf8'
)

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

async function runSql(database, sql, label) {
  const conn = await mysql.createConnection({ ...DB_CFG, database })
  try {
    const statements = sql
      .split(';')
      .map(s => s.replace(/--[^\n]*/g, '').trim())
      .filter(Boolean)
    let affected = 0
    for (const stmt of statements) {
      if (!(await canRun(conn, stmt))) continue
      const [r] = await conn.query(stmt)
      affected += r && r.affectedRows ? r.affectedRows : 0
    }
    const [menus] = await conn.query(
      "SELECT COUNT(*) AS c FROM information_schema.tables WHERE table_schema = ? AND table_name IN ('sys_menu','company_menu','tenant_sys_menu','tenant_company_menu')",
      [database]
    )
    let summaryMenu = 0
    if (menus[0].c > 0) {
      const checks = []
      if (await tableExists(conn, 'sys_menu')) {
        checks.push("SELECT COUNT(*) AS c FROM sys_menu WHERE menu_id IN (29923,2999) OR component='lobster/summary-config/index'")
      }
      if (await tableExists(conn, 'company_menu')) {
        checks.push("SELECT COUNT(*) AS c FROM company_menu WHERE menu_id IN (29923,2999) OR component='lobster/summary-config/index'")
      }
      if (await tableExists(conn, 'tenant_sys_menu')) {
        checks.push("SELECT COUNT(*) AS c FROM tenant_sys_menu WHERE menu_id IN (29923,2999) OR component='lobster/summary-config/index'")
      }
      if (await tableExists(conn, 'tenant_company_menu')) {
        checks.push("SELECT COUNT(*) AS c FROM tenant_company_menu WHERE menu_id IN (29923,2999) OR component='lobster/summary-config/index'")
      }
      for (const q of checks) {
        const [r] = await conn.query(q)
        summaryMenu += Number(r[0].c || 0)
      }
    }
    const [tbl] = await conn.query(
      "SELECT COUNT(*) AS c FROM information_schema.tables WHERE table_schema = ? AND table_name = 'company_lobster_summary_config'",
      [database]
    )
    console.log(`[${label}] ${database}: affected=${affected}, summary_menu_left=${summaryMenu}, summary_table=${Number(tbl[0].c)}`)
  } finally {
    await conn.end()
  }
}

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

async function canRun(conn, stmt) {
  const m = stmt.match(/^DELETE\s+FROM\s+([a-zA-Z0-9_]+)/i)
  if (!m) return true
  return tableExists(conn, m[1])
}

async function listTenantDbs() {
  const conn = await mysql.createConnection({ ...DB_CFG, database: MASTER_DB })
  try {
    const [rows] = await conn.query(
      "SELECT id, tenant_code, db_url FROM tenant_info WHERE db_url IS NOT NULL AND db_url <> ''"
    )
    const dbs = new Map()
    for (const t of rows) {
      const name = dbNameFromUrl(t.db_url)
      if (name) dbs.set(name, `${t.id}:${t.tenant_code}`)
    }
    return dbs
  } finally {
    await conn.end()
  }
}

async function main() {
  const onlyTenant = process.argv[2]

  if (!onlyTenant) {
    console.log('=== master ===')
    await runSql(MASTER_DB, MASTER_SQL, 'master')
  }

  const tenantDbs = await listTenantDbs()
  const targets = onlyTenant
    ? [[onlyTenant, onlyTenant]]
    : [...tenantDbs.entries()]

  console.log('=== tenants === count', targets.length)
  for (const [db, label] of targets) {
    try {
      await runSql(db, TENANT_SQL, label)
    } catch (e) {
      console.error(`[FAIL] ${db}: ${e.message}`)
    }
  }
  console.log('Done.')
}

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