//******************************************************************************************************
//  Program.cs - Gbtc
//
//  Copyright © 2026, Grid Protection Alliance.  All Rights Reserved.
//
//  Licensed to the Grid Protection Alliance (GPA) under one or more contributor license agreements. See
//  the NOTICE file distributed with this work for additional information regarding copyright ownership.
//  The GPA licenses this file to you under the MIT License (MIT), the "License"; you may not use this
//  file except in compliance with the License. You may obtain a copy of the License at:
//
//      http://opensource.org/licenses/MIT
//
//  Unless agreed to in writing, the subject software distributed under the License is distributed on an
//  "AS-IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Refer to the
//  License for the specific language governing permissions and limitations.
//
//  Code Modification History:
//  ----------------------------------------------------------------------------------------------------
//  01/15/2026 - J. Ritchie Carroll
//       Generated original version of source code.
//
//******************************************************************************************************

using System.Diagnostics;
using System.Xml.Linq;
using Gemstone.Configuration;
using Gemstone.Data;
using Gemstone.Expressions.Model;
using Gemstone.IO;
using Gemstone.StringExtensions;
using Gemstone.Units;
using Microsoft.Extensions.Configuration;
using Microsoft.Win32;
using ConfigSettings = Gemstone.Configuration.Settings;

namespace DNP3ConfigLoader;

internal class Program
{
    public const string ConfigFileNameTemplate = "{0}.exe.config";

    // The following code attempts to acquire database connection information from the host openHistorian
    // service assumed to be on the same machine as the SynchroWave STTP config exporter application.

    private static string GetConfigFilePath(Settings settings)
    {
        #pragma warning disable CA1416 // Application currently targets Windows only
        using RegistryKey? gpaKey = Registry.LocalMachine.OpenSubKey($@"SOFTWARE\Grid Protection Alliance\{settings.HostService}");
        string installPath = gpaKey?.GetValue("InstallPath") as string ?? settings.DefaultInstallPath;
        #pragma warning restore CA1416

        return Path.Combine(installPath, string.Format(ConfigFileNameTemplate, settings.HostService));
    }

    private static string GetCoreDataProviderString(string dataProviderString)
    {
        // TODO: Currently only providing data provider string translations for SQL Server and SQLite, add more as needed:
        const string SqlClientDataProviderString = "AssemblyName=Microsoft.Data.SqlClient; ConnectionType=Microsoft.Data.SqlClient.SqlConnection";
        const string SqliteDataProviderString = "AssemblyName=Microsoft.Data.Sqlite; ConnectionType=Microsoft.Data.Sqlite.SqliteConnection";

        if (dataProviderString.Contains("System.Data.SQLite"))
            return SqliteDataProviderString;

        if (dataProviderString.Contains("System.Data.SqlClient"))
            return SqlClientDataProviderString;

        return dataProviderString;
    }

    private static AdoDataConnection OpenConnection(Settings settings)
    {
        string configFilePath = GetConfigFilePath(settings);

        if (!File.Exists(configFilePath))
            throw new InvalidOperationException($"{settings.HostService} configuration database cannot be opened, config file \"{configFilePath}\" cannot be found.");

        // Load needed database settings from target config file
        XDocument serviceConfig = XDocument.Load(configFilePath);

        string? connectionString = serviceConfig
            .Descendants("systemSettings")
            .SelectMany(systemSettings => systemSettings.Elements("add"))
            .Where(element => "ConnectionString".Equals((string)element.Attribute("name")!, StringComparison.OrdinalIgnoreCase))
            .Select(element => (string)element.Attribute("value")!)
            .FirstOrDefault();

        ArgumentNullException.ThrowIfNull(connectionString);

        Dictionary<string, string> connectionSettings = connectionString.ParseKeyValuePairs();

        connectionSettings["Trusted_Connection"] = "true";
        connectionSettings["TrustServerCertificate"] = "true";

        connectionString = connectionSettings.JoinKeyValuePairs();

        string? dataProviderString = serviceConfig
            .Descendants("systemSettings")
            .SelectMany(systemSettings => systemSettings.Elements("add"))
            .Where(element => "DataProviderString".Equals((string)element.Attribute("name")!, StringComparison.OrdinalIgnoreCase))
            .Select(element => (string)element.Attribute("value")!)
            .FirstOrDefault();

        ArgumentNullException.ThrowIfNull(dataProviderString);

        // Open database
        return new AdoDataConnection(connectionString, GetCoreDataProviderString(dataProviderString));
    }

    private static void Main()
    {
        Stopwatch stopwatch = Stopwatch.StartNew();

        Settings settings = LoadSettings();
        ValueExpressionParser.DefaultTypeRegistry.RegisterSymbol(nameof(Settings), settings);

        Console.WriteLine("Starting DNP3 Configuration Loader...");
        Console.WriteLine("    Parameters:");
        Console.WriteLine($"        Host Service: {settings.HostService}");
        Console.WriteLine($"        Default Install Path: {settings.DefaultInstallPath}");
        Console.WriteLine($"        CIM Export File Path: {settings.CIMExportFilePath}");
        Console.WriteLine($"        Device CSV File Path: {settings.DeviceCSVFilePath}");
        Console.WriteLine($"        Default DNP3 Port: {settings.DefaultDNP3Port}");
        Console.WriteLine($"        Database Connection String: {settings.ConnectionString}");
        Console.WriteLine($"        Target Source: {settings.TargetSource}");
        Console.WriteLine($"        Company Acronym: {settings.CompanyAcronym}");
        Console.WriteLine($"        IEEE C37.118 Version: {settings.IEEEC37_118Version}");
        Console.WriteLine($"        Map Quality to State Flags: {settings.MapQualityToStateFlags}");
        Console.WriteLine($"        Publish Flags as Separate Measurements: {settings.PublishFlagsAsSeparateMeasurements}");
        Console.WriteLine($"        Tag Match Pattern: {settings.TagMatchPattern}");
        Console.WriteLine($"        Quality Tag Suffix: {settings.QualityTagSuffix}");
        Console.WriteLine($"        Polling Interval: {settings.PollingInterval:N2} seconds");
        Console.WriteLine($"        Valid Time Range: {settings.ValidTimeRange:N2} seconds");
        Console.WriteLine($"        Output Directory: {settings.OutputDirectory}");
        Console.WriteLine($"        Template Directory: {settings.TemplateDirectory}");
        Console.WriteLine($"        Tag Map Directory: {settings.TagMapDirectory}");
        Console.WriteLine($"        openPDC Console Path: {settings.OpenPDCConsolePath}");
        Console.WriteLine($"        Initialize Adapters: {settings.InitializeAdapters}");
        Console.WriteLine($"        Comment Char: {settings.CommentChar}");
        Console.WriteLine($"        Default Access ID: {settings.DefaultAccessID}");
        Console.WriteLine($"        Default Frames Per Second: {settings.DefaultFramesPerSecond}");
        Console.WriteLine($"        Default Measurement Reporting Interval: {settings.DefaultMeasurementReportingInterval}");
        Console.WriteLine($"        Default Enable Devices: {settings.DefaultEnableDevices}");
        Console.WriteLine($"        Command Delay Milliseconds: {settings.CommandDelayMilliseconds}");
        Console.WriteLine($"        Process Timeout Milliseconds: {settings.ProcessTimeoutMilliseconds}");
        Console.WriteLine();

        // Create database connection
        Console.WriteLine("Opening database connection...");
        using AdoDataConnection connection = OpenConnection(settings);

        // Get Node ID
        Guid? nodeID = connection.ExecuteScalar<Guid?>("SELECT ID FROM Node WHERE Name = 'Default'") ?? connection.ExecuteScalar<Guid?>("SELECT TOP 1 ID FROM Node");

        if (!nodeID.HasValue)
            throw new Exception("Failed to load default node ID");

        // Get Historian ID
        int historianID = connection.ExecuteScalar<int>($"SELECT ID FROM Historian WHERE Acronym = '{settings.TargetSource}'");

        if (historianID == 0)
            throw new Exception($"Failed to load historian record for target source: \"{settings.TargetSource}\"");

        // Get Protocol ID for DNP3
        int protocolID = connection.ExecuteScalar<int>("SELECT ID FROM Protocol WHERE Acronym = 'DNP3'");

        if (protocolID == 0)
            throw new Exception("Failed to load DNP3 protocol ID");

        // Load signal type IDs
        Dictionary<string, int> signalTypeIDs = new()
        {
            ["ALOG"] = connection.ExecuteScalar<int>("SELECT ID FROM SignalType WHERE Acronym = 'ALOG'"),
            ["DIGI"] = connection.ExecuteScalar<int>("SELECT ID FROM SignalType WHERE Acronym = 'DIGI'"),
            ["CALC"] = connection.ExecuteScalar<int>("SELECT ID FROM SignalType WHERE Acronym = 'CALC'")
        };

        ProcessParameters processParams = new(settings, connection, nodeID.Value, historianID, protocolID, signalTypeIDs);

        // Create database indexes to optimize queries
        CreateIndexes(connection);

        // Process CIM export file if specified and exists
        if (!string.IsNullOrEmpty(settings.CIMExportFilePath))
        {
            if (File.Exists(settings.CIMExportFilePath))
            {
                Console.WriteLine("Processing CIM export file...");
                CIMExportProcessor.ProcessCIMExportFile(processParams);
            }
            else
            {
                Console.WriteLine($"     CIM export file not found: {settings.CIMExportFilePath}");
                Console.WriteLine("     Skipping CIM export processing...");
            }
        }

        // Verify device CSV file exists before processing
        if (!File.Exists(settings.DeviceCSVFilePath))
        {
            Console.WriteLine($"ERROR: Device CSV file not found: {settings.DeviceCSVFilePath}");
            Console.WriteLine("       Either provide a Devices.csv file or specify a valid CIM export file.");
            return;
        }

        // Process device CSV file
        Console.WriteLine("Processing device CSV file...");
        DeviceCSVProcessor.ProcessDeviceCSV(processParams);

        Console.WriteLine();
        Console.WriteLine("Closing database connection...");

        // Initialize the openPDC DNP3 adapters post configuration updates
        if (settings.InitializeAdapters)
            AdapterInitializer.InitializeAdapters(processParams);

        stopwatch.Stop();
        Console.WriteLine();
        Console.WriteLine("DNP3 Configuration Loader complete!");
        Console.WriteLine($"    Total runtime: {Time.ToElapsedTimeString(stopwatch.Elapsed.TotalSeconds, 2)}");
        Console.WriteLine();
    }

    private static Settings LoadSettings()
    {
        // Define settings for the application
        ConfigSettings settings = new()
        {
            SQLite = ConfigurationOperation.Disabled,
            INIFile = ConfigurationOperation.ReadWrite,
            ConfiguredINIPath = FilePath.GetAbsolutePath("")
        };

        // Define settings for service components
        Settings.DefineSettings(settings, ConfigSettings.SystemSettingsCategory);

        // Bind settings to configuration sources
        settings.Bind(new ConfigurationBuilder()
            .ConfigureGemstoneDefaults(settings));

        settings.Save(true);

        return new Settings();
    }

    private static void CreateIndexes(AdoDataConnection connection)
    {
        // Check if this is a SQL Server connection
        if (connection.DatabaseType != DatabaseType.SQLServer)
            return;

        // Create index on Measurement.PointTag
        Console.WriteLine("Creating database index on Measurement.PointTag...");
        connection.ExecuteNonQuery("""
           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
        Console.WriteLine("Creating database index on Measurement.AlternateTag...");
        connection.ExecuteNonQuery("""
           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
        Console.WriteLine("Creating database index on Measurement.SignalReference...");
        connection.ExecuteNonQuery("""
           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
        Console.WriteLine("Creating database index on Measurement.DeviceID, Measurement.SignalTypeID...");
        connection.ExecuteNonQuery("""
           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
           """);

        // Create index on Measurement.DeviceID (for per-device bulk loading of measurement cache)
        Console.WriteLine("Creating database index on Measurement.DeviceID...");
        connection.ExecuteNonQuery("""
           IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Measurement_DeviceID' AND object_id = OBJECT_ID('Measurement'))
           BEGIN
               CREATE INDEX IX_Measurement_DeviceID ON Measurement (DeviceID)
           END
           """);

        // Create index on Device.Acronym
        Console.WriteLine("Creating database index on Device.Acronym...");
        connection.ExecuteNonQuery("""
           IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Device_Acronym' AND object_id = OBJECT_ID('Device'))
           BEGIN
               CREATE INDEX IX_Device_Acronym ON Device (Acronym)
           END
           """);

        // Create index on CustomActionAdapter.AdapterName
        Console.WriteLine("Creating database index on CustomActionAdapter.AdapterName...");
        connection.ExecuteNonQuery("""
           IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_CustomActionAdapter_AdapterName' AND object_id = OBJECT_ID('CustomActionAdapter'))
           BEGIN
               CREATE INDEX IX_CustomActionAdapter_AdapterName ON CustomActionAdapter (AdapterName)
           END
           """);

        // Create index on CustomFilterAdapter.AdapterName
        Console.WriteLine("Creating database index on CustomFilterAdapter.AdapterName...");
        connection.ExecuteNonQuery("""
           IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_CustomFilterAdapter_AdapterName' AND object_id = OBJECT_ID('CustomFilterAdapter'))
           BEGIN
               CREATE INDEX IX_CustomFilterAdapter_AdapterName ON CustomFilterAdapter (AdapterName)
           END
           """);
    }
}
