"""
ATD sensor database write module.

Writes elaborated ATD sensor data to database tables.
"""

import numpy as np
from typing import List
from datetime import datetime


def write_radial_link_data(conn, control_unit_id: str, chain: str,
                           x_global: np.ndarray, y_global: np.ndarray, z_global: np.ndarray,
                           x_local: np.ndarray, y_local: np.ndarray, z_local: np.ndarray,
                           x_diff: np.ndarray, y_diff: np.ndarray, z_diff: np.ndarray,
                           timestamps: np.ndarray, node_list: List[int],
                           temperature: np.ndarray, err_flag: np.ndarray) -> None:
    """
    Write RL elaborated data to ELABDATADISP table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        x_global, y_global, z_global: Global coordinates (n_timestamps, n_sensors)
        x_local, y_local, z_local: Local coordinates (n_timestamps, n_sensors)
        x_diff, y_diff, z_diff: Differential coordinates (n_timestamps, n_sensors)
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    # Check if data already exists in database
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATADISP
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            if record_exists:
                # Update existing record
                update_query = """
                    UPDATE ELABDATADISP
                    SET X = %s, Y = %s, Z = %s,
                        XShift = %s, YShift = %s, ZShift = %s,
                        T_node = %s, calcerr = %s
                    WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                      AND EventDate = %s AND EventTime = %s
                """
                
                conn.execute_update(update_query, (
                    float(x_global[t, sensor_idx]), float(y_global[t, sensor_idx]), float(z_global[t, sensor_idx]),
                    float(x_diff[t, sensor_idx]), float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx]),
                    control_unit_id, chain, node_id, date_str, time_str
                ))
            else:
                # Insert new record
                insert_query = """
                    INSERT INTO ELABDATADISP
                    (UnitName, ToolNameID, NodeNum, EventDate, EventTime,
                     X, Y, Z, XShift, YShift, ZShift, T_node, calcerr)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                conn.execute_update(insert_query, (
                    control_unit_id, chain, node_id, date_str, time_str,
                    float(x_global[t, sensor_idx]), float(y_global[t, sensor_idx]), float(z_global[t, sensor_idx]),
                    float(x_diff[t, sensor_idx]), float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                ))


def write_load_link_data(conn, control_unit_id: str, chain: str,
                        force: np.ndarray, force_diff: np.ndarray,
                        timestamps: np.ndarray, node_list: List[int],
                        temperature: np.ndarray, err_flag: np.ndarray) -> None:
    """
    Write LL elaborated data to ELABDATAFORCE table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        force: (n_timestamps, n_sensors) force data
        force_diff: (n_timestamps, n_sensors) differential force
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATAFORCE
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            if record_exists:
                # Update existing record
                update_query = """
                    UPDATE ELABDATAFORCE
                    SET Force = %s, ForceShift = %s, T_node = %s, calcerr = %s
                    WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                      AND EventDate = %s AND EventTime = %s
                """
                
                conn.execute_update(update_query, (
                    float(force[t, sensor_idx]), float(force_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx]),
                    control_unit_id, chain, node_id, date_str, time_str
                ))
            else:
                # Insert new record
                insert_query = """
                    INSERT INTO ELABDATAFORCE
                    (UnitName, ToolNameID, NodeNum, EventDate, EventTime,
                     Force, ForceShift, T_node, calcerr)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                conn.execute_update(insert_query, (
                    control_unit_id, chain, node_id, date_str, time_str,
                    float(force[t, sensor_idx]), float(force_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                ))


def write_pressure_link_data(conn, control_unit_id: str, chain: str,
                             pressure: np.ndarray, pressure_diff: np.ndarray,
                             timestamps: np.ndarray, node_list: List[int],
                             temperature: np.ndarray, err_flag: np.ndarray) -> None:
    """
    Write PL elaborated data to ELABDATAPRESSURE table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        pressure: (n_timestamps, n_sensors) pressure data
        pressure_diff: (n_timestamps, n_sensors) differential pressure
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATAPRESSURE
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            if record_exists:
                # Update
                update_query = """
                    UPDATE ELABDATAPRESSURE
                    SET Pressure = %s, PressureShift = %s, T_node = %s, calcerr = %s
                    WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                      AND EventDate = %s AND EventTime = %s
                """
                
                conn.execute_update(update_query, (
                    float(pressure[t, sensor_idx]), float(pressure_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx]),
                    control_unit_id, chain, node_id, date_str, time_str
                ))
            else:
                # Insert
                insert_query = """
                    INSERT INTO ELABDATAPRESSURE
                    (UnitName, ToolNameID, NodeNum, EventDate, EventTime,
                     Pressure, PressureShift, T_node, calcerr)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                conn.execute_update(insert_query, (
                    control_unit_id, chain, node_id, date_str, time_str,
                    float(pressure[t, sensor_idx]), float(pressure_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                ))


def write_extensometer_data(conn, control_unit_id: str, chain: str,
                            extension: np.ndarray, extension_diff: np.ndarray,
                            timestamps: np.ndarray, node_list: List[int],
                            temperature: np.ndarray, err_flag: np.ndarray) -> None:
    """
    Write extensometer elaborated data to ELABDATAEXTENSION table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        extension: (n_timestamps, n_sensors) extension data
        extension_diff: (n_timestamps, n_sensors) differential extension
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATAEXTENSION
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            if record_exists:
                # Update
                update_query = """
                    UPDATE ELABDATAEXTENSION
                    SET Extension = %s, ExtensionShift = %s, T_node = %s, calcerr = %s
                    WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                      AND EventDate = %s AND EventTime = %s
                """
                
                conn.execute_update(update_query, (
                    float(extension[t, sensor_idx]), float(extension_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx]),
                    control_unit_id, chain, node_id, date_str, time_str
                ))
            else:
                # Insert
                insert_query = """
                    INSERT INTO ELABDATAEXTENSION
                    (UnitName, ToolNameID, NodeNum, EventDate, EventTime,
                     Extension, ExtensionShift, T_node, calcerr)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                conn.execute_update(insert_query, (
                    control_unit_id, chain, node_id, date_str, time_str,
                    float(extension[t, sensor_idx]), float(extension_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                ))


def write_extensometer_3d_data(conn, control_unit_id: str, chain: str,
                               x_disp: np.ndarray, y_disp: np.ndarray, z_disp: np.ndarray,
                               x_diff: np.ndarray, y_diff: np.ndarray, z_diff: np.ndarray,
                               timestamps: np.ndarray, node_list: List[int],
                               temperature: np.ndarray, err_flag: np.ndarray) -> None:
    """
    Write 3DEL elaborated data to ELABDATA3DEL table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        x_disp, y_disp, z_disp: Displacement components (n_timestamps, n_sensors)
        x_diff, y_diff, z_diff: Differential components (n_timestamps, n_sensors)
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATA3DEL
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            if record_exists:
                # Update
                update_query = """
                    UPDATE ELABDATA3DEL
                    SET X = %s, Y = %s, Z = %s,
                        XShift = %s, YShift = %s, ZShift = %s,
                        T_node = %s, calcerr = %s
                    WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                      AND EventDate = %s AND EventTime = %s
                """
                
                conn.execute_update(update_query, (
                    float(x_disp[t, sensor_idx]), float(y_disp[t, sensor_idx]), float(z_disp[t, sensor_idx]),
                    float(x_diff[t, sensor_idx]), float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx]),
                    control_unit_id, chain, node_id, date_str, time_str
                ))
            else:
                # Insert
                insert_query = """
                    INSERT INTO ELABDATA3DEL
                    (UnitName, ToolNameID, NodeNum, EventDate, EventTime,
                     X, Y, Z, XShift, YShift, ZShift, T_node, calcerr)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                conn.execute_update(insert_query, (
                    control_unit_id, chain, node_id, date_str, time_str,
                    float(x_disp[t, sensor_idx]), float(y_disp[t, sensor_idx]), float(z_disp[t, sensor_idx]),
                    float(x_diff[t, sensor_idx]), float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                ))


def write_crackmeter_data(conn, control_unit_id: str, chain: str,
                         displacement: np.ndarray, displacement_diff: np.ndarray,
                         timestamps: np.ndarray, node_list: List[int],
                         temperature: np.ndarray, err_flag: np.ndarray,
                         n_dimensions: int, sensor_type: str = 'CrL') -> None:
    """
    Write crackmeter elaborated data to ELABDATACRL table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        displacement: (n_timestamps, n_sensors*n_dimensions) displacement data
        displacement_diff: (n_timestamps, n_sensors*n_dimensions) differential data
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
        n_dimensions: 1, 2, or 3
        sensor_type: 'CrL', '2DCrL', or '3DCrL'
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATACRL
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s AND SensorType = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str, sensor_type))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            # Prepare values for each dimension
            if n_dimensions == 1:
                values = (
                    float(displacement[t, sensor_idx]),
                    float(displacement_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                )
            elif n_dimensions == 2:
                values = (
                    float(displacement[t, sensor_idx*2]),
                    float(displacement[t, sensor_idx*2+1]),
                    float(displacement_diff[t, sensor_idx*2]),
                    float(displacement_diff[t, sensor_idx*2+1]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                )
            else:  # 3 dimensions
                values = (
                    float(displacement[t, sensor_idx*3]),
                    float(displacement[t, sensor_idx*3+1]),
                    float(displacement[t, sensor_idx*3+2]),
                    float(displacement_diff[t, sensor_idx*3]),
                    float(displacement_diff[t, sensor_idx*3+1]),
                    float(displacement_diff[t, sensor_idx*3+2]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                )
            
            if record_exists:
                # Update based on dimensions
                if n_dimensions == 1:
                    update_query = """
                        UPDATE ELABDATACRL
                        SET Displacement = %s, DisplacementShift = %s, T_node = %s, calcerr = %s
                        WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                          AND EventDate = %s AND EventTime = %s AND SensorType = %s
                    """
                    conn.execute_update(update_query, values + (control_unit_id, chain, node_id, 
                                                                date_str, time_str, sensor_type))
                elif n_dimensions == 2:
                    update_query = """
                        UPDATE ELABDATACRL
                        SET Disp_X = %s, Disp_Y = %s,
                            DispShift_X = %s, DispShift_Y = %s,
                            T_node = %s, calcerr = %s
                        WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                          AND EventDate = %s AND EventTime = %s AND SensorType = %s
                    """
                    conn.execute_update(update_query, values + (control_unit_id, chain, node_id,
                                                                date_str, time_str, sensor_type))
                else:  # 3D
                    update_query = """
                        UPDATE ELABDATACRL
                        SET Disp_X = %s, Disp_Y = %s, Disp_Z = %s,
                            DispShift_X = %s, DispShift_Y = %s, DispShift_Z = %s,
                            T_node = %s, calcerr = %s
                        WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                          AND EventDate = %s AND EventTime = %s AND SensorType = %s
                    """
                    conn.execute_update(update_query, values + (control_unit_id, chain, node_id,
                                                                date_str, time_str, sensor_type))
            else:
                # Insert based on dimensions
                if n_dimensions == 1:
                    insert_query = """
                        INSERT INTO ELABDATACRL
                        (UnitName, ToolNameID, NodeNum, EventDate, EventTime, SensorType,
                         Displacement, DisplacementShift, T_node, calcerr)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """
                    conn.execute_update(insert_query, (control_unit_id, chain, node_id, date_str, time_str,
                                                       sensor_type) + values)
                elif n_dimensions == 2:
                    insert_query = """
                        INSERT INTO ELABDATACRL
                        (UnitName, ToolNameID, NodeNum, EventDate, EventTime, SensorType,
                         Disp_X, Disp_Y, DispShift_X, DispShift_Y, T_node, calcerr)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """
                    conn.execute_update(insert_query, (control_unit_id, chain, node_id, date_str, time_str,
                                                       sensor_type) + values)
                else:  # 3D
                    insert_query = """
                        INSERT INTO ELABDATACRL
                        (UnitName, ToolNameID, NodeNum, EventDate, EventTime, SensorType,
                         Disp_X, Disp_Y, Disp_Z, DispShift_X, DispShift_Y, DispShift_Z, T_node, calcerr)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """
                    conn.execute_update(insert_query, (control_unit_id, chain, node_id, date_str, time_str,
                                                       sensor_type) + values)


def write_pcl_data(conn, control_unit_id: str, chain: str,
                  y_disp: np.ndarray, z_disp: np.ndarray,
                  y_local: np.ndarray, z_local: np.ndarray,
                  alpha_x: np.ndarray, alpha_y: np.ndarray,
                  y_diff: np.ndarray, z_diff: np.ndarray,
                  timestamps: np.ndarray, node_list: List[int],
                  temperature: np.ndarray, err_flag: np.ndarray,
                  sensor_type: str = 'PCL') -> None:
    """
    Write PCL/PCLHR elaborated data to ELABDATAPCL table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        y_disp, z_disp: Cumulative displacements (n_timestamps, n_sensors)
        y_local, z_local: Local displacements (n_timestamps, n_sensors)
        alpha_x, alpha_y: Roll and inclination angles (n_timestamps, n_sensors)
        y_diff, z_diff: Differential displacements (n_timestamps, n_sensors)
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
        sensor_type: 'PCL' or 'PCLHR'
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATAPCL
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s AND SensorType = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str, sensor_type))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            if record_exists:
                # Update
                update_query = """
                    UPDATE ELABDATAPCL
                    SET Y = %s, Z = %s,
                        Y_local = %s, Z_local = %s,
                        AlphaX = %s, AlphaY = %s,
                        YShift = %s, ZShift = %s,
                        T_node = %s, calcerr = %s
                    WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                      AND EventDate = %s AND EventTime = %s AND SensorType = %s
                """
                
                conn.execute_update(update_query, (
                    float(y_disp[t, sensor_idx]), float(z_disp[t, sensor_idx]),
                    float(y_local[t, sensor_idx]), float(z_local[t, sensor_idx]),
                    float(alpha_x[t, sensor_idx]), float(alpha_y[t, sensor_idx]),
                    float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx]),
                    control_unit_id, chain, node_id, date_str, time_str, sensor_type
                ))
            else:
                # Insert
                insert_query = """
                    INSERT INTO ELABDATAPCL
                    (UnitName, ToolNameID, NodeNum, EventDate, EventTime, SensorType,
                     Y, Z, Y_local, Z_local, AlphaX, AlphaY, YShift, ZShift, T_node, calcerr)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                conn.execute_update(insert_query, (
                    control_unit_id, chain, node_id, date_str, time_str, sensor_type,
                    float(y_disp[t, sensor_idx]), float(z_disp[t, sensor_idx]),
                    float(y_local[t, sensor_idx]), float(z_local[t, sensor_idx]),
                    float(alpha_x[t, sensor_idx]), float(alpha_y[t, sensor_idx]),
                    float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                ))


def write_tube_link_data(conn, control_unit_id: str, chain: str,
                        x_disp: np.ndarray, y_disp: np.ndarray, z_disp: np.ndarray,
                        x_star: np.ndarray, y_star: np.ndarray, z_star: np.ndarray,
                        x_local: np.ndarray, y_local: np.ndarray, z_local: np.ndarray,
                        x_diff: np.ndarray, y_diff: np.ndarray, z_diff: np.ndarray,
                        timestamps: np.ndarray, node_list: List[int],
                        temperature: np.ndarray, err_flag: np.ndarray) -> None:
    """
    Write TuL elaborated data to ELABDATATUBE table.
    
    Args:
        conn: Database connection
        control_unit_id: Control unit identifier
        chain: Chain identifier
        x_disp, y_disp, z_disp: Clockwise cumulative displacements
        x_star, y_star, z_star: Counterclockwise cumulative displacements
        x_local, y_local, z_local: Local displacements
        x_diff, y_diff, z_diff: Differential displacements
        timestamps: (n_timestamps,) datetime array
        node_list: List of node IDs
        temperature: (n_timestamps, n_sensors) temperature data
        err_flag: (n_timestamps, n_sensors) error flags
    """
    n_timestamps = len(timestamps)
    n_sensors = len(node_list)
    
    for sensor_idx, node_id in enumerate(node_list):
        for t in range(n_timestamps):
            timestamp = timestamps[t]
            date_str = timestamp.strftime('%Y-%m-%d')
            time_str = timestamp.strftime('%H:%M:%S')
            
            # Check if record exists
            check_query = """
                SELECT COUNT(*) as count
                FROM ELABDATATUBE
                WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                  AND EventDate = %s AND EventTime = %s
            """
            
            result = conn.execute_query(check_query, (control_unit_id, chain, node_id,
                                                      date_str, time_str))
            
            record_exists = result[0]['count'] > 0 if result else False
            
            if record_exists:
                # Update
                update_query = """
                    UPDATE ELABDATATUBE
                    SET X = %s, Y = %s, Z = %s,
                        X_star = %s, Y_star = %s, Z_star = %s,
                        X_local = %s, Y_local = %s, Z_local = %s,
                        XShift = %s, YShift = %s, ZShift = %s,
                        T_node = %s, calcerr = %s
                    WHERE UnitName = %s AND ToolNameID = %s AND NodeNum = %s
                      AND EventDate = %s AND EventTime = %s
                """
                
                conn.execute_update(update_query, (
                    float(x_disp[t, sensor_idx]), float(y_disp[t, sensor_idx]), float(z_disp[t, sensor_idx]),
                    float(x_star[t, sensor_idx]), float(y_star[t, sensor_idx]), float(z_star[t, sensor_idx]),
                    float(x_local[t, sensor_idx]), float(y_local[t, sensor_idx]), float(z_local[t, sensor_idx]),
                    float(x_diff[t, sensor_idx]), float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx]),
                    control_unit_id, chain, node_id, date_str, time_str
                ))
            else:
                # Insert
                insert_query = """
                    INSERT INTO ELABDATATUBE
                    (UnitName, ToolNameID, NodeNum, EventDate, EventTime,
                     X, Y, Z, X_star, Y_star, Z_star,
                     X_local, Y_local, Z_local, XShift, YShift, ZShift, T_node, calcerr)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                conn.execute_update(insert_query, (
                    control_unit_id, chain, node_id, date_str, time_str,
                    float(x_disp[t, sensor_idx]), float(y_disp[t, sensor_idx]), float(z_disp[t, sensor_idx]),
                    float(x_star[t, sensor_idx]), float(y_star[t, sensor_idx]), float(z_star[t, sensor_idx]),
                    float(x_local[t, sensor_idx]), float(y_local[t, sensor_idx]), float(z_local[t, sensor_idx]),
                    float(x_diff[t, sensor_idx]), float(y_diff[t, sensor_idx]), float(z_diff[t, sensor_idx]),
                    float(temperature[t, sensor_idx]), float(err_flag[t, sensor_idx])
                ))
