local function chart_load(win, chart)
    local db = win.db.exec("SELECT * FROM chart WHERE id="..win.chart.id)
    local db_chart = db[1]

    chart.info = db_chart.info
    
    chart.date.day = db_chart.day
    chart.date.month = db_chart.month
    chart.date.year = db_chart.year
    chart.date.hour = db_chart.hour
    chart.date.min = db_chart.min
    chart.date.sec = db_chart.sec
    chart.date.zone = db_chart.zone
    
    chart.place.info = db_chart.place
    chart.place.lon = db_chart.lon
    chart.place.lat = db_chart.lat
    
    chart.house = db_chart.house
    
    program.calc()
    program.draw()
end

-- Tree items callbacks

local function category_select(self)
    self.window.category = self
    self.window.chart = nil
end

local function chart_select(self)
    self.window.chart = self
    self.window.category = nil

    if pref.db_sql.autoload and self.window.parent.active then
	chart_load(self.window, self.window.parent.active)
    end
end

--

local function load_tree(win)
    win.tree.clear()
    
    -- Load category
    
    local groups = { [0] = win.tree }
    
    local list = win.db.exec("SELECT * FROM category")
    
    for _, item in ipairs(list) do
	local parent = groups[item.parent]
	local new = parent.itemgroup(item.name)
	
	new.id = item.id
	new.window = win
	new.callback = category_select
	new.parent  = parent
	new.open(true)
	
	groups[item.id] = new
    end

    win.tree.value(0)
    win.category = groups[1]
    
    -- Load charts
    
    list = win.db.exec("SELECT * FROM chart")
    
    for _, item in ipairs(list) do
	local category = groups[item.category]
	
	local new = category.item(
	    string.format("%s\t%02i\\/%02i\\/%4i %02i:%02i:%02i %s", 
		item.info, item.day, item.month, item.year,
		item.hour, item.min, item.sec, item.place))
	
	new.callback = chart_select
	new.id = item.id
	new.window = win
	new.category = category
    end
end

-- Menu callbacks

local function category_new(self)
    local name = gui.input("Category name", "")
    
    if name then
	local win = self.window.db_win
	local parent = win.category
	
	if not parent then
	    parent = win.chart.category
	end

	win.db.exec(string.format(
	    "INSERT INTO category (parent, name) VALUES (%i, %q)",
	    parent.id, name))

	local id = win.db.exec("SELECT max(ROWID) as id FROM category")

	local new = parent.itemgroup(name)
	
	new.id = id[1].id
	new.window = win
	new.callback = category_select
	new.parent  = parent
	new.open(true)
    end
end

local function category_rename(self)
    local win = self.window.db_win
    
    if not win.category then
	gui.alert("Select category, please")
	return
    end
    
    local name = gui.input("Category name", "")
    
    if name then
	win.db.exec(string.format(
	    "UPDATE category SET name = %q WHERE id = %i",
	    name, win.category.id))
	    
	win.category.label(name)
    end
end

local function category_del(self)
    local win = self.window.db_win
    
    if not win.category then
	gui.alert("Select category, please")
	return
    end

    if win.category.id == 1 then
	gui.alert("It's impossible!")
	return
    end

    local list = win.db.exec("SELECT id FROM category WHERE parent = "..win.category.id)
    
    if #list > 0 then
	gui.alert("Contain category inside")
	return
    end

    win.db.exec(string.format(
	"UPDATE chart SET category = %i WHERE category = %i",
	win.category.parent.id, win.category.id))
    
    win.db.exec("DELETE FROM category WHERE id = "..win.category.id)

    load_tree(win)
end

local function chart_to(self)
    local win = self.window.db_win
    local chart = self.window.active
    
    if not chart then
	gui.alert("No active chart")
	return
    end
    
    if not win.chart then
	gui.alert("Select chart, please")
	return
    end
    
    chart_load(win, chart)
end

local function chart_from(self)
    local win = self.window.db_win
    local chart = self.window.active
    
    if chart then
	if win.chart then			-- Save to chart, update
	    win.db.exec(string.format(
		"UPDATE chart SET "..
		"info = %q, day = %i, month = %i, year = %i, hour = %i, min = %i, sec = %i, "..
		"lon = %f, lat = %f, place = %q, zone = %q, house = %i WHERE id = %i",
		chart.info, chart.date.day, chart.date.month, chart.date.year, chart.date.hour, chart.date.min, chart.date.sec,
		chart.place.lon, chart.place.lat, chart.place.info, chart.date.zone, chart.house,
		win.chart.id))
		
	    win.chart.label(string.format("%s\t%02i/%02i/%4i %02i:%02i:%02i %s", 
		chart.info, chart.date.day, chart.date.month, chart.date.year,
		chart.date.hour, chart.date.min, chart.date.sec, chart.place.info))
	else					-- Save to category, new
	    win.db.exec(string.format(
		"INSERT INTO chart"..
		"(info, day, month, year, hour, min, sec, "..
		"lon, lat, place, zone, house, category) VALUES "..
		"(%q, %i, %i, %i, %i, %i, %i, "..
		"%f, %f, %q, %q, %i, %i)",
		chart.info, chart.date.day, chart.date.month, chart.date.year, chart.date.hour, chart.date.min, chart.date.sec,
		chart.place.lon, chart.place.lat, chart.place.info, chart.date.zone, chart.house,
		win.category.id))
		
	    if win.db.error() then
		print(win.db.error())
	    end
	    
	    local id = win.db.exec("SELECT max(ROWID) as id FROM chart")
	    
	    local new = win.category.item(string.format("%s\t%02i\\/%02i\\/%4i %02i:%02i:%02i %s", 
		chart.info, chart.date.day, chart.date.month, chart.date.year,
		chart.date.hour, chart.date.min, chart.date.sec, chart.place.info))
	
	    new.callback = chart_select
	    new.id = id[1].id
	    new.window = win
	    new.category = win.category
	end
    else
	gui.alert("No active chart")
    end
end

local function chart_del(self)
    local win = self.window.db_win

    if win.chart then
	win.db.exec("DELETE FROM chart WHERE id="..win.chart.id)
	win.chart.category.remove(win.chart)
	
	win.chart = nil
    else
	gui.message("Select chart, please")
    end
end

local function chart_autoload(self)
    pref.db_sql.autoload = self.value()
end

--

local function open_db(win)
    win.db = sqlite.open(pref.db_sql.path)

    -- Tables exists?

    local res, err = win.db.exec("SELECT * FROM category LIMIT 1")
    
    if err then
	res, err = win.db.exec[[
	    CREATE TABLE chart (
		id		INTEGER PRIMARY KEY AUTOINCREMENT,
		category	INT,
		day		INT,
		month		INT,
		year		INT,
		hour		INT,
		min		INT,
		sec		INT,
		lon		REAL,
		lat		REAL,
		place		TEXT,
		zone		TEXT,
		house		INT,
		info		TEXT
	    )
	]]

	res, err = win.db.exec[[
	    CREATE TABLE category (
		id		INTEGER PRIMARY KEY AUTOINCREMENT,
		parent		INT,
		name		TEXT
	    )
	]]

	res, err = win.db.exec[[
	    INSERT INTO category (id, parent, name) VALUES (1, 0, "Charts")
	]]
    end
end

local function file(self)
    local win = self.window.db_win
    local name = gui.filechooser("DB SQL file", "*.db", pref.db_sql.path)
    
    if name then
	pref.db_sql.path = name

	win.db.close()
		
	open_db(win)    
	load_tree(win)
    end
end

-- Main

local function main(self)
    if self.window.db_win then
	self.window.db_win.show()
	return
    end

    if not pref.db_sql then
	pref.db_sql = {
	    autoload = false,
	    path = "data/charts/chart.db"
	}
    end

    local win = gui.window(500, 250, "Data base - SQL")
    
    local menu = win.menubar(0, 0, win.w(), 24)
    
    --
    
    local menu_1 = menu.itemgroup("Category")
    
    local i1, i2, i3 = menu_1.item(
	{ label = "New", callback = category_new },
	{ label = "Rename", callback = category_rename },
	{ label = "Delete", callback = category_del }
    )

    i1.window = self.window
    i2.window = self.window
    i3.window = self.window

    --

    local menu_2 = menu.itemgroup("Chart")
    
    local i1, i2, i3 = menu_2.item(
	{ label = "Save from active", callback = chart_from, key = "F2" },
	{ label = "Load to active", callback = chart_to, key = "F3" },
	{ label = "Delete", callback = chart_del, key = "F8" },
	{ label = "---" }
    )
    
    i1.window = self.window
    i2.window = self.window
    i3.window = self.window

    local auto = menu_2.checkbutton(0, 0, 0, 18, "Autoload")
    auto.callback = chart_autoload
    auto.value(pref.db_sql.autoload)

    --
    
    local menu_3 = menu.itemgroup("Options")

    i1 = menu_3.item(
	{ label = "File...", callback = file }
    )

    i1.window = self.window
    
    --
    
    local tree = win.browser(0, menu.b(), win.w(), win.h() - menu.h(), "")
    tree.indented(true)
    
    tree.labels{
	{ label = "Charts", width = 200 }, 
	{ label = "Moment" }
    }
    
    win.resizable(tree)

    win.tree = tree
    win.info = info
    win.auto = auto
    win.parent = self.window
    
    self.window.db_win = win 

    open_db(win)    
    load_tree(win)

    win.show()
end

return {label = "Data base/SQL", callback = main, key = "F10"}