text:
### A Pluto.jl notebook ###
# v0.14.1

using Markdown
using InteractiveUtils

# This Pluto notebook uses @bind for interactivity. When running this notebook outside of Pluto, the following 'mock version' of @bind gives bound variables a default value (instead of an error).
macro bind(def, element)
    quote
        local el = $(esc(element))
        global $(esc(def)) = Core.applicable(Base.get, el) ? Base.get(el) : missing
        el
    end
end

# ╔═╡ 3dd53878-9893-11eb-2cc0-b74e03668e27
using LibPQ, PostgresCatalog, FunSQL, PlutoUI

# ╔═╡ 63e4beb0-ece4-4009-9c3c-017be4c3812e
using FunSQL: SQLTable, render, From, Get, Where, Select, Fun

# ╔═╡ 9e7ff019-74fe-4938-baff-89cbf0d5dd0f
using HypertextLiteral: @htl

# ╔═╡ c984eacf-986e-45be-bfa1-7bab2760c678
using HypertextLiteral: @htl_str

# ╔═╡ b945b0d9-b8bd-412e-9626-c54174c670f7
using HypertextLiteral, Dates

# ╔═╡ 3d0791b6-e413-451e-a12b-bbbeacd8fa7f
md"# Customized Record Set Display"

# ╔═╡ d407fb80-d6ff-411e-b92e-871436bd72bc
@htl("""$(@bind selected_person_id Slider(42382:42389, show_value=true))
	     show record source?
	$(@bind show_source CheckBox(default=true))
	""")

# ╔═╡ 8302f4a5-cd38-4a1c-bd62-dca7a180c2a7
md"## Querying The OHDSI Database"

# ╔═╡ 455550f4-f26e-494d-9fe6-d01bf3c5e2e3
FunSQL.SQLTable(tbl::PostgresCatalog.PGTable) = SQLTable(Symbol(tbl.name), Symbol[Symbol(col.name) for col in tbl]..., schema=Symbol(tbl.schema.name));

# ╔═╡ 7080c41a-8087-428f-a7d0-c788a1a04e35
conn = LibPQ.Connection("postgresql:///synpuf-5pct?host=/var/run/postgresql");

# ╔═╡ 2f9f894e-4fb2-43a5-affb-dcdcb96f8a94
run(q) = execute(conn, render(q));

# ╔═╡ 1bb7f067-5d57-4719-89f3-ce3a71661b3b
catalog = PostgresCatalog.introspect(conn);

# ╔═╡ baaa2caa-9fac-4888-8cf0-3e86b5addc75
Person = From(SQLTable(catalog["public"]["person"]));

# ╔═╡ 5f25c5fe-ec6e-42bc-82a4-6d95777f6536
Visit = From(SQLTable(catalog["public"]["visit_occurrence"]));

# ╔═╡ 95d32133-2963-4a23-b7e1-93a89eef9162
SelectedPerson = Person |> Where(Fun("=", Get.person_id, selected_person_id)) |> Select([Get.person_id,(show_source ? [Get.person_source_value] : [])..., Get.gender_concept_id, Get.year_of_birth, Get.month_of_birth]...);

# ╔═╡ e4a46cd9-dee2-462d-9a73-d95f119edb89
Text(render(SelectedPerson))

# ╔═╡ cefdb9b1-a3bb-4576-897f-4914d90db235
SelectedVisits = Visit |> Where(Fun("=", Get.person_id, selected_person_id)) |> Select(Get.person_id, Get.visit_occurrence_id, Get.visit_start_date, Get.visit_end_date, Get.visit_type_concept_id);

# ╔═╡ 1b2e0959-38eb-4e5f-9480-933d2bcc9710
Text(render(SelectedVisits))

# ╔═╡ 80b9977f-13da-4a5d-85e7-28e929ba1eb7
visits = run(SelectedVisits);

# ╔═╡ b4965721-ad8a-4253-af0d-b0d626c9e64c
persons = run(SelectedPerson)

# ╔═╡ 91e020de-97e3-4ec4-80ee-3dee44cc46e6
current_person = collect(persons)[1];

# ╔═╡ 80b3b7a8-df90-4dff-87af-7dbb952e6969
@htl("""

Visits for Person #$(current_person.person_id)

person_idvisit_occurrence_id
	      visit_start_datevisit_end_date

	  $((@htl("

$(row.person_id)$(row.visit_occurrence_id)
	      $(row.visit_start_date)$(row.visit_end_date)

	  ") for row in visits))

""")

# ╔═╡ 9e392d76-9c64-4623-a208-84c29f856847
HTML("""

person_id

42384

gender_concept_id

8507

""")

# ╔═╡ 9f20ba16-cfc6-44d5-b8ec-88eff96e2b79
@htl("""

person_id

$(current_person.person_id)

gender_concept_id

$(current_person.gender_concept_id)

""")

# ╔═╡ 0bc10936-7d8c-4a61-9d09-30844455f2c9
persons.column_names

# ╔═╡ d0118a9b-98cf-4952-85a7-8766f31b7f4a
template = "
" * join(["
$n
\$(row.$n)" for n in persons.column_names]) * "
"

# ╔═╡ fc08593b-fefa-4bbb-acc2-580f0cbe404c
begin
	function_changed = show_source;
    eval(:(person_row(row) = @htl_str $template))
end

# ╔═╡ 5d946a84-8a2c-4b9d-a36c-b504ef2d3415
begin
	function_changed; # cause dynamic update
@htl("""

	  $(person_row(current_person))

""")
end

# ╔═╡ da174432-221c-46a4-b56c-a93375e6bd15
person_row(current_person)

# ╔═╡ 2b923ab2-683e-455e-9864-d3c23306a427
HypertextLiteral.content(d::Date) = d

# ╔═╡ 5a327f9d-7a90-425e-8a8a-bea2a0479d3f

# ╔═╡ 8cafcc87-9b02-42c0-ae2b-529a0e5666b1
md"## Custom Object Support"

# ╔═╡ 78f07dbb-4db1-43b9-8296-1d74a69db7a6
begin
	struct MyCustomType
	    data::String
    	style::Any
    end
	MyCustomType(data; kwargs...) =
	   MyCustomType(data, tuple(kwargs...))
end;

# ╔═╡ 4516f3b9-3554-4637-8db9-f4c3a8a4d9a4
Base.show(io::IO, m::MIME"text/html", c::MyCustomType) =
    show(io, m, @htl("""

This is...
		   $(c.data)

	"""))

# ╔═╡ f0f7364d-98df-4200-a278-8b3a66c138ef
MyCustomType("Properly <escaped>")

# ╔═╡ 08f53b21-897b-4584-8ec8-e66282194f6b
MyCustomType("Green & Underlined",
	text_decoration = :underline, color = :green)

# ╔═╡ Cell order:
# ╟─3d0791b6-e413-451e-a12b-bbbeacd8fa7f
# ╟─5d946a84-8a2c-4b9d-a36c-b504ef2d3415
# ╟─d407fb80-d6ff-411e-b92e-871436bd72bc
# ╠═80b3b7a8-df90-4dff-87af-7dbb952e6969
# ╠═8302f4a5-cd38-4a1c-bd62-dca7a180c2a7
# ╠═3dd53878-9893-11eb-2cc0-b74e03668e27
# ╠═63e4beb0-ece4-4009-9c3c-017be4c3812e
# ╠═455550f4-f26e-494d-9fe6-d01bf3c5e2e3
# ╠═7080c41a-8087-428f-a7d0-c788a1a04e35
# ╠═2f9f894e-4fb2-43a5-affb-dcdcb96f8a94
# ╠═1bb7f067-5d57-4719-89f3-ce3a71661b3b
# ╠═baaa2caa-9fac-4888-8cf0-3e86b5addc75
# ╠═5f25c5fe-ec6e-42bc-82a4-6d95777f6536
# ╠═95d32133-2963-4a23-b7e1-93a89eef9162
# ╟─e4a46cd9-dee2-462d-9a73-d95f119edb89
# ╠═cefdb9b1-a3bb-4576-897f-4914d90db235
# ╟─1b2e0959-38eb-4e5f-9480-933d2bcc9710
# ╠═80b9977f-13da-4a5d-85e7-28e929ba1eb7
# ╠═91e020de-97e3-4ec4-80ee-3dee44cc46e6
# ╠═b4965721-ad8a-4253-af0d-b0d626c9e64c
# ╠═9e392d76-9c64-4623-a208-84c29f856847
# ╠═9e7ff019-74fe-4938-baff-89cbf0d5dd0f
# ╠═9f20ba16-cfc6-44d5-b8ec-88eff96e2b79
# ╠═0bc10936-7d8c-4a61-9d09-30844455f2c9
# ╠═d0118a9b-98cf-4952-85a7-8766f31b7f4a
# ╠═c984eacf-986e-45be-bfa1-7bab2760c678
# ╠═fc08593b-fefa-4bbb-acc2-580f0cbe404c
# ╠═da174432-221c-46a4-b56c-a93375e6bd15
# ╠═b945b0d9-b8bd-412e-9626-c54174c670f7
# ╠═2b923ab2-683e-455e-9864-d3c23306a427
# ╠═5a327f9d-7a90-425e-8a8a-bea2a0479d3f
# ╟─8cafcc87-9b02-42c0-ae2b-529a0e5666b1
# ╠═78f07dbb-4db1-43b9-8296-1d74a69db7a6
# ╠═4516f3b9-3554-4637-8db9-f4c3a8a4d9a4
# ╠═f0f7364d-98df-4200-a278-8b3a66c138ef
# ╠═08f53b21-897b-4584-8ec8-e66282194f6b


text:
# ---
# title: Lines and markers
# cover: assets/lines_and_markers.png
# description: Basic linear and scatter plots.
# author: "[Pietro Vertechi](https://github.com/piever)"
# ---

# ## A simple scatter plot

using AlgebraOfGraphics, CairoMakie
set_aog_theme!() #src

df = (x=rand(100), y=rand(100))
xy = data(df) * mapping(:x, :y)
draw(xy)

# ## A simple lines plot

x = range(-π, π, length=100)
y = sin.(x)
df = (; x, y)
xy = data(df) * mapping(:x, :y)
layer = visual(Lines)
draw(layer * xy)

# ## Lines and scatter combined plot

x = range(-π, π, length=100)
y = sin.(x)
df = (; x, y)
xy = data(df) * mapping(:x, :y)
layers = visual(Scatter) + visual(Lines)
draw(layers * xy)

#

x = range(-π, π, length=100)
y = sin.(x)
df1 = (; x, y)
df2 = (x=rand(10), y=rand(10))
layers = data(df1) * visual(Lines) + data(df2) * visual(Scatter)
fg = draw(layers * mapping(:x, :y))

# save cover image #src
mkpath("assets") #src
save("assets/lines_and_markers.png", fg) #src

ext: jl


text:
struct Camera
    origin::Vector
    # orthonormal basis
    u::Vector
    v::Vector
    w::Vector
    lensradius::Real
    # focus plane window
    lowerleftcorner::Vector
    horizontal::Vector
    vertical::Vector

    """
        Camera(lookfrom, lookat, vup, vfov, aspect, aperture, focus_dist)

    Initialize camera position, orientation, field of view and aperture.

    # Arguments:
    - lookfrom: camera location within scene
    - lookat: coordinates towards which camera is oriented
    - vup: "up" direction
    - vfov: "field of view" angle, in rad
    - aspect: aspect ratio (width / height) of focus window
    - aperture: aperture (diameter) of camera lense
    - focus_dist: distance of focus plane from camera
    """
    function Camera(lookfrom::Vector, lookat::Vector, vup::Vector, vfov::Real, aspect::Real, aperture::Real, focus_dist::Real)
        # vfov is top to bottom in rad
        half_height = tan(vfov/2)
        half_width = aspect * half_height
        # orthonormal basis
        w = unitvector(lookfrom - lookat)
        u = unitvector(cross(vup, w))
        v = cross(w, u)
        # define the focus plane window
        lowerleftcorner = (lookfrom
            - half_width *focus_dist*u
            - half_height*focus_dist*v
            -             focus_dist*w)

        new(lookfrom, u, v, w, aperture / 2, lowerleftcorner,
            2*half_width *focus_dist*u,
            2*half_height*focus_dist*v)
    end
end

"""
    getray(camera, s, t)

Get a ray originating from a random position on the lens (to imitate
depth of field), targeting the focus window at relative coordinates.

# Arguments:
- `camera::Camera`: camera
- `s::Real`: relative x-coordinate within focus window (real number between 0 and 1)
- `t::Real`: relative y-coordinate within focus window (real number between 0 and 1)
"""
function getray(camera::Camera, s::Real, t::Real)
    rd = camera.lensradius * random_in_unit_disk()
    offset = rd[1]*camera.u + rd[2]*camera.v
    ray_origin = camera.origin + offset
    direction = camera.lowerleftcorner + s*camera.horizontal + t*camera.vertical - ray_origin
    return Ray(ray_origin, direction)
end


text:
@testset "DictTable" begin
    inds = Indices(['A', 'B', 'C'])
    col_a = Dictionary(inds, [1, 2, 3])
    col_b = Dictionary(inds, [2.0, 4.0, 6.0])
    col_c = Dictionary(inds, [true,false,true])

    t = @inferred(DictTable(a = col_a, b = col_b))::DictTable

    @test DictTable(t) == t
    @test DictTable(t; c = col_c) == DictTable(a = col_a, b = col_b, c = col_c)
    @test DictTable(t, DictTable(c = col_c)) == DictTable(a = col_a, b = col_b, c = col_c)
    @test DictTable(t; b = nothing) == DictTable(a = col_a)

    @test columnnames(t) == (:a, :b)
    @test propertynames(t) == (:a, :b)
    @test columns(t) == (a = col_a, b = col_b)
    @test rows(t) === t
    @test Tables.istable(t) === true
    @test Tables.rowaccess(t) === true
    @test Tables.columnaccess(t) === true
    @test Tables.schema(t) == Tables.Schema(NamedTuple{(:a,:b),Tuple{Int,Float64}})
    @test @inferred(Tables.materializer(t)(map(x -> 2 .* x, Tables.columns(t)))) isa DictTable
    @test Tables.materializer(t)(map(x -> 2 .* x, Tables.columns(t))) == DictTable(a = 2 .* col_a, b = 2 .* col_b)

    @test t.a == col_a
    @test keys(t) === inds
    @test length(t) === 3
    @test @inferred(t['B']) === (a = 2, b = 4.0)
    @test @inferred(getindices(t, keys(t)))::DictTable == t
    @test @inferred(getindices(t, Indices(['A', 'C'])))::DictTable == DictTable(a = Dictionary(['A', 'C'], [1, 3]), b = Dictionary(['A', 'C'], [2.0, 6.0]))
    @test @inferred(view(t, keys(t)))::DictTable == t
    @test @inferred(view(t, Indices(['A', 'C'])))::DictTable == DictTable(a = Dictionary(['A', 'C'], [1, 3]), b = Dictionary(['A', 'C'], [2.0, 6.0]))
    @test_throws IndexError t['D']

    @test similar(t) isa typeof(t)
    @test keys(similar(t)) === keys(t)

    io = IOBuffer()
    show(io, MIME"text/plain"(), t)
    str = String(take!(io))
    @test str == """
        DictTable with 2 columns and 3 rows:
             a  b
           ┌───────
         A │ 1  2.0
         B │ 2  4.0
         C │ 3  6.0"""

    @test @inferred(TypedTables.getproperties((:b, :a))(t))::DictTable == DictTable(b = col_b, a = col_a)

    @test t == t
    @test isequal(t, t)
    @test !isless(t, t)

    t2 = empty(t)
    @test t2 isa typeof(t)
    @test isempty(t2)

    t3 = copy(t)
    @test t3 isa typeof(t)
    @test t3 == t
    t3['C'] = (a = 4, b = 8.0)
    @test t3['C'] === (a = 4, b = 8.0)
    v3 = view(t3, keys(t3))
    v3['C'] = (a = 5, b = 10.0)
    @test t3['C'] === (a = 5, b = 10.0)

    empty!(t3)
    @test isempty(t3)

    insert!(t3, 'D', (a = 10, b = 0.0))
    @test length(t3) === 1
    @test t3['D'] === (a = 10, b = 0.0)
    delete!(t3, 'D')
    @test isempty(t3)

    @testset "Merging Tables" begin
        i = Indices(['A', 'B', 'C'])
        t1 = DictTable(a = Dictionary(i, [1,2,3]),)
        t2 = DictTable(b = Dictionary(i, [2.0, 4.0, 6.0]),)
        t3 = DictTable(a = Dictionary(i, [1,2,3]), b = Dictionary(i, [2.0, 4.0, 6.0]))

        @test @inferred(map(merge, t1, t2))::DictTable == t3
        @test @inferred(mapview(merge, t1, t2))::DictTable == t3
        @test @inferred(broadcast(merge, t1, t2))::DictTable == t3
    end

    @testset "GetProperty on Tables" begin
        i = Indices(['A', 'B', 'C'])
        t = DictTable(a = Dictionary(i, [1,2,3]), b = Dictionary(i, [2.0, 4.0, 6.0]), c = Dictionary(i, [true, false, true]))

        @test @inferred(map(getproperty(:a), t))::Dictionary == Dictionary(i, [1,2,3])
        @test @inferred(mapview(getproperty(:a), t))::Dictionary == Dictionary(i, [1,2,3])
        @test @inferred(broadcast(getproperty(:a), t))::Dictionary == Dictionary(i, [1,2,3])
        @test @inferred(mapreduce(getproperty(:a), +, t)) === 6
        i2 = Indices(['A', 'C'])
        @test @inferred(filter(getproperty(:c), t))::DictTable == DictTable(a = Dictionary(i2, [1,3]), b = Dictionary(i2, [2.0, 6.0]), c = Dictionary(i2, [true, true]))
        @test @inferred(findall(getproperty(:c), t))::Indices{Char} == i2

        @test @inferred(map(getproperties((:a,)), t))::DictTable == DictTable(a = Dictionary(i, [1,2,3]))
        @test @inferred(mapview(getproperties((:a,)), t))::DictTable == DictTable(a = Dictionary(i, [1,2,3]))
        @test @inferred(broadcast(getproperties((:a,)), t))::DictTable == DictTable(a = Dictionary(i, [1,2,3]))
        @test @inferred(mapreduce(getproperties((:a,)), (acc, row) -> acc + row.a, t; init = 0)) === 6
    end

    # @testset "@Select and @Compute on Tables" begin
    #     t = Table(a = [1,2,3], b = [2.0, 4.0, 6.0], c = [true, false, true])

    #     c = @Compute(2*$a)
    #     @test @inferred(c(t))::Vector == [2, 4, 6] # (Works because 2 * vector works)
    #     @test @inferred(map(c, t))::Vector == [2, 4, 6]
    #     @test @inferred(mapview(c, t))::MappedArray == [2, 4, 6]
    #     @test @inferred(broadcast(c, t))::Vector == [2, 4, 6]
    #     @test @inferred(mapreduce(c, +, t)) === 12

    #     c2 = @Compute($b > 3.0)
    #     @test @inferred(filter(c2, t))::Table == Table(a = [2,3], b = [4.0,6.0], c = [false,true])
    #     @test @inferred(findall(c2, t))::Vector{Int} == [2, 3]

    #     s = @Select(sum = $a + $b)
    #     @test @inferred(s(t))::Table == Table(sum = [3.0, 6.0, 9.0]) # (Works because vector + vector works)
    #     @test @inferred(map(s, t))::Table == Table(sum = [3.0, 6.0, 9.0])
    #     @test @inferred(mapview(s, t))::Table == Table(sum = [3.0, 6.0, 9.0])
    #     @test @inferred(broadcast(s, t))::Table == Table(sum = [3.0, 6.0, 9.0])
    #     @test @inferred(mapreduce(s, (acc, row) -> acc + row.sum, t; init = 0.0)) === 18.0
    # end

    # @testset "missing in Tables" begin
    #     t = Table(a = [1, 2, 3], b = [2.0, 4.0, missing])

    #     @test t[1]::eltype(t) == (a = 1, b = 2.0)
    #     @test isequal(t[3]::eltype(t), (a = 3, b = missing))

    #     @test (t == t) === missing
    #     @test isequal(t, t)
    #     @test !isless(t, t)
    # end

    # @testset "Tables.jl" begin
    #     t = Table(a = [1, 2, 3], b = [2.0, 4.0, missing])
    #     @test isequal(t |> columntable, getfield(t, :data))
    #     r = t |> rowtable
    #     @test length(r) == 3
    #     for (a, b) in zip(t, r)
    #         @test isequal(a, b)
    #     end
    #     @test isequal(Table(t |> columntable), t)
    #     @test isequal(Table(t |> rowtable), t)
    # end

    # @testset "group" begin
    #     t = Table(a = [1,2,1], b = [2.0, 4.0, 6.0])
    #     out = group(getproperty(:a), t)
    #     @test typeof(out) <: Dict{Int, <:Table}
    #     @test out == Dict(1 => Table(a=[1, 1], b=[2.0, 6.0]),
    #                       2 => Table(a=[2],    b=[4.0]))
    # end

    # @testset "innerjoin" begin
    #     customers = Table(id = 1:3, name = ["Alice", "Bob", "Charlie"], address = ["12 Beach Street", "163 Moon Road", "6 George Street"])
    #     orders = Table(customer_id = [2, 2, 3, 3], items = ["Socks", "Tie", "Shirt", "Underwear"])

    #     t = innerjoin(getproperty(:id), getproperty(:customer_id), customers, orders)

    #     @test t isa Table
    #     @test t == Table(id = [2, 2, 3, 3],
    #                      name = ["Bob", "Bob", "Charlie", "Charlie"],
    #                      address = ["163 Moon Road", "163 Moon Road", "6 George Street", "6 George Street"],
    #                      customer_id = [2, 2, 3, 3],
    #                      items = ["Socks", "Tie", "Shirt", "Underwear"])

    #     # Issue #34
    #     orders2 = Table(customer_id = [2, 2, 3, 3], items = ["Socks", "Tie", "Shirt", missing])
    #     t2 = innerjoin(getproperty(:id), getproperty(:customer_id), customers, orders2)
    #     @test t2 isa Table
    #     @test isequal(t2, Table(id = [2, 2, 3, 3],
    #                       name = ["Bob", "Bob", "Charlie", "Charlie"],
    #                       address = ["163 Moon Road", "163 Moon Road", "6 George Street", "6 George Street"],
    #                       customer_id = [2, 2, 3, 3],
    #                       items = ["Socks", "Tie", "Shirt", missing]))
    # end
end