var DB = {
	type: {
		primary_key: { name: "INTEGER PRIMARY KEY NOT NULL"},
		string:      { name: "varchar", limit: 255 },
		text:        { name: "text" },
		integer:     { name: "integer" },
		float:       { name: "float" },
		decimal:     { name: "decimal" },
		datetime:    { name: "datetime" },
		timestamp:   { name: "datetime" },
		time:        { name: "datetime" },
		date:        { name: "date" },
		binary:      { name: "blob" },
		boolean:     { name: "boolean" }
	},

	FIRST: "first",
	ALL:   "all",

	quote: function(name) {
		return "'" + name + "'";
	}
};

DB.table = Class.create({
	initialize: function(table_name, options) {
		this.name = table_name;
		this.options = options || "";
		this.columns = [];

		this.columns.push({
				name: DB.quote('id'),
				type: DB.type.primary_key.name
			});
	},

	column: function(col_name, type, options) {
		var col = { 
			name: DB.quote(col_name),
			type: DB.type[type].name,
			limit: false
	 	};

		if (options && options.limit) {
				col.limit = options.limit;
		} else if (DB.type[type].limit) {
			col.limit = DB.type[type].limit;
		}

		this.columns.push(col);
	},

	to_sql: function() {
		var sql = "CREATE TABLE " + this.name;
	 
		if (this.columns.length > 0) {
			sql += "( " + this.columns.map( function(col) {
					return col.name + " " + col.type + 
						(col.limit ? (" (" + col.limit + ")") : "");
				}).join(', ');

			sql += " )" + this.options;
		}

		return sql;
	}
});

DB.Store = Class.create( {
	initialize: function(db_name) {
		try {
			// Currently we store all databases in the profile directory. 
			this.db_path = File.join(FileUtils.profile_dir(), db_name);
			log.debug("Using DB: " + this.db_path);

			var db_service = LG.service("@mozilla.org/storage/service;1",
				'mozIStorageService');

			// Create a database connection, and create a new DB if necessary.
			this.db = db_service.openDatabase(File.instance(this.db_path));

			this.build_classes();
		}
		catch(e) {
			log.error("ERROR: caught exception in DB.Store.initialize(): " + e);
		}
	},

	// Process SQL 
	execute: function(sql) {
		var results = [];

		log.debug("executing sql: " + sql);
		try {
			var statement = this.db.createStatement(sql);

			var names = []

			var num_cols = statement.columnCount;
			var col;

			for(col = 0; col < num_cols; col++) {
				names.push(statement.getColumnName(col));
			}

			while(statement.executeStep()) {
				var row = new Hash();

				for (col = 0; col < num_cols; col++) {
					var type = statement.getTypeOfIndex(col);

					switch(type) {

					case statement.VALUE_TYPE_INTEGER:
						row[names[col]] = statement.getInt32(col);
						break;

					case statement.VALUE_TYPE_FLOAT:
						row[names[col]] = statement.getDouble(col);
						break;

					case statement.VALUE_TYPE_NULL:
						row[names[col]] = null;
						break;

					case statement.VALUE_TYPE_BLOB: // TODO: Not supporting blobs for now
						var size = 0;
						var data = {};
						statement.getBlob(col, data);
						row[names[col]] = data;
						break;

					default:
						//log("row[" + names[col] + "]: " + statement.getString(col));
						row[names[col]] = statement.getUTF8String(col);
					}
				}
				results.push(row);
				//log("execute - results: " + Object.inspect(results));
			} 
		}

		catch(e) {
			log.error("Error executing SQL: " + e);
			return results;
		} 
		finally {
			statement.reset();
		}

		return results;
	},

	// Run a SQL query and return an array of hash records
	select: function(sql) { return this.execute(sql) },

	// Return a single hash record
	select_one: function(sql) {
		var result = this.select(sql);
		if(result)
			return result.first();
		else
			return null;
	},

	// Return a single value from a record
	select_value: function(sql) {
		var result = this.select_one(sql);
		if(result) {
			result.remove('toJSONString');
			return result.values().first();
		}
		else
			return null;
	},

	insert: function(sql, id_value) {
		this.execute(sql)
		if(id_value) {
			return id_value;
		}
		else {
			return this.db.lastInsertRowID;
		}
	},

	// Transaction Support 
	begin_db_transaction: function() {
		this.db.beginTransactionAs(db.TRANSACTION_DEFERRED);
	},

	commit_db_transaction: function() {
		this.db.commitTransaction();
	},

	rollback_db_transaction: function() {
		this.db.rollbackTransaction();
	},

	// Schema methods

	// Create a table using a passed in builder function.
	// NOTE: tables automatically have an id column as primary_key.
	create_table: function(table_name, builder) {
		var table = new DB.table(table_name);

		if (builder) {
			builder(table);
		}

		this.execute(table.to_sql());
		this.build_class(table_name);
	},

	drop_table: function(table_name) {
		this.execute("DROP TABLE " + table_name);
	},

	// TODO: Figure out why this causes an error!!!
	rename_table: function(old_name, new_name) {
		this.execute("ALTER TABLE " + old_name + " RENAME TO " + new_name);
	},

	add_column: function(table_name, col_name, type, limit) {
		limit = limit || "";

		this.execute("ALTER TABLE " + table_name + " ADD " +
				DB.quote(col_name) + " " + type + " " + limit);
	},

	remove_column: function(table_name, col_name) {
		this.execute("ALTER TABLE " + DB.quote(table_name) + " DROP " + 
				DB.quote(col_name) + ";");
	},

	get_column_options: function(options) {
		var opt_sql = "";

		if(options.default)
			opt_sql = " DEFAULT " + options.default + ", " + options.column;

		if(options.null)
			opt_sql += " NOT NULL";

		return opt_sql;
	},

	// Schema Reflection... 
	table_names: function() {
		var tables = this.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
		.pluck('name');

		return $A(tables);
	},

  column_names: function(table_name) {
		var names = this.execute("PRAGMA table_info(" + table_name + ")").pluck('name');
		return names.select( function(name) { return (name != "id") } );
	},

	build_classes: function() {
		var table_names = this.table_names();

		for(var i = 0; i < table_names.size(); i++) {
			this.build_class(table_names[i]);
		}
	},

	build_class: function(table_name) {
		// Create the class name from the table name.
		var class_name = this.table_to_class_name(table_name);
		var ClassImpl = Class.create();

		ClassImpl.db = this;
		ClassImpl.table = table_name;

		DB[class_name] = ClassImpl;
		Object.extend(DB[class_name], DB.ClassMethods);
		Object.extend(DB[class_name].prototype, DB.InstanceMethods);

		var col_names = this.column_names(table_name);
		Object.extend(DB[class_name].prototype, { 
				column_names: col_names,
				table_name: table_name,
				parent: DB[class_name]
			});

//		log.debug("Created DB class " + class_name);
//		for(var prop in DB[class_name]) { log.debug("DB[" + class_name + "][" + prop + "]: " + DB[class_name][prop]) };
	},

	table_to_class_name: function(table_name) {
		var class_name = ("-" + table_name.dasherize()).camelize();
		class_name = class_name.gsub(/(.*)s$/, "#{1}");

		return class_name;
	}
});

DB.Store.remove = function(db_name) {
	File.remove(File.join(FileUtils.profile_dir(), db_name));
}

/* Extend with this to get easy access to your table.  
   In order for these to work you need to set:
     this.db = <some DB.Store>;
     this.table = <some table name>;
*/
DB.ClassMethods = {
	count: function() {
		return this.db.select_value("SELECT count(*) FROM " + this.table);
	},

	find_by_sql: function(sql) {
		this.db.select(sql);
	},

	/*
	 TODO: Not all of these implemented yet...
	 VALID_FIND_OPTIONS = [ :conditions, :include, :joins, :limit, :offset,
	 :order, :select, :readonly, :group, :from, :lock ]
	 */
	find: function(which, options) {
		if (options == null) { options = {}; }

		if (which instanceof Array) { // List of IDs

		} else if (which instanceof Number) {  // Single ID

		} else if (which == 'first') { // First record found
			options.limit = 1;
			return this.db.select_one( this.construct_find_sql(options));

		} else if (which == 'all') { // All records
			return this.db.select( this.construct_find_sql(options));
		}

		return null;
	},

	construct_find_sql: function(options) {
		options = options || {};

		var sql  = "SELECT " + (options.select ? options.select : "*");
		sql     += " FROM " + (options.from ? options.from : this.table);

		if (options.conditions) {
			sql   += " WHERE " + options.conditions;
		}	

		if (options.order) {
			sql   += " ORDER BY " + options.order;
		}

		// Offsets need limits
		if (options.limit) {
			sql   += " LIMIT " + options.limit;

			if (options.offset) {
				sql += " OFFSET " + options.offset;
			}
		}

		return sql;
	},

	/*
	delete: function () {
		this.delete_items();  // Tidy up
		var statement = mDBConn.createStatement("DELETE FROM playlist WHERE ID = (?1)");
		statement.bindInt32Parameter(0, this.id);
		statement.execute();
	},

	delete_items: function () {
		var statement = mDBConn.createStatement("DELETE FROM play_item WHERE playlist = (?1)");
		statement.bindInt32Parameter(0, this.id);
		statement.execute();
	},
	*/

	// Creates a record with values matching those of the instance attributes
  // and returns its id.
	create: function(attributes) {
		return new this(attributes).save();
	}
};

DB.InstanceMethods = {
	initialize: function(attributes) {
		attributes = $H(attributes);
		var me = this;
		attributes.each( function(pair) {
				me[pair.key] = pair.value;
			});

		this.connection = this.parent.db;
	},

	// TODO: Figure out what kind of escaping we need to do to make this safe
	// from SQL injections etc...
	save: function() {
		var sql = "INSERT INTO " + this.table_name +
			"(" + this.column_names.join(',') + ") " + "VALUES("; 

		var me = this;
		var first = true;
		this.column_names.each( function(name) { 
				if(first) {
					first = false;
				} else {
					sql += ", ";
				}

				if ('string' == typeof me[name])
					sql += ("'" + me[name] + "'"); 
				else
					sql += me[name];
			});
		sql += ")";

		return this.connection.insert(sql);
	}
};