local dbi = require'DBI'
local cqueues = require'cqueues'

local function db_do(db, sql, ...)
	-- print('>>',sql,...)
	local stmt = assert(db:prepare(sql))
	::tryagain::
	local ok, err = stmt:execute(...)
	if not ok and err:match"locked" then
		print'still waiting'
		cqueues.sleep(1)
		goto tryagain
	end
	assert(ok, err)
	return stmt
end

local extra_methods = {}
function extra_methods.fetch_one(db, sql, ...)
	local stmt = db_do(db, sql, ...)
	local row = stmt:fetch(true)
	stmt:close()
	return row
end
function extra_methods.fetch(db, sql, ...)
	local stmt = db_do(db, sql, ...)
	local rows, i = {}, 1
	for row in stmt:rows(true) do
		rows[i] = row
		i = i + 1
	end
	stmt:close()
	return rows
end
function extra_methods.exec(db, sql, ...)
	local stmt = db_do(db, sql, ...)
	stmt:close()
end

local function get_conn()
	local c = assert(dbi.Connect('SQLite3','database.db'))
	-- fuck it
	local mt = getmetatable(c)
	for k,v in pairs(extra_methods) do mt.__index[k] = v end
	mt.__close = function(x) x:close() end
	c:exec('pragma foreign_keys=ON;')
	return c
end

local function apply_migrations(migrations)
	local conn <close> = get_conn()
	local ver = conn:fetch_one"pragma user_version".user_version
	for i = ver+1, #migrations do
		print("applying migration #"..i)
		conn:exec(migrations[i])
	end
	conn:exec("pragma user_version="..#migrations)
	assert(conn:commit())
end


return {conn=get_conn,apply_migrations=apply_migrations}