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

const logger = getLogger('sync-qm-symbols');

export async function syncQMSymbols(
  payload: JobPayload,
  container: IServiceContainer
): Promise<{ processed: number; created: number; updated: number }> {
  logger.info('Starting QM symbols sync...');

  try {
    const mongoClient = container.mongodb;
    const postgresClient = container.postgres;

    // 1. Get all QM symbols from MongoDB
    const qmSymbols = await mongoClient.find('qmSymbols', {});
    logger.info(`Found ${qmSymbols.length} QM symbols to process`);

    let created = 0;
    let updated = 0;

    for (const symbol of qmSymbols) {
      try {
        // 2. Resolve exchange
        const exchangeId = await resolveExchange(
          symbol.exchangeCode || symbol.exchange,
          postgresClient
        );

        if (!exchangeId) {
          logger.warn('Unknown exchange, skipping symbol', {
            symbol: symbol.symbol,
            exchange: symbol.exchangeCode || symbol.exchange,
          });
          continue;
        }

        // 3. Check if symbol exists
        const existingSymbol = await findSymbol(symbol.symbol, exchangeId, postgresClient);

        if (existingSymbol) {
          // Update existing
          await updateSymbol(existingSymbol.id, symbol, postgresClient);
          await upsertProviderMapping(existingSymbol.id, 'qm', symbol, postgresClient);
          updated++;
        } else {
          // Create new
          const newSymbolId = await createSymbol(symbol, exchangeId, postgresClient);
          await upsertProviderMapping(newSymbolId, 'qm', symbol, postgresClient);
          created++;
        }
      } catch (error) {
        logger.error('Failed to process symbol', { error, symbol: symbol.symbol });
      }
    }

    // 4. Update sync status
    await updateSyncStatus('qm', 'symbols', qmSymbols.length, postgresClient);

    const result = { processed: qmSymbols.length, created, updated };
    logger.info('QM symbols sync completed', result);
    return result;
  } catch (error) {
    logger.error('QM symbols sync failed', { error });
    throw error;
  }
}

// Helper functions
async function resolveExchange(exchangeCode: string, postgresClient: any): Promise<string | null> {
  if (!exchangeCode) {
    return null;
  }

  // Simple mapping - expand this as needed
  const exchangeMap: Record<string, string> = {
    NASDAQ: 'NASDAQ',
    NYSE: 'NYSE',
    TSX: 'TSX',
    TSE: 'TSX', // TSE maps to TSX
    LSE: 'LSE',
    CME: 'CME',
  };

  const normalizedCode = exchangeMap[exchangeCode.toUpperCase()];
  if (!normalizedCode) {
    return null;
  }

  const query = 'SELECT id FROM exchanges WHERE code = $1';
  const result = await postgresClient.query(query, [normalizedCode]);
  return result.rows[0]?.id || null;
}

async function findSymbol(symbol: string, exchangeId: string, postgresClient: any): Promise<any> {
  const query = 'SELECT * FROM symbols WHERE symbol = $1 AND exchange_id = $2';
  const result = await postgresClient.query(query, [symbol, exchangeId]);
  return result.rows[0] || null;
}

async function createSymbol(
  qmSymbol: any,
  exchangeId: string,
  postgresClient: any
): Promise<string> {
  const query = `
    INSERT INTO symbols (symbol, exchange_id, company_name, country, currency)
    VALUES ($1, $2, $3, $4, $5)
    RETURNING id
  `;

  const result = await postgresClient.query(query, [
    qmSymbol.symbol,
    exchangeId,
    qmSymbol.companyName || qmSymbol.name,
    qmSymbol.countryCode || 'US',
    qmSymbol.currency || 'USD',
  ]);

  return result.rows[0].id;
}

async function updateSymbol(symbolId: string, qmSymbol: any, postgresClient: any): Promise<void> {
  const query = `
    UPDATE symbols 
    SET company_name = COALESCE($2, company_name),
        country = COALESCE($3, country),
        currency = COALESCE($4, currency),
        updated_at = NOW()
    WHERE id = $1
  `;

  await postgresClient.query(query, [
    symbolId,
    qmSymbol.companyName || qmSymbol.name,
    qmSymbol.countryCode,
    qmSymbol.currency,
  ]);
}

async function upsertProviderMapping(
  symbolId: string,
  provider: string,
  qmSymbol: any,
  postgresClient: any
): Promise<void> {
  const query = `
    INSERT INTO provider_mappings 
    (symbol_id, provider, provider_symbol, provider_exchange, last_seen)
    VALUES ($1, $2, $3, $4, NOW())
    ON CONFLICT (provider, provider_symbol)
    DO UPDATE SET 
      symbol_id = EXCLUDED.symbol_id,
      provider_exchange = EXCLUDED.provider_exchange,
      last_seen = NOW()
  `;

  await postgresClient.query(query, [
    symbolId,
    provider,
    qmSymbol.qmSearchCode || qmSymbol.symbol,
    qmSymbol.exchangeCode || qmSymbol.exchange,
  ]);
}

async function updateSyncStatus(
  provider: string,
  dataType: string,
  count: number,
  postgresClient: any
): Promise<void> {
  const query = `
    UPDATE sync_status 
    SET last_sync_at = NOW(),
        last_sync_count = $3,
        sync_errors = NULL,
        updated_at = NOW()
    WHERE provider = $1 AND data_type = $2
  `;

  await postgresClient.query(query, [provider, dataType, count]);
}
