module FiordlandPacks

export metrics, metrics_plot

using Airtable, CSV, DataFrames, Dates, HTTP, Indicators, JSON3, PrettyTables, UnicodePlots, VegaLite

"""

"""
function metrics(date::String, invoices::Int)
    @show make_visitor_plot(date)
    @show build_page_rank_table(date)
    @show north_star(date)
    @show airtable_sales_for_period(date)
    @show sales(date, invoices) #number of invoices sent MYOB, PayPal  
end

# update csv manually because script is sometimes wrong for now

function metrics_plot()
    if Sys.isapple()
        file = "/Users/david/julia/FiordlandPacks/images_and_tables/FiordlandPacks.csv"
    else
        file = "/media/david/SSD1/FiordlandPacks/images_and_tables/FiordlandPacks.csv"
    end
    df = DataFrame(CSV.File(file))
    transform!(df, [:Shopify_Sales, :Invoice_Sales] => (a, b) -> a .+ b)  
    select!(df, :Week, :Contact_Submit, :Pack_Builder_Clicks, :Checkouts_Created, :Customer_Actions, 
        :Shopify_Sales, :Invoice_Sales, :Shopify_Sales_Invoice_Sales_function => "Sales", :Google_Spend)   
    table = pretty_table(last(df, 12); title="Metrics", 
        header=["Week", "Contact", "Builder", "Checkout", "ACTIONS", "Shopify", "Invoice", "Sales", "Google"], 
        display_size=(30, 300))

    df2 = Base.stack(df, [:Customer_Actions, :Sales, :Google_Spend])
    transform!(df2, :Week => ByRow(x -> Dates.format(x, "yyyy-mm-dd")))
    rename!(df2, :variable => "Metric", :value => "Number")
    df2 |>
    @vlplot(
        :line,
        x = :Week,
        y = :Number,
        color = :Metric,
        title = "Metrics Plot",
        width = 400,
        height = 400
    ) |>
    if Sys.isapple()
        save("/Users/david/julia/FiordlandPacks/images_and_tables/metrics_fiordland_packs.png")
    else
        save("/media/david/SSD1/FiordlandPacks/images_and_tables/metrics_fiordland_packs.png")
    end
    return table
end

### PLAUSIBLE

# Needs ENV["Plausible"] = "" in startup.jl
function query_plausible(url::String)
    headers = ["Authorization" => "Bearer $(ENV["Plausible"])"]
    data = JSON3.read(HTTP.get(url, headers).body)
    return data.results
end

# +5 day because of moving average, need refactor later.
function get_traffic(end_date::Dates.Date, days::Int, SITE_ID::String="fiordlandpacks.nz")::DataFrame
    start_date = end_date - Dates.Day(days + 5)

    url = "https://plausible.io/api/v1/stats/timeseries?site_id=$SITE_ID&period=custom&date=$(dts(start_date)),$(dts(end_date))"
    df = DataFrame(query_plausible(url))

    return df
end

"""
make_visitor_plot(date::String)

Takes a date in string form yy-mm-dd, number of days defaults to 30.
Returns a unicode plot of unique site visits, compared to 1 year ago.
"""
function make_visitor_plot(date::String, days::Int=30)
    current = std(date)
    if current == Dates.today()
        current -= Dates.Day(1)
    end
    last_year = current - Dates.Year(1)

    c = get_traffic(current, days)[!, 2] |> x -> Indicators.sma(x, n=7) |> x -> last(x, days) |> x -> round.(x, digits=1)
    p = get_traffic(last_year, days)[!, 2] |> x -> Indicators.sma(x, n=7) |> x -> last(x, days) |> x -> round.(x, digits=1)

    plt = lineplot(collect(-days:-1), [c p], color=[:blue :yellow], title="Unique Visitors: 7 day moving average", name=["Last $days Days", "Last Year"], xlabel="Day", ylabel="Visitors", canvas=DotCanvas, border=:ascii)

    vcat(DataFrame("Days" => collect(-days:-1), "Visits" => c, "Period" => "Last $days Days"), DataFrame("Days" => collect(-days:-1), "Visits" => p, "Period" => "Previous Year")) |>
    @vlplot(
        :line,
        x = :Days,
        y = :Visits,
        color = :Period,
        title = "Unique Visitors: 7 Day Moving Average",
        width = 400,
        height = 400
    ) |>
    if Sys.isapple()
        save("/Users/david/julia/FiordlandPacks/images_and_tables/visits_fiordland_packs.png")
    else
        save("/media/david/SSD1/FiordlandPacks/images_and_tables/visits_fiordland_packs.png")
    end
    return plt
end

# Date to string formatter
function dts(date::Dates.Date)::String
    return Dates.format(date, "yyyy-mm-dd")
end

# String to date formatter
function std(date::String)::Dates.Date
    return Date("$date", dateformat"y-m-d")
end

function get_page_rank(end_date::Dates.Date, days::Int, SITE_ID::String="fiordlandpacks.nz")::DataFrame
    start_date = end_date - Dates.Day(days)
    url = "https://plausible.io/api/v1/stats/breakdown?site_id=$SITE_ID&period=custom&date=$(dts(start_date)),$(dts(end_date))&property=event:page&limit=10"
    df = DataFrame(query_plausible(url))
    rename!(df, :page => "$(days)_days")
    select!(df, Not(:visitors))
    return df
end

"""
build_page_rank_table(date::String)

Takes a date in string form yy-mm-dd.
Returns a pretty table of top 10 pages for 7, 30, 365 days.
Make sure repl window is wide enough
"""
function build_page_rank_table(date::String)
    current = Date("$date", dateformat"y-m-d")
    if current == Dates.today()
        current -= Dates.Day(1)
    end

    week = get_page_rank(current, 7)
    month = get_page_rank(current, 30)
    year = get_page_rank(current, 365)
    table = hcat(week, month, year)
    return pretty_table(table; title="Page Rank", header=["week", "month", "year"])
end

# "Outbound+Link:+Click", "Contact+Submit"
function get_custom_events(end_date::Dates.Date, event::String, days::Int=7, SITE_ID::String="fiordlandpacks.nz")::@NamedTuple{Event::String, Number::Int64}
    start_date = end_date - Dates.Day(days)
    url = "https://plausible.io/api/v1/stats/breakdown?site_id=$SITE_ID&period=custom&date=$(dts(start_date)),$(dts(end_date))&property=event:props:method&filters=event:name%3D%3D$event"
    df = DataFrame(query_plausible(url))
    e = replace(event, "+" => " ") |> x -> titlecase(x)
    isempty(df) ? (return (Event=e, Number=0)) : (return (Event=e, Number=first(df.visitors)))
end

# "thank_you", "checkouts"
function get_pageview_events(end_date::Dates.Date, event::String, days::Int=7, SITE_ID::String="fiordlandpacks.nz")::@NamedTuple{Event::String, Number::Int64}
    start_date = end_date - Dates.Day(days)
    url = "https://plausible.io/api/v1/stats/breakdown?site_id=$SITE_ID&period=custom&date=$(dts(start_date)),$(dts(end_date))&property=event:props:method&filters=event:page==/**$event**"
    df = DataFrame(query_plausible(url))
    e = replace(event, "_" => " ") |> x -> titlecase(x)
    isempty(df) ? (return (Event=e, Number=0)) : (return (Event=e, Number=first(df.visitors)))
end

function north_star(date::String, days::Int=7)
    d = std(date)
    contact = get_custom_events(d, "Contact+Submit", days)
    pack_builders = get_custom_events(d, "Outbound+Link:+Click", days)
    checkouts = get_pageview_events(d, "checkouts", days)
    df = DataFrame([contact, pack_builders, checkouts])
    push!(df, ["Total" sum(df.Number)])
    df[2, 1] = "Pack Builders"
    return pretty_table(df; title="North Star Metric", header=["Event", "Number"], body_hlines=[3])
end

function sales(date::String, invoices_sent::Int, days::Int=7)
    d = std(date)
    shopify = get_pageview_events(d, "thank_you", days)
    inv = (Event="Invoice", Number=invoices_sent)
    df = DataFrame([shopify, inv])
    push!(df, ["Total" sum(df.Number)])
    df[1, 1] = "Shopify"
    return pretty_table(df; title="Sales", header=["", "Number"], body_hlines=[2])
end

### AIRTABLE
base = (key="appt7SR7DsTsh22zO", name="Pack Builders")
tables = [
    #(key="tblZzSmPq7LhdHUFk", name="25l Strong"),
    #(key="tbltssUMDKNkjC8ld", name="35l"),
    (key="tbldrQLd5cO2D1XEb", name="45l"),
    (key="tbl1JARaob1EhEv2l", name="55l"),
    (key="tbl8NXUqwPniIOxbM", name="65l") #=,
    (key="tblp5cmBqyD4SY9zv", name="AC50"),
    (key="tblKCK6N2BapDupSF", name="AC60"),
    (key="tblSLbNuFaOb0Qorp", name="AC70"),
    (key="tbl9Etx9No0cPn11A", name="Waist Bag")=#
]



function table_sales_for_period(table::String, end_date::Dates.Date, days::Int=7, base::String="appt7SR7DsTsh22zO")::DataFrame
    start_date = end_date - Dates.Day(days)
    q = AirTable(table, AirBase(base))#; filterByFormula="{Status} = 'Todo'")
    data = [getfield(i, 3) for i in Airtable.query(q)]
    # https://discourse.julialang.org/t/construct-dataframe-from-uneven-named-tuples/102970
    cols = union(keys.(data)...)
    df = DataFrame([c => get.(data, c, missing) for c in cols]...)
    df.Created = Date.(chop.(df.Created, tail=14), "yyyy-mm-dd")
    function is_between(date::Dates.Date, start_date::Dates.Date=start_date, end_date::Dates.Date=end_date)::Bool
        return (start_date <= date <= end_date)
    end
    subset!(df, :Created => x -> is_between.(x), skipmissing=true)
    sort!(df)
    return df
end

"""
airtable_sales_for_period(date::String)

Takes a date in string form yy-mm-dd, tables default to variable list above, number of days defaults to f, base defaults to pack builders.
Returns a returns a pretty table of last 7 days sales.
Note sales recorded in jotform all default to 2023-08-18, the date they were imported into airtable.
"""
function airtable_sales_for_period(date::String, tables=tables::Vector{@NamedTuple{key::String, name::String}}, days::Int=7, base::String="appt7SR7DsTsh22zO")
    end_date = Date("$date", dateformat"y-m-d")
    if end_date == Dates.today()
        end_date -= Dates.Day(1)
    end

    for t in tables
        df = table_sales_for_period(t.key, end_date)
        select!(df, :Number, :Created, :Name)
        !isempty(df) && println(pretty_table(df; title="$(t.name)", header=["Order Number", "Date", "Name"], crop=:none), "\n") #, display_size = (30, 300)
    end
    return
end

# /admin/orders.json?created_at_min=2012-08-01 12:00&limit=2012-08-01 12:00
function shopify_sales_for_period(end_date::Dates.Date, days::Int=7)::DataFrame
    start_date = end_date - Dates.Day(days)

end

### JOTFORM

#=
# Needs ENV["Jotform"] = "" in startup.jl
# Query api
k=FiordlandPacks.query_jotform("https://api.jotform.com/user/submissions")

# save json to file
using JSON3
open("my2.json", "w") do f
   JSON3.pretty(f, JSON3.write(k))
   println(f)
end

# ExpandNestedData.jl -> works badly on this json, crap api.

function query_jotform(url::String)
    tail = "?apiKey=$(ENV["Jotform"])"
    data = JSON3.read(HTTP.get((url*tail)).body)
    return data.content
end

using CSV, DataFrames, Dates, DataFramesMeta

# example file: "Downloads/25l_Strong_Pack_Builder2023-08-16_08_54_03.csv"
# Note: mutates the input file, just easier.
function  jotform_csv_to_airtable!(file::String) 
    df=DataFrame(CSV.File(file))

    @transform!(df, @byrow :Date = parse(Date, :"Submission Date", Dates.DateFormat("u d, Y")))
    f(x) = ismissing(x) ? "" : strip(x) * "\n"
    g(x) = ismissing(x) ? "" : string(x)
    @transform!(df, @byrow :Address = (f(:"Street Address") * f(:"Street Address Line 2") * f(:"City") * f(:"State / Province") * g(:"Postal / Zip Code")))
    @transform!(df, @byrow :Name = titlecase(strip(:"First Name") * " " * strip(:"Last Name")))
    select!(df, :Date, :Name, :Address, :)
    select!(df, Not([:"Submission Date", :"Street Address", :"Street Address Line 2", :"City", :"State / Province", :"Postal / Zip Code", :"First Name", :"Last Name"]))
    rename!(df, :"Side Panels (assuming you have the pack on)" => "Side Panels", :"Side Pockets (assuming you have the pack on)" => "Side Pockets", "Removable Strap Bottle Pockets (+\$25 ea)" => "Removable Strap Bottle Pockets", "Waist Belt Colour and Fabric" => "Waist Belt Fabric and Colour")
    #CSV.write(file, df)
    return df
end

function validate_exported_df(df::DataFrame, file::String)
    std = DataFrame(CSV.File("/Users/david/julia/FiordlandPacks/forms/Pack_Builder_Options.csv"))
    #println(setdiff(names(df), names(std)), "   ", file) #to check col name differences
    #println(length(names(df)), "    ", names(df))
    #println(names(df)[7:end-1]) #for 25l it is [4:end-1]
    @info file
    for col in names(df)[7:end-1]
        @info col
        @info setdiff(df[!, Symbol(col)], std[!, Symbol(col)])
    end
    println()
end

for i in ["Downloads/65l_Pack_Builder.csv", "Downloads/55l_Pack_Builder.csv", "Downloads/45l_Pack_Builder.csv", "Downloads/35l_Pack_Builder.csv", "Downloads/25l_Strong_Pack_Builder.csv"]
    df= jotform_csv_to_airtable!(i) #|> x -> validate_exported_df(x, i)
    CSV.write("$(i)_final-2023-08-18", df)
end
=#



end # module