require 'sqlite3'

module SQLite3Tools
	class ::Array
		def sqlite3_to_str
			self.map { |sym| "`" + sym.to_s + "`" }.join(", ")
		end
	end

	class ::Array
		def to_hash other
			Hash[ *( 0...self.size() ).inject( [] ) { |arr, ix| arr.push( other[ix], self[ix] ) } ]
		end
	end

	class SQLite3::Database
		def select columns, query
			result = [];
			self.execute "SELECT #{columns.sqlite3_to_str} #{query}" do |row|
				result.push ( row.to_hash columns )
			end
			result
		end
	end
end

class Backend < SiteManager

	include SQLite3Tools

	def initialize db_paths
		dbs = db_prepare db_paths
		@info_db = dbs[:info]
		@action_db = dbs[:actions]
	end

	private
	def db_prepare db_paths

		def db_initialize type, db
			sql = File.read "#{type}.sql"
			db.execute_batch sql
		end

		dbs = {}
		db_paths.each do |type, path|
			dbs[type] = [ ( File.exists? path ), ( SQLite3::Database.new path ) ]
		end

		# Set sqlite3 options here
		dbs.each do |type, db|
			db[1].type_translation = true
		end

		dbs.each do |type, db|
			begin
				( db_initialize type, db[1] ) unless db[0]
			rescue
				File.delete db_paths[type]
			end
		end

		dbs.each do |type, db|
			dbs[type] = db[1]
		end

		return dbs

	end

	private
	def make_placeholders n
		n = n - 1
		placeholders = "?"
		n.times { placeholders << ", ?" }
		placeholders
	end

	public
	def select_all_actions_by_status action, fields, status
		query = "FROM `#{action}` WHERE `status` = '#{status}'"
		rows = @action_db.select fields, query

		h = { :action => action }
		rows.map { |t| t.merge h }
	end

	def insert_action table, data
		@action_db.execute "INSERT INTO #{table} VALUES ( ?, #{make_placeholders data.length} )", ( data.values.insert 1, "pending" )
		@action_db.last_insert_row_id
	end

	def delete_action_by_id action, id
# 		puts "DELETE FROM `#{action}` WHERE `rowid` = #{id}"
	end

	def select_info_by_id table, fields, id
		info = @info_db.select fields, "FROM `#{table}` WHERE `rowid` = '#{id}'"
		info.first
	end

	def select_info_by_name table, fields, name
		info = @info_db.select fields, "FROM `#{table}` WHERE `name` = '#{name}'"
		info.first
	end

	def insert_info table, data
		@info_db.execute "INSERT INTO #{table} VALUES ( #{make_placeholders data.length} )", data.values
		@info_db.last_insert_row_id
	end
end