/**
 * @module custom_cil-config
 * @audience installer
 * @layer api-handler
 * @stability experimental
 *
 * Pass 5 of the CIL ingestion pipeline: configuration field extraction.
 *
 * Scans the repository for config sources (JSON files, .env files, spine.config.json,
 * Supabase migration SQL) and creates `cil_config` items documenting each field.
 * Then scans all cil_chunk code_content for references to those field names and
 * writes `cil_configures` links from the config item to the chunks that read it.
 *
 * Actions:
 *   POST ?action=extract   { project_id, repo_path?, force? } — scan + create/update cil_config items
 *   POST ?action=link       { project_id }                    — write cil_configures links (chunks → configs)
 *   POST ?action=run        { project_id, repo_path?, force? } — extract + link in one call
 *   GET  ?action=status     { project_id }                    — count cil_config items + links
 */

import { createHandler } from './_shared/middleware'
import { adminDb } from './_shared/db'
import { resolveTypeId, resolveLinkTypeId } from './_shared/resolve-ids'
import * as fs from 'fs'
import * as path from 'path'

// ─── TYPES ────────────────────────────────────────────────────────────────────

interface ExtractedConfigField {
  field_name: string
  field_type: 'string' | 'number' | 'boolean' | 'json' | 'env' | 'unknown'
  default_value: string
  description: string
  source_file: string
  source_type: 'json' | 'env' | 'sql' | 'ts'
}

// ─── CONFIG FILE GLOBS ───────────────────────────────────────────────────────

// Files we'll scan for config fields (relative to repo root)
const CONFIG_FILE_PATTERNS: { glob: RegExp; source_type: ExtractedConfigField['source_type'] }[] = [
  { glob: /spine\.config\.json$/,            source_type: 'json' },
  { glob: /\.env(\.example|\.local|\.prod)?$/, source_type: 'env'  },
  { glob: /config\.json$/,                   source_type: 'json' },
  { glob: /settings\.json$/,                 source_type: 'json' },
  { glob: /manifest\.json$/,                 source_type: 'json' },
  { glob: /tsconfig[^/]*\.json$/,            source_type: 'json' },
  { glob: /migrations\/\d+_.*\.sql$/,        source_type: 'sql'  },
]

// ─── EXTRACTORS ──────────────────────────────────────────────────────────────

function extractFromJson(content: string, sourceFile: string): ExtractedConfigField[] {
  const fields: ExtractedConfigField[] = []
  try {
    const obj = JSON.parse(content)
    const walk = (node: any, prefix: string) => {
      if (typeof node !== 'object' || node === null || Array.isArray(node)) return
      for (const [k, v] of Object.entries(node)) {
        const fullKey = prefix ? `${prefix}.${k}` : k
        const type = v === null ? 'unknown'
          : typeof v === 'boolean' ? 'boolean'
          : typeof v === 'number'  ? 'number'
          : typeof v === 'string'  ? 'string'
          : typeof v === 'object'  ? 'json'
          : 'unknown'
        fields.push({
          field_name:    fullKey,
          field_type:    type as ExtractedConfigField['field_type'],
          default_value: v === null ? 'null' : typeof v === 'object' ? JSON.stringify(v).slice(0, 200) : String(v),
          description:   '',
          source_file:   sourceFile,
          source_type:   'json',
        })
        // Recurse one level for nested objects (don't go too deep)
        if (typeof v === 'object' && v !== null && !Array.isArray(v) && prefix.split('.').length < 2) {
          walk(v, fullKey)
        }
      }
    }
    walk(obj, '')
  } catch {
    // invalid JSON — skip
  }
  return fields
}

function extractFromEnv(content: string, sourceFile: string): ExtractedConfigField[] {
  const fields: ExtractedConfigField[] = []
  const lines = content.split('\n')
  for (const line of lines) {
    const trimmed = line.trim()
    if (!trimmed || trimmed.startsWith('#')) continue
    const eq = trimmed.indexOf('=')
    if (eq === -1) continue
    const key = trimmed.slice(0, eq).trim()
    const val = trimmed.slice(eq + 1).trim()
    // Infer description from preceding comment line
    fields.push({
      field_name:    key,
      field_type:    'env',
      default_value: val || '',
      description:   '',
      source_file:   sourceFile,
      source_type:   'env',
    })
  }
  return fields
}

function extractFromSql(content: string, sourceFile: string): ExtractedConfigField[] {
  // Extract column names from CREATE TABLE statements — useful for Supabase schema config
  const fields: ExtractedConfigField[] = []
  const tableRe = /CREATE TABLE(?:\s+IF NOT EXISTS)?\s+(?:\w+\.)?(\w+)\s*\(([^;]+)\)/gis
  let m: RegExpExecArray | null
  while ((m = tableRe.exec(content)) !== null) {
    const tableName = m[1]
    const body = m[2]
    const colRe = /^\s*["']?(\w+)["']?\s+([\w\s[\]()]+?)(?:\s+(?:NOT NULL|DEFAULT|PRIMARY|UNIQUE|REFERENCES|CHECK).*)?,?\s*$/gm
    let cm: RegExpExecArray | null
    while ((cm = colRe.exec(body)) !== null) {
      const colName = cm[1]
      if (['CONSTRAINT', 'PRIMARY', 'UNIQUE', 'CHECK', 'FOREIGN', 'INDEX'].includes(colName.toUpperCase())) continue
      fields.push({
        field_name:    `${tableName}.${colName}`,
        field_type:    'unknown',
        default_value: '',
        description:   '',
        source_file:   sourceFile,
        source_type:   'sql',
      })
    }
  }
  return fields
}

// ─── WALK REPO FOR CONFIG FILES ──────────────────────────────────────────────

function walkForConfigFiles(repoPath: string, maxDepth = 3): string[] {
  const results: string[] = []
  const walk = (dir: string, depth: number) => {
    if (depth > maxDepth) return
    let entries: fs.Dirent[]
    try {
      entries = fs.readdirSync(dir, { withFileTypes: true })
    } catch { return }

    for (const entry of entries) {
      if (entry.name.startsWith('.') && !entry.name.startsWith('.env')) continue
      if (['node_modules', '.git', 'dist', 'build', '.assembled', 'coverage'].includes(entry.name)) continue
      const full = path.join(dir, entry.name)
      if (entry.isDirectory()) {
        walk(full, depth + 1)
      } else {
        const rel = path.relative(repoPath, full)
        const matched = CONFIG_FILE_PATTERNS.some(p => p.glob.test(rel))
        if (matched) results.push(rel)
      }
    }
  }
  walk(repoPath, 0)
  return results
}

// ─── HANDLER ─────────────────────────────────────────────────────────────────

export const handler = createHandler(async (ctx, body) => {
  const action = ctx.query?.action || body?.action || 'run'
  const projectId = ctx.query?.project_id || body?.project_id
  if (!projectId) throw new Error('project_id is required')

  // ── STATUS ────────────────────────────────────────────────────────────────────
  if (action === 'status') {
    const [configItems, configuresTypeId] = await Promise.all([
      adminDb.from('items').select('id', { count: 'exact' })
        .filter('data->>project_id', 'eq', projectId)
        .filter('data->>kb_type', 'eq', 'cil_config')
        .eq('is_active', true),
      resolveLinkTypeId('cil_configures').catch(() => null),
    ])

    let linkCount = 0
    if (configuresTypeId && (configItems.count || 0) > 0) {
      const { data: configIds } = await adminDb.from('items').select('id')
        .filter('data->>project_id', 'eq', projectId)
        .filter('data->>kb_type', 'eq', 'cil_config').eq('is_active', true)
      const ids = (configIds || []).map((r: any) => r.id)
      if (ids.length) {
        const { count } = await adminDb.from('links').select('id', { count: 'exact' })
          .eq('link_type_id', configuresTypeId!).in('source_id', ids)
        linkCount = count || 0
      }
    }

    return {
      project_id: projectId,
      config_items: configItems.count || 0,
      configures_links: linkCount,
    }
  }

  // ── EXTRACT — scan repo files and create/update cil_config items ──────────────
  if (action === 'extract' || action === 'run') {
    // Resolve repo_path from project item or body
    let repoPath: string = body?.repo_path || ''
    if (!repoPath) {
      const { data: proj } = await adminDb.from('items').select('data').eq('id', projectId).maybeSingle()
      repoPath = proj?.data?.repo_path || ''
    }
    if (!repoPath) throw new Error('repo_path not set on project. Pass repo_path in request body.')
    if (!fs.existsSync(repoPath)) throw new Error(`repo_path does not exist: ${repoPath}`)

    const force = body?.force ?? false
    const filePath: string | undefined = body?.file_path
    const configTypeId = await resolveTypeId('item', 'cil_config')

    let configFiles = walkForConfigFiles(repoPath)
    if (filePath) {
      configFiles = configFiles.filter(rel => rel === filePath || rel.startsWith(filePath + '/'))
    }
    const errors: string[] = []
    let created = 0, updated = 0, skipped = 0

    // Prefetch all existing cil_config items for this project in one query
    const { data: existingConfigs } = await adminDb.from('items').select('id, data')
      .filter('data->>project_id', 'eq', projectId)
      .filter('data->>kb_type', 'eq', 'cil_config').eq('is_active', true)

    // Map: `relFile::field_name` → existing item
    const existingBySlug = new Map<string, any>()
    for (const item of existingConfigs || []) {
      const slug = `${item.data?.config_table}::${item.data?.field_name}`
      existingBySlug.set(slug, item)
    }

    // Collect all fields from all config files (pure filesystem — fast)
    const allFields: { relFile: string; field: ExtractedConfigField }[] = []
    for (const relFile of configFiles) {
      const absFile = path.join(repoPath, relFile)
      const pattern = CONFIG_FILE_PATTERNS.find(p => p.glob.test(relFile))
      if (!pattern) continue
      try {
        const content = fs.readFileSync(absFile, 'utf-8')
        let rawFields: ExtractedConfigField[] = []
        if (pattern.source_type === 'json') rawFields = extractFromJson(content, relFile)
        else if (pattern.source_type === 'env')  rawFields = extractFromEnv(content, relFile)
        else if (pattern.source_type === 'sql')  rawFields = extractFromSql(content, relFile)
        for (const field of rawFields) allFields.push({ relFile, field })
      } catch (err) {
        errors.push(`${relFile}: ${err instanceof Error ? err.message : String(err)}`)
      }
    }

    // Batch upsert: collect inserts and updates, execute in parallel
    const now = new Date().toISOString()
    const toInsert: object[] = []
    const toUpdate: { id: string; data: object }[] = []

    for (const { relFile, field } of allFields) {
      const slug = `${relFile}::${field.field_name}`
      const existing = existingBySlug.get(slug)

      if (existing && !force) { skipped++; continue }

      const itemData = {
        kb_type:                  'cil_config',
        project_id:               projectId,
        config_table:             relFile,
        record_slug:              slug,
        field_name:               field.field_name,
        field_type:               field.field_type,
        default_value:            field.default_value,
        description:              field.description,
        settings:                 '',
        current_value_query:      '',
        current_value_query_type: field.source_type === 'env' ? 'env' : 'json_path',
        code_edge_chunk_ids:      '',
        source_type:              field.source_type,
      }

      if (existing) {
        toUpdate.push({ id: existing.id, data: { ...existing.data, ...itemData } })
        updated++
      } else {
        toInsert.push({ type_id: configTypeId, title: slug, status: 'active', is_active: true, data: itemData, created_at: now, updated_at: now })
        created++
      }
    }

    // Execute inserts in batches, updates in parallel
    const INSERT_BATCH = 500
    const insertPromises: Promise<void>[] = []
    for (let i = 0; i < toInsert.length; i += INSERT_BATCH) {
      insertPromises.push(
        adminDb.from('items').insert(toInsert.slice(i, i + INSERT_BATCH))
          .then(({ error: e }) => { if (e) errors.push(`config insert batch: ${e.message}`) })
      )
    }
    const updatePromises = toUpdate.map(({ id, data }) =>
      adminDb.from('items').update({ data, updated_at: now }).eq('id', id)
        .then(({ error: e }) => { if (e) errors.push(`config update ${id}: ${e.message}`) })
    )
    await Promise.all([...insertPromises, ...updatePromises])

    const extractResult = {
      project_id: projectId,
      repo_path: repoPath,
      file_path: filePath || null,
      config_files_scanned: configFiles.length,
      config_items_created: created,
      config_items_updated: updated,
      config_items_skipped: skipped,
      errors,
    }

    // If action is just 'extract', stop here
    if (action === 'extract') return extractResult

    // Otherwise fall through to link pass below
    const linkResult = await runLinkPass(projectId, errors)
    return {
      ...extractResult,
      ...linkResult,
    }
  }

  // ── LINK — scan all cil_chunks and write cil_configures links ────────────────
  if (action === 'link') {
    const errors: string[] = []
    const result = await runLinkPass(projectId, errors)
    return { project_id: projectId, ...result, errors }
  }

  throw new Error(`Unknown action: ${action}. Valid: extract, link, run, status`)
})

// ─── LINK PASS ────────────────────────────────────────────────────────────────

async function runLinkPass(
  projectId: string,
  errors: string[]
): Promise<{ configures_links_created: number }> {
  const configuresTypeId = await resolveLinkTypeId('cil_configures').catch(() => null)
  if (!configuresTypeId) return { configures_links_created: 0 }

  // Load all cil_config + cil_chunk items in parallel
  const [{ data: configItems }, { data: chunkItems }] = await Promise.all([
    adminDb.from('items').select('id, data')
      .filter('data->>project_id', 'eq', projectId)
      .filter('data->>kb_type', 'eq', 'cil_config').eq('is_active', true),
    adminDb.from('items').select('id, data')
      .filter('data->>project_id', 'eq', projectId)
      .filter('data->>kb_type', 'eq', 'cil_chunk').eq('is_active', true),
  ])

  if (!configItems?.length || !chunkItems?.length) return { configures_links_created: 0 }

  // Prefetch ALL existing cil_configures links for this project's config items in one query
  // This eliminates the per-match SELECT — O(n*m) → O(1) lookup
  const configIds = configItems.map((c: any) => c.id)
  const SUPABASE_IN_LIMIT = 500
  const existingLinkPairs = new Set<string>()
  for (let i = 0; i < configIds.length; i += SUPABASE_IN_LIMIT) {
    const { data: existingLinks } = await adminDb.from('links').select('source_id, target_id')
      .in('source_id', configIds.slice(i, i + SUPABASE_IN_LIMIT))
      .eq('link_type_id', configuresTypeId)
    for (const l of existingLinks || []) {
      existingLinkPairs.add(`${l.source_id}:${l.target_id}`)
    }
  }

  // Run the O(n*m) regex scan fully in-memory — accumulate new links to batch insert
  const now = new Date().toISOString()
  const toInsert: object[] = []

  for (const configItem of configItems) {
    const fieldName: string = configItem.data?.field_name || ''
    if (!fieldName || fieldName.length < 2) continue

    const shortName = fieldName.split('.').pop()!
    const isEnv = configItem.data?.source_type === 'env'
    const searchPattern = isEnv
      ? new RegExp(`process\\.env\\.${fieldName}\\b`)
      : new RegExp(`\\b${shortName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}\\b`)

    for (const chunk of chunkItems) {
      const code: string = chunk.data?.code_content || ''
      if (!code) continue
      if (!searchPattern.test(code)) continue

      const pairKey = `${configItem.id}:${chunk.id}`
      if (existingLinkPairs.has(pairKey)) continue // already linked

      existingLinkPairs.add(pairKey) // dedupe within this run
      toInsert.push({
        source_id:    configItem.id,
        target_id:    chunk.id,
        link_type_id: configuresTypeId,
        metadata:     { field_name: fieldName, matched_in_chunk: chunk.data?.chunk_name || '' },
        created_at:   now,
      })
    }
  }

  // Batch insert all new links
  const INSERT_BATCH = 500
  let linksCreated = 0
  for (let i = 0; i < toInsert.length; i += INSERT_BATCH) {
    const { error, data } = await adminDb.from('links').insert(toInsert.slice(i, i + INSERT_BATCH)).select('id')
    if (error) errors.push(`config-link batch insert: ${error.message}`)
    else linksCreated += data?.length || 0
  }

  return { configures_links_created: linksCreated }
}