import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';

export interface KbSearchResult {
  id: string;
  title: string;
  type: string;
  content: string | null;
  question: string | null;
  answer: string | null;
  url: string | null;
  similarity: number;
}

async function getOpenAIApiKey(restaurantId: string): Promise<string> {
  try {
    /* raw: SELECT encrypted_api_key FROM restaurant_api_keys WHERE restaurant_id = $1 AND provider_name = 'openai' AND is_active = true LIMIT 1 */
    const { rows } = await db.execute(sql`
      SELECT encrypted_api_key FROM restaurant_api_keys
      WHERE restaurant_id = ${restaurantId} AND provider_name = 'openai' AND is_active = true
      LIMIT 1
    `);
    const row = rows[0] as { encrypted_api_key: string } | undefined;
    if (row?.encrypted_api_key) {
      const key = row.encrypted_api_key;
      if (key.startsWith('sk-')) return key;
      try {
        const { decrypt } = await import('../utils/crypto');
        const decrypted = decrypt(key);
        if (!decrypted.startsWith('sk-')) {
          console.error('[KB Embeddings] Decrypted OpenAI key does not start with sk- — key may be corrupted. Restaurant:', restaurantId);
          throw new Error('Decrypted key appears invalid');
        }
        return decrypted;
      } catch (decryptErr) {
        console.error('[KB Embeddings] Failed to decrypt OpenAI API key for restaurant', restaurantId, '— the key may need to be re-saved in settings.', decryptErr);
        throw new Error('OpenAI API key decryption failed. Please re-save your OpenAI key in Restaurant Settings > AI Keys.');
      }
    }
  } catch (err: any) {
    if (err.message?.includes('decryption') || err.message?.includes('re-save')) throw err;
  }
  const envKey = process.env.OPENAI_API_KEY;
  if (!envKey) throw new Error('OpenAI API key not configured. Add your key in Restaurant Settings > AI Keys.');
  return envKey;
}

async function generateEmbedding(text: string, apiKey: string): Promise<number[]> {
  const response = await fetch('https://api.openai.com/v1/embeddings', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: `Bearer ${apiKey}`,
    },
    body: JSON.stringify({
      model: 'text-embedding-3-small',
      input: text.slice(0, 8000),
    }),
  });

  if (!response.ok) {
    const err = await response.text();
    throw new Error(`OpenAI embeddings error: ${response.status} ${err}`);
  }

  const data = await response.json();
  return data.data[0].embedding as number[];
}

function buildEntryText(entry: {
  type: string;
  title: string;
  content?: string | null;
  question?: string | null;
  answer?: string | null;
  url?: string | null;
}): string {
  if (entry.type === 'qa' && entry.question && entry.answer) {
    return `Q: ${entry.question}\nA: ${entry.answer}`;
  }
  if (entry.url && entry.content) {
    return `${entry.title}\nSource: ${entry.url}\n${entry.content}`;
  }
  if (entry.url) {
    return `${entry.title} ${entry.url}`;
  }
  return `${entry.title}\n${entry.content || ''}`.trim();
}

export async function generateKbEmbedding(kbId: string, restaurantId: string): Promise<void> {
  try {
    /* raw: SELECT id, type, title, content, question, answer, url FROM knowledge_base WHERE id = $1 AND restaurant_id = $2 */
    const { rows } = await db.execute(sql`
      SELECT id, type, title, content, question, answer, url
      FROM knowledge_base WHERE id = ${kbId} AND restaurant_id = ${restaurantId}
    `);
    if (!rows[0]) return;

    const entry = rows[0] as { type: string; title: string; content?: string | null; question?: string | null; answer?: string | null; url?: string | null };
    const text = buildEntryText(entry);
    if (!text.trim()) {
      /* raw: UPDATE knowledge_base SET embedding_status = 'skipped' WHERE id = $1 */
      await db.execute(sql`UPDATE knowledge_base SET embedding_status = 'skipped' WHERE id = ${kbId}`);
      return;
    }

    /* raw: UPDATE knowledge_base SET embedding_status = 'generating', embedding_error = NULL WHERE id = $1 */
    await db.execute(sql`UPDATE knowledge_base SET embedding_status = 'generating', embedding_error = NULL WHERE id = ${kbId}`);

    const apiKey = await getOpenAIApiKey(restaurantId);
    const embedding = await generateEmbedding(text, apiKey);

    /* raw: UPDATE knowledge_base SET embedding = $1::vector, embedding_status = 'done', embedding_error = NULL, updated_at = NOW() WHERE id = $2 */
    await db.execute(sql`
      UPDATE knowledge_base SET embedding = ${`[${embedding.join(',')}]`}::vector, embedding_status = 'done', embedding_error = NULL, updated_at = NOW()
      WHERE id = ${kbId}
    `);
  } catch (err: any) {
    const errorMsg = err.message || 'Unknown embedding error';
    console.error('[KB Embeddings] Failed to generate embedding for', kbId, errorMsg);
    /* raw: UPDATE knowledge_base SET embedding_status = 'error', embedding_error = $2 WHERE id = $1 */
    await db.execute(sql`UPDATE knowledge_base SET embedding_status = 'error', embedding_error = ${errorMsg.slice(0, 500)} WHERE id = ${kbId}`).catch(() => {});
  }
}

export async function reEmbedAll(restaurantId: string): Promise<{ queued: number }> {
  /* raw: SELECT id FROM knowledge_base WHERE restaurant_id = $1 AND is_active = true */
  const { rows } = await db.execute(sql`SELECT id FROM knowledge_base WHERE restaurant_id = ${restaurantId} AND is_active = true`);

  /* raw: UPDATE knowledge_base SET embedding_status = 'pending' WHERE restaurant_id = $1 AND is_active = true */
  await db.execute(sql`UPDATE knowledge_base SET embedding_status = 'pending' WHERE restaurant_id = ${restaurantId} AND is_active = true`);

  for (const row of rows as { id: string }[]) {
    generateKbEmbedding(row.id, restaurantId).catch(() => {});
  }

  return { queued: rows.length };
}

export async function searchKnowledgeBase(
  restaurantId: string,
  queryText: string,
  limit = 5
): Promise<KbSearchResult[]> {
  const apiKey = await getOpenAIApiKey(restaurantId);
  const queryEmbedding = await generateEmbedding(queryText, apiKey);
  const embeddingStr = `[${queryEmbedding.join(',')}]`;

  /* raw: SELECT id, title, type, content, question, answer, url, 1 - (embedding <=> $1::vector) AS similarity FROM knowledge_base WHERE ... ORDER BY embedding <=> $1::vector LIMIT $3 */
  try {
    const { rows } = await db.execute(sql`
      SELECT id, title, type, content, question, answer, url,
             1 - (embedding <=> ${embeddingStr}::vector) AS similarity
      FROM knowledge_base
      WHERE restaurant_id = ${restaurantId}
        AND is_active = true
        AND embedding IS NOT NULL
      ORDER BY embedding <=> ${embeddingStr}::vector
      LIMIT ${limit}
    `);
    return rows as unknown as KbSearchResult[];
  } catch (err) {
    console.error('[KB Search] Search failed:', err);
    return [];
  }
}

export function formatSearchResults(results: KbSearchResult[]): string {
  if (!results.length) return 'No relevant knowledge base entries found.';

  return results
    .map(r => {
      if (r.type === 'qa' && r.question && r.answer) {
        return `Q: ${r.question}\nA: ${r.answer}`;
      }
      if (r.content) {
        return `[${r.title}]\n${r.content.slice(0, 1000)}`;
      }
      if (r.url) {
        return `[${r.title}] ${r.url}`;
      }
      return r.title;
    })
    .join('\n\n');
}
