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

interface SiteRow {
  id: string
  name: string
  region: string
  status: string
  metadata: string | Record<string, unknown> | null
  created_at: Date
  updated_at: Date
  [key: string]: unknown
}

export async function getSites(context: Context) {
  const db = context.db
  let query = 'SELECT * FROM sites WHERE 1=1'
  const params: unknown[] = []
  let paramCount = 1

  // Tenant-aware filtering (superior to Azure)
  if (context.tenantContext) {
    if (context.tenantContext.isSystemAdmin) {
      // System admins can see all sites
    } else if (context.tenantContext.tenantId) {
      // Filter by tenant
      query += ` AND (tenant_id = $${paramCount} OR tenant_id IS NULL)`
      params.push(context.tenantContext.tenantId)
      paramCount++
    } else {
      // Non-tenant users only see system sites
      query += ` AND tenant_id IS NULL`
    }
  } else {
    // Unauthenticated users see nothing
    query += ` AND 1=0`
  }

  query += ' ORDER BY created_at DESC'

  const result = await db.query(query, params)
  return result.rows.map(mapSite)
}

export async function getSite(context: Context, id: string) {
  const db = context.db
  let query = 'SELECT * FROM sites WHERE id = $1'
  const params: unknown[] = [id]
  let paramCount = 2

  // Tenant-aware filtering
  if (context.tenantContext) {
    if (context.tenantContext.isSystemAdmin) {
      // System admins can see all sites
    } else if (context.tenantContext.tenantId) {
      query += ` AND (tenant_id = $${paramCount} OR tenant_id IS NULL)`
      params.push(context.tenantContext.tenantId)
      paramCount++
    } else {
      query += ` AND tenant_id IS NULL`
    }
  } else {
    query += ` AND 1=0`
  }

  const result = await db.query(query, params)

  if (result.rows.length === 0) {
    throw AppErrors.notFound('Site', id)
  }

  return mapSite(result.rows[0])
}

function mapSite(row: SiteRow) {
  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,
    region: row.region,
    status: row.status,
    metadata,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  }
}

