using System.Data;
using IWS5.Server.Models.Export;
using Microsoft.Data.SqlClient;

namespace IWS5.Server.DataAccess;

public static class DocumentRepository
{
    internal static List<ATDocument> GetDocuments(SqlConnection sqlConnection, int workItemId, SqlTransaction transaction = null, params string[] fileExtensions)
{
    var documents = new List<ATDocument>();
    
    string documentQuery;
    SqlCommand selectDocumentsToConvertCmd;
    
    if (fileExtensions.Length == 0)
    {
        documentQuery =
            @"SELECT [DocumentId], [DocumentName], [WorkItemId], [Document], d.CabinetId as CabinetId, dc.CabinetName as CabinetName
                  FROM [dbo].[ATDocument] d
                  JOIN [dbo].[ATDocumentCabinet] dc on d.CabinetId = dc.CabinetID
                  WHERE [WorkItemId]  = @workItemId";
            
        selectDocumentsToConvertCmd = new SqlCommand(documentQuery, sqlConnection, transaction);
        selectDocumentsToConvertCmd.Parameters.AddWithValue("@workItemId", workItemId);
    }
    else
    {
        documentQuery = $@"
                SELECT [DocumentId], [DocumentName], [WorkItemId], [Document], d.CabinetId as CabinetId, dc.CabinetName as CabinetName
                FROM [dbo].[ATDocument] d
                JOIN [dbo].[ATDocumentCabinet] dc on d.CabinetId = dc.CabinetID
                WHERE [WorkItemId] = @workItemId
                  AND LOWER(
                        CASE 
                            WHEN CHARINDEX('.', REVERSE(DocumentName)) > 0 
                            THEN RIGHT(DocumentName, CHARINDEX('.', REVERSE(DocumentName)) - 1)
                            ELSE ''
                        END
                      ) IN ('{string.Join("\', \'", fileExtensions)}')";
                  
        selectDocumentsToConvertCmd = new SqlCommand(documentQuery, sqlConnection, transaction);
        selectDocumentsToConvertCmd.Parameters.AddWithValue("@workItemId", workItemId);
    }
    
    var selectDocumentsToConvertDap = new SqlDataAdapter(selectDocumentsToConvertCmd);
    var selectDocumentsToConvertDs = new DataSet();
    selectDocumentsToConvertDap.Fill(selectDocumentsToConvertDs);

    if (selectDocumentsToConvertDs.Tables[0].Rows.Count == 0)
    {
        return documents;
    }
    
    foreach (DataRow dataRow in selectDocumentsToConvertDs.Tables[0].Rows)
    {
        documents.Add(new ATDocument
        {
            Id = (int)dataRow["DocumentId"],
            Name = dataRow["DocumentName"].ToString(),
            WorkItemId = workItemId,
            File = dataRow["Document"] as byte[],
            CabinetName = dataRow["CabinetName"] != DBNull.Value ? dataRow["CabinetName"].ToString() : null,
            CabinetId = (int)dataRow["CabinetId"]
        });
    }
    
    return documents;
}
}