text:
#=
[ Info: Benchmark ResultSet
  0.001309 seconds (296 allocations: 8.453 KiB)
Number of rows: 10
=#

include("timestamps_tests.jl")
#include("oranumbers_tests.jl") # see issue #21

import Oracle

using Test
using Dates
import DataFrames

@testset "ODPI version number" begin
    @test Oracle.odpi_version(998877) == v"99.88.77"
    @test Oracle.odpi_version(98877) == v"9.88.77"
    @test Oracle.odpi_version(90807) == v"9.8.7"
    println("ODPI Version Number: $(Oracle.odpi_version())")
end

@testset "exec mode" begin
    a = Oracle.ORA_MODE_EXEC_DESCRIBE_ONLY
    b = Oracle.ORA_MODE_EXEC_COMMIT_ON_SUCCESS
    x = a | b
    @test x == Oracle.OraExecMode(0x00000030)
    @test x & a == Oracle.ORA_MODE_EXEC_DESCRIBE_ONLY
    @test x & b == Oracle.ORA_MODE_EXEC_COMMIT_ON_SUCCESS
end

@testset "subtract_missing" begin
    @test Oracle.subtract_missing(Float64) == Float64
    @test_throws AssertionError Oracle.subtract_missing(Missing)
    @test Oracle.subtract_missing(Union{Missing, Float64}) == Float64
    @test Oracle.subtract_missing(Union{Float64, Missing}) == Float64
    @test_throws ErrorException Oracle.subtract_missing(Union{Int, Union{Float64, Missing}})
end

if !isfile(joinpath(@__DIR__, "credentials.jl"))
    msg = """
Before running tests, create a file `test/credentials.jl` with the content:

username = "your-username"
password = "your-password"
connect_string = "your-connect-string"
auth_mode = Oracle.ORA_MODE_AUTH_DEFAULT # or Oracle.ORA_MODE_AUTH_SYSDBA if user is SYSDBA
"""

    @warn(msg)
    exit()
end

include("credentials.jl")

conn = Oracle.Connection(username, password, connect_string, auth_mode=auth_mode)

@testset "Client Version" begin
    v = Oracle.client_version(Oracle.context(conn))
    println("")
    println("### CLIENT VERSION ###")
    println(v)
end

@testset "Server Version" begin
    release, server_version = Oracle.server_version(conn)
    println("### SERVER VERSION ###")
    println("release = $release")
    println("server_version = $server_version")
end

@testset "Database Encoding" begin
    Oracle.query(conn, "select value from nls_database_parameters where parameter='NLS_CHARACTERSET'") do cursor
        for row in cursor
            println("Database NLS_CHARACTERSET = ", row["VALUE"])
        end
    end
end

# Connection Encoding Info
println("Connection encoding info: ", conn.encoding_info)

# Current Schema
println("Current Schema: ", Oracle.current_schema(conn))

println("")

# https://www.oraclehome.com.br/2012/03/29/sys_context-userenv/
function get_client_identifier_and_info(conn::Oracle.Connection)
    local identifier
    local info

    Oracle.query(conn, "SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') CTX_CLIENT_IDENTIFIER, SYS_CONTEXT('USERENV', 'CLIENT_INFO') CTX_CLIENT_INFO FROM DUAL") do cursor
        for row in cursor
            identifier = row["CTX_CLIENT_IDENTIFIER"]
            info = row["CTX_CLIENT_INFO"]
        end
    end

    return identifier, info
end

@testset "connection" begin

    @testset "ping" begin
        Oracle.ping(conn)
    end

    @testset "stmt cache size" begin
        original_cache_size = Oracle.stmt_cache_size(conn)

        new_cache_size = 5
        Oracle.stmt_cache_size!(conn, new_cache_size)
        @test Oracle.stmt_cache_size(conn) == new_cache_size

        Oracle.stmt_cache_size!(conn, original_cache_size)
        @test Oracle.stmt_cache_size(conn) == original_cache_size
    end

    @testset "Supported encodings" begin
        for enc in Oracle.SUPPORTED_CONNECTION_ENCODINGS
            conn_enc = Oracle.Connection(username, password, connect_string, auth_mode=auth_mode, encoding=enc, nencoding=enc)
            Oracle.close(conn_enc)
        end
    end

    @testset "sys_context" begin
        identifier, info = get_client_identifier_and_info(conn)
        @test ismissing(identifier)
        @test ismissing(info)

        Oracle.set_client_identifier(conn, "NEW-CLIENT-IDENTIFIER")
        Oracle.set_client_info(conn, "NEW-CLIENT-INFO")

        identifier, info = get_client_identifier_and_info(conn)
        @test identifier == "NEW-CLIENT-IDENTIFIER"
        @test info == "NEW-CLIENT-INFO"
    end
end

@testset "execute/commit/rollback" begin
    Oracle.execute(conn, "CREATE TABLE TB_TEST ( ID INT NULL )")
    Oracle.execute(conn, "INSERT INTO TB_TEST ( ID ) VALUES ( 1 )")
    Oracle.execute(conn, "INSERT INTO TB_TEST ( ID ) VALUES ( null )")
    Oracle.commit(conn)

    Oracle.execute(conn, "INSERT INTO TB_TEST ( ID ) VALUES ( 3 )")
    Oracle.rollback(conn)
end

@testset "Stmt" begin
    stmt = Oracle.Stmt(conn, "SELECT * FROM TB_TEST")
    @test stmt.bind_count == 0

    @test stmt.info.is_query
    @test !stmt.info.is_DDL
    @test !stmt.info.is_DML
    @test stmt.info.statement_type == Oracle.ORA_STMT_TYPE_SELECT

    @testset "fetch size" begin
        @test Oracle.fetch_array_size(stmt) == Oracle.ORA_DEFAULT_FETCH_ARRAY_SIZE

        Oracle.fetch_array_size!(stmt, 150)
        @test Oracle.fetch_array_size(stmt) == 150
    end

    Oracle.execute(stmt)
    @test Oracle.ncol(stmt) == 1

    column_info = Oracle.column_info(stmt, 1)
    @test Oracle.column_name(column_info) == "ID"

    Oracle.close(stmt)
end

@testset "fetch" begin
    stmt = Oracle.Stmt(conn, "SELECT ID FROM TB_TEST")
    Oracle.execute(stmt)

    result = Oracle.fetch(stmt)
    @test result.found

    value = Oracle.query_oracle_value(stmt, 1)[]
    @test value == 1.0
    @test isa(value, Float64)

    result = Oracle.fetch(stmt)
    @test result.found

    value = Oracle.query_oracle_value(stmt, 1)[]
    @test ismissing(value)
    @test isa(value, Missing)

    Oracle.close(stmt)
end

@testset "fetchrow" begin
    stmt = Oracle.Stmt(conn, "SELECT ID FROM TB_TEST")
    Oracle.execute(stmt)

    row = Oracle.fetchrow(stmt)
    @test row != nothing
    @test row["ID"] == 1.0
    @test row[1] == 1.0

    row = Oracle.fetchrow(stmt)
    @test row != nothing
    @test ismissing(row["ID"])
    @test ismissing(row[1])

    row = Oracle.fetchrow(stmt)
    @test row == nothing
end

@testset "Drop" begin
    Oracle.execute(conn, "DROP TABLE TB_TEST")
end

@testset "ResultSet" begin

    function check_data(columns, rs::Oracle.ResultSet)
        # check_data should be used with non-empty results
        @assert !isempty(columns)
        @assert !isempty(rs)

        rows, cols = size(rs)
        @test cols == length(columns)
        for (c, col) in enumerate(columns)
            @test length(col) == rows
            for (r, value) in enumerate(col)
                @test rs[r,c] == value
            end
        end
    end

    Oracle.execute(conn, "CREATE TABLE TB_TEST_QUERY ( ID NUMBER(5,0), NAME VARCHAR2(255), AMOUNT NUMBER(15,2) )")

    select_sql = "SELECT ID, NAME, AMOUNT FROM TB_TEST_QUERY"

    let
        rs = Oracle.query(conn, select_sql)
        @test isa(rs, Oracle.ResultSet)
        @test isempty(rs)
        @test size(rs) == (0, 3)
        @test_throws AssertionError rs[1,1]
        @test "ID" ∈ names(rs)
    end

    col_id = [1, 2, 3, 4]
    col_name = ["first", "sec", "third", "4th"]
    col_amount = [10.5, 20.5, 30.5, 40.5]
    num_rows = length(col_id)

    columns = [ col_id, col_name, col_amount ]
    Oracle.execute(conn, "INSERT INTO TB_TEST_QUERY ( ID, NAME, AMOUNT ) VALUES ( :1, :2, :3 )", columns)
    Oracle.commit(conn)

    let
        rs = Oracle.query(conn, "SELECT COUNT(ID) FROM TB_TEST_QUERY")
        @test rs[1,1] == 4
        @test size(rs) == (1, 1)
    end

    let
        rs = Oracle.query(conn, select_sql)
        @test isa(rs, Oracle.ResultSet)
        @test !isempty(rs)
        @test Oracle.nrow(rs) == 4
        @test Oracle.ncol(rs) == 3
        @test size(rs) == (4, 3)

        check_data(columns, rs)

        @test rs[1, 1] == col_id[1]
        @test rs[1, "ID"] == col_id[1]
        @test rs[:, 1] == col_id
        @test rs[:, "ID"] == col_id
        @test rs[end, 1] == col_id[end]
        @test rs[2:4, 1] == col_id[2:4]

        println(DataFrames.DataFrame(rs))
    end

    Oracle.execute(conn, "DROP TABLE TB_TEST_QUERY")
end

@testset "ExternOracleValue" begin
    Oracle.execute(conn, "CREATE TABLE TB_TEST_DATATYPES ( ID NUMBER(38,0) NULL, name VARCHAR2(255) NULL,  amount NUMBER(15,2) NULL)")

    Oracle.execute(conn, "INSERT INTO TB_TEST_DATATYPES ( ID, name, amount ) VALUES ( 1, 'hello world', 123.45 )")
    Oracle.execute(conn, "INSERT INTO TB_TEST_DATATYPES ( ID, name, amount ) VALUES ( 2, '📚📚📚📚⏳😀⌛😭', 10 )")
    Oracle.execute(conn, "INSERT INTO TB_TEST_DATATYPES ( ID, name, amount ) VALUES ( 3, 'áÁàÀãÃâÂéÉíÍóÓõÕúÚçÇ', .1 )")
    Oracle.commit(conn)

    stmt = Oracle.Stmt(conn, "SELECT ID, name, amount FROM TB_TEST_DATATYPES")
    Oracle.execute(stmt)
    @test Oracle.ncol(stmt) == 3

    row = Oracle.fetchrow(stmt)
    @test row != nothing

    iter = 1
    id_values = [1, 2, 3]
    name_values = [ "hello world", "📚📚📚📚⏳😀⌛😭", "áÁàÀãÃâÂéÉíÍóÓõÕúÚçÇ" ]
    amount_values = [123.45, 10, .1]

    println("")
    println("Printing out UTF-8 strings")

    while row != nothing

        value_id = row[1]
        value_name = row[2]
        value_amount = row[3]

        println(value_name)

        @test id_values[iter] == value_id
        @test name_values[iter] == value_name
        @test amount_values[iter] == value_amount

        row = Oracle.fetchrow(stmt)
        iter += 1
    end

    println("")

    Oracle.close(stmt)
    Oracle.execute(conn, "DROP TABLE TB_TEST_DATATYPES")
end

@testset "OraData" begin
    ctx = Oracle.Context()

    data_handle = Ref{Oracle.OraData}()

    let
        str = "hey you"
        Oracle.dpiData_setBytes(data_handle, str)
    end

    let
        local ptr_bytes::Ptr{Oracle.OraBytes} = Oracle.dpiData_getBytes(data_handle)
        ora_string = unsafe_load(ptr_bytes) # get a OraBytes
        @test unsafe_string(ora_string.ptr, ora_string.length) == "hey you"
    end
end

@testset "JuliaOracleValue" begin

    @testset "Int" begin
        julia_oracle_value = Oracle.JuliaOracleValue(Oracle.ORA_ORACLE_TYPE_NATIVE_INT, Oracle.ORA_NATIVE_TYPE_INT64, Int64)
        julia_oracle_value[] = 10
        @test julia_oracle_value[] == 10
        @test julia_oracle_value[1] == julia_oracle_value[]
        @test_throws AssertionError julia_oracle_value[0]
    end

    @testset "UInt64" begin
        julia_oracle_value = Oracle.JuliaOracleValue(UInt64(10000))
        @test julia_oracle_value[] == UInt64(10000)
    end

    @testset "Missing" begin
        julia_oracle_value = Oracle.JuliaOracleValue(Oracle.ORA_ORACLE_TYPE_NATIVE_INT, Oracle.ORA_NATIVE_TYPE_INT64, Union{Missing, Int64})
        julia_oracle_value[] = missing
        @test ismissing(julia_oracle_value[])
    end

    @testset "Float64" begin
        julia_oracle_value = Oracle.JuliaOracleValue(Oracle.ORA_ORACLE_TYPE_NUMBER, Oracle.ORA_NATIVE_TYPE_DOUBLE, Float64)
        julia_oracle_value[] = 10.5
        @test julia_oracle_value[] == 10.5
    end

    @testset "VARCHAR" begin
        julia_oracle_value = Oracle.JuliaOracleValue(Oracle.ORA_ORACLE_TYPE_VARCHAR, Oracle.ORA_NATIVE_TYPE_BYTES, String)
        julia_oracle_value[] = "hey you"
        @test julia_oracle_value[] == "hey you"
    end

    @testset "RAW" begin
        julia_oracle_value = Oracle.JuliaOracleValue(Oracle.ORA_ORACLE_TYPE_RAW, Oracle.ORA_NATIVE_TYPE_BYTES, Vector{UInt8})
        test_data = rand(UInt8, 200)
        julia_oracle_value[] = copy(test_data)
        @test julia_oracle_value[] == test_data
    end

    @testset "Stmt bind" begin
        stmt = Oracle.Stmt(conn, "SELECT :A, :B FROM DUAL")
        val_a = Oracle.JuliaOracleValue(Oracle.ORA_ORACLE_TYPE_NUMBER, Oracle.ORA_NATIVE_TYPE_DOUBLE, Float64)
        val_a[] = 10.5
        val_b = Oracle.JuliaOracleValue(Oracle.ORA_ORACLE_TYPE_VARCHAR, Oracle.ORA_NATIVE_TYPE_BYTES, String)
        val_b[] = "hey"
        Oracle.bind!(stmt, val_a, :A)
        Oracle.bind!(stmt, val_b, 2)
        Oracle.close(stmt)
    end

    @testset "Stmt bind repeated vars" begin
        Oracle.stmt(conn, "SELECT :A, :A FROM DUAL") do stmt
            stmt[:A] = 1
            Oracle.execute(stmt)
        end
    end

    @testset "type inference" begin

        @test Oracle.infer_oracle_type_tuple(Bool) == Oracle.infer_oracle_type_tuple(true)

        v_int = Oracle.JuliaOracleValue(10)
        @test v_int[] == 10

        v_str = Oracle.JuliaOracleValue("str")
        @test v_str[] == "str"

        v_double = Oracle.JuliaOracleValue(10.5)
        @test v_double[] == 10.5

        v_raw = Oracle.JuliaOracleValue(UInt8[0x10, 0x11, 0x12, 0x13])
        @test v_raw[] == UInt8[0x10, 0x11, 0x12, 0x13]
    end
end

@testset "Timestamp" begin
    Oracle.execute(conn, "CREATE TABLE TB_DATE ( DT DATE NULL )")
    Oracle.execute(conn, "INSERT INTO TB_DATE (DT) VALUES ( TO_DATE('2018-12-31', 'yyyy-mm-dd') )")
    Oracle.execute(conn, "INSERT INTO TB_DATE (DT) VALUES ( TO_DATE('2018-12-31 11:55:35 P.M.', 'yyyy-mm-dd HH:MI:SS A.M.') )")
    Oracle.commit(conn)

    let
        dates = [ Date(2018, 12, 31), DateTime(2018, 12, 31, 23, 55, 35) ]

        row_number = 1

        Oracle.query(conn, "SELECT DT FROM TB_DATE") do cursor
            for row in cursor
                @test row["DT"] == dates[row_number]
                @test isa(row["DT"], DateTime) # Oracle DATE columns stores DateTime up to seconds.

                row_number += 1
            end
        end
    end

    Oracle.execute(conn, "DROP TABLE TB_DATE")
end

@testset "RAW" begin
    Oracle.execute(conn, "CREATE TABLE TB_QUERY_RAW ( RAW_COLUMN RAW(2000) )")
    Oracle.execute(conn, "INSERT INTO TB_QUERY_RAW ( RAW_COLUMN ) VALUES ( utl_raw.cast_to_raw('raw column value') )")

    Oracle.query(conn, "SELECT RAW_COLUMN FROM TB_QUERY_RAW") do cursor
        for row in cursor
            byte_array = row["RAW_COLUMN"]
            @test isa(byte_array, Vector{UInt8})
            @test String(byte_array) == "raw column value"
        end
    end

    Oracle.execute(conn, "DROP TABLE TB_QUERY_RAW")
end

@testset "Lob" begin
    @testset "Temp Lob" begin
        for lob_type_num in ( Oracle.ORA_ORACLE_TYPE_BLOB, Oracle.ORA_ORACLE_TYPE_CLOB, Oracle.ORA_ORACLE_TYPE_NCLOB )
            lob = Oracle.Lob(conn, lob_type_num)
            @test Oracle.oracle_type(lob) == lob_type_num
            @test Oracle.is_character_data(lob) == (lob_type_num != Oracle.ORA_ORACLE_TYPE_BLOB)
            @test Oracle.chunk_size(lob) != 0
            Oracle.close(lob)
        end
    end

    @testset "Read BLOB" begin

        lyric = "hey you. 🎵 🎶 Out there in the cold. getting lonely, getting old. Can you feel me? 📼📼📼📼"

        Oracle.execute(conn, "CREATE TABLE TB_BLOB ( B BLOB )")
        Oracle.execute(conn, "INSERT INTO TB_BLOB ( B ) VALUES ( utl_raw.cast_to_raw('$lyric'))")

        function check_blob_data(test_data::String, lob, buffer_size)

            @test Oracle.oracle_type(lob) == Oracle.ORA_ORACLE_TYPE_BLOB

            out = IOBuffer()
            open(lob, "r", buffer_size=buffer_size) do io
                while !eof(io)
                    write(out, read(io, Char))
                end

                @test eof(io)
                @test_throws EOFError read(io, Char)
            end

            @test test_data == String(take!(out))
            Oracle.close(lob)
        end

        function query_and_check_blob_data(test_data::String, buffer_size)

            local row

            Oracle.stmt(conn, "SELECT B FROM TB_BLOB") do stmt
                Oracle.execute(stmt)
                row = Oracle.fetchrow(stmt)
            end

            @test row != nothing
            blob = row[1]
            check_blob_data(test_data, blob, buffer_size)
        end

        buff_sizes_to_check = [ 30, sizeof(lyric)-1, sizeof(lyric), sizeof(lyric)+1, nothing ]

        for buff_size in buff_sizes_to_check
            query_and_check_blob_data(lyric, buff_size)
        end

        Oracle.query(conn, "SELECT B FROM TB_BLOB") do cursor
            for row in cursor
                blob = row[1]
                bytes = Vector{UInt8}(undef, 1000)
                local n_bytes_read
                open(blob, "r") do io
                    n_bytes_read = readbytes!(io, bytes)
                end
                result_str = String(bytes[1:n_bytes_read])
                @test result_str == lyric
            end
        end

        Oracle.query(conn, "SELECT B FROM TB_BLOB") do cursor
            for row in cursor
                blob = row[1]
                bytes = Vector{UInt8}(undef, 8)
                local n_bytes_read
                open(blob, "r") do io
                    n_bytes_read = readbytes!(io, bytes)
                end
                result_str = String(bytes[1:n_bytes_read])
                @test result_str == "hey you."
            end
        end

        Oracle.query(conn, "SELECT B FROM TB_BLOB") do cursor
            for row in cursor
                blob = row[1]
                bytes = Vector{UInt8}(undef, 8)
                local n_bytes_read
                open(blob, "r") do io
                    n_bytes_read = readbytes!(io, bytes, 9)
                end
                result_str = String(bytes[1:n_bytes_read])
                @test result_str == "hey you. "
            end
        end

        Oracle.execute(conn, "DROP TABLE TB_BLOB")
    end
#=
    # TODO: not working
    @testset "Write BLOB" begin
        #Oracle.execute(conn, "DROP TABLE TB_WRITE_BLOB")
        Oracle.execute(conn, "CREATE TABLE TB_WRITE_BLOB ( B BLOB )")

        blob = Oracle.Lob(conn, Oracle.ORA_ORACLE_TYPE_BLOB)
        data = rand(UInt8, 5000)
        write(blob, data)

        stmt = Oracle.Stmt(conn, "INSERT INTO TB_WRITE_BLOB ( B ) VALUES ( :1 )")
        stmt[1] = blob # ERROR: DPI-1014: conversion between Oracle type 0 and native type 3008 is not implemented
        Oracle.execute(stmt)
        Oracle.close(stmt)

        Oracle.execute(conn, "DROP TABLE TB_WRITE_BLOB")
    end
=#
    @testset "Read CLOB" begin
        #utfchar_5bytes = "a🎵" # https://github.com/oracle/odpi/issues/94
        test_string = "abcdefghij"^250
        @assert sizeof(test_string) == 2500

        Oracle.execute(conn, "CREATE TABLE TB_CLOB ( B CLOB )")
        Oracle.execute(conn, "INSERT INTO TB_CLOB ( B ) VALUES ( '$(test_string)' )")

        let
            local clob::Oracle.Lob

            try
                Oracle.stmt(conn, "SELECT B FROM TB_CLOB") do stmt
                    Oracle.execute(stmt)
                    row = Oracle.fetchrow(stmt)
                    @test row != nothing
                    clob = row[1]
                end

                open(clob, "r") do io
                    i = 1
                    while !eof(io)
                        @test read(io, Char) == test_string[i]
                        i += 1
                    end

                    seek(io, 2)
                    @test read(io, Char) == test_string[3]
                    @test position(io) == 3

                    seekstart(io)
                    @test read(io, Char) == test_string[1]

                end
            finally
                Oracle.close(clob)
            end
        end

        let
            local clob::Oracle.Lob

            try
                Oracle.stmt(conn, "SELECT B FROM TB_CLOB") do stmt
                    Oracle.execute(stmt)
                    row = Oracle.fetchrow(stmt)
                    @test row != nothing
                    clob = row[1]
                end

                open(clob, "r") do io
                    @test read(io, String) == test_string
                end
            finally
                Oracle.close(clob)
            end
        end

        let
            local clob::Oracle.Lob

            try
                Oracle.stmt(conn, "SELECT B FROM TB_CLOB") do stmt
                    Oracle.execute(stmt)
                    row = Oracle.fetchrow(stmt)
                    @test row != nothing
                    clob = row[1]
                end

                open(clob, "r", buffer_size=2000) do io

                    seek(io, length(test_string)-1)
                    @test read(io, Char) == test_string[end]

                    seekstart(io)
                    i = 1
                    while !eof(io)
                        @test read(io, Char) == test_string[i]
                        i += 1
                    end
                end
            finally
                Oracle.close(clob)
            end
        end

        let
            local clob::Oracle.Lob

            try
                Oracle.stmt(conn, "SELECT B FROM TB_CLOB") do stmt
                    Oracle.execute(stmt)
                    row = Oracle.fetchrow(stmt)
                    @test row != nothing
                    clob = row[1]
                end

                open(clob, "r", buffer_size=2000) do io
                    @test read(io, String) == test_string
                end
            finally
                Oracle.close(clob)
            end
        end

        Oracle.execute(conn, "DROP TABLE TB_CLOB")
    end
end

@testset "Fetch Many" begin
    Oracle.execute(conn, "CREATE TABLE TB_TEST_FETCH_MANY ( ID NUMBER(4,0) NULL, VAL NUMBER(4,0) NULL )")

    let
        col1 = collect(1:10)
        col2 = 10*col1
        Oracle.execute(conn, "INSERT INTO TB_TEST_FETCH_MANY ( ID, VAL ) VALUES ( :1, :2 )", [col1, col2])
    end

    Oracle.stmt(conn, "SELECT ID, VAL FROM TB_TEST_FETCH_MANY") do stmt
        Oracle.execute(stmt)
        @test Oracle.ncol(stmt) == 2

        fetch_rows_result = Oracle.fetchrows(stmt, 3)

        @test fetch_rows_result.buffer_row_index == 0
        @test fetch_rows_result.num_rows_fetched == 3
        @test fetch_rows_result.more_rows == 1
    end

    Oracle.execute(conn, "DROP TABLE TB_TEST_FETCH_MANY")
end

@testset "Cursor" begin
    Oracle.execute(conn, "CREATE TABLE TB_TEST_CURSOR ( ID NUMBER(4,0) NULL, VAL NUMBER(15,0) NULL, VAL_FLT NUMBER(4,2), STR VARCHAR2(255) )")

    num_iterations = 10

    let
        col1 = collect(1:num_iterations)
        col2 = 10*col1
        col3 = fill(10.01, num_iterations)
        col4 = fill("📚📚📚📚⏳😀⌛😭", num_iterations)
        columns = [ col1, col2, col3, col4 ]
        Oracle.execute(conn, "INSERT INTO TB_TEST_CURSOR ( ID, VAL, VAL_FLT, STR ) VALUES ( :1, :2, :3, :4 )", columns)
    end

    @testset "all rows" begin
        row_number = 0

        Oracle.query(conn, "SELECT * FROM TB_TEST_CURSOR", fetch_array_size=3) do cursor
            for row in cursor
                row_number += 1
                @test row["ID"] == row_number
                @test isa(row["ID"], Int)
                @test row["ID"] == row[1]

                @test row["VAL"] == row_number * 10
                @test isa(row["VAL"], Int)
                @test row["VAL"] == row[2]

                @test row["VAL_FLT"] == 10.01
                @test isa(row["VAL_FLT"], Float64)
                @test row["VAL_FLT"] == row[3]

                @test row["STR"] == "📚📚📚📚⏳😀⌛😭"
                @test isa(row["STR"], String)
                @test row["STR"] == row[4]
            end
        end
        @test row_number == num_iterations
    end

    @testset "collect" begin
        local resultset

        Oracle.query(conn, "SELECT * FROM TB_TEST_CURSOR") do cursor
            resultset = collect(cursor)
        end

        @test length(resultset) == num_iterations
        row_number = 0
        for row in resultset
            row_number += 1
            @test row["ID"] == row_number
            @test isa(row["ID"], Int)
            @test row["ID"] == row[1]

            @test row["VAL"] == row_number * 10
            @test isa(row["VAL"], Int)
            @test row["VAL"] == row[2]

            @test row["VAL_FLT"] == 10.01
            @test isa(row["VAL_FLT"], Float64)
            @test row["VAL_FLT"] == row[3]

            @test row["STR"] == "📚📚📚📚⏳😀⌛😭"
            @test isa(row["STR"], String)
            @test row["STR"] == row[4]
        end
    end

    @testset "2nd row" begin
        row_number = 0
        Oracle.query(conn, "SELECT * FROM TB_TEST_CURSOR WHERE ID = 2", fetch_array_size=3) do cursor
            for row in cursor
                row_number += 1
                @test row["ID"] == 2
                @test row["VAL"] == 20
            end
        end
        @test row_number == 1
    end

    @testset "1st and 2nd rows" begin
        row_number = 0

        Oracle.query(conn, "SELECT * FROM TB_TEST_CURSOR WHERE ID = 1 OR ID = 2", fetch_array_size=3) do cursor
            for row in cursor
                row_number += 1
                @test row["ID"] == row_number
                @test row["VAL"] == row_number * 10
            end
        end

        @test row_number == 2
    end

    @testset "1st, 2nd, 3rd rows" begin
        row_number = 0

        Oracle.query(conn, "SELECT * FROM TB_TEST_CURSOR WHERE ID = 1 OR ID = 2 OR ID = 3", fetch_array_size=3) do cursor
            for row in cursor
                row_number += 1
                @test row["ID"] == row_number
                @test row["VAL"] == row_number * 10
            end
        end

        @test row_number == 3
    end

    @testset "1st, 2nd, 3rd, 4th rows" begin
        row_number = 0

        Oracle.query(conn, "SELECT * FROM TB_TEST_CURSOR WHERE ID = 1 OR ID = 2 OR ID = 3 OR ID = 4", fetch_array_size=3) do cursor
            for row in cursor
                row_number += 1
                @test row["ID"] == row_number
                @test row["VAL"] == row_number * 10
            end
        end

        @test row_number == 4
    end

    Oracle.execute(conn, "DROP TABLE TB_TEST_CURSOR")
end

@testset "execute script" begin
    Oracle.execute(conn, "CREATE TABLE TB_ACCOUNTS ( ID NUMBER(4,0), AMOUNT NUMBER(12,2) )")

    let
        Oracle.stmt(conn, "INSERT INTO TB_ACCOUNTS ( ID, AMOUNT ) VALUES ( :ID, :AMT )") do stmt
            stmt[:ID] = 1
            stmt[:AMT] = 20.50
            Oracle.execute(stmt)

            stmt[:ID] = 2
            stmt[:AMT] = 100.00
            Oracle.execute(stmt)

            stmt[:ID] = 3
            stmt[:AMT] = 150.00
            Oracle.execute(stmt)
        end

        Oracle.commit(conn)
    end

    Oracle.execute_script(conn, joinpath(@__DIR__, "update_account_2.sql"))

    Oracle.query(conn, "SELECT AMOUNT FROM TB_ACCOUNTS WHERE ID = 2") do cursor
        row_count = 0
        for row in cursor
            row_count += 1
            @test row["AMOUNT"] == 10.0
        end

        @test row_count == 1
    end

    @testset "query with substring" begin
        i = 1
        for sql in split("SELECT 1 FROM DUAL; SELECT 2 FROM DUAL", ';')
            rs = Oracle.query(conn, sql)
            @test rs[1,1] == i
            i += 1
        end
    end

    Oracle.execute(conn, "DROP TABLE TB_ACCOUNTS")
end

@testset "Bind" begin

    @testset "bind int, flt, str, date by name" begin
        Oracle.execute(conn, "CREATE TABLE TB_BIND_BY_NAME ( ID NUMBER(15,0) NULL, FLT NUMBER(15,4) NULL, STR VARCHAR(255) NULL, DT DATE NULL)")

        stmt = Oracle.Stmt(conn, "INSERT INTO TB_BIND_BY_NAME ( ID, FLT, STR, DT ) VALUES ( :id, :flt, :str, :dt )")
        @test stmt.bind_count == 4

        @test_throws AssertionError stmt[:invalid_bind_name] = 10

        for i in 1:10
            stmt[:id] = 1 + i
            stmt[:flt] = 10.23 + i
            stmt[:str] = "🍕 $i"
            stmt[:dt] = Date(2018,12,31) + Dates.Day(i)
            Oracle.execute(stmt)
        end
        Oracle.commit(conn)

        let
            row_number = 1

            Oracle.query(conn, "SELECT * FROM TB_BIND_BY_NAME") do cursor
                for row in cursor
                    @test row["ID"] == 1 + row_number
                    @test row["FLT"] == 10.23 + row_number
                    @test row["STR"] == "🍕 $row_number"
                    @test row["DT"] == Date(2018,12,31) + Dates.Day(row_number)

                    row_number += 1
                end
            end

            @test row_number == 11
        end

        @testset "Benchmark ResultSet" begin
            @info("Benchmark ResultSet")
            @time rs = Oracle.query(conn, "SELECT * FROM TB_BIND_BY_NAME")
            println("Number of rows: $(Oracle.nrow(rs))")
        end

        @testset "reuse stmt for queries" begin
            query_stmt = Oracle.Stmt(conn, "SELECT FLT FROM TB_BIND_BY_NAME WHERE ID = :id")

            query_stmt[:ID] = 2
            Oracle.query(query_stmt) do cursor
                for row in cursor
                    @test row["FLT"] == 10.23 + 1
                end
            end

            query_stmt[:ID] = 3
            Oracle.query(query_stmt) do cursor
                for row in cursor
                    @test row["FLT"] == 10.23 + 2
                end
            end

            Oracle.close(query_stmt)
        end

        Oracle.execute(conn, "DELETE FROM TB_BIND_BY_NAME")

        stmt[:id, Int] = missing
        stmt[:flt, Float64] = missing
        stmt[:str, String] = missing
        stmt[:dt, Date] = missing

        @test_throws ErrorException stmt[:dt] = missing
        @test_throws MethodError stmt[:dt, Int] = 1

        Oracle.execute(stmt)
        Oracle.commit(conn)

        let
            row_number = 0

            Oracle.query(conn, "SELECT * FROM TB_BIND_BY_NAME") do cursor
                for row in cursor
                    @test ismissing(row["ID"])
                    @test ismissing(row["FLT"])
                    @test ismissing(row["STR"])
                    @test ismissing(row["DT"])
                    row_number += 1
                end
            end

            @test row_number == 1
        end

        Oracle.close(stmt)
        Oracle.execute(conn, "DROP TABLE TB_BIND_BY_NAME")
    end

    @testset "bind int, flt, str, date by position" begin
        Oracle.execute(conn, "CREATE TABLE TB_BIND_BY_POSITION ( ID NUMBER(15,0) NULL, FLT NUMBER(15,4) NULL, STR VARCHAR(255) NULL, DT DATE NULL)")

        stmt = Oracle.Stmt(conn, "INSERT INTO TB_BIND_BY_POSITION ( ID, FLT, STR, DT ) VALUES ( :1, :2, :3, :4 )")
        @test stmt.bind_count == 4

        @test_throws AssertionError stmt[0] = 10
        @test_throws AssertionError stmt[5] = 10

        for i in 1:10
            stmt[1] = 1 + i
            stmt[2] = 10.23 + i
            stmt[3] = "🍕 $i"
            stmt[4] = Date(2018,12,31) + Dates.Day(i)
            Oracle.execute(stmt)
        end
        Oracle.commit(conn)

        let
            row_number = 1

            Oracle.query(conn, "SELECT * FROM TB_BIND_BY_POSITION") do cursor
                for row in cursor
                    @test row["ID"] == 1 + row_number
                    @test row["FLT"] == 10.23 + row_number
                    @test row["STR"] == "🍕 $row_number"
                    @test row["DT"] == Date(2018,12,31) + Dates.Day(row_number)

                    row_number += 1
                end
            end

            @test row_number == 11
        end

        Oracle.execute(conn, "DELETE FROM TB_BIND_BY_POSITION")

        stmt[1, Int] = missing
        stmt[2, Float64] = missing
        stmt[3, String] = missing
        stmt[4, Date] = missing

        @test_throws ErrorException stmt[:dt] = missing
        @test_throws MethodError stmt[:dt, Int] = 1

        Oracle.execute(stmt)
        Oracle.commit(conn)

        let
            row_number = 0

            Oracle.query(conn, "SELECT * FROM TB_BIND_BY_POSITION") do cursor
                for row in cursor
                    @test ismissing(row["ID"])
                    @test ismissing(row["FLT"])
                    @test ismissing(row["STR"])
                    @test ismissing(row["DT"])
                    row_number += 1
                end
            end

            @test row_number == 1
        end

        Oracle.close(stmt)

        Oracle.execute(conn, "DELETE FROM TB_BIND_BY_POSITION")
        Oracle.commit(conn)

        let
            # tutorial example
            Oracle.stmt(conn, "INSERT INTO TB_BIND_BY_POSITION ( ID, FLT, STR, DT ) VALUES ( :id, :flt, :str, :dt )") do stmt
                stmt[1] = 1
                stmt[2] = 10.1234
                stmt[3] = "this is a string"
                stmt[4, Date] = missing # we must inform the type when setting value as missing

                Oracle.execute(stmt)
                Oracle.commit(conn)
            end

            row_number = 0

            Oracle.query(conn, "SELECT * FROM TB_BIND_BY_POSITION") do cursor
                for row in cursor
                    @test row["ID"] == 1
                    @test row["FLT"] ≈ 10.1234
                    @test row["STR"] == "this is a string"
                    @test ismissing(row["DT"])
                    row_number += 1
                end
            end

            @test row_number == 1
        end

        Oracle.execute(conn, "DROP TABLE TB_BIND_BY_POSITION")
    end

    @testset "Bind DateTime and Timestamp" begin
        Oracle.execute(conn, "CREATE TABLE TB_BIND_TIMESTAMP ( TS TIMESTAMP(9) NULL )")

        ts = Oracle.Timestamp(2018, 12, 31, 23, 58, 59, 999_200_300)

        dt_now = Dates.now()
        Oracle.stmt(conn, "INSERT INTO TB_BIND_TIMESTAMP ( TS ) VALUES ( :ts )") do stmt
            stmt[:ts] = dt_now
            Oracle.execute(stmt)
            stmt[:ts] = ts
            Oracle.execute(stmt)
        end

        Oracle.commit(conn)

        let row_number = 0

            Oracle.query(conn, "SELECT TS FROM TB_BIND_TIMESTAMP") do cursor
                for row in cursor
                    row_number += 1

                    if row_number == 1
                        @test row["TS"] == dt_now
                    else
                        @test row["TS"] == ts
                    end

                    @test isa(row["TS"], Oracle.Timestamp)
                end
            end

            @test row_number == 2
        end

        Oracle.execute(conn, "DROP TABLE TB_BIND_TIMESTAMP")
    end

    @testset "Bind TimestampTZ" begin
        Oracle.execute(conn, "CREATE TABLE TB_BIND_TIMESTAMP_TZ ( TS_TZ TIMESTAMP(9) WITH TIME ZONE, TS_LTZ TIMESTAMP(9) WITH LOCAL TIME ZONE )")

        ts_tz = Oracle.TimestampTZ(false, 2018, 12, 31, 23, 58, 59, 999_200_300, 5, 30)
        ts_ltz = Oracle.TimestampTZ(true, 2018, 12, 31, 23, 58, 59, 999_200_400)

        Oracle.stmt(conn, "INSERT INTO TB_BIND_TIMESTAMP_TZ ( TS_TZ, TS_LTZ ) VALUES ( :ts_tz, :ts_ltz )") do stmt

            @test_throws ErrorException stmt[:ts_tz] = ts_tz
            @test_throws ErrorException stmt[:ts_ltz] = ts_ltz

            stmt[:ts_tz] = Oracle.Variable(conn, ts_tz)
            stmt[:ts_ltz] = Oracle.Variable(conn, ts_ltz)
            Oracle.execute(stmt)
        end

        Oracle.commit(conn)

        Oracle.stmt(conn, "SELECT TS_TZ, TS_LTZ FROM TB_BIND_TIMESTAMP_TZ") do query_stmt
            Oracle.execute(query_stmt)
            row = Oracle.fetchrow(query_stmt)
            @test row != nothing

            read_ts_tz = row[1]
            read_ts_ltz = row[2]

            @test ts_tz == read_ts_tz
            @test nanosecond(ts_ltz) == nanosecond(read_ts_ltz)
        end

        Oracle.execute(conn, "DROP TABLE TB_BIND_TIMESTAMP_TZ")
    end

    @testset "Bind RAW" begin
        Oracle.execute(conn, "CREATE TABLE TB_RAW ( RAW_BYTES RAW(2000) )")

        bytes = rand(UInt8, 5)

        Oracle.stmt(conn, "INSERT INTO TB_RAW ( RAW_BYTES ) VALUES ( :a )") do stmt
            @test_throws ErrorException stmt[1] = bytes
            @test_throws ErrorException Oracle.Variable(conn, bytes)
            ora_var = Oracle.Variable(conn, Vector{UInt8}, Oracle.ORA_ORACLE_TYPE_RAW, Oracle.ORA_NATIVE_TYPE_BYTES)
            ora_var[1] = bytes
            stmt[1] = ora_var
            Oracle.execute(stmt)
        end

        Oracle.commit(conn)

        Oracle.stmt(conn, "SELECT RAW_BYTES FROM TB_RAW") do stmt
            Oracle.execute(stmt)
            row = Oracle.fetchrow(stmt)
            @test row != nothing
            @test row["RAW_BYTES"] == bytes
        end

        Oracle.execute(conn, "DROP TABLE TB_RAW")
    end
end

@testset "Variables" begin

    @testset "get/set values to Variables" begin
        ora_var = Oracle.Variable(conn, Float64, Oracle.ORA_ORACLE_TYPE_NATIVE_DOUBLE, Oracle.ORA_NATIVE_TYPE_DOUBLE)
        @test length(ora_var) == ora_var.buffer_capacity

        ora_var[1] = 0.0
        @test ora_var[1] == 0.0

        let
            for i in 1:ora_var.buffer_capacity
                ora_var[i] = Float64(i)
            end

            for i in 1:ora_var.buffer_capacity
                @test ora_var[i] == Float64(i)
            end
        end

        @test_throws AssertionError ora_var[0]
        @test_throws AssertionError ora_var[0] = 1.0

        @test_throws AssertionError ora_var[-1] = 1.0
        @test_throws AssertionError ora_var[ora_var.buffer_capacity+1] = 1.0

        @test_throws AssertionError ora_var[-1] == 1.0
        @test_throws AssertionError ora_var[ora_var.buffer_capacity+1] == 1.0
    end

    Oracle.execute(conn, "CREATE TABLE TB_VARIABLES ( FLT NUMBER(15,4) NULL )")

    @testset "low-level define API" begin
        ora_var = Oracle.Variable(conn, Union{Missing, Float64})

        Oracle.execute(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( :1 )", [ [123.45, 456.78, missing] ])
        stmt = Oracle.Stmt(conn, "SELECT FLT FROM TB_VARIABLES")
        Oracle.execute(stmt)

        Oracle.define(stmt, 1, ora_var)

        fetch_result = Oracle.fetchrows(stmt)
        @test fetch_result.num_rows_fetched == 3

        v = Oracle.ExternOracleValue(ora_var, ora_var.oracle_type, ora_var.native_type, ora_var.buffer_handle)
        @test v[fetch_result.buffer_row_index + 1] == 123.45
        @test v[fetch_result.buffer_row_index + 2] == 456.78
        @test ismissing(v[fetch_result.buffer_row_index + 3])

        Oracle.close(stmt)
        Oracle.rollback(conn)
    end

    @testset "high-level define API" begin
        Oracle.execute(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 0.00 )")
        Oracle.execute(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 1.00 )")
        Oracle.execute(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 0.01 )")
        Oracle.execute(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 100.00 )")

        vals = [ 0.00, 1.00, 0.01, 100.00 ]

        let
            ora_var = Oracle.Variable(conn, Float64)
            stmt = Oracle.Stmt(conn, "SELECT FLT FROM TB_VARIABLES")
            Oracle.execute(stmt)
            Oracle.define(stmt, 1, ora_var)
            result = Oracle.fetch(stmt)
            i = 1
            while result.found
                @test ora_var[result] ≈ vals[i]
                result = Oracle.fetch(stmt)
                i += 1
            end
            Oracle.close(stmt)
        end

        Oracle.rollback(conn)
    end

    @testset "bind to stmt" begin
        ora_var = Oracle.Variable(conn, Float64)
        stmt = Oracle.Stmt(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( :flt )")
        stmt[:flt] = ora_var
        Oracle.close(stmt)
    end

    @testset "low-level executeMany" begin
        how_many = 10

        let
            ora_var = Oracle.Variable(conn, Float64)

            for i in 1:how_many
                ora_var[i] = Float64(i)
            end

            stmt = Oracle.Stmt(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( :flt )")
            stmt[:flt] = ora_var
            result = Oracle.dpiStmt_executeMany(stmt.handle, Oracle.ORA_MODE_EXEC_DEFAULT, UInt32(how_many))
            Oracle.error_check(Oracle.context(stmt), result)

            Oracle.close(stmt)
            Oracle.commit(conn)
        end

        Oracle.query(conn, "SELECT FLT FROM TB_VARIABLES") do cursor
            row_number = 1.0
            for row in cursor
                @test row["FLT"] == row_number
                row_number += 1
            end
            @test row_number == how_many + 1
        end
    end

    Oracle.execute(conn, "DROP TABLE TB_VARIABLES")

    function check_data(cursor::Oracle.Cursor, columns)
        row_count = length(columns[1])
        column_count = length(columns)

        row_number = 1
        for row in cursor
            for c in 1:column_count
                val = columns[c][row_number]
                if ismissing(val)
                    @test ismissing(row[c])
                else
                    @test val == row[c]
                end
            end
            row_number += 1
        end
    end

    @testset "high-level executeMany" begin
        Oracle.execute(conn, "CREATE TABLE TB_EXECUTE_MANY ( ID NUMBER(15,0) NULL, FLT NUMBER(15,4) NULL, STR VARCHAR2(4000) )")

        columns = [ [1, 2, 3, 4, 5], [10.5, 20.5, 30.5, 40.5, missing], ["1", "2nd string is awesomely the biggest one", "3", "4th string is bigger", missing] ]
        Oracle.execute(conn, "INSERT INTO TB_EXECUTE_MANY ( ID, FLT, STR ) VALUES ( :1, :2, :3 )", columns)

        Oracle.query(conn, "SELECT ID, FLT, STR FROM TB_EXECUTE_MANY") do cursor
            check_data(cursor, columns)
        end

        Oracle.execute(conn, "DROP TABLE TB_EXECUTE_MANY")
    end

    @testset "execute_many with inputs and outputs, bind by name" begin
        Oracle.execute(conn, "CREATE TABLE TB_EXEC_MANY_INOUT ( ID NUMBER(15, 0) NOT NULL, STR VARCHAR2(4000), LB BLOB NOT NULL, FEW_BYTES RAW(200) NULL )")
        Oracle.execute(conn, "ALTER TABLE TB_EXEC_MANY_INOUT ADD CONSTRAINT XPK_TB_EXEC_MANY_INOUT PRIMARY KEY (ID)")
        Oracle.execute(conn, "CREATE SEQUENCE SQ_TB_EXEC_MANY_INOUT INCREMENT BY 1  START WITH 1001")

        input_strings = ["input1", "input2", missing]
        num_iters = length(input_strings)
        var_input = Oracle.Variable(conn, input_strings)

        # blob vars
        blob_data = [ rand(UInt8, 5000) for i in 1:num_iters ]
        blobs = [ Oracle.Lob(conn, Oracle.ORA_ORACLE_TYPE_BLOB) for i in 1:num_iters ]
        for i in 1:num_iters
            write(blobs[i], blob_data[i])
        end
        var_blob = Oracle.Variable(conn, blobs)

        # raw vars
        raw_data = [ rand(UInt8, 200) for i in 1:(num_iters-1) ]
        raw_data = vcat(raw_data, [missing])
        var_raw = Oracle.Variable(conn, raw_data)

        var_output = Oracle.Variable(conn, Int, buffer_capacity=num_iters)

        vars = Dict(:var_input => var_input, :var_blob => var_blob, :var_raw => var_raw, :var_output => var_output)

        try
            Oracle.stmt(conn, "INSERT INTO TB_EXEC_MANY_INOUT ( ID, STR, LB, FEW_BYTES ) VALUES ( SQ_TB_EXEC_MANY_INOUT.nextval, :var_input, :var_blob, :var_raw ) RETURNING ID INTO :var_output") do stmt
                Oracle.execute_many(stmt, num_iters, vars)
            end

            Oracle.commit(conn)

            Oracle.query(conn, "SELECT ID, STR, LB, FEW_BYTES FROM TB_EXEC_MANY_INOUT ORDER BY ID") do cursor

                column_names = names(cursor)
                @test "ID" ∈ column_names
                @test "STR" ∈ column_names
                @test "LB" ∈ column_names
                @test "FEW_BYTES" ∈ column_names

                i = 1
                for row in cursor

                    if !ismissing(row["STR"])
                        @test parse(Int, row["STR"][end]) + 1000 == row["ID"]
                    end

                    @test row["ID"] == Oracle.get_returned_data(var_output, i)[1]
                    @test read(row["LB"]) == blob_data[i]

                    if !ismissing(row["FEW_BYTES"])
                        @test row["FEW_BYTES"] == raw_data[i]
                    end

                    i += 1
                end
            end

            for i in 1:num_iters
                @test Oracle.get_returned_data(var_output, i)[1] == 1000 + i
            end

        finally
            Oracle.execute(conn, "DROP SEQUENCE SQ_TB_EXEC_MANY_INOUT")
            Oracle.execute(conn, "DROP TABLE TB_EXEC_MANY_INOUT")
        end
    end

    @testset "execute_many with inputs and outputs, bind by position" begin
        Oracle.execute(conn, "CREATE TABLE TB_EXEC_MANY_INOUT ( ID NUMBER(15, 0) NOT NULL, STR VARCHAR2(4000) )")
        Oracle.execute(conn, "ALTER TABLE TB_EXEC_MANY_INOUT ADD CONSTRAINT XPK_TB_EXEC_MANY_INOUT PRIMARY KEY (ID)")
        Oracle.execute(conn, "CREATE SEQUENCE SQ_TB_EXEC_MANY_INOUT INCREMENT BY 1  START WITH 1001")

        input_data = ["input1", "input2", missing]
        num_iters = length(input_data)
        var_input = Oracle.Variable(conn, input_data)
        var_output = Oracle.Variable(conn, Int, buffer_capacity=num_iters)

        vars = [ var_input, var_output ]

        try
            Oracle.stmt(conn, "INSERT INTO TB_EXEC_MANY_INOUT ( ID, STR ) VALUES ( SQ_TB_EXEC_MANY_INOUT.nextval, :var_input ) RETURNING ID INTO :var_output") do stmt
                Oracle.execute_many(stmt, num_iters, vars)
            end

            Oracle.commit(conn)

            Oracle.query(conn, "SELECT ID, STR FROM TB_EXEC_MANY_INOUT ORDER BY ID") do cursor
                i = 1
                for row in cursor
                    if !ismissing(row["STR"])
                        @test parse(Int, row["STR"][end]) + 1000 == row["ID"]
                    end
                    @test row["ID"] == Oracle.get_returned_data(var_output, i)[1]
                    i += 1
                end
            end

            for i in 1:num_iters
                @test Oracle.get_returned_data(var_output, i)[1] == 1000 + i
            end

        finally
            Oracle.execute(conn, "DROP SEQUENCE SQ_TB_EXEC_MANY_INOUT")
            Oracle.execute(conn, "DROP TABLE TB_EXEC_MANY_INOUT")
        end
    end

    @testset "BLOB Variable" begin
        Oracle.execute(conn, "CREATE TABLE TB_BLOB_VARIABLE ( B BLOB )")

        test_data = rand(UInt8, 5000)

        let
            blob = Oracle.Lob(conn, Oracle.ORA_ORACLE_TYPE_BLOB)
            write(blob, test_data)

            ora_var = Oracle.Variable(conn, blob)

            Oracle.stmt(conn, "INSERT INTO TB_BLOB_VARIABLE ( B ) VALUES ( :1 )") do stmt
                stmt[1] = ora_var
                Oracle.execute(stmt)
            end
        end

        let
            Oracle.query(conn, "SELECT B FROM TB_BLOB_VARIABLE") do cursor
                for row in cursor
                    blob = row["B"]
                    @test isa(blob, Oracle.Lob)
                    read_data = read(blob)
                    @test read_data == test_data
                    Oracle.close(blob)
                end
            end
        end

        Oracle.execute(conn, "DROP TABLE TB_BLOB_VARIABLE")
    end

    @testset "CLOB Variable" begin
        Oracle.execute(conn, "CREATE TABLE TB_CLOB_VARIABLE ( C CLOB )")

        test_data = "Lorem ipsum dolor sit amet"^1000

        let
            clob = Oracle.Lob(conn, Oracle.ORA_ORACLE_TYPE_CLOB)
            write(clob, test_data)

            ora_var = Oracle.Variable(conn, clob)

            Oracle.stmt(conn, "INSERT INTO TB_CLOB_VARIABLE ( C ) VALUES ( :1 )") do stmt
                stmt[1] = ora_var
                Oracle.execute(stmt)
            end
        end

        let
            Oracle.query(conn, "SELECT C FROM TB_CLOB_VARIABLE") do cursor
                for row in cursor
                    clob = row["C"]
                    @test isa(clob, Oracle.Lob)
                    read_data = read(clob)
                    @test read_data == test_data
                    Oracle.close(clob)
                end
            end
        end

        Oracle.execute(conn, "DROP TABLE TB_CLOB_VARIABLE")
    end

# see issue #21
#    @testset "OraNumber" begin
#        @testset "read" begin
#            nums_str = [
#                         "0",
#                         "1",
#                         "10",
#                         "100",
#                         "2200",
#                         "2244",
#                         "2244.5566",
#                         "0.01",
#                         "0.0001",
#                         "0.0002",
#                         "0.5555",
#                         "0.0505",
#                         "0.0121",
#                         "0.0005",
#                         "10000000",
#                         "10000001",
#                         "50000000",
#                         "50005000",
#                         "54200254000456.5005",
#                         "9999999999999999999999999999999999.9999",
#                         "-1",
#                         "-10",
#                         "-100",
#                         "-2200",
#                         "-2244",
#                         "-2244.5566",
#                         "-0.01",
#                         "-0.0001",
#                         "-0.0002",
#                         "-0.5555",
#                         "-0.0505",
#                         "-0.0121",
#                         "-0.0005",
#                         "-10000000",
#                         "-10000001",
#                         "-50000000",
#                         "-50005000",
#                         "-54200254000456.5005",
#                         "-9999999999999999999999999999999999.9999"
#                    ]
#
#            Oracle.execute(conn, "CREATE TABLE TB_NUMBER_VARIABLE ( N NUMERIC(38, 4) )")
#
#            for str in nums_str
#                Oracle.execute(conn, "INSERT INTO TB_NUMBER_VARIABLE ( N ) VALUES ( $str )")
#            end
#
#            let
#                ora_var_number = Oracle.Variable(conn, Oracle.OraNumbers.OraNumber)
#                stmt = Oracle.Stmt(conn, "SELECT N FROM TB_NUMBER_VARIABLE")
#                Oracle.execute(stmt)
#                Oracle.define(stmt, 1, ora_var_number)
#                result = Oracle.fetch(stmt)
#
#                i = 1
#                while result.found
#                    val = ora_var_number[result]
#
#                    @test Oracle.OraNumbers.isnormalized(val)
#                    @test Oracle.OraNumbers.normalize(val) == val
#
#                    if val != zero(Oracle.OraNumbers.OraNumber)
#                        # encode_exponent_byte cannot be used on zero or special numbers
#                        @test val.ex == Oracle.OraNumbers.encode_exponent_byte(Oracle.OraNumbers.decode_exponent_byte(val), Oracle.OraNumbers.isnegative(val))
#                    end
#
#                    for mantissa_byte_index in 1:Oracle.OraNumbers.sizeof_mantissa(val)
#                        @test Oracle.OraNumbers.encode_mantissa_byte(Oracle.OraNumbers.decode_mantissa_byte(val, mantissa_byte_index), Oracle.OraNumbers.isnegative(val)) == val.mantissa[mantissa_byte_index]
#                    end
#
#                    @test -(-val) == val
#
#                    # Testing normalize
#                    if val != zero(Oracle.OraNumbers.OraNumber)
#                        for exponent_increment in 1:3
#                            if val.len + exponent_increment > 21
#                                # overflow
#                                @test_throws ErrorException Oracle.OraNumbers.inc_exponent(val, exponent_increment)
#                            else
#                                @test Oracle.OraNumbers.normalize(Oracle.OraNumbers.inc_exponent(val, exponent_increment)) == val
#                            end
#                        end
#                    end
#
#                    #Oracle.OraNumbers.debug_number(val)
#                    str = nums_str[i]
#                    @test string(val) == str
#
#                    # Testing -x
#                    if val == zero(Oracle.OraNumbers.OraNumber)
#                        @test string(-val) == string(val)
#                    else
#                        if Oracle.OraNumbers.ispositive(val)
#                            @test string(-val) == "-" * str
#                        else
#                            @test string(-val) == str[2:end]
#                        end
#                    end
#
#                    result = Oracle.fetch(stmt)
#                    i += 1
#                end
#                Oracle.close(stmt)
#            end
#
#            Oracle.execute(conn, "DROP TABLE TB_NUMBER_VARIABLE")
#        end
#
#        @testset "write" begin
#            Oracle.execute(conn, "CREATE TABLE TB_WRITE_NUMBER_VARIABLE ( N NUMERIC(38, 4) )")
#
#            stmt = Oracle.Stmt(conn, "INSERT INTO TB_WRITE_NUMBER_VARIABLE ( N ) VALUES ( :1 )")
#
#            try
#
#                let
#                    ora_var = Oracle.Variable(conn, Oracle.OraNumber)
#                    ora_var[1] = zero(Oracle.OraNumber)
#                    ora_var[2] = one(Oracle.OraNumber)
#                    ora_var[3] = Oracle.OraNumber(11, 56, (0x2f, 0x51, 0x63, 0x2f, 0x65, 0x61, 0x2d, 0x33, 0x60, 0x66, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00))
#
#                    stmt[1] = ora_var
#
#                    result = Oracle.dpiStmt_executeMany(stmt.handle, Oracle.ORA_MODE_EXEC_DEFAULT, UInt32(3))
#                    Oracle.error_check(Oracle.context(stmt), result)
#                end
#
#                Oracle.query(conn, "SELECT N FROM TB_WRITE_NUMBER_VARIABLE") do cursor
#
#                    data = [ 0, 1, -54200254000456.5005]
#
#                    i = 1
#                    for row in cursor
#                        @test row["N"] ≈ data[i]
#                        i += 1
#                    end
#                end
#
#            finally
#                Oracle.close(stmt)
#                Oracle.execute(conn, "DROP TABLE TB_WRITE_NUMBER_VARIABLE ")
#            end
#        end
#    end
end

@testset "ObjectType" begin

    obj_name = "MY_CUSTOM_TYPE"

    Oracle.execute(conn, """
CREATE TYPE $obj_name AS OBJECT
(
    ID NUMBER(5),
    NAME VARCHAR2(50)
)
""")

    object_type = Oracle.ObjectType(conn, obj_name)

    @test Oracle.object_type_schema(object_type) == "SYS"
    @test Oracle.object_type_name(object_type) == obj_name
    @test Oracle.object_type_num_attributes(object_type) == 2
    @test !Oracle.object_type_is_collection(object_type)
    @test Oracle.object_type_element_type_info(object_type) == nothing

    Oracle.execute(conn, "DROP TYPE $obj_name")
end

function init_raw_queue(connection::Oracle.Connection)
        Oracle.execute(connection, """
DECLARE
BEGIN
    dbms_aqadm.CREATE_queue_table(
        queue_table => 'my_queue_table',
        multiple_consumers => FALSE,
        queue_payload_type => 'RAW',
        compatible => '8.1.3',
        comment => 'Creating prop queue table');
END;
""")

        Oracle.execute(connection, """
DECLARE
BEGIN
    dbms_aqadm.CREATE_queue(
        queue_name => 'my_queue',
        queue_table => 'my_queue_table',
        comment => 'My Queue');
END;
""")

        Oracle.execute(connection, """
DECLARE
BEGIN
    dbms_aqadm.start_queue(
        queue_name => 'my_queue');
END;
""")
end

function drop_raw_queue(connection::Oracle.Connection)
    Oracle.execute(connection, """
DECLARE
BEGIN
 dbms_aqadm.stop_queue
    (
        queue_name => 'my_queue',
        wait       => TRUE
    );
END;
""")

Oracle.execute(connection, """
DECLARE
BEGIN
 dbms_aqadm.drop_queue
    (
        queue_name => 'my_queue'
    );
END;
""")

Oracle.execute(connection, """
DECLARE
BEGIN
    dbms_aqadm.drop_queue_table
    (
        queue_table => 'my_queue_table',
        force => TRUE
    );
END;
""")
end

# https://docs.oracle.com/database/121/ADQUE/aq_intro.htm#ADQUE2507
@testset "Queue" begin

    init_raw_queue(conn)

    try
        queue = Oracle.Queue(conn, "my_queue")
        msg = Oracle.Message(conn)
        Oracle.set_correlation!(msg, "correl")
        Oracle.set_payload_bytes!(msg, UInt8[10, 20, 30, 40, 50])
        @test Oracle.get_payload_bytes(msg) == UInt8[10, 20, 30, 40, 50]

        Oracle.enqueue(queue, msg)
        Oracle.enqueue(queue, fill(msg, 9))
        @test Oracle.get_correlation(msg) == "correl"
        #Oracle.clear_correlation!(msg)
        #@test Oracle.get_correlation(msg) == nothing

        dequeued_message = Oracle.dequeue(queue)
        @test Oracle.get_payload_bytes(dequeued_message) == UInt8[10, 20, 30, 40, 50]
        @test Oracle.get_correlation(dequeued_message) == "correl"

        dequeued_vec = Oracle.dequeue(queue, 4)
        @test length(dequeued_vec) == 4
        for msg in dequeued_vec
            @test Oracle.get_payload_bytes(dequeued_message) == UInt8[10, 20, 30, 40, 50]
        end

        dequeued_vec = Oracle.dequeue(queue, 100)
        @test length(dequeued_vec) == 5
        for msg in dequeued_vec
            @test Oracle.get_payload_bytes(dequeued_message) == UInt8[10, 20, 30, 40, 50]
        end

    finally
        drop_raw_queue(conn)
    end
end

if auth_mode != Oracle.ORA_MODE_AUTH_SYSDBA
    @testset "Pool" begin

        @testset "Create a Pool" begin
            ctx = Oracle.Context()

            let
                pool = Oracle.Pool(ctx, username, password, connect_string)
                Oracle.close(pool)
            end

            let
                pool = Oracle.Pool(ctx, username, password, connect_string, min_sessions=2, max_sessions=4, session_increment=1)
                @test Oracle.pool_get_mode(pool) == Oracle.ORA_MODE_POOL_GET_NOWAIT
                Oracle.close(pool)
            end

            let
                pool = Oracle.Pool(ctx, username, password, connect_string, get_mode=Oracle.ORA_MODE_POOL_GET_WAIT)
                @test Oracle.pool_get_mode(pool) == Oracle.ORA_MODE_POOL_GET_WAIT
                Oracle.close(pool)
            end
        end

function print_open_busy_count(pool)
    println("Open Count = $(Oracle.pool_get_open_count(pool)), busy count = $(Oracle.pool_get_busy_count(pool))")
end

        @testset "Acquire connection from pool" begin
            pool = Oracle.Pool(username, password, connect_string, max_sessions=2, session_increment=1)

            print_open_busy_count(pool)

            conn_1 = Oracle.Connection(pool)
            conn_2 = Oracle.Connection(pool)

            print_open_busy_count(pool)

            Oracle.close(conn_1)

            print_open_busy_count(pool)

            conn_3 = Oracle.Connection(pool)

            print_open_busy_count(pool)

            Oracle.close(conn_2)
            Oracle.close(conn_3)

            print_open_busy_count(pool)

            Oracle.close(pool)
        end

        @testset "Supported encodings" begin
            for enc in Oracle.SUPPORTED_CONNECTION_ENCODINGS
                pool = Oracle.Pool(username, password, connect_string, encoding=enc, nencoding=enc)
                conn_enc = Oracle.Connection(pool, auth_mode=auth_mode)
                Oracle.close(conn_enc)
                Oracle.close(pool)
            end
        end
    end
end

#=
@testset "shutdown/startup" begin
    # The connection needs to have been established at least with authorization mode set to ORA_MODE_AUTH_SYSDBA or ORA_MODE_AUTH_SYSOPER
    Oracle.shutdown_database(conn)
    Oracle.startup_database(conn)
end
=#

Oracle.close(conn)


text:
push!(LOAD_PATH, "/Users/neal/Projects/mimix/src")

using ArgParse
using CSV
using DataFrames
using Mamba
using MicrobiomeMixedModels
using Random
using YAML

function parse_commandline()
    s = ArgParseSettings("Fit a model to artificially-generated data.")
    @add_arg_table s begin
        "output"
            help = "File to which simulation results are written."
        "--iters", "-i"
            arg_type = Int
            default = 500
            help = "Number of MCMC iterations."
        "--burnin", "-b"
            arg_type = Int
            default = 0
            help = "Number of initial MCMC iterations to remove as burn-in."
        "--thin", "-t"
            arg_type = Int
            default = 1
            help = "Retain one of every 'thin' iterations."
        "--chains", "-c"
            arg_type = Int
            default = 1
            help = "Number of MCMC chains to run."
        "--seed"
            arg_type = Int
            default = 1
            help = "Reseed the random number generator."
        "--data"
            nargs = '*'
            help = "YAML defining the settings for artificial data generation."
        "--monitor"
            nargs = '*'
            help = "YAML defining the nodes to monitor through MCMC."
        "--inits"
            nargs = '*'
            help = "YAML defining initial values of stochastic nodes."
        "--hyper"
            nargs = '*'
            help = "YAML defining hyperparameters of stochastic node priors."
        "--factors"
            arg_type = Int
            default = 0
            help = "Number of factors to use in fitting the MIMIX model."
        "--loadings"
            default = "DL"
            help = "Distributions on loadings (G for Gaussian priors, DL for Dirichlet-Laplace priors)."
        "--permanova"
            help = "Run PERMANOVA with vegan::adonis() in R."
            action = :store_true
    end
    return parse_args(s)
end

function ar1_covar(d::Int, cor::Float64, var::Float64)
    @assert var > zero(var)
    @assert abs(cor) <= one(cor)
    Σ = zeros(d, d)
    for j in 1:d
        for i in 1:d
            Σ[i, j] = var * cor^abs(i - j)
        end
    end
    Σ
end

function generate_data(;
    N::Int=40, K::Int=100, L::Int=-1, q::Int=5, m_min::Int=2500, m_max::Int=5000,
    μ::Vector=collect(range(1, stop=-1, length=K)), block_cor::Float64=0.9, error_cor::Float64=0.9,
    form::String="grouped", dense::Float64=0.1, block_var::Float64=1.0, error_var::Float64=1.0,
    a_support::Vector{Float64}=collect(0.01:0.01:0.5), seed::Int=1)

    @assert length(μ) == K
    @assert iseven(N)
    @assert N % q == 0

    Random.seed!(seed)

    # Fixed effects due to single treatment
    X = transpose([ones(div(N, 2))... zeros(div(N, 2))...])
    β = zeros(K)
    if form == "grouped"
        ngroup = round(Int64, dense / 0.05)
        group = shuffle(collect(1:5:K))[1:ngroup]
        for g in group
            v = rand(Uniform(-2, 2))
            v += 1.0sign(v)
            for i in g:min(g + 4, K)
                β[i] = v
            end
        end
    elseif form == "random"
        nindividual = round(Int64, dense * K)
        individual = shuffle(collect(1:K))[1:nindividual]
        for i in individual
            v = rand(Uniform(-2, 2))
            v += 1.0sign(v)
            β[i] = v
        end
    end

    # Random effects due to block
    Z = reshape(repeat(collect(1:q), outer=[div(N, q)]), N, 1)
    γ = rand(MvNormal(ar1_covar(K, block_cor, block_var)), q)

    # Unconstrained transformed probabilities
    Σ = ar1_covar(K, error_cor, error_var)
    θ = zeros(N, K)
    for i in 1:N
        θ[i, :] = rand(MvNormal(μ + β * X[i] + γ[:, Z[i]], Σ))
    end

    # Multivariate count data
    e = exp.(θ)
    ϕ = e ./ sum(e, dims=2)
    m = sample(m_min:m_max, N)
    Y = zeros(Int, (N, K))
    for i in 1:N
        Y[i, :] = rand(Multinomial(m[i], ϕ[i, :]))
    end

    # Define and return dicts of data (for mcmc) and truth (for validation)
    truth = Dict{Symbol, Any}(
        :β => β
    )

    data = Dict{Symbol, Any}(
        :X => X,
        :Y => Y,
        :Z => Z,
        :N => N,
        :K => K,
        :m => m,
        :q => q,
        :L => L
    )
    data[:p] = size(X, 2)
    data[:num_blocking_factors] = size(Z, 2)
    data[:blocking_factor] = Dict{Int, Int}()
    for level in unique(Z)
        bf = findall(vec(any(Z .== level, dims=1)))
        @assert length(bf) == 1
        data[:blocking_factor][level] = bf[1]
    end

    return data, truth
end

args = parse_commandline()

output = abspath(args["output"])
if !isdir(output)
    mkdir(output)
end

@assert 0 <= args["seed"]   "Seed must be non-negative"

if args["permanova"]
    data_conf = load_config(AbstractString[abspath(data_path) for data_path in args["data"]])

    seed = args["seed"]
    data, truth = generate_data(; seed = seed, data_conf...)

    println("Beginning PERMANOVA test with vegan::adonis in R.")
    sim = permanova(data)
    global_results = DataFrame(
        reject_global_null = sim[:pval] < 0.05,
        dense = data_conf[:dense],
        block_var = data_conf[:block_var],
        error_var = data_conf[:error_var],
        form = data_conf[:form]
    )

    global_test_path = joinpath(output, "global-test.tsv")
    println("Writing global test results to $global_test_path")
    CSV.write(global_test_path, global_results, delim='\t')
else  # mimix
    @assert 0 < args["iters"]   "Iters must be positive"
    @assert 0 <= args["burnin"] "Burn-in must be non-negative"
    @assert 0 < args["thin"]    "Thin must be positive"
    @assert 0 < args["chains"]  "Chains must be positive"

    factors = args["factors"]
    loadings = args["loadings"]
    if (factors > 0) & (loadings == "DL")
        model_type = MIMIX(factors)
    elseif (factors > 0) & (loadings == "G")
        model_type = MIMIXGaussian(factors)
    elseif factors == 0
        model_type = MIMIXNoFactors()
    else
        ValueError("--factors requires a non-negative integer.")
    end

    monitor_conf = load_config(AbstractString[abspath(data_path) for data_path in args["monitor"]])
    hyper_conf = load_config(AbstractString[abspath(data_path) for data_path in args["hyper"]])
    data_conf = load_config(AbstractString[abspath(data_path) for data_path in args["data"]])
    inits_conf = load_config(AbstractString[abspath(data_path) for data_path in args["inits"]])

    model = get_model(model_type, monitor_conf, hyper_conf)

    seed = args["seed"]
    data, truth = generate_data(; L = factors, seed = seed, data_conf...)

    inits = get_inits(model_type, inits_conf, data)
    inits = [inits for _ in 1:args["chains"]]

    println("Beginning MCMC simulation")
    Random.seed!(args["seed"])
    mcmc_kwargs = Dict(Symbol(key) => args[key] for key in ["burnin", "thin", "chains"])
    sim = mcmc(model, data, inits, args["iters"]; mcmc_kwargs...)

    # summarize global test results
    sim_omega = sim[:, [startswith(name, 'ω') for name in sim.names], :].value
    num_included_each_iter = sum(sim_omega, dims=2)
    post_prob_inclusion = mean(num_included_each_iter .> 0.0)
    global_results = DataFrame(
        reject_global_null = post_prob_inclusion > 0.9,
        dense = data_conf[:dense],
        block_var = data_conf[:block_var],
        error_var = data_conf[:error_var],
        form = data_conf[:form]
    )

    global_test_path = joinpath(output, "global-test.tsv")
    println("Writing global test results to $global_test_path")
    CSV.write(global_test_path, global_results, delim='\t')

    # summarize local parameter estimates
    sim_beta_names = sim.names[[startswith(name, 'β') for name in sim.names]]
    sim_beta = sim[:, sim_beta_names, :]
    results = DataFrame(mamba_name = sim_beta_names)
    nodes = Symbol[]
    vals = Float64[]
    for name in results[:mamba_name]
        for (node, value) in truth
            if startswith(name, String(node))
                push!(nodes, node)
                if '[' in name
                    index = name[collect(findfirst("[", name))[1]:end]
                    index = strip(index, ['[', ']'])
                    index = parse.(Int, split(index, ','))
                    push!(vals, value[index...])
                else
                    push!(vals, value)
                end
            end
        end
    end
    results[:mamba_node] = nodes
    results[:value] = vals

    post_summary = summarystats(sim_beta)
    post_quantiles = quantile(sim_beta)
    results[:mean] = post_summary.value[:, 1]
    for (i, q) in enumerate(post_quantiles.colnames)
        results[Symbol(q)] = post_quantiles.value[:, i]
    end

    local_estimates_path = joinpath(output, "local-estimates.tsv")
    println("Writing local parameter estimates to $local_estimates_path")
    CSV.write(local_estimates_path, results, delim='\t')
end

ext: jl


text:
# ---
# title: 328. Odd Even Linked List
# id: problem328
# author: Tian Jun
# date: 2020-10-31
# difficulty: Medium
# categories: Linked List
# link: <https://leetcode.com/problems/odd-even-linked-list/description/>
# hidden: true
# ---
#
# Given a singly linked list, group all odd nodes together followed by the even
# nodes. Please note here we are talking about the node number and not the value
# in the nodes.
#
# You should try to do it in place. The program should run in O(1) space
# complexity and O(nodes) time complexity.
#
# **Example 1:**
#
#
#
#     Input:1->2->3->4->5->NULL
#     Output:1->3->5->2->4->NULL
#
#
# **Example 2:**
#
#
#
#     Input: 2->1->3->5->6->4->7->NULL
#     Output:2->3->6->7->1->5->4->NULL
#
#
#
#
# **Constraints:**
#
#   * The relative order inside both the even and odd groups should remain as it was in the input.
#   * The first node is considered odd, the second node even and so on ...
#   * The length of the linked list is between `[0, 10^4]`.
#
#
## @lc code=start
using LeetCode

## add your code here:
## @lc code=end


text:
# See ?extrap_prep for documentation for all these methods

extrap_prep{T}(g::Val{:gradient}, ::Type{T}, n::Val{1}) = extrap_prep(g, T, n, Val{1}())
extrap_prep{T}(g::Val{:gradient}, ::Type{Tuple{T}}, n::Val{1}) = extrap_prep(g, T, n)
extrap_prep{T}(g::Val{:gradient}, ::Type{Tuple{T,T}}, n::Val{1}) = extrap_prep(g, T, n)
extrap_prep{T}(g::Val{:gradient}, ::Type{Tuple{Tuple{T,T}}}, n::Val{1}) = extrap_prep(g, T, n)
function extrap_prep{S,T}(g::Val{:gradient}, ::Type{Tuple{S,T}}, ::Val{1})
    quote
        $(extrap_prep(g, S, n, Val{1}(), Val{:lo}()))
        $(extrap_prep(g, T, n, Val{1}(), Val{:hi}()))
    end
end
extrap_prep{S,T}(g::Val{:gradient}, ::Type{Tuple{Tuple{S,T}}}, n::Val{1}) = extrap_prep(g, Tuple{S,T}, n)
# needed for ambiguity resolution
extrap_prep{T<:Tuple}(::Val{:gradient}, ::Type{T}, ::Val{1}) = :(throw(ArgumentError("The 1-dimensional extrap configuration $T is not supported")))

function extrap_prep{T,N}(g::Val{:gradient}, ::Type{T}, n::Val{N})
    Expr(:block, [extrap_prep(g, T, n, Val{d}()) for d in 1:N]...)
end

function extrap_prep{T<:Tuple,N}(g::Val{:gradient}, ::Type{T}, n::Val{N})
    length(T.parameters) == N || return :(throw(ArgumentError("The $N-dimensional extrap configuration $T is not supported")))
    exprs = Expr[]
    for d in 1:N
        Tdim = T.parameters[d]
        if Tdim <: Tuple
            length(Tdim.parameters) == 2 || return :(throw(ArgumentError("The extrap configuration $Tdim for dimension $d is not supported - must be a tuple of length 2 or a simple configuration type"))
                )
            if Tdim.parameters[1] != Tdim.parameters[2]
                push!(exprs, extrap_prep(g, Tdim, n, Val{d}()))
            else
                push!(exprs, extrap_prep(g, Tdim.parameters[1], n, Val{d}()))
            end
        else
            push!(exprs, extrap_prep(g, Tdim, n, Val{d}()))
        end
    end
    return Expr(:block, exprs...)
end

function extrap_prep{S,T,N,d}(g::Val{:gradient}, ::Type{Tuple{S,T}}, n::Val{N}, dim::Val{d})
    quote
        $(extrap_prep(g, S, n, dim, Val{:lo}()))
        $(extrap_prep(g, T, n, dim, Val{:hi}()))
    end
end

extrap_prep{T,N,d}(g::Val{:gradient}, ::Type{T}, n::Val{N}, dim::Val{d}) = extrap_prep(g, Tuple{T,T}, n, dim)
extrap_prep(g::Val{:gradient}, args...) = extrap_prep(args...)


text:
# Use baremodule to shave off a few KB from the serialized `.ji` file
baremodule libpng_jll
using Base
using Base: UUID
import JLLWrappers

JLLWrappers.@generate_main_file_header("libpng")
JLLWrappers.@generate_main_file("libpng", UUID("b53b4c65-9356-5827-b1ea-8c7a1a84506f"))
end  # module libpng_jll


text:
using SafeTestsets

@time begin
@time @safetestset "NNODE" begin include("NNODE_tests.jl") end
@time @safetestset "NNPDEHan" begin include("NNPDEHan_tests.jl") end
@time @safetestset "NNPDENS" begin include("NNPDENS_tests.jl") end
end


text:
# multivariate gamma function
# Chikuse, 2003 Eq. 1.5.7
mvgamma(m, a) = π^(m * (m - 1)//4) * prod(i -> gamma(a - (i - 1)//2), 1:m)

# tr(A'B)
tr_At_B(A, B) = sum((Ai, Bi) -> dot(Ai, Bi), zip(eachcol(A), eachcol(B)))

# hypergeometric functions with matrix arguments
pFq((), (), x) = exp(tr(x)) # ₀F₀
# TODO: Implement ₀F₁
# see P Koev, A Edelman. The efficient evaluation of the hypergeometric function of a
# matrix argument. Math. Comp. 75 (2006), 833-846
# pFq(a::NTuple{p}, b::NTuple{q}, x)


text:
module Edges
#using Pkg
#Pkg.update()
using DataFrames
using GraphDataFrameBridge
using MetaGraphs
using CSV
using LightGraphs
using Random
using DataFramesMeta
using Colors
using Logging

using SemanticModels.Graphs
using SemanticModels.Extraction

export edgetype,
    edges,
    create_kg_from_code_edges,
    create_kg_from_markdown_edges,
    format_edges_dataframe,
    assign_vertex_colors_by_key,
    write_graph_to_dot_file

function edgetype(var, val::Expr)
    if val.head == :call
        return :output
    elseif length(val.args) >=2 && typeof(val.args[2]) <: Expr && val.args[2].head == :tuple
        @show val.args
        return :structure
    else
        return :takes
    end
end

edgetype(var, val::Symbol) = :destructure
edgetype(var, val) = :takes
edgetype(var::Symbol, val::Symbol) = :takes
edgetype(args...) = @show args

function edges(mc, subdef, scope)
    @info("Making edges",scope=scope)
    edg = Any[]
    for ( var,val ) in mc.vc
        @show var, val
        typ = edgetype(var, val)
        val = typ==:structure ? val.args[2] : val
        e = (scope, typ, :var, var, :val, val)
        push!(edg, e)
        if typ == :output
            e = (scope, typ, :var, var, :exp, val)
            push!(edg, e)
        end
        if typ == :structure
            e = (scope, typ, :var, var, :tuple, val.args[2])
            push!(edg, e)
        end

        if typeof(val) <: Expr && val.head == :vect
            push!(edg, (scope, :has, :value, var, :property, :collection))
        end
        if typeof(val) <: Expr && val.head == :call
            push!(edg, (scope, :input, :func, val.args[1], :args, Symbol.(val.args[2:end])))
        end
        if typ == :destructure
            @debug var.args
            for lhs in var.args
                push!(edg, (scope, :comp, :var, val, :var, lhs))
            end
        end
        if typ == :structure
            @debug var, val
            for rhs in val.args
                push!(edg, (scope, :comp, :var, var, :val, rhs))
            end
        end

    end
    for (funcname, smc) in subdef
        @debug "Recursing"
        # @show funcname, smc
        subedges = edges(smc, [], "$scope.$funcname")
        for e in subedges
            push!(edg, e)
        end
    end
    return edg
end

function preprocess_vertex_name(orig_str_rep)
    #TODO this should work and be faster:
    # replace(orig_str_rep, "^" => "exp", "*" => "star", "-" => "neg", "\"" => " ") # \" #this comment works around a bug in emacs julia formater
    clean_str = replace(
                  replace(
                      replace(
                          replace(orig_str_rep, "^" => "exp"),
                          "*" => "star"),
                      "-" => "neg"),
                  "\"" => " ") # \" #this comment works around a bug in emacs julia formater
    return clean_str
end

function format_edges_dataframe(code_edges, output_path::String)

    edges_df = []

    for e in code_edges

        # Going from left to right, there are two edges we need to create (?)

        # Edge one
        src_1_vname = "$(e[1])"
        src_1_vtype = "missing"
        #dst_1_vname = preprocess_vertex_name(("\""*"\""*"$(e[4])"*"\""*"\""))
        dst_1_vname = "$(e[4])"
        #dst_1_vtype = e[3] # this is the data type; we'll use it once we have reconciliation rules in place
        dst_1_vtype = "missing"

        src_1_vhash = Graphs.gen_vertex_hash("$src_1_vname", "src_1_vtype")
        dst_1_vhash = Graphs.gen_vertex_hash("$dst_1_vname", "dst_1_vtype")

        edge_1_relation = e[2]
        edge_1_description = e[2]
        edge_1_value = e[6] # check this; unclear if we want to (only) store as metadata or actually create edge 2

        edge_1_attrs = (src_vhash = "$src_1_vhash",
            src_name = src_1_vname,
            src_vtype = src_1_vtype,
            dst_vhash = "$dst_1_vhash",
            dst_name = dst_1_vname,
            dst_vtype = dst_1_vtype,
            edge_relation = edge_1_relation,
            edge_description = edge_1_description,
            value = "$edge_1_value"
         )

        push!(edges_df, edge_1_attrs)

        # Edge two
        src_2_vname = dst_1_vname
        src_2_vtype = dst_1_vtype
        #dst_2_vname = preprocess_vertex_name(("\""*"\""*"$(e[6])"*"\""*"\""))
        dst_2_vname = "$(e[6])"
        dst_2_vtype = "missing"

        src_2_vhash = dst_1_vhash
        dst_2_vhash = Graphs.gen_vertex_hash("$dst_2_vname", "dst_2_vtype")

        edge_2_relation = string(e[5])
        edge_2_description = string(e[5])
        edge_2_value = string(e[6])

        edge_2_attrs = (src_vhash = "$src_2_vhash",
            src_name = src_2_vname,
            src_vtype = src_2_vtype,
            dst_vhash = "$dst_2_vhash",
            dst_name = dst_2_vname,
            dst_vtype = dst_2_vtype,
            edge_relation = edge_2_relation,
            edge_description = edge_2_description,
            value = "$edge_2_value"
         )

        push!(edges_df, edge_2_attrs)
    end

    open(output_path, "w") do io
        print(io, repr(Graphs.load_graph_data(edges_df)))
    end

    return edges_df

end

function create_kg_from_code_edges(code_edges)

    G = MetaDiGraph()
    G_prime = Graphs.insert_edges_from_jl(code_edges, G)
    return G_prime
end

function create_kg_from_code_edges(code_edges, G::MetaDiGraph)

    G_prime = Graphs.insert_edges_from_jl(code_edges, G)
    return G_prime
end

function create_kg_from_markdown_edges(path)

    G = Extraction.definitiongraph(path, Extraction.sequentialnamer())

    # this is a hack for now..
    # TODO: modify the markdown definitions.jl script to ensure vertex/edge props match the schema
    for v in vertices(G)

        v_hash = Graphs.gen_vertex_hash(get_prop(G, v, :name), "missing")
        set_indexing_prop!(G, v, :v_hash, "$v_hash")

        set_props!(G, v,
            Dict(:v_name=>get_prop(G, v, :name),
                 :v_type=>"missing"))
    end

    for e in LightGraphs.edges(G)

        src_vhash = Graphs.gen_vertex_hash(get_prop(G, e.src, :name), "missing")
        dst_vhash = Graphs.gen_vertex_hash(get_prop(G, e.dst, :name), "missing")

        set_props!(G, Edge(e.src, e.dst), Dict(:e_rel=>"verb",
                                                :e_desc=>"Verb",
                                                :e_value=>"is defined as",
                                                :weight=>1))
    end

    return G
end

function create_kg_from_markdown_edges(path, extraction_rule="definition")

    G = Extraction.definitiongraph(path, Extraction.sequentialnamer())

#     vertex_df = DataFrame(vertex=String[],
#                           v_hash=String[],
#                           v_name=String[],
#                           v_type=String[],
#                           v_text=String[])

#     edges_df = DataFrame(src_vhash = String[],
#                         src_name = String[],
#                         src_vtype = String[],
#                         dst_vhash = String[],
#                         dst_name = String[],
#                         dst_vtype = String[],
#                         edge_relation = String[],
#                         edge_description = String[],
#                         value = String[])

    # this is a hack for now..
    # TODO: modify the markdown definitions.jl script to ensure vertex/edge props match the schema
    for v in vertices(G)

        v_hash = Graphs.gen_vertex_hash(get_prop(G, v, :name), "concept")
        set_indexing_prop!(G, v, :v_hash, "$v_hash")

        set_props!(G, v,
            Dict(:v_name=>get_prop(G, v, :name),
                 :v_type=>"concept",
                 :v_text=>length(get_prop(G, v, :text))==0 ? "no_text" : get_prop(G, v, :text)))

#         push!(vertex_df, (vertex="$v", v_hash="$v_hash", v_name=get_prop(G, v, :name), v_type="concept",  v_text=length(get_prop(G, v, :text))==0 ? "no_text" : get_prop(G, v, :text)))

    end

    for e in LightGraphs.edges(G)

        src_vhash = Graphs.gen_vertex_hash(get_prop(G, e.src, :name), "concept")
        dst_vhash = Graphs.gen_vertex_hash(get_prop(G, e.dst, :name), "concept")

        set_props!(G, Edge(e.src, e.dst), Dict(:e_rel=>"verb",
                                                :e_desc=>"Verb",
                                                :e_value=>"is defined as",
                                                :weight=>1))

#         push!(edges_df, (src_vhash="$src_vhash", src_name = get_prop(G, e.src, :name), src_vtype="concept",
#                 dst_vhash="$dst_vhash", dst_name = get_prop(G, e.dst, :name), dst_vtype = "concept",
#                 edge_relation= "verb", edge_description="Verb", value="is defined as"))
    end

    # for debugging
    #CSV.write("../examples/epicookbook/data/markdown_vertices.csv",vertex_df)
    #CSV.write("../examples/epicookbook/data/markdown_edges.csv",edges_df)
    return G
end

"""    assign_vertex_colors_by_key(G::MetaDiGraph, color_by::Symbol)

Takes as input an existing MetaDiGraph, and a group_by, which is assumed to correspond to an existing vertex prop.
Groups the vertices in G by the group_by field, computes the number of unique colors needed, and generates a hash table, with key equal to the vertex hash, and value equal to the assigned color.

see also: [`assign_edge_styles_by_key`](@ref), [1write_graph_to_dot_file1](@ref)
"""
function assign_vertex_colors_by_key(G::MetaDiGraph, group_by::Symbol)

    # element 1 = white; element 2 = black; for sake of making graph easier to read, add +2 buffer and start indexing colors at 3
    cols = distinguishable_colors(length(unique(get_prop(G, v, group_by) for v in vertices(G)))+2, [RGB(1,1,1)])[3:end]

    color_type_lookup = Dict()

    for (i, v_type) in enumerate(unique([get_prop(G, v, group_by) for v in vertices(G)]))
        color_type_lookup[v_type] = "#$(hex(cols[i]))"
    end

    vertex_color_lookup = Dict()

    for v in vertices(G)
        vertex_color_lookup[get_prop(G, v, :v_hash)] = color_type_lookup[get_prop(G, v,group_by)]
    end

    return vertex_color_lookup

end

"""    assign_edge_styles_by_key(G::MetaDiGraph, group_by::Symbol)

Takes as input an existing MetaDiGraph, and a group_by field, which is assumed to correspond to an existing edge prop.
Groups the directed edges in G by the group_by field, computes the number of unique edge styles needed, and generates a hash table, with key equal to the edge id, and value equal to the assigned (line) style.

see also: [`assign_edge_styles_by_key`](@ref), [1write_graph_to_dot_file1](@ref)
"""
function assign_edge_style_by_key(G::MetaDiGraph, groupy_by::Symbol)
    # TODO; need to figure out how to generate arbitrary styles, or put a cap on number based on schema
end

# TODO: fix syntax and puncutuation errors so the graph can be loaded from a dot file
function write_graph_to_dot_file(G::MetaDiGraph, output_path::String, graph_name::String, v_color_lookup)

    head = "digraph " * graph_name * " {"

     open(output_path, "w") do io

        println(io, head)

        for v in vertices(G)
            v_color = v_color_lookup[get_prop(G, v, :v_hash)]
            vname = preprocess_vertex_name(get_prop(G, v, :v_name))
            println(io, string("$v" *  " [color=" * "\"" * "$v_color" *  "\"" * " ," * " label=" * "\"" * "$vname" * "\"" * "];"))

        end

        for e in LightGraphs.edges(G)

            src_vname = preprocess_vertex_name(get_prop(G, e.src, :v_name))
            dst_vname = preprocess_vertex_name(get_prop(G, e.dst, :v_name))
            e_rel = get_prop(G, e, :e_rel)
            #e_value = get_prop(G, e, :e_value)

            println(io, string("$(e.src)" * " -> " * "$(e.dst)" * " [label=" * "\"" * "$(e_rel)" * "\""  * "];"))

        end

        println(io, "}")

    end

    # Example dot file
    #         digraph graphname {
    #          // The label attribute can be used to change the label of a node
    #          a [label="Foo"];
    #          // Here, the node shape is changed.
    #          b [shape=box];
    #          // These edges both have different line properties
    #          a -- b -- c [color=blue];
    #          b -- d [style=dotted];
    #      }
    end

end

# command-line usage
# julia -i --project ../bin/extract.jl ../examples/epicookbook/src/ScalingModel.jl ../examples/epicookbook/src/SEIRmodel.jl

using SemanticModels.Parsers
using SemanticModels.Graphs
using SemanticModels.Extraction
@debug "Done Loading Package"
using DataFrames
using MetaGraphs
using LightGraphs

if length(ARGS) < 1
    error("You must provide a file path to a .jl file", args=ARGS)
end

mdown_path = "../examples/epicookbook/epireceipes_Automates_GTRI_ASKE_2rules_output/json"

function markdowngraph(mdown_path)
    try
        return Edges.create_kg_from_markdown_edges(mdown_path, "definition")
    catch
        @warn "Failed to read markdown" path=mdown_path
        return MetaDiGraph()
    end
end

G_markdown = markdowngraph(mdown_path)

@info("Graph created from markdown has v vertices and e edges.", v=nv(G_markdown), e=ne(G_markdown))

num_files = length(ARGS)
global G_temp = MetaDiGraph()

for i in 1:num_files

    output_path = "../examples/epicookbook/data/edges_from_code_$i.jl"

    path = ARGS[i]
    @info "Parsing julia script" file=path
    expr = parsefile(path)
    mc = defs(expr.args[3].args)
    @info "script uses modules" modules=mc.modc
    @info "script defines functions" funcs=mc.fc.defs
    @info "script defines glvariables" funcs=mc.vc
    subdefs = recurse(mc)
    @info "local scope definitions" subdefs=subdefs

    for func in subdefs
        funcname = func[1]
        mc = func[2]
        @info "$funcname uses modules" modules=mc.modc
        @info "$funcname defines functions" funcs=mc.fc.defs
        @info "$funcname defines variables" funcs=mc.vc
    end

    edg = Edges.edges(mc, subdefs, expr.args[2])
    @info("Edges found", path=path)
    #for e in edg
        #println(e)
    #end

    code_edges_df = Edges.format_edges_dataframe(edg, output_path)

    if i == 1
        # We only need to ingest the markdown info once.
        G_code = Edges.create_kg_from_code_edges(output_path, G_markdown)
    else
        G_code = Edges.create_kg_from_code_edges(output_path, G_temp)
    end

    @info("Code graph $i has v vertices and e edges.", v=nv(G_code), e=ne(G_code))

    global vcolors = Edges.assign_vertex_colors_by_key(G_code, :v_type)
    global G_temp = copy(G_code)

end

@info("All markdown and code files have been parsed; writing final knowledge graph to dot file")
dot_file_path = "../examples/epicookbook/data/dot_file_ex1.dot"
Edges.write_graph_to_dot_file(G_temp, dot_file_path, "G_code_and_markdown", vcolors)

# Generate svg file
run(`dot -Tsvg -O $dot_file_path`)


text:
using ITensorGaussianMPS
using ITensors
using LinearAlgebra
using Test

function expect_compat(psi::MPS, ops::AbstractString...; kwargs...)
  if ITensors.version() >= v"0.2"
    return expect(psi, ops...; kwargs...)
  end
  psi = copy(psi)
  N = length(psi)
  ElT = real(promote_itensor_eltype(psi))
  Nops = length(ops)
  s = siteinds(psi)
  site_range::UnitRange{Int} = get(kwargs, :site_range, 1:N)
  Ns = length(site_range)
  start_site = first(site_range)
  offset = start_site - 1
  orthogonalize!(psi, start_site)
  psi[start_site] ./= norm(psi[start_site])
  ex = ntuple(n -> zeros(ElT, Ns), Nops)
  for j in site_range
    orthogonalize!(psi, j)
    for n in 1:Nops
      ex[n][j - offset] = real(scalar(psi[j] * op(ops[n], s[j]) * dag(prime(psi[j], s[j]))))
    end
  end
  return Nops == 1 ? ex[1] : ex
end

@testset "Electron" begin
  # Half filling
  N = 40
  Nf_up = N ÷ 2
  Nf_dn = N ÷ 2
  Nf = Nf_up + Nf_dn

  # Maximum MPS link dimension
  _maxlinkdim = 200

  # DMRG cutoff
  _cutoff = 1e-8

  # Hopping
  t = 1.0

  # Electron-electron on-site interaction
  U = 1.0

  # Make the free fermion Hamiltonian for the up spins
  os_up = OpSum()
  for n in 1:(N - 1)
    os_up .+= -t, "Cdagup", n, "Cup", n + 1
    os_up .+= -t, "Cdagup", n + 1, "Cup", n
  end

  # Make the free fermion Hamiltonian for the down spins
  os_dn = OpSum()
  for n in 1:(N - 1)
    os_dn .+= -t, "Cdagdn", n, "Cdn", n + 1
    os_dn .+= -t, "Cdagdn", n + 1, "Cdn", n
  end

  # Hopping Hamiltonians for the up and down spins
  h_up = hopping_hamiltonian(os_up)
  h_dn = hopping_hamiltonian(os_dn)

  # Get the Slater determinant
  Φ_up = slater_determinant_matrix(h_up, Nf_up)
  Φ_dn = slater_determinant_matrix(h_dn, Nf_dn)

  # Create an MPS from the slater determinants.
  s = siteinds("Electron", N; conserve_qns=true)
  ψ0 = slater_determinant_to_mps(
    s, Φ_up, Φ_dn; eigval_cutoff=1e-4, cutoff=_cutoff, maxdim=_maxlinkdim
  )
  @test maxlinkdim(ψ0) ≤ _maxlinkdim

  # The total non-interacting part of the Hamiltonian
  os_noninteracting = OpSum()
  for n in 1:(N - 1)
    os_noninteracting .+= -t, "Cdagup", n, "Cup", n + 1
    os_noninteracting .+= -t, "Cdagdn", n, "Cdn", n + 1
    os_noninteracting .+= -t, "Cdagup", n + 1, "Cup", n
    os_noninteracting .+= -t, "Cdagdn", n + 1, "Cdn", n
  end

  H_noninteracting = MPO(os_noninteracting, s)
  @test tr(Φ_up' * h_up * Φ_up) + tr(Φ_dn' * h_dn * Φ_dn) ≈ inner(ψ0, H_noninteracting, ψ0) rtol =
    1e-3

  # The total interacting Hamiltonian
  os_interacting = OpSum()
  for n in 1:(N - 1)
    os_interacting .+= -t, "Cdagup", n, "Cup", n + 1
    os_interacting .+= -t, "Cdagdn", n, "Cdn", n + 1
    os_interacting .+= -t, "Cdagup", n + 1, "Cup", n
    os_interacting .+= -t, "Cdagdn", n + 1, "Cdn", n
  end
  for n in 1:N
    os_interacting .+= U, "Nupdn", n
  end
  H = MPO(os_interacting, s)

  # Random starting state
  ψr = randomMPS(s, n -> n ≤ Nf ? (isodd(n) ? "↑" : "↓") : "0")

  @test flux(ψr) == QN(("Nf", Nf, -1), ("Sz", 0))
  @test flux(ψ0) == QN(("Nf", Nf, -1), ("Sz", 0))

  @test inner(ψ0, H, ψ0) < inner(ψr, H, ψr)

  sweeps = Sweeps(3)
  setmaxdim!(sweeps, 10, 20, _maxlinkdim)
  setcutoff!(sweeps, _cutoff)
  setnoise!(sweeps, 1e-5, 1e-6, 1e-7, 0.0)
  er, _ = dmrg(H, ψr, sweeps; outputlevel=0)

  sweeps = Sweeps(3)
  setmaxdim!(sweeps, _maxlinkdim)
  setcutoff!(sweeps, _cutoff)
  setnoise!(sweeps, 1e-5, 1e-6, 1e-7, 0.0)
  e0, _ = dmrg(H, ψ0, sweeps; outputlevel=0)

  @test e0 > inner(ψ0, H_noninteracting, ψ0)
  @test e0 < er
end

@testset "Regression test for bug away from half filling" begin
  N = 3
  t = 1.0
  os_up = OpSum()
  for n in 1:(N - 1)
    os_up .+= -t, "Cdagup", n, "Cup", n + 1
    os_up .+= -t, "Cdagup", n + 1, "Cup", n
  end
  os_dn = OpSum()
  for n in 1:(N - 1)
    os_dn .+= -t, "Cdagdn", n, "Cdn", n + 1
    os_dn .+= -t, "Cdagdn", n + 1, "Cdn", n
  end
  h_up = hopping_hamiltonian(os_up)
  h_dn = hopping_hamiltonian(os_dn)
  s = siteinds("Electron", N; conserve_qns=true)
  H = MPO(os_up + os_dn, s)
  Nf_up, Nf_dn = 1, 0
  Φ_up = slater_determinant_matrix(h_up, Nf_up)
  Φ_dn = slater_determinant_matrix(h_dn, Nf_dn)
  ψ = slater_determinant_to_mps(s, Φ_up, Φ_dn; eigval_cutoff=0.0, cutoff=0.0)
  @test inner(ψ, H, ψ) ≈ tr(Φ_up'h_up * Φ_up) + tr(Φ_dn'h_dn * Φ_dn)
  @test maxlinkdim(ψ) == 2
  @test flux(ψ) == QN(("Nf", 1, -1), ("Sz", 1))
  ns_up = expect_compat(ψ, "Nup")
  ns_dn = expect_compat(ψ, "Ndn")
  @test ns_up ≈ diag(Φ_up * Φ_up')
  @test ns_dn ≈ diag(Φ_dn * Φ_dn')
  @test sum(ns_up) ≈ Nf_up
  @test sum(ns_dn) ≈ Nf_dn
end


text:
using ApproxFun, Test
    import ApproxFun: testfunctional, testbandedoperator

@testset "Fractional" begin
    @testset "Jupyer example" begin
        S = Legendre() ⊕ JacobiWeight(0.5,0.,Ultraspherical(1))
        Q½ = LeftIntegral(S,0.5)

        y = (I+Q½)\1
        @test values(y)[1] ≈ 0.33627096683893143

        S = Legendre()⊕JacobiWeight(0.5,0.,Ultraspherical(1))
        testfunctional(rdirichlet(S))
        B=rdirichlet(S)
        D=LeftDerivative(1.5) : S
        testbandedoperator(D)
    end

    ## Avazzadev et al

    # Example 1

    x=Fun(0..1)
    Q=gamma(0.5)*LeftIntegral(0.5)
    f=(2/105*sqrt(x)*(105-56x^2+48x^3))
    u=Q\f
    @test norm(u-(x^3-x^2+1))<100eps()

    # Example 2

    x=Fun(0..1)
    Q=gamma(0.5)*LeftIntegral(0.5)
    u=Q\(exp(x)-1)
    @test norm(u-exp(x)*erf(sqrt(x))/sqrt(π)) < 100eps() # 5.0036177384681187e-14

    # Example 3
    x=Fun(0..1)
    Q=gamma(1/5)*LeftIntegral(1/5)
    u=Q\(x+1)
    @test norm(u-(1+1.25x)*sin(0.8π)/(π*x^(1/5))) < 10eps()

    # Example 4
    x=Fun(0..1)
    Q=gamma(1-1/3)*LeftIntegral(1-1/3)
    u=Q\x^(7/6)
    @test norm(u-7*gamma(1/6)/(18*sqrt(π)*gamma(2/3))*sqrt(x)) < 100eps()

    # Example 5

    d=Interval(0,1)
    x=Fun(d)
    f=x+4/3*x^(3/2)
    S=Legendre(d)⊕JacobiWeight(.5,0.,Jacobi(.5,.5,d))
    Q=gamma(.5)*LeftIntegral(S,.5)

    @test sum(f/sqrt(1-x)) ≈ last(Q*f)

    L=I+Q

    @test last(L.ops[2]*f) ≈ last(Q*f)

    @test last(L*f) ≈ last(f)+last(Q*f)

    u=L\f
    @test norm(u-x)  < 10eps()

    # Example 6

    d=Interval(0,1)
    x=Fun(d)
    f=x^2+16/15*x^(5/2)
    S=Legendre(d)⊕JacobiWeight(.5,0.,Jacobi(.5,.5,d))
    Q=gamma(.5)*LeftIntegral(S,.5)
    L=I+Q
    u=L\f
    @test norm(u-x^2) < 10eps()

    # Example 7

    d=Interval(0.,1.)
    x=Fun(d)
    f=2sqrt(x)
    S=Legendre(d)⊕JacobiWeight(.5,0.,Jacobi(.5,.5,d))
    Q=gamma(.5)*LeftIntegral(S,.5)
    L=I+Q
    u=L\f

    @test norm(1-exp(π*x)*erfc(sqrt(π*x))-u) < 100eps()

    # Example 8

    d=Interval(0.,1.)
    x=Fun(d)
    f=1/(x+1)+2*Fun(x->asinh(sqrt(x))/sqrt(1+x),JacobiWeight(.5,0.,d))
    S=Legendre(d)⊕JacobiWeight(.5,0.,Jacobi(.5,.5,d))
    Q=gamma(.5)*LeftIntegral(S,.5)
    L=I+Q
    u=L\f
    norm((u-1/(x+1)).coefficients) < 1000eps()   # 1.2011889731154679e-14

    ## Test for bug

    QL = LeftIntegral(0.5)	: Legendre()					→	JacobiWeight(0.5,0.,Ultraspherical(1))
    QU = LeftIntegral(0.5)	: JacobiWeight(0.5,0.,Ultraspherical(1))	→	Legendre()

    λ=0.25
    L=[λ*I QU; QL λ*I]
    @test L[2,5] ≈ 0.

    ## Paper examples

    S=Legendre()⊕JacobiWeight(0.5,0.,Ultraspherical(1))
    Q½=LeftIntegral(S,0.5)

    y=(I+Q½)\1

    x=Fun()
    @test norm(exp(1+x)*erfc(sqrt(1+x))-y) < 100eps()

    S=Legendre()⊕JacobiWeight(0.5,0.,Ultraspherical(1))
    x=Fun()
    Q½=LeftIntegral(S,0.5)

    y=(I+exp(-(1+x)/2)*Q½[exp((1+x)/2)])\exp(-(1+x)/2)

    @test norm(y-exp((1+x)/2)*erfc(sqrt(1+x))) < 100eps()
end