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;
}

async function scrapeYangMing(
  containerId: string,
  retries = 2
): Promise<{ raw: string | null; formatted: string | null }> {
  const url = "https://www.yangming.com/en/esolution/cargo_tracking";

  for (let attempt = 1; attempt <= retries; attempt++) {
    let browser;
    try {
      browser = await puppeteer.launch({
        headless: true,
        args: [
          "--no-sandbox",
          "--disable-setuid-sandbox",
          "--disable-blink-features=AutomationControlled",
        ],
      });

      const page = await browser.newPage();
      page.setDefaultNavigationTimeout(120000);

      await page.setUserAgent(
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36"
      );
      await page.setViewport({ width: 1920, height: 1080 });

      // Navigate to page
      await page.goto(url, { waitUntil: "networkidle2" });
      await new Promise((resolve) => setTimeout(resolve, 1500));

      // Fill container number
      const input = await page.$('input[type="text"]');
      if (input) {
        await input.type(containerId, { delay: 50 });
      }

      // Press Enter
      await page.keyboard.press("Enter");

      // Wait for results
      await new Promise((resolve) => setTimeout(resolve, 12000));

      const html = await page.content();

      // Extract ETA date from "Discharged" event
      const dateData = await page.evaluate(() => {
        const rows = Array.from(document.querySelectorAll('tr'));
        
        for (const row of rows) {
          const cells = row.querySelectorAll('td');
          if (cells.length >= 2) {
            const dateCell = cells[0]?.textContent?.trim();
            const eventCell = cells[1]?.textContent?.trim();
            
            if (eventCell === 'Discharged' && dateCell) {
              return {
                date: dateCell,
                event: eventCell,
              };
            }
          }
        }
        return null;
      });

      await browser.close();

      if (dateData) {
        const dateMatch = dateData.date.match(/(\d{4})\/(\d{2})\/(\d{2})/);
        if (dateMatch) {
          const formattedDate = `${dateMatch[1]}-${dateMatch[2]}-${dateMatch[3]}`;
          return { raw: dateData.date, formatted: formattedDate };
        }
      }

      return { raw: null, formatted: null };
    } catch (error) {
      if (browser) await browser.close();
      if (attempt === retries) {
        return { raw: null, formatted: null };
      }
      await new Promise((resolve) => setTimeout(resolve, 2000));
    }
  }

  return { raw: null, formatted: null };
}

async function batchUpdateYangMing() {
  console.log("\n╔═══════════════════════════════════════════════╗");
  console.log("║  BATCH UPDATE: YMLU Containers (Yang Ming)  ║");
  console.log("╚═══════════════════════════════════════════════╝\n");

  try {
    // Query for all YMLU containers
    console.log("📋 Querying database for all YMLU containers...\n");

    const result = await pool.query<Container>(
      `SELECT 
        id,
        container_number,
        carrier,
        vessel_tracking_url,
        eta,
        scraped_eta,
        user_id,
        created_by_admin_id
       FROM container_shipment 
       WHERE container_number LIKE 'YMLU%'
       ORDER BY container_number`
    );

    const containers = result.rows;

    if (containers.length === 0) {
      console.log("ℹ️  No YMLU containers found in database\n");
      await pool.end();
      return;
    }

    console.log(`✅ Found ${containers.length} active containers\n`);
    console.log("📊 Sample containers:\n");
    containers.slice(0, 5).forEach((c, i) => {
      console.log(`  ${i + 1}. ${c.container_number}`);
      if (c.eta) {
        console.log(`     Current ETA: ${new Date(c.eta).toString()}`);
      }
    });
    if (containers.length > 5) {
      console.log(`  ... and ${containers.length - 5} more containers\n`);
    }

    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 failCount = 0;

    for (let i = 0; i < containers.length; i++) {
      const container = containers[i];
      process.stdout.write(`[${i + 1}/${containers.length}] ${container.container_number}: `);

      try {
        const { raw, formatted } = await scrapeYangMing(container.container_number);

        if (formatted) {
          const toYmd = (v: any): string | null => {
            if (!v) return null;
            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;
          const carrier = container.carrier || "Yang Ming";

          if (oldScrapedYmd !== newScrapedYmd) {
            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(), carrier = COALESCE(carrier, 'Yang Ming')
                 WHERE id = $4`,
                [newScrapedYmd, raw, baseline, container.id]
              );

              const description = `${container.container_number}: ETA actualizada por seguimiento ${baseline} → ${newScrapedYmd} (${carrier})`;
              const recipients = [container.user_id, container.created_by_admin_id].filter(
                (v, idx, arr) => v && arr.indexOf(v) === idx
              );
              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 {
              await pool.query(
                `UPDATE container_shipment 
                 SET scraped_eta = $1, eta_raw = $2, last_eta_scrape_attempt = NOW(), updated_at = NOW(), carrier = COALESCE(carrier, 'Yang Ming')
                 WHERE id = $3`,
                [newScrapedYmd, raw, container.id]
              );
            }
          }

          // Insert history
          await pool.query(
            `INSERT INTO container_tracking_history (container_shipment_id, carrier, eta_scraped, eta_raw_text, scrape_status, created_at)
             VALUES ($1, 'Yang Ming', $2, $3, 'success', NOW())`,
            [container.id, newScrapedYmd, raw]
          );

          console.log(`✅ ${newScrapedYmd}`);
          successCount++;
        } else {
          console.log("❌ No date found");
          failCount++;
        }
      } catch (error) {
        console.log(`❌ Error: ${error instanceof Error ? error.message : error}`);
        failCount++;
      }
    }

    console.log("\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━");
    console.log(`\n✅ Scraping complete!`);
    console.log(`   ✓ Updated: ${successCount} containers`);
    console.log(`   ✗ Failed: ${failCount} containers\n`);

    await pool.end();
  } catch (error) {
    console.error("Database error:", error instanceof Error ? error.message : error);
    await pool.end();
  }
}

batchUpdateYangMing();