Function bodies 451 total

export async function getSpeciesNeedingResync(
  db: Database,
  daysOld: number
): Promise<SpeciesNeedingResync[]> {
  return await db.all(
    `
    SELECT
      group_id,
      canonical_genus,
      canonical_species_name,
      iucn_redlist_category,
      iucn_last_updated,
      CAST((julianday('now') - julianday(iucn_last_updated)) AS INTEGER) as days_since_update
    FROM species_name_group
    WHERE iucn_redlist_category IS NOT NULL
      AND iucn_last_updated IS NOT NULL
      AND julianday('now') - julianday(iucn_last_updated) > ?
    ORDER BY iucn_last_updated ASC
    `,
    [daysOld]
  );
}

export async function getIucnSyncStats(db: Database): Promise<IUCNSyncStats> {
  const stats = await db.get<IUCNSyncStats>(`
    SELECT
      COUNT(*) as total_syncs,
      SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful_syncs,
      SUM(CASE WHEN status = 'not_found' THEN 1 ELSE 0 END) as not_found_count,
      SUM(CASE WHEN status IN ('api_error', 'rate_limited') THEN 1 ELSE 0 END) as error_count,
      MAX(sync_date) as last_sync_date
    FROM iucn_sync_log
  `);

  if (!stats) {
    return {
      total_syncs: 0,
      successful_syncs: 0,
      not_found_count: 0,
      error_count: 0,
      last_sync_date: null,
    };
  }

  return {
    total_syncs: stats.total_syncs || 0,
    successful_syncs: stats.successful_syncs || 0,
    not_found_count: stats.not_found_count || 0,
    error_count: stats.error_count || 0,
    last_sync_date: stats.last_sync_date || null,
  };
}

export async function createCanonicalRecommendation(
  db: Database,
  recommendation: {
    groupId: number;
    currentGenus: string;
    currentSpecies: string;
    suggestedGenus: string;
    suggestedSpecies: string;
    iucnTaxonId: number;
    iucnUrl?: string;
    reason: string;
  }
): Promise<number> {
  try {
    const stmt = await db.prepare(
      `INSERT INTO iucn_canonical_recommendations (
        group_id,
        current_canonical_genus,
        current_canonical_species,
        suggested_canonical_genus,
        suggested_canonical_species,
        iucn_taxon_id,
        iucn_url,
        reason
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      RETURNING id`
    );

    try {
      const result = await stmt.get<{ id: number }>(
        recommendation.groupId,
        recommendation.currentGenus,
        recommendation.currentSpecies,
        recommendation.suggestedGenus,
        recommendation.suggestedSpecies,
        recommendation.iucnTaxonId,
        recommendation

export async function getCanonicalRecommendations(
  db: Database,
  filters?: {
    groupId?: number;
    status?: RecommendationStatus;
    limit?: number;
  }
): Promise<CanonicalRecommendation[]> {
  let query = `
    SELECT
      id,
      group_id,
      current_canonical_genus,
      current_canonical_species,
      suggested_canonical_genus,
      suggested_canonical_species,
      iucn_taxon_id,
      iucn_url,
      reason,
      status,
      created_at,
      reviewed_at,
      reviewed_by
    FROM iucn_canonical_recommendations
  `;

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

  if (filters?.groupId !== undefined) {
    conditions.push("group_id = ?");
    params.push(filters.groupId);
  }

  if (filters?.status) {
    conditions.push("status = ?");
    params.push(filters.status);
  }

  if (conditions.length > 0) {
    query += " WHERE " + conditions.join(" AND ");
  }

  query += " ORDER BY created_at DESC";

  if (filters?.limit) {
   

export async function acceptCanonicalRecommendation(
  db: Database,
  recommendationId: number,
  reviewedBy: number
): Promise<boolean> {
  try {
    // Get the recommendation
    const rec = await db.get<CanonicalRecommendation>(
      `SELECT * FROM iucn_canonical_recommendations WHERE id = ? AND status = 'pending'`,
      [recommendationId]
    );

    if (!rec) {
      throw new Error(`Pending recommendation ${recommendationId} not found`);
    }

    // Perform the update in a transaction
    await db.run("BEGIN TRANSACTION");

    try {
      const now = new Date().toISOString();

      // 1. Update the canonical name in species_name_group
      const updateResult = await db.run(
        `UPDATE species_name_group
         SET canonical_genus = ?,
             canonical_species_name = ?
         WHERE group_id = ?`,
        [rec.suggested_canonical_genus, rec.suggested_canonical_species, rec.group_id]
      );

      if (!updateResult.changes || updateResult.changes === 0) {
  

export async function rejectCanonicalRecommendation(
  db: Database,
  recommendationId: number,
  reviewedBy: number
): Promise<boolean> {
  try {
    const now = new Date().toISOString();

    const result = await db.run(
      `UPDATE iucn_canonical_recommendations
       SET status = 'rejected',
           reviewed_at = ?,
           reviewed_by = ?
       WHERE id = ? AND status = 'pending'`,
      [now, reviewedBy, recommendationId]
    );

    if (!result.changes || result.changes === 0) {
      throw new Error(`Pending recommendation ${recommendationId} not found`);
    }

    return true;
  } catch (err) {
    logger.error("Failed to reject canonical recommendation", {
      recommendationId,
      reviewedBy,
      error: err,
    });
    // Re-throw our own errors with their original messages
    if (err instanceof Error && err.message.includes("not found")) {
      throw err;
    }
    throw new Error("Failed to reject canonical recommendation");
  }
}

export async function getGoogleAccount(sub: string) {
  const members = await query<{
    google_sub: string;
    member_id: number;
    google_email: string;
  }>(
    `SELECT google_sub, member_id, google_email FROM ${googleAccountTableName} WHERE google_sub = ?`,
    [sub]
  );
  return members.pop();
}

export async function getGoogleAccountByMemberId(member_id: number) {
  const members = await query<{
    google_sub: string;
    member_id: number;
    google_email: string;
  }>(`SELECT google_sub, member_id, google_email FROM google_account WHERE member_id = ?`, [
    member_id,
  ]);
  return members.pop();
}

export async function createGoogleAccount(memberId: number, sub: string, email: string) {
  return insertOne(googleAccountTableName, {
    member_id: memberId,
    google_sub: sub,
    google_email: email,
  });
}

export async function getFacebookAccount(facebookId: string) {
  const accounts = await query<{
    facebook_id: string;
    member_id: number;
    facebook_email: string;
  }>(
    `SELECT facebook_id, member_id, facebook_email FROM ${facebookAccountTableName} WHERE facebook_id = ?`,
    [facebookId]
  );
  return accounts.pop();
}

export async function getFacebookAccountByMemberId(member_id: number) {
  const accounts = await query<{
    facebook_id: string;
    member_id: number;
    facebook_email: string;
  }>(`SELECT facebook_id, member_id, facebook_email FROM facebook_account WHERE member_id = ?`, [
    member_id,
  ]);
  return accounts.pop();
}

export async function createFacebookAccount(memberId: number, facebookId: string, email: string) {
  return insertOne(facebookAccountTableName, {
    member_id: memberId,
    facebook_id: facebookId,
    facebook_email: email,
  });
}

export async function createOrUpdatePassword(memberId: number, passwordEntry: ScryptPassword) {
  const conn = db(true);
  try {
    const stmt = await conn.prepare(`
			INSERT INTO password_account (member_id, N, r, p, salt, hash) VALUES (?, ?, ?, ?, ?, ?)
			ON CONFLICT(member_id) DO UPDATE SET
				N = excluded.N,
				r = excluded.r,
				p = excluded.p,
				salt = excluded.salt,
				hash = excluded.hash
			`);
    try {
      const { N, r, p, salt, hash } = passwordEntry;
      await stmt.run(memberId, N, r, p, salt, hash);
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    logger.error("Failed to set password", err);
    throw new Error("Failed to set password");
  }
}

export async function getMemberPassword(memberId: number) {
  const members = await query<ScryptPassword>(
    `SELECT * FROM password_account WHERE member_id = ?`,
    [memberId]
  );
  return members.pop();
}

export async function createMember(
  email: string,
  name: string,
  credentials: {
    password?: string;
    google_sub?: string;
  } = {},
  isAdmin: boolean = false
) {
  const conn = db(true);
  await conn.exec("BEGIN TRANSACTION;");

  try {
    const userStmt = await conn.prepare(
      "INSERT INTO members (display_name, contact_email, is_admin) VALUES (?, ?, ?)"
    );
    // is this a bug... we should return the data, not the lastID
    let memberId;
    try {
      memberId = (await userStmt.run(name, email, isAdmin ? 1 : 0)).lastID;
    } finally {
      await userStmt.finalize();
    }

    if (credentials.google_sub) {
      const googleStmt = await conn.prepare(
        "INSERT INTO google_account (google_sub, member_id, google_email) VALUES (?, ?, ?)"
      );
      try {
        await googleStmt.run(credentials.google_sub, memberId, email);
      } finally {
        await googleStmt.finalize();
      }
    }

    if (credentials.password) {
      const { N, r, p, sal

export async function getMemberByEmail(email: string) {
  const members = await query<MemberRecord>("SELECT * FROM members WHERE contact_email = ?", [
    email,
  ]);
  return members.pop();
}

export async function getRosterWithPoints() {
  return query<
    MemberRecord & {
      fishTotalPoints: number;
      plantTotalPoints: number;
      coralTotalPoints: number;
      hasPassword: number;
      hasGoogleAccount: number;
      hasFacebookAccount: number;
    }
  >(`
		SELECT
			m.*,
			COALESCE(fish_points.total, 0) as fishTotalPoints,
			COALESCE(plant_points.total, 0) as plantTotalPoints,
			COALESCE(coral_points.total, 0) as coralTotalPoints,
			CASE WHEN pa.member_id IS NOT NULL THEN 1 ELSE 0 END as hasPassword,
			CASE WHEN ga.member_id IS NOT NULL THEN 1 ELSE 0 END as hasGoogleAccount,
			CASE WHEN fa.member_id IS NOT NULL THEN 1 ELSE 0 END as hasFacebookAccount
		FROM members m
		LEFT JOIN password_account pa ON m.id = pa.member_id
		LEFT JOIN google_account ga ON m.id = ga.member_id
		LEFT JOIN facebook_account fa ON m.id = fa.member_id
		LEFT JOIN (
			SELECT
				member_id,
				SUM(
					points +
					IFNULL(article_points, 0) +
					(IFNULL(first_time_species,

export async function getMemberWithPoints(memberId: number) {
  const rows = await query<
    MemberRecord & {
      fishTotalPoints: number;
      plantTotalPoints: number;
      coralTotalPoints: number;
      hasPassword: number;
      hasGoogleAccount: number;
      hasFacebookAccount: number;
    }
  >(
    `
		SELECT
			m.*,
			COALESCE(fish_points.total, 0) as fishTotalPoints,
			COALESCE(plant_points.total, 0) as plantTotalPoints,
			COALESCE(coral_points.total, 0) as coralTotalPoints,
			CASE WHEN pa.member_id IS NOT NULL THEN 1 ELSE 0 END as hasPassword,
			CASE WHEN ga.member_id IS NOT NULL THEN 1 ELSE 0 END as hasGoogleAccount,
			CASE WHEN fa.member_id IS NOT NULL THEN 1 ELSE 0 END as hasFacebookAccount
		FROM members m
		LEFT JOIN password_account pa ON m.id = pa.member_id
		LEFT JOIN google_account ga ON m.id = ga.member_id
		LEFT JOIN facebook_account fa ON m.id = fa.member_id
		LEFT JOIN (
			SELECT
				member_id,
				SUM(
					points +
					IFNULL(article_points, 0) +

export async function searchMembers(
  searchQuery: string,
  limit: number = 10
): Promise<MemberRecord[]> {
  if (!searchQuery || searchQuery.trim().length < 2) {
    return [];
  }

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

  return query<MemberRecord>(
    `
		SELECT * FROM members
		WHERE LOWER(display_name) LIKE ?
		   OR LOWER(contact_email) LIKE ?
		ORDER BY display_name
		LIMIT ?
	`,
    [searchPattern, searchPattern, limit]
  );
}

export async function getAwardsForMembers(
  memberIds: number[]
): Promise<Map<number, AwardRecord[]>> {
  if (memberIds.length === 0) {
    return new Map();
  }

  const placeholders = memberIds.map(() => "?").join(", ");
  const allAwards = await query<AwardRecord>(
    `SELECT * FROM awards WHERE member_id IN (${placeholders}) ORDER BY member_id, date_awarded DESC`,
    memberIds
  );

  // Group awards by member_id
  const awardsByMember = new Map<number, AwardRecord[]>();
  for (const award of allAwards) {
    if (!awardsByMember.has(award.member_id)) {
      awardsByMember.set(award.member_id, []);
    }
    awardsByMember.get(award.member_id)!.push(award);
  }

  return awardsByMember;
}

export async function getMemberWithAwards(memberId: number) {
  const [members, awards] = await Promise.all([
    query<MemberRecord>("SELECT * FROM members WHERE id = ?", [memberId]),
    getAwardsForMember(memberId),
  ]);
  const member = members.pop();
  return { ...member, awards };
}

export async function getSpecialtyAwardProgress(
  memberId: number
): Promise<SpecialtyAwardProgressData> {
  // Get member's approved submissions with genus information (for catfish limitation)
  const submissions = await query<{
    species_class: string;
    species_latin_name: string;
    species_type: string;
    water_type: string;
    spawn_locations: string;
    canonical_genus: string | null;
  }>(
    `
		SELECT
			s.species_class,
			s.species_latin_name,
			s.species_type,
			s.water_type,
			s.spawn_locations,
			COALESCE(
				sng_common.canonical_genus,
				sng_scientific.canonical_genus
			) as canonical_genus
		FROM submissions s
		LEFT JOIN species_common_name cn ON s.common_name_id = cn.common_name_id
		LEFT JOIN species_name_group sng_common ON cn.group_id = sng_common.group_id
		LEFT JOIN species_scientific_name scin ON s.scientific_name_id = scin.scientific_name_id
		LEFT JOIN species_name_group sng_scientific ON scin.group_id = sng_scientific.group_id
		WHERE s.m

export async function grantAward(
  memberId: number,
  awardName: string,
  dateAwarded: Date,
  awardType: "species" | "meta_species" | "manual" = "species"
) {
  try {
    const conn = db(true);
    const stmt = await conn.prepare(
      "INSERT INTO awards (member_id, award_name, date_awarded, award_type) VALUES (?, ?, ?, ?)"
    );
    try {
      await stmt.run(memberId, awardName, dateAwarded.toISOString(), awardType);
    } finally {
      await stmt.finalize();
    }

    // Create activity feed entry for award grant
    try {
      // Determine award type based on name
      const isMetaAward =
        awardName.includes("Senior Specialist") || awardName.includes("Expert Specialist");

      await createActivity("award_granted", memberId, awardName, {
        award_name: awardName,
        award_type: isMetaAward ? "meta" : "specialty",
      });
    } catch (activityError) {
      logger.error("Failed to create activity feed entry for award", activityError);
      // Don't f

export async function getAdminEmails(): Promise<string[]> {
  const rows = await query<{ contact_email: string }>(
    `SELECT contact_email FROM members where is_admin = 1`
  );
  return rows.map((row) => row.contact_email);
}

export async function regenerateSessionInDB(
  oldSessionId: string | undefined,
  newSessionId: string,
  memberId: number,
  expiresOn: string
): Promise<void> {
  await withTransaction(async (db) => {
    // Delete old session if it exists
    if (oldSessionId && oldSessionId !== "undefined") {
      const deleteStmt = await db.prepare("DELETE FROM sessions WHERE session_id = ?");
      try {
        await deleteStmt.run(oldSessionId);
      } finally {
        await deleteStmt.finalize();
      }
    }

    // Create new session
    const insertStmt = await db.prepare(`
      INSERT INTO sessions (session_id, member_id, expires_on)
      VALUES (?, ?, ?);
    `);
    try {
      await insertStmt.run(newSessionId, memberId, expiresOn);
    } finally {
      await insertStmt.finalize();
    }
  });
}

export async function updateSetting(key: string, value: string): Promise<void> {
  const stmt = await writeConn.prepare(
    `INSERT INTO settings (key, value, updated_at)
     VALUES (?, ?, CURRENT_TIMESTAMP)
     ON CONFLICT(key) DO UPDATE SET
       value = excluded.value,
       updated_at = CURRENT_TIMESTAMP`
  );
  await stmt.run(key, value);
  await stmt.finalize();
}

export async function querySpeciesNames() {
  // Query split schema tables and create paired records
  const groups = await query<{
    group_id: number;
    program_class: string;
    canonical_genus: string;
    canonical_species_name: string;
  }>(
    "SELECT group_id, program_class, canonical_genus, canonical_species_name FROM species_name_group"
  );

  const results: NameSynonym[] = [];

  for (const group of groups) {
    const [commonNames, scientificNames] = await Promise.all([
      query<{ common_name: string }>(
        "SELECT common_name FROM species_common_name WHERE group_id = ? ORDER BY common_name",
        [group.group_id]
      ),
      query<{ scientific_name: string }>(
        "SELECT scientific_name FROM species_scientific_name WHERE group_id = ? ORDER BY scientific_name",
        [group.group_id]
      ),
    ]);

    // Pair common names with scientific names
    const maxLength = Math.max(commonNames.length, scientificNames.length);
    for (let i = 0; i < m

export async function mergeSpecies(
  canonicalGroupId: number,
  defunctGroupId: number
): Promise<void> {
  try {
    await withTransaction(async (db) => {
      // Get all names from defunct group
      const commonNamesDefunct = await db.all<Array<{ common_name_id: number; common_name: string }>>(
        "SELECT common_name_id, common_name FROM species_common_name WHERE group_id = ?",
        [defunctGroupId]
      );
      const scientificNamesDefunct = await db.all<Array<{ scientific_name_id: number; scientific_name: string }>>(
        "SELECT scientific_name_id, scientific_name FROM species_scientific_name WHERE group_id = ?",
        [defunctGroupId]
      );

      // For each defunct common name, update submissions and handle duplicates
      for (const defunctName of commonNamesDefunct) {
        // Find matching common name in canonical group (case-insensitive)
        const matchingCanonical = await db.all<Array<{ common_name_id: number }>>(
          "SELECT common_name

export async function getSpeciesGroup(groupId: number) {
  const rows = await query<{
    group_id: number;
    program_class: string;
    species_type: string;
    canonical_genus: string;
    canonical_species_name: string;
    base_points: number | null;
    is_cares_species: number;
  }>(
    `
		SELECT * FROM species_name_group WHERE group_id = ?`,
    [groupId]
  );

  return rows.pop();
}

export async function getGroupIdFromNameId(
  nameId: number,
  isCommonName: boolean
): Promise<number | undefined> {
  if (isCommonName) {
    const rows = await query<{ group_id: number }>(
      "SELECT group_id FROM species_common_name WHERE common_name_id = ?",
      [nameId]
    );
    return rows.pop()?.group_id;
  } else {
    const rows = await query<{ group_id: number }>(
      "SELECT group_id FROM species_scientific_name WHERE scientific_name_id = ?",
      [nameId]
    );
    return rows.pop()?.group_id;
  }
}

function buildSpeciesSearchQuery(
  search?: string,
  species_type?: string,
  species_class?: string,
  sort: "name" | "reports" | "breeders" = "reports",
  limit?: number,
  cares_only?: boolean
): { sql: string; params: unknown[] } {

export async function searchSpeciesTypeahead(
  searchQuery: string,
  filters: Omit<SpeciesFilters, "search" | "sort"> = {},
  limit: number = 10
): Promise<SpeciesNameRecord[]> {
  if (!searchQuery || searchQuery.trim().length < 2) {
    return [];
  }

  const searchPattern = `%${searchQuery.trim().toLowerCase()}%`;
  const conditions: string[] = ["1=1"];
  const params: unknown[] = [];

  if (filters.species_type) {
    conditions.push("AND sng.species_type = ?");
    params.push(filters.species_type);
  }

  if (filters.species_class) {
    conditions.push("AND sng.program_class = ?");
    params.push(filters.species_class);
  }

  // Build WHERE clause for both queries
  const whereClause = conditions.join(" ");

  // UNION query: search both common names and scientific names
  // Each subquery joins with species_name_group for metadata
  // Uses canonical names from species_name_group as fallback for pairing
  const sql = `
    SELECT
      cn.common_name_id as name_id,
      cn

export async function getSpeciesForExplorer(
  filters: SpeciesFilters = {}
): Promise<SpeciesExplorerItem[]> {
  const { species_type, species_class, search, sort = "reports", cares_only } = filters;

  const { sql, params } = buildSpeciesSearchQuery(
    search,
    species_type,
    species_class,
    sort,
    undefined, // No limit for explorer - return all results
    cares_only
  );

  return query<SpeciesExplorerItem>(sql, params);
}

export async function getSpeciesDetail(groupId: number) {
  const groupRows = await query<{
    group_id: number;
    program_class: string;
    species_type: string;
    canonical_genus: string;
    canonical_species_name: string;
    base_points: number | null;
    is_cares_species: number;
    iucn_redlist_category: string | null;
    iucn_population_trend: string | null;
    iucn_last_updated: string | null;
    iucn_redlist_id: number | null;
    iucn_redlist_url: string | null;
  }>(
    `
		SELECT group_id, program_class, species_type, canonical_genus, canonical_species_name, base_points, is_cares_species, iucn_redlist_category, iucn_population_trend, iucn_last_updated, iucn_redlist_id, iucn_redlist_url
		FROM species_name_group
		WHERE group_id = ?
	`,
    [groupId]
  );

  if (groupRows.length === 0) {
    return null;
  }

  // Get all names from split schema tables
  const [commonNames, scientificNames] = await Promise.all([
    query<{ common_name_id: number; common_name: s

export async function getBreedersForSpecies(groupId: number) {
  return query<SpeciesBreeder>(
    `
		SELECT
			m.id as member_id,
			m.display_name as member_name,
			COUNT(s.id) as breed_count,
			MIN(s.approved_on) as first_breed_date,
			MAX(s.approved_on) as latest_breed_date,
			GROUP_CONCAT(
				s.id || '|' ||
				s.species_common_name || '|' ||
				s.species_latin_name || '|' ||
				s.approved_on || '|' ||
				COALESCE(s.points, 0)
			) as submissions_concat
		FROM members m
		JOIN submissions s ON m.id = s.member_id
		LEFT JOIN species_common_name cn ON s.common_name_id = cn.common_name_id
		LEFT JOIN species_scientific_name scin ON s.scientific_name_id = scin.scientific_name_id
		WHERE (cn.group_id = ? OR scin.group_id = ?)
		  AND s.approved_on IS NOT NULL
		GROUP BY m.id, m.display_name
		ORDER BY breed_count DESC, latest_breed_date DESC
	`,
    [groupId, groupId]
  ).then((rows) => {
    return rows.map((row) => ({
      ...row,
      submissions: row.submissions_concat
 

export async function getFilterOptions() {
  const speciesTypes = await query<{ species_type: string }>(`
		SELECT DISTINCT species_type
		FROM submissions
		WHERE approved_on IS NOT NULL
		ORDER BY species_type
	`);

  return {
    species_types: speciesTypes.map((s) => s.species_type),
  };
}

export async function getCommonNamesForGroup(groupId: number): Promise<CommonName[]> {
  return query<CommonName>(
    `
    SELECT common_name_id, group_id, common_name
    FROM species_common_name
    WHERE group_id = ?
    ORDER BY common_name
  `,
    [groupId]
  );
}

export async function getScientificNamesForGroup(groupId: number): Promise<ScientificName[]> {
  return query<ScientificName>(
    `
    SELECT scientific_name_id, group_id, scientific_name
    FROM species_scientific_name
    WHERE group_id = ?
    ORDER BY scientific_name
  `,
    [groupId]
  );
}

export async function getNamesForGroup(groupId: number): Promise<SpeciesNames> {
  const [common_names, scientific_names] = await Promise.all([
    getCommonNamesForGroup(groupId),
    getScientificNamesForGroup(groupId),
  ]);

  return {
    common_names,
    scientific_names,
  };
}

export async function getSynonymsForGroup(groupId: number): Promise<SpeciesSynonym[]> {
  // Post-migration 030: species_name table no longer exists
  // Return data from split tables (species_common_name and species_scientific_name)

  const [commonNames, scientificNames] = await Promise.all([
    query<{ common_name_id: number; group_id: number; common_name: string }>(
      "SELECT common_name_id, group_id, common_name FROM species_common_name WHERE group_id = ? ORDER BY common_name",
      [groupId]
    ),
    query<{ scientific_name_id: number; group_id: number; scientific_name: string }>(
      "SELECT scientific_name_id, group_id, scientific_name FROM species_scientific_name WHERE group_id = ? ORDER BY scientific_name",
      [groupId]
    ),
  ]);

  // Return cross-product of common names × scientific names
  // This maintains backward compatibility with the old paired model
  const results: SpeciesSynonym[] = [];

  if (commonNames.length === 0 || scientificNames.length === 0

export async function addCommonName(groupId: number, commonName: string): Promise<number> {
  const trimmed = commonName.trim();

  if (!trimmed) {
    throw new Error("Common name cannot be empty");
  }

  // Verify species group exists
  const groups = await query<{ group_id: number }>(
    "SELECT group_id FROM species_name_group WHERE group_id = ?",
    [groupId]
  );

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

  try {
    const conn = writeConn;
    const stmt = await conn.prepare(`
      INSERT INTO species_common_name (group_id, common_name)
      VALUES (?, ?)
      RETURNING common_name_id
    `);

    try {
      const result = await stmt.get<{ common_name_id: number }>(groupId, trimmed);

      if (!result || !result.common_name_id) {
        throw new Error("Failed to insert common name");
      }

      return result.common_name_id;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    if (err instanceof 

export async function addScientificName(groupId: number, scientificName: string): Promise<number> {
  const trimmed = scientificName.trim();

  if (!trimmed) {
    throw new Error("Scientific name cannot be empty");
  }

  // Verify species group exists
  const groups = await query<{ group_id: number }>(
    "SELECT group_id FROM species_name_group WHERE group_id = ?",
    [groupId]
  );

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

  try {
    const conn = writeConn;
    const stmt = await conn.prepare(`
      INSERT INTO species_scientific_name (group_id, scientific_name)
      VALUES (?, ?)
      RETURNING scientific_name_id
    `);

    try {
      const result = await stmt.get<{ scientific_name_id: number }>(groupId, trimmed);

      if (!result || !result.scientific_name_id) {
        throw new Error("Failed to insert scientific name");
      }

      return result.scientific_name_id;
    } finally {
      await stmt.finalize();
   

export async function updateCommonName(commonNameId: number, newName: string): Promise<number> {
  const trimmed = newName.trim();

  if (!trimmed) {
    throw new Error("Common name cannot be empty");
  }

  try {
    const conn = writeConn;
    const stmt = await conn.prepare(`
      UPDATE species_common_name
      SET common_name = ?
      WHERE common_name_id = ?
    `);

    try {
      const result = await stmt.run(trimmed, commonNameId);
      return result.changes || 0;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    if (err instanceof Error && err.message.includes("UNIQUE constraint")) {
      throw new Error("This common name already exists for this species");
    }
    logger.error("Failed to update common name", err);
    throw new Error("Failed to update common name");
  }
}

export async function updateScientificName(
  scientificNameId: number,
  newName: string
): Promise<number> {
  const trimmed = newName.trim();

  if (!trimmed) {
    throw new Error("Scientific name cannot be empty");
  }

  try {
    const conn = writeConn;
    const stmt = await conn.prepare(`
      UPDATE species_scientific_name
      SET scientific_name = ?
      WHERE scientific_name_id = ?
    `);

    try {
      const result = await stmt.run(trimmed, scientificNameId);
      return result.changes || 0;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    if (err instanceof Error && err.message.includes("UNIQUE constraint")) {
      throw new Error("This scientific name already exists for this species");
    }
    logger.error("Failed to update scientific name", err);
    throw new Error("Failed to update scientific name");
  }
}

export async function deleteCommonName(commonNameId: number): Promise<number> {
  try {
    const conn = writeConn;
    const stmt = await conn.prepare("DELETE FROM species_common_name WHERE common_name_id = ?");

    try {
      const result = await stmt.run(commonNameId);
      return result.changes || 0;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    logger.error("Failed to delete common name", err);
    throw new Error("Failed to delete common name");
  }
}

export async function deleteScientificName(scientificNameId: number): Promise<number> {
  try {
    const conn = writeConn;
    const stmt = await conn.prepare(
      "DELETE FROM species_scientific_name WHERE scientific_name_id = ?"
    );

    try {
      const result = await stmt.run(scientificNameId);
      return result.changes || 0;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    logger.error("Failed to delete scientific name", err);
    throw new Error("Failed to delete scientific name");
  }
}

export async function addSynonym(
  groupId: number,
  commonName: string,
  scientificName: string
): Promise<number> {
  // Post-migration 030: Insert into both split tables
  // Returns the common_name_id as the primary identifier

  const trimmedCommon = commonName.trim();
  const trimmedScientific = scientificName.trim();

  if (!trimmedCommon || !trimmedScientific) {
    throw new Error("Common name and scientific name cannot be empty");
  }

  // Verify species group exists
  const groups = await query<{ group_id: number }>(
    "SELECT group_id FROM species_name_group WHERE group_id = ?",
    [groupId]
  );

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

  try {
    // Add common name
    const commonNameId = await addCommonName(groupId, trimmedCommon);

    // Add scientific name (ignore duplicates since it might already exist)
    try {
      await addScientificName(groupId, trimmedScientific);
    } catch (err) {
      // If scie

export async function updateSynonym(
  nameId: number,
  updates: {
    commonName?: string;
    scientificName?: string;
  }
): Promise<number> {
  // Post-migration 030: nameId is always a common_name_id (from getSynonymsForGroup)
  // This function only updates common names for backward compatibility

  const { commonName, scientificName } = updates;

  // At least one field must be provided
  if (commonName === undefined && scientificName === undefined) {
    throw new Error("At least one field (commonName or scientificName) must be provided");
  }

  // Only common name updates are supported via this wrapper
  // (nameId comes from getSynonymsForGroup which returns common_name_id)
  if (scientificName !== undefined) {
    throw new Error(
      "updateSynonym() only supports updating common names. Use updateScientificName() directly to update scientific names."
    );
  }

  if (commonName !== undefined) {
    return await updateCommonName(nameId, commonName);
  }

  return 0;
}

export async function deleteSynonym(nameId: number, force = false): Promise<number> {
  // Post-migration 030: nameId is always a common_name_id (from getSynonymsForGroup)
  // This function only deletes common names for backward compatibility

  // Verify the common name exists and get its group
  const commonNameRecords = await query<{ common_name_id: number; group_id: number }>(
    "SELECT common_name_id, group_id FROM species_common_name WHERE common_name_id = ?",
    [nameId]
  );

  if (commonNameRecords.length === 0) {
    throw new Error(`Common name ID ${nameId} not found`);
  }

  const groupId = commonNameRecords[0].group_id;

  // Check if this is the last common name for the group (only if not force)
  if (!force) {
    const groupCommonNames = await query<{ count: number }>(
      "SELECT COUNT(*) as count FROM species_common_name WHERE group_id = ?",
      [groupId]
    );

    const count = groupCommonNames[0]?.count || 0;
    if (count <= 1) {
      throw new Error(
 

export async function getSpeciesForAdmin(
  filters: SpeciesAdminFilters = {},
  sort: "name" | "points" | "class" = "name",
  limit = 50,
  offset = 0
): Promise<SpeciesAdminListResult> {
  const { species_type, program_class, has_base_points, is_cares_species, iucn_category, search } =
    filters;

  // Build WHERE conditions
  const conditions: string[] = ["1=1"];
  const params: unknown[] = [];

  if (species_type) {
    conditions.push("AND sng.species_type = ?");
    params.push(species_type);
  }

  if (program_class) {
    conditions.push("AND sng.program_class = ?");
    params.push(program_class);
  }

  if (has_base_points !== undefined) {
    conditions.push(
      has_base_points ? "AND sng.base_points IS NOT NULL" : "AND sng.base_points IS NULL"
    );
  }

  if (is_cares_species !== undefined) {
    conditions.push("AND sng.is_cares_species = ?");
    params.push(is_cares_species ? 1 : 0);
  }

  if (iucn_category) {
    if (iucn_category === "with_data") {
      condit