import { Context } from '../types/context'

interface RegionRow {
  id: string
  name: string
  code: string
  country: string
  latitude: string | null
  longitude: string | null
  metadata: string | Record<string, unknown> | null
  created_at: Date
  updated_at: Date
  [key: string]: unknown
}

export async function getRegions(context: Context) {
  const db = context.db
  const result = await db.query('SELECT * FROM regions ORDER BY name ASC')
  return result.rows.map(mapRegion)
}

export async function getRegion(context: Context, id: string) {
  const db = context.db
  const result = await db.query('SELECT * FROM regions WHERE id = $1', [id])

  if (result.rows.length === 0) {
    throw new Error('Region not found')
  }

  return mapRegion(result.rows[0])
}

export async function getRegionByCode(context: Context, code: string) {
  const db = context.db
  const result = await db.query('SELECT * FROM regions WHERE code = $1', [code])

  if (result.rows.length === 0) {
    throw new Error('Region not found')
  }

  return mapRegion(result.rows[0])
}

function mapRegion(row: RegionRow) {
  const metadata = typeof row.metadata === 'string' 
    ? (JSON.parse(row.metadata) as Record<string, unknown>)
    : ((row.metadata as Record<string, unknown>) || {})

  return {
    id: row.id,
    name: row.name,
    code: row.code,
    country: row.country,
    coordinates: row.latitude && row.longitude
      ? { latitude: parseFloat(row.latitude), longitude: parseFloat(row.longitude) }
      : null,
    metadata,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  }
}

