Function bodies 451 total

async function globalSetup() {
	console.log("Running global setup for E2E tests...");

	// Ensure test user exists
	await ensureTestUserExists(TEST_USER.email, TEST_USER.password, TEST_USER.displayName || "E2E Test User");

	console.log("Global setup complete ✓");
}

export async function login(page: Page, user: TestUser = TEST_USER): Promise<void> {
	// Navigate to test login page (only available in test mode)
	await page.goto("/test-login");

	// Fill in credentials
	await page.fill('input[name="email"]', user.email);
	await page.fill('input[name="password"]', user.password);

	// Submit form and wait for navigation to complete
	await Promise.all([
		page.waitForNavigation({ waitUntil: "networkidle" }),
		page.click('button[type="submit"]'),
	]);

	// Verify we're logged in by checking for logout button
	await page.waitForSelector('button:has-text("Log Out")', {
		timeout: 5000,
	});
}

export async function logout(page: Page): Promise<void> {
	// Navigate to test logout endpoint (destroys session and redirects to home)
	await page.goto("/test-logout");

	// Wait for redirect to home (logged out state)
	await page.waitForSelector('button:has-text("Log In"), a:has-text("Log In")');
}

  constructor(page: Page, options: { enabled?: boolean } = {}) {
    this.page = page;
    this.enabled = options.enabled ?? true;

    if (this.enabled) {
      this.setupNetworkLogging();
      this.setupConsoleLogging();
      this.setupPageErrorLogging();
    }
  }

  private setupNetworkLogging() {
    this.page.on("request", (request: Request) => {
      const log: NetworkLog = {
        method: request.method(),
        url: request.url(),
        status: null,
        statusText: "",
        requestHeaders: request.headers(),
        responseHeaders: {},
        requestBody: this.getRequestBody(request),
        responseBody: null,
        timing: {
          startTime: Date.now(),
          endTime: null,
          duration: null,
        },
      };

      // Store with request as key so we can update on response
      this.networkLogs.push(log);
    });

    this.page.on("response", async (response: Response) => {
      const request = response.request();
      const log = this.networkLogs.find(
        (l) => l.url === request.url() && l.method === request.method() && !l.timing.endTime
      );

      if (log) {
        log.status = response.status();
        log.statusText = response.statusText();
        log.responseHeaders = response.he

  private setupConsoleLogging() {
    this.page.on("console", (msg) => {
      const log: ConsoleLog = {
        type: msg.type() as ConsoleLog["type"],
        text: msg.text(),
        location: msg.location()
          ? `${msg.location().url}:${msg.location().lineNumber}`
          : undefined,
        args: msg.args().map((arg) => String(arg)),
        timestamp: Date.now(),
      };

      this.consoleLogs.push(log);

      // Echo to test console with appropriate formatting
      const prefix = this.getConsolePrefix(log.type);
      console.log(`${prefix} ${log.text}`);
      if (log.location) {
        console.log(`  at ${log.location}`);
      }
    });
  }

  private setupPageErrorLogging() {
    this.page.on("pageerror", (error) => {
      console.error("❌ Page Error:", error.message);
      console.error(error.stack);
    });
  }

  private getRequestBody(request: Request): string | null {
    try {
      const postData = request.postData();
      if (!postData) return null;

      // Truncate large bodies
      return postData.length > 10000
        ? postData.substring(0, 10000) + "... (truncated)"
        : postData;
    } catch {
      return null;
    }
  }

  private async getResponseBody(response: Response): Promise<string | null> {
    try {
      const contentType = response.headers()["content-type"] || "";

      // Only capture text-based responses
      if (
        !contentType.includes("json") &&
        !contentType.includes("html") &&
        !contentType.includes("text") &&
        !contentType.includes("javascript")
      ) {
        return `<binary: ${contentType}>`;
      }

      const body = await response.text();

      // Truncate large responses
      return body.length > 50000
        ? body.substring(0, 50000) + "... (truncated)"
        : body;
    } catch {
      return null;
    }
  }

  private getConsolePrefix(type: ConsoleLog["type"]): string {
    switch (type) {
      case "error":
        return "🔴";
      case "warn":
        return "🟡";
      case "info":
        return "ℹ️";
      case "debug":
        return "🔍";
      default:
        return "📝";
    }
  }

  printSummary() {
    console.log("\n=== Test Debug Summary ===");
    console.log(`Network requests: ${this.networkLogs.length}`);
    console.log(`Failed requests: ${this.getFailedRequests().length}`);
    console.log(`Console messages: ${this.consoleLogs.length}`);
    console.log(`Console errors: ${this.getConsoleErrors().length}`);

    const failedRequests = this.getFailedRequests();
    if (failedRequests.length > 0) {
      console.log("\n❌ Failed Requests:");
      failedRequests.forEach((req) => {
        console.log(`  ${req.method} ${req.url} - ${req.status} ${req.statusText}`);
        if (req.error) {
          console.log(`    Error: ${req.error}`);
        }
      });
    }

    const consoleErrors = this.getConsoleErrors();
    if (consoleErrors.length > 0) {
      console.log("\n🔴 Console Errors:");
      consoleErrors.forEach((log) => {
        console.log(`  ${log.text}`);
        if (log.location) {
          console.log(`    at ${log.location}`);
        }
      

  findRequest(url: string | RegExp, method?: string): NetworkLog | undefined {
    const regex = typeof url === "string" ? new RegExp(url) : url;
    return this.networkLogs.find(
      (log) => regex.test(log.url) && (!method || log.method === method)
    );
  }

  async saveToFile(filename: string) {
    const fs = await import("fs/promises");
    const data = {
      networkLogs: this.networkLogs,
      consoleLogs: this.consoleLogs,
      summary: {
        totalRequests: this.networkLogs.length,
        failedRequests: this.getFailedRequests().length,
        totalConsoleMessages: this.consoleLogs.length,
        consoleErrors: this.getConsoleErrors().length,
      },
    };

    await fs.writeFile(filename, JSON.stringify(data, null, 2));
    console.log(`Debug logs saved to ${filename}`);
  }

export async function createTestSubmission(options: TestSubmissionOptions): Promise<number> {
	const dbConn = db(true); // Uses overridden connection in tests, else global writeConn

	const now = new Date().toISOString();
	const submittedOn = options.submitted ? now : null;
	// Set witnessed_on based on witnessedDaysAgo parameter (default 0 = today)
	// For waiting period tests, use 0 (today). For approval tests, use 70+ days to satisfy 60-day requirement
	const witnessedDaysAgo = options.witnessedDaysAgo !== undefined ? options.witnessedDaysAgo : 0;
	const witnessedOn = options.witnessed ? new Date(Date.now() - witnessedDaysAgo * 24 * 60 * 60 * 1000).toISOString() : null;

	// Set reproduction_date based on reproductionDaysAgo parameter (default 70 for mature spawns)
	const reproductionDaysAgo = options.reproductionDaysAgo !== undefined ? options.reproductionDaysAgo : 70;
	const reproductionDate = new Date(Date.now() - reproductionDaysAgo * 24 * 60 * 60 * 1000).toISOString().split("T"

export async function getTestDatabase(): Promise<Database> {
	const dbPath = path.join(__dirname, "../../db/database.db");
	return await open({
		filename: dbPath,
		driver: sqlite3.Database,
		mode: sqlite3.OPEN_READWRITE,
	});
}

export async function ensureTestUserExists(email: string, password: string, displayName: string, isAdmin = false): Promise<number> {
	const db = await getTestDatabase();

	try {
		// Check if user exists
		const existing = await db.get<{ id: number }>("SELECT id FROM members WHERE contact_email = ?", email);

		if (existing) {
			console.log(`Test user ${email} already exists (ID: ${existing.id})`);

			// Update admin status if needed
			if (isAdmin) {
				await db.run("UPDATE members SET is_admin = ? WHERE id = ?", 1, existing.id);
			}

			// Ensure password account exists
			const passwordAccount = await db.get("SELECT * FROM password_account WHERE member_id = ?", existing.id);
			if (!passwordAccount) {
				const passwordEntry = await makePasswordEntry(password);
				await db.run(
					`INSERT INTO password_account (member_id, N, r, p, salt, hash) VALUES (?, ?, ?, ?, ?, ?)`,
					existing.id,
					passwordEntry.N,
					passwordEntry.r,
					passwordEntry.p,
					passwordEntry.salt

export async function createTestSubmission(
	memberId: number,
	options: {
		speciesType?: string;
		speciesCommonName?: string;
		speciesLatinName?: string;
		isDraft?: boolean;
	} = {}
): Promise<number> {
	const db = await getTestDatabase();

	try {
		const {
			speciesType = "Fish",
			speciesCommonName = "Test Guppy",
			speciesLatinName = "Poecilia reticulata",
			isDraft = true,
		} = options;

		const result = await db.run(
			`
			INSERT INTO submissions (
				member_id, species_type, species_common_name, species_latin_name,
				reproduction_date, temperature, ph, submitted_on, program
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
		`,
			memberId,
			speciesType,
			speciesCommonName,
			speciesLatinName,
			new Date().toISOString(),
			"75",
			"7.0",
			isDraft ? null : new Date().toISOString(),
			speciesType.toLowerCase()
		);

		const submissionId = result.lastID as number;
		console.log(`Created test submission ${submissionId} for member ${memberId}`);
		return submissionId;

export async function deleteSubmission(submissionId: number): Promise<void> {
	const db = await getTestDatabase();

	try {
		await db.run("DELETE FROM submissions WHERE id = ?", submissionId);
		console.log(`Deleted submission ${submissionId}`);
	} finally {
		await db.close();
	}
}

export async function getSubmissionsForMember(memberId: number): Promise<any[]> {
	const db = await getTestDatabase();

	try {
		return await db.all("SELECT * FROM submissions WHERE member_id = ? ORDER BY id DESC", memberId);
	} finally {
		await db.close();
	}
}

export async function cleanupTestUserSubmissions(email: string): Promise<void> {
	const db = await getTestDatabase();

	try {
		const user = await db.get<{ id: number }>("SELECT id FROM members WHERE contact_email = ?", email);

		if (user) {
			await db.run("DELETE FROM submissions WHERE member_id = ?", user.id);
			console.log(`Cleaned up submissions for ${email}`);
		}
	} finally {
		await db.close();
	}
}

export async function fillTomSelectTypeahead(
	page: Page,
	fieldName: string,
	searchText: string,
	exactMatch = false,
	waitForFieldLinking = true
): Promise<void> {
	// Tom Select wraps the original select and creates a ts-wrapper sibling
	// Structure: <select class="tomselected"> + <div class="ts-wrapper"><div class="ts-control"><input></div></div>

	// Wait for Tom Select to be initialized on this field
	await page.waitForSelector(`select[name="${fieldName}"].tomselected`, { timeout: 10000 });

	// Get the Tom Select wrapper for this specific field (to avoid matching other dropdowns)
	const tsWrapper = page.locator(`select[name="${fieldName}"] + .ts-wrapper`);
	await tsWrapper.waitFor({ timeout: 2000 });

	// Find the ts-control and input
	const control = tsWrapper.locator('.ts-control');
	const input = control.locator('input');

	// Open dropdown and type search text
	await control.click();
	await input.fill(searchText);

	// Wait for the API call to complete (instead of arbitra

export async function selectTomSelectOption(page: Page, fieldName: string, value: string): Promise<void> {
	// Find the Tom Select control
	const control = page.locator(`select[name="${fieldName}"]`).locator('..').locator('.ts-control');

	// Click to open dropdown
	await control.click();

	// Wait for dropdown
	await page.waitForSelector('.ts-dropdown .ts-dropdown-content .option', {
		state: "visible",
	});

	// Click the option with the matching text or value
	await page.click(`.ts-dropdown .option:has-text("${value}"), .ts-dropdown .option[data-value="${value}"]`);

	await page.waitForTimeout(200);
}

export async function selectTomSelectMultiple(page: Page, fieldName: string, values: string[]): Promise<void> {
	// Wait for Tom Select to be initialized
	await page.waitForSelector(`select[name="${fieldName}"].tomselected`, { timeout: 5000 });

	// Find the ts-control
	const control = page.locator(`select[name="${fieldName}"] + .ts-wrapper .ts-control`);

	for (const value of values) {
		// Click to open dropdown
		await control.click();

		// Wait longer for dropdown to fully open/render
		await page.waitForTimeout(800);

		// Verify dropdown is actually open before clicking
		const dropdownVisible = await page.locator('.ts-dropdown .option').first().isVisible();
		if (!dropdownVisible) {
			// Dropdown didn't open, try clicking control again
			await control.click();
			await page.waitForTimeout(500);
		}

		// Click the option directly
		await page.click(`.ts-dropdown .option:has-text("${value}")`, { force: true });

		// Wait for selection to process
		await page.waitForTimeout(30

export async function clearTomSelect(page: Page, fieldName: string): Promise<void> {
	// Find the clear button in the Tom Select control
	const clearButton = page.locator(`select[name="${fieldName}"]`).locator('..').locator('.ts-control .clear');

	if (await clearButton.isVisible()) {
		await clearButton.click();
		await page.waitForTimeout(200);
	}
}

function le(e,t){var s=Object.assign({},re,t),i=s.dataAttr,n=s.labelField,o=s.valueField,r=s.disabledField,l=s.optgroupField,a=s.optgroupLabelField,c=s.optgroupValueField,d=e.tagName.toLowerCase(),u=e.getAttribute("placeholder")||e.getAttribute("data-placeholder")
if(!u&&!s.allowEmptyOption){let t=e.querySelector('option[value=""]')
t&&(u=t.textContent)}var p={placeholder:u,options:[],optgroups:[],items:[],maxItems:null}
return"select"===d?(()=>{var t,d=p.options,u={},h=1
let g=0
var f=e=>{var t=Object.assign({},e.dataset),s=i&&t[i]
return"string"==typeof s&&s.length&&(t=Object.assign(t,JSON.parse(s))),t},m=(e,t)=>{var i=P(e.value)
if(null!=i&&(i||s.allowEmptyOption)){if(u.hasOwnProperty(i)){if(t){var a=u[i][l]
a?Array.isArray(a)?a.push(t):u[i][l]=[a,t]:u[i][l]=t}}else{var c=f(e)
c[n]=c[n]||e.textContent,c[o]=c[o]||i,c[r]=c[r]||e.disabled,c[l]=c[l]||t,c.$option=e,c.$order=c.$order||++g,u[i]=c,d.push(c)}e.selected&&p.items.push(i)}}
p.maxItems=e.hasAttribute("multiple")?null:1,B(e.child

function getTypeaheadConfig(element) {
	const labelField = element.dataset.labelField || 'text';
	let searchFields = ['text'];

	if (element.dataset.searchFields) {
		const fields = element.dataset.searchFields.split(',').map(f => f.trim()).filter(f => f);
		if (fields.length > 0) {
			searchFields = fields;
		}
	}

	return {
		apiUrl: element.dataset.apiUrl,
		valueField: element.dataset.valueField || 'value',
		labelField: labelField,
		searchFields: searchFields,
		minQueryLength: parseInt(element.dataset.minQueryLength) || 2,
		maxItems: parseInt(element.dataset.maxItems) || 1,
		allowCreate: element.dataset.allowCreate === 'true',
		placeholder: element.dataset.placeholder || element.placeholder || '',
		loadingClass: element.dataset.loadingClass || 'loading',
		debounceMs: parseInt(element.dataset.debounceMs) || 300,
		createOnBlur: element.dataset.createOnBlur === "true",
	};
}

function buildTomSelectOptions(element, config) {
	return {
		valueField: config.valueField,
		labelField: config.labelField,
		searchField: config.searchFields || [config.labelField],
		create: config.allowCreate,
		createOnBlur: config.createOnBlur,
		maxItems: config.maxItems,
		placeholder: config.placeholder,

		load: function (query, callback) {
			if (query.length < config.minQueryLength) {
				callback();
				return;
			}

			element.classList.add(config.loadingClass);
			performFetchSearch(element, config, query, callback);
		},

		render: {
			option: function(item, escape) {
				return renderOption(item, escape, element);
			},
			loading: function(data, escape) {
				return '<div class="loading-indicator">Searching...</div>';
			}
		},

		onChange: function (value) {
			const selectedOption = this.options[value];

			// Manually update the underlying select element's value
			// Tom Select manages its own UI, but we need to ensure the form value is set
			if (element.value 

function performFetchSearch(element, config, query, callback) {
	const url = new URL(config.apiUrl, window.location.origin);
	url.searchParams.set('q', query);

	// Add any additional parameters from data attributes
	Object.keys(element.dataset).forEach(key => {
		if (key.startsWith('param')) {
			const paramName = key.replace('param', '').toLowerCase();
			url.searchParams.set(paramName, element.dataset[key]);
		}
	});

	fetch(url)
		.then(response => response.json())
		.then(data => {
			element.classList.remove(config.loadingClass);
			callback(data);
		})
		.catch(error => {
			console.error('Typeahead search error:', error);
			element.classList.remove(config.loadingClass);
			callback();
		});
}

function renderOption(item, escape, element) {
	const template = element.dataset.optionTemplate;

	if (template) {
		// Use custom template if provided
		return template
			.replace(/\{\{(\w+)\}\}/g, (match, field) => {
				return escape(item[field] || '');
			});
	}

	// Default rendering
	let html = '<div>';
	html += '<span class="font-medium">' + escape(item[element.dataset.labelField || 'text']) + '</span>';

	// Add secondary text if available
	const secondaryField = element.dataset.secondaryField;
	if (secondaryField && item[secondaryField]) {
		html += '<br><span class="text-sm text-gray-500">' + escape(item[secondaryField]) + '</span>';
	}

	html += '</div>';
	return html;
}

async function checkR2Usage() {
  console.log("=".repeat(60));
  console.log("R2 STORAGE USAGE ANALYSIS");
  console.log("=".repeat(60));

  // Initialize R2
  const r2Enabled = initR2();
  if (!r2Enabled) {
    console.error("❌ R2 is not configured. Check your config.json");
    process.exit(1);
  }

  console.log("\n📊 Fetching data from R2 and database...\n");

  try {
    // Get all R2 objects
    console.log("Listing all objects in R2...");
    const r2Objects = await listAllObjects("submissions/");

    // Calculate total size
    const totalBytes = r2Objects.reduce((sum, obj) => sum + obj.size, 0);
    const totalMB = totalBytes / (1024 * 1024);
    const totalGB = totalBytes / (1024 * 1024 * 1024);

    console.log("\n" + "=".repeat(60));
    console.log("R2 STORAGE STATISTICS");
    console.log("=".repeat(60));
    console.log(`Total objects: ${r2Objects.length.toLocaleString()}`);
    console.log(`Total size: ${totalMB.toFixed(2)} MB (${totalGB.toFixed(3)} GB)`);
    console.l

async function createTestStatusData() {
  try {
    // Create a test user
    const testEmail = `baptest+status_${Date.now()}@porcnick.com`;
    const memberId = await createMember(testEmail, "Status Test User");

    logger.info(`Created test user with ID: ${memberId}`);

    // Get current date for various status calculations
    const now = new Date();
    const database = db(true);

    // 1. DRAFT submission (not submitted)
    await database.run(
      `
      INSERT INTO submissions (
        member_id, program, species_type, species_class, species_common_name, species_latin_name,
        water_type, count, reproduction_date, created_on, updated_on
      ) VALUES (?, 'fish', 'Fish', 'A', 'Draft Guppy', 'Poecilia reticulata',
        'Fresh', '20', date('now', '-10 days'), datetime('now'), datetime('now'))
    `,
      [memberId]
    );
    logger.info("Created DRAFT submission");

    // 2. PENDING WITNESS submission (submitted, needs witness)
    await database.run(
      `
   

async function downloadImage(url: string): Promise<Buffer> {
  console.log(`  Downloading: ${url}`);

  try {
    const response = await fetch(url, {
      headers: {
        'User-Agent': 'Mozilla/5.0 (compatible; BAS-BAP-Bot/1.0)',
      },
    });

    if (!response.ok) {
      throw new Error(`HTTP ${response.status}: ${response.statusText}`);
    }

    const arrayBuffer = await response.arrayBuffer();
    return Buffer.from(arrayBuffer);
  } catch (error) {
    throw new Error(`Failed to download: ${error instanceof Error ? error.message : String(error)}`);
  }
}

async function createThumbnail(imageBuffer: Buffer): Promise<Buffer> {
  try {
    return await sharp(imageBuffer)
      .resize(THUMBNAIL_WIDTH, THUMBNAIL_HEIGHT, {
        fit: 'inside',
        withoutEnlargement: true,
      })
      .jpeg({ quality: THUMBNAIL_QUALITY, progressive: true })
      .toBuffer();
  } catch (error) {
    throw new Error(`Failed to create thumbnail: ${error instanceof Error ? error.message : String(error)}`);
  }
}

async function uploadToR2(key: string, buffer: Buffer, contentType: string = 'image/jpeg'): Promise<void> {
  try {
    await s3Client.send(
      new PutObjectCommand({
        Bucket: config.storage.s3Bucket,
        Key: key,
        Body: buffer,
        ContentType: contentType,
        CacheControl: 'public, max-age=31536000', // 1 year
      })
    );
  } catch (error) {
    throw new Error(`Failed to upload to R2: ${error instanceof Error ? error.message : String(error)}`);
  }
}

async function checkR2Exists(key: string): Promise<boolean> {
  try {
    await s3Client.send(
      new HeadObjectCommand({
        Bucket: config.storage.s3Bucket,
        Key: key,
      })
    );
    return true;
  } catch {
    return false;
  }
}

async function updateImageUrl(
  db: any,
  imageId: number,
  newUrl: string,
  originalUrl: string,
  source: string,
  attribution?: string
): Promise<void> {
  await db.run(
    `UPDATE species_images
     SET image_url = ?,
         original_url = ?,
         source = ?,
         attribution = ?,
         license = ?
     WHERE id = ?`,
    [newUrl, originalUrl, source, attribution || 'FishBase.org', 'CC BY-NC', imageId]
  );
}

async function main() {
  const args = process.argv.slice(2);
  const dryRun = args.includes('--dry-run');
  const force = args.includes('--force');
  const all = args.includes('--all');
  const speciesIdArg = args.find(arg => arg.startsWith('--species-id='));
  const speciesId = speciesIdArg ? parseInt(speciesIdArg.split('=')[1]) : undefined;

  console.log('\n=== Download & Upload Species Images to R2 ===\n');
  console.log(`Mode: ${dryRun ? '🟡 DRY RUN' : '🔴 EXECUTE'}`);
  console.log(`Force re-upload: ${force}`);
  console.log(`Thumbnail size: ${THUMBNAIL_WIDTH}x${THUMBNAIL_HEIGHT}`);
  console.log(`Quality: ${THUMBNAIL_QUALITY}%\n`);

  const dbPath = join(__dirname, '../db/database.db');
  const db = await open({
    filename: dbPath,
    driver: sqlite3.Database,
    mode: dryRun ? sqlite3.OPEN_READONLY : sqlite3.OPEN_READWRITE,
  });

  // Get images to process
  let query = `
    SELECT
      si.id,
      si.group_id,
      si.image_url,
      si.display_order,
      sng.canoni

async function main() {
  console.log("Opening database...");

  // Open database connection
  const db = await open({
    filename: config.databaseFile,
    driver: sqlite3.Database,
    mode: sqlite3.OPEN_READONLY,
  });

  console.log("Querying species database...");

  // Query all species with their synonyms
  const stmt = await db.prepare(`
    SELECT
      sng.group_id,
      sng.species_type,
      sng.program_class,
      sng.canonical_genus,
      sng.canonical_species_name,
      sng.base_points,
      sng.is_cares_species,
      sng.external_references,
      GROUP_CONCAT(sn.common_name, ' | ') as common_names,
      GROUP_CONCAT(sn.scientific_name, ' | ') as scientific_names,
      COUNT(sn.name_id) as synonym_count
    FROM species_name_group sng
    LEFT JOIN species_name sn ON sng.group_id = sn.group_id
    GROUP BY sng.group_id
    ORDER BY
      sng.species_type,
      sng.program_class,
      sng.canonical_genus,
      sng.canonical_species_name
  `);

  const specie

function randomChoice<T>(arr: T[]): T {
  if (arr.length === 0) {
    throw new Error("Cannot select from empty array");
  }
  return arr[Math.floor(Math.random() * arr.length)]!;
}

function generateFishSubmission(memberName: string, memberEmail: string): FormValues {
  const fish = randomChoice(fishNames);
  const waterType = randomChoice(["Fresh", "Brackish"] as const);

  return {
    member_name: memberName,
    member_email: memberEmail,
    species_type: "Fish",
    species_class: fish.class,
    species_common_name: fish.common,
    species_latin_name: fish.latin,
    water_type: waterType,
    count: randomInt(10, 50).toString(),
    reproduction_date: randomDate(90).toISOString().split("T")[0],
    tank_size: randomChoice(["10", "20", "29", "40", "55", "75"]) + " gallons",
    filter_type: randomChoice(filterTypes),
    water_change_volume: randomChoice(["10%", "20%", "25%", "30%"]),
    water_change_frequency: randomChoice(["weekly", "bi-weekly", "twice weekly"]),
    temperature: randomInt(72, 82).toString() + "F",
    ph: (6.5 + Math.random() * 1.5).toFixed(1),
    substrate_type: randomChoice(substrates),
    substrate_depth: randomInt(1, 3) + " inche

function generatePlantSubmission(memberName: string, memberEmail: string): FormValues {
  const plant = randomChoice(plantNames);

  return {
    member_name: memberName,
    member_email: memberEmail,
    species_type: "Plant",
    species_class: plant.class,
    species_common_name: plant.common,
    species_latin_name: plant.latin,
    water_type: "Fresh",
    reproduction_date: randomDate(60).toISOString().split("T")[0],
    tank_size: randomChoice(["10", "20", "29", "40"]) + " gallons",
    filter_type: randomChoice(filterTypes),
    water_change_volume: randomChoice(["20%", "25%", "30%"]),
    water_change_frequency: "weekly",
    temperature: randomInt(70, 78).toString() + "F",
    ph: (6.8 + Math.random() * 0.6).toFixed(1),
    substrate_type: "planted substrate",
    substrate_depth: "2-3 inches",
    substrate_color: "black",
    propagation_method: randomChoice(propagationMethods),
    light_type: randomChoice(lightTypes),
    light_strength: randomChoice(["low", "medium", "

function generateCoralSubmission(memberName: string, memberEmail: string): FormValues {
  const coral = randomChoice(coralNames);

  return {
    member_name: memberName,
    member_email: memberEmail,
    species_type: "Coral",
    species_class: coral.class,
    species_common_name: coral.common,
    species_latin_name: coral.latin,
    water_type: "Salt",
    reproduction_date: randomDate(120).toISOString().split("T")[0],
    tank_size: randomChoice(["20", "40", "75", "120"]) + " gallons",
    filter_type: "protein skimmer + sump",
    water_change_volume: randomChoice(["10%", "15%", "20%"]),
    water_change_frequency: "weekly",
    temperature: randomInt(76, 80).toString() + "F",
    ph: (8.1 + Math.random() * 0.3).toFixed(1),
    substrate_type: "live sand",
    substrate_depth: "2 inches",
    substrate_color: "white",
    foods: [randomChoice(coralFoods), randomChoice(coralFoods)],
    light_type: randomChoice(["LED", "T5", "Metal Halide"]),
    light_strength: coral.class === 

function generateInvertSubmission(memberName: string, memberEmail: string): FormValues {
  const invert = randomChoice(invertNames);

  return {
    member_name: memberName,
    member_email: memberEmail,
    species_type: "Invert",
    species_class: invert.class,
    species_common_name: invert.common,
    species_latin_name: invert.latin,
    water_type: "Fresh",
    count: randomInt(20, 100).toString(),
    reproduction_date: randomDate(30).toISOString().split("T")[0],
    tank_size: randomChoice(["5", "10", "20"]) + " gallons",
    filter_type: "sponge filter",
    water_change_volume: "20%",
    water_change_frequency: "weekly",
    temperature: randomInt(72, 78).toString() + "F",
    ph: (7.0 + Math.random() * 0.5).toFixed(1),
    substrate_type: randomChoice(["gravel", "sand"]),
    substrate_depth: "1-2 inches",
    substrate_color: randomChoice(["natural", "black"]),
    foods: ["algae", "biofilm", "blanched vegetables"],
    spawn_locations: ["moss", "plants"],
  };
}

function generateCatfishSubmission(
  memberName: string,
  memberEmail: string,
  catfish: { common: string; latin: string; class: string }
): FormValues {
  return {
    member_name: memberName,
    member_email: memberEmail,
    species_type: "Fish",
    species_class: catfish.class,
    species_common_name: catfish.common,
    species_latin_name: catfish.latin,
    water_type: "Fresh",
    count: randomInt(20, 100).toString(),
    reproduction_date: randomDate(60).toISOString().split("T")[0],
    tank_size: randomChoice(["20", "29", "40", "55"]) + " gallons",
    filter_type: randomChoice(filterTypes),
    water_change_volume: randomChoice(["20%", "25%", "30%"]),
    water_change_frequency: "weekly",
    temperature: randomInt(74, 78).toString() + "F",
    ph: (6.8 + Math.random() * 0.8).toFixed(1),
    substrate_type: randomChoice(["sand", "fine gravel"]),
    substrate_depth: "2 inches",
    substrate_color: randomChoice(["natural", "black"]),
    foods: [
      randomChoice(["si

function generateSpecialtyFishSubmission(
  memberName: string,
  memberEmail: string,
  fish: { common: string; latin: string; class: string }
): FormValues {
  return {
    member_name: memberName,
    member_email: memberEmail,
    species_type: "Fish",
    species_class: fish.class,
    species_common_name: fish.common,
    species_latin_name: fish.latin,
    water_type: randomChoice(["Fresh", "Brackish"] as const),
    count: randomInt(10, 50).toString(),
    reproduction_date: randomDate(90).toISOString().split("T")[0],
    tank_size: randomChoice(["10", "20", "29", "40", "55", "75"]) + " gallons",
    filter_type: randomChoice(filterTypes),
    water_change_volume: randomChoice(["10%", "20%", "25%", "30%"]),
    water_change_frequency: randomChoice(["weekly", "bi-weekly", "twice weekly"]),
    temperature: randomInt(72, 82).toString() + "F",
    ph: (6.5 + Math.random() * 1.5).toFixed(1),
    substrate_type: randomChoice(substrates),
    substrate_depth: randomInt(1, 3) + " inch

async function createWitnessTestSubmissions(
  users: Array<{ id: number; name: string; email: string }>,
  johnId: number
) {
  logger.info("Creating submissions in various witness states...");

  const member1 = users.find((u) => u.name === "Sarah Johnson");
  const member2 = users.find((u) => u.name === "Michael Chen");
  const member3 = users.find((u) => u.name === "Emily Davis");
  const member4 = users.find((u) => u.name === "Robert Wilson");

  if (!member1 || !member2 || !member3 || !member4) {
    throw new Error("Required test users not found");
  }

  // 1. Draft submission (not submitted)
  logger.info("Creating draft submission...");
  const draftFormData = generateFishSubmission(member1.name, member1.email);
  const draftId = await createSubmission(member1.id, draftFormData, false); // false = draft
  logger.info(`Created draft submission ${draftId} for ${member1.name}`);

  // 2. Submitted - Awaiting Witness (pending)
  logger.info("Creating submission awaiting witness..

function inferProgramClass(tags: string, commonName: string): string {
  const tagLower = tags.toLowerCase();
  const nameLower = commonName.toLowerCase();

  // Check for specific plant types in order of specificity
  if (nameLower.includes("sword") || tagLower.includes("sword")) return "Sword Plants";
  if (nameLower.includes("anubias")) return "Anubias & Lagenandra";
  if (nameLower.includes("cryptocoryne") || nameLower.includes("crypt ")) return "Cryptocoryne";
  if (nameLower.includes("aponogeton") || nameLower.includes("crinum"))
    return "Apongetons & Criniums";
  if (nameLower.includes("lily") || nameLower.includes("nymphaea")) return "Water Lilies";
  if (
    tagLower.includes("floating") ||
    nameLower.includes("frogbit") ||
    nameLower.includes("duckweed")
  )
    return "Floating Plants";
  if (nameLower.includes("moss") || nameLower.includes("riccia") || nameLower.includes("liverwort"))
    return "Primitive Plants";
  if (
    tagLower.includes("stem plant") ||
   

async function main() {
  const csvPath = path.join(process.cwd(), "all_aquatic_plants_unified.csv");
  const outputPath = path.join(process.cwd(), "db", "migrations", "021-import-aquatic-plants.sql");

  console.log("Reading CSV:", csvPath);

  const csvContent = fs.readFileSync(csvPath, "utf-8");
  const parseResult = Papa.parse<PlantRow>(csvContent, {
    header: true,
    skipEmptyLines: true,
  });

  if (parseResult.errors.length > 0) {
    console.error("CSV parsing errors:", parseResult.errors);
  }

  console.log(`Parsed ${parseResult.data.length} rows`);

  // Group by scientific name
  const speciesMap = new Map<string, SpeciesGroup>();
  let skippedCount = 0;

  for (const row of parseResult.data) {
    const scientificName = row.Scientific_Name?.trim();

    if (!scientificName) {
      skippedCount++;
      continue;
    }

    const parsed = parseScientificName(scientificName);
    if (!parsed) {
      console.warn(`Skipping invalid scientific name: "${scientificName}"`)

function mapToIUCN(classification: string): string | null {
  const normalized = classification.trim().toUpperCase();

  // Direct mapping
  if (CARES_TO_IUCN_MAP[normalized]) {
    return CARES_TO_IUCN_MAP[normalized];
  }

  // Try to extract code from longer string (e.g., "Critically Endangered (CR)")
  const match = normalized.match(/\b(EX|EW|CR|EN|VU|NT|LC|DD|NE|C[A-Z]{2})\b/);
  if (match) {
    const code = match[1];
    return CARES_TO_IUCN_MAP[code] || null;
  }

  return null;
}

async function parseCSV(filePath: string): Promise<ParsedSpecies[]> {
  console.log(`Reading CSV file: ${filePath}`);

  const fileContent = await fs.readFile(filePath, "utf-8");
  const records: CSVRow[] = parse(fileContent, {
    columns: true,
    skip_empty_lines: true,
    trim: true,
  });

  console.log(`Found ${records.length} rows in CSV`);

  const parsed: ParsedSpecies[] = [];
  let skipped = 0;

  for (const row of records) {
    // Get classification from either column
    const rawClassification = row.classification || row.iucn_classification || "";

    if (!rawClassification) {
      skipped++;
      continue;
    }

    const iucnCategory = mapToIUCN(rawClassification);
    if (!iucnCategory) {
      console.warn(`Warning: Could not map classification "${rawClassification}" for ${row.scientific_name}`);
      skipped++;
      continue;
    }

    const parsed_name = parseScientificName(row.scientific_name);
    if (!parsed_name) {
      console.warn(`Warning: Could not