using System.Data;
using IWS5.Core.Enums;
using IWS5.Server.Models.Tracking;
using Microsoft.Data.SqlClient;

namespace IWS5.Server.DataAccess;

public static class TrackingRepository
{
    internal static TrackingDefinition GetTrackingDefinition(SqlConnection connection, int processId, int eventId)
    {
        TrackingDefinition trackingDefinition = null;
        const string sqlStr =
            @"SELECT TOP (1) Id, EventName, Description, Message, EnableExternal FROM ATTrackingDefinition With (NoWait) 
                           WHERE ProcessId = @processId AND EventId = @eventId AND Enable = @enabled";

        SqlCommand command = new SqlCommand(sqlStr, connection);
        command.Parameters.AddWithValue("@processId", processId);
        command.Parameters.AddWithValue("@eventId", eventId);
        command.Parameters.AddWithValue("@enabled", true);

        SqlDataAdapter dap2 = new SqlDataAdapter(command);
        DataSet ds2 = new DataSet();
        dap2.Fill(ds2);

        int rowCnt2 = ds2.Tables[0].Rows.Count;

        if (rowCnt2 == 0)
        {
            return trackingDefinition;
        }

        var row = ds2.Tables[0].Rows[0];
        trackingDefinition = new TrackingDefinition
        {
            Id = int.TryParse(row["Id"].ToString(), out var id) ? id : 0,
            Message = row["Message"].ToString(),
            EnabledExternal = bool.TryParse(row["EnableExternal"].ToString(), out var enabledExternal) &&
                              enabledExternal
        };

        return trackingDefinition;
    }

    internal static void InsertTrackingRecord(SqlConnection connection, int trackingDefinitionId, int workItemId,
        string lastAction, int sourceQueueId, string sourceUser, string buttonId, string message)
    {
        const string sqlStr = @"
            Insert Into ATTrackingRecord (TrackingDate, WorkItemId, UserName, TrackingDefinitionId, LastActionId, Message) 
            Values (@trackingDate, @workItemId,@userName, @trackingDefinitionId, @lastActionid, @message)";

        SqlCommand cmd2 = new SqlCommand(sqlStr, connection);
        cmd2.Parameters.AddWithValue("@trackingDate", DateTime.Now);
        cmd2.Parameters.AddWithValue("@workItemId", workItemId);
        if ((sourceQueueId > 0 || string.IsNullOrEmpty(lastAction)) && !string.IsNullOrEmpty(sourceUser))
        {
            cmd2.Parameters.AddWithValue("@userName", sourceUser);
        }
        else
        {
            cmd2.Parameters.AddWithValue("@userName", DBNull.Value);
        }

        cmd2.Parameters.AddWithValue("@trackingDefinitionId", trackingDefinitionId);
        if (!string.IsNullOrEmpty(buttonId))
            cmd2.Parameters.AddWithValue("@lastActionId", buttonId);
        else
            cmd2.Parameters.AddWithValue("@lastActionId", DBNull.Value);
        cmd2.Parameters.AddWithValue("@message", message);
        cmd2.ExecuteNonQuery();
    }

    internal static List<ExternalTrackingTemplate> GetExternalTrackingData(SqlConnection connection,
        int trackingDefinitionId)
    {
        var externalTrackingTemplates = new List<ExternalTrackingTemplate>();
        const string sqlStr =
            @"SELECT t.Id, t.Name, T.DbName, c.ConnectionString
              FROM [dbo].[ATTrackingDefinitionTemplates] AS tdt
              JOIN ATTemplates t ON tdt.TemplateId = t.Id
              JOIN ATConnection c ON t.DbConnectionId = c.Id
              WHERE tdt.TrackingDefinitionId = @trackingDefinitionId        
              ORDER BY tdt.[Order]      
              
              SELECT etd.Id, tdt.TrackingDefinitionId, tdt.TemplateId, etd.DBTableName, etd.DbColumnName, etd.SourceTypeId, etd.SourceValue, etd.DbColumnName, etd.DBDataType
                FROM [dbo].[ATTrackingDefinitionTemplates] tdt
                JOIN ATExternalTrackingDefinition etd ON tdt.TemplateId = etd.TemplateId
                WHERE tdt.TrackingDefinitionId = @trackingDefinitionId";

        SqlCommand command = new SqlCommand(sqlStr, connection);
        command.Parameters.AddWithValue("@trackingDefinitionId", trackingDefinitionId);
        SqlDataAdapter dap = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        dap.Fill(ds);

        int templatesRowsCount = ds.Tables[0].Rows.Count;
        if (templatesRowsCount == 0)
        {
            return externalTrackingTemplates;
        }
        
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            var externalTrackingTemplate = new ExternalTrackingTemplate
            {
                Id = int.Parse(row["Id"].ToString()),
                Name = row["Name"].ToString(),
                ConnectionString = row["ConnectionString"].ToString(),
                DbName = row["DbName"].ToString(),
                ExternalTrackingDefinitions = new List<ExternalTrackingDefinition>()
            };
            externalTrackingTemplates.Add(externalTrackingTemplate);
        }
        

        foreach (DataRow row in ds.Tables[1].Rows)
        {
            var templateId = int.Parse(row["TemplateId"].ToString());
            var externalTrackingDefinition = new ExternalTrackingDefinition
            {
                Id = int.Parse(row["Id"].ToString()),
                TrackingDefinitionId = int.Parse(row["TrackingDefinitionId"].ToString()),
                TemplateId = templateId,
                DbTableName = row["DBTableName"].ToString(),
                DbColumnName = row["DbColumnName"].ToString(),
                SourceType = (ATExternalTrackingSourceType)Enum.Parse(typeof(ATExternalTrackingSourceType),
                    row["SourceTypeId"].ToString()),
                SourceValue = row["SourceValue"].ToString(),
                DbDataType = row["DBDataType"].ToString()
            };
            var requiredTemplate = externalTrackingTemplates.First(template => template.Id == templateId);
            if (requiredTemplate.ExternalTrackingDefinitions == null)
            {
                requiredTemplate.ExternalTrackingDefinitions = [externalTrackingDefinition];
            }
            else
            {
                requiredTemplate.ExternalTrackingDefinitions.Add(externalTrackingDefinition);
            }
        }

        return externalTrackingTemplates;
    }
}