module Basics
    using Dates
    using JuliaDB
    using JuliaDBMeta
    using Lazy: @>

    export rfm

    function rfm(tbl::IndexedTable, end_cal::Union{Date, Nothing} = Date("2018-12-31", DateFormat("y-m-d")))
        cols = Dict(:PANALOKARD_NO => :pk, :POST_DATE => :date, :AMOUNT => :amount);
        elog_tbl = @> tbl begin
            select((:PANALOKARD_NO, :POST_DATE, :AMOUNT))
            renamecol(cols...)
            reindex(:pk)
        end

        elog_tbl = setcol(elog_tbl, :date => map(x -> Date(string(x), DateFormat("yyyymmdd")), select(elog_tbl, :date)));

        if !(end_cal isa Nothing)
            elog_tbl = filter(t -> t.date <= end_cal, elog_tbl);
        end

        # merge transactions to day
        dat = groupby((s_amt = :amount => sum,), elog_tbl, (:pk, :date));

        # extract repeat transactions elog
        dat1 = @apply dat begin
            @groupby :pk (; subtable = _)
            @filter length(:subtable) > 1
            flatten
        end

        # filter repeat transactions first date
        first_date = @apply dat1 begin
            @groupby :pk {
                mindate = minimum(:date),
                amount = :s_amt[argmin(:date)]
            }
        end

        # compute sufficient stats for repeat transactions
        mxdate = maximum(select(dat, :date));
        dat1 = @apply dat1 begin
            @groupby :pk {
                x = length(_) - 1,
                mx = sum(:s_amt),
                tx = ((maximum(:date) - minimum(:date)) / Day(365)) * 12,
                tcal = ((mxdate - minimum(:date)) / Day(365)) * 12
            }
        end

        upmx = select(dat1, :mx) - select(first_date, :amount);

        dat1 = @> dat1 begin
            insertcol(3, :upmx, upmx)
            renamecol((:mx => :oldmx, :upmx => :mx))
            @select (:pk, :x, :mx, :tx, :tcal)
        end

        # extract non-repeat transactions and compute sufficient statistics
        dat2 = @apply dat begin
            @groupby :pk (; subtable = _)
            @filter length(:subtable) == 1
            flatten
        end

        dat2 = @apply dat2 begin
            @groupby :pk {
                x = length(_) - 1,
                mx = 0,
                tx = ((maximum(:date) - minimum(:date)) / Day(365)) * 12,
                tcal = ((mxdate - minimum(:date)) / Day(365)) * 12
            }
        end

        cbsfinal = merge(dat1, dat2);
        cbsfinal = renamecol(cbsfinal, Dict(:mx => Symbol("m.x"), :tx => Symbol("t.x"), :tcal => Symbol("T.cal")))
        return cbsfinal
    end
end