import { and, desc, eq, sql } from 'drizzle-orm';
import {
  supportKbArticleTranslations,
  supportKbArticles,
  supportMessages,
} from '@/server/db/schema';
import type { KnowledgeAdapter, KnowledgeChunk } from './types';
import type { AdapterContext } from '@/server/support/types';

const LABEL = 'חיפוש במאגר ידע';
const MAX_RESULTS = 3;
const BODY_PREVIEW_CHARS = 200;

export const kbSearchAdapter: KnowledgeAdapter = {
  id: 'kb-search',
  label: LABEL,
  description: 'חיפוש סמנטי במאמרי ידע רלוונטיים',
  async fetch(ctx: AdapterContext): Promise<KnowledgeChunk> {
    const base: KnowledgeChunk = {
      adapterId: 'kb-search',
      label: LABEL,
      data: {},
      summary: '',
    };

    try {
      const piiKey = ctx.env.PII_KEY;
      if (!piiKey) {
        return { ...base, summary: 'חיפוש ידע לא זמין (חסר מפתח הצפנה)' };
      }

      const messageRows = await ctx.db
        .select({
          body: sql<string>`pgp_sym_decrypt(CASE WHEN substring(${supportMessages.bodyEncrypted}::text, 1, 2) = '\\x' THEN ${supportMessages.bodyEncrypted}::bytea ELSE decode(${supportMessages.bodyEncrypted}::text, 'base64') END, ${piiKey})::text`,
        })
        .from(supportMessages)
        .where(
          and(
            eq(supportMessages.parentType, ctx.parentType),
            eq(supportMessages.parentId, ctx.parentId),
          ),
        )
        .orderBy(desc(supportMessages.createdAt))
        .limit(3);

      const queryText = messageRows
        .map((m) => m.body)
        .filter(Boolean)
        .join(' ')
        .trim();

      if (!queryText) {
        return { ...base, summary: 'אין הודעות לחיפוש ידע' };
      }

      const matches = (await ctx.db.execute<{
        id: string;
        title: string;
        body_md: string;
        rank: number;
      }>(sql`
        SELECT
          a.id,
          tr.title,
          tr.body_md,
          ts_rank(
            to_tsvector('simple', tr.title || ' ' || tr.body_md),
            plainto_tsquery('simple', ${queryText})
          ) AS rank
        FROM ${supportKbArticles} a
        INNER JOIN ${supportKbArticleTranslations} tr
          ON tr.article_id = a.id
          AND tr.locale = ${ctx.locale}
        WHERE a.is_active = true
          AND to_tsvector('simple', tr.title || ' ' || tr.body_md)
              @@ plainto_tsquery('simple', ${queryText})
        ORDER BY rank DESC
        LIMIT ${MAX_RESULTS}
      `)) as {
        rows: Array<{ id: string; title: string; body_md: string; rank: number }>;
      };

      if (matches.rows.length === 0) {
        return { ...base, summary: 'לא נמצאו מאמרי ידע רלוונטיים', data: { queryText } };
      }

      const articles = matches.rows.map((m) => ({
        articleId: m.id,
        title: m.title,
        bodyPreview: m.body_md.slice(0, BODY_PREVIEW_CHARS),
        rank: Number(m.rank),
      }));

      const summary = articles
        .map(
          (a) =>
            `• ${a.title}: ${a.bodyPreview}${a.bodyPreview.length >= BODY_PREVIEW_CHARS ? '…' : ''}`,
        )
        .join('\n');

      return {
        ...base,
        data: { queryText, articles },
        summary,
      };
    } catch (err) {
      return { ...base, error: String(err) };
    }
  },
};