#!/usr/bin/env python3
"""
Test the card import feature against the MTG card list XLSX.
Reads card names, calls the import API, and returns corrected card details.
"""
import openpyxl
import requests
import json
from datetime import datetime

# Configuration
BACKEND_URL = "http://localhost:5555"
INPUT_FILE = "/home/wall-o/MTG card list.xlsx"
OUTPUT_FILE = "/home/wall-o/MTG card list - corrected.xlsx"

def read_card_names_from_xlsx(filepath):
    """Read card names from the XLSX file."""
    wb = openpyxl.load_workbook(filepath)
    ws = wb.active
    
    card_names = []
    for row in ws.iter_rows(min_row=2, values_only=True):  # Skip header
        if row[0]:  # First column has card name
            card_names.append(str(row[0]).strip())
    
    return card_names

def call_card_import_api(card_names):
    """Call the card import API to get corrected spellings."""
    url = f"{BACKEND_URL}/api/v1/card-import/"
    
    payload = {
        "card_names": card_names
    }
    
    try:
        response = requests.post(url, json=payload, timeout=30)
        response.raise_for_status()
        return response.json()
    except requests.exceptions.RequestException as e:
        print(f"Error calling API: {e}")
        return None

def create_corrected_xlsx(card_names, import_result, output_filepath):
    """Create a new XLSX with corrected card details."""
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Corrected Card List"
    
    # Headers
    headers = ["Original Name", "Corrected Name", "Status", "Imported At"]
    ws.append(headers)
    
    # Add data
    if import_result and "card_names" in import_result:
        imported_cards = import_result["card_names"]
        
        for i, original_name in enumerate(card_names):
            if i < len(imported_cards):
                corrected_name = imported_cards[i]
                ws.append([
                    original_name,
                    corrected_name,
                    "Imported",
                    datetime.now().isoformat()
                ])
            else:
                ws.append([
                    original_name,
                    "Not Found",
                    "Failed",
                    datetime.now().isoformat()
                ])
    else:
        # If API failed, just copy original names
        for name in card_names:
            ws.append([
                name,
                name,
                "API Error",
                datetime.now().isoformat()
            ])
    
    # Save
    wb.save(output_filepath)
    print(f"Saved corrected card list to: {output_filepath}")

def main():
    print("Reading card names from XLSX...")
    card_names = read_card_names_from_xlsx(INPUT_FILE)
    print(f"Found {len(card_names)} card names")
    
    if not card_names:
        print("No card names found in the file")
        return
    
    print(f"First 5 card names: {card_names[:5]}")
    
    print("\nCalling card import API...")
    import_result = call_card_import_api(card_names)
    
    if import_result:
        print(f"API Response: {json.dumps(import_result, indent=2)}")
        create_corrected_xlsx(card_names, import_result, OUTPUT_FILE)
    else:
        print("API call failed. Creating output with original names.")
        create_corrected_xlsx(card_names, None, OUTPUT_FILE)

if __name__ == "__main__":
    main()