! 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
    
    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
        
    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
    
    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 = " "
        Print *, id
        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
    
    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 UPDATE INTO checkin(player, year, month, day, hour, minute, second, platform) 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
        integer::i
        
        ! This call properly retrieves the offset from UTC if you want it...
        call date_and_time(values=values)
        values(1:3) = 0
        values(5:8) = 0
        
        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
                    do i = 1, 7
                        if(i < 4) then
                            values(i) = stmt%column_int(i-1)
                        else if(i >= 5) then
                            values(i-1) = stmt%column_int(i-1)
                        end if
                    end do
                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
    
end module captain_db