local sqlite3 = require('lsqlite3')
local fetch = require( "fetch" )
local textutils = require( "textutils" )

local db = sqlite3.open( "database.sqlite" )

local data = {}

-- Make sure the table exists before we use it
local function createTable( symbol )
    for row in db:nrows( "SELECT name FROM sqlite_master WHERE type='table' AND name='" .. symbol .. "';" ) do
        return
    end

    print( "Creating table for " .. symbol )
    db:exec( "CREATE TABLE `" .. symbol .. [[` (
        `date`  STRING UNIQUE,
        `volume`    INTEGER,
        `open`  REAL,
        `close` REAL,
        `high`  REAL,
        `low`   REAL,
        `adjustedClose` REAL
    ); ]] )
end

local function insert( symbol, csvData )
    print( "Inserting data into " .. symbol )
    createTable( symbol )
    local insertStatement = db:prepare( [[ INSERT OR IGNORE INTO `]] .. symbol .. [[` VALUES (:date, :volume, :open, :close, :high, :low, :adjustedClose) ]] )

    for i, row in ipairs( csvData ) do
        insertStatement:bind_names( row )
        insertStatement:step()
        insertStatement:reset()
    end

    insertStatement:finalize()
    print( "    Complete!" )
end

-- get all the data for a certain stock (most recent first)
function data.update( symbol )
    print( "Update data for " .. symbol )
    local data = fetch.downloadCSV( symbol )
    print("    Complete!")
    insert( symbol, data )
end

-- get all the data for a certain stock (most recent first)
function data.all( symbol )
    print( "Getting ALL data for " .. symbol )
    local all = {}
    local n = 0
    for row in db:nrows( "SELECT * FROM `" .. symbol .. "`;" ) do
        all[n] = textutils.serialise( row )
        n = n + 1
    end
end

-- get all the data for a certain stock (most recent first)
function data.allUntil( symbol, date )
    local all = {}
    local n = 0
    for row in db:nrows( "SELECT * FROM `" .. symbol .. "` WHERE `date` <= date('" .. date .. "') ORDER BY `date` DESC;" ) do
        -- all[n] = textutils.serialise( row )
        print(textutils.serialise( row ))
        all[n] = row
        n = n + 1
    end
end

data.update( "AVN.AX" )

-- data.all( "AVN.AX" )
-- data.allUntil( "AVN.AX", "2015-11-13" )
-- data.all( "AAPL" )

-- createTable( "AAPL" )
-- createTable( "BOOA" )

return data