Function bodies 451 total

export async function createSpeciesGroup(data: {
  programClass: string;
  speciesType: string;
  canonicalGenus: string;
  canonicalSpeciesName: string;
  basePoints?: number | null;
  isCaresSpecies?: boolean;
}): Promise<number> {
  const {
    programClass,
    speciesType,
    canonicalGenus,
    canonicalSpeciesName,
    basePoints,
    isCaresSpecies,
  } = data;

  // Validate inputs
  const trimmedGenus = canonicalGenus.trim();
  const trimmedSpecies = canonicalSpeciesName.trim();
  const trimmedClass = programClass.trim();

  if (!trimmedGenus || !trimmedSpecies) {
    throw new Error("Canonical genus and species name cannot be empty");
  }

  if (!trimmedClass) {
    throw new Error("Program class cannot be empty");
  }

  if (!["Fish", "Plant", "Invert", "Coral"].includes(speciesType)) {
    throw new Error("Species type must be Fish, Plant, Invert, or Coral");
  }

  if (basePoints !== undefined && basePoints !== null && (basePoints < 0 || basePoints > 100)) {
    throw ne

export async function updateSpeciesGroup(
  groupId: number,
  updates: {
    canonicalGenus?: string;
    canonicalSpeciesName?: string;
    speciesType?: string;
    programClass?: string;
    basePoints?: number | null;
    isCaresSpecies?: boolean;
    externalReferences?: string[];
    imageLinks?: string[];
  }
): Promise<number> {
  const {
    canonicalGenus,
    canonicalSpeciesName,
    speciesType,
    programClass,
    basePoints,
    isCaresSpecies,
    externalReferences,
    imageLinks,
  } = updates;

  // At least one field must be provided
  if (Object.keys(updates).length === 0) {
    throw new Error("At least one field must be provided");
  }

  const fields: string[] = [];
  const values: unknown[] = [];

  if (canonicalGenus !== undefined) {
    const trimmed = canonicalGenus.trim();
    if (!trimmed) {
      throw new Error("Canonical genus cannot be empty");
    }
    fields.push("canonical_genus = ?");
    values.push(trimmed);
  }

  if (canonicalSpeciesName !

export async function deleteSpeciesGroup(groupId: number, force = false): Promise<number> {
  // Verify species group exists
  const groups = 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 = ?",
    [groupId]
  );

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

  // Check for approved submissions using new FK columns
  const submissions = await query<{ count: number }>(
    `SELECT COUNT(DISTINCT s.id) as count
     FROM submissions s
     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`,
    [groupId, groupId]
  );

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

  if (sub

export async function bulkSetPoints(groupIds: number[], points: number | null): Promise<number> {
  if (!groupIds || groupIds.length === 0) {
    throw new Error("At least one group ID must be provided");
  }

  if (points !== null && (points < 0 || points > 100)) {
    throw new Error("Points must be between 0 and 100, or null");
  }

  try {
    const conn = writeConn;
    const placeholders = groupIds.map(() => "?").join(", ");
    const stmt = await conn.prepare(`
      UPDATE species_name_group
      SET base_points = ?
      WHERE group_id IN (${placeholders})
    `);

    try {
      const result = await stmt.run(points, ...groupIds);

      logger.info("Bulk updated species points", {
        groupIds,
        points,
        updatedCount: result.changes || 0,
      });

      return result.changes || 0;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    logger.error("Failed to bulk set points", err);
    throw new Error("Failed to bulk set points");
  }
}

export async function isFirstTimeSpeciesForProgram(
  groupId: number
): Promise<{
  isFirstTime: boolean;
  priorBreedCount: number;
}> {

export async function getCommonNamesByText(
  commonNameText: string,
  limit?: number
): Promise<CommonNameWithSpecies[]> {
  const sql = `
    SELECT
      scn.common_name_id,
      scn.group_id,
      scn.common_name,
      sng.canonical_genus,
      sng.canonical_species_name,
      sng.program_class
    FROM species_common_name scn
    JOIN species_name_group sng ON scn.group_id = sng.group_id
    WHERE scn.common_name = ?
    ORDER BY sng.canonical_genus, sng.canonical_species_name
    ${limit ? "LIMIT ?" : ""}
  `;

  const params = limit ? [commonNameText, limit] : [commonNameText];
  return query<CommonNameWithSpecies>(sql, params);
}

export function getSpeciesExternalReferences(
  groupId: number
): Promise<SpeciesExternalReference[]> {
  return query<SpeciesExternalReference>(
    `SELECT * FROM species_external_references
     WHERE group_id = ?
     ORDER BY display_order ASC`,
    [groupId]
  );
}

export async function setSpeciesExternalReferences(
  groupId: number,
  references: string[]
): Promise<void> {
  try {
    return await withTransaction(async (db) => {
      // Delete existing references
      const deleteStmt = await db.prepare(
        "DELETE FROM species_external_references WHERE group_id = ?"
      );
      await deleteStmt.run(groupId);
      await deleteStmt.finalize();

      // Insert new references
      if (references.length > 0) {
        const insertStmt = await db.prepare(`
          INSERT INTO species_external_references
          (group_id, reference_url, display_order)
          VALUES (?, ?, ?)
        `);

        for (let i = 0; i < references.length; i++) {
          await insertStmt.run(groupId, references[i], i);
        }

        await insertStmt.finalize();
      }
    });
  } catch (err) {
    logger.error("Failed to set species external references", err);
    throw new Error("Failed to set species external references");
  }
}

export function getSpeciesImages(groupId: number): Promise<SpeciesImage[]> {
  return query<SpeciesImage>(
    `SELECT * FROM species_images
     WHERE group_id = ?
     ORDER BY display_order ASC`,
    [groupId]
  );
}

export async function setSpeciesImages(groupId: number, imageUrls: string[]): Promise<void> {
  try {
    return await withTransaction(async (db) => {
      // Delete existing images
      const deleteStmt = await db.prepare("DELETE FROM species_images WHERE group_id = ?");
      await deleteStmt.run(groupId);
      await deleteStmt.finalize();

      // Insert new images
      if (imageUrls.length > 0) {
        const insertStmt = await db.prepare(`
          INSERT INTO species_images
          (group_id, image_url, display_order)
          VALUES (?, ?, ?)
        `);

        for (let i = 0; i < imageUrls.length; i++) {
          await insertStmt.run(groupId, imageUrls[i], i);
        }

        await insertStmt.finalize();
      }
    });
  } catch (err) {
    logger.error("Failed to set species images", err);
    throw new Error("Failed to set species images");
  }
}

export async function setSpeciesImagesWithMetadata(
  groupId: number,
  images: SpeciesImageInput[]
): Promise<void> {
  try {
    return await withTransaction(async (db) => {
      // Delete existing images
      const deleteStmt = await db.prepare("DELETE FROM species_images WHERE group_id = ?");
      await deleteStmt.run(groupId);
      await deleteStmt.finalize();

      // Insert new images with metadata
      if (images.length > 0) {
        const insertStmt = await db.prepare(`
          INSERT INTO species_images
          (group_id, image_url, display_order, source, attribution, license, title, original_url)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        `);

        for (const img of images) {
          await insertStmt.run(
            groupId,
            img.image_url,
            img.display_order,
            img.source || null,
            img.attribution || null,
            img.license || null,
            img.title || null,
            img.original_url || null
 

export async function getCaresCoverageStats(): Promise<CaresCoverageStats> {
  const totalRow = await query<{ count: number }>(
    `SELECT COUNT(*) as count FROM species_name_group WHERE is_cares_species = 1`
  );
  const total_cares_species = totalRow[0]?.count || 0;

  const maintainedRow = await query<{ count: number }>(
    `SELECT COUNT(DISTINCT sng.group_id) as count
     FROM species_name_group sng
     JOIN species_collection c ON c.group_id = sng.group_id
       AND c.removed_date IS NULL AND c.visibility = 'public'
     WHERE sng.is_cares_species = 1`
  );
  const maintained_species = maintainedRow[0]?.count || 0;

  const most_maintained = await query<{
    group_id: number;
    canonical_genus: string;
    canonical_species_name: string;
    keeper_count: number;
  }>(
    `SELECT sng.group_id, sng.canonical_genus, sng.canonical_species_name,
            COUNT(DISTINCT c.member_id) as keeper_count
     FROM species_name_group sng
     JOIN species_collection c ON c.group_i

export async function getNotesForSubmission(submissionId: number): Promise<SubmissionNote[]> {
  const notes = await query<SubmissionNote>(
    `SELECT
			sn.id,
			sn.submission_id,
			sn.admin_id,
			m.display_name as admin_name,
			sn.note_text,
			sn.created_at
		FROM submission_notes sn
		LEFT JOIN members m ON sn.admin_id = m.id
		WHERE sn.submission_id = ?
		ORDER BY sn.created_at ASC`,
    [submissionId]
  );
  return notes;
}

export async function addNote(
  submissionId: number,
  adminId: number,
  noteText: string
): Promise<number> {
  const stmt = await writeConn.prepare(`
		INSERT INTO submission_notes (submission_id, admin_id, note_text)
		VALUES (?, ?, ?)
	`);

  try {
    const result = await stmt.run(submissionId, adminId, noteText);
    return result.lastID as number;
  } finally {
    await stmt.finalize();
  }
}

export async function updateNote(noteId: number, noteText: string): Promise<void> {
  const stmt = await writeConn.prepare(`
		UPDATE submission_notes
		SET note_text = ?
		WHERE id = ?
	`);

  try {
    await stmt.run(noteText, noteId);
  } finally {
    await stmt.finalize();
  }
}

export async function deleteNote(noteId: number): Promise<void> {
  const stmt = await writeConn.prepare(`
		DELETE FROM submission_notes
		WHERE id = ?
	`);

  try {
    await stmt.run(noteId);
  } finally {
    await stmt.finalize();
  }
}

export async function getNoteById(noteId: number): Promise<SubmissionNote | null> {
  const notes = await query<SubmissionNote>(
    `SELECT
			sn.id,
			sn.submission_id,
			sn.admin_id,
			m.display_name as admin_name,
			sn.note_text,
			sn.created_at
		FROM submission_notes sn
		LEFT JOIN members m ON sn.admin_id = m.id
		WHERE sn.id = ?`,
    [noteId]
  );
  return notes[0] || null;
}

export function formToDB(memberId: number, form: FormValues, submit: boolean) {
  const program = (() => {
    switch (form.species_type) {
      case "Fish":
      case "Invert":
        return "fish";
      case "Plant":
        return "plant";
      case "Coral":
        return "coral";
      case undefined:
        return undefined;
      default:
        logger.warn("Unknown species type", form.species_type);
        throw new Error("Unknown species type");
    }
  })();

  const arrayToJSON = (formField: unknown) => {
    if (Array.isArray(formField)) {
      return JSON.stringify(formField.filter((v) => v !== ""));
    }
    return undefined;
  };

  return {
    member_id: memberId,
    program,
    submitted_on: submit ? new Date().toISOString() : undefined,
    witness_verification_status: submit ? ("pending" as const) : undefined,
    ...form,
    member_name: undefined,
    member_email: undefined,
    foods: arrayToJSON(form.foods),
    spawn_locations: arrayToJSON(form.sp

export async function createSubmission(memberId: number, form: FormValues, submit: boolean) {
  try {
    return await withTransaction(async (db) => {
      // Prepare submission data (supplements handled separately)
      const entries = formToDB(memberId, form, submit);

      // Extract supplements for normalized table
      const supplementTypes = form.supplement_type;
      const supplementRegimens = form.supplement_regimen;

      const fields = [];
      const values = [];
      const marks = [];
      for (const [field, value] of Object.entries(entries)) {
        if (value === undefined) {
          continue;
        }
        fields.push(field);
        values.push(value);
        marks.push("?");
      }

      const stmt = await db.prepare(`
        INSERT INTO submissions
        (${fields.join(", ")})
        VALUES
        (${marks.join(", ")})`);

      const result = await stmt.run(values);
      await stmt.finalize();

      const submissionId = result.lastID as numbe

export function getSubmissionsByMember(
  memberId: number,
  includeUnsubmitted: boolean,
  includeUnapproved: boolean
) {
  let expr = `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name,
			sng.is_cares_species
		FROM submissions
		LEFT JOIN members ON submissions.member_id == members.id
		LEFT JOIN species_common_name cn ON submissions.common_name_id = cn.common_name_id
		LEFT JOIN species_scientific_name scin ON submissions.scientific_name_id = scin.scientific_name_id
		LEFT JOIN species_name_group sng ON (cn.group_id = sng.group_id OR scin.group_id = sng.group_id)
		WHERE submissions.member_id = ?`;

  if (!includeUnsubmitted) {
    expr += ` AND 

export async function getSubmissionById(id: number) {
  const result = await query<Submission>(
    `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name
		FROM submissions LEFT JOIN members
		ON submissions.member_id == members.id
		WHERE submissions.id = ?`,
    [id]
  );
  return result.pop();
}

export async function deleteSubmission(id: number) {
  try {
    const conn = writeConn;
    const deleteRow = await conn.prepare("DELETE FROM submissions WHERE id = ?");
    try {
      return deleteRow.run(id);
    } finally {
      await deleteRow.finalize();
    }
  } catch (err) {
    logger.error("Failed to delete submission", err);
    throw new Error("Failed to delete submission");
  }
}

export async function deleteSubmissionWithAuth(
  submissionId: number,
  userId: number,
  isAdmin: boolean
): Promise<void> {
  try {
    return await withTransaction(async (db) => {
      // Get current submission
      const stmt = await db.prepare(`
        SELECT id, member_id, approved_on
        FROM submissions WHERE id = ?`);
      const current: Submission[] = await stmt.all(submissionId);
      await stmt.finalize();

      if (!current[0]) {
        throw new Error("Submission not found");
      }

      const submission = current[0];

      // Admin can delete anything
      if (isAdmin) {
        const deleteStmt = await db.prepare("DELETE FROM submissions WHERE id = ?");
        await deleteStmt.run(submissionId);
        await deleteStmt.finalize();
        logger.info(`Admin ${userId} deleted submission ${submissionId}`);
        return;
      }

      // Non-admin: must be owner
      if (submission.member_id !== userId) {
        throw new Error("Cannot delete anoth

export function getApprovedSubmissionsInDateRange(startDate: Date, endDate: Date, program: string) {
  return query<Submission>(
    `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name
		FROM submissions JOIN members
		ON submissions.member_id == members.id
		WHERE reproduction_date > ? AND reproduction_date < ?
		AND approved_on IS NOT NULL AND points IS NOT NULL
		AND program = ?
	`,
    [startDate.toISOString(), endDate.toISOString(), program]
  );
}

export async function getOutstandingSubmissions(program: string) {
  const { filterEligibleSubmissions } = await import("@/utils/waitingPeriod");

  const allWitnessed = await query<Submission>(
    `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name,
			sng.is_cares_species
		FROM submissions
		JOIN members ON submissions.member_id == members.id
		LEFT JOIN species_common_name cn ON submissions.common_name_id = cn.common_name_id
		LEFT JOIN species_scientific_name scin ON submissions.scientific_name_id = scin.scientific_name_id
		LEFT JOIN species_name_group sng ON (cn.group_id = sng.group_id OR scin.group_id = sng.group_id)
		WHERE submitted_on IS NOT

export function getWitnessQueue(program: string) {
  return query<Submission>(
    `
		SELECT
			submissions.*,
			members.display_name as member_name,
			sng.is_cares_species
		FROM submissions
		JOIN members ON submissions.member_id == members.id
		LEFT JOIN species_common_name cn ON submissions.common_name_id = cn.common_name_id
		LEFT JOIN species_scientific_name scin ON submissions.scientific_name_id = scin.scientific_name_id
		LEFT JOIN species_name_group sng ON (cn.group_id = sng.group_id OR scin.group_id = sng.group_id)
		WHERE submitted_on IS NOT NULL
		AND witness_verification_status = 'pending'
		AND program = ?
		ORDER BY submitted_on ASC`,
    [program]
  );
}

export function getWaitingPeriodSubmissions(program: string) {
  return query<Submission>(
    `
		SELECT
			submissions.*,
			members.display_name as member_name,
			witnessed_members.display_name as witnessed_by_name,
			sng.is_cares_species
		FROM submissions
		JOIN members ON submissions.member_id == members.id
		LEFT JOIN members as witnessed_members ON submissions.witnessed_by == witnessed_members.id
		LEFT JOIN species_common_name cn ON submissions.common_name_id = cn.common_name_id
		LEFT JOIN species_scientific_name scin ON submissions.scientific_name_id = scin.scientific_name_id
		LEFT JOIN species_name_group sng ON (cn.group_id = sng.group_id OR scin.group_id = sng.group_id)
		WHERE submitted_on IS NOT NULL
		AND witness_verification_status = 'confirmed'
		AND approved_on IS NULL
		AND program = ?
		ORDER BY witnessed_on ASC`,
    [program]
  );
}

export async function getOutstandingSubmissionsCounts() {
  const rows = await query<{ count: number; program: string }>(`
		SELECT COUNT(1) as count, program
		FROM submissions JOIN members
		ON submissions.member_id == members.id
		WHERE submitted_on IS NOT NULL
		AND approved_on IS NULL
		AND witness_verification_status = 'confirmed'
		GROUP BY program`);
  return Object.fromEntries(rows.map((row) => [row.program, row.count]));
}

export async function getWitnessQueueCounts() {
  const rows = await query<{ count: number; program: string }>(`
		SELECT COUNT(1) as count, program
		FROM submissions JOIN members
		ON submissions.member_id == members.id
		WHERE submitted_on IS NOT NULL
		AND witness_verification_status = 'pending'
		GROUP BY program`);
  return Object.fromEntries(rows.map((row) => [row.program, row.count]));
}

export async function confirmWitness(submissionId: number, witnessAdminId: number) {
  const startTime = Date.now();

  try {
    logger.info("Starting witness confirmation", {
      submissionId,
      witnessAdminId,
      timestamp: new Date().toISOString(),
    });

    return await withTransaction(async (db) => {
      // Check current state and prevent self-witnessing - use transaction db
      const stmt = await db.prepare(`
				SELECT id, member_id, witness_verification_status, species_common_name
				FROM submissions WHERE id = ?`);
      const current: Submission[] = await stmt.all(submissionId);
      await stmt.finalize();

      // Validate submission exists
      if (!current[0]) {
        throw new ValidationError("Submission not found", "submissionId", submissionId);
      }

      const submission = current[0];

      // Validate authorization - prevent self-witnessing
      if (submission.member_id === witnessAdminId) {
        throw new AuthorizationError(
          

export async function declineWitness(submissionId: number, witnessAdminId: number) {
  const startTime = Date.now();

  try {
    logger.info("Starting witness decline", {
      submissionId,
      witnessAdminId,
      timestamp: new Date().toISOString(),
    });

    return await withTransaction(async (db) => {
      // Check current state and prevent self-witnessing - use transaction db
      const stmt = await db.prepare(`
				SELECT id, member_id, witness_verification_status, species_common_name
				FROM submissions WHERE id = ?`);
      const current: Submission[] = await stmt.all(submissionId);
      await stmt.finalize();

      // Validate submission exists
      if (!current[0]) {
        throw new ValidationError("Submission not found", "submissionId", submissionId);
      }

      const submission = current[0];

      // Validate authorization - prevent self-witnessing
      if (submission.member_id === witnessAdminId) {
        throw new AuthorizationError(
          "Cann

export async function requestChanges(
  submissionId: number,
  adminId: number,
  reason: string
): Promise<void> {
  try {
    return await withTransaction(async (db) => {
      // Get current submission state
      const stmt = await db.prepare(`
        SELECT id, submitted_on, approved_on, denied_on
        FROM submissions WHERE id = ?`);
      const current: Submission[] = await stmt.all(submissionId);
      await stmt.finalize();

      if (!current[0]) {
        throw new Error("Submission not found");
      }

      // Validate submission state
      if (!current[0].submitted_on) {
        throw new Error("Cannot request changes on draft submissions");
      }

      if (current[0].approved_on) {
        throw new Error("Cannot request changes on approved submissions");
      }

      if (current[0].denied_on) {
        throw new Error("Cannot request changes on denied submissions");
      }

      // Set changes_requested fields
      const updateStmt = await db.prepare(`
  

export function getApprovedSubmissions(program: string) {
  return query<
    Submission &
      Required<Pick<Submission, "submitted_on" | "approved_on" | "points" | "total_points">>
  >(
    `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name
		FROM submissions JOIN members
		ON submissions.member_id == members.id
		WHERE submitted_on IS NOT NULL
		AND approved_on IS NOT NULL
		AND points IS NOT NULL
		AND program = ?`,
    [program]
  );
}

export async function updateSubmission(id: number, updates: UpdateFor<Submission>) {
  const entries = Object.fromEntries(
    Object.entries(updates).filter(([, value]) => value !== undefined)
  );
  const fields = Object.keys(entries);
  const values = Object.values(entries);
  const setClause = fields.map((field) => `${field} = ?`).join(", ");

  try {
    const conn = writeConn;
    const stmt = await conn.prepare(`UPDATE submissions SET ${setClause} WHERE id = ?`);
    try {
      const result = await stmt.run(...values, id);
      return result.changes;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    logger.error("Failed to update submission", err);
    throw new Error("Failed to update submission");
  }
}

export async function approveSubmission(
  approvedBy: number,
  id: number,
  speciesIds: { common_name_id: number; scientific_name_id: number },
  updates: ApprovalFormValues
) {
  try {
    return await withTransaction(async (db) => {
      // Get current submission state
      const stmt = await db.prepare(`
        SELECT id, submitted_on, approved_on, denied_on, witness_verification_status
        FROM submissions WHERE id = ?`);
      const current: Submission[] = await stmt.all(id);
      await stmt.finalize();

      if (!current[0]) {
        throw new Error("Submission not found");
      }

      // Validate submission state
      if (!current[0].submitted_on) {
        throw new Error("Cannot approve draft submissions");
      }

      if (current[0].approved_on) {
        throw new Error("Cannot approve already approved submissions");
      }

      if (current[0].denied_on) {
        throw new Error("Cannot approve denied submissions");
      }

      // Update submission

export function getTodayApprovedSubmissions() {
  return query<
    Submission &
      Required<Pick<Submission, "submitted_on" | "approved_on" | "points" | "total_points">>
  >(
    `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name
		FROM submissions JOIN members
		ON submissions.member_id == members.id
		WHERE DATE(approved_on) = DATE('now', 'localtime')
		AND approved_on IS NOT NULL
		AND points IS NOT NULL
		ORDER BY approved_on DESC`,
    []
  );
}

export function getLast48HoursApprovedSubmissions() {
  return query<
    Submission &
      Required<Pick<Submission, "submitted_on" | "approved_on" | "points" | "total_points">>
  >(
    `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name
		FROM submissions JOIN members
		ON submissions.member_id == members.id
		WHERE datetime(approved_on) >= datetime('now', '-2 days')
		AND approved_on IS NOT NULL
		AND points IS NOT NULL
		ORDER BY approved_on DESC`,
    []
  );
}

export function getLast30DaysApprovedSubmissions() {
  return query<
    Submission &
      Required<Pick<Submission, "submitted_on" | "approved_on" | "points" | "total_points">>
  >(
    `
		SELECT
			submissions.*,
			submissions.points +
				IFNULL(submissions.article_points, 0) +
				(IFNULL(submissions.first_time_species, 0) * 5) +
				(IFNULL(submissions.cares_species, 0) * 5) +
				(IFNULL(submissions.flowered, 0) * submissions.points) +
				(IFNULL(submissions.sexual_reproduction, 0) * submissions.points)
				as total_points,
			members.display_name as member_name
		FROM submissions JOIN members
		ON submissions.member_id == members.id
		WHERE datetime(approved_on) >= datetime('now', '-30 days')
		AND approved_on IS NOT NULL
		AND points IS NOT NULL
		ORDER BY approved_on DESC`,
    []
  );
}

export function getSubmissionImages(submissionId: number): Promise<SubmissionImage[]> {
  return query<SubmissionImage>(
    `SELECT * FROM submission_images
     WHERE submission_id = ?
     ORDER BY display_order ASC`,
    [submissionId]
  );
}

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

  const placeholders = submissionIds.map(() => "?").join(",");
  const allImages = await query<SubmissionImage>(
    `SELECT * FROM submission_images
     WHERE submission_id IN (${placeholders})
     ORDER BY submission_id, display_order ASC`,
    submissionIds
  );

  // Group images by submission_id
  const imagesBySubmission = new Map<number, SubmissionImage[]>();
  for (const image of allImages) {
    const existing = imagesBySubmission.get(image.submission_id) || [];
    existing.push(image);
    imagesBySubmission.set(image.submission_id, existing);
  }

  return imagesBySubmission;
}

export async function addSubmissionImage(
  submissionId: number,
  imageData: Omit<SubmissionImage, "id" | "submission_id" | "display_order">
): Promise<number> {
  try {
    // Get current max display order
    const maxOrder = await query<{ max_order: number | null }>(
      "SELECT MAX(display_order) as max_order FROM submission_images WHERE submission_id = ?",
      [submissionId]
    );
    const nextOrder = (maxOrder[0]?.max_order ?? -1) + 1;

    const stmt = await writeConn.prepare(`
      INSERT INTO submission_images
      (submission_id, r2_key, public_url, file_size, uploaded_at, content_type, display_order)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    try {
      const result = await stmt.run(
        submissionId,
        imageData.r2_key,
        imageData.public_url,
        imageData.file_size,
        imageData.uploaded_at,
        imageData.content_type,
        nextOrder
      );
      return result.lastID as number;
    } finally {
      await stmt.finalize();

export async function deleteSubmissionImage(
  submissionId: number,
  r2Key: string
): Promise<void> {
  try {
    const stmt = await writeConn.prepare(
      "DELETE FROM submission_images WHERE submission_id = ? AND r2_key = ?"
    );
    try {
      await stmt.run(submissionId, r2Key);
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    logger.error("Failed to delete submission image", err);
    throw new Error("Failed to delete submission image");
  }
}

export function getSubmissionSupplements(
  submissionId: number
): Promise<SubmissionSupplement[]> {
  return query<SubmissionSupplement>(
    `SELECT * FROM submission_supplements
     WHERE submission_id = ?
     ORDER BY display_order ASC`,
    [submissionId]
  );
}

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

  const placeholders = submissionIds.map(() => "?").join(",");
  const allSupplements = await query<SubmissionSupplement>(
    `SELECT * FROM submission_supplements
     WHERE submission_id IN (${placeholders})
     ORDER BY submission_id, display_order ASC`,
    submissionIds
  );

  // Group supplements by submission_id
  const supplementsBySubmission = new Map<number, SubmissionSupplement[]>();
  for (const supplement of allSupplements) {
    const existing = supplementsBySubmission.get(supplement.submission_id) || [];
    existing.push(supplement);
    supplementsBySubmission.set(supplement.submission_id, existing);
  }

  return supplementsBySubmission;
}

async function setSubmissionSupplementsInTransaction(
  db: Database,
  submissionId: number,
  supplements: Array<{ type: string; regimen: string }>
): Promise<void> {
  // Delete existing supplements
  const deleteStmt = await db.prepare(
    "DELETE FROM submission_supplements WHERE submission_id = ?"
  );
  await deleteStmt.run(submissionId);
  await deleteStmt.finalize();

  // Insert new supplements
  if (supplements.length > 0) {
    const insertStmt = await db.prepare(`
      INSERT INTO submission_supplements
      (submission_id, supplement_type, supplement_regimen, display_order)
      VALUES (?, ?, ?, ?)
    `);

    for (let i = 0; i < supplements.length; i++) {
      const supp = supplements[i];
      if (supp.type || supp.regimen) {
        // Only insert if at least one field is non-empty
        await insertStmt.run(submissionId, supp.type, supp.regimen, i);
      }
    }

    await insertStmt.finalize();
  }
}

export async function setSubmissionSupplements(
  submissionId: number,
  supplements: Array<{ type: string; regimen: string }>,
  db?: Database
): Promise<void> {
  try {
    if (db) {
      // Use existing transaction
      return await setSubmissionSupplementsInTransaction(db, submissionId, supplements);
    } else {
      // Create new transaction
      return await withTransaction(async (transactionDb) => {
        return await setSubmissionSupplementsInTransaction(transactionDb, submissionId, supplements);
      });
    }
  } catch (err) {
    logger.error("Failed to set submission supplements", err);
    throw new Error("Failed to set submission supplements");
  }
}

export async function updateTankPreset(
  tank: Partial<Tank> & { member_id: number; preset_name: string }
) {
  return updateOne(
    tableName,
    { member_id: tank.member_id, preset_name: tank.preset_name },
    {
      updated_on: new Date().toISOString(),
      ...tank,
    }
  );
}

export async function saveCredential(data: {
  member_id: number;
  credential_id: string;
  public_key: Buffer;
  counter: number;
  transports?: string;
  device_name?: string;
  authenticator_attachment?: string;
}): Promise<number> {
  const conn = writeConn;
  const stmt = await conn.prepare(`
    INSERT INTO webauthn_credentials (
      member_id, credential_id, public_key, counter, transports, device_name, authenticator_attachment
    ) VALUES (?, ?, ?, ?, ?, ?, ?)
    RETURNING id
  `);

  try {
    const result = await stmt.get<{ id: number }>(
      data.member_id,
      data.credential_id,
      data.public_key,
      data.counter,
      data.transports || null,
      data.device_name || null,
      data.authenticator_attachment || null
    );

    if (!result) {
      throw new Error("Failed to insert credential");
    }

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

export async function getCredentialById(id: number): Promise<WebAuthnCredential | null> {
  const rows = await query<WebAuthnCredential>(
    "SELECT * FROM webauthn_credentials WHERE id = ?",
    [id]
  );
  return rows[0] || null;
}

export async function getCredentialsByMember(memberId: number): Promise<WebAuthnCredential[]> {
  return query<WebAuthnCredential>(
    "SELECT * FROM webauthn_credentials WHERE member_id = ? ORDER BY created_on DESC",
    [memberId]
  );
}