import { Pool } from "pg";
import puppeteer from "puppeteer-extra";
import StealthPlugin from "puppeteer-extra-plugin-stealth";
import * as dotenv from "dotenv";

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

// Add stealth plugin
puppeteer.use(StealthPlugin());

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
import { notifyAlertNow } from "../src/lib/email-service";

interface Container {
  id: string;
  container_number: string;
  carrier: string;
  vessel_tracking_url: string;
  eta: string | null;
  scraped_eta: string | null;
  user_id: string | null;
  created_by_admin_id: string | null;
}

// ============================================================
// EXTRACT ETA FROM MAERSK HTML (Feeder arrival)
// ============================================================
function extractEtaFromMaerskHtml(htmlContent: string): string | null {
  try {
    const cleanHtml = htmlContent.replace(/<!--[\s\S]*?-->/g, '');

    // Find ALL "Feeder arrival" / "Vessel arrival" milestones and use the LAST one.
    // Maersk lists milestones chronologically, so the first arrival is an intermediate
    // (transshipment) port and the LAST arrival is the final destination = real ETA.
    const arrivalRegex = /(Feeder arrival|Vessel arrival)[\s\S]{0,300}?<span[^>]*data-test="milestone-date"[^>]*>([^<]+)<\/span>/g;
    const matches = Array.from(cleanHtml.matchAll(arrivalRegex));

    if (matches.length > 0) {
      const last = matches[matches.length - 1];
      return last[2]; // e.g., "19 Jul 2026 08:00"
    }

    return null;
  } catch (error) {
    console.error(`[Parse] Error: ${error}`);
    return null;
  }
}

// ============================================================
// FORMAT DATE TO YYYY-MM-DD
// ============================================================
function formatDate(dateStr: string): string | null {
  if (!dateStr) return null;

  // Match pattern: "31 May 2026 20:24" → "2026-05-31"
  const match = dateStr.match(/(\d{1,2})\s+(\w{3})\s+(\d{4})/i);
  if (!match) return null;

  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 day = match[1].padStart(2, "0");
  const monthNum = monthMap[match[2].toLowerCase()];
  const year = match[3];

  if (!monthNum) return null;
  return `${year}-${monthNum}-${day}`;
}

// ============================================================
// SCRAPE WITH PUPPETEER + STEALTH
// ============================================================
async function scrapeMaerskWithPuppeteer(
  trackingUrl: string,
  containerNum: string
): Promise<{ raw: string | null; formatted: string | null }> {
  let browser = null;
  try {
    browser = await puppeteer.launch({
      headless: true,
      args: [
        "--no-sandbox",
        "--disable-setuid-sandbox",
        "--disable-dev-shm-usage",
        "--disable-gpu",
      ],
    });

    const page = await browser.newPage();
    await page.setViewport({ width: 1920, height: 1080 });
    await page.setUserAgent(
      "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36"
    );

    // Navigate to tracking page
    await page.goto(trackingUrl, { waitUntil: "domcontentloaded", timeout: 60000 });

    // Wait for JS to render (3 seconds should be enough)
    await new Promise(resolve => setTimeout(resolve, 3000));

    // Get rendered HTML
    const html = await page.content();
    
    // Close browser before parsing
    await browser.close();
    browser = null;

    // Extract ETA
    const rawEta = extractEtaFromMaerskHtml(html);
    const formatted = rawEta ? formatDate(rawEta) : null;

    return { raw: rawEta, formatted };
  } catch (error) {
    const msg = (error as any)?.message || String(error);
    if (browser) {
      try {
        await browser.close();
      } catch (e) {}
    }
    return { raw: null, formatted: null };
  }
}

// ============================================================
// MAIN: BATCH UPDATE ALL MAERSK CONTAINERS
// ============================================================
async function batchUpdateAllMaerskContainers() {
  console.log("╔═══════════════════════════════════════════════════╗");
  console.log("║ BATCH UPDATE: MNBU & SUDU & CXRU (Maersk)        ║");
  console.log("╚═══════════════════════════════════════════════════╝\n");

  try {
    // Fetch ALL Maersk containers (identified by MNBU, SUDU or CXRU prefix)
    console.log("📋 Querying database for all MNBU & SUDU & CXRU containers...\n");
    const result = await pool.query(
      `SELECT 
        id,
        container_number,
        carrier,
        vessel_tracking_url,
        eta,
        scraped_eta,
        user_id,
        created_by_admin_id
       FROM container_shipment
       WHERE (container_number LIKE 'MNBU%' OR container_number LIKE 'SUDU%' OR container_number LIKE 'CXRU%')
       AND status NOT IN ('delivered', 'cancelled')
       ORDER BY created_at DESC`
    );

    const containers: Container[] = result.rows;
    console.log(`✅ Found ${containers.length} active MNBU containers\n`);

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

    // Display sample
    console.log("📊 Sample containers:\n");
    containers.slice(0, 5).forEach((c, i) => {
      console.log(`  ${i + 1}. ${c.container_number}`);
      console.log(`     Current ETA: ${c.eta || "NULL"}`);
      console.log(`     URL: ${c.vessel_tracking_url || "NULL"}\n`);
    });

    if (containers.length > 5) {
      console.log(`  ... and ${containers.length - 5} more containers\n`);
    }

    // Start scraping
    console.log("🌐 Starting scrape process...\n");
    console.log(
      "⚠️  This may take several minutes depending on number of containers"
    );
    console.log("━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n");

    let successCount = 0;
    let updateCount = 0;
    let skipCount = 0;
    let errorCount = 0;

    // Process each container
    for (let i = 0; i < containers.length; i++) {
      const container = containers[i];
      const progress = `[${i + 1}/${containers.length}]`;

      process.stdout.write(`${progress} ${container.container_number}: `);

      // Generate tracking URL if not present
      let trackingUrl = container.vessel_tracking_url;
      if (!trackingUrl) {
        trackingUrl = `https://www.maersk.com/tracking/${container.container_number}`;
        
        // Also save the URL to the database
        try {
          await pool.query(
            `UPDATE container_shipment 
             SET vessel_tracking_url = $1
             WHERE id = $2`,
            [trackingUrl, container.id]
          );
        } catch (e) {
          // Silent fail - URL generation was successful anyway
        }
      }

      // Scrape
      const { raw, formatted } = await scrapeMaerskWithPuppeteer(
        trackingUrl,
        container.container_number
      );

      if (!formatted) {
        console.log("❌ (Extract failed)");
        errorCount++;
        continue;
      }

      // Normalize current + new ETA to YYYY-MM-DD to detect a real change
      const toYmd = (v: any): string | null => {
        if (!v) return null;
        // Si ya viene como 'YYYY-MM-DD' (string), úsalo tal cual.
        if (typeof v === "string") {
          const m = v.match(/^(\d{4})-(\d{2})-(\d{2})/);
          if (m) return `${m[1]}-${m[2]}-${m[3]}`;
        }
        // Use UTC for DATE columns to ensure consistency globally (Spain=China=USA)
        const d = new Date(v);
        if (Number.isNaN(d.getTime())) return null;
        const y = d.getUTCFullYear();
        const mo = String(d.getUTCMonth() + 1).padStart(2, "0");
        const da = String(d.getUTCDate()).padStart(2, "0");
        return `${y}-${mo}-${da}`;
      };
      const userEtaYmd = toYmd(container.eta);
      const oldScrapedYmd = toYmd(container.scraped_eta);
      const newScrapedYmd = toYmd(formatted);
      const baseline = oldScrapedYmd ?? userEtaYmd;
      const etaChanged = !!baseline && !!newScrapedYmd && baseline !== newScrapedYmd;

      if (oldScrapedYmd !== newScrapedYmd) {
        try {
          // Use carrier from DB or default to "Maersk" for MNBU
          const carrier = container.carrier || "Maersk";

          if (etaChanged) {
            // Store the scraped ETA (NOT the user `eta`) and flag the change
            await pool.query(
              `UPDATE container_shipment 
               SET scraped_eta = $1, eta_raw = $2, previous_eta = $3, eta_changed_at = NOW(),
                   last_eta_scrape_attempt = NOW(), updated_at = NOW()
               WHERE id = $4`,
              [formatted, raw, baseline, container.id]
            );

            // Notify the trader and/or the admin that own this container
            const description = `${container.container_number}: ETA actualizada por seguimiento ${baseline} → ${newScrapedYmd} (${carrier})`;
            const recipients = [container.user_id, container.created_by_admin_id].filter(
              (v, i, arr) => v && arr.indexOf(v) === i
            );
            for (const recipientId of recipients) {
              try {
                const alertRes = await pool.query(
                  `INSERT INTO shipping_alert (user_id, container_shipment_id, title, alert_type, description, is_read, severity)
                   VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id`,
                  [recipientId, container.id, "ETA Actualizada", "eta_changed", description, false, "warning"]
                );
                await notifyAlertNow(pool, alertRes.rows[0].id);
              } catch (alertError) {
                console.log(`⚠️ Alert error: ${(alertError as any)?.message}`);
              }
            }
          } else {
            // First scrape matching user ETA — just store the scraped value
            await pool.query(
              `UPDATE container_shipment 
               SET scraped_eta = $1, eta_raw = $2, last_eta_scrape_attempt = NOW(), updated_at = NOW()
               WHERE id = $3`,
              [formatted, raw, container.id]
            );
          }

          // Insert history record (use Maersk as carrier if null)
          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, carrier, formatted, raw, "success"]
          );

          const oldEta = baseline || "NULL";
          console.log(`✅ ${oldEta} → ${formatted}`);
          successCount++;
          updateCount++;
        } catch (dbError) {
          console.log(`❌ DB Error: ${(dbError as any)?.message}`);
          errorCount++;
        }
      } else {
        console.log(`✓ ${formatted} (already current)`);
        successCount++;
      }
    }

    console.log("\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━");
    console.log("\n╔═══════════════════════════════════════════════════╗");
    console.log("║    MNBU & SUDU & CXRU BATCH SUMMARY              ║");
    console.log("╠═══════════════════════════════════════════════════╣");
    console.log(`║ Total containers processed:    ${String(containers.length).padEnd(21)}║`);
    console.log(`║ Successfully scraped:          ${String(successCount).padEnd(21)}║`);
    console.log(`║ ETAs updated:                  ${String(updateCount).padEnd(21)}║`);
    console.log(`║ Skipped (no URL):              ${String(skipCount).padEnd(21)}║`);
    console.log(`║ Errors:                        ${String(errorCount).padEnd(21)}║`);
    console.log("╚═══════════════════════════════════════════════════╝\n");

    console.log("✅ Maersk batch update complete!");
    console.log("💾 All updated ETAs are now in the database");
    console.log("👥 Changes are visible to ALL admins\n");

    await pool.end();
  } catch (error) {
    console.error("❌ Critical error:", error);
    await pool.end();
    process.exit(1);
  }
}

// Run the batch update
batchUpdateAllMaerskContainers();