import knex, { Knex } from 'knex'
import { env } from '../config/env'
import { logger as rootLogger } from '../config/logger'

/**
 * Instância Knex compartilhada entre todos os plugins.
 * Usa o mesmo DATABASE_URL do Prisma — mesmo banco, mesma conexão pool.
 * Prisma cuida das tabelas core; Knex cuida das tabelas dinâmicas dos plugins.
 */
function createKnex(): Knex {
  // DATABASE_URL formato: postgresql://user:pass@host:port/db
  const connection = env.DATABASE_URL

  const instance = knex({
    client: 'pg',
    connection,
    pool: { min: 1, max: 5 },
    acquireConnectionTimeout: 10000,
  })

  instance.on('query-error', (err: Error, query: { sql: string }) => {
    rootLogger.error({ err, sql: query.sql }, '[Knex] Query error')
  })

  return instance
}

export const db = createKnex()

/** Garante que a tabela de log do janitor existe (usada por UnifiedStorageProvider) */
export async function ensurePluginTables(knexInstance: Knex): Promise<void> {
  const hasJanitorLogs = await knexInstance.schema.hasTable('janitor_logs')
  if (!hasJanitorLogs) {
    await knexInstance.schema.createTable('janitor_logs', (t) => {
      t.increments('id').primary()
      t.string('filename').notNullable()
      t.string('category').notNullable()
      t.string('status').notNullable()
      t.text('message').nullable()
      t.timestamps(true, true)
    })
    rootLogger.info('[Knex] Tabela janitor_logs criada')
  }

  const hasNanobanaUsage = await knexInstance.schema.hasTable('nanobana_usage')
  if (!hasNanobanaUsage) {
    await knexInstance.schema.createTable('nanobana_usage', (t) => {
      t.increments('id').primary()
      t.string('partner_id').notNullable()
      t.string('type').notNullable()
      t.timestamps(true, true)
    })
    rootLogger.info('[Knex] Tabela nanobana_usage criada')
  }
}
