add rate limit to submittions

416bdd1 1 day ago

14.7 kB

import { randomUUID } from "node:crypto";
import { BigQuery } from "@google-cloud/bigquery";
import type { SupplyChainTool, ToolCategory, ToolStatus } from "@/lib/tool-types";
import { categories, toolStatuses } from "@/lib/tool-types";
import { parseGithubRepoUrl } from "@/lib/github";

type NewTool = Omit<
  SupplyChainTool,
  "id" | "slug" | "createdAt" | "averageRating" | "ratingCount" | "status"
>;

export class DuplicateRepoError extends Error {
  constructor() {
    super("This repository has already been submitted.");
    this.name = "DuplicateRepoError";
  }
}

type DemoState = {
  tools: SupplyChainTool[];
  ratings: Map<string, number>;
};

declare global {
  var __chainkitDemoState: DemoState | undefined;
}

const demoTools: SupplyChainTool[] = [
  {
    id: "intelligent-supply-chain",
    slug: "intelligent-supply-chain-management",
    name: "Intelligent Supply Chain Management",
    summary:
      "Azure ML accelerator using deep-learning forecasts and distributed computing to simulate inventory optimization.",
    category: "Planning",
    repoUrl: "https://github.com/MSUSAzureAccelerators/Intelligent-Supply-Chain-Management",
    websiteUrl: "",
    submittedBy: "Chainkit demo",
    status: "approved",
    createdAt: "2026-11-06T10:00:00.000Z",
    averageRating: 0,
    ratingCount: 0,
  },
  {
    id: "scimai-gym",
    slug: "scimai-gym",
    name: "SCIMAI Gym",
    summary:
      "Python environment for supply-chain inventory management experiments with deep reinforcement learning.",
    category: "Planning",
    repoUrl: "https://github.com/frenkowski/SCIMAI-Gym",
    websiteUrl: "",
    submittedBy: "Chainkit demo",
    status: "approved",
    createdAt: "2026-11-06T10:00:00.000Z",
    averageRating: 0,
    ratingCount: 0,
  },
  {
    id: "product-demand-forecasting",
    slug: "product-demand-forecasting-ml",
    name: "Product Demand Forecasting with ML",
    summary:
      "Machine-learning project exploring product demand forecasting for supply-chain planning.",
    category: "Planning",
    repoUrl: "https://github.com/trydoff/Product-Demand-Forecasting-Using-ML",
    websiteUrl: "",
    submittedBy: "Chainkit demo",
    status: "approved",
    createdAt: "2026-11-06T10:00:00.000Z",
    averageRating: 0,
    ratingCount: 0,
  },
  {
    id: "timefold-solver",
    slug: "timefold-solver",
    name: "Timefold Solver",
    summary:
      "Open-source AI solver for vehicle routing, task assignment, and other operational planning problems.",
    category: "Logistics",
    repoUrl: "https://github.com/TimefoldAI/timefold-solver",
    websiteUrl: "",
    submittedBy: "Chainkit demo",
    status: "approved",
    createdAt: "2026-11-06T10:00:00.000Z",
    averageRating: 0,
    ratingCount: 0,
  },
  {
    id: "late-delivery-prediction",
    slug: "supply-chain-late-delivery-prediction",
    name: "Late Delivery Prediction",
    summary:
      "Industrial ML pipeline for shipment-delay prediction with model comparisons and a Streamlit batch app.",
    category: "Visibility",
    repoUrl: "https://github.com/workkrishnpatel/supply-chain-late-delivery-prediction",
    websiteUrl: "",
    submittedBy: "Chainkit demo",
    status: "approved",
    createdAt: "2026-11-06T10:00:00.000Z",
    averageRating: 0,
    ratingCount: 0,
  },
  {
    id: "ai-warehouse-slotting",
    slug: "ai-warehouse-slotting",
    name: "AI Warehouse Slotting",
    summary:
      "Warehouse-management prototype integrating ML demand forecasting and dynamic slotting optimization.",
    category: "Warehousing",
    repoUrl: "https://github.com/ArwaElsayed5/Graduation-Project",
    websiteUrl: "",
    submittedBy: "Chainkit demo",
    status: "approved",
    createdAt: "2026-11-06T10:00:00.000Z",
    averageRating: 0,
    ratingCount: 0,
  },
  {
    id: "fraudlens-ai",
    slug: "fraudlens-ai",
    name: "FraudLens AI",
    summary:
      "Procurement audit prototype that applies anomaly detection and NLP to flag suspicious bid and vendor patterns.",
    category: "Procurement",
    repoUrl: "https://github.com/thenishantraj/fraudlens-ai",
    websiteUrl: "",
    submittedBy: "Chainkit demo",
    status: "approved",
    createdAt: "2026-11-06T10:00:00.000Z",
    averageRating: 0,
    ratingCount: 0,
  },
];

function getDemoState(): DemoState {
  globalThis.__chainkitDemoState ??= {
    tools: demoTools.map((tool) => ({ ...tool })),
    ratings: new Map(),
  };
  return globalThis.__chainkitDemoState;
}

function getBigQueryConfig() {
  const projectId = process.env.BIGQUERY_PROJECT_ID;
  const dataset = process.env.BIGQUERY_DATASET;
  if (!projectId && !dataset) return undefined;
  if (!projectId || !dataset) {
    throw new Error(
      "Set both BIGQUERY_PROJECT_ID and BIGQUERY_DATASET to enable BigQuery.",
    );
  }
  if (!/^[a-z][a-z0-9-]{4,28}[a-z0-9]$/.test(projectId)) {
    throw new Error("BIGQUERY_PROJECT_ID is not a valid Google Cloud project ID.");
  }
  if (!/^[A-Za-z0-9_]{1,1024}$/.test(dataset)) {
    throw new Error("BIGQUERY_DATASET contains unsupported characters.");
  }
  return { projectId, dataset };
}

let bigQueryClient: BigQuery | undefined;

function getBigQuery() {
  const config = getBigQueryConfig();
  if (!config) return undefined;
  bigQueryClient ??= new BigQuery({ projectId: config.projectId });
  return { client: bigQueryClient, ...config };
}

function toolTable(projectId: string, dataset: string) {
  return `\`${projectId}.${dataset}.tools\``;
}

function ratingTable(projectId: string, dataset: string) {
  return `\`${projectId}.${dataset}.tool_ratings\``;
}

function isToolStatus(value: string): value is ToolStatus {
  return toolStatuses.some((item) => item === value);
}

function isToolCategory(value: string): value is ToolCategory {
  return categories.some((item) => item === value);
}

function mapBigQueryRow(row: Record<string, unknown>): SupplyChainTool {
  const category = String(row.category);
  if (!isToolCategory(category)) {
    throw new Error(`BigQuery returned an unsupported category: ${category}`);
  }
  const status = String(row.status ?? "approved");
  if (!isToolStatus(status)) {
    throw new Error(`BigQuery returned an unsupported status: ${status}`);
  }
  return {
    id: String(row.id),
    slug: String(row.slug),
    name: String(row.name),
    summary: String(row.summary),
    category,
    repoUrl: String(row.repoUrl),
    websiteUrl: String(row.websiteUrl ?? ""),
    submittedBy: String(row.submittedBy),
    status,
    createdAt: new Date(String(row.createdAt)).toISOString(),
    averageRating: Number(row.averageRating ?? 0),
    ratingCount: Number(row.ratingCount ?? 0),
  };
}

const APPROVED_ONLY = "WHERE COALESCE(t.status, 'approved') = 'approved'";

function buildToolQuery(
  projectId: string,
  dataset: string,
  where = "",
  order = "",
) {
  return `
    SELECT
      t.tool_id AS id,
      t.slug,
      t.name,
      t.summary,
      t.category,
      t.repo_url AS repoUrl,
      t.website_url AS websiteUrl,
      t.submitted_by AS submittedBy,
      COALESCE(t.status, 'approved') AS status,
      t.created_at AS createdAt,
      COALESCE(AVG(r.rating), 0) AS averageRating,
      COUNT(r.rating) AS ratingCount
    FROM ${toolTable(projectId, dataset)} AS t
    LEFT JOIN ${ratingTable(projectId, dataset)} AS r
      ON t.tool_id = r.tool_id
    ${where}
    GROUP BY
      t.tool_id, t.slug, t.name, t.summary, t.category, t.repo_url,
      t.website_url, t.submitted_by, t.status, t.created_at
    ${order}
  `;
}

export async function getTools(): Promise<SupplyChainTool[]> {
  const bigQuery = getBigQuery();
  if (!bigQuery) return getDemoState().tools.filter((tool) => tool.status === "approved");

  const [rows] = await bigQuery.client.query({
    query: buildToolQuery(
      bigQuery.projectId,
      bigQuery.dataset,
      APPROVED_ONLY,
      "ORDER BY t.created_at DESC",
    ),
  });
  return rows.map(mapBigQueryRow);
}

/** Moderation view: tools in a given status, newest first. */
export async function listToolsByStatus(status: ToolStatus): Promise<SupplyChainTool[]> {
  const bigQuery = getBigQuery();
  if (!bigQuery) return getDemoState().tools.filter((tool) => tool.status === status);

  const [rows] = await bigQuery.client.query({
    query: buildToolQuery(
      bigQuery.projectId,
      bigQuery.dataset,
      "WHERE COALESCE(t.status, 'approved') = @status",
      "ORDER BY t.created_at DESC",
    ),
    params: { status },
  });
  return rows.map(mapBigQueryRow);
}

export async function setToolStatus(toolId: string, status: ToolStatus) {
  const bigQuery = getBigQuery();
  if (!bigQuery) {
    const tool = getDemoState().tools.find((candidate) => candidate.id === toolId);
    if (!tool) return false;
    tool.status = status;
    return true;
  }
  const [job] = await bigQuery.client.createQueryJob({
    query: `
      UPDATE ${toolTable(bigQuery.projectId, bigQuery.dataset)}
      SET status = @status
      WHERE tool_id = @toolId
    `,
    params: { toolId, status },
  });
  await job.getQueryResults();
  return (await affectedRows(job)) > 0;
}

export async function getToolBySlug(slug: string) {
  const bigQuery = getBigQuery();
  if (!bigQuery) {
    return getDemoState().tools.find(
      (tool) => tool.slug === slug && tool.status === "approved",
    );
  }
  const [rows] = await bigQuery.client.query({
    query: `${buildToolQuery(
      bigQuery.projectId,
      bigQuery.dataset,
      `${APPROVED_ONLY} AND t.slug = @slug`,
    )} LIMIT 1`,
    params: { slug },
  });
  return rows[0] ? mapBigQueryRow(rows[0]) : undefined;
}

async function getToolById(toolId: string) {
  const bigQuery = getBigQuery();
  if (!bigQuery) {
    return getDemoState().tools.find(
      (tool) => tool.id === toolId && tool.status === "approved",
    );
  }
  const [rows] = await bigQuery.client.query({
    query: `${buildToolQuery(
      bigQuery.projectId,
      bigQuery.dataset,
      `${APPROVED_ONLY} AND t.tool_id = @toolId`,
    )} LIMIT 1`,
    params: { toolId },
  });
  return rows[0] ? mapBigQueryRow(rows[0]) : undefined;
}

async function affectedRows(job: { getMetadata: () => Promise<unknown[]> }) {
  const [metadata] = (await job.getMetadata()) as [
    { statistics?: { query?: { numDmlAffectedRows?: string } } },
  ];
  return Number(metadata.statistics?.query?.numDmlAffectedRows ?? 0);
}

/**
 * Stores a new submission as `pending`. Throws DuplicateRepoError when the same
 * repository (case-insensitive, ignoring `.git` and trailing slash) is already
 * listed or awaiting review.
 *
 * BigQuery cannot enforce uniqueness, so the check is part of the INSERT
 * statement. Two exactly simultaneous submissions can still race; the per-IP
 * rate limit makes that impractical to exploit.
 */
export async function submitTool(input: NewTool) {
  const repo = parseGithubRepoUrl(input.repoUrl);
  if (!repo) throw new Error("repoUrl must be a GitHub repository URL.");

  const createdAt = new Date().toISOString();
  const id = randomUUID();
  const slugBase =
    input.name
      .toLowerCase()
      .normalize("NFKD")
      .replace(/[\u0300-\u036f]/g, "")
      .replace(/[^a-z0-9]+/g, "-")
      .replace(/^-|-$/g, "")
      .slice(0, 48) || "supply-chain-tool";
  const tool: SupplyChainTool = {
    ...input,
    repoUrl: repo.canonicalUrl,
    id,
    slug: `${slugBase}-${id.slice(0, 8)}`,
    status: "pending",
    createdAt,
    averageRating: 0,
    ratingCount: 0,
  };
  const bigQuery = getBigQuery();

  if (!bigQuery) {
    const state = getDemoState();
    const duplicate = state.tools.some(
      (existing) =>
        existing.status !== "rejected" &&
        parseGithubRepoUrl(existing.repoUrl)?.repoKey === repo.repoKey,
    );
    if (duplicate) throw new DuplicateRepoError();
    state.tools.unshift(tool);
    return tool;
  }

  // The REGEXP_REPLACE branch also catches rows written before repo_key existed.
  const [job] = await bigQuery.client.createQueryJob({
    query: `
      INSERT INTO ${toolTable(bigQuery.projectId, bigQuery.dataset)}
        (tool_id, slug, name, summary, category, repo_url, repo_key, website_url, submitted_by, status, created_at)
      SELECT
        @id, @slug, @name, @summary, @category, @repoUrl, @repoKey, @websiteUrl, @submittedBy, 'pending', TIMESTAMP(@createdAt)
      FROM (SELECT 1)
      WHERE NOT EXISTS (
        SELECT 1 FROM ${toolTable(bigQuery.projectId, bigQuery.dataset)} AS existing
        WHERE COALESCE(existing.status, 'approved') != 'rejected'
          AND (
            existing.repo_key = @repoKey
            OR LOWER(REGEXP_REPLACE(existing.repo_url, r'(\.git)?/?$', '')) = CONCAT('https://github.com/', @repoKey)
          )
      )
    `,
    params: {
      id: tool.id,
      slug: tool.slug,
      name: tool.name,
      summary: tool.summary,
      category: tool.category,
      repoUrl: tool.repoUrl,
      repoKey: repo.repoKey,
      websiteUrl: tool.websiteUrl,
      submittedBy: tool.submittedBy,
      createdAt: tool.createdAt,
    },
  });
  await job.getQueryResults();
  if ((await affectedRows(job)) === 0) throw new DuplicateRepoError();
  return tool;
}

export async function rateTool(toolId: string, voterId: string, rating: number) {
  const bigQuery = getBigQuery();
  if (!bigQuery) {
    const state = getDemoState();
    const tool = state.tools.find(
      (candidate) => candidate.id === toolId && candidate.status === "approved",
    );
    if (!tool) return undefined;

    const key = `${toolId}:${voterId}`;
    const previousRating = state.ratings.get(key);
    const total = tool.averageRating * tool.ratingCount;
    const nextCount = tool.ratingCount + (previousRating === undefined ? 1 : 0);
    const nextTotal = total - (previousRating ?? 0) + rating;
    state.ratings.set(key, rating);
    tool.averageRating = Number((nextTotal / nextCount).toFixed(1));
    tool.ratingCount = nextCount;
    return tool;
  }

  const tool = await getToolById(toolId);
  if (!tool) return undefined;

  await bigQuery.client.query({
    query: `
      MERGE ${ratingTable(bigQuery.projectId, bigQuery.dataset)} AS target
      USING (SELECT @toolId AS tool_id, @voterId AS voter_id, @rating AS rating) AS source
        ON target.tool_id = source.tool_id AND target.voter_id = source.voter_id
      WHEN MATCHED THEN
        UPDATE SET rating = source.rating, updated_at = CURRENT_TIMESTAMP()
      WHEN NOT MATCHED THEN
        INSERT (tool_id, voter_id, rating, created_at, updated_at)
        VALUES (source.tool_id, source.voter_id, source.rating, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())
    `,
    params: { toolId, voterId, rating },
  });
  return getToolById(toolId);
}

	import { randomUUID } from "node:crypto";
	import { BigQuery } from "@google-cloud/bigquery";
	import type { SupplyChainTool, ToolCategory, ToolStatus } from "@/lib/tool-types";
	import { categories, toolStatuses } from "@/lib/tool-types";
	import { parseGithubRepoUrl } from "@/lib/github";

	type NewTool = Omit<
	SupplyChainTool,
	"id" \| "slug" \| "createdAt" \| "averageRating" \| "ratingCount" \| "status"
	>;

	export class DuplicateRepoError extends Error {
	constructor() {
	super("This repository has already been submitted.");
	this.name = "DuplicateRepoError";
	}
	}

	type DemoState = {
	tools: SupplyChainTool[];
	ratings: Map<string, number>;
	};

	declare global {
	var __chainkitDemoState: DemoState \| undefined;
	}

	const demoTools: SupplyChainTool[] = [
	{
	id: "intelligent-supply-chain",
	slug: "intelligent-supply-chain-management",
	name: "Intelligent Supply Chain Management",
	summary:
	"Azure ML accelerator using deep-learning forecasts and distributed computing to simulate inventory optimization.",
	category: "Planning",
	repoUrl: "https://github.com/MSUSAzureAccelerators/Intelligent-Supply-Chain-Management",
	websiteUrl: "",
	submittedBy: "Chainkit demo",
	status: "approved",
	createdAt: "2026-11-06T10:00:00.000Z",
	averageRating: 0,
	ratingCount: 0,
	},
	{
	id: "scimai-gym",
	slug: "scimai-gym",
	name: "SCIMAI Gym",
	summary:
	"Python environment for supply-chain inventory management experiments with deep reinforcement learning.",
	category: "Planning",
	repoUrl: "https://github.com/frenkowski/SCIMAI-Gym",
	websiteUrl: "",
	submittedBy: "Chainkit demo",
	status: "approved",
	createdAt: "2026-11-06T10:00:00.000Z",
	averageRating: 0,
	ratingCount: 0,
	},
	{
	id: "product-demand-forecasting",
	slug: "product-demand-forecasting-ml",
	name: "Product Demand Forecasting with ML",
	summary:
	"Machine-learning project exploring product demand forecasting for supply-chain planning.",
	category: "Planning",
	repoUrl: "https://github.com/trydoff/Product-Demand-Forecasting-Using-ML",
	websiteUrl: "",
	submittedBy: "Chainkit demo",
	status: "approved",
	createdAt: "2026-11-06T10:00:00.000Z",
	averageRating: 0,
	ratingCount: 0,
	},
	{
	id: "timefold-solver",
	slug: "timefold-solver",
	name: "Timefold Solver",
	summary:
	"Open-source AI solver for vehicle routing, task assignment, and other operational planning problems.",
	category: "Logistics",
	repoUrl: "https://github.com/TimefoldAI/timefold-solver",
	websiteUrl: "",
	submittedBy: "Chainkit demo",
	status: "approved",
	createdAt: "2026-11-06T10:00:00.000Z",
	averageRating: 0,
	ratingCount: 0,
	},
	{
	id: "late-delivery-prediction",
	slug: "supply-chain-late-delivery-prediction",
	name: "Late Delivery Prediction",
	summary:
	"Industrial ML pipeline for shipment-delay prediction with model comparisons and a Streamlit batch app.",
	category: "Visibility",
	repoUrl: "https://github.com/workkrishnpatel/supply-chain-late-delivery-prediction",
	websiteUrl: "",
	submittedBy: "Chainkit demo",
	status: "approved",
	createdAt: "2026-11-06T10:00:00.000Z",
	averageRating: 0,
	ratingCount: 0,
	},
	{
	id: "ai-warehouse-slotting",
	slug: "ai-warehouse-slotting",
	name: "AI Warehouse Slotting",
	summary:
	"Warehouse-management prototype integrating ML demand forecasting and dynamic slotting optimization.",
	category: "Warehousing",
	repoUrl: "https://github.com/ArwaElsayed5/Graduation-Project",
	websiteUrl: "",
	submittedBy: "Chainkit demo",
	status: "approved",
	createdAt: "2026-11-06T10:00:00.000Z",
	averageRating: 0,
	ratingCount: 0,
	},
	{
	id: "fraudlens-ai",
	slug: "fraudlens-ai",
	name: "FraudLens AI",
	summary:
	"Procurement audit prototype that applies anomaly detection and NLP to flag suspicious bid and vendor patterns.",
	category: "Procurement",
	repoUrl: "https://github.com/thenishantraj/fraudlens-ai",
	websiteUrl: "",
	submittedBy: "Chainkit demo",
	status: "approved",
	createdAt: "2026-11-06T10:00:00.000Z",
	averageRating: 0,
	ratingCount: 0,
	},
	];

	function getDemoState(): DemoState {
	globalThis.__chainkitDemoState ??= {
	tools: demoTools.map((tool) => ({ ...tool })),
	ratings: new Map(),
	};
	return globalThis.__chainkitDemoState;
	}

	function getBigQueryConfig() {
	const projectId = process.env.BIGQUERY_PROJECT_ID;
	const dataset = process.env.BIGQUERY_DATASET;
	if (!projectId && !dataset) return undefined;
	if (!projectId \|\| !dataset) {
	throw new Error(
	"Set both BIGQUERY_PROJECT_ID and BIGQUERY_DATASET to enable BigQuery.",
	);
	}
	if (!/^[a-z][a-z0-9-]{4,28}[a-z0-9]$/.test(projectId)) {
	throw new Error("BIGQUERY_PROJECT_ID is not a valid Google Cloud project ID.");
	}
	if (!/^[A-Za-z0-9_]{1,1024}$/.test(dataset)) {
	throw new Error("BIGQUERY_DATASET contains unsupported characters.");
	}
	return { projectId, dataset };
	}

	let bigQueryClient: BigQuery \| undefined;

	function getBigQuery() {
	const config = getBigQueryConfig();
	if (!config) return undefined;
	bigQueryClient ??= new BigQuery({ projectId: config.projectId });
	return { client: bigQueryClient, ...config };
	}

	function toolTable(projectId: string, dataset: string) {
	return `\`${projectId}.${dataset}.tools\``;
	}

	function ratingTable(projectId: string, dataset: string) {
	return `\`${projectId}.${dataset}.tool_ratings\``;
	}

	function isToolStatus(value: string): value is ToolStatus {
	return toolStatuses.some((item) => item === value);
	}

	function isToolCategory(value: string): value is ToolCategory {
	return categories.some((item) => item === value);
	}

	function mapBigQueryRow(row: Record<string, unknown>): SupplyChainTool {
	const category = String(row.category);
	if (!isToolCategory(category)) {
	throw new Error(`BigQuery returned an unsupported category: ${category}`);
	}
	const status = String(row.status ?? "approved");
	if (!isToolStatus(status)) {
	throw new Error(`BigQuery returned an unsupported status: ${status}`);
	}
	return {
	id: String(row.id),
	slug: String(row.slug),
	name: String(row.name),
	summary: String(row.summary),
	category,
	repoUrl: String(row.repoUrl),
	websiteUrl: String(row.websiteUrl ?? ""),
	submittedBy: String(row.submittedBy),
	status,
	createdAt: new Date(String(row.createdAt)).toISOString(),
	averageRating: Number(row.averageRating ?? 0),
	ratingCount: Number(row.ratingCount ?? 0),
	};
	}

	const APPROVED_ONLY = "WHERE COALESCE(t.status, 'approved') = 'approved'";

	function buildToolQuery(
	projectId: string,
	dataset: string,
	where = "",
	order = "",
	) {
	return `
	SELECT
	t.tool_id AS id,
	t.slug,
	t.name,
	t.summary,
	t.category,
	t.repo_url AS repoUrl,
	t.website_url AS websiteUrl,
	t.submitted_by AS submittedBy,
	COALESCE(t.status, 'approved') AS status,
	t.created_at AS createdAt,
	COALESCE(AVG(r.rating), 0) AS averageRating,
	COUNT(r.rating) AS ratingCount
	FROM ${toolTable(projectId, dataset)} AS t
	LEFT JOIN ${ratingTable(projectId, dataset)} AS r
	ON t.tool_id = r.tool_id
	${where}
	GROUP BY
	t.tool_id, t.slug, t.name, t.summary, t.category, t.repo_url,
	t.website_url, t.submitted_by, t.status, t.created_at
	${order}
	`;
	}

	export async function getTools(): Promise<SupplyChainTool[]> {
	const bigQuery = getBigQuery();
	if (!bigQuery) return getDemoState().tools.filter((tool) => tool.status === "approved");

	const [rows] = await bigQuery.client.query({
	query: buildToolQuery(
	bigQuery.projectId,
	bigQuery.dataset,
	APPROVED_ONLY,
	"ORDER BY t.created_at DESC",
	),
	});
	return rows.map(mapBigQueryRow);
	}

	/** Moderation view: tools in a given status, newest first. */
	export async function listToolsByStatus(status: ToolStatus): Promise<SupplyChainTool[]> {
	const bigQuery = getBigQuery();
	if (!bigQuery) return getDemoState().tools.filter((tool) => tool.status === status);

	const [rows] = await bigQuery.client.query({
	query: buildToolQuery(
	bigQuery.projectId,
	bigQuery.dataset,
	"WHERE COALESCE(t.status, 'approved') = @status",
	"ORDER BY t.created_at DESC",
	),
	params: { status },
	});
	return rows.map(mapBigQueryRow);
	}

	export async function setToolStatus(toolId: string, status: ToolStatus) {
	const bigQuery = getBigQuery();
	if (!bigQuery) {
	const tool = getDemoState().tools.find((candidate) => candidate.id === toolId);
	if (!tool) return false;
	tool.status = status;
	return true;
	}
	const [job] = await bigQuery.client.createQueryJob({
	query: `
	UPDATE ${toolTable(bigQuery.projectId, bigQuery.dataset)}
	SET status = @status
	WHERE tool_id = @toolId
	`,
	params: { toolId, status },
	});
	await job.getQueryResults();
	return (await affectedRows(job)) > 0;
	}

	export async function getToolBySlug(slug: string) {
	const bigQuery = getBigQuery();
	if (!bigQuery) {
	return getDemoState().tools.find(
	(tool) => tool.slug === slug && tool.status === "approved",
	);
	}
	const [rows] = await bigQuery.client.query({
	query: `${buildToolQuery(
	bigQuery.projectId,
	bigQuery.dataset,
	`${APPROVED_ONLY} AND t.slug = @slug`,
	)} LIMIT 1`,
	params: { slug },
	});
	return rows[0] ? mapBigQueryRow(rows[0]) : undefined;
	}

	async function getToolById(toolId: string) {
	const bigQuery = getBigQuery();
	if (!bigQuery) {
	return getDemoState().tools.find(
	(tool) => tool.id === toolId && tool.status === "approved",
	);
	}
	const [rows] = await bigQuery.client.query({
	query: `${buildToolQuery(
	bigQuery.projectId,
	bigQuery.dataset,
	`${APPROVED_ONLY} AND t.tool_id = @toolId`,
	)} LIMIT 1`,
	params: { toolId },
	});
	return rows[0] ? mapBigQueryRow(rows[0]) : undefined;
	}

	async function affectedRows(job: { getMetadata: () => Promise<unknown[]> }) {
	const [metadata] = (await job.getMetadata()) as [
	{ statistics?: { query?: { numDmlAffectedRows?: string } } },
	];
	return Number(metadata.statistics?.query?.numDmlAffectedRows ?? 0);
	}

	/**
	* Stores a new submission as `pending`. Throws DuplicateRepoError when the same
	* repository (case-insensitive, ignoring `.git` and trailing slash) is already
	* listed or awaiting review.
	*
	* BigQuery cannot enforce uniqueness, so the check is part of the INSERT
	* statement. Two exactly simultaneous submissions can still race; the per-IP
	* rate limit makes that impractical to exploit.
	*/
	export async function submitTool(input: NewTool) {
	const repo = parseGithubRepoUrl(input.repoUrl);
	if (!repo) throw new Error("repoUrl must be a GitHub repository URL.");

	const createdAt = new Date().toISOString();
	const id = randomUUID();
	const slugBase =
	input.name
	.toLowerCase()
	.normalize("NFKD")
	.replace(/[\u0300-\u036f]/g, "")
	.replace(/[^a-z0-9]+/g, "-")
	.replace(/^-\|-$/g, "")
	.slice(0, 48) \|\| "supply-chain-tool";
	const tool: SupplyChainTool = {
	...input,
	repoUrl: repo.canonicalUrl,
	id,
	slug: `${slugBase}-${id.slice(0, 8)}`,
	status: "pending",
	createdAt,
	averageRating: 0,
	ratingCount: 0,
	};
	const bigQuery = getBigQuery();

	if (!bigQuery) {
	const state = getDemoState();
	const duplicate = state.tools.some(
	(existing) =>
	existing.status !== "rejected" &&
	parseGithubRepoUrl(existing.repoUrl)?.repoKey === repo.repoKey,
	);
	if (duplicate) throw new DuplicateRepoError();
	state.tools.unshift(tool);
	return tool;
	}

	// The REGEXP_REPLACE branch also catches rows written before repo_key existed.
	const [job] = await bigQuery.client.createQueryJob({
	query: `
	INSERT INTO ${toolTable(bigQuery.projectId, bigQuery.dataset)}
	(tool_id, slug, name, summary, category, repo_url, repo_key, website_url, submitted_by, status, created_at)
	SELECT
	@id, @slug, @name, @summary, @category, @repoUrl, @repoKey, @websiteUrl, @submittedBy, 'pending', TIMESTAMP(@createdAt)
	FROM (SELECT 1)
	WHERE NOT EXISTS (
	SELECT 1 FROM ${toolTable(bigQuery.projectId, bigQuery.dataset)} AS existing
	WHERE COALESCE(existing.status, 'approved') != 'rejected'
	AND (
	existing.repo_key = @repoKey
	OR LOWER(REGEXP_REPLACE(existing.repo_url, r'(\.git)?/?$', '')) = CONCAT('https://github.com/', @repoKey)
	)
	)
	`,
	params: {
	id: tool.id,
	slug: tool.slug,
	name: tool.name,
	summary: tool.summary,
	category: tool.category,
	repoUrl: tool.repoUrl,
	repoKey: repo.repoKey,
	websiteUrl: tool.websiteUrl,
	submittedBy: tool.submittedBy,
	createdAt: tool.createdAt,
	},
	});
	await job.getQueryResults();
	if ((await affectedRows(job)) === 0) throw new DuplicateRepoError();
	return tool;
	}

	export async function rateTool(toolId: string, voterId: string, rating: number) {
	const bigQuery = getBigQuery();
	if (!bigQuery) {
	const state = getDemoState();
	const tool = state.tools.find(
	(candidate) => candidate.id === toolId && candidate.status === "approved",
	);
	if (!tool) return undefined;

	const key = `${toolId}:${voterId}`;
	const previousRating = state.ratings.get(key);
	const total = tool.averageRating * tool.ratingCount;
	const nextCount = tool.ratingCount + (previousRating === undefined ? 1 : 0);
	const nextTotal = total - (previousRating ?? 0) + rating;
	state.ratings.set(key, rating);
	tool.averageRating = Number((nextTotal / nextCount).toFixed(1));
	tool.ratingCount = nextCount;
	return tool;
	}

	const tool = await getToolById(toolId);
	if (!tool) return undefined;

	await bigQuery.client.query({
	query: `
	MERGE ${ratingTable(bigQuery.projectId, bigQuery.dataset)} AS target
	USING (SELECT @toolId AS tool_id, @voterId AS voter_id, @rating AS rating) AS source
	ON target.tool_id = source.tool_id AND target.voter_id = source.voter_id
	WHEN MATCHED THEN
	UPDATE SET rating = source.rating, updated_at = CURRENT_TIMESTAMP()
	WHEN NOT MATCHED THEN
	INSERT (tool_id, voter_id, rating, created_at, updated_at)
	VALUES (source.tool_id, source.voter_id, source.rating, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())
	`,
	params: { toolId, voterId, rating },
	});
	return getToolById(toolId);
	}