import { neon } from "@neondatabase/serverless"

let sqlClient: ReturnType<typeof neon> | null = null

export function getSql() {
  if (!sqlClient) {
    const dbUrl = process.env.DATABASE_URL || process.env.POSTGRES_URL
    if (!dbUrl) {
      console.error(`
========================================
[DB] DATABASE NOT CONFIGURED
========================================
This app requires a Neon PostgreSQL database.

To fix this error:
1. Copy .env.example to .env.local
2. Set POSTGRES_URL or DATABASE_URL with your Neon connection string
3. Restart the dev server

Get your connection string from:
- Vercel Project Settings > Environment Variables
- Or https://console.neon.tech

Example: POSTGRES_URL=postgresql://user:pass@ep-xxx.neon.tech/db?sslmode=require
========================================
`)
      // Return a function that acts like a tagged template literal but rejects
      const errorFn = () => Promise.reject(new Error("Database not configured. Please set POSTGRES_URL or DATABASE_URL in your .env.local file. See .env.example for details."))
      return Object.assign(errorFn, {
        // Support for tagged template literal calls
        raw: errorFn,
      }) as ReturnType<typeof neon>
    }
    try {
      console.log("[DB] Connecting to Neon database...")
      sqlClient = neon(dbUrl, {
        disableWarningInBrowsers: true,
      })
      console.log("[DB] Database client initialized successfully")
    } catch (error: any) {
      console.error("[DB] Failed to initialize database client:", error.message)
      throw new Error(`Database initialization failed: ${error.message}`)
    }
  }
  return sqlClient
}

export const sql = getSql()

export async function safeQuery<T = any>(query: TemplateStringsArray, ...values: any[]): Promise<T[]> {
  try {
    const result = await sql(query, ...values)
    return Array.isArray(result) ? result : []
  } catch (error: any) {
    if (error.message?.includes("Database not configured")) {
      console.error("[DB] Query error: Database not configured")
      throw error // Re-throw configuration errors so callers know the database isn't available
    }
    console.error("[DB] Query error:", error.message)
    return [] // Return empty array for actual query errors
  }
}

export function handleDbError(error: unknown): never {
  console.error("Database error:", error)
  throw new Error("Une erreur de base de données est survenue")
}

export interface PaginationParams {
  page?: number
  limit?: number
}

export interface PaginatedResult<T> {
  data: T[]
  total: number
  page: number
  limit: number
  totalPages: number
}

export function getPaginationValues(params: PaginationParams) {
  const page = Math.max(1, params.page || 1)
  const limit = Math.min(100, Math.max(1, params.limit || 20))
  const offset = (page - 1) * limit
  return { page, limit, offset }
}

export function formatFCFA(amount: number): string {
  return (
    new Intl.NumberFormat("fr-FR", {
      style: "decimal",
      minimumFractionDigits: 0,
      maximumFractionDigits: 0,
    }).format(amount) + " FCFA"
  )
}
