// src/tests/utils/cleanup.ts
import { getPool } from '../../services/db/connection.db';
import { logger } from '../../services/logger.server';
import fs from 'node:fs/promises';
import path from 'path';

export interface TestResourceIds {
  userIds?: string[];
  flyerIds?: number[];
  storeIds?: number[];
  recipeIds?: number[];
  masterItemIds?: number[];
  budgetIds?: number[];
}

/**
 * A robust cleanup utility for integration tests.
 * It deletes entities in the correct order to avoid foreign key violations.
 * It's designed to be called in an `afterAll` hook.
 *
 * @param ids An object containing arrays of IDs for each resource type to clean up.
 */
export const cleanupDb = async (ids: TestResourceIds) => {
  const pool = getPool();
  logger.info('[Test Cleanup] Starting database resource cleanup...');

  const {
    userIds = [],
    flyerIds = [],
    storeIds = [],
    recipeIds = [],
    masterItemIds = [],
    budgetIds = [],
  } = ids;

  try {
    // --- Stage 1: Delete most dependent records ---
    // These records depend on users, recipes, flyers, etc.
    if (userIds.length > 0) {
      await pool.query('DELETE FROM public.recipe_comments WHERE user_id = ANY($1::uuid[])', [userIds]);
      await pool.query('DELETE FROM public.suggested_corrections WHERE user_id = ANY($1::uuid[])', [userIds]);
      await pool.query('DELETE FROM public.shopping_lists WHERE user_id = ANY($1::uuid[])', [userIds]); // Assumes shopping_list_items cascades
      await pool.query('DELETE FROM public.user_watched_items WHERE user_id = ANY($1::uuid[])', [userIds]);
      await pool.query('DELETE FROM public.user_achievements WHERE user_id = ANY($1::uuid[])', [userIds]);
      await pool.query('DELETE FROM public.activity_log WHERE user_id = ANY($1::uuid[])', [userIds]);
    }

    // --- Stage 2: Delete parent records that other things depend on ---
    if (recipeIds.length > 0) {
      await pool.query('DELETE FROM public.recipes WHERE recipe_id = ANY($1::int[])', [recipeIds]);
    }

    // Flyers might be created by users, but we clean them up separately.
    // flyer_items should cascade from this.
    if (flyerIds.length > 0) {
      await pool.query('DELETE FROM public.flyers WHERE flyer_id = ANY($1::bigint[])', [flyerIds]);
    }

    // Stores are parents of flyers, so they come after.
    if (storeIds.length > 0) {
      await pool.query('DELETE FROM public.stores WHERE store_id = ANY($1::int[])', [storeIds]);
    }

    // Master items are parents of flyer_items and watched_items.
    if (masterItemIds.length > 0) {
      await pool.query('DELETE FROM public.master_grocery_items WHERE master_grocery_item_id = ANY($1::int[])', [masterItemIds]);
    }

    // Budgets are parents of nothing, but depend on users.
    if (budgetIds.length > 0) {
      await pool.query('DELETE FROM public.budgets WHERE budget_id = ANY($1::int[])', [budgetIds]);
    }

    // --- Stage 3: Delete the root user records ---
    if (userIds.length > 0) {
      const { rowCount } = await pool.query('DELETE FROM public.users WHERE user_id = ANY($1::uuid[])', [userIds]);
      logger.info(`[Test Cleanup] Cleaned up ${rowCount} user(s).`);
    }

    logger.info('[Test Cleanup] Finished database resource cleanup successfully.');
  } catch (error) {
    logger.error({ error }, '[Test Cleanup] CRITICAL: An error occurred during database cleanup.');
    throw error; // Re-throw to fail the test suite
  }
};