Function bodies 451 total

function fuzzyMatchList(input: string | undefined, validOptions: string[]): string[] {
  if (!input || !input.trim()) return [];

  // Split on commas, semicolons, or " and "
  const parts = input
    .split(/[,;]|\s+and\s+/i)
    .map((s) => s.trim())
    .filter(Boolean);

  const matched: string[] = [];
  for (const part of parts) {
    const lower = part.toLowerCase();
    // Exact match first
    const exact = validOptions.find((opt) => opt.toLowerCase() === lower);
    if (exact) {
      matched.push(exact);
      continue;
    }
    // Prefix match
    const prefix = validOptions.find((opt) => opt.toLowerCase().startsWith(lower));
    if (prefix) {
      matched.push(prefix);
      continue;
    }
    // Contains match
    const contains = validOptions.find((opt) => opt.toLowerCase().includes(lower));
    if (contains) {
      matched.push(contains);
      continue;
    }
    // Keep original if no match found
    matched.push(part);
  }

  return [...new Set(matched)];
}

function deriveProgram(speciesType: string): string {
  switch (speciesType) {
    case "Fish":
    case "Invert":
      return "fish";
    case "Plant":
      return "plant";
    case "Coral":
      return "coral";
    default:
      return "fish";
  }
}

async function matchSpecies(
  commonName: string | undefined,
  latinName: string | undefined
): Promise<SpeciesMatch | null> {
  if (!commonName && !latinName) return null;

  // Try exact latin name match first
  if (latinName && latinName.trim()) {
    const byLatin = await query<{
      common_name_id: number;
      scientific_name_id: number;
      group_id: number;
      common_name: string;
      scientific_name: string;
      base_points: number | null;
      program_class: string | null;
    }>(
      `SELECT
        cn.common_name_id,
        scin.scientific_name_id,
        sng.group_id,
        cn.common_name,
        scin.scientific_name,
        sng.base_points,
        sng.program_class
      FROM species_scientific_name scin
      JOIN species_name_group sng ON scin.group_id = sng.group_id
      LEFT JOIN species_common_name cn ON cn.group_id = sng.group_id
      WHERE LOWER(scin.scientific_name) = LOWER(?)
      LIMIT 1`,
      [latinName.trim()]
    );
    if (byLati

async function handleParseCsv(args: ParseCsvArgs) {
  const { file_path, preview_limit } = args;
  const { rows, columns } = await parseCsvFile(file_path);

  const displayRows = preview_limit ? rows.slice(0, preview_limit) : rows;

  // Per-row field presence diagnostics
  const diagnostics = displayRows.map((row, index) => {
    const missing: string[] = [];
    const present: string[] = [];
    for (const col of columns) {
      if (!row[col] || !row[col].trim()) {
        missing.push(col);
      } else {
        present.push(col);
      }
    }
    return { row_index: index, present_count: present.length, missing_fields: missing };
  });

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            file_path,
            total_rows: rows.length,
            columns,
            column_count: columns.length,
            displayed_rows: displayRows.length,
            rows: displayRows,
            

async function handleValidateImport(args: ValidateImportArgs) {
  const { file_path, member_id, admin_id, default_points } = args;
  const { rows } = await parseCsvFile(file_path);

  // Resolve member if member_id provided
  let resolvedMember: { id: number; display_name: string } | null = null;
  if (member_id) {
    const members = await query<{ id: number; display_name: string }>(
      "SELECT id, display_name FROM members WHERE id = ?",
      [member_id]
    );
    if (members.length > 0) {
      resolvedMember = members[0];
    }
  }

  const validations: RowValidation[] = [];
  let readyCount = 0;
  let attentionCount = 0;

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i];
    const warnings: string[] = [];

    // Member resolution
    let rowMemberId: number | null = resolvedMember?.id ?? null;
    let rowMemberName: string | null = resolvedMember?.display_name ?? null;

    if (!rowMemberId && row.member_name) {
      const memberMatches = await query<{ id: 

async function handleSearchMember(args: SearchMemberArgs) {
  const { name, email } = args;

  const conditions: string[] = [];
  const params: string[] = [];

  if (name) {
    conditions.push("LOWER(display_name) LIKE LOWER(?)");
    params.push(`%${name.trim()}%`);
  }

  if (email) {
    conditions.push("LOWER(contact_email) LIKE LOWER(?)");
    params.push(`%${email.trim()}%`);
  }

  if (conditions.length === 0) {
    throw new Error("Provide at least name or email");
  }

  const results = await query<{
    id: number;
    display_name: string;
    contact_email: string;
    is_admin: number;
  }>(
    `SELECT id, display_name, contact_email, is_admin
     FROM members
     WHERE ${conditions.join(" OR ")}
     ORDER BY display_name
     LIMIT 20`,
    params
  );

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            count: results.length,
            members: results.map((m) => ({
     

async function handleSearchSpecies(args: SearchSpeciesArgs) {
  const { query: searchQuery, species_type } = args;

  if (!searchQuery || searchQuery.trim().length < 2) {
    throw new Error("Query must be at least 2 characters");
  }

  const pattern = `%${searchQuery.trim()}%`;

  const typeCondition = species_type
    ? "AND sng.species_type = ?"
    : "";
  const typeParams = species_type ? [species_type] : [];

  const results = await query<{
    group_id: number;
    common_name_id: number;
    scientific_name_id: number;
    common_name: string;
    scientific_name: string;
    program_class: string;
    species_type: string;
    base_points: number | null;
  }>(
    `SELECT DISTINCT
      sng.group_id,
      cn.common_name_id,
      scin.scientific_name_id,
      cn.common_name,
      scin.scientific_name,
      sng.program_class,
      sng.species_type,
      sng.base_points
    FROM species_name_group sng
    LEFT JOIN species_common_name cn ON cn.group_id = sng.group_id
    

async function handleListCsvFiles(args: ListCsvFilesArgs) {
  const { directory_path } = args;

  const entries = await readdir(directory_path);
  const csvFiles: Array<{ name: string; path: string; size: number }> = [];

  for (const entry of entries) {
    if (extname(entry).toLowerCase() === ".csv") {
      const fullPath = join(directory_path, entry);
      const stats = await stat(fullPath);
      csvFiles.push({
        name: entry,
        path: fullPath,
        size: stats.size,
      });
    }
  }

  csvFiles.sort((a, b) => a.name.localeCompare(b.name));

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            directory: directory_path,
            count: csvFiles.length,
            files: csvFiles,
          },
          null,
          2
        ),
      },
    ],
  };
}

function createSpeciesServer(): Server {
  const server = new Server(
    {
      name: "species-database",
      version: "1.0.0",
    },
    {
      capabilities: {
        resources: {},
        tools: {},
      },
    }
  );

  initializeSpeciesServer(server);
  return server;
}

function createMemberServer(): Server {
  const server = new Server(
    {
      name: "member-management",
      version: "1.0.0",
    },
    {
      capabilities: {
        resources: {},
        tools: {},
      },
    }
  );

  initializeMemberServer(server);
  return server;
}

function createBackfillServer(): Server {
  const server = new Server(
    {
      name: "backfill",
      version: "1.0.0",
    },
    {
      capabilities: {
        resources: {},
        tools: {},
      },
    }
  );

  initializeBackfillServer(server);
  return server;
}

function createMcpHandler(
  serverName: string,
  transports: Record<string, StreamableHTTPServerTransport>,
  createServer: () => Server
) {
  return async (req: Request, res: Response) => {
    const sessionId = req.headers["mcp-session-id"] as string | undefined;

    try {
      let transport: StreamableHTTPServerTransport;

      if (sessionId && transports[sessionId]) {
        // Reuse existing transport for this session
        transport = transports[sessionId];
        await transport.handleRequest(req, res, req.body);
      } else if (!sessionId && isInitializeRequest(req.body)) {
        // New initialization request - create new transport and session
        logger.info(`${serverName} MCP client initializing new session [${domain}]`);

        transport = new StreamableHTTPServerTransport({
          sessionIdGenerator: () => randomUUID(),
          onsessioninitialized: (sid) => {
            logger.info(`${serverName} session initialized: ${sid}`);
            transports

export async function startMcpHttpServer(): Promise<void> {
  if (!config.mcp?.enabled) {
    logger.info("MCP HTTP server disabled in config");
    return;
  }

  const mcpPort = config.mcp.port || 3001;
  const mcpHost = config.mcp.host || "127.0.0.1";

  const app = express();

  app.use(express.json());

  // Health check endpoint
  app.get("/health", (_req, res) => {
    res.json({
      status: "ok",
      domain,
      database: dbName,
      databasePath: dbPath,
      servers: ["species", "members", "backfill"],
    });
  });

  // Species MCP Server - POST for main communication
  const speciesHandler = createMcpHandler(
    "Species",
    speciesTransports,
    createSpeciesServer
  );
  app.post("/mcp/species", speciesHandler);
  app.get("/mcp/species", speciesHandler); // Also support GET for SSE

  // Member Management MCP Server - POST for main communication
  const memberHandler = createMcpHandler(
    "Members",
    memberTransports,
    createMemberServer
  );
  app.p

export async function stopMcpHttpServer(): Promise<void> {
  // Close all active transports
  for (const sessionId in speciesTransports) {
    try {
      await speciesTransports[sessionId].close();
      delete speciesTransports[sessionId];
    } catch (error) {
      logger.error(`Error closing species transport ${sessionId}:`, error);
    }
  }

  for (const sessionId in memberTransports) {
    try {
      await memberTransports[sessionId].close();
      delete memberTransports[sessionId];
    } catch (error) {
      logger.error(`Error closing member transport ${sessionId}:`, error);
    }
  }

  for (const sessionId in backfillTransports) {
    try {
      await backfillTransports[sessionId].close();
      delete backfillTransports[sessionId];
    } catch (error) {
      logger.error(`Error closing backfill transport ${sessionId}:`, error);
    }
  }

  // Close HTTP server
  if (serverInstance) {
    return new Promise((resolve, reject) => {
      serverInstance?.close((err) => {

async function handleListMembers(args: ListMembersArgs) {
  const { query: searchQuery, is_admin, has_submissions, limit = 100, offset = 0 } = args;

  const conditions: string[] = ["1=1"];
  const params: (string | number)[] = [];

  if (is_admin !== undefined) {
    conditions.push("m.is_admin = ?");
    params.push(is_admin ? 1 : 0);
  }

  if (searchQuery && typeof searchQuery === 'string' && searchQuery.trim().length >= 2) {
    const searchPattern = `%${searchQuery.trim().toLowerCase()}%`;
    conditions.push("(LOWER(m.display_name) LIKE ? OR LOWER(m.contact_email) LIKE ?)");
    params.push(searchPattern, searchPattern);
  }

  let sql = `
    SELECT m.*, COUNT(s.id) as submission_count
    FROM members m
    LEFT JOIN submissions s ON m.id = s.member_id AND s.approved_on IS NOT NULL
    WHERE ${conditions.join(" AND ")}
    GROUP BY m.id
  `;

  if (has_submissions !== undefined) {
    sql += has_submissions ? " HAVING submission_count > 0" : " HAVING submission_count = 0";
  }

async function handleGetMemberDetail(args: GetMemberDetailArgs) {
  const { member_id } = args;

  const members = await query<Member>("SELECT * FROM members WHERE id = ?", [member_id]);

  if (members.length === 0) {
    throw new Error(`Member ${member_id} not found`);
  }

  const member = members[0];

  // Get submission counts
  const submissions = await query<{ count: number }>(
    "SELECT COUNT(*) as count FROM submissions WHERE member_id = ?",
    [member_id]
  );

  const approvedSubmissions = await query<{ count: number }>(
    "SELECT COUNT(*) as count FROM submissions WHERE member_id = ? AND approved_on IS NOT NULL",
    [member_id]
  );

  const totalPoints = await query<{ total: number }>(
    `
    SELECT SUM(
      points +
      IFNULL(article_points, 0) +
      (IFNULL(first_time_species, 0) * 5) +
      (IFNULL(flowered, 0) * points) +
      (IFNULL(sexual_reproduction, 0) * points)
    ) as total
    FROM submissions
    WHERE member_id = ? AND approved_on IS NOT N

async function handleMergeMembers(args: MergeMembersArgs) {
  const { from_member_id, to_member_id, preview } = args;

  if (from_member_id === to_member_id) {
    throw new Error("Cannot merge a member into itself");
  }

  // Get both members
  const fromMembers = await query<Member>("SELECT * FROM members WHERE id = ?", [from_member_id]);
  const toMembers = await query<Member>("SELECT * FROM members WHERE id = ?", [to_member_id]);

  if (fromMembers.length === 0) {
    throw new Error(`Source member ${from_member_id} not found`);
  }
  if (toMembers.length === 0) {
    throw new Error(`Destination member ${to_member_id} not found`);
  }

  const fromMember = fromMembers[0];
  const toMember = toMembers[0];

  // Get counts for preview
  const submissionCount = await query<{ count: number }>(
    "SELECT COUNT(*) as count FROM submissions WHERE member_id = ?",
    [from_member_id]
  );

  const awardCount = await query<{ count: number }>(
    "SELECT COUNT(*) as count FROM awards WH

async function handleUpdateMember(args: UpdateMemberArgs) {
  const { member_id, contact_email, display_name } = args;

  const updates: string[] = [];
  const values: (string | number)[] = [];

  if (contact_email !== undefined && typeof contact_email === 'string') {
    updates.push("contact_email = ?");
    values.push(contact_email.trim());
  }
  if (display_name !== undefined && typeof display_name === 'string') {
    updates.push("display_name = ?");
    values.push(display_name.trim());
  }

  if (updates.length === 0) {
    throw new Error("No fields to update");
  }

  values.push(member_id);

  await withTransaction(async (db) => {
    const stmt = await db.prepare(`
      UPDATE members
      SET ${updates.join(", ")}
      WHERE id = ?
    `);
    await stmt.run(...values);
    await stmt.finalize();
  });

  // Fetch updated member
  const updated = await query<Member>("SELECT * FROM members WHERE id = ?", [member_id]);

  return {
    content: [
      {
        type: "tex

async function handleDeleteMember(args: DeleteMemberArgs) {
  const { member_id, force } = args;

  // Check if member exists
  const members = await query<Member>("SELECT * FROM members WHERE id = ?", [member_id]);
  if (members.length === 0) {
    throw new Error(`Member ${member_id} not found`);
  }

  // Check for approved submissions
  const approvedSubmissions = await query<{ count: number }>(
    "SELECT COUNT(*) as count FROM submissions WHERE member_id = ? AND approved_on IS NOT NULL",
    [member_id]
  );

  const approvedCount = approvedSubmissions[0]?.count || 0;

  if (approvedCount > 0 && !force) {
    throw new Error(
      `Member has ${approvedCount} approved submissions. Use force: true to delete anyway.`
    );
  }

  // Get submission count before delete
  const allSubmissions = await query<{ count: number }>(
    "SELECT COUNT(*) as count FROM submissions WHERE member_id = ?",
    [member_id]
  );

  const submissionCount = allSubmissions[0]?.count || 0;

  await w

async function handleSetAdminStatus(args: SetAdminStatusArgs) {
  const { member_id, is_admin } = args;

  const members = await query<Member>("SELECT * FROM members WHERE id = ?", [member_id]);
  if (members.length === 0) {
    throw new Error(`Member ${member_id} not found`);
  }

  await withTransaction(async (db) => {
    const stmt = await db.prepare("UPDATE members SET is_admin = ? WHERE id = ?");
    await stmt.run(is_admin ? 1 : 0, member_id);
    await stmt.finalize();
  });

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            member_id,
            display_name: members[0].display_name,
            is_admin,
            message: `Admin status ${is_admin ? "granted" : "revoked"}`,
          },
          null,
          2
        ),
      },
    ],
  };
}

async function formatSpeciesGroup(group: SpeciesNameGroup): Promise<{
  group_id: number;
  program_class: string;
  canonical_genus: string;
  canonical_species_name: string;
  species_type: string;
  base_points: number | null;
  is_cares_species: boolean;
  external_references: string[];
  image_links: string[];
}> {

async function handleCreateSpeciesGroup(args: CreateSpeciesGroupArgs) {
  const {
    program_class,
    canonical_genus,
    canonical_species_name,
    species_type,
    base_points,
    is_cares_species,
  } = args;

  const group_id = await createSpeciesGroup({
    programClass: program_class,
    speciesType: species_type,
    canonicalGenus: canonical_genus,
    canonicalSpeciesName: canonical_species_name,
    basePoints: base_points,
    isCaresSpecies: is_cares_species,
  });

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            group_id,
            message: "Species group created successfully",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleUpdateSpeciesGroup(args: UpdateSpeciesGroupArgs) {
  const { group_id, program_class, base_points, is_cares_species, external_references, image_links } = args;

  const changes = await updateSpeciesGroup(group_id, {
    programClass: program_class,
    basePoints: base_points,
    isCaresSpecies: is_cares_species,
    externalReferences: external_references,
    imageLinks: image_links,
  });

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            group_id,
            changes,
            updated_fields: Object.keys(args).filter((k) => k !== "group_id"),
            message: changes > 0 ? "Species group updated successfully" : "Species group not found",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleDeleteSpeciesGroup(args: DeleteSpeciesGroupArgs) {
  const { group_id, force } = args;

  const changes = await deleteSpeciesGroup(group_id, force);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            group_id,
            changes,
            message: changes > 0 ? "Species group deleted successfully" : "Species group not found",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleAddSpeciesSynonym(args: AddSpeciesSynonymArgs) {
  const { group_id, common_name, scientific_name } = args;

  const name_id = await addSynonym(group_id, common_name, scientific_name);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            name_id,
            group_id,
            message: "Synonym added successfully",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleUpdateSpeciesSynonym(args: UpdateSpeciesSynonymArgs) {
  const { name_id, common_name, scientific_name } = args;

  const changes = await updateSynonym(name_id, {
    commonName: common_name,
    scientificName: scientific_name,
  });

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            name_id,
            changes,
            message: changes > 0 ? "Synonym updated successfully" : "Synonym not found",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleDeleteSpeciesSynonym(args: DeleteSpeciesSynonymArgs) {
  const { name_id, force } = args;

  const changes = await deleteSynonym(name_id, force);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            name_id,
            changes,
            message: changes > 0 ? "Synonym deleted successfully" : "Synonym not found",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleListCommonNamesByText(args: ListCommonNamesByTextArgs) {
  const { common_name, limit } = args;

  const results = await getCommonNamesByText(common_name, limit);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            common_name,
            count: results.length,
            results,
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleDeleteCommonNameById(args: DeleteCommonNameByIdArgs) {
  const { common_name_id } = args;

  const changes = await deleteCommonName(common_name_id);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            common_name_id,
            changes,
            message: changes > 0 ? "Common name deleted successfully" : "Common name not found",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleBulkDeleteCommonNames(args: BulkDeleteCommonNamesArgs) {
  const { common_name, common_name_ids, preview = false } = args;

  // Validate that we have either common_name or common_name_ids
  if (!common_name && !common_name_ids) {
    throw new Error("Must provide either 'common_name' or 'common_name_ids'");
  }

  if (common_name && common_name_ids) {
    throw new Error("Cannot provide both 'common_name' and 'common_name_ids'");
  }

  const options = common_name ? { commonName: common_name } : { commonNameIds: common_name_ids! };
  const result = await bulkDeleteCommonNames(options, preview);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            preview,
            count: result.count,
            ...(result.preview && { preview_results: result.preview }),
            message: preview
              ? `Preview: Would delete ${result.count} common name(s)`
              :

async function handleMergeSpeciesGroups(args: MergeSpeciesGroupsArgs) {
  const { canonical_group_id, defunct_group_id, preview } = args;

  if (canonical_group_id === defunct_group_id) {
    throw new Error("Cannot merge a species with itself");
  }

  // Get both groups
  const groups = await query<SpeciesNameGroup>(
    "SELECT * FROM species_name_group WHERE group_id IN (?, ?)",
    [canonical_group_id, defunct_group_id]
  );

  if (groups.length !== 2) {
    throw new Error("One or both species groups not found");
  }

  const canonical = groups.find((g) => g.group_id === canonical_group_id);
  const defunct = groups.find((g) => g.group_id === defunct_group_id);

  // Get synonyms to move (from both split tables)
  const commonNamesToMove = await query<{ common_name_id: number; common_name: string }>(
    "SELECT * FROM species_common_name WHERE group_id = ?",
    [defunct_group_id]
  );
  const scientificNamesToMove = await query<{ scientific_name_id: number; scientific_name: str

async function handleSearchSpecies(args: SearchSpeciesArgs) {
  const {
    query: searchQuery,
    species_type,
    program_class,
    has_base_points,
    is_cares_species,
    sort_by = "name",
    limit = 100,
    offset = 0,
    count_only = false,
  } = args;

  const filters: SpeciesAdminFilters = {
    species_type,
    program_class,
    has_base_points,
    is_cares_species,
    search: searchQuery,
  };

  const result = await getSpeciesForAdmin(filters, sort_by as "name" | "points" | "class" | undefined, limit, offset);

  // If count_only is true, return just the count
  if (count_only) {
    return {
      content: [
        {
          type: "text" as const,
          text: JSON.stringify(
            {
              success: true,
              total_count: result.total_count,
              count_only: true,
            },
            null,
            2
          ),
        },
      ],
    };
  }

  return {
    content: [
      {
        type: "text" as const,
      

async function handleGetSpeciesDetail(args: GetSpeciesDetailArgs) {
  const { group_id } = args;

  const speciesDetail = await getSpeciesDetail(group_id);

  if (!speciesDetail) {
    throw new Error(`Species group ${group_id} not found`);
  }

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            species: speciesDetail,
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleSetBasePoints(args: SetBasePointsArgs) {
  const { group_id, group_ids, species_type, program_class, base_points, preview } = args;

  // Determine which species to update
  let targetGroupIds: number[] = [];

  if (group_id) {
    targetGroupIds = [group_id];
  } else if (group_ids && group_ids.length > 0) {
    targetGroupIds = group_ids;
  } else if (species_type || program_class) {
    // Query to get group_ids matching filters
    const filters: SpeciesAdminFilters = { species_type, program_class };
    const result = await getSpeciesForAdmin(filters, "name", 10000, 0);
    targetGroupIds = result.species.map((s) => s.group_id);
  } else {
    throw new Error("Must provide group_id, group_ids, or species_type/program_class filter");
  }

  if (targetGroupIds.length === 0) {
    return {
      content: [
        {
          type: "text" as const,
          text: JSON.stringify(
            {
              success: true,
              updated_count: 0,
         

async function handleToggleCaresStatus(args: ToggleCaresStatusArgs) {
  const { group_id, is_cares_species } = args;

  const changes = await updateSpeciesGroup(group_id, {
    isCaresSpecies: is_cares_species,
  });

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            group_id,
            changes,
            is_cares_species,
            message: changes > 0 ? "CARES status updated" : "Species group not found",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleUpdateCanonicalName(args: UpdateCanonicalNameArgs) {
  const {
    group_id,
    new_canonical_genus,
    new_canonical_species_name,
    preserve_old_as_synonym = true,
  } = args;

  if (!new_canonical_genus && !new_canonical_species_name) {
    throw new Error("At least one new field must be provided");
  }

  const groups = await query<SpeciesNameGroup>(
    "SELECT * FROM species_name_group WHERE group_id = ?",
    [group_id]
  );

  if (groups.length === 0) {
    throw new Error(`Species group ${group_id} not found`);
  }

  const oldGroup = groups[0];
  const oldCanonicalName = `${oldGroup.canonical_genus} ${oldGroup.canonical_species_name}`;

  await withTransaction(async (db) => {
    // Update canonical name
    const updates: string[] = [];
    const values: (string | number)[] = [];

    if (new_canonical_genus) {
      updates.push("canonical_genus = ?");
      values.push(new_canonical_genus.trim());
    }
    if (new_canonical_species_name) {
      u

async function handleSyncIucnData(args: SyncIucnDataArgs) {
  const { group_id, group_ids, sync_missing, days_old, limit = 10, preview = false } = args;

  const database = db(true); // Write access for IUCN data updates
  let targetSpecies: { group_id: number; canonical_genus: string; canonical_species_name: string }[] = [];

  // Determine which species to sync
  if (group_id) {
    const species = await query<{ group_id: number; canonical_genus: string; canonical_species_name: string }>(
      "SELECT group_id, canonical_genus, canonical_species_name FROM species_name_group WHERE group_id = ?",
      [group_id]
    );
    if (species.length === 0) {
      throw new Error(`Species group ${group_id} not found`);
    }
    targetSpecies = species;
  } else if (group_ids && group_ids.length > 0) {
    targetSpecies = await query<{ group_id: number; canonical_genus: string; canonical_species_name: string }>(
      `SELECT group_id, canonical_genus, canonical_species_name FROM species_nam

async function handleGetIucnSyncLog(args: GetIucnSyncLogArgs) {
  const { group_id, limit = 100 } = args;

  const database = db();
  const log = await getIucnSyncLog(database, group_id, limit);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            total_entries: log.length,
            log_entries: log,
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleGetSpeciesMissingIucn() {
  const database = db();
  const missing = await getSpeciesWithMissingIucn(database);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            total_count: missing.length,
            species: missing,
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleGetSpeciesNeedingResync(args: GetSpeciesNeedingResyncArgs) {
  const { days_old = 365 } = args;

  const database = db();
  const needingResync = await getSpeciesNeedingResync(database, days_old);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            total_count: needingResync.length,
            days_old_threshold: days_old,
            species: needingResync,
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleGetIucnSyncStats() {
  const database = db();
  const stats = await getIucnSyncStats(database);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            stats,
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleGetCanonicalRecommendations(args: GetCanonicalRecommendationsArgs) {
  const { group_id, status, limit } = args;

  const database = db();
  const recommendations = await getCanonicalRecommendations(database, {
    groupId: group_id,
    status,
    limit,
  });

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success: true,
            total_count: recommendations.length,
            recommendations: recommendations.map((rec) => ({
              id: rec.id,
              group_id: rec.group_id,
              current_name: `${rec.current_canonical_genus} ${rec.current_canonical_species}`,
              suggested_name: `${rec.suggested_canonical_genus} ${rec.suggested_canonical_species}`,
              iucn_taxon_id: rec.iucn_taxon_id,
              iucn_url: rec.iucn_url,
              reason: rec.reason,
              status: rec.status,
              created_at: rec.created_at,
              r

async function handleAcceptCanonicalRecommendation(args: AcceptCanonicalRecommendationArgs) {
  const { recommendation_id, reviewed_by } = args;

  const database = db(true); // Write access

  const success = await acceptCanonicalRecommendation(database, recommendation_id, reviewed_by);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success,
            recommendation_id,
            reviewed_by,
            message: "Canonical name recommendation accepted and applied successfully",
          },
          null,
          2
        ),
      },
    ],
  };
}

async function handleRejectCanonicalRecommendation(args: RejectCanonicalRecommendationArgs) {
  const { recommendation_id, reviewed_by } = args;

  const database = db(true); // Write access

  const success = await rejectCanonicalRecommendation(database, recommendation_id, reviewed_by);

  return {
    content: [
      {
        type: "text" as const,
        text: JSON.stringify(
          {
            success,
            recommendation_id,
            reviewed_by,
            message: "Canonical name recommendation rejected successfully",
          },
          null,
          2
        ),
      },
    ],
  };
}

export function getUploadRateLimiters() {
  return [
    strictUploadLimiter, // First check unauthenticated limits
    uploadRateLimiter, // Then check per-minute limits
    dailyUploadLimiter, // Finally check daily limits
  ];
}

export async function onSubmissionSend(sub: Submission, member: MemberRecord) {
  if (EMAILS_DISABLED) {
    logger.info("Email disabled - would have sent submission confirmation", { submissionId: sub.id });
    return;
  }

  const admins = await getAdminEmails();

  return transporter.sendMail({
    from: fromEmail,
    to: member.contact_email,
    cc: admins,
    bcc: DEBUG_EMAIL,
    subject: `Submission Confirmation - ${sub.species_common_name}`,
    html: renderOnSubmission({
      domain: config.server.domain,
      submission: sub,
      member,
    }),
  });
}

export async function sendChangesRequest(sub: Submission, contact_email: string, content: string) {
  if (EMAILS_DISABLED) {
    logger.info("Email disabled - would have sent changes request", { submissionId: sub.id });
    return;
  }

  const admins = await getAdminEmails();

  return transporter.sendMail({
    from: fromEmail,
    to: contact_email,
    cc: admins,
    bcc: DEBUG_EMAIL,
    subject: `Changes Requested - ${sub.species_common_name}`,
    text: content,
  });
}

export async function onChangesRequested(
  submission: Submission,
  member: MemberRecord,
  reason: string
) {
  if (EMAILS_DISABLED) {
    logger.info("Email disabled - would have sent changes requested", { submissionId: submission.id });
    return;
  }

  const admins = await getAdminEmails();

  return transporter.sendMail({
    from: fromEmail,
    to: member.contact_email,
    cc: admins,
    bcc: DEBUG_EMAIL,
    subject: `Changes Requested - ${submission.species_common_name}`,
    html: renderOnChangesRequested({
      domain: config.server.domain,
      submission,
      member,
      reason,
      programContactEmail: config.email.adminsEmail,
    }),
  });
}

export async function onSubmissionApprove(sub: Submission, member: MemberRecord) {
  if (EMAILS_DISABLED) {
    logger.info("Email disabled - would have sent submission approval", { submissionId: sub.id });
    return;
  }

  return transporter.sendMail({
    from: fromEmail,
    to: member.contact_email,
    bcc: DEBUG_EMAIL,
    subject: `Submission Approved! - ${sub.species_common_name}`,
    html: renderOnApprove({
      domain: config.server.domain,
      submission: sub,
      member,
    }),
  });
}

export async function sendResetEmail(email: string, display_name: string, code: string) {
  if (EMAILS_DISABLED) {
    logger.info("Email disabled - would have sent password reset", { email });
    return;
  }

  return transporter.sendMail({
    from: fromEmail,
    to: email,
    subject: "Reset Password",
    html: renderResetEmail({
      domain: config.server.domain,
      display_name,
      code,
      programContactEmail: config.email.adminsEmail,
    }),
  });
}