import pg from 'pg'
import { migrate } from 'postgres-migrations'
import { dirname } from 'path'
import { fileURLToPath } from 'url'

const __filename = fileURLToPath(import.meta.url)
const __dirname = dirname(__filename)

const dbConfig: string | pg.ClientConfig = process.env.DATABASE_URL
  ? process.env.DATABASE_URL
  : {
    host: process.env.DB_HOST ?? '0.0.0.0',
    port: Number(process.env.DB_PORT ?? 5432),
    user: process.env.DB_USER ?? 'yasser',
    password: process.env.DB_PASSWORD ?? '',
    database: process.env.DB_NAME ?? 'perauset',
  }

async function main() {
  // Create database if it doesn't exist (skip when using connection string — DB is pre-provisioned)
  if (typeof dbConfig !== 'string') {
    const client = new pg.Client({ ...dbConfig, database: 'postgres' })
    try {
      await client.connect()
      if (process.env.RESET_DB === 'true') {
        console.log('Dropping database...')
        await client.query(`DROP DATABASE IF EXISTS "${dbConfig.database}"`)
      }
      console.log(`Creating database "${dbConfig.database}"...`)
      await client.query(`CREATE DATABASE "${dbConfig.database}"`)
    } catch (e: any) {
      if (e.code === '42P04') {
        console.log('Database already exists')
      } else {
        throw e
      }
    } finally {
      await client.end()
    }
  }

  // Run migrations
  const client = new pg.Client(dbConfig as any)
  await client.connect()
  console.log('Running migrations...')
  await migrate({ client }, `${__dirname}/../../sql`, { logger: undefined })
  console.log('Migrations complete')
  await client.end()
}

main().catch((e) => {
  console.error('Migration failed:', e)
  process.exit(1)
})