#!/usr/bin/env python3
"""
Simple script to check Excel file structure
"""

import pandas as pd
import os

def check_excel_file():
    """Check the Excel file structure"""
    
    print("📊 Checking Excel File Structure")
    print("=" * 40)
    
    # Check if file exists
    file_path = "uploads/rolecall.xlsx"
    
    if not os.path.exists(file_path):
        print(f"❌ File not found: {file_path}")
        return
    
    print(f"✅ File found: {file_path}")
    print(f"   File size: {os.path.getsize(file_path)} bytes")
    
    try:
        # Read just the first few rows to check structure
        df = pd.read_excel(file_path, engine='openpyxl', nrows=5)
        
        print(f"\n📋 File Structure:")
        print(f"   Total columns: {len(df.columns)}")
        print(f"   Sample rows: {len(df)}")
        
        print(f"\n📝 All columns:")
        for i, col in enumerate(df.columns):
            print(f"   {i+1:2d}. '{col}'")
        
        # Check for required columns
        required_columns = [
            "Payroll Number",
            "Employee Name", 
            "License Number",
            "License Code",
            "Start Date",
            "Expiry/Update  Date",
            "State"
        ]
        
        print(f"\n🎯 Required columns check:")
        available_columns = []
        missing_columns = []
        
        for col in required_columns:
            if col in df.columns:
                available_columns.append(col)
                print(f"   ✅ '{col}' - Found")
            else:
                missing_columns.append(col)
                print(f"   ❌ '{col}' - Missing")
        
        print(f"\n📊 Summary:")
        print(f"   Available required columns: {len(available_columns)}/{len(required_columns)}")
        print(f"   Missing columns: {missing_columns}")
        
        if available_columns:
            print(f"\n🔍 Sample data from available columns:")
            sample_df = df[available_columns]
            print(sample_df.to_string(index=False))
        
    except Exception as e:
        print(f"❌ Error reading Excel file: {e}")

if __name__ == "__main__":
    check_excel_file()