using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Text.Json;

namespace Mesa_Backlog.Library;

public class ExcelReader
{

    /// <summary>
    /// https://social.msdn.microsoft.com/Forums/en-US/2e030743-5d66-4e53-bbff-bb2eee0cbc9b/readingwriting-excel-without-excel?forum=Vsexpressvcs
    /// </summary>
    private static DataTable GetSheet(string fileName, string selectSql)
    {
        DataTable results = new();
#if Linux
            ()("Built on Linux!");
#elif OSX
            ()("Built on macOS!");
#elif Windows || !NETCORE
#pragma warning disable CA1416
        for (int i = 0; i < int.MaxValue; i++)
        {
            try
            {
                OleDbConnectionStringBuilder connectionStringBuilder = new()
                {
                    Provider = "Microsoft.ACE.OLEDB.12.0",
                    DataSource = fileName
                };
                connectionStringBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=1;ReadOnly=1;");
                using OleDbConnection connection = new(connectionStringBuilder.ConnectionString);
                connection.Open();
                using (OleDbDataAdapter adapter = new(selectSql, connection))
                    adapter.Fill(results);
                connection.Close();
                break;
            }
            catch (Exception)
            {
                if (i > 3) //2019-12-28 - 001
                    throw;
                Thread.Sleep(1000);
            }
#pragma warning restore CA1416
        }
#else
            ()("Built in unknown!");
#endif
        return results;
    }

    private static string DataTableToJSON(DataTable table)
    {
        string name;
        string value;
        object @object;
        StringBuilder jsonString = new();
        if (table.Rows.Count > 0)
        {
            _ = jsonString.Append('[');
            for (int i = 0; i < table.Rows.Count; i++)
            {
                _ = jsonString.Append('{');
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    @object = table.Rows[i][j];
                    if (@object is null)
                        value = string.Empty;
                    else
                        value = string.Concat(@object);
                    if (value.Contains('"'))
                        value = value.Replace("\"", "\\\"");
                    if (value.Contains('\n'))
                        value = value.Replace("\t", " ").Replace("\n", "<br />");
                    name = table.Columns[j].ColumnName.ToString();
                    if (name == ",")
                        name = "Title";
                    if (name.Contains('"'))
                        name = name.Replace("\"", "\\\"");
                    if (name.Contains('\n'))
                        name = name.Replace("\t", " ").Replace("\n", "<br />");
                    _ = jsonString.Append('"').Append(name).Append("\":").Append('"').Append(value).Append('"');
                    if (j < table.Columns.Count - 1)
                        _ = jsonString.Append(',');
                }
                _ = jsonString.Append('}');
                if (i != table.Rows.Count - 1)
                    _ = jsonString.Append(',');
            }
            _ = jsonString.Append(']');
        }
        return jsonString.ToString();
    }

    public static string GetJson(string reportFullPath, string sheet)
    {
        string result;
        string selectSql = string.Concat("SELECT * FROM [", sheet, "$]");
        using (DataTable dataTable = GetSheet(reportFullPath, selectSql))
        {
            if (dataTable.Rows.Count == 0)
                throw new Exception("No rows");
            result = DataTableToJSON(dataTable);
        }
        return result;
    }

    public static FIBacklogMesa[] GetFIBacklogMesaCollection(string json)
    {
        FIBacklogMesa[]? results;
        results = JsonSerializer.Deserialize<FIBacklogMesa[]>(json, new JsonSerializerOptions() { PropertyNameCaseInsensitive = true });
        if (results is null || !results.Any())
            throw new NullReferenceException();
        return results;
    }

    public static string GetJson(FIBacklogMesa[]? fIBacklogMesaCollection) => JsonSerializer.Serialize(fIBacklogMesaCollection, new JsonSerializerOptions() { WriteIndented = true });

}