import { prisma } from '@/lib/prisma'

let downloadLogsTableEnsured = false

async function executeSafely(sql: string) {
  try {
    await prisma.$executeRawUnsafe(sql)
  } catch (error: any) {
    const message = error?.message || ''
    // 表已存在、扩展已存在等错误可以忽略
    if (
      message.includes('already exists') ||
      message.includes('duplicate key value') ||
      message.includes('extension') // pgcrypto 等扩展相关
    ) {
      return
    }
    console.error('[DownloadLog] Failed to execute SQL safely:', error)
  }
}

export async function ensureMaterialDownloadLogsTable() {
  if (downloadLogsTableEnsured) return

  console.log('[DownloadLog] Ensuring material_download_logs table and indexes...')

  // 确保 pgcrypto 扩展存在（用于 gen_random_uuid）
  await executeSafely(`CREATE EXTENSION IF NOT EXISTS "pgcrypto"`)

  // 创建日志表
  await executeSafely(`
    CREATE TABLE IF NOT EXISTS "material_download_logs" (
      "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      "materialId" TEXT NOT NULL,
      "userId" TEXT NOT NULL,
      "downloadedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `)

  // 索引
  await executeSafely(
    `CREATE INDEX IF NOT EXISTS "material_download_logs_materialId_idx" ON "material_download_logs" ("materialId")`
  )
  await executeSafely(
    `CREATE INDEX IF NOT EXISTS "material_download_logs_userId_idx" ON "material_download_logs" ("userId")`
  )
  await executeSafely(
    `CREATE INDEX IF NOT EXISTS "material_download_logs_downloadedAt_idx" ON "material_download_logs" ("downloadedAt" DESC)`
  )

  // 审计日志保留策略：删除素材时保留下载日志，不允许 materialId 外键级联删除
  await executeSafely('ALTER TABLE "material_download_logs" DROP CONSTRAINT IF EXISTS "material_download_logs_materialId_fkey"')
  await executeSafely('ALTER TABLE "material_download_logs" DROP CONSTRAINT IF EXISTS "MaterialDownloadLog_materialId_fkey"')

  downloadLogsTableEnsured = true
  console.log('[DownloadLog] material_download_logs table ensured.')
}