import { Pool } from "pg";
import * as dotenv from "dotenv";
import axios from "axios";
const cheerio = require("cheerio");

// Load environment variables - try both .env and .env.local
dotenv.config({ path: ".env.local" });
dotenv.config({ path: ".env" });

// Direct PostgreSQL connection (no Next.js dependencies)
const pool = new Pool({ 
  connectionString: process.env.DATABASE_URL 
});

/**
 * Parse ETA from Maersk HTML
 */
function parseEtaFromMaerskHtml(htmlContent: string): string | null {
  try {
    // Remove HTML comments to clean up parsing
    const cleanHtml = htmlContent.replace(/<!--[\s\S]*?-->/g, '');
    
    // Look for "Feeder arrival" or "Vessel arrival" followed by milestone-date span
    const arrivalRegex = /(Feeder arrival|Vessel arrival)[\s\S]*?<span data-test="milestone-date">([^<]+)<\/span>/;
    const match = cleanHtml.match(arrivalRegex);
    
    if (match) {
      const eventType = match[1];
      const dateStr = match[2];
      console.log(`     ✓ Found ${eventType}: ${dateStr}`);
      return dateStr;
    }

    // Fallback: Find all milestone-dates
    const dateRegex = /<span data-test="milestone-date">([^<]+)<\/span>/g;
    const dates = [...cleanHtml.matchAll(dateRegex)];
    
    if (dates.length >= 5) {
      console.log(`     ✓ Found ${dates.length} dates, using 5th (typical arrival position)`);
      return dates[4][1];
    }

    console.log(`     ✗ No dates found in HTML`);
    return null;
  } catch (error) {
    console.error(`     ✗ HTML parsing error: ${error}`);
    return null;
  }
}

/**
 * Format ETA to YYYY-MM-DD
 */
function formatETA(etaString: string): string | null {
  if (!etaString || etaString.toLowerCase().includes("no encontrado") || etaString.toLowerCase().includes("error")) {
    return null;
  }

  try {
    const match = etaString.match(/(\d{1,2})\s+(\w{3})\s+(\d{4})/i);
    if (match) {
      const monthMap: { [key: string]: string } = {
        jan: "01", feb: "02", mar: "03", apr: "04",
        may: "05", jun: "06", jul: "07", aug: "08",
        sep: "09", oct: "10", nov: "11", dec: "12"
      };
      const monthNum = monthMap[match[2].toLowerCase()];
      if (monthNum) {
        const day = match[1].padStart(2, "0");
        return `${match[3]}-${monthNum}-${day}`;
      }
    }
  } catch (error) {
    console.error(`     ✗ Format error: ${error}`);
  }

  return null;
}

/**
 * Scrape Maersk container ETA
 */
async function scrapeMaersk(trackingUrl: string, retries = 3): Promise<{ raw: string | null; formatted: string | null }> {
  for (let attempt = 1; attempt <= retries; attempt++) {
    try {
      const response = await axios.get(trackingUrl, {
        timeout: 20000,
        headers: {
          "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
          "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
          "Accept-Language": "en-US,en;q=0.9",
          "Accept-Encoding": "gzip, deflate, br",
          "Cache-Control": "max-age=0",
          "Upgrade-Insecure-Requests": "1",
          "Sec-Fetch-Dest": "document",
          "Sec-Fetch-Mode": "navigate",
          "Sec-Fetch-Site": "none",
          "Pragma": "no-cache",
          "DNT": "1",
        },
      });

      const eta = parseEtaFromMaerskHtml(response.data);
      const formatted = eta ? formatETA(eta) : null;
      
      return { raw: eta, formatted };
    } catch (error) {
      const errorMsg = error instanceof Error ? error.message : String(error);
      if (attempt < retries) {
        console.log(`     ⚠️ Attempt ${attempt} failed: ${errorMsg}, retrying...`);
        // Wait before retry
        await new Promise(resolve => setTimeout(resolve, 2000));
      } else {
        console.log(`     ✗ HTTP Error after ${retries} attempts: ${errorMsg}`);
        return { raw: null, formatted: null };
      }
    }
  }
  return { raw: null, formatted: null };
}

/**
 * Main scraping function
 */
async function scrapeAllMaerskContainers() {
  console.log("╔═══════════════════════════════════════════════════╗");
  console.log("║    Manual Maersk ETA Scraper (All Containers)    ║");
  console.log("╚═══════════════════════════════════════════════════╝\n");

  try {
    // Step 1: Get all Maersk containers
    console.log("📋 Fetching all Maersk containers from database...\n");
    const result = await pool.query(
      `SELECT id, container_number, carrier, vessel_tracking_url, eta, updated_at
       FROM container_shipment 
       WHERE LOWER(carrier) LIKE '%maersk%' 
       AND status NOT IN ('delivered', 'cancelled')
       ORDER BY updated_at DESC`
    );

    const containers = result.rows;
    console.log(`Found ${containers.length} Maersk containers:\n`);

    containers.slice(0, 15).forEach((c, idx) => {
      console.log(`  ${idx + 1}. ${c.container_number} (Current: ${c.eta || 'NULL'})`);
    });
    
    if (containers.length > 15) {
      console.log(`  ... and ${containers.length - 15} more`);
    }

    if (containers.length === 0) {
      console.log("❌ No Maersk containers found");
      await pool.end();
      process.exit(0);
    }

    // Step 2: Scrape each container
    console.log(`\n\n🌐 Scraping ${containers.length} containers...\n`);
    let successCount = 0;
    let skipCount = 0;

    for (const container of containers) {
      try {
        if (!container.vessel_tracking_url) {
          console.log(`⚠️  ${container.container_number}: No tracking URL`);
          skipCount++;
          continue;
        }

        process.stdout.write(`  ${container.container_number}: `);
        console.log(`\n     URL: ${container.vessel_tracking_url}`);

        const { raw, formatted } = await scrapeMaersk(container.vessel_tracking_url);

        if (formatted && formatted !== container.eta) {
          // Update database
          await pool.query(
            `UPDATE container_shipment 
             SET eta = $1, eta_raw = $2, last_eta_scrape_attempt = NOW(), updated_at = NOW()
             WHERE id = $3`,
            [formatted, raw, container.id]
          );

          // Insert history
          await pool.query(
            `INSERT INTO container_tracking_history 
             (container_shipment_id, carrier, eta_scraped, eta_raw_text, scrape_status)
             VALUES ($1, $2, $3, $4, $5)`,
            [container.id, container.carrier, formatted, raw, "success"]
          );

          console.log(`\n     ✅ Updated: ${formatted}`);
          successCount++;
        } else if (formatted === container.eta) {
          console.log(`\n     → ${formatted} (no change)`);
        } else {
          console.log(`\n     ⚠️ Could not extract date`);
        }

        // Delay between requests
        await new Promise((resolve) => setTimeout(resolve, 1500));
      } catch (error) {
        console.error(`\n     ❌ Error: ${error}`);
      }
    }

    // Step 3: Summary
    console.log(`\n\n╔═══════════════════════════════════════════════════╗`);
    console.log(`║                   SUMMARY                         ║`);
    console.log(`╠═══════════════════════════════════════════════════╣`);
    console.log(`║ Total Maersk containers:  ${containers.length}`);
    console.log(`║ Successfully updated:     ${successCount}`);
    console.log(`║ Skipped (no URL):         ${skipCount}`);
    console.log(`╚═══════════════════════════════════════════════════╝\n`);

    // Step 4: Show updated containers
    console.log("Recent updates:\n");
    const updatedResult = await pool.query(
      `SELECT container_number, eta, last_eta_scrape_attempt
       FROM container_shipment 
       WHERE LOWER(carrier) LIKE '%maersk%' 
       AND eta IS NOT NULL
       ORDER BY last_eta_scrape_attempt DESC
       LIMIT 10`
    );

    updatedResult.rows.forEach((c, idx) => {
      const updated = c.last_eta_scrape_attempt ? '✅' : '⚠️';
      console.log(`  ${updated} ${c.container_number}: ${c.eta}`);
    });

    await pool.end();
    process.exit(0);
  } catch (error) {
    console.error("\nFatal error:", error);
    await pool.end();
    process.exit(1);
  }
}

scrapeAllMaerskContainers();