from config import *
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.sql import text
from dnp3loader.process_parameters import ProcessParameters
from dnp3loader.process_cim_export_file import process_cim_export_file
from dnp3loader.process_device_csv import process_device_csv
from initialize_adapters import initialize_adapters
import time


def create_indexes(session: Session):
    if not is_sql_server:
        return

    # Create index on Measurement.PointTag
    print("Creating database index on Measurement.PointTag...")
    session.execute(
        text(
            f"""
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Measurement_PointTag' AND object_id = OBJECT_ID('Measurement'))
            BEGIN
                CREATE INDEX IX_Measurement_PointTag ON Measurement (PointTag)
            END
            """
        )
    )

    # Create row length restricted index on Measurement.AlternateTag via field alias AlternateTagIndexed
    # This is required because SQL Server has max index key length of 900 bytes and AlternateTag is a varchar(max)
    print("Creating database index on Measurement.AlternateTag...")
    session.execute(
        text(
            f"""
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Measurement_AlternateTag' AND object_id = OBJECT_ID('Measurement'))
            BEGIN
                ALTER TABLE Measurement
                ADD AlternateTagIndexed AS CAST(LEFT(AlternateTag, 900) AS varchar(900)) PERSISTED;

                CREATE INDEX IX_Measurement_AlternateTag ON Measurement (AlternateTagIndexed);
            END
            """
        )
    )

    # Create index on Measurement.SignalReference
    print("Creating database index on Measurement.SignalReference...")
    session.execute(
        text(
            f"""
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Measurement_SignalReference' AND object_id = OBJECT_ID('Measurement'))
            BEGIN
                CREATE INDEX IX_Measurement_SignalReference ON Measurement (SignalReference)
            END
            """
        )
    )

    # Create index on Measurement.DeviceID, Measurement.SignalTypeID
    print("Creating database index on Measurement.DeviceID, Measurement.SignalTypeID...")
    session.execute(
        text(
            f"""
            IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Measurement_DeviceID_SignalTypeID' AND object_id = OBJECT_ID('Measurement'))
            BEGIN
                CREATE INDEX IX_Measurement_DeviceID_SignalTypeID ON Measurement (DeviceID, SignalTypeID)
            END
            """
        )
    )


def main():
    start_time = time.time()

    print("Starting DNP3 Configuration Loader...")
    print("    Parameters:")
    print(f"        Device CSV File Path: {device_csv_file_path}")
    print(f"        Database Connection String: {db_connection_string}")
    print(f"        Target Source: {target_source}")
    print(f"        Company Acronym: {company_acronym}")
    print(f"        IEEE C37.118 Version: {ieee_c37_118_version}")
    print(f"        Map Quality to State Flags: {map_quality_to_state_flags}")
    print(f"        Publish Flags as Separate Measurements: {publish_flags_as_separate_measurements}")
    print(f"        Tag Match Pattern: {tag_match_pattern}")
    print(f"        Quality Tag Suffix: {quality_tag_suffix}")
    print(f"        Polling Interval: {polling_interval:,.2f} seconds")
    print(f"        Output Directory: {output_directory}")
    print(f"        Template Directory: {template_directory}")
    print(f"        openPDC Console Path: {open_pdc_console_path}")
    print(f"        Initialize Adapter: {initialize_adapters}")
    print()

    # Create database engine
    print("Opening database connection...")
    engine = create_engine(db_connection_string)
    session_factory = sessionmaker(bind=engine)

    # Connect to database
    session = session_factory()

    # Establish process parameters
    process_parameters = ProcessParameters(
        cim_export_file_path,
        device_csv_file_path,
        default_dnp3_port,
        session,
        is_sql_server,
        target_source,
        company_acronym,
        ieee_c37_118_version,
        map_quality_to_state_flags,
        publish_flags_as_separate_measurements,
        tag_match_pattern,
        quality_tag_suffix,
        polling_interval,
        valid_time_range,
        output_directory,
        template_directory,
        tag_map_directory,
        comment_char,
    )

    # Create database indexes to optimize queries
    create_indexes(session)

    if cim_export_file_path:
        # Process CIM export file
        print("Processing CIM export file...")
        process_cim_export_file(process_parameters)

    try:
        # Disable all SQL Server database triggers so ODBC driver can be used for inserts
        if is_sql_server:
            print("Disabling all database triggers (required for SQL Server ODBC connection)...")
            session.execute(text('sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"'))

        # Process device CSV file
        acronyms = process_device_csv(process_parameters)

    finally:
        print()

        # Re-enable all SQL Server database triggers
        if is_sql_server:
            print("Re-enabling all database triggers...")
            session.execute(text('sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"'))

        # Commit any pending changes
        session.commit()

        # Close database connection
        print("Closing database connection...")
        session.close()

        # Shutdown database engine
        engine.dispose()

    # Initialize the openPDC DNP3 adapters post configuration updates
    if initialize_adapters:
        initialize_adapters(acronyms, open_pdc_console_path)

    print()
    print("DNP3 Configuration Loader complete!")

    total_runtime = time.time() - start_time
    print(f"    Total runtime: {total_runtime:,.2f} seconds")
    print()


if __name__ == "__main__":
    main()
