"""
ATD sensor data processing module.

Functions for loading and structuring ATD sensor data from database.
Handles RL (Radial Link), LL (Load Link), and other extensometer types.
"""

import numpy as np
from typing import Tuple, Optional, List
from datetime import datetime
from scipy.signal import medfilt


def load_radial_link_data(conn, control_unit_id: str, chain: str,
                          initial_date: str, initial_time: str,
                          node_list: List[int]) -> Optional[np.ndarray]:
    """
    Load Radial Link raw data from RawDataView table.
    
    RL sensors measure 3D acceleration and magnetic field (MEMS + magnetometer).
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        initial_date: Starting date (YYYY-MM-DD)
        initial_time: Starting time (HH:MM:SS)
        node_list: List of RL node IDs
        
    Returns:
        Raw data array with columns: [timestamp, node_id, ax, ay, az, mx, my, mz, temp, err]
    """
    try:
        # Query for each RL node
        all_data = []
        
        for node_id in node_list:
            query = """
                SELECT Date, Time, 
                       Val0, Val1, Val2,  -- acceleration X, Y, Z
                       Val3, Val4, Val5,  -- magnetic field X, Y, Z
                       Val6               -- temperature
                FROM RawDataView
                WHERE UnitName = %s AND ToolNameID = %s
                  AND NodeType = 'RL' AND NodeNum = %s
                  AND ((Date = %s AND Time >= %s) OR (Date > %s))
                ORDER BY Date, Time
            """
            
            results = conn.execute_query(query, (control_unit_id, chain, node_id,
                                                 initial_date, initial_time, initial_date))
            
            if results:
                for row in results:
                    timestamp = datetime.combine(row['Date'], row['Time'])
                    all_data.append([
                        timestamp, node_id,
                        row['Val0'], row['Val1'], row['Val2'],  # ax, ay, az
                        row['Val3'], row['Val4'], row['Val5'],  # mx, my, mz
                        row['Val6'],  # temperature
                        0.0  # error flag
                    ])
        
        if all_data:
            return np.array(all_data, dtype=object)
        return None
        
    except Exception as e:
        raise Exception(f"Error loading RL data: {e}")


def define_radial_link_data(raw_data: np.ndarray, n_sensors: int,
                            n_despike: int, temp_max: float, temp_min: float
                            ) -> Tuple[np.ndarray, np.ndarray, np.ndarray, 
                                      np.ndarray, np.ndarray, np.ndarray]:
    """
    Structure RL data with NaN handling, despiking, and validation.
    
    Args:
        raw_data: Raw data array from load_radial_link_data
        n_sensors: Number of RL sensors
        n_despike: Window size for median filter despiking
        temp_max: Maximum valid temperature
        temp_min: Minimum valid temperature
        
    Returns:
        Tuple of (acceleration, magnetic_field, timestamps, temperature, err_flag, resultant_vectors)
        - acceleration: (n_timestamps, n_sensors*3) array for ax, ay, az
        - magnetic_field: (n_timestamps, n_sensors*3) array for mx, my, mz
        - timestamps: (n_timestamps,) datetime array
        - temperature: (n_timestamps, n_sensors) array
        - err_flag: (n_timestamps, n_sensors) error flags
        - resultant_vectors: (n_timestamps, n_sensors, 2) for [acc_magnitude, mag_magnitude]
    """
    if raw_data is None or len(raw_data) == 0:
        return None, None, None, None, None, None
    
    # Get unique timestamps
    timestamps = np.unique(raw_data[:, 0])
    n_timestamps = len(timestamps)
    
    # Initialize arrays
    acceleration = np.zeros((n_timestamps, n_sensors * 3))
    magnetic_field = np.zeros((n_timestamps, n_sensors * 3))
    temperature = np.zeros((n_timestamps, n_sensors))
    err_flag = np.zeros((n_timestamps, n_sensors))
    
    # Fill data by node
    for sensor_idx in range(n_sensors):
        node_id = int(raw_data[sensor_idx * n_timestamps, 1]) if sensor_idx * n_timestamps < len(raw_data) else 0
        node_mask = raw_data[:, 1] == node_id
        node_data = raw_data[node_mask]
        
        # Extract acceleration (columns 2, 3, 4)
        acceleration[:, sensor_idx*3] = node_data[:, 2]      # ax
        acceleration[:, sensor_idx*3+1] = node_data[:, 3]    # ay
        acceleration[:, sensor_idx*3+2] = node_data[:, 4]    # az
        
        # Extract magnetic field (columns 5, 6, 7)
        magnetic_field[:, sensor_idx*3] = node_data[:, 5]    # mx
        magnetic_field[:, sensor_idx*3+1] = node_data[:, 6]  # my
        magnetic_field[:, sensor_idx*3+2] = node_data[:, 7]  # mz
        
        # Extract temperature (column 8)
        temperature[:, sensor_idx] = node_data[:, 8]
        
        # Temperature validation with forward fill
        temp_valid = (temperature[:, sensor_idx] >= temp_min) & (temperature[:, sensor_idx] <= temp_max)
        if not np.all(temp_valid):
            err_flag[~temp_valid, sensor_idx] = 0.5
            for i in range(1, n_timestamps):
                if not temp_valid[i]:
                    temperature[i, sensor_idx] = temperature[i-1, sensor_idx]
    
    # Despike acceleration and magnetic field
    if n_despike > 1:
        for col in range(n_sensors * 3):
            acceleration[:, col] = medfilt(acceleration[:, col], kernel_size=n_despike)
            magnetic_field[:, col] = medfilt(magnetic_field[:, col], kernel_size=n_despike)
    
    # Calculate resultant vectors (magnitude)
    resultant_vectors = np.zeros((n_timestamps, n_sensors, 2))
    for sensor_idx in range(n_sensors):
        # Acceleration magnitude
        ax = acceleration[:, sensor_idx*3]
        ay = acceleration[:, sensor_idx*3+1]
        az = acceleration[:, sensor_idx*3+2]
        resultant_vectors[:, sensor_idx, 0] = np.sqrt(ax**2 + ay**2 + az**2)
        
        # Magnetic field magnitude
        mx = magnetic_field[:, sensor_idx*3]
        my = magnetic_field[:, sensor_idx*3+1]
        mz = magnetic_field[:, sensor_idx*3+2]
        resultant_vectors[:, sensor_idx, 1] = np.sqrt(mx**2 + my**2 + mz**2)
    
    return acceleration, magnetic_field, timestamps, temperature, err_flag, resultant_vectors


def load_load_link_data(conn, control_unit_id: str, chain: str,
                       initial_date: str, initial_time: str,
                       node_list: List[int]) -> Optional[np.ndarray]:
    """
    Load Load Link raw data from RawDataView table.
    
    LL sensors measure force/load.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        initial_date: Starting date
        initial_time: Starting time
        node_list: List of LL node IDs
        
    Returns:
        Raw data array with columns: [timestamp, node_id, force, temp, err]
    """
    try:
        all_data = []
        
        for node_id in node_list:
            query = """
                SELECT Date, Time, Val0, Val1
                FROM RawDataView
                WHERE UnitName = %s AND ToolNameID = %s
                  AND NodeType = 'LL' AND NodeNum = %s
                  AND ((Date = %s AND Time >= %s) OR (Date > %s))
                ORDER BY Date, Time
            """
            
            results = conn.execute_query(query, (control_unit_id, chain, node_id,
                                                 initial_date, initial_time, initial_date))
            
            if results:
                for row in results:
                    timestamp = datetime.combine(row['Date'], row['Time'])
                    all_data.append([
                        timestamp, node_id,
                        row['Val0'],  # force
                        row['Val1'],  # temperature
                        0.0  # error flag
                    ])
        
        if all_data:
            return np.array(all_data, dtype=object)
        return None
        
    except Exception as e:
        raise Exception(f"Error loading LL data: {e}")


def define_load_link_data(raw_data: np.ndarray, n_sensors: int,
                         n_despike: int, temp_max: float, temp_min: float
                         ) -> Tuple[np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
    """
    Structure LL data with NaN handling and validation.
    
    Args:
        raw_data: Raw data array from load_load_link_data
        n_sensors: Number of LL sensors
        n_despike: Window size for despiking
        temp_max: Maximum valid temperature
        temp_min: Minimum valid temperature
        
    Returns:
        Tuple of (force_data, timestamps, temperature, err_flag)
    """
    if raw_data is None or len(raw_data) == 0:
        return None, None, None, None
    
    timestamps = np.unique(raw_data[:, 0])
    n_timestamps = len(timestamps)
    
    force_data = np.zeros((n_timestamps, n_sensors))
    temperature = np.zeros((n_timestamps, n_sensors))
    err_flag = np.zeros((n_timestamps, n_sensors))
    
    for sensor_idx in range(n_sensors):
        node_id = int(raw_data[sensor_idx * n_timestamps, 1]) if sensor_idx * n_timestamps < len(raw_data) else 0
        node_mask = raw_data[:, 1] == node_id
        node_data = raw_data[node_mask]
        
        force_data[:, sensor_idx] = node_data[:, 2]
        temperature[:, sensor_idx] = node_data[:, 3]
        
        # Temperature validation
        temp_valid = (temperature[:, sensor_idx] >= temp_min) & (temperature[:, sensor_idx] <= temp_max)
        if not np.all(temp_valid):
            err_flag[~temp_valid, sensor_idx] = 0.5
            for i in range(1, n_timestamps):
                if not temp_valid[i]:
                    temperature[i, sensor_idx] = temperature[i-1, sensor_idx]
    
    # Despike
    if n_despike > 1:
        for col in range(n_sensors):
            force_data[:, col] = medfilt(force_data[:, col], kernel_size=n_despike)
    
    return force_data, timestamps, temperature, err_flag


def load_pressure_link_data(conn, control_unit_id: str, chain: str,
                            initial_date: str, initial_time: str,
                            node_list: List[int]) -> Optional[np.ndarray]:
    """
    Load Pressure Link raw data from RawDataView table.
    
    PL sensors measure pressure.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        initial_date: Starting date
        initial_time: Starting time
        node_list: List of PL node IDs
        
    Returns:
        Raw data array with columns: [timestamp, node_id, pressure, temp, err]
    """
    try:
        all_data = []
        
        for node_id in node_list:
            query = """
                SELECT Date, Time, Val0, Val1
                FROM RawDataView
                WHERE UnitName = %s AND ToolNameID = %s
                  AND NodeType = 'PL' AND NodeNum = %s
                  AND ((Date = %s AND Time >= %s) OR (Date > %s))
                ORDER BY Date, Time
            """
            
            results = conn.execute_query(query, (control_unit_id, chain, node_id,
                                                 initial_date, initial_time, initial_date))
            
            if results:
                for row in results:
                    timestamp = datetime.combine(row['Date'], row['Time'])
                    all_data.append([
                        timestamp, node_id,
                        row['Val0'],  # pressure
                        row['Val1'],  # temperature
                        0.0  # error flag
                    ])
        
        if all_data:
            return np.array(all_data, dtype=object)
        return None
        
    except Exception as e:
        raise Exception(f"Error loading PL data: {e}")


def define_pressure_link_data(raw_data: np.ndarray, n_sensors: int,
                              n_despike: int, temp_max: float, temp_min: float
                              ) -> Tuple[np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
    """
    Structure PL data with NaN handling and validation.
    
    Args:
        raw_data: Raw data array from load_pressure_link_data
        n_sensors: Number of PL sensors
        n_despike: Window size for despiking
        temp_max: Maximum valid temperature
        temp_min: Minimum valid temperature
        
    Returns:
        Tuple of (pressure_data, timestamps, temperature, err_flag)
    """
    if raw_data is None or len(raw_data) == 0:
        return None, None, None, None
    
    timestamps = np.unique(raw_data[:, 0])
    n_timestamps = len(timestamps)
    
    pressure_data = np.zeros((n_timestamps, n_sensors))
    temperature = np.zeros((n_timestamps, n_sensors))
    err_flag = np.zeros((n_timestamps, n_sensors))
    
    for sensor_idx in range(n_sensors):
        node_id = int(raw_data[sensor_idx * n_timestamps, 1]) if sensor_idx * n_timestamps < len(raw_data) else 0
        node_mask = raw_data[:, 1] == node_id
        node_data = raw_data[node_mask]
        
        pressure_data[:, sensor_idx] = node_data[:, 2]
        temperature[:, sensor_idx] = node_data[:, 3]
        
        # Temperature validation
        temp_valid = (temperature[:, sensor_idx] >= temp_min) & (temperature[:, sensor_idx] <= temp_max)
        if not np.all(temp_valid):
            err_flag[~temp_valid, sensor_idx] = 0.5
            for i in range(1, n_timestamps):
                if not temp_valid[i]:
                    temperature[i, sensor_idx] = temperature[i-1, sensor_idx]
    
    # Despike
    if n_despike > 1:
        for col in range(n_sensors):
            pressure_data[:, col] = medfilt(pressure_data[:, col], kernel_size=n_despike)
    
    return pressure_data, timestamps, temperature, err_flag


def load_extensometer_3d_data(conn, control_unit_id: str, chain: str,
                              initial_date: str, initial_time: str,
                              node_list: List[int]) -> Optional[np.ndarray]:
    """
    Load 3D Extensometer (3DEL) raw data from RawDataView table.
    
    3DEL sensors measure 3D displacements.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        initial_date: Starting date
        initial_time: Starting time
        node_list: List of 3DEL node IDs
        
    Returns:
        Raw data array with columns: [timestamp, node_id, dx, dy, dz, temp, err]
    """
    try:
        all_data = []
        
        for node_id in node_list:
            query = """
                SELECT Date, Time, Val0, Val1, Val2, Val3
                FROM RawDataView
                WHERE UnitName = %s AND ToolNameID = %s
                  AND NodeType = '3DEL' AND NodeNum = %s
                  AND ((Date = %s AND Time >= %s) OR (Date > %s))
                ORDER BY Date, Time
            """
            
            results = conn.execute_query(query, (control_unit_id, chain, node_id,
                                                 initial_date, initial_time, initial_date))
            
            if results:
                for row in results:
                    timestamp = datetime.combine(row['Date'], row['Time'])
                    all_data.append([
                        timestamp, node_id,
                        row['Val0'],  # displacement X
                        row['Val1'],  # displacement Y
                        row['Val2'],  # displacement Z
                        row['Val3'],  # temperature
                        0.0  # error flag
                    ])
        
        if all_data:
            return np.array(all_data, dtype=object)
        return None
        
    except Exception as e:
        raise Exception(f"Error loading 3DEL data: {e}")


def define_extensometer_3d_data(raw_data: np.ndarray, n_sensors: int,
                                n_despike: int, temp_max: float, temp_min: float
                                ) -> Tuple[np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
    """
    Structure 3DEL data with NaN handling and validation.
    
    Args:
        raw_data: Raw data array from load_extensometer_3d_data
        n_sensors: Number of 3DEL sensors
        n_despike: Window size for despiking
        temp_max: Maximum valid temperature
        temp_min: Minimum valid temperature
        
    Returns:
        Tuple of (displacement_data, timestamps, temperature, err_flag)
        displacement_data has shape (n_timestamps, n_sensors*3) for X, Y, Z
    """
    if raw_data is None or len(raw_data) == 0:
        return None, None, None, None
    
    timestamps = np.unique(raw_data[:, 0])
    n_timestamps = len(timestamps)
    
    displacement_data = np.zeros((n_timestamps, n_sensors * 3))
    temperature = np.zeros((n_timestamps, n_sensors))
    err_flag = np.zeros((n_timestamps, n_sensors))
    
    for sensor_idx in range(n_sensors):
        node_id = int(raw_data[sensor_idx * n_timestamps, 1]) if sensor_idx * n_timestamps < len(raw_data) else 0
        node_mask = raw_data[:, 1] == node_id
        node_data = raw_data[node_mask]
        
        # X, Y, Z displacements
        displacement_data[:, sensor_idx*3] = node_data[:, 2]
        displacement_data[:, sensor_idx*3+1] = node_data[:, 3]
        displacement_data[:, sensor_idx*3+2] = node_data[:, 4]
        
        temperature[:, sensor_idx] = node_data[:, 5]
        
        # Temperature validation
        temp_valid = (temperature[:, sensor_idx] >= temp_min) & (temperature[:, sensor_idx] <= temp_max)
        if not np.all(temp_valid):
            err_flag[~temp_valid, sensor_idx] = 0.5
            for i in range(1, n_timestamps):
                if not temp_valid[i]:
                    temperature[i, sensor_idx] = temperature[i-1, sensor_idx]
    
    # Despike
    if n_despike > 1:
        for col in range(n_sensors * 3):
            displacement_data[:, col] = medfilt(displacement_data[:, col], kernel_size=n_despike)
    
    return displacement_data, timestamps, temperature, err_flag


def load_crackmeter_data(conn, control_unit_id: str, chain: str,
                        initial_date: str, initial_time: str,
                        node_list: List[int], sensor_type: str = 'CrL'
                        ) -> Optional[np.ndarray]:
    """
    Load Crackmeter (CrL, 2DCrL, 3DCrL) raw data from RawDataView table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        initial_date: Starting date
        initial_time: Starting time
        node_list: List of CrL node IDs
        sensor_type: 'CrL' (1D), '2DCrL' (2D), or '3DCrL' (3D)
        
    Returns:
        Raw data array
    """
    try:
        all_data = []
        
        for node_id in node_list:
            if sensor_type == '3DCrL':
                query = """
                    SELECT Date, Time, Val0, Val1, Val2, Val3
                    FROM RawDataView
                    WHERE UnitName = %s AND ToolNameID = %s
                      AND NodeType = %s AND NodeNum = %s
                      AND ((Date = %s AND Time >= %s) OR (Date > %s))
                    ORDER BY Date, Time
                """
            elif sensor_type == '2DCrL':
                query = """
                    SELECT Date, Time, Val0, Val1, Val2
                    FROM RawDataView
                    WHERE UnitName = %s AND ToolNameID = %s
                      AND NodeType = %s AND NodeNum = %s
                      AND ((Date = %s AND Time >= %s) OR (Date > %s))
                    ORDER BY Date, Time
                """
            else:  # CrL (1D)
                query = """
                    SELECT Date, Time, Val0, Val1
                    FROM RawDataView
                    WHERE UnitName = %s AND ToolNameID = %s
                      AND NodeType = %s AND NodeNum = %s
                      AND ((Date = %s AND Time >= %s) OR (Date > %s))
                    ORDER BY Date, Time
                """
            
            results = conn.execute_query(query, (control_unit_id, chain, sensor_type, node_id,
                                                 initial_date, initial_time, initial_date))
            
            if results:
                for row in results:
                    timestamp = datetime.combine(row['Date'], row['Time'])
                    if sensor_type == '3DCrL':
                        all_data.append([timestamp, node_id, row['Val0'], row['Val1'], row['Val2'], row['Val3'], 0.0])
                    elif sensor_type == '2DCrL':
                        all_data.append([timestamp, node_id, row['Val0'], row['Val1'], row['Val2'], 0.0])
                    else:
                        all_data.append([timestamp, node_id, row['Val0'], row['Val1'], 0.0])
        
        if all_data:
            return np.array(all_data, dtype=object)
        return None
        
    except Exception as e:
        raise Exception(f"Error loading {sensor_type} data: {e}")


def define_crackmeter_data(raw_data: np.ndarray, n_sensors: int, n_dimensions: int,
                          n_despike: int, temp_max: float, temp_min: float
                          ) -> Tuple[np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
    """
    Structure crackmeter data.
    
    Args:
        raw_data: Raw data array
        n_sensors: Number of sensors
        n_dimensions: 1, 2, or 3 for CrL, 2DCrL, 3DCrL
        n_despike: Window size for despiking
        temp_max: Maximum valid temperature
        temp_min: Minimum valid temperature
        
    Returns:
        Tuple of (displacement_data, timestamps, temperature, err_flag)
    """
    if raw_data is None or len(raw_data) == 0:
        return None, None, None, None
    
    timestamps = np.unique(raw_data[:, 0])
    n_timestamps = len(timestamps)
    
    displacement_data = np.zeros((n_timestamps, n_sensors * n_dimensions))
    temperature = np.zeros((n_timestamps, n_sensors))
    err_flag = np.zeros((n_timestamps, n_sensors))
    
    for sensor_idx in range(n_sensors):
        node_id = int(raw_data[sensor_idx * n_timestamps, 1]) if sensor_idx * n_timestamps < len(raw_data) else 0
        node_mask = raw_data[:, 1] == node_id
        node_data = raw_data[node_mask]
        
        for dim in range(n_dimensions):
            displacement_data[:, sensor_idx*n_dimensions+dim] = node_data[:, 2+dim]
        
        temperature[:, sensor_idx] = node_data[:, 2+n_dimensions]
        
        # Temperature validation
        temp_valid = (temperature[:, sensor_idx] >= temp_min) & (temperature[:, sensor_idx] <= temp_max)
        if not np.all(temp_valid):
            err_flag[~temp_valid, sensor_idx] = 0.5
            for i in range(1, n_timestamps):
                if not temp_valid[i]:
                    temperature[i, sensor_idx] = temperature[i-1, sensor_idx]
    
    # Despike
    if n_despike > 1:
        for col in range(n_sensors * n_dimensions):
            displacement_data[:, col] = medfilt(displacement_data[:, col], kernel_size=n_despike)
    
    return displacement_data, timestamps, temperature, err_flag


def load_pcl_data(conn, control_unit_id: str, chain: str,
                 initial_date: str, initial_time: str,
                 node_list: List[int], sensor_type: str = 'PCL') -> Optional[np.ndarray]:
    """
    Load Perimeter Cable Link (PCL/PCLHR) raw data from RawDataView table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        initial_date: Starting date
        initial_time: Starting time
        node_list: List of PCL node IDs
        sensor_type: 'PCL' or 'PCLHR'
        
    Returns:
        Raw data array with columns: [timestamp, node_id, ax, ay, temp, err]
    """
    try:
        all_data = []
        
        for node_id in node_list:
            query = """
                SELECT Date, Time, Val0, Val1, Val2
                FROM RawDataView
                WHERE UnitName = %s AND ToolNameID = %s
                  AND NodeType = %s AND NodeNum = %s
                  AND ((Date = %s AND Time >= %s) OR (Date > %s))
                ORDER BY Date, Time
            """
            
            results = conn.execute_query(query, (control_unit_id, chain, sensor_type, node_id,
                                                 initial_date, initial_time, initial_date))
            
            if results:
                for row in results:
                    timestamp = datetime.combine(row['Date'], row['Time'])
                    all_data.append([
                        timestamp, node_id,
                        row['Val0'],  # ax (angle X)
                        row['Val1'],  # ay (angle Y)
                        row['Val2'],  # temperature
                        0.0  # error flag
                    ])
        
        if all_data:
            return np.array(all_data, dtype=object)
        return None
        
    except Exception as e:
        raise Exception(f"Error loading {sensor_type} data: {e}")


def define_pcl_data(raw_data: np.ndarray, n_sensors: int,
                   n_despike: int, temp_max: float, temp_min: float
                   ) -> Tuple[np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
    """
    Structure PCL data with NaN handling and validation.
    
    Args:
        raw_data: Raw data array from load_pcl_data
        n_sensors: Number of PCL sensors
        n_despike: Window size for despiking
        temp_max: Maximum valid temperature
        temp_min: Minimum valid temperature
        
    Returns:
        Tuple of (angle_data, timestamps, temperature, err_flag)
        angle_data has shape (n_timestamps, n_sensors*2) for ax, ay
    """
    if raw_data is None or len(raw_data) == 0:
        return None, None, None, None
    
    timestamps = np.unique(raw_data[:, 0])
    n_timestamps = len(timestamps)
    
    angle_data = np.zeros((n_timestamps, n_sensors * 2))
    temperature = np.zeros((n_timestamps, n_sensors))
    err_flag = np.zeros((n_timestamps, n_sensors))
    
    for sensor_idx in range(n_sensors):
        node_id = int(raw_data[sensor_idx * n_timestamps, 1]) if sensor_idx * n_timestamps < len(raw_data) else 0
        node_mask = raw_data[:, 1] == node_id
        node_data = raw_data[node_mask]
        
        # Extract angles
        angle_data[:, sensor_idx*2] = node_data[:, 2]      # ax
        angle_data[:, sensor_idx*2+1] = node_data[:, 3]    # ay
        
        temperature[:, sensor_idx] = node_data[:, 4]
        
        # Temperature validation
        temp_valid = (temperature[:, sensor_idx] >= temp_min) & (temperature[:, sensor_idx] <= temp_max)
        if not np.all(temp_valid):
            err_flag[~temp_valid, sensor_idx] = 0.5
            for i in range(1, n_timestamps):
                if not temp_valid[i]:
                    temperature[i, sensor_idx] = temperature[i-1, sensor_idx]
    
    # Despike
    if n_despike > 1:
        for col in range(n_sensors * 2):
            angle_data[:, col] = medfilt(angle_data[:, col], kernel_size=n_despike)
    
    return angle_data, timestamps, temperature, err_flag


def load_tube_link_data(conn, control_unit_id: str, chain: str,
                       initial_date: str, initial_time: str,
                       node_list: List[int]) -> Optional[np.ndarray]:
    """
    Load Tube Link (TuL) raw data from RawDataView table.
    
    TuL sensors measure 3D angles for tunnel monitoring.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        initial_date: Starting date
        initial_time: Starting time
        node_list: List of TuL node IDs
        
    Returns:
        Raw data array with columns: [timestamp, node_id, ax, ay, az, temp, err]
    """
    try:
        all_data = []
        
        for node_id in node_list:
            query = """
                SELECT Date, Time, Val0, Val1, Val2, Val3
                FROM RawDataView
                WHERE UnitName = %s AND ToolNameID = %s
                  AND NodeType = 'TuL' AND NodeNum = %s
                  AND ((Date = %s AND Time >= %s) OR (Date > %s))
                ORDER BY Date, Time
            """
            
            results = conn.execute_query(query, (control_unit_id, chain, node_id,
                                                 initial_date, initial_time, initial_date))
            
            if results:
                for row in results:
                    timestamp = datetime.combine(row['Date'], row['Time'])
                    all_data.append([
                        timestamp, node_id,
                        row['Val0'],  # ax (angle X)
                        row['Val1'],  # ay (angle Y)
                        row['Val2'],  # az (angle Z - correlation)
                        row['Val3'],  # temperature
                        0.0  # error flag
                    ])
        
        if all_data:
            return np.array(all_data, dtype=object)
        return None
        
    except Exception as e:
        raise Exception(f"Error loading TuL data: {e}")


def define_tube_link_data(raw_data: np.ndarray, n_sensors: int,
                         n_despike: int, temp_max: float, temp_min: float
                         ) -> Tuple[np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
    """
    Structure TuL data with NaN handling and validation.
    
    Args:
        raw_data: Raw data array from load_tube_link_data
        n_sensors: Number of TuL sensors
        n_despike: Window size for despiking
        temp_max: Maximum valid temperature
        temp_min: Minimum valid temperature
        
    Returns:
        Tuple of (angle_data, timestamps, temperature, err_flag)
        angle_data has shape (n_timestamps, n_sensors*3) for ax, ay, az
    """
    if raw_data is None or len(raw_data) == 0:
        return None, None, None, None
    
    timestamps = np.unique(raw_data[:, 0])
    n_timestamps = len(timestamps)
    
    angle_data = np.zeros((n_timestamps, n_sensors * 3))
    temperature = np.zeros((n_timestamps, n_sensors))
    err_flag = np.zeros((n_timestamps, n_sensors))
    
    for sensor_idx in range(n_sensors):
        node_id = int(raw_data[sensor_idx * n_timestamps, 1]) if sensor_idx * n_timestamps < len(raw_data) else 0
        node_mask = raw_data[:, 1] == node_id
        node_data = raw_data[node_mask]
        
        # Extract 3D angles
        angle_data[:, sensor_idx*3] = node_data[:, 2]      # ax
        angle_data[:, sensor_idx*3+1] = node_data[:, 3]    # ay
        angle_data[:, sensor_idx*3+2] = node_data[:, 4]    # az (correlation)
        
        temperature[:, sensor_idx] = node_data[:, 5]
        
        # Temperature validation
        temp_valid = (temperature[:, sensor_idx] >= temp_min) & (temperature[:, sensor_idx] <= temp_max)
        if not np.all(temp_valid):
            err_flag[~temp_valid, sensor_idx] = 0.5
            for i in range(1, n_timestamps):
                if not temp_valid[i]:
                    temperature[i, sensor_idx] = temperature[i-1, sensor_idx]
    
    # Despike
    if n_despike > 1:
        for col in range(n_sensors * 3):
            angle_data[:, col] = medfilt(angle_data[:, col], kernel_size=n_despike)
    
    return angle_data, timestamps, temperature, err_flag
