import pandas as pd
import os
import sys
import re

from utils import normalize_io_term

def load_io_path_mappings():
    """Load IO path mappings from IO-To-Path.xlsx"""
    io_path_file = "IO-To-Path.xlsx"
    
    if not os.path.exists(io_path_file):
        print(f"CRITICAL: IO path file not found: {io_path_file}")
        sys.exit(1)
    
    try:
        # Read all sheets from IO-To-Path.xlsx
        apf_df = pd.read_excel(io_path_file, sheet_name='APF')
        m12dr_df = pd.read_excel(io_path_file, sheet_name='M12DR')
        hub_df = pd.read_excel(io_path_file, sheet_name='Hub')
        sorter_hub_df = pd.read_excel(io_path_file, sheet_name='SorterHub')
        sio_df = pd.read_excel(io_path_file, sheet_name='SIO')
        ib16_df = pd.read_excel(io_path_file, sheet_name='IB16')
        ob16e_df = pd.read_excel(io_path_file, sheet_name='OB16E')
        ib16s_df = pd.read_excel(io_path_file, sheet_name='IB16S')
        
        print(f"Loaded IO path mappings:")
        print(f"  APF: {len(apf_df)} rows")
        print(f"  M12DR: {len(m12dr_df)} rows") 
        print(f"  Hub: {len(hub_df)} rows")
        print(f"  SorterHub: {len(sorter_hub_df)} rows")
        print(f"  SIO: {len(sio_df)} rows")
        print(f"  IB16: {len(ib16_df)} rows")
        print(f"  OB16E: {len(ob16e_df)} rows")
        print(f"  IB16S: {len(ib16s_df)} rows")
        
        return apf_df, m12dr_df, hub_df, sorter_hub_df, sio_df, ib16_df, ob16e_df, ib16s_df
        
    except Exception as e:
        print(f"CRITICAL: Error loading IO path mappings: {e}")
        sys.exit(1)

def get_io_path(tagname, term, signal_type, device_type, apf_df, m12dr_df, hub_df, sorter_hub_df, sio_df, ib16_df, ob16e_df, ib16s_df):
    """Get IO path for a given tagname, term, and signal type"""
    
    if device_type == 'UNKNOWN' or pd.isna(term):
        return None
    
    # Select appropriate dataframe
    if device_type == 'APF':
        df = apf_df
    elif device_type == 'M12DR':
        df = m12dr_df
    elif device_type == 'Hub':
        # Check if the hub has S0 or VS in its name to determine which sheet to use
        if 'S0' in str(tagname).upper() or "VS" in str(tagname).upper():
            df = sorter_hub_df
        else:
            df = hub_df
    elif device_type == 'SIO':
        df = sio_df
    elif device_type == 'IB16':
        df = ib16_df
    elif device_type == 'OB16E':
        df = ob16e_df
    elif device_type == 'IB16S':
        df = ib16s_df
    else:
        return None
    
    if df is None:
        return None
    
    # Get term variations to handle IO1 vs IO01 inconsistencies
    term_variations = normalize_io_term(term)
    
    # Try to find matching row
    matching_row = None
    for term_var in term_variations:
        mask = df['IO'] == term_var
        if mask.any():
            matching_row = df[mask].iloc[0]
            break
    
    if matching_row is None:
        return None
    
    # Select appropriate path column based on term type first, then signal type
    path_value = None
    term_upper = str(term).upper()
    
    # Check term prefix first (SI/SO have dedicated columns)
    if term_upper.startswith('SI') and (device_type == 'APF' or device_type == 'SIO'):
        path_value = matching_row.get('SIPath')
    elif term_upper.startswith('SO') and (device_type == 'APF' or device_type == 'SIO'):
        path_value = matching_row.get('SOPath')
    elif signal_type == 'IOLink' and (device_type == 'M12DR' or device_type == 'SIO'):
        path_value = matching_row.get('IOLinkPath')
    # Then check signal type for regular I/O
    elif signal_type == 'I':
        path_value = matching_row.get('IPath')
    elif signal_type == 'O':
        path_value = matching_row.get('OPath')
    elif signal_type == 'SPARE':  # SPARE entries default to IPath, fallback to OPath
        path_value = matching_row.get('IPath')
        # If IPath is empty, try OPath as fallback
        if pd.isna(path_value) or path_value == '':
            path_value = matching_row.get('OPath')
    
    if pd.isna(path_value) or path_value == '':
        return None
    
    path_str = str(path_value)
    # Replace device prefix with actual tagname for non-local devices
    if device_type in ['APF', 'M12DR', 'Hub', 'SIO']:
        if device_type == 'APF':
            path_str = path_str.replace('APF', str(tagname))
        elif device_type == 'M12DR':
            path_str = path_str.replace('M12DR', str(tagname))
        elif device_type == 'Hub':
            path_str = path_str.replace('Hub', str(tagname))
        elif device_type == 'SIO':
            path_str = path_str.replace('SIO', str(tagname))
    else:
        # For local slot devices (IB16, OB16E, IB16S)
        slot_match = re.search(r'SLOT(\d+)', str(tagname).upper())
        if slot_match:
            slot = slot_match.group(1)
            path_str = f"Local:{slot}:{path_str}"
        else:
            return None
    
    return path_str