import { spawn } from 'child_process';
import path from 'path';
import { Pool } from 'pg';

interface CarrierResult {
  eta: string | null;
  success: boolean;
}

interface ScraperResult {
  container: string;
  timestamp: string;
  carriers: {
    [key: string]: CarrierResult;
  };
  error?: string;
}

/**
 * Call Python scraper subprocess
 * Returns ETA data for container from all carriers
 */
export async function callPythonScraper(container: string): Promise<ScraperResult> {
  return new Promise((resolve, reject) => {
    const pythonScript = path.resolve(process.cwd(), 'scripts', 'shipping-scraper-python.py');
    
    console.log(`[PythonScraper] 🚀 Launching Python subprocess: ${pythonScript} ${container}`);
    
    const python = spawn('python3', [pythonScript, container], {
      cwd: process.cwd(),
      timeout: 180000, // 3 minutes max
      maxBuffer: 10 * 1024 * 1024, // 10MB buffer
    });

    let stdout = '';
    let stderr = '';

    python.stdout.on('data', (data) => {
      stdout += data.toString();
      console.log(`[PythonScraper] ${data.toString()}`);
    });

    python.stderr.on('data', (data) => {
      stderr += data.toString();
      console.error(`[PythonScraper] ERROR: ${data.toString()}`);
    });

    python.on('close', (code) => {
      console.log(`[PythonScraper] Process exited with code ${code}`);
      
      if (code !== 0) {
        reject(new Error(`Python scraper failed: ${stderr}`));
        return;
      }

      try {
        const result = JSON.parse(stdout.trim());
        resolve(result as ScraperResult);
      } catch (error) {
        reject(new Error(`Failed to parse Python output: ${stdout}`));
      }
    });

    python.on('error', (error) => {
      console.error(`[PythonScraper] Process error: ${error.message}`);
      reject(error);
    });
  });
}

/**
 * Extract ETA from carrier results
 * Tries carriers in order of reliability
 */
export function extractBestETA(result: ScraperResult): {
  eta: string | null;
  carrier: string | null;
} {
  const carrierOrder = ['CMA', 'Maersk', 'MSC', 'HMM', 'COSCO', 'ZIM', 'YangMing'];
  
  for (const carrier of carrierOrder) {
    const carrierResult = result.carriers[carrier];
    if (carrierResult?.success && carrierResult?.eta) {
      return {
        eta: carrierResult.eta,
        carrier,
      };
    }
  }
  
  return {
    eta: null,
    carrier: null,
  };
}

/**
 * Parse ETA string to Date
 * Handles formats like "Fri 24-APR-2026", "2026-04-24", "24/04/2026", etc.
 */
export function parseETADate(etaString: string | null): Date | null {
  if (!etaString) return null;
  
  // Try common formats
  const formats = [
    /(\w{3})\s+(\d{1,2})-(\w{3})-(\d{4})/i, // "Fri 24-APR-2026"
    /(\d{4})-(\d{2})-(\d{2})/,              // "2026-04-24"
    /(\d{2})\/(\d{2})\/(\d{4})/,            // "24/04/2026"
    /(\d{1,2})-(\w{3})-(\d{4})/i,           // "24-APR-2026"
  ];

  for (const format of formats) {
    const match = etaString.match(format);
    if (match) {
      try {
        // Try different parsing approaches
        const date = new Date(etaString);
        if (!isNaN(date.getTime())) {
          return date;
        }
      } catch (e) {
        // Continue to next format
      }
    }
  }

  // Fallback: try native parsing
  const date = new Date(etaString);
  if (!isNaN(date.getTime())) {
    return date;
  }

  return null;
}

/**
 * Save ETA to database
 */
export async function saveETAToDatabase(
  pool: Pool,
  container: string,
  eta: string | null,
  carrier: string | null
): Promise<boolean> {
  try {
    const query = `
      UPDATE container_shipment
      SET 
        eta_raw = $2,
        carrier = COALESCE(carrier, $3),
        updated_at = NOW()
      WHERE container_number = $1
      RETURNING id
    `;
    
    const result = await pool.query(query, [container, eta, carrier]);
    
    if (result.rows.length === 0) {
      console.warn(`[Database] ⚠️ Container ${container} not found in database`);
      return false;
    }

    const eta_date = parseETADate(eta);
    if (eta_date) {
      // Update the eta (DATE) field if we can parse it
      const updateDateQuery = `
        UPDATE container_shipment
        SET eta = $2
        WHERE container_number = $1
      `;
      await pool.query(updateDateQuery, [container, eta_date.toISOString().split('T')[0]]);
    }

    console.log(`[Database] ✅ Saved ETA for ${container}: ${eta} (${carrier})`);
    return true;
  } catch (error) {
    console.error(`[Database] ❌ Error saving ETA: ${(error as any).message}`);
    return false;
  }
}

/**
 * Full scraping pipeline: call Python -> extract ETA -> save to DB
 */
export async function scrapAndSaveETA(
  pool: Pool,
  container: string
): Promise<{
  success: boolean;
  eta: string | null;
  carrier: string | null;
  error?: string;
}> {
  try {
    console.log(`\n${'='.repeat(60)}`);
    console.log(`[Pipeline] 🎯 Starting ETA scraping for ${container}`);
    console.log(`${'='.repeat(60)}\n`);

    // Call Python scraper
    const result = await callPythonScraper(container);

    if (result.error) {
      console.error(`[Pipeline] ❌ Scraper error: ${result.error}`);
      return {
        success: false,
        eta: null,
        carrier: null,
        error: result.error,
      };
    }

    // Extract best ETA
    const { eta, carrier } = extractBestETA(result);

    if (!eta) {
      console.warn(`[Pipeline] ⚠️ No ETA found for ${container}`);
      return {
        success: false,
        eta: null,
        carrier: null,
        error: 'No ETA found from any carrier',
      };
    }

    // Save to database
    const saved = await saveETAToDatabase(pool, container, eta, carrier);

    console.log(`\n[Pipeline] ✅ Complete! ETA: ${eta} (${carrier})\n`);

    return {
      success: saved,
      eta,
      carrier,
    };
  } catch (error) {
    const errorMsg = (error as any).message || 'Unknown error';
    console.error(`[Pipeline] ❌ Error: ${errorMsg}`);
    return {
      success: false,
      eta: null,
      carrier: null,
      error: errorMsg,
    };
  }
}