export aggregate_labels, audiodata_db

export aggregate_labels, audiodata_db, raven_of_avianz, avianz_of_raven, label_summary

using CSV, DataFrames, Glob, Random, DBInterface, DuckDB, JSON3

using CSV, DataFrames, Glob, Random, DBInterface, DuckDB, JSON3, Dates

end
function avianz_file_of_dict(f, payload)
    isfile(f) && cp(f, "$f.backup")
    open(f, "w") do io
        JSON3.write(io, payload)
    end

# map(x -> avianz_of_raven!(x), a)

# map(x -> avianz_of_raven(x), a)

function avianz_of_raven!(f::String)

function avianz_of_raven(f::String)

    avianzf = f * ".data"
    open(avianzf, "w") do io
        JSON3.write(io, data)
        println(io)

    basef = f = replace(f, "Table.1.selections.txt" => "")
    isfile((basef * ".WAV")) ? avianzf = basef * "WAV.data" : avianzf = basef * "wav.data"
    avianz_file_of_dict(avianzf, data)
end
function dict_of_avianz_file(f)
    json_string = read(f, String)
    x = JSON3.read(json_string)
    return x
end
function df_of_avianz_dict(data)
    df = DataFrame(
        start_time = Float64[],
        end_time = Float64[],
        low_f = Float64[],
        high_f = Float64[],
        Species = String[],
    )
    for (index1, item1) in enumerate(data[2:end])
        st, en, lf, hf = item1[1:4]
        sp = map(x -> x[:species], item1[5])
        lsp = length(sp)
        for (index2, item2) in enumerate(sp)
            push!(
                df,
                (
                    start_time = st,
                    end_time = en,
                    low_f = lf,
                    high_f = hf,
                    Species = sp[index2],
                ),
            )
        end

    #isfile(avianzf) && rm(f)

    return df
end
function prepare_df_for_raven(data)
    l = length(data.start_time)
    df = DataFrame(
        "Selection" => collect(1:l),
        "View" => ["Spectrogram 1" for x = 1:l],
        "Channel" => [1 for x = 1:l],
        "Begin Time (s)" => data.start_time,
        "End Time (s)" => data.end_time,
        "Low Freq (Hz)" => data.low_f,
        "High Freq (Hz)" => data.high_f,
        "Species" => data.Species,
    )
    return df
end
# using CSV, DataFrames, JSON3
function raven_of_avianz(file::String)
    data = dict_of_avianz_file(file)
    if length(data) >= 2 #ignores empty .data files
        df = df_of_avianz_dict(data) |> prepare_df_for_raven
        outfile = replace(
            file,
            ".WAV.data" => ".Table.1.selections.txt",
            ".wav.data" => ".Table.1.selections.txt",
        )
        CSV.write(outfile, df, delim = '\t')
    end

    end
    function read_avianz_data(f)
        json_string = read(f, String)
        x = JSON3.read(json_string)
        return x
    end
    function write_new_file!(f, payload)
        cp(f, "$f.backup")
        open(f, "w") do io
            JSON3.write(io, payload)
        end

        x = read_avianz_data(file)

        x = dict_of_avianz_file(file)

                    write_new_file!(file, payload)

                    avianz_file_of_dict(file, payload)

function label_summary(folder::String, avianz = true)
    if avianz == true
        files = glob("$folder/*.['W','w']['A','a']['V','v'].data")
        df = DataFrame(
            start_time = Float64[],
            end_time = Float64[],
            low_f = Float64[],
            high_f = Float64[],
            Species = String[],
            File = String[],
        )
        for file in files
            df1 = dict_of_avianz_file(file) |> x -> df_of_avianz_dict(x)
            f = split(file, "/")[end] |> x -> replace(x, ".data" => "")
            df1.File = ["$f" for x = 1:length(df1.start_time)]
            df = vcat(df, df1)
        end
        select!(df, [:File, :start_time, :end_time, :low_f, :high_f, :Species])
        CSV.write("$folder/label_summary_avianz-$(Dates.today()).csv", df, delim = '\t')
        return df
    else
        files = glob("$folder/*.Table.1.selections.txt")
        df = DataFrame(
            File = String[],
            start_time = Float64[],
            end_time = Float64[],
            low_f = Float64[],
            high_f = Float64[],
            Species = String[],
        )
        for file in files
            df1 = DataFrame(CSV.File(file))
            for d in eachrow(df1)
                f =
                    split(file, "/")[end] |>
                    x -> replace(x, ".Table.1.selections.txt" => "")
                push!(
                    df,
                    (
                        File = f,
                        start_time = d."Begin Time (s)",
                        end_time = d."End Time (s)",
                        low_f = d."Low Freq (Hz)",
                        high_f = d."High Freq (Hz)",
                        Species = d."Species",
                    ),
                )
            end
        end
        CSV.write("$folder/label_summary_raven-$(Dates.today()).csv", df, delim = '\t')
        return df
    end
end