using System.Data;
using IWS5.Core.Enums;
using IWS5.Server.Enums;
using IWS5.Server.Models.DocumentConversion;
using IWS5.Server.Models.Export;
using IWS5.Server.Models.TriggerActionModels;
using Microsoft.Data.SqlClient;

namespace IWS5.Server.DataAccess;

public static class DefinitionRepository
{
    internal static List<ATExportDefinition> GetExportDefinitions(SqlConnection sqlConnection,
        List<int> exportTemplates)
    {
        var exportDefinitions = new List<ATExportDefinition>();

        var selectExpDefSql =
            @$"SELECT ATExportDefinition.Id, ProcessId, TemplateId, eft.Name AS DoneFlag, ExportFileFormatId, idt.[TypeName] as ExportFileFormat
              FROM ATExportDefinition
              LEFT JOIN ATExportFlagTxt eft ON ATExportDefinition.DoneFlagId = eft.Id
              LEFT JOIN ATImportDocumentType idt ON ATExportDefinition.ExportFileFormatId = idt.Id
              WHERE TemplateId IN ({string.Join(", ", exportTemplates)})
              ORDER By TemplateId, Id";

        var selectExpDefCmd = new SqlCommand(selectExpDefSql, sqlConnection);
        var selectExpDefDap = new SqlDataAdapter(selectExpDefCmd);
        var selectExpDefDs = new DataSet();
        selectExpDefDap.Fill(selectExpDefDs);

        if (selectExpDefDs.Tables[0].Rows.Count == 0)
        {
            return exportDefinitions;
        }

        foreach (DataRow dataRow in selectExpDefDs.Tables[0].Rows)
        {
            exportDefinitions.Add(new ATExportDefinition
            {
                Id = (int)dataRow["Id"],
                ProcessId = (int)dataRow["ProcessId"],
                TemplateId = (int)dataRow["TemplateId"],
                DoneFlag = dataRow["DoneFlag"] != DBNull.Value ? dataRow["DoneFlag"].ToString() : null,
                ExportFileFormatId = dataRow["ExportFileFormatId"] != DBNull.Value
                    ? (int)dataRow["ExportFileFormatId"]
                    : 0,
                ExportFileFormat = dataRow["ExportFileFormat"] != DBNull.Value
                    ? dataRow["ExportFileFormat"].ToString()
                    : null,
            });
        }

        return exportDefinitions;
    }

    internal static List<ATConvertDefinition> GetConvertDefinitionsOrdered(SqlConnection sqlConnection,
        List<int> convertTemplates, SqlTransaction transaction)
    {
        var convertDefinitions = new List<ATConvertDefinition>();

        var selectConvertDefSql = @"
        SELECT 
            cd.Id, 
            ProcessId, 
            TemplateId, 
            sourceType.TypeName as SourceDocType, 
            resultType.TypeName as ExportDocType, 
            cd.MergeOrderId,
            MergeDocs, 
            KeepInMemory, 
            CoverSheet, 
            Barcodes, 
            cd.BarcodesFontId,
            bf.Name BarcodeFontName,
            cd.ReplaceDocument
        FROM [dbo].[ATConvertDefinition] cd
        LEFT JOIN [dbo].[ATImportDocumentType] as sourceType ON cd.SourceDocTypeId = sourceType.Id
        LEFT JOIN [dbo].[ATImportDocumentType] as resultType ON cd.ExportDocTypeId = resultType.Id
        LEFT JOIN [dbo].[ATBarcodeFont] bf ON cd.BarcodesFontId = bf.Id
        WHERE TemplateId IN (" + string.Join(", ", convertTemplates) + @")
        ORDER BY TemplateId, Id";

        var selectConvDefCmd = new SqlCommand(selectConvertDefSql, sqlConnection, transaction);
        var selectConvDefDap = new SqlDataAdapter(selectConvDefCmd);
        var selectConvDefDs = new DataSet();
        selectConvDefDap.Fill(selectConvDefDs);

        foreach (DataRow dataRow in selectConvDefDs.Tables[0].Rows)
        {
            convertDefinitions.Add(new ATConvertDefinition
            {
                Id = (int)dataRow["Id"],
                ProcessId = (int)dataRow["ProcessId"],
                TemplateId = (int)dataRow["TemplateId"],
                SourceDocType = dataRow["SourceDocType"] != DBNull.Value ? dataRow["SourceDocType"].ToString() : null,
                ExportDocType = dataRow["ExportDocType"] != DBNull.Value ? dataRow["ExportDocType"].ToString() : null,
                ApplyMergeDocs = dataRow["MergeDocs"] != DBNull.Value && (bool)dataRow["MergeDocs"],
                MergeOrder = dataRow["MergeDocs"] != DBNull.Value &&
                             int.TryParse(dataRow["MergeOrderId"].ToString(), out var mergeOrder)
                    ? (MergeOrder)mergeOrder
                    : MergeOrder.DocumentNameAsc,
                ApplyCoverSheet = dataRow["CoverSheet"] != DBNull.Value && (bool)dataRow["CoverSheet"],
                ApplyBarcode = dataRow["Barcodes"] != DBNull.Value && (bool)dataRow["Barcodes"],
                BarcodeFont = dataRow["BarcodesFontId"] != DBNull.Value &&
                              int.TryParse(dataRow["BarcodesFontId"].ToString(), out var barCodeFont)
                    ? (ATBarcodeFont?)barCodeFont
                    : null,
                BarcodeFontName = dataRow["BarcodeFontName"] != DBNull.Value
                    ? dataRow["BarcodeFontName"].ToString()
                    : null,
                ReplaceDocument = dataRow["ReplaceDocument"] != DBNull.Value && (bool)dataRow["ReplaceDocument"]
            });
        }

        return convertDefinitions;
    }

    internal static List<ATTriggerDefinition> GetTriggerDefinitions(SqlConnection sqlConnection, int eventId, ATTriggerHook? hook = null)
    {
        var triggerDefinitions = new List<ATTriggerDefinition>();

        string selectTriggerDefSql = @"
        SELECT TOP (1000)
            etd.Id,
            etd.ProcessId,
            etd.TemplateId,
            t.Name AS TemplateName,
            etd.TriggerName,
            ett.[Order],
            etd.HookId,
            etd.ActionId,
            etd.Description,
            etd.TriggerAction
        FROM [dbo].[ATEventTriggerTemplate] ett
        JOIN [dbo].[ATEventTriggerDefinition] etd ON ett.TemplateId = etd.TemplateId
        JOIN [dbo].ATTemplates t ON ett.TemplateId = t.Id
        WHERE ett.EventId = @EventId AND ett.Enabled = @enabled ";

        if (hook.HasValue)
        {
            selectTriggerDefSql += " AND etd.HookId = @HookId ";
        }
        
        selectTriggerDefSql += " ORDER BY ett.TemplateId";

        using var selectTriggerDefCmd = new SqlCommand(selectTriggerDefSql, sqlConnection);
        selectTriggerDefCmd.Parameters.AddWithValue("@EventId", eventId);
        selectTriggerDefCmd.Parameters.AddWithValue("@enabled", true);
        if (hook.HasValue)
        {
            selectTriggerDefCmd.Parameters.AddWithValue("@HookId", (int)hook.Value);
        }

        using var adapter = new SqlDataAdapter(selectTriggerDefCmd);
        var dataSet = new DataSet();
        adapter.Fill(dataSet);

        if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
            return triggerDefinitions;

        foreach (DataRow row in dataSet.Tables[0].Rows)
        {
            triggerDefinitions.Add(new ATTriggerDefinition
            {
                Id = row["Id"] != DBNull.Value ? (int)row["Id"] : 0,
                ProcessId = row["ProcessId"] != DBNull.Value ? (int)row["ProcessId"] : 0,
                TemplateId = row["TemplateId"] != DBNull.Value ? (int)row["TemplateId"] : 0,
                TemplateName = row["TemplateName"] != DBNull.Value ? row["TemplateName"].ToString() : null,
                TriggerName = row["TriggerName"] != DBNull.Value ? row["TriggerName"].ToString() : null,
                Order = row["Order"] != DBNull.Value ? Convert.ToInt32(row["Order"]) : 0,
                Hook = row["HookId"] != DBNull.Value ? (ATTriggerHook)Convert.ToInt32(row["HookId"]) : default,
                Action = row["ActionId"] != DBNull.Value ? (ATTriggerAction)Convert.ToInt32(row["ActionId"]) : default,
                Description = row["Description"] != DBNull.Value ? row["Description"].ToString() : null,
                TriggerAction = row["TriggerAction"] != DBNull.Value ? row["TriggerAction"].ToString() : null
            });
        }

        return triggerDefinitions;
    }
}