import { Injectable, Logger } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource, In } from 'typeorm';
import { createReadStream } from 'fs';
import * as Papa from 'papaparse';
import { BreachedRecordIdentifier } from '../have-i-been-pawned/entities/identifier.entity';
import { BreachRecord } from '../have-i-been-pawned/entities/breach-data.entity';

@Injectable()
export class CsvPopulateService {
  private readonly logger = new Logger(CsvPopulateService.name);
  private readonly CHUNK_SIZE = 1000;

  constructor(
    @InjectRepository(BreachedRecordIdentifier)
    private identifierRepository: Repository<BreachedRecordIdentifier>,
    @InjectRepository(BreachRecord)
    private breachRepository: Repository<BreachRecord>,
    private dataSource: DataSource,
  ) {}

  async importCsvData(
    filePaths: string[],
    batchSize: number = 1000,
    defaultSource: string = 'CSV Import',
  ): Promise<void> {
    if (!this.dataSource.isInitialized) {
      await this.dataSource.initialize();
    }

    for (const filePath of filePaths) {
      await this.processCsvFile(filePath, batchSize, defaultSource);
    }
  }

  private async processCsvFile(
    filePath: string,
    batchSize: number,
    source: string,
  ): Promise<void> {
    return new Promise(async (resolve, reject) => {
      const records = [];
      let rowCount = 0;
      console.time("Started processing CSV file");
      try {
        const fileStream = createReadStream(filePath);


        Papa.parse(fileStream, {
          header: true,
          skipEmptyLines: true,
          delimiter: ',',
          transformHeader: (h) => h.trim().toLowerCase(),
          dynamicTyping: true,

          step: async (result, parser) => {
            try {
              rowCount++;
              const data = this.extractDataFromRow(result.data, source);


              if (data.emailData || data.phoneData) {
                records.push(data);
              }

              if (records.length >= batchSize) {
                parser.pause();
                await this.processBatch([...records]);
                records.length = 0;
                parser.resume();
              }
            } catch (error) {
              reject(error);
            }
          },

          complete: async () => {
            try {
              if (records.length > 0) {
                await this.processBatch(records);
              }
              console.log(`Processed ${rowCount} rows from ${filePath}`);
              console.timeEnd("Ended processing CSV file");
              resolve();
            } catch (error) {
              reject(error);
            }
          },

          error: (error) => reject(error),
        });
      } catch (error) {
        reject(error);
      }
    });
  }

  private async processBatch(records: any[]): Promise<void> {
    console.time("Started processing batch");
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();


    try {
      console.log(`Processing batch of ${records.length} records`);
      await queryRunner.startTransaction();

      // Group identifiers using Map for O(1) lookups
      const identifierMap = new Map<
        string,
        {
          type: 'email' | 'phone';
          value: string;
          breaches: Array<{ source: string; year: number | null; metadata: any }>;
        }
      >();

      for (const record of records) {
        if (record.emailData) {
          const key = `email:${record.emailData.value.toLowerCase()}`;
          this.updateIdentifierMap(identifierMap, key, 'email', record.emailData);
        }

        if (record.phoneData) {
          const key = `phone:${record.phoneData.value}`;
          this.updateIdentifierMap(identifierMap, key, 'phone', record.phoneData);
        }
      }

      // Process identifiers in optimized way
      const identifiers = Array.from(identifierMap.values());
      const [existingEmails, existingPhones] = await Promise.all([
        this.getExistingIdentifiers('email', identifiers),
        this.getExistingIdentifiers('phone', identifiers),
      ]);

      const existingIdentifiers = [...existingEmails, ...existingPhones];
      const newIdentifiers = this.createNewIdentifiers(identifiers, existingIdentifiers);

      // Save new identifiers in chunks
      const savedIdentifiers = await this.chunkedSave(
        this.identifierRepository,
        newIdentifiers,
      );
      const allIdentifiers = [...existingIdentifiers, ...savedIdentifiers];

      // Process breach records with optimized lookups
      const breachRecordsToSave = await this.processBreachRecords(
        allIdentifiers,
        identifierMap,
      );

      // Save breach records in chunks
      await this.chunkedSave(this.breachRepository, breachRecordsToSave);

      await queryRunner.commitTransaction();
      console.log(`Processed batch of ${records.length} records`);
      console.timeEnd("Ended processing batch");
    } catch (error) {
      await queryRunner.rollbackTransaction();
      console.error(`Batch processing failed: ${error.message}`, error.stack);
      throw error;
    } finally {
      console.log(`Releasing query runner`);
      await queryRunner.release();
    }
  }

  private updateIdentifierMap(
    map: Map<string, any>,
    key: string,
    type: 'email' | 'phone',
    data: any,
  ) {
    if (!map.has(key)) {
      map.set(key, {
        type,
        value: type === 'email' ? data.value.toLowerCase() : data.value,
        breaches: [],
      });
    }
    map.get(key).breaches.push(data);
  }

  private async getExistingIdentifiers(
    type: 'email' | 'phone',
    identifiers: any[],
  ): Promise<BreachedRecordIdentifier[]> {
    const filtered = identifiers.filter((i) => i.type === type);
    if (filtered.length === 0) return [];

    const values = filtered.map((i) => i.value);
    const chunks = this.chunkArray(values, this.CHUNK_SIZE);
    const results: BreachedRecordIdentifier[] = [];

    for (const chunk of chunks) {
      results.push(
        ...(await this.identifierRepository.find({
          where: { type, value: In(chunk) },
        })),
      );
    }

    return results;
  }

  private createNewIdentifiers(
    identifiers: any[],
    existing: BreachedRecordIdentifier[],
  ): BreachedRecordIdentifier[] {
    return identifiers
      .filter(
        (id) =>
          !existing.some((e) => e.type === id.type && e.value === id.value),
      )
      .map((id) => {
        const newId = new BreachedRecordIdentifier();
        newId.type = id.type;
        newId.value = id.value;
        return newId;
      });
  }

  private async processBreachRecords(
    identifiers: BreachedRecordIdentifier[],
    identifierMap: Map<string, any>,
  ): Promise<BreachRecord[]> {
    const existingBreachMap = await this.getExistingBreachMap(identifiers);
    const breachRecordsToSave: BreachRecord[] = [];

    for (const identifier of identifiers) {
      const key = `${identifier.type}:${identifier.value}`;
      const breachesData = identifierMap.get(key)?.breaches || [];

      if (breachesData.length === 0) continue;

      const { sources, years, metadata } = this.processBreachData(breachesData);
      const compositeKey = `${identifier.id}_${JSON.stringify(metadata)}`;

      const breachRecord = existingBreachMap.has(compositeKey)
        ? this.updateExistingBreach(
            existingBreachMap.get(compositeKey),
            sources,
            years,
          )
        : this.createNewBreach(identifier, sources, years, metadata);

      breachRecordsToSave.push(breachRecord);
    }

    return breachRecordsToSave;
  }

  private processBreachData(breachesData: any[]) {
    return {
      sources: [...new Set(breachesData.map((b) => b.source))],
      years: [
        ...new Set(breachesData.filter((b) => b.year).map((b) => b.year)),
      ],
      metadata: breachesData[breachesData.length - 1].metadata,
    };
  }

  private async getExistingBreachMap(
    identifiers: BreachedRecordIdentifier[],
  ): Promise<Map<string, BreachRecord>> {
    const identifierIds = identifiers.map((i) => i.id);
    const chunks = this.chunkArray(identifierIds, this.CHUNK_SIZE);
    const existingBreachRecords: BreachRecord[] = [];

    for (const chunk of chunks) {
      existingBreachRecords.push(
        ...(await this.breachRepository.find({
          where: { identifier: In(chunk) },
          relations: ['identifier'],
        })),
      );
    }

    return new Map(
      existingBreachRecords.map((br) => [
        `${br.identifier.id}_${JSON.stringify(br.metadata)}`,
        br,
      ]),
    );
  }

  private updateExistingBreach(
    existing: BreachRecord,
    sources: string[],
    years: number[],
  ): BreachRecord {
    existing.source = [...new Set([...existing.source, ...sources])];
    existing.breachedYear = [...new Set([...existing.breachedYear, ...years])];
    return existing;
  }

  private createNewBreach(
    identifier: BreachedRecordIdentifier,
    sources: string[],
    years: number[],
    metadata: any,
  ): BreachRecord {
    const breachRecord = new BreachRecord();
    breachRecord.identifier = identifier;
    breachRecord.source = sources;
    breachRecord.breachedYear = years;
    breachRecord.metadata = metadata;
    return breachRecord;
  }

  private async chunkedSave<T>(repository: Repository<T>, items: T[]): Promise<T[]> {
    const saved: T[] = [];
    for (let i = 0; i < items.length; i += this.CHUNK_SIZE) {
      saved.push(...(await repository.save(items.slice(i, i + this.CHUNK_SIZE))));
    }
    return saved;
  }

  private chunkArray<T>(array: T[], size: number): T[][] {
    return Array.from({ length: Math.ceil(array.length / size) }, (_, i) =>
      array.slice(i * size, i * size + size),
    );
  }

  /**
   * Extract email, phone, and additional metadata from a CSV row
   */
  private extractDataFromRow(
    row: any,
    source: string,
  ): {
    emailData?: { value: string; year: number | null; source: string; metadata: Record<string, any> };
    phoneData?: { value: string; year: number | null; source: string; metadata: Record<string, any> };
  } {
    const result: any = {};

    // Skip if row is empty or not an object
    if (!row || typeof row !== 'object') {
      return result;
    }

    const breachedYear = 2025;

    // Find email in the row
    const emailValue = this.findValueByPossibleKeys(row, [
      'email',
      'Email',
      'EMAIL',
      'email_address',
      'emailAddress',
      'mail',
      'user_email',
      'userEmail',
      'user_mail',
      'userMail',
      'e-mail',
      'e_mail',
    ]);

    // Find phone in the row
    const phoneValue = this.findValueByPossibleKeys(row, [
      'phone',
      'Phone',
      'PHONE',
      'phoneNumber',
      'phone_number',
      'mobile',
      'cell',
      'telephone',
      'tel',
      'contact',
      'mobilePhone',
      'mobile_phone',
      'cellphone',
      'cell_phone',
      'mobile_code_type', // For the specific file format observed
    ]);

    // Create a comprehensive metadata object with all fields from the row
    const metadata: Record<string, any> = {};

    // Add all fields from the row to metadata (except email and phone)
    Object.keys(row).forEach(key => {
      if (row[key] !== null && row[key] !== undefined) {
        // Don't include the actual email/phone in metadata to avoid duplication
        const keyLower = key.toLowerCase();
        if (!['email', 'phone'].includes(keyLower)) {
          metadata[key] = row[key];
        }
      }
    });

    // For special handling of phone numbers with country code
    if (row.mobile_phone && row.mobile_code_type) {
      metadata.formatted_phone = `${row.mobile_code_type}${row.mobile_phone}`;
    }

    // Handle specific fields that might need special processing
    if (row.date_of_birth_ad && row.date_of_birth_bs) {
      metadata.date_of_birth = {
        ad: row.date_of_birth_ad,
        bs: row.date_of_birth_bs,
      };
    }

    if (emailValue && this.isValidEmail(emailValue)) {
      result.emailData = {
        value: emailValue.toLowerCase().trim(),
        year: breachedYear,
        source: 'Kaju Breach Forums Leak-2025-May 8GB Demo Data - Nepal Police',
        metadata: { ...metadata },
      };
    }

    if (phoneValue) {
      // Clean the phone number (remove non-numeric characters if needed)
      const cleanedPhone = this.cleanPhoneNumber(phoneValue);

      result.phoneData = {
        value: cleanedPhone,
        year: breachedYear,
        source: 'Kaju Breach Forums Leak-2025-May 8GB Demo Data - Nepal Police',
        metadata: { ...metadata },
      };
    }

    return result;
  }

  /**
   * Clean phone number by removing common prefixes and non-numeric characters
   */
  private cleanPhoneNumber(phone: string): string {
    if (!phone) return phone;
    if (phone.startsWith('-')) {
      return phone.substring(1).trim();
    }

    return phone.trim();
  }

  /**
   * Find a value in the row by checking multiple possible keys
   */
  private findValueByPossibleKeys(row: any, possibleKeys: string[]): string | null {
    for (const key of possibleKeys) {
      if (row[key] && typeof row[key] === 'string') {
        return row[key].trim();
      } else if (row[key] !== undefined && row[key] !== null) {
        // Handle non-string values by converting to string
        return String(row[key]).trim();
      }
    }

    // Try to find column by case-insensitive match
    const rowKeys = Object.keys(row);
    for (const possibleKey of possibleKeys) {
      const lowercasePossibleKey = possibleKey.toLowerCase();
      for (const rowKey of rowKeys) {
        if (rowKey.toLowerCase() === lowercasePossibleKey && row[rowKey] !== undefined && row[rowKey] !== null) {
          if (typeof row[rowKey] === 'string') {
            return row[rowKey].trim();
          } else {
            // Handle non-string values
            return String(row[rowKey]).trim();
          }
        }
      }
    }

    return null;
  }

  /**
   * Basic email validation
   */
  private isValidEmail(email: string): boolean {
    if (!email || typeof email !== 'string') return false;

    // More comprehensive email regex
    const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;
    return emailRegex.test(email.trim());
  }
}