import "server-only";
import { sqlPublic } from "@/db";
import { embedQuery } from "@/lib/ai/provider";
import { cache } from "react";

export const PER_PAGE = 24;
export const SORTS = ["relevance", "name", "updated", "quality"] as const;
export type Sort = (typeof SORTS)[number];
export type Filters = { q?: string; category?: string[]; tag?: string[]; use?: string[]; os?: string[]; exec?: string[]; iface?: string[]; source?: string[]; pricing?: string[]; status?: string[]; depth?: string[]; selfhost?: boolean; sort?: Sort; page?: number };

export type ToolCard = {
  id: string; slug: string; name: string; summary: string; productType: string; productStatus: string; sourceModel: string; licenseSpdx: string | null; pricingModel: string[];
  operatingSystems: string[]; executionModes: string[]; interfaces: string[]; depth: string; qualityScore: number; featured: boolean; updatedAt: Date; checkedAt: Date | null; linkState: string;
  vendorName: string | null; primaryCategory: { slug: string; name: string } | null; score?: number;
};
const CARD_SELECT = `t.id, t.slug, t.name, t.summary, t.product_type AS "productType", t.product_status AS "productStatus", t.source_model AS "sourceModel", t.license_spdx AS "licenseSpdx", t.pricing_model AS "pricingModel",
  t.operating_systems AS "operatingSystems", t.execution_modes AS "executionModes", t.interfaces, t.depth, t.quality_score AS "qualityScore", t.featured, t.updated_at AS "updatedAt", t.checked_at AS "checkedAt", t.link_state AS "linkState",
  v.name AS "vendorName", (SELECT jsonb_build_object('slug', c.slug, 'name', c.name) FROM tool_categories tc JOIN categories c ON c.id = tc.category_id WHERE tc.tool_id = t.id AND tc.is_primary LIMIT 1) AS "primaryCategory"`;

/** Build the WHERE clause fragments for the structured filters (shared by search, facets and category pages). */
function whereFragments(f: Filters) {
  const parts: ReturnType<typeof sqlPublic>[] = [];
  const s = sqlPublic;
  if (f.category?.length) parts.push(s`t.id IN (SELECT tc.tool_id FROM tool_categories tc JOIN categories c ON c.id = tc.category_id LEFT JOIN categories p ON p.id = c.parent_id WHERE c.slug = ANY(${f.category}) OR p.slug = ANY(${f.category}))`);
  if (f.tag?.length) parts.push(s`t.id IN (SELECT tt.tool_id FROM tool_tags tt JOIN tags g ON g.id = tt.tag_id WHERE g.kind='tag' AND g.slug = ANY(${f.tag}))`);
  if (f.use?.length) parts.push(s`t.id IN (SELECT tt.tool_id FROM tool_tags tt JOIN tags g ON g.id = tt.tag_id WHERE g.kind='use_case' AND g.slug = ANY(${f.use}))`);
  if (f.os?.length) parts.push(s`t.operating_systems && ${f.os}::text[]`);
  if (f.exec?.length) parts.push(s`t.execution_modes && ${f.exec}::text[]`);
  if (f.iface?.length) parts.push(s`t.interfaces && ${f.iface}::text[]`);
  if (f.source?.length) parts.push(s`t.source_model = ANY(${f.source})`);
  if (f.pricing?.length) parts.push(s`t.pricing_model && ${f.pricing}::text[]`);
  if (f.status?.length) parts.push(s`t.product_status = ANY(${f.status})`);
  if (f.depth?.length) parts.push(s`t.depth = ANY(${f.depth})`);
  if (f.selfhost) parts.push(s`t.self_hostable = true`);
  return parts;
}
function andAll(parts: ReturnType<typeof sqlPublic>[]) {
  const s = sqlPublic; if (!parts.length) return s``;
  return parts.reduce((acc, p, i) => (i === 0 ? s`AND ${p}` : s`${acc} AND ${p}`), s``);
}
const cleanQuery = (q?: string) => (q ?? "").replace(/[\u0000-\u001f<>]/g, " ").replace(/\s+/g, " ").trim().slice(0, 200);
/** tsquery with prefix matching on the last token (so "postg" matches PostgreSQL). */
function prefixQuery(q: string) { const toks = q.toLowerCase().split(/[^\p{L}\p{N}+#.]+/u).filter(Boolean).slice(0, 8); if (!toks.length) return null; return toks.map((t, i) => (i === toks.length - 1 ? `${t.replace(/[':&|!()]/g, "")}:*` : t.replace(/[':&|!()]/g, ""))).filter((t) => t !== ":*").join(" & "); }

export async function searchTools(f: Filters): Promise<{ items: ToolCard[]; total: number; page: number; pages: number; mode: "lexical" | "hybrid" | "browse"; tookMs: number }> {
  const t0 = Date.now(); const s = sqlPublic;
  const q = cleanQuery(f.q); const page = Math.max(1, f.page ?? 1); const off = (page - 1) * PER_PAGE;
  const where = andAll(whereFragments(f));
  const sort = f.sort ?? (q ? "relevance" : "name");
  const orderBy = sort === "name" ? s`t.name ASC` : sort === "updated" ? s`t.updated_at DESC, t.name` : sort === "quality" ? s`t.quality_score DESC, t.name` : s`t.featured DESC, t.quality_score DESC, t.name`;
  if (!q) {
    const items = await s<(ToolCard & { total: number })[]>`SELECT ${s.unsafe(CARD_SELECT)}, count(*) OVER()::int AS total FROM public_tools t LEFT JOIN vendors v ON v.id = t.vendor_id WHERE true ${where} ORDER BY ${orderBy} LIMIT ${PER_PAGE} OFFSET ${off}`;
    const total = items[0]?.total ?? 0;
    return { items: items.map(({ total: _t, ...r }) => r), total, page, pages: Math.max(1, Math.ceil(total / PER_PAGE)), mode: "browse", tookMs: Date.now() - t0 };
  }
  const pq = prefixQuery(q);
  const vec = await embedQuery(q);
  // lexical: full-text (weighted, prefix) ∪ trigram on name/aliases (typo tolerance)
  const lexical = s`
    SELECT t.id,
      (ts_rank_cd(t.search_vector, to_tsquery('english', f_unaccent(${pq})), 32) * 1.0
       + greatest(similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))), (SELECT coalesce(max(similarity(f_unaccent(lower(a)), f_unaccent(lower(${q})))),0) FROM unnest(t.aliases) a)) * 0.9
       + CASE WHEN f_unaccent(lower(t.name)) LIKE f_unaccent(lower(${q})) || '%' THEN 0.6 ELSE 0 END
       + CASE WHEN f_unaccent(lower(t.name)) = f_unaccent(lower(${q})) THEN 1.0 ELSE 0 END
       + t.quality_score / 1000.0 + CASE WHEN t.featured THEN 0.03 ELSE 0 END) AS lex
    FROM public_tools t
    WHERE (t.search_vector @@ to_tsquery('english', f_unaccent(${pq}))
       OR similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) > 0.3
       OR EXISTS (SELECT 1 FROM unnest(t.aliases) a WHERE similarity(f_unaccent(lower(a)), f_unaccent(lower(${q}))) > 0.3)
       OR f_unaccent(lower(t.name)) LIKE '%' || f_unaccent(lower(${q})) || '%')
      ${where}`;
  const rows = vec
    ? await s<(ToolCard & { total: number; score: number })[]>`
      WITH lex AS (${lexical}), lexr AS (SELECT id, lex, row_number() OVER (ORDER BY lex DESC) AS r FROM lex),
      vec AS (SELECT t.id, row_number() OVER (ORDER BY t.embedding <=> ${JSON.stringify(vec)}::vector) AS r FROM public_tools t WHERE t.embedding IS NOT NULL ${where} ORDER BY t.embedding <=> ${JSON.stringify(vec)}::vector LIMIT 60),
      fused AS (SELECT coalesce(l.id, v.id) AS id, coalesce(1.0/(60+l.r),0)*1.2 + coalesce(1.0/(60+v.r),0) + coalesce(l.lex,0)/50 AS score FROM lexr l FULL OUTER JOIN vec v ON v.id = l.id)
      SELECT ${s.unsafe(CARD_SELECT)}, f.score, count(*) OVER()::int AS total FROM fused f JOIN public_tools t ON t.id = f.id LEFT JOIN vendors v ON v.id = t.vendor_id
      ORDER BY ${sort === "relevance" ? s`f.score DESC, t.name` : orderBy} LIMIT ${PER_PAGE} OFFSET ${off}`
    : await s<(ToolCard & { total: number; score: number })[]>`
      WITH lex AS (${lexical})
      SELECT ${s.unsafe(CARD_SELECT)}, l.lex AS score, count(*) OVER()::int AS total FROM lex l JOIN public_tools t ON t.id = l.id LEFT JOIN vendors v ON v.id = t.vendor_id
      ORDER BY ${sort === "relevance" ? s`l.lex DESC, t.name` : orderBy} LIMIT ${PER_PAGE} OFFSET ${off}`;
  const total = rows[0]?.total ?? 0;
  const tookMs = Date.now() - t0;
  s`INSERT INTO search_events (query, result_count, mode, duration_ms) VALUES (${q.slice(0, 120)}, ${total}, ${vec ? "hybrid" : "lexical"}, ${tookMs})`.catch(() => {});
  return { items: rows.map(({ total: _t, ...r }) => r), total, page, pages: Math.max(1, Math.ceil(total / PER_PAGE)), mode: vec ? "hybrid" : "lexical", tookMs };
}

export type Facet = { value: string; label: string; count: number };
export async function facets(f: Filters): Promise<Record<string, Facet[]>> {
  const s = sqlPublic; const where = andAll(whereFragments({ ...f, q: undefined }));
  const q = cleanQuery(f.q); const pq = prefixQuery(q);
  const match = q ? s`AND (t.search_vector @@ to_tsquery('english', f_unaccent(${pq})) OR similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) > 0.3 OR f_unaccent(lower(t.name)) LIKE '%' || f_unaccent(lower(${q})) || '%')` : s``;
  const base = s`SELECT t.* FROM public_tools t WHERE true ${where} ${match}`;
  const [cats, os, exec, iface, source, pricing, status, depth] = await Promise.all([
    s<Facet[]>`WITH b AS (${base}) SELECT c.slug AS value, c.name AS label, count(DISTINCT b.id)::int AS count FROM b JOIN tool_categories tc ON tc.tool_id=b.id JOIN categories c ON c.id=tc.category_id GROUP BY c.slug, c.name ORDER BY count DESC, label LIMIT 40`,
    s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.operating_systems) x GROUP BY x ORDER BY count DESC`,
    s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.execution_modes) x GROUP BY x ORDER BY count DESC`,
    s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.interfaces) x GROUP BY x ORDER BY count DESC`,
    s<Facet[]>`WITH b AS (${base}) SELECT source_model AS value, source_model AS label, count(*)::int FROM b GROUP BY 1 ORDER BY count DESC`,
    s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.pricing_model) x GROUP BY x ORDER BY count DESC`,
    s<Facet[]>`WITH b AS (${base}) SELECT product_status AS value, product_status AS label, count(*)::int FROM b GROUP BY 1 ORDER BY count DESC`,
    s<Facet[]>`WITH b AS (${base}) SELECT depth AS value, depth AS label, count(*)::int FROM b GROUP BY 1 ORDER BY count DESC`,
  ]);
  return { category: cats, os, exec, iface, source, pricing, status, depth };
}

export async function autocomplete(qRaw: string, limit = 8) {
  const q = cleanQuery(qRaw); if (q.length < 1) return [];
  const s = sqlPublic; const pq = prefixQuery(q);
  return s<{ slug: string; name: string; summary: string; kind: string }[]>`
    (SELECT t.slug, t.name, t.summary, 'tool' AS kind FROM public_tools t
      WHERE f_unaccent(lower(t.name)) LIKE f_unaccent(lower(${q})) || '%' OR t.search_vector @@ to_tsquery('english', f_unaccent(${pq})) OR similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) > 0.35
        OR EXISTS (SELECT 1 FROM unnest(t.aliases) a WHERE f_unaccent(lower(a)) LIKE f_unaccent(lower(${q})) || '%')
      ORDER BY (f_unaccent(lower(t.name)) LIKE f_unaccent(lower(${q})) || '%') DESC, similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) DESC, t.quality_score DESC LIMIT ${limit})
    UNION ALL
    (SELECT c.slug, c.name, c.description AS summary, 'category' AS kind FROM categories c WHERE f_unaccent(lower(c.name)) LIKE '%' || f_unaccent(lower(${q})) || '%' AND c.parent_id IS NOT NULL
       AND EXISTS (SELECT 1 FROM tool_categories tc JOIN public_tools t ON t.id = tc.tool_id WHERE tc.category_id = c.id) ORDER BY c.name LIMIT 3)`;
}

export type ToolDetail = {
  id: string; slug: string; name: string; aliases: string[]; summary: string; description: string; productType: string; productStatus: string;
  homepageUrl: string; docsUrl: string | null; repoUrl: string | null; pricingUrl: string | null; licenseUrl: string | null; privacyUrl: string | null; securityUrl: string | null; logoUrl: string | null; logoAttribution: string | null;
  capabilities: string[]; workflows: string[]; advantages: string[]; limitations: string[]; prerequisites: string[]; audiences: string[]; comparisonNotes: string | null;
  operatingSystems: string[]; executionModes: string[]; interfaces: string[]; deploymentModels: string[]; integrations: string[]; dataFormats: string[]; languages: string[];
  sourceModel: string; licenseSpdx: string | null; pricingModel: string[]; pricingCheckedAt: string | null; privacyPosture: string | null; dataResidency: string | null; selfHostable: boolean | null;
  confidence: string; uncertaintyNote: string | null; depth: string; qualityScore: number; linkState: string; featured: boolean; reviewedAt: string | null; checkedAt: Date | null; publishedAt: Date | null; updatedAt: Date;
  vendor: { slug: string; name: string; websiteUrl: string | null } | null;
  categories: { slug: string; name: string; isPrimary: boolean; label: string | null; parent: { slug: string; name: string } | null }[];
  tags: { slug: string; name: string }[]; useCases: { slug: string; name: string }[];
  alternatives: { slug: string; name: string; summary: string; sourceModel: string; pricingModel: string[] }[];
  sources: { url: string; title: string | null; sourceType: string; supports: string[]; checkedAt: string; note: string | null; httpStatus: number | null }[];
  linkChecks: { field: string; url: string; ok: boolean; statusCode: number | null; redirected: boolean; checkedAt: Date }[];
  collections: { slug: string; title: string }[];
};
export const getTool = cache(async (slug: string): Promise<ToolDetail | null> => {
  const s = sqlPublic;
  const [t] = await s<any[]>`SELECT t.*, to_jsonb(v) - 'id' - 'created_at' - 'updated_at' - 'description' AS vendor FROM public_tools t LEFT JOIN vendors v ON v.id = t.vendor_id WHERE t.slug = ${slug}`;
  if (!t) return null;
  const [cats, tags, alts, srcs, links, cols] = await Promise.all([
    s<any[]>`SELECT c.slug, c.name, tc.is_primary AS "isPrimary", tc.label, CASE WHEN p.id IS NULL THEN NULL ELSE jsonb_build_object('slug', p.slug, 'name', p.name) END AS parent FROM tool_categories tc JOIN categories c ON c.id=tc.category_id LEFT JOIN categories p ON p.id=c.parent_id WHERE tc.tool_id=${t.id} ORDER BY tc.is_primary DESC, c.name`,
    s<any[]>`SELECT g.slug, g.name, g.kind FROM tool_tags tt JOIN tags g ON g.id=tt.tag_id WHERE tt.tool_id=${t.id} ORDER BY g.name`,
    s<any[]>`SELECT a.slug, a.name, a.summary, a.source_model AS "sourceModel", a.pricing_model AS "pricingModel" FROM tool_alternatives ta JOIN public_tools a ON a.id=ta.alternative_id WHERE ta.tool_id=${t.id} UNION SELECT a.slug, a.name, a.summary, a.source_model, a.pricing_model FROM tool_alternatives ta JOIN public_tools a ON a.id=ta.tool_id WHERE ta.alternative_id=${t.id} ORDER BY 2`,
    s<any[]>`SELECT so.url, so.title, so.source_type AS "sourceType", ts.supports, ts.checked_at AS "checkedAt", ts.note, so.http_status AS "httpStatus" FROM tool_sources ts JOIN sources so ON so.id=ts.source_id WHERE ts.tool_id=${t.id} ORDER BY so.source_type, so.url`,
    s<any[]>`SELECT DISTINCT ON (field) field, url, ok, status_code AS "statusCode", redirected, checked_at AS "checkedAt" FROM public_link_checks WHERE tool_id=${t.id} ORDER BY field, checked_at DESC`,
    s<any[]>`SELECT c.slug, c.title FROM collection_items ci JOIN public_collections c ON c.id=ci.collection_id WHERE ci.tool_id=${t.id} ORDER BY c.title`,
  ]);
  return {
    id: t.id, slug: t.slug, name: t.name, aliases: t.aliases, summary: t.summary, description: t.description, productType: t.product_type, productStatus: t.product_status,
    homepageUrl: t.homepage_url, docsUrl: t.docs_url, repoUrl: t.repo_url, pricingUrl: t.pricing_url, licenseUrl: t.license_url, privacyUrl: t.privacy_url, securityUrl: t.security_url, logoUrl: t.logo_url, logoAttribution: t.logo_attribution,
    capabilities: t.capabilities, workflows: t.workflows, advantages: t.advantages, limitations: t.limitations, prerequisites: t.prerequisites, audiences: t.audiences, comparisonNotes: t.comparison_notes,
    operatingSystems: t.operating_systems, executionModes: t.execution_modes, interfaces: t.interfaces, deploymentModels: t.deployment_models, integrations: t.integrations, dataFormats: t.data_formats, languages: t.languages,
    sourceModel: t.source_model, licenseSpdx: t.license_spdx, pricingModel: t.pricing_model, pricingCheckedAt: t.pricing_checked_at, privacyPosture: t.privacy_posture, dataResidency: t.data_residency, selfHostable: t.self_hostable,
    confidence: t.confidence, uncertaintyNote: t.uncertainty_note, depth: t.depth, qualityScore: t.quality_score, linkState: t.link_state, featured: t.featured, reviewedAt: t.reviewed_at, checkedAt: t.checked_at, publishedAt: t.published_at, updatedAt: t.updated_at,
    vendor: t.vendor ? { slug: t.vendor.slug, name: t.vendor.name, websiteUrl: t.vendor.website_url ?? null } : null,
    categories: cats, tags: tags.filter((g) => g.kind === "tag"), useCases: tags.filter((g) => g.kind === "use_case"), alternatives: alts, sources: srcs, linkChecks: links, collections: cols,
  };
});

export const getToolsBySlugs = cache(async (slugs: string[]) => {
  if (!slugs.length) return [] as ToolDetail[];
  const out = await Promise.all(slugs.slice(0, 5).map((s) => getTool(s)));
  return out.filter((x): x is ToolDetail => !!x);
});

export type CategoryNode = { id: string; slug: string; name: string; description: string; count: number; children: CategoryNode[] };
export const categoryTree = cache(async (): Promise<CategoryNode[]> => {
  const s = sqlPublic;
  const rows = await s<{ id: string; slug: string; name: string; description: string; parentId: string | null; sortOrder: number; count: number }[]>`
    SELECT c.id, c.slug, c.name, c.description, c.parent_id AS "parentId", c.sort_order AS "sortOrder",
      (SELECT count(DISTINCT tc.tool_id)::int FROM tool_categories tc JOIN public_tools t ON t.id = tc.tool_id JOIN categories cc ON cc.id = tc.category_id WHERE cc.id = c.id OR cc.parent_id = c.id) AS count
    FROM categories c ORDER BY c.sort_order, c.name`;
  const byId = new Map(rows.map((r) => [r.id, { ...r, children: [] as CategoryNode[] }]));
  const roots: CategoryNode[] = [];
  for (const r of byId.values()) { if (r.parentId && byId.has(r.parentId)) byId.get(r.parentId)!.children.push(r); else roots.push(r); }
  return roots;
});
export const getCategory = cache(async (slug: string) => {
  const s = sqlPublic;
  const [c] = await s<{ id: string; slug: string; name: string; description: string; parentId: string | null }[]>`SELECT id, slug, name, description, parent_id AS "parentId" FROM categories WHERE slug=${slug}`;
  if (!c) return null;
  const parent = c.parentId ? (await s<{ slug: string; name: string }[]>`SELECT slug, name FROM categories WHERE id=${c.parentId}`)[0] : null;
  const children = await s<{ slug: string; name: string; description: string; count: number }[]>`SELECT c.slug, c.name, c.description, (SELECT count(*)::int FROM tool_categories tc JOIN public_tools t ON t.id=tc.tool_id WHERE tc.category_id=c.id) AS count FROM categories c WHERE c.parent_id=${c.id} ORDER BY c.sort_order, c.name`;
  return { ...c, parent, children };
});

export const listCollections = cache(async () => {
  const s = sqlPublic;
  return s<{ slug: string; title: string; description: string; count: number; updatedAt: Date; preview: { slug: string; name: string }[] }[]>`
    SELECT c.slug, c.title, c.description, c.updated_at AS "updatedAt", (SELECT count(*)::int FROM collection_items ci JOIN public_tools t ON t.id=ci.tool_id WHERE ci.collection_id=c.id) AS count,
      (SELECT coalesce(jsonb_agg(jsonb_build_object('slug', t.slug, 'name', t.name) ORDER BY ci.position), '[]') FROM (SELECT ci.* FROM collection_items ci JOIN public_tools t ON t.id=ci.tool_id WHERE ci.collection_id=c.id ORDER BY ci.position LIMIT 6) ci JOIN public_tools t ON t.id = ci.tool_id) AS preview
    FROM public_collections c ORDER BY c.sort_order, c.title`;
});
export const getCollection = cache(async (slug: string) => {
  const s = sqlPublic;
  const [c] = await s<{ id: string; slug: string; title: string; description: string; updatedAt: Date }[]>`SELECT id, slug, title, description, updated_at AS "updatedAt" FROM public_collections WHERE slug=${slug}`;
  if (!c) return null;
  const items = await s<(ToolCard & { blurb: string | null })[]>`SELECT ${s.unsafe(CARD_SELECT)}, ci.blurb FROM collection_items ci JOIN public_tools t ON t.id=ci.tool_id LEFT JOIN vendors v ON v.id=t.vendor_id WHERE ci.collection_id=${c.id} ORDER BY ci.position`;
  return { ...c, items };
});

export const homeData = cache(async () => {
  const s = sqlPublic;
  const [stats] = await s<{ tools: number; deep: number; categories: number; vendors: number; collections: number; lastVerified: Date | null }[]>`
    SELECT (SELECT count(*)::int FROM public_tools) AS tools, (SELECT count(*)::int FROM public_tools WHERE depth='deep') AS deep,
           (SELECT count(DISTINCT tc.category_id)::int FROM tool_categories tc JOIN public_tools t ON t.id=tc.tool_id) AS categories,
           (SELECT count(DISTINCT vendor_id)::int FROM public_tools) AS vendors, (SELECT count(*)::int FROM public_collections) AS collections,
           (SELECT max(checked_at) FROM public_tools) AS "lastVerified"`;
  const featured = await s<ToolCard[]>`SELECT ${s.unsafe(CARD_SELECT)} FROM public_tools t LEFT JOIN vendors v ON v.id=t.vendor_id WHERE t.featured ORDER BY t.quality_score DESC, t.name LIMIT 8`;
  const recent = await s<ToolCard[]>`SELECT ${s.unsafe(CARD_SELECT)} FROM public_tools t LEFT JOIN vendors v ON v.id=t.vendor_id WHERE t.checked_at IS NOT NULL ORDER BY t.checked_at DESC, t.name LIMIT 8`;
  const tree = await categoryTree();
  const collections = await listCollections();
  return { stats, featured, recent, tree, collections };
});

export const allPublicSlugs = cache(async () => sqlPublic<{ slug: string; updatedAt: Date }[]>`SELECT slug, updated_at AS "updatedAt" FROM public_tools ORDER BY slug`);
export const relatedTools = cache(async (toolId: string, limit = 6) => {
  const s = sqlPublic;
  return s<ToolCard[]>`SELECT ${s.unsafe(CARD_SELECT)} FROM public_tools t LEFT JOIN vendors v ON v.id=t.vendor_id
    WHERE t.id <> ${toolId} AND t.id IN (SELECT tc2.tool_id FROM tool_categories tc1 JOIN tool_categories tc2 ON tc2.category_id = tc1.category_id WHERE tc1.tool_id = ${toolId} AND tc1.is_primary)
      AND t.id NOT IN (SELECT alternative_id FROM tool_alternatives WHERE tool_id=${toolId})
    ORDER BY t.quality_score DESC, t.name LIMIT ${limit}`;
});
export const useCaseList = cache(async () => sqlPublic<{ slug: string; name: string; count: number }[]>`SELECT g.slug, g.name, count(*)::int FROM tags g JOIN tool_tags tt ON tt.tag_id=g.id JOIN public_tools t ON t.id=tt.tool_id WHERE g.kind='use_case' GROUP BY g.slug, g.name ORDER BY count DESC, g.name`);
export const tagList = cache(async () => sqlPublic<{ slug: string; name: string; count: number }[]>`SELECT g.slug, g.name, count(*)::int FROM tags g JOIN tool_tags tt ON tt.tag_id=g.id JOIN public_tools t ON t.id=tt.tool_id WHERE g.kind='tag' GROUP BY g.slug, g.name ORDER BY count DESC, g.name LIMIT 60`);