Function bodies 451 total

async function runTests() {
  console.log("🧪 Testing Image Processor\n");
  console.log("=".repeat(50));

  // Create test directory
  const testDir = path.join(process.cwd(), "test-images");
  await fs.mkdir(testDir, { recursive: true });
  await fs.mkdir(path.join(testDir, "processed"), { recursive: true });

  // Test 1: Valid image processing
  console.log("\n📸 Test 1: Processing valid image");
  console.log("-".repeat(30));

  const validImage = await createTestImage(1200, 800, "Fish Photo");
  await fs.writeFile(path.join(testDir, "test-fish.jpg"), validImage);
  console.log(`✓ Created test image: ${validImage.length} bytes`);

  const result = await processImage(validImage);
  console.log(`✓ Processed successfully in ${result.metadata.processingTimeMs}ms`);
  console.log(
    `  • Original: ${result.original.width}x${result.original.height} (${result.original.size} bytes)`
  );
  console.log(
    `  • Medium: ${result.medium.width}x${result.medium.height} (${result.medium.size} 

async function createTestImage(outputPath: string) {
  console.log("Creating test image...");

  // Create a test image with text
  const svg = `
    <svg width="1200" height="800" xmlns="http://www.w3.org/2000/svg">
      <rect width="1200" height="800" fill="#4a90e2"/>
      <text x="50%" y="50%" font-size="60" fill="white" text-anchor="middle" dominant-baseline="middle">
        Test Image 1200x800
      </text>
      <text x="50%" y="60%" font-size="30" fill="white" text-anchor="middle" dominant-baseline="middle">
        Created at ${new Date().toISOString()}
      </text>
    </svg>
  `;

  const buffer = await sharp(Buffer.from(svg)).jpeg().toBuffer();

  await fs.writeFile(outputPath, buffer);
  console.log(`Test image created at: ${outputPath}`);
  return outputPath;
}

async function testImageProcessing(imagePath: string) {
  console.log(`\n=== Testing image: ${imagePath} ===\n`);

  try {
    // Read the image
    const buffer = await fs.readFile(imagePath);
    console.log(`✓ Read image (${buffer.length} bytes)`);

    // Get original metadata
    const originalMetadata = await sharp(buffer).metadata();
    console.log(
      `✓ Original: ${originalMetadata.width}x${originalMetadata.height}, format: ${originalMetadata.format}`
    );

    // Validate the image
    console.log("\n--- Validation ---");
    try {
      await validateImageBuffer(buffer);
      console.log("✓ Image validation passed");
    } catch (error) {
      console.log(`✗ Validation failed: ${error.message}`);
      return;
    }

    // Process the image
    console.log("\n--- Processing ---");
    const startTime = Date.now();
    const result = await processImage(buffer, { preferWebP: false });
    const processingTime = Date.now() - startTime;

    console.log(`✓ Processing co

async function testMultipleImages() {
  const testDir = path.join(process.cwd(), "test-images");
  await fs.mkdir(testDir, { recursive: true });

  console.log("=== Creating test images of various sizes ===\n");

  // Create test images of different sizes
  const testCases = [
    { width: 800, height: 600, name: "landscape-small" },
    { width: 1920, height: 1080, name: "landscape-hd" },
    { width: 3000, height: 2000, name: "landscape-large" },
    { width: 600, height: 800, name: "portrait-small" },
    { width: 1080, height: 1920, name: "portrait-hd" },
    { width: 500, height: 500, name: "square-small" },
    { width: 2000, height: 2000, name: "square-large" },
  ];

  for (const testCase of testCases) {
    const svg = `
      <svg width="${testCase.width}" height="${testCase.height}" xmlns="http://www.w3.org/2000/svg">
        <defs>
          <linearGradient id="grad" x1="0%" y1="0%" x2="100%" y2="100%">
            <stop offset="0%" style="stop-color:rgb(74,144,226);stop-op

async function main() {
  const args = process.argv.slice(2);

  if (args.length > 0) {
    // Test with provided image
    const imagePath = path.resolve(args[0]);
    try {
      await fs.access(imagePath);
      await testImageProcessing(imagePath);
    } catch (error) {
      console.error(`Error: Could not access file ${imagePath}`);
      process.exit(1);
    }
  } else {
    // Run automated tests with generated images
    console.log("No image path provided. Running automated tests with generated images.\n");
    await testMultipleImages();
  }

  console.log("\n=== All tests completed ===");
}

async function main() {
  console.log("Testing oEmbed Implementation\n");
  console.log("=".repeat(80));

  for (const url of testUrls) {
    console.log(`\nFetching: ${url}`);
    console.log("-".repeat(80));

    try {
      const metadata = await parseVideoUrlWithOEmbed(url);

      console.log(`Platform: ${metadata.platform}`);
      console.log(`Video ID: ${metadata.videoId}`);
      console.log(`Title: ${metadata.title || "(not available)"}`);
      console.log(`Author: ${metadata.author || "(not available)"}`);
      console.log(`Thumbnail: ${metadata.thumbnailUrl}`);
      console.log(`Dimensions: ${metadata.width}x${metadata.height || "(not available)"}`);
    } catch (error) {
      console.error(`Error: ${error}`);
    }
  }

  console.log("\n" + "=".repeat(80));
  console.log("\nCache Statistics:");
  const stats = getCacheStats();
  console.log(`  Total entries: ${stats.size}`);
  stats.entries.forEach((entry) => {
    const age = Math.round(entry.age / 1000);
    console.

export function validatePasswordComplexity(password: string): PasswordValidationResult {
  const errors: string[] = [];

  // Level 1: Length requirement (applies to all levels)
  if (password.length < MIN_LENGTH) {
    errors.push(`Password must be at least ${MIN_LENGTH} characters`);
  }

  if (PASSWORD_COMPLEXITY_LEVEL >= 2) {
    // Level 2: Basic complexity requirements
    if (!/[a-z]/.test(password)) {
      errors.push("Password must contain at least one lowercase letter");
    }
    if (!/[A-Z]/.test(password)) {
      errors.push("Password must contain at least one uppercase letter");
    }
    if (!/[0-9]/.test(password)) {
      errors.push("Password must contain at least one number");
    }
  }

  if (PASSWORD_COMPLEXITY_LEVEL >= 3) {
    // Level 3: Special character requirement
    // eslint-disable-next-line no-useless-escape
    if (!/[!@#$%^&*()_+\-=\[\]{};':"\\|,.<>\/?]/.test(password)) {
      errors.push("Password must contain at least one special character");
    

export function getPasswordRequirementsMessage(): string {
  let message = `Must be at least ${MIN_LENGTH} characters`;

  if (PASSWORD_COMPLEXITY_LEVEL >= 2) {
    message += " and contain uppercase, lowercase, and numbers";
  }

  if (PASSWORD_COMPLEXITY_LEVEL >= 3) {
    message += ", and a special character";
  }

  return message;
}

function withNodeCallback<Type = void>(
  fn: (callback: (error: Error | null | undefined, result: Type) => void) => void
): Promise<Type> {
  return new Promise<Type>((resolve, reject) => {
    fn((error, result) => {
      if (error) {
        reject(error);
      } else {
        resolve(result);
      }
    });
  });
}

export async function makePasswordEntry(password: string): Promise<ScryptPassword> {
  const salt = randomBytes(16);
  const scryptOptions = {
    N: 16384,
    r: 8,
    p: 1,
  };
  const hash = await withNodeCallback<Buffer>((callback) =>
    scrypt(password, salt, kKeyLen, scryptOptions, callback)
  );
  const passwordEntry: ScryptPassword = {
    ...scryptOptions,
    salt: salt.toString("base64"),
    hash: hash.toString("base64"),
  };
  return passwordEntry;
}

export async function checkPassword(
  passwordEntry: ScryptPassword | undefined,
  clearPassword: string
) {
  if (passwordEntry === undefined) {
    return false;
  }

  const result = await withNodeCallback<Buffer>((callback) =>
    scrypt(
      clearPassword,
      Buffer.from(passwordEntry.salt, "base64"),
      kKeyLen,
      {
        N: passwordEntry.N,
        r: passwordEntry.r,
        p: passwordEntry.p,
      },
      callback
    )
  );
  return result.toString("base64") === passwordEntry.hash;
}

export async function generateRegistrationOptionsForMember(
  memberId: number,
  userEmail: string,
  userName: string
): Promise<PublicKeyCredentialCreationOptionsJSON> {
  // Get existing credentials to exclude (prevent re-registering same authenticator)
  const existingCredentials = await getCredentialsByMember(memberId);

  const options = await generateRegistrationOptions({
    rpName,
    rpID,
    userName: userEmail,
    userDisplayName: userName,
    // Use member ID as user ID (stable identifier)
    userID: Buffer.from(String(memberId)),
    attestationType: "none", // We don't need attestation for this use case
    excludeCredentials: existingCredentials.map((cred) => ({
      id: cred.credential_id,
      transports: cred.transports
        ? (JSON.parse(cred.transports) as AuthenticatorTransportFuture[])
        : undefined,
    })),
    authenticatorSelection: {
      residentKey: "required", // Require discoverable credentials for iCloud Keychain sync
      userVerific

export async function generateAuthenticationOptionsForLogin(): Promise<PublicKeyCredentialRequestOptionsJSON> {
  const options = await generateAuthenticationOptions({
    rpID,
    userVerification: "preferred",
    // Don't specify allowCredentials - allow any registered passkey (discoverable)
  });

  // Save challenge for verification
  await saveChallenge(options.challenge, "authentication");

  return options;
}

export async function createActivity(
  activityType: "submission_approved" | "award_granted",
  memberId: number,
  relatedId: string,
  activityData: SubmissionApprovedData | AwardGrantedData
): Promise<void> {
  try {
    const conn = db(true);
    const stmt = await conn.prepare(`
            INSERT INTO activity_feed (activity_type, member_id, related_id, activity_data)
            VALUES (?, ?, ?, ?)
        `);

    try {
      await stmt.run(activityType, memberId, relatedId, JSON.stringify(activityData));
    } finally {
      await stmt.finalize();
    }

    logger.info(`Created activity: ${activityType} for member ${memberId}`);
  } catch (error) {
    logger.error("Failed to create activity feed entry", error);
    throw new Error("Failed to create activity feed entry");
  }
}

export async function getRecentActivity(limit: number = 10): Promise<ActivityFeedItem[]> {
  const activities = await query<ActivityFeedItem>(
    `
        SELECT
            af.*,
            m.display_name as member_name
        FROM activity_feed af
        JOIN members m ON af.member_id = m.id
        ORDER BY af.created_at DESC, af.id DESC
        LIMIT ?
    `,
    [limit]
  );

  // Batch fetch awards for all members in the activity feed
  const memberIds = [...new Set(activities.map((a) => a.member_id))];
  const awardsMap = await getAwardsForMembers(memberIds);

  // Attach awards to each activity
  return activities.map((activity) => ({
    ...activity,
    awards: awardsMap.get(activity.member_id) || [],
  }));
}

export async function deleteExpiredAuthCodes(cutoff: Date) {
  try {
    const conn = writeConn;
    const deleteRow = await conn.prepare("DELETE FROM auth_codes WHERE expires_on < ?");
    try {
      return await deleteRow.run(cutoff.toISOString());
    } finally {
      await deleteRow.finalize();
    }
  } catch (err) {
    logger.error("Failed to delete auth codes", err);
    throw new Error("Failed to delete auth codes");
  }
}

export async function registerForCares(
  collectionEntryId: number,
  memberId: number,
  photoKey: string,
  photoUrl: string
): Promise<void> {
  // Verify the entry exists, belongs to this member, is a CARES-eligible species,
  // and is not already registered
  const entries = await query<{
    id: number;
    group_id: number | null;
    is_cares_species: number;
    cares_registered_at: string | null;
  }>(
    `SELECT c.id, c.group_id, sng.is_cares_species, c.cares_registered_at
     FROM species_collection c
     LEFT JOIN species_name_group sng ON c.group_id = sng.group_id
     WHERE c.id = ? AND c.member_id = ? AND c.removed_date IS NULL`,
    [collectionEntryId, memberId]
  );

  if (entries.length === 0) {
    throw new Error('Collection entry not found or access denied');
  }

  const entry = entries[0];

  if (!entry.group_id) {
    throw new Error('Only species linked to the database can be registered for CARES');
  }

  if (!entry.is_cares_species) {
    throw new Erro

export async function updateCaresPhoto(
  collectionEntryId: number,
  memberId: number,
  photoKey: string,
  photoUrl: string
): Promise<{ oldPhotoKey: string | null }> {

export async function getCaresEligibility(
  collectionEntryId: number,
  memberId: number
): Promise<{
  eligible: boolean;
  registered: boolean;
  photoUrl: string | null;
} | null> {

export async function getCaresProfile(memberId: number): Promise<CaresProfile> {
  // Get CARES registrations with seal calculations
  const registrations = await query<RegistrationRow>(
    `SELECT
      c.id AS collection_id,
      c.group_id,
      COALESCE(
        (SELECT common_name FROM species_common_name WHERE group_id = c.group_id LIMIT 1),
        c.common_name
      ) AS common_name,
      COALESCE(
        sng.canonical_genus || ' ' || sng.canonical_species_name,
        c.scientific_name
      ) AS scientific_name,
      c.cares_registered_at,
      c.cares_photo_key,
      c.cares_photo_url,
      c.cares_last_confirmed,
      c.images,
      CASE WHEN c.images IS NOT NULL AND c.images != '[]' THEN 1 ELSE 0 END AS has_photo,
      COALESCE((
        SELECT COUNT(*) FROM cares_article ca
        WHERE ca.member_id = c.member_id AND ca.species_group_id = c.group_id
      ), 0) AS article_count,
      COALESCE((
        SELECT COUNT(*) FROM cares_fry_share fs
        WHERE 

export async function getCaresRegistrations(memberId: number): Promise<Array<{
  collection_id: number;
  group_id: number;
  common_name: string | null;
  scientific_name: string | null;
}>> {

export async function createFryShare(
  memberId: number,
  speciesGroupId: number,
  recipientName: string,
  recipientMemberId: number | null,
  recipientClub: string | null,
  shareDate: string,
  notes: string | null
): Promise<number> {
  // Verify member has this species registered for CARES
  const registered = await query<{ cnt: number }>(
    `SELECT COUNT(*) AS cnt FROM species_collection
     WHERE member_id = ? AND group_id = ? AND cares_registered_at IS NOT NULL AND removed_date IS NULL`,
    [memberId, speciesGroupId]
  );

  if ((registered[0]?.cnt ?? 0) === 0) {
    throw new Error('You must have this species registered for CARES to record a fry share');
  }

  const stmt = await writeConn.prepare(`
    INSERT INTO cares_fry_share (member_id, species_group_id, recipient_name, recipient_member_id, recipient_club, share_date, notes)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `);

  try {
    const result = await stmt.run(
      memberId, speciesGroupId, recipientName,
      recip

export async function getCaresStats(): Promise<CaresStats> {
  const rows = await query<{ species_count: number; member_count: number }>(
    `SELECT
      COUNT(DISTINCT sc.group_id) AS species_count,
      COUNT(DISTINCT sc.member_id) AS member_count
    FROM species_collection sc
    JOIN species_name_group sng ON sc.group_id = sng.group_id
    WHERE sng.is_cares_species = 1
      AND sc.removed_date IS NULL`
  );

  return {
    speciesCount: rows[0]?.species_count ?? 0,
    memberCount: rows[0]?.member_count ?? 0,
  };
}

export async function isMemberCaresParticipant(memberId: number): Promise<boolean> {
  const rows = await query<{ cnt: number }>(
    `SELECT COUNT(*) AS cnt
    FROM species_collection sc
    JOIN species_name_group sng ON sc.group_id = sng.group_id
    WHERE sc.member_id = ?
      AND sng.is_cares_species = 1
      AND sc.removed_date IS NULL`,
    [memberId]
  );
  return (rows[0]?.cnt ?? 0) > 0;
}

export async function getMemberCaresCount(memberId: number): Promise<number> {
  const rows = await query<{ cnt: number }>(
    `SELECT COUNT(*) AS cnt
    FROM species_collection sc
    JOIN species_name_group sng ON sc.group_id = sng.group_id
    WHERE sc.member_id = ?
      AND sng.is_cares_species = 1
      AND sc.removed_date IS NULL`,
    [memberId]
  );
  return rows[0]?.cnt ?? 0;
}

export async function getCollectionForMember(
  memberId: number,
  options?: {
    includeRemoved?: boolean;
    includePrivate?: boolean;
    viewerId?: number | null;
  }
): Promise<CollectionEntry[]> {
  const { includeRemoved = false, includePrivate = false, viewerId = null } = options || {};

  let sql = `
    SELECT
      c.*,
      sng.canonical_genus || ' ' || sng.canonical_species_name AS canonical_scientific_name,
      (SELECT common_name FROM species_common_name
       WHERE group_id = c.group_id LIMIT 1) AS canonical_common_name,
      sng.program_class,
      sng.species_type,
      sng.is_cares_species,
      m.display_name AS member_display_name
    FROM species_collection c
    LEFT JOIN species_name_group sng ON c.group_id = sng.group_id
    JOIN members m ON c.member_id = m.id
    WHERE c.member_id = ?
  `;

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

  // Filter by removed status
  if (!includeRemoved) {
    sql += ' AND c.removed_date IS NULL';
  }

  // Fi

export async function addToCollection(
  memberId: number,
  data: AddCollectionData
): Promise<number> {
  // Validate that we have either group_id OR common_name
  if (!data.group_id && !data.common_name) {
    throw new Error('Must provide either group_id (canonical species) or common_name');
  }

  // Check for duplicate - different logic for canonical vs non-canonical
  if (data.group_id) {
    // Canonical species - check by group_id
    const existing = await query<{ id: number }>(
      `SELECT id FROM species_collection
       WHERE member_id = ? AND group_id = ? AND removed_date IS NULL`,
      [memberId, data.group_id]
    );
    if (existing.length > 0) {
      throw new Error('Species already in collection. Please update the existing entry.');
    }
  } else {
    // Non-canonical species - check by common_name and scientific_name
    const existing = await query<{ id: number }>(
      `SELECT id FROM species_collection
       WHERE member_id = ?
         AND common_name =

export async function updateCollectionEntry(
  id: number,
  memberId: number,
  updates: UpdateCollectionData
): Promise<void> {
  // Build dynamic update query
  const updateFields: string[] = ['updated_at = CURRENT_TIMESTAMP'];
  const params: (string | number | null)[] = [];

  // If updating names, convert to custom entry (set group_id to NULL)
  if (updates.common_name !== undefined || updates.scientific_name !== undefined) {
    updateFields.push('group_id = ?');
    params.push(null);

    if (updates.common_name !== undefined) {
      updateFields.push('common_name = ?');
      params.push(updates.common_name || null);
    }

    if (updates.scientific_name !== undefined) {
      updateFields.push('scientific_name = ?');
      params.push(updates.scientific_name || null);
    }
  }

  if (updates.acquired_date !== undefined) {
    updateFields.push('acquired_date = ?');
    params.push(updates.acquired_date || null);
  }

  if (updates.notes !== undefined) {
    updateFields.p

export async function removeFromCollection(
  id: number,
  memberId: number
): Promise<void> {
  const stmt = await writeConn.prepare(`
    UPDATE species_collection
    SET removed_date = CURRENT_DATE, updated_at = CURRENT_TIMESTAMP
    WHERE id = ? AND member_id = ? AND removed_date IS NULL
  `);

  try {
    const info = await stmt.run(id, memberId);
    if (info.changes === 0) {
      throw new Error('Collection entry not found, already removed, or access denied');
    }
  } finally {
    await stmt.finalize();
  }
}

export async function getCollectionEntry(
  id: number,
  memberId?: number
): Promise<CollectionEntry | null> {
  let sql = `
    SELECT
      c.*,
      sng.canonical_species_name || ' ' || sng.canonical_genus AS canonical_scientific_name,
      (SELECT common_name FROM species_common_name
       WHERE group_id = c.group_id LIMIT 1) AS canonical_common_name,
      sng.program_class,
      sng.species_type,
      sng.is_cares_species,
      m.display_name AS member_display_name
    FROM species_collection c
    LEFT JOIN species_name_group sng ON c.group_id = sng.group_id
    JOIN members m ON c.member_id = m.id
    WHERE c.id = ?
  `;

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

  if (memberId) {
    sql += ' AND (c.member_id = ? OR c.visibility = ?)';
    params.push(memberId, 'public');
  } else {
    sql += ' AND c.visibility = ?';
    params.push('public');
  }

  const rows = await query<CollectionRow>(sql, params);

  if (rows.length === 0) return null;

  const row = rows[0];

export async function getCollectionStats(memberId: number): Promise<CollectionStats> {
  const stats = await query<StatsRow>(
    `SELECT
      COUNT(CASE WHEN removed_date IS NULL THEN 1 END) as current,
      COUNT(*) as lifetime
    FROM species_collection
    WHERE member_id = ?`,
    [memberId]
  );

  const byClass = await query<ClassCountRow>(
    `SELECT
      sng.program_class,
      COUNT(*) as count
    FROM species_collection c
    JOIN species_name_group sng ON c.group_id = sng.group_id
    WHERE c.member_id = ? AND c.removed_date IS NULL
    GROUP BY sng.program_class`,
    [memberId]
  );

  const byType = await query<TypeCountRow>(
    `SELECT
      sng.species_type,
      COUNT(*) as count
    FROM species_collection c
    JOIN species_name_group sng ON c.group_id = sng.group_id
    WHERE c.member_id = ? AND c.removed_date IS NULL
    GROUP BY sng.species_type`,
    [memberId]
  );

  return {
    current: stats[0]?.current || 0,
    lifetime: stats[0]?.lifetime || 0,

export async function getRecentCollectionAdditions(limit = 10): Promise<CollectionEntry[]> {
  const rows = await query<CollectionRow>(
    `SELECT
      c.*,
      sng.canonical_species_name || ' ' || sng.canonical_genus AS scientific_name,
      (SELECT common_name FROM species_common_name
       WHERE group_id = c.group_id LIMIT 1) AS common_name,
      sng.program_class,
      sng.species_type,
      sng.is_cares_species,
      m.display_name AS member_display_name
    FROM species_collection c
    JOIN species_name_group sng ON c.group_id = sng.group_id
    JOIN members m ON c.member_id = m.id
    WHERE c.visibility = 'public' AND c.removed_date IS NULL
    ORDER BY c.created_at DESC
    LIMIT ?`,
    [limit]
  );

  return rows.map(row => ({
    ...row,
    images: row.images ? JSON.parse(row.images) as ImageMetadata[] : null,
    species: {
      common_name: row.common_name || null,
      scientific_name: row.scientific_name || null,
      program_class: row.program_class || nu

export async function updateCollectionImages(
  id: number,
  memberId: number,
  images: ImageMetadata[]
): Promise<void> {
  // Validate max 5 images
  if (images.length > 5) {
    throw new Error('Maximum 5 images allowed per collection entry');
  }

  await updateCollectionEntry(id, memberId, { images });
}

export function db(write = false) {
  if (write) {
    return writeConn;
  } else {
    return readOnlyConn;
  }
}

export async function init() {
  readOnlyConn = await open({
    filename: config.database.file,
    driver: sqlite3.Database,
    mode: sqlite3.OPEN_READONLY,
  });

  writeConn = await open({
    filename: config.database.file,
    driver: sqlite3.Database,
    mode: sqlite3.OPEN_READWRITE,
  });
}

export async function insertOne(table: TableName, row: PartialRow) {
  try {
    const stmt = await writeConn.prepare(`
			INSERT INTO ${table}
			(${Object.keys(row).join(", ")})
			VALUES
			(${Object.keys(row)
        .map(() => "?")
        .join(", ")})`);
    try {
      await stmt.run(...Object.values(row));
    } finally {
      await stmt.finalize();
    }
  } catch (error) {
    throw new Error(`SQLite insert query failed: ${(error as Error).message}`);
  }
}

export async function updateOne(table: TableName, key: PartialRow, fields: PartialRow) {
  try {
    const updates = Object.keys(fields)
      .map((key) => `${key} = ?`)
      .join(", ");
    const where = Object.keys(key)
      .map((key) => `${key} = ?`)
      .join(" AND ");
    const stmt = await writeConn.prepare(`UPDATE ${table} SET ${updates} WHERE ${where}`);
    try {
      await stmt.run(...Object.values(fields), ...Object.values(key));
    } finally {
      await stmt.finalize();
    }
  } catch (error) {
    throw new Error(`SQLite update query failed: ${(error as Error).message}`);
  }
}

export async function query<T>(sql: string, params: unknown[] = []): Promise<T[]> {
  try {
    const db = readOnlyConn;
    const stmt = await db.prepare(sql);
    try {
      const rows: T[] = await stmt.all(...params);
      return rows;
    } finally {
      await stmt.finalize();
    }
  } catch (error) {
    throw new Error(`SQLite query failed: ${(error as Error).message}`);
  }
}

export async function deleteOne(table: TableName, key: PartialRow) {
  try {
    const where = Object.keys(key)
      .map((key) => `${key} = ?`)
      .join(" AND ");
    const deleteRow = await writeConn.prepare(`DELETE FROM ${table} WHERE ${where}`);
    try {
      return await deleteRow.run(...Object.values(key));
    } finally {
      await deleteRow.finalize();
    }
  } catch (error) {
    throw new Error(`SQLite delete failed: ${(error as Error).message}`);
  }
}

export async function withTransaction<T>(fn: (db: Database) => Promise<T>): Promise<T> {
  const db = writeConn;
  await db.exec("BEGIN TRANSACTION;");
  try {
    const result = await fn(db);
    await db.exec("COMMIT;");
    return result;
  } catch (err) {
    try {
      await db.exec("ROLLBACK;");
    } catch {
      // Ignore rollback errors - transaction may not be active
      // This is the standard pattern for sqlite3 package
    }
    throw err;
  }
}

export async function recordExternalDataSync(
  db: Database,
  groupId: number,
  source: ExternalDataSource,
  status: ExternalSyncStatus,
  linksAdded: number = 0,
  imagesAdded: number = 0,
  errorMessage?: string
): Promise<number> {
  try {
    const now = new Date().toISOString();

    const result = await db.run(
      `INSERT INTO external_data_sync_log
       (group_id, source, sync_date, status, links_added, images_added, error_message)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [groupId, source, now, status, linksAdded, imagesAdded, errorMessage || null]
    );

    // Update last_external_sync timestamp on species
    if (status === "success") {
      await db.run(
        `UPDATE species_name_group
         SET last_external_sync = ?
         WHERE group_id = ?`,
        [now, groupId]
      );
    }

    logger.info(
      `Recorded external data sync: source=${source}, group_id=${groupId}, status=${status}`
    );

    return result.lastID!;
  } catch (error) {
    log

export async function getSpeciesSyncLog(
  db: Database,
  groupId: number,
  source?: ExternalDataSource,
  limit = 100
): Promise<ExternalDataSyncLogEntry[]> {
  try {
    let query = `
      SELECT id, group_id, source, sync_date, status,
             links_added, images_added, error_message
      FROM external_data_sync_log
      WHERE group_id = ?
    `;
    const params: (number | string)[] = [groupId];

    if (source) {
      query += ` AND source = ?`;
      params.push(source);
    }

    query += ` ORDER BY sync_date DESC LIMIT ?`;
    params.push(limit);

    const entries = await db.all<ExternalDataSyncLogEntry[]>(query, params);
    return entries;
  } catch (error) {
    logger.error("Failed to get species sync log", error);
    throw error;
  }
}

export async function getAllSyncLog(
  db: Database,
  source?: ExternalDataSource,
  status?: ExternalSyncStatus,
  limit = 100
): Promise<ExternalDataSyncLogEntry[]> {
  try {
    let query = `
      SELECT id, group_id, source, sync_date, status,
             links_added, images_added, error_message
      FROM external_data_sync_log
      WHERE 1=1
    `;
    const params: (number | string)[] = [];

    if (source) {
      query += ` AND source = ?`;
      params.push(source);
    }

    if (status) {
      query += ` AND status = ?`;
      params.push(status);
    }

    query += ` ORDER BY sync_date DESC LIMIT ?`;
    params.push(limit);

    const entries = await db.all<ExternalDataSyncLogEntry[]>(query, params);
    return entries;
  } catch (error) {
    logger.error("Failed to get all sync log", error);
    throw error;
  }
}

export async function getSpeciesWithMissingExternalData(
  db: Database,
  minSubmissions = 1
): Promise<SpeciesWithMissingExternalData[]> {
  try {
    const species = await db.all<SpeciesWithMissingExternalData[]>(
      `SELECT
         sng.group_id,
         sng.canonical_genus,
         sng.canonical_species_name,
         COUNT(DISTINCT s.submission_id) as submission_count,
         sng.last_external_sync
       FROM species_name_group sng
       INNER JOIN species_scientific_name ssn ON sng.group_id = ssn.group_id
       INNER JOIN submissions s ON s.scientific_name_id = ssn.scientific_name_id
       WHERE s.status = 'approved'
       AND NOT EXISTS (
         SELECT 1 FROM species_external_references ser
         WHERE ser.group_id = sng.group_id
       )
       AND NOT EXISTS (
         SELECT 1 FROM species_images si
         WHERE si.group_id = sng.group_id
       )
       GROUP BY sng.group_id, sng.canonical_genus, sng.canonical_species_name, sng.last_external_sync
       H

export async function getSpeciesNeedingExternalSync(
  db: Database,
  daysOld = 90
): Promise<SpeciesNeedingExternalSync[]> {
  try {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - daysOld);
    const cutoffISO = cutoffDate.toISOString();

    const species = await db.all<SpeciesNeedingExternalSync[]>(
      `SELECT
         sng.group_id,
         sng.canonical_genus,
         sng.canonical_species_name,
         sng.last_external_sync,
         CASE
           WHEN sng.last_external_sync IS NULL THEN NULL
           ELSE CAST((julianday('now') - julianday(sng.last_external_sync)) AS INTEGER)
         END as days_since_sync
       FROM species_name_group sng
       WHERE sng.last_external_sync IS NULL
          OR sng.last_external_sync < ?
       ORDER BY
         days_since_sync DESC NULLS FIRST,
         sng.canonical_genus,
         sng.canonical_species_name`,
      [cutoffISO]
    );

    return species;
  } catch (error) {
    logger.error("Fai

export async function getExternalDataSyncStats(
  db: Database
): Promise<ExternalDataSyncStats> {
  try {
    // Get overall statistics
    const overall = await db.get<{
      total_species: number;
      species_with_links: number;
      species_with_images: number;
    }>(
      `SELECT
         COUNT(DISTINCT sng.group_id) as total_species,
         COUNT(DISTINCT CASE WHEN ser.group_id IS NOT NULL THEN sng.group_id END) as species_with_links,
         COUNT(DISTINCT CASE WHEN si.group_id IS NOT NULL THEN sng.group_id END) as species_with_images
       FROM species_name_group sng
       LEFT JOIN species_external_references ser ON sng.group_id = ser.group_id
       LEFT JOIN species_images si ON sng.group_id = si.group_id`
    );

    // Get sync operation statistics
    const syncStats = await db.get<{
      total_syncs: number;
      successful_syncs: number;
      error_count: number;
      last_sync_date: string | null;
    }>(
      `SELECT
         COUNT(*) as total_syncs,
 

export async function updateIucnData(
  db: Database,
  groupId: number,
  data: IUCNData
): Promise<number> {
  try {
    const now = new Date().toISOString();

    const updates: string[] = ["iucn_redlist_category = ?", "iucn_last_updated = ?"];
    const values: (string | number | null)[] = [data.category, now];

    if (data.taxonId !== undefined) {
      updates.push("iucn_redlist_id = ?");
      values.push(data.taxonId);
    }

    if (data.populationTrend !== undefined) {
      updates.push("iucn_population_trend = ?");
      values.push(data.populationTrend);
    }

    if (data.url !== undefined) {
      updates.push("iucn_redlist_url = ?");
      values.push(data.url);
    }

    values.push(groupId);

    const stmt = await db.prepare(
      `UPDATE species_name_group
       SET ${updates.join(", ")}
       WHERE group_id = ?`
    );

    try {
      const result = await stmt.run(...values);

      if (!result.changes || result.changes === 0) {
        throw new Error(`Spec

export async function recordIucnSync(
  db: Database,
  groupId: number,
  status: SyncStatus,
  data?: IUCNData,
  errorMessage?: string
): Promise<number> {
  try {
    const now = new Date().toISOString();

    const stmt = await db.prepare(
      `INSERT INTO iucn_sync_log (group_id, sync_date, status, category_found, error_message)
       VALUES (?, ?, ?, ?, ?)
       RETURNING id`
    );

    try {
      const result = await stmt.get<{ id: number }>(
        groupId,
        now,
        status,
        data?.category || null,
        errorMessage || null
      );
      return result!.id;
    } finally {
      await stmt.finalize();
    }
  } catch (err) {
    logger.error("Failed to record IUCN sync", { groupId, status, error: err });
    throw new Error("Failed to record sync log");
  }
}

export async function getIucnSyncLog(
  db: Database,
  groupId?: number,
  limit?: number
): Promise<IUCNSyncLogEntry[]> {
  let query = `
    SELECT id, group_id, sync_date, status, category_found, error_message
    FROM iucn_sync_log
  `;

  const params: (number | undefined)[] = [];

  if (groupId !== undefined) {
    query += " WHERE group_id = ?";
    params.push(groupId);
  }

  query += " ORDER BY id DESC";

  if (limit !== undefined) {
    query += " LIMIT ?";
    params.push(limit);
  }

  return await db.all(query, params);
}

export async function getSpeciesWithMissingIucn(
  db: Database
): Promise<SpeciesWithMissingIUCN[]> {
  return await db.all(`
    SELECT group_id, canonical_genus, canonical_species_name, program_class
    FROM species_name_group
    WHERE iucn_redlist_category IS NULL
    ORDER BY canonical_genus, canonical_species_name
  `);
}