local m = {}

local sqlite3 = require("lsqlite3")

local channelStartId = 36000
local channelIdOffset = 10
local databasePath = "/opt/userdata/recording.db"
local databaseCurrentVersion = 71

-- Notification are simple monitoring channels
function m.CreateRecordingDefiniton(number, config)
    local channel = device:AddChannel(string.format("Recording-%d",number))
    Logger:Info(string.format("Create Channel: Recording-%d",number))
    Logger:Debug(string.format("TypeURN: '%s', Name: '%s', Type: '%s'", config.TypeURN, config.Name, config.DPT))
    channel.TypeURN = config.TypeURN
    channel.FieldHandlerName = "RecordingApp"
    channel.StartId = channelStartId + (10 * (number - 1))
    channel:AddParameterSet("Recording", "DPT", config.DPT)
    channel:AddParameterSet("Recording", "Name", config.Name)
    channel:AddParameterSet("Recording", "AggregationFunction", config.AggregationFunctionString)
end

function m.ConfigureAppChannel(config)
    Logger:Info("-- recording_module.lua: configure parameter")
    helper.LogConfiguration("Recording-App", config)
    local channel = device:AddChannel("Recording-App")
    channel.Enabled = config.EnabledBool
end

function file_exists(name)
    local f=io.open(name,"r")
    if f~=nil then 
        io.close(f)
        return true
    else 
        return false
    end
end

function GetDatabaseUserVersion()
    if not file_exists(databasePath) then
        return 0
    end
    
    local db = sqlite3.open(databasePath, sqlite3.OPEN_READONLY)
    if db == nil then
        return 0
    end
    
    local function callback(udata, cols, values, names)
        udata[1] = values[1]
        return 0
    end
    
    local udata = {}
    
    db:exec("PRAGMA user_version", callback, udata)    
    db:close()
    
    return tonumber(udata[1]) or 0
end

function m.CheckDatabase(maximumRecordingChannels)
    Logger:Info("check for database and init if not present")
    
    local version = GetDatabaseUserVersion()
    Logger:Info("database current version: " .. version .. ", required: " .. databaseCurrentVersion)
    
    -- Remark: if a new product version has an increased number of recordings, need to alter existing database here.
    if version == 70 then
        -- we have a V7.0 database structure, we need to add the Settings filed to the visus table
        m.dbAccess = sqlite3.open(databasePath)
        m.dbAccess:exec("PRAGMA user_version="..databaseCurrentVersion..";");
        m.dbAccess:exec("PRAGMA MAX_PAGE_COUNT=1048576;")
        m.dbAccess:exec("ALTER TABLE visus ADD Settings TEXT;")
        Logger:Info(string.format("altered old existing database."))
    elseif version ~= databaseCurrentVersion then
        m.DeleteDatabase()
        Logger:Debug("-- recording_module.lua: database not present - creating empty database.")
        m.dbAccess = sqlite3.open(databasePath)
        local res = m.dbAccess:exec([[
                        PRAGMA MAX_PAGE_COUNT=1048576;
                        PRAGMA journal_mode=WAL;
                        PRAGMA user_version=]] .. databaseCurrentVersion .. [[;
                        
                        CREATE TABLE IF NOT EXISTS "recordings" (
                            [RecordingId]  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            [Name]  TEXT DEFAULT "" NOT NULL,
                            [Type]  TEXT DEFAULT "" NOT NULL,
                            [Linked]  INTEGER DEFAULT 0 NOT NULL,
                            [Aggregation]  INTEGER DEFAULT 0 NOT NULL
                        );
                        
                        CREATE TABLE IF NOT EXISTS "visus" (
                            [VisuId]  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            [AccessToken]  TEXT NOT NULL UNIQUE,
                            [Name]  TEXT NOT NULL,
                            [Interval]  INTEGER NOT NULL DEFAULT 86400,
                            [Settings]  TEXT
                        );
                        
                        CREATE TABLE IF NOT EXISTS "visuconfig" (
                            [VisuconfigId]  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            [VisuId]  INTEGER,
                            [RecordingId]  INTEGER,
                            [Settings]  TEXT,
                            FOREIGN KEY ([VisuId]) REFERENCES "visus" ([VisuId]),
                            FOREIGN KEY ([RecordingId]) REFERENCES "recordings" ([RecordingId])
                        );
                        
                        CREATE TRIGGER IF NOT EXISTS delete_visuconfig_when_type_changed
                        AFTER UPDATE ON recordings
                        WHEN old.Type <> new.Type OR old.Linked <> new.Linked OR old.Aggregation <> new.Aggregation
                        BEGIN
                        DELETE FROM visuconfig WHERE visuconfig.RecordingId = new.RecordingId;
                        END;
                        ]])
        Logger:Info(string.format("db creation main - result %d.", res))
        -- fill Recordings table
        local sql="INSERT INTO recordings VALUES (NULL, '', '', 0, 0)"
        for i = 1, maximumRecordingChannels-1 do
            sql = sql .. ",(NULL, '', '', 0, 0)"
        end
        res = m.dbAccess:exec(sql)
        Logger:Info(string.format("db configure recordings - result %d.", res))
        
        -- create data tables
        sql=""
        for i = 1, maximumRecordingChannels do
            sql=sql.."CREATE TABLE IF NOT EXISTS \"data_" .. i .."\" (\n"
            sql=sql..[[
    [id]  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Timestamp]  INTEGER NOT NULL,
    [Value]  NUMERIC NOT NULL
);
]]
            sql=sql.."CREATE INDEX IF NOT EXISTS idx_data_" .. i .. "_timestamp ON data_" .. i .. " (Timestamp);\n"
            sql=sql.."CREATE INDEX IF NOT EXISTS idx_data_" .. i .. "_id ON data_" .. i .. " (id);\n"
            sql=sql.."CREATE TRIGGER IF NOT EXISTS delete_data_" .. i .. "_on_change\n"
            sql=sql.."AFTER UPDATE ON recordings\n"
            sql=sql.."WHEN (old.Type <> new.Type OR old.Linked <> new.Linked OR old.Aggregation <> new.Aggregation) AND (new.RecordingId = ".. i .. ")\n"
            sql=sql.."BEGIN\n"
            sql=sql.."    DELETE FROM data_" .. i .. ";\n"
            sql=sql.."END;\n"
        end
        res = m.dbAccess:exec(sql)        
        Logger:Info(string.format("db create data - result %d.", res))
    else
        m.dbAccess = sqlite3.open(databasePath)
        m.dbAccess:exec("PRAGMA MAX_PAGE_COUNT=1048576;")
        Logger:Info(string.format("found existing database."))
    end
end

function m.DeleteDatabase()
    if file_exists(databasePath) then
        Logger:Info("Delete DB")
        os.remove(databasePath)
    end
    if file_exists(databasePath.."-shm") then
        os.remove(databasePath.."-shm")
    end
    if file_exists(databasePath.."-wal") then
        os.remove(databasePath.."-wal")
    end
end

function m.UnconfigureDatabaseChannels(firstId,lastId)
    local sql="UPDATE recordings SET Name='', Type='', Linked=0, Aggregation=0 WHERE RecordingId BETWEEN " .. firstId .. " AND " .. lastId .. ";"
    local res = m.dbAccess:exec(sql)
    Logger:Debug(string.format("Unconfigure DB channels %d to %d; result is %d",firstId, lastId, res))
end

function m.ConfigureDatabaseChannel(number, config, linked)
    local sql="UPDATE recordings SET Name='"..config.Name .. "', Type='" .. config.DPT .. "', Linked=" .. linked .. ", Aggregation=" .. config.AggregationFunctionNum .. " WHERE RecordingId=" .. number
    local res = m.dbAccess:exec(sql)
    Logger:Info(string.format("Configure DB channel %d: ('%s', '%s', %d, %d); result is %d",number, config.Name, config.DPT, linked, config.AggregationFunctionNum, res))
end

function m.CloseDatabase()
    m.dbAccess:exec("VACUUM;")
    Logger:Info("closing database.")
    if m.dbAccess then 
        m.dbAccess:close_vm(false)
    end
end

return m
