wulf-pulse/lib/utils/db-helpers.ts

/**
 * Database Helper Functions
 * Additional utility functions for database operations
 */

import postgresClient from '../services/postgres-client';
import { EntityType } from '../types/sync';
import { getTableName } from './sync-helpers';

/**
 * Upsert a single record
 * @param entity Entity type
 * @param data Record data
 * @returns Upserted record
 */
export async function upsertRecord(
  entity: EntityType,
  data: Record<string, any>
): Promise<any> {
  const tableName = getTableName(entity);
  return await postgresClient.upsert(tableName, data);
}

/**
 * Bulk upsert records with batching
 * @param entity Entity type
 * @param records Array of records
 * @param batchSize Number of records per batch (default: 100)
 * @returns Total number of records upserted
 */
// Columns where a null incoming value should preserve the existing DB value rather than overwrite it
const PRESERVE_EXISTING_ON_NULL: Partial<Record<EntityType, string[]>> = {
  [EntityType.TICKETS]: ['assigned_resource_id', 'first_response_assigned_resource_id', 'first_response_initiating_resource_id'],
  [EntityType.TASKS]: ['assigned_resource_id'],
};

export async function bulkUpsertRecords(
  entity: EntityType,
  records: Record<string, any>[],
  batchSize: number = 100
): Promise<number> {
  if (records.length === 0) return 0;

  const tableName = getTableName(entity);
  const preserveOnNull = PRESERVE_EXISTING_ON_NULL[entity] ?? [];
  let totalUpserted = 0;

  // Process in batches
  for (let i = 0; i < records.length; i += batchSize) {
    const batch = records.slice(i, i + batchSize);
    const count = await postgresClient.bulkUpsert(tableName, batch, ['id'], preserveOnNull);
    totalUpserted += count;
  }

  return totalUpserted;
}

/**
 * Soft delete records not in the provided ID list
 * @param entity Entity type
 * @param activeIds Array of IDs that should remain active
 * @returns Number of records soft-deleted
 */
export async function softDeleteMissingRecords(
  entity: EntityType,
  activeIds: (number | string)[]
): Promise<number> {
  if (activeIds.length === 0) return 0;

  const tableName = getTableName(entity);
  
  const query = `
    UPDATE ${tableName}
    SET is_deleted = true, deleted_at = CURRENT_TIMESTAMP
    WHERE id NOT IN (${activeIds.map((_, i) => `$${i + 1}`).join(', ')})
      AND is_deleted = false
  `;

  const result = await postgresClient.query(query, activeIds);
  return result.rowCount || 0;
}

/**
 * Get last sync time for an entity
 * @param entity Entity type
 * @returns Last sync timestamp or null
 */
export async function getLastSyncTime(
  entity: EntityType
): Promise<Date | null> {
  const query = `
    SELECT completed_at
    FROM sync_history
    WHERE entity_type = $1
      AND status = 'completed'
    ORDER BY completed_at DESC
    LIMIT 1
  `;

  const result = await postgresClient.query<{ completed_at: Date }>(
    query,
    [entity]
  );

  return result.rows[0]?.completed_at || null;
}

/**
 * Get record count for an entity
 * @param entity Entity type
 * @param includeDeleted Include soft-deleted records
 * @returns Record count
 */
export async function getRecordCount(
  entity: EntityType,
  includeDeleted: boolean = false
): Promise<number> {
  const tableName = getTableName(entity);
  return await postgresClient.count(tableName, {}, includeDeleted);
}

/**
 * Get records modified since a specific date
 * @param entity Entity type
 * @param since Date to filter from
 * @param limit Maximum number of records
 * @returns Array of records
 */
export async function getRecordsModifiedSince(
  entity: EntityType,
  since: Date,
  limit?: number
): Promise<any[]> {
  const tableName = getTableName(entity);
  
  const query = `
    SELECT *
    FROM ${tableName}
    WHERE synced_at >= $1
      AND is_deleted = false
    ORDER BY synced_at DESC
    ${limit ? `LIMIT ${limit}` : ''}
  `;

  const result = await postgresClient.query(query, [since]);
  return result.rows;
}

/**
 * Get all active IDs for an entity
 * @param entity Entity type
 * @returns Array of active record IDs
 */
export async function getActiveIds(entity: EntityType): Promise<number[]> {
  const tableName = getTableName(entity);
  
  const query = `
    SELECT id
    FROM ${tableName}
    WHERE is_deleted = false
  `;

  const result = await postgresClient.query<{ id: number }>(query);
  return result.rows.map((row: { id: number }) => row.id);
}

/**
 * Restore soft-deleted record
 * @param entity Entity type
 * @param id Record ID
 */
export async function restoreRecord(
  entity: EntityType,
  id: number | string
): Promise<void> {
  const tableName = getTableName(entity);
  
  const query = `
    UPDATE ${tableName}
    SET is_deleted = false, deleted_at = NULL
    WHERE id = $1
  `;

  await postgresClient.query(query, [id]);
}

/**
 * Hard delete soft-deleted records older than specified days
 * @param entity Entity type
 * @param daysOld Number of days
 * @returns Number of records deleted
 */
export async function purgeOldDeletedRecords(
  entity: EntityType,
  daysOld: number = 90
): Promise<number> {
  const tableName = getTableName(entity);
  
  const query = `
    DELETE FROM ${tableName}
    WHERE is_deleted = true
      AND deleted_at < NOW() - INTERVAL '${daysOld} days'
  `;

  const result = await postgresClient.query(query);
  return result.rowCount || 0;
}

/**
 * Get sync statistics for an entity
 * @param entity Entity type
 * @returns Sync statistics
 */
export async function getSyncStatistics(entity: EntityType): Promise<{
  totalRecords: number;
  activeRecords: number;
  deletedRecords: number;
  lastSyncTime: Date | null;
  lastSyncStatus: string | null;
}> {
  const tableName = getTableName(entity);
  
  // Get record counts
  const countQuery = `
    SELECT
      COUNT(*) as total,
      COUNT(*) FILTER (WHERE is_deleted = false) as active,
      COUNT(*) FILTER (WHERE is_deleted = true) as deleted
    FROM ${tableName}
  `;

  const countResult = await postgresClient.query<{
    total: string;
    active: string;
    deleted: string;
  }>(countQuery);

  // Get last sync info
  const syncQuery = `
    SELECT completed_at, status
    FROM sync_history
    WHERE entity_type = $1
    ORDER BY started_at DESC
    LIMIT 1
  `;

  const syncResult = await postgresClient.query<{
    completed_at: Date;
    status: string;
  }>(syncQuery, [entity]);

  return {
    totalRecords: parseInt(countResult.rows[0]?.total || '0'),
    activeRecords: parseInt(countResult.rows[0]?.active || '0'),
    deletedRecords: parseInt(countResult.rows[0]?.deleted || '0'),
    lastSyncTime: syncResult.rows[0]?.completed_at || null,
    lastSyncStatus: syncResult.rows[0]?.status || null,
  };
}

/**
 * Vacuum analyze table to optimize performance
 * @param entity Entity type
 */
export async function optimizeTable(entity: EntityType): Promise<void> {
  const tableName = getTableName(entity);
  await postgresClient.query(`VACUUM ANALYZE ${tableName}`);
}

/**
 * Check if record exists
 * @param entity Entity type
 * @param id Record ID
 * @returns True if record exists
 */
export async function recordExists(
  entity: EntityType,
  id: number | string
): Promise<boolean> {
  const tableName = getTableName(entity);
  
  const query = `
    SELECT EXISTS(SELECT 1 FROM ${tableName} WHERE id = $1) as exists
  `;

  const result = await postgresClient.query<{ exists: boolean }>(query, [id]);
  return result.rows[0]?.exists || false;
}

/**
 * Get records by IDs
 * @param entity Entity type
 * @param ids Array of record IDs
 * @returns Array of records
 */
export async function getRecordsByIds(
  entity: EntityType,
  ids: (number | string)[]
): Promise<any[]> {
  if (ids.length === 0) return [];

  const tableName = getTableName(entity);
  
  const query = `
    SELECT *
    FROM ${tableName}
    WHERE id = ANY($1::bigint[])
      AND is_deleted = false
  `;

  const result = await postgresClient.query(query, [ids]);
  return result.rows;
}

/**
 * Update sync timestamp for records
 * @param entity Entity type
 * @param ids Array of record IDs
 */
export async function updateSyncTimestamp(
  entity: EntityType,
  ids: (number | string)[]
): Promise<void> {
  if (ids.length === 0) return;

  const tableName = getTableName(entity);
  
  const query = `
    UPDATE ${tableName}
    SET synced_at = CURRENT_TIMESTAMP
    WHERE id = ANY($1::bigint[])
  `;

  await postgresClient.query(query, [ids]);
}