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 ALTER_SQL = `
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_execution_config' AND COLUMN_NAME = 'quality_gate_enabled');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE lobster_execution_config ADD COLUMN quality_gate_enabled tinyint NOT NULL DEFAULT 1 COMMENT ''quality gate enabled'' AFTER auto_start_instance_on_tag',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_execution_config' AND COLUMN_NAME = 'quality_pass_score_percent');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE lobster_execution_config ADD COLUMN quality_pass_score_percent int NOT NULL DEFAULT 75 COMMENT ''runtime pass score percent'' AFTER quality_gate_enabled',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_execution_config' AND COLUMN_NAME = 'quality_target_score_percent');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE lobster_execution_config ADD COLUMN quality_target_score_percent int NOT NULL DEFAULT 75 COMMENT ''e2e target score percent'' AFTER quality_pass_score_percent',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_execution_config' AND COLUMN_NAME = 'quality_min_node_score_percent');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE lobster_execution_config ADD COLUMN quality_min_node_score_percent int NOT NULL DEFAULT 60 COMMENT ''e2e min node score percent'' AFTER quality_target_score_percent',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_execution_config' AND COLUMN_NAME = 'quality_min_e2e_avg_score_percent');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE lobster_execution_config ADD COLUMN quality_min_e2e_avg_score_percent int NOT NULL DEFAULT 75 COMMENT ''e2e min avg score percent'' AFTER quality_min_node_score_percent',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lobster_execution_config' AND COLUMN_NAME = 'quality_multiturn_min_avg_score_percent');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE lobster_execution_config ADD COLUMN quality_multiturn_min_avg_score_percent int NOT NULL DEFAULT 45 COMMENT ''multiturn min avg score percent'' AFTER quality_min_e2e_avg_score_percent',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
`

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

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

async function applyOnDb(conn, label) {
  const [tables] = await conn.query("SHOW TABLES LIKE 'lobster_execution_config'")
  if (!tables.length) {
    console.log(`${label}: skip (no lobster_execution_config table)`)
    return
  }
  await conn.query(ALTER_SQL)
  const ok = await columnExists(conn, 'quality_gate_enabled')
  console.log(`${label}: quality_gate_enabled=${ok ? 'OK' : 'MISSING'}`)
}

async function main() {
  const dbs = new Set(process.argv.slice(2))
  const masterConn = await mysql.createConnection({ ...DB_CFG, database: MASTER_DB })
  if (!dbs.size) {
    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 {
      await applyOnDb(conn, db)
    } catch (e) {
      console.error(`${db}: ${e.message}`)
    } finally {
      await conn.end()
    }
  }
}

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