import csv
import os
import time
from dataclasses import dataclass
from typing import Dict, List, Set, Tuple, Final
from .process_parameters import ProcessParameters
from .table_operations.measurement_operations import *
from .table_operations.device_operations import *
from .table_operations.custom_action_adapter_operations import get_custom_action_adapter_record
from .table_operations.custom_filter_adapter_operations import get_custom_filter_adapter_record
from config import get_clean_identifier

DEVICE_ACRONYM_TEMPLATE: Final[str] = "{0}-DNP3"

map_types: Final[Dict[str, str]] = {
    "binary": "DIGI",  # 1-bit
    "doubleBitBinary": "DIGI",  # 2-bits
    "analog": "ALOG",
    "counter": "ALOG",
    "frozenCounter": "ALOG",
    "controlStatus": "DIGI",  # 1-bit
    "setpointStatus": "DIGI",  # 1-bit
}

# Case-insensitive map of DNP3 signal types
map_keys: Final[Dict[str, str]] = {key.lower(): key for key in map_types}

FLAGS_SOURCE: Final[str] = "DNP3 Flags Source"
FLAGS_DESTINATION: Final[str] = "DNP3 Flags Destination"

S_BIT_INVERTED: Final[str] = "S-bit inverted"
X_BIT_INVERTED: Final[str] = "X-bit inverted"

DEFAULT_DIGITAL_LABEL: Final[str] = "".join(f"DIGITAL{i}{' ' * 8 if i < 10 else ' ' * 7}" for i in range(16))


@dataclass
class Mapping:
    ID: int
    Source: str
    Index: int


def process_device_csv(params: ProcessParameters) -> List[str]:
    """
    Process device CSV file.
    """

    processed_acronyms: Set[str] = set()
    processed_count = 0
    total_processing_time = 0.0
    total_processed_device_rows = 0

    with open(params.device_csv_file_path, newline="") as csvfile:
        reader = csv.DictReader(csvfile)
        rows = list(reader)
        total_rows = len(rows)

        print(f'\nProcessing device CSV file "{params.device_csv_file_path}" with {total_rows:,} rows...')

        for row in rows:
            row_start_time = time.time()

            device_acronym, processed_acronyms, processed_device_rows = process_device_csv_row(params, row)

            if device_acronym is None:
                continue

            processed_acronyms.update(processed_acronyms)

            total_processing_time += time.time() - row_start_time
            total_processed_device_rows += processed_device_rows
            processed_count += 1

            # Calculate estimated time remaining based on tag map rows processed
            if total_processed_device_rows > 0:
                average_time_per_tag_row = total_processing_time / total_processed_device_rows

                # Estimate remaining tag map rows (rough approximation based on average so far)
                avg_tag_rows_per_device = total_processed_device_rows / processed_count
                remaining_devices = total_rows - processed_count
                estimated_remaining_tag_rows = remaining_devices * avg_tag_rows_per_device

                estimated_time_remaining = average_time_per_tag_row * estimated_remaining_tag_rows

                # Format estimated time remaining
                if estimated_time_remaining >= 3600:  # More than 1 hour
                    hours = int(estimated_time_remaining // 3600)
                    minutes = int((estimated_time_remaining % 3600) // 60)
                    eta_str = f"{hours}h {minutes}m"
                elif estimated_time_remaining >= 60:  # More than 1 minute
                    minutes = int(estimated_time_remaining // 60)
                    seconds = int(estimated_time_remaining % 60)
                    eta_str = f"{minutes}m {seconds}s"
                else:  # Less than 1 minute
                    seconds = int(estimated_time_remaining)
                    eta_str = f"{seconds}s"
            else:
                eta_str = "calculating..."
                average_time_per_tag_row = 0.0

            print(f"\nProcessing complete for {device_acronym}\n\n")

            print(
                f"{processed_count:,} out of {total_rows:,} CSV device rows processed, {processed_count / total_rows * 100:.2f}% complete..."
            )

            print(f"{total_processed_device_rows:,} total tag map rows processed")

            if total_rows - processed_count > 0:
                print(f"Estimated time remaining: {eta_str}")

            print()
            print("-" * 80)

            if device_acronym:
                processed_acronyms.add(device_acronym)

    return list(processed_acronyms)


def process_tag_map_csv_row(
    params: ProcessParameters,
    tag_map_row: Dict[str, str],
    device_record: Device,
    indexes: Dict[str, int],
    map_sections: Dict[str, List[Mapping]],
) -> List[str] | None:
    # Process each row of the device tag-name CSV file, expecting the following columns:
    #   - MapType, e.g., "binary", "analog", "counter", etc. (see map_types dictionary)
    #   - DNPIndex
    #   - TagName
    #   - Scale (optional, defaults to 1.0)
    #
    # This function creates a new Measurement record for each row.

    # Get map type from "MapType" column value
    map_type = str(tag_map_row["MapType"]).strip()

    if map_type.lower() not in map_keys:
        print(f'     ERROR: invalid map type name "{map_type}" for device - mapping row skipped')
        return None

    # Get time-series framework signal type from map type
    signal_type = map_types[map_keys[map_type.lower()]]

    if signal_type == "DIGI":
        indexes["digitalIndex"] += 1
        index = indexes["digitalIndex"]
        signalTypeSuffix = "DV"
    else:
        indexes["analogIndex"] += 1
        index = indexes["analogIndex"]
        signalTypeSuffix = "AV"

    source_dnp_index = str(tag_map_row["DNPIndex"]).strip()

    if not source_dnp_index.isdecimal():
        print(f'     ERROR: invalid DNP3 index "{source_dnp_index}" for device: not a number - mapping row skipped')
        return None

    # Get DNP3 index from "DNPIndex" column value
    dnp3index = int(source_dnp_index)

    # Get tag name from "TagName" column value
    point_tag = get_clean_identifier(str(tag_map_row["TagName"]).strip())
    signal_reference = f"{device_record.Acronym}-{signalTypeSuffix}{index}"
    description = f"{device_record.Name} {map_type} {point_tag}, DNP3 index: {dnp3index} [{signal_type}{index:04d}]"
    alternate_tag = str(tag_map_row.get("AlternateTag", "")).strip()
    negated = True if str(tag_map_row.get("Negate", "FALSE")).strip().upper() == "TRUE" else False
    concatenated = True if str(tag_map_row.get("Concatenate", "FALSE")).strip().upper() == "TRUE" else False
    invert_sbit = True if str(tag_map_row.get("InvertSBit", "FALSE")).strip().upper() == "TRUE" else False
    invert_xbit = True if str(tag_map_row.get("InvertXBit", "FALSE")).strip().upper() == "TRUE" else False

    if signal_type == "ALOG":
        # Define label for analog signals
        if params.ieee_c37_118_version < 2011:
            # Analog label is limited to 16 characters in IEEE C37.118-2005
            alternate_tag = alternate_tag[:16].ljust(16)
    else:
        # Define label for digital signals
        if params.ieee_c37_118_version < 2011:
            # Digital label is limited to 16 characters per bit in IEEE C37.118-2005
            alternate_tag = f"{alternate_tag[:16].ljust(16)}{DEFAULT_DIGITAL_LABEL[16:]}"

    # Get scale from "Scale" column value, defaulting to 1.0 if not provided
    scale = float(tag_map_row.get("Scale", 1.0))

    # Negate the scale if specified
    if negated:
        scale = -scale

    first_intermediate_measurement_record: Measurement | None = None
    second_intermediate_measurement_record: Measurement | None = None

    if concatenated:
        # Validate that concatenation is only specified for digital points
        if map_type.lower() != "doublebitbinary":
            print(
                f'     ERROR: concatenation only supported for "doubleBitBinary" type - mapping row for "{point_tag}" skipped'
            )
            return None

        # Define an intermediate measurement point for the first part (low 2-bits) of the concatenated measurement
        first_intermediate_measurement_record = get_measurement_record(
            params.session,
            params.is_sql_server,
            device_record.ID,
            device_record.HistorianID,
            params.signal_type_ids["CALC"],
            f"{point_tag}!LOW",
            f"{alternate_tag}!LOW",
            f"{device_record.Acronym}.{point_tag}-CV1",
            scale,
            f"{device_record.Name} first intermediate {map_type} (low value) of concatenated measurement for {point_tag}, DNP3 index: {dnp3index} ({FLAGS_SOURCE})",
        )

        # Define an intermediate measurement point for the second part (high 2-bits) of the concatenated measurement
        second_intermediate_measurement_record = get_measurement_record(
            params.session,
            params.is_sql_server,
            device_record.ID,
            device_record.HistorianID,
            params.signal_type_ids["CALC"],
            f"{point_tag}!HIGH",
            f"{alternate_tag}!HIGH",
            f"{device_record.Acronym}.{point_tag}-CV2",
            scale,
            f"{device_record.Name} second intermediate {map_type} (high value) of concatenated measurement for {point_tag}, DNP3 index: {dnp3index + 1}",
        )

        # Define a modified description for the concatenated measurement
        description = f"{device_record.Name} concatenated {point_tag}, DNP3 indexes: {dnp3index} and {dnp3index + 1} [{signal_type}{index:04d}] ({FLAGS_DESTINATION})"

    if invert_sbit:
        # Validate that invert_sbit is only specified for digital points
        if map_type.lower() == "doublebitbinary":
            description += f" ({S_BIT_INVERTED})"
        else:
            print(
                f'     WARNING: S-bit inversion only supported for "doubleBitBinary" type - S-bit inversion for "{point_tag}" skipped'
            )
            invert_sbit = False

    if invert_xbit:
        # Validate that invert_xbit is only specified for digital points
        if map_type.lower() == "doublebitbinary":
            description += f" ({X_BIT_INVERTED})"
        else:
            print(
                f'     WARNING: X-bit inversion only supported for "doubleBitBinary" type - X-bit inversion for "{point_tag}" skipped'
            )
            invert_xbit = False

    # Add or update DNP3 measurement record
    measurement_record = get_measurement_record(
        params.session,
        params.is_sql_server,
        device_record.ID,
        device_record.HistorianID,
        params.signal_type_ids[signal_type],
        point_tag,
        alternate_tag,
        signal_reference,
        scale,
        description,
    )

    if not measurement_record:
        return None

    new_adapter_acronyms: List[str] = []

    if invert_sbit:
        # Verify dynamic filter exists to augment measurements targeted for S-bit inversion,
        # equation flips first bit, index 0
        variable_list = f"value[]=FILTER ActiveMeasurements WHERE Description LIKE '%{S_BIT_INVERTED}%'"

        connection_string = (
            f"VariableList={{{variable_list}}}; "
            "ExpressionText={(CAST(value[INDEX], int) XOR 1)}; "
            "FilterOperation=ValueAugmentation; UsePrecisionTimer=false"
        )

        # Create CustomFilterAdapter record
        adapter_name = "DNP3!S-BIT-INVERTER"

        get_custom_filter_adapter_record(
            params.session,
            params.is_sql_server,
            params.node_id,
            adapter_name,
            "DynamicCalculator.dll",
            "DynamicCalculator.DynamicFilter",
            connection_string,
            0,
            True,
        )

        new_adapter_acronyms.append(adapter_name)

    if invert_xbit:
        # Verify dynamic filter exists to augment measurements targeted for X-bit inversion,
        # equation flips second bit, index 1
        variable_list = f"value[]=FILTER ActiveMeasurements WHERE Description LIKE '%{X_BIT_INVERTED}%'"

        connection_string = (
            f"VariableList={{{variable_list}}}; "
            "ExpressionText={(CAST(value[INDEX], int) XOR 2)}; "
            "FilterOperation=ValueAugmentation; UsePrecisionTimer=false"
        )

        # Create CustomFilterAdapter record
        adapter_name = "DNP3!X-BIT-INVERTER"

        get_custom_filter_adapter_record(
            params.session,
            params.is_sql_server,
            params.node_id,
            adapter_name,
            "DynamicCalculator.dll",
            "DynamicCalculator.DynamicFilter",
            connection_string,
            0,
            True,
        )

        new_adapter_acronyms.append(adapter_name)

    if concatenated:
        if not first_intermediate_measurement_record or not second_intermediate_measurement_record:
            print(
                f'     ERROR: concatenation specified for "{point_tag}" but intermediate measurements not found - mapping row skipped'
            )
            return None

        # Verify state flags transfer filter adapter exists to copy state flags from
        # the first intermediate measurement to the modeled concatenated measurement
        connection_string = (
            f"SourceMeasurements=FILTER ActiveMeasurements WHERE Description LIKE '%{FLAGS_SOURCE}%' ORDER BY AlternateTag; "
            f"DestinationMeasurements=FILTER ActiveMeasurements WHERE Description LIKE '%{FLAGS_DESTINATION}%' ORDER BY AlternateTag; "
            "Flags=0xFFFFFFFF"
        )

        # Create CustomFilterAdapter record
        adapter_name = "DNP3!STATE-FLAGS-TRANSFERER"

        get_custom_filter_adapter_record(
            params.session,
            params.is_sql_server,
            params.node_id,
            adapter_name,
            "DataQualityMonitoring.dll",
            "DataQualityMonitoring.StateFlagsTransferAdapter",
            connection_string,
            0,
            True,
        )

        new_adapter_acronyms.append(adapter_name)

        # Only intermediate measurements need to be added to the map sections for reading from DNP3, the
        # concatenated measurement will be calculated dynamically from the two intermediate measurements
        map_sections[map_type].append(
            Mapping(ID=first_intermediate_measurement_record.PointID, Source=params.target_source, Index=dnp3index)
        )
        map_sections[map_type].append(
            Mapping(ID=second_intermediate_measurement_record.PointID, Source=params.target_source, Index=dnp3index + 1)
        )

        # Define a dynamic calculator that will combine the two intermediate points into a single measurement
        # Each value will be interpreted using only the lower 2-bits to be combined into a 4-bit nibble
        # Inputs are the intermediate measurements, and the output is the modeled concatenated measurement
        variable_list = (
            f"first={first_intermediate_measurement_record.SignalID}; "
            f"second={second_intermediate_measurement_record.SignalID}"
        )

        connection_string = (
            f"VariableList={{{variable_list}}}; "
            f"OutputMeasurements={measurement_record.SignalID}; "
            "ExpressionText={(CAST(first, int) AND 3) OR ((CAST(second, int) AND 3) << 2)}; "
            f"CalculationInterval={params.polling_interval}; "
            f"LagTime={params.valid_time_range}; "
            f"LeadTime={params.valid_time_range}; "
            "FramesPerSecond=1; UsePrecisionTimer=false; UseLatestValues=true; "
            "TimestampSource=LocalClock; UseLocalClockAsRealTime=true; "
            "IgnoreBadTimestamps=true; RoundToNearestTimestamp=true; "
            "PerformTimestampReasonabilityCheck=false; "
            "OutlierOperation=PublishWithBadState"
        )

        # Create CustomActionAdapter record
        adapter_name = f"{device_record.Acronym}.{point_tag}!CONCAT-CALC"

        get_custom_action_adapter_record(
            params.session,
            params.is_sql_server,
            params.node_id,
            adapter_name,
            "DynamicCalculator.dll",
            "DynamicCalculator.DynamicCalculator",
            connection_string,
            0,
            True,
        )

        new_adapter_acronyms.append(adapter_name)
    else:
        # Add the DNP3 measurement record to the map sections for reading from DNP3
        map_sections[map_type].append(
            Mapping(ID=measurement_record.PointID, Source=params.target_source, Index=dnp3index)
        )

    if not params.publish_flags_as_separate_measurements:
        return new_adapter_acronyms

    # Add or update DNP3 quality flag measurement record
    signal_type = "ALOG"
    indexes["analogIndex"] += 1
    index = indexes["analogIndex"]
    signalTypeSuffix = "AV"

    # Use the first intermediate low value for quality flags if concatenated; otherwise, use the normal value
    quality_point_tag = f"{point_tag}{"!LOW" if concatenated else ""}{params.quality_tag_suffix}"
    signal_reference = f"{device_record.Acronym}-{signalTypeSuffix}{index}"
    description = f"{device_record.Name} {"concatenated" if concatenated else map_type} {point_tag} Quality Flags [{signal_type}{index:04d}]"
    alternate_tag = str(tag_map_row.get("AlternateTag", "")).strip()

    if params.ieee_c37_118_version == 2011:
        alternate_tag = f"{alternate_tag}{params.quality_tag_suffix}"
    else:
        # Analog label is limited to 16 characters in IEEE C37.118-2005
        suffix = params.quality_tag_suffix
        suffixLen = len(suffix)

        if suffixLen > 10:
            suffixLen = 10
            suffix = suffix[:10]

        alternate_tag = f"{alternate_tag[:16 - suffixLen].ljust(16 - suffixLen)}{suffix}"

    scale = 1.0

    get_measurement_record(
        params.session,
        params.is_sql_server,
        device_record.ID,
        device_record.HistorianID,
        params.signal_type_ids[signal_type],
        quality_point_tag,
        alternate_tag,
        signal_reference,
        scale,
        description,
    )

    return new_adapter_acronyms


def process_device_csv_row(params: ProcessParameters, device_row: Dict[str, str]) -> Tuple[str | None, Set[str], int]:
    # Process each row of the device CSV file, expecting the following columns:
    #   - Name
    #   - Substation (optional, defaults to Name if not provided)
    #   - Connection (HostIP:Port)
    #   - TagMapFileName
    #
    # This function creates a new Device record for each row.

    processed_acronyms: Set[str] = set()

    # Get device name from "Name" column value
    device_name = str(device_row["Name"]).strip()

    # If device name starts with '#' or ';' character, skip this row
    if device_name.startswith("#") or device_name.startswith(";"):
        print(f">> Skipping device: {device_name[1:]} -- row commented out in CSV file")
        return None, processed_acronyms, 0

    # Create valid device acronym from device name
    device_acronym = DEVICE_ACRONYM_TEMPLATE.format(get_clean_identifier(device_name))

    print()
    print(f">> Processing device: {device_name}")

    # Establish device configuration file and mapping file paths
    device_config_file_path = f"{params.output_directory}{device_acronym}-config.xml"
    device_mapping_file_path = f"{params.output_directory}{device_acronym}-mapping.xml"

    # Build device connection string
    connection_string = (
        f"CommsFilePath={device_config_file_path.replace('/', '\\')}; "
        f"MappingFilePath={device_mapping_file_path.replace('/', '\\')}; "
        f"MapQualityToStateFlags={str(params.map_quality_to_state_flags).lower()}; "
        f"PublishFlagsAsSeparateMeasurements={str(params.publish_flags_as_separate_measurements).lower()}; "
        f"TagMatchPattern={params.tag_match_pattern}; "
        f"QualityTagSuffix={params.quality_tag_suffix}; "
        f"PollingInterval={params.polling_interval}"
    )

    substation_name = str(device_row.get("Substation", device_name)).strip()

    if not substation_name:
        substation_name = device_name

    # Add or update DNP3 device record
    device_record = get_device_record(
        params.session,
        params.is_sql_server,
        params.node_id,
        params.historian_id,
        params.protocol_id,
        device_acronym,
        substation_name,
        connection_string,
    )

    if not device_record:
        return None, processed_acronyms, 0

    processed_acronyms.add(device_acronym)

    # Load a copy of the 'Device.xml' template
    device_template = params.device_template

    # Parse "Connection" column value into host IP and port
    connection = str(device_row["Connection"]).strip()
    host_ip, port = connection.split(":")

    # Replace template parameters with values from device CSV file
    device_template = device_template.replace("{HostIP}", host_ip)
    device_template = device_template.replace("{Port}", port)

    # Write target device XML configuration file to output directory
    with open(device_config_file_path, "w") as device_file:
        device_file.write(device_template)

    # Get tag map file name
    tag_map_file_name = str(device_row["TagMapFileName"]).strip()

    # Create dictionary of map sections to hold processed tag map file rows
    map_sections: Dict[str, List[Mapping]] = {key: [] for key in map_types.keys()}

    processed_count = 0
    last_update_time = time.time()
    tag_map_name = os.path.basename(tag_map_file_name)

    # Process tag map file, loading map sections dictionary
    with open(tag_map_file_name, newline="") as tag_map_file:
        reader = csv.DictReader(tag_map_file)
        indexes = {"digitalIndex": 0, "analogIndex": 0}
        rows = list(reader)
        total_rows = len(rows)

        print(f'      Processing tag map "{tag_map_name}" with {total_rows:,} rows...')

        for tag_map_row in rows:
            new_adapter_acronyms = process_tag_map_csv_row(params, tag_map_row, device_record, indexes, map_sections)

            if new_adapter_acronyms is not None:
                processed_acronyms.update(new_adapter_acronyms)

            processed_count += 1
            current_time = time.time()

            if current_time - last_update_time >= 2.0:  # Show message every 2 seconds or so
                print(
                    f"         >> Processed {processed_count:,} tag map rows so far, {processed_count / total_rows * 100:,.2f}% complete..."
                )
                last_update_time = current_time

        print(f'      Done! Processed {processed_count:,} tag map rows for "{tag_map_name}"')

        # Remove any left over digitals beyond the last index
        print(f"\n      Checking for unreferenced DIGI measurements...")
        remove_unreferenced_measurements(params, indexes["digitalIndex"], device_record, "DIGI")

        # Remove any left over analogs beyond the last index
        print(f"\n      Checking for unreferenced ALOG measurements...")
        remove_unreferenced_measurements(params, indexes["analogIndex"], device_record, "ALOG")

    # Load all map sections into a string
    map_section_lines: list[str] = []

    # Build the map section string
    for map_type, map_section_list in map_sections.items():
        if len(map_section_list) > 0:
            map_section_lines.append(f"  <{map_type}Map>")

            for map_section in map_section_list:
                # Load a copy of the 'IndexMap.xml' template
                index_map_template = params.index_map_template

                # Replace template parameters
                index_map_template = index_map_template.replace("{ID}", str(map_section.ID))
                index_map_template = index_map_template.replace("{Source}", map_section.Source)
                index_map_template = index_map_template.replace("{Index}", str(map_section.Index))

                map_section_lines.append(index_map_template)

            map_section_lines.append(f"  </{map_type}Map>")

    # Join map section lines into a string
    map_sections_string = "\n".join(map_section_lines)

    # Load a copy of the 'Mapping.xml' template
    mapping_template = params.mapping_template

    # Replace template parameters
    mapping_template = mapping_template.replace("{MapSections}", map_sections_string)

    # Write target mapping XML configuration file to output directory
    with open(device_mapping_file_path, "w") as mapping_file:
        mapping_file.write(mapping_template)

    return device_acronym, processed_acronyms, processed_count


def remove_unreferenced_measurements(
    params: ProcessParameters, last_index: int, device_record: Device, signal_type: str
):
    if last_index <= 0:
        return

    # Build the query without immediately loading all results into memory.
    measurements_query = params.session.query(Measurement).filter(
        Measurement.DeviceID == device_record.ID,
        Measurement.SignalTypeID == params.signal_type_ids[signal_type],
    )

    processed_count = 0
    removed_count = 0
    last_update_time = time.time()

    # Use yield_per to fetch results in batches
    for measurement in measurements_query.yield_per(100):
        index = parse_signal_reference_index(measurement.SignalReference)

        if index > last_index:
            params.session.delete(measurement)
            removed_count += 1

        processed_count += 1
        current_time = time.time()

        if current_time - last_update_time >= 2.0:  # Show message every 2 seconds or so
            print(f"        >> Checked {processed_count:,} {signal_type} measurements so far...")
            last_update_time = current_time

    print(f"        Done! Checked {processed_count:,} {signal_type} measurements, {removed_count:,} removed")


# Parse index from signal reference formatted as "<Acronym>-<SignalTypeSuffix><Index>"
def parse_signal_reference_index(signal_reference: str) -> int:
    # Split signal reference on hyphens
    parts = signal_reference.split("-")

    # Verify signal reference has at least two parts
    if len(parts) < 2:
        return -1

    # Get last part of signal reference, could be more than two parts if acronym has a hyphen
    index = parts[-1]

    # Signal type suffix is expected to be two characters followed by a decimal number
    if len(index) < 3:
        return -1

    # Move past the signal type suffix
    index = index[2:]

    # Verify index is a decimal number
    if not index.isdigit():
        return -1

    return int(index)
