// Advanced Editor Data Base

/*
    create: dialog for a new database
    extract: get a file from the base
    save: put a file in the base
    A close dialog opens from the menu.
    The database is also a project.
*/

const fs = require("fs")
const util = require("util")
const {dialog} = require('electron').remote   
var sqlite;

var dbName = undefined   // filename of the database to create
var dbPath = undefined
var db = undefined
var rowsCtr = 0
var data = undefined    // row
var modified=false
exports.dbName = dbName
exports.dbPath = dbPath
exports.data = data
exports.modified=modified


var db_title = undefined
var db_cat
var db_ext
var db_desc
var db_keys
var db_date
var db_modif
exports.db_title=db_title


function exit(parent, mainWin) {
    mainWin.restartEditor() 
    parent.innerHTML = ""
}

// Load a database

exports.load = function(fpath) {
    if(fpath==undefined) return
    if(sqlite == undefined) {
        sqlite=require("sql.js")
    }    

    this.dbPath = fpath
    var npos = fpath.lastIndexOf("/")
    this.dbName = fpath.substr(npos + 1)
    if(!fs.existsSync(fpath)) return false
    var buffer = fs.readFileSync(fpath)
    
    var uInt8Array = new Uint8Array(buffer)
    if(uInt8Array == undefined) {
        alert("No content in "+fpath)
        db = new sqlite.Database()
        return false
    }

    db = new sqlite.Database(buffer)
    try {
        var data = db.exec("SELECT COUNT(title) FROM pages")
        rowsCtr = data[0].values
        return rowsCtr
    }
    catch(e) {
        var tables = db.exec("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
        alert(JSON.stringify(tables, null, ' '))
    }
    return false
}

// Save a new database

exports.save =function(mainWin) {
    if(!this.modified) return false
    var storage = db.export()
    var buffer = new Buffer(storage)
    if(this.dbName == undefined) {
        var p = dialog.showSaveDialog(null, {
            title:"Save new database in a file",
            defaultPath: this.dbPath,
            buttonLabel: "Save database"
        })
        if(p == undefined || p.length == 0) return
        var pos = p.lastIndexOf(".")
        if(pos == -1) p += ".db"
        this.dbPath = p
        this.dbName = mainWin.extractFile(this.dbPath)
        mainWin.showDB(0)
    }
    fs.writeFileSync(this.dbPath, buffer)
    this.modified=false
    return true
}


exports.saveInDB=function(mainWin) {
    if(this.dbName == undefined) return
    if(this.db_title==undefined) return     // for CTRL-S
    var row = db.exec("SELECT * FROM pages WHERE title='"+this.db_title+"' COLLATE NOCASE")
    if(row && row.length > 0)  {
        let dt = row[0].values[6]
        if(dt!=null)  db_date = dt
        db.run("DELETE FROM pages WHERE title='"+this.db_title+"' COLLATE NOCASE")
    }
    db_modif = buildDate(new Date())

    var r = db.run("INSERT INTO pages VALUES(?,?,?,?,?,?,?,?)", 
               [this.db_title, db_cat, db_ext, db_desc, db_keys, mainWin.editor.getValue(), db_date, db_modif])
    if(!r) {
        alert("Error, not inserted")
        return false
    }
    this.modified=true
    return true
}

// Create a new database

exports.create = function(mainWin, parent) {
    if(sqlite == undefined) {
        sqlite=require("sql.js")
    }  
    var titlebar = document.createElement("div")
    titlebar.innerHTML = "CREATING A DATABASE OF SCRIPTS / PAGES"
    parent.appendChild(titlebar)

    var sTLab = document.createElement("p")
    sTLab.innerHTML = "Defining the main table:"
    parent.appendChild(sTLab)
        
    var sP2 = document.createElement("p")
        var sQuery = document.createElement("textarea")
        sQuery.setAttribute("cols", "60")
        sQuery.setAttribute("rows", "6")
        sQuery.setAttribute("id", "data")
        sQuery.setAttribute("style", "font-weight:bold")
        sQuery.innerHTML = "CREATE TABLE pages(title text NOT NULL PRIMARY KEY COLLATE NOCASE, cat text, ext text, desc text, keys text, data text, date text, modif text)"
        sP2.appendChild(sQuery)
    parent.appendChild(sP2)
    
    var sP3 = document.createElement("small")
    sP3.innerHTML="Add columns in the query above if needed."
    sP3.setAttribute("style", "font-style:italic")
    parent.appendChild(sP3)

    var sP4 = document.createElement("p")
        var b1 = document.createElement("button")
        b1.onclick=function() { 
            db = new sqlite.Database()
            var query = document.getElementById("data").value
            var r = db.run(query)
            if(r) {
                mainWin.closeDB()
                this.modified=true
                save(mainWin)   
            }
            else {
                alert("Error, not created")
            }
            exit(parent, mainWin)
        }
        b1.innerHTML="Create"
        sP4.appendChild(b1)

        var b0 = document.createElement("button")
        b0.onclick=function() { 
            exit(parent, mainWin)
        }
        b0.innerHTML="Cancel"
        sP4.appendChild(b0)
    parent.appendChild(sP4)
       
}

exports.close = function() {
    this.dbName=undefined
    this.dbPath=undefined  
    this.modified=false
}

// Insert of update a post

var data = undefined
exports.data = data

function buildDate(dt) {
    const y = dt.getFullYear()
    const m = dt.getMonth() + 1
    var d = "0" + dt.getDate()
    d=d.substr(-2)
    const h = dt.getHours()
    const n = dt.getMinutes()
    return (y + "-" + m + "-" + d + " " + h + ":" + n)  
}

exports.insert = function(mainWin, parent) {
    if(this.dbName==undefined) {
       this.create(mainWin, parent) 
       return
    }
    var titlebar = document.createElement("div")
    titlebar.innerHTML = "INSERT or REPLACE the SCRIPT / DOCUMENT"
    parent.appendChild(titlebar)
    
    var aedb = this   // for onclick
    var defTitle = ""
    var dt = new Date()
    db_ext=""
    
    if(mainWin.AEditFileName != undefined) {
        db_ext = mainWin.getExtension(mainWin.AEditFileName).substr(1)
        defTitle = mainWin.extractNode(mainWin.getFileNode())
        defTitle = defTitle.charAt(0).toUpperCase() + defTitle.substr(1)
        if(!mainWin.contentToSave) {
            if(fs.existsSync(mainWin.AEditFileName)) {
                var stats = fs.statSync(mainWin.AEditFileName)
                dt = new Date(util.inspect(stats.mtime));
            }
        }
    }
    
    db_date=buildDate(dt)
    
    const edate = document.createElement("p")
    edate.innerHTML = " Modified: <b>" + db_date + "</b>"
    parent.appendChild(edate)      
    
    var spTitle = document.createElement("p")
        var slabTitle = document.createElement("span")
        slabTitle.innerHTML = "Title: "
        spTitle.appendChild(slabTitle)        
        var sInTitle = document.createElement("input")
        sInTitle.setAttribute("size", 32)
        sInTitle.setAttribute("id", "inTitle")
        sInTitle.setAttribute("value", defTitle)
        spTitle.appendChild(sInTitle)    
        if(data != undefined) {
            sInTitle.value = data.title
        }
    parent.appendChild(spTitle)   
    

    var spCat = document.createElement("p")
        const clab = document.createElement("span")
        clab.innerHTML = " Cat/Path: "
        spCat.appendChild(clab)   
        var sInCat = document.createElement("input")
        sInCat.setAttribute("size", 48)
        sInCat.id = "inCat"
        sInCat.value = mainWin.AEditFileName
        spCat.appendChild(sInCat)       
        const efor = document.createElement("span")
        efor.innerHTML = " Format: <b>" + db_ext + "</b>"
        spCat.appendChild(efor)  
    parent.appendChild(spCat)    
    
    
    var spDesc = document.createElement("p")
        var sLabDesc = document.createElement("span")
        sLabDesc.innerHTML = "Description: "
        spDesc.appendChild(sLabDesc)    
        var sInDesc = document.createElement("input")
        sInDesc.setAttribute("size", 60)
        sInDesc.setAttribute("id", "inDesc")
        spDesc.appendChild(sInDesc)  
 
    parent.appendChild(spDesc)      
    
    
    var spKeys = document.createElement("p")
        var slabKeys = document.createElement("span")
        slabKeys.innerHTML = "Keywords: "
        spKeys.appendChild(slabKeys)      
        var sInKeys = document.createElement("input")
        sInKeys.setAttribute("size", 48)
        sInKeys.setAttribute("id", "inKeys")
        spKeys.appendChild(sInKeys)    
    parent.appendChild(spKeys)     

    var sBar = document.createElement("p")
        var bAdd = document.createElement("button")
        bAdd.innerHTML="Add"
        bAdd.onclick=function() { 
            defTitle=document.getElementById("inTitle").value
            if(defTitle != "" && aedb.db_title != undefined && 
               (defTitle.toLowerCase() == aedb.db_title.toLowerCase())) {
                if(!confirm(defTitle + " already exist. Replace?")) return
            }
            aedb.db_title = defTitle
            db_desc = document.getElementById("inDesc").value
            db_keys = document.getElementById("inKeys").value
            db_cat = document.getElementById("inCat").value  
            if(aedb.saveInDB(mainWin)) {
                rowsCtr++  
                mainWin.document.getElementById("rowsCtr").innerHTML = rowsCtr
                aedb.modified=true
            }
            else {
                alert(aedb.db_title + " not added in " + aedb.dbName)
            }
            exit(parent, mainWin)
        }
    sBar.appendChild(bAdd)

    var bZap = document.createElement("button")
    bZap.onclick=function() { 
        exit(parent, mainWin)
    }
    bZap.innerHTML="Cancel"
    sBar.appendChild(bZap)
    
    parent.appendChild(sBar)
}

// Read from the database

var interface
var aedb=this
function suppress(title, dt, cat) {
    var lab = "Delete " + title
    var query = `DELETE FROM pages WHERE title='${title}' AND modif='${dt}'`
    if(cat != "") {
        query += " AND cat='" + cat + "'"
        lab += " (" + cat + ")"
    }
    if(!confirm(lab+'?')) return false
    db.run(query)
    rowsCtr--  
    interface.document.getElementById("rowsCtr").innerHTML = rowsCtr
    aedb.modified=true
    return true
}


function displayList(cb, plist, dlist, parent, mainWin) {
    var ctr = 0
    plist.innerHTML =""    
    for(var item of dlist) {
        var li = document.createElement("li")
        var url = item[1]
        var dt = item[7]
        let ld="led"
        if(url==mainWin.AEditFileName) ld = "ledf"
        
        var ilact = document.createElement("div")
        ilact.className='ilact'
        ilact.onclick=function() {
            let n = this.parentNode.getAttribute("data-num")
            cb(dlist[n])
            exit(parent, mainWin)
        }
        ilact.innerHTML=`<span class='${ld}'>&gt;</span><span class='liTitle'>${item[0]}</span><span class='liDate'>${dt}</span>${url}`;
        li.appendChild(ilact)

        var info =  document.createElement("div")
        info.className='cross'
        info.onclick=function() {
            let n = this.parentNode.getAttribute("data-num")
            let o = dlist[n]
            let data = "Title: "+ o[0] + "\n"
            data+= "Cat: "+ o[1] + "\n"
            data+= "Ext: "+ o[2] + "\n"
            data+= "Desc: "+ o[3] + "\n"
            data+= "Keys: "+ o[4] + "\n"
            data+= "Created: "+ o[6] + "\n"
            data+= "Modified: "+ o[7] 
            alert(data)
        }
        info.setAttribute('title', 'Show full record')
        info.innerHTML="?"
        li.appendChild(info)
        
        
        var ildel =  document.createElement("div")
        ildel.className='cross'
        ildel.onclick=function() {
            let n = this.parentNode.getAttribute("data-num")
            let o = dlist[n]
            if(suppress(o[0], o[7], o[1])) {
                dlist.splice(n,1)
                displayList(cb, plist, dlist, parent, mainWin)
            }  
        }
        ildel.setAttribute('title', 'Delete')
        ildel.innerHTML="x"
        li.appendChild(ildel)
        li.setAttribute("data-num", ctr)
        plist.appendChild(li)
        ctr++
    }    
}

exports.read = function(mainWin, parent, cb) {
    if(this.dbName==undefined) {
       this.create(mainWin, parent) 
       return
    }
    interface=mainWin
    let aedb=this

    var spTitle = document.createElement("p")
        var slabTitle = document.createElement("span")
        slabTitle.innerHTML = "Title: "
        spTitle.appendChild(slabTitle)    
    
        var sInTitle = document.createElement("input")
        sInTitle.setAttribute("size", 32)
        sInTitle.id = "inTitle"
        spTitle.appendChild(sInTitle)    

        var slabCat = document.createElement("span")
        slabCat.innerHTML = " &nbsp;Path/Category: "
        spTitle.appendChild(slabCat)  
    
        var spInCat = document.createElement("input")
        spInCat.id = "inCat"
        spInCat.setAttribute("size", 32)
        spTitle.appendChild(spInCat)   
    
        var slabExt = document.createElement("span")
        slabExt.innerHTML = " &nbsp;Ext: "
        spTitle.appendChild(slabExt)    
    
        var sInExt = document.createElement("input")
        sInExt.setAttribute("size", 8)
        sInExt.id = "inExt"
        spTitle.appendChild(sInExt)  
    
    parent.appendChild(spTitle)    
    
    var spDesc = document.createElement("p")
    
        var slabDesc = document.createElement("span")
        slabDesc.innerHTML = "Description: "
        spDesc.appendChild(slabDesc) 
    
        var sInDesc = document.createElement("input")
        sInDesc.setAttribute("size", 48)
        sInDesc.id = "inDesc"
        spDesc.appendChild(sInDesc)   
    
        var slabMod = document.createElement("span")
        slabMod.innerHTML = " &nbsp;Since last modified: "
        spDesc.appendChild(slabMod)        
        
        var sInMod = document.createElement("input")
        sInMod.setAttribute("size", 20)
        sInMod.id = "inMod"
        spDesc.appendChild(sInMod)       
    parent.appendChild(spDesc)      
    
    /* FUTURE
    var spKeys = document.createElement("p")
        var slabKeys = document.createElement("span")
        slabKeys.innerHTML = "Keywords: "
        spKeys.appendChild(slabKeys)        
        
        var sInKeys = document.createElement("input")
        sInKeys.setAttribute("size", 60)
        sInKeys.id = "inKeys"
        spKeys.appendChild(sInKeys)    
    parent.appendChild(spKeys)     
    */
    
    var sBar = document.createElement("p")
        var tlabel = document.createElement("span")
        tlabel.innerHTML = "GET FROM DATABASE"
        tlabel.id = "read"
        sBar.appendChild(tlabel)

        var bGet = document.createElement("button")
        bGet.onclick=function() { 
            const t = document.getElementById("inTitle").value
            const d = document.getElementById("inDesc").value
            const e = document.getElementById("inExt").value
            //const k = document.getElementById("inKeys").value
            const modif = document.getElementById("inMod").value
            const cat = document.getElementById("inCat").value
            const content = mainWin.editor.getValue()
            var condition = ""
            if(t != '') {
                condition = "title='" + t + "'"
            }
            if(d != '') {
                if(condition != "") condition +=" AND "
                condition += "desc='" + d + "'"
            }
            if(e != '') {
                if(condition != "") condition +=" AND "
                condition += "ext='" + e + "'"
            }  
            /*
            if(k != '') {
                if(condition != "") condition +=" AND "
                condition += "keys LIKE '%" + k +"%'"
            }
            */
            if(modif != '') {
                if(condition != "") condition +=" AND "
                condition += "date(modif) >= '" + modif + "'"
            }
            //alert("condition="+condition)   
            let query = "SELECT * FROM pages"
            if(condition != "") query += " WHERE " + condition
            query += " ORDER BY modif DESC"
            //alert(query)
            let row = db.exec(query)
            if(row && row.length > 0)  {
                var plist =document.getElementById("itemList")
                displayList(cb, plist, row[0].values, parent, mainWin)
            }
            else {
                alert("Found nothing")
            }
        }
        bGet.innerHTML="Search"
        sBar.appendChild(bGet)

        var bZap = document.createElement("button")
        bZap.onclick=function() { 
            exit(parent, mainWin)
        }
        bZap.innerHTML="Cancel"
        sBar.appendChild(bZap)
    parent.appendChild(sBar)
    
    var spField = document.createElement("fieldset")
    spField.id = "lfield"
    var spList  = document.createElement("ul")
    spList.id="itemList"
    spField.appendChild(spList)
    parent.appendChild(spField)
    
    // Initial list
    
    let query = "SELECT * FROM pages ORDER BY modif DESC"
    let row = db.exec(query)
    if(row && row.length > 0) {
        var plist=document.getElementById("itemList")
        displayList(cb, plist, row[0].values, parent, mainWin)
    }    
       
}