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

export async function getPillars(context: Context) {
  const db = context.db
  const result = await db.query('SELECT * FROM pillars ORDER BY code ASC')
  return result.rows.map(mapPillar)
}

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

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

  const pillar = mapPillar(result.rows[0])
  
  // Get controls for this pillar
  const controlsResult = await db.query(
    'SELECT * FROM controls WHERE pillar_id = $1 ORDER BY code ASC',
    [pillar.id]
  )
  pillar.controls = controlsResult.rows.map(mapControl)

  return pillar
}

export interface FindingsFilter {
  controlId?: string
  resourceId?: string
  status?: string
  severity?: string
}

interface FindingRow {
  id: string
  control_id: string
  resource_id: string | null
  status: string
  severity: string
  description: string | null
  remediation: string | null
  created_at: Date
  updated_at: Date
  [key: string]: unknown
}

interface RiskRow {
  id: string
  resource_id: string | null
  severity: string
  description: string | null
  mitigation: string | null
  created_at: Date
  updated_at: Date
  [key: string]: unknown
}

interface PillarRow {
  id: string
  code: string
  name: string
  description: string | null
  [key: string]: unknown
}

interface ControlRow {
  id: string
  code: string
  name: string
  description: string | null
  pillar_id: string
  [key: string]: unknown
}

export async function getFindings(context: Context, filter?: FindingsFilter) {
  const db = context.db
  let query = 'SELECT * FROM findings WHERE 1=1'
  const params: unknown[] = []
  let paramCount = 1

  if (filter?.controlId) {
    query += ` AND control_id = $${paramCount}`
    params.push(filter.controlId)
    paramCount++
  }

  if (filter?.resourceId) {
    query += ` AND resource_id = $${paramCount}`
    params.push(filter.resourceId)
    paramCount++
  }

  if (filter?.status) {
    query += ` AND status = $${paramCount}`
    params.push(filter.status)
    paramCount++
  }

  if (filter?.severity) {
    query += ` AND severity = $${paramCount}`
    params.push(filter.severity)
    paramCount++
  }

  query += ' ORDER BY created_at DESC'

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

export async function getRisks(context: Context, resourceId?: string) {
  const db = context.db
  let query = 'SELECT * FROM risks WHERE 1=1'
  const params: unknown[] = []
  let paramCount = 1

  if (resourceId) {
    query += ` AND resource_id = $${paramCount}`
    params.push(resourceId)
  }

  query += ' ORDER BY severity DESC, created_at DESC'

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

function mapPillar(row: PillarRow) {
  return {
    id: row.id,
    code: row.code,
    name: row.name,
    description: row.description || null,
    controls: [],
  }
}

function mapControl(row: ControlRow) {
  return {
    id: row.id,
    code: row.code,
    name: row.name,
    description: row.description || null,
    pillar: null, // Will be set by caller if needed
    findings: [],
  }
}

function mapFinding(row: FindingRow) {
  return {
    id: row.id,
    control: null, // Will be populated by resolver
    resource: null, // Will be populated by resolver
    status: row.status,
    severity: row.severity,
    title: (row as { title?: string }).title || '',
    description: row.description || null,
    recommendation: row.remediation || null,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  }
}

function mapRisk(row: RiskRow) {
  return {
    id: row.id,
    resource: null, // Will be populated by resolver
    pillar: null, // Will be populated by resolver
    severity: row.severity,
    title: (row as { title?: string }).title || '',
    description: row.description || null,
    mitigation: row.mitigation || null,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  }
}

