import { Pool } from "pg";
import * as dotenv from "dotenv";

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

const pool = new Pool({ 
  connectionString: process.env.DATABASE_URL 
});

async function checkContainerData() {
  try {
    const result = await pool.query(
      `SELECT 
        id,
        container_number,
        carrier,
        vessel_tracking_url,
        eta,
        eta_raw,
        status,
        last_eta_scrape_attempt,
        created_at
       FROM container_shipment 
       WHERE container_number = $1`,
      ["MNBU4288732"]
    );

    if (result.rows.length === 0) {
      console.log("❌ Container not found");
      await pool.end();
      process.exit(1);
    }

    const container = result.rows[0];
    
    console.log("╔═══════════════════════════════════════════════════╗");
    console.log("║         Container Database Check                 ║");
    console.log("╚═══════════════════════════════════════════════════╝\n");

    console.log("Container Details:");
    console.log(`  ID: ${container.id}`);
    console.log(`  Number: ${container.container_number}`);
    console.log(`  Carrier: ${container.carrier}`);
    console.log(`  Status: ${container.status}`);
    console.log(`  Created: ${container.created_at}`);
    console.log(`  ETA (current): ${container.eta}`);
    console.log(`  ETA (raw): ${container.eta_raw}`);
    console.log(`  Last scrape attempt: ${container.last_eta_scrape_attempt}`);
    console.log(`\nTracking URL:`);
    if (container.vessel_tracking_url) {
      console.log(`  ✅ ${container.vessel_tracking_url}`);
    } else {
      console.log(`  ❌ NULL - No tracking URL!`);
    }

    // Check scraping history
    const historyResult = await pool.query(
      `SELECT scrape_status, eta_scraped, eta_raw_text, updated_at
       FROM container_tracking_history
       WHERE container_shipment_id = $1
       ORDER BY updated_at DESC
       LIMIT 5`,
      [container.id]
    );

    console.log(`\nRecent Scraping History (${historyResult.rows.length} records):`);
    if (historyResult.rows.length > 0) {
      historyResult.rows.forEach((row, idx) => {
        console.log(`  ${idx + 1}. ${row.updated_at.toISOString()}`);
        console.log(`     Status: ${row.scrape_status}`);
        console.log(`     ETA: ${row.eta_scraped || "NULL"}`);
        console.log(`     Raw: ${row.eta_raw_text || "NULL"}`);
      });
    } else {
      console.log(`  No history found`);
    }

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

checkContainerData();