import type { Pool } from 'pg';
import { pgAll, pgGet } from '@/shared/db';
import type { EmbeddingUpsertDraft } from './types';

interface EmbeddingRow {
  message_id: string;
  model_version: string;
  created_at: string;
}

function formatVector(embedding: number[]): string {
  return `[${embedding.join(',')}]`;
}

export async function upsertEmbedding(
  pool: Pool,
  draft: EmbeddingUpsertDraft,
): Promise<void> {
  try {
    await pool.query(
      `INSERT INTO macsync.message_embeddings (message_id, embedding, model_version)
       VALUES ($1, $2::vector, $3)
       ON CONFLICT (message_id) DO UPDATE SET
         embedding = excluded.embedding,
         model_version = excluded.model_version,
         created_at = now()`,
      [draft.messageId, formatVector(draft.embedding), draft.modelVersion],
    );
  } catch (err) {
    throw new Error(
      `upsertEmbedding failed for message ${draft.messageId}: ${err instanceof Error ? err.message : String(err)}`,
      { cause: err },
    );
  }
}

export async function bulkUpsertEmbeddings(
  pool: Pool,
  drafts: readonly EmbeddingUpsertDraft[],
): Promise<void> {
  if (drafts.length === 0) return;

  const CHUNK = 500;
  for (let i = 0; i < drafts.length; i += CHUNK) {
    const chunk = drafts.slice(i, i + CHUNK);
    const values = chunk
      .map((_, ri) => {
        const b = ri * 3;
        return `($${b + 1}, $${b + 2}::vector, $${b + 3})`;
      })
      .join(',\n');

    const params: unknown[] = [];
    for (const d of chunk) {
      params.push(d.messageId, formatVector(d.embedding), d.modelVersion);
    }

    try {
      await pool.query(
        `INSERT INTO macsync.message_embeddings (message_id, embedding, model_version)
         VALUES ${values}
         ON CONFLICT (message_id) DO UPDATE SET
           embedding = excluded.embedding,
           model_version = excluded.model_version,
           created_at = now()`,
        params,
      );
    } catch (err) {
      throw new Error(
        `bulkUpsertEmbeddings chunk ${i}–${i + chunk.length} failed: ${err instanceof Error ? err.message : String(err)}`,
        { cause: err },
      );
    }
  }
}

export async function getEmbeddingCoverage(
  pool: Pool,
): Promise<{ total: number; covered: number }> {
  try {
    const row = await pgGet<{ total: string; covered: string }>(
      pool,
      `SELECT
         COUNT(*) AS total,
         COUNT(e.message_id) AS covered
       FROM macsync.messages m
       LEFT JOIN macsync.message_embeddings e ON e.message_id = m.id
       WHERE m.body != ''`,
    );
    return {
      total: Number(row?.total ?? 0),
      covered: Number(row?.covered ?? 0),
    };
  } catch (err) {
    throw new Error(
      `getEmbeddingCoverage failed: ${err instanceof Error ? err.message : String(err)}`,
      { cause: err },
    );
  }
}

export interface SemanticSearchHit {
  readonly messageId: string;
  readonly distance: number;
}

export async function semanticSearch(
  pool: Pool,
  opts: {
    embedding: number[];
    limit?: number;
    conversationId?: string;
    deviceId?: string;
  },
): Promise<readonly SemanticSearchHit[]> {
  const vec = formatVector(opts.embedding);
  const limit = Math.min(opts.limit ?? 50, 200);
  const conditions: string[] = [];
  const params: unknown[] = [vec, limit];

  if (opts.conversationId) {
    conditions.push(`m.conversation_id = $${params.length + 1}`);
    params.push(opts.conversationId);
  }
  if (opts.deviceId) {
    conditions.push(`m.device_id = $${params.length + 1}`);
    params.push(opts.deviceId);
  }

  const where = conditions.length ? `AND ${conditions.join(' AND ')}` : '';

  try {
    const rows = await pgAll<{ message_id: string; distance: string }>(
      pool,
      `SELECT e.message_id,
              (e.embedding <=> $1::vector) AS distance
       FROM macsync.message_embeddings e
       JOIN macsync.messages m ON m.id = e.message_id
       WHERE true ${where}
       ORDER BY e.embedding <=> $1::vector
       LIMIT $2`,
      params,
    );

    return rows.map((r) => ({
      messageId: r.message_id,
      distance: Number(r.distance),
    }));
  } catch (err) {
    throw new Error(
      `semanticSearch failed: ${err instanceof Error ? err.message : String(err)}`,
      { cause: err },
    );
  }
}
