import * as dotenv from "dotenv";
import * as path from "path";

// Load env from .env file FIRST, before any other imports
dotenv.config({ path: path.resolve(process.cwd(), ".env") });

import pool from "@/lib/db-script";
import { scrapeShippingETA, updateETAInDatabase } from "@/lib/shipping-scraper";

/**
 * Standalone script to test CMA CGM carrier scraping AND update database
 * Run with: npx tsx scripts/test-cma-scraper.ts
 */

async function main() {
  try {
    // Test CMA
    const trackingUrlCMA =
      "https://www.cma-cgm.com/ebusiness/tracking/detail/CMAU0863618?SearchCriteria=BL&SearchByReference=MUC0137256";
    const containerNumberCMA = "CMAU0863618";

    console.log("=== Testing CMA CGM Carrier Scraping with Database Update ===\n");
    console.log(`Container: ${containerNumberCMA}`);
    console.log(`Tracking URL: ${trackingUrlCMA}\n`);

    // Step 1: Check if container exists in database
    console.log("📊 Step 1: Checking if CMA container exists in database...");
    const containerCheck = await pool.query(
      `SELECT id, carrier, container_number, vessel_tracking_url, eta FROM container_shipment WHERE container_number = $1`,
      [containerNumberCMA]
    );

    if (containerCheck.rows.length === 0) {
      console.log(`❌ Container ${containerNumberCMA} not found in database!`);
      console.log("Create the container first via the admin panel with:");
      console.log(`  Container Number: ${containerNumberCMA}`);
      console.log(`  Carrier: CMA`);
      console.log(`  Vessel Tracking URL: ${trackingUrlCMA}\n`);

      // Try to find any CMA container instead
      console.log("Let me try to find any CMA container instead...\n");
      const cmaCheck = await pool.query(
        `SELECT id, carrier, container_number, vessel_tracking_url, eta FROM container_shipment 
         WHERE container_number ILIKE '%CMA%' LIMIT 1`
      );

      if (cmaCheck.rows.length === 0) {
        console.log("❌ No CMA containers found either.");
        await pool.end();
        process.exit(1);
      }

      const container = cmaCheck.rows[0];
      await testContainer(pool, container, "CMA");
    } else {
      const container = containerCheck.rows[0];
      await testContainer(pool, container, "CMA");
    }

    console.log("\n✅ ALL DONE! The ETA should now appear in the dashboard.");
    await pool.end();
    process.exit(0);
  } catch (error) {
    console.error("❌ Fatal error:", error);
    await pool.end();
    process.exit(1);
  }
}

async function testContainer(pool: any, container: any, carrierName: string) {
  console.log(`\n✅ Container found!`);
  console.log(`   ID: ${container.id}`);
  console.log(`   Carrier: ${container.carrier}`);
  console.log(`   Current ETA: ${container.eta}`);
  console.log(`   Tracking URL: ${container.vessel_tracking_url}\n`);

  // Step 2: Scrape ETA
  console.log(`📥 Step 2: Scraping ${carrierName} website...`);
  const startTime = Date.now();

  const result = await scrapeShippingETA(
    container.container_number,
    carrierName,
    container.vessel_tracking_url
  );

  const elapsed = Date.now() - startTime;
  console.log(`✅ Scraping completed in ${elapsed}ms`);
  console.log(`   Status: ${result.status}`);
  console.log(`   Raw ETA: ${result.etaRaw}`);
  console.log(`   Formatted ETA: ${result.etaFormatted}\n`);

  // Step 3: Update database
  console.log("💾 Step 3: Updating database...");
  await updateETAInDatabase(pool, container.id, carrierName, result);
  console.log(`✅ Database updated!\n`);

  // Step 4: Verify update
  console.log("🔍 Step 4: Verifying update...");
  const verify = await pool.query(
    `SELECT 
      cs.eta,
      cs.eta_raw,
      cth.eta_scraped,
      cth.scrape_status,
      cth.created_at
    FROM container_shipment cs
    LEFT JOIN container_tracking_history cth ON cs.id = cth.container_shipment_id
    WHERE cs.id = $1
    ORDER BY cth.created_at DESC
    LIMIT 1`,
    [container.id]
  );

  if (verify.rows.length > 0) {
    const row = verify.rows[0];
    console.log(`✅ Update verified!`);
    console.log(`   container_shipment.eta: ${row.eta}`);
    console.log(`   container_shipment.eta_raw: ${row.eta_raw}`);
    console.log(`   container_tracking_history.eta_scraped: ${row.eta_scraped}`);
    console.log(`   container_tracking_history.scrape_status: ${row.scrape_status}`);
    console.log(`   Last scraped: ${row.created_at}`);
  }
}

main();