import mongoose from 'mongoose';
import * as fs from 'fs';
import * as path from 'path';
import { Contract } from '../models/Contract';
import { Project } from '../models/Project';
import { Company } from '../models/Company';
import { CustomerGroup } from '../models/CustomerGroup';
import { connectDatabase, disconnectDatabase } from '../config/database';

// Helper function to parse date from DD/MM/YYYY format
function parseDate(dateStr: string | undefined): Date | null {
  if (!dateStr || dateStr.trim() === '') return null;
  
  // Handle various date formats
  const cleaned = dateStr.trim();
  const parts = cleaned.split('/');
  
  if (parts.length === 3) {
    const day = parseInt(parts[0], 10);
    const month = parseInt(parts[1], 10) - 1; // Month is 0-indexed
    const year = parseInt(parts[2], 10);
    
    if (!isNaN(day) && !isNaN(month) && !isNaN(year)) {
      return new Date(year, month, day);
    }
  }
  
  return null;
}

// Helper function to parse number from string (handles commas and spaces)
function parseNumber(numStr: string | undefined): number | undefined {
  if (!numStr || numStr.trim() === '') return undefined;
  const cleaned = numStr.replace(/[,\s]/g, '');
  const num = parseFloat(cleaned);
  return isNaN(num) ? undefined : num;
}

// Helper function to determine contract status based on dates
function determineStatus(startDate: Date | null, endDate: Date | null): 'active' | 'expired' | 'Expiring' | 'draft' {
  if (!endDate) return 'draft';
  
  const now = new Date();
  const daysUntilExpiry = Math.floor((endDate.getTime() - now.getTime()) / (1000 * 60 * 60 * 24));
  
  if (daysUntilExpiry < 0) return 'expired';
  if (daysUntilExpiry <= 90) return 'Expiring';
  if (startDate && startDate > now) return 'draft';
  return 'active';
}

// Helper function to extract customer group from data
function extractCustomerGroup(row: any[]): string {
  // Check multiple possible columns for customer group
  // Column 10 might have group info, also check project type column 6
  const groupStr1 = (row[10] || '').trim();
  const projectType = (row[6] || '').trim();
  const customerCode = (row[3] || '').trim();
  
  // Combine all sources
  const combined = `${groupStr1} ${projectType} ${customerCode}`.toUpperCase();
  
  // Map common groups
  if (combined.includes('TTE')) return 'TTE';
  if (combined.includes('ECO')) return 'ECO';
  if (combined.includes('ASSETCO')) return 'ASSETCO';
  if (combined.includes('EPC')) return 'EPC';
  if (combined.includes('OM') || projectType === 'OM') return 'OM';
  
  return 'OTHER';
}

// Helper function to determine contract type
function determineContractType(row: any[]): 'O&M' | 'EPC' {
  const projectType = (row[6] || '').trim(); // Column 6: "Là dự án"
  const groupStr = (row[10] || '').trim(); // Column 10: "Nhóm đối tượng"
  
  if (groupStr.includes('EPC') || projectType === 'EPC') return 'EPC';
  return 'O&M';
}

// Helper function to check if contract is free (EPC contracts are usually free)
function isFreeContract(row: any[]): boolean {
  const groupStr = (row[10] || '').trim();
  return groupStr.includes('EPC') || determineContractType(row) === 'EPC';
}

// Helper function to extract revenue plan from quarterly columns
function extractRevenuePlan(row: any[]): Array<{ year: number; q1: number; q2: number; q3: number; q4: number }> | undefined {
  const revenuePlan: Array<{ year: number; q1: number; q2: number; q3: number; q4: number }> = [];
  
  // Columns 16-19: 2024 Q1-Q4
  // Columns 20-23: 2025 Q1-Q4
  // Columns 24-27: 2026 Q1-Q4
  // Columns 28-31: 2027 Q1-Q4
  // Columns 32-35: 2028 Q1-Q4
  
  const yearOffsets = [
    { year: 2024, startCol: 16 },
    { year: 2025, startCol: 20 },
    { year: 2026, startCol: 24 },
    { year: 2027, startCol: 28 },
    { year: 2028, startCol: 32 },
  ];
  
  for (const { year, startCol } of yearOffsets) {
    const q1 = parseNumber(row[startCol]);
    const q2 = parseNumber(row[startCol + 1]);
    const q3 = parseNumber(row[startCol + 2]);
    const q4 = parseNumber(row[startCol + 3]);
    
    if (q1 !== undefined || q2 !== undefined || q3 !== undefined || q4 !== undefined) {
      revenuePlan.push({
        year,
        q1: q1 || 0,
        q2: q2 || 0,
        q3: q3 || 0,
        q4: q4 || 0,
      });
    }
  }
  
  return revenuePlan.length > 0 ? revenuePlan : undefined;
}

// Parse CSV file (handles quoted fields and multi-line values)
function parseCSV(filePath: string): any[][] {
  const content = fs.readFileSync(filePath, 'utf-8');
  const rows: any[][] = [];
  
  let currentRow: string[] = [];
  let current = '';
  let inQuotes = false;
  
  for (let i = 0; i < content.length; i++) {
    const char = content[i];
    const nextChar = i < content.length - 1 ? content[i + 1] : '';
    
    if (char === '"') {
      if (inQuotes && nextChar === '"') {
        // Escaped quote
        current += '"';
        i++; // Skip next quote
      } else {
        // Toggle quote state
        inQuotes = !inQuotes;
      }
    } else if (char === ',' && !inQuotes) {
      // Field separator
      currentRow.push(current.trim());
      current = '';
    } else if ((char === '\n' || char === '\r') && !inQuotes) {
      // End of row (but skip if we're in quotes)
      if (currentRow.length > 0 || current.trim() !== '') {
        currentRow.push(current.trim());
        rows.push(currentRow);
        currentRow = [];
        current = '';
      }
      // Skip \r\n combination
      if (char === '\r' && nextChar === '\n') {
        i++;
      }
    } else {
      current += char;
    }
  }
  
  // Push last row if any
  if (currentRow.length > 0 || current.trim() !== '') {
    currentRow.push(current.trim());
    if (currentRow.some(field => field.trim() !== '')) {
      rows.push(currentRow);
    }
  }
  
  return rows;
}

async function seedContracts() {
  try {
    await connectDatabase();
    console.log('✅ Connected to database');

    // Get or create default company
    let company = await Company.findOne({});
    if (!company) {
      company = new Company({
        name: 'Vu Phong Energy',
        code: 'VPEG',
        status: 'active',
      });
      await company.save();
      console.log('✅ Created default company');
    }

    // Ensure customer groups exist
    const customerGroups = [
      { name: 'OM', code: 'OM', description: 'Nhóm đối tượng O&M' },
      { name: 'TTE', code: 'TTE', description: 'Nhóm đối tượng TTE' },
      { name: 'Eco', code: 'ECO', description: 'Nhóm đối tượng Eco' },
      { name: 'Assetco', code: 'ASSETCO', description: 'Nhóm đối tượng Assetco' },
      { name: 'EPC', code: 'EPC', description: 'Nhóm đối tượng EPC (Miễn phí)' },
      { name: 'Other', code: 'OTHER', description: 'Nhóm đối tượng khác' },
    ];

    for (const groupData of customerGroups) {
      const existing = await CustomerGroup.findOne({ code: groupData.code });
      if (!existing) {
        const group = new CustomerGroup({
          ...groupData,
          isActive: true,
        });
        await group.save();
        console.log(`✅ Created customer group: ${groupData.name}`);
      }
    }

    // Read and parse CSV
    const csvPath = path.join(__dirname, '../../../HopDongOM-HN.csv');
    console.log(`📖 Reading CSV file: ${csvPath}`);
    
    if (!fs.existsSync(csvPath)) {
      throw new Error(`CSV file not found: ${csvPath}`);
    }
    
    const rows = parseCSV(csvPath);
    console.log(`📊 Found ${rows.length} rows in CSV`);

    // Skip header rows (first 3 rows are headers)
    const dataRows = rows.slice(3);
    
    let imported = 0;
    let updated = 0;
    let skipped = 0;

    for (let i = 0; i < dataRows.length; i++) {
      const row = dataRows[i];
      
      // Skip empty rows
      if (!row || row.length < 3 || !row[2] || row[2].trim() === '') {
        skipped++;
        continue;
      }

      try {
        // Extract data from CSV row
        const contractNumber = (row[2] || '').trim(); // Column 2: Số hợp đồng
        if (!contractNumber) {
          skipped++;
          continue;
        }

        const signDateStr = (row[1] || '').trim(); // Column 1: Ngày ký
        const endDateStr = (row[8] || '').trim(); // Column 8: Hạn hợp đồng
        
        const signDate = parseDate(signDateStr);
        const endDate = parseDate(endDateStr);
        
        // Use sign date as start date, or current date if not available
        const startDate = signDate || new Date();
        
        if (!endDate) {
          console.log(`⚠️  Skipping contract ${contractNumber}: No end date`);
          skipped++;
          continue;
        }

        const customerCode = (row[3] || '').trim(); // Column 3: Mã khách hàng
        const customerName = (row[4] || '').trim(); // Column 4: Tên khách hàng
        const capacity = parseNumber(row[5]); // Column 5: Công suất
        const siteAddress = (row[7] || '').trim(); // Column 7: Địa chỉ thi công
        const projectName = (row[9] || '').trim(); // Column 9: Trích yếu/ tên dự án
        const paymentCycleStr = (row[11] || '').trim(); // Column 11: Payment cycle (30 ngày, 60 ngày)
        
        const customerGroup = extractCustomerGroup(row);
        const contractType = determineContractType(row);
        const isFree = isFreeContract(row);
        
        const serviceTotal = parseNumber(row[13]); // Column 13: Tổng lần
        const servicePerformed = parseNumber(row[14]); // Column 14: Đã thực hiện
        
        // Service frequency might be in column 11 (payment cycle) or column 12
        let serviceFrequency = (row[12] || '').trim();
        if (!serviceFrequency && paymentCycleStr && !paymentCycleStr.includes('ngày')) {
          serviceFrequency = paymentCycleStr;
        }
        
        // Notes and debt are in the last columns - need to find them
        // They might be at different positions depending on how many quarterly columns are filled
        let notes = '';
        let debtAmount: number | undefined = undefined;
        
        // Try to find notes and debt from the end of the row
        // Notes column header says "Lý do/ ghi chú" and debt says "Nợ quá hạn"
        // They should be near the end, after all quarterly columns
        for (let j = row.length - 1; j >= 0; j--) {
          const cell = (row[j] || '').trim();
          if (cell && !notes && !cell.match(/^\d+([.,]\d+)?$/)) {
            // If it's not a pure number, it might be notes
            if (cell.length > 10) {
              notes = cell;
            }
          }
          if (cell && debtAmount === undefined) {
            const num = parseNumber(cell);
            if (num !== undefined && num > 0) {
              debtAmount = num;
            }
          }
        }
        
        // If notes not found, try column around 36-37 (after quarterly columns)
        if (!notes && row.length > 35) {
          notes = (row[35] || row[36] || '').trim();
        }
        if (debtAmount === undefined && row.length > 36) {
          debtAmount = parseNumber(row[37]);
        }
        
        const revenuePlan = extractRevenuePlan(row);
        
        // Determine status
        const status = determineStatus(startDate, endDate);
        
        // Determine payment cycle
        let paymentCycle: '30_days' | '60_days' | 'custom' | undefined = undefined;
        if (paymentCycleStr.includes('30')) paymentCycle = '30_days';
        else if (paymentCycleStr.includes('60')) paymentCycle = '60_days';
        else if (paymentCycleStr) paymentCycle = 'custom';

        // Get or create project
        // Use project name or customer name as project name
        const projectNameToUse = projectName || customerName || `Project ${contractNumber}`;
        let project = await Project.findOne({ 
          $or: [
            { name: projectNameToUse },
            { code: contractNumber }
          ]
        });

        if (!project) {
          project = new Project({
            companyId: company._id,
            name: projectNameToUse,
            code: contractNumber.substring(0, 20), // Use contract number as code (truncated)
            location: {
              address: siteAddress || 'Chưa xác định',
              coordinates: { lat: 0, lng: 0 }, // Default coordinates
            },
            capacityMWp: (capacity || 0) / 1000, // Convert kWp to MWp
            commissioningDate: startDate,
            status: 'operational',
          });
          await project.save();
          console.log(`✅ Created project: ${projectNameToUse}`);
        }

        // Check if contract already exists
        const existingContract = await Contract.findOne({ contractNumber });
        
        const contractData: any = {
          projectId: project._id,
          contractType,
          contractNumber,
          partyA: customerName,
          partyB: 'Vu Phong Energy',
          customerCode: customerCode || undefined,
          customerGroup,
          siteAddress: siteAddress || undefined,
          capacityKWp: capacity,
          startDate,
          endDate,
          status,
          isFree,
          paymentCycle,
          serviceTotal,
          servicePerformed,
          serviceFrequency: serviceFrequency || undefined,
          notes: notes || undefined,
          debtAmount: debtAmount || undefined,
          currency: 'VND',
        };

        if (revenuePlan && revenuePlan.length > 0) {
          contractData.revenuePlan = revenuePlan;
        }

        if (existingContract) {
          await Contract.findOneAndUpdate(
            { _id: existingContract._id },
            { $set: contractData },
            { new: true }
          );
          updated++;
          console.log(`🔄 Updated contract: ${contractNumber}`);
        } else {
          const contract = new Contract(contractData);
          await contract.save();
          imported++;
          console.log(`✅ Imported contract: ${contractNumber} (${customerName})`);
        }
      } catch (error: any) {
        console.error(`❌ Error processing row ${i + 4}:`, error.message);
        console.error(`   Row data: ${row.slice(0, 5).join(', ')}...`);
        skipped++;
      }
    }

    console.log('\n📊 Import Summary:');
    console.log(`   ✅ Imported: ${imported} contracts`);
    console.log(`   🔄 Updated: ${updated} contracts`);
    console.log(`   ⏭️  Skipped: ${skipped} rows`);
    console.log(`   📝 Total processed: ${imported + updated + skipped} rows`);

    console.log('\n✅ Contract seeding completed successfully!');
  } catch (error: any) {
    console.error('❌ Error seeding contracts:', error);
    process.exit(1);
  } finally {
    await disconnectDatabase();
    process.exit(0);
  }
}

// Run the seed function
seedContracts();