from sqlalchemy.orm import Session
from sqlalchemy.sql import text

from ..model.device import Device
from ..model.runtime import Runtime
from ..model.trackedchange import TrackedChange


def get_device_record(
    session: Session,
    is_sql_server: bool,
    node_id: str,
    historian_id: int,
    protocol_id: int,
    device_acronym: str,
    device_name: str,
    connection_string: str,
) -> Device | None:
    try:
        # Lookup device record by acronym, creating a new record if one does not exist
        device_record = session.query(Device).filter(Device.Acronym == device_acronym).one_or_none()

        if device_record:
            # If the record exists, check if it matches the provided details
            if (
                str(device_record.NodeID) == node_id
                and int(device_record.HistorianID) == historian_id
                and int(device_record.ProtocolID) == protocol_id
                and str(device_record.Name) == device_name
                and str(device_record.ConnectionString) == connection_string
            ):
                return device_record
        else:
            device_record = Device()

        # Update record fields
        device_record.NodeID = node_id
        device_record.Acronym = device_acronym
        device_record.Name = device_name
        device_record.HistorianID = historian_id
        device_record.ProtocolID = protocol_id
        device_record.ConnectionString = connection_string
        device_record.Enabled = True

        # Save record updates
        session.add(device_record)
        session.commit()

        # Re-query to get any database assigned information
        device_record = session.query(Device).filter(Device.Acronym == device_acronym).one()

        runtime_record = (
            session.query(Runtime)
            .filter(Runtime.SourceID == device_record.ID, Runtime.SourceTable == "Device")
            .one_or_none()
        )

        # Manually insert the device record details into the Runtime table, if it doesn't exist
        if not runtime_record:
            runtime_record = Runtime()
            runtime_record.SourceID = device_record.ID
            runtime_record.SourceTable = "Device"

            session.add(runtime_record)
            session.commit()

        if is_sql_server:
            # Manually insert the device record details into the TrackedChange table (in lieu of triggers)
            trackedchange_record = TrackedChange()
            trackedchange_record.TableName = "Device"
            trackedchange_record.PrimaryKeyColumn = "ID"
            trackedchange_record.PrimaryKeyValue = str(device_record.ID)

            session.add(trackedchange_record)
            session.commit()

        return device_record
    except Exception as e:
        session.rollback()
        print(f'Error occurred while reading or creating Device record for "{device_name}": {e}')
        return None
