import type { IServiceContainer } from '@stock-bot/handlers';
import { getLogger } from '@stock-bot/logger';
import type { JobPayload } from '../../../types/job-payloads';

const logger = getLogger('enhanced-sync-clear-postgresql-data');

export async function clearPostgreSQLData(
  payload: JobPayload,
  container: IServiceContainer
): Promise<{
  exchangesCleared: number;
  symbolsCleared: number;
  mappingsCleared: number;
}> {
  logger.info('Clearing existing PostgreSQL data...');

  try {
    const postgresClient = container.postgres;

    // Start transaction for atomic operations
    await postgresClient.query('BEGIN');

    // Get counts before clearing
    const exchangeCountResult = await postgresClient.query(
      'SELECT COUNT(*) as count FROM exchanges'
    );
    const symbolCountResult = await postgresClient.query('SELECT COUNT(*) as count FROM symbols');
    const mappingCountResult = await postgresClient.query(
      'SELECT COUNT(*) as count FROM provider_mappings'
    );

    const exchangesCleared = parseInt(exchangeCountResult.rows[0].count);
    const symbolsCleared = parseInt(symbolCountResult.rows[0].count);
    const mappingsCleared = parseInt(mappingCountResult.rows[0].count);

    // Clear data in correct order (respect foreign keys)
    await postgresClient.query('DELETE FROM provider_mappings');
    await postgresClient.query('DELETE FROM symbols');
    await postgresClient.query('DELETE FROM exchanges');

    // Reset sync status
    await postgresClient.query(
      'UPDATE sync_status SET last_sync_at = NULL, last_sync_count = 0, sync_errors = NULL'
    );

    await postgresClient.query('COMMIT');

    logger.info('PostgreSQL data cleared successfully', {
      exchangesCleared,
      symbolsCleared,
      mappingsCleared,
    });

    return { exchangesCleared, symbolsCleared, mappingsCleared };
  } catch (error) {
    const postgresClient = container.postgres;
    await postgresClient.query('ROLLBACK');
    logger.error('Failed to clear PostgreSQL data', { error });
    throw error;
  }
}
