#!/usr/bin/env python3
"""
Batch Update CMA CGM (CMAU) containers
Queries database, scrapes ETA, and updates database
"""

import sys
import json
import time
import psycopg2
import psycopg2.extras
import undetected_chromedriver as uc
from selenium.webdriver.common.by import By
from datetime import datetime
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv('.env.local')
load_dotenv('.env')

DATABASE_URL = os.getenv('DATABASE_URL')

def scrap_cma(container):
    """Scrape CMA CGM using direct URL"""
    options = uc.ChromeOptions()
    options.add_argument("--disable-gpu")
    options.add_argument("--no-sandbox")
    options.add_argument("--disable-blink-features=AutomationControlled")
    
    driver = uc.Chrome(options=options)
    
    try:
        url = f"https://www.cma-cgm.com/ebusiness/tracking/detail/{container}"
        print(f"  🌐 {url}", file=sys.stderr)
        driver.get(url)
        time.sleep(8)
        
        # Accept cookies
        try:
            driver.execute_script("document.getElementById('onetrust-accept-btn-handler').click();")
            time.sleep(2)
        except:
            pass
        
        # Extract date
        xpath_attempts = [
            "//div[contains(@class, 'timeline')]//span[contains(text(), '202')]",
            "//div[contains(@class, 'timeline--item-eta')]//span",
            "//span[@class='date-value']"
        ]
        
        eta = None
        for xpath in xpath_attempts:
            try:
                elements = driver.find_elements(By.XPATH, xpath)
                if elements:
                    eta = elements[0].text.strip()
                    if eta and len(eta) > 5:
                        break
            except:
                continue
        
        driver.quit()
        return eta if eta else "No Encontrado"
        
    except Exception as e:
        try:
            driver.quit()
        except:
            pass
        return f"Error"

def parse_cma_date(date_str):
    """Parse CMA date format: 05-Apr-2026 or 05 Apr 2026"""
    try:
        # Try format: 05-Apr-2026
        return datetime.strptime(date_str, "%d-%b-%Y").date()
    except:
        try:
            # Try format: 05 Apr 2026
            return datetime.strptime(date_str, "%d %b %Y").date()
        except:
            return None

def batch_update_cma():
    print("\n╔═══════════════════════════════════╗")
    print("║  BATCH UPDATE: CMAU (CMA CGM)    ║")
    print("╚═══════════════════════════════════╝\n")
    
    try:
        conn = psycopg2.connect(DATABASE_URL)
        conn.autocommit = False
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        
        # Query containers
        print("📋 Querying database for all CMAU containers...\n")
        
        cur.execute("""
            SELECT id, container_number, carrier, eta
            FROM container_shipment 
            WHERE container_number LIKE 'CMAU%'
            ORDER BY container_number
        """)
        
        containers = cur.fetchall()
        
        if not containers:
            print("ℹ️  No CMAU containers found in database\n")
            conn.close()
            return
        
        print(f"✅ Found {len(containers)} active containers\n")
        print("📊 Sample containers:\n")
        for i, c in enumerate(containers[:5], 1):
            print(f"  {i}. {c['container_number']}")
            if c['eta']:
                print(f"     Current ETA: {c['eta']}")
        
        if len(containers) > 5:
            print(f"  ... and {len(containers) - 5} more containers\n")
        
        print("🌐 Starting scrape process...\n")
        print("⚠️  This may take several minutes depending on number of containers")
        print("━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n")
        
        success_count = 0
        fail_count = 0
        
        for idx, container in enumerate(containers, 1):
            container_id = container['id']
            container_number = container['container_number']
            
            sys.stdout.write(f"[{idx}/{len(containers)}] {container_number}: ")
            sys.stdout.flush()
            
            try:
                eta_raw = scrap_cma(container_number)
                
                if eta_raw not in ["No Encontrado", "Error"]:
                    eta_date = parse_cma_date(eta_raw)
                    
                    if eta_date:
                        # Update database
                        cur.execute("""
                            UPDATE container_shipment 
                            SET eta = %s, eta_raw = %s, last_eta_scrape_attempt = NOW(), updated_at = NOW(), carrier = COALESCE(carrier, 'CMA CGM')
                            WHERE id = %s
                        """, (eta_date, eta_raw, container_id))
                        
                        # Insert history
                        cur.execute("""
                            INSERT INTO container_tracking_history (container_shipment_id, carrier, eta_scraped, eta_raw_text, scrape_status, created_at)
                            VALUES (%s, 'CMA CGM', %s, %s, 'success', NOW())
                        """, (container_id, eta_date, eta_raw))
                        
                        conn.commit()
                        print(f"✅ {eta_date}")
                        success_count += 1
                    else:
                        print(f"❌ Could not parse date: {eta_raw}")
                        fail_count += 1
                else:
                    print(f"❌ {eta_raw}")
                    fail_count += 1
                    
            except Exception as e:
                print(f"❌ Error: {str(e)[:50]}")
                fail_count += 1
                conn.rollback()
        
        print("\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━")
        print(f"\n✅ Scraping complete!")
        print(f"   ✓ Updated: {success_count} containers")
        print(f"   ✗ Failed: {fail_count} containers\n")
        
        cur.close()
        conn.close()
        
    except Exception as e:
        print(f"❌ Database error: {str(e)}")
        sys.exit(1)

if __name__ == '__main__':
    batch_update_cma()