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);
}