/**
 * Development current-schema applier.
 *
 * The project has not shipped production data yet, so dev/test databases are
 * initialized from current table definitions.
 */

import { DbConfigError } from './errors.js'

/**
 * @param {import('knex').Knex} db
 * @param {Array<[string, Function]>} targetTables
 * @returns {Promise<string[]>} created table names
 */
export async function applyCurrentSchema(db, targetTables) {
  if (!db || !db.schema) throw new DbConfigError('applyCurrentSchema: db handle is required')
  assertTargetTables(targetTables, 'applyCurrentSchema')

  const created = []
  for (const [tableName, buildTable] of targetTables) {
    if (!(await db.schema.hasTable(tableName))) {
      await db.schema.createTable(tableName, (table) => buildTable(table, db))
      created.push(tableName)
    }
  }
  return created
}

/**
 * Development-only schema sync.
 *
 * Non-destructive by design:
 * - create missing tables
 * - add missing columns to existing tables
 * - never drop target tables or truncate data
 *
 * @param {import('knex').Knex} db
 * @param {Array<[string, Function]>} targetTables
 * @returns {Promise<{ createdTables: string[], alteredTables: Array<{ tableName: string, addedColumns: string[] }> }>}
 */
export async function syncCurrentSchema(db, targetTables) {
  if (!db || !db.schema) throw new DbConfigError('syncCurrentSchema: db handle is required')
  assertTargetTables(targetTables, 'syncCurrentSchema')

  const createdTables = []
  const alteredTables = []
  for (const [tableName, buildTable] of targetTables) {
    if (!(await db.schema.hasTable(tableName))) {
      await db.schema.createTable(tableName, (table) => buildTable(table, db))
      createdTables.push(tableName)
      continue
    }
    const addedColumns = await addMissingColumns(db, tableName, buildTable)
    if (addedColumns.length > 0) alteredTables.push({ tableName, addedColumns })
  }
  return { createdTables, alteredTables }
}

/**
 * @param {Array<[string, Function]>} targetTables
 * @param {string} caller
 */
function assertTargetTables(targetTables, caller) {
  if (!Array.isArray(targetTables)) throw new DbConfigError(`${caller}: targetTables must be an array`)
  for (const [tableName, buildTable] of targetTables) {
    if (!tableName || typeof buildTable !== 'function') {
      throw new DbConfigError(`${caller}: invalid table definition`)
    }
  }
}

/**
 * @param {import('knex').Knex} db
 * @param {string} tableName
 * @param {Function} buildTable
 * @returns {Promise<string[]>}
 */
async function addMissingColumns(db, tableName, buildTable) {
  const client = db.client && db.client.config && db.client.config.client
  if (client !== 'mysql' && client !== 'mysql2') return []

  const existing = await db(tableName).columnInfo()
  const shadowName = shadowTableName()
  await db.schema.dropTableIfExists(shadowName)
  try {
    await createShadowTable(db, shadowName, buildTable)
    const shadow = await db(shadowName).columnInfo()
    const columnSql = await readCreateTableColumns(db, shadowName)
    const added = []
    for (const columnName of Object.keys(shadow)) {
      if (existing[columnName]) continue
      const definition = columnSql.get(columnName)
      if (!definition) continue
      await db.raw(`ALTER TABLE ${quoteIdent(tableName)} ADD COLUMN ${definition}`)
      added.push(columnName)
    }
    return added
  } finally {
    await db.schema.dropTableIfExists(shadowName)
  }
}

/**
 * MySQL constraint names are unique within a schema. A shadow table therefore
 * cannot reuse the named constraints already owned by the real table.
 *
 * @param {import('knex').Knex} db
 * @param {string} shadowName
 * @param {Function} buildTable
 */
async function createShadowTable(db, shadowName, buildTable) {
  const statements = db.schema
    .createTable(shadowName, (table) => buildTable(table, db))
    .toSQL()

  let constraintIndex = 0
  for (const statement of statements) {
    const sql = statement.sql.replace(/\bconstraint\s+`[^`]+`/gi, () => {
      const name = `${shadowName}_c${constraintIndex}`
      constraintIndex += 1
      return `constraint ${quoteIdent(name)}`
    })
    await db.raw(sql, statement.bindings)
  }
}

/**
 * @param {import('knex').Knex} db
 * @param {string} tableName
 * @returns {Promise<Map<string, string>>}
 */
async function readCreateTableColumns(db, tableName) {
  const [rows] = await db.raw(`SHOW CREATE TABLE ${quoteIdent(tableName)}`)
  const createSql = rows && rows[0] && rows[0]['Create Table']
  if (!createSql) return new Map()
  const map = new Map()
  for (const rawLine of createSql.split('\n')) {
    const line = rawLine.trim().replace(/,$/, '')
    const match = line.match(/^`([^`]+)`\s+/)
    if (match) map.set(match[1], line)
  }
  return map
}

/**
 * @param {string} tableName
 */
function shadowTableName() {
  const suffix = Math.random().toString(36).slice(2, 10)
  return `__s_${suffix}`
}

/**
 * @param {string} name
 */
function quoteIdent(name) {
  if (!/^[A-Za-z0-9_]+$/.test(name)) throw new DbConfigError(`invalid SQL identifier: ${name}`)
  return `\`${name}\``
}