import sqlite3


def write_sqlite_file(plants_dict, filename, return_connection=False):
	"""
	Write database into sqlite format from nested dict.

	Parameters
	----------
	plants_dict : dict
		Has the structure inherited from <read_csv_file_to_dict>.
	filename : str
		Output filepath; should not exist before function call.
	return_connection : bool (default False)
		Whether to return an active database connection.

	Returns
	-------
	conn: sqlite3.Connection
		Only returned if `return_connection` is True.

	Raises
	------
	OSError
		If SQLite cannot make a database connection.
	sqlite3.Error
		If database has already been populated.

	"""
	try:
		conn = sqlite3.connect(filename)
	except:
		raise OSError('Cannot connect to {0}'.format(filename))
	conn.isolation_level = None  # faster database insertions

	c = conn.cursor()

	try:
		c.execute('''CREATE TABLE powerplants (
						country TEXT,
						country_long TEXT,
						name TEXT,
						gppd_idnr TEXT UNIQUE NOT NULL,
						capacity_mw REAL,
						latitude REAL,
						longitude REAL,
						primary_fuel TEXT,
						other_fuel1 TEXT,
						other_fuel2 TEXT,
						other_fuel3 TEXT,
						commissioning_year TEXT,
						owner TEXT,
						source TEXT,
						url TEXT,
						geolocation_source TEXT,
						wepp_id TEXT,
						year_of_capacity_data INTEGER,
						generation_gwh_2013 REAL,
						generation_gwh_2014 REAL,
						generation_gwh_2015 REAL,
						generation_gwh_2016 REAL,
						generation_gwh_2017 REAL,
						generation_data_source TEXT,
						estimated_generation_gwh REAL )''')
	except:
		raise sqlite3.Error('Cannot create table "powerplants" (it might already exist).')

	c.execute('begin')
	for k, p in plants_dict.iteritems():
		stmt = u'''INSERT INTO powerplants VALUES (
					?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'''
		vals = (
				p['country'],
				p['country_long'],
				p['name'],
				p['gppd_idnr'],
				p['capacity_mw'],
				p['latitude'],
				p['longitude'],
				p['primary_fuel'],
				p['other_fuel1'],
				p['other_fuel2'],
				p['other_fuel3'],
				p['commissioning_year'],
				p['owner'],
				p['source'],
				p['url'],
				p['geolocation_source'],
				p['wepp_id'],
				p['year_of_capacity_data'],
				p['generation_gwh_2013'],
				p['generation_gwh_2014'],
				p['generation_gwh_2015'],
				p['generation_gwh_2016'],
				p['generation_gwh_2017'],
				p['generation_data_source'],
				p['estimated_generation_gwh'])
		c.execute(stmt, vals)

	c.execute('commit')

	index_stmt = '''CREATE INDEX idx_country ON powerplants (country)'''
	c.execute(index_stmt)

	if return_connection:
		return conn
	else:
		conn.close()