import { DataSource } from 'typeorm';

export const fetchWorkflowDataByRegistrationId = async (
  dataSource: DataSource,
  sqlQuery: string,
  registrationId: number,
): Promise<Record<string, unknown>> => {
  const [workflowData] = await dataSource.query(sqlQuery, [registrationId]);
  return (workflowData ?? {}) as Record<string, unknown>;
};

/**
 * Batch fetch workflow data for multiple registrations using the same workflow query.
 * This optimizes the N+1 query problem by fetching all registrations in a single query.
 * 
 * @param dataSource - TypeORM DataSource
 * @param sqlQuery - Base SQL query with WHERE clause expecting registration ID
 * @param registrationIds - Array of registration IDs to fetch
 * @returns Map of registrationId -> workflow data
 */
export const fetchWorkflowDataForMultipleRegistrations = async (
  dataSource: DataSource,
  sqlQuery: string,
  registrationIds: number[],
): Promise<Map<number, Record<string, unknown>>> => {
  const resultMap = new Map<number, Record<string, unknown>>();
  
  if (registrationIds.length === 0) {
    return resultMap;
  }

  try {
    // Modify the query to use IN clause instead of single parameter
    // The query typically has: WHERE pr.id = $1 AND pr.deleted_at IS NULL
    // We need to change it to: WHERE pr.id = ANY($1::int[]) AND pr.deleted_at IS NULL
    const batchQuery = sqlQuery.replace(
      /WHERE\s+(\w+)\.id\s*=\s*\$1/i,
      'WHERE $1.id = ANY($1::int[])'
    );

    // Execute batch query with array of IDs
    const results = await dataSource.query(batchQuery, [registrationIds]);

    // Map results by registrationId (look for common field names)
    for (const row of results) {
      const regId = row.registrationId || row.registration_id || row.id;
      if (regId) {
        resultMap.set(Number(regId), row as Record<string, unknown>);
      }
    }

    // Fill in empty objects for registrations with no data
    for (const regId of registrationIds) {
      if (!resultMap.has(regId)) {
        resultMap.set(regId, {});
      }
    }

    return resultMap;
  } catch (error) {
    // If batch query fails, fall back to individual queries (safety net)
    console.error('Batch workflow query failed, falling back to individual queries:', error?.message || error);
    
    for (const regId of registrationIds) {
      try {
        const data = await fetchWorkflowDataByRegistrationId(dataSource, sqlQuery, regId);
        resultMap.set(regId, data);
      } catch (err) {
        // Set empty object on individual failure
        resultMap.set(regId, {});
      }
    }
    
    return resultMap;
  }
};