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

export interface ResourceRelationship {
  id: string
  sourceResourceId: string
  targetResourceId: string
  relationshipType: string
  metadata: Record<string, unknown>
  createdAt: Date
}

interface DatabaseRow {
  id: string
  source_resource_id: string
  target_resource_id: string
  relationship_type: string
  metadata: Record<string, unknown> | null
  created_at: Date
  [key: string]: unknown
}

interface ResourceInventoryRow {
  id: string
  resource_type: string
  provider: string
  name: string
  region: string | null
  metadata: Record<string, unknown> | null
  [key: string]: unknown
}

export interface ResourceGraphNode {
  id: string
  resourceType: string
  provider: string
  name: string
  region?: string
  metadata: Record<string, unknown>
}

export interface ResourceGraphEdge {
  id: string
  source: string
  target: string
  relationshipType: string
  metadata: Record<string, unknown>
}

export interface ResourceGraph {
  nodes: ResourceGraphNode[]
  edges: ResourceGraphEdge[]
}

export interface GraphQuery {
  provider?: string
  resourceType?: string
  region?: string
  siteId?: string
  relationshipType?: string
  maxDepth?: number
}

export async function createResourceRelationship(
  context: Context,
  sourceResourceId: string,
  targetResourceId: string,
  relationshipType: string,
  metadata?: Record<string, unknown>
): Promise<ResourceRelationship> {
  const db = context.db
  const result = await db.query(
    `INSERT INTO resource_relationships (
      source_resource_id, target_resource_id, relationship_type, metadata
    ) VALUES ($1, $2, $3, $4)
    ON CONFLICT (source_resource_id, target_resource_id, relationship_type) 
    DO UPDATE SET metadata = $4
    RETURNING *`,
    [
      sourceResourceId,
      targetResourceId,
      relationshipType,
      JSON.stringify(metadata || {}),
    ]
  )

  return mapRelationship(result.rows[0])
}

export async function deleteResourceRelationship(
  context: Context,
  id: string
): Promise<boolean> {
  const db = context.db
  await db.query('DELETE FROM resource_relationships WHERE id = $1', [id])
  return true
}

export async function getResourceGraph(
  context: Context,
  query?: GraphQuery
): Promise<ResourceGraph> {
  const db = context.db

  // Build query for nodes
  let nodeQuery = 'SELECT * FROM resource_inventory WHERE 1=1'
  const nodeParams: unknown[] = []
  let paramCount = 1

  if (query?.provider) {
    nodeQuery += ` AND provider = $${paramCount}`
    nodeParams.push(query.provider)
    paramCount++
  }

  if (query?.resourceType) {
    nodeQuery += ` AND resource_type = $${paramCount}`
    nodeParams.push(query.resourceType)
    paramCount++
  }

  if (query?.region) {
    nodeQuery += ` AND region = $${paramCount}`
    nodeParams.push(query.region)
    paramCount++
  }

  if (query?.siteId) {
    nodeQuery += ` AND site_id = $${paramCount}`
    nodeParams.push(query.siteId)
    paramCount++
  }

  const nodeResult = await db.query(nodeQuery, nodeParams)
  const nodes = nodeResult.rows.map(mapGraphNode)

  // Build query for edges
  let edgeQuery = `
    SELECT r.*, 
           src.provider as source_provider,
           src.resource_type as source_type,
           src.name as source_name,
           tgt.provider as target_provider,
           tgt.resource_type as target_type,
           tgt.name as target_name
    FROM resource_relationships r
    INNER JOIN resource_inventory src ON r.source_resource_id = src.id
    INNER JOIN resource_inventory tgt ON r.target_resource_id = tgt.id
    WHERE 1=1
  `
  const edgeParams: unknown[] = []
  paramCount = 1

  if (query?.relationshipType) {
    edgeQuery += ` AND r.relationship_type = $${paramCount}`
    edgeParams.push(query.relationshipType)
    paramCount++
  }

  // Filter edges based on node filters
  if (query?.provider) {
    edgeQuery += ` AND (src.provider = $${paramCount} OR tgt.provider = $${paramCount})`
    edgeParams.push(query.provider)
    paramCount++
  }

  if (query?.resourceType) {
    edgeQuery += ` AND (src.resource_type = $${paramCount} OR tgt.resource_type = $${paramCount})`
    edgeParams.push(query.resourceType)
    paramCount++
  }

  if (query?.region) {
    edgeQuery += ` AND (src.region = $${paramCount} OR tgt.region = $${paramCount})`
    edgeParams.push(query.region)
    paramCount++
  }

  const edgeResult = await db.query(edgeQuery, edgeParams)
  const edges = edgeResult.rows.map(mapGraphEdge)

  return { nodes, edges }
}

export async function getResourceDependencies(
  context: Context,
  resourceId: string,
  maxDepth: number = 3
): Promise<ResourceGraphNode[]> {
  const db = context.db

  // Use recursive CTE to find all dependencies
  const query = `
    WITH RECURSIVE dependencies AS (
      -- Base case: direct dependencies
      SELECT target_resource_id, 1 as depth
      FROM resource_relationships
      WHERE source_resource_id = $1
      
      UNION
      
      -- Recursive case: dependencies of dependencies
      SELECT r.target_resource_id, d.depth + 1
      FROM resource_relationships r
      INNER JOIN dependencies d ON r.source_resource_id = d.target_resource_id
      WHERE d.depth < $2
    )
    SELECT DISTINCT ri.*
    FROM dependencies d
    INNER JOIN resource_inventory ri ON d.target_resource_id = ri.id
    ORDER BY ri.name
  `

  const result = await db.query(query, [resourceId, maxDepth])
  return result.rows.map(mapGraphNode)
}

export async function getResourceDependents(
  context: Context,
  resourceId: string,
  maxDepth: number = 3
): Promise<ResourceGraphNode[]> {
  const db = context.db

  // Use recursive CTE to find all dependents
  const query = `
    WITH RECURSIVE dependents AS (
      -- Base case: direct dependents
      SELECT source_resource_id, 1 as depth
      FROM resource_relationships
      WHERE target_resource_id = $1
      
      UNION
      
      -- Recursive case: dependents of dependents
      SELECT r.source_resource_id, d.depth + 1
      FROM resource_relationships r
      INNER JOIN dependents d ON r.target_resource_id = d.source_resource_id
      WHERE d.depth < $2
    )
    SELECT DISTINCT ri.*
    FROM dependents d
    INNER JOIN resource_inventory ri ON d.source_resource_id = ri.id
    ORDER BY ri.name
  `

  const result = await db.query(query, [resourceId, maxDepth])
  return result.rows.map(mapGraphNode)
}

export async function findResourcePath(
  context: Context,
  sourceResourceId: string,
  targetResourceId: string,
  maxDepth: number = 10
): Promise<ResourceGraphEdge[]> {
  const db = context.db

  // Use recursive CTE to find path between resources
  const query = `
    WITH RECURSIVE path AS (
      -- Base case: direct relationship
      SELECT source_resource_id, target_resource_id, relationship_type, metadata, id, 1 as depth
      FROM resource_relationships
      WHERE source_resource_id = $1
      
      UNION
      
      -- Recursive case: extend path
      SELECT r.source_resource_id, r.target_resource_id, r.relationship_type, r.metadata, r.id, p.depth + 1
      FROM resource_relationships r
      INNER JOIN path p ON r.source_resource_id = p.target_resource_id
      WHERE p.depth < $3 AND r.target_resource_id != $1
    )
    SELECT * FROM path
    WHERE target_resource_id = $2
    ORDER BY depth
    LIMIT 1
  `

  const result = await db.query(query, [sourceResourceId, targetResourceId, maxDepth])
  return result.rows.map(mapGraphEdge)
}

export async function getResourcesByProvider(
  context: Context,
  provider: string
): Promise<ResourceGraphNode[]> {
  const db = context.db
  const result = await db.query(
    'SELECT * FROM resource_inventory WHERE provider = $1',
    [provider]
  )
  return result.rows.map(mapGraphNode)
}

export async function getResourcesByRegion(
  context: Context,
  region: string
): Promise<ResourceGraphNode[]> {
  const db = context.db
  const result = await db.query(
    'SELECT * FROM resource_inventory WHERE region = $1',
    [region]
  )
  return result.rows.map(mapGraphNode)
}

export async function getResourcesByTags(
  context: Context,
  tags: string[]
): Promise<ResourceGraphNode[]> {
  const db = context.db
  const result = await db.query(
    'SELECT * FROM resource_inventory WHERE tags @> $1::jsonb',
    [JSON.stringify(tags)]
  )
  return result.rows.map(mapGraphNode)
}

function mapRelationship(row: DatabaseRow): ResourceRelationship {
  return {
    id: row.id,
    sourceResourceId: row.source_resource_id,
    targetResourceId: row.target_resource_id,
    relationshipType: row.relationship_type,
    metadata: (row.metadata as Record<string, unknown>) || {},
    createdAt: row.created_at,
  }
}

function mapGraphNode(row: ResourceInventoryRow): ResourceGraphNode {
  return {
    id: row.id,
    resourceType: row.resource_type,
    provider: row.provider,
    name: row.name,
    region: row.region || undefined,
    metadata: (row.metadata as Record<string, unknown>) || {},
  }
}

function mapGraphEdge(row: DatabaseRow): ResourceGraphEdge {
  return {
    id: row.id,
    source: row.source_resource_id,
    target: row.target_resource_id,
    relationshipType: row.relationship_type,
    metadata: (row.metadata as Record<string, unknown>) || {},
  }
}

