#!/usr/bin/env python3
"""
Test script to verify Excel to CSV conversion functionality
"""

import pandas as pd
import os
import sys
from csv_utils import robust_csv_loader, is_excel_file

def test_excel_detection():
    """Test the Excel file detection function."""
    print("=== Testing Excel File Detection ===")
    
    test_files = [
        "test.csv",
        "test.xls", 
        "test.xlsx",
        "test.xlsm",
        "test.txt",
        "test.XLSX",  # Test case sensitivity
        "file.with.dots.xlsx"
    ]
    
    for filename in test_files:
        result = is_excel_file(filename)
        print(f"{filename}: {'Excel' if result else 'Not Excel'}")
    
    print()

def test_excel_loading():
    """Test loading Excel files through the robust loader."""
    print("=== Testing Excel File Loading ===")
    
    # Create a sample Excel file for testing
    test_data = pd.DataFrame({
        'Email': ['user1@example.com', 'user2@example.com', 'user3@example.com'],
        'FY23/24 全年IPM': ['100%', '120%', '95%'],
        'FY24/25 全年IPM': ['110%', '125%', '98%']
    })
    
    # Save as Excel
    excel_path = 'test_kpi.xlsx'
    test_data.to_excel(excel_path, index=False)
    print(f"Created test Excel file: {excel_path}")
    
    try:
        # Test loading the Excel file
        print("\nLoading Excel file with robust_csv_loader...")
        df = robust_csv_loader(excel_path, required_columns=['Email'])
        
        print(f"\nSuccessfully loaded {len(df)} rows")
        print(f"Columns: {df.columns.tolist()}")
        print("\nFirst few rows:")
        print(df.head())
        
    except Exception as e:
        print(f"Error loading Excel file: {e}")
    
    finally:
        # Clean up test file
        if os.path.exists(excel_path):
            os.remove(excel_path)
            print(f"\nCleaned up test file: {excel_path}")

def main():
    """Run all tests."""
    test_excel_detection()
    test_excel_loading()
    
    print("\n=== Testing analyze_correlations_v2.py with Excel files ===")
    print("The analyze_correlations_v2.py script now supports Excel files!")
    print("You can run it with Excel files like this:")
    print("  python3 analyze_correlations_v2.py -k kpi_data.xlsx -s scores.csv")
    print("  python3 analyze_correlations_v2.py -k kpi_data.xls -s scores.xlsx")
    print("\nThe script will automatically detect Excel files and convert them to CSV for processing.")

if __name__ == "__main__":
    main()