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

const logger = getLogger('enhanced-sync-all-exchanges');

export async function syncAllExchanges(
  payload: JobPayload,
  container: IServiceContainer
): Promise<SyncResult> {
  const clearFirst = payload.clearFirst || true;
  logger.info('Starting comprehensive exchange sync...', { clearFirst });

  const result: SyncResult = {
    processed: 0,
    created: 0,
    updated: 0,
    skipped: 0,
    errors: 0,
  };

  try {
    const postgresClient = container.postgres;

    // Clear existing data if requested
    if (clearFirst) {
      await clearPostgreSQLData(postgresClient);
    }

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

    // 1. Sync from EOD exchanges (comprehensive global data)
    const eodResult = await syncEODExchanges(container);
    mergeResults(result, eodResult);

    // 2. Sync from IB exchanges (detailed asset information)
    const ibResult = await syncIBExchanges(container);
    mergeResults(result, ibResult);

    // 3. Update sync status
    await updateSyncStatus('all', 'exchanges', result.processed, postgresClient);

    await postgresClient.query('COMMIT');

    logger.info('Comprehensive exchange sync completed', result);
    return result;
  } catch (error) {
    const postgresClient = container.postgres;
    await postgresClient.query('ROLLBACK');
    logger.error('Comprehensive exchange sync failed', { error });
    throw error;
  }
}

async function clearPostgreSQLData(postgresClient: any): Promise<void> {
  logger.info('Clearing existing PostgreSQL data...');

  // 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'
  );

  logger.info('PostgreSQL data cleared successfully');
}

async function syncEODExchanges(container: IServiceContainer): Promise<SyncResult> {
  const mongoClient = container.mongodb;
  const exchanges = await mongoClient.find('eodExchanges', { active: true });
  const result: SyncResult = { processed: 0, created: 0, updated: 0, skipped: 0, errors: 0 };

  for (const exchange of exchanges) {
    try {
      // Create provider exchange mapping for EOD
      await createProviderExchangeMapping(
        'eod', // provider
        exchange.Code,
        exchange.Name,
        exchange.CountryISO2,
        exchange.Currency,
        0.95, // very high confidence for EOD data
        container
      );

      result.processed++;
      result.created++; // Count as created mapping
    } catch (error) {
      logger.error('Failed to process EOD exchange', { error, exchange });
      result.errors++;
    }
  }

  return result;
}

async function syncIBExchanges(container: IServiceContainer): Promise<SyncResult> {
  const mongoClient = container.mongodb;
  const exchanges = await mongoClient.find('ibExchanges', {});
  const result: SyncResult = { processed: 0, created: 0, updated: 0, skipped: 0, errors: 0 };

  for (const exchange of exchanges) {
    try {
      // Create provider exchange mapping for IB
      await createProviderExchangeMapping(
        'ib', // provider
        exchange.exchange_id,
        exchange.name,
        exchange.country_code,
        'USD', // IB doesn't specify currency, default to USD
        0.85, // good confidence for IB data
        container
      );

      result.processed++;
      result.created++; // Count as created mapping
    } catch (error) {
      logger.error('Failed to process IB exchange', { error, exchange });
      result.errors++;
    }
  }

  return result;
}

async function createProviderExchangeMapping(
  provider: string,
  providerExchangeCode: string,
  providerExchangeName: string,
  countryCode: string | null,
  currency: string | null,
  confidence: number,
  container: IServiceContainer
): Promise<void> {
  if (!providerExchangeCode) {
    return;
  }

  const postgresClient = container.postgres;

  // Check if mapping already exists
  const existingMapping = await findProviderExchangeMapping(
    provider,
    providerExchangeCode,
    container
  );
  if (existingMapping) {
    // Don't override existing mappings to preserve manual work
    return;
  }

  // Find or create master exchange
  const masterExchange = await findOrCreateMasterExchange(
    providerExchangeCode,
    providerExchangeName,
    countryCode,
    currency,
    container
  );

  // Create the provider exchange mapping
  const query = `
    INSERT INTO provider_exchange_mappings 
    (provider, provider_exchange_code, provider_exchange_name, master_exchange_id, 
     country_code, currency, confidence, active, auto_mapped)
    VALUES ($1, $2, $3, $4, $5, $6, $7, false, true)
    ON CONFLICT (provider, provider_exchange_code) DO NOTHING
  `;

  await postgresClient.query(query, [
    provider,
    providerExchangeCode,
    providerExchangeName,
    masterExchange.id,
    countryCode,
    currency,
    confidence,
  ]);
}

async function findOrCreateMasterExchange(
  providerCode: string,
  providerName: string,
  countryCode: string | null,
  currency: string | null,
  container: IServiceContainer
): Promise<any> {
  const postgresClient = container.postgres;

  // First, try to find exact match
  let masterExchange = await findExchangeByCode(providerCode, container);

  if (masterExchange) {
    return masterExchange;
  }

  // Try to find by similar codes (basic mapping)
  const basicMapping = getBasicExchangeMapping(providerCode);
  if (basicMapping) {
    masterExchange = await findExchangeByCode(basicMapping, container);
    if (masterExchange) {
      return masterExchange;
    }
  }

  // Create new master exchange (inactive by default)
  const query = `
    INSERT INTO exchanges (code, name, country, currency, active)
    VALUES ($1, $2, $3, $4, false)
    ON CONFLICT (code) DO UPDATE SET
      name = COALESCE(EXCLUDED.name, exchanges.name),
      country = COALESCE(EXCLUDED.country, exchanges.country),
      currency = COALESCE(EXCLUDED.currency, exchanges.currency)
    RETURNING id, code, name, country, currency
  `;

  const result = await postgresClient.query(query, [
    providerCode,
    providerName || providerCode,
    countryCode || 'US',
    currency || 'USD',
  ]);

  return result.rows[0];
}

function getBasicExchangeMapping(providerCode: string): string | null {
  const mappings: Record<string, string> = {
    NYE: 'NYSE',
    NAS: 'NASDAQ',
    TO: 'TSX',
    LN: 'LSE',
    LON: 'LSE',
  };

  return mappings[providerCode.toUpperCase()] || null;
}

async function findProviderExchangeMapping(
  provider: string,
  providerExchangeCode: string,
  container: IServiceContainer
): Promise<any> {
  const postgresClient = container.postgres;
  const query =
    'SELECT * FROM provider_exchange_mappings WHERE provider = $1 AND provider_exchange_code = $2';
  const result = await postgresClient.query(query, [provider, providerExchangeCode]);
  return result.rows[0] || null;
}

async function findExchangeByCode(code: string, container: IServiceContainer): Promise<any> {
  const postgresClient = container.postgres;
  const query = 'SELECT * FROM exchanges WHERE code = $1';
  const result = await postgresClient.query(query, [code]);
  return result.rows[0] || null;
}

async function updateSyncStatus(
  provider: string,
  dataType: string,
  count: number,
  postgresClient: any
): Promise<void> {
  const query = `
    INSERT INTO sync_status (provider, data_type, last_sync_at, last_sync_count, sync_errors)
    VALUES ($1, $2, NOW(), $3, NULL)
    ON CONFLICT (provider, data_type)
    DO UPDATE SET 
      last_sync_at = NOW(),
      last_sync_count = EXCLUDED.last_sync_count,
      sync_errors = NULL,
      updated_at = NOW()
  `;

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

function mergeResults(target: SyncResult, source: SyncResult): void {
  target.processed += source.processed;
  target.created += source.created;
  target.updated += source.updated;
  target.skipped += source.skipped;
  target.errors += source.errors;
}
