// Hybrid-Classifier + Merge Engine
// Priorität: LLM semantic > Local Parser values > User Correction
import { parseInput, LocalParserResult } from './local-parser'
import { classifyWithLlm } from './llm-service'
import type { ClassificationResult } from './llm-caller'
import { getDb } from '../database/connection'

export interface HybridResult extends ClassificationResult {
  localHints: LocalParserResult
  usedFallback: boolean
}

export async function classifyInput(
  input: string,
  preferredLlm: 'claude' | 'openai' = 'claude'
): Promise<HybridResult> {
  const today = new Date().toISOString().split('T')[0]

  // Schritt 1: Lokaler Parser (synchron, immer)
  const localHints = parseInput(input)

  // Schritt 2: LLM-Klassifikation (async via Worker)
  let llmResult: ClassificationResult
  let usedFallback = false

  try {
    llmResult = await classifyWithLlm(input, localHints, today, preferredLlm)
  } catch (err) {
    console.warn('[Classifier] LLM fehlgeschlagen, nutze lokalen Fallback:', err)
    // Fallback: nur lokale Analyse
    llmResult = buildFallback(input, localHints)
    usedFallback = true
  }

  // Schritt 3: Merge Engine
  const merged = merge(llmResult, localHints)

  return { ...merged, localHints, usedFallback }
}

// Merge: LLM-Felder + lokale Hints kombinieren
// Regel: LLM-Werte haben Vorrang, lokale Werte füllen Lücken
function merge(llm: ClassificationResult, local: LocalParserResult): ClassificationResult {
  const fields = { ...llm.fields }

  // Datum: lokaler Parser ist zuverlässiger (harte Regex-Fakten)
  if (local.date && !fields.date) {
    fields.date = local.date
  }
  // Zeit: lokaler Parser
  if (local.time && !fields.time) {
    fields.time = local.time
  }
  // Person: lokaler Parser (erstes Ergebnis)
  if (local.persons?.length && !fields.person) {
    fields.person = local.persons[0]
  }
  // Ort: lokaler Parser
  if (local.locations?.length && !fields.location) {
    fields.location = local.locations[0]
  }
  // Betrag: lokaler Parser
  if (local.amount !== undefined && fields.amount === undefined) {
    fields.amount = local.amount
    fields.currency = local.currency ?? 'EUR'
  }

  return { ...llm, fields }
}

// Fallback wenn LLM nicht verfügbar
function buildFallback(input: string, local: LocalParserResult): ClassificationResult {
  const category = local.suggestedCategory ?? 'note'
  const title = input.slice(0, 60).trim()

  return {
    category: category as ClassificationResult['category'],
    confidence: local.confidence * 0.6, // reduzierte Konfidenz ohne LLM
    title,
    fields: {
      date:     local.date,
      time:     local.time,
      person:   local.persons?.[0],
      location: local.locations?.[0],
      amount:   local.amount,
      currency: local.currency,
    },
    tags: [],
  }
}

// ─── CRUD-OPERATIONEN ─────────────────────────────────────────────────────

export interface EntryPayload {
  category: ClassificationResult['category']
  title: string
  raw_input: string
  status?: string
  fields: Record<string, unknown>
  tags?: string[]
}

export function saveEntry(payload: EntryPayload): number {
  const db = getDb()

  const result = db.prepare(`
    INSERT INTO entries (category, title, raw_input, status)
    VALUES (?, ?, ?, ?)
  `).run(
    payload.category,
    payload.title,
    payload.raw_input,
    payload.status ?? 'active'
  )

  const entryId = result.lastInsertRowid as number

  // Kategorie-Tabelle befüllen
  insertCategoryFields(db, entryId, payload.category, payload.fields)

  // Tags speichern
  if (payload.tags?.length) {
    saveTags(db, entryId, payload.tags)
  }

  return entryId
}

function insertCategoryFields(
  db: ReturnType<typeof getDb>,
  entryId: number,
  category: string,
  fields: Record<string, unknown>
): void {
  const f = fields

  switch (category) {
    case 'idea':
      db.prepare(`INSERT INTO ideas (entry_id, description, potential, next_steps) VALUES (?, ?, ?, ?)`)
        .run(entryId, f.description ?? null, f.potential ?? null, f.next_steps ?? null)
      break
    case 'project':
      db.prepare(`INSERT INTO projects (entry_id, goal, deadline, budget, status) VALUES (?, ?, ?, ?, ?)`)
        .run(entryId, f.goal ?? null, f.deadline ?? f.date ?? null, f.budget ?? null, f.project_status ?? 'planning')
      break
    case 'task':
      db.prepare(`INSERT INTO tasks (entry_id, description, due_date, assigned_to, priority, project_id) VALUES (?, ?, ?, ?, ?, ?)`)
        .run(entryId, f.description ?? null, f.due_date ?? f.date ?? null, f.person ?? null, f.priority ?? 'medium', f.project_id ?? null)
      break
    case 'event':
      db.prepare(`INSERT INTO events (entry_id, date, time, location, participants, agenda) VALUES (?, ?, ?, ?, ?, ?)`)
        .run(entryId, f.date ?? null, f.time ?? null, f.location ?? null, f.person ?? null, f.agenda ?? null)
      break
    case 'note':
      db.prepare(`INSERT INTO notes (entry_id, body) VALUES (?, ?)`)
        .run(entryId, f.body ?? f.description ?? null)
      break
    case 'contact':
      db.prepare(`INSERT INTO contacts (entry_id, name, role, company, phone, email, notes) VALUES (?, ?, ?, ?, ?, ?, ?)`)
        .run(entryId, f.name ?? f.person ?? null, f.role ?? null, f.company ?? null, f.phone ?? null, f.email ?? null, f.notes ?? null)
      break
    case 'finance':
      db.prepare(`INSERT INTO finances (entry_id, type, amount, currency, date, fin_category, description) VALUES (?, ?, ?, ?, ?, ?, ?)`)
        .run(entryId, f.type ?? null, f.amount ?? null, f.currency ?? 'EUR', f.date ?? null, f.fin_category ?? null, f.description ?? null)
      break
  }
}

function saveTags(db: ReturnType<typeof getDb>, entryId: number, tags: string[]): void {
  for (const tag of tags) {
    const normalized = tag.toLowerCase().trim()
    if (!normalized) continue
    db.prepare(`INSERT OR IGNORE INTO tags (name) VALUES (?)`).run(normalized)
    const tagRow = db.prepare(`SELECT id FROM tags WHERE name = ?`).get(normalized) as { id: number }
    db.prepare(`INSERT OR IGNORE INTO entry_tags (entry_id, tag_id) VALUES (?, ?)`).run(entryId, tagRow.id)
  }
}

function getCategoryFields(
  db: ReturnType<typeof getDb>,
  entryId: number,
  category: string
): Record<string, unknown> {
  switch (category) {
    case 'idea':    return (db.prepare(`SELECT description, potential, next_steps FROM ideas    WHERE entry_id = ?`).get(entryId) ?? {}) as Record<string, unknown>
    case 'project': return (db.prepare(`SELECT goal, deadline, budget, status as project_status FROM projects WHERE entry_id = ?`).get(entryId) ?? {}) as Record<string, unknown>
    case 'task':    return (db.prepare(`SELECT description, due_date, assigned_to as person, priority FROM tasks WHERE entry_id = ?`).get(entryId) ?? {}) as Record<string, unknown>
    case 'event':   return (db.prepare(`SELECT date, time, location, participants as person, agenda FROM events WHERE entry_id = ?`).get(entryId) ?? {}) as Record<string, unknown>
    case 'note':    return (db.prepare(`SELECT body FROM notes    WHERE entry_id = ?`).get(entryId) ?? {}) as Record<string, unknown>
    case 'contact': return (db.prepare(`SELECT name, role, company, phone, email, notes FROM contacts WHERE entry_id = ?`).get(entryId) ?? {}) as Record<string, unknown>
    case 'finance': return (db.prepare(`SELECT type, amount, currency, date, fin_category, description FROM finances WHERE entry_id = ?`).get(entryId) ?? {}) as Record<string, unknown>
    default:        return {}
  }
}

export function getEntry(id: number): unknown {
  const db = getDb()
  const entry = db.prepare(`SELECT * FROM entries WHERE id = ?`).get(id) as (Record<string, unknown> & { id: number; category: string }) | undefined
  if (!entry) return null
  const fields = getCategoryFields(db, entry.id, entry.category)
  const tagRows = db.prepare(`SELECT t.name FROM tags t JOIN entry_tags et ON t.id = et.tag_id WHERE et.entry_id = ?`).all(entry.id) as { name: string }[]
  return { ...entry, fields, tags: tagRows.map(t => t.name) }
}

export function updateEntryFull(id: number, data: {
  title?: string
  status?: string
  raw_input?: string
  category?: string
  fields?: Record<string, unknown>
  tags?: string[]
}): void {
  const db = getDb()

  if (data.title || data.status || data.raw_input) {
    const sets: string[] = []
    const vals: unknown[] = []
    if (data.title)     { sets.push('title = ?');     vals.push(data.title) }
    if (data.status)    { sets.push('status = ?');    vals.push(data.status) }
    if (data.raw_input) { sets.push('raw_input = ?'); vals.push(data.raw_input) }
    sets.push('updated_at = CURRENT_TIMESTAMP')
    vals.push(id)
    db.prepare(`UPDATE entries SET ${sets.join(', ')} WHERE id = ?`).run(...vals)
  }

  if (data.fields && data.category) {
    const f = data.fields
    switch (data.category) {
      case 'idea':
        db.prepare(`UPDATE ideas SET description=?, potential=?, next_steps=? WHERE entry_id=?`).run(f.description ?? null, f.potential ?? null, f.next_steps ?? null, id); break
      case 'project':
        db.prepare(`UPDATE projects SET goal=?, deadline=?, budget=?, status=? WHERE entry_id=?`).run(f.goal ?? null, f.deadline ?? f.date ?? null, f.budget ?? null, f.project_status ?? 'planning', id); break
      case 'task':
        db.prepare(`UPDATE tasks SET description=?, due_date=?, assigned_to=?, priority=? WHERE entry_id=?`).run(f.description ?? null, f.due_date ?? f.date ?? null, f.person ?? null, f.priority ?? 'medium', id); break
      case 'event':
        db.prepare(`UPDATE events SET date=?, time=?, location=?, participants=?, agenda=? WHERE entry_id=?`).run(f.date ?? null, f.time ?? null, f.location ?? null, f.person ?? null, f.agenda ?? null, id); break
      case 'note':
        db.prepare(`UPDATE notes SET body=? WHERE entry_id=?`).run(f.body ?? f.description ?? null, id); break
      case 'contact':
        db.prepare(`UPDATE contacts SET name=?, role=?, company=?, phone=?, email=?, notes=? WHERE entry_id=?`).run(f.name ?? null, f.role ?? null, f.company ?? null, f.phone ?? null, f.email ?? null, f.notes ?? null, id); break
      case 'finance':
        db.prepare(`UPDATE finances SET type=?, amount=?, currency=?, date=?, fin_category=?, description=? WHERE entry_id=?`).run(f.type ?? null, f.amount ?? null, f.currency ?? 'EUR', f.date ?? null, f.fin_category ?? null, f.description ?? null, id); break
    }
  }

  if (data.tags !== undefined) {
    db.prepare(`DELETE FROM entry_tags WHERE entry_id = ?`).run(id)
    if (data.tags.length) saveTags(db, id, data.tags)
  }
}

export function getEntries(category?: string): unknown[] {
  const db = getDb()
  if (category) {
    return db.prepare(`SELECT * FROM entries WHERE category = ? AND status != 'archived' ORDER BY created_at DESC`).all(category)
  }
  return db.prepare(`SELECT * FROM entries WHERE status != 'archived' ORDER BY created_at DESC`).all()
}

export function getDashboard(): unknown {
  const db = getDb()
  const recentIdeas   = db.prepare(`SELECT * FROM entries WHERE category = 'idea'    AND status = 'active' ORDER BY created_at DESC LIMIT 5`).all()
  const openTasks     = db.prepare(`SELECT * FROM entries WHERE category = 'task'    AND status = 'active' ORDER BY created_at DESC LIMIT 5`).all()
  const nextEvents    = db.prepare(`SELECT e.*, ev.date, ev.time, ev.location FROM entries e JOIN events ev ON e.id = ev.entry_id WHERE e.status = 'active' AND ev.date >= date('now') ORDER BY ev.date ASC LIMIT 5`).all()
  const activeProjects = db.prepare(`SELECT * FROM entries WHERE category = 'project' AND status = 'active' ORDER BY created_at DESC LIMIT 5`).all()
  const totalEntries  = (db.prepare(`SELECT COUNT(*) as n FROM entries`).get() as { n: number }).n

  return { recentIdeas, openTasks, nextEvents, activeProjects, totalEntries }
}

export function searchEntries(query: string): unknown[] {
  const db = getDb()

  // Präfix-Suche aufbauen: "münchen büro" → "münchen* büro*"
  const ftsQuery = query.trim()
    .split(/\s+/)
    .filter(Boolean)
    .map(w => `${w}*`)
    .join(' ')

  if (!ftsQuery) return []

  try {
    return db.prepare(`
      SELECT e.id, e.category, e.title, e.created_at, e.status
      FROM entries_fts
      JOIN entries e ON entries_fts.rowid = e.id
      WHERE entries_fts MATCH ?
      ORDER BY rank
      LIMIT 50
    `).all(ftsQuery)
  } catch {
    // FTS-Fehler → Fallback: LIKE-Suche
    const like = `%${query}%`
    return db.prepare(`
      SELECT id, category, title, created_at, status
      FROM entries
      WHERE title LIKE ? OR raw_input LIKE ?
      ORDER BY created_at DESC
      LIMIT 50
    `).all(like, like)
  }
}

export function linkEntries(sourceId: number, targetId: number, relation: string): void {
  const db = getDb()
  db.prepare(`INSERT INTO links (source_id, target_id, relation) VALUES (?, ?, ?)`).run(sourceId, targetId, relation)
}

export function getLinks(entryId: number): unknown[] {
  const db = getDb()
  return db.prepare(`
    SELECT l.*, e.title as target_title, e.category as target_category
    FROM links l
    JOIN entries e ON l.target_id = e.id
    WHERE l.source_id = ?
    UNION ALL
    SELECT l.*, e.title as target_title, e.category as target_category
    FROM links l
    JOIN entries e ON l.source_id = e.id
    WHERE l.target_id = ?
  `).all(entryId, entryId)
}