#!/usr/bin/env python
"""
Script to inspect the new sensor report format and compare it with the old one
"""
import pandas as pd

def inspect_new_sensor_report():
    print("Inspecting new sensor report: SensorReport Cardinal 2025-12-23_processed.xlsx")
    
    try:
        # Try to read the new sensor report with different header options
        print("\nTrying to read with header=4 (same as old format)...")
        new_sensor_df = pd.read_excel('SensorReport Cardinal 2025-12-23_processed.xlsx', header=4)
        
        print(f"New sensor report shape: {new_sensor_df.shape}")
        print(f"New sensor report columns: {list(new_sensor_df.columns)}")
        
        print("\nFirst few rows of new sensor report:")
        print(new_sensor_df.head())
        
        print("\nData types of columns:")
        print(new_sensor_df.dtypes)
        
        # Check for key columns that are expected by the current code
        expected_cols = ['ID', 'Remote', 'Group', 'Type', 'Serial No', 'Name']
        print(f"\nChecking for expected columns: {expected_cols}")
        for col in expected_cols:
            if col in new_sensor_df.columns:
                print(f"  [OK] {col}: Present")
            else:
                print(f"  [MISSING] {col}: Missing")
        
        # Look at a sample of the data to understand its structure
        print(f"\nSample data for first 10 rows:")
        sample_cols = [col for col in expected_cols if col in new_sensor_df.columns]
        if sample_cols:
            print(new_sensor_df[sample_cols].head(10))
        
        # Try different header values to see if the structure is different
        print("\nTrying with header=0 (first row)...")
        new_sensor_df_h0 = pd.read_excel('SensorReport Cardinal 2025-12-23_processed.xlsx', header=0)
        print(f"With header=0 - Shape: {new_sensor_df_h0.shape}, Columns: {list(new_sensor_df_h0.columns[:10])}")  # First 10 columns
        
        print("\nTrying with header=3...")
        new_sensor_df_h3 = pd.read_excel('SensorReport Cardinal 2025-12-23_processed.xlsx', header=3)
        print(f"With header=3 - Shape: {new_sensor_df_h3.shape}, Columns: {list(new_sensor_df_h3.columns[:10])}")
        
        # Also try to see the first few rows without setting a header
        print("\nFirst few rows without setting header (to see raw structure):")
        raw_df = pd.read_excel('SensorReport Cardinal 2025-12-23_processed.xlsx', header=None)
        print(raw_df.head(10))
        
    except Exception as e:
        print(f"Error reading new sensor report: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    inspect_new_sensor_report()