include("../citydb.jl")

using RBT
setdb(citydb)

@query(6*(3+4))

@query(department.name)

@query(department.employee.name)

@query(employee.name)

@query(employee.department.name)

@query(employee.salary)

@query(employee)

@query(department.employee)

@query(count(department))

@query(department.count(employee))

@query(count(department.employee))

@query(count(employee))

@query(max(employee.salary))

@query(max(department.count(employee)))

@query(department:select(name,count(employee)))

@query(select(department,name,count(employee)))

@query(
    department
    :select(
        name,
        count(employee),
        max(employee.salary)))

@query(
    employee
    :filter(salary>200000)
    :select(name,surname,position,salary))

@query(
    employee
    :select(name,surname,position,salary)
    :filter(salary>200000))

@query(
    employee
    :filter((salary>100000)&(salary<=200000))
    :count)

@query(
    department
    :filter(count(employee)>1000)
    .name)

@query(
    count(
        department
        :filter(count(employee)>1000)))

@query(
    department
    :select(
        name,
        count(employee:filter(salary>100000))))

@query(
    department
    :filter(count(employee)<1000)
    :select(
        name,
        employee
            :filter(salary>125000)
            :select(name,surname,position)))

@query(department.name:sort)

@query(employee:sort(salary))

@query(employee:sort(salary:desc))

@query(
    employee
    :sort(
        salary:desc,
        surname:asc,
        name:asc))

@query(
    department
    :select(name, size => count(employee))
    :filter(size>1000)
    :sort(size:desc))

@query(
    department
    :define(size => count(employee))
    :select(name, size)
    :filter(size>1000)
    :sort(size:desc))

@query(first(employee))

@query(first(employee).name)

@query(
    department
    :select(name, size => count(employee))
    :sort(size:desc)
    :first)

@query(
    department
    :select(name, size => count(employee))
    :first(size))

@query(employee:last)

@query(
    department
    :select(name, size => count(employee))
    :sort(size)
    :last)

@query(
    department
    :select(name, size => count(employee))
    :last(size:desc))

@query(employee:take(5))

@query(employee:skip(10):take(5))

@query(employee:skip(-10))

@query(employee:take(count(employee)/2))

@query(department:reverse)

@query(department:select(id,name))

@query(department:get(5))

@query(department:get(-1))

@query(
    department[5]
    :select(id, name, count(employee)))

@query(department[5].employee)

@query(
    employee
    :filter(salary>managed_by.salary)
    :select(name, surname, position, managed_by, salary-managed_by.salary))

@query(
    department[26].employee
    :select(
        name,
        surname,
        position,
        connect(managed_by),
        count(connect(manages))))

@query(
    department[26].employee
    :sort_connect(managed_by)
    :select(
        depth(managed_by),
        name,
        surname,
        position))

@query(employee.position:unique)

@query(
    department
    :select(
        name,
        count(unique(employee.position)),
        count(employee)))

@query(employee:group(position))

@query(
    employee
    :group(position)
    :select(position, size => count(employee))
    :sort(size:desc))

@query(
    employee
    :group(position)
    :define(department => unique(employee.department))
    :filter(count(department)>=5)
    :select(position, department)
    :sort(count(department):desc))

@query(
    employee
    :group(name)
    :select(name, size => count(employee))
    :sort(size:desc))

@query(
    employee
    :group(name)
    :filter(count(employee)>=10)
    :select(name, max_salary => max(employee.salary))
    :sort(max_salary:desc))

@query(
    employee
    :group(department, salary_bracket => salary/10000*10000 :desc)
    :select(department, salary_bracket, salary_bracket+9999, count(employee)))

@query(
    employee
    :group_cube(department, salary_bracket => salary/10000*10000 :desc)
    :select(department, salary_bracket, salary_bracket+9999, count(employee)))

@query(
    employee
    :group_cube(
        department,
        salary_bracket => salary/10000*10000 :desc)
    :select(
        department,
        low => salary_bracket,
        high => salary_bracket+9999,
        size => count(employee))
    :dataframe)

@query(
    employee
    :partition(department:take(3))
    :select(department.name, count(unique(employee.position)), count(employee), max(employee.salary)))

@query(
    employee
    :partition_cube(department:take(3))
    :select(
        department.name,
        num_pos => count(unique(employee.position)),
        num_empl => count(employee),
        max_salary => max(employee.salary),
        pop_position => employee:group(position):first(count(employee)).position)
    :dataframe)

@query(range(0, 60000, max(employee.salary)))

@query(
    employee
    :define(salary_bracket => salary/60000*60000)
    :partition(
        department:take(3),
        name => ["ANTHONY", "BRIAN"],
        salary_bracket => range(0, 60000, max(employee.salary)))
    :select(
        dept => department.name,
        name,
        low => salary_bracket,
        high => salary_bracket+59999,
        pop_position => employee:group(position):first(count(employee)).position,
        num_pos => count(unique(employee.position)),
        num_empl => count(employee))
    :dataframe)

@query(department:json)

@query(
    department
    :select(
        name,
        size => count(employee),
        head => employee:first(salary))
    :json)

@query(
    department
    :json(
        name,
        size => count(employee),
        head => employee:first(salary)))

@query(employee:dataframe)

@query(
    department
    :select(
        name,
        size => count(employee),
        max_salary => max(employee.salary))
    :dataframe)

@query(
    department
    :dataframe(
        name,
        size => count(employee),
        max_salary => max(employee.salary)))

@query(
    mix(a => range(2,1,10),
        b => range(2,1,10),
        c => range(2,1,10))
    :filter((a <= b) & (b <= c))
    :select(a, b, c, (a*b)*c))

@query(
    mix(department, department)
    :filter((left.id != right.id) & (left.count(employee)/10 == right.count(employee)/10))
    :select(left.name, left.count(employee), right.name, right.count(employee)))

@query(
    pack(
        a => range(1,1,5),
        z => range(95,1,99)))

@query(pack(employee, department))

@query(pack(employee, department).employee)

@query(pack(employee, department).department)

@query(pack(employee, department):select(employee.position, department.name))

@query(
    employee
    :take(500)
    :filter(salary > max(unlink(employee).salary)/2))

@query(
    employee[10]
    :define(namesake => link((left.id!=right.id)&(left.name==right.name), employee))
    :select(name, count(namesake), namesake:take(3)))

@query(
    employee:filter((position==POSITION) & (name==NAME)),
    POSITION="POLICE OFFICER",
    NAME="CHARLES")

@query(
    department
    :filter(count(employee)>SIZE)
    :select(name, count(employee)-SIZE),
    SIZE=1000)

@query(
    employee:filter(department.name in DEPTS),
    DEPTS=["POLICE", "FIRE"])

@query(
    employee
    :filter(salary==MAX_SALARY)
    :given(MAX_SALARY => max(employee.salary)))

@query(
    department
    :select(
        name,
        employee
            :filter(salary==MAX_SALARY)
            :given(MAX_SALARY => max(employee.salary))))

@query(
    department
    :dataframe(
        name,
        past_names => before.name))

@query(
    department
    :dataframe(
        name,
        next_name => first(after).name))

@query(
    department
    :select(1+count(before), name))

@query(
    department
    :define(size => count(employee))
    :dataframe(
        name,
        size,
        total => sum(and_before.size)))

@query(
    department
    :define(size => count(employee))
    :filter(size == max(and_around.size))
    :select(name, size))

@query(
    employee
    :filter(department.name == DEPT)
    :dataframe(
        name,
        surname,
        position,
        salary,
        salary_diff => max(and_around(position).salary)-salary),
    DEPT="TREASURER")

@query(
    department
    :select(
        name,
        employee
            :filter(salary==max(and_around.salary))
            :frame))

q1 = RBT.@prepare(department.employee.name)

q2 = RBT.@prepare(count(department))

q3 = RBT.@prepare(department:select(name,count(employee)))

q4 = RBT.@prepare(count(employee:filter((salary>100000)&(salary<200000))))

q5 = RBT.@prepare(X*(Y*Z), X=Int, Y=Nullable{Int}, Z=Vector{Int})

q6 = RBT.@prepare(employee:filter((name == NAME) & (salary > MIN_SALARY)), NAME=UTF8String, MIN_SALARY=Int)

RBT.params(q1)

RBT.params(q5)

RBT.params(q6)

q1()

q2()

q3()

q4()

q5(X=5, Y=Nullable(4), Z=[3,2,1])

q6(NAME="CHARLES", MIN_SALARY=100000)