! Copyright (c) 2021 Approximatrix, LLC <support@approximatrix.com>
!
! Permission is hereby granted, free of charge, to any person obtaining a copy
! of this software and associated documentation files (the "Software"), to deal
! in the Software without restriction, including without limitation the rights
! to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
! copies of the Software, and to permit persons to whom the Software is
! furnished to do so, subject to the following conditions:
!
! The above copyright notice and this permission notice shall be included in
! all copies or substantial portions of the Software.
!
! The Software shall be used for Good, not Evil.
!
! THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
! IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
! FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
! AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
! LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
! OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
! SOFTWARE.

module captain_db
use sqlite
implicit none
    
    integer, parameter::PLAYER_NAME_LENGTH = 128
    integer, parameter::FILENAME_NAME_LENGTH = 1024
    
    integer, parameter::JOB_STATUS_SUCCESS = 1
    integer, parameter::JOB_STATUS_FAILURE = 2
    integer, parameter::JOB_STATUS_WORKING = 3
    integer, parameter::JOB_STATUS_PENDING = 0
    
    integer, parameter::PLAYER_STATUS_BUSY    = JOB_STATUS_WORKING
    integer, parameter::PLAYER_STATUS_IDLE    = 100
    integer, parameter::PLAYER_STATUS_OFFLINE = 101
    
    integer, parameter::SCHEDULE_DAILY = 0
    
    character(1024)::database_file
    type(c_ptr)::db
    
    type :: work_pair
    
        integer::instruction
        integer::player
    
    end type
    
    type, extends(work_pair) :: job
    
        integer::id
        integer::status
        character(32)::time
        
    end type
    
    type, extends(work_pair) :: group_entry
    
        integer::group
        
    end type
    
    type :: task
    
        integer::job
        integer::number
        integer::status
    
    end type
    
    interface is_player_busy
        module procedure is_player_busy_by_id
        module procedure is_player_busy_by_name
    end interface is_player_busy
    
contains

    subroutine initialize_db(filename)
    use logging
    implicit none
    
        character(*), intent(in)::filename
        logical::exists
        
        inquire(file=filename, exist=exists)
        if(.not. exists) then
            call write_log("Database does not exist, exiting")
            call shutdown()
            stop
        end if
        
        if(sqlite3_open(filename, db) == SQLITE_OK) then
            database_file = filename
        else
            Print *, "ERROR: Could not open db"
            stop
        end if
        call destroy_old_sessions_db()
    end subroutine initialize_db
    
    subroutine shutdown_db()
    implicit none
    
        integer::i
    
        i = sqlite3_close(db)
        
    end subroutine shutdown_db
    
    subroutine add_player_db(name, token)
    implicit none
    
        character(*), intent(in)::name
        character(*), intent(in), optional::token
        type(sqlite3_stmt)::stmt
        
        character(64)::my_token
        
        if(present(token)) then
            my_token = token
        else
            my_token = "None"
        end if

        if(stmt%prepare(db, "INSERT INTO players(name, token) VALUES(?, ?)") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK .and. stmt%bind_text(2, my_token) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine add_player_db
    
    subroutine update_player_token_db(name, token)
    implicit none
    
        character(*), intent(in)::name
        character(*), intent(in)::token
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "UPDATE players SET token=? WHERE name=?") == SQLITE_OK) then
            if(stmt%bind_text(2, name) == SQLITE_OK .and. stmt%bind_text(1, token) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine update_player_token_db
    
    subroutine get_player_token_db(name, token)
    implicit none
    
        character(*), intent(in)::name
        character(*), intent(out)::token
        type(sqlite3_stmt)::stmt
        
        token = " "
        
        if(stmt%prepare(db, "SELECT token FROM  players WHERE name=?") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK ) then
                if(stmt%step() == SQLITE_ROW) then
                    call stmt%column_text(0, token)
                end if
            end if
        end if
        call stmt%finalize()
    
    end subroutine get_player_token_db
    
    function player_has_token_db(name)
    implicit none
    
        character(*), intent(in)::name
        logical::player_has_token_db
        type(sqlite3_stmt)::stmt
        
        character(128)::tk
        
        player_has_token_db = .false.
        
        if(stmt%prepare(db, "SELECT token FROM  players WHERE name=?") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK ) then
                if(stmt%step() == SQLITE_ROW) then
                    call stmt%column_text(0, tk)
                    player_has_token_db = (trim(tk) /= "None")
                end if
            end if
        end if
        call stmt%finalize()
        
        
    end function player_has_token_db
        
    
    subroutine remove_player_db(name)
    implicit none
    
        character(*), intent(in)::name
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "DELETE FROM players WHERE name=?") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine remove_player_db
    
    function get_player_count()
    implicit none
        
        type(sqlite3_stmt)::stmt
        integer::get_player_count
        
        get_player_count = 0
        if(stmt%prepare(db, "SELECT COUNT(*) FROM players") == SQLITE_OK) then
            if(stmt%step() == SQLITE_ROW) then
                get_player_count = stmt%column_int(0)
            end if
        end if
        call stmt%finalize()
        
    end function get_player_count
    
    function get_player_names() result(res)
    implicit none
        
        type(sqlite3_stmt)::stmt
        character(len=PLAYER_NAME_LENGTH), dimension(:), pointer::res
        integer::i,n
        
        n = get_player_count()
        if(n > 0) then
            allocate(res(n))
            if(stmt%prepare(db, "SELECT name FROM players ORDER BY name") == SQLITE_OK) then
                i = 1
                do while(stmt%step() == SQLITE_ROW .and. i <= n) 
                    call stmt%column_text(0, res(i))
                    i = i + 1
                end do
            end if
            call stmt%finalize()
            
        end if
        
    end function get_player_names
    
    function get_instructions_count(player)
    implicit none
        
        type(sqlite3_stmt)::stmt
        integer, intent(in), optional::player
        integer::get_instructions_count
        
        get_instructions_count = 0
        
        if(present(player)) then
        
            if(stmt%prepare(db, "SELECT COUNT(*) FROM available WHERE player=?") == SQLITE_OK) then
                if(stmt%bind_int(1, player) == SQLITE_OK) then
                    if(stmt%step() == SQLITE_ROW) then
                        get_instructions_count = stmt%column_int(0)
                    end if
                end if
            end if
            call stmt%finalize()
            
        else
        
            if(stmt%prepare(db, "SELECT COUNT(*) FROM instructions") == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    get_instructions_count = stmt%column_int(0)
                end if
            end if
            call stmt%finalize()
            
        end if
        
    end function get_instructions_count
    
    function get_instruction_ids(player) result(res)
    implicit none
        
        type(sqlite3_stmt)::stmt
        integer, intent(in), optional::player
        integer, dimension(:), pointer::res
        integer::i,n
        
        res => null()
        
        if(present(player)) then
            n = get_instructions_count(player)
        else
            n = get_instructions_count()
        end if
        
        if(n > 0) then
            allocate(res(n))
            res = -1
        
            if(present(player)) then
        
                if(stmt%prepare(db, "SELECT instruction FROM available WHERE player=?") == SQLITE_OK) then
                    if(stmt%bind_int(1, player) == SQLITE_OK) then
                        i = 1
                        do while(stmt%step() == SQLITE_ROW .and. i <= n) 
                            res(i) = stmt%column_int(0)
                            i = i + 1
                        end do
                    end if
                end if
                call stmt%finalize()

            else
            
                if(stmt%prepare(db, "SELECT id, name FROM instructions ORDER BY name") == SQLITE_OK) then
                    i = 1
                    do while(stmt%step() == SQLITE_ROW .and. i <= n) 
                        res(i) = stmt%column_int(0)
                        i = i + 1
                    end do
                end if
                call stmt%finalize()
                
            end if
            
        end if
        
    end function get_instruction_ids
    
    function get_instruction_names() result(res)
    implicit none
        
        type(sqlite3_stmt)::stmt
        character(len=PLAYER_NAME_LENGTH), dimension(:), pointer::res
        integer::i,n
        
        n = get_instructions_count()
        if(n > 0) then
            allocate(res(n))
            if(stmt%prepare(db, "SELECT name FROM instructions ORDER BY name") == SQLITE_OK) then
                i = 1
                do while(stmt%step() == SQLITE_ROW .and. i <= n) 
                    call stmt%column_text(0, res(i))
                    i = i + 1
                end do
            end if
            call stmt%finalize()
            
        end if
        
    end function get_instruction_names
    
    function get_jobs_count()
    implicit none

        type(sqlite3_stmt)::stmt
        integer::get_jobs_count
        
        get_jobs_count = 0
        if(stmt%prepare(db, "SELECT COUNT(*) FROM jobs") == SQLITE_OK) then
            if(stmt%step() == SQLITE_ROW) then
                get_jobs_count = stmt%column_int(0)
            end if
        end if
        call stmt%finalize()
    
    end function get_jobs_count
    
    subroutine get_player_name(id, str)
    implicit none
    
        integer, intent(in)::id
        character(*), intent(out)::str
        
        type(sqlite3_stmt)::stmt

        str = " "

        if(stmt%prepare(db, "SELECT name FROM players WHERE id=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    call stmt%column_text(0,str)
                end if
            end if
        end if
        call stmt%finalize()
        
    end subroutine get_player_name
    
    function get_player_id(name)
    implicit none
    
        integer::get_player_id
        character(*), intent(in)::name
        
        type(sqlite3_stmt)::stmt

        get_player_id = -1

        if(stmt%prepare(db, "SELECT id FROM players WHERE name=?") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    get_player_id = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
        
    end function get_player_id
    
    subroutine get_instruction_name(id, str)
    implicit none
    
        integer, intent(in)::id
        character(*), intent(out)::str
        
        type(sqlite3_stmt)::stmt

        str = " "

        if(stmt%prepare(db, "SELECT name FROM instructions WHERE id=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    call stmt%column_text(0,str)
                end if
            end if
        end if
        call stmt%finalize()
        
    end subroutine get_instruction_name
    
    function get_instruction_id(name)
    implicit none
    
        integer::get_instruction_id
        character(*), intent(in)::name
        
        type(sqlite3_stmt)::stmt

        get_instruction_id = -1

        if(stmt%prepare(db, "SELECT id FROM instructions WHERE name=?") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    get_instruction_id = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
        
    end function get_instruction_id
    
    function get_jobs() result(jobs)
    implicit none
    
        type(job), dimension(:), pointer::jobs
        type(sqlite3_stmt)::stmt
        integer::i, n
        
        n = get_jobs_count()
        jobs => null()
        
        if(n > 0) then
            allocate(jobs(n))
            if(stmt%prepare(db, "SELECT id,instruction,player,status,time  FROM jobs ORDER BY id DESC") == SQLITE_OK) then
                i = 1
                do while(stmt%step() == SQLITE_ROW .and. i <= n) 
                    jobs(i)%id = stmt%column_int(0)
                    jobs(i)%instruction = stmt%column_int(1)
                    jobs(i)%player = stmt%column_int(2)
                    jobs(i)%status = stmt%column_int(3)
                    call stmt%column_text(4, jobs(i)%time)
                    i = i + 1
                end do
            end if
            call stmt%finalize()
        end if
    
    end function get_jobs
    
    function get_jobs_for_instruction(id) result(jobs)
    implicit none
    
        type(job), dimension(:), pointer::jobs
        integer, intent(in)::id
        type(sqlite3_stmt)::stmt
    
        integer::n, i
        
        jobs => null()
        n = 0
        if(stmt%prepare(db, "SELECT COUNT(*) FROM jobs WHERE instruction=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    n = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
        
        if(n > 0) then
            allocate(jobs(n))
            if(stmt%prepare(db, &
               "SELECT id,player,status,time FROM jobs WHERE instruction=? ORDER BY id DESC") &
                   == SQLITE_OK) then
                   
                if(stmt%bind_int(1, id) == SQLITE_OK) then
                    i = 1
                    do while(stmt%step() == SQLITE_ROW .and. i <= n) 
                        jobs(i)%id = stmt%column_int(0)
                        jobs(i)%instruction = id
                        jobs(i)%player = stmt%column_int(1)
                        jobs(i)%status = stmt%column_int(2)
                        if(stmt%column_type(3) == SQLITE_NULL) then
                            jobs(i)%time = "Not started"
                        else
                            call stmt%column_text(3, jobs(i)%time)
                        end if
                        i = i + 1
                    end do
                end if
                
            end if
            call stmt%finalize()
        
        end if
    
    end function get_jobs_for_instruction
    
    function is_player_busy_by_id(id)
    implicit none

        logical::is_player_busy_by_id
        integer, intent(in)::id
        type(sqlite3_stmt)::stmt
        
        is_player_busy_by_id = .false.

        if(stmt%prepare(db, "SELECT COUNT(*) FROM jobs WHERE player=? AND status=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK .and. stmt%bind_int(2, JOB_STATUS_WORKING) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    is_player_busy_by_id = (stmt%column_int(0) > 0)
                end if
            end if
        end if
        call stmt%finalize()
    
    end function is_player_busy_by_id
    
    function is_player_busy_by_name(name)
    implicit none

        logical::is_player_busy_by_name
        character(*), intent(in)::name
        integer::id
        
        is_player_busy_by_name = .false.
        id = get_player_id(name)
        is_player_busy_by_name = is_player_busy_by_id(id)
    
    end function is_player_busy_by_name
    
    function get_instruction_players(id) result(res)
    implicit none
    
        integer, dimension(:), pointer::res
        integer, intent(in)::id
        type(sqlite3_stmt)::stmt
        integer::n, i
        
        res => null()
        if(stmt%prepare(db, "SELECT COUNT(*) FROM available WHERE instruction=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    n = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
    
        if(n > 0) then
            allocate(res(n))
            res = -1
            if(stmt%prepare(db, "SELECT player FROM available WHERE instruction=?") == SQLITE_OK) then
                if(stmt%bind_int(1, id) == SQLITE_OK) then
                    i = 1
                    do while(stmt%step() == SQLITE_ROW .and. i <= n)
                        res(i) = stmt%column_int(0)
                        i = i + 1
                    end do
                end if
            end if
            call stmt%finalize()
        end if
    
    end function get_instruction_players
    
    subroutine add_player_for_instruction(instruction, player)
    implicit none
    
        integer, intent(in)::instruction, player
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "INSERT OR IGNORE INTO available(instruction, player) VALUES(?,?)") == SQLITE_OK) then
            if(stmt%bind_int(1, instruction) == SQLITE_OK .and. stmt%bind_int(2, player) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine add_player_for_instruction
    
    subroutine remove_player_for_instruction(instruction, player)
    implicit none
    
        integer, intent(in)::instruction, player
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "DELETE FROM available WHERE instruction=? AND player=?") == SQLITE_OK) then
            if(stmt%bind_int(1, instruction) == SQLITE_OK .and. stmt%bind_int(2, player) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine remove_player_for_instruction
    
    subroutine add_new_job(instruction, player)
    implicit none
    
        integer, intent(in)::instruction, player
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "INSERT INTO jobs(instruction, player, status) VALUES(?, ?, ?)") == SQLITE_OK) then
            if(stmt%bind_int(1, instruction) == SQLITE_OK .and. &
               stmt%bind_int(2, player) == SQLITE_OK .and. &
               stmt%bind_int(3, JOB_STATUS_PENDING) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
        
    end subroutine add_new_job
    
    function get_job(id)
    implicit none
    
        integer, intent(in)::id
        type(job)::get_job
        type(sqlite3_stmt)::stmt
        
        get_job%id = -1
        
        if(stmt%prepare(db, "SELECT instruction,player,status,time FROM jobs WHERE id=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    get_job%instruction = stmt%column_int(0)
                    get_job%player = stmt%column_int(1)
                    get_job%status = stmt%column_int(2)
                    if(stmt%column_type(3) == SQLITE_NULL) then
                        get_job%time = "Not started"
                    else
                        call stmt%column_text(3,get_job%time)
                    end if
                    get_job%id = id
                end if
            end if
        end if
        call stmt%finalize()
        
    end function get_job
    
    function get_job_tasks(job) result(res)
    implicit none
        
        integer, intent(in)::job
        type(task), dimension(:), pointer::res
        
        type(sqlite3_stmt)::stmt
        integer::n, i
        
        n = 0 
        if(stmt%prepare(db, "SELECT COUNT(*) FROM tasks WHERE job=?") == SQLITE_OK) then
            if(stmt%bind_int(1, job) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    n = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
        
        if(n == 0) then
            res => null()
            
        else
            allocate(res(n))
            res%number = 0
            res%status = JOB_STATUS_PENDING
            if(stmt%prepare(db, "SELECT task, status FROM tasks WHERE job=? ORDER BY task ASC") == SQLITE_OK) then
                if(stmt%bind_int(1, job) == SQLITE_OK) then
                    i = 1
                    do while(stmt%step() == SQLITE_ROW .and. i <= n) 
                        res(i)%number = stmt%column_int(0)
                        res(i)%status = stmt%column_int(1)
                        res(i)%job = job
                        i = i + 1
                    end do
                end if
            end if
            call stmt%finalize()
        end if
    
    end function get_job_tasks
    
    subroutine update_job_status(job_id, status)
    implicit none
    
        integer, intent(in)::job_id, status
        type(sqlite3_stmt)::stmt

        if(stmt%prepare(db, "UPDATE jobs SET status=? WHERE id=?") == SQLITE_OK) then
            if(stmt%bind_int(1, status) == SQLITE_OK .and. stmt%bind_int(2, job_id) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
        
        call update_job_time(job_id)
        
    end subroutine update_job_status
    
    subroutine update_job_time(job_id)
    implicit none
    
        integer, intent(in)::job_id
        character(10)::update_date
        character(8)::update_time
        integer, dimension(8)::values
        type(sqlite3_stmt)::stmt
        
        call date_and_time(values=values)
        
        write(update_date, '(I4,A1,I0.2,A1,I0.2)') values(1), "-", values(2), "-", values(3)
        write(update_time, '(I2,A1,I0.2,A1,I0.2)') values(5), ":", values(6), ":", values(7)
        if(stmt%prepare(db, "UPDATE jobs SET time=? WHERE id=?") == SQLITE_OK) then
            if(stmt%bind_text(1, update_date//" "//update_time) == SQLITE_OK .and. &
               stmt%bind_int(2, job_id) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
        
    end subroutine update_job_time
    
    subroutine insert_task(job_id, task_id)
    implicit none
    
        integer, intent(in)::job_id, task_id
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "INSERT INTO tasks(job, task, status) VALUES(?,?,?)") == SQLITE_OK) then
            if(stmt%bind_int(1, job_id) == SQLITE_OK .and. &
               stmt%bind_int(2, task_id) == SQLITE_OK .and. &
               stmt%bind_int(3, JOB_STATUS_WORKING) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
        
        call update_job_time(job_id)
        
    end subroutine insert_task
    
    subroutine update_task_status(job_id, task_id, status)
    implicit none
    
        integer, intent(in)::job_id, task_id, status
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "UPDATE tasks SET status=? WHERE job=? AND task=?") == SQLITE_OK) then
            if(stmt%bind_int(1, status) == SQLITE_OK .and. &
               stmt%bind_int(2, job_id) == SQLITE_OK .and. &
               stmt%bind_int(3, task_id) == SQLITE_OK) then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
        
        call update_job_time(job_id)
        
    end subroutine update_task_status
    
    subroutine update_task_type(job_id, task_id, task_type)
    implicit none
    
        integer, intent(in)::job_id, task_id
        character(len=*), intent(in)::task_type
        type(sqlite3_stmt)::stmt

        if(stmt%prepare(db, "UPDATE tasks SET type=? WHERE job=? AND task=?") == SQLITE_OK) then
            if(stmt%bind_text(1, task_type) == SQLITE_OK .and. &
               stmt%bind_int(2, job_id) == SQLITE_OK .and. &
               stmt%bind_int(3, task_id) == SQLITE_OK) &
            then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine update_task_type
    
    function get_task_type(job_id, task_id, task_type) result(ret)
    implicit none
    
        integer, intent(in)::job_id, task_id
        character(len=*), intent(out), optional::task_type
        logical::ret
        type(sqlite3_stmt)::stmt

        ret = .false.

        if(stmt%prepare(db, "SELECT type FROM tasks WHERE job=? AND task=?") == SQLITE_OK) then
            if(stmt%bind_int(1, job_id) == SQLITE_OK .and. &
               stmt%bind_int(2, task_id) == SQLITE_OK) &
            then
                if(stmt%step() == SQLITE_ROW) then
                    if(.not. stmt%column_is_null(0)) then
                        ret = .true.
                        if(present(task_type)) then
                            call stmt%column_text(0, task_type)
                        end if
                    end if
                end if
            end if
        end if
        call stmt%finalize()
        
    end function get_task_type
    
    function is_final_job_status(job_id)
    implicit none
    
        integer, intent(in)::job_id
        logical::is_final_job_status
        integer::i
        
        i = get_job_status(job_id)
        
        is_final_job_status = (i == JOB_STATUS_SUCCESS .or. i == JOB_STATUS_FAILURE)
        
    end function is_final_job_status
    
    function get_job_status(job_id) result(status)
    implicit none
    
        integer, intent(in)::job_id
        integer::status
        type(sqlite3_stmt)::stmt

        status = -1

        if(stmt%prepare(db, "SELECT status FROM jobs WHERE id=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_int(1, job_id) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    status = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
    
    end function get_job_status
    
    function get_job_instruction(job_id) result(status)
    implicit none
    
        integer, intent(in)::job_id
        integer::status
        type(sqlite3_stmt)::stmt

        status = -1

        if(stmt%prepare(db, "SELECT instruction FROM jobs WHERE id=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_int(1, job_id) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    status = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
    
    end function get_job_instruction
    
    function get_pending_job_for_player(player) result(job)
    implicit none
    
        integer, intent(in)::player
        integer::job
        type(sqlite3_stmt)::stmt

        job = -1

        if(stmt%prepare(db, "SELECT id FROM jobs WHERE player=? AND status=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_int(1, player) == SQLITE_OK .and. stmt%bind_int(2, JOB_STATUS_PENDING) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    job = stmt%column_int(0)
                end if
            end if
        end if
        call stmt%finalize()
    
    end function get_pending_job_for_player
    
    subroutine mark_working_jobs_as_failed(player)
    implicit none
    
        integer, intent(in)::player
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "UPDATE jobs SET status=? WHERE player=? AND status=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_int(1, JOB_STATUS_FAILURE) == SQLITE_OK .and. &
               stmt%bind_int(2, player) == SQLITE_OK .and. &
               stmt%bind_int(3, JOB_STATUS_WORKING) == SQLITE_OK) then
               
                call stmt%step_now()
            
            end if
        end if
        call stmt%finalize()
        
    end subroutine mark_working_jobs_as_failed
    
    subroutine scan_instructions_for_db()
    use config
    use utilities
    use logging
    implicit none
        
        character(len=2048)::cmdline
        
        call combine_paths(script_dir, "scan_instructions.sh", cmdline)
        cmdline = trim(cmdline)//" "//trim(database_file)//" "//trim(instructions_dir)//" 1>/dev/null 2>/dev/null"
        
        call write_log("Scan Command: "//trim(cmdline))
        call execute_command_line(trim(cmdline), wait=.true.)
        
    end subroutine scan_instructions_for_db
    
    subroutine add_group_db(name)
    implicit none
    
        character(*), intent(in)::name
        
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "INSERT INTO groups(name) VALUES(?)") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK) then
               
                call stmt%step_now()
            
            end if
        end if
        call stmt%finalize()
    
    end subroutine add_group_db
    
    subroutine get_group_name_db(id, name)
    implicit none
    
        integer, intent(in)::id
        character(*), intent(out)::name
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "SELECT name FROM groups WHERE id=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK) then
               
                if(stmt%step() == SQLITE_ROW) then
                    call stmt%column_text(0, name)
                end if
            
            end if
        end if
        call stmt%finalize()
        
    end subroutine get_group_name_db
    
    function get_group_id_db(name)
    implicit none
    
        character(*), intent(in)::name
        integer::get_group_id_db
        type(sqlite3_stmt)::stmt
        
        get_group_id_db = -1
        
        if(stmt%prepare(db, "SELECT id FROM groups WHERE name=?") == SQLITE_OK) then
            if(stmt%bind_text(1, name) == SQLITE_OK) then
               
                if(stmt%step() == SQLITE_ROW) then
                    get_group_id_db = stmt%column_int(0)
                end if
            
            end if
        end if
        call stmt%finalize()
        
    end function get_group_id_db
    
    subroutine delete_group_db(id)
    implicit none
    
        integer, intent(in)::id
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "DELETE FROM groups WHERE id=?") == SQLITE_OK) then
            if(stmt%bind_int(1, id) == SQLITE_OK) then
               
                call stmt%step_now()
            
            end if
        end if
        call stmt%finalize()
    
    end subroutine delete_group_db
    
    function get_group_count_db()
    implicit none
    
        integer::get_group_count_db
        type(sqlite3_stmt)::stmt
        
        get_group_count_db = 0
        
        if(stmt%prepare(db, "SELECT COUNT(*) FROM groups") == SQLITE_OK) then
            if(stmt%step() == SQLITE_ROW) then
                get_group_count_db = stmt%column_int(0)
            end if
        end if
        call stmt%finalize()
    
    end function get_group_count_db
    
    function get_groups_db() result(res)
    implicit none
    
        integer, dimension(:), pointer::res
        type(sqlite3_stmt)::stmt
        integer::i, n
        
        res => null()
        n = get_group_count_db()
        
        if(n > 0) then
            
            allocate(res(n))
            res = -1
            
            if(stmt%prepare(db, "SELECT id FROM groups") == SQLITE_OK) then
                i = 0
                do while(stmt%step() == SQLITE_ROW .AND. i <= n) 
                    i = i + 1
                    res(i) = stmt%column_int(0)
                end do
            end if
            call stmt%finalize()
        
        end if
    
    end function get_groups_db
    
    function is_entry_in_group_db(group, instruction, player)
    implicit none
    
        integer, intent(in)::group, instruction, player
        type(sqlite3_stmt)::stmt
        logical::is_entry_in_group_db
        
        is_entry_in_group_db = .FALSE.
        
        if(stmt%prepare(db, &
            "SELECT COUNT(*) FROM group_instructions WHERE group_id=? AND instruction=? AND player=?") == SQLITE_OK) &
        then
            if(stmt%bind_int(1, group) == SQLITE_OK .AND. &
               stmt%bind_int(2, instruction) == SQLITE_OK .AND. &
               stmt%bind_int(3, player) == SQLITE_OK) &
            then
               
                if(stmt%step() == SQLITE_ROW) then
                    is_entry_in_group_db = (stmt%column_int(0) > 0)
                end if
            
            end if
        end if
        call stmt%finalize()
        
    end function is_entry_in_group_db
    
    subroutine add_entry_to_group_db(group, instruction, player)
    implicit none
    
        integer, intent(in)::group, instruction, player
        type(sqlite3_stmt)::stmt
        
        if(instruction >= 0 .AND. player >= 0 .AND. group >= 0 .AND. &
           .NOT. is_entry_in_group_db(group, instruction, player)) &
        then
        
            if(stmt%prepare(db, "INSERT INTO group_instructions(group_id, instruction, player) VALUES(?, ?, ?)") == SQLITE_OK) then
                if(stmt%bind_int(1, group) == SQLITE_OK .AND. &
                   stmt%bind_int(2, instruction) == SQLITE_OK .AND. &
                   stmt%bind_int(3, player) == SQLITE_OK) &
                then
                   
                    call stmt%step_now()
                
                end if
            end if
            call stmt%finalize()
        
        end if
        
    end subroutine add_entry_to_group_db
    
    subroutine remove_entry_from_group_db(group, instruction, player)
    implicit none
    
        integer, intent(in)::group, instruction, player
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "DELETE FROM group_instructions WHERE group_id=? AND instruction=? AND player=?") == SQLITE_OK) then
            if(stmt%bind_int(1, group) == SQLITE_OK .AND. &
               stmt%bind_int(2, instruction) == SQLITE_OK .AND. &
               stmt%bind_int(3, player) == SQLITE_OK) &
            then
               
                call stmt%step_now()
            
            end if
        end if
        call stmt%finalize()
        
    end subroutine remove_entry_from_group_db
    
    function get_group_entries_count_db(group)
    implicit none
    
        integer, intent(in)::group
        integer::get_group_entries_count_db
        type(sqlite3_stmt)::stmt
    
        get_group_entries_count_db = 0
    
        if(stmt%prepare(db, "SELECT COUNT(*) FROM group_instructions WHERE group_id=?") == SQLITE_OK) then
            if(stmt%bind_int(1, group) == SQLITE_OK) then
               
                if(stmt%step() == SQLITE_ROW) then
                    get_group_entries_count_db = stmt%column_int(0)
                end if
            
            end if
        end if
        call stmt%finalize()
        
    end function get_group_entries_count_db
    
    function get_group_entries_db(group) result(res)
    implicit none
    
        integer, intent(in)::group
        type(group_entry), dimension(:), pointer::res
        type(sqlite3_stmt)::stmt
        integer::i, n
        
        res => null()
        
        n = get_group_entries_count_db(group)
        if(n > 0) then
        
            allocate(res(n))
            res%instruction = -1
            res%player = -1
            res%group = group
            
            if(stmt%prepare(db, "SELECT instruction, player FROM group_instructions WHERE group_id=?") == SQLITE_OK) then
                if(stmt%bind_int(1, group) == SQLITE_OK) then
                   
                    i = 0
                    do while(stmt%step() == SQLITE_ROW .and. i <= n)
                        i = i + 1
                        res(i)%instruction = stmt%column_int(0)
                        res(i)%player = stmt%column_int(1)
                    end do
                
                end if
            end if
            call stmt%finalize()
            
        end if
        
    end function get_group_entries_db
    
    function get_available_count_db()
    implicit none
    
        integer::get_available_count_db
        type(sqlite3_stmt)::stmt
    
        get_available_count_db = 0
    
        if(stmt%prepare(db, "SELECT COUNT(*) FROM available") == SQLITE_OK) then
            if(stmt%step() == SQLITE_ROW) then
                get_available_count_db = stmt%column_int(0)
            end if
        end if
        call stmt%finalize()
        
    end function get_available_count_db
    
    function get_available_work_pairs_db() result(res)
    implicit none
    
        type(work_pair), dimension(:), pointer::res
        integer::n, i
        type(sqlite3_stmt)::stmt
        
        n = get_available_count_db()
        res => null()
        
        if(n > 0) then
            allocate(res(n))
            res%instruction = -1
            res%player = -1
            
            if(stmt%prepare(db, "SELECT instruction, player FROM available") == SQLITE_OK) then
                i = 1
                do while(stmt%step() == SQLITE_ROW .AND. i <= n)
                    res(i)%instruction = stmt%column_int(0)
                    res(i)%player = stmt%column_int(1)
                    i = i + 1
                end do
            end if
            call stmt%finalize()
            
        end if
        
    end function get_available_work_pairs_db
    
    subroutine acknowledge_checkin(player, platform)
    implicit none
    
        integer, intent(in)::player
        character(*), intent(in), optional::platform
        type(sqlite3_stmt)::stmt
        integer, dimension(8)::right_now
        integer::res
        
        call date_and_time(values=right_now)
        
        if(stmt%prepare(db, &
            "INSERT OR REPLACE INTO checkin(player, year, month, day, hour, minute, second, os)" &
            //" VALUES(?, ?, ?, ?, ?, ?, ?, ?)") &
            == SQLITE_OK) &
        then
            if(stmt%bind_int(1, player) == SQLITE_OK .AND. &
               stmt%bind_int(2, right_now(1)) == SQLITE_OK .AND. &
               stmt%bind_int(3, right_now(2)) == SQLITE_OK .AND. &
               stmt%bind_int(4, right_now(3)) == SQLITE_OK .AND. &
               stmt%bind_int(5, right_now(5)) == SQLITE_OK .AND. &
               stmt%bind_int(6, right_now(6)) == SQLITE_OK .AND. &
               stmt%bind_int(7, right_now(7)) == SQLITE_OK ) &
            then
               
                if(.not. present(platform)) then
                    res = stmt%bind_null(8)
                else
                    res = stmt%bind_text(8, platform)
                end if
                
                if(res == SQLITE_OK) then
                    call stmt%step_now()
                end if
               
            end if
        end if
        call stmt%finalize()
        
    end subroutine acknowledge_checkin
    
    ! For definition of values. see Fortran's DATE_AND_TIME
    subroutine get_last_checkin_time(player, values)
    use sqlite
    implicit none
    
        integer, intent(in)::player
        integer, dimension(8), intent(out)::values
        
        type(sqlite3_stmt)::stmt
        
        ! This call properly retrieves the offset from UTC if you want it...
        values = 0
        call date_and_time(values=values)
        
        if(stmt%prepare(db, "SELECT year, month, day, hour, minute, second FROM checkin WHERE player=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_int(1, player) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    values(1) = stmt%column_int(0)
                    values(2) = stmt%column_int(1)
                    values(3) = stmt%column_int(2)
                    values(5) = stmt%column_int(3)
                    values(6) = stmt%column_int(4)
                    values(7) = stmt%column_int(5)
                else
                    values = 0
                end if
            end if
        end if
        call stmt%finalize()

    end subroutine get_last_checkin_time
    
    subroutine get_player_platform(player, platform)
    use sqlite
    implicit none
    
        integer, intent(in)::player
        character(*), intent(out)::platform
        type(sqlite3_stmt)::stmt
        
        platform = " "
        
        if(stmt%prepare(db, "SELECT platform FROM checkin WHERE player=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_int(1, player) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    if(stmt%column_type(0) == SQLITE_TEXT) then
                        call stmt%column_text(0, platform)
                    end if
                end if
            end if
        end if
        call stmt%finalize()
    
    end subroutine get_player_platform
    
    pure function julian_date(values) result(jd)
    implicit none
    
        integer, dimension(3), intent(in)::values
        integer::jd
        
        jd = (1461 * (values(1) + 4800 + (values(2)-14)/12))/4 + &
             (367 * (values(2) - 2 - 12*((values(2)-14)/12)))/12 - &
             (3*((values(1) + 4900 + (values(2)-14)/12)/100))/4 + &
             values(3) - 32075
        
    end function julian_date
    
    pure function seconds_difference(first, second) result(diff)
    implicit none
        
        integer, dimension(8), intent(in)::first, second
        integer(kind=8)::diff
        
        diff = (julian_date(first(1:3)) - julian_date(second(1:3))) * 24 * 3600
        
        diff = diff + (first(5) - second(5)) * 3600
        diff = diff + (first(6) - second(6)) * 60
        diff = diff + (first(7) - second(7))
        
    end function seconds_difference
    
    function is_player_online(player) result(ret)
    implicit none
    
        integer, intent(in)::player
        logical::ret
        
        integer, dimension(8)::now, last
        
        call date_and_time(values=now)
        call get_last_checkin_time(player, last)
        
        ! All zeroes mean that it was never online
        if(all(last == 0)) then
        
            ret = .false.
        
        else

            ret = (seconds_difference(now, last) <= 60) 
            
        end if
        
    end function is_player_online

    subroutine add_scheduled_job(player, instruction, day, hour)
    implicit none
        
        integer, intent(in)::player, instruction, day, hour
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "INSERT INTO schedule(instruction, player, day, hour) VALUES(?, ?, ?, ?") == SQLITE_OK) then
            if(stmt%bind_int(1, instruction) == SQLITE_OK .and. &
               stmt%bind_int(2, player) == SQLITE_OK .and. &
               stmt%bind_int(3, day) == SQLITE_OK .and. &
               stmt%bind_int(4, hour) == SQLITE_OK) &
            then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine add_scheduled_job
    
    subroutine delete_scheduled_job(player, instruction, day, hour)
    implicit none
        
        integer, intent(in)::player, instruction, day, hour
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "DELETE FROM schedule WHERE instruction=? AND player=? AND day=? AND hour=?") == SQLITE_OK) then
            if(stmt%bind_int(1, instruction) == SQLITE_OK .and. &
               stmt%bind_int(2, player) == SQLITE_OK .and. &
               stmt%bind_int(3, day) == SQLITE_OK .and. &
               stmt%bind_int(4, hour) == SQLITE_OK) &
            then
                call stmt%step_now()
            end if
        end if
        call stmt%finalize()
    
    end subroutine delete_scheduled_job
    
    subroutine queue_scheduled_jobs(player)
    implicit none
    
        integer, intent(in)::player
        
        ! Needs implementation
    
    end subroutine queue_scheduled_jobs

    function new_user_db(username, password, email, auth_level)
    use config, only: app_salt
    use m_crypt, only: hash
    use auth_levels, only: AUTH_NORMAL_USER
    implicit none
    
        character(*), intent(in)::username, password, email
        integer, intent(in), optional::auth_level
        logical::new_user_db
        type(sqlite3_stmt)::stmt
        
        character(len=:), pointer::hashed_pass
        
        integer::user_auth_level
        
        if(present(auth_level)) then
            user_auth_level = auth_level
        else
            user_auth_level = AUTH_NORMAL_USER
        end if
        
        new_user_db = .FALSE.
        
        hashed_pass => hash(trim(password)//trim(app_salt))
        if(associated(hashed_pass)) then
        
            if(stmt%prepare(db, "INSERT INTO users(username, password, email, level) VALUES(?, ?, ?, ?)") == SQLITE_OK) then
                if(stmt%bind_text(1, username) == SQLITE_OK .and. &
                   stmt%bind_text(2, hashed_pass) == SQLITE_OK .and. &
                   stmt%bind_text(3, email) == SQLITE_OK .and. &
                   stmt%bind_int(4, user_auth_level) == SQLITE_OK) then
                   
                    new_user_db = any(stmt%step() == (/ SQLITE_OK, SQLITE_DONE /))
                
                end if
            end if
            call stmt%finalize()
            
            deallocate(hashed_pass)
        end if
    
    end function new_user_db
    
    function new_admin_db(username, password)
    use AUTH_LEVELS, only: AUTH_ADMIN_USER
    implicit none
    
        character(len=*), intent(in)::username, password
        logical::new_admin_db
        
        ! Dangerous debug line....
        ! Print *, trim(username)//":"//trim(password)

        new_admin_db = new_user_db(username, password, trim(username)//"@localhost", AUTH_ADMIN_USER)
    
    end function new_admin_db
    
    function get_password_hash_pointer_db(username) result(password)
    implicit none
    
        character(len=*), intent(in)::username
        character(len=:), pointer::password
        type(sqlite3_stmt)::stmt
        integer::textlength
        
        password => null()
        
        if(stmt%prepare(db, "SELECT password FROM users WHERE username=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_text(1, username) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    
                    textlength = stmt%column_text_length(0)
                    if(textlength > 0) then
                        allocate(character(len=textlength) :: password)
                        call stmt%column_text(0, password)
                    end if
                end if
            end if
            call stmt%finalize()
        end if
    
    end function get_password_hash_pointer_db
    
    function validate_user_db(username, password)
    use config, only: app_salt
    use m_crypt, only: verify_hash
    use logging
    implicit none
    
        character(len=*), intent(in)::username, password
        logical::validate_user_db
    
        character(len=:), pointer::db_hashed_pass
        
        validate_user_db = .FALSE.
                    
        db_hashed_pass => get_password_hash_pointer_db(username)
        if(associated(db_hashed_pass)) then
        
            validate_user_db = verify_hash(trim(password)//trim(app_salt), db_hashed_pass)
            
            deallocate(db_hashed_pass)
        end if
        
    end function validate_user_db
    
    function get_user_id_db(username)
    implicit none
        
        character(len=*), intent(in)::username
        integer::get_user_id_db
    
        type(sqlite3_stmt)::stmt
        
        get_user_id_db = -1
        
        if(stmt%prepare(db, "SELECT id FROM users WHERE username=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_text(1, username) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    get_user_id_db = stmt%column_int(0)
                end if
            end if
            call stmt%finalize()
        end if
    
    end function get_user_id_db
    
    function get_user_auth_db(username)
    implicit none
        
        character(len=*), intent(in)::username
        integer::get_user_auth_db
    
        type(sqlite3_stmt)::stmt
        
        get_user_auth_db = -1
        
        if(stmt%prepare(db, "SELECT level FROM users WHERE username=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_text(1, username) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    get_user_auth_db = stmt%column_int(0)
                end if
            end if
            call stmt%finalize()
        end if
    
    end function get_user_auth_db
    
    function get_session_auth_db(session)
    use auth_levels, only: AUTH_NONE
    implicit none
        
        character(len=*), intent(in)::session
        integer::get_session_auth_db
    
        type(sqlite3_stmt)::stmt
        
        get_session_auth_db = AUTH_NONE
        
        if(stmt%prepare(db, "SELECT level FROM session_auth WHERE session=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_text(1, session) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    get_session_auth_db = stmt%column_int(0)
                end if
            end if
            call stmt%finalize()
        end if
    
    end function get_session_auth_db
    
    subroutine get_session_username_db(session, username)
    implicit none
        
        character(len=*), intent(in)::session
        character(len=*), intent(out)::username
        
        type(sqlite3_stmt)::stmt
        
        username = ' '
        
        if(stmt%prepare(db, "SELECT username FROM session_auth WHERE session=? LIMIT 1") == SQLITE_OK) then
            if(stmt%bind_text(1, session) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    call stmt%column_text(0, username)
                end if
            end if
            call stmt%finalize()
        end if
    
    end subroutine get_session_username_db
    
    function create_user_session_db(username) result(session)
    use m_uuid
    implicit none
    
        character(len=*), intent(in)::username
        character(len=UUID_LENGTH)::session, internal_session
        
        integer::userid, res
        type(sqlite3_stmt)::stmt
        
        internal_session = generate_uuid4()
        userid = get_user_id_db(username)
        session = ' '
        
        if(stmt%prepare(db, "INSERT INTO sessions(user, session, accessed) VALUES(?, ?, datetime('now'))") == SQLITE_OK) then
            if(stmt%bind_int(1, userid) == SQLITE_OK .AND. &
               stmt%bind_text(2, internal_session) == SQLITE_OK) then
                
                if(any(stmt%step() == (/SQLITE_OK, SQLITE_DONE, SQLITE_ROW/))) then
                    session = internal_session
                end if
                
            end if
        
            call stmt%finalize()
        end if
    
    end function create_user_session_db

    subroutine destroy_session_db(session)
    implicit none
    
        character(len=*), intent(in)::session
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "DELETE FROM sessions WHERE session=?") == SQLITE_OK) then
            if(stmt%bind_text(1, session) == SQLITE_OK) then
                call stmt%step_now()
            end if
            call stmt%finalize()
        end if
           
    end subroutine destroy_session_db

    subroutine destroy_old_sessions_db()
    implicit none
    
        type(sqlite3_stmt)::stmt

        if(stmt%prepare(db, "DELETE FROM sessions WHERE accessed < datetime('now', '-1 day')") == SQLITE_OK) then
            call stmt%step_now()
            call stmt%finalize()
        end if

    end subroutine destroy_old_sessions_db
    
    subroutine update_session_db(session)
    implicit none
    
        character(len=*), intent(in)::session
        type(sqlite3_stmt)::stmt
        
        if(stmt%prepare(db, "UPDATE sessions SET accessed=datetime('now') WHERE session=?") == SQLITE_OK) then
            if(stmt%bind_text(1, session) == SQLITE_OK) then
                call stmt%step_now()
            end if
            call stmt%finalize()
        end if
    
    end subroutine update_session_db

    function session_expired_db(session)
    use logging
    implicit none
    
        character(len=*), intent(in)::session
        logical::session_expired_db
        
        type(sqlite3_stmt)::stmt
        
        session_expired_db = .true.
        
        ! Statement should return the count of sessions that _has not_ expired!
        if(stmt%prepare(db, "SELECT COUNT(*) FROM sessions WHERE accessed > datetime('now', '-30 minutes') AND session=?") &
           == SQLITE_OK) &
        then
            
            if(stmt%bind_text(1, session) == SQLITE_OK) then
                
                if(stmt%step() == SQLITE_ROW) then
                    
                    ! If expired, the value would be 0...
                    session_expired_db = .not. (stmt%column_int(0) > 0)
                end if
                
            end if
        
            call stmt%finalize()
        end if
        
        if(session_expired_db) then
            call destroy_session_db(session)
        end if
        
    end function session_expired_db

    function is_valid_session_db(session)
    implicit none
    
        character(len=*), intent(in)::session
        logical::is_valid_session_db
        
        ! The call below will say a non-existent session is "expired," so whatever...
        is_valid_session_db = (.not. session_expired_db(session))
        
    end function is_valid_session_db
        
    subroutine add_job_upload_db(job_id, category, filename)
    implicit none
    
        integer, intent(in)::job_id
        character(len=*), intent(in)::category, filename
    
        type(sqlite3_stmt)::stmt

        if(stmt%prepare(db, "INSERT INTO job_uploads(job, category, filename) VALUES(?, ?, ?)")  == SQLITE_OK) then
                
            if(stmt%bind_int(1, job_id) == SQLITE_OK .and. stmt%bind_text(2, category) == SQLITE_OK .and. &
               stmt%bind_text(3, filename) == SQLITE_OK) &
            then
            
                call stmt%step_now()
            
            end if
            call stmt%finalize()
        end if
    
    end subroutine add_job_upload_db

    function get_job_upload_count_by_category_db(job_id, category) result(entries)
    implicit none
    
        integer, intent(in)::job_id
        character(len=*), intent(in)::category
        integer::entries
        type(sqlite3_stmt)::stmt
    
        entries = 0
    
        if(stmt%prepare(db, "SELECT COUNT(*) FROM job_uploads WHERE job=? AND category=?") == SQLITE_OK) then
            if(stmt%bind_int(1, job_id) == SQLITE_OK .and. stmt%bind_text(2, category) == SQLITE_OK) then
                if(stmt%step() == SQLITE_ROW) then
                    entries = stmt%column_int(0)
                end if
            end if
            call stmt%finalize()
        end if
        
    end function get_job_upload_count_by_category_db
    
    function get_job_uploads_by_category_db(job_id, category, count) result(res)
    implicit none

        integer, intent(in)::job_id
        character(len=*), intent(in)::category
        integer, intent(out), optional::count
        
        character(len=FILENAME_NAME_LENGTH), dimension(:), pointer::res
        
        type(sqlite3_stmt)::stmt
        integer::entries, i
        
        res => null()
        entries = get_job_upload_count_by_category_db(job_id, category)
        
        if(entries > 0) then
            allocate(res(entries))
            
            i = 1
            if(stmt%prepare(db, "SELECT filename FROM job_uploads WHERE job=? AND category=?") == SQLITE_OK) then
                if(stmt%bind_int(1, job_id) == SQLITE_OK .and. stmt%bind_text(2, category) == SQLITE_OK) then
                    do while(stmt%step() == SQLITE_ROW)
                        call stmt%column_text(0, res(i))
                        i = i + 1
                    end do
                end if
            end if
        end if
        
        if(present(count)) then
            count = entries
        end if

    end function get_job_uploads_by_category_db

end module captain_db