import { createHandler } from './_shared/middleware'
import { adminDb } from './_shared/db'
import { create as adminCreate, update as adminUpdate } from './admin-data'
import { generate } from './embeddings'
import { resolveTypeId } from './_shared/resolve-ids'

// ─── INTERFACES ───────────────────────────────────────────────────────────────

interface TableSchema {
  table_name: string
  description?: string
  columns: Array<{
    name: string
    type: string
    nullable: boolean
    description?: string
    is_primary_key?: boolean
    is_foreign_key?: boolean
    references?: string
  }>
  rls_enabled?: boolean
  notes?: string
}

interface SchemaIngestionRequest {
  tables: TableSchema[]
  force_update?: boolean
  schema_version?: string
}

interface SchemaIngestionResponse {
  success: boolean
  tables_created: number
  tables_updated: number
  tables_skipped: number
  embeddings_generated: number
  errors: string[]
}

// ─── HELPERS ──────────────────────────────────────────────────────────────────

async function findExistingSchemaItem(typeId: string, tableName: string): Promise<any | null> {
  const { data } = await adminDb
    .from('items')
    .select('id, data')
    .eq('type_id', typeId)
    .filter('data->>kb_type', 'eq', 'db_schema')
    .filter('data->>table_name', 'eq', tableName)
    .maybeSingle()
  return data
}

function buildSemanticText(table: TableSchema): string {
  const lines: string[] = [
    `Table: ${table.table_name}`,
    table.description ? `Description: ${table.description}` : `${table.table_name} stores data records.`,
    '',
    'Columns:'
  ]

  for (const col of table.columns) {
    const attrs: string[] = [col.type]
    if (col.is_primary_key) attrs.push('PRIMARY KEY')
    if (col.is_foreign_key) attrs.push(`FOREIGN KEY → ${col.references || ''}`)
    if (!col.nullable) attrs.push('NOT NULL')
    const desc = col.description ? ` — ${col.description}` : ''
    lines.push(`  ${col.name} (${attrs.join(', ')})${desc}`)
  }

  if (table.rls_enabled !== undefined) {
    lines.push('', `Row Level Security: ${table.rls_enabled ? 'enabled' : 'disabled'}`)
  }

  if (table.notes) {
    lines.push('', `Notes: ${table.notes}`)
  }

  return lines.join('\n')
}

function buildDescriptionHtml(table: TableSchema, cause: string, semanticText: string): string {
  const colRows = table.columns.map(col => {
    const attrs: string[] = [col.type]
    if (col.is_primary_key) attrs.push('PK')
    if (col.is_foreign_key) attrs.push(`FK → ${col.references || ''}`)
    if (!col.nullable) attrs.push('NOT NULL')
    const desc = col.description ? ` — ${col.description}` : ''
    return `<code>${col.name}</code> (${attrs.join(', ')})${desc}`
  })

  const sections: string[] = [
    `<p>${cause}</p>`,
    `<p><strong>Columns</strong><br/>${colRows.join('<br/>')}</p>`
  ]

  if (table.rls_enabled !== undefined) {
    sections.push(`<p><strong>Row Level Security:</strong> ${table.rls_enabled ? 'Enabled' : 'Disabled'}</p>`)
  }

  if (table.notes) {
    sections.push(`<p><strong>Notes:</strong> ${table.notes}</p>`)
  }

  const escapedSemantic = semanticText
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
  sections.push(`<pre><code>${escapedSemantic}</code></pre>`)

  return sections.join('\n')
}

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

async function handleIngestSchema(ctx: any, body: SchemaIngestionRequest): Promise<SchemaIngestionResponse> {
  const response: SchemaIngestionResponse = {
    success: true,
    tables_created: 0,
    tables_updated: 0,
    tables_skipped: 0,
    embeddings_generated: 0,
    errors: []
  }

  const tables = body.tables || []
  const forceUpdate = body.force_update ?? false
  const schemaVersion = body.schema_version || '1.0.0'
  const typeId = await resolveTypeId('item', 'kb_article')

  for (const table of tables) {
    try {
      if (!table.table_name) {
        response.errors.push('Invalid table schema: missing table_name')
        continue
      }

      const existing = await findExistingSchemaItem(typeId, table.table_name)

      if (existing && !forceUpdate) {
        response.tables_skipped++
        continue
      }

      const title = `Table: ${table.table_name}`
      const cause = table.description || `${table.table_name} stores data records.`
      const context = 'Columns: ' + table.columns.map(c => `${c.name} (${c.type})`).join(', ')
      const semanticText = buildSemanticText(table)

      const errorSignatures = [
        `duplicate key value violates unique constraint "${table.table_name}_pkey"`,
        `insert or update on table "${table.table_name}" violates foreign key constraint`,
        `null value in column of relation "${table.table_name}" violates not-null constraint`,
        `new row for relation "${table.table_name}" violates check constraint`
      ]

      const descriptionHtml = buildDescriptionHtml(table, cause, semanticText)

      const itemData = {
        kb_type: 'db_schema',
        cause,
        context,
        semantic_text: semanticText,
        error_signatures: errorSignatures,
        table_name: table.table_name,
        columns: table.columns,
        rls_enabled: table.rls_enabled,
        schema_version: schemaVersion,
        audience: ['developer', 'ai_system'],
        security_level: 'internal',
        priority: 'medium',
        category: 'technical',
        source_info: {
          source_type: 'automated_ingestion',
          author: 'schema-ingestion-v1.0',
          ingestion_timestamp: new Date().toISOString()
        }
      }

      let itemId: string

      if (existing) {
        const ctxWithQuery = { ...ctx, query: { ...ctx.query, entity: 'items', id: existing.id } }
        await adminUpdate(ctxWithQuery, {
          title,
          status: 'published',
          description: descriptionHtml,
          data: itemData,
          is_active: true
        })
        itemId = existing.id
        response.tables_updated++
      } else {
        const result: any = await adminCreate(ctx, {
          entity: 'items',
          type_id: typeId,
          title,
          status: 'published',
          description: descriptionHtml,
          data: itemData,
          is_active: true
        })
        itemId = result?.id
        if (!itemId) throw new Error('Failed to create kb_article for table: no ID returned')
        response.tables_created++
      }

      // Use sanctioned generate — never write to embeddings table directly
      await generate(ctx, { item_id: itemId, force_regenerate: forceUpdate })
      response.embeddings_generated++

    } catch (error) {
      const msg = error instanceof Error ? error.message : String(error)
      console.error(`Schema ingestion error for ${table.table_name}:`, error)
      response.errors.push(`${table.table_name}: ${msg}`)
    }
  }

  if (
    response.errors.length > 0 &&
    response.tables_created === 0 &&
    response.tables_updated === 0 &&
    response.tables_skipped === 0
  ) {
    response.success = false
  }

  return response
}

// ─── HANDLER EXPORT ───────────────────────────────────────────────────────────

export const handler = createHandler(async (ctx, body) => {
  const action = (ctx as any).query?.action || 'ingest'
  if (action === 'ingest') return await handleIngestSchema(ctx, body)
  throw new Error('Unknown action. Use ingest.')
})