// src/tests/utils/cleanup.ts
import { getPool } from '../../services/db/connection.db';
import { logger } from '../../services/logger.server';

interface CleanupOptions {
  userIds?: (string | null | undefined)[];
  flyerIds?: (number | null | undefined)[];
  storeIds?: (number | null | undefined)[];
  recipeIds?: (number | null | undefined)[];
  budgetIds?: (number | null | undefined)[];
  masterItemIds?: (number | null | undefined)[];
  shoppingListIds?: (number | null | undefined)[];
  suggestedCorrectionIds?: (number | null | undefined)[];
  recipeCommentIds?: (number | null | undefined)[];
}

/**
 * A centralized utility to clean up database records created during tests.
 * It deletes records in an order that respects foreign key constraints.
 * It performs operations on a single client connection but does not use a
 * transaction, ensuring that a failure to delete from one table does not
 * prevent cleanup attempts on others.
 */
export const cleanupDb = async (options: CleanupOptions) => {
  const pool = getPool();
  const client = await pool.connect();

  try {
    // Order of deletion matters to avoid foreign key violations.
    // Children entities first, then parents.

    if (options.suggestedCorrectionIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.suggested_corrections WHERE suggested_correction_id = ANY($1::int[])', [options.suggestedCorrectionIds]);
      logger.debug(`Cleaned up ${options.suggestedCorrectionIds.length} suggested correction(s).`);
    }

    if (options.budgetIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.budgets WHERE budget_id = ANY($1::int[])', [options.budgetIds]);
      logger.debug(`Cleaned up ${options.budgetIds.length} budget(s).`);
    }

    if (options.recipeCommentIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.recipe_comments WHERE recipe_comment_id = ANY($1::int[])', [options.recipeCommentIds]);
      logger.debug(`Cleaned up ${options.recipeCommentIds.length} recipe comment(s).`);
    }

    if (options.recipeIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.recipes WHERE recipe_id = ANY($1::int[])', [options.recipeIds]);
      logger.debug(`Cleaned up ${options.recipeIds.length} recipe(s).`);
    }

    if (options.flyerIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.flyers WHERE flyer_id = ANY($1::int[])', [options.flyerIds]);
      logger.debug(`Cleaned up ${options.flyerIds.length} flyer(s).`);
    }

    if (options.storeIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.stores WHERE store_id = ANY($1::int[])', [options.storeIds]);
      logger.debug(`Cleaned up ${options.storeIds.length} store(s).`);
    }

    if (options.masterItemIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.master_grocery_items WHERE master_grocery_item_id = ANY($1::int[])', [options.masterItemIds]);
      logger.debug(`Cleaned up ${options.masterItemIds.length} master grocery item(s).`);
    }

    if (options.shoppingListIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.shopping_lists WHERE shopping_list_id = ANY($1::int[])', [options.shoppingListIds]);
      logger.debug(`Cleaned up ${options.shoppingListIds.length} shopping list(s).`);
    }

    if (options.userIds?.filter(Boolean).length) {
      await client.query('DELETE FROM public.users WHERE user_id = ANY($1::uuid[])', [options.userIds]);
      logger.debug(`Cleaned up ${options.userIds.length} user(s).`);
    }
  } catch (error) {
    logger.error({ error, options }, 'A database cleanup operation failed.');
  } finally {
    client.release();
  }
};