import { createConnection } from 'mysql2/promise'
import { normalizeHeroSettings, type HeroSettings } from '@/lib/heroConfig'

let settingsTableReady: Promise<void> | null = null

function openConnection() {
  return createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    port: Number(process.env.DB_PORT || 3306),
  })
}

function ensureSettingsTable() {
  if (!settingsTableReady) {
    settingsTableReady = (async () => {
      const connection = await openConnection()
      try {
        await connection.execute(`
          CREATE TABLE IF NOT EXISTS app_settings (
            \`key\` VARCHAR(100) NOT NULL PRIMARY KEY,
            value JSON NOT NULL,
            updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `)
      } finally {
        await connection.end()
      }
    })().catch((error) => {
      settingsTableReady = null
      throw error
    })
  }
  return settingsTableReady
}

export async function getHeroSettings(): Promise<HeroSettings> {
  await ensureSettingsTable()
  const connection = await openConnection()
  try {
    const [rows] = await connection.execute("SELECT value FROM app_settings WHERE `key` = 'hero_slides' LIMIT 1")
    const row = (rows as Array<{ value: string | object }>)[0]
    if (!row) {
      const settings = normalizeHeroSettings(null)
      await connection.execute(`
        INSERT INTO app_settings (\`key\`, value, updated_at) VALUES ('hero_slides', ?, NOW(3))
        ON DUPLICATE KEY UPDATE \`key\` = VALUES(\`key\`)
      `, [JSON.stringify(settings)])
      return settings
    }
    try {
      return normalizeHeroSettings(typeof row.value === 'string' ? JSON.parse(row.value) : row.value)
    } catch {
      return normalizeHeroSettings(null)
    }
  } finally {
    await connection.end()
  }
}